数据库的增删查改（进阶）

1.数据库约束

约束类型

• NOT NULL:指示某列不能存储NULL值。
• UNIQUE:保证某列的某行必须有唯一值。
• DEFAULT:规定没有给赋值是的默认值。
• PRIMARY KEY:NOT NULL和UNIQUE的结合。确保某列（或某两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY:保证一个表中的数据匹配另一个表中的值得参照完整性。
• CHECK:保证列中的值符合指定的条件。

（1）NOT NULL约束
创建表是可以指定某列不为空：

 create table student(
 	id char(12) not null,
 	name varchar(32),
 	age smallint(6)
 );                                                                                 `

结果如下：

（2）UNIQUE:唯一约束
指定sn列为唯一的：

 create table student(
     id char(12),
     sn int unique,
     name varchar(32),
     age smallint(6)
     );

结果如下：

（3）DEFAULT：默认值约束
指定插入数据时，name列为空，默认值为unkown：

create table student(
    id char(12) not null,
    sn int unique,
    name varchar(32) default 'unkown',
    age smallint(6)
    );

结果如下：

（4）PRIMARY KEY:主键约束

对于整数类型的主键，常搭配自增长auto_increment
指定id列为主键：

create table student(
   	id char(12) primary key,
    sn int unique,
  	name varchar(32),
    age smallint(6)
     );

结果如下：

(5)FOREIGN KEY:外键
外键用于关联其他表的主键或唯一键
语法：

foreign key (字段) references 表名(列)

创建学生成绩表sc,id为主键：

create table sc(
    id char(12) primary key,
    name varchar(32),
    english double(4,1),
    chiness double(4,1),
    math double(4,1)
   );

创建学生表student，id为主键，scid为外键，关联sc表中的id：

create table student(
    id char(12) primary key,
    sn int unique,
    name varchar(32) default 'unkown',
    age smallint,
    scid char(12),
    foreign key (scid) references sc(id)
    );

结果如下：

（6）CHECK约束
MySQL使用时不报错，但忽略该约束：

 create table student(
      id char(12),
      name varchar(32),
      age smallint,
      sex varchar(1),
      check (sex='男' or sex='女')
    );

2.查询

2.1聚合查询

函数	说明
COUNT(*)	统计元组个数
COUNT([DISTINCT/ALL]<列名>)	统计一列中的值
SUM([DISTINCT/ALL]<列名>)	计算一列值的总和（数值型）
AVG([DISTINCT/ALL]<列名>)	计算一列值的平均值 （数值型）
MAX([DISTINCT/ALL]<列名>)	求一列值中的最大值
MIN([DISTINCT/ALL]<列名>)	求一列值中的最小值

注意：

1. 如果指定 DISTINCT短语，则表示在计算是要取消指定列中的重复值。如果不指定 DISTINCT短语或ALL短语（ALL为默认值）则表示不取消默认值。
2. 当聚合函数遇到空值是，除了count(*)外，都跳过空值而只处理非空值。

案例：
COUNT
a.统计班里有多少个同学

select count(*) from student;

查看学生个数：

b.查看学生表中age列的个数

select count(age) from student;

SUM
查看某班语文成绩的总和分数：

select sum(chiness) from sc;

AVG
查看某班数学的平均成绩：

select avg(math) from sc;

MAX
查看某班数学成绩的最高分：

select max(math) from sc;

MIN
查看某班数学成绩的最低分：

 select min(math) from sc;

（1）GROUP BY子句
GROUP BY子句将查询结果按某一列或多列的值分组，值相等的为一组。
分组后聚合函数键作用于每一个组，即每一组都有一个函数值。

求各个课程号级相应的选课人数：

select Cno,count(Sno) 
from sc 
group by Cno;

该语句查询结果按Cno的值分组，所有具有相同的元组的值唯一组，然后对每一组作用聚合函数COUNT进行计算，以求得改组的学生人数。

（2）HAVING
如果分组后还要求一定的条件对这些组进行筛选，最终只输出满足于指定条件的组，则可以使用HAVING短语指定筛选条件。
查找选修了两门课程以上的学生学号：

select Sno from sc group by Sno having count(*)>2;

WHERE子句与HAVING短语的区别在于作用对象不同。WHERE子句作用于基本表或试图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。
注意：WHERE子句中是不能用聚合函数作为条件表达式的，聚合函数只能用于SELECT 子句和GROUP BY子句中的HAVING子句。

2.2联合查询

前面查询的都是只涉及一个表进行的，。若一个查询同时设计两个以上的表，则成为联合查询。

2.2.1内连接
语法：

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

查看学生李勇所有选修课的成绩：

select first.Grade from student second inner join sc first on second.Sno=first.Sno and second.Sname='李勇';
或
select first.Grade from student second,sc first where second.Sno=first.Sno and second.Sname='李勇';

结果如下：

2.2.2外连接
外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说左外连接，右侧的表完全显示我们就说右外连接。
语法：

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

查看以student表为主体列出每个学生的基本情况及其选课情况：

select student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade 
from student 
left outer join sc 
on (student.Sno=sc.Sno);

结果如下：

2.2.3自连接
连接操作不仅可以在两个表之间进行，也是可以在一个表与其自己进行连接，成为表的自连接。

查看每一门的间接先修课：

select first.Cno,second.Cpno 
from course first,course second
where first.Cpno=second.Cno;

2.2.4嵌套查询

• 在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块子句或者HAVING短语的条件中的查询称为嵌套查询。

• 上层查询块的称为外层查询或父查询，下层查询块的称为内层查询或子查询。

• SQL语言允许多层嵌套查询。

  特别指出，子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。

（1）带有IN谓词的子查询
查看与“刘晨”在同一个系学习的学生：

select Sno,Sname,Sdept
from student
where Sdept in(
    select Sdept
    from student
   	where Sname='刘晨'
    );

结果如下：

查看选修了课程名为“信息系统”的学生学号和姓名：

select Sno,Sname 
from student 
where Sno in (
   select Sno 
   from sc 
   where Cno in (
   		select Cno 
   		from course 
   		where Cname='信息系统'
    	)
    );

结果如下：

（2）带有比较运算符的子查询
带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。

找出每个学生超过他自己选修课程平均成绩的课程号。

--x是表sc的别名，又称为元组变量，可以用来表示sc的一个元组。
select Sno,Cno 
from sc x 
where Grade>=(
    select avg(Grade) 
    from sc y 
    where y.Sno=x.Sno
    );

结果如下：

（3）带有EXISTS谓词的子查询
EXISTS代表存在量词。带有 EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

查看所有选修了1号课程的学生姓名：

select Sname 
from student 
where exists 
	(select * 
	from sc 
	where Sno=student.Sno and Cno='1'
   );

结果如下：

NOT EXISTS
查询没有选修1号课程的学生姓名：

select Sname 
from student 
where not exists 
	(select * 
	from sc 
	where Sno=student.Sno and Cno='1'
   );

结果如下：

2.3集合查询
SELECT 语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。
集合操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT.

查询计算机科学系的学生及年龄不大于19岁的学生 ：

select * 
from student 
where Sdept='CS' 
union 
select * 
from student
 where Sage<=19;