SQL高级：

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=utf8
ALTER 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中的函数：

常用日期函数：

addtime(date 2,time_interval)	将time_interval加到data2
current_date()	当前日期
current_time()	当前时间
current_timestamp()	当前时间戳
date(datetime)	返回datetime的日期部分
date_add(date2,inteval d_value d_type)	在date2中加上日期或时间
date_sub(date2,interval d_value d_type)	在data2上减去一个时间
datediff(date1,date2)	两个日期差
now（）	当前时间
year|month|day（datetime）	年月日

字符串函数：

charset（str）	返回字串字符集
concat(string [])	连接字串
instr(string,substring)	返回substring在string中出现的位置，没有返回0
ucase(string)	转换成大写
lcase(string)	转换成小写
left(string,length)	从string中的左边取length个字符
length（string）	length长度
replace(str,search_str,replace_str)	在str中用到replace_str替换search_str
strcmp（str1，str2）	逐个按照字符比较两个字符串的大小
substring（str,position,[length]）	从str的postion开始，取length个字符
ltrim（string）rtrim（string）trim	去除前端空格，或者后端空格，或去前后空格

常规函数：

abs（num）	绝对值
bin(decimal_number)	十进制转二进制
ceiling(number1)	向上取整
conv(number,from,to)	进制转换
floor（num）	向下取整
format（num，decimal_places）	保留小数位数
hex（decimalNum）	转十六进制
least（num1，num2，…）	求最小值
mod(numerator,denominator)	求余
rand()	随机数

自定义函数：

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

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

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

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

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

自定义函数：

-- 定义函数之前首先需要定义分隔符
DELIMITER ;; -- 自定义分隔符，这里定义的分隔符是;;定义好之后，只有遇到;;才会结束

-- 自定义函数
CREATE DEFINER='root'@'localhost' FUNCTION func_compare(a INT) RETURNS VARCHAR(200) CHARSET utf8
BEGIN -- 函数开始
-- routine body goes here

-- 这里写的是if语句
IF a >= 10 THEN 
	RETURN '大于等于10' ;
ELSE
	RETURN '小于10';
END IF;
END -- 函数结束
;;
-- 重新定义分隔符
DELIMITER ;

-- 使用函数
SELECT func_compare(4);