MySQL基本语法

一、DDL（Data Definition Languages、数据定义语言）

1、创建和管理数据库

1.1 创建数据库

方式1：创建数据库

CREATE DATABASE 数据库名;

方式2：创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

方式3：判断数据库是否已经存在，不存在则创建数据库（ 推荐 ）

CREATE DATABASE IF NOT EXISTS 数据库名;

如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。

注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删 旧库完成的。

1.2 使用数据库

查看当前所有的数据库

SHOW DATABASES;

查看当前正在使用的数据库

SELECT DATABASE();

查看指定库下所有的表

SHOW TABLES FROM 数据库名;

查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;

使用/切换数据库

USE 数据库名;

2.3修改数据库

更改数据库字符集

ALTER DATABASE 数据库名 CHARACTER SET 字符集;

方式1：删除指定的数据库

DROP DATABASE 数据库名;

方式2：删除指定的数据库（推荐）

DROP DATABASE IF EXISTS 数据库名;

2、创建和管理表

2.1创建表

CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);

加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表； 如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

创建表举例

CREATE TABLE dept(
-- int类型，自增
deptno INT AUTO_INCREMENT,
dname VARCHAR(14),
loc VARCHAR(13),
-- 主键
PRIMARY KEY (deptno)
);

查看表结构

DESC emp;

创建方式2：

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

查看表结构2：

SHOW CREATE TABLE 表名;

2.2修改表

追加一列

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】

举例：

ALTER TABLE dept80
ADD job_id varchar(15);

修改一列

ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名
2】;

举例：

ALTER TABLE dept80
MODIFY last_name VARCHAR(30);

重命名一列

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

举例：

ALTER TABLE dept80
CHANGE department_name dept_name varchar(15);

删除一列

ALTER TABLE 表名 DROP 【COLUMN】字段名

举例：

ALTER TABLE dept80
DROP COLUMN job_id;

2.3重命名表

方式1：使用RENAME

RENAME TABLE emp
TO myemp;

方式2：

ALTER table dept
RENAME [TO] detail_dept; -- [TO]可以省略

2.4重命名表

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

举例：

DROP TABLE dept80;

2.5清空表

TRUNCATE TABLE detail_dept;

TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚

DELETE FROM emp2;

二、DML（Data Manipulation Language、数据操作语言）

1、新增一条数据

1.1 方式1：VALUES的方式添加

情况1：为表的所有字段按默认顺序插入数据

INSERT INTO 表名
VALUES (value1,value2,....);

值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

举例：

INSERT INTO departments
VALUES (70, 'Pub', 100, 1700);

情况2：为表的指定字段插入数据

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

为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的 默认值。

在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,…valuen需要与 column1,…columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误。

情况3：同时插入多条记录

INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔 开，基本语法格式如下：

INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

或者

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

举例：

INSERT INTO emp(emp_id,emp_name)
VALUES (1001,'shkstart'),
       (1002,'atguigu'),
       (1003,'Tom');

• 使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含 义如下： Records：表明插入的记录条数。
• Duplicates：表明插入时被忽略的记录，原因可能是这 些记录包含了重复的主键值。
• Warnings：表明有问题的数据值，例如发生数据类型转换。

一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句 在处理过程中 效率更高 。因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句 快，所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。

小结：

• VALUES 也可以写成 VALUE ，但是VALUES是标准写法。
• 字符和日期型数据应包含在单引号中。

1.2 方式2：将查询结果插入到表中

INSERT还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。

基本语法格式如下：

INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

• 在 INSERT 语句中加入子查询。
• 不必书写 VALUES 子句。
• 子查询中的值列表应与 INSERT 子句中的列名对应。

举例：

INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;

2、删除一条数据

使用DELETE语句从表中删除数据

DELETE FROM table_name [WHERE <condition>];

table_name指定要执行删除操作的表；“[WHERE ]”为可选参数，指定删除条件，如果没有WHERE子句， DELETE语句将删除表中的所有记录。

举例：

DELETE FROM departments
WHERE department_name = 'Finance';

如果省略 WHERE 子句，则表中的全部数据将被删除

DELETE FROM copy_emp;

3、修改一条数据

使用UPDATE语句更新数据。语法如下：

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

可以一次更新多条数据。

如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;

举例：

UPDATE employees
SET department_id = 70
WHERE employee_id = 113;

如果省略 WHERE 子句，则表中的所有数据都将被更新。

UPDATE copy_emp
SET department_id = 110;

4、查询数据

4.1 基本的SELECT语句

SELECT…

SELECT 1; #没有任何子句
SELECT 9/2; #没有任何子句

