当前位置：首页 > article >正文

MySQL学习笔记（二）

article 2025/1/9 15:49:32

一、SQL-函数

函数-介绍

函数是指一段可以直接被另一段程序调用的程序或代码。
在这里插入图片描述

字符串函数

在这里插入图片描述

示例

--concat
select concat('Hello','MySql');
--upper
select upper('Hello');
--lpad
select lpad('01',5,'-');
--trim
select trim(' Hello  MySQL ');  --中间空格还在，头尾空格去除
--substring
select substring('Hello MySQL',1,5);

案例
由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如:1号员工的工号应该为00001。

update emp set workno = lpad(workno,5,'0');

数值函数

常见的数值函数如下
在这里插入图片描述
示例

--ceil
select ceil(1.1);
--floor
select floor(1.9);
--mod
select mod(7,4);
--rand
select rand();
--round
select round(2.344,2);

案例
通过数据库的函数，生成一个六位数的随机验证码。

select lpad(round(rand()*100000,0),6,'0');

rand直接生成，再乘1000000后
在这里插入图片描述

日期函数

常见的日期函数如下
在这里插入图片描述
示例

--curdate()
select curdate();
--curtime()
select curtime();
--now()
select now();
--YEAR MONTH DAY
select YEAR(now());
select MONTH(now());
select DAY(now());
--date_add
select date_add((now(),INTERVAL 70 MONTH);
--datediff
select datediff('2021-10-01','2021-12-01'); --得到-61,第一个时间减去第二个时间

案例
查询所有员工的入职天数，并根据天数倒序排序。

select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;

流程函数

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率
在这里插入图片描述
示例

--if
select if(false,'OK','Error');
--ifnull
select ifnull('OK','Default'); --返回ok
select ifnull('','Default'); --返回第一个空串
select ifnull(null,'Default'); --返回Default
--case when then else end
--需求：查询emp表的员工姓名和工作地址（北京/上海-》一线城市，其他->二线城市）
select
	name,
	case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end )
	from emp;

在这里插入图片描述

select
	id,
	name,
	(case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end) '数学',
	(case when english>= 85 then '优秀' when english>=60 then '及格' else '不及格' end) '英语',
	(case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格' end) '语文'
	from scores;

总结

在这里插入图片描述

二、SQL-约束

约束-介绍

1.概念:约束是作用于表中字段上的规则，用于限制存储在表中的数据。
2.目的:保证数据库中数据的正确、有效性和完整性。
在这里插入图片描述

对字段数据约束

create table user(
	id int primary key auto_increment '主键',
	name varchar(10) not null unique comment '姓名',
	age int check (age >0 && age <= 120) comment '年龄',
	status char(1) default '1' comment '状态',
	gender char(1) comment '性别'
) comment '用户表';

外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性
(不建议使用外键)
在这里插入图片描述

语法

在这里插入图片描述
示例

--添加外键
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;

删除/更新行为

在这里插入图片描述

--外键的删除和更新行为
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;

总结

在这里插入图片描述

三、SQL-多表查询

介绍

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种:

一对多（多对一）
多对多
一对一

多表关系

一对多

在这里插入图片描述

多对多

在这里插入图片描述
代码建立

一对一

在这里插入图片描述
示例

在这里插入图片描述

多表查询

概述

在这里插入图片描述

--消除无效的笛卡尔积
select * from emp , dept where emp.dept_id = dept.id;

多表查询分类

在这里插入图片描述

连接查询-内连接

在这里插入图片描述
内连接演示

--1.查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）
--表结构：emp,dept
--连接条件：emp.dept_id = dept.id
select emp.name , dept.name from emp , dept where emp.dept_id = dept.id;
--起别名来简化sql编写,注意起别名后where处条件就不可以用原名了
select e.name , d.name from emp e , dept d where e.dept.id = d.id;


--2.查询每一个员工的姓名，及关联的部门的名称（显式内连接实现） --- INNER JOIN ... ON ...
-- 表结构：emp , dept
-- 连接条件：emp.dept_id = dept.id
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
--inner关键字也可以省略
select e.name, d.name from emp e join dept d on e.dept_id = d.id;

隐式连接好理解好书写，语法简单，担心的点较少。但是显式连接可以减少字段的扫描，有更快的执行速度。这种速度优势在3张或更多表连接时比较明显

连接查询-外连接

在这里插入图片描述
外连接演示

--1.查询emp表的所有数据，和对应的部门信息（左外连接）
--表结构:emp，dept
--连接条件:emp.dept_id=dept.id
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;

select e.*, d.name from emp e left join dept d on e.dept_id = d.id; --outer可省

根据左表的所有数据去查右边的值，没有的就为空，最后返回以左表为主体的值
在这里插入图片描述

--2.查询dept表的所有数据，和对应的员工信息(右外连)
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

select d.*, e.* from dpt d left outer join emp e on e.dept_id = d.id;

在这里插入图片描述

连接查询-自连接

在这里插入图片描述
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重

在这里插入图片描述

--1.查询员工 及其 所属领导的名字
--表结构：emp
select a.name , b.name from 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
--1.将薪资低于5000的员工，和年龄大于 50 岁的员工全部查询出来
select * from emp where salary < 5000;
union all
select * from emp where age > 50;

select * from emp where salary < 5000;
union	--删掉all即对结果去除重复
select * from emp where age > 50;

在这里插入图片描述

上为分别查
下为union all后

子查询

在这里插入图片描述

子查询-标量子查询

在这里插入图片描述

--标量子查询
--1.查询“销售部”的所有员工信息
--a.查询“销售部”部门ID
select id from dept where name = '销售部';

--b.根据销售部部门ID，查询员工信息
select *from emp where dept_id = 4; --即上条查询出的结果
--合并即为
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 = '方东白');

