mysql数据库【基础】

Alan and fish

已于 2023-11-27 08:51:04 修改

阅读量164

点赞数 1

分类专栏：数据库 # 关系型数据库文章标签：数据库 mysql

于 2023-11-02 09:45:50 首次发布

本文链接：https://blog.csdn.net/qq_35653657/article/details/133957613

版权

关系型数据库同时被 2 个专栏收录

12 篇文章 0 订阅

订阅专栏

数据库

9 篇文章 0 订阅

订阅专栏

本教程适合有一定基础的人，我是用来复习mysql数据，跟着教程走一遍熟悉一下mysql的语句

数据准备

下面的数据库查询语句都是基于此表进行查询的

员工表

创建表：

-- 创建表
drop table if exists emp;
create table emp
(
    id          int comment '编号',
    workno      varchar(10) comment '工号',
    name        varchar(10) comment '姓名',
    gender      char(1) comment '性别',
    age         tinyint unsigned comment '年龄',
    idcard      char(18) comment '身份证号',
    workaddress varchar(50) comment '工作地址',
    entrydate   date comment '入职时间'
) comment '员工表';

插入数据：

-- 插入数据
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (1, '00001', '柳岩666', '女', 20, '123456789012345678', '北京', '2000-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (2, '00002', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (13, '00013', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');

成绩表

创建表：

create table score
(
    id      int comment 'ID',
    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);

1.DDL 数据定义语言

1.1 数据库操作

-- 1.查询所有数据库
show
databases
-- 2.查询当前数据库
select database()
-- 3.创建数据库
create
database if not exists itcast
-- 创建带有字符集编码的数据库
create
database if not exists itheima DEFAULT CHARSEt utf8mb4
-- 4.删除数据库
drop
test
-- 5.使用数据库
use mysql

1.2 表查询

-- 1.查询当前数据库的所有表
show tables;

-- 2.查询表结构
desc db

-- 3.查询指定表的创建语句
show
create table db

1.3 表创建

在数据库优化中，能用char的尽量不使用varchar，因为varchar会浪费很多存储空间

-- 创建表
create table tb_user
(
    id     int comment '编号',
    name   varchar(50) comment '姓名',
    age    int comment '年龄',
    gender varchar(1) comment '性别'
)comment '用户表'

根据需求创建一个表：

-- 设计一张员工信息表，要求如下:
-- 1.编号(纯数字)
-- 2.员工工号 (字符串类型,长度不超过10位)
-- 3. 员工姓名(字符串类型，长度不超过10位)
-- 4.性别(男/女,存储- -个汉字)
-- 5. 年龄(正常人年龄,不可能存储负数)
-- 6. 身份证号(二代身份证号均为18位,身份证中有X这样的字符)
-- 7. 入职时间(取值年月日即可)

create table emp
(
    id         int comment '编号',
    uid        varchar(10) comment '员工工号',
    name       varchar(10) comment '员工姓名',
    gender     char(2) comment '性别',
    age        tinyint comment '年龄',
    id_card    char(18) comment '身份证号',
    entry_date date comment '入职时间'
)comment '员工信息表'

1.4 表修改

-- 1.添加字段
alter table emp
    add nickname varchar(20)

-- 2.表数字段
alter table emp modify nickname varchar (10)

-- 3.修改字段类型和字段名
alter table emp change gender sex char (2) comment '性别'

-- 4.修改表名
alter table emp rename to employ

1.5 表删除

--1. 删除字段
alter table emp drop nickname

-- 2.删除表
drop table if exists tb_user

-- 3.清空表
    TRUNCATE table employ
    desc emp
    show tables

2. DML数据操作语言

2.1 插入语句

-- 1.给指定字段添加数据
insert into employ(id, uid, name, sex, age, id_card, entry_date)
values (1, '1', 'Itcast', '男', 38, 123456789, '2000-01-01');
-- 2. 给全部字段添加数据
insert into employ
values (2, '2', '张无忌', '男', 19, 4554656265, '2022-05-30');
-- 3.批量添加数据
insert into employ
values (3, '3', '赵敏', '女', 19, 4354656265, '2022-05-30'),
       (4, '4', '小昭', '女', 15, 4454656265, '2022-05-30'),
       (5, '5', '金毛狮王', '男', 50, 4554656265, '2022-05-30'),
       (6, '6', '段誉', '男', 20, 4654656265, '2022-05-30'),
       (7, '7', '韦一笑', '男', 19, 4754656265, '2022-05-30');

