MySql 的 SQL 语句怎么写？

• 
结构化查询语言

  （Structured Query Language，简称 SQL）是一种
  
数据库查询和设计语言

  。

• 
每条 SQL 结尾建议添加 ';' 分号

  ，用来表示 这条 SQL 结束了（不加的时候，不能同时执行多条同级别的 SQL）

• 编写 SQL 时，
  
建议每条子 SQL 语句单独一行

  ，方便理解

• 通常使用空格和缩进增强语句的可读性，同样空格在分行书写时需要检查空格是否正确添加。

• 个人建议：
  
关键大写

  。

• 注释：
  
多行注释（/* */）、单行注释（#）、单行注释（--[空格]）

  。

分类

数据定义语言 DDL（Data Definition Language）

• 
修改数据中表的结构

  。

• 用来
  
创建、删除、修改

  数据库中的各种
  
对象

  ，创建、删除、修改
  
表的结构

  ，比如
  
表、视图、索引、同义词、聚簇

  等。

• 代表关键字：

  • 
create（创建）


  • 
alter（更改）


  • 
drop（删除）


• 常见命令语句：

# 创建一个新的数据库(new_database)
CREATE DATABASE new_database;

# 将 指定的数据库编码设置为 UTF-8
ALTER DATABASE new_database CHARACTER SET utf8;

# 删除指定的数据库
DROP DATABASE new_database;

# 创建表（表名：user_info）
# 该表有 id、user_name、password 三个字段
# id 是主键字段
CREATE TABLE user_info
(
    id        char(36) PRIMARY KEY,
    user_name varchar(30) NOT NULL,
    password  varchar(30) NOT NULL
);

# 在表 user_info 中添加新列 annual(double 类型)
ALTER TABLE user_info
    ADD COLUMN annual double;

# 删除表 user_info
DROP TABLE user_info;

数据查询语言 DQL（Data Query Language）

• 按照指定的组合、条件表达式或排序
  
检索已存在的数据库中数据

  ，
  
不改变数据库中数据

  。

• 以
  
SELECT 关键字

  起，是
  
由 SELECT 子句，FROM 子句，WHERE 子句、ORDER BY 字句、GROUP BY 字句

  组成的查询块。

• 代表关键字：

  • 
select


  • 
where


• 常用子语句有：

  • 
select 子句


  • 
from 子句


  • 
where 子句


  • 
order by 子句


  • 
group by 子句


• 常见命令语句：

# 表 user_info 只有 id、user_name、password 三个字段

# 查询 user_info 表中的所有数据
SELECT id, user_name, password
FROM user_info;
# * 号表示获取全部字段，但不建议使用
SELECT *
FROM user_info;

# 条件查询（id 大于 5）
SELECT id, user_name, password
FROM user_info
WHERE id > 5;

数据操纵语言 DML（Data Manipulation Language）

• 
修改数据中表的数据

  。

• 向表中
  
添加、删除、修改

  表中数据的操作语言。

• 代表关键字：

  • 
insert（添加）


  • 
update（更改）


  • 
delete（删除）


• 常用子语句有：

  • 
insert 子句


  • 
update 子句


  • 
delete 子句


• 常见的命令语句：

# 表 user_info 只有 id、user_name、password 三个字段

# 插入一条数据
INSERT INTO user_info(id, user_name, password)
VALUES ('01', '0001', '003567');
# 插入多条数据
INSERT INTO user_info(id, user_name, password)
VALUES ('01', '0001', '003567'),
       ('02', '0002', '900369');

# 删除 表user_info 的某条数据
DELETE
FROM user_info
WHERE id = '01';
# 删除表 user_info 中所有数据
DELETE
FROM user_info;

# 修改 表 user_info 中某条数据
UPDATE user_info
SET user_name = '012',
    password  = null
WHERE id = '02';

数据控制语言 DCL（Data Control Language）

• 用来
  
授予或收回

  访问数据库的某种特权、
  
对数据库进行监视

  等操作。

• 代表关键字：

  • 
grant(赋予用户某种控制权限)


  • 
revoke(取消用户某种控制权限)


事务控制语言 TCL（Transaction Control Language）

• 用来控制事务的
  
提交和回滚

  。

• 代表关键字：

  • 
commit（提交事务）


  • 
commit work（提交事务）


  • 
rollback（回滚事务）


  • 
rollback work（回滚事务）


  • 
savepoint [保存点名称]（创建事务保存点）


  • 
release savepoint [保存点名称]（删除事务保存点）


• 常见的命令语句：

# 开启一个事务
BEGIN;
START TRANSACTION;

