进学阁

业精于勤荒于嬉，行成于思毁于随

0%

MySQL语法整理

发表于 更新于 分类于

SQL通用语法

  • SQL语句可以单行或多行书写，以分号结尾。
  • 可使用空格和缩进来增强语句的可读性。
  • MySQL数据库的SQL语言不区分大小写，关键字建议使用大写，对所有列和表名使用小写，这样做使代码更容易阅读和调试。
  • 3种注释： 单行注释： – 注释内容
    单行注释：# 注释内容（mysql特有）
    多行注释：/注释/

SQL分类

Mysql中SQL语句的分类可以分成：数据定义语言DDL、数据操纵语言DML、数据控制语言DCL、数据查询语言DQL。

  DDL：数据定义语言，这些语句主要用来创建、修改、删除数据库的逻辑结构，其中包括表结构，视图和索引等。常用的关键字有主要包括create、drop、alter等，truncate也是DDL关键字。

  DML：数据操纵语言，这些语句用于添加、删除、更新和查询数据库中的数据，并检查数据完整性。常用的关键字有insert、delete、update、select等。

  DCL：数据控制语言，这些语句主要用来控制数据库的访问权限。常用的关键字有主要包括grant、revoke、commit、rollback等。DCL主要用来控制数据库的权限。

  DQL：数据查询语言，各种简单查询，连接查询等 都属于DQL。

DDL

DDL：Data Define Language数据定义语言，主要用来对数据库、表进行一些管理操作。如：建库、删库、建表、修改表、删除表、对列的增删改等等。

1
2
3
4
5
6
7
8
9
10
11
#创建数据库
	CREATE DATABASE dbname
	#查看系统中都有哪些数据库
	SHOW DATABASES
	#建库通用的写法
	drop database if exists 旧库名;
	create database 新库名;
	#选择某个数据库
	USE dbname
	#删除数据库
	DROP DATABASE dbname
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
--创建表语法
	create table 表名(
    	字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
    	字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
    	字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
	)[表的一些设置];

	--建表示例
	create table test2(
  		a int not null comment '字段a',
  		b int not null default 0 comment '字段b'
	);

	#删除表
	DROP TABLE tablename
	#删除表更常见的用法
	DROP TABLE IF EXISTS tablename

    -- 只复制表结构
    create table 表名 like 被复制的表名;
    -- 复制表结构+数据
   create table 表名 [as] select 字段,... from 被复制的表 [where 条件];

	#更改表名 
	ALTER TABLE tablename RENAME [TO] new_table
	#示例:将表名由emp改成emp1
	ALTER TABLE emp RENAME emp1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#修改字段类型
ALTER TABLE tablename MODIFY [COLUMN] column_definition
#示例：将emp字段类型改成VARCHAR(20)
ALTER TABLE emp MODIFY name VARCHAR(20)
#增加字段
ALTER TABLE tablename ADD [column] column_definition
#示例：增加类型为int(3)的age字段
ALTER TABLE emp ADD COLUMN age INT(3)
#删除字段
ALTER TABLE tablename DROP [column] col_name
#修改字段名
ALTER TABLE tablename CHANGE [column] old_col_name column_definition
#示例：将age改名为age1，并修改字段类型
ALTER TABLE emp CHANGE age age1 INT(4)

注意：modify不能修改列名，change可以修改列名。
使用上面命令增加的字段默认都在表的最后位置，而CHANGE/MODIFY不会修改字段的位置。如果要修改字段的位置，要用AFTER/FIRST等关键字：

1
2
3
4
#将birth字段添加到ename之后
	ALTER TABLE emp ADD birth DATE AFTER ename
	#将age字段放在最前面
	ALTER TABLE emp MODIFY age INT(3) FIRST

DML

DML(Data Manipulation Language)数据操作语言，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除，是必须要掌握的指令，DML和SQL中的select俗称CRUD（增删改查）。

新增数据

1
2
#可以不指定字段名称，但value后面的顺序应该和字段的排列顺序一致
	INSERT INTO tablename (field1,field2,...,fieldn) VALUES (value1,value2,...valuen)

使用INSERT时，可以一次性插入多条数据：

1
2
3
4
5
6
INSERT INTO tablename (field1,field2,...,fieldn) 
	VALUES 
	(record1_value1,record1_value2,...record1_valuen)
	(record2_value1,record2_value2,...record2_valuen)	
	...
	(recordn_value1,recordn_value2,...recordn_valuen)

