本文共 5567 字，大约阅读时间需要 18 分钟。

SQL高级：

数据的完整性：

作用：保证用户输入的数据保存到数据库中是正确的，确保数据的完整性=在建表的时候给表添加约束

完整性的分类：实体完整性，域完整性，引用完整性

实体完整性：

定义：表中每一行数据（记录）代表一个实体（entity）

实体完整性的作用：表示每一行数据不重复

约束类型：

主键约束（primary key）：（特点）每个表中只有一个主键，被约束的数据唯一，且不能为null

-- 第一种方式：在写列名的时候直接指明主键CREATE TABLE s1(	id INT PRIMARY KEY,	NAME VARCHAR(20))CHARSET=utf8;-- 第二种方式：此种方式的优势在于，可以创建联合主键CREATE TABLE s2(	id INT,	NAME VARCHAR(20),	PRIMARY KEY(id))CHARSET=utf8-- 第三种方式：单独添加主键CREATE TABLE s3(	id INT,	NAME VARCHAR(20))CHARSET=utf8ALTER TABLE s3 ADD PRIMARY KEY (id);

唯一约束（unique）：（特点）数据唯一，可以为null，null不算重复

-- 唯一约束CREATE TABLE s4(	id INT UNIQUE,	NAME VARCHAR(10))CHARSET=utf8

自动增长约束（auto_increment）：（特点）默人从列最大值+1操作，给主键添加自动增长约束，列只能是整数类型：

-- 自动增长序列CREATE TABLE s5(	id INT PRIMARY KEY AUTO_INCREMENT,	NAME VARCHAR(50))CHARSET=utf8;

域完整性：

域完整性的作用：限制单元格的数据正确，不对照此列的其他单元格比较，域代表当前的单元格

域完整性约束分类：

数据类型：

非空约束（Not Null）

默认值约束（default）

check约束（mysql不支持），check（sex=‘男’ or sex=‘女’）

数据类型：

数值类型，日期类型，字符串类型

非空约束：

-- 非空约束CREATE TABLE s6(	id INT PRIMARY KEY ,	NAME VARCHAR(20) NOT NULL,	sex VARCHAR(10))CHARSET=utf8;

默认约束：

-- 默认值约束CREATE TABLE s7(	id INT PRIMARY KEY,	NAME VARCHAR(20) NOT NULL,	sex VARCHAR(1) DEFAULT '男')CHARSET=utf8;INSERT INTO s7 VALUES(1,'zhngsan',DEFAULT);

引用完整性（参照完整性）：

--  引用完整性CREATE TABLE s8(	id INT PRIMARY KEY,	NAME VARCHAR(50) NOT NULL,	sex VARCHAR(10) DEFAULT '男' )CHARSET=utf8;CREATE TABLE s9(	 id INT PRIMARY KEY,	 socre INT,	 FOREIGN KEY (id) REFERENCES s8(id) -- 外键列的数据类型一定要与逐渐的类型一致)CHARSET=utf8;

表和表关系：

一对一：

例如t_person表和t_card表，即人和身份证，这种情况需要找出主从关系，即谁是主表，谁是从表，人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。设计从表可以有两种方案：在t_card表中添加外键列（相对于t_user表），并且给外键添加唯一约束；给t_card表的主键添加外键约束（相对于t_user表），即t_card表的主键也是外键

一对多（多对一）：

最为常见的就是一对多！一对多和多对一，这是从从哪个角度去看总结出来的，t_user和t_selection的关系，从t_user来看就是一对多，而从t-selection的角度来看就是多对一！这种情况都是在多方创建外键！

多对多：

例如t_stu和s_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生，这种情况通常需要创建中间表来处理多对多的关系，例如在创建一张表t_stu_tea表，给出两个外键，已给相对于t_stu表的外键，另一个相对于t_teacher表的外键

多表查询：

1.合并结果集

-- 查询计算机系的全体学生，以及年龄在19岁以下的学生 SELECT * FROM student WHERE sdept = 'CS' UNION  SELECT * FROM student WHERE sage < 19;

作用：合并结果集就是把两个select语句的查询结果合并到一起！要求：被合并的两个结果：列数，列类型必须相同

-- 合并结果集-- 在进行合并结果集时，两个表的字段个数和类型必须一致-- 合并结果集时并不是对原表进行合并，是对查出来的虚拟表进行合并-- union会自动进行去重，union all不会进行去重-- 当类型有高低等级，会自动转成高等级类型

2.连接查询

2.1等值连接

-- 这种查询的方式是靠两个表之间的公共属性empId实现的SELECT * FROM emp,bonus WHERE emp.empId = bonus.empId;

