MySQL学习笔记-基础篇

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,'1','柳岩','女',20,'123456789012345678','北京','2000-01-01'),
(2,'2','张无忌','男',18,'123456789012345670','北京','2005-09-01'),
(3,'3','韦一笑','男',38,'123456789712345670','上海','2011-01-01'),
(4,'4','赵敏','女',19,'123456789022345678','北京','2004-01-05'),
(5,'5','小昭','女',16,'123456789013345678','北京','2003-02-19'),
(6,'6','杨逍','男',28,'123456789012445678','重庆','2002-06-21'),
(7,'7','范伟','男',40,'123456789012355678','成都','2001-02-03'),
(7,'7','王大','男',60,'122456789012355671','天津','2011-12-03');

insert into emp(id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (8,'8','李铁','男',25,null,'广东','2022-5-8');

select name,workno,age from emp;

select id,workno,name,gender,age,idcard,workaddress,entrydate from emp;

select * from emp;

select workaddress as '工作地址' from emp;
select distinct  workaddress '工作地址' from emp; -- 去重复，as别名可以省略
/*聚合函数 count、max、min、avg、sum*/
select COUNT(*) from emp;
select avg(age) from emp;
select max(age) from emp;
select min(age) from emp;
select sum(age) as '北京员工年龄之和' from emp where workaddress = '北京';
select * from emp where workaddress = '北京';
/*分组查询 group by*/
select gender,count(*) from emp group by gender;
select gender,avg(age) from emp group by gender;
select workaddress,count(*) from emp where age < 45 group by workaddress having count(*) >= 3;
select workaddress,count(*) as add_count from emp where age < 45 group by workaddress having add_count >= 3;-- 把count(*)用别名命名后调用。
/*排序查询 order by*/
select * from emp order by age asc; -- asc 升序
select * from emp order by entrydate desc ; -- desc 降序
select * from emp order by age asc , entrydate desc ; -- 年龄升序排序，日期降序
/*分页查询 limit*/
select * from emp limit 0,10; -- 起始索引，展示个数
select * from emp limit 10; -- 从0开始可以简写

-- -------------------------函数-----------------------
-- 字符串函数
-- concat  字符串拼接
select concat('Hello' , 'MySQL');
-- lower  转小写
select lower('Hello');
-- upper  转大写
select upper('Hello');
-- lpad  左填充（被填充，总长度，填充内容）
select lpad('01',5,'-'); -- 给01左填充符号-，总长度为5，结果：---01
-- rpad  右填充（被填充，总长度，填充内容）
select rpad('01',5,'-');
-- trim 去除头部和尾部空格
select trim('  Hello  MySQL  ');
-- substring  截取
select substring('Hello MySQL',1,5);

-- 案例：将员工表员工编号改为5位数，不足五位的用'0'填充
update emp set workno = lpad(workno,5,'0');

-- 数值函数
-- ceil(x)      向上取整
select ceil(1.5);
-- floor(x)     向下取整
select floor(1.5);
-- mod(x,y)     返回x/y的模
select mod(3,4);  -- 类似取余数
-- rand()       返回0~1内的随机数
select rand();
-- round(x,y)   求参数x的四舍五入的值，保留y位小数
select round(2.345,2); -- 2.35
select round(2.344,2); -- 2.34
-- 案例：生成六位随机验证码
select lpad(round(rand()*1000000,0),6 ,'0') as '验证码'; -- 使用rand函数随机生成0~1之间的数字乘以1000000，使用round函数四舍五入掉小数点后面数值

-- 日期函数
-- curdate()        返回当前日期
select curdate();
-- curtime()        返回当前时间
select curtime();
-- now()            返回当前日期和时间
select now();
-- year(date)       获取指定的date的年份
select year(now());
-- month(date)      获取指定的date的月份
select month(now());
-- day(date)        获取指定date的日期
select day(now());
-- date_add(date,interval expr type)        返回一个日期/时间值加上一个时间间隔expr后的时间值
select date_add(now(),INTERVAL 70 day);
select date_add(now(),INTERVAL 10 month);
select date_add(now(),INTERVAL 1 year);
-- datediff(date1,date2)                    返回起始时间date1 和结束时间date2 之间的天数
select datediff('2020-11-01','2020-10-01'); -- 注意这里是第一个时间减第二个时间
select datediff('2020-10-01','2020-11-01'); -- 负31
-- 案例：查询员工入职天数并排序
select name,datediff(curdate(),entrydate) as days from emp order by days desc ;

-- 流程（控制）函数
-- if(值,t,f)            如果值为true，则返回t，否则返回f
select if(true,'OK','Error');
-- ifnull(值1,值2)    如果值1不为空，返回值1，否则返回值2
select ifnull('OK','Default');
select ifnull('','Default');
select ifnull(null,'Default');
-- case when 值1 then aaa else bbb end           如果值1为true，返回aaa，否则返回bbb
-- 需求：查询员工姓名和工作地址（北京/上海为一线城市，其他为二线城市）
select
    name,
    (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;
-- case 值1 when 值2 then bbb else 默认值 end   如果值1等于值2，返回bbb ，否则返回默认值

-- --------------------------------- 约束 ---------------------------------
-- 准备数据
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,'后勤部');

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(id, name, age, job, salary, entrydate, managerid, dept_id) VALUES
            (1,'金庸',66,'总裁',20000,'2000-01-01',null,5),
            (2,'张无忌',50,'项目经理',12500,'2000-01-10',1,1),
            (3,'杨逍',33,'开发',8500,'2000-11-03',2,1),
            (4,'韦一笑',48,'开发',8500,'2002-02-05',2,1),
            (5,'常玉树',43,'开发',8500,'2004-09-07',3,1),
            (6,'任我行',37,'市场部总监',5500,'2005-04-01',1,2),
            (7,'小昭',19,'调研',6000,'2004-10-12',6,2),
            (8,'赵敏',26,'调研',4200,'2018-07-30',6,2),
            (9,'何沅君',30,'行政办总监',12500,'2000-01-05',1,5),
            (10,'任盈盈',21,'前台',6200,'2000-02-01',10,5),
            (11,'周芷若',23,'人事',5500,'2001-05-07',10,5),
            (12,'小龙女',18,'助理',4050,'2010-11-01',10,5),
            (13,'阮星竹',24,'财务',9900,'2002-07-14',1,3),
            (14,'岳不群',41,'销售部总监',10000,'2000-06-01',1,4),
            (15,'胡一刀',38,'销售',2800,'2005-03-22',14,4),
            (16,'郭靖',27,'销售',2800,'2010-10-10',14,4),
            (17,'陈友谅',41,null,null,'2022-05-10',1,null);

/*
 添加外键
   语法：
 create table 表名(
        字段名  数据类型),
        ...
        [constraint] [外键名称] foreign key(外键字段名) references 主表 (主表列名);

 alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (主表列名);
 */
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
-- 再打开表格可以看到dept_id已经有蓝色的小钥匙了，在这里蓝色小钥匙代表外键（黄色钥匙（id）代表主键）。
/*
 删除外键
 语法：
 alter table 表名 drop foreign key 外键名称;
 */
alter table emp drop foreign key fk_emp_dept_id;
/*
 更新外键
 cascade 在更新时同时更新外键子表的记录 、 no action 、 restrict 、 set null 删除时对应外键设置为null（要求允许取null）、 set default
 语法：
 alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表 (主表字段名) no update 行为 on delete 行为;
 */
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 ;

-- --------------------------- 多表关系 演示 ---------------------------

-- 多对多 ---------------
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 ,
    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) ;

