MySQL学习笔记1

一、MySQL概述

MySQL是一种关系型的数据管理系统，分别通过数据库、数据管理系统、SQL语句组成

二、SQL

1、DDL

1-1 DDL-数据库操作

• 查询

 show databases;-- 查询用户中所有的数据库。

• 创建

create database if not exists test; -- 如果不存在test数据库的话,创建数据库test,

• 删除

 drop database if exists hhh;

• 使用

use test;-- 使用数据库test;

1-2 DDL-表操作

• 查询表结构

desc table_name;-- 查询表table_name的结构

• 添加表中字段

alter table table_name add new_name varchar(10);

• 修改字段的数据类型

 alter table table_name modify new_name varchar(9);

• 修改字段名和字段类型

alter table table_name change new_name new_name01 varchar(8);

• 删除字段

alter table table_name drop new_name01;

• 修改表名

alter table table_name rename to new_table_name ;

• 删除表

truncate table table_name; -- 删除表中数据
drop table if exists table_name;-- 整张表同数据一同删除。

2、DML(数据的增删改查)

-- 添加数据
insert into table_name (id, worknumber, name, gander, age, entrydate, personality,workaddres) 
values 
(参数1,参数2,参数3,参数4,参数5,参数6,参数7,参数8),
（.....）,
(......);

-- ----------------------------------
-- 修改数据
update table_name set 字段='new_xxx' where id=3;

-- 删除数据
delete from table_name where id=3;
-- 没有where语句，删除全部数据

3、DQL

• 以下查询均围绕此表进行操作来进行演示

create table employee
(
    id          int              null,
    worknumber  varchar(20)      null,
    name        varchar(30)      null,
    gander      char             null,
    age         tinyint unsigned null,
    entrydate   varchar(30)      null,
    personality varchar(50)      null,
    workaddres  varchar(30)      null comment '工作地址'
);

1.基本查询

select workaddres,name from employee;

select * from employee;
-- *代表查询所有字段数据。

select name as'名字' from employee;
-- as 表示给对应字段起别名，更好使用该字段

/*不重复查询 关键字distinct*/
select distinct workaddres from employee;

2.条件查询(where )

-- <>、!=为不等于

select * from employee where age=18;

select * from employee where age<=20;

select * from employee where age>=20&& age<=45;
select * from employee where age between 20 and 65;
/*包含参数值*/

select * from employee where entrydate is /*not*/ null;

select * from employee where gander='女'&&age<25;

select * from employee where age=20||age=18||age=45;

select * from employee where age in (18,20,45);
/*同上一条一样:查询的数据中满足in(参数)中参数其中之一即可*/

--like的使用
--1. % 用于表示任意个字符串，且是模糊的
select * from employee where entrydate like '%22';/*表示前面任意多少的字符，只要后面是22即可*/
--2. _ 用于表示一个字符串，且是模糊的
select * from employee where name like 'l__';/*查询的字符串只有三个，且最前面那个是l*/

3.使用聚合函数查询

select count(*) from employee;/*统计对应字段的数量,并且所有为NULL的字段不参与统计*/

select avg(age) from employee;/*求平均*/

select max(age) from employee;

select min(age) from employee;

select sum(age) from employee where workaddres='扬州';/*求合*/

4.分组查询（group by xxx having xxx）

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

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

-- 3.查询年龄小于45的员工，并且根据工作地址分组，获取员工数量>=3的工作地址
select workaddres,count(*) as 地址统计 from employee where age<45 group by workaddres having 地址统计>=3;
/*having 即分完组后再进行以此条件筛选*/

5.排序查询（order by）

-- 1.根据年龄升序排序。
select * from employee order by age desc;/*asc==升序,默认为升序*/

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

6.分页查询（limit）

-- 数据一条为单位,参数1表示前面已经有N1条数据了，参数2表示 继N1条数据之后需要展示N2条数据

-- 1. 查询第一页的员工数据，每一页展示10条数据
select * from employee limit 0,10;

-- 2.查询第二页的员工数据，每一页展示10条数据
select * from employee limit 10,10;

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

4、DCL(用户管理、权限控制)