2.2 修改语句

-- 1.修改id为1的数据，将name修改为itheima
update employ
set name='itheima'
where id = 1;

-- 2.修改id为1的数据，将name修改为周芷若,性别修改为女
update employ
set name='周芷若',
    sex='女'
where id = 1;
-- 3.将所有的员工的入职日期修改为 2008-01-01
update employ set entry_date='2008-01-01';

2.3 删除语句

-- 1.删除性别为女的员工
delete from employ where sex='女';

-- 2.删除所有员工
delete  from employ;

3.DQL 数据查询语言

3.1 基本查询

-- 1.查询指定字段name, workno, aqe 返回
select name, workno, age
from emp;

-- 2.查询所有字段返回
select *
from emp;

-- 3.查询所有员工的工作地址，起别名
select workaddress as '工作地址'
from emp;
-- as也可以省略
select workaddress '工作地址'
from emp;

-- 4.查询公司员工的上班地址(不要重复)
select DISTINCT workaddress
from emp;

3.2 条件查询

-- 1.查询年龄等于88的员工
select *
from emp
where age = 88;

-- 2.查询年龄小于20的员工信息
select *
from emp
where age < 20;

-- 3.查询年龄小于等于20的员工信息
select *
from emp
where age <= 20;

-- 4.查询没有身份证号的员工信息
select *
from emp
where idcard is null;

-- 5.查询有身份证号的员工信息
select *
from emp
where idcard is not null;

-- 6.查询年龄不等于88的员工信息
select *
from emp
where not age = 88;

-- 7.查询年龄在15岁(包含)到20岁(包含)之间的员工信息
select *
from emp
where age >= 15
  and age <= 20;
  
select *
from emp
where age >= 15 && age <= 20;

select *
from emp
where age between 15 and 20;

-- 8.查询性别为女且年龄小于25岁的员工信息
select *
from emp
where age < 25
  and gender = '女';

-- 9.查询年龄等于18或20或40的员工信息
select *
from emp
where age = 18
   or age = 20
   or age = 40;
   
select *
from emp
where age in (18, 20, 40);

-- 10. 查询姓名为两个字的员工信息
select *
from emp
where name like '__';

-- 11. 查询省份证最后一位是X的员工
select *
from emp
where idcard like '%X';

select *
from emp
where idcard like '_________________X';

3.3 聚合查询

-- 1.统计该企业员工数量
select count(*) from emp;
-- 用下面这种方式计算，一旦出现了null就会少算
select count(idcard) from emp;

-- 2.统计该企业员工的平均年龄
select avg(age) from emp;

-- 3.统计该企业员工的最大年龄
select max(age) from emp;

-- 4.统计该企业员工的最小年龄
select min(age) from emp;

-- 5.统计在西安地区的员工年龄之和
select sum(age) from emp where workaddress='西安';

3.4 分组查询

在分组查询的时候需要使用到group by语句和having语句以及where语句，那么having语句和where语句有什么区别呢？

执行时机不同: where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤。
判断条件不同: where不能对聚合函数进行判断，而having可以。

分组查询一般查询分组条件和聚合查询结果

-- 1.根据性别分组，统计男性员工和女性员工的数量
select gender, count(*)
from emp
group by gender;

-- 2.根据性别分组,统计男性员工和女性员工的平均年龄
select gender, avg(age)
from emp
group by gender;

-- 3.查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress, count(*)
from emp
where age < 45
group by workaddress
having count(*) > 3;

select workaddress, count(*) as address_count
from emp
where age < 45
group by workaddress
having address_count > 3;

3.5 排序查询

排序查询中排序条件：

ASC:升序（默认）
DESC: 降序
如果是多字段排序，首先按照第一个字段排序，然后再按照第二个字段排序。

-- 1.根据年龄对公司的员工进行升序排序
select *
from emp
order by age asc;

-- 2.根据入职时间，对员工进行降序排序
select *
from emp
order by entrydate asc;

-- 3.根据年龄对公司的员工进行升序排序， 年龄相同， 再按照入职时间进行降序排序
select *
from emp
order by age asc, entrydate desc;

