数据库学习

安装配置mysql不想写

mysql语法介绍

1. sql语句可以单行或多行,分号结尾

2. 可缩进

3. 不区分大小写,关键字建议大写

4. 注释

– 单行注释

# mysql特有单行注释

/* 多行注释
*/

DDL 数据定义语言,定义数据库对象(数据库,表,字段)
DML 数据操作语言,对数据库表中进行增删改
DQL 数据查询语言,查询数据库中表的记录
DCL 数据控制语言,创建数据库用户,控制数据库的访问权限

DDL语法

数据库操作

查询所有数据库

SHOW DATABASES;

查询当前数据库

当不知道自己处在哪里时可以使用

SELECT DATABASE();

创建

CREATE DATABASE 数据库名
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]

[1] 如果数据存在那么不执行该语句,如果不存在则执行
[2] 指定字符集 ,数据库中utf8一个字符最多3个字节,但有的字符会占4个字节,可以使用utf8mb4
[3] 指定排序方式
写的时候方括号不要带

删除

DROP DATABASE [IF EXISTS] 数据库名

[1] 存在则删

使用

USE 数据库名;

表操作

查询当前数据库所有表(必须在某个数据库中)

show tables;

查询表结构

desc 表名

查询指定表的建表语句

show create table 表名

表创建

create table 表名
    (
        字段1 字段1类型 [COMMENT 字段1注释],
        字段2 字段2类型 [COMMENT 字段2注释],
        字段3 字段3类型 [COMMENT 字段3注释],
        字段4 字段4类型 [COMMENT 字段4注释]
    )[COMMENT 表注释];

示例

mysql> create table user
-> (
-> id int comment '编号',
-> name varchar(50) comment '姓名',
-> age int comment '年龄',
-> gender varchar(1) comment '性别'
-> );

数据类型

tinyint     1b
smallint    2b
mediumint   3b
int/integer 4b
bigint      8b
float       4b
double      8b
decimal     依赖于M(精度)和D(标度) 小数值(精确点数)
123.45 		精度123 标度45
数据类型之后加上unsigned可以用来做无符号
tinyint unsigned -> 0-255

字符串类型

char 		0-255B		定长字符串
varchar		0-65535B	变长字符串
tinyblob	0-255B		二进制数据
tinytext	0-255B		短文本字符串
blob		0-65535B	二进制形式的长文本数据
text		0-65535B	长文本数据
mediumblob	0-16777215B	二进制中等文本
mediumtext	~			中等文本
longblob	0-4294967295B极大二进制文本
longtext 	~			极大文本

日期类型

data		3	YYYY-MM-DD			日期值
time		3	HH:MM:SS			时间值或持续时间
year		1	YYYY				年份
datatime	8	YYYY-MM-DD HH:MM:SS	  混合日期和时间值
timestamp	4	YYYY-MM-DD HH:MM:SS	  混合日期和时间值,时间戳

样例 员工表

create table employee
    (
     id tinyint comment '编号',
     word_number varchar(10) comment '员工编号',
     name varchar(10) comment '员工姓名',
     gender char(1) comment '性别',
     age int unsigned comment '年龄',
     id_card char(18) comment '身份证号',
     entrance_date date comment '入职时间'
     )comment '员工表';

修改

添加字段

alter table 表名 add 字段名 类型(长度) [comment 注释] [约束] 

修改数据类型

alter table 表名 modify 字段名 新数据类型(长度)

修改字段名和字段类型

alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束]

删除字段

alter table 表名 drop 字段名

修改表名

alter table 表名 rename to 新表名

删除表

drop table [if exists] 表名

删除指定表,并重新创建该表

truncate table 表名

DataGrip

创建好项目后的链接操作,选择mysql

配置链接信息,同时点击下方下载驱动,下载好后test成功即可使用

此处选择所有架构,来让所有数据库显示,架构即为数据库,新建数据库时也是选择架构Schema

DML

对数据库的数据记录进行增删改操作

添加数据

给指定字段添加数据

insert into 表名(字段1,字段2,...) values(值1,值2,...)

给全部字段添加数据

insert into 表名 values(值1,值2...)

批量添加数据

insert into 表名 (字段1,字段2,...) values (值1,值2,...),(值1,值2,...),...