这里有个变种的写法 INSERT INTO SELECT FROM 

1
2
#可以不指定字段名称，但value后面的顺序应该和字段的排列顺序一致
	INSERT INTO tablename (field1,field2,...,fieldn) SELECT value1,value2,...valuen FROM SOURETABLE

更新数据

1
2
3
UPDATE tablename SET field1=value1,field2=value2,...,fieldn=valuen
	#示例
	UPDATE emp SET sal=4000 WHERE ename='lisa'

变种写法UPDATE JOIN SET ，主要用于两张表的数据同步

1
2
3
4
5
UPDATE table1
JOIN table2 ON table1.common_column = table2.common_column
SET table1.column_to_update = table2.value
WHERE table1.condition_column = condition_value;

删除数据

1
2
3
4
DELETE FROM tablename [WHERE CONDITION]
#示例
DELETE FROM emp WHERE ename='tony'

如果使用DELETE关键字时，后面没加条件，就会把整张表的数据删掉。

delete、truncate与drop*

三者都可以删除表中的数据，三者的差别：

delete truncate drop
回滚 可回滚 不可回滚 不可回滚
删除内容 表结构还在，删除表的全部或者一部分数据行 表结构还在，删除表中的所有数据 从数据库中删除表结果和所有的数据行，索引和权限也会被删除
删除速度 删除速度慢，需要逐行删除 删除速度快 删除速度最快

想删除部分数据，用delete；
保留表结构而删除所有数据，用truncate；
不再需要一张表，用drop。

  • delete一次删除一行，会将删除操作作为食物记录在日志中保存以便进行回滚操作，TRUNCATE TABLE则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
  • 表和索引所占空间。当表被TRUNCATE后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。DROP语句将表所占用的空间全释放掉。
  • 一般而言，执行速度：DROP>TRUNCATE>DELETE。
  • 应用范围。TRUNCATE只能对TABLE；DELETE可以是TABLE和VIEW
  • TRUNCATE和DELETE只删除数据，而DROP则删除整个表（结构和数据）。
  • TRUNCATE与不带 WHERE的DELETE：只删除数据，而不删除表的结构（定义）drop 语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。
  • truncate、drop是ddl，操作立即生效。
1
2
3
TRUNCATE TABLE table_name;

DROP TABLE table_name;

SELECT语句

简单查询
1
2
3
4
#查询指定列
SELECT 查询的列1,查询的列2 FROM tablename [WHERE CONDITION]
#查询所有列
select * from 表名
列别名
1
2
3
4
5
6
7
8
9
select 列 [as] 别名 from 表;
-- 使用双引号创建别名
select a "列1",b "列2" from test1;
-- 使用单引号创建别名
select a '列1',b '列2' from test1;
-- 不用引号创建别名
select a 列1,b 列2 from test1;
-- 使用as创建别名
select a as 列1,b as 列2 from test1;

建议给列起别名时，使用as关键字，sql易读。

条件查询
1
select 列名 from 表名 where 列 运算符 值

WHERE后面跟的条件中，除了可以使用=，还可以使用>、<、>=、<=、！=等比较运算符，多个条件之间可以用OR、AND等逻辑运算符。

模糊查询
1
2
-- ％对应于0个或更多字符,_只是 LIKE 语句中的一个字符
select 列名 from 表名 where 列 like pattern;
区间查询

操作符 BETWEEN … AND 会选取介于两个值之间的数据范围，这些值可以是数值、文本或者日期，属于一个闭区间查询。

1
selec 列名 from 表名 where 列名 between 值1 and 值2;
IN查询
1
select 列名 from 表名 where 字段 in (值1,值2,值3,值4);

in后面括号中可以包含多个值，对应记录的字段满足in中任意一个都会被返回。
in列表的值类型必须一致或兼容。
in列表中不支持通配符。

EXIST
1
2
SELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE B.id  = A.id);

等价于：

1、SELECT * FROM A;
2、SELECT I FROM B WHERE B.id = A.id;

:::info
EXISTS()查询会执行SELECT * FROM A查询，执行A.length次，并不会将  EXISTS()查询结果结果进行缓存，因为EXISTS()查询返回一个布尔值true或flase，它只在乎EXISTS()的查询中是否有记录，与具体的结果集无关。

  EXISTS()查询是将主查询的结果集放到子查询中做验证，根据验证结果是true或false来决定主查询数据结果是否得以保存。

  可以看出，使用EXIST时，子查询中适合放大表（数据量大的表）。

