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

本文链接：https://blog.csdn.net/hannah2233/article/details/128094215

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

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

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

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

文章目录

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

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

3-1 SQL概述

关系数据库的标准语言
特点：
- 综合统一： DDL，DML，DCL
- 高度非过程化
- 面向集合的操作方式
- 同一种语法结构提供两种使用方式
  - 自含式语言
  - 嵌入式语言
- 语言简洁
SQL三大类11个命令词：
- 数据定义DDL ： create，drop，alter
- 数据操纵DML ： select ，insert，update，delete
- 数据控制DCL：grant，revoke
SQL支持数据库的三级模式结构：
用户用SQL语言对 基本表、视图、索引等进行操作
- 基本表（模式）
- 存储文件（内模式）
- 视图（外模式）

3-2 DDL 数据定义功能

- 定义表（模式）
  - 创建删除表，修改表定义
- 定义视图（外模式）
  - 创建删除视图
  - 间接修改视图定义：删除+创建
- 定义索引（内模式）
  - 创建删除索引
  - 间接修改索引定义：删除+创建
定义、删除与修改基本表

3-2-1 定义基本表

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

3-2-1-1-1

定义基本表：
- 关系名（表名）
- 属性名（列名）
- 属性数据类型
- 完整性约束
定义基本表语法格式：

例子：

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
};

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

常用完整性约束
- 主码约束： primary key
- 参照完整性约束： foreign key...references...
- 唯一性约束： unique
- 非空值约束： not null
- 取值约束： check
SQL支持的数据类型
- 整数数据
  - bigint
  - int
  - smallint
  - tinyint
  - bit
- 精确数值数据
- decimal
- numeric
- 浮点数值数据
  - float ：
  - real
- 字符串数据
  - char
  - varchar
- 日期数据类型

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

3-2-3-1

整数数据 ：
- bigint：以8个字节来存储正负数，范围： -2⁶³ 到 2⁶³ - 1
- int ： 4个字节
- smallint ： 2个字节
- tinyint ：最小的整数类型，存储正负数
- bit ：值只能是0 或 1
精确数值数据 ：
- decimal ：存储从
- numeric
浮点数值数据 ：
- float
- real
字符串数据 ：
- char
- varchar
日期时间数据
- date ：日期类型
- time
- datetime
例子：
- 建立一个”学生选课“表SC，由学号Sno，课程号Cno，成绩Grade组成，其中（Sno，Cno）为主码
- ```
Create table SC(
	Sno char(5),
    Cno int,
    Grade int,
    primary key(Sno,Cno)
)
```

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

create table T(
	TID char(4),
    Tname varchar(8) not null,
    title varchar(10),
    primary key(TID)
);

 table C(
	CID char(4),
    CNAME varchar(10) not null,
    TID char(4),
    primary key(CID),
    foreign key(TID) references T(TID)
)

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

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)
)

3-2-3 修改基本表

3-2-3-1

增加列【可以增删列，可以增删完整性约束】
- alter table STUDENT add CTIME datetime
- alter table STUDENT add primary key(sid)
删除列
- alter table STUDENT drop CTIME
- 删除主键约束：
  - alter table SYUDENT drop 主键约束的名字
- 删除学生姓名必须取唯一值的约束
  - alter table STUDENT drop unique(Sname)
- 删除一列
  
  alter table Student drop column score;
修改列数据类型

alter table SC alter column score INT;

3-2-4 删除基本表

drop table <表名>;

3-3 DML

3-3-1 SELECT 表单查询

查询语句格式：

select A,B,...C
from r,t,y
where condition
Group By columns
Having condition
Order by columns

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

3-3-1-1 单表查询

-- 查询语句格式
select [all|distinct]
	<目标列表达式>[<别名>]
	<目标列表达式>[<别名>]
from <表名或视图名>[别名]
	[<表名或视图名>[别名]]
[where<条件表达式>]
[group by<列名>]
	[having<条件表达式>]
[ordedr by <列名>]

查询涉及一个表，是一种最简单的查询操作
- 选择表中的若干列： select 子句
- 选择表中的若干元组： where 子句
- 对查询结果排序： order by 子句
- 使用集函数： 5个集函数
- 对查询结果分组： group by 子句
- 对分组之后结果进行筛选： having 子句
选择表中的若干列：

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

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

- 查询所有列
- 查询经过计算的值
例题：
- 查询全体学生的学号与姓名
```
select sno,sname from student;
```

例题：

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

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

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

例题：

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

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

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

消除取值重复的行

