【数据库复习】数据库约束，往表中插入查询结果，查询（聚合查询，联合查询（内外连接图解））

数据库约束

• NOT NULL：指定某列不能存储null值
• UNIQUE （唯一约束）：某列的信息不能重复但可以为null
• DEFAULT （默认值约束）：规定没有给列赋值时的默认值
  – 指定插入数据时，如果name为空，默认值设置为‘unkown’

DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL,
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT 'unkown',
   qq_mail VARCHAR(20)
);

• PRIMARY KEY （主键约束）：相当于not null 和 unique的结合。某列的信息不能重复也不能为null
  – 指定id为主键

DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL PRIMARY KEY,
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT 'unkown',
   qq_mail VARCHAR(20)
);

又因为主键就是唯一且不能为空，是 not null 和 unique 的结合，所以此处可以将not null去掉

   id INT PRIMARY KEY,

• FOREIGN KEY （外键约束）：保证一个表中的数据匹配到另一个表的值的参照完整性
  外键是用于关联其他表的主键或唯一键

foreign key (字段名) references 主表（列）

【例】
比如说此处再创建一个班级表，id设置为主键

DROP TABLE IF EXITS classes;
CREATE TABLE classes(
	id INT PRIMARY KEY,
	name VARCHAR(20),
);

学生表中使用id为主键，classes_id为外键，关联班级表中的id

DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT PRIMARY KEY,
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT 'unkown',
   qq_mail VARCHAR(20)
   classes_id INT，
   FOREIGN KEY (classes_id) REFERENCES classes(id)
);

• CHECK约束

drop table if exists test_user;
create table test_user (
   id int,
   name varchar(20),
   sex varchar(1),
   check (sex ='男' or sex='女')
   -- 所以此处性别只能填男或者女，其他会报错，这就是check约束
);

新增 （此处是指插入查询结果）

INSERT INTO table_name [(column[,column...])] SELECT ...

【例】 比如说原来有一张表student（里面的字段有name,qq_mail），现在新建了一张表test_user（里面字段有name,email,sex,mobile）,需要把之前student表中的数据复制过来

INSERT INTO test_user (name,emil) select name,qq_mail from student;

查询

1. 聚合查询

① 聚合函数

• COUNT 返回查询到的数据的数量

-- 统计班上一共有多少同学
SELECT COUNT(*) FROM student;
SELECT COUNT(0) FROM student;

--统计班上收集的qq_mail有多少条（为null的不会计算进去哦）
SELECT COUNT(qq_mail) FROM student;

• SUM 返回查询到的数据的总和，一般都使用在数字类型上，不是数字没有意义

-- 统计同学们数学总分
SELECT SUM(math) FROM  exam_result;

-- 统计数学不及格的同学的数学总分
SELECT SUM(math) FROM exam_result WHERE math<60;

• AVG 返回查询到的数据的平均值，一般都使用在数字类型上，不是数字没有意义

-- 统计班上同学的平均总分
SELECT AVG(chinese+math+english) AS average_score FROM exam_result;

• MAX 返回查询到的数据的最大值，一般都使用在数字类型上，不是数字没有意义

-- 返回班上数学最高分
SELECT MAX(math) FROM exam_result;

• MIN 返回查询到的数据的最小值，一般都使用在数字类型上，不是数字没有意义

-- 数学及格的数学最低分
SELECT MIN(math) FROM exam_result WHERE math > 60;

② GROUP BY

group by子句可以对指定列进行分组查询，但是需要满足：使用group by进行分组查询时，select指定的字段必须是分组所依据的字段，如果是非分组依据字段想要出现在select中则必须包含在聚合函数当中

SELECT column1,SUM(column2) FROM table_name GROUP BY column1,column3;

==【例】==查询classes表（有课程号cid，课程名cname，分数score）中各科目的最高分，最低分，平均分

SELECT cname,MAX(score),MIN(score),AVG(score) FROM classes GROUP BY cname;

③ HAVING

如果用了GROUP BY子句进行了分组之后，需要对分组结果再进行过滤时，不可以在使用WHERE语句，而是要用HAVING
==【例】==显示平均分低于60的科目和他的平均分

SELECT cname,AVG(score) FROM classes GROUP BY cname WHERE AVG(score)<60;

