数据库基础 day-02外键添加，子查询，表连接，交叉连接，限定行数查询，分组查询，视图，增删改，去重

外键添加

以下内容参考自小白不再菜

• 定义

• MySQL外键约束（FOREIGN KEY）用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。
• 外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键的值必须等于另一个表中主键的某个值。
• 外键是表的一个字段，不是本表的主键，但对应另一个表的主键。定义外键后，不允许删除另一个表中具有关联关系的行。

• 主要作用：保持数据的一致性、完整性

• 主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。
• 从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。

• 外键约束字段的选取

• 父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
• 必须为父表定义主键
• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
• 在父表的表名后面指定列名或列名的组合。这个列或列的组合必须是父表的主键或候选键。
• 外键中列的数目必须和父表的主键中列的数目相同。
• 外键中列的数据类型必须和父表主键中对应列的数据类型相同。

• 父子表应均为：innoDM
• 外键删除时和更新时：
• 以下内容参考自MySQL外键约束 删除时 和 更新时 各取值的含义

• 删除时，更新时有四个选项 ：
• CASCADE 当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则也删除外键在子表（即包含外键的表）中的记录
• NO ACTION ,RESTRICT 当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除。
• SET NULL 当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（不过这就要求该外键允许取null）

• 总结

• 外键的使用对于减少数据库冗余性，以及保证数据完整性和一致性有很大作用。
• 如果两张表之间存在外键关系，则MySQL不能直接删除表(Drop Table)，而应该先删除外键，之后才可以删除。所以一般默认就好（RESTRICT）。

子查询

-- 第二部分:子查询。查询中嵌套查询。
-- 1.相等性子查询，子查询有一个值（一行一列），加not也只能是一行一列。支持比较运算符。
-- SELECT * FROM t_student WHERE id = (SELECT id FROM t_student WHERE `name` = '张家')
-- SELECT * FROM t_student WHERE NOT id = (SELECT id FROM t_student WHERE `name` = '张家')
-- SELECT * FROM t_student WHERE id = (SELECT id FROM t_score WHERE score >= 100)
-- 2、IN子查询，用于匹配多个值,括号里面是or的意思，可以支持查询多行一列,NOT IN 不包含在里面
-- SELECT * FROM t_student WHERE id IN (1,2,3,4,5,6,7)
-- SELECT * FROM t_student WHERE id NOT IN (SELECT id FROM t_score WHERE score >= 80 AND score<=100) 

-- 3、ANY和ALL关键字查询，用于比较运算多个值,ALL所有的都要满足，ANY任一个满足条件就可以。不支持all和any中使用常量集合
-- SELECT * FROM t_student WHERE id > ALL(SELECT id FROM t_score WHERE score >= 80 AND score<=100) 

-- SELECT * FROM t_student WHERE id > ANY(SELECT id FROM t_score WHERE score >= 80 AND score<=100) 

-- 不支持：SELECT * FROM t_student WHERE id >ALL (2,3,4,5)

-- 4、EXISTS关键字查询，表示判定子查询是否存在，相当于boolean判断，前面也可以加not
-- SELECT * FROM t_student WHERE NOT EXISTS(SELECT id FROM t_score WHERE score >= 200)
-- 5、以子查询作为临时表
-- SELECT * FROM(SELECT * FROM t_student WHERE sex = '女')查询出错，子表必须起别名AS
-- SELECT * FROM(SELECT * FROM t_student WHERE sex = '女') AS t1

-- 6、相关子查询，子查询和父查询相关，无法单独执行，不能脱离父查询执行
-- 查询出比同姓学生身高高出10公分的学生
 SELECT * FROM t_student t1 WHERE height -10 >(SELECT AVG(height) FROM t_student t2 WHERE t2.last_name = t1.last_name)

表连接

-- 第三部分：表连接(重点！),不同的表根据连接条件，连接在一起。
-- 包括：内连接，外连接，全连接，mysql不支持全连接，但由间接连接手段。
-- 1、内连接：INNER JOIN,ON用作连接条件，当列名有冲突时，可以加表明前缀。参与与连接的两个表，必须和连接条件相匹配。
-- 查询出学生的基本信息以及考试分数(注意id信息在两个表中和都有，因此要标注出在那个表)
-- 连接条件不用必须是等号，任意连接条件都可以，只要有合理的连接逻辑就可以。
-- SELECT t1.id,`name`,birthday,t2.score FROM t_student t1 INNER JOIN t_score t2 ON t1.id = t2.student_id WHERE sex = '女'