3.6 分页查询

语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

注意
● 起始索引从0开始，起始索引= (查询页码-1) *每页显示记录数。
● 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
● 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

-- 1.查询第1页员工数据，每页展示10条记录
select  * from emp limit 0,10;
-- 第一页可以省略起始索引
select  * from emp limit 10;

-- 2.查询第2页员工数据，每页展示10条记录-------->因为每页的展示数量是10，则第二页的开始索引是10
select  * from emp limit 10,10

3.7 DQL语句练习

-- 1.查询年龄为20,21,22,23岁 的员工信息。
select * from emp where age in(20,21,22,23);

-- 2.查询性别为男,并且年龄在20-40岁(含)以内的姓名为三个字的员工。
select * from emp where age between 20 and 40 and name like '___';

-- 3.统计员工表中,年龄小于60岁的,男性员工和女性员工的人数。
select gender,count(*) from emp where age<60 group by gender;

-- 4.查询所有年龄小于等于35岁员工的姓名和年龄,并对查询结果按年龄升序排序,如果年龄相同按入职时间降序排序。
select name,age from emp where  age<35 order by age ASC ,entrydate desc ;

-- 5.查询性别为男，且年龄在20-40 岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
select * from emp where gender='男' and age between 20 and 40 order by age ASC,entrydate DESC limit 5;

4. DCL 数据控制语言

4.1 用户管理

注意：

主机名可以使用%统配
这类SQL开发人员操作比较少，主要是DBA（数据库管理员）使用

-- 创建用户itcast ，只能够 在当前主机LocaLhost访问，密码123456;
create user 'itcast'@'localhost' identified by '123456';

-- 创建用户heima ，可以在任意主机访问该数据库，密码123456 ;
create user 'itheima'@'%' identified by '123456';

-- 修改用户heima 的访问密码为1234
alter user 'itheima'@'%' identified with mysql_native_password by '1234';

-- 删除itcast@LocaLhost用户
drop user 'itheima'@'%';

4.2 权限管理

注意：

多个权限之间，使用逗号分隔
授权时，数据库名和表名可以使用* 进行通配，代表所有。

-- 查询权限
show grants for 'itcast'@'localhost';

-- 授予权限
grant all on itcast.* to 'itcast'@'localhost';

-- 撤销权限
revoke all on itcast.* from 'itcast'@'localhost';

5.函数

这里只是介绍几种常见的函数，基本函数语法

5.1 字符串函数

-- concat：拼接
select concat('Hello','MySQL');

-- lower：转小写
select lower('HellO');

-- upper：转大小
select UPPER('Hello');

-- lpad:左填充
select lpad('01',5,'-');

-- rpad：右填充
select rpad('01',5,'*');

-- trim:去除空格
select trim('Hello MySQL');

-- substring：截取长度,mysql中的起始索引是从0开始
select substring('hello mysql',1,5);

案例：

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

5.2 数值函数

函数语法：

-- ceil：向上取整
select ceil(1.1);

-- floor：向下取整
select floor(1.1);

-- mod：取模
select mod(3,4);

-- rand:随机数
select rand();

-- round：四舍五入,保留两位小数
select round(2.34556,2);

举个例子：

-- ceil：向上取整
select ceil(1.1);

-- floor：向下取整
select floor(1.1);

-- mod：取模
select mod(3,4);

-- rand:随机数
select rand();

-- round：四舍五入,保留两位小数
select round(2.34556,2);

5.3 日期函数

函数语法：

-- curdate(): 当前日期
select curdate();

-- curtime()：当前时间
select curtime();

-- now()：当前的时间和日期
select now();

-- 查看年、月、日
select YEAR(now());

select MONTH(now());

select DAY(now());

-- 时间后退
-- 当前时间向前70天
select date_add(now(), INTERVAL 70 DAY);
-- 当前时间向前5个月
select date_add(now(), INTERVAL 5 MONTH);

-- datediff： 查询两个时间之间的间隔,第一个时间减去第二个时间
select datediff('2021-10-01', '2023-10-22');
select datediff('2023-10-01', '2021-10-22');

举个例子：

-- 案例： 查询所有员工入职的天数吗，并根据入职天数进行倒叙排序
select name, datediff(now(), entrydate) as '入职天数'
from emp
order by datediff(now(), entrydate) desc;

