【SQL Server + MySQL二 】SQL: DDL数据定义【定义、修改、删除基本表】，DML【憎删改查】，DCL数据控制语言

极其感动！！！当时学数据库的时候，没白学！！

• 时隔很长时间回去看数据库的笔记都能看懂，每次都靠这份笔记巩固
• 真的是语雀分享要花钱，要不一定把笔记给贴出来
• (;´༎ຶД༎ຶ`) ，除了vue和react之外，最常看的一份笔记了！！！！

xd们，一定要收藏粘贴下来，真的没有自夸！！！！

• 当时学的时候，想来：就一份笔记么？脑子有病的人才一直记来记去的【框框自嘲】！有啥可分享的？现在，真香！！！

三、关系数据语言-- SQL概述

3-1 SQL概述

1. 关系数据库的标准语言

2. 特点 ：

   • 综合统一： DDL，DML，DCL
   • 高度非过程化
   • 面向集合的操作方式
   • 同一种语法结构提供两种使用方式
     • 自含式语言
     • 嵌入式语言
   • 语言简洁

3. SQL三大类11个命令词 ：

   • 数据定义DDL ： create，drop，alter
   • 数据操纵DML ： select ，insert，update，delete
   • 数据控制DCL：grant，revoke

4. SQL支持数据库的三级模式结构 ：

   

5. 用户用SQL语言对 基本表、视图、索引等进行操作

   • 基本表（模式）
   • 存储文件（内模式）
   • 视图（外模式）

   

3-2 DDL 数据定义功能

1. • 定义表（模式）
     • 创建删除表，修改表定义
   • 定义视图（外模式）
     • 创建删除视图
     • 间接修改视图定义 ： 删除+创建
   • 定义索引（内模式）
     • 创建删除索引
     • 间接修改索引定义 ： 删除+创建

2. 定义、删除与修改基本表

3-2-1 定义基本表

3-2-1-1 创建基本表

3-2-1-1-1

1. 定义基本表 ：

   • 关系名（表名）
   • 属性名（列名）
   • 属性数据类型
   • 完整性约束

2. 定义基本表语法格式 ：

   

3. 例子：

   create database teach;   -- 建表
   use teach；
   create table student{
   	sid char(8) primary key,
   	sname varchar(20) not null,
   	sqender char(1),
   	sdept int,
   	sbirth date
   };
   1
   2
   3
   4
   5
   6
   7
   8
   9

3-2-1-1-2 表级完整性约束 与 列级完整性约束

1. 常用完整性约束

   • 主码约束 ： primary key
   • 参照完整性约束 ： foreign key...references...
   • 唯一性约束 ： unique
   • 非空值约束 ： not null
   • 取值约束 ： check

2. SQL支持的数据类型

   • 整数数据

     • bigint

     • int

     • smallint

     • tinyint

     • bit

       

   • 精确数值数据

   • decimal

   • numeric

     

   • 浮点数值数据

     • float ：

     • real

     • 

   • 字符串数据

     • char

     • varchar

       

   • 日期数据类型

     

3-2-2 SQL支持的数据类型

3-2-3-1

1. 整数数据 ：

   • bigint：以8个字节来存储正负数，范围 ： -2⁶³ 到 2⁶³ - 1
   • int ： 4个字节
   • smallint ： 2个字节
   • tinyint ： 最小的整数类型，存储正负数
   • bit ： 值只能是0 或 1

   

2. 精确数值 数据 ：

   • decimal ： 存储从
   • numeric

3. 浮点 数值 数据 ：

   • float
   • real
   • 

4. 字符串数据 ：

   • char
   • varchar
   • 

5. 日期时间数据

   • date ： 日期类型
   • time
   • datetime
   • 

6. 例子 ：

   • 建立一个”学生选课“表SC，由学号Sno，课程号Cno，成绩Grade组成，其中（Sno，Cno）为主码

   • Create table SC(
     	Sno char(5),
         Cno int,
         Grade int,
         primary key(Sno,Cno)
     )
     1
     2
     3
     4
     5
     6

3-2-3-2 创建教学数据库中四个基本表

1. 

2. create table T(
   	TID char(4),
       Tname varchar(8) not null,
       title varchar(10),
       primary key(TID)
   );
   1
   2
   3
   4
   5
   6

    table C(
   	CID char(4),
       CNAME varchar(10) not null,
       TID char(4),
       primary key(CID),
       foreign key(TID) references T(TID)
   )
   1
   2
   3
   4
   5
   6
   7

   create table S(
   	SID char(4) primary key,
       SNAME varchar(10) not null,
       age samllint,
       sex char(1)
   )
   1
   2
   3
   4
   5
   6

   create table SC(
   	SID char(4) not null,
       CID char(4) not null,
       score real,
       primary key(SID, CID),
       foreign key(SID) references S(SID),
       foreign key(CID) references C(CID)
   )
   1
   2
   3
   4
   5
   6
   7
   8

3-2-3 修改基本表

3-2-3-1

1. 增加 列 【可以 增删列，可以增删完整性约束】

   • alter table STUDENT add CTIME datetime
   • alter table STUDENT add primary key(sid)

2. 删除列

   • alter table STUDENT drop CTIME

   • 删除主键约束 ：

     • alter table SYUDENT drop 主键约束的名字

   • 删除 学生姓名必须取唯一值的约束

     • alter table STUDENT drop unique(Sname)

   • 删除 一列

     alter table Student drop column score;

3. 修改列数据类型

• alter table SC alter column score INT;

3-2-4 删除基本表

1. drop table <表名>;

3-3 DML

3-3-1 SELECT 表单查询

1. 查询语句格式 ：

   select A,B,...C
   from r,t,y
   where condition
   Group By columns
   Having condition
   Order by columns
   1
   2
   3
   4
   5
   6

   

2. 两者不同 ：

   • select是等值选择
   • 在condition中要有等值连接条件
   • select结果不是集合是包，复元组

3-3-1-1 单表查询

-- 查询语句格式
select [all|distinct]
	<目标列表达式>[<别名>]
	<目标列表达式>[<别名>]
from <表名或视图名>[别名]
	[<表名或视图名>[别名]]
[where<条件表达式>]
[group by<列名>]
	[having<条件表达式>]
[ordedr by <列名>]
	
1
2
3
4
5
6
7
8
9
10
11

1. 查询涉及一个表，是一种最简单的查询操作

   • 选择表中的若干列 ： select 子句
   • 选择表中的若干元组 ： where 子句
   • 对查询结果排序 ： order by 子句
   • 使用集函数 ： 5个集函数
   • 对查询结果分组 ： group by 子句
   • 对分组之后结果进行筛选 ： having 子句

2. 选择表中的若干列 ：

• 属投影运算 ： 不消除重复行

3-3-1-1-1 select子句 ： 查询指定属性列 :

1. • 查询所有列
   • 查询经过计算的值

2. 例题 ：

   • 查询全体学生的学号与姓名

   select sno,sname from student;
   1

   

3. 例题 ：

   • 查询全体学生的姓名及其出生年份

     select ssex,2020-age from student;
     select sname,year(getdate())-sage as 'birth' from student;
     select year(getdate())
     1
     2
     3

     • year(): 返回指定日期的“年”日期部分的整数

4. 例题 ：

   select Sname,'Year of Birth:',2020-Sage,Lower(Sdept) from Student;
   1

   

​

select Sname Name,'Year of Birth:' BIRTH,2020-Sage BIRTHDAY,lower(Sdept) department
from student;
1
2

3-3-1-1-2 选择表中若干元组【where】

1. 消除取值重复的行

• 在SC表中查询选修了课程的学生学号【一个学生选修多项，会有重复的学号】

  select distinct Sno from SC;
  1

• 查询选修课程的各种成绩

  select distinct Cno,Grade from SC;
  1

2. 查询满足条件的元组

   • 选择运算
     • 通过 where Condition子句实现
     • 

3. 【where】查询计算机系全体学生的名单

   select Sname from Student where Sdept = 'CS'
   1

   

4. 【where】

   • 使用谓词 ：Between…and…

   • not between … and…

     • between 后： 范围的下限
     • and后：范围的上限

   • 多重条件查询实现

   • select Sname,Sdept,Sage
     from Student
     where Sage between 20 and 23
     1
     2
     3

5. 【where】【确定集合】使用谓词：

   • in<值表>

   • not in<值表>

   • <值表> : 用逗号分隔的一组取值

   • 查询是信息系、数学系和计算机科学系的学生的姓名和性比

     select name,Ssex
     from  Student 
     where Sdept in('IS','MA','CS')
     1
     2
     3

6. 【where】【字符串匹配】使用谓词 LIKE或NOT LIKE

   • [not] like ‘<匹配串>’[escape’<换码字符>']

   • 通配符 ：

     • % : 代表任意长度的字符串
     • _ ： 代表任意单个字符

   • 查询姓”欧阳“且全名为三个汉字的学生的姓名

     • 
       select Sname,Sno,Ssex
       from Student
       where Sname like '欧阳_'
       1
       2
       3
       4

7. Escape 短语

   • 当用户查询的字符串本身含有 % 或_时，要使用**ESCAPE’<换码字符>'**短语对通配符进行转义

   • 查询DB_…i…

     select *
     from Course
     where Cname Like 'DB\_%i__' ESCAPE '\';
     1
     2
     3

8. 涉及空值的查询

   • 使用谓词IS NULL 或者IS NOT NULL

   • select Sno,Cno
     from SC
     where Grade is null;
     1
     2
     3

9. 多重条件查询

   • 逻辑运算符and和or来联结多个查询条件

     • and优先级高于or
     • 用括号改变优先级

   • select Sname
     from Student 
     where Sdept = 'CS' and Sage<20;
     1
     2
     3

   • 查询信息系(IS)/数学系（MA）和计算机(CS)学生的姓名和sex

     select Sname,Ssex
     from student
     where Sdept in('IS','MA','CS')
     1
     2
     3

   • 

3-3-1-1-3 对查询结果排序【5.3P22】

1. 使用ORDER BY 一个列名或多个列名子句 :

   • 按一个或多个属性列排序
   • 升序： ASC；降序：DESC； 缺省值为升序

2. 当排序列含空值时

   • ACS：排序列为空值得元组最后显示
   • DESC： 排序列为空值得元组最先显示

3. 例题 ： 查询选修了3号课程的学生的学号及成绩，查询结果按分数降序排列

select Sno,Grade
	from SC
	where Cno = 3
	order by Grade desc;
1
2
3
4

4. 例题 ： 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中得学生按年龄降序排列

   select * 
   from Student
   order by Sdept,Sage desc
   1
   2
   3

3-3-1-1-4 使用集函数【统计不计Null值】

1. 统计个数 ： count(<列名>)

   • 计算列总和 ： sum(<列名>)
   • 计算列平均值 ： avg(<列名>)
   • 求列最大值 ： max(<列名>)
   • 求列最小值” min(<列名>)
   • 注意 :
     • 统计时不计NULL值
     • COUNT(*) : 统计行数
     • DISTINCT ：在列名前加DISTINCT，则统计时去掉重复行

2. 例题 ：

   • 查询学生的总人数：

     • select count(*) from student
     • select count(sno) from student

   • 查询选修了课程的学生人数

     • select count(distinct Sno) from SC

   • 计算1号课程的学生平均成绩

     select avg(Grade)
     	from SC
     	where Cno = 1;
     1
     2
     3

   • 查询选修1号课程的学生最高分数

   select max(Grade)
   	from SC
   	where Cno = 1;
   1
   2
   3

3. 对查询结果 分组

   • 分组方法 ： 按指定的一列或多列值分组，值相等为一组
   • 使用group by 字句后，select子句的列名列表中只能出现分组属性和集函数
   • group by子句的作用对象是查询的中间结果表

4. 【对查询结果分组】求各个课程号及相应的课程成绩在90分以上的学生人数

   select Cno,Count(Sno) 
   	from sc
   	where Grade >= 90
       grounp by cno;
   1
   2
   3
   4

   • 求各个课程号及相应的选课人数

     select cno,count(*) from sc group by cno
     1

5. 使用having短语筛选最终输出结果

   • 只用满足having短语指定条件的组才输出

6. having短语 与where子句的区别 ： 作用对象不同

   • 查询选修3门以上课程的学号

     select Sno 
     	from SC
     	group by Sno
     	having count(*) >= 3
     1
     2
     3
     4

   • 查询有3门以上课程在90分 以上的学生的学号及90分以上的课程数

     select Sno, count(*)
     	from SC
     	where Grade >= 90
     	group by Sno
     	having count(*) >= 3;
     1
     2
     3
     4
     5

7. 统计每门课程的最高分

   select cno,max(grade)
   	from SC
   	group by cno
   1
   2
   3

3-3-1-2 连接查询

3-3-1-2-1 交叉连接（广义笛卡尔积）

1. 等值连接

   • 查询每个学生及其选修课程的学生

     • select Student.*,SC.* 
       	FROM Student,SC
       	where Student.Sno = SC.SNO;
       1
       2
       3

     • 等值连接

2. 查询计算机系（CS）学生的学号、姓名、所在系，选修的课程号，课程名和成绩

select SC.Sno,Sname,Sdept,SC.Cno,Cname,Grade
	from Student,SC,Course
	where Student.Sno = SC.Sno and Course.Cno = SC.Cno and Sdept='CS';
1
2
3

3-3-1-2-1 交叉连接（自身连接）

1. select a1.Cname,a2.Cname
   	from Course a1,Course a2
   	where a1.Cpno = a2.Cno
   1
   2
   3

3-3-3 SQL—数据更新UPDATE

3-3-3-1

1. 

2. 插入数据

   • 插入单个元组

     insert 
     	into <表名>[(<属性列1>)],
     	values (<常量1>[,<常量2>]...)
     1
     2
     3

     • 功能 ： 把新元组插入指定表中

   • 插入子查询结果

     insert into <表名>
     [(<属性列1>[,<属性列2>…])]
     子查询;

     
     - 
     
     1
     2
     3

3. 插入数据—例子 ：

   • 将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；18岁）插入到Student表中

     insert 
        into Student 
        values('95020','陈东', 'M',18,'IS');
     insert into SC(Sno,Cno)
     	values('95020',1);
     insert into SC values('95020',2,null),('95021',2,null),('95022',2,null)
     1
     2
     3
     4
     5
     6

4. 插入子查询—例子 ：

   -- 建表
   create table Deptage(Sdept char(15)),Avage smallint);
   
   -- 插入数据
   insert into Deptage(Sdept,Avgage)
   	select Sdept,Avg(Sage)
   	from Student
   	group by Sdept;
   1
   2
   3
   4
   5
   6
   7
   8

3-3-3-2 完整性校验

1. DBMS在形式插入语句时会检查元组是否破环表上已定义的完整性规则
   • 实体完整性
   • 参照完整性
   • 用户定义的完整性
     • 有NOT NULL约束的属性列是否提供非空值
     • 对于有unique约束的属性列是否提供了非重复值
     • 对于有值域约束的属性列所提供的属性值是否在值域范围内

3-3-33 修改数据

1. 修改指定表中满足where子句条件的元组

   update <表名>
   	set <列名>=<表达式>
   	where <条件>
   1
   2
   3

2. 三种修改方式

   • 修改某一个元组的值
   • 修改多个元组的值
   • 带子查询的修改语句

3. 修改某一个元组的值

   • 将学生95001的年龄改为22岁

     update Student
     	set Sage = 22
     	where Sno= '95001';
     1
     2
     3

   • 将所有学生的年龄增加1岁

     update student set Sage = Sage+1;
     1

   • 将信息系所有学生的年龄增加1岁

     update Student
     	set Sage = Sage +1
     	where Sdept ='IS'
     1
     2
     3

4. 带子查询的修改语句

   • 将计算机科学系全体学生的成绩置0

     update SC
     	set Grade=0
     	where 'CS'=(select Sdept from Student where Student.Sno = SC.Sno);
     1
     2
     3

3-3-4 删除

3-3-4-1

1. • 删除某一个元素的值
   • 删除多个元组的值
   • 带子查询的删除语句
2. • 删除学号为95019的学生记录

   delete 
   	from Student
   	where Sno = '95019';
   1
   2
   3

   • 删除2号课程 的所有选课记录

     delete
     	from SC
     	where Cno = 2;
     1
     2
     3

   • 删除所有的学生选课记录

     delete from SC;
     1

3. 带子查询的删除语句 ：

• 删除计算机科学系所有学生的选课记录

  delete 
  	from SC
  	where 'CS'=(select Sdept 
                 from Student
                 where Student.Sno = SC.Sno)
  1
  2
  3
  4
  5

3-3-3-2 带子查询的删除语句

3-4 视图

3-4-1 关系（Relations）

1. 三种类型关系

   • 表（Table）-- 基表，存储关系（Base tables，Stored relations）
   • 视图（Views）— 虚拟关系
   • 临时结果—用于构建子查询的结果

2. SQL支持数据库的三级模式结构 :

   • 

3. 视图的特点 ：

   • 虚表 ： 一个或几个基本表（或视图）导出的表
   • 只存放试图的定义，不会出现数据冗杂
   • 基表中的数据发生改变，从试图中查询出的数据也随之改变

4. 基于视图的操作

   • 定义视图（DDL）
     • 建立
     • 定义基于该视图的新视图
     • 删除
   • 查询视图（DML）
   • 更新视图（DML）

3-4-2 定义视图（DDL）

1. 建立视图 ：

   create view <视图名>[<列名>]
   	as <子查询>
   	[with check option];
   1
   2
   3

2. 组成视图的属性列名或全部省略或全部指定 ：

   • 省略视图的各个属性列名，则隐含该视图由子查询中select子句目标列中的各字段组成
   • 必须明确指定组成视图的所有列名的情形 ：
     • 某个目标列不是单纯的属性名，而是集函数或列表达式
     • 目标列为*
     • 多表连接时选出了几个同名列作为视图的字段
     • 需要在视图中为某个列启用新的合适的名字

3. 视图定义的select语句 ：

   • 不能包含order by子句和distinct短语

4. with check option

   • 透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）

3-4-3 建立视图 ：

1. DBMS执行create view语句时只是把视图的定义存入数据字典，并不执行其中的select语句

2. 只是对视图查询时，才按视图的定义从基本表中将数据查出

3. 行列子集视图 ：

   • 从单个基本表导出

   • 只是去掉了基本表的某些行和某些列，但保留了码

   • 例子 ： 建立信息系学生的视图 ：

     create view V_StuIS as
     	select Sno,Sname,Sage
     	from Student
     	where Sdept='IS'
     1
     2
     3
     4

4. with check option 的视图 ：

• 建立信息系学生的视图，并要求透过该视图进行的更新操作涉及信息系学生

  create view V_STUIS
  	as select Sno,Sname,Sage from Student
      where Sdept = 'IS' with check option;
  1
  2
  3

• 对V_StuIS 视图的更新操作 ：

  • 修改操作 : DBMS自动加上Sdept =‘IS’的条件
  • 删除操作： DBMS自动加上Sdept=’IS’的条件
  • 插入操作 ： DBMS 自动检查Sdept属性值是否为’IS’
    • 如果不是，则拒绝该插入操作
    • 没有提供Sdept属性值，则自动定义Sdept为’IS’

5. 基于多个基表的视图

   • 建立信息系选修了1号课程的学生视图

     create view V_S1(Sno,Sname,Grade)
     	ass select Student.Sno,Sname,Grade
     	from Student，SC
     	where Sdept = 'IS' and
     		Student.Sno = SC.Sno and
     		SC.Cno = 1;
     1
     2
     3
     4
     5
     6

6. 基于视图的视图 ：

   • 建立信息系选修了1号课程且成绩在90分以上的学生的视图

     create view V_S2
     	as 
     	select Sno,Sname,Grade
     	from V_S1
     	where Grade >= 90;
     1
     2
     3
     4
     5

     • 视图建立在V_S1之上

7. 带表达式的视图 ：

3-4-3-1 例子 ：

1. 建立1号课程的选课试图，并要求透过该视图进行更新操作时只涉及1号课程

   create view V_SCCno
   	as
   	select Sno,Cno,Grade
   	from SC
   	where Cno = 1
   	with check option;
   1
   2
   3
   4
   5
   6

2. 以select * 方式创建的视图

   • 将student表中所有女生记录定义为一个视图

     create view V_Student1(stdnum,name,sex,age,dept)
     	as select *
     	from Student
     	where Ssex='女'
     1
     2
     3
     4

   • 修改基表Student的结构后，Student表与V_Student1视图的映像关系，导致视图不能正确工作

3. 上例可以这样写 ：

   create view V_Student2(stdnum,name,sex,age,dept)
   	as select Sno,Sname,Ssex,Sage,Sdept
   	from Student
   	where Ssex='女'
   1
   2
   3
   4

   • 为基表Student增加属性列不会破坏Student表与V_Student2视图的关系

3-4-4 删除视图

1. drop view <视图名>;
   • 该语句从数据字典中删除指定的视图定义
   • 由该视图到处的其他视图定义仍在数据字典中，但已不能使用，必须删除
   • 删除基表时，由该基表导出的所有视图定义都必须删除

3-4-4-1 删除视图 例子

1. 删除视图V_S1

   drop view V_S1;

   • 执行此语句后，V_S1 视图的定义将从数据字典中删除，

3-4-5 查询视图

1. 例1 ： 在信息系学生的视图中找出年龄小于20岁的学生

   

3-4-6 更新视图[避免]

1. DBMS ：
   • 视图实体化法(View Materialization)
   • 视图消解法(View Resolution)

3-4-7 视图的作用

1. 视图最终定义在基本表上，对视图的一切操作最终要转换对基本表的操作
2. 对于非行列子集视图进行查询或更新时还有可能出现问题
3. 视图的作用 ：
   • 能够简化用户的操作
   • 使用户能以多种角度看待同一种数据
   • 视图对重构数据库提供了一定程度的逻辑独立性
   • 视图能够对机密数据提供安全保护

3-5 索引

3-5-1 建立与删除索引

1. 建立 索引是加快查询速度的有效手段
2. 建立索引
   • DBMS自动建立
     • Primary key
     • unique
   • DBA或表的属主（即建立表的人）根据需要建立
3. 维护索引 ： DBMS自动完成
4. 使用索引 ：ＤＢＭＳ自动选择是否使用索引以及使用哪些索引

3-5-2-1 建立索引

1. create [unique][cluster] index <索引名>
   	on＜表名＞（列名）
   1
   2

   • 用<表名>指定要建索引的基本表名字
   • 索引可以建立在该表的一列
   • 用<次序>指定索引值的排列次序，升序 ： ASC，降序：DESC
   • unique 表名此索引的每一个索引值值对应唯一的数据记录

2. 唯一值索引 ：

   • 对于已含重复值的属性列不能建unique索引
   • 

3-5-2-2 删除索引

1. drop index<索引号>
   • 删除索引时，系统会从数据字典中删除有关该索引的描述
   • 例子 ： 删除Student表的idx_Stusname索引
     • drop index idx_Stusname;

3-6 DCL【数据控制语言，grant和revoke（授权和回收权力）】 和嵌入式SQL

3-6-1

1. 完整性 ：
   • 数据库的完整性 ： 数据库中数据的有效性、正确性和相容性
   • SQL语言定义完整性约束条件
     • 码
     • 取值唯一的列
     • 参照完整性
     • 其他约束条件
2. 并发控制 ：
   • 多个用户并发对数据库进行操作时，对他们加以控制、协调，以保证并发造作正确执行，并保证数据库的一致性
3. 恢复
4. 安全性

3-6-2 SQL语言的两种使用方式

1. 在终端交互方式使用，称为交互式SQL
2. 嵌入在高级语言的程序中使用，称为嵌入式SQL
   • 宿主语言： java，C