[TOC]
0x00 前言
什么是 SQL语言？
答：SQL指结构化查询语言，全称是 Structured Query Language，是一种 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言。它是用于访问和处理数据库的标准的计算机语言。
SQL 能做什么？
- SQL 面向数据库执行查询
- SQL 可从数据库取回数据
- SQL 可在数据库中插入新的记录
- SQL 可更新数据库中的数据
- SQL 可从数据库删除记录
- SQL 可创建新数据库
- SQL 可在数据库中创建新表
- SQL 可在数据库中
创建存储过程 - SQL 可在数据库中
创建视图 - SQL 可以
设置表、存储过程和视图的权限
MySQL是属于关系型数据库 RDBMS中的数据存储在被称为表的数据库对象中，表是相关的数据项的集合，它由列cols和行rows组成。
什么是 RDBMS ?
答：指关系型数据库管理系统，全称 Relational Database Management System;RDBMS 是 SQL 的基础同样也是所有现代数据库系统的基础，比如 MSSQL Server、IBM DB2、Oracle、MySQL 以及 Microsoft Access。
如何学习SQL和查看命令帮助?
答:linux中采用man,help与info命令，在mysql中是help建议在学习的时候多采用帮助文档；1
2
3
4
5
6
7
8
9
10
11
12mysql> help contents
Account Management
Administration
...
mysql> help Account Management
CREATE USER
DROP USER
GRANT
RENAME USER
REVOKE
SET PASSWORD
注释：除了 SQL 标准之外，大部分 SQL 数据库程序都拥有它们自己的专有扩展，比如MySQL 与 Oracle 之间扩展模块是有所不同的！
0x01 学习环境
学习SQL环境快速安装：
- 系统:CentOS7
- 容器:Docker , Docker-Compose
- 数据库版本: MySQL 8.0
- 数据库管理: Adminer 简单数据库管理
- 镜像来源:https://hub.docker.com/_/mysql
1 | #安装Docker-ce (注意安装前删除老版本) |
docker-compose.yml WeiyiGeek.1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25# Use root/example as user/password credentials 默认MySQL版本是8.x
version: '3.1'
services:
db:
image: mysql
command: --default-authentication-plugin=mysql_native_password
restart: always
environment:
MYSQL_ROOT_PASSWORD: example
ports:
- 3308:3306
db5:
image: mysql:5.7.28
container_name: mysql5
command: --default-authentication-plugin=mysql_native_password
restart: always
environment:
MYSQL_ROOT_PASSWORD: example
ports:
- 3305:3306
adminer:
image: adminer
restart: always
ports:
- 8080:8080
运行和查询1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18#手动运行容器
$ docker run --name mysql-test -e MYSQL_ROOT_PASSWORD=my-secret-pw --default-authentication-plugin=mysql_native_password -d mysql:tag
$ docker run -it --network some-network --rm mysql mysql -hsome-mysql -uexample-user -p #对Docker网络中的MySQL进行连接
$ docker run -it --rm mysql mysql -hsome.mysql.host -usome-mysql-user -p #连接其他MySQL
#使用自定义MySQL配置文件
$ docker run --name some-mysql -v /my/custom:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag
$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
#在主机系统的适当卷上创建一个数据目录，例如/my/own/datadir。
$ docker run --name some-mysql -v /my/own/datadir:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag
#测试查询
docker ps
# CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
# 4ba28666e63f adminer "entrypoint.sh doc..." 45 hours ago Up 44 hours 0.0.0.0:9080->8080/tcp data_adminer_1
# 2172955ddedc mysql "docker-entrypoint..." 45 hours ago Up 44 hours 33060/tcp, 0.0.0.0:9001->3306/tcp data_db_1
...
0x02 SQL基础
SQL标准语句
- 1.建议在每一句SQL后加上一个分号(
在数据库系统中分隔每条 SQL 语句的标准方法,这样在对服务器的相同请求中执行一条以上的 SQL 语句),因为某些数据库系统要求在每条 SQL 语句的末端使用分号。 - 2.SQL 对大小写不敏感
SELECT 与 select 是相同的； - 3.SQL 数据库备份文件中及其交互命令行中的注释是
/* 注释案例 */ 和 --注释案例；
常见的SQL语句:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32//0.CRUD (创建/查询/更新/删除)
//1.数据定义语言 - DDL
CREATE DATABASE|TABLE|INDEX|USER|VIEW - 创建新数据库 / 创建新表 / 创建索引（搜索键）/ 创建用户 / 创建视图
ALTER DATABASE|TABLE|INDEX|USER - 修改数据库 / 变更（改变）数据库表 / 修改索引 / 更改用户
DROP DATABASE|TABLE|INDEX|USER - 删除数据库 / 删除表 / 删除索引 / 删除用户
RENAME DATABASE|TABLE|USER - 重命名数据库和数据表 / 重命名用户
TRUNCATE TABLE - 清空表数据
//2. 数据查询语言- DQL(查询)
SELECT - 从数据库中提取数据
FROM 字句
WHERE 字句
//3.数据操作语言 - DML(增删改)
UPDATE - 更新数据库中的数据
DELETE - 从数据库中删除数据
INSERT INTO - 向数据库中插入新数据
//4.数据控制语言 - DCL(权限)
GRANT - 设置数据库表rw权限设置（访问、安全权限）
REVOKE - 取消数据库表rw权限设置
IF...ELSE
WHILE
BEGIN
TRANSACTION
//5.事务处理语言 - TPL
COMMIT - 事务提交
ROLLBACK - 事务回滚
SQL分类
SQL语言大致分为以下几类:
数据定义语言（DDL）:用于创建、修改、和删除数据库内的数据结构(
create drop alter(修改) rename);数据查询语言（DQL）: 从数据库中的一个或多个表中查询数据(SELECT)条件包
WHERE (条件) ，ORDER BY (排序) ，having , Group By (分组), limit(限制显示)数据操作语言（DML）:增删修改数据库中的数据，包括
插入(INSERT)、更新(UPDATE)和删除(DELETE);数据控制语言（DCL）:用于对数据库的访问权限进行控制，如1.给用户授予访问权限（GRANT）;2.取消用户访问权限（REVOKE）
指针控制语言（CCL）:
CURSOR Control Language语句DECLARE CURSOR，FETCH INITO和update where current用于对一个或多个表单独行操作；事务处理语言（TPL）: 能够确保被DML语句影响的表的所有行得到及时更新，TPL语句包括
BEGIN ，TRANSACTION ，COMMIT(提交) ，ROLLBACK(回滚)等等;
补充学习:
- 1.大多数数据库软件系统都允许使用编程函数在结果集中进行导航，比如：
Move-To-First-Record、Get-Record-Content、Move-To-Next-Record等等。
0x03 SQL语句
SELECT 语句
描述:SELECT 语句用于从数据库中选取数据,可以加上条件或者语句参数进行过滤显示数据; SQL 查询的五种子句where(条件查询)、having（筛选）、group by（分组）、order by（排序 ASC | DESC）、limit（限制结果数）;
1 | /* SQL SELECT 语法 */ |
WHERE 子句中的运算符:
- (1) 比较运算符:= > < >= ,<=, !=,
<> 表示（不等于）, BETWEEN(在某个范围内), LIKE(搜索某种模式), IN(指定针对某个列的多个可能值)，IS(是否赛某列的值) - (2) 逻辑运算:
(优先级排列), not (满足不包含该条件的值) , and, or
NOT与谓词进行组合条件的查询：
- (1) NOT BERWEEN…AND…对介于起始值和终止值间的数据时行查询可改成<起始值AND>终止值
- (2) IS NOT NULL 对非空值进行查询
- (3) NOT IN 该式根据使用的关键字是包含在列表内还是排除在列表外，指定表达式的搜索，搜索表达式可以是常量或列名，而列名可以是一组常量，但更多情况下是子查询
- (4) NOT LIKE 匹配显示不满足条件的行
- (5) NOT REGEXP 不显示正则表达式匹配的行
NULL 值判断
- (1) IS NULL 对空值进行查询
- (2) IS NOT NULL 对非空值进行查询
基础示例:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51set names utf8; /*命令用于设置使用的字符集（如果在配置文件中设置了Server或者Client端的字符集则不需要指定）*/
-- 查询用户数据库.表
mysql> select user,host from mysql.user;
mysql> select id,name,country from websites;
-- 重复去掉 distinct： 从 "Websites" 表的 "country" 列中选取唯一不同的值，也就是去掉 "country" 列重复值
mysql> select distinct country from websites;
-- 比较运算符
mysql> SELECT * FROM websites WHERE country="CN";
mysql> SELECT * FROM websites WHERE id=1; /* SQL 可以直接输入 数字 表示数值**/
mysql> SELECT * FROM websites WHERE id <> 1; /*显示不包括id=1的数据;*/
mysql> SELECT * FROM websites WHERE id>=2 and country != "CN"; /**采用逻辑运算符和比较运算符连用*/
-- 不带比较运算符的 WHERE 子句
mysql> SELECT host FROM mysql.user WHERE 0; /*则会返回一个空集，因为每一行记录 WHERE 都返回 false。*/
mysql> SELECT host FROM mysql.user WHERE 1; /*返回MySQL表中host字段,因为每一行记录 WHERE 都返回 true。*/
-- 空值判断is null：打印编写满足列某值的某行是,如果不满足则返回该标字段及其字段的值为NULL;)
mysql> SELECT * FROM information_schema.FILES WHERE CHECKSUM IS NULL
mysql> SELECT * FROM information_schema.FILES WHERE CHECKSUM IS NOT NULL
-- 条件语句between.and. ：查询 emp 表中 SAL 列中大于等于 1500 的小于 3000 的值
mysql> SELECT * FROM emp WHERE sal between 1500 and 3000;
-- 运算符 In (包含运算). 查询 EMP 表 SAL 列中等于 5000，3000，1500 的值。
mysql> SELECT * FROM emp WHERE sal in (5000,3000,1500);
-- 模糊查询Like：查询 EMP 表中 Ename 列中有 M 的值，M 为要查询内容中的模糊信息。
mysql> SELECT * FROM emp WHERE ename like 'Java%';
mysql> SELECT * FROM tb_stu WHERE name like'%程序_';
mysql> SELECT * FROM tb_stu WHERE name like'%PHP%';
-- 模糊查询REGEXP：使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式查询匹配的模糊信息。
mysql> SELECT host,user FROM user WHERE user REGEXP '[^root]'
mysql> SELECT host,user FROM user WHERE user REGEXP '^root'
-- 联合使用() 比较 条件 逻辑 运算符
SELECT * FROM websites WHERE alexa > 15 AND (country='CN' OR country='USA');
SELECT * FROM websites WHERE (alexa > 15 and alexa % 2 != 0) AND (country='CN' OR country='USA');
-- 排序语句 ORDER BY:多列排序的时候，先按照第一个column name排序，在按照第二个column name排序；
SELECT * FROM tb_name WHERE address <> '' order by addtime desc, id asc; /**查询不为空的数据*/
-- 显示行数 limit语句：查询前n条/后n条记录
SELECT Host,User,password_last_changed FROM mysql.user WHERE 1 limit 0,3; --前三条
SELECT Host,User,password_last_changed FROM mysql.user WHERE 1 ORDER BY HOST DESC limit 0,3; --后三条
-- 子查询也叫内部查询如select avg(score) from studentscore就是子查询
-- 查询学生成绩表里分数低于平均成绩的学生姓名和成绩，并根据分数降序排列
select name,score form studentscore where score < (select avg(score) from studentscore) order by score DESC
WeiyiGeek.
多表查询
1 | CREATE DATABASE student; |
基础示例:1
2
3
4-- 联表查询
SELECT user.xh,user.xm,user.nj,user_kc.kcm,user_kc.grade
FROM user,user_kc
WHERE user.xh=user_kc.xh;
WeiyiGeek.联表查询
函数使用
- user() ：当前用户
- now() ：当前时间
基础示例:
1 | select user(); |
注意事项:
- 注:SQL语句中的DISTINCT必须与WHERE子句联合使用，否则输出的信息不会有变化且字段不能用*代替;
- 注:SQL语句中的BETWEEN .. AND …条件语句，下限在前，上限在后，查询的范围包涵有上下限的值。
- 注:SQL语句中文本与数值字段，使用单引号来环绕文本值（大部分数据库系统也接受双引号），数值是不需要加上单双引号，但是注意字段类型的影响;
- 注:WHERE 子句并不一定带比较运算符，当不带运算符时会执行一个隐式转换 WHERE 0|1。当 0 时转化为 false，1 转化为 true。
- 注:我们可以把比较运算符，逻辑运算符，以及条件运算符，加上括号()来组成复杂的表达式。
- 注:对字段进行排序时若不指定排序方式，则默认为ASC升序；对查询信息进行多条件排序是为了共同限制记录的输出，一般情况下，由于不是单一条件限制，所以在输出效果上有一些差别。
- 注:在对数据库中的日期字段进行查询筛选的时候，注意不同数据库对日期型数据存在差异；
- 注:在使用REGEXP子句后后面的正则匹配需要采用’ ‘进行包含;
INSERT 语句
描述:INSERT INTO 语句用于向表中插入新记录。
1 | -- INSERT INTO 语句可以有两种编写形式。 |
基础示例:
1 | -- 向 "Websites" 表中插入一个新行。 |
WeiyiGeek.
注意事项:
- 注:在ID或者其他字段设置自增长的时候可以不用向id插入数值，因为id 列是自动更新的，表中的每条记录都有一个唯一的数字。
- 注:insert into select 和select into from 的区别（MySQL 数据库不支持，Mssql支持），前则要求行表scorebak 必须存在，后者要求表scorebak 不存在；
UPDATE 语句
描述:UPDATE 语句用于更新表中已存在的记录。
语法:
1 | UPDATE table_name |
基础示例:
1 | -- 假设我们要把 "菜鸟教程" 的 alexa 排名更新为 5000，country 改为 USA。 |
补充说明:
1.在 MySQL 中可以通过设置 sql_safe_updates 这个自带的参数来解决，当该参数开启的情况下，你必须在update 语句后携带 where 条件，否则就会报错。
1 | mysql> SHOW VARIABLES LIKE 'sql_safe_updates'; -- 值得(注意) |
注意事项:
- 注:WHERE 子句规定哪条记录或者哪些记录需要更新,WHERE 子句规定哪条记录或者哪些记录需要更新;
DELETE 语句
描述:DELETE 语句用于删除表中的行。
1 | DELETE FROM table_name |
基础数据:
1 | -- 假设我们要从 "Websites" 表中删除网站名为 "ORACLE" 且国家为 USA 的网站 。 |
WeiyiGeek.
补充说明: WeiyiGeek.
1.SQL关于删除的三个语句：DROP、TRUNCATE、 DELETE的区别:1
2
3
4
5
6
7
8
9
10
11
12
13
14删除表: drop table 表名;
清空表数据: delete from 表名;
清空表数据: truncate table 表名;
#1.删除表test并释放空间，将test删除的一干二净。
DROP table test;
#2.删除表test里的内容并释放空间，但不删除表的定义，表的结构还在。
TRUNCATE test;
#3.删除整个表:仅删除表test内的所有内容，保留表的定义，不释放空间。
DELETE FROM test 或者 DELETE * FROM test ; @
#删除指定数据:删除表test中年龄等于30的且国家为US的数据
DELETE FROM test WHERE age=30 AND country='US';
2.什么时候使用DROP?什么时候使用DELETE?
答：对于结构删除，如数据库删除、表删除、索引删除等当使用DROP，而对于数据的删除，和事务有关, 或者想触发 trigger则用DELETE，想保留表而将所有数据删除和事务无关用 truncate 即可，如果是整理表内部的碎片,可以用 truncate 跟上 reuse stroage再重新导入/插入数据；;
3.总结
1.truncate和delete 只删除数据不会影响表结构，drop语句删除表的结构被依赖的约束(constrain), 触发器(trigger), 索引(index)，依赖于该表的存储过程/函数将保留, 但是变为 invalid 状态。
2.delete语句是DML改操作会放到rollback segment中事务提交后才生效（如果有与之对应的触发器执行时候将被触发），而truncate和drop是DDL操作会立即生效，源数据不妨到rollback segment中既不能回滚也不能触发trigger；
3.delete 语句不影响表所占用的 extent, 高水线(high watermark)保持原位置不动，drop 语句将表所占用的空间全部释放，truncate 语句缺省情况下见空间释放到 minextents 个 extent, 除非使用 reuse storage， truncate会将高水线复位(回到最开始)。
4.速度：
drop > truncate > delete，安全性: 小心使用 drop 和 truncate, 尤其没有备份的时候否则哭都来不及。5.不带where参数的delete语句是删除mysql表中所有内容，delete的效果有点像将mysql表中所有记录一条一条删除到删完，而使用truncate table也可以清空mysql表中所有内容，相当于保留mysql表的结构，重新创建了这个表，所有的状态都相当于新表。
注意事项:
- 注:与update语句一样，WHERE 子句规定哪条记录或者哪些记录需要删除;如果您省略了WHERE子句，所有的记录都将被删除！
- 注:在效率上truncate比delete快，但truncate删除后不记录mysql日志不可以恢复数据;
0x04 SQL进阶
描述:SQL语句联合使用的其他子语句，是相当的重要的；
TOP 子句
描述:SELECT TOP 子句用于规定要返回的记录的数目，它对于拥有数千条记录的大型表来说，是非常有用的。
1 | -- #SQL Server / MS Access 语法 |
基础示例:
1 | -- 从 websites 表中选取前面百分之 50 的记录(MSSQL)： |
注意事项:
- 注:并非所有的数据库系统都支持 SELECT TOP 语句。 MySQL 支持 LIMIT 语句来选取指定的条数数据， Oracle 可以使用 ROWNUM 来选取。
LIKE 和 REGEXP 子句
基础语法:1
2
3
4/* LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式 */
SELECT column_name(s)
FROM table_name
WHERE column_name [LIKE|REGEXP] pattern;
LIKE 模糊查询通配符说明: % 表示多个字值，_ 下划线表示一个字符;
REGEXP 模糊查询通配符说明:'[abc]'字符列中的任何单一字符，[!abc]或者[^abc]不在字符列中的任何单一字符；
- M% : 为能配符，正则表达式，表示的意思为模糊查询信息为 M 开头的。
- %M% : 表示查询包含M的所有内容。
- %M_ : 表示查询以M在倒数第二位的所有内容。
'^[a-zA-Z0-9]':表示查询以a-z或者A-Z以及0-9开头的内容；
基础示例:
1 | -- LIKE子句的模糊查询 |
WeiyiGeek.
IN 子句
描述:IN 操作符允许您在 WHERE 子句中规定多个值。
1 | -- SQL IN 语法（以 , 号分割） |
IN 与 = 的异同说明?
- 相同点：均在WHERE中使用作为筛选条件之一、均是等于的含义；
- 不同点：IN可以规定多个值，等于规定一个值；
基础示例:
1 | -- in 与 = 的转换 |
WeiyiGeek.
BETWEEN 操作符
描述:BETWEEN 操作符用于选取介于两个值之间的数据范围内的值(值可以是数值，文本或者日期)。
1 | -- SQL BETWEEN 语法 |
基础演示: WeiyiGeek.1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25-- SQL 语句选取 alexa 介于 1 和 20 之间的所有网站：
SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20;
-- 如需显示不在上面实例范围内的网站，请使用 NOT BETWEEN：
SELECT * FROM Websites
WHERE alexa NOT BETWEEN 1 AND 20;
-- 选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站(注意ADD子句)
SELECT * FROM Websites
WHERE (alexa BETWEEN 1 AND 20)
AND country NOT IN ('USA', 'IND');
-- 选取 name 以介于 'A' 和 'H' 之间字母开始的所有网站(带有文本值的 BETWEEN 操作符)
SELECT * FROM Websites
WHERE name BETWEEN 'A' AND 'H';
-- SQL 语句选取 date 介于 '2016-05-10' 和 '2016-05-14' 之间的所有访问记录：
SELECT * FROM access_log
WHERE date BETWEEN '2016-05-10' AND '2016-05-14';
-- MySQL用户的修改时间范围
SELECT User,password_last_changed FROM user
WHERE password_last_changed
BETWEEN ' 2020-01-03 09:22:32' AND ' 2020-01-03 09:22:38';
注意事项:
- 注意在不同的数据库中，BETWEEN 操作符会产生不同的结果！
- 某些数据库中BETWEEN 选取介于两个值之间（
但不包括两个测试值的字段 ，且包括两个测试值的字段，且包括第一个测试值但不包括最后一个测试值的字段）
- 某些数据库中BETWEEN 选取介于两个值之间（
AS 别名
描述:通过使用 SQL，可以为表名称或列名称指定别名(创建别名是为了让列名称的可读性更强)。
在下面的情况下使用别名很有用：
- 在查询中涉及超过一个表
- 在查询中使用了函数
- 列名称很长或者可读性差
- 需要把两个列或者多个列结合在一起
基础语法:1
2
3
4
5
6
7-- 列的 SQL 别名语法
SELECT column_name AS alias_name
FROM table_name;
-- 表的 SQL 别名语法
SELECT column_name(s)
FROM table_name AS alias_name;
基础演示: WeiyiGeek.AS别名字段1
2
3
4-- 字段别名的示例
SELECT xh AS '学号',kcm AS '课程', grade AS '成绩'
FROM user_kc
WHERE grade > 70;
1 | -- 表的别名实例 |
WeiyiGeek.
1 | -- 字段拼接（这里采用CONCAT函数后面会深入讲解） |
WeiyiGeek.
JOIN 连接
描述:SQL join 用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。。
下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。
- 1.A : 属于集合A的所有的元素的集合
2.CuB U A :属于集合A的元素同时满足不属于集合B的元素
3.交集：以属于A且属于B的元素为元素的集合称为A与B的交（集），记作A∩B（或B∩A），读作“A交B”（或“B交A”），即
A∩B={x|x∈A,且x∈B}4.并集：以属于A或属于B的元素为元素的集合称为A与B的并（集），记作AUB（或BUA）读作“A并B”（或“B并A”），即
A∪B={x|x∈A,或x∈B}。5.补集：属于全集U不属于集合A的元素组成的集合称为集合A的补集，记作CuA，即CuA={x|x∈U,且x不属于A},Cu(A∩B)
6.B :属于集合B的所有的元素的集合
- 7.CuA U B : 属于集合B的元素同时满足不属于集合A的元素
WeiyiGeek.
不同的 SQL JOIN 分类:
- INNER JOIN (内连接)：如果表中有至少一个匹配，则返回行
- OUTER JOIN (外连接):
- LEFT JOIN(左连接)：即使右表中没有匹配，也从左表返回所有的行
- RIGHT JOIN(右连接)：即使左表中没有匹配，也从右表返回所有的行
- FULL JOIN(全连接)：只要其中一个表中存在匹配，则返回行
INNER JOIN（内连接-笛卡尔积）
描述:SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行,在表中存在至少一个匹配时返回行。1
2
3
4
5
6
7
8
9
10
11
12-- 在表中存在至少一个匹配时返回行。
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
-- 或:(INNER JOIN 与 JOIN 是相同的)
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
基础示例:1
2
3
4
5-- 显示满足ON条件的行，否则以左边基准表 + user_kc 每一条
SELECT u.xh,u.xm,kc.kcm,kc.grade
FROM user AS u
INNER JOIN user_kc AS kc ON u.xh = kc.xh
ORDER BY u.xh
WeiyiGeek.
LEFT JOIN 关键字
描述:LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
SQL LEFT JOIN 语法1
2
3
4
5
6
7
8
9
10SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
或：
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
基础实例：1
2
3
4
5
6
7-- LEFT JOIN 关键字演示
INSERT INTO user VALUES (1006,'WeiyiGeek','2019',21,'网络安全工程');
SELECT u.xh,u.xm,kc.kcm,kc.grade
FROM user AS u
LEFT JOIN user_kc AS kc ON u.xh = kc.xh
ORDER BY u.xh
WeiyiGeek.
RIGHT JOIN 关键字
描述:RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。实际就是与LEFT相似只不过是左边关联不上的为NULL
SQL RIGHT JOIN 语法:1
2
3
4
5
6
7
8
9
10
11SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
-- 或
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;
基础实例: WeiyiGeek.1
2
3
4
5-- 这次将user_kc作为左表,而user作为右表
SELECT u.xh,u.xm,kc.kcm,kc.grade
FROM user_kc AS kc -- 注意此处与上面是不同的
RIGHT JOIN user AS u ON u.xh = kc.xh
ORDER BY u.xh
FULL OUTER JOIN 关键字
描述:FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行 它结合了 LEFT JOIN 和 RIGHT JOIN 的结果即 A 并 B。
FULL OUTER JOIN 语法:1
2
3
4SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
基础实例:1
2
3
4
5
6#MySQL中不支持 FULL OUTER JOIN，你可以在 SQL Server 测试以下实例。
SELECT Websites.name, access_log.count, access_log.date
FROM Websites
FULL OUTER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;
总结:
- 1.首先连接的结果可以在逻辑上看作是由SELECT语句指定的列组成的新表。左连接与右连接的左右指的是以两张表中的哪一张为基准它们都是外连接。
- 2.外连接就好像是为非基准表添加了一行全为空值的万能行，用来与基准表中找不到匹配的行进行匹配,两个没有空值的表进行左连接，
左表是基准表，左表的所有行都出现在结果中，右表则可能因为无法与基准表匹配而出现是空值的字段。 - 3.JOIN 分类得到的结果数：
1
2
3inner join <= min(left join, right join)
full join >= max(left join, right join)
当 inner join < min(left join, right join) 时 full join > max(left join, right join) - 4.在使用 join 时，on 和 where 条件的区别如下:
1
2- on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真都会返回左边表中的记录。
- where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。 - 5.MySQL中不支持 FULL OUTER JOIN，你可以在 SQL Server 测试以下实例。
UNION 操作符
描述:SQL UNION 操作符合并两个或多个 SELECT 语句的结果。
1 | -- SQL UNION 语法 |
基础实例: WeiyiGeek.1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19-- 从 "Websites" 和 "apps" 表中选取所有不同的country（只有不同的值）：
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;
-- 使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的中国(CN)的数据（也有重复的值）：
SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;
-- union 与 union all 在于前者可以剔除重复的字段的行，后者则是显示所有不管是否重复
SELECT xh,'#',xm FROM user
UNION ALL
SELECT xh,kcm,'-' FROM user_kc
ORDER BY xh
注意事项：
- 注:UNION 内部的每个 SELECT 语句必须拥有相同数量的列
- 注:列也必须拥有相似的数据类型，同时每个 SELECT 语句中的列的顺序必须相同。
- 注:UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
GROUP BY 语句
描述：GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
基础语法:
1 | -- SQL GROUP BY 语法 |
基础应用:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22-- 统计 access_log 各个 site_id 的访问量
SELECT site_id, SUM(counts) AS nums FROM access_log GROUP BY site_id;
-- SQL GROUP BY 多表连接统计有记录的网站的记录数量：
SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites -- 关键点-表字段结合
ON access_log.site_id=Websites.id
GROUP BY Websites.name; -- 关键点
-- 基础实例
SELECT Aggregate.NAME,COUNT(Persons.ID) AS 'NUMS'
FROM Persons
LEFT JOIN Aggregate
ON Aggregate.ID = Persons.ID
GROUP BY Aggregate.NAME;
SELECT Aggregate.NAME,COUNT(Persons.ID) AS 'NUMS'
FROM Persons
LEFT JOIN Aggregate
ON Aggregate.NAME = Persons.LastName
GROUP BY Aggregate.NAME;
WeiyiGeek.
HAVING 语句
描述:HAVING 子句可以让我们筛选分组后的各组数据; 在 SQL 中增加 HAVING 子句原因是 WHERE 关键字无法与聚合函数一起使用。
1 | -- SQL HAVING 语法 |
基础示例： WeiyiGeek.HAVING1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16-- 查找总访问量大于 200 的网站，并且 alexa 排名小于 200。
SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
WHERE Websites.alexa < 200
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;
-- 过滤掉分组结果为NULL的rows
SELECT Aggregate.NAME,COUNT(Persons.ID) AS 'NUMS'
FROM Persons
LEFT JOIN Aggregate
ON Aggregate.NAME = Persons.LastName
GROUP BY Aggregate.NAME
HAVING Aggregate.NAME IS NOT NULL;
SELECT INTO 语句
描述:SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中,但是需要注意 MySQL 数据库不支持 SELECT ... INTO 语句，但支持 INSERT INTO ... SELECT , 当然你可以使用以下语句来拷贝表结构及数据(后面讲解CREATE会讲)：1
2
3CREATE TABLE 新表
AS
SELECT * FROM 旧表
SELECT INTO 语法：1
2
3
4
5
6
7
8
9-- 复制所有的列插入到新表中：
SELECT *
INTO newtable [IN externaldb]
FROM table1;
-- 只复制希望的列插入到新表中：
SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;
基础实例:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18-- 创建 Websites 的备份复件,只复制中国的网站插入到新表中：
SELECT *
INTO WebsitesBackup2016
FROM Websites;
WHERE country='CN';
-- 复制多个表中的数据插入到新表中：
SELECT Websites.name, access_log.count, access_log.date
INTO WebsitesBackup2016
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id;
-- SELECT INTO 语句可用于通过另一种模式创建一个新的空表。
SELECT *
INTO newtable
FROM table1
WHERE 1=0; -- 只需要添加促使查询没有数据返回的 WHERE 子句即可
注意事项:
- 注:新表将会使用 SELECT 语句中定义的列名称和类型进行创建。但是可以使用 AS 子句来应用新名称。
INSERT INTO SELECT 语句
描述:INSERT INTO SELECT 语句从一个表复制数据，目标表中任何已存在的行都不会受影响。然后把数据插入到一个已存在的表中(非常注意不同于SELECT INTO语句转存的表必须存在，而且字段类型一致)。
SQL INSERT INTO SELECT 语法：1
2
3
4
5
6
7
8-- 从一个表中复制所有的列插入到另一个已存在的表中：
INSERT INTO table2
SELECT * FROM table1;
-- 只复制希望的列插入到另一个已存在的表中：
INSERT INTO table2 (column_name(s))
SELECT column_name(s)
FROM table1;
基础实例:1
2
3-- 复制 "apps" 中的数据插入到 "Websites" 中：
INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps;
select into from 和 insert into select 两则的区别？
- 相同点都是用来复制表，不同点前者MySQL数据库不支持，而后者语句语法支持 以及 复制的时候是否要目标表存在；
注意事项:
- 注:
select into [newTable] from：要求目标表不存在，因为在插入时会自动创建；将查询出来的数据整理到一张新表中保存，表结构与查询结构一致。 - 注:
insert into [newTable] select from：要求目标表存在，为已经存在的表批量添加新数据。
1 | -- 即，查询出来结果--->复制一张同结构的空表--->将数据拷贝进去。 |