insert into 表名 values (值1,值2,...),(值1,值2,...),...

插入时如果是字符串或者日期应该在引号内

展示数据

在datagrip可以双击表打开,或者使用命令

select * from 表名

修改数据

update 表名 set 字段1=值1,字段2=值2,...[where 条件]

修改语句的条件可以没有,如果没有,则是修改整个表的数据

例

update employee set name='zhangsan' where id=2;

删除数据

delete from 表名 [where 条件]

条件没有则删除表里所有数据

delete不能删除某个字段的值,设成null即可

目前已使用的命令集合

show databases;
create database if not exists test1;
create database if not exists DML_Study;
use DML_Study;
select database();
desc employee;
insert into employee (id, work_number, name, gender, age, id_card, entrance_date, nickname) values(1,'1','zhangsan','男',18,'123456789012345678','2000-01-01','zhangsan');
alter table employee drop nickname;
select * from employee;
-- 此为无效命令
insert into employee (id, work_number, name, gender, age, id_card, entrance_date) values(1,'1','zhangsan','男',-1,'123456789012345678','2000-01-01');
insert into employee values(2,'2','lisi','男',19,'123456789012345679','2002-02-03');
select * from employee;

update employee set name='zhangsan' where id=2;
select * from employee;
update employee set name='xiaozhao',gender='女' where id=1;
update employee set entrance_date='2008-1-1';

-- 删除数据
delete from employee where id = 5;
select * from employee;
-- 删除所有成员
-- delete from employee;

DQL

数据查询语言,用来查询表中数据记录

基本查询

表创建以及数据插入

create database dql_study;
use dql_study;

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(10) comment '工作地址',
    entrydate   date comment '入职时间'
) comment '员工表';

insert into emp
    values (1, '1', 'name1', '男', 20, '123456789012345678', '北京', '2022-1-1'),
           (2, '2', 'name2', '女', 22, '123456789012345679', '北京', '2021-1-1'),
           (3, '3', 'name3', '男', 19, '123456789012345670', '北京', '2023-1-1'),
           (4, '4', 'name4', '女', 18, '123456789012345671', '北京', '2020-1-1'),
           (5, '5', 'name5', '男', 24, '123456789012345672', '北京', '2019-1-1'),
           (6, '6', 'name6', '男', 23, '123456789012345673', '北京', '2022-1-1'),
           (7, '7', 'name7', '女', 22, '123456789012345674', '北京', '2017-1-1'),
           (8, '8', 'name8', '男', 22, '123456789012345675', '北京', '2017-1-1'),
           (9, '9', 'name9', '女', 21, '123456789012345676', '北京', '2018-1-1'),
           (10, '10', 'name10', '男', 20, null, '北京', '2022-1-1');

查询多个字段

select 字段1,字段2,... from 表名

select * from 表名

设置别名

select 字段1 [as 别名1],字段2 [as 别名2],... from 表名

去除重复记录

select distinct 字段列表 from 表名

条件查询

select 字段列表 from 表名 where 条件列表

不建议使用&&,||,!,他们在release版本时可能被清除掉

-- 条件查询
insert into emp
    values (11, '11', 'name11', '男', 88, '123456789087654321', '天津', '2002-3-2');
insert into emp
    values (12, '12', 'name12', '男', 82, '12345678908765432X', '上海', '2002-3-3');
-- 查询年龄等于88的员工
select *
    from emp
    where age = 88;
-- 查询年龄小于20的员工
select *
    from emp
    where age < 20;
-- 查询没有身份证号的员工
select *
    from emp
    where idcard is null;
-- 查询有身份证号的员工
select *
    from emp
    where idcard is not null;
-- 查询年龄不等于88的员工
select *
    from emp
    where age != 88;
select *
    from emp
    where age <> 88;
-- 查询年龄在15到20岁之间的员工
select *
    from emp
    where age >= 15
      and age <= 20;
select *
    from emp
    where age between 15 and 20;

-- 查询性别为女且年龄小于25岁的员工
select *
    from emp
    where gender = '女'
      and age <= 25;
-- 查询姓名为2个字的员工
select *
    from emp
    where length(name) = 2;
select *
    from emp
    where name like '__';