# 提交事务
-- COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
COMMIT;
COMMIT WORK;

# 事务保存点的 创建与删除
-- SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
SAVEPOINT rollPoint;

-- RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
RELEASE SAVEPOINT rollPoint;

# 回滚事务
-- ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
ROLLBACK;
ROLLBACK WORK;
-- ROLLBACK TO identifier 把事务回滚到标记点；
ROLLBACK TO rollPoint;

常见函数

• 
字符串比较


=、<=>

  前者不支持比较 null，后者支持。

聚合函数

• 注意：只有
  
count(x) 函数默认不忽略 null 的行，其他函数默认忽略 null 的行

  ；
  
添加 distinct 关键字可以将指定列的识别改为 ALL

  。

• 
AVG(x)

  计算
  
平均值


• 
COUNT(x)

  统计
  
数量

  （统计指定列中非 NULL 的行的数目）

• 
MAX(x)

  找出
  
最大值


• 
MIN(x)

  找出
  
最小值


• 
SUM(x)

  计算
  
总和


字符串处理函数

• 
CONCAT(str1, str2, ...)


拼接

  字符串

• 
RTRIM(str)、TRIM(str)、LTRIM(str)


去除字符串的空格

  （分别是：去除字符串左边的空格、去除字符串右边的空格、去除字符串两边的空格）

• 
UPPER(str)、UCASE（str）

  将字符串的字符全部
  
转成大写的字符


• 
LOWER(str)、LCASE(str)

  将字符串的字符全部
  
转成小写的字符


• 
STRCMP(str1, str2)


比较字符串的大小

  （具体怎样比较的不是很清楚）

• 
LENGTH(str)

  获取字符串的
  
字节数


• 
CHAR_LENGTH(str)

  获取字符串的
  
字符数


• 
LEFT(str, length)

  从
  
左边开始截取 length 个字符

  ，（length 大于等于 str 的字符数时，截取整个 str；length 小于等于 0 时，不截取，为 空）

• 
RIGHT(str, length)

  从
  
右边开始截取 length 个字符

  ，（length 大于等于 str 的字符数时，截取整个 str；length 小于等于 0 时，不截取，为 空）

• 
SUBSTRING(str, length)、SUBSTR(str, length)

  截取字符（length = 0 或 length 的绝对值大于 str 的字符数时，为空），（length 大于 0 时，从左边、第
  
  length 个字符开始截取到字符串末尾；length 小于 0 时，从右边往左边截取 length 的绝对值个字符）

• 
SUBSTRING(str, start, length)


从 start 个字符开始，截取 length 个字符

  （start = 0 或 start 的绝对值大于 str 的字符数时，为空；length 的大于 str
  
  的字符数时，取剩下的字符），（start 大于 0 时，从左边、第 start 个字符开始截取 length 字符；start 小于 0 时，从右边往左边截取 start 的绝对值个字符）

处理数值的数学函数

— 运算：MOD(10, 3) 等价于 10%3，那么 SQL 中可以写公式吗？可以的，

SQL 支持计算四则运算的公式

。

• 
ABS(x)

  计算
  
绝对值


• 
CEIL(x)


向上取整

  （取整得到的数值大于等于 x，如：ceil(4.0) = 4，ceil(4.1) = 5，ceil(-4.9) = -4，ceil(-4) = -4）

• 
FLOOR(x)


向下取整

  （取整得到的数值小于等于 x，如：ceil(4.0) = 4，ceil(4.9) = 4，ceil(-4.1) = -5，ceil(-4) = -4）

• 
MOD(x, y)

  获取
  
模值

  （就是求余，求 x/ABS(y) 的余值）

• 
RAND()

  获取
  
随机数

  （结果范围在 0~1 之间，如需要扩大范围，乘以相应的的值即可，结果是一个小数位很长的小数，一般需要相应取整）

• 
ROUND(x, y)、ROUND(x)


四舍五入

  （y 表示要保留的小数的位数，当 y 大于小数 x 的小数位数时，保留小数 x 的所有小数位数）

  • 当 y=0 时，保留零位小数，ROUND(x) 即 ROUND(x, 0)，如：ROUND(3.54)=4

  • 当 y=1 时，保留一位小数，以此类推，如：ROUND(3.54, 1)=3.5

  • 当 y=-1 时，保留零位小数，个位也需要四舍五入，如：ROUND(32.54, -1)=30

  • 当 y=-2 时，保留零位小数，个位和十位也需要四舍五入，以此类推，如：ROUND(352.54)=400

• 
TRUNCATE(x, y)


