SQL语言种类DCL、DDL、DML、TCL-SQL语言开发看这一篇就够了

初始SQL

SQL语言呢是一种描述性的语言，和我们所熟悉的Java、php、python等程序语言一样，也是一种编程语言。只不过呢SQL是为了操作关系型数据库而开发的一种语言。

• 一种描述性语言
  之所以说其是一种描述性语言，是因为其是面向语义的。比如说我们要从某一个表中取出符合某种条件的数据，那么根据这句话，我们就可以写出相应的SQL语句。所以说，每个SQL语言都很容易理解。学习它的难度要比其他语言低很多。

• SQL语言的作用：对存储在RDBMS中的数据进行增删改查等操作
  所谓的关系型数据库呢，就是我们之前所提到的像MySQL、SQL Server、Oracle呀，这样的数据库。当然前面也提到了现在有一些这非关系型数据库，也已经开始支持SQL语言来对存储其中的数据进行操作了。比如Hive，就可以呢支持使用SQL语言来查询存储在Hadoop中的数据，可见呢SQL语言不但学习难度低，而且引用范围也是越来越广，实在是每个程序员所必备的一种语言。

• 常用的SQL语言种类：DCL、DDL、DML、TCL
  DCL：数据库管理语句，用于管理数据库访问的。比如授权语句、用户建立语句等
  DDL：数据定义类语句的简称，主要用于建立数据库对象的，比如建立表的语句，修改表结构的语句等等
  DML：数据操作语句的一种简称，也是我们平时使用最多的一种语言，从其名字中大家就可以知道DML主要用于操作存储在数据库中的数据，也就是之前所说的执行这个增删改查，这些任务的语句全都属于DML类语句
  TCL：这是一种事务控制类语句的简称，主要的作用是实现对事物的控制，比如开启事务、提交事务、回滚事务等等，这些都是属于TCL类语句。

DCL（Data Control Language)

如果我们要访问数据库呢，就必须要有相应的数据账号，大家可能已经注意到了，我们之前呢，在演示中使用的都是root账号，也就是MySQL的特权账号，这在生产环境中是非常不安全的，所以我们在项目开始，实际使用到数据库前呢，所要做的第一件事，就是建立程序所使用的数据库访问账号，并对这个账号赋予相应的权限。
前面我们说过，这类操作呢是通过DCL语言来实现的，常用的DCL主要有三个

• 建立数据库账号：create user
  我们需要使用这个语句来建立我们需要使用的数据库账号，并且指定这个账号的密码。

接下来建立好账号后，我们还要对这个账号进行授权

• 对用户授权：grant
  至于可以为数据库账号授予那些权限，我们下面再来讨论

最后，如果我们发现有的账号具有并不是很需要的权限，超过了它使用范围的权限，就可以使用

• 收回用户权限：revoke
  把那些不需要的权限收回

任务一：建立程序使用的数据库账号

下面是create user 命令一个简单的语法

CREATE USER [ IF NOT EXISTS]
		user [auth_option] [,user [auth_option]] ...
		DEFAULT ROLE role [, role ] ...
		[REQUIRE {NONE | tls_option [[AND] tls_option]
		...}]
		[WITH resource_option [resource_option] ...]
		[password_option | lock_option] ...

有中括号的这些参数呢，就是可选的参数；而没有中括号就是呢必须使用的

user [auth_option]

用户名@访问控制列表
上面的user就是我们所要建立的账号名，这里要注意MySQL的用户名是由两部分组成的，第一部分是用户名，第二部分是访问控制列表，第一部分和第二部分之间是用@符号来进行分割的。访问控制列表决定了我们可以从那些客户端进行访问。大家是不是还记得在之前访问MySQL章节的最后呀，我们解决了MySQL无法访问的故障，其中有一点造成MySQL无法访问的原因呢，就是在访问控制列表中并没有包括执行MySQL连接请求的客户端的IP，如果我们在建立账号时候没有指定访问控制列表，默认的情况下呢，是在除了本地服务器之外的任何地方都可以使用这个账号来访问MySQL，这是极其不安全的一种方法。以我们这个项目来说，如果我们要建立一个账号，可以使用命令

CREATE USER mc_class@'192.168.1.%'
	IDENTIFIED WITH 'mysql_native_password' by '1234567';

上面的mc_class就是用户名，**192.168.1.%就是访问控制列表，%**是一个通配符，表示在这个网段下的所有主机都可以访问我们的数据库服务器。IDENTIFIED WITH用于指定MySQL密码所使用的加密方式的，以及mc_class账号的密码。

可以使用help命令来查看我们使用语句的帮助信息

mysql>help create user

Name: 'CREATE USER'
Description:
Syntax:
CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

user:
    (see )

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'hash_string'
}

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
  | PASSWORD HISTORY {DEFAULT | N}
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

The CREATE USER statement creates new MySQL accounts. It enables
authentication, role, SSL/TLS, resource-limit, and password-management
properties to be established for new accounts. It also controls whether
accounts are initially locked or unlocked.

To use CREATE USER, you must have the global CREATE USER privilege, or
the INSERT privilege for the mysql system database. When the read_only
system variable is enabled, CREATE USER additionally requires the
CONNECTION_ADMIN or SUPER privilege.

CREATE USER either succeeds for all named users or rolls back and has
no effect if any error occurs. By default, an error occurs if you try
to create a user that already exists. If the IF NOT EXISTS clause is
given, the statement produces a warning for each named user that
already exists, rather than an error.

URL: http://dev.mysql.com/doc/refman/8.0/en/create-user.html

** DEFAULT ROLE role **可以用于指定角色，角色呢实际上就是特殊的用户了，可以通过角色来管理用户的权限，具有相同角色的用户呢是具有相同的权限的。
**REQUIRE {NONE | tls_option}]**可用于对资源的限制，资源的限制呢可以通过下面的

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