-- --------------------------------- 连接 ---------------------------------
/*   【   内连接   】
 隐式内连接：
    select 字段列表 from 表1，表2 where 条件 ;
 显式内连接：
    select 字段列表 from 表1 [inner] join 表2 on 条件；

  ※※※ inner可省略。
 */
-- 内连接演示
-- 1、 查询每位员工的姓名，及关联的部门名称（隐式内连接）
-- 表结构： emp ， dept
-- 连接条件：emp.dept_id=dept.id
select * 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、 查询每位员工的姓名，及关联的部门名称（显式内连接）
select * from emp inner join dept on emp.dept_id=dept.id;
select e.name '员工姓名', d.name '部门' from emp e join dept d on e.dept_id=d.id;

/*   【   外连接   】
 左外连接：
     select 字段列表 from 表1 left [outer] join 表2 on 条件 ;
     相当于查询表1（左表）的所有数据，包含表1和表2交集部分的数据。
 右外连接：
     select 字段列表 from 表1 right [outer] join 表2 on 条件 ;
     相当于查询表2（右表）的所有数据，包含表1和表2交集部分的数据。

  ※※※ [outer]可省略。
 */
-- 外连接演示
-- 1、查询emp表所有的数据和对应的部门信息（左外连接）
-- 表结构： 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.name from emp e right join dept d on d.id = e.dept_id;
/*   【   自连接   】
 自连接：
     select 字段列表 from 表A 别名A join 表A 别名B on 条件;
     自连接查询，可以是内连接查询，也可以是外连接查询。
 */
-- 自连接演示
-- 1、 查询每位员工的领导
-- 表结构： emp
select a.name '员工',b.name '领导' from emp a join emp b on a.managerid=b.id;
-- 2、 查询所有员工及领导，如果没有领导也要查询出来
-- 表结构： emp a, emp b
select a.name '员工',b.name '领导' from emp a left join emp b on a.managerid = b.id;
/*     【   联合查询     】
 把多次查询的结果合并起来，形成新的查询结果集。
 语法：
       select 字段列表 from 表A ...
       union [all]
       select 字段列表 from 表B ...;
    ※※※ union all 会将全部数据直接合并在一起，union会对合并之后的数据去重。
    ※※※ 对于联合查询的多张表的列数、字段类型必须保持一致！
 */
