参照完整性

  1. 实体完整性:主键不能为空
  2. 参照完整性:外键一定存在于外键对应的表中
  3. 用户自定义的完整性:字段的数据应当符合一定规范(比如:学分=(学时/8)*0.5,成绩属于0-100)

SQL中的实现方法:

  1. 空值约束:sname char(20) not null

  2. 唯一性约束:

    1. 列级约束:sname char(20) not null unique
    2. 表级约束:CONSTRAINT Uniq_name unique(sname)
  3. 默认值约束:Sgender char(2) not null default ‘男’

  4. check约束:

    1
    2
    3
    4
    5
    6
    7
    Create table Author(
    A_id char(10) not null Primary key,
    name varchar(40) not null,
    sage tinyint null check (sage<30),
    gender char(2) check (gender = '男' or gender = '女'),
    birthplace varchar(32)
    )

数据库的范式要求

第一范式 1NF:属于第一范式关系的所有属性都不可再分,即数据项不可分。第一范式强调数据表的原子性,是其他范式的基础。

第二范式 2NF:在符合1NF的基础上,消除了非主属性对于码的部分函数依赖。

第三范式 3NF:3NF在2NF的基础之上,消除了非主属性对于码的传递函数依赖。

DDL(Data Definition Language,数据定义语言)

  • create 创建数据库和数据库的一些对象
  • drop 删除数据库/表、索引、条件约束以及数据表的权限等
  • alter 修改数据库表的定义及数据属性
CreateAlterDrop
Create datebase
Create SchemaDrop Schema
Create TableAlter TableDrop Table
Create IndexDrop Index
Create ViewDrop View
Create ProcedureAlter ProcedureDrop Procedure
Create triggerAlter triggerDrop trigger
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 创建表
Create Table Student (
Sno char(8) not null Primary key,
-- Fee_id int Identity(1,2) not null primary key, 字段自动赋值禁止更新
Sname char(20) not null unique,
Sdept char(20) null,
Sgender char(2) not null,
Sbirthday Date null
)
-- 修改表
Alter Table student Add SID Char(16);
Alter Table student Alter Column sname char(20);
Alter Table student Drop Column sgender;

-- 删除表
Drop Table Student;

DML(Data Manipulation Language,数据操纵语言)

  • insert 向数据库中插入一条数据
  • delete 删除表中的一条或者多条记录
  • updata 修改表中的数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 插入值
insert into sc (sno,cno,grade) values ('001', 'A', 90);
insert into sc values ('001', 'A', 90);
-- 插入子查询结果
insert into sc (sno,cno)
select sno, 'A' from student
where sno like'821%';
insert into deptment (deptname, stu_quantity)
select sdept, count(*) from student group by sdept;

-- 更新数据
update student set sdept ='IS' where sno = '001'
update student set sage = sage + 1
update sc set grade = grade + 10 where cno = 'A'
-- 带有子查询的更新
update sc
set grade = grade*0.05
where cno = 'A' and sno in (
select sno from student where sdept = 'CS'
)

-- 删除数据
delete from sc where cno ='B' and sno ='001';
-- 带有子查询的删除
delete from sc where sno in(
select sno from student where sdept = 'IS' and sname = '张强'
)

DQL(Data Query Language,数据查询语言)

  • selete 查询表中的数据
  • from 查询哪张表、视图
  • where 约束条件
1
2
3
4
5
6
7
Select sno, '本科学生'2021 - Sage from student;
Select sno, DateDiff(yy, sbirthday, getdate()) from student;
Select sno 学号, 2021 - Sage 出生年份
from student;

-- 取消重复行 [distinct | all]
Select distinct sno from sc;

查询满足条件的元组

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 查询满足条件的元组: Select … From … where
Select * from student where sdept <> 'IS';
Select sno, sage from student where sname = '张立';

-- between and
Select distinct sno from sc
Where not grade between 60 and 90;

-- [in | not in]
Select sno, grade from sc
Where cno in ('001', '002', '003');
Select sno, grade from sc
Where cno not in ('001', '002', '003');

-- Like / not like
-- 百分号(%)与任意多个(含零个)任意字符匹配;下划线(_)与一个任意字符匹配,换码字符'\'使后面的通配符失效,变成普通字符
Select * from student where sname like '刘军%';
Select * from student where sname like '刘_军';
Select * from student where sname like '刘军';
Select * from student where sname = '刘军%';

-- 空值匹配 null
Select * from student Where sdept is null;
Select * from student Where sdept = null; -- 不报错,但无结果

查询结果排序

1
2
3
4
5
-- 结果排序
Select sno, sname from student Where sdept = 'IS' Order by sage asc;
Select sno, grade from sc Where cno = 'A' Order by grade desc;
-- top N
Select top 10 sno, grade from sc Where cno='c1' Order by grade desc;

集函数

1
2
3
4
5
-- 集函数(除count外,集函数只处理非空值,这对求平均值有重要意义)
Select count(*) from student where sdept='IS';
Select count(sno) , count(distinct sno) from sc;
Select count(*) 数, sum(grade) 和, avg(grade) 均, max(grade) 大, min(grade) 小
from sc where sno='001';

分组汇总

  • Where子句作用: 在汇总前起对元组进行筛选
  • Having子句作用: 根据having后的条件,对汇总结果进行筛选
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 分组汇总
-- groupy 的字段必须出现
Select Sdept, count(*) from student group by sdept;
Select Sdept, Sgender, count(*) from student
group by sdept, Sgender;

-- Where子句作用: 在汇总前起对元组进行筛选
-- Having子句作用: 根据having后的条件,

Select cno, count(*), avg(grade) from sc
where sno like '82%'
group by cno
having avg(grade) < 60

连接查询

  • 内连接: 两个表中满足连接条件的元组进行连接
  • 外连接: 一个表中符合where条件的全部元组,每个元组与另一个表中的零个或N个元组进行连接,分为左连接, 右连接和全连接
  • 交叉连接: 两个表中所有元组的任意连接
  • 自连接: 同一个表中的元组根据指定条件相互连接
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 内连接
select student.sno, sname, cno, grade
from student inner join sc -- inner为默认选项, 可以省略
on student.sno = sc.sno
where sdept ='MA'and grade < 60;

-- 外连接
select student.sno, sname, Sdept, cno, grade
from student left outer join sc
on student.sno = sc.sno;
select student.sno, sname, cno, grade
from sc right outer join student
on student.sno = sc.sno;
select sno, sname, sdept, dpt_name, dpt_desc
from student full outer join department
on student.sdept = department.dpt_id

-- 交叉联接
-- 写法1:隐式的交叉连接,没有CROSS JOIN
select student.sno, sname, cno, cname
from student , course
where sdept ='IS';
-- 写法2:显式的交叉连接,使用CROSS JOIN
select sno, sname, cno, cname
from student CROSS join course;

-- 自连接-必须使用别名
Select E. 姓名 下级, S. 姓名 上级
from 雇员 E,雇员 S Where E.经理 = S.雇员号