sql语句汇总

利用sql语句来操作数据库

创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET charset_name] [COLLATE collation_name]

• 其中charset_name是为数据库指定的默认字符集

• Collate是为数据库指定的默认校对规则（校对规则是在字符集内用于比较字符的一套规则，可以控制select查询时where条件大小写是否敏感的规则。)

示例一：创建一个名称为mydb1的数据库。

CREATE  DATABASE  IF NOT EXISTS  mydb1;

示例二：创建一个使用gbk字符集的mydb2数据库。

CREATE  DATABASE  IF NOT EXISTS  mydb2 CHARACTER SET gbk;

查看数据库

语法：

# 显示所有数据库
SHOW DATABASES
# 显示数据库创建语句
SHOW CREATE DATABASE db_name

修改数据库

注意: 数据库一旦创建成功, 其名字无法修改

语法：

ALTER DATABASE db_name [CHARACTER SET charset_name] [COLLATE collation_name]

示例：查看服务器中的数据库，并把mydb2库的字符集修改为utf8

ALTER DATABASE mydb2 CHARACTER SET utf8

删除数据库

语法：

DROP DATABASE [IF EXISTS] db_name 

选择数据库

语法：

# 选择数据库 
USE db_name
# 查询当前选择的数据库 
SELECT DATABASE()

没有退出数据库的命令, 如果想退出当前数据库进入另一个数据,直接use切换到另一个数据库就可以了

利用sql语句来操作数据库表

创建表

CREATE TABLE student 
(
    student_id varchar(20) PRIMARY KEY COMMENT '学生编号',
    student_name varchar(20) NOT NULL COMMENT '学生姓名',
    gender char(10) NOT NULL DEFAULT '男' COMMENT '性别',
    birth_day date NOT NULL COMMENT '生日',
    age int NOT NULL DEFAULT '0' COMMENT '年龄',
    class_id varchar(20) NOT NULL DEFAULT 'C100' COMMENT '班级编号',
    score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',
    teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号'
)COMMENT='学生表';
--查询表结构
desc student

注意：

• 不区分大小写，但最好关键字都大写

• 关键字COMMENT设置备注

• 除主键字段外，建议其他所有字段都设置默认值，根据字段的业务含义赋默认值

• 永远不要更新主键的值

• 判断是否为空 gender is null / gender is not null

字符串类型：

• 定长字符串 — char(n)：长度固定的字符串类型，其长度是在创建表时指定的，默认开辟n个字符的空间大小，效率高。
• 不定长字符串 — varchar(n)：长度不固定的字符串类型，创建表时指定一个最大长度，或不指定长度。先判断字符长度，开辟对应长度的空间，效率低，节省空间。

数值类型：

• TINYINT: 占用1个字节，相对于java中的byte
• SMALLINT:占用2个字节，相对于java中的short
• INT：占用4个字节，相对于java中的int
• BIGINT：占用8个字节，相对于java中的long
• FLOAT：4字节单精度浮点类型，相对于java中的float
• DOUBLE：8字节双精度浮点类型，相对于java中的double

日期类型:

• DATE：日期 2017-11-05

• TIME：时间 格式 ‘HH:MM:SS’ 19:19:19

• DATETIME：日期时间 2017-11-05 19:19:19 年份范围：1000~9999

• TIMESTAMP：时间戳 2017-11-05 19:19:19 年份范围：1970~2037

逻辑型:

• BIT型字段只能取两个值：0或1。

字段的约束

1. 主键约束：保证所约束列中的值必须唯一且不能为空。

   • 添加主键约束(创建表时)： col_name datatype primary key
   • 更新表时：Alter table tabname add primary key(col_name)
   • 设置主键自动增长：col_name datatype primary key auto_increment

2. 唯一约束: unique 保证所约束列的值必须唯一，即不能重复。

   NULL和任何值都不等，包括它本身

3. 非空约束: not null 保证所约束列的值不能为空

4. 外键约束

操纵表

• 更新表名： RENAME TABLE 旧表名 TO 新表名;

• 更新字段名：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 字段类型;

• 更新字段类型：ALTER TABLE 表名 MODIFY 字段名 新字段类型;

