MySQL数据库-(基础篇二)

1_菜鸟初学者

已于 2023-12-04 21:30:40 修改

阅读量130

点赞数

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

于 2023-10-23 09:59:03 首次发布

本文链接：https://blog.youkuaiyun.com/m0_48328683/article/details/133813251

版权

MySQL 专栏收录该内容

4 篇文章

订阅专栏

3.函数

3.1概念

函数是指一段可以直接被另一段程序调用的程序或代码.

如：数据库表中，存储的是入职日期，怎样快速计算入职天数？

数据库表中，存储的是学生的分数值，如90、56、30,怎样快速判定分数的等级呢？

3.2字符串函数

常用的函数如下:

SELECT 函数(参数);

示例：

SELECT CONCAT('Hello', 'MySQL');
SELECT UPPER('Hello');
SELECT LOWER('Hello');
select lpad('01', 5, '-');
SELECT rpad('01', 5, '-');
select trim(' Hello MySQL ');

3.3数值函数

常见数值函数:

SELECT ceil(1.1);
select floor(1.9);
select mod(7,4);
select rand();
select round(2.45,1)

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

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

3.4日期函数

常见日期函数:

select curdate(); #16:48:20
select curtime(); #16:48:20
select now(); #2023-10-13 16:48:08
select year(NOW()); #2023
select date_add(now(),INTERVAL 70 DAY); #2023-12-22 16:50:16
select datediff('2022-12-01','2022-08-01'); #122

例题：查询员工入职天数，并根据入职天数倒序排序

select name, datediff(curdate(), firstworktime ) entrydays from emp order by entrydays desc;

3.5流程函数

流程函数也是很常使用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率.

# 大于等于85优秀，大于等于60及格，其他不及格
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 score.chinese >= 85 then '优秀' when score.chinese >= 60 then '及格' else '不及格' end) '语文'
from score;

3.6函数小结

4.约束

4.1概述

1.概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

2.目的：保证数据库中数据的正确、有效性和完整性。

3.分类

4.2约束演示

create table user(
    id int primary key auto_increment,
    name varchar(10) not null unique ,
    age int check ( 0 < age <= 120 ),
    status char(1) default '1',
    gender char(1)
);

4.3外键约束

概念

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

注：目前上述的两张表，在数据库层面，并未建立连接，所以是无法保证数据的一致性和完整性的.

语法

-- 添加外键
alter table user add constraint fk_user_dept_id foreign key  (dept_id) references dept(id);
-- 删除外键
alter table user drop foreign key fk_user_dept_id;

删除/更新行为

ALTER TABLE user ADD CONSTRAINT 外键名称 foreign key (外键字段) REFERENCES 主表名(主表字段名) ON  UPDATE  CASCADE ON DELETE CASCADE ;

-- 添加外键
alter table user add constraint fk_user_dept_id foreign key  (dept_id) references dept(id);
-- 删除外键
alter table user drop foreign key fk_user_dept_id;
-- 添加外键
ALTER TABLE user ADD CONSTRAINT fk_user_dept_id foreign key (dept_id) REFERENCES dept(id) ON  UPDATE  CASCADE ON DELETE CASCADE;
ALTER TABLE user ADD CONSTRAINT fk_user_dept_id foreign key (dept_id) REFERENCES dept(id) ON  UPDATE  set null ON DELETE set null ;

4.4约束小结

5.多表查询

5.1多表关系

概述

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

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

一对多（多对一）

案例：部门与员工的关系
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方外键

多对多：

案例:学生与课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现：建立第三中间表，中间表至少包含两个外键，分别关联两方主键

5.2多表查询概述

5.3内连接

内连接查询语法：

隐式内连接

SELECT 字段列表 FROM 表1,表2 WHERE 条件...;

显示内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN表2 ON 连接条件...;

内连接查询的是两张表交集的部分

-- 内连接
-- 1.查询每个员工的姓名，及关系部门的名称（隐式内连接实现）
-- 表结构 user,dept
-- 内连接条件 user.dept_id = dept.id
select user.name, dept.name from user,dept where user.dept_id = dept.id;
-- 起别名后使用表名报错
select e.name,d.name from user e, dept d where e.dept_id = d.id;
-- 2.查询每个员工的姓名，及关系部门的名称（显示内连接实现）
-- 表结构 user,dept
-- 内连接条件 user.dept_id = dept.id
select u.name,d.name from user u join dept d on u.dept_id = d.id;