-- 模糊匹配两个字符
-- 查询年龄或18或20或40的员工信息
select *
    from emp
    where age = 18
       or age = 20
       or age = 40;
select *
    from emp
    where age in (18, 20, 40);
-- 查询身份证号最后一位为X的员工信息
select *
    from emp
    where idcard like '%X';-- 前面匹配任意次,我们只需要最后一位是X,用%即可
select *
    from emp
    where idcard like '_________________X';

聚合函数

将一列数据作为一个整体,进行纵向计算

有count,max,min,avg,sum等

select 聚合函数(字段列表) from 表名

-- 聚合函数

-- 统计员工数量
select count(id)
    from emp;
-- 统计平均年龄
select avg(age)
    from emp;
-- 得到最大年龄
select max(age)
    from emp;
-- 得到最小年龄
select min(age)
    from emp;
-- 得到北京地区年龄之和
select sum(age)
    from emp
    where workaddress = '北京';

分组查询

select 字段列表 from 表名 [where 条件] group by 分组名 [having 分组后过滤条件]

where 和 having区别

执行时机: where 是分组之前进行过滤,不满足where条件,不参与分组;

having是对分组之后的结果过滤

判断条件不同: where不能对聚合函数判断,having可以

-- 分组查询
-- 根据性别分组,统计男性员工和女性员工数量
select gender, count(*)
    from emp
    group by gender;
-- 根据性别分组,统计男性员工和女性员工平均年龄
select gender, avg(age)
    from emp
    group by gender;
-- 查询年龄小于45的员工,并根据地址分组,获取员工数量大于等于3的工作地址
select workaddress, count(*) as address_count
    from emp
    where age < 45
    group by workaddress
    having address_count >= 3;

排序查询

select 字段列表 from 表名 order by 字段1 排序方式,字段2 排序方式

排序方式: ASC 升序(默认)

​ DESC 降序

-- 排序查询
-- 根据年龄对公司员工升序排序
select name, age
    from emp
    order by age asc;
-- 根据入职时间对员工降序排序
select *
    from emp
    order by entrydate desc;
-- 根据年龄对公司员工升序排序,再根据入职时间降序排序
select name, age, entrydate
    from emp
    order by age asc, entrydate desc;

分页查询

select 字段列表 from 表名 limit 起始索引,查询记录数

起始索引从0开始

分页查询时数据库的方言,不同数据库有不同实现,mysql是limit

查询第一页可以省略起始索引

-- 分页查询
select * from emp limit 0,10;
select * from emp limit 10,10;

案例

-- 查询年龄为20,21,22,23岁的员工信息
select *
    from emp
    where age in (20, 21, 22, 23);
-- 查询性别为男,年龄在20-40岁以内的姓名为三个字的员工
select *
    from emp
    where age between 20 and 40 and gender='男'
      and length(name) = 3;
-- 统计员工表中,年龄小于60岁,男性员工和女性员工的人数
select gender,count(*) as work_num
    from emp
    where age < 60
    group by gender;
-- 查询所有年龄小于35的员工的姓名和年龄,并对查询结果按年龄升序排序,如果年龄相同按入职时间降序排序
select name,age
    from emp
    where age <= 35
    order by age asc,entrydate desc;
-- 查询性别为男,且年龄在20-40岁的前五个员工信息,对查询结果升序排序,后按入职时间降序
select *
    from emp
    where age between 20 and 40 and gender='男'
    order by age asc,entrydate desc
    limit 0,5;

执行顺序

from > where > group by > having > select > order by > limit

DCL

数据控制语言,用来管理数据库用户,控制数据库的访问权限

管理用户

查询用户

use mysql;
select * from user

创建用户

create user '用户名'@'主机名' identified by '密码' 

修改用户密码

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码'

删除用户

drop user '用户名'@'主机名'

use mysql;
select *
    from user;
-- 只能在当前主机访问,密码123456
create user 'test'@'localhost' identified by '123456';
-- 创建用户,可以在任意主机访问数据
create user 'test1'@'%' identified by '123456';
-- 修改密码
alter user 'test'@'localhost' identified with mysql_native_password by '12345';
-- 删除用户
drop user 'test1'@'%';

权限控制

查询权限