-- 2、外连接：左外连接，右外连接
-- 2.1左外连接：LEFT JOIN特点：LEFT JOIN前的表(左表)数据全部显示，LEFT JOIN后的表(右表)不匹配的列显示为null
-- SELECT t1.id,`name`,birthday,t2.score FROM t_student t1 LEFT JOIN t_score t2 ON t1.id = t2.student_id WHERE sex = '女'

-- 2.2右外连接：RIGHT JOIN特点：RIGHT JOIN后的表(右表)数据全部显示，RIGHT JOIN前的表(左表)不匹配的列显示为null
-- SELECT t1.id,`name`,birthday,t2.score FROM t_student t1 RIGHT JOIN t_score t2 ON t1.id = t2.student_id WHERE sex = '女'
-- 2.3表连接之后，相当于一个全新的表，可以对这个表进行任意操作。
SELECT t1.id,`name`,birthday,t2.score FROM t_student t1 
RIGHT JOIN t_score t2 ON t1.id = t2.student_id 
WHERE sex = '女' ORDER BY t2.score DESC
-- 3、全连接：左表和右表都全部显示，连接不上的显示为null。mysql目前不支持全连接，FULL JOIN。但可以通过左右连接实现。UNION连接两个表查询的结果。

-- 3.1联合查询，union关键字，结果集union结果集，注意两个结果集的列的个数相同。
SELECT t1.id,`name`,birthday,t2.score FROM t_student t1 
RIGHT JOIN t_score t2 ON t1.id = t2.student_id
UNION
SELECT t1.id,`name`,birthday,t2.score FROM t_student t1 
RIGHT JOIN t_score t2 ON t1.id = t2.student_id

-- 显示列名以第一个关键字列名为准
SELECT id,height FROM t_student WHERE id > 990
UNION
SELECT birthday,id FROM t_student WHERE id< 10

SQL JOIN 中 on 与 where 的区别
以下内容参考自SQL JOIN 中 on 与 where 的区别

• left join : 左连接，返回左表中所有的记录以及右表中连接字段相等的记录。
• right join : 右连接，返回右表中所有的记录以及左表中连接字段相等的记录。
• inner join : 内连接，又叫等值连接，只返回两个表中连接字段相等的行。
• full join : 外连接，返回两个表中的行：left join + right join。
• cross join : 结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。
• 关键字ON

• 数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
• 在使用 left jion 时，on 和 where 条件的区别如下：

• on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。
• where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

• left join、right join、full join 的特殊性：不管 on 上的条件是否为真都会返回 left 或 right 表中的记录，full 则具有 left 和 right 的特性的并集。 而 inner jion 没这个特殊性，则条件放在 on 中和 where 中，返回的结果集是相同的。

MySQL UNION 操作符
以下内容参考自MySQL UNION 操作符

• MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
• 

交叉连接（使用概率小）

-- 1、交叉连接:全相联,左表和右表的每一条数据都相互连接。
 SELECT t1.*,t2.score FROM t_student t1 INNER JOIN t_score t2

-- 1.1 可以理解成表连接，但是SELECT * FROM t_student t1,t_score t2会导致交叉连接，产生很多的数据，效率较低，不推荐。表连接时，推荐采用采用 INNER JOIN
SELECT * FROM t_student t1,t_score t2 WHERE t1.id = t2.student_id;

限定行数查询

-- 1、限定行数查询，可以限定从指定的位置查询到指定的位置
-- limit关键字，sqlserver：top oracle：rownum
-- LIMIT后跟一个参数的时候，表示限定查询的行数，开始的位置是表的行索引，默认从第0行开始查询。
SELECT * FROM t_student WHERE sex = '男' ORDER BY id LIMIT 5
-- 使用最频繁，从0位置开始查询，查询出来5行。
SELECT * FROM t_student WHERE sex = '男' ORDER BY id LIMIT 0,5
-- OFFSET：表示偏移位置，从第几行开始查。
SELECT * FROM t_student WHERE sex = '男' ORDER BY id LIMIT 5 OFFSET 0

