一、函数
函数
是指一段可以直接被另一段程序调用的程序或代码
1.1 字符串函数
函数 | 功能 |
---|---|
CONCAT(S1,S2…n) | 字符串拼接,将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个长度的字符串 |
#concat
select concat('abcd','efgh');
#lower
select lower('AJNasdas');
#upper
select upper('ahosdHOH');
#lpad
select lpad('01',5,'-');
#substring
select substring('str asd sad',1,5);
#rpad
select rpad('01',6,'0');
#将所有人的ID全部转换为00001为类型的ID
update user set ID = lpad(ID,10,'0');
1.2 数值函数
函数 | 功能 |
---|---|
CEIL(x) |
向上取整 |
| FLOOR(X) | 向下取整|
|MOD(x;y) | 返回x/y的模|
| RAND0 | 返回0~1内的随机数|
| ROUND(x,y) | 求参数x的四舍五入的值,保留y位小数|
# 向上取整ceil
select ceil(1.456);
#先下取整floor
select floor(2.5541);
#获取随机数rand
select rand();
#四取五入round
select round(1.253,2);
#获取一个6位数的验证码
select lpad(floor(rand() *1000000),6,'0');
1.3 日期函数
函数 | 功能 |
---|---|
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前日期和时间 |
YEAR(date) | 获取指定date的年份 |
MONTH(date) | 获取指定date的月份 |
DAY(date) | 获取指定date的日期 |
DATE ADD(date,INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
DATEDIFF(date1date2) | 返回起始时间date1 和 结束时间date2之间的天数 |
#当前日期
select curdate();
#当前时间
select curtime();
#返回当前日期时间
select now();
#指定当前的年份
select year(now());
#指定当前的月份
select MONTH(now());
#指定当前的日期
select day(now());
#返回两个日期之前相差多少天
select datediff(now(),'2021-5-4');
#在当前热气上面添加相对应的的时间
select date_add(now(),INTERVAL 70 year );
1.4 流程控制语句
函数 | 功能 |
---|---|
IF(value , t, f) | 如果value为true,则返回t,否则返回f |
IFNULL(value1, value2) | 如果value1不为空,返回value1,否则返回value2 |
CASE WHEN [val1 ] THEN [res11 … ELSE[ default] END | 如果val1为true,返回res1,… 否则返回default默认值 |
CASE [ expr] WHEN [val1 ] THEN [res1] … ELSE[ default] END | 如果expr的值等于val1,返回res1,… 否则返回default默认值 |
create table student(
name varchar(10) comment '学生姓名',
math int comment '数学成绩',
english int comment '英语成绩'
) comment '学生信息表';
insert into student(name, math, english) values ('TOM',86,45),('Jack',78,65);
#通过分数来划分等级
select
name,
(case when math >=85 then '优秀' when math >= 60 then '及格'else '不及格'end) '数学',
(case when student.english >=85 then '优秀' when student.english >= 60 then '及格'else '不及格'end)
from student;
二、约束
1.概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据
2.目的:保证数据库中数据的正确、有效性和完整性。
2.1 约束基本分类
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | NOT NULL |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | DEFAULT |
检查约束(8.0.16版本之后) | 保证字段值满足某一个条件 | CHECK |
外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 | FOREIGN KEY |
#创建有约束条件的table
create table classmate(
id int primary key AUTO_INCREMENT comment '学号自己增长',
name varchar(10) not null unique comment '名字不为空唯一',
age int check ( age>10 && age<100 ) comment '年龄条件',
status char(1) default '1' comment '状态默认为1',
gender char(1) comment '性别'
) comment '信息表';
#添加数据
insert into classmate(name, age, gender) value ('巴蜀',55,'男'),('tom',23,'男'),('jack',45,'男');
2.2 外键约束
概念
外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性
- 添加外键
CREATE TABLE 表名(
字段名 数据类型
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
alter table emp drop foreign key fk_emp_dept_id;
2.3 删除/更新行为
行为 | 说明 |
---|---|
NO ACTION | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与 RESTRICT 一致) |
RESTRICT | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与NO ACTION一致) |
CASCADE | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录。 |
SET NULL | 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为nu(这就要求该外键允许取nu)。 |
SET DEFAULT | 父表有变更时,子表将外键列设置成一个默认的值(Innodb不支持) |
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on UPDATE cascade on DELETE cascade ;
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null ;
三、多表查询
3.1 多表关系
概述
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种
:
- 一对多(多对一)
- 多对多
- 一对一
一对多
-
案例: 部门与员工的关系
-
关系:一个部门对应多个员工,一个员工对应一个部门
-
实现: 在多的一方建立外键,指向一的一方的主键
多对多 -
案例: 学生与 课程的关系
-
关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
-
实现: 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
create table student(
id int primary key auto_increment comment '主键id',
name varchar(10) comment '姓名',
idCard varchar(10) comment '学号'
) comment '学生信息';
create table curse(
id int primary key auto_increment comment '主键id',
name varchar(10) comment '课程名字'
)comment '课程表';
create table student_curse(
stuID int not null comment '对应的学生id',
id int primary key auto_increment comment '主键id',
curseID int not null comment '课程ID',
constraint fk_curseID foreign key (curseID) references curse(id),
constraint fk_stuID foreign key (stuID) references student(id)
)comment '中间表,通过这个表来关联他们'
#添加数据
insert into student values (null,'张三','22240101'),(null,'李四','22240102'),(null,'王五','22240103');
insert into curse values (null,'Java'),(null,'PHP'),(null,'HTML'),(null,'C');
insert into student_curse values (1,null,1),(1,null,2),(1,null,3),(2,null,1),(3,null,1),(2,null,2)
一对一
- 案例: 用户与用户详情的关系
- 关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
- 实现: 在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
3.2 多表查询概述
概述:
指从多张表中查询数据
笛卡尔积:
笛卡尔乘积是指在数学中,两个集合A集合和 B集合的所有组合情况。(在多表查询时,需要消除无效的笛卡尔积)
3.3 多表查询分类
连接查询
内连接:相当于查询A、B交集部分数据
外连接:
左外连接:查询左表所有数据,以及两张表交集部分数据
右外连接:查询右表所有数据,以及两张表交集部分数据
自连接:当前表与自身的连接查询,自连接必须使用表别名
- 子查询:
3.3.1 内连接
查询他们ID相同时对应的名字
隐式内连接
select curse.name,student.name from curse,student where curse.id = student.id;
显示内连接
select curse.name,student.name from curse inner join student on curse.id = student.id;
3.3.2 外连接
左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER]JOIN 表2 0N 条件 ...;
相当于查询表1(左表)的所有数据 包含 表1和表2交集部分的数据
select c.*,s.* from curse c left join student s on c.id = s.id;
右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER]JOIN 表2 0N 条件...;
相当于查询表2(右表)的所有数据 包含 表1和表2交集部分的数据
select c.*,s.* from curse c right join student s on c.id = s.id;
3.3.3 连接查询-自连接
自连接查询语法:
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;
自连接查询,可以是内连接查询,也可以是外连接查询。
select s1.name , s2.name from student s1 join student s2 on s1.id = s2.idCard;
3.4 联合查询-union,union all
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
SELECT 字段列表 FROM 表A ...
UNION[ ALL]
SELECT 字段列表 FROM 表B ....;
主要事项:
对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重
select student.name from student
union
select curse.name from curse;
3.5 子查询
- 概念:
SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个
- 根据子查询结果不同,分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结案为多行多列)
- 根据子查询位置,分为:
WHERE之后、FROM之后、SELECT之后
3.5.1 标量子查询
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询成为标量子查询
常用的操作符:= <> > >=
#查询小王的入职时间
select entrydate from emp where username='小王';
#查找所有入职时间大于小王的人----------------标量子查询
select username from emp where entrydate> (select entrydate from emp where username='小王');
3.5.2 列子查询
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询
常用的操作符:IN 、NOTIN、ANY 、SOME、ALL
#查询与张三学习课程一样的人的工作信息
#获取到的为当前id为1同学所学的课程
select curseID from student_curse where stuID =1 ;
#获取到与该同学雪一样课程的同学
select *from student_curse where curseID in (select curseID from student_curse where stuID =1);
3.5.3 行子查询
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询
常用的操作符:= 、<>、IN 、NOT IN
select entrydate,workadesss from emp where username = '小王';
# 利用杭子查询查询出和小王入职时间和工作地点相同的人的数据
select *from emp where (entrydate,workadesss)=(select entrydate,workadesss from emp where username = '小王') ;
3.5.4 表子查询
子查询返回的结果是多行多列,这种子查询称为表子查询
常用的操作符:IN
# 先获取出入职时间在2021-1-1之后的员工信息
select * from emp where entrydate>'2021-1-1';
# 将上述的表与部门表建立联系
select e.* ,d.* from (select * from emp where entrydate>'2021-1-1') e left join dept d on d.id=e.dept_id;
3.6 多表查询小练习
3.6.1 小练习一
#查询员工的年龄,姓名,职位 和部门信息
select e.username ,e.age,d.name from dept d ,emp e where d.id=e.dept_id;
#查询年龄小于三十岁的姓名,年龄,职位 和部门信息
select e.username ,e.age, d.name from emp e,dept d where e.age<30 and d.id=e.dept_id;
# 查询拥有员工的部门ID,部门姓名
select distinct d.id , d.name from dept d , emp e where e.dept_id = d.id;
#查询年龄大于30岁的员工,及其归属的部门名称,如果员工没有分配部门,也需要展示出来
select e.username,d.name from emp e left join dept d on d.id = e.dept_id where e.age>20;
# 查询研发部门所有员工信息和工资等级
select *
from dept d,
salgrade s,
emp e
where d.id = e.id
and e.name = '研发部'
and (e.salary between s.losal and s.hisal);
#查询研发部的平均薪水
select avg(e.salary)
from emp s,
dept d
where s.id = e.id
and d.name = '研发部';
#查询比‘灭绝’工资高的员工信息
select * from emp where salary > ( select salary from emp where name = '灭绝')
四、事务
4.1 事务简介
事务
是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功
,要么同时失败。
默认MvsQL的事务是自动提交的,也就是说,当执行一条DML语句,MvSQL会立即隐式的提交事务
4.2 事务的操作
查看/设置事务提交方式
SELECT @@autocommit
SET @@autocommit = 0 :
提交事务
COMMIT ;
回滚事务
ROLLBACK
#这里手动开启事务
select @@autocommit;
#将其设置手动提交事务
set @@autocommit = 0;
#查询张三的钱
select * from account where name = '张三';
#进行转账
update account set money = money -1000 where name = '张三';
在这里产生异常,后面 的代码就执行不了
update account set money = money +1000 where name = '李四';
#手动提交代码
commit ;
#回滚事务
rollback ;
法二
开启事务
START TRANSACTION 或 BEGIN
提交事务
COMMT ;
回滚事务
ROLLBACK
start transaction ;
#查询张三的钱
select * from account where name = '张三';
#进行转账
update account set money = money -1000 where name = '张三';
在这里产生异常,后面 的代码就执行不了
update account set money = money +1000 where name = '李四';
#手动提交代码
commit ;
#回滚事务
rollback ;
4.3 事务的四大特性(ACID)
- 原子性 (Atomicity):
事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
- 一致性(Consistency):
事务完成时,必须使所有的数据都保持一致状态
- 隔离性 (lsolation):
数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性(Durability):
事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
4.5 并发事务问题
问题 | 描述 |
---|---|
脏读 | 一个事务读到另外一个事务还没有提交的数据 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了幻影”。 |
脏读
不可重复读
幻读
4.6 事务隔离级别
隔离级别 | 赃读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted | x | x | x |
Read committed | √ | x | x |
Repeatable Read(默认) | √ | √ | x |
Serializable | √ | √ | √ |
从上往下,隔离级别越来越高,安全性越高,但是执行效率逐渐降低
– 查看事务隔离级别
SELECT @@TRANSACTION ISOLATION;
- 设置事务隔离级别
SET[SESSION GLOBAL] TRANSACTON ISOLATION LEVEL READ UNCOMMITTED READ COMMITTED REPEATABLE READ I SERIALIZABLE