• 删除字段默认值：ALTER TABLE 表名 ALTER COLUMN 字段名 DROP DEFAULT;

• 设置主键：ALTER TABLE 表名 ADD PRIMARY KEY (主键字段列表);

• 删除主键字段：ALTER TABLE 表名 DROP PRIMARY KEY;

• 新增字段： ALTER TABLE 表名 ADD COLUMN 新字段名 字段类型及其他属性;

• 删除字段： ALTER TABLE 表名 DROP COLUMN 字段名;

• 删除表：DROP TABLE 表名;

RENAME TABLE teacher_db TO teacher;
ALTER TABLE teacher CHANGE teacher_id id varchar(20);
ALTER TABLE teacher MODIFY id varchar(30);
ALTER TABLE teacher DROP PRIMARY KEY;
ALTER TABLE teacher ADD PRIMARY KEY(id);
ALTER TABLE teacher ALTER COLUMN teacher_name DROP DEFAULT;
ALTER TABLE teacher DROP COLUMN gender;
ALTER TABLE teacher ADD COLUMN gender varchar(10) DEFAULT '男' COMMENT '性别';
SELECT * FROM teacher;

• 插入数据

  • 插入整行字段：

    1. INSERT INTO 表名 VALUES (v1,v2,v3,v4);
    • 值的顺序与表中的字段顺序必须一致，不安全
    2. INSERT INTO 表名(字段1,字段2,字段3,字段4) VALUES (v1,v2,v3,v4);
    • 采用这种方式一一对应，安全

  • 插入部分字段：INSERT INTO 表名(字段1,字段2) VALUES (v1,v2);

  • 一次插入多行：

    • INSERT INTO 表名 VALUES (v1,v2,v3,v4), ('v1,v2,v3,v4);

  • 借助其他结果集插入：

    • INSERT INTO 表1 SELECT * FROM 表2;

  • 克隆表

    • SELECT * INTO teacher_bak FROM teacher;
    • CREATE TABLE teacher_bak AS SELECT * FROM teacher;

  SELECT * FROM teacher;
  INSERT INTO teacher VALUES('T100','李铭','男');
  INSERT INTO teacher(id,teacher_name)
         VALUES('T101','李刚'),
               ('T102','李小龙'),
               ('T103','王力');
  CREATE TABLE teacher_bak
  AS
  SELECT * FROM teacher;
  SELECT * FROM teacher_bak;
  

• 更新数据

  UPDATE tab_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]    
  #示例:
  UPDATE employee SET salary = 30000;
  

• 删除数据

  DELETE FROM tab_name [WHERE where_definition]
  # 示例：
  DELETE FROM employee WHERE name IS NULL;
  

  1. WHERE用来筛选要删除的记录，如果不使用WHERE子句，将删除表中所有数据 (逐条删除，效率不高)。

     TRUNCATE TABLE 表名 (删除表中所有记录，直接摧毁表，然后重建表，效率高)

  2. DELETE 语句不能删除某一列的值，应使用ALTER TABLE 表名 DROP COLUMN 列名

  3. delete语句仅删除记录，不删除表本身。如要删除表，使用DROP TABLE 表名

查询数据

• 查询部分列：SELECT 字段列表 FROM 表名;

• 查询所有列

  • SELECT * FROM 表名;
  • SELECT 所有字段列表 FROM 表名;

• 排序返回结果

  • SELECT 字段列表 FROM 表名 ORDER BY 字段1 [ASC/DESC],字段2 [ASC/DESC]…;
  • 默认为升序排序ASC