MySQL中limit的用法
以下内容参考自动力字节

• mySql提供了limit ，主要用于提取前几条或者中间某几行数据。
  
• 取得前5条数据
  
• 从第二条开始取两条数据
  
• 取得薪水最高的前5名
  

分组查询

-- 分组查询：即按照一定的条件，将所有数据分组，对分组之后的数据进行统计查询
-- GROUP BY：表示分组。分组之后，可查询的数据只有分组列和聚合函数列
-- 一般count里面使用主键
SELECT sex,count(id) FROM t_student GROUP BY sex
-- 聚合函数：count：分组之后记录的个数；sum，avg，max，min
SELECT sex,count(id)人数,AVG(height) 平均身高,MIN(height) 最低身高,MAX(weight) 最大体重,SUM(iq) 智商总和 FROM t_student GROUP BY sex

• 查询结果
  
• GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

-- 多分组，符合分组，按多条件分组，使用逗号分割。
SELECT sex,CHAR_LENGTH(`name`) 字数,COUNT(id) FROM t_student 
GROUP BY sex,CHAR_LENGTH(`name`) ORDER BY 字数

-- 按出生年份分组
SELECT sex,YEAR(birthday) 出生年份,count(id) FROM t_student
GROUP BY sex,YEAR(birthday) ORDER BY 出生年份
-- 分组过滤，分组筛选，使用关键字，只能在分组列及集合函数列的条件。
-- 只查询2000年以后的分组
-- 注意: where是 写在group by前面，优先级高于group by 。
SELECT sex,YEAR(birthday) 出生年份,count(id) FROM t_student
GROUP BY sex,YEAR(birthday) HAVING 出生年份 >= 2000
ORDER BY 出生年份

• 无论是LENGTH()还是**CHAR_LENGTH()**都是为了统计字符串的长度。只不过，LENGTH()是按照字节来统计的，CHAR_LENGTH()是按照字符来统计的。例如：一个包含5个字符且每个字符占两个字节的字符串而言，LENGTH()返回长度10，CHAR_LENGTH()返回长度是5；如果对于单字节的字符，则两者返回结果相同。
• 编码是UTF-8，所以一个中文占3个字节,英文占1个字节。
• YEAR函数返回指定日期的年的部分
  语法：YEAR(date)
  参数：date参数是合法的日期表达式。
  返回值：int型数据

having子句
以下内容参考自SQL HAVING 子句

• 在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据。
• where 和having之后都是筛选条件，但是有区别的：

• where在group by前， having在group by 之后
• 聚合函数（avg、sum、max、min、count），不能作为条件放在where之后，但可以放在having之后

视图

• 视图就是一个结果集，不是一个真实的表，实际就是一个select查询语句
• 视图的作用是为了才做方便，减少重复，视图可以和表一样使用。
• 可以把查询语句写在视图里面，再次查询时，直接查询该视图即可。

-- 对视图的查询
-- SELECT * FROM v_fulladdress_1

增删改，去重

-- 增删改
-- 增加()VALUES(),第一个括号表示插入那几列，第二个括号表示插入那些值
INSERT INTO t_student(`stu_id`,`name`,sex,birthday,height)
VALUES('2000','三三','男','2000-1-1',180);
-- 如果不指定列名的话，意味着对每一列都需要赋值
INSERT INTO t_score VALUES (50,50,50,99.5,'A')
-- 要插入的数据，可以通过一个查询而得到
INSERT INTO t_student (stu_id,`name`,sex,birthday,height) 
SELECT '999',`name`,sex,birthday,height FROM t_hr_humen WHERE id = 10

-- 修改语法
UPDATE t_student SET sex ='男',`name` ='张大欣' WHERE id = 1

UPDATE t_student SET weight = weight + 10 WHERE id < 5

-- UPDATE支持表连接
UPDATE t_student t1 
INNER JOIN t_score t2 on t1.id = t2.student_id
SET iq = 300
WHERE t2.score = 100;

-- 删除语法
DELETE FROM t_student WHERE id = 1

-- 去重复,DISTINCT比较影响查询功能
SELECT sex FROM t_student
SELECT DISTINCT sex FROM t_student
-- 有多列表示联合去重，如只有id和性别相同才算相同。
SELECT DISTINCT id,sex FROM t_student