MySql基础篇——DDL、DQL、DML和DCL_ddl dql dml dcl

一、DQL(数据查询语言)

1.简单查询

1. 基本语法:
   select 字段名 from 表名;
2. as关键字可以给查询的列起别名，将显示的查询结果列名显示为别名。as可以省略为空格。
3. 字段名可以使用数学表达式：
   select 字段名*5 from 表名;

2.条件查询

where关键字后面可以添加一下查询条件：

= 等于
<> 或 != 不等于
< 小于
> 大于
between … and … 在两个值之间，等同于 >= and <=
is null 为null（is not null 不为空）
and 并且
or 或者
in （value1，value2）包括，相当于多个or（not in 不在这个范围中）
not 可以取非，主要用在is或in中
like 模糊查询，%代表任意多个字符，_表示任意一个字符

3.单行处理函数

一行一行处理的，处理完后总行数不变。

3.1 流程控制函数

IF(VALUE,VALUE1,VALUE2)  # 如果VLAUE为真，则输出VALUE1，否则输出VALUE2。

IFNULL(VALUE,VALUE1)  # 如果value不为null，则为value，否则为value1。

CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE  结果3 END    # 相当于if else if else if else

CASE expr WHEN 常量1 THEN 值1 WHEN 常量值2 THEN 值2 ELSE 值n END   # 相当于switch ... case...
1
2
3
4
5
6
7

3.2 字符串函数

ASCLL(S)                 # 返回字符串s中的第一个字符的ASCII码值
CHAR_LENGTH(s)           # 返回字符串s的字符数，s为null则返回为null
LENGTH(s)                # 返回字符串s的字节数

concat(string1,string2....)   # 字符串拼接
concat_ws(x,s1,s2....sn)    # 同concat(string1,string2....) 函数，但是每个字符串之间都要加上x

INSERT(str,idx,len,replacestr)   # 将字符串str从第idx位置开始，len个字符长的字串替换为字符串replacestr，字符串索引从1开始。
REPLACE(str,a,b)  # 用字符串b替换字符串str中所有出现的字符串a

upper(s)    # 转大写
lower (s)   # 转小写

LEFT(str,n)  # 返回字符串str最左边的n个字符
RIGHT(str,n)  # 返回字符串str最右边的n个字符

TRIM(s)      # 去掉字符串s开始和结尾的空格
TRIM(s1 FROM s)  # 去掉字符串s开始与结尾的s1
TRIM(LEADING  s1 FROM s)  # 去掉字符串s开始处的s1
TRIM(TRAILING  s1 FROM s)  # 去掉字符串s结尾处的s1
LTRIM(s)     # 去掉字符串s左侧的空格
RTRIM(s)    # 去掉字符串s右侧的空格

repeat(n)    # 返回str重复n次的结果
space(n)   # 返回n个空格

strcmp(s1,s2)  # 比较字符串s1,s2的ASCII码值的大小
SUBSTR(s,index,len)  # 返回字符串s的index位置其len个字符，作用同SUBSTRING(s,n,len)、MID(s,n,len)相同
LOCATE(substr,str)  #  返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0。
ELT(m,s1,s2,....,sn)  #  返回指定位置的字符串，如果m = 1;则返回s1，如果m=n，则返回sn
FIELD(s,s1,s2,s3) # 返回s在字符串列表中第一次出现的位置
FIND_IN_SET(s1,s2)   # 返回字符串s1在字符串s2中出现的位置。其中字符串s2是一个以逗号分隔的字符串
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

3.3 其他函数

str_to_date 将字符串转换为日期
date_format 将date类型转换成具有一定格式的varchar字符串类型
round(数字，保留几位小数(0保留整数)) 四舍五入
rand 生成随机数
1
2
3
4

4.多行处理函数（分组函数/聚合函数）

4.1 常见聚合函数

1. count 计数
   count(*)，统计表中的总行数，因为不存在每个字段都为null的数据。
   count(具体某个字段)，统计该字段下所有不为null的元素总数。
2. sum 求和，以下四个函数都不统计null在内。如果所在字段都是null，那么结果也是null。
3. avg 平均值
4. max 最大值
5. min 最小值

4.2 注意事项

（1）输入多行，最终输出一行。
（2）分组函数在使用的时候必须先进行分组，然后才能使用。分组函数是对基于已经过滤过的数据进行计算，所以不用用在where中。分组函数在分组后进行计算，如果没有分组，整张表默认为一组。分组函数按照分组进行计算后，如果想对结果再进行分组函数计算，（例如求得每个部门中的最低工资，还想求所有部门中最低工资最低的部门），就得用子查询解决。
（3）分组函数自动忽略null。所以对于求平均数等需要注意
（4）分组函数不能直接使用在where子句中。
（5）mysql中聚合函数不能嵌套使用，Oracle中可以嵌套使用。

5.分组查询

1. 在一条select 语句当中，如果有group by语句的话，select后面只能跟分组字段或者分组函数。

2. select ... from ... where ... group by ... order by [字段1]，[字段2]
   按照两个字段分组

