前言
视频参考:黑马程序员
这部分是基础操作,在window环境下运行的。
SQL通用语法及分类
DDL
DDL用于对数据的定义
数据库操作
show databases; #查询所有的数据库
select database(); #查询当前所在的数据库
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则] #创建数据库
drop database [if exists] 数据库名 #删除数据库
use 数据库名 #使用数据库
UTF8字符集长度为3字节,有些符号占4字节,所以推荐用utf8mb4字符集
表操作
show tables; #查询当前数据库的所有表
desc 表名; #查询表的结构
create table 表名(
名称 类型 comment "注释",
名称 类型 comment "注释",
名称 类型 comment "注释",
....
名称 类型 comment "注释",
名称 类型 comment "注释"
); #创建表
创建表最后一个字段是没有逗号的
alter table 表名 add 名称 类型 comment "注释" #添加字段
alter table 表名 modify 名称 新数据的类型 #修改数据的类型
alter table 表名 change 旧名称 新名称 类型 comment "注释" #修改名称和字段类型
alter table 表名 drop 名称 #删除表中数据
alter table 表名 rename to 新表名 # 修改表名
drop table 表名 #删除表
truncate table 表名 #删除表,并重新创建该表
DML
DML用于数据操作
添加数据
insert into 表名 (字段1,字段2...) values(值1,值2....) #在指定的字段添加数据
insert into 表名 values(值1,值2...) #在全部字段添加数据
#values(值1,值2...) values(值1,值2...) values(值1,值2...)多跟几个可以批量添加数据
字符串和日期这类数据需要用单引号引起来
修改数据
update 表名 set 字段1 = 值1, 字段2 = 值2,.... [where 条件]
删除数据
delete from 表名 [where 条件]
DQL
DQL用于数据查询
基础查询
select 字段1,字段2... from 表名; #根据字段查询
select *from 表名 #查询所有
select 字段1 [别名1],..... from 表名 #设置别名
#在select 后加上distingct可以不显示重复项
条件查询
select 字段列表 from 表名 where 条件
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
BETWEEN … AND … | 在某个范围内(含最小、最大值) |
IN(…) | 在in之后的列表中的值,多选一 |
LIKE 占位符 | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
IS NULL | 是NULL |
逻辑运算符 | 功能 |
---|---|
AND 或 && | 并且(多个条件同时成立) |
OR 或 || | 或者(多个条件任意一个成立) |
NOT 或 ! | 非,不是 |
-- 年龄等于30
select * from employee where age = 30;
-- 年龄小于30
select * from employee where age < 30;
-- 小于等于
select * from employee where age <= 30;
-- 没有身份证
select * from employee where idcard is null or idcard = '';
-- 有身份证
select * from employee where idcard;
select * from employee where idcard is not null;
-- 不等于
select * from employee where age != 30;
-- 年龄在20到30之间
select * from employee where age between 20 and 30;
select * from employee where age >= 20 and age <= 30;
-- 下面语句不报错,但查不到任何信息
select * from employee where age between 30 and 20;
-- 性别为女且年龄小于30
select * from employee where age < 30 and gender = '女';
-- 年龄等于25或30或35
select * from employee where age = 25 or age = 30 or age = 35;
select * from employee where age in (25, 30, 35);
-- 姓名为两个字
select * from employee where name like '__';
-- 身份证最后为X
select * from employee where idcard like '%X';
视频教程中的示例
聚合查询
select 聚合函数(字段列表) from 表名 [where 条件];
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
分组查询
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后的过滤条件];
where和having的不同:
- 执行时间不同,where是在分组之前过滤,不满足where条件不参加分组;having是分组后对结果过滤;
- 判断条件不同:where不同对聚合函数进行判断,having可以。
其中优先级where>group by>having
排序查询
select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;
#ASC 升序(默认)
#DESC 降序
多字段排序时,先满足了第一个字段,才会去满足第二个序列
分页查询
select 字段列表 from 表名 limit 起始索引,查询记录数;
起始索引计算 = (页面数-1)*查询记录数。
- 起始索引从0开始,起始索引 = (查询页码 - 1) * 每页显示记录数
- 分页查询是数据库的方言,不同数据库有不同实现,MySQL是LIMIT
- 如果查询的是第一页数据,起始索引可以省略,直接简写 LIMIT 10
DQL执行顺序
from> where > group by > select > order by > limit
DLC
管理用户
user mysql; #使用用户
select *from user; #查询用户
--创建用户
create user '用户名'@'主机名' identified by '密码';
--修改用户密码
alter user '用户名'@'主机名' identified with mysql_nativa_password by '新密码';
--删除用户
drop user '用户名'@'主机名';
create user 'zhang'@'%' identified by '12345';
alter user 'zhang'@'%' identified with mysql_native_password by '123456';
drop user 'zhang'@'%';
这里的主机名称如果写locathost则只能在本机上使用。
权限控制
show grants for '用户名'@'主机名'; #查看权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'; #授予权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'; #撤销权限
- 多个权限用逗号分隔
- 授权时,数据库名和表名可以用 * 进行通配,代表所有
权限 | 说明 |
---|---|
ALL, ALL PRIVILEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
CREATE | 创建数据库/表 |
这里其实跟linxu里面的用户类似,不同的用户拥有不同的权限。
函数
字符串函数
函数 | 功能 |
---|---|
concat(s1, s2, …, sn) | 字符串拼接,将s1, s2, …, sn拼接成一个字符串 |
lower(str) | 将字符串全部转为小写 |
upper(str) | 将字符串全部转为大写 |
lpad(str, n, pad) | 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度 |
rpad(str, n, pad) | 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度 |
trim(str) | 去掉字符串头部和尾部的空格 |
substring(str, start, len) | 返回从字符串str从start位置起的len个长度的字符串 |
注意这里的num的类型是varchar,因为这个函数操作的对象是字符串。
数值函数
函数 | 功能 |
---|---|
ceil(x) | 向上取整 |
floor(x) | 向下取整 |
mod(x, y) | 返回x/y的模 |
rand() | 返回0~1内的随机数 |
round(x, y) | 求参数x的四舍五入值,保留y位小数 |
rand()先生成一个0到1的随机数,然后*1000000,但是还会有小叔部分,用四舍五入去掉小数位,但是生成的随机数有可能是5位,4位,甚至1位,毕竟就算0.00001也是0到1的一个随机数,用在lpad()保证是6位数,不足的用0补全。
日期函数
函数 | 功能 |
---|---|
curdate() | 返回当前日期 |
curtime() | 返回当前时间 |
now() | 返回当前日期和时间 |
year(date) | 获取指定date的年份 |
month(date) | 获取指定date的月份 |
day(date) | 获取指定date的日期 |
data_add(date, INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
datediff(date1, date2) | 返回起始时间date1和结束时间date2之间的天数 |
流程函数
函数 | 功能 |
---|---|
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默认值 |
约束
用于限制表中的数据,为了保证表中的数据的准确和可靠性。MySQL数据库通过约束防止无效的数据进入到表中,以保护数据的实体完整性。
分类
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | not null |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | unique |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | default |
检查约束 | 保证字段值满足某一个条件 | check |
外键约束 | 用来让两张图的数据之间建立连接,保证数据的一致性和完整性 | foreign key |
主要归类为列级约束和表级约束
列级约束
:NOT NULL
|DEFAULT
|PRIMARY KEY
|UNIQUE
|CHECK
表级约束
:PRIMARY KEY
|UNIQUE
|CHECK
|FOREIGN KEY
常用约束
约束条件 | 关键字 |
---|---|
主键 | primary key |
自动增长 | auto_increment |
不为空 | not null |
唯一 | unique |
逻辑条件 | check |
默认值 | default |
示例
create table user(
id int primary key auto_increment, -- 设置主键,并且id会从1自动增长
name varchar(10) not null unique, -- 约束name不能为空,并且不可以重复
age int check(age > 0 and age < 120), -- 保证年龄在(1,120)的区间内
status char(1) default '1', -- 将状态的默认值设置为1
gender char(1) -- 性别没有约束
);
外键约束
附表添加一个外键绑定在主表的主键上,有点类似于继承关系了,比如一个公司有很多部门,将这些部分建一个主表,每个部分下又有很多细分,再将这个细分部门绑定在同一个上司部门。
添加外键
create table 表名(
字段名 字段类型,
...
constraint 外键名称 foreign key(外键字段名) references 主表(主表列名)
);
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);
-- 例子
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);
删除外键
alter table 表名 drop foreign key 外键名;
目前这里就只是逻辑上添加了外键,但是没有绑定在一起,假如一个公司的开发部id是5,现在给改成3,它的下属表中的id值并没有更新,这个时候就要添加更新行为,将他们绑定
行为 | 说明 |
---|---|
no action | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新(与RESTRICT一致) |
restrict | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新(与NO ACTION一致) |
cascade | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则也删除/更新外键在子表中的记录 |
set null | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(要求该外键允许为null) |
set default | 父表有变更时,子表将外键设为一个默认值(Innodb不支持) |
更新行为:
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名) on update 行为
删除行为:
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名) on delete 行为;
示例:
insert into class values (1,'一班'),(2,'二班'),(3,'三班'); -- 添加数据
alter table score add stu_class int; -- 在score表中添加一个绑定key的列
alter table score add constraint stu_class foreign key(stu_class) references class(id); -- 绑定key
多表查询
多表的关系
一对一
比如一个用户的信息有很多,所以拆分为两个表,一个表用于存储简单的信息,一个表用于存储较为复杂的信息,可以提高操作效率。
实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)。
一对多
还是公司部门那种,一个部门中会有多个员工
多对多
学生选课程,一个学生可以选多个课程,课程也可以被多个学生选择。
实现:建立一个中间表,用于保存他们直接的关系。
在单表中的查询可以直接使用select *from 表名;
但是多个表中不能直接用select *fron 表名,表名....;
多余的项是由笛卡尔乘积得来的,我这里两个表总共只有6条记录,查出的9条是3x3得来的,这里就需要添加判断条件,使key对应的显示出来
连接查询
内连接
内连接查询的是两张表交集的部分
隐式内连接
select 字段列表 from 表1, 表2 where 条件 ...;
显示内连接
select 字段列表 from 表1 join 表2 on 连接条件
示例:
-- 隐式内连接
select s.name,c.班级 from score s , class c where s.stu_class = c.id;
-- 显示内连接
select s.name,c.班级 from score s join class c on c.id = s.stu_class;
score s
这里是更改了别名,这里改名之后,后面就不能用score.stu_class
因为from的优先级>where。
外连接
外连接查的是AB交集,并且包含其中一个集合的全部元素
左连接
select *from 表1 left join 表2 on 条件
右连接
select *from 表1 right join 表2 on 条件
在两张表中新加信息
这个时候如果用内连接查询并集是找不到小张还有四班的
使用左连接查询学生与班级的对应信息
select * from score s left join class c on s.stu_class = c.id;
使用右连接查询班级与学生的对应信息
select * from score s right join class c on s.stu_class = c.id;
其实只需要用左连接就可以了
select * from score s right join class c on s.stu_class = c.id;
select * from class c left join score s on s.stu_class = c.id; -- 这两句是等价的
自连接
当前表与自身的连接查询,自连接必须使用表别名
自连接查询,可以是内连接查询,也可以是外连接查询,用法和上面两种相同
使用场景:一个部门有很多人,这么多人中又有很多小组,小组中有一个领导,可以在表内查询员工之间的关系。
联合查询
示例:
-- 查询数学成绩小于70和语文成绩大于70的学生
select *from score where ma_score < 70 -- 注意这里没有分号
union
select *from score where ch_score > 70;
如果使用union all
会查看重复项
子查询
SQL语句中嵌套select语句,称谓嵌套查询,又称子查询。
子查询外部的语句可以是 insert / update / delete / select 的任何一个
根据子查询结果可以分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列
标量子查询
子查询返回的结果是单个值(数字、字符串、日期等)。
常用操作符:=
<
>
>=
<=
示例:
-- 根据学生的班级查找学生
-- 需求一:查找三班
-- 需求二:查找三班的学生
select *from score where stu_class = (select id from class where 班级 = '三班');
上面这段语句查找了三班的学生。
列子查询
返回的结果是一列(可以是多行)
操作符 | 描述 |
---|---|
in | 在指定的集合范围内,多选一 |
not in | 不在指定的集合范围内 |
any | 子查询返回列表中,有任意一个满足即可 |
some | 与ANY等同,使用SOME的地方都可以使用ANY |
all | 子查询返回列表的所有值都必须满足 |
示例:
-- 查找三班和二班的所有学生
select *from score where stu_class in (select id from class where 班级 = '三班' or 班级 = '一班');
后面接的select都是要加()
的
行子查询
返回的结果是一行(可以是多列)。
常用操作符:=, <, >, IN, NOT IN
示例:
-- 查找和小王语文成绩一样的人
select * from score where (ch_score,ma_score) = (select ch_score,ma_score from score where name = '小王');
表子查询
返回的结果是多行多列
常用操作符:in
示例:
-- 查询与xxx1,xxx2的职位和薪资相同的员工
select * from employee where (job, salary) in (select job, salary from employee where name = 'xxx1' or name = 'xxx2');
-- 查询入职日期是2006-01-01之后的员工,及其部门信息
select e.*, d.* from (select * from employee where entrydate > '2006-01-01') as e left join dept as d on e.dept = d.id;
-- 黑马课程中的示例
事务
先新建一个表用于后面的操作
create table bank
(
id int auto_increment primary key ,
name varchar(10) not null ,
money int
);
insert into bank values(1,'张三',2000),(2,'李四',2000);
事务是一组操作的集合,事务会把所有操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
事务创建
示例:
-- 张三给李四转1000块
-- 张三-1000
update bank set money = money - 1000 where name = '张三';
-- 李四+1000
update bank set money = money + 1000 where name = '李四';
在这个过程中是没有错误的,正常运行了,但是假如在李四+1000的过程中运行发生了错误则张三的前减少了,李四的钱却没有增加(bank 狂喜),对于这种情况就要一个事务,如果没有转成功则他们的钱都不变化。
mysql中是自动提交的,在执行完一句sql语句就会变化,我们可以改成手动提交,每次运行如果没有报错就提交更新,有报错就撤销刚才的操作,这很类似git里的提交操作了,先把操作保存在缓存区,没错误在提交。如果是自动提交,运行完表就会发生改变,手动提交运行完表没有改变,提交后才改变
方式一
将自动提交关掉,改为手动提交
-- 查看事务提交方式
select @@autocommit;
-- 设置事务提交方式,1为自动提交,0为手动提交,该设置只对当前会话有效
SET @@autocommit = 0;
-- 提交事务
commit;
-- 回滚事务
rollback;
-- 先设置为手动提交 (设置一次即可,不需要每次运行都设置)
set @@autocommit = 0;
-- -----------------------------------
-- 张三-1000
update bank set money = money - 1000 where name = '张三';
-- 李四+1000
update bank set money = money + 1000 where name = '李四';
-- 成功运行就commit
commit ;
-- 失败了就撤销操作
rollback ;
这样设置了之后不管运行什么语句都需要重新提交,还是很不方便的。
方式二
这个方式不需要设置为手动提交,可以在要运行事务的地方开启一个事务
开启事务:
start transaction 或 begin transaction;
提交事务:
commit;
回滚事务:
rollback;
-- 开启事务
start transaction;
-- 张三-1000
update bank set money = money - 1000 where name = '张三';
-- 李四+1000
update bank set money = money + 1000 where name = '李四';
-- 成功运行就commit
commit ;
-- 失败了就撤销操作
rollback ;
四大特性ACID
- 原子性(Atomicity):事务是不可分割的最小操作但愿,要么全部成功,要么全部失败
- 一致性(Consistency):事务完成时,必须使所有数据都保持一致状态
- 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
- 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
并发事务处理
问题 | 描述 |
---|---|
脏读 | 一个事务读到另一个事务还没提交的数据 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是再插入数据时,又发现这行数据已经存在 |
并发事务隔离级别:
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted | √ | √ | √ |
Read committed | × | √ | √ |
Repeatable Read(默认) | × | × | √ |
Serializable | × | × | × |
Serializable 性能最差 安全性最好 Read uncommited反之
-- 查看事务隔离级别:
select @@transaction_isolation;
设置事务隔离级别:
set [ session | global ] transaction isolation level {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE };
session是会话级别,表示只针对当前会话有效,global表示对所有会话有效