通用SQL语法

1. SQL语句可以单行或多行书写，以分号结尾。
2. SQL语句可以使用空格/缩进来增强语句的可读性。
3. MySOL数据库的SQL语句不区分大小写，关键字建议使用大写。
4. 注释：
   单行注释：--注释内容 或 #注释内容(MySQL特有)
   多行注释：/*注释内容*/

SQL分类

DDL-数据库操作

查询

查询所有数据库

show database;

查询当前数据库

select database();

创建

数据库创建语法。

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

创建数据库，以默认方式。

create database my_db;

创建数据库，如果存在就不创建。

create database if not exists my_db;

创建数据库指定数据库默认字符集。

create database my_test default charset utf8mb4;

删除

数据库删除语法。

DROP DATABASE [IF EXISTS] 数据库名;

删除数据库。

drop database my_test;

删除数据库，如果存在就删除。

drop database if exists my_test;

切换

数据库使用语法。

USE 数据库名;

使用数据库。

use my_db;

此时可以通过上述查看选择的数据库命令来查看当前选择的数据库。

select database();

DDL-表操作

查询

查询当前数据库所有表。

show tables;

这里可以在后面的创建表命令后操作。
查询表结构。

DESC 表名;

desc tb_user;

查询指定表的建表语句

SHOW CREATE TABLE 表名;

show create table tb_user;

创建表

基本语法。

CREATE TABLE 表名(
字段1 字段1类型[COMMENT 字段1注释],
字段2 字段2类型[COMMENT 字段2注释],
字段3 字段3类型[COMMENT 字段3注释],
...
字段n 字段n类型[COMMENT 字段n注释]
) [COMMENT 表注释]；

注意：最后一个字段没有逗号

创建一个用户表。

create table tb_user(id int comment '编号',name varchar(50) comment '姓名',age int comment '年龄',gender varchar(1) comment '性别') comment '用户表';

数据类型

数值类型

字符串类型

日期时间类型

案例：设计一张员工信息表，要求如下：
1.编号（纯数字)
2.员工工号（字符串类型，长度不超过10位）
3.员工姓名（字符串类型，长度不超过10位)
4.性别（男/女，存储一个汉字)
5.年龄（正常人年龄，不可能存储负数)
6.身份证号（二代身份证号均为18位，身份证中有X这样的字符)
7.入职时间（取值年月日即可）

创建表SQL

create table emp(id int comment '编号',work_no varchar(10) comment '工号',name varchar(10) comment '姓名',gender char(1) comment '性别',age tinyint unsigned comment '年龄',id_card char(18) comment '身份证号',entry_date date comment '入职时间'
)comment '员工表';

查看表结构

desc emp;

添加字段

添加字段语法格式。

ALTER TABLE 表名 ADD 字段名类型(长度) [COMMENT 注释] [约束];

案例：为emp表增加一个新的字段”呢称”为nickname,类型为varchar(20)

alter table emp add nickname varchar(20) comment '昵称';

查看表。

desc emp;

修改字段

修改数据类型

ALTER TABLE 表名 MODIFY 字段名新数据类型(长度);

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];

案例：将emp表的nickname字段修改为username,类型为varchar(30)

alter table emp change nickname username varchar(30) comment '用户名';

查看表。

desc emp;

删除字段

删除字段

ALTER TABLE 表名 DROP 字段名;

案例：将emp表的字段username删除

alter table emp drop username;

查看表。

desc emp;

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

案例：将emp表的表名修改为employee

alter table emp rename to employee;

查看数据库中表。

show tables;

删除表

删除表

DROP TABLE [IF EXISTS] 表名;

案例：删除用户表。

drop table if exists tb_user;

删除指定表，并重新创建该表。（清空表中数据）

TRUNCATE TABLE 表名;

清空员工表中的数据。

truncate table employee;

DML

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增删改操作。

添加数据

给指定字段添加数据

INSERT INTO 表名 (字段名1,字段名2,...) VALUES(值1,值2,...);

给全部字段添加数据

INSERT INTO 表名 VALUES(值1,值2,...);

批量添加数据

INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);

INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);

注意：

• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
• 字符串和日期型数据应该包含在引号中。
• 插入的数据大小，应该在字段的规定范围内。

案例：给员工表插入一条记录。

insert into employee(id, work_no, name, gender, age, id_card, entry_date)
values (1,'1','张三','男',23,'510145200011225413','2011-11-26')

或者

insert into employee values(4, '4', '赵六', '男', 24, '510145200011225413', '2011-10-24');

插入多条记录