-- 1、 查询 薪资低于10000 、 年龄大于50的员工
select * from emp where salary < 10000
union all
select * from emp where age > 50;
/*   【     子查询    】
 概念： SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

 语法： select * from t1 where column1 = ( select column1 from t2 );
     子查询外部的语句可以是insert 、 update 、delete 、 select(常见) 任何一个。

 根据子查询结果不同，分为：
    1、 标量子查询：子查询结果为单个值（数字、字符串、日期等），最简单的形式
            常用操作符： =   <>   >   >=   <   <=
    2、 列子查询：子查询结果为一列（可以是多行）
            常用操作符： in       在指定的集合范围内，多选一
                   not in   不在指定的集合范围内
                   any      子查询返回列表中，有任意一个满足即可
                   some     与ANY等同，使用SOME的地方都可以使用ANY
                   all      子查询返回列表的所有值都必须满足
    3、 行子查询：子查询结果为一行（可以是多列）
            常用操作符： = 、 <> 、 in 、 not in
    4、 表子查询：子查询结果为多行多列
            常用操作符： in

 根据子查询位置，分为：where之后、 from之后 、 select之后。
 */
-- 一、 标量子查询：
-- 1、查询 “研发部” 的所有员工信息
-- a. 查询 “研发部” 部门id
select id from dept where name = '研发部';
-- b. 根据研发部id 查询员工信息
select * from emp where dept_id = 4;
-- c. 用标量子查询合并
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 > '2000-01-10';
-- c. 用标量子查询合并
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 (2,4);
-- c. 用列子查询合并
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 = 3;
-- 合并：
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、 查询比研发部期中任意一人工资高的员工信息
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));
-- 4、 查询年龄比“销售部”最小的员工的员工姓名和年龄
select name,age from emp where age < all (select age 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 = 12500 and managerid = 1;
select * from emp where (salary,managerid) = (12500,1);
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、 查询入职日期是“2005-01-01” 之后的员工信息，及其部门信息
select * from emp where entrydate > '2005-01-01' ;
select e.*,d.* from (select * from emp where entrydate > '2005-01-01') e left join dept d on e.dept_id=d.id;

-- 练习：
-- 插入薪资等级表
create table salgrade(
    grade int comment '等级',
    losal int comment '最低工资',
    hisal int comment '最高工资'
) comment '薪资等级表';

insert into salgrade values (1,0,3000),(2,3001,5000),(3,5001,8000),(4,8001,10000),(5,10001,15000)
                                ,(6,15001,20000),(7,20001,25000),(8,25001,30000);
-- 1、 查询员工的姓名、年龄、职位、部门信息。
select e.name,e.age,e.job,d.name from emp e left join dept d on e.dept_id = d.id;
-- 2、 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
select e.name,e.age,e.job,d.name from emp e left join dept d on e.dept_id = d.id where e.age < 30 ;
-- 3、 查询拥有员工的部门ID、部门名称。
select distinct d.id ,d.name from emp e,dept d where e.dept_id = d.id ;
-- 4、 查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来。
select e.name,d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40;
-- 5、 查询所有员工的工资等级。
select e.name,e.salary,s.grade from emp e ,salgrade s where e.salary>=s.losal and e.salary<=s.hisal;
-- 6、 查询“研发部”所有员工的信息及工资等级。
select e.name , d.name from emp e ,dept d where  e.dept_id=d.id and d.name = '研发部' ;    -- 查询4人
select e.*, d.name, s.grade
from emp e,
     dept d,
     salgrade s
where e.dept_id = d.id
  and d.name = '研发部'
  and e.salary between s.losal and s.hisal;
-- 7、 查询“研发部”员工的平均工资。
select avg(e.salary) from emp e,dept d where e.dept_id = d.id and d.name = '研发部';
-- 8、 查询工资比“赵敏”高的员工信息。
select e.* from emp e where e.salary > (select salary from emp where emp.name = '赵敏');
-- 9、 查询比平均薪资高的员工信息。
select e.* from emp e where e.salary > (select avg(salary) from emp);
-- 10、 查询低于本部门平均工资的员工信息。
select avg(e1.salary) from emp e1 where e1.dept_id = 1;
select avg(e1.salary) from emp e1 where e1.dept_id = 2;
select *, (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id)  as '当前部门平均薪资'
from emp e2
where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);
-- 11、 查询所有的部门信息，并统计部门的员工人数。
select id ,name from dept;
select count(*) from emp where dept_id = 1;
select d.id , d.name , (select count(*) from emp e where e.dept_id = d.id) as'人数' from dept d;
-- 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.studentid = c.id;

-- ------------------------  事务操作  ------------------------
-- 数据准备
create table account(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    money int comment '余额'
) comment '账户表';

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

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

-- 查看/设置事务提交方式
 select @@autocommit;   -- 1为自动，0为手动
 set @@autocommit=0;    -- 设置为手动提交
 set @@autocommit=1;
-- 提交事务
commit;
-- 回滚事务
rollback;

-- 方式二：
-- 开启事务
start transaction; --  或 begin;

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

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

-- 查看事务隔离级别
select @@transaction_isolation;
/*
 READ UNCOMMITTED       读未提交
 READ COMMITTED         读已提交
 REPEATABLE READ        可重复读
 SERIALIZABLE           串行化
 */
-- 设置隔离级别
set session transaction isolation level read committed ;

set session transaction isolation level repeatable read ;  -- 默认