:::

NOT IN查询

not in和in刚好相反，in是列表中被匹配的都会被返回，NOT IN是和列表中都不匹配的会被返回。

1
select 列名 from 表名 where 字段 not in (值1,值2,值3,值4);

用 not in 的时候，如果 not in 后面的选项中有 null，不会查询出来任何数据。sql 语句本身直接返回 false。所以使用 not in 的时候，要保证 in 中的条件不会出现 null 的情况。

NULL值查询
1
2
3
select 列名 from 表名 where 列 is null;
select 列名 from 表名 where 列 is not null;

排序查询
1
2
3
4
5
6
SELECT * FROM tablename 
[WHERE CONDITION] 
[ORDER BY field1 [DESC|ASC],field2 [DESC|ASC],...,fieldn [DESC|ASC]]
-- 示例
select * from product order by price desc,order_count asc

DESC表示按字段进行降序排列，ASC表示升序排列，默认是升序排列。支持多个字段进行排序，多字段排序之间用逗号隔开，order by后越靠前的字段排序优先级越高。

LIMIT语句
1
select 列 from 表 limit [offset,] count;

offset：表示偏移量，通俗点讲就是跳过多少行，offset可以省略，默认为0，表示跳过0行；范围：[0,+∞)。
count：跳过offset行之后开始取数据，取count行记录；范围：[0,+∞)。
limit中offset和count的值不能用表达式。

1
2
3
4
5
6
7
#获取前n行记录
	select 列 from 表 limit 0,n;
	select 列 from 表 limit n;
	#获取排名第n到m的记录
	select 列 from 表 limit n-1,m-n+1;
	#示例：显示从第2条数据开始的3条数据
	SELECT * FROM emp ORDER BY sal LIMIT 1,3
分页查询

LIMIT经常和ORDER BY一起使用，进行数据的分页显示。

  开发过程中，分页我们经常使用，分页一般有2个参数：

  page：表示第几页，从1开始，范围[1,+∞)。

  pageSize：每页显示多少条记录，范围[1,+∞)。

  如：page = 2，pageSize = 10，表示获取第2页10条数据。使用limit分页的语法：

1
2
select 列 from 表名 limit (page - 1) * pageSize,pageSize;

注意事项：

  1. limit中不能使用表达式，只能够跟明确的数字。
  2. limit后面的2个数字不能为负数。
分组查询
1
2
3
4
5
6
SELECT [filed1,field2,...,fieldn] fun_name
FROM tablename
[WHERE where_condition]
[GROUP BY field1,field2,...,fieldn]
[HAVING where_condition]

fun_name表示聚合函数，常用的有sum、count(*)、avg、max、min等。GROUP BY后面跟的是要进行分类聚合的字段。HAVING表示对分类后的结果在进行条件过滤。

分组中，select后面只能有两种类型的列：

1）出现在group by后的列；

2）或者使用聚合函数的列。

  Group BY用来创建分组，如果分组中有NULL值，将NULL作为一个分组返回。如果列中有多行NULL值，它们将分为一组。

UNION语句

将多个查询的数据合并用到的关键字是UNION/UNION ALL：

1
2
3
4
5
6
7
SELECT * FROM t1
UNION|UNION ALL
SELECT * FROM t2
...
UNION|UNION ALL
SELECT * FROM tn

UNION和UNION ALL的区别：

  • UNION ALL：把结果集直接合并在一起；
  • UNION：将UNION ALL的结果进行去重。

因为UNION有个去重的过程，所以UNION ALL的效率较高。

COUNT语句*

COUNT(*)函数返回表中所有行的数量，包括NULL值行。

1
2
SELECT COUNT(*) FROM students;

COUNT(column_name)函数返回指定列非NULL值的数量，忽略NULL值行。

1
2
SELECT COUNT(age) FROM students;

COUNT(DISTINCT column_name)返回指定列中不同值的数量，忽略NULL值行。

1
2
SELECT COUNT(DISTINCT age) FROM students;

COUNT(DISTINCT expr,[expr…])函数返回指定表达式中不同非NULL值的数量，忽略NULL值行。

1
2
SELECT COUNT(DISTINCT age, gender) FROM students;

COUNT(IF(condition, column_name, NULL))函数返回满足指定条件的指定列的非NULL值数量。