3. 分组完后可以使用having进行条件判断
   select ... from ... where ... group by ... having ...

4. having后面只能跟聚合函数和分组条件，并且having中可以使用别名。

5. 只有mysql中允许没有group by单独使用having，所以不要这样使用。

6. 如果过滤条件中使用聚合函数，则此过滤条件必须声明在HAVING中。

6.排序

order by [字段]
按照[字段]排序，默认升序。

order by [字段] desc
按照[字段]排序，指定降序

order by [字段] asc
按照[字段]排序，指定升序

order by [字段1] asc，[字段2] asc
先按照[字段1]升序排序，[字段1]相同按照字段2升序排序

order by 2
按照查询结果的第二列排序

7.单表查询总结

7.1 执行顺序：

from -> where -> group by -> having -> select -> order by -> limit

8.连接查询

8.1 分类

根据语法的年代分为SQL92和SQL99，以下重点介绍SQL99。
表连接的方式分为，内连接（等值连接，非等值连接，自连接），外连接（左外连接，右外连接），全连接。

8.2 笛卡尔积

当两张表连接进行查询时，没有任何条件限制会发生笛卡尔积现象。即一张表中的每一条数据都和另一张表每个字段匹配。

# 也叫交叉连接
select * from 表1，表2;
select * from 表1 CROSS JOIN 表2; # sql99
1
2
3

8.3 内连接

内连接只有能够匹配上条件的数据才能查询出来。如果连接的表中有一个条件字段为null，那么该条记录不显示。

等值连接

select * from 表1,表2 where 表1.字段 = 表2.字段  # 92的语法，where时添加条件。
select * from 表1 inner join 表2 on 表1.字段 = 表2.字段 # 99 语法
1
2

非等值连接

select * from 表1,表2 where 表1.字段 between 表2.字段 and 表2.字段 # 92 语法
select * from 表1 inner join 表2 on 表1.字段 >= 表2.字段 # 99 语法
1
2

自连接

内连接之自连接就是同一张表连接在一起。

select 别名1.字段,别名2.字段 from 表1 别名1 inner join 表1 别名1 on 别名1.字段=别名2.字段;
如果用*代替查询字段，则会查出双倍表1字段，相同的字段名会用【字段1】显示
1
2

8.4 外连接

mysql中不支持sql92的外连接

92语法

select * from 表1,表2 where 表1.字段 = 表2.字段(+)  # 左外连接
select * from 表1,表2 where 表1.字段(+) = 表2.字段 # 右外连接
1
2

左外连接

select * from 表1 left JOIN 表2 on 表1.字段 = 表2.字段 # 99 语法
将join关键字左边的表看成主表，关键字右边看成附表。
将主表数据都查出来，主表符合条件的字段将附表字段也查出来。不符合条件的则附表字段显示为null。
1
2
3

右外连接

select * from 表1 right outer JOIN 表2 on 表1.字段 = 表2.字段 # 99 语法
将join关键字右边的表看成主表，关键字左边看成附表。
将主表数据都查出来，主表符合条件的字段将附表字段也查出来。不符合条件的则附表字段显示为null。

1
2
3
4

8.5 全连接

又称满外连接

select * from 表1 FULL outer JOIN 表2 on 表1.字段 = 表2.字段 # 99 语法,MYSQL不支持
结果是表1表2的所有数据
1
2

全连接就是左右两张表都是主表，都查出来。

8.6 SQL99新特性

自然连接会自动查询两张表中所有相同的字段，然后进行等值连接。

SELECT * from 表1 NATURAL JOIN 表2;
1

USING指定数据库里的同名字段，但是只能配合JOIN一起使用。

SELECT * from 表1 JOIN 表2 USING(表1表2相同字段名);
1

9.子查询

8.1 使用方式

select 
	...(select)
from 
	...(select)
where 
	...(select)
where后面子查询，可以将子查询出来的结果当判断条件。
from后面的子查询，可以将子查询的查询结果当初一张临时表。
select后面的子查询，要求子查询结果一次只能返回一条记录，多余一条就报错。
1
2
3
4
5
6
7
8
9

8.2 分类

• 分类方式一：

我们按内查询的结果返回一条还是多条记录，将子查询分为单行子查询、多行子查询。

• 分类方式二：

我们按内查询是否被执行多次，将子查询划分相关（或关联）子查询和不相关（或非关联）子查询。

8.3 分类方式一

8.3.1 单行子查询
操作符有=，>,>=,<,<=,<>
8.3.2 多行子查询
in 等于列表中的任意一个。
any 需要和单行操作符一起使用，只要满足子查询结果的一个即可。
all 需要和单行操作符一起使用，要满足子查询结果的所有值。
some 实际上是any的别名，作用相同，一般常用any。

8.4 分类方式二

8.4.1 相关子查询
如果子查询需要执行多次，子查询依赖外部查询传入条件才能查询，这样的查询叫做相关子查询。
关联查询通常也会和EXISTS操作符来一起使用。EXISTS用于检查子查询是否至少返回一条数据。