show grants for '用户名'@'主机名'

授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'

撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'

show grants for 'test'@'localhost';
grant all on * to 'test'@'localhost';
revoke all on * from 'test'@'localhost';

函数

可以直接调用的程序或代码

字符串函数

-- 字符串函数

-- concat
select concat('Hello','Mysql');
-- lower
select lower('HELLO');
-- upper
select upper('hello');
-- lpad
select lpad('hello',6,'w'); -- 填充长度小于字符串长度也可以看作是分割字符串
-- rpad
select rpad('hello',6,'w');
-- trim
select trim('    add   div  ');
-- substring
select substring('hello',1,2); -- 起始点为1

-- 由于业务需求变更,企业员工的工号统一为5位数,不足5位用0补足,例如1的工号改为00001
update emp set workno = lpad(workno, 5, '0');

唯一要注意的就是substring的起始点

数值函数

-- ceil
select ceil(1.5);
-- floor
select floor(1.5);
-- mod
select mod(3,2);
-- rand
select rand();
-- round
select round(1.4555,0);

-- 通过函数,生成随机的6位验证码
select lpad(floor(rand() * (1e6)),6,'0');

日期函数

-- 日期函数
-- 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 year);
-- datediff
select datediff(now(),'2024-1-1');

-- 查询所有员工入职天数,并按天数倒序排序
select name,datediff(now(),entrydate) as entryDays
    from emp
    order by entryDays desc;

流程函数

-- 流程函数

-- if
select if(true, '1', '0');
-- ifnull
select ifnull(null, 'Default');
select ifnull('ok', 'Default');

-- case when then else and
select name, case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end
    from emp;

create table stu
(
    name    varchar(10) comment '姓名',
    math    int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
) comment '学生成绩单'

insert into stu
    values ('张三', 99, 20, 59),
           ('李四', 69, 50, 89),
           ('王五', 39, 70, 79);
-- 成绩>=85 优秀,<85且>=60及格,其余不及格

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

约束

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