1
2
3
4
-- 这个 SQL 语句的含义是统计学生表中性别为 ‘Female’ 的人数。通过 IF 函数，将所有性别
-- 为 ‘Female’ 的行转换成 1，然后 COUNT 函数就会返回这些 1 的数量，即性别为 ‘Female’ 
-- 的学生数量
SELECT COUNT(IF(gender='Female', 1, NULL)) FROM students;

ount(*)、count(1)、count(列名)的选择

  COUNT(*) 是最常用的方式，它可以统计所有行的数量，包括 NULL 值。

  COUNT(1) 也可以统计所有行的数量，包括 NULL 值。

  COUNT(column_name) 只会统计指定列中非 NULL 值的数量，而忽略 NULL 值。因此，如果需要统计指定列的非 NULL 值数量，那么可以使用 COUNT(column_name)。这种方式的性能相对较低，因为需要扫描整个表，对于每一行都要进行判断。

关联查询

Mysql中常用的关联查询有左外连接、右外连接、全连接、自连接等。

笛卡尔积

笛卡尔积简单点理解：有两个集合A和B，笛卡尔积表示A集合中的元素和B集合中的元素任意相互关联产生的所有可能的结果。假如A中有m个元素，B中有n个元素，A、B笛卡尔积产生的结果有m*n个结果，相当于循环遍历两个集合中的元素，任意组合。

过程：拿A集合中的第1行，去匹配集合B中所有的行，然后再拿集合A中的第2行，去匹配集合B中所有的行，最后结果数量为m*n。

内连接 (INNER JOIN)

内连接返回两个表中满足连接条件的行。

1
2
3
4
SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

左连接 (LEFT JOIN)

左连接返回左表中的所有行以及右表中满足连接条件的行。如果右表中没有匹配的行，结果中包含的右表列将包含NULL。

1
2
3
4
SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

右连接 (RIGHT JOIN)

右连接返回右表中的所有行以及左表中满足连接条件的行。如果左表中没有匹配的行，结果中包含的左表列将包含NULL。

1
2
3
4
SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

全连接 (FULL JOIN)

MySQL不直接支持FULL JOIN，但可以通过使用UNION来模拟。

1
2
3
4
5
6
7
8
SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id
UNION
SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

交叉连接 (CROSS JOIN)

交叉连接返回两个表的笛卡尔积，即表1中的每一行与表2中的每一行组合。

1
2
3
4
SELECT employees.employee_name, departments.department_name
FROM employees
CROSS JOIN departments;

自连接（Self-Join）

自连接（Self-Join）是在同一个表中进行的联接查询，用于将表中的一行与另一行相关联。自连接常用于需要比较表中不同行的数据的情况，例如查找员工和他们的经理、产品的父子关系等。

1
2
3
4
SELECT e1.employee_name AS employee, e2.employee_name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

在自连接查询中，使用表别名（如 e1 和 e2）是必需的，以区分表的不同实例。

自连接可能对大表进行性能影响，因此在实际应用中需要注意索引和查询优化。

根据需求，可以使用不同的连接类型（INNER JOIN、LEFT JOIN等）来决定是否包括没有匹配关系的行。

DCL

DCL：数据控制语言，这些语句主要用来控制数据库的访问权限。常用的关键字有主要包括grant、revoke、commit、rollback等。DCL主要用来控制数据库的权限。

DQL

数据查询语言，各种简单查询，连接查询等 都属于DQL。

查询语句的书写和执行顺序

Mysql的一般书写顺写为：

1
2
3
4
5
6
7
8
9
10
select  <要返回的数据列>
from   <表名>
<join, left join, right join...> join    <join表>
on   <join条件>
where   <where条件>
group by  <分组条件>
having  <分组后的筛选条件>
order by  <排序条件>
limit  <行数限制>

常见执行顺序*
  1. 一个查询语句同时出现了where、group by、having、order by的时候，执行顺序和编写顺序是：
  2. 执行where xx对全表数据做筛选，返回第1个结果集。
  3. 针对第1个结果集使用group by分组，返回第2个结果集。
  4. 针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。
  5. 针对第3个结集执行having xx进行筛选，返回第4个结果集。
  6. 针对第4个结果集排序。

按照执行顺序的关键词首字母分别是W（where）->G（Group）->S（Select）->H（Having）->O（Order），对应汉语首字母可以编成容易记忆的顺口溜：我(W)哥(G)是(SH)偶(O)像。

完整执行顺序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
from 
    <表名>          # 笛卡尔积
on 
    <筛选条件>          #对笛卡尔积的虚表进行筛选
<join, left join, right join...> join 
    <join表>  #指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