资源限制	说明
MAX_QUERIES_PER_HOUR	每小时可使用查询总数
MAX_UPDATES_PER_HOUR	每小时所更新的最大数量
MAX_CONNECTIONS_PER_HOUR	每小时所连接的数量
MAX_USER_CONNECTIONS	同时可连接的用户量是多少

下面的代码可以指定账号所使用的认证方式

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'hash_string'
}

MySQL支持那些认证方式呢？我们可以使用show plugins进行查看

mysql> show plugins;
+---------------------------------+----------+--------------------+---------+---------+
| Name                            | Status   | Type               | Library | License |
+---------------------------------+----------+--------------------+---------+---------+
| mysql_native_password           | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password                 | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| caching_sha2_password           | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |

可以看到MySQL共有3个插件

认证插件名	描述
mysql_native_password	是MySQL5.7(包括）版本之前所使用的默认认证插件
caching_sha2_password	是MySQL8.0默认所使用的
sha256_password

如果我们需要使用诸如3M呀、MHA这样的高可用方式了，建议大家使用 mysql_native_password 认证插件来建立用户。如果没有这个需要，并且使用的是最新版的，支持MySQL8.0的程序的驱动的话，就可以使用caching_sha2_password,好处是它使用了缓存，可以加快认证的速度。

如果我们需要使用SSL这样的加密连接的话，还可以使用一些加密连接相关的一些参数

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

建立一个测试账号

CREATE USER mc_class@'192.168.1.%' 
	IDENTIFIED  BY '123456' WITH MAX_USER_CONNECTIONS 1;

受影响的行: 0 时间: 0.017s

这就代表成功了

password_option: {
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
  | PASSWORD HISTORY {DEFAULT | N}
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
}

上面代码可以指定密码过期时间，历史密码的重复使用次数等

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

上面代码可以对一个账号进行锁定或者解锁。

任务二：给账号授权

• MySQL常用权限

权限名称	说明
Insert	向表中插入数据的权限
Delete	删除表中数据的权限
update	修改表中数据的权限
Select	查询表中数据的权限
Execute	智行存储过程的权限

授权语句：↓

GRANT
	priv_type [(column_list)]
		[,priv_type[(column_list)]] ...
	ON [object_type] priv_level
	TO user_or_role [,user_or_ole] ...	
	[WITH GRANT OPTION]

1. GRANT后： 权限列表
2. ON后： 对那些对象进行授权，对象可以是一个库、表、存储过程、函数，如果是表中列，就要在上面权限列表中的（column_list）来进行制定
3. TO后： 把权限授予指定的用户或角色

查看MySQL支持的全部权限列表：↓

show privileges

在这里插入代码片

-- 给用户mc_class授予mysql.user表上的user和host列的查询权限
GRANT select(user,host) on mysql.user to mc_class@'localhost';
-- 给用户mc_class授予mysql.user表的所有列的查询权限；没有给出列列表是查询所有列
GRANT select  on mysql.user to mc_class@'localhost';
-- 给用户  授予一个库所有表的查询权限
GRANT select ON mysql.* TO mc_class@'localhost';

授权注意事项

• 使用GRANT授权的数据库账号必需存在
• 用户使用grant命令授权必须具有grant option权限
• 只能授予别的用户自己有的权限
• 获取命令帮助 help grant

任务三：回收用户权限

REVOKE
	priv_type [(column_list)]
		[,priv_type[(column_list)]] ...
	ON [object_type[ priv_level	
	FROM user_or_role	[, user_or_role] ...

例如：↓

REVOKE DELETE,INSERT,UPDATE ON mysql.*
FRom mc_class@'localhost';

DDL(Data Definition language)

DDL就是数据库定义语言的简称，用于定义数据的各种对象，比如数据库的表、库、视图等等。常用的数据库语言有以下几种：

DDL 分类

建立/修改/删除数据库：create/alter/drop database

在建立数据库对象前，要先建立书库，其他的对象像表、视图等都在某一个数据库中的。可以使用create database语句来创建一个数据库。如果要对一个数据库进行修改的话，可以使用alter database ，但是要注意修改的只是数据库对象的本身，并不会对数据库中存在的其它对象造成影响。而要删除数据库呢，则要使用drop database语句，要特别注意的是执行时候，要特别小心，因为drop database除了会删除数据库之外呢，还会删除存储在数据库内的所有的对象和数据，并且删除后除了使用之前的备份恢复外，别无它法。所以只有在你已经十分的确认数据库中的数据不再需要，并且也已经进行过完整的备份之后，才可以执行drop database语句来删除数据库。

那么建立好数据库之后呢，我们就可以在数据库中建立我们所需要的表了。

建立/修改/删除表：create/alter/drop table

我们可以使用create table 语句来创建表，同样我们如果对一个表进行修改呢，可以使用alter table语句，是对表的结构来进行修改，当然在一些时候呀也会影响到表中的数据，特别是在对枚举类型的枚举值在进行修改的时候，就有可能会影响到原来表中已经存在的数据，这一点大家要特别的注意。如果我们要删除一个表的话就可以使用drop table 命令，同样呢，drop table命令会同时删除表中的所有数据，并且呢也只能通过之前的备份来进行恢复，所以在执行drop table前也需要非常的谨慎。

建立好表之后，为了提高数据的查询性能，还需要根据所使用的SQL在表上呢建立一些索引，创建索引使用的命令是

建立/删除索引：create/drop index

索引呢并不是越多越好，过多的索引不但不能提升数据的查询性能，反而影响数据的查询效率，所以呢，我们不但要建立索引，也同样需要删除一些不必要的索引，对于没用的索引可以使用drop index命令来进行删除，删除索引并不会对表中已存在的数据造成影响

除了建立和删除表之外呢，还可以清空表

清空表：truncate table

为什么清空表不叫数据管理命令，而叫数据定义命令呢？实际上truncate table做的操作呢，实际上就相当于drop table create table，也就是先删除表再创建表，所以truncate table命令实际上就是DDL命令，其所实现的功能呢就是删除表中的所有数据并保留这个表的结构，但是并不会记录数据的删除日志，所以也无法通过日志对已删除的数据进行恢复。

另一个常见的DDL操作呢，就是对表重命名

重命名表：rename table

可以对表进行重命名，还可以利用其在同一个数据库的不同实例之间移动表，rename table命令并不会影响表中的数据

视图呢，也是我们很常用的一个数据库对象，我们可以利用视图来简化查询，在MySQL中要创建视图，我们可以使用

建立/修改/删除视图：create/alter/drop view

任务一：建立数据库imc_db

先来看下创建数据库的语法：↓

CREATE {DATABASE | SCHEMA } [IF NOT EXISTS] db_name 
	[specification] ...
ALTER  {DATABASE | SCHEMA } [IF NOT EXISTS] db_name 
	[specification] ...
DROP {DATABASE | SCHEMA }  [IF EXISTS]  db_name 

specification:
		[DEFAULT] CHARACTER SET [=] charset_name
	|	[DEFAULT] COLLATE [=] collation_name

创建数据库

1. 数据库的名称要见名知意，不能使用关键字
2. 需要指定字符集和字符编码，如果不指定就会使用系统默认的字符集和排序规则

修改数据库

1. MySQL目前并不支持修改数据库的名字
2. 只能修改数据库的编码和字符集这两项

删除数据库
1 删除之后不能恢复，只能通过备份

create  DATABASE imc_db;

任务二：建立数据库imc_db中的表

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    
create_definition:
    col_name column_definition
 
...其它使用help命令查询

TEMPORARY：临时表，是当前线程创建的表，线程结束也会删除，我们并不需要指定这个关键字

修改表语句

当我们建立好表后，由于业务的变化，我们不免的需要对一个已经存在的表进行修改，比如经常会根据业务需求增加表中的列或修改表中列的定义。
语法：↓

ALTER  TABLE  tbl_name
	[alter_specification [, alter_specification] ....]
alter_specification:
	| ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
	| ALTER  [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT }
	| CHANGE [COLUMN] old_col_name new_col_name column_definition
		[FIRST | AFTER col_name]
	| MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
	| DROP [COLUMN] col_name
	| RENAME COLUMN old_col_name TO new_col_name

1. ADD:: 增加列，列的名称、数据类型、在表中的位置
2. ALTER 修改列的默认值
3. CHANGE 修改列的默认值时再修改列的定义
4. MODIFY 不修改列的默认值情况下修改列的定义、位置
5. DROP删除表中不需要的列，删除列的时候数据也会被删除，需要做下备份
6. RENAME 只修改列的名称

删除表语句

语法：↓

DROP [TEMPORARY] TABLE [IF EXISTS]	
	tb1_name [, tb1_name] ...

删除表可以一次删除多个，使用“，”分割。删除会同时删除表的结构和数据，除非你确认表中的数据不再需要或者已经进行备份的前提下，否则不要轻易尝试删除一个表。

任务三：维护表中的索引

语法：↓

CREATE 	[UNIQUE] INDEX index_name
	ON 	tb1_name (index_col_name, ...)

index_col_name:
	col_name [(length)] [ASC | DESC]

删除索引：↓

DROP INDEX index_name ON tb1_name

值得一提的是，无论是CREATE INDEX 还是DROP INDEX最终都会转换为alter语句。删除索引并不会对表中的数据造成影响，但可能会影响sql的执行计划

任务四：清空表

语句：↓

TRUNCATE TABLE imc_note;

• 优点：比delete全表数据要快很多，并且不会生成大量二进制日志
• 缺点：因为不记二进制日志，所以我们无法通过对二进制日志的反解析对它进行恢复

任务五：重命名表

RENAME TABLE imc_note TO bak_imc_note;

不会影响表中的数据，只是修改表的名称

DML(Data Manipulation language)

DML就是数据操作语句的英文简称。所谓的数据操作语句的作用，从名称中我们就可以知道一二了， DML语句作用就是用于操作存储在数据库中的数据。比如我们希望向表中添加、修改、删除数据，这样的SQL语句就都是DML语句:

• 新增表中的数据：insert into
• 删除表中的数据：delete
• 修改表中的数据：update
• 查询表中的数据：select

任务一：向表中写入数据

INSERT 
    [INTO] tbl_name     [(col_name [, col_name] ...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ON DUPLICATE KEY UPDATE 
    	col_name=expr
    	[,col_name=expr] ... ]

❕往具有主键和唯一索引的表中插入数据时，如果出现主键或唯一索引冲突，插入的操作可能就会被回滚，为了避免这种情况发生，可以在insert语句中指定当出现唯一索引冲突时候的操作行为，比如我们可以设置当出现唯一索引冲突时则更新表中的某几列的值为新的插入数据的值
例子：?

class_id	class_name	add_time
1	MySQL	2019-08-26 20:07:32
2	Redis	2019-08-26 20:07:32
3	MongoDB	2019-08-26 20:07:32

插入一条

INSERT INTO imc_class (class_name)
VALUES('MySQL')
ON DUPLICATE KEY UPDATE
	col_name=expr
	[col_name=expr]...]

结果将变成下面情况：?
MySQL的没有改变，唯一索引值以外的add_time变成了当前时间

class_id	class_name	add_time
1	MySQL	2019-08-26 20:12:32
2	Redis	2019-08-26 20:07:32
3	MongoDB	2019-08-26 20:07:32

编写INSERT语句的思路

• 确认要把数据插入到那个表

• 确认表的数据库结构，那些列不能为null，那些列可以为NULL，对于不能为NULL的列是否有默认值

  查看表的建表语句：↓

SHOW CREATE TABLE tb_name

• 确认对应插入列的插入值的清单

任务二：查询表中的数据

语法：?

SELECT
	[ALL | DISTINCT | DISTINCTROW ]
	select_expr [,select_expr  ... ]
	[FROM table_references
	[WHERE where_condition]
	[GROUP BY {col_name | expr | position}]
	[HAVING where_condition]
	[ORDER BY {col_name | expr | position}
		[ASC | DESC] ,  ... ]
	[LIMIT {[offset,] row_count | row_count OFFSET offset}]

看到组成select语句的关键字一共3部分，一个就是select语句本身，也可以说select关键字是select语句中唯一必选的一个关键字，select关键字之后跟着的则是我们希望在表中取得的列，这个列可以是表中本身所存在的列，也可以是几个列所结合后产生的列，或者干脆就是一个计算的表达式，比如我们可以在select语句中这样执行，?

SELECT ‘HELLO' ,'MySQL',2018+1

Hello	MySQL	2018+1
Hello	MySQL	2019

需求：查询出课程名称中包含MYSQL的课程的课程名称

SELECT title 
FROM imc_course
WHERE title like '%MYSQL%'

结果：↓

title
MySQL课程-79889
MySQL课程-69546
MySQL课程-01234
MySQL课程-56789

编写查询语句的思路

• 首先确定我们要获取的数据存在哪些表中 确定FROM子句
• 其次是确定我们要取表中的哪些列 确定SELECT子句
• 确认是否需要对表中的数据进行过滤 确定WHERE子句

MySQL的比较运算符

比较运算符	说明
= > < >= <= <> !=	<>和!=都表示不等于
BETWEEN min AND max	列的值大于等于最小值，小于等于最大值
IS NULL 、 IS NOT NULL	判断列的值是否为空，上面的比较运算符不能判断为null的值
LIKE 、 NOT LIKE	%代表任何数量的字符 _代表任何一个字符
IN 、NOT IN	判断列的值是否在指定范围内

>,需求：学习人数大于1000人的课程有那些？并列出他们的课程标题和学习人数**

SELECT title,study_cnt
FROM imc_course
WHERE study_cnt>1000

结果：↓

title	study_cnt
MySQL课程-79889	3503
MySQL课程-69546	3921

BETWEEN AND ,需求：学习人数大于等于1000人小于等于2000人的课程有那些？并列出他们的课程标题和学习人数

SELECT title,study_cnt
FROM imc_course
WHERE study_cnt BETWEEN 1000 AND 2000

结果：↓

title	study_cnt
MySQL课程-84741	1334
Oracle课程-1498	1498

IS NULL，需求：查询列的值为null的或不为null的过滤条件

CREATE TABLE test_is (id INT, c1 VARCHAR(10),PRIMARY KEY (id));
INSERT INTO test_is VALUES (1,'aa'),(2,NULL),(3,'cc');

SELECT * FROM test_is WHERE c1 = NULL

结果：↓

id	c1

说明使用=比较运算符是查不到值为null的行，需要使用我们的IS NULL

SELECT * FROM test_is WHERE c1 = NULL

结果：↓

id	c1
2	(NULL)

LIKE，需求：使用LIKE运算符

SELECT 'this is mysql course' LIKE 'mysql'
 

结果：↓

SELECT ‘this is mysql course’ LIKE ‘mysql’
1

在mysql中返回1表示为true

逻辑运算符

逻辑运算符	说明
AND ， &&	AND运算符两边的结果都为真时，返回结果才为真
OR	OR 表达式两表表达式有一个为真，返回结果为真
XOR	XOR (异或）运算符两边的表达式一真一假时返回真，两真两假时返回假

AND ，需求：如何合并WHERE子句中的多个过滤条件

示例：查询出课程表中课程标题含有MySQL的并且学习人数大于5000人的课程标题

• title like ‘%mysql%’
• study_cnt >5000
  组合上面的2个过滤条件，就要使用MySQL的逻辑运算符了

select title,stduy_cnt
FROM  imc_ course
WHERE title LIKE '%mysql%'
AND study_cnt>5000 

XOR，需求：查询出课程标题中含有MySQL关键字并且学习人数小于5000，课程标题中不含MySQL关键字并且学习人数大于5000的课程，课程标题和学习人数

两种实现方式:

• 一种是分为两个SQL语句来写，最后把它们的结果合并到一起

SELECT title,study_cnt FROM imc_course
WHERE title LIKE '%mysql%' AND study_cnt <5000
UNION ALL
SELECT title,study_cnt FROM imc_course
WHERE title  NOT  LIKE '%mysql%' AND study_cnt >5000

• 一种是使用XOR逻辑运算符

SELECT title,study_cnt FROM imc_course
WHERE title LIKE '%mysql%' XOR study_cnt <5000

关联查询JOIN 关键字

内关联

INNER JOIN 示意图

外关联左连接

LEFT JOIN 示意图

外关联右连接

GROUP BY 分组查询

把结果列按某些列分成不同的组，并对分组后的数据进行聚合操作

SELECT level_name ,count(*)
FROM imc_course a 
JOIN imc_level b ON a.level_id=b.level
group by level_name

按照level_name进行分组，每个level_name的值在结果表中只会有一行数据，分组键除了在group by 子句出现，还会在select子句出现，在select子句除了分组键之外，还可以有一个或多个聚合函数。
注意，所有出现在select子句中的非聚合函数的列呀，都应该出现group by 子句之后，为什么这么说呢？因为MySQL在宽松的SQL_model下，是可以支持select子句中非聚合函数列同group by 子句中的列不同的情况，但是这种情况下查询的结果可能并不正确。

HAVING子句过滤分组结果

可以对聚合后的数据进行过滤

常用的聚合函数

聚合函数	说明
COUNT(*) / COUNT(col)	计算符合条件的行数
SUM(col_name)	计算表中符合条件的数值列的合计值，只能用在数值列
AVG (col_name)	计算表中符合条件的数值列的平均值，只能用在数值列
MAX(col_name)	计算表中符合条件的任意列的最大值
MIN(col_name)	计算表中符合条件的任意列的最小值

order by 子句对查询结构进行排序

• 使用Order by子句是对查询结果进行排序的最安全方法
• 列名后增加ASC关键字指定该列的升序进行排序，或是指定DESC关键字指定该列的降序进行排序
• order by 子句也可以使用select子句中未出现的列或是函数

limit 子句限制返回结构集的行数

• 常用于数据列表分页
• 一定要和order by 子句配合使用，由于limit子句每次获取不同的分页数据的时候，都会重复的来执行获取数据的查询语句，所以只是返回数据的起始行数不一样而已，所以为了保证每次获取的数据都是按照相同的顺序来进行排列的一定要配合order by子句使用
• limit 起始偏移量，结果集的行数
  示例：?

-- 分页返回课程Id和课程名称，每页返回10行记录
SELECT course_id,title
FROM imc_course
ORDER BY study_cnt DESC
LIMIT 10,10

创建视图

创建视图就是在AS关键字后面写一个SELECT语句，语法：?

CREATE VIEW view_name
AS
	SELECT
		[ALL | DISTINCT | DISTINCTROW ]
		select_expr [, select_expr ...]
		[FROM table_references
		[WHERE where_condition]

需求：定义一个包括课程Id、课程名称、课程分类、课程方向以及课程难度的视图

CREATE VIEW vm_course
AS
SELECTG a.course_id,a.title,b.class_name,c.type_name ,d.level_name
FROM imc_course a
JOIN imc_class b ON b.`class_id`=a.class_id
JOIN imc_type c ON c.type_id=a.type_id
JOIN imc_level d  ON d.level_id=a.level_id

接下来，可以查询视图就可以达到查询select语句的效果

SELECT * FROM vm_course

结果：?

course_id	title	class_name	type_name	level_name
1	MySQL课程	大数据	算法&数学	入门
2	PostgreSQL课程	大数据	前端开发	高级

任务三：按条件删除表中的数据

语法：?

DELETE 
	FROM table_name
		[WHERE where_confition]
		[ORDER BY ...]
		[LIMIT row_count]

编写DELETE语句的思路

• 确定要删除的数据存储在那张表中 FROM子句
• 确认删除数据的过滤条件 WHERE子句
• 确认是否只删除有限条数据 ORDER BY … LIMIT子句

需求：删除课程表中没有章节信息的课程

DELECT a 
FROM imc_course a
LEFT JOIN imc_chapter b ON a.course_id =b.course_id
WHERE 	b.course_id IS NULL

需求：删除课程方向表中重复的课程方向，保留方向ID最小的一条，并在方向名称上增加唯一索引

1. 先查询课程方向表中重复的课程方向

SELECT type_name,count(*)
FROM imc_type
GROUP BY type_name HAVING count(*)>1

结果：

type_name	count(*)
后端开发	2
前沿技术	2
数据库	2

2. 保留方向ID最小的一条，我们需要知道方向ID最小的ID

SELECT type_name,MIN(type_id) AS min_type_id, count(*)
FROM imc_type
GROUP BY type_name HAVING count(*)>1

结果：

type_name	min_type_id	count(*)
后端开发	2	2
前沿技术	5	2
数据库	8	2

3. 删除课程方向重复并且保留方向Id最小的一条

DELETE a
FROM imc_type a
JOIN (
		SELECT type_name,MIN(type_id) AS min_type_id, count(*)
		FROM imc_type
		GROUP BY type_name HAVING count(*)>1
	  ) b 
ON a.type_name=b.type_name AND a.type_id>min_type_id

4. 建立唯一索引

CREATE UNIQUE INDEX uqx_typename ON imc_type(type_name)

任务四：修改表中符合条件的数据

UPDATE table_name
SET col_name1{expr1 | DEFAULT}
	[,col_name2{expr2 | DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT ROW_count]

编写UPDATE语句的思路

• 确定要更新的数据存储在那张表中 UPDATE子句
• 确定要更新的新的列及值 SET子句
• 确认更新数据的条件 WHERE 子句

需求：冻结账号”沙占“的账号

UPDATE imc_user 
SET user_status=0
WHERE user_name ='沙占'

user_name	user_status
沙占	0

需求：随机推荐10门课程

课程的推荐是我们在工作中遇到的非常多的一个需求，比如商城的推荐。实际上用户的推荐是一个非常复杂的过程，需要大量的运算才能完成推荐结果的，在这里我们采用非常简单的方式来完成推荐结果

UPDATE  imc_course
SET is_recommand=1
ORDER BY RAND()
LIMIT 10

需求：利用课程评价表中的用户对课程的评分，来更新课程表中课程评分

1. 计算出每门课程的平均评分

SELECT course_id,
				,AVG(content_scroe) AS avg_content
				,AVG(level_score)   AS avg_level
				,AVG(logic_score)   AS avg_logic
				,AVG(score)         AS avg_score
FROM imc_classvalue
GROUP BY course_id

2. 将第一步的查询当成子查询来使用

UPDATE imc_course a
JOIN
(	SELECT course_id,
				,AVG(content_scroe) AS avg_content
				,AVG(level_score)   AS avg_level
				,AVG(logic_score)   AS avg_logic
				,AVG(score)         AS avg_score
	FROM imc_classvalue
	GROUP BY course_id) b 
ON a.course_id=b.course_id
SET a.content_score=b.avg_content
	,a.level_score=b.avg_level
	,a.logic_score=b.avg_logic
	,a.score=b.avg_score

系统函数

使用系统函数可以大大提高我们的工作，使得原来需要大量sQL来完成的工作，使用系统函数就可以完成。

常用的时间函数

函数名	说明
CURDATE ()/CURTIME()	返回当前日期/时间
NOW()	返回当前的日期和时间
DATE_FORMAT(date_fmt,fmt)	按照fmt的格式对日期进行格式化
SEC_TO_TIME(seconds)	把秒数转换为（小时:分:秒)
TIME_TO_SEC(time)	把时间(小时:分:秒)转换为秒数
DATEDIFF(date1,date2)	返回date1和date2两个日期相差的天数
DATE_ADD(date,INTERVAL expr unit)	对给定的日期增加或减少指定的时间单元（unit:DAY天/HOUR小时/MINUTE分钟/SECOND秒）
EXTRACT(unit FROM date)	返回日期date的指定部分
UNIX_TIMESTAMP()	返回unix 时间戳(1970年1月1号到当前时间经过的秒数）
FROM_UNIXTIME()	把unix时间戳转化为日期时间

> fmt
%Y:四位的年       %m:月份(00，12）        %d:天(00,31)
%H:小时(00,24)    %i:分钟(00,59)         %s:秒(00,59)

三个函数的差别

SELECT CURDATE(),CURTIME(),NOW()

差别结果：?

curdate()	curtime()	now()
2019-03-02	03:23:01	2019-03-02 03:23:21

SEC_TO_TIME、SEC_TO_TIME的区别

SELECT SEC_TO_TIME(60),TIME_TO_SEC('1:00:00')

sec_to_time	time_to_sec
00:01:00	3600

DATEDIFF ，需求:计算每门课程上线时间距当前时间的天数

SELECT title,DATEDIFF(NOW(),online_time)
FROM imc_course

DATE_ADD示例

SELECT NOW(),
	  ,DATE_ADD(NOW(),INTERVAL  1  DAY) --当前时间加1天
	  ,DATE_ADD(NOW(),INTERVAL  -1  DAY) --当前时间减1天
	  ,DATE_ADD(NOW(),INTERVAL  '-1:30' HOUR_MINUTE) --当前时间减去1:30

EXTRACT示例

SELECT  NOW()
	   ,EXTRACT(YEAR FROM NOW()) --提取当前时间的年份
	   ,EXTRACT(MONTH FROM NOW()) --提取出月份
	   ,EXTRACT(DAY FROM NOW())  --提取日期

now()	ex…	ex…	extract(…
2019-03-02 03:45:35	2019	3	2

UNIX_TIMESTAMP、FROM_UNIXTIME

以前开发人员喜欢把日期使用unix时间戳函数来使用，因为大家认为是整数类型来保存日期数据的，使用起来回更好。
其实，现在看来也存在一些问题，因为unix时间戳在使用时候必须经过函数转换才能显示成为日期的形式，MySQL给我门提供了时间戳转换为日期的函数。不过现在已经不建议使用unix时间戳来保存数据了，这两个函数也就不常用了

常用的字符串函数

函数名	说明
CONCAT(str1,str2,…)	把字符串str1、str2连接为一个字符串
CONCAT_WS(sep,str1,str2,…)	用指定的分隔符连接字符串
CHAR_LENGTH(str)	返回字符串的字符个数
LENGTH(str)	返回字符串str的字节个数
FORMAT(N,D[,locale])	将数字N格式化为格式locale，如”#,###,###.##“，并舍入到D位小数
LEFT(str,len)/RIGHT(str,len)	从字符串的左/右边起返回指定len长度的子字符串
SUBSTRING(str,pos,[len])	从字符串str的pos位置起返回长度为len的子串
SUBSTRING_INDEX(str,delim,count)	返回字符串str按delim分割的前count个子字符串
LOCATE(substr,str)	在字符串str中返回子串substr第一次出现的位置
TRIM( [remstr FROM ] str)	从字符串两端删除不需要的字符remstr

FORMAT示例

SELECT FORMAT(123456.789,4) 

结果是：123，456.7890

LEFT、RIGHT示例

SELECT LEFT('www.imooc.com',3)
	  ,RIGHT('www.imooc.com'3)

left(…	right(…
www	com

SUBSTRING示例

1	2	3	4	5	6	7	8	9	10	11	12	13
w	w	w	.	i	m	o	o	c	.	c	o	m

SELECT SUBSTRING('wwww.imooc.com',5)

结果：↓

SUB…
imooc.com

SUBSTRING_INDEX示例

SELECT SUBSTRING_INDEX('192.168.0.100‘, '.' , -2)  --从右边起是-

结果：↓

substring_index('192.168.0.100‘, ‘.’ , -2)
0.100

LOCATE示例

通常和SUBSTRING函数一起使用
需求：截取课程标题中横线之前的部分

SELECT  'MySQL课程-04045'
	    ,LOCATE('-','MySQL课程-04045')
	    ,SUBSTRING('MySQL课程-04045',1,LOCATE('-','MySQL课程-04045')）
	    ,SUBSTRING('MySQL课程-04045',1,LOCATE('-','MySQL课程-04045')-1）
	    ,SUBSTRING_INDEX('MySQL课程-04045','-',1) --更为简单

查询结果：↓

MySQL课程-04045	8	MySQL课程-	MySQL课程	MySQL课程

TRIM示例

SELECT TRIM('   imooc   '),TRIM('x' FROM 'xxxxximoocxxxxx')

结果：↓

trim…	trim…
imooc	imooc

其它常用函数

函数名	说明
ROUND(x,d)	对数值x进行四舍五入保留d位小数
RAND()	返回一个在0和1间的随机数
CASE WHEN [condition] THEN result [WHEN[condition] THEN result…] [ELSE result] END	用于实现其它语言中的case when功能，提供数据流控制
MD5(str)	返回str的MD5值

RAND

使用rund（）函数对表中的数据进行随机排序，并不是一种推荐的做法，特别是对于存在大量数据的表，在使用RAND()函数进行随机排序时候性能会非常的差。

CASE WHEN

需求：显示每个用户的昵称和性别，再只显示男性

SELECT user_nick
	   ,CASE WHEN sex=1 THEN '男'
	   		 WHEN sex=0 THEN '女'
	   		 ELSE '未知'
	    END  AS '性别'
  FROM user
  WHERE CASE WHEN sex=1 THEN '男'
	   		 WHEN sex=0 THEN '女'
	   		 ELSE '未知'
	    END  ='男'

user_nick	性别
严州桥	男
侯路杰	男

公共表表达式CTE(Common Table Expressions)

在MySQL8.0中有了比子查询更好的一种选择，有如下特点：↓

• MySQL8.0版本之后才可以使用
• 功能类似于子查询，都可以在复杂的查询中定义一个临时表，这个表不用于存储数据，仅在查询执行期间有效，但同子查询不同的是由CTE定义的是一个命名临时表
• CTE生成的临时表可以在查询中多次引用，并且还可以实现自身的自引用
• 性能和可读性都要优于子查询

CTE基础语法

-- WITH开头
WITH [RECURSIVE]  -RECURSIVE,定义的时候指定了代表是可自引用的临时表，通常用于递归查询
-- 定义临时表的表名了，后面是可选列的列表，注意一定要和下面子查询的列是一一对应的
cte_name [(column_list)] AS (
 	query
)
-- 如果要在一个查询中用多个公共表表达式的话，还可以在下面继续定义其它的公共表表达式
[,cte_name [(column_list)] AS (
 	query
)]
--使用公共表表达式 
SELECT * FROM cte_name;

需求：CTE递归生成序列

WITH RECURSIVE cte_test AS  (
SELECT 1 AS n
UNION ALL 
SELECT 1+n FROM cte_test WHERE n<10 --n<10避免无限循环下去
)
SELECT * FROM cte_test

n
1
2
3
4
5
6
7
8
9
10

需求：递归查询课程的评论信息

WITH RECURSIVE replay(quest_id ,quest_title,user_id,replyid, path ) 
AS( 
-- 要想递归查询，先找到递归查询的根
SELECT quest_id ,quest_title,user_id,replyid,CAST(quest_id AS CHAR(200)) AS path 
FROM  imc_question WHERE course_id=59  AND replyid=0
-- 关联根的所有回复的信息
UNION ALL
SELECT a.quest_id ,a.quest_title,a.user_id,a.replyid,CONCAT(b.path,'>>', a.quest_id) AS path 
FROM imc_question a 
JOIN replay b ON a.replay_id=b.quest_id
)
-- 上面完成了公共表表达式的定义，下面来查询
SELECT * FROM replay

quest_id	quest_title	user_id	replyid	path
205	用户2169关于MySQL课程的评论	2169	0	205
2246	用户2991对于用户324关于MySQL课程的评论	2991	1471	1471>>2246
2320	用户2476对于用户2832对于MySQL课程的评论	2476	2257	1379>>2257>>2320
…	…	…	…	…

窗口函数

所谓的窗口函数实际上指的就是一组记录上执行的函数，符合条件的每一条记录都会分别执行窗口函数，因此窗口函数并不会改变记录集的行数。
使用语法

-- 窗口函数有些特有的系统函数，还有聚合函数也可以
function_name ([ exp])
-- OVER关键字是窗口函数的一个重点，指定了窗口的范围，上面的function_name就是在OVER指定的范围内计算的
OVER (
	-- PARTITION 用于查询返回的结果集进行分组的，分成不同大小的分组，窗口函数在不同的分组上执行
	[PARTITION BY exp [, ...]]
	-- ORDER BY 指按照那些字段进行排序，通过排序后的字段呢可以按照顺序进行编号，可以配合PARTITION 子句使用，也可以单独使用
	[ORDER BY exp [ASC | DESC ] [, ...]]
)

常用的窗口函数

函数名	说明
聚合函数	聚合函数都可以作为窗口函数使用
ROW_NUMBER()	返回窗口分区内数据的行号
RANK()	类似于row_number，只是对于相同的数据会产生重复的行号，之后的数据行号会产生间隔，例如：有两个并列第一，之后的数据会是第三名
DENSE_RANK()	类似于rank，区别在于当组内某行数据重复时，虽然行号会重复，但后续的行号不会产生间隔，例如：有两个数据是并列第一，下一个就是第二

示例

row_number、rank、dense_rank之间的区别

1. 原来结果集

WITH test (study_name,class_name,score) AS (
SELECT 'sqlercn','MySQL',95
UNION ALL
SELECT 'tom','MySQL',99
UNION ALL 
SELECT 'Jerry','MySQL',99
UNION ALL
SELECT 'Gavin','MySQL',98
UNION ALL
SELECT 'sqlercn','PostGreSQL',99
UNION ALL
SELECT 'tom','PostGreSQL',99
UNION ALL
SELECT 'Jerry','PostGreSQL',98
)
--使用公共表表达式
SELECT * 
FROM test

study_name	class_name	score
tom	MySQL	99
Jerry	MySQL	99
Gavin	MySQL	98
sqlercn	MySQL	95
tom	PostGreSQL	99
Jerry	PostGreSQL	98
sqlercn	PostGreSQL	99

2. 使用三个窗口函数

-- 第1步的窗口函数定义省略
SELECT  study_name,class_name,score
						--PARTITION 子句指定分组(窗口)  ORDER子句指定组内按照分数排序
	   ,ROW_NUMBER() OVER (PARTITION BY class_name ORDER BY score DESC) AS rw
	   ,RANK()       OVER (PARTITION BY class_name ORDER BY score DESC) AS rk
	   ,DENSE_RANK() OVER (PARTITION BY class_name ORDER BY score DESC) AS drk
FROM test 
ORDER BY class_name,rw

study_name	class_name	score	rw	rk	drk
tom	MySQL	99	1	1	1
Jerry	MySQL	99	2	1	1
Gavin	MySQL	98	3	3	2
sqlercn	MySQL	95	4	4	3
sqlercn	PostGreSQL	99	1	1	1
tom	PostGreSQL	99	2	1	1
Jerry	PostGreSQL	98	3	3	2

按学习人数对课程进行排名，并列出每类课程学习人数排名前3的课程名称、学习人数及名次

WITH tmp AS (
--查询每类课程下的课程根据分值排序的结果
SELECT class_name,title,score
      ,RANK() OVER(PARTITION BY class_name ORDER BY score DESC) AS cnt
FROM imc_course a 
JOIN imc_class  b ON b.class_id=a.class_id
)
-- 使用公共表表达式
SELECT * 
FROM tmp WHERE cnt<=3

class_name	title	score	cnt
Docker	PostgreSQL课程1	9.1	1
Docker	PostgreSQL课程2	9.1	1
HadoopDock	HBase课程1	9.2	1
HadoopDock	HBase课程2	9.1	2
…	…	…	…

需求：每门课程的学习人数占本类课程总学习人数的百分比

WITH tmp AS (
SELECT class_name,title,study_cnt
   	  ,SUM(study_cnt) OVER (PARTITION BY class_name) AS class_total
FROM imc_course a
JOIN imc_class b ON a.class_id=b.class_id
)
SELECT class_name,title, CONCAT(study_cnt/class_total*100,'%')
FROM tmp
ORDER BY class_name

class_name	title	concat(…
Docker	SQLServer课程	19.6756%
Docker	PostgreSQL课程	8.9576%
Docker	MySQL	29.8739%

总结

上面的例子，可以看到通过公共表达式和窗口函数可以做到很多的统计类的查询分析。实际上窗口函数是经常会用到统计分析中的一种函数，大家在以后工作中经常使用窗口函数，它来给我们工作中带来很多方便。

在SQL开发中易犯的错误

使用count(*)来判断是否存在符合条件的数据

在使用InnoDB存储引擎的情况下，count(*)这个效率并不是很高，每次查询都会在表中扫描完所有符合条件的数据后，才会返回结果。所以并不适合查询表中是否存在符合条件的数据。
如何达到更高的判断是否存在符合条件的数据呢？使用

SELECt ... LIMIT 1

上面语句只返回一条符合条件的记录，这样可以应用到MySQL对LIMIT子句的优化，在查询到一条符合条件的记录之后就返回结果，那性能要比count(*)往往要更高一些

在执行一个更新语句后，使用查询方式判断此更新语句是否执行成功

实际的工作中不推荐这样的方法，并且我们完全没有必要这么做，在MySQL中已经为我们提供了一个非常方便的函数，叫

ROW_COUNT()

这个函数可以在UPDATE或DELECT语句之后，来返回所有受影响的行数

试图在ON条件中过滤不满足条件的记录

这个错误我们看下面的例子，就比较清晰了
需求：查询出分类ID为5的课程名称和分类名称

SELECT a.class_name ,b.title ,a.class_id
FROM imc_class  a
JOIN imc_course b
ON b.class_id=a.class_id AND a.class_id=5

结果：↓

class_name	title	class_id
数据库	MySQL课程-04097	5
数据库	MySQL课程-84741	5
数据库	SQLSERVER课程-04097	5
数据库	PostgreSQL课程-04097	5
数据库	MySQL课程-53657	5

查询出来了分类为5的这些课程，可以看到在一些情况在ON子句进行数据过滤可以起到跟WHERE子句相同的效果；在另一方面，在ON子句进行数据过滤是起不到这个效果的，比如我们把关联换成外关联

SELECT a.class_name ,b.title ,a.class_id
FROM imc_class  a
-- JOIN 改为 LEFT JOIN
LEFT JOIN imc_course b
ON b.class_id=a.class_id AND a.class_id=5

结果：↓

class_name	title	class_id
AI	(NULL)	14
DOcker	(NULL)	10
Hadoop	(NULL)	9
HBase	(NULL)	7
Linux	(NULL)	12
数据库	MySQL课程-84741	5
数据库	MySQL课程-84741	5
…	…	…

看到会显示出来所有的分类Id，在ON子句的过滤条件并没有起到作用， 因为我们知道LEFT JOIN 会列出左表的所有数据，所以实际上这个过滤是无效的，最安全的过滤条件呢还是使用WHERE条件中进行过滤

在使用In进行子查询判断时，在列中未指定正确的表明

例如

Select A1 FROM A Where A1 in (select A1 from B)

这时尽管B中不存在A1列，数据库也不会报错，而是会列出A表所有的数据。是不是不相信这种情况会发生，我们来看一个例子：↓

SELECT *
FROM imc_course 
-- imc_class表中是不存在title列的
WHERE title IN (SELECT title FROM imc_class)

理论上上面一条语句会报错，但是实际执行结果呢是非但没有报错，反而列出了课程表中的所有信息。所以这也是我们在开发过成中比较容易犯的一个错误，并且这个错误犯了后还很不容易查找，因为它是没有任何报错信息的。

避免的方式

引用列名的时候指定具体的表名

SELECT *
FROM imc_course 
-- imc_class表中是不存在title列的
WHERE title IN (SELECT imc_class.title FROM imc_class)

上面语句执行就会报错了，因为imc_class表中是没有title列的

使用JOIN关联来代替子查询

这种方式比子查询更好

对于表中定义的具有not null和default值的列，在插入数据时插入NULL值

这显然是不符合数据库约束的，只要我们不这么做就可以了

总结

• 如何使用DCL语句来管理数据库的访问
• 如何使用DDL语句来建立数据库对象
• 如何使用DML语句来操作数据库中的数据
• MySQL常用的系统函数
• MySQL8.0中新增的通用表达式和窗口函数
• 在SQL开发中易犯的错误