数据库高级
学习数据库相关知识后，请务必一定要看注意事项。
《数据库语句需要注意的事项》:https://blog.csdn.net/qq_43098690/article/details/121661835
一、数据库的完整性
顾名思义，保证保存在数据库中的数据必须是有效的，即有效性和准确性。
确保数据的完整性 = 在创建表时给表中添加约束(多个约束条件之间使用空格间隔)
完整性的分类：
- 实体完整性（行完整性）
- 域完整性（列完整性）
- 引用完整性（关联表完整性）
主键约束：primary key
唯一约束：unique [key]
非空约束：not null
默认约束：default
自动增长：auto_increment
外键约束: foreign key
建议这些约束应该在创建表的时候设置
下面有个例子，可以使用：
## 建立一个 学生表
create table student(
studentno int primary key auto_increment,
loginPwd varchar(20) not null default '123456',
studentname varchar(50) not null,
sex char(2) not null,
gradeid int not null,
phone varchar(255) not null,
address varchar(255) default '学生宿舍',
borndate datetime,
email varchar(50)
);
1. 实体完整性(行完整性)
啥叫实体？
实体：即表中的一行(一条记录)代表一个实体（entity）
实体完整性的作用：标识每一行数据不重复。
实体完整性主要用到的约束类型：
? 主键约束（primary key）
? 唯一约束(unique)
? 自动增长列(auto_increment)
1.1 主键约束（primary key）
注：每个表中要有一个主键。 数据唯一，且不能为 null
示例
第一种添加方式
## 直接在需要设置的列名 设置主键
CREATE TABLE student( id int primary key, name varchar(50) );
第二种添加方式
## 此种方式优势在于，可以创建联合主键
## 标明 需要被定为主键 的 列名
CREATE TABLE student( id int, name varchar(50), primary key(id) );
CREATE TABLE student( classid int, stuid int, name varchar(50), primary key(classid,stuid) );
第三种添加方式
## 通过 修改 的方式，给表添加主键
CREATE TABLE student( id int, name varchar(50) );
ALTER TABLE student ADD PRIMARY KEY (id);
1.2 唯一约束（unique）
特点：数据不能重复。
比如，常见的身份证号码不能重复
## 设置身份证号 唯一
CREATE TABLE student( Id int primary key, Name varchar(15),idcardnum varchar(18) unique );
1.3 自动增长列（auto_increment）
sqlserver数据库 (identity-标识列)
oracle数据库(sequence-序列)
给主键添加自动增长的数值，列只能是整数类型
## 设置 学生id，自动增长
CREATE TABLE student( Id int primary key auto_increment, Name varchar(50) );
INSERT INTO student(name) values(‘tom’);
2. 域完整性
域是什么东西？
域代表当前单元格
域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较
域完整性约束：数据类型
非空约束（not null）
默认值约束(default)
check约束（mysql不支持）
这个check 约束，表示这里只能有这几个选项
check(sex=‘男’ or sex=‘女’) ## 只能男或女 check(sex=‘男’ default or sex=‘女’);
2.1 非空约束
not null
## 设置名字不允许为空
CREATE TABLE student( Id int primary key, Name varchar(50) not null, Sex varchar(10) );
insert into student values(1,null,'男'); ## 这个会失败，因为名字不允许为空
INSERT INTO student values(2,'tom',null);
2.2 默认值设置
default
CREATE TABLE student( Id int primary key, Name varchar(50) not null, Sex varchar(10) default '男' );insert intostudent1 values(1,'tom','女');insert intostudent1 values(2,'jerry',default);
3.引用完整性
前提是基于表与表之间的关系。
外键约束：FOREIGN KEY
示例：
## 这个的效果就是，score表中 sid 列，由student的id内容组成。受 student(id)牵制。## 比如，student 的id 有三个学生 1 ，2,3; 那么 score 的sid 只能是(1,2,3),否则会报错。CREATE TABLE student(id int primary key, name varchar(50) not null, sex varchar(10) default '男' );create table score( id int primary key, score int, sid int , constraint fk_score_sid foreign key(sid) references student(id) ); ## constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)
外键列的数据类型一定要与主键的类型一致
第二种添加方式：
## 修改的方式，修改score1表，去添加一个外键alter table score add CONSTRAINT fk_student_score foreign key(sid) REFERENCES student(id);
二、多表的查询
1. 多表的关系
首先应该确认一下，表之间的关系有几种。
1.1 一对多/ 多对一
客户与订单（一对多），部门和员工（一对多），书和书架（多对一）
一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.
1.2 多对多关系
学生和课表
多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一 方的主键.
1.3 一对一关系
在实际的开发中应用不多.因为一对一可以创建成一张表.
唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外 键设置为unique.
主键对应：让一对一的双方的主键进行建立关系.
2. 多表查询
多表查询有如下几种：
1.合并结果集:UNION 、 UNION ALL
2.连接查询
? 2.1内连接 [INNER] JOIN ON
? 2.2外连接 OUTER JOIN ON
? -左外连接 LEFT [OUTER] JOIN
? -左外连接 LEFT [OUTER] JOIN
? - 全外连接（MySQL不支持）FULL JOIN
? 2.3 自然连接 NATURAL JOIN
3.子查询
1. 合并结果集
作用：合并结果集就是把两个select语句的查询结果合并到一起！
合并结果集有两种方式：
UNION：去除重复记录，例如：SELECT* FROM t1 UNION SELECT * FROM t2；
UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。
注意：被合并的两个结果：列数、列类型必须相同。
? 
2. 连接查询
连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。
连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1), (a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。
那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过 条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。
举个例子，可以实际操练一下
CREATE TABLE dept1( deptno int primary key, dname varchar(14), loc varchar(13));insert into dept1 values(10,'服务部','北京');insert into dept1 values(20,'研发部','北京');insert into dept1 values(30,'销售部','北京');insert into dept1 values(40,'主管部','北京');CREATE TABLE emp1( empno int, ename varchar(50), job varchar(50), mgr int, hiredate date, sal double, comm double, deptno int);insert into emp1 values(1001,'张三','文员',1006,'2019-1-1',1000,2010,10);insert into emp1 values(1002,'李四','程序员',1006,'2019-2-1',1100,2000,20);insert into emp1 values(1003,'王五','程序员',1006,'2019-3-1',1020,2011,20);insert into emp1 values(1004,'赵六','销售',1006,'2019-4-1',1010,2002,30);insert into emp1 values(1005,'张猛','销售',1006,'2019-5-1',1001,2003,30);insert into emp1 values(1006,'谢娜','主管',1006,'2019-6-1',1011,2004,40);
直接查询会有问题
select * from emp,dept;
发现出现笛卡尔积 情况，那么使用主外键关系做为条件来去除无用信息
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
查询指定的列
SELECT emp.ename,emp.sal,emp.comm,dept.dnameFROM emp,deptWHERE emp.deptno=dept.deptno;
2.1 连接查询之 内连接
语法：
select 列名from 表1inner join 表2on 表1.列名=表2.列名 //外键列的关系where.....
等价于:
select 列名from 表1,表2where 表1.列名=表2.列名 and ...(其他条件)
注:
<1>表1和表2的顺序可以互换
<2>找两张表的等值关系时,找表示相同含义的列作为等值关系。
<3>点操作符表示“的”,格式:表名.列名
<4>可以使用as,给表名起别名,注意定义别名之后,统一使用别名
来个样版：
//查询学生表中的学生姓名和分数表中的分数select name,scorefrom student as sinner join scores as con s.studentid=c.stuid等价于：select name,scorefrom student as s,scores as cwhere s.studentid=c.stuid
上面是两个表之间，如果三个表以上怎么做？
语法： select 列名 from 表1 inner join 表2 on 表1.列名=表2.列名 inner join 表3 on 表1或表2.列名=表3.列名 where 等价于: select 列名 from 表1,表2,表3 where 表1.列名=表2.列名 and 表1/表2.列名=表3.列名
来个练习题
练习: student2 ---学生表 stuid 学员id int 主键，自增长 stuname 学员名字 varchar(10) password 登录密码 varchar(10) birthday 出生日期 date 数据： 1 花儿 111111 1990-02-09 2 少年 222222 1989-03-12 3 小胡 333333 1989-09-12 subject2 ---科目表 subjectid 科目编号 int 主键，自增长 subjectName 科目名称 varchar(10)数据： 1 java 2 mysql 3 html scores2 ---分数表 sid 分数主键 int 主键，自增长 score 分数 int subjectid 科目 int studentid 学生编号 int 数据： 1 89 1 1 2 90 2 1 3 87 2 2 4 98 3 3
需求： 1.显示出花儿的考试成绩以及对应科目 2.显示出所有考试学生的信息 3.查询出mysql的考试信息 注意：学生仅仅是学生对象，不涉及考试科目、分数。如果需要科目、考试分数，直接可以用学生id 去对应表查找。 4.查询出考试学员的总分（因为是总分，所以不需要科目） 5.查询每科的平均分 最好可以练一下，写完五个 再对照答案答案1. 列表展示 成绩和科目， 成绩科目学生是三个表 条件是 学生名字=小花select score,subjectname from subject2,scores2,student2where subject2.subjectid=scores2.subjectid and student2.stuid=scores2.studentid and student2.stuname='小花';2. 信息即* 学生表、科目表、分数表， 所有学生(默认就是所有学生，所以可以不写)select * from subject2,scores2,student2where subject2.subjectid=scores2.subjectid and student2.stuid=scores2.studentid;3. 考试信息* ， 科目、分数。 条件是 科目=mysqlselect * from subject2,scores2where subject2.subjectid=scores2.subjectid and subjectname='mysql';注意：学生仅仅是学生对象，不涉及考试科目、分数。如果需要科目、考试分数，直接可以用学生id 去对应表查找。4. 查询总分(聚合函数sum) 分数 学生（因为是总分，所以不需要科目）select stuname,sum(score) from scores2,student2where student2.stuid=scores2.studentidgroup by stuname;5. select studentname,avg(score) from scores2,subject2where scores2.subject=subject2.subjectidgroup by subjectname;
2.2 连接查询之外连接
包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。
– 外联查询
– 左外联:select 列名 from 主表 left join 次表 on 主表.列名=次表.列名
– 1.主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null
– 2.主表和次表不能随意调换位置
使用场景:一般会作为子查询的语句使用
## 本语句用到了起别名的方式 student t ## student的别名就是tselect depname,name from(select e.*,d.depname from department d left join employee e on e.depid=d.depid) aa where aa.name is null;
– 右外联:select 列名 from 次表 right join 主表 on 主表.列名=次表.列名
a.左外连接
SELECT * FROM emp eLEFT OUTER JOIN dept dON e.deptno=d.deptno;
左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的 显示NULL。
b.右外连接
右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。
SELECT * FROM emp eRIGHT OUTER JOIN dept dON e.deptno=d.deptno;
3.自然连接
自然连接（NATURAL INNER JOIN）：自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列。
下面给 出几个例子。
语句：
select * from emp e natural join dept d;
答案：
16：C
17：D
? 首先，判断 主表 从生成结果看，CX CY,数据全 是主表（非主表，如果主表没有的话，就会显示为null）
? 语句上看， left 主表在左边， right主表在右边。
3.子查询
一个select语句中包含另一个完整的select语句。
说白了就是嵌套，子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么 就是子查询语句了。
一般子查询出现的位置：
a. where后，作为条为被查询的一条件的一部分；
b. from后，作表；
当子查询出现在where后作为条件时，还可以使用如下关键字：
a. any
b. all
子查询结果集的形式：
a. 单行单列（用于条件）
b. 单行多列（用于条件）
c. 多行单列（用于条件）
d. 多行多列（用于表）
示例：
- 工资高于JONES的员工。
分析：工资>JONES工资，其中JONES工资需要一条子查询。
第一步：查询JONES的工资
SELECT sal FROM emp WHERE ename='JONES';
第二步：查询高于JONES工资的员工
SELECT * FROM emp WHERE sal > (第一步结果);
合成
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');
- 查询与SCOTT同一个部门的员工。
查询条件：部门=SCOTT的部门编号，其中SCOTT 的部门编号需要一条子查询。
第一步：查询SCOTT的部门编号
SELECT deptno FROM emp WHERE ename='SCOTT';
第二步：查询部门编号等于SCOTT的部门编号的员工
SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename='SCOTT');
- 工资高于30号部门所有人的员工信息
查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。
第一步：查询30部门所有人工资
SELECT sal FROM emp WHERE deptno=30;
第二步：查询高于30部门所有人工资的员工信息
SELECT * FROM emp WHERE sal > ALL (第一步)
合成
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)
最后给一个练习题地址
SQL高级语法 练习题（一个商城数据库存储）：https://blog.csdn.net/qq_43098690/article/details/121664964