【编程学习】SQL数据库知识要点总结（一）

参照完整性

  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 修改数据库表的定义及数据属性
Create Alter Drop
Create datebase
Create Schema Drop Schema
Create Table Alter Table Drop Table
Create Index Drop Index
Create View Drop View
Create Procedure Alter Procedure Drop Procedure
Create trigger Alter trigger Drop 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.雇员号