5.4外连接

外连接查询语法：

左外连接

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;

相当于查询表1（左表）的所有数据和表2交集部分的数据

右外连接

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON条件...;

相当于查询表2[右表]的所有数据包含表1和表2交集部分的数据

案例

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

-- 2.查询dept表的所有数据，和对应得员工信息（右外连接）
-- 表结构: user,dept
-- 连接条件 user.dept_id = dept.id
select d.*,u.* from user u right outer join dept d on d.id = u.dept_id;

5.5自连接

查询语法

SELECT 字段列表 FROM 表A 别名A JOIN 表B 别名B ON 条件...;

自连接可以是内连接查询，可以是外连接查询.

把一张表看成两张表

案例

-- 自连接
-- 1.查询员工及其所属领导的名字
-- 表结构:user a user b
select a.name,b.name from user a ,user b where a.manageid = b.id;

-- 2.查询所有员工user及其领导的名字，没有领导也要查询
select a.name,b.name from user a left join user b on a.manageid = b.id;

联合查询 -union,union all

SELECT 字段列表 FROM 表A...

UNION[ALL]

SELECT 字段列表 FROM 表B...;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

union all会将全部的数据直接合并在一个起，union会对合并后的数据去重

5.6子查询

概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

子查询外部的语句可以是INSERT / UPDATE/ DELETE /SELECT 的任何一个

根据子查询结果不同，分为：

标量子查询（子查询结果为单个值）

列量子查询（子查询结果为一列）

行子查询（子查询结果为一行）

表子查询（子查询结果为多行多列）

根据子查询位置，分为：WHERE之后、FROM之后、SELECT之后。

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。

常用的操作符号：= <> > >= < <=

-- 标量子查询
-- 1.查询“市场部”的所有员工信息
-- a.查询“市场部”的id
select id from dept where name='市场部';
-- b.根据市场部id查询员工信息
select * from user where dept_id = 2;
select * from user where dept_id = (select id from dept where name='市场部');

列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询

常用操作符号

-- 列子查询
-- 1.查询“市场部“和”财务部“的所有员工信息
-- a.查询“财务部”和“市场部”的部门的id
select id from dept where name = '市场部' or name = '财务部';
-- b.根据部门id查询员工信息
select * from user where dept_id in(2,3);
select * from user where dept_id in(select id from dept where name = '市场部' or name = '财务部');
-- 2.查询比财务部所有人工资都高的员工信息
-- a.查询所有财务部人员工资
select salary from user where dept_id = (select id from dept where dept.name = '财务部');
-- b比财务部所有人工资都高的员工信息
select * from user where salary > all(select salary from user where dept_id = (select id from dept where dept.name = '财务部'));

-- 3.比研发部任意一人工资高的员工信息
-- a.查询研发部的id和人员工资
select id from dept where name = '研发部';
select salary from user where dept_id = (select id from dept where name = '研发部');

-- 查询比研发部任意一人工资高的员工信息
select * from user where salary > any (select salary from user where dept_id = (select id from dept where name = '研发部'));

行子查询：

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询

常用的操作符号：= 、<> 、IN 、NOT IN

-- 行子查询
-- 1.查询与“AAA"的薪资及直属领导相同的员工信息
-- a.查询'AAA'的信息和直属领导id
select salary,manageid from user where name = 'AAA';
-- 2.查询与“AAA"的薪资及直属领导相同的员工信息
select * from user where manageid = 1 and salary = 2000;
select * from user where (manageid,salary) = (select manageid,salary from user where name = 'AAA');

表子查询

子查询的返回结果是多行多列，这种子查询称为表子查询

常用的操作符号:IN

-- 表子查询
-- 1.查询与'AAA' ， ’BBB'的职位和薪资相同的员工信息
-- a.查询‘AAA’，'BBB'的职位和薪资
select salary, job from user where name = 'AAA' or name = 'BBB';
-- b.查询与'AAA' ， ’BBB'的职位和薪资相同的员工信息
select * from user where (salary,job) in (select salary, job from user where name = 'AAA' or name = 'BBB');

-- 2.查询入职日期为2023-09-17 17:37:28之后的的员工信息，及其部门
-- a.查询入职日期为2023-09-17 17:37:28之后的员工信息
select * from user where entrytime > '2023-09-17 17:37:28';
-- b.查询员工信息，及其部门
select e.*, d.name from (select * from user where entrytime > '2023-09-17 17:37:28') as e left join dept d on e.dept_id = d.id;

