SQL通用语法
- SQL 语句可以单行或多行书写,以分号结尾。
- SQL 语句可以使用空格/缩进 来增强语句的可读性。
- MySQL 数据库的SQL语句不区分大小写,关键字建议使用大写。
- 注释:
- 单行注释:-- 注释内容 或 # 注释内容(MySQL特有)
- 多行注释:/* 注释内容 */
SQL分类
分类 | 全称 | 说明 |
---|---|---|
DDL | Data Definition Language | 数据定义语言,用来定义数据库对象(数据库,表,字段) |
DML | Data Manipulation Language | 数据操作语言,用来对数据库表中的数据进行增删改 |
DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 |
DCL | Data Control Language | 数据控制语言,用来创建数据库用户、控制数据库的访问权限 |
DDL
DDL - 数据库操作
-
查询
查询所有数据库
SHOW DATABASES;
查询当前数据库
SELECT DATABASE();
-
创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
IF NOT EXISTS :如果这个数据库名称不存在则创建,存在则不执行任何操作
-
删除
DROP DATABASE [IF EXISTS] 数据库名;
IF EXISTS:如果存在则删除,不存在则不执行任何操作
-
使用
USE 数据库名;
DDL - 表操作
-
查询当前数据库所有表
SHOW TABLES;
-
查询表结构
DESC 表名;
-
查询指定表的建表语句
SHOW CREATE TABLE 表名;
DDL - 表操作 - 创建
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT 字段1注释],
字段2 字段2类型 [COMMENT 字段2注释],
字段3 字段3类型 [COMMENT 字段3注释],
......
字段n 字段n类型 [COMMENT 字段n注释]
) [COMMENT 表注释];
例:
DDL - 表操作 - 修改
-
添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
案例:
为emp表增加一个新的字段 “昵称” 为 nickname,类型为varchar(20)
ALTER TABLE emp ADD nickname varchar(20) comment 昵称;
-
修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
-
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
案例:
将emp表的nickname字段修改为username,类型为varchar(30)
ALTER TABLE emp change nickname username varchar(30);
-
删除字段
ALTER TABLE 表名 DROP 字段名;
案例:
将emp表的字段username 删除
ALTER TABLE emp drop username;
-
修改表名
ALTER TABLE 表名 RENAME TO 新表名;
案例:
将emp表的表名修改为employee
ALTER TABLE emp RENAME TO employee;
DDL - 表操作 - 删除
-
删除表
DROP TABLE [IF EXISTS] 表名;
-
删除指定表,并重新创建该表
TRUNCATE TABLE 表名;
注意:在删除表时,表中的全部数据也会被删除。
DML
DML,用来对数据库中表的数据记录进行增删改操作。
- 添加数据(INSERT)
- 修改数据(UPDATE)
- 删除数据(DELETE)
DML - 添加数据
-
给指定字段添加数据
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,...);
注意:
- 插入数据时,指定的字段顺序需要与值的顺序是一一对应的。
- 字符串和日期型数据应该包含在引号中。
- 插入的数据大小,应该在字段的规定范围内。
DML - 修改数据
UPDATE 表名 SET 字段名1 = 值1,字段名2 = 值2,... [WHERE 条件];
注意:修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
DML - 删除数据
DELETE FROM 表名 [WHERE 条件];
注意:
- DELETE 语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据。
- DELETE 语句不能删除某一字段的值(可以使用UPDATE)。
DQL
DQL,数据查询语言,用来查询数据库中表的记录。
DQL - 语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
DQL - 基本查询
-
查询多个字段
SELECT 字段1,字段2,字段3 ... FROM 表名; SELECT * FROM 表名;
-
设置别名
SELECT 字段1 [AS 别名1],字段2 [AS 别名2] ... FROM 表名;
-
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
DQL - 条件查询
-
语法
select 字段列表 from 表名 where 条件列表;
-
条件
比较运算符 功能 > 大于 >= 大于等于 < 小于 <= 小于等于 = 等于 <> 或 != 不等于 between … and … 在某个范围之内(含最小、最大值) in(…) 在in之后的列表中的值,多选一 like 占位符 模糊匹配( _
匹配单个字符,%
匹配任意个字符)is null 是null and 或 && 并且(多个条件同时成立) or 或 || 或者(多个条件任意一个成立) not 或 ! 非,不是 -
条件查询案例
-
查询年龄等于 28 的员工信息
select * from emp where age = 28;
-
查询年龄小于 35 的员工信息
select * from emp where age < 35;
-
查询年龄小于等于 20 的员工信息
select * from emp where age <= 20;
-
查询没有身份证号的员工信息
select * from emp where idcard is null;
-
查询有身份证号的员工信息
select * from emp where idcard is not null;
-
查询年龄不等于 38 的员工信息
select * from emp where age != 38; select * from emp where age <> 38;
-
查询年龄在20岁(包含) 到 35岁(包含)之间的员工信息
select * from emp where age >= 20 and age <= 35; select * from emp where age >= 20 && age <= 35; select * from emp where age between 20 and 35;
-
查询性别为 女 且年龄小于 25岁的员工信息
select * from emp where gender = '女' and age < 25;
-
查询年龄等于18 或 20 或 40 的员工信息
select * from emp where age = 18 or age = 20 or age = 40; select * from emp where age in (18,20,40);
-
查询姓名为两个字的员工信息
select * from emp where name like '__';
-
查询身份证号最后一位是X的员工信息
select * from emp where idcard like '%X';
-
DQL - 聚合函数
-
介绍
将一列数据作为一个整体,进行纵向计算。
-
常见聚合函数
函数 功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和 -
语法
select 聚合函数(字段列表) from 表名;
注意:null 值不参与所有聚合函数运算。
-
聚合函数案例
-
统计该企业员工数量
select count(*) from emp;
-
统计该企业员工的平均年龄
select avg(age) from emp;
-
统计该企业员工的最大年龄
select max(age) from emp;
-
统计该企业员工的最小年龄
select min(age) from emp;
-
统计西安地区员工的年龄之和
select sum(age) from emp where workaddress = '西安';
-
DQL - 分组查询
-
语法
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
-
where 与 having 区别
- 执行时机不同:where 是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
注意:
- 执行顺序:where > 聚合函数 > having
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
-
分组查询案例
-
根据性别分组,统计男性员工 和 女性员工的数量
select gender, count(*) from emp group by gender;
-
根据性别分组,统计男性员工 和 女性员工的平均年龄
select gender, avg(age) from emp group by gender;
-
查询年龄小于 45 的员工,并根据工作地址分组 获取员工数量大于等于3的工作地址
select workaddress, count(*) as address_count from emp where age < 45 group by workaddress having count(*) >= 3;
-
DQL - 排序查询
-
语法
select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;
-
排序方式
- ASC:升序(默认值)
- DESC:降序
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
-
排序查询案例
-
根据年龄对公司的员工进行升序排序
select * from emp order by age asc;
-
根据入职时间,对员工进行降序排序
select * from emp order by entrydate desc;
-
根据年龄对公司的员工进行升序排序,年龄相同,再按照入职时间进行降序排序
select * from emp order by age asc, entrydate desc;
-
DQL - 分页查询
-
语法
select 字段列表 from 表名 limit 起始索引, 查询记录数;
注意:
- 其实索引从0开始,起始索引= (查询页码 - 1)* 每页显示记录数。
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是limit。
- 如果查询的是第一页数据,起始索引可以省略,直接简写为
limit 查询记录数
。如limit 10
-
分页查询案例
-
查询第1页员工数据,每页展示10条记录
select * from emp limit 10; select * from emp limit 0, 10;
-
查询第2页员工数据,每页展示10条记录
select * from emp limit 1, 10;
-
DQL - 执行顺序
DCL
数据控制语言,用来管理数据库 用户、控制数据库的访问 权限。
DCL - 管理用户
-
查询用户
use mysql; select * from user;
-
创建用户
create user '用户名'@'主机名' identified by '密码';
-
修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
-
删除用户
drop user '用户名'@'主机名';
注意:
- 主机名可以使用 % 通配。
- 这类SQL开发人员操作的比较少,主要是DBA(数据库管理员)使用。
DCL - 权限控制
MySQL 中定义了很多种权限,但是常见的就以下几种:
权限 | 说明 |
---|---|
all,all privileges | 所有权限 |
select | 查询数据 |
insert | 插入数据 |
update | 修改数据 |
delete | 删除数据 |
alter | 修改表 |
drop | 删除数据库/表/视图 |
create | 创建数据库/表 |
其他权限描述及含义,可以直接参考官方文档。
-
语法
-
查询权限
show grants for '用户名'@'主机名';
-
授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
-
撤销权限
revoke 权限列表 on 数据库.表名 from '用户名'@'主机名';
注意:
- 多个权限之间,使用逗号分隔。
- 授权时,数据库名和表名可以使用 * 进行通配,代表所有。
-
函数
函数 是指一段可以直接被另一段程序调用的程序或代码。
字符串函数
MySQL 中内置了很多字符串函数,常用的几个如下:
函数 | 功能 |
---|---|
concat(S1, S2, …, Sn) | 字符串拼接,将S1,S2,…,Sn 拼接成一个字符串 |
lower(str) | 将字符串str全部转为小写 |
upper(str) | 将字符串str全部转为大写 |
lpad(str, n, pad) | 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度 |
rpad(str, n, pad) | 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度 |
trim(str) | 去掉字符串头部和尾部的空格 |
substring(str, start, len) | 返回从字符串str从start位置起的len个长度的字符串 |
select 函数(参数);
数值函数
常见的数值函数如下:
函数 | 功能 |
---|---|
ceil(x) | 向上取整 |
floor(x) | 向下取整 |
mod(x, y) | 返回x/y的模 |
rand() | 返回0~1内的随机数 |
round(x, y) | 求参数x的四舍五入的值,保留y位小数 |
日期函数
常见的日期函数如下:
函数 | 功能 |
---|---|
curdate() | 返回当前日期 |
curtime() | 返回当前时间 |
now() | 返回当前日期和时间 |
year(date) | 获取指定date的年份 |
month(date) | 获取指定date的月份 |
day(date) | 获取指定date的日期 |
date_add(date, interval expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
datediff(date1, date2) | 返回起始时间date1 和 结束时间date2 之间的天数 |
案例:
-
查询所有员工的入职天数,并根据入职天数倒序排序
select name, datediff(curdate(), entrydate) as entrydays from emp order by entrydays desc;
流程函数
流程函数也是很常用的一类函数,可以在SQL语句中实现条件筛选,从而提高语句的效率。
函数 | 功能 |
---|---|
if(value, t, f) | 如果value为true,则返回t,否则返回f |
ifnull(value1, value2) | 如果value1不为空,返回value1,否则返回value2 |
case when [val1] then [res1] … else [default] end | 如果val1为true,返回res1,… 否则返回default默认值 |
case [expr] when [val1] then [res1] … else [default] end | 如果expr的值等于val1,返回res1,… 否则返回default默认值 |
约束
概述
-
概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据。
-
目的:保证数据库中数据的正确、有效性和完整性。
-
分类:
约束 描述 关键词 非空约束 限制该字段的数据不能为null not null 唯一约束 保证该字段的所有数据都是唯一、不重复的 unique 主键约束 主键是一行数据的唯一标识,要求非空且唯一 primary key 默认约束 保存数据时,如果未指定该字段的值,则采用默认值 default 检查约束(8.0.16版本之后) 保证字段值满足某一条件 check 外键约束 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 foreign key
注意:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
多表查询
多表关系
概述
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
- 一对多(多对一)
- 多对多
- 一对一
一对多(多对一)
- 案例:部门 与 员工的关系
- 关系:一个部门对应多个员工,一个员工对应一个部门
- 实现:在多的一方建立外键,指向一的一方的主键
多对多
- 案例:学生 与 课程的关系
- 关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
- 实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
一对一
- 案例:用户 与 用户详情的关系
- 关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
- 实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(unique)
多表查询概述
- 概述:指从多张表中查询数据
- 笛卡尔积:笛卡尔乘积是指在数学中,两个集合A集合 和 B集合的所有组合情况。(在多表查询时,需要消除无效的笛卡尔积)
- 多表查询分类
- 连接查询
- 内连接:相当于查询A、B交集部分数据
- 外连接:
- 左外连接:查询左表所有数据,以及两张表交集部分数据
- 右外连接:查询右表所有数据,以及两张表交集部分数据
- 自连接:当前表与自身的连接查询,自连接必须使用表别名
- 子查询
- 连接查询
连接查询 - 内连接
-
内连接查询语法
-
隐式内连接
select 字段列表 from 表1, 表2 where 条件 ...;
-
显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件 ...;
-
-
内连接演示
-- 1. 查询每一个员工的姓名 , 及关联的部门的名称(隐式内连接实现) -- 表结构:emp , dept -- 连接条件:emp.dept_id = dept.id select emp.name, dept.name from emp, dept where emp.dept_id = dept.id; select e.name, d.name from emp e, dept d where e.dept_id = d.id; -- 2. 查询每一个员工的姓名 , 及关联的部门的名称(显式内连接实现) -- 表结构:emp , dept -- 连接条件:emp.dept_id = dept.id select e.name, d.name from emp e inner join dept d where e.dept_id = d.id; select e.name, d.name from emp e join dept d where e.dept_id = d.id;
注意:如果我们为表起了别名,那么此时我们就不能够再直接通过表名来限定字段。
连接查询 - 外连接
-
左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件 ...;
-
右外连接
select 字段连接 from 表1 right [outer] join 表2 on 条件 ...;
-
外连接演示
-- 1. 查询emp 表的所有数据,和对应的部门信息(左外连接) -- 表结构:emp , dept -- 连接条件:emp.dept_id = dept.id select e.*, d.name from emp e left join dept d on e.dept_id = d.id; -- 2. 查询dept表的所有数据,和对应的员工信息(右外连接) -- 表结构:emp , dept -- 连接条件:emp.dept_id = dept.id select d.*, e.* from emp e right join dept d on e.dept_id = d.id;
连接查询 - 自连接
-
自连接查询语法:
select 字段列表 from 表A 别名A join 表A 别名B on 条件 ...;
自连接查询,可以是内连接查询,也可以是外连接查询。
-
自连接演示
-- 1. 查询员工 及其 所属领导的名字 -- 表结构:emp select a.name, b.name from emp a, emp b where a.managerid = b.id; -- 2. 查询所有员工 emp 及其领导的名字 emp,如果员工没有领导,也需要查询出来 -- 表结构:emp a, emp b select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;
联合查询 - union, union all
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...;
联合查询演示
-- union all, union
-- 1. 将薪资低于 5000 的员工,和 年龄大于50 岁的员工全部查询出来
select * from emp where salary < 5000
union
select * from emp where age > 50;
-- union all : 当多个条件同时满足,会出现重复记录
-- union : 当多个条件同时满足,会将重复的记录进行合并,实现去重
注意: 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致!!!
子查询
-
概念:SQL 语句中嵌套select 语句,称为嵌套查询,又称子查询。
select * from t1 where column1 = (select column1 from t2);
子查询外部的语句可以是insert / update / delete / select 的任何一个。
-
根据子查询结果不用,分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
标量子查询
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询成为标量子查询。
常用的操作符: = <> > >= < <=
案例:
-- 1. 查询 “销售部” 的所有员工信息
-- a. 查询“销售部” 部门ID
select id from dept where name = '销售部';
-- b. 根据销售部门ID,查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');
-- 2. 查询在 “方东白” 入职之后的员工信息
-- a. 查询 方东白 的入职日期
select entrydate from emp where name = '方东白';
-- b. 查询指定入职日期之后入职的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '方东白');
列子查询
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用的操作符:in、not in、any、some、all
操作符 | 描述 |
---|---|
in | 在指定的集合范围之内,多选一 |
not in | 不在指定的集合范围之内 |
any | 子查询返回列表中,有任意一个满足即可 |
some | 与any等同,使用some的地方都可以使用any |
all | 子查询返回列表的所有值都必须满足 |
案例:
-- 1. 查询 “销售部” 和 “市场部” 的所有员工信息
-- a. 查询 “销售部” 和 “市场部” 的部门ID
select id from dept where name = '销售部' or name = "市场部";
-- b. 根据部门ID,查询员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
-- 2. 查询比财务部所有人工资都高的员工信息
-- a. 查询所有财务部 人员工资
select id from dept where name = '财务部'; -- 得到财务部id
select salary from emp where dept_id = (select id from dept where name = '财务部'); -- 得到财务部 所有人员工资
-- b. 比 财务部 所有人 工资都高的员工信息
select * from emp where salary > all (select salary from emp where dept_id = ( select id from dept where name = '财务部') );
-- 3. 查询比研发部其中任意一人工资高的员工信息
select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '财务部') );
行子查询
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。
常用的操作符: = 、<> 、in 、not in
案例:
-- 1. 查询与 “张无忌”的 薪资及直属领导 相同的员工信息
-- a. 查询 “张无忌”的薪资及直属领导
select salary, managerid from emp where name = '张无忌';
-- b. 查询与 “张无忌” 的薪资及直属领导相同的员工信息
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');
表子查询
子查询返回的结果是多行多列,这种子查询称为表子查询。
常用的操作符:in
案例:
-- 1. 查询与 “鹿杖客” ,“宋远桥”的 职位和薪资相同 的员工信息
-- a. 查询 “鹿杖客” ,“宋远桥”的 职位和薪资
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';
-- b. 查询与 “鹿杖客” ,“宋远桥”的 职位和薪资相同 的员工信息
select * from emp where (job, salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥');
-- 2. 查询入职日期是 “2006-01-01” 之后的员工信息,及其部门信息
-- a. 入职日期是 “2006-01-01” 之后的员工信息
select * from emp where entrydate > '2006-01-01';
-- b. 查询这 部分员工 对应的 部门信息
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;
事务
事务 是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
默认MySQL 的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务。
事务操作、
-
查看/设置事务提交方式
select @@autocommit; -- 1 表示自动提交 -- 0 表示手动提交 set @@autocommit = 0;
-
提交事务
commit;
-
回滚事务
rollback;
-
开启事务
start transaction 或 begin;
-
查看事务隔离级别
select @@transaction_isolation;
-
设置事务隔离级别
set [session|global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable} -- session 表示当前会话 -- global 表示全局
select job, salary from emp where name = ‘鹿杖客’ or name = ‘宋远桥’);
– 2. 查询入职日期是 “2006-01-01” 之后的员工信息,及其部门信息
– a. 入职日期是 “2006-01-01” 之后的员工信息
select * from emp where entrydate > ‘2006-01-01’;
– b. 查询这 部分员工 对应的 部门信息
select e., d. from (select * from emp where entrydate > ‘2006-01-01’) e left join dept d on e.dept_id = d.id;
# 事务
事务 是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
默认MySQL 的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务。
## 事务操作、
- 查看/设置事务提交方式
```mysql
select @@autocommit; -- 1 表示自动提交 -- 0 表示手动提交
set @@autocommit = 0;
-
提交事务
commit;
-
回滚事务
rollback;
-
开启事务
start transaction 或 begin;
-
查看事务隔离级别
select @@transaction_isolation;
-
设置事务隔离级别
set [session|global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable} -- session 表示当前会话 -- global 表示全局
注意:事务隔离级别越高,数据越安全,但是性能越低。