select * from 表名 where exists(子查询);
如果子查询中不存在满足条件的行，子查询外的查询语句就不执行，该条记录不显示。
如果子查询中存在满足条件的行，子查询外的查询语句执行，该条记录显示。
1
2
3

not exists关键字表示如果不存在某种条件，则返回true，否则返回false。
8.4.2 不相关子查询
当子查询从数据表中查询了数据结果，这个结果只执行一次，不会根据主查询的变化而变化，那么这样的子查询就叫做不相关子查询。

10.union合并查询结果集

union可以将两次的查询结果上下拼接在一起。

select ... from ... where ....
union
select ... from ... where ....
1
2
3

• union在进行结果集合并时，要求两个结果集的列数相同。并且数据类型也相同。
• union操作符返回两个查询的并集，并去除重复记录。
• union all操作符返回两个查询的结果集和并集，对于两个结果集的重复部分不去重。
• 执行union all语句时所需要的资源比union语句少。如果明确知道合并数据后的结果语句数据不存在重复数据，尽量使用union all提高查询效率。

11.limit

可以将查询结果集的一部分取出来，分页。

limit startIndex,length

1. startIndex为起始下标，length是长度。startIndex缺省时为0，表示获取前length个数据。
2. limit在order by之后执行。
3. startIndex = (pageNo - 1) * pageSize

---

二、DDL(数据定义语言)

1. 数据库操作

1.1 创建数据库

CREATE DATABASE 数据库名; # 创建数据库
CREATE DATABASE 数据库名 CHARACTER SET 字符串; # 创建数据库并指定字符集
CREATE DATABASE IF NOT EXISTS 数据库名; # 如果数据库不存在则创建数据库
1
2
3

1.2 修改数据库

Alter DATABASE 数据库名 CHARACTER SET 字符集;
1

1.3 删除数据库

DROP DATABASE 数据库名;
DROP DATABASE IF EXISTS 数据库名;
1
2

2. 表操作

1.1 创建表

CREATE TABLE 表名(); # 创建数据库
CREATE TABLE IF NOT EXISTS 表名(); # 如果数据库不存在则创建数据库
CREATE TABLE 表名 AS (查询语句); # 基于现有的表复制成一个新表，同时导入查出的数据
1
2
3

1.2 修改表字段

添加一个字段，默认添加再最后一个。
ALTER TABLE 表名 ADD [COLUMN] 字段名 数据类型 [first|after 字段名];
修改一个字段：数据类型、长度、默认值
ALTER TABLE 表名 MODIFY 字段名 数据类型;
重命名一个字段
ALTER TABLE 表名 CHANGE 旧字段 新字段 数据类型;
删除一个字段
ALTER TABLE 表名 DROP COLUMN 字段名
1
2
3
4
5
6
7
8

1.3 重命名表

RENAME TABLE 旧表名 to 新表名;
ALTER TABLE 旧表名 RENAME TO 新表名;
1
2

1.4 删除表

drop table if exists 表名; # 不能回滚
1

1.5 清空表

truncate table 表名;
truncate 和 delete都可以对表中所有数据进行删除，同时保留表结构。
truncate不可以回滚，delete可以回滚。
1
2
3

truncate table 速度比delete快，且使用的系统和事务日志资源少，但TRUNCATE无事务且不触发TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。

3. 拓展内容

3.1 阿里巴巴《java开发手册》之mysql字段命名

• 【强制】 表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价大，所以字段名称需要慎重考虑。
• 【强制】禁用保留字，如desc，range，match，delayed等。
• 【强制】表必备三字段：id，gmt_create,gmt_modified。gmt_create,gmt_modified的类型均为datetime类型。
• 【推荐】表的命名最好是遵循“业务名称_表的作用”

3.2 mysql8新特性——DDL的原子性
在mysql8中，innodb表的ddl支持事物完整性，即ddl操作要么成功，要么回滚。ddl操作回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log（隐藏表）中，用于回滚操作。通过设置参数，可将ddl操作日志打印输出到mysql错误日志中。

三、DML(数据操作语言)

1. 增

一定要按字段的先手顺序添加 
INSERT INTO 表名 VALUES (数据1，数据2...) (数据1，数据2...); 

数据要和字段名对应，没显示写出的字段默认为null。如果有要求不为null，则回添加失败
INSERT INTO 表名(字段名1，字段名2...) VALUES(数据1，数据2...); 

INSET INTO 表名(字段名1，字段名2...) (子查询)
1
2
3
4
5
6
7

2. 改

UPDATE 表名 SET 字段1 = 值1,字段2 = 值1 WHERE 条件
1

3. 删

DELETE FROM 表名 where 条件
1

4. MYSQL8新特性—计算列

在创建表和修改表的时候可以增加计算列，简单的来说就是某一列的值是通过别的列计算得来的。
例如，a的列值为1，b的列值为2，c的列被定义为a+b,那么无需手动插入c。

CREATE TABLE test(
	a INT,
	b INT,
	c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);
1
2
3
4
5