• 去重

  • SELECT DISTINCT 字段名 FROM 表名 LIMIT 10

  • 限制返回结果

    • 返回前几行：关键字TOP、LIMIT

      • SELECT TOP 10 字段列表 FROM 表名;

      • SELECT 字段列表 FROM 表名 LIMIT 10;

      • 一般与ORDER BY关键字配合使用，返回按某些字段排序后的前几行；

      • Mysql数据库不支持top的写法，仅支持Limit的写法

    • 返回中间几行：关键字LIMIT M OFFSET N（从第N行开始，返回M行记录）

      • SELECT 字段列表 FROM 表名 LIMIT M OFFSET N;
      • SELECT 字段列表 FROM 表名 LIMIT N,M;
      • 一般与ORDER BY关键字配合使用，按M行为一页，返回某一个分页的记录；

    • 返回后几行： 关键字TOP、LIMIT、ORDER BY

      • SQL语句中，没有返回后几行的专用写法，一般转换为按返回前几行的相反方式排序后，再返回前几行。使用这种方式变相的返回后几行。

    SELECT student_id, student_name, birth_day, age FROM student;
    SELECT student_id, student_name, birth_day, age FROM student 
    ORDER BY age ASC, student_name ASC;
    /*等价于*/
    SELECT student_id, student_name, birth_day, age FROM student 
    ORDER BY 4 ASC, 2 ASC;
    SELECT  student_id, student_name, birth_day, age FROM student 
    ORDER BY 4 ASC, 2 ASC
    LIMIT 10;
    SELECT  student_id, student_name, birth_day, age FROM student 
    ORDER BY 4 ASC, 2 ASC
    LIMIT 10,20
    

过滤数据

• 使用WHERE子句：SELECT 字段列表FROM 表名WHERE 过滤条件;

• 常用操作符
  

• 过滤单个值

  SELECT * FROM student WHERE birth_day <= '2005-12-31';

• 过滤NULL值

  SELECT * FROM student WHERE class_id IS NULL;

• 过滤集合

  SELECT * FROM student WHERE birth_day BETWEEN ‘2005-01-31’ AND ‘2005-12-31’;

  SELECT * FROM student WHERE age IN (10,11,15)

  SELECT * FROM student WHERE student_id NOT IN ('S20160001','S20160002')

  IN不支持通配符

• 使用通配符过滤数据：关键字LIKE

  符号	含义
  百分号%	匹配0~多个任意字符
  下划线_	匹配1个任意字符
  方括号[]、[^]	匹配1个字符集中的字符

  SELECT * FROM student WHERE student_name LIKE ‘陈%’

  Mysql中的转义符\

  Mysql数据库不支持[]和[^]，Sql Server支持

  使用通配符的注意点：

  • 不要过度使用通配符；
  • 如果确实需要使用，也尽量不要把通配符用在匹配模式的开始处；
  • 要特别注意通配符的位置，否则很有可能返回的结果与预期不一致；

• 组合WHERE子句

  • 使用逻辑操作符组合WHERE子句：

  • 比如：如何取出所有年龄大于15岁并且姓陈或男性同学？
    SELECT * FROM student WHERE age > 15 AND (student_name LIKE '陈%' OR gender = '男');

  • 在sql语句中 AND 和 OR 优先级相同

  • AND、OR谁在前面先执行谁

  • 在条件比较多的时候选用合理的()来消除歧义

    -- 知道老师的身份证号，想取出70年代出生的所有老师？
    -- 7-14位表示出生日期
    ALTER TABLE teacher ADD COLUMN identity_num char(18) DEFAULT '622301197012312360';
    SELECT * FROM teacher WHERE identity_num REGEXP '[0-9]{6}197.[0-9]{8}';
    

创建新的输出字段

• 数值计算 操作符：+, -, *, /

  SELECT trans_id,stock_name,price,volume,price*volume FROM t_stock_trans_dtl;

  注意：Teradata： INT / INT 返回INT类型

• 字段拼接

  示例：如何按“洪都航空（sh600316）”展示股票名称和股票代码？

  1. Access、SQL Server：使用拼接操作符加号（+)

  ​ SELECT stock_name + '(' + stock_code + ')' ​ FROM t_stock_trans_dtl;

  2. DB2、Oracle、Teradata：使用拼接操作符两个竖杠（||）

     SELECT stock_name || '(' || stock_code || ')' FROM t_stock_trans_dtl;

  3. MySQL：使用函数 CONCAT()

     SELECT CONCAT(stock_name,'(',stock_code,')') FROM t_stock_trans_dtl;

  4. 实现数值型字段的拼接

     CAST()函数可以将某种数据类型的表达式转化为另一种数据类型
     SELECT CAST('678' AS INT) + 123;

     CONVERT()函数可以将指定的数据类型转换为另一种数据类型
     SELECT CONVERT(INT, '678') + 123;