--1.用户管理
-- 创建用户 itcast ，只能够在当前主机localhost访间，密码123456
create user'itcast'@'localhost' identified by '123456';

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

-- 修改用户 itcast 的访问密码为 12345;
alter user 'itcast'@'localhost' identified by '12345';

-- 删除itcast@localhost用户
drop user'itcast'@'localhost';

-- -----------------------------------------------------

-- 2.权限控制
-- 查询权限
show grants for 'lee'@'%';
-- 授予权限
grant all/*权限列表*/ on users.* to 'lee'@'%';-- 把all在users数据库的所有数据的所有权限给lee用户
-- 撤销权限
revoke all on users.* from 'lee'@'%'
-- 注意:多个权限之间，使用逗号分隔授权时，数据库名和表名可以使用*进行通配，代表所有

三、函数

1、字符串函数

-- 一、字符串函数
-- conCat
select concat('Hello','MySQL');
-- lower
select lower('Hello');
-- upper
select upper('Hello');

-- lpad
select lpad('88',6,'52');-- 数据从left填充

-- rpad
select rpad('88',6,'52');-- 数据从right填充

-- trim
select trim('  hello  world     ');-- 去除字符串前后空格

-- substring
select substring('hello world',1,5);-- 截取1~5号位置的字符串；不以索引0开始

-- 案例：
-- 1、由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如:1号员工的工号应该为00001
select *from users.employee;
update users.employee set worknumber=lpad(worknumber,5,'0');


-- ---------------------------------------------

2、数值函数

-- 二、数值函数
-- ceil
select ceil(1.1);
-- floor
select floor(1.9);
-- mod
select mod(7,4);
-- rand
select rand();
-- round
select round(/*x*/,/*y*/);-- 求参数x的四舍五入的值，保留y位小数,y过长会默认将0省略

-- 案例:通过数据库的函数，生成一个六位数的随机验证码。
select rpad(round(rand()*1000000,0),6,'0');

-- ---------------------------------------------

3、日期函数

-- 三、日期函数
-- curdate
select curdate();
-- curtime
select curtime();
-- now
select now();
-- Year Month Day  --通过当前时间放回对应的年、月、日;
select YEAR(now());
select MONTH(now());
select DAY(now());

-- date_add 第一个参数 对应的时间加上interval后面的参数80 后的时间
select date_add(now(),Interval 80 YEAR/*MONTH、DAY*/);

-- 两个时间相差的天数。
select datediff('2022-10-20','2024,10,20');

-- 案例 查询所有员工的入职天数，并根据入职天数倒序排序。
select users.employee.name,datediff('2024-4-06',users.employee.entrydate) day from  users.employee order by day desc ;

-- ---------------------------------------------

4、流程控制函数

-- 四、流程控制函数
-- if
select if(true,'ok','error');

-- ifnull
select ifnull(null,'default');

-- case[参数1] when [参数2] then[参数3] else[参数4] end  如果参数1的值等于参数2，返回参数3 否则返回参数4 默认值
select
    users.employee.name,
    (case users.employee.workaddres when '北京' then '一线城市' else '二线城市' end) as 地区水平
from users.employee;

-- 案例:统计班级各个学员的成绩，展示的规则如下:
-- >= 85，展示优秀
-- >= 60，展示及格
-- >否则，展示不及格
select users.score.name,
       case when users.score.math_ >=85 then '优秀'  when users.score.math_>=60 then '及格' else '不及格' end as 数学,
       case when users.score.english >=85 then '优秀'  when users.score.english>=60 then '及格' else '不及格' end as 英语,
       case when users.score.chinese >=85 then '优秀'  when users.score.chinese>=60 then '及格' else '不及格' end as 语文
       from score;

四、约束

约束就是对字段的性质进行一定范围的控制。

1、关键字约束

• 创建一个表进行演示
  其中的关键字:primary key 、auto_increment、not null、unique、check ( age>0&&age<120 ) etc

use users;
create table  people(
    id int primary key  auto_increment comment '工号',/*primary key 、auto_increment*/
    name varchar(50) not null  unique comment '名字',/*not null、unique*/
    age int check ( age>0&&age<120 ) comment '年龄',/*check ( age>0&&age<120 )*/
    status char(1)  default 1 comment '状态', /*default 1*/
    gender char(1) comment '性别'
) comment '人员统计表';

