MySQL关键语法总结（一）

半汀烟雨·碎菁寒

于 2022-11-06 21:57:22 发布

阅读量263

点赞数

文章标签： sql mysql

本文链接：https://blog.csdn.net/qq1059637081/article/details/127721944

版权

MySQL关键语法总结（一）

一、基本SELECT查询语句

1、`distinct`去重

SELECT DISTINCT last_name FROM employees;

SELECT DISTINCT department_id, salary
FROM employees;

注意：DISTINCT 其实是对后面所有列名的组合进行去重，你能看到最后的结果是 74 条，因为这 74 个部门id不同，都有 salary 这个属性值。如果你想要看都有哪些不同的部门（department_id），只需要写DISTINCT department_id即可，后面不需要再加其他的列名了。

2、所有运算符或列值遇到null值，运算的结果都为null

在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。

3、着重号：如果表中的字段、表名和保留字、数据库系统或常用方法冲突，加上着重号````````

SELECT * FROM `ORDER`;

4、`DESCRIBE`或`DESC`显示表结构

DESC employees;

5、等号运算符

注意：

如果等号两边都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。
如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。
<=>为安全等于运算符，两边的操作数的值都为NULL时，返回的结果为1而不是NULL。

# 查不到数据，为NULL
SELECT last_name, commission_pct FROM employees WHERE commission_pct = NULL;
# 这样查才能查到数据
SELECT last_name, commission_pct FROM employees WHERE commission_pct <=> NULL;
# 不过还是建议这样写
SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NULL;

6、其他运算符

最小值运算符：LEAST(VALUE1, VALUE2, ...)
最大值运算符：GREATEST(VALUE1, VALUE2, ...)
BETWEEN AND运算符。

SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500;

IN运算符：判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，或者IN列表中存在NULL，则结果为NULL。

# 这样是查不到NULL的数据的
SELECT last_name, commission_pct FROM employees WHERE commission_pct IN (0.10,0.15,NULL)

LIKE运算符：%匹配0个或多个，_只能匹配1个。注意如果给定的值或者匹配条件为NULL，则返回结果为NULL。
ESCAPE运算符：回避特殊符号使用转义符。

# 如果不用\转义符，想用$当转义符，就得加ESCAPE
SELECT job_id FROM jobs WHERE job_id LIKE 'IT$_%' ESCAPE '$';
# 如果用\当转义符，就不用加ESCAPE
SELECT job_id FROM jobs WHERE job_id LIKE 'IT\_%';

REGEXP运算符：expr REGEXP 正则表达式，正则表达式是一个相当复杂且强大的东西，放在一章单独讲。
逻辑运算符：NULL+逻辑运算符太抽象了记不住，这里只写容易错的。

OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。

NOT 1	NOT 0	NOT 2	NOT NULL
0	1	0	NULL
1AND -1	0 AND 1	0 AND NULL	1 AND NULL
1	0	0	NULL
1 OR 0	1 OR NULL	0 \|\| NULL	NULL \|\| NULL
1	1	NULL	NULL
1 XOR -1	1 XOR 0	0 XOR 0	1 XOR NULL
0	1	0	NULL

7、排序：ASC、DESC

SELECT employee_id, last_name, salary*12 annsal, hire_date
FROM employees
ORDER BY annsal DESC, hire_date ASC;

8、分页：LIMIT，LIMIT 子句必须放在整个SELECT语句的最后

# LIMIT [位置偏移量,] 行数
SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;

9、多表查询

连接 n个表，至少需要n-1个连接条件。比如，连接三个表，至少需要两个连接条件。

（1）等值连接与非等值连接

# 等值连接
SELECT employees.last_name, departments.department_name, locations.location_id
FROM employees, departments, locations
WHERE employees.department_id = departments.department_id
AND departments.location_id = locations.location_id;

# 非等值连接
SELECT employees.last_name, employees.salary
FROM employees, job_grades
WHERE employees.salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal;

（2）自连接与非自连接