2. 联合查询

比如说现在有两张表table1，table2，table1中含有a,b,c三个字段，table2中含有c,d两个字段
table1：

a	b	c
a1	b1	c1
a2	b2	c2

table2 :

c	d
c1	d1
c3	d3

① 交叉连接（笛卡尔积） cross join

select 字段 from 表1 [别名1] , 表2 [别名2];
select 字段 from 表1 [别名1] cross join 表2 [别名2];

没有任何关联条件，实际结果就是笛卡尔积，结果集会很大，没有意义，很少使用
结果表的行数是两表行数相乘

select * from table1,table2;
select * from table1 cross join table2;

a	b	c	c	d
a1	b1	c1	c1	d1
a1	b1	c1	c3	d3
a2	b2	c2	c1	d1
a2	b2	c2	c3	d3

② 内连接inner join

inner join 可以写成join
显示的是表1表2交叉重合的部分

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件1 [and 连接条件2];
select 字段 from 表1 别名1，表2 别名2 where 连接条件1 [and 连接条件2];

select * from table1 t1 inner join table2 t2 on t1.c<=>t2.c;
select * from table1 t1,table2 t2 where t1.c<=>t2.c;

a	b	c	c	d
a1	b1	c1	c1	d1

③ 外连接

• 左外连接left join

-- 左外连接，表1完全显示
select 字段名  from 表名1 left join 表名2 on 连接条件;

select * from table1 t1 left join table2 t2 on t1.c<=>t2.c;

a	b	c	c	d
a1	b1	c1	c1	d1
a2	b2	c2

• 右外连接right join

-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

a	b	c	c	d
a1	b1	c1	c1	d1
			c3	d3

④ 自连接

自连接作为内连接的一种特例，可以将一个表与它自身进行连接，称为自连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。
比如说有个学生成绩表（score），里面包含字段学号id，课程名cname，课程成绩sco，查询成绩表中英语成绩高于数学成绩的信息

	select * from score s1 
	inner join score s2 
	on s1.id = s2.id 
	and s1.sco<s2.sco 
	and s1.cname = 'math' 
	and s2.cname = 'english';

⑤ 子查询（嵌套查询）

嵌入在其他sql语句中的select语句

• 单行子查询：返回一行记录的子查询
  ==【例】==在student表中查询‘chen’的同班同学

select * from student where class_id = 
	(select class_id from student where name = 'chen');

• 多行子查询：返回多行记录的子查询
  ==【例】==查询math 或 english课程的成绩信息（score表中包含字段课程号cid，学生学号sid,成绩sco；课程表中包含字段课程号id，name）

1. [NOT] IN

select * from score where cid in 
		(select id from course where name='math' or name='english');

2. [NOT] EXISTS

select * from score s where exists 
		(select s.cid from course c where 
				(name ='math' or name = 'english') and s.cid = s.id);

• 在from子句当中使用子查询：子查询语句出现在from子句中，将子查询的结果当作一个临时表使用
  ==【例】==查询所有比‘计卓1702班’平均分高的成绩信息
  step1：先查询‘计卓1702班’的平均分，将其看作临时表

select 
	avg(score.sco) sco 
from 
	score s 
	join student stu on s.sid = stu.id
	join classes c on stu.cid = c.id
where
	c.name = '计卓1702班';

steo2: 将上面的结果当作一个临时表

select
	* 
from
	score s,	
	(select 
		avg(score.sco) sco 
	from 
		score s 
		join student stu on s.sid = stu.id
		join classes c on stu.cid = c.id
	where
		c.name = '计卓1702班';
	)tmp
where 
	s.sco > tmp.sco;	

⑥ 合并查询

合并多个select的执行结果，可以用UNION , UNION ALL。使用的时候需要注意UNION和UNION ALL使用时前后查询的结果集中，字段需一致
注意：UNION 和 UNION ALL都是取得两个结果集的并集。前者会自动去掉结果集中的重复行，后者不会去掉结果集中的重复行
==【例】==查询id<3，或者名字为’english‘的课程

• UNION

select * from course where id<3
union
select * from course where name='english';

相当于

select * from course where id<3 or name='english';

• UNION ALL

-- 结果集中会出现重复的数据
select * from course where id<3
union all
select * from course where name='english';