2、外键约束

• 案例示范—（将部门表中的部门名称字段与员工表中的部门字段关联）

-- 创建部门表
create table dept
(
    id   int auto_increment primary key comment 'ID',
    name varchar(50) not null comment '部门名称'
)comment'部门表';
-- 插入 相关部门信息
INSERT INTo dept(id, name) VALUES(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');


-- 创建员工表
create table emp
(
    id        int auto_increment primary key comment 'ID',
    keyname   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 '部门'
)comment'员工表';

-- 插入相关数据
INSERT INTo emp (id, keyname, 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,'开发',10580,'2804-09-07',3,1),
    (6,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1);

-- 添加外键，将两张表联系起来
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.on update cascade on delete cascade 表示关联后子类会随着父表删除、改动而变化。。。
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade;

-- 2.set null 父表改动后子表设置为null
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null;

五、多表查询

1.多对多查询

-- 1.多对多查询-----------------------------------------------------------

-- 学生表创建
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,'lee', '2000100101'),(null,'jack','2000100102'),(null,'mei','2000100103'),(null,'dai','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);
-- --------------------------------------------------------------------

2. 一对一查询

• 用途:一般一组信息拆分成两组会使得操作性更好，而采取一对一的外键联接方式

-- 2. 一对一查询  用途:一般一组信息拆分成两组会使得操作性更好，而采取一对一的外键联接方式
-- 存储用户基本信息的表
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 th_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', /* 设置unique约束,设定其与tb_user表的主键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,'李彦宏',55,'1','18800009999');

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

3.内连接

-- 使用users 数据库中的emp与dept表

-- 3.内连接
-- 隐式内连接 通过where emp.dept_id=dept.id 将俩表连接，好像不需要外键？
select * from emp,dept where emp.dept_id=dept.id;

-- 显示内连接  表1 inner join 表2 on [条件];
select  * from emp inner join dept  on emp.dept_id = dept.id ;

-- ----------------------------------------------------------

4.外连接

-- 4.外连接演示
-- 左外连接、右外连接
-- 4-1.查询emp表的所有数据，和对应的部门信息(左外连接)
select emp.keyname,dept.name from emp left outer join dept on emp.dept_id = dept.id;

-- 4-2.查询dept表的所有数据，和对应的员工信息(右外连接)
select emp.keyname,dept.name from emp right outer join dept on emp.dept_id = dept.id;

-- 其实左外连接、右外连接都是一样的，表换个位置就行了。

-- -----------------------------------------------------------

5.自连接

• 就是一个表中信息的连接。

-- 5.自连接  就是一个表中信息的连接。
-- 5-1.查询员工及其所属领导的名字表结构:emp
select a.keyname ,b.keyname from emp a,emp b where a.managerid = b.id;
-- 5-2.查询所有员工emp及其领导的名字emp，如果员工没有领导，也需要查询出来表结构:emp a，emp b
select a.keyname'员工',b.keyname' 领导'from emp a left join emp b on a.managerid = b.id;

-- --------------------------------------------------------------

6.联合查询

• union all 就是将查询的两个表结合结合起来

-- 联合查询 union all 就是将查询的两个表结合结合起来
-- 6-1、将薪资低于 5000的员工，和年龄大于 50 岁的员工全部查询出来.
--
select*from emp where salary<5000
union all
select *from emp where age >50;
-- ---------------
select*from emp where salary<5000
union  /*去除all相对于去重*/
select *from emp where age >50;

-- --------------------------------------------------------------------

7.标量子查询

-- 标量子查询
-- 两个案例
-- 7-1.查询“研发部”的所有员工信息
-- a.查询“研发部”部门ID
select id from dept where name ='研发部';

-- b.根据研发部部门ID，查询员工信息
select*from emp where dept_id =1;

-- c.结合a、b
select*from emp where dept_id =(select id from dept where name ='研发部');

-- ----------------------------------
-- 7-2.查询在"张无忌”入职之后的员工信息
-- a.获取张无忌的入职时间
select entrydate from emp where keyname='张无忌';
-- b 将a带入到b中。
select * from emp where entrydate>(select entrydate from emp where keyname='张无忌');


-- --------------------------------------------------------------

8.列子查询（in、all、any）

-- 列子查询 in、all、any==some

-- 8-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 = '研发部');

