MySQL基础（第三章）

order by

通过order by可对查询结果进行排序，默认是升序，使用降序需要加上DESC，如ORDER BY 字段名 DESC。

limit

通过limit可以限制查询结果的条数，如前5条记录LIMIT 5，从第二条记录开始显示3条记录，LIMIT 1,3，注意limit的偏移量是从0开始的，limit一般出现在查询语句的最后。

内连接

示例：

SELECT u.id,proId,username,proName 
from cms_user u INNER JOIN provinces p
ON proId=p.id

INNER JOIN的注意事项：
1、需要指定on，也就是进行连接的两个表共有的字段
2、对于两个表中同名的字段，需要使用表的别名来区分

外连接

数据：


可以发现，在cms_user表中id为12的记录的proId在provinces表中是没有的，而provinces表中id为6的记录在cms_user表中没有相应的proId。

左外连接

示例：

SELECT u.id,proId,username,proName 
from cms_user u LEFT JOIN provinces p
ON proId=p.id ORDER BY proId DESC LIMIT 5; 

结果：

可以发现，左外连接其实就是连接两表的同时保留左表的全部内容。

右外连接

示例：

SELECT u.id,p.id 省份编号,username,proName 
from cms_user u RIGHT JOIN provinces p
ON proId=p.id ORDER BY 省份编号 DESC; 

结果：

可以发现，右外连接其实就是连接两表的同时保留右表的全部内容。

外键

外键的存在使得表与表之间有了联系，如先有部门，再有员工，部门是父表，员工则是子表，关联这两个表的字段是部门表的id字段也是员工表depId字段，这是这个字段被称为外键。
外键的作用：
1、限制子表数据的添加，如必须先有了1号部门，才能录入1号部门的员工信息；
2、限制父表数据的删改，如要撤销1号部门，则必须先将1号部门的员工调走或裁员。

创建带外键的表

CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED auto_increment KEY,
depName VARCHAR(20) NOT NULL UNIQUE) 
ENGINE=INNODB; #只有INNODB引擎才支持外键，所以指定引擎，但一般默认就是INNODB

CREATE TABLE employee(
id SMALLINT UNSIGNED auto_increment KEY,
ename VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
CONSTRAINT 外键 FOREIGN KEY (depId) REFERENCES department(id)) #创建名为外键的外键
ENGINE=INNODB;

创建表时创建外键的格式是：CONSTRAINT 外键名 FOREIGN KEY (字段名) REFERENCES 父表(父表的字段名)，CONSTRAINT 外键名可以省略，但一般不省略，因为如果需要删除外键则需要指定键名。

删除和添加外键

ALTER TABLE employee ADD CONSTRAINT 外键 #删除名为外键的外键


ALTER TABLE employee ADD CONSTRAINT 外键
FOREIGN KEY (depId) REFERENCES department(id); #添加名为外键的外键

通过外键使父表和子表联动

在创建外键的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包括RESTRICT、NO ACTION、SET NULL和CASCADE。其中RESTRICT和NO ACTION相同，是指在子表有关联记录的情况下父表不能更新（默认就是如此）；CASCADE表示父表在更新或者删除时，更新或者删除子表对应记录；SET NULL则是表示父表在更新或者删除的时候，子表的对应字段被SET NULL（需要注意，这种情况下，子表用于外键的字段不能有 NOT NULL约束）。

CASCADE示例：

CREATE TABLE employee(
id SMALLINT UNSIGNED auto_increment KEY,
ename VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
CONSTRAINT 外键 
FOREIGN KEY (depId) 
REFERENCES department(id) 
ON DELETE CASCADE #设置删除级联
ON UPDATE CASCADE)#设置更新级联
ENGINE=INNODB;

SET NULL示例：

CREATE TABLE employee(
id SMALLINT UNSIGNED auto_increment KEY,
ename VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
CONSTRAINT 外键 
FOREIGN KEY (depId) 
REFERENCES department(id) 
ON DELETE SET NULL  #设置删除级联
ON UPDATE SET NULL) #设置更新级联
ENGINE=INNODB;

联合查询

联合查询简单说就是把两个查询结果进行直接的拼接，分为UNION和UNION ALL，区别在于：UNION在拼接后会对重复结果进行去重，而UNION ALL则不会。
示例：

SELECT username FROM cms_user UNION SELECT username FROM cms_admin;
SELECT username FROM cms_user UNION ALL SELECT username FROM cms_admin;

子查询

子查询就是要执行的查询的语句的条件是另一个查询语句的结果的情况。

案例数据

记录学生成绩的表：

CREATE TABLE student (
id SMALLINT UNSIGNED PRIMARY KEY auto_increment,
sname VARCHAR(20) NOT NULL,
score TINYINT UNSIGNED); #UNSIGNED声明数值数据是无符号的

奖学金等级表：

CREATE TABLE scholarship(
id TINYINT UNSIGNED PRIMARY KEY auto_increment,
level TINYINT UNSIGNED);

90分以上一等奖学金，80以上二等，70分以上三等，低于70分没有奖学金。

示例：

#查询获得一等奖学金的学生的姓名和分数
SELECT sname,score FROM student WHERE score>=
(SELECT level FROM scholarship WHERE id=1);

子查询中的EXISTS

示例：

SELECT id,ename,depId FROM employee WHERE EXISTS
(SELECT * FROM department WHERE id=3);

EXISTS实际是一个执行查询的判断，当子查询有结果时才进行上一级的查询，需要注意，子查询的结果不影响上一级查询的结果，只是判断上一级执行与否。

子查询中ANY，SOME，ALL

示例：

#查询获得学金的学生的姓名和分数
SELECT sname,score FROM student WHERE score>=ANY
(SELECT level FROM scholarship);
#查询获得一等奖学金的学生的姓名和分数
SELECT sname,score FROM student WHERE score>=ALL
(SELECT level FROM scholarship);
#查询没有获得学金的学生的姓名和分数
SELECT sname,score FROM student WHERE score<ALL
(SELECT level FROM scholarship);
#查询没有获得一等奖学金的学生的姓名和分数
SELECT sname,score FROM student WHERE score<ANY
(SELECT level FROM scholarship);

从以上示例可知：
当ALL和>或>=结合使用时，是获取最大值，和<或<=结合使用时，是获取最小值；
ANY正好和ALL相反；
SOME和ANY是相同的，故不做测试。

正则表达式查询

示例：
数据

语句

SELECT id,sname FROM student WHERE sname REGEXP 's[0-9]{1}';
#查询姓名形如s1,s2...s9的学生的学号和姓名

sql语句中的正则表达式格式：

SELECT 字段1,字段2... FROM 表名 WHERE 字段 REGEXP 正则表达式;