insert into employee
values (5, '5', '赵六', '男', 24, '510145200011255413', '2015-10-25'),(6, '6', '赵六', '男', 24, '510145200011265413', '2016-10-26'),(7, '7', '赵六', '男', 24, '510145200011275413', '2017-10-27');

修改数据

UPDATE 表名 SET 字段名1=值1,字段名2=值2,...[WHERE 条件];

注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

案例：
修改id为1的数据，将name修改为"张小明"

update employee set name ='张小明' where id = 1;

修改id为1的数据，将name修改为"小昭"，gender修改为"女"

update employee set name ='小昭',gender='女' where id = 1;

将所有的员工入职日期修改为"2008-01-01"

update employee set entry_date = '2008-01-01';

注意：若使用DataGrip执行上述SQL报错Unsafe query: 'Update' statement without 'where' updates all table rows at once，这是DataGrip的安全检查，这时我们点击Execute即可。

若点击后报错You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.，这是由于mysql开启了安全检查。
查看sql_safe_updates是否开启。

show variables like 'sql_safe_updates';

可以看见当前已启用，这时可以使用以下语句进行修改。

-- 会话级别（0表示关闭，1表示开启）
set sql_safe_updates=0;-- 全局（0表示关闭，1表示开启）
set global sql_safe_updates=0;

这时修改后再次尝试，就不会报错了。

删除数据

DELETE FROM 表名 [WHERE 条件];

注意：

• DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• DELETE语句不能删除某一个字段的值（可以使用UPDATE)。

这里测试操作，建议先备份原始数据。不需要备份，可以直接跳转到案例。

DataGrip备份数据

选择自己需要的，我这里选择insert。


我这里复制存储备份的数据。

INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (1, '1', '张小明', '男', 23, '510145200011225413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (2, '2', '李四', '男', 24, '510145200011225413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (3, '3', '王五', '男', 24, '510145200011225413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (4, '4', '赵六', '男', 24, '510145200011225413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (5, '5', '赵六', '男', 24, '510145200011255413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (6, '6', '赵六', '男', 24, '510145200011265413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (7, '7', '赵六', '男', 24, '510145200011275413', '2008-01-01');

案例：
删除gender为"女"的员工

delete from employee where gender = '女';

删除所有员工

delete from employee;

此时若出现问题，解决方法同上。

DQL

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

基本语法

SELECT字段列表
FROM表名列表
WHERE条件列表
GROUP BY分组字段列表
HAVING分组后条件列表
ORDER BY排序字段列表
LIMIT分页参数

基本查询

查询多个字段

-- 查询指定字段
SELECT 字段1,字段2,字段3... FROM 表名;-- 查询所有字段
SELECT * FROM 表名;

设置别名

SELECT 字段1 [AS 别名1],字段2 [AS 别名2]... FROM 表名;

去除重复记录

SELECT DISTINCT字段列表FROM表名；

案例：
准备查询数据。

drop table if exists employee;
create table employee
(id           int comment '编号',work_no      varchar(10) comment '工号',name         varchar(10) comment '姓名',gender       char(1) comment '性别',age          tinyint unsigned comment '年龄',id_card      char(18) comment '身份证号',work_address varchar(50) comment '工作地址',entry_date   date comment '入职时间'
) comment '员工表';INSERT INTO employee (id, work_no, name, gender, age, id_card, work_address, entry_date)
values (1, '1', '柳岩', '女', 20, '123456789012345678', '北京', '2000-01-01'),(2, '2', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01'),(3, '3', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01'),(4, '4', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01'),(5, '5', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01'),(6, '6', '杨道', '男', 28, '12345678931234567X', '北京', '2006-01-01'),(7, '7', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01'),(8, '8', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01'),(9, '9', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01'),(10, '10', '陈友凉', '男', 53, '123456789012345670', '上海', '2011-01-01'),(11, '11', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01'),(12, '12', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01'),(13, '13', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01'),(14, '14', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01'),(15, '15', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01'),(16, '16', '周芷若', '女', 18, null, '北京', '2012-06-01');

基本查询
1.查询指定字段name,work_no,age

select name,work_no,age from employee;

2.查询所有字段

select * from  employee;

3.查询所有员工的工作地址，起别名

-- as关键字可以省略
select work_address as '工作地址' from employee;

4.查询公司员工的上班地址（不要重复）

select distinct work_address from employee;

条件查询(WHERE)

基本语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

条件–>运算符

案例：

查询年龄等于88的员工

select * from employee where age = 88;

查询年龄小于20的员工信息

select * from employee where age < 20;

查询年龄小于等于20的员工信息

select * from employee where age <= 20;

查询没有身份证号的员工信息

select * from employee where id_card is null;

查询有身份证号的员工信息

select * from employee where id_card is not null;

查询年龄不等于88的员工信息

select * from employee where age != 88;
或者
select * from employee where age <> 88;

查询年龄在15岁（包含）到20岁（包含）之间的员工信息

select * from employee where age >= 15 && age <= 20;
或者
select * from employee where age >= 15 and age <= 20;
或者
select * from employee where age between 15 and 20;

查询性别为女且年龄小于25岁的员工信息

select * from employee where gender = '女' && age < 25;

查询年龄等于18或20或40的员工信息

select * from employee where age = 18 or age = 20 or age = 40;
或者
select * from employee where age in(18, 20, 40);

查询姓名为两个字的员工信息

select * from employee where name like '__';

查询身份证号最后一位是X的员工信息

select * from employee where id_card like '_________________X';
或者
select * from employee where id_card like '%X';

聚合函数(count、max、min、avg、sum)

将一列数据作为一个整体，进行纵向计算。

常见聚合函数

基本语法

SELECT 聚合函数(字段列表) FROM 表名;

注意：所有的聚合函数，null不参与计算。

案例：
统计该企业员工数量

select count(id) from employee;

统计该企业员工工作地址总数量

-- 用distinct关键字去除重复的工作地址
select count(distinct work_address) from employee;

统计该企业员工的平均年龄

select avg(age) from employee;

统计该企业员工的最大年龄

select max(age) from employee;

统计该企业员工的最小年龄

select min(age) from employee;

统计西安地区员工的年龄之和

select sum(age) from employee where work_address  = '西安';

分组查询(GROUP BY)

基本语法

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

where与having区别

• 执行时机不同：where是分组之前进行过滤，不满足where:条件，不参与分组；而naving是分组之后对结果进行过滤。
• 判断条件不同：where.不能对聚合函数进行判断，而having可以。

注意

• 执行顺序：where>聚合函数>having。
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

案例：
根据性别分组，统计男性员工和女性员工的数量

select gender,count(gender) '数量' from employee group by gender;

根据性别分组，统计男性员工和女性员工的平均年龄

select gender,avg(age) '平均年龄' from employee group by gender;

查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址

select work_address,count(work_address) from employee where age < 45 group by work_address having count(work_address) >= 3 ;

排序查询(ORDER BY)

语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2,...;

排序方式

• ASC：升序（默认值）
• DESC：降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

案例：
根据年龄对公司的员工进行升序排序

-- asc可以省略，默认就是升序
select * from employee order by age asc ;

根据入职时间，对员工进行降序排序

select * from employee order by entry_date desc ;

根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序

select * from employee order by age asc, entry_date desc;

分页查询(LIMIT)

基本语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

注意：

• 起始索引从0开始，起始索引 =（查询页码 - 1）* 每页显示记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是 LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

案例：
查询第1页员工数据，每页展示10条记录

select * from employee limit 0, 10;
或者
-- 查询结果是第一页数据，可以省略起始索引
select * from employee limit 10;

查询第2页员工数据，每页展示10条记录

select * from employee limit 10, 10;

DQL综合案例

查询年龄为20,21,22,23岁的女性员工信息。

select * from employee where gender = '女' and age in(20,21,22,23);

查询性别为男，并且年龄在20-40岁（含）以内的姓名为三个字的员工。

select * from employee where gender = '男' and age between 20 and 40 and name like '___';

统计员工表中，年龄小于60岁的，男性员工和女性员工的人数。

select gender,count(gender) '员工人数'  from employee where age < 60 group by gender;

查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。

select name,age from employee where age <= 35 order by age,entry_date desc;

查询性别为男，且年龄在20-40岁（含）以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。

select * from employee where gender = '男' and age between 20 and 40 order by age,entry_date limit 5;

DCL

DCL英文全称是Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。

管理用户

查询用户

USE mysql;
SELECT FROM user;

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

删除用户

DROP USER '用户名'@'主机名';

注意：

• 主机名可以使用%通配。
• 这类SQL开发人员操作的比较少，主要是DBA(Database Administrator 数据库管理员)使用。

权限控制

MySQL中定义了很多种权限，但是常用的就以下几种

查询权限

SHOW GRANTS FOR '用户名'@'主机名';

授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意：

• 多个权限之间，使用逗号分隔
• 授权时，数据库名和表名可以使用 * 进行通配，代表所有。