• 使用别名: 关键字AS

  SELECT CONCAT(stock_name,'(',stock_code,')') AS '股票名称和代码' FROM t_stock_trans_dtl;

  建议：不论中文别名中是否有空格，都用引号引起来

• CASE WHEN逻辑转换

  • CASE WHEN语法一：

    CASE WHEN 条件表达式1 THEN 结果表达式1
    	 WHEN 条件表达式2 THEN 结果表达式2
    				…
    	 WHEN 条件表达式N THEN 结果表达式N
    	 [ELSE ELSE 结果表达式]
    END
    

    没有ELSE部分时，如果所有条件表达式都不满足，则返回NULL。

    SELECT * FROM t_stock_trans_dtl;
    SELECT trans_id, volume,
    CASE WHEN volume >=3000 THEN '大单'
         WHEN volume <3000 and volume > 300 THEN '中单'
         ELSE '小单'
    END
    AS '买入量'
    FROM t_stock_trans_dtl
    WHERE opt_typ = '买入';
    

    每一个CASE WHEN都应该有ELSE分支

  • CASE WHEN语法二：

    CASE 条件表达式
    	WHEN 匹配表达式1 THEN 结果表达式1
    	WHEN 匹配表达式2 THEN 结果表达式2
    	…
    	WHEN匹配表达式N THEN 结果表达式N
    	[ELSE ELSE结果表达式]
    END
    

    适用于条件表达式的结果，是一个可穷举的列表。但不适用于条件表达式的结果是一个范围的情况。

    SELECT trans_id, volume,
    CASE opt_typ
         WHEN '买入' THEN price*volume
         WHEN '卖出' THEN 0 - price*volume
         ELSE 0
    END
    AS '交易金额'
    FROM t_stock_trans_dtl;
    

  • 嵌套CASE WHEN：

    CASE WHEN 外层条件表达式1 THEN (CASE WHEN 内层条件表达式1 THEN 内层结果表达式1
    											…
    								   WHEN 内层条件表达式M THEN 内层结果表达式M
    								   ELSE 内层ELSE结果表达式
    							   END)
    	…
    	WHEN 外层条件表达式N THEN 外层结果表达式N
    	ELSE 外层ELSE结果表达式
    END
    

    两种语法格式，都可以多层嵌套，但不推荐多层嵌套。建议最多嵌套两层并使用括号格式化SQL语句。

使用函数创建新的输出字段

• 数值函数

  函数	作用	示例
  abs(n)	返回n的绝对值	abs(-3) = 3
  round(n,d)	返回n的四舍五入值，保留d位小数	round(3.234,2) = 3.23
  rand()	返回0~1之间的随机数	0.2511382673
  pow(x,y)	返回x的y次幂	pow(2,3) = 8
  mod(m,n)	返回m除以n的余数	mod(7,3) = 1
  三角函数	实现三角运算的函数	sin(90)

  SELECT ABS(volume) AS 'volume', ROUND(fee,2) AS 'fee' FROM  t_stock_trans_dtl
  

