文章目录
- DDL
- 数据库操作-查询
- 查询所有数据库
- 创建
- 删除
- 使用
- 表操作-查询
- 查询当前数据库所有表
- 查询表结构
- 查询指定表的建表语句
- 表操作-创建
- 表操作-修改
- 添加字段
- 修改数据类型
- 修改字段名和字段类型
- 删除字段
- 修改表名
- 表操作-删除
- 删除表
- 删除指定表、并重新创建该表
- DML
- 数值类型
- 字符串类型
- 日期类型
- 添加数据
- 修改数据
- 删除数据
- DQL-数据查询语言
- 条件查询
- 聚合函数
- 分组查询
- where与having的区别:
- 排序查询
- 分页查询
- DQL-执行顺序
- 总结
- DCL
- 管理用户
- 查询用户
- 创建用户
- 修改用户密码
- 删除用户
- 权限控制
- 查询权限
- 授予权限
- 撤销权限
- 函数
- 字符串函数
- 数值函数
- 日期函数
- 流程函数
- 约束
- 外键约束
- 添加外键
- 删除外键
- 删除/更新行为
- 多表查询
- 分类
- 内连接
- 外连接
- 自连接
- 联合查询-union, union all
- 子查询
- 列子查询
- 行子查询
- 表子查询
- 事务
- 事务操作
- 事务四大特性
- 并发事务问题
- 事务隔离级别
DDL
数据库操作-查询
查询所有数据库
show databases;
select database();
创建
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
删除
drop database [if exists] 数据库名;
使用
use 数据库名;
表操作-查询
查询当前数据库所有表
show tables;
查询表结构
desc 表名;
查询指定表的建表语句
show create table 表名;
表操作-创建
create table 表名(
字段1 字段1类型[ comment 字段1注释 ],
字段1 字段1类型[ comment 字段1注释 ],
字段1 字段1类型[ comment 字段1注释 ],
…
字段n 字段n类型 [ comment 字段n注释 ]
)[ comment 表注释 ];
表操作-修改
添加字段
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
删除字段
alter table 表名 drop 字段名;
修改表名
alter table 表名 rename to 新表名;
表操作-删除
删除表
drop table [if exists] 表名;
删除指定表、并重新创建该表
truncate table 表名;
DML
数值类型
字符串类型
日期类型
添加数据
添加单条字段
-
insert into table_name(字段名) values(数值);
-
insert into table_name values(全体字段对应的数值);
修改数据
update table_name set 字段名=值1, 字段2=值2, ... [where 条件];
删除数据
delete from table_name [where 条件];
删除表
drop table table_name;
注意:
- delete如果没有条件的时候,则会删除整张表的所有数据。
- delete语句不能删除某一个字段的值(可以使用update)。
DQL-数据查询语言
**关键字:**SELECT
语法:
select
字段列表
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
group by
排序字段列表
limit
分页参数;
-
查询多个字段
select 字段1, 字段2, 字段3, ... from table_name;
select * from table_name;
-
设置别名
select 字段1 [as 别名1], 字段2 [as 别名2], ... from table_name;
- 去除重复记录
select distinct 字段列表 from table_name;
条件查询
-
select 字段列表 from 表名 where 条件列表;
-
条件
比较运算符 功能 > 大于 >= 大于等于 < 小于 <= 小于等于 = 等于 <> 或 != 不等于 between … and … 在某个范围之内(含最小、最大值) in(…) 在in之后的列表中的值,多选一 like 占位符 模糊匹配(_匹配单个字符,%匹配任意个字符) is null 是null
逻辑运算符 | 功能 |
---|---|
and 或 && | 并且 |
or 或 || | 或者 |
not 或 ! | 非 |
例:查询名字为2个字的人
select * from table_name where name like '__';
查询身份证号中最后一位是X的人
select * from table_name where identification like '%X';
聚合函数
常见的聚合函数
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
语法:select 聚合函数(字段列表) from 表明;
分组查询
语法:select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]
where与having的区别:
- 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
排序查询
-
语法:
select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;
-
排序方式
asc:升序(默认值)
desc:降序
注:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
select name, age from employee where age <= 35 order by age, entry——date desc;
分页查询
select 字段列表 from 表名 limit 起始索引, 查询记录数;
注:
- 起始索引从0开始、起始索引 = (查询页码 - 1) * 每页显示记录数。
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是limit。
- 如果查询的是第一页数据,起始索引可以省略,直接简写为limit 10(查询记录数)。
DQL-执行顺序
总结
DCL
Data Control Language
管理用户
查询用户
use mysql;
select * from user;
创建用户
create user '用户名'@'主机名' identified by '密码';
例:
create user 'lilcancy'@'%' identified by '123456';
修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
例:
alter user 'lilcancy'@'%' identified by mysql_native_password by '1234';
删除用户
drop user '用户名'@'主机名';
注意:
- 主机名可以使用%通配。
- 这类SQL开发人员操作的比较少,主要是DBA(数据库管理员)使用。
权限控制
权限 | 说明 |
---|---|
ALL, ALL PRIVILEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
CREATE | 创建数据库/表 |
查询权限
show grants for '用户名'@'主机名';
授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
撤销权限
revoke 权限列表 on 数据库.表名 from '用户名'@'主机名';
函数
字符串函数
函数 | 功能 |
---|---|
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个长度的字符串 |
数值函数
函数 | 功能 |
---|---|
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之间的天数 |
使用datediff()时,返回的是date1 - date2
案例:
select name, datediff(curdate(), entry_date) as 'entrydays' from emp order by entrydays desc;
流程函数
函数 | 功能 |
---|---|
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默认值 |
案例:
select name,
employee.work_address,
case employee.work_address
when '上海' then '一线'
when '北京' then '一线'
else '其他' end as '什么城市'
from employee;
约束
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | NOT NULL |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | DEFAULT |
检查约束(8.0.16版本之后) | 保证字段值满足某一条件 | CHECK |
外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 | FOREIGN KEY |
注:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
外键约束
添加外键
create table 表名(
字段名 数据类型,
…
[constraint] [外键名称] forgeign key(外键字段名) references 主表(主表列名)
);
atler table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);
删除外键
alter table 表名 drop foreign key 外键名称;
删除/更新行为
行为 | 说明 |
---|---|
NO ACTION | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与RESTRICT一致) |
RESTRICT | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与NO ACTION一致) |
CASCADE | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录。 |
SET NULL | 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(这就要求该外键允许取null)。 |
SET DEFAULT | 父表有变更时,子表将外键列设置成一个默认的值(Innodb不支持)。 |
多表查询
多表查询涉及一对多、多对多、一对一。
分类
连接查询:
-
内连接:相当于查询A、B交集部分数据
-
外连接:左外连接:查询左表所以数据,以及两张表交集部分数据
右外连接:查询右表所以数据,以及两张表交集部分数据
-
自连接:当前表与自身的连接查询,自连接必须使用表别名
内连接
隐式内连接
select 字段列表 from 表1, 表2 where 条件...;
显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
外连接
左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件...;
右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
自连接
select 字段列表 from 表A 别名A join 表A 别名B on 条件...;
联合查询-union, union all
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...;
注意:联合查询需要保持查询的列数必须一致,字段类型也需要一致。
union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
子查询
SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
select * from t1 where column1 = (select column1 from t2);
子查询外部的语句可以是INSERT / UPDATE / DELECT/ SELECT的任何一个。
根据子查询结果不同,分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
根据子查询位置,分为:WHERE之后、FROM之后、SELECT之后。
列子查询
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用操作符:IN、NOT IN、ANY、SOME、ALL
行子查询
子查询返回的结果是一行(可以是多列)
常用的操作符:=、<>、IN、NOT IN
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');
表子查询
多行多列
常用操作符:IN
事务
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
事务操作
- 查看/设置事务提交方式
select @@autocommit;
set @@autocommit=0;
- 提交事务
commit;
- 回滚事务
rollback;
- 开启事务
start transaction 或 begin;
- 提交事务
commit;
- 回滚事务
rollback;
事务四大特性
- 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
- 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
- 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
并发事务问题
问题 | 描述 |
---|---|
脏读 | 一个事务读到另外一个事务还没有提交的数据 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复度。 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了“幻影”。 |
事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted | √ | √ | √ |
Read committed | × | √ | √ |
Repeatable Read(默认) | × | × | √ |
Serializable | × | × | × |
# 查看事务隔离级别
select @@transaction_isolation;
# 设置事务隔离级别
set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable}
持续更新中…
图片资料来源于黑马程序员b站课程:https://www.bilibili.com/video/BV1Kr4y1i7ru/?share_source=copy_web&vd_source=a8c23842c1f1523e29764a6bc36e3910