use yueshu;
create table user(
    id int primary key auto_increment comment 'ID唯一标识',
    name varchar(10) not null unique comment '姓名',
    age int check ( age between 1 and 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',20,'0','男');

-- 检查非空,唯一
# insert into user (id, name, age, status, gender) values (1,'Tom3',20,'1','男');
# insert into user (name, age, status, gender) values (null,20,'1','男');

外键约束

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

1. 创建表时设置外键

create table  表名(
    字段名 数据类型,
    ...
    [constraint] [外键名称] foreign key (外键字段名) references 主表 (主表列名)
);

2. 创建后设置外键

   alter table 表名 add constraint 外键名 foreign key (外键字段名) references 主表 (主表列名)

-- 外键约束

-- 创建父表
create table dept
(
    id   int auto_increment comment 'id' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';
insert into  dept (name)
    values ('研发部'),
           ('市场部'),
           ('财务部'),
           ('销售部'),
           ('总经办');
-- 创建子表
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 '员工表';

insert into emp
    values (1,'金庸',66,'总裁',20000,'2000-1-1',null,5),
           (2,'张无忌',20,'项目经理',12500,'2005-12-5',1,1),
           (3,'杨逍',33,'开发',8400,'2000-11-3',2,1),
           (4,'韦一笑',48,'开发',11000,'2002-2-5',2,1),
           (5,'常遇春',43,'开发',10500,'2004-9-7',3,1),
           (6,'小昭',19,'程序员鼓励师',6600,'2004,10-12',2,1);

alter table emp add constraint fk_dept_id foreign key (dept_ID) references dept (id);

3. 删除外键

   alter table 表名 drop foreign key 外键名称

外键删除更新行为

alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名 (主表字段名) on update 行为 on delete 行为 

多表查询

多表关系

1. 一对多

   多的一方建立外键,指向另一方的主键

2. 多对多

   建立一张中间表,至少包含两个外键,分别关联两边的主键

-- 多对多关系

use multi_table;

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,'黛绮丝','2000100101'),
                (null,'谢逊','2000100102'),
                (null,'殷天正','2000100103'),
                (null,'韦一笑','2000100104');

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

insert into course
    values (null,'java'),
            (null,'PHP'),
            (null,'MYSQL'),
            (null,'Hadoop');

create table student_course(
    id int auto_increment comment '主键' primary key,
    student_id int not null comment '学生id',
    course_id int not null comment '课程id',
    constraint fk_student_id foreign key (student_id) references student (id),
    constraint fk_course_id foreign key (course_id) references course (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);

3. 一对一

   多用于单标拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中

   在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一

-- 一对一
create table tb_user(
    id int auto_increment primary key comment '主键id',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '性别',
    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 '专业',
    primary_school varchar(50) comment '小学',
    middle_school varchar(50) comment '中学',
    university varchar(50) comment '大学',
    user_id int unique comment '用户id',
    constraint fk_user_id foreign key (user_id) references tb_user (id)
)comment '用户教育信息表';

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

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

多表查询

从多张表中进行查询

-- 单表
select * from emp;
-- 多表查询,直接排列组合展示,笛卡尔积,我们需要消除无效的笛卡尔积
select *
    from emp,dept;

-- 让两个链接的id相等即可
select *
    from emp,dept
    where emp.dept_ID = dept.id;

内连接

查询A,B表交集部分数据

外连接:

• 左外连接:查询左表所有数据,包含交集部分数据
• 右外连接:查询右表所有数据,包含交集部分数据

自连接: 当前表与自身的连接查询,自连接必须使用表别名

• 隐式内连接:

  select 字段列表 from 表1,表2 where 条件

• 显示内连接

  select 字段列表 from 表1 [inner] join 表2 on 连接条件 

外连接

左外连接:

select 字段列表 from 表1 left [outer] join 表2 on 条件

右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件

自连接

select 字段列表 from 表A 别名A join 表A 别名B on 条件

自连接查询,可以是内连接查询,也可以是外连接查询

-- 自连接
-- 查询员工及所属领导的名字
select e1.name,e2.name from emp e1 join emp e2 on e1.managerID = e2.id;
-- 查询所有员工emp和其领导的名字emp,如果员工没有领导,也要查询出来
select e1.name,e2.name from emp e1 left join emp e2 on e1.managerID = e2.id;

联合查询

对于联合查询,就是把多次查询的结果合并起来,形成一个新的查询结果集

select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...;

去掉all,去重操作

-- 将薪资低于5000的员工和年龄大于50的员工全部查询出来
select name from emp where salary < 5000
union
select name from emp where age > 50;

查询的字段必须保持一致

子查询

sql语句中嵌套select语句,成为嵌套查询,又称子查询

select * from t1 where column1 = (select column1 from t2);

标量子查询

子查询返回的结果是单个值(数字,字符串,日期等)

-- 查询销售部所有的员工信息
select *
    from emp
    where dept_ID = ( select id from dept where name = '销售部' );

-- 查询在小龙女之后的员工信息
select *
    from emp
    where id > (select id from emp where name = '小龙女');

列子查询

子查询返回的结果是一列

常见操作符:in,not in,any,some,all

-- 列子查询
-- 查询销售部和市场部的所有员工信息
select *
    from emp
    where dept_ID in ( select id from dept where name = '销售部' or name = '市场部' );
-- 查询比财务部所有人工资都高的员工信息
select e1.*
    from emp e1
    where e1.salary > all ( select e2.salary
                            from emp e2
                            where e2.dept_ID = ( select dept.id
                                            from dept
                                            where dept.name = '财务部' ) );
-- 查询比研发部其中任意一个员工工资高的员工信息
select e1.*
    from emp e1
    where e1.salary > any ( select e2.salary
                            from emp e2
                            where e2.dept_ID = ( select dept.id
                                            from dept
                                            where dept.name = '研发部' ) );

行子查询

查询结果是一行

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

表子查询

返回的是多行多列

-- 表子查询
-- 查询与'令狐冲',陆小凤职位和薪资相同的员工信息

select *
    from emp
    where (job,salary) in (select job,salary
                          from emp
                          where name in ('令狐冲','陆小凤'));

-- 查询入职日期是2006-1-1之后的员工信息,及其部门信息
select e.*,dept.*
    from (select * from
                    emp
                   where entrydate > '2006-1-1') e
    left join dept on e.dept_ID = dept.id;

案例

-- ---------------------------------------> 多表查询案例 <----------------------------------
create table salgrade(
    grade int,
    losal int,
    hisal int
) comment '薪资等级表';

insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);


-- 1. 查询员工的姓名、年龄、职位、部门信息 （隐式内连接）
select emp.name,emp.age,emp.job,dept.name
    from emp,dept where emp.dept_ID = dept.id;
-- 2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
select emp.name,emp.age,emp.job,dept.name
    from emp join dept on emp.dept_ID = dept.id
    where emp.age < 30;
-- 3. 查询拥有员工的部门ID、部门名称
select distinct dept.id,dept.name
    from emp,dept where emp.dept_ID = dept.id;

-- 4. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来
select emp.name,dept.name
    from emp left join dept on emp.dept_ID = dept.id
    where emp.age > 40;
-- 5. 查询所有员工的工资等级
select emp.name,emp.salary,salgrade.grade
    from emp,salgrade
    where emp.salary between salgrade.losal and salgrade.hisal;
-- 6. 查询 "研发部" 所有员工的信息及 工资等级
select emp.name,emp.salary,salgrade.grade,emp.dept_ID
    from emp,salgrade
    where
        emp.dept_ID = (select id
                       from dept
                       where name = '研发部')
        and
        (emp.salary between salgrade.losal and salgrade.hisal);

-- 7. 查询 "研发部" 员工的平均工资
select avg(salary)
    from emp
    where dept_ID = (select id
                     from dept
                     where name = '研发部');

-- 8. 查询工资比 "小龙女" 高的员工信息。
select *
    from emp
    where salary > (select salary
                    from emp
                    where name = '小龙女');

-- 9. 查询比平均薪资高的员工信息
select *
    from emp
    where salary > (select avg(salary)
                    from emp);
-- 10. 查询低于本部门平均工资的员工信息
-- 先找到一列记录每个部门平均工资的列
select *
    from emp e1
    where e1.salary < (select avg(e2.salary)
                    from emp e2
                    where e1.dept_ID = e2.dept_ID);
-- 11. 查询所有的部门信息, 并统计部门的员工人数
select dept.*,(select count(*)
               from emp
               where dept_ID = dept.id)
    from dept;
select count(emp.id),(select dept.name
                      from dept
                      where emp.dept_ID = dept.id)
    from emp
    group by emp.dept_ID;
-- 12. 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
select student.name,student.no,course.name
    from student,course,student_course
    where student.id = student_course.student_id and course.id = student_course.course_id;

事务

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

事务操作

• 查看/设置事务提交方式

  select @@autocommit;
  set @@autocommit = 0;

• 提交事务

  commit

• 回滚事务

  rollback

简单事务操作

• 开启事务

  start transaction/begin;

• 提交事务

  commit;

• 回滚事务

  rollback;

-- 创建银行账户表
create table account(
    id int auto_increment primary key comment 'id',
    name varchar(10) comment '姓名',
    money int unsigned comment '余额'
) comment '账户表';

insert into account
            values (null,'张三',2000),
                   (null,'李四',2000);
-- 数据恢复
update account
    set money = 2000
    where name in ('张三','李四');

-- 事务模拟,转账
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 = '李四';
-- 出现异常
rollback; -- 撤回刚才执行的操作,提交后没有任何影响
-- 提交事务
commit;

-- 事务模拟2
select @@autocommit;
set @@autocommit = 1;
-- 开启事务
start transaction;
select * from account
    where name = '张三';
update account set money = money-1000 where name = '张三';
update account set money = money+1000 where name = '李四';
-- 出现异常 比如小于0,自动回滚,停止事务
-- rollback; -- 撤回刚才执行的操作,提交后没有任何影响
-- 提交事务
commit;

事务特性

• 原子性: 不可分割,全部成功或全部失败
• 一致性: 事务完成时,必须使所有的数据保持一致状态
• 隔离性: 保证事务不受外部并发操作影响的独立环境下运行
• 持久性: 事务一旦提交或回滚,对数据库的改变是永久的

并发事务问题

事务隔离级别

查看事务隔离级别

select @@transaction_isolation

设置事务隔离级别

set [seesion|gloal] transaction isolation level [read uncommitted | read committed | repeatable read serializable]