截取

  部分数值（y 表示要截取的小数的位数，当 y 大于小数 x 的小数位数时，截取小数 x 的所有位数）

  • 当 y=0 时，截取整数位，如：TRUNCATE(3.54, 0)=3

  • 当 y=1 时，保留一位小数，以此类推，如：TRUNCATE(3.54, 1)=3.5

  • 当 y=-1 时，保留零位小数，个位取零，截取十位及以上，如：TRUNCATE(32.54, -1)=30

  • 当 y=-2 时，保留零位小数，个位和十位取零，截取百位及以上，以此类推，如：TRUNCATE(352.54)=300

时间处理函数

• 
CURDATE()、CURRENT_DATE()

  获取
  
当前日期（实时）


• 
CURTIME()、CURRENT_TIME()

  获取
  
当前时间（实时），没有毫秒


• 
NOW()、CURRENT_TIMESTAMP()

  获取
  
当前日期和时间（实时），没有毫秒


• 
DATE(date)

  从输入的日期（日期时间）中获取
  
日期


• 
TIME(date)

  从输入的日期（日期时间）中获取
  
时间


• 
YEAR(date)

  从输入的日期（日期时间）中
  
获取当前年份


• 
MONTH(date)、MONTHNAME(date)

  从输入的日期（日期时间）中
  
获取当前月份

  ，前者返回阿拉伯数字、后者返回月份的英文名称

• 
DAY(date)

  从输入的日期（日期时间）中
  
获取本月当前天数


• 
HOUR(date)

  从输入的日期（日期时间）中
  
获取当前小时数


• 
MINUTE(date)

  从输入的日期（日期时间）中
  
获取当前分钟数


• 
SECOND(date)

  从输入的日期（日期时间）中
  
获取当前秒数


• 
WEEK(date)、DAYNAME(date)

  从输入的日期（日期时间）中获取当月天数
  
属于星期几

  ，前者前者返回阿拉伯数字（0~6，表示 周一~周天），后者返回周几的英文名称

DQL 和 DML 语句的关键字

DQL（数据操纵语言）


关键字的执行顺序

问题

• 
from 字句，第一顺序


• 
where 字句，第二顺序


• 
group by 字句，第三顺序


• 
having 字句，第四顺序


• 
select 字句，第五顺序


• 
order by 字句，第六顺序


SELECT 关键字

• 
select 字句

  ：
  
select 关键字 加上 需要查询的字段


• select，选择，用在查询 SQL 中

• select 关键字后跟 表字段，是 from 引入的表中的属性（字段），查询多个属性时，用 ‘,’ 分割

• 当然，查询表的所有字段时，可以用通配符 ‘*’ 来代替所有属性（效率慢，实际开发尽量不要使用）

# 表 user_info 只有 id、user_name、password 三个字段

# 查询 user_info 表中的所有数据
SELECT id, user_name, password
FROM user_info;

SELECT *
FROM user_info;

FROM 关键字

• 
from 字句

  ：
  
from 关键字 加上 需要导入的表（表联结同样在此处添加）


• from，标记，用来 接入数据表。

• 同样，接入多个表用 ‘,’ 分割，此时 表联结规则写到 WHERE 子句中。

• 若，用联结关键词接入多个表，此时 表联结规则写到 ON 后面中。

内联结（inner join）、等值联结

• 连接方式：
  
[表1] INNER JOIN [表2] ON [表1].[字段a] = [表2].[字段b]


• 最常使用。

SELECT *
FROM author
         INNER JOIN article ON article_author_id = author_id;

自然联结（natural join）

• 事实上，我们迄今为止建立的每个内联结都是自然联结，很可能永远都不会用到不是自然联结的内联结。

• 自然联结是
  
内联结的一个分支

  。

• 自然联结
  
排除多次出现，使每一列只返回一次（自行实现）

  。

• 没有对应的关键字，
  
使用自联结或内联结的方式皆可

  。

自联结（self join）

• 指的是：两次导入同一张表。

• 常用的连接方式：
  
[表1], [表2] WHERE [表1].[字段a] = [表2].[字段b]


• 自联结通常
  
作为外部语句

  ，用来
  
替代从相同表中检索数据的使用子查询语句

  。

• 虽然最终的结果是相同的，但
  
许多 DBMS 处理联结远比处理子查询快得多

  。

SELECT *
FROM author AS a1,
     author AS a2
WHERE a1.author_id > a2.author_id;

左外联结（left outer join）、（left join）

• 以联结关键词
  
左边

  的表为基准，根据联结规制逐一与右表行进行对应。

• 输出的行数是
  
左表的行数

  。

• 若 右表表中某行数据
  
没有与左表对应上，那么就不输出

  。