SELECT … FROM

语法：

SELECT 标识选择哪些列
FROM 标识从哪个表中选择

选择全部列：

SELECT *
FROM departments;

一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符‘*’。使用通配符虽然可以节 省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。 在生产环境下，不推荐你直接使用 SELECT * 进行查询。

选择特定列：

SELECT department_id, location_id
FROM departments;

MySQL中的SQL语句是不区分大小写的，因此SELECT和select的作用是相同的，但是，许多开发人员习惯将关键字大写、数据列和表名小写，读者也应该养成一个良好的编程习惯，这样写出来的代 码更容易阅读和维护。

列的别名

• 重命名一个列
• 便于计算
• 紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特 殊的字符并区分大小写。
• AS 可以省略
• 建议别名简短，见名知意

举例

SELECT last_name AS name, commission_pct comm
FROM employees;

在SELECT语句中使用关键字DISTINCT去除重复行

SELECT DISTINCT department_id
FROM employees;

1. DISTINCT 需要放到所有列名的前面，如果写成 SELECT salary, DISTINCT department_id FROM employees 会报错。
2. DISTINCT 其实是对后面所有列名的组合进行去重.

空值参与运算

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

SELECT employee_id,salary,commission_pct,
12 * salary * (1 + commission_pct) "annual_sal"
FROM employees;

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

着重号

错误的：

SELECT * FROM order;

正确的：

SELECT * FROM `order`;

我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在 SQL语句中使用一对``（着重号）引起来。

查询常数

SELECT 查询还可以对常数进行查询。对的，就是在 SELECT 查询结果中增加一列固定的常数列。这列的 取值是我们指定的，而不是从数据表中动态取出的。

你可能会问为什么我们还要对常数进行查询呢？

SQL 中的 SELECT 语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个 固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。

比如说，我们想对 employees 数据表中的员工姓名进行查询，同时增加一列字段 corporation ，这个 字段固定值为“火星”，可以这样写：

SELECT '火星' as corporation, last_name FROM employees;

显示表结构

使用DESCRIBE 或 DESC 命令，表示表结构。

DESCRIBE employees;
或
DESC employees;

mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id | int(6) | NO | PRI | 0 | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| email | varchar(25) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
| job_id | varchar(10) | NO | MUL | NULL | |
| salary | double(8,2) | YES | | NULL | |
| commission_pct | double(2,2) | YES | | NULL | |
| manager_id | int(6) | YES | MUL | NULL | |
| department_id | int(4) | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

其中，各个字段的含义分别解释如下：

• Field：表示字段名称。
• Type：表示字段类型，这里 barcode、goodsname 是文本型的，price 是整数类型的。
• Null：表示该列是否可以存储NULL值。
• Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一 部分；MUL表示在列中某个给定值允许出现多次。
• Default：表示该列是否有默认值，如果有，那么值是多少。
• Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

过滤数据

语法：

SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件

举例：

SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90;

4.2 运算符

算术运算符

运算符	名称	作用	示例
+	加法运算符	计算两个值或表达式的和	SELECT A+B
-	减法运算符	计算两个值或表达式的差	SELECT A-B
*	乘法运算符	计算两个值或表达式的乘积	SELECT A*B
/或DIV	除法运算符	计算两个值或表达式的商	SELECT A/B或者SELECT A DIV B
%或MOD	求模（求余）运算符	计算两个值或表达式的余数	SELECT A%B或者SELECT A MOD B

比较运算符

运算符	名称	作用	示例
=	等于运算符	判断两个值、字符串或表达式是否相等	SELECT C FROM TABLE WHERE A=B
<=>	安全等于运算符	安全地判断两个值、字符串或表达式是否相等	SELECT C FROM TABLE WHERE A<=>B
<>(!=)	不等于运算符	判断两个值、字符串或表达式是否不相等	SELECT C FROM TABLE WHERE A<>B SELECT C FROM TABLE WHERE A!=B
<	小于运算符	判断前面的值、字符串或表达式是否小于后面的值、字符串或表达式	SELECT C FROM TABLE WHERE A<B
<=	小于等于运算符	判断前面的值、字符串或表达式是否小于等于后面的值、字符串或表达式	SELECT C FROM TABLE WHERE A<=B
>	大于运算符	判断前面的值、字符串或表达式是否大于后面的值、字符串或表达式	SELECT C FROM TABLE WHERE A>B
>=	大于等于运算符	判断前面的值、字符串或表达式是否大于等于后面的值、字符串或表达式	SELECT C FROM TABLE WHERE A>=B

此外，还有非符号类型的运算符：