5.4 流程函数

函数语法：

-- if,第一个参数是需要判断的参数
select if(false, 'ok', 'default');

-- ifnull
-- 如果第一个参数是null，就返回第二个，否则就返回第一个
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,展示优秀
-- >= 6日，展示及格
-- 否则，展示不及格
select name,(case when math>=85 then '优秀' when math>=60 then '及格' else '不及格' end) '数学',
       (case when english>=85 then '优秀' when english>=0 then '及格' else '不及格' end) '英语',
       (case when chinese>=85 then '优秀' when chinese>=60 then '及格' else '不及格' end) '语文' from score;

6.约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
目的：保证数据库中数据的正确、有效性和完整性。

6.1 约束演示

语法举例，创建表的时候给表添加约束：

-- 创建表格
drop table if exists tb_user;
create table tb_user(
    id int AUTO_INCREMENT primary key comment 'ID唯一标识',-- 主键约束
    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 '性别'
);
-- 验证约束
insert into tb_user(name,age,status,gender) values ('Tom1',19,'1','男'),
('Tom2',25,'0','男');

insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男');
insert into tb_user(name,age,status,gender) values (null,19,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom3',19,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom4',80,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom5',-1,'1','男');
insert into tb_user(name,age,status,gender) values ('Tom5',121,'1','男');
insert into tb_user(name,age,gender) values ('Tom5',120,'男');

6.2 外键约束

数据准备：

-- 创建一个部门表
create table dept
(
    id   int primary key comment '部门ID',
    name varchar(10) comment '部门名称'
);

-- 插入数据
insert into dept
values (001, '研发部'),
       (002, '市场部'),
       (003, '财务部'),
       (004, '销售部'),
       (005, '总经办');

6.2.1 添加外键

创建一个employ表，并且添加外键

-- 创建一个员工表，并给给员工表添加外键
drop table if exists employ;
create table employ
(
    id        int auto_increment comment 'ID' primary key,
    name      varchar(50) not null comment '姓名',
    age       int check (age > 0 && age < 70) comment '年龄',
    job       varchar(20) comment '职位',
    salary    float comment '薪水',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id   int comment '部门ID' ,
    constraint fk_employ_dept foreign key (dept_id) references dept(id) -- 可以直接在创建表的时候添加外键
) comment '员工表';

或者外部添加外键：

-- 插入数据
INSERT INTO employ (id, name, age, job, salary, entrydate, managerid, dept_id)

给employ表插入数据

INSERT INTO employ (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
       (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
       (3, '杨逍', 33, '开发', 8400, ' 2000-11-03', 2, 1),
       (4, '韦一笑', 48, '开发', 11000, ' 2002-02-05', 2, 1),
       (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
       (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);

6.2.2 删除外键

-- 删除外键
alter table employ drop foreign key  fk_employ_dept;

6.2.3 外键更新\删除行为

-- CASCADE:级联更新删除
alter table employ
    add constraint fk_employ_dept foreign key (dept_id) references dept(id) on update cascade on delete cascade;

-- SET NULL:设置为空
alter table employ
    add constraint fk_employ_dept foreign key (dept_id) references dept(id) on update set null on delete set null;

7.多表查询

7.1 多表关系

7.1.1 一对多（多对一）

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

7.1.2 多对多

案例: 学生与课程的关系
关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

脚本代码：

-- 创建学生表
create table student
(
    id   int auto_increment primary key comment '主键ID',
    name varchar(20) comment '姓名',
    no   char(11) comment '学号'
) comment '学生表';

-- 创建课程表
create table course
(
    id   int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';

-- 创建中间表
create table student_course
(
    id         int auto_increment primary key comment '主键ID',
    student_id int comment '学生ID',
    course_id  int comment '课程ID',
    constraint fk_course_id foreign key (course_id) references course (id),
    constraint fk_student_id foreign key (student_id) references student (id)

) comment '学生课程中间表';

-- 插入数据
insert into student
values (null, '黛绮丝', '2000100101'),
       (null, '谢逊', '2000100102'),
       (null, '殷天正', '2000100103'),
       (null, '韦一笑', '2000100104');

insert into course
values (null, 'Java'),
       (null, 'PHP'),
       (null, 'MySQL'),
       (null, 'Hadoop');

7.1.3 一对一

案例: 用户与用户详情的关系
关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另
一张表中，以提升操作效率
实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

脚本代码

drop table if exists tb_user;
create table tb_user
(
    id     int auto_increment primary key comment '主键ID',
    name   varchar(10) 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);

7.2 多表查询概述

7.2.1 数据准备

执行下面的脚本，创建对应的表，并且添加数据：

-- 删除其他数据
drop table employ;
drop table if exists dept;
drop table if exists emp;

-- 创建dept表，并插入数据
create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';

INSERT INTO dept (id, name)
VALUES (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4,'销售部'),
       (5, '总经办'),
       (6, '人事部');


-- 创建emp表，并插入数据
create table emp
(
    id        int auto_increment comment 'ID' primary key,
    name      varchar(50) not null comment '姓名',
    age       int comment '年龄',
    job       varchar(20) comment '职位',
    salary    int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id   int comment '部门ID'
) comment '员工表';

-- 添加外键
alter table emp
    add constraint fk_emp_dept_id foreign key (dept_id) references dept (id);

INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
       (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
       (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
       (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
       (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
       (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1),
       (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3),
       (8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3),
       (9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3),
       (10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2),
       (11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2),
       (12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2),
       (13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2),
       (14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4),
       (15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4),
       (16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4),
       (17, '陈友谅', 42, null, 2000, '2011-10-12', 1, null);

7.2.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 e.name, d.name
from emp e inner join dept d on e.dept_id = d.id;

select e.name, d.name
from emp e join dept d on e.dept_id = d.id;

-- 外连接
-- 左外连接：完全包含左表和与右表交集的数据
-- 1. 查询emp表的所有数据， 和对应的部「1信息(左外连接)
-- 表结构：emp，dept
-- 连接条件：emp.dept_id=dept.id
select e.*, d.name
from emp e left 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;

7.2.3 自查询

自查询是同一个表进行关联查询，所以每一个表都必须有自己的别名

-- 1.查询员工及其所属领导的名字
-- 表必须取别名
select a.name, b.name as '上级领导'
from emp a join emp b on a.managerid = b.id;

select a.name, b.name as '上级领导'
from emp a, emp b where a.managerid = b.id;


-- 2.查询所有员工emp及其领导的名字emp，如果员 工没有领导，也需 要查询出来
select a.name, b.name as '上级领导'
from emp a left join emp b on a.managerid = b.id;

7.2.4 联合查询

联合查询就是将两张表合并到一起，举个例子：

-- 查询员工表中薪水小于5000和年龄大于50的员工信息
select * from emp where salary<5000
union
select * from emp where age>50

7.2.5 子查询

标量子查询

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

-- b.根据销售部的id 查询员工的信息
select * from emp where dept_id=(select dept.id from dept where name='销售部');

-- 2.查询在方东白入职之间的员工信息
select * from emp where entrydate>(select entrydate from emp where name='方东白');

列子查询

- 1.查询‘销售部’和‘市场部’的所有员工的信息
select * from emp where dept_id in (select dept.id from dept where name in('市场部' , '销售部'));

-- 2.查询比财务部所有人工资都高的人的信息
select * from emp where salary>all (select salary from emp where dept_id =(select dept.id from dept where name='财务部'));

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

行子查询
- 子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询。
- 常用的操作符: = 、<>、IN、NOT IN

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

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

表子查询
- 子查询返回的结果是多行多列，这种子查询称为表子查询。
- 常用的操作符: IN

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

8.事务

8.1 事务简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
就比如: 张三给李四转账1000块钱，张三银行账户的钱减少1000，而李四银行账户的钱要增加
1000。这一组操作就必须在一个事务的范围内，要么都成功，要么都失败。
在这里插入图片描述

8.2 事务操作

8.2.1 数据准备

首先创建一个account表，模拟银行转账操作：

drop table if exists account;
create table account(
id int primary key AUTO_INCREMENT comment 'ID',
name varchar(10) comment '姓名',
money double(10,2) comment '余额'
) comment '账户表';

当前account数据库中的结果：
在这里插入图片描述

8.2.2 模拟转账操作（引例）

-- 1.查询张三账户余额
select money from account where name='张三';

-- 2.将张三账户余额-1000
update account set money=money-1000 where name='张三';

-- 3.将李四账户余额+1000
update account set money=money+1000 where name='李四';

在这里插入图片描述
转账成功；如果此时第三步的时候出现了异常，会出现什么情况呢？

-- 1.查询张三账户余额
select money from account where name='张三';

-- 2.将张三账户余额-1000
update account set money=money-1000 where name='张三';

程序抛出异常...
-- 3.将李四账户余额+1000
update account set money=money+1000 where name='李四';

在这里插入图片描述
此时就会出现张三转账过去了，但是李四没有收到钱的情况，为了解决这个问题就需要使用到事务。mysql中的每条语句都是具有事务的，并且是自定提交了，因此我们就需要把上面几条sql语句绑定成一个事务中。

8.2.3 方式一：手动提交事务

-- 查询当前的事务状态
-- 方式1：
select @@autocommit;-- 等于1，说明是自动提交事务，等于0，说明是手动提交事务

set @@autocommit=0;-- 设置为手动提交事务

-- 1.查询张三账户余额
select money 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；

8.2.4 方式二：自动提交事务

先设置提交方式为自动提交：

set @@autocommit=1;-- 设置为自动提交事务

-- 开启事务
start transaction ;

-- 1.查询张三账户余额
select money 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

rollback；

8.3 事务的特性

原子性(Atomicity) :事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性(Consistency) :事务完成时，必须使所有的数据都保持一致状态。
隔离性(Isolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性(Durability) :事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
事务的四大特性简称为ACID

8.4 并发事务问题

问题	描述
脏读	一个事务读到另外一个事务还没有提交的数据。
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影”。

8.4.1脏读

在这里插入图片描述
假如A事务中有查询和更新两个操作，在A事务还没执行完之间，事务B就开始对数据库进行查询，则查询到的数据就是未修改的数据，等它查询完之后，数据库也被修改了。

8.4.2 不可重复读

在这里插入图片描述
首先A事务对数据库进行查询操作，在A事务还没有执行完之前，事务B对数据库进行了修改，此时事务A又对数据库进行一次查询操作，此时的查询结果与第一次查询的结果就不一致了，这种情况就被称为不可重复读。

8.4.3 幻读

假设解决了不可重复读的问题。
在这里插入图片描述
首先事务A先对数据库查询id为1的数据，但是没有查询到，所以它的下一步是将这个没有查询到的数据插入进去，在这步操作前，假设事务B对数据库已经完成了插入操作，此时事务A再向数据库中插入id为1的数据，此时却插入不了，因为id为主键，数据库中已经存在了id为1的数据，但是再查询这个数据却也查不到，因为解决了不可重复读的问题，这个过程就好像发生了幻觉一样，因此被称为幻读。

8.5 事务隔离级别

隔离级别	脏读	不可重复读	幻读
Read Uncommitted（已读未提交）	√	√	√
Read Commit（已读提交）	×	√	√
Repeatable Read（可重复读）	×	×	√
Serializable（串行化）	×	×	×

mysql的默认事务隔离级别是Repeatable Read
注意：事务的隔离级别越高，数据约安全，但是性能越低；

8.5.1 事务隔离级别操作语句

设置事务隔离级别的语法
set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable}

-- 查看事务的隔离级别,默认的隔离级别就是REPEATABLE-READ
select @@transaction_isolation;

-- 设置隔离级别
set session transaction isolation level  read uncommitted ;
set session transaction isolation level  REPEATABLE READ;

8.5.2 验证每种隔离级别是否解决了对应的问题

模拟并发事务验证上面的结果：

验证Read Uncommitted是否会出现脏读
验证read committed是否解决了脏读问题
验证read committed没有解决不可重复读问题
验证repeatable read可以解决事务的不可重复读问题
验证repeatable read不能解决幻读问题
验证serializable是否能够解决幻读问题

Alan and fish

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
mysql数据库【基础】

- 1. 查询所有数据库showdatabases -- 2. 查询当前数据库select database() -- 3. 创建数据库createdatabase if not exists itcast -- 创建带有字符集编码的数据库createdatabase if not exists itheima DEFAULT CHARSEt utf8mb4 -- 4. 删除数据库droptest -- 5. 使用数据库use mysql-- 删除其他数据。
复制链接

扫一扫