SELECT *
FROM author
         LEFT JOIN article ON author_id = article_author_id;

右外联结（right outer join）、（right join）

• 以联结关键词
  
右边

  的表为基准，根据联结规制逐一与左表行进行对应。

• 输出的行数是
  
右表的行数

  。

• 若 左表表中某行数据
  
没有与右表对应上，那么就不输出

  。

SELECT *
FROM author
         RIGHT JOIN article ON author_id = article_author_id;

WHERE 关键字

• where 字句无法与 聚合函数 一起使用。

• 
where 字句

  ：where 关键字 加上对应的判定 key-value 对。

• 行级过滤。

AND 和 OR

• AND：两个条件进行统一计算，
  
在满足第一个条件下，满足第二个条件

  。

• OR：
  
满足第一个条件 或者 满足第二个条件

  （满足第一个条件后，不会在执行下去）

• 圆括号（()）：任何时候，只要 同时具有 多个 AND OR 两种操作符，都需要
  
添加圆括号明确的将操作符分组，消除歧义

  。

大于、等于、不等于 操作符

• 等于：
  
=


• 不等于：
  
<>、!=


• 小于：
  
<


• 大于：
  
>


• 小于等于：
  
<=


• 大于等于：
  
>=


关键字 操作符

• 
BETWEEN AND

  操作符，闭区间，是
  
大于等于 和 小于等于

  的合并。

SELECT id, `name`, sex
FROM information
WHERE id BETWEEN 2 AND 5;

SELECT id, `name`, sex
FROM information
WHERE id >= 2
  AND id <= 5;

• 
IN

  操作符 和
  
OR

  操作符，用来
  
指定条件范围

  ，OR 使用的是 全局搜索，时间久；而 IN 使用的是 匹配 搜索，时间短。

SELECT id, `name`, sex
FROM information
WHERE id IN (2, 6);

SELECT id, `name`, sex
FROM information
WHERE id = 2
   OR id = 6;

• 
NOT

  操作符：
  
否定其后所跟的条件

  ，相当于
  
取反

  。

SELECT id, `name`, sex
FROM information
WHERE id != 1;

SELECT id, `name`, sex
FROM information
WHERE NOT id = 1;

• 
LIKE

  操作符，模糊匹配。（耗时长，非必要不使用）

# 百分号(%) 通配符：% 表示任何字符（或字符串）出现任何次数（0~n）
# 下滑线(_) 通配符：_ 表示任何字符出现一次

-- 例如：查询 姓：胡 的用户；查询姓名中有：兴 的用户
SELECT id, `name`, sex
FROM information
WHERE `name` LIKE '胡%';

SELECT id, `name`, sex
FROM information
WHERE `name` LIKE '%兴%';


-- 例如：查询 姓名第二位为：好 的用户；查询姓名中只有一位的 用户
SELECT id, `name`, sex
FROM information
WHERE `name` LIKE '_好%';

SELECT id, `name`, sex
FROM information
WHERE `name` LIKE '_';

HAVING 关键字

• 
having 字句

  ：having 关键字 加上对应的判定 key-value 对。

• having 字句可以与 聚合函数 一起使用。

• 组级过滤。

• 故，使用到聚合函数时，用 having 来替换 where。

• where 是
  
先过滤再分组

  ，having 是
  
先分组再过滤

  。故，where 的效率高。

• 在使用上，基本没有不同。

DML（数据操纵语言）

INSERT 关键字

• 添加数据到
  
表

  中。

• 
推荐 在 insert 后加 into 关键字

  。

-- 插入的写法：INSERT INTO for1(column1, column2, ...) VALUES(value1, value2, ...)
-- 如果 所有列都插入，可以省略 column
INSERT INTO author
VALUES (3, "000", "111");

-- 插入多条数据
INSERT INTO author
VALUES (3, "000", "111"),
       (4, "000", "111");

UPDATE 关键字

• 更新
  
表中数据行

  。

• 在关键字 SET 后
  
添加了 对应列的 key=value 后，该列才更改

  。

• 更新的值是
  
以 column=value, column1=value1, ...，的形式来书写

  的。

• value 可以是 null（该字段允许为空）。

• 如果没有 条件，那么会更新所有行。

-- UPDATE 语句总是以要更新的表名开始。
UPDATE table_name
SET `name`= '兴'
WHERE id = 5;

DELETE 关键字

• 删除
  
表中数据行

  。

-- 删除整行：
DELETE
FROM table_name
WHERE id = 7;
-- 删除整张表的数据：
DELETE
FROM table_name;
TRUNCATE TABLE table_name; -- （更快）