# 自连接
SELECT CONCAT(worker.last_name, ' works for ', manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id;

（3）内连接和外连接

内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。
如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。
在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接，只能用 (+) 表示。然而MySQL并不支持（+）；
JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接
满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。而MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

# sql92
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.department_id = e.department_id
AND d.location_id = l.location_id;

# sql99
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;

# 左外连接
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

（4）自然连接：可以把自然连接理解为 SQL92 中的等值连接

自然连接一定是等值连接，但等值连接不一定是自然连接。等值连接不把重复的属性除去，而自然连接要把重复的属性除去。

# sql92
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

# sql99
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

（5）USING连接：

与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING的括号 () 中填入要指定的同名字段。同时使用 JOIN…USING 可以简化 JOIN ON 的等值连接。

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

10、UNION

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNIONALL关键字分隔。
UNION表示去重，UNION ALL不去重。

SELECT * FROM employeesWHERE email LIKE '&%a%' OR department_id > 90;
# 等价
SELECT * FROM employees WHERE email LIKE '&%a%'
UNION
SELECT * FROM employees WHERE department_id > 90;

请添加图片描述

比较容易混淆的：

# 107
SELECT COUNT(*) FROM employees;
# 27
SELECT COUNT(*) FROM departments;

# 等值连接：106
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

#自然连接：32
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e NATURAL JOIN departments d;

# USING连接：106
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e JOIN departments d
USING (department_id);

# 内连接：106
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e JOIN departments d
	ON e.department_id = d.department_id;

# 左连接：107
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e LEFT JOIN departments d
	ON e.department_id = d.department_id;

# 右连接：122
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e RIGHT JOIN departments d
	ON e.department_id = d.department_id;

二、流程控制

1、单行函数

单行函数大多数如RAND(x) SQRT(x) LENGTH(s) MD5(str) SHA(str)等，这类函数用多了查API即可，这里介绍 if else switch case 流程控制语句

函数	用法
IF(value,value1,value2)	if (value == true) return value1, else return vlaue2
IFNULL(value1, value2)	if (value1 != null) return value1, else return value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE 值n] END	if … else … if … else …
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值2 THEN 值2 … [ELSE 值n] END	swich… case …

SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪'
WHEN salary>=10000 THEN '潜力股'
WHEN salary>=8000 THEN '屌丝'
ELSE '草根' END "描述"
FROM employees;

SELECT department_id, department_name, manager_id, CASE location_id
	WHEN 1700 THEN
		'北京'
	WHEN 2500 THEN
		'上海'
	ELSE
		'杭州'
END '地点'
FROM departments;

2、聚合函数

（1）group by

在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中，包含在GROUP BY子句中的列不必包含在SELECT列表中

# group by
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

（2）having子句

使用条件：

行已经被分组。
使用了聚合函数。
满足HAVING子句中条件的分组将被显示。
HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。
不能在WHERE子句中使用聚合函数

# 1、用了group by和max()
# 2、max()还要被显示
# 3、不能在where里用max()
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

WHERE和HAVING的对比：

区别1：
- WHERE可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；
- HAVING必须要与GROUP BY配合使用，可以把分组计算的函数和分组字段作为筛选条件。
- 这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。
- 另外，WHERE排除的记录不再包括在分组中。
区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选
- 这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。
WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用 HAVING，普通条件用WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。

WHERE	先筛选数据再关联，执行效率高	不能使用分组中的计算函数进行筛选
HAVING	可以使用分组中的计算函数	在最后的结果集中进行筛选，执行效率较低

3、SELECT的执行过程

（1）查询的结构：

from：从哪些表中筛选
on：关联多表查询时，去除笛卡尔积
where：从表中筛选的条件
group by：分组依据
having：在统计结果中再次筛选
order by：排序
limit：分页

# 关键字顺序
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

（2）SELECT执行顺序

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7

（3）SELECT的执行原理

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。
SELECT 先执行 FROM 这一步。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：
- 首先先通过CROSS JOIN求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
- 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
- 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。
- 如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。
然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段 。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4 。
当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT阶段 。
首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1 和 vt5-2 。
当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段 ，得到虚拟表 vt6 。
最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段，得到最终的结果，对应的是虚拟表vt7 。

三、子查询

1、单行子查询

SELECT last_name
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
);

2、多行子查询

SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id)
	IN
			(SELECT manager_id, department_id
			FROM employees
			WHERE employee_id in (174,141))
AND employee_id NOT IN (174,141);

# 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > 
	(SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50);

# 显式员工的employee_id,last_name和location。
# 其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。
SELECT employee_id, last_name, 
	( CASE department_id
	WHEN (
	SELECT department_id
	FROM departments
	WHERE location_id = 1800
	) THEN
		'Canada'
	ELSE
		'USA'
END
	) location
FROM employees;

3、相关子查询

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

# 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
# 方式1：相关子查询
SELECT last_name, salary, department_id
FROM employees `outer1`
WHERE salary > (
		SELECT AVG(salary)
		FROM employees
		WHERE department_id = `outer1`.department_id
	);

