MySQL——数据库学习基础：函数，约束，多表查询，事务

learn_more1

已于 2023-12-20 19:46:10 修改

阅读量247

点赞数 11

分类专栏： MySQL 文章标签：数据库 mysql 学习

于 2023-12-01 21:27:36 首次发布

本文链接：https://blog.csdn.net/learn_more1/article/details/134676630

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一.函数

1.聚合函数

在这里插入图片描述

(1).count


-- 返回非NULL数据的总行数.
select count(height) from students; 
-- 返回总行数，包含null值记录;
select count(*) from students;

(2).max

-- 查询女生的编号最大值
select max(id) from students where gender = 2;

(3).min

-- 查询未删除的学生最小编号
select min(id) from students where is_delete = 0;

(4).sum

-- 查询男生的总身高
select sum(height) from students where gender = 1;
-- 平均身高
select sum(height) / count(*) from students where gender = 1;

(5).avg

-- 求男生的平均身高, 聚合函数不统计null值，平均身高有误
select avg(height) from students where gender = 1;
-- 求男生的平均身高, 包含身高是null的
select avg(ifnull(height,0)) from students where gender = 1;

2.字符串函数

在这里插入图片描述

(1).concat

#--concat
select concat('Hello','MySQL');
# HelloMySQL

(2).lower

#--lower
select lower('Hello');
# hello

(3).upper

#--upper
select upper('hello');
# HELLO

(4).lpad

#--lpad
select lpad('01',5,'-');
# ---01

(5).rpad

#--rpad
select rpad('01',5,'-');
# 01---

(6).trim

#--trim
select trim('  Hello  MySQL  ');
# Hello  MySQL

(7).substring

注意：MySQL中的其实索引为1

#--substring
select substring('Hello World',1,5);
#Hello

案例：企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0

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

3.数值函数

在这里插入图片描述

(1).ceil

向上取整

#--ceil  向上取整
select ceil(1.1);
# 2

(2).floor

向下取整

#--floor  向下取整
select floor(1.9);
# 1

(3).mod

返回X/Y的模

#--mod  返回X/Y的模
select mod(6,4);
# 2

(4).rand

返回0-1内的随机数

#--rand   返回0-1内的随机数
select rand();

(5).round

求参数X的四舍五入的值，保留y位小数

#--round   求参数X的四舍五入的值，保留y位小数
select round(3.69,2);
# 3.69

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

select lpad(round(rand()*1000000,0),6,'0');
# 453021

4.日期函数

在这里插入图片描述

#--curdate  返回当前日期
select curdate();

#--curtime  返回当前时间
select curtime();

#--now   返回当前日期和时间
select now();

#--year  获取指定date的年份
select year(curdate());

#--month  获取指定date的月份
select month(curdate());

#--day  获取指定date 的日期
select day(curdate());

date_add

返回一个日期/时间值加上一个时间间隔expr后的时间值

#--date_add  返回一个日期/时间值加上一个时间间隔expr后的时间值
select date_add(now(),interval 70 day );
# 2024-02-06 21:13:36

datediff

返回起始时间date1和结束时间date2之间的天数

#--datediff  返回起始时间date1和结束时间date2之间的天数
select datediff('2023-6-8','2023-11-28');
# -173

注意：此时间差是前面的时间 - 后面的时间

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

select name,datediff(curdate(),entryDate) as 'entryDays' from emp order by entryDays desc ;

5.流程控制函数

在这里插入图片描述

#--流程控制函数
#--if
select if(true, 'OK','Error');


#--ifNull
select ifnull('ok','Default');

select ifnull('','Default');

select ifnull(null,'Default');