• 文本函数

  函数	作用	示例
  length(str)	返回字符串str的长度	length(‘ABCDE’) = 5
  left(str,n)	返回字符串str的左端n个字符	left('ABCDE’,3) = ‘ABC’
  right(str,n)	返回字符串str的右端n个字符	right('ABCDE’,3) = ‘CDE’
  trim(str)	删除str左右两边的空格	trim(‘ ABC ‘)=‘ABC’
  upper(str)	将字符串str转为大写	upper(‘Abc’) = ‘ABC’
  lower(str)	将字符串str转为小写	lower(‘Abc’) = ‘abc’
  substring(str,m,n)	返回从字符串str第m位后面的n个字符	substring('ABCDE’,2,3) = ‘BCD’
  instr(str1,str2)	返回字符串str2在str1中首次出现的位置	instr(‘foobarbar’, 'bar’) = 4
  concat(str1,str2,…)	返回字符串str1,str2,…按顺序拼接后的结果	concat(‘ABC’,‘DEFG’,'H’) ‘ABCDEFGH’

  -- 如何根据股票交易明细表中的股票代码，返回股票所在的证券交易所？
  SELECT stock_code,left(stock_code,2) FROM t_stock_trans_dtl;
  SELECT stock_code,substring(stock_code,1,2) FROM t_stock_trans_dtl;
  

• 日期和时间函数

  函数	作用	示例
  current_date()	返回当前日期	’2018-02-27’
  current_time()	返回当前时间	’12:34:10’
  year(date)	返回日期date所在的年份	year(‘2018-02-27’) = 2018
  month(date)	返回日期date所在的月份	month(‘2018-02-27’) = 2
  day(date)	返回日期date所在的日	day(‘2018-02-27’) = 27
  hour(date)	返回日期date所在的小时	hour(’12:40:32’) = 12
  minute(date)	返回日期date所在的分钟	minute(’12:40:32’) = 40
  dayofyear(date)	返回date是当年的第几天	dayofyear(’2018-02-27’) = 58
  dayofmonth(date)	返回date是当月的第几天	dayofmonth(’2018-02-27’) = 27
  dayofweek(date)	返回date是当周的第几天（第1天是周日）	dayofweek(’2018-02-27’) = 3
  date_format(date,格式)	返回date格式化后的结果	date_format(current_date(),’%Y’)= 2018

• IF函数（三元运算符的效果）

  • SELECT IF(10< 5, '大', '小')

• 函数的嵌套

  -- 如何根据股票交易明细表中的股票代码，返回股票所在的证券交易所（要求返回大写字母）？
  SELECT stock_code,upper(left(stock_code,2)) FROM t_stock_trans_dtl;
  

汇总统计

• 汇总统计

  总共有多少名学生？         SELECT COUNT(*) FROM student;
  所有学生的数学成绩总和？  	SELECT SUM(score) FROM student;
  所有学生的数学成绩最高分？   SELECT MAX(score) FROM student;
  所有学生的数学成绩最低分？	SELECT MIN(score) FROM student;
  所有学生的数学成绩平均分？	SELECT AVG(score) FROM student;
  

  • 统计结果忽略值为NULL的行！

  • 可以和DISTINCT搭配去重

    例如：SELECT COUNT(DISTINCT score) FROM student;

  • 和分组函数一起查询的字段要求是GROUP BY后的字段

• 分组汇总统计 关键字：GROUP BY

  SELECT 分组函数, 列(要求出现在 GROUP BY 后面)
  FROM 表名
  [WHERE 筛选条件]
  GROUP BY 分组的列表
  [ORDER BY 子句] 
  

  比如：如何获取每个班级的数学成绩平均分（因为学生太多，这里先不计算成绩在80分以下的学生），以便比较不同班级的成绩？

  SELECT class_id,avg(score) FROM student WHERE score > 80 GROUP BY class_id;
  

-- 如何统计不同的交易所的总的买入金额？
SELECT left(stock_code,2) AS 'location',sum(price*volume) AS '成交量'
FROM t_stock_trans_dtl
GROUP BY location;

-- 如何统计不同的交易所的不同交易类型的总的交易金额
SELECT left(stock_code,2) AS 'location',opt_typ,abs(sum(price*volume)) AS '成交量'
FROM t_stock_trans_dtl
GROUP BY location, opt_typ;

• 过滤分组结果 关键字：GROUP BY + HAVING

  • WHERE：过滤行，在GROUP BY前执行。
  • HAVING：过滤分组，在GROUP BY后执行。
  • WHERE用于过滤原始表中的数据，HAVING用于筛选分组后的结果集
  • 能够使用WHERE的，优先使用WHERE
  • 分组函数做条件一定放在HAVING子句中

• 按多个字段分组

  案例：查询每个部门每个工种的员工的平均工资

  SELECT AVG(salary), department_id, job_id  
  FROM employees  
  GROUP BY department_id, job_id;
  

• 排序分组结果 关键字：GROUP BY + ORDER BY

  • 集合具有无序性，如果每次查询都想得到确定的排序结果，则必须使用ORDER BY

  -- 如何获取每个班级的数学成绩平均分（不计算成绩在80分以下的学生且过滤掉平均分在90分以下的班级），然后以平均分从高到低排序？
  SELECT
  class_id,avg(score)
  FROM student
  WHERE score > 80
  GROUP BY class_id
  HAVING avg(score) > 90
  ORDER BY avg(score) DESC;
  

• SELECT子句顺序

   一个完成的SELECT语句包含可选的几个子句。SELECT语句的定义如下：
  <SELECT clause> 
  [<FROM clause>] 
  [<WHERE clause>] 
  [<GROUP BY clause>] 
  [<HAVING clause>]
  [<ORDER BY clause>] 
  [<LIMIT clause>]   
   SELECT子句是必选的，其它子句如WHERE子句、GROUP BY子句等是可选的。
  

  SQL Select语句完整的执行顺序：

  1、from子句组装来自不同数据源的数据； 
  2、where子句基于指定的条件对记录行进行筛选； 
  3、group by子句将数据划分为多个分组； 
  4、使用聚集函数进行计算； 
  5、使用having子句筛选分组； 
  6、计算所有的表达式； 
  7、select 的字段；
  8、使用order by对结果集进行排序。
  

开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最终结果

子查询及EXISTS

外键约束

• 外键：唯一标识其他表中的一条记录，用来通知数据库两张表字段之间的对应关系, 并让数据库帮我们维护这样关系的键就叫做外键
• 外键作用: 确保数据库数据的完整性和一致性
• 添加外键: foreign key(外键) references 关联表(关联列)
• 外键约束会导致效率低，不灵活，所以一般开发时不用，而是通过代码维护

多表设计

1. 一对多：在多的一方添加列保存一的一方的主键来作为外键，来保存两张表之间的关系
2. 一对一：在任意一方添加列保存另一方的主键作为外键, 来保存两张表之间的关系
3. 多对多：在一张第三方的表中分别保存两张表的主键作为外键，来保存两张表之间的关系，可以把多对多的关系拆分成两个一对多的关系来理解

子查询：嵌套在其他查询中的查询

• 使用子查询作为计算字段

  -- 如何同时查询出学生编号、学生姓名、老师编号、老师姓名？
    SELECT * FROM student;
    SELECT student_id, student_name, teacher_id,
    (
     SELECT teacher_name 
     FROM teacher
     WHERE teacher.teacher_id = student.teacher_id
    )AS teacher_name 
    FROM student;
    -- 使用表别名消除字段歧义
    SELECT student_id, student_name, teacher_id,
    (
     SELECT teacher_name 
     FROM teacher b
     WHERE b.teacher_id = a.teacher_id
    )AS teacher_name 
    FROM student a;
  

  子查询只能对应一条记录

• 使用子查询过滤数据（IN）

  -- 如何获取姓牛的老师教了哪些学生？
  SELECT student_id, student_name 
  FROM student
  WHERE teacher_id IN (
        SELECT teacher_id 
        FROM teacher
        WHERE teacher_name like '牛%' 
  );
  -- IN后是一个集合
  

• 使用子查询过滤数据（EXISTS）

  -- 如何获取姓牛的老师教了哪些学生？
  SELECT student_id, student_name 
  FROM student
  WHERE EXISTS(
        SELECT 1 
        FROM teacher
        WHERE student.teacher_id = teacher.teacher_id
        AND teacher.teacher_name like '牛%' 
  );
  -- 如何获取除姓牛的老师之外的其他老师教了哪些学生？
  SELECT
  student_id,student_name
  FROM student a
  WHERE NOT EXISTS ( SELECT 1 FROM teacher b
                     WHERE a.teacher_id = b.teacher_id
                     AND b.teacher_name like '牛%'
  );
  

  • EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。

组合查询UNION ALL

• 组合查询 UNION ALL

  • 用于合并多个结果集

  • 查询结果有重复数据（交集重复），得到结果集的全部数据

• 组合查询 UNION

  • 合并结果去重

  -- 合并来源于不同的表的结果集
  -- 如何同时查询出所有的学生编号、学生姓名和老师编号、老师姓名？
  SELECT student_id,student_name FROM student
  UNION
  SELECT teacher_id,teacher_name FROM teacher;
  

  需要注意的几点：

  1. 待合并的结果集的字段数量必须一致。
  2. 合并后的结果集的title与第一个结果集保持一致。
  3. 待合并的结果集的字段顺序、字段类型的大类及字段值的含义尽量保持一
     致。
  • UNION ALL与UNION混用

  -- 同时查询出年龄为10岁或一年级一班（前面两个结果集需要去除重复）或性别为男（合并时不去除重复）的所有学生？
  SELECT * FROM student WHERE age = 10
  UNION
  SELECT * FROM student WHERE class_id = 'G0101'
  UNION ALL
  SELECT * FROM student WHERE gender = '男';
  

  1. UNION ALL与UNION的执行优先级一致，谁在前谁先执行；
  2. 不可以使用括号改变执行优先级；

• 组合查询结果集的排序

  -- 如何同时查询出年龄为10岁或一年级一班的所有学生（按姓名升序排序）？
  SELECT * FROM student WHERE age = 10
  UNION ALL
  SELECT * FROM student WHERE class_id = 'G0101'
  ORDER BY student_name;
  

连接表

• 关于笛卡尔积

  • 笛卡尔积：两个集合的乘积

• 交叉连接CROSS JOIN

  使用CROSS JOIN实现两个集合的笛卡尔积。

  SELECT * FROM student;--3
  SELECT * FROM teacher;--4
  SELECT * FROM student CROSS JOIN teacher;--12=3*4
  

• 内连接INNER JOIN

  示例：如何同时查询出学生编号、学生姓名、老师编号、老师姓名？
  

  SELECT * FROM student a INNER JOIN teacher b
  ON a.teacher_id = b.teacher_id;
  
  SELECT * FROM student a INNER JOIN teacher b
  WHERE a.teacher_id = b.teacher_id;
  -- 非标准写法，MySQL独有，其他数据库不支持。不推荐！
  
  SELECT * FROM student a , teacher b
  WHERE a.teacher_id = b.teacher_id;
  -- INNER关键字可以省略
  -- 等值连接 sql92 标准
  
  SELECT 
    a.ename AS empName,
    b.ename AS managerName 
  FROM emp a,emp b 
  WHERE a.mgr = b.empno;
  -- INNER关键字可以省略 
  -- 等值连接 sql92 标准
  

• 全外连接FULL OUTER JOIN

  示例：如何同时查询出学生编号、学生姓名、老师编号、老师姓名？
  

  SELECT * FROM  student a FULL OUTER JOIN teacher b
  ON a.teacher_id = b.teacher_id;
  -- 虽然绝大数据数据库都支持，但MySQL不支持FULL OUTER JOIN。
  -- OUTER关键字可以省略
  

  Mysql可以使用UNION结合LEFT JOIN 和 RIGHT JOIN实现全外连接

• 左外连接LEFT OUTER JOIN

  示例：如何同时查询出学生编号、学生姓名、老师编号、老师姓名？
  

  SELECT * FROM student a LEFT OUTER JOIN teacher b
  ON a.teacher_id = b.teacher_id; 
  -- OUTER关键字可以省略
  

• 右外连接RIGHT OUTER JOIN

  示例：如何同时查询出学生编号、学生姓名、老师编号、老师姓名？
  

  SELECT * FROM student a RIGHT OUTER JOIN teacher b
  ON a.teacher_id = b.teacher_id; 
  -- OUTER关键字可以省略
  

• 连接表的执行步骤

  • 第一步：参与连接的两个表做笛卡尔积；
  • 第二步：根据ON后的连接条件筛选笛卡尔积的结果；
  • 第三步：补充左表（LEFT JOIN）或右表（RIGHT JOIN）不满足连接条件的数据（INNER JOIN内关联时无此步骤）；
  • 第四步：根据WHERE后的过滤条件筛选第三步的结果；

• 多表连接：多表连接，就是第一张表与后面的表依次连接，重复执行表连接的步骤而已！

• 非唯一键连接：表关联时，首先需要确认的一点，就是关联条件字段在关联表中是不是唯一。在绝大多数的情况下，关联条件字段都是关联表中的主键或能唯一确定一条 记录的字段。如果不是，很可能是SQL的关联条件有问题，需要仔细确认是否与需求相符。