-- 8-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 = '研发部'));

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


-- ------------------------------------------------------

9.行子查询

-- 行子查询

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

-- -------------------------------------------------------------

10.表子查询 (in)

-- 表子查询  in
-- 10-1.查询与“鹿杖客"“宋远桥”的职位和薪资相同的员工信息。
-- 查询“鹿杖客"“宋远桥”的职位和薪资a.
select job,salary from emp where keyname ='鹿杖客'or keyname ='宋远桥';
-- 查询与“鹿杖客"“宋远桥”的职位和薪资相同的员工信息b.
select * from emp where (job,salary) in ( select job, salary from emp where keyname ='鹿杖客'or keyname ='宋远桥');
-- 疑问: 这个in和上面的=不是一样的吗？-- 哦哦，这里有两个的数据，也就是有两行数据进行查询，而行子查询只有一行。


-- 10-2.查询入职日期是“2006-01-01"之后的员工信息及其部门信息
-- a:入职日期是“2006-01-01”之后的员工信息
select *from emp where entrydate>'2006-01-01';
-- b:查询这部分员工，对应的部门信息:
select*from (select * from emp where entrydate >'2006-01-01') e left join dept d on e.dept_id = d.id ;
-- 解释:这个案例解释了可以把select出来的数据通过别名作为一张表进行使用。

练习

--- 练习----------

-- 根据需求，完成SOL语句的编写
-- 查询员工的姓名、年龄、职位、部门信息。
    select e.keyname,e.age,e.job,d.name from emp e left join dept d on e.dept_id = d.id;

-- 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
    select * from emp where age<30;
    select e.keyname,age,job,dept.name from (select * from emp where age<30) e,dept where e.dept_id=dept.id;

-- 查询拥有员工的部门ID、部门名称。
    select emp.dept_id from emp;
    select * from dept where id in (select emp.dept_id from emp);

-- 查询所有年龄大于40岁的员工,及其归属的部门名称;如果员工没有分配部门,也需要展示出来。
    select * from (select * from emp where age>40) e left join dept on e.dept_id=dept.id;

-- 查询所有员工的工资等级。
    select salary from emp;
    select emp.keyname,
    case when salary<=5000 then '低工资'
        when salary<=10000 then '还差点'
        when salary<=15000 then '不错了'
        when salary>15000 then '好' end as '工资水平'
    from emp;

-- 查询"研发部"所有员工的信息及工资等级。
    select e.keyname,
           case when salary<=5000 then '低工资'
                when salary<=10000 then '还差点'
                when salary<=15000 then '不错了'
                when salary>15000 then '好' end as '工资水平'
    from (select * from emp where emp.dept_id=1) e left join dept d on e.dept_id = d.id;

    -- 查询"研发部"员工的平均工资。
        select avg(salary) from emp where dept_id=1;
-- 查询工资比"灭绝"高的员工信息。
    select emp.keyname from emp where keyname='陈友谅';
    select emp.salary from emp,(select * from emp where keyname='陈友谅') e where emp.salary>e.salary;

-- 查询比平均薪资高的员工信息。
    select avg(salary) from emp ;
    select * from emp where salary> (select avg(salary) from emp);

六、事务

1.事务的概念

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

2.事务的结构

begin;-- start transaction;
-- 需要执行的sql语句集合
commit;-- 提交

rollback;-- 回滚

3.事务四大特性

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

4.并发事务问题

• 赃读 ：一个事务读到另外一个事务还没有提交的数据。
  

• 不可重复读:：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
  

• 幻读 ：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影”。
  

5.事务隔离级别

• 事务隔离级别与对应能解决的并发问题
  
• – 查看事务隔离级别
  select @@transaction_isolation
• 设置事务隔离级别
  set session transaction isolation level 隔离级别