在SC表中查询选修了课程的学生学号【一个学生选修多项，会有重复的学号】
```
select distinct Sno from SC;
```
查询选修课程的各种成绩
```
select distinct Cno,Grade from SC;
```

查询满足条件的元组
- 选择运算
  - 通过 where Condition子句实现

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

select Sname from Student where Sdept = 'CS'

【where】
- 使用谓词：Between…and…
- not between … and…
  - between 后：范围的下限
  - and后：范围的上限
- 多重条件查询实现
- ```
select Sname,Sdept,Sage
from Student
where Sage between 20 and 23
```
【where】【确定集合】使用谓词：
- in<值表>
- not in<值表>
- <值表> : 用逗号分隔的一组取值
- 查询是信息系、数学系和计算机科学系的学生的姓名和性比
```
select name,Ssex
from  Student 
where Sdept in('IS','MA','CS')
```
【where】【字符串匹配】使用谓词 LIKE或NOT LIKE
- [not] like ‘<匹配串>’[escape’<换码字符>']
- 通配符：
  - % : 代表任意长度的字符串
  - _ ：代表任意单个字符
- 查询姓”欧阳“且全名为三个汉字的学生的姓名
  - ```
  select Sname,Sno,Ssex
  from Student
  where Sname like '欧阳_'
```
Escape 短语
- 当用户查询的字符串本身含有 % 或_时，要使用**ESCAPE’<换码字符>'**短语对通配符进行转义
- 查询DB_…i…
```
select *
from Course
where Cname Like 'DB\_%i__' ESCAPE '\';
```
涉及空值的查询
- 使用谓词IS NULL 或者IS NOT NULL
- ```
select Sno,Cno
from SC
where Grade is null;
```
多重条件查询
- 逻辑运算符and和or来联结多个查询条件
  - and优先级高于or
  - 用括号改变优先级
- ```
select Sname
from Student 
where Sdept = 'CS' and Sage<20;
```
- 查询信息系(IS)/数学系（MA）和计算机(CS)学生的姓名和sex
```
select Sname,Ssex
from student
where Sdept in('IS','MA','CS')
```

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

使用ORDER BY 一个列名或多个列名子句 :
- 按一个或多个属性列排序
- 升序： ASC；降序：DESC； 缺省值为升序
当排序列含空值时
- ACS：排序列为空值得元组最后显示
- DESC：排序列为空值得元组最先显示
例题：查询选修了3号课程的学生的学号及成绩，查询结果按分数降序排列

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

例题：查询全体学生情况，查询结果按所在系的系号升序排列，同一系中得学生按年龄降序排列
```
select * 
from Student
order by Sdept,Sage desc
```

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

统计个数： count(<列名>)
- 计算列总和： sum(<列名>)
- 计算列平均值： avg(<列名>)
- 求列最大值： max(<列名>)
- 求列最小值” min(<列名>)
- 注意 :
  - 统计时不计NULL值
  - COUNT(*) : 统计行数
  - DISTINCT ：在列名前加DISTINCT，则统计时去掉重复行
例题：
- 查询学生的总人数：
  - 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号课程的学生最高分数
```
select max(Grade)
	from SC
	where Cno = 1;
```
对查询结果分组
- 分组方法：按指定的一列或多列值分组，值相等为一组
- 使用group by 字句后，select子句的列名列表中只能出现分组属性和集函数
- group by子句的作用对象是查询的中间结果表
【对查询结果分组】求各个课程号及相应的课程成绩在90分以上的学生人数
```
select Cno,Count(Sno) 
	from sc
	where Grade >= 90
    grounp by cno;
```
- 求各个课程号及相应的选课人数
```
select cno,count(*) from sc group by cno
```
使用having短语筛选最终输出结果
- 只用满足having短语指定条件的组才输出
having短语与where子句的区别：作用对象不同
- 查询选修3门以上课程的学号
```
select Sno 
	from SC
	group by Sno
	having count(*) >= 3
```
- 查询有3门以上课程在90分以上的学生的学号及90分以上的课程数
```
select Sno, count(*)
	from SC
	where Grade >= 90
	group by Sno
	having count(*) >= 3;
```

统计每门课程的最高分

select cno,max(grade)
	from SC
	group by cno

3-3-1-2 连接查询

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

等值连接

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

select Student.*,SC.* 
	FROM Student,SC
	where Student.Sno = SC.SNO;

等值连接

查询计算机系（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';

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

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

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

3-3-3-1

插入数据
- 插入单个元组
```
insert 
	into <表名>[(<属性列1>)],
	values (<常量1>[,<常量2>]...)
```
  - 功能：把新元组插入指定表中
- 插入子查询结果
  insert into <表名>
  [(<属性列1>[,<属性列2>…])]
  子查询;
```
- 
```

插入数据—例子：

将一个新学生记录（学号：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)