where 
    <where条件>     #对上述虚表进行筛选
group by
    <分组条件>      #分组
<sum()等聚合函数>   #用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
having 
    <分组筛选>      #对分组后的结果进行聚合筛选
select 
    <返回数据列表>       #返回的单列必须在group by子句中，聚合函数除外
distinct
order by 
    <排序条件>      #排序
limit 
    <行数限制>

执行流程：

  • from：select * from table_1, table_2; 与 select * from table_1 join table_2; 的结果一致，都是表示求笛卡尔积；
  •   用于直接计算两个表笛卡尔积，得到虚拟表VT1，这是所有select语句最先执行的操作，其他操作时在这个表上进行的，也就是from操作所完成的内容。
  • on：从VT1表中筛选符合条件的数据，形成VT2表；
  • join：将该join类型的数据补充到VT2表中，例如left join会将左表的剩余数据添加到虚表VT2中，形成VT3表；若表的数量大于2，则会重复1-3步；
  • where：执行筛选，（不能使用聚合函数）得到VT4表；
  • group by：对VT4表进行分组，得到VT5表；其后处理的语句，如select，having，所用到的列必须包含在group by条件中，没有出现的需要用聚合函数；
  • having：筛选分组后的数据，得到VT6表；
  • select：返回列得到VT7表；
  • distinct：用于去重得到VT8表；
  • order by：用于排序得到VT9表；
  • limit：返回需要的行数，得到VT10。

三大范式*

数据库的三大范式的设计目的是减少数据冗余。

第一范式

第一范式是最基本的范式。如果数据库表中的所有字段值都不可再分解（列不可再分），就说明该数据库表满足了第一范式，确保数据库表字段的原子性。

  第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。

第二范式

第二范式：表必须有一个主键，非主键列必须完全依赖于主键，而不能只依赖于主键的一部分。

第三范式

满足第三范式（3NF）必须先满足第二范式（2NF）。
第三范式：非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

  • 2NF和3NF的区别
    2NF依据是非主键列是否完全依赖于主键，还是依赖于主键的一部分。
    3NF依据是非主键列是直接依赖于主键，还是直接依赖于非主键。
简单总结
  • 1、第一范式： 列不可分。
  • 2、第二范式：列必须直接依赖主键。
  • 3、第三范式： 表里面的列不能出现其它表的非主键字段
范式化和反范式化设计的优缺点
  • 范式化
    优点：可以尽量得减少数据冗余，使得更新快，体积小。
    缺点：对于查询需要多个表进行关联，减少写得效率增加读得效率。
  • 反范式化
    优点：可以减少表得关联，可以更好得进行索引优化。
    缺点：数据冗余，数据的修改需要更多的成本。

约束

创建表的时候，同时可以指定所插入数据的一些规则，比如说某个字段不能为空值，某个字段的值（比如年龄）不能小于零等等，这些规则称为约束。约束是在表上强制执行的数据校验规则。
数据库中的五大约束：

  1. 主键约束： 唯一，非空【常用】；
  2. 唯一约束 ：唯一，可以为空，但只能有一个【常用】；
  3. 非空约束：非空【常用】；
  4. 默认约束 ：该列数据的默认值；
  5. 外键约束 ： 两表间的关系；
  6. 检查约束 ： 检查字段值是否符合要求。

约束示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
--添加主键约束
Alter table 表名 add Constraint 主键名 primary key(字段)
--添加唯一约束
Alter table 表名 add Constraint 约束名 unique(字段)
--添加默认约束
Alter table 表名 add Constraint 约束名 default(默认内容) for 字段名
--添加检查约束
Alter table 表名 add Constraint 约束名 check (字段表达)
--添加外键约束
Alter table 表名 add Constraint 约束名 foreign key(字段) references 表名(字段名)
--约束常常在建表时添加，如对某个极端添加非空约束
`account_name` varchar(64) NOT NULL COMMENT '账户名称',

SQL相关问题

HAVNG和WHERE的区别*

  • 语法

       where用表中列名，having用select结果别名；

  • 是否能使用索引

        where可以使用索引，having不能使用索引，只能在临时结果集操作；

  • 是否能使用聚集函数

where 后面不能使用聚集函数，having是专门使用聚集函数的。

  • 一个聚集函数从多个输入行中计算出一个结果，比如count（数目）， sum（和），avg（ 均值 ）， max（最大值）和min（最小值）。
  • HAVING是对聚合(数据分组)后的结果进行条件的过滤，而WHERE是在聚合(数据分组)前就对记录进行过滤。如果逻辑允许，尽可能用WHERE先过滤条件，因为这样结果集减小，聚合的效率将大大提高，最后再根据逻辑看是否用HAVING进行过滤。