#--case when then else end
#--需求：查询emp表的员工姓名和工作地址（北京/上海---》一线城市，其他----》二线城市）
select
    name,
    (case workAddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工组地址'
from emp;

案例：统计班级各个学员的成绩，展示的规则如下

#–>=85 展示优秀
#-- >=60 展示及格
#–否则展示不及格

创建学生的分数表

create table score(
    id int comment '10',
    name varchar(20) comment '姓名',
    math int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
)comment '学员成绩表';
insert into score(id, name, math, english, chinese) VALUES (1,'Tom',67,88,95),(2,'Rose',23,66,90),(3,'Jack',56,98,76);

实现需求

select *from score;

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 score;

二.约束

1.概念

约束是作用于表中字段上的规则，用于限制存储在表中的数据

2.目的

保证数据库中数据的正确，有效性和完整性

3.分类

在这里插入图片描述
注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

create table user(
    id int primary key auto_increment comment '主键',
    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 '用户表';

#--插入数据
insert into user( name, age, status, gender) values ('Tom1',19,'1','男'),('Tom2',25,'0','男');
insert into user(name, age, status, gender) values ('Tom3',19,'1','男');

insert into user(name,age,status,gender) values (null,19,'1','男'); # 验证not  null约束条件
insert into user(name,age,status,gender) values ('Tom3',19,'1','男'); # 验证unique唯一约束条件

insert into user(name,age,status,gender) values ('Tom4',80,'1','男');
insert into user(name,age,status,gender) values ('Tom5',-1,'1','男'); # 验证大于0
insert into user(name,age,status,gender) values ('Tom4',121,'1','男'); # 验证小于120

insert into user(name,age,gender) values ('Tom5',120,'男'); # 验证default 默认约束条件

在这里插入图片描述

4.外键约束

概念:外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性
在这里插入图片描述

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

在这里插入图片描述

三.多表查询

概述：指从多张表中查询数据
在这里插入图片描述

1.多表关系

在这里插入图片描述

create table tb_user(
    id int auto_increment primary key comment '主键ID',
    name varchar(50) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '1:男,2:女',
    phone char(11) comment '手机号'
)comment '用户基本信息表';

create table tb_user_edu(
    id int auto_increment primary key comment '主键ID',
    degree varchar(20) comment '学历',
    major varchar(50) comment '专业',
    primarySchool varchar(50) comment '小学',
    middleSchool varchar(50) comment '中学',
    university varchar(50) comment '大学',
    userId int unique comment '用户ID',
    constraint fk_userId foreign key (userId) references tb_user(id)
)comment '用户教育信息表';


insert into tb_user(id, name, age, gender, phone) values
          (null,'黄渤',45,'1','18800001111'),
          (null,'冰冰',35,'2','18800002222'),
          (null,'码云',55,'1','18800008888'),
          (null,'李彦宏',50,'1','18800009999');

insert into tb_user_edu(id, degree, major, primarySchool, middleSchool, university, userId) values
               (null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
               (null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
               (null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
               (null,'本科','应用数学','阳泉区第一小学','阳泉区第一中学','清华大学',4);

在这里插入图片描述

create table student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
)comment '学生表';
insert into student values (null,'黛绮丝','200100101'),(null,'谢逊','200100102'),(null,'殷天正','200100103'),(null,'韦一笑','200100202');


create table course(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
)comment '课程表';
insert into course values (null,'java'),(name,'PHP'),(null,'MySQL'),(null,'Hadoop');


create table student_course(
    id int auto_increment comment '主键' primary key ,
    studentId int not null comment '学生ID',
    courseId int not null comment '课程ID',
    constraint fk_courseId foreign key (courseId) references course(id),
    constraint fk_studentId foreign key (studentId) references student(id)
)comment '学生课程中间表';

insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

2.内连接

在这里插入图片描述

-- 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.查询每一个员工的姓名，及关联的部门的名称（显式内连接实现） ---inner join...on...
-- 表结构：emp，dept
-- 连接条件： emp.dept_id = dept.id
select emp.name,d.name
from dept,emp inner join dept d on emp.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 d.id = e.dept_id;

-- 2.查询dept表的所有数据，和对应的员工信息（右外连接）

select d.*,e.* from emp e right join dept d on d.id = e.dept_id;
select d.*, e.* from dept d left join emp e on d.id = e.dept_id;

4.自连接

在这里插入图片描述
代码演示：

-- 自链接
-- 1.查询员工及其所属领导的名字
-- 表结构：emp  看成两张表

select a.name '员工名字',b.name '领导名字' from emp a,emp b where a.managerId = b.id;


-- 2.查询所有员工emp及其领导的名字，如果员工没有领导，也需要查询出来

select a.name '员工',b.name '领导' from emp a left join emp b on a.managerId = b.id;

5.联合查询

在这里插入图片描述
代码演示：

-- 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 -- 去重
select * from emp where age > 50 ;

注意：
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
union all 会将全部的数据直接合并在一起，union会对合并之后的数据去重

6.子查询

在这里插入图片描述

(1).标量子查询

在这里插入图片描述
概念：子查询返回的结果是单个值（数字，字符串，日期等），最简单的形式，这种子查询为标量子查询
常用的操作符：= <> > >= < <=
代码演示：

-- 标量子查询
-- 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).列子查询

子查询返回的结果是一列（可以是多列），这种子查询称为列子查询
常用的操作符：IN . NOT IN , ANY , SOME , 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 = '财务部';
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.查询比研发部其中任意一人工资高的员工信息
-- a.查询研发部所有人员工资
select * from emp where dept_id = (select id from dept where name = '研发部');

-- b.比研发部其中任意一人工资高的员工信息
-- any some 都一样
select * from emp where salary > any (select * from emp where dept_id = (select id from dept where name = '研发部'));

(3).行子查询

子查询返回的结果是一行（可以是多行），这种子查询称为行子查询
常见的操作符： = , <> , 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 = '张无忌');

(4).表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询
常用的操作符：IN
代码演示：

-- 表子查询
-- 1.查询与“鹿港可”，“宋远桥”的职位和薪资相同的员工信息
-- a.查询“鹿港可”，“宋远桥”的职位和薪资
select salary,job from emp where name = '鹿港可' or name = '宋远桥';

-- b.查询与鹿港可”，“宋远桥”的职位和薪资相同的员工信息
select * from emp where (job,salary) in (select salary,job from emp where name = '鹿港可' or name = '宋远桥');

四.事务

概述：事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败
在这里插入图片描述
第一种：需要把事务设置为手动提交

代码演示：

select @@autocommit;-- 查看事务是自动提交还是手动提交

select @@autocommit = 1; -- 设置为手动提交

-- 转账操作（张三给李四转账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 ;


-- 回滚事务
rollback ;

第二种：不需要改变事务提交方式，但需要开启事务
在这里插入图片描述
代码演示：

-- 方式二
-- 转账操作（张三给李四转账1000员）
start transaction ;

-- 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 ;

-- 回滚事务
rollback ;

事务的四大特性：
在这里插入图片描述
并发事务问题：

事务隔离级别：

注意：事务隔离级别越高，数据越安全，但是性能越低

learn_more1

关注

11
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL——数据库学习基础：函数，约束，多表查询，事务

是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作。概念：子查询返回的结果是单个值（数字，字符串，日期等），最简单的形式，这种子查询为。案例：企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。子查询返回的结果是一列（可以是多列），这种子查询称为。子查询返回的结果是一行（可以是多行），这种子查询称为。子查询返回的结果是多行多列，这种子查询称为表子查询。
复制链接

扫一扫