子查询-列子查询

在这里插入图片描述

--列子查询
--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 * from emp where name = '财务部';

select salary from emp where dept_id = (select * from emp where name = '财务部');
--b:比财务部 所有人工资都高的员工信息
select * from emp where salary > all (select salary from emp where dept_id = (select * from emp where name = '财务部'))
--3.查询比研发部其中任意一人工资高的员工信息
--a.查询研发部所有人工资
select salary from emp where dept_id = (select id from dept where name = '研发部');
--b.比研发部其中任意一人工资高的员工信息(只要高于一个人就可以)
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));

子查询-行子查询

在这里插入图片描述

--行子查询
--1.查询与“张无忌”的薪资及直属领导相同的员工信息
--a.查询“张无忌”的薪资及直属领导
select salary,managerid from emp where name = '张无忌';

--b.查询与“张无忌”的薪资及直属领导相同的员工信息
select * from emp where (salary, managerid) = (select salary,managerid from emp where name = '张无忌');

子查询-表子查询

--表子查询
--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;

2.a查询数据
在这里插入图片描述
2.b查询数据

总结

在这里插入图片描述

练习

四、SQL-事务

介绍

在这里插入图片描述
默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务

事务-操作

在这里插入图片描述

--方式一
select @@autocommit;	--查询事务提交方式
set @@autocommit = 0;	--设置手动提交

--转账操作(张三给李四转账1000)
--1.查询张三账户余额
select * from account where name='张三';
--2.将张三账户余额-1000
update account set money = money - 1000 where name ='张三';
程序执行报错...
--3.将李四账户余额+1000
update account set money= money + 1000 where name ='李四';

--提交事务  设置手动提交后，输入指令后再commit才会生效
commit;

--回滚事务;
rollback;

在这里插入图片描述

--方式二
start transaction;	--表示开始手动控制事务，只要没有commit是不会提交，异常则rollback

--转账操作(张三给李四转账1000)
--1.查询张三账户余额
select * from account where name='张三';
--2.将张三账户余额-1000
update account set money = money - 1000 where name ='张三';
程序执行报错...
--3.将李四账户余额+1000
update account set money= money + 1000 where name ='李四';

--提交事务  设置手动提交后，输入指令后再commit才会生效
commit;

--回滚事务;
rollback;

事务-四大特性

在这里插入图片描述

事务-并发问题

在这里插入图片描述

事务-隔离级别

在这里插入图片描述

--查看事务隔离级别
select @@transaction_isolation;

--设置事务隔离级别
set session transaction isolation level read uncommitted;

set session transaction isolation level repeatable read;