2.2自然连接

-- 在等值连接的基础上，把目标列中重复的属性列去掉则为自然连接-- 注意点：因为在两个表中都出现了empId这个属性，所以我们在引用属性的时候加上前缀SELECT emp.`empId`,NAME,supervisor,salary,bonus FROM emp,bonus WHERE emp.empId = bonus.empId;-- 一句sql语句可以同时完成选择和连接查询，这是where子句是由连接谓词和选择谓词组成的复合条件

2.3自身连接

-- 一个表与自己进行连接，称为表的自身连接-- 查询每一门课的间接先修课（即先修课的先修课）子查询SELECT c1.`cno`,c2.`cpno` FROM course c1,course c2 WHERE c1.`cpno`=c2.`cno`;

2.4外连接：

-- 外连接 -- 在通常的连接操作中，只有满足连接条件的元组才能作为结果输出，如（查询每个学生及其选修课程的情况）结果中没有’201215123‘，’2012151125‘ -- 这两个学生的信息，原因在于他们没有选课，在sc表中没有相应的元组，导致了student中这些元组在连接时被舍弃了 -- 但有时想以student表为主体列出每个学生的基本情况及其选课情况，在没有某个学生的信息时，仍把student的悬浮元组保留在结果关系中 -- 而在sc表的属性上添加空值null，这时就需要使用外连接 -- 左外连接  -- 表1 left outer join (表2) on (表1.主码=表2.主码) --->表示表1左外连接上表2（保留表1信息） -- 右外连接 -- 表1 right outer join (表2) on (表1.主码=表2.主码) --->表示表1右外连接上表2（保留表2信息） -- 多表连接 -- 靠主表主码和从表外码进行连接

3.子查询：

定义：一个select语句中包含了另一个完整的select语句，子查询就是嵌套查询，即select中包含select，如果一条语句中存在两个或者两个以上的select，那么就是子查询语句了

子查询出现的位置：

where后，作为被查询条件的一部分

from后，作为表

注意：当子查询出现在where后作为条件时，还可以使用如下关键字：any all（很少用）

子查询结果集形式：

单行单列（用于条件）

单行多列（用于条件）

多行单列（用于条件）

多行多列（用于表）

-- 嵌套查询（不相关嵌套） -- 查询与“刘晨”在同一个系学习的学生-- 第一步先查询“刘晨”所在的系，再以此为根据找出学生信息 SELECT * FROM student WHERE sdept IN (SELECT sdept FROM student WHERE sname = '刘晨');-- 自连接查询SELECT s2.`sname`,s2.`sage`,s2.`sdept`,s2.`sno`,s2.`ssex` FROM student s1,student s2 WHERE s1.sdept=s2.sdept AND s1.sname = '刘晨';  -- 查询选修了课程名为“信息系统”的学生学号和姓名 -- 先从course表中查出“信息系统”的课程号，再从sc表中查出选了此课的学生学号，最后再从student中查出姓名 SELECT sname,sno FROM student  WHERE sno IN (SELECT sno FROM sc  WHERE cno IN (SELECT cno FROM course     WHERE cname = '信息系统')) -- 使用多表连接，可以简化sql语句 SELECT student.sno,student.sname FROM student,sc,course WHERE student.`sno`=sc.`sno` AND sc.`cno`=course.`cno` AND course.`cname` = '信息系统';

MySQL中的函数：

常用日期函数：

字符串函数：

常规函数：

自定义函数：

定义：mysql中的函数与存储过程类似，都是一组sql集

与存储过程的区别：函数可以return值，存储过程不能直接return，但是有输出参数可以输出多个返回值；

函数可以嵌入到sql语句中使用，而存储过程不能；

函数一般用于实现简单的有针对性的功能（例如求绝对值，返回当前时间），存储过程用于实现复杂的功能（如复杂的业务逻辑功能）；

函数的关键字是function，存储过程是：procedure

自定义函数：

-- 定义函数之前首先需要定义分隔符DELIMITER ;; -- 自定义分隔符，这里定义的分隔符是;;定义好之后，只有遇到;;才会结束-- 自定义函数CREATE DEFINER='root'@'localhost' FUNCTION func_compare(a INT) RETURNS VARCHAR(200) CHARSET utf8BEGIN -- 函数开始-- routine body goes here-- 这里写的是if语句IF a >= 10 THEN 	RETURN '大于等于10' ;ELSE	RETURN '小于10';END IF;END -- 函数结束;;-- 重新定义分隔符DELIMITER ;-- 使用函数SELECT func_compare(4);

转载地址：https://blog.csdn.net/m0_56433040/article/details/117917767 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！