运算符	名称	作用	示例
IS NULL	为空运算符	判断值、字符串或表达式是否为空	SELECT B FROM TABLE WHERE A IS NULL
IS NOT NULL	不为空运算符	判断值、字符串或表达式是否不为空	SELECT B FROM TABLE WHERE A IS NOT NULL
LEAST	最小值运算符	在多个值中返回最小值	SELECT LEAST(A,B) FROM TABLE
GREATEST	最大值运算符	在多个值中返回最大值	SELECT GREATEST(A,B) FROM TABLE
BETWEEN AND	两值之间的运算符	判断一个值是否在两个值之间	SELECT D FROM TABLE WHERE C BETWEEN A AND B
ISNULL	为空运算符	判断一个值、字符串或表达式是否为空	SELECT B FROM TABLE WHERE A ISNULL
IN	属于运算符	判断一个值是否为列表中的任意一个值	SELECT D FROM TABLE WHERE C IN (A,B)
NOT IN	不属于运算符	判断一个值是否不是一个列表中的任意一个值	SELECT D FROM TABLE WHERE C NOT IN (A,B)
LIKE	模糊匹配运算符	判断一个值是否符合模糊匹配规则	SELECT C FROM TABLE WHERE A LIKE B
REGEXP	正则表达式运算符	判断一个值是否符合正则表达式的规则	SELECT C FROM TABLE WHERE A REGEXP B
RLIKE	正则表达式运算符	判断一个值是否符合正则表达式的规则	SELECT C FROM TABLE WHERE A RLIKE B

逻辑运算符

运算符	作用	示例
NOT 或 !	逻辑非	SELECT NOT A
AND 或 &&	逻辑与	SELECT A AND B SELECT A && B
OR 或 ||	逻辑或	SELECT A OR B SELECT A || B
XOR	逻辑异或	SELECT A XOR B

位运算符

运算符	作用	示例
&	按位与（位AND）	SELECT A & B
|	按位或（位OR）	SELECT A | B
^	按位异或（位XOR）	SELECT A ^ B
~	按位取反	SELECT ~ A
>>	按位右移	SELECT A >> 2
<<	按位左移	SELECT A << 2

运算符的优先级

优先级	运算符
1	:=，=（赋值）
2	||，OR，XOR
3	&&，AND
4	NOT
5	BETWEEN，CASE，WHEN，THEN，ELSE
6	=（比较运算符），<=>，>=，>，<，<>，!=，IS，LIKE，REGEXP，IN
7
8	&
9	<<与>>
10	-和+
11	*，/，DIV，%，MOD
12	^
13	-（负号）和 ~（按位取反）
14	!
15	()

数字编号越大，优先级越高，优先级高的运算符先进行计算。可以看到，赋值运算符的优先级最低，使 用“()”括起来的表达式的优先级最高。

4.3 排序与分页

排序规则

• 使用 ORDER BY 子句排序
• ASC（ascend）: 升序
• DESC（descend）:降序
• ORDER BY 子句在SELECT语句的结尾。

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;

默认是升序

多列排序：

SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;

分页

格式：

LIMIT [位置偏移量,] 行数

第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是 1，以此类推）；第二个参数“行数”指示返回的记录条数。

*分页显式公式：（当前页数-1）每页条数，每页条数

SELECT * FROM table
LIMIT(PageNo - 1)*PageSize,PageSize;

4.4 多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个 关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进 行关联。

基本语法

使用JOIN…ON子句创建连接的语法结构：

SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件

• 内连接(INNER JOIN):合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
• 外连接(OUTER JOIN):两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的 行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)
• 左外连接(LEFT JOIN)
• 右外连接(RIGHT JOIN)

UNION的使用

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

语法格式：

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

4.5 聚合函数

聚合函数类型

• AVG()
• SUM()
• MAX()
• MIN()
• COUNT()

GROUP BY

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

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

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

GROUP BY中使用WITH ROLLUP

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所 有记录的总和，即统计记录数量。

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

注意： 当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥 的。

HAVING

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

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

4.6 子查询

单行子查询

SELECT last_name, job_id, salary
FROM employees
WHERE job_id =
		(SELECT job_id
		FROM employees
		WHERE employee_id = 141)
AND salary >
		(SELECT salary
		FROM employees
		WHERE employee_id = 143)
;

多行子查询

操作符	含义
IN	等于列表中的任意一个
ANY	需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME	实际上是ANY的别名，作用相同，一般常使用ANY

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
							SELECT AVG(salary) avg_sal
							FROM employees
							GROUP BY department_id
    						）

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

主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。