# 方式2：在FROM中使用子查询
SELECT last_name, salary, e1.department_id
FROM employees e1,
	(SELECT department_id,AVG(salary) dept_avg_sal
	FROM employees 
	GROUP BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;

# 题目：查询员工的id,salary,按照department_name 排序
# 方式1：在order by中使用子查询
SELECT employee_id, salary
FROM employees e
ORDER BY (
	SELECT department_name
	FROM departments d
	WHERE e.department_id = d.department_id
);

# 方式2：连表查询
SELECT e.employee_id, e.salary
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
ORDER BY d.department_name;

四、数据库和表的增删改查

1、修改数据库

# 更改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等
# 删除指定的数据库
DROP DATABASE IF EXISTS 数据库名;

2、创建表

CREATE TABLE emp (
	emp_id INT,
	emp_name VARCHAR(20),
	salary DOUBLE,
	birthday DATE
);

CREATE TABLE dept(
	deptno INT AUTO_INCREMENT,
	dname VARCHAR(14),
    loc VARCHAR(13),
	# 主键
	PRIMARY KEY (deptno)
);

可以使用 AS subquery 选项，将创建表和插入数据结合起来：

CREATE TABLE IF NOT EXISTS dept80 
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

# 创建的emp2是空表
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2;

3、修改表

# 添加新列
ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
# 重命名一个列
ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;
# 删除列
ALTER TABLE 表名 DROP 【COLUMN】 字段名
# 重命名表
RENAME TABLE 表名1 RENAME TO 表名2;
# 删除表
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
# 清空表
TRUNCATE TABLE 表名;

# 添加列
ALTER TABLE dept80 ADD job_id varchar(15);
# 重命名列
ALTER TABLE dept80 CHANGE department_name dept_name varchar(15);
# 删除列
ALTER TABLE dept80 DROP COLUMN job_id;
# 重命名表
ALTER table dept80 RENAME TO detail_dept;
# 删除表
DROP TABLE IF EXISTS detail_dept;
# 清空表
TRUNCATE TABLE detail_dept;

注意：

DROP TABLE 语句不能回滚，数据和结构都被删除，所有正在运行的相关事务被提交，所有相关索引被删除。
TRUNCATE TABLE，删除表中所有的数据，释放表的存储空间，TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚。
TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。
在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即：DDL操作要么成功要么回滚。

4、插入数据

INSERT INTO 表名 VALUES (value1,value2,....);
INSERT INTO 表名(column1 [, column2, …, columnn]) VALUES (value1 [,value2, …, valuen]);

# 不必书写VALUES子句子查询中的值列表应与 INSERT 子句中的列名对应
INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

INSERT INTO departments VALUES (100, 'Finance', NULL, NULL);
INSERT INTO departments(department_id, department_name) VALUES (80, 'IT');

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

5、更新数据

UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]

# 如果没有where子句，则所有的数据都会被更新
UPDATE employees
SET department_id = 70
WHERE employee_id = 113;

6、删除数据

DELETE FROM table_name [WHERE <condition>];

# 如果省略 WHERE 子句，则表中的所有数据都将被删除。
DELETE FROM departments
WHERE department_name = 'Finance';

7、MySQL8新特性：计算列

CREATE TABLE tb1(
	id INT,
	a INT,
	b INT,
	c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

半汀烟雨·碎菁寒

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL关键语法总结（一）

MySQL语法关键总结1，总结MySQL基础增删改查语法
复制链接

扫一扫

MySQL关键语法总结（一）

MySQL关键语法总结（一）

一、基本SELECT查询语句

1、distinct去重

2、所有运算符或列值遇到null值，运算的结果都为null

3、着重号：如果表中的字段、表名和保留字、数据库系统或常用方法冲突，加上着重号````````

4、DESCRIBE或DESC显示表结构

5、等号运算符

6、其他运算符

7、排序：ASC、DESC

8、分页：LIMIT，LIMIT 子句必须放在整个SELECT语句的最后

9、多表查询

（1）等值连接与非等值连接

（2）自连接与非自连接

（3）内连接和外连接

（4）自然连接：可以把自然连接理解为 SQL92 中的等值连接

（5）USING连接：

10、UNION

二、流程控制

1、单行函数

2、聚合函数

（1）group by

（2）having子句

3、SELECT的执行过程

（1）查询的结构：

（2）SELECT执行顺序

（3）SELECT的执行原理

三、子查询

1、单行子查询

2、多行子查询

3、相关子查询

四、数据库和表的增删改查

1、修改数据库

2、创建表

3、修改表

4、插入数据

5、更新数据

6、删除数据

7、MySQL8新特性：计算列

“相关推荐”对你有帮助么？

1、`distinct`去重

4、`DESCRIBE`或`DESC`显示表结构