5.7多表查询案例

-- 1.查询员工的姓名、年龄、职位、部门信息（隐式内连接）
-- 表：emp, dept
-- 连接条件：user_id = dept.id
-- 显示内连接
select u.name,u.age,u.job,d.name from user u join dept d on u.dept_id = d.id;
-- 隐式内连接
select u.name,u.age,u.job,d.name from user u, dept d where u.dept_id = d.id;
-- 2.查询年龄小于30的员工的姓名、年龄、职位、部门信息（显示内连接）
-- 表：emp, dept
-- 连接条件：user_id = dept.id
select u.name,u.age,u.job,d.name from user u join dept d on d.id = u.dept_id where u.age < 30;
-- 3.查询拥有员工的部门id、部门名称
-- 表：emp, dept
-- 连接条件：user_id = dept.id
select distinct d.id, d.name from user u, dept d where u.dept_id = d.id;

-- 4.查询所有年龄大于40的员工，及其归属部门名称；如果员工没有分配部门，也需要展示出来
-- 表：user, dept
-- 连接条件：user_id = dept.id
-- 外连接

select u.*,d.name from user u left join dept d on u.dept_id = d.id where u.age > 40;
-- 5.查询所有员工的工资等级
-- 表：user，salgrade
-- 连接条件: user.salaryo >= salgrade.losal and user.salary <= saltrade.hisal
select u.*,s.grade from user u,saltrade s where u.salary >= s.losal and u.salary <= s.hisal;
-- 6.查询“研发部”所有员工的信息及工资等级
-- 表：user,salgrade,dept
-- 连接条件: user.salary between salgrade.losal and  saltrade.hisal, user.dept_id = dept.id
-- 查询条件：dept.name = “研发部”
select u.*,s.grade from user u,dept d,saltrade s where u.dept_id = d.id and (u.salary between s.losal and s.hisal) and d.name = '研发部' ;

-- 7.查询“研发部”员工的平均工资
-- 表:user, dept
-- 连接条件 :user.dept_id = dept.id
select avg(u.salary) from user u,dept d where u.dept_id = d.id and d.name = '研发部';

-- 8.查询工资比小王高的员工信息
-- 表:user
select * from user where user.salary > (select salary from user where name = '小李');

-- 9.查询比平均工资高的员工信息
-- a.查询员工的平均工资
select avg(salary) from user;
-- b.查询比平均工资高的员工信息
select * from user where user.salary > (select avg(salary) from user);

-- 10.查询低于本部门平均工资的员工信息
-- a.查询指定部门平均薪资 1
select avg(u.salary) from user u where u.dept_id = 1;
-- b.查询低于本部门平均工资的员工信息
select * from user u2 where  u2.salary < (select avg(u.salary) from user u where u.dept_id = u2.dept_id);

-- 11.查询所有的部门信息,并统计部门的员工人数
select d.id,d.name,(select count(*) from user u where u.dept_id = d.id) '人数' from dept d;
select count(*) from user where dept_id = 1;
-- 12 查询所有学生的选课情况，展示学生名称，学号，课程名称
-- 表：student ， course ,student_course
-- 连续条件:student.id = student_course.studentid,course.id =student_course.courseid
select s.name,s.no,c.name from student s, student_course sc,course c where s.id =sc.studentid and sc.courserid = c.id;

6.事务

6.1事务简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功要么同时失败。

默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会隐式的提交事务

6.2事务操作

查看/设置事务提交方式

SELECT @@autocommit;
SET @@atuocommit=0;

提交事务
```
COMMIT;
```
回滚事务
```
ROLLBACK;
```
开启事务
```
START TRANSACTION 或 BEGIN;
```
提交事务
```
COMMIT;
```
回滚事务
```
ROLLBOCK;
```

6.3事务四大特性

原子性：（Atomicity）:事务是不可分割的最小操作单元，要么都成功，要么都失败。
一致性：（Consistency）:事务完成时，必须使所有的数据都保持一致状态.
隔离性（Isonlation）:数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性：（Durabiltiy）:事务一旦提交或者回滚，它对数据库的数据的改变就是永久的。

6.4并发事务问题

6.5事务隔离级别

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