[SELECT *] 和 [SELECT 全部字段]的区别1、是否解析数据字典

1、前者要解析数据字典（数据字典是指对数据的数据项、数据结构、数据流、数据存储、处理逻辑、外部实体等进行定义和描述，其目的是对数据流程图中的各个元素做出详细的说明），后者不需要数据字典。

2、是否可指定输出顺序

  前者与建表列顺序相同（建表后不增删字段的话），后者可以指定字段顺序。

3、表字段改名，前者不需要修改，后者需要改。

4、是否可优化

  后者可以建立索引进行优化，前者无法优化。

5、可读性

  后者的可读性比前者要高。

Mysql分组中的坑

 分组中select后面的列只能有2种：1）出现在group by后面的列；2）使用聚合函数的列。

  这是在Mysql后期版本加上的功能，在早期版本中没这个要求。建议：在写分组查询的时候，最好按照标准的规范来写，即：select后面出现的列必须在group by中或者必须使用聚合函数。

数据库开发规范

基础规范

 1）必须使用InnoDB存储引擎解读：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高。

  2）必须使用UTF8字符集解读：万国码，无需转码，无乱码风险，节省空间。

  3）数据表、数据字段必须加入中文注释。

  4）禁止使用存储过程、视图、触发器、Event解读：高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。

  5）禁止存储大文件或者大照片解读：为何要让数据库做它不擅长的事情？大文件和照片存储在文件系统，数据库里存URL。

命名规范

 1）库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用.

  2）表名t_xxx，非唯一索引名uniq_xxx，唯一索引名idx_xxx.

  3）单实例表数目必须小于500。

  4）单表列数目必须小于30。

  5）表必须有主键，例如自增主键：

6）禁止使用外键，如果有外键完整性约束，需要应用程序控制解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先。

:::tips
a）主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用。
b）主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率。

:::

字段设计规范

1）必须把字段定义为NOT NULL并且提供默认值：

:::tips
 a）null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化。

  b）null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多。

  c）null值需要更多的存储空间，无论是表还是索引中每行中的null的列都需要额外的空间来标识。

  d）对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。

:::

2）禁止使用TEXT、BLOB类型解读：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能。
3）禁止使用小数存储货币解读，小数容易导致钱对不上。
4）必须使用varchar(20)存储手机号：

:::tips
a）涉及到区号或者国家代号，可能出现±。
b）varchar可以支持模糊查询，例如：like“138%”。

:::

5）禁止使用ENUM，可使用TINYINT代替：

:::tips
a）增加新的ENUM值要做DDL操作。
b）ENUM的内部实际存储就是整数。

:::

6）单表索引建议控制在5个以内。
7）单索引字段数不允许超过5个。字段超过5个时，实际已经起不到有效过滤数据的作用了。
8）禁止在更新十分频繁、区分度不高的属性上建立索引：

:::tips
a）更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能。
b）“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。

:::

9）建立组合索引，必须把区分度高的字段放在前面解读：能够更加有效的过滤数据。
10）禁止使用SELECT *，只获取必要的字段，需要显示说明列属性：

:::tips
a）读取不需要的列会增加CPU、IO、NET消耗。
b）不能有效的利用覆盖索引。
c）使用SELECT *容易在增加或者删除字段后出现程序BUG。

:::

11）禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性解读：容易在增加或者删除字段后出现程序BUG。

  12）禁止使用属性隐式转换解读：SELECT uid FROM t_user WHERE phone=13800000000 会导致全表扫描，而不能命中phone索引。

  13）禁止在WHERE条件的属性上使用函数或者表达式：SELECT uid FROM t_user WHERE from_unixtime(day)>=’2017-01-15’会导致全表扫描，正确的写法是：SELECT uid FROM t_user WHERE day>=unix_timestamp(‘2017-01-15 00:00:00’)。

  14）禁止负向查询，以及%开头的模糊查询：

:::tips
a）负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描。
b）%开头的模糊查询，会导致全表扫描。

:::

15）禁止使用OR条件，必须改为IN查询解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮助实施查询优化呢？

  16）应用程序必须捕获SQL异常，并有相应处理.

  17）同表的增删字段、索引合并一条DDL语句执行，提高执行效率，减少与数据库的交互。