插入子查询—例子：

-- 建表
create table Deptage(Sdept char(15)),Avage smallint);

-- 插入数据
insert into Deptage(Sdept,Avgage)
	select Sdept,Avg(Sage)
	from Student
	group by Sdept;

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

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

3-3-33 修改数据

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

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

三种修改方式
- 修改某一个元组的值
- 修改多个元组的值
- 带子查询的修改语句

修改某一个元组的值

将学生95001的年龄改为22岁

update Student
	set Sage = 22
	where Sno= '95001';

将所有学生的年龄增加1岁
```
update student set Sage = Sage+1;
```

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

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

带子查询的修改语句

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

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

3-3-4 删除

3-3-4-1

- 删除某一个元素的值
- 删除多个元组的值
- 带子查询的删除语句

删除学号为95019的学生记录

delete 
	from Student
	where Sno = '95019';

删除2号课程的所有选课记录
```
delete
	from SC
	where Cno = 2;
```
删除所有的学生选课记录
```
delete from SC;
```

带子查询的删除语句：

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

delete 
	from SC
	where 'CS'=(select Sdept 
               from Student
               where Student.Sno = SC.Sno)

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

3-4 视图

3-4-1 关系（Relations）

三种类型关系
- 表（Table）-- 基表，存储关系（Base tables，Stored relations）
- 视图（Views）— 虚拟关系
- 临时结果—用于构建子查询的结果
SQL支持数据库的三级模式结构 :
视图的特点：
- 虚表：一个或几个基本表（或视图）导出的表
- 只存放试图的定义，不会出现数据冗杂
- 基表中的数据发生改变，从试图中查询出的数据也随之改变
基于视图的操作
- 定义视图（DDL）
  - 建立
  - 定义基于该视图的新视图
  - 删除
- 查询视图（DML）
- 更新视图（DML）

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

建立视图：

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

组成视图的属性列名或全部省略或全部指定：
- 省略视图的各个属性列名，则隐含该视图由子查询中select子句目标列中的各字段组成
- 必须明确指定组成视图的所有列名的情形：
  - 某个目标列不是单纯的属性名，而是集函数或列表达式
  - 目标列为*
  - 多表连接时选出了几个同名列作为视图的字段
  - 需要在视图中为某个列启用新的合适的名字
视图定义的select语句：
- 不能包含order by子句和distinct短语
with check option
- 透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）

3-4-3 建立视图：

DBMS执行create view语句时只是把视图的定义存入数据字典，并不执行其中的select语句
只是对视图查询时，才按视图的定义从基本表中将数据查出
行列子集视图 ：
- 从单个基本表导出
- 只是去掉了基本表的某些行和某些列，但保留了码
- 例子：建立信息系学生的视图：
```
create view V_StuIS as
	select Sno,Sname,Sage
	from Student
	where Sdept='IS'
```
with check option 的视图：

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

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

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

基于多个基表的视图

建立信息系选修了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号课程且成绩在90分以上的学生的视图
```
create view V_S2
	as 
	select Sno,Sname,Grade
	from V_S1
	where Grade >= 90;
```
  - 视图建立在V_S1之上
带表达式的视图：

3-4-3-1 例子：

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

create view V_SCCno
	as
	select Sno,Cno,Grade
	from SC
	where Cno = 1
	with check option;

以select * 方式创建的视图
- 将student表中所有女生记录定义为一个视图
```
create view V_Student1(stdnum,name,sex,age,dept)
	as select *
	from Student
	where Ssex='女'
```
- 修改基表Student的结构后，Student表与V_Student1视图的映像关系，导致视图不能正确工作

上例可以这样写：

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

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

3-4-4 删除视图

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

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

删除视图V_S1

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

3-4-5 查询视图

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

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

DBMS ：
- 视图实体化法(View Materialization)
- 视图消解法(View Resolution)

3-4-7 视图的作用

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

3-5 索引

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

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

3-5-2-1 建立索引

```
create [unique][cluster] index <索引名>
	on＜表名＞（列名）
```
- 用<表名>指定要建索引的基本表名字
- 索引可以建立在该表的一列
- 用<次序>指定索引值的排列次序，升序： ASC，降序：DESC
- unique 表名此索引的每一个索引值值对应唯一的数据记录
唯一值索引：
- 对于已含重复值的属性列不能建unique索引

3-5-2-2 删除索引

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

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

3-6-1

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