mysql学习笔记

mysq学习笔记

     在学习mysql的时候随手做的笔记，为了方便以后自己来查看，所以在这留个记录。

单表查询

create database test02;
use test02;
​
show databases ;
​
select * from emp;
​
insert into emp(id, workno, name, gender, age, idcard, entrydata)
    values(2,'1234567894','张三','男','18','123456789123456789','2024-3-29');
​
insert into emp(id, workno, name, gender, age, idcard, entrydata)
    values(14,'1234567894','张三丰','男',18,'123456789123456789','2024-3-29'),
          (13,'22547898','张无忌','男',28,'123456789122456789','2024-4-29'),
          (4,'223547898','李白','男',28,'123456789122456789','2024-4-29'),
          (5,'223547898','胡适','男',28,'123456789122456789','2024-4-29'),
          (6,'223547898','白居易','男',28,'123456789122456789','2024-4-29'),
          (7,'22','赵敏','男',28,'123456789122456789','2024-4-29'),
          (8,'27898','李世民','男',28,'123456789122456789','2024-4-29'),
          (9,'47898','刘禅','男',28,'123456789122456789','2024-4-29'),
          (10,'247898','刘彻','男',28,'123456789122456789','2024-4-29'),
          (11,'228','刘德华','男',28,'123456789122456789','2024-4-29'),
          (12,'12','孟浩然','男',28,'123456789122456789','2024-4-29');
​
delete from emp where id=2;
​
update emp set name = '王五',gender = '女' where id = 3;
​
update emp set entrydata = '2008-3-8' where id = 3;
​
select id, workno, name, gender, age, idcard, entrydata from emp where age < 20;
​
select id, workno, name, gender, age, idcard, entrydata from emp where age between 30 and 50;
​
select idcard as  '身份证号' from emp;
​
select distinct idcard as  '身份证号' from emp;
​
select id, workno, name, gender, age, idcard, entrydata from emp where name  like'__';
​
select id, workno, name, gender, age, idcard, entrydata from emp where age = 18 or age = 15 or age = 88;
select id, workno, name, gender, age, idcard, entrydata from emp where age in(18,15,88);
​
select id, workno, name, gender, age, idcard, entrydata from emp where idcard  like '%X' or idcard like '%Y';
​
-- 聚合函数
-- 1.统计员工数量
select count(id) from emp;
​
-- 2.求所有员工的平均年龄
select avg(age) from emp;
​
-- 3.求员工的最大，最小年龄
select max(age) from emp;
​
select min(age) from emp;
​
-- 4.求所有姓刘的员工的平均年龄
select sum(age) from emp where name like '刘%';
​
-- 分组查询
-- 1.根据性别分组，统计 男性员工 和 女性员工的数量
select gender, count(*) from emp group by gender;
​
-- 2.根据性别分组，统计 男性员工 和 女性员工的平均年龄
select gender '性别',avg(age) '平均年龄' from emp group by gender;
​
-- 3.查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址 -》having --> 分组后对结果进行过滤
select workaddress, count(*) from emp where age < 45  group by workaddress having count(*) >=3 ;
​
-- 排序查询
-- 1. 根据年龄对公司的员工进行升序排序
select * from emp order by age  ;
​
-- 2. 根据入职时间对公司的员工进行降序排序
select * from emp order by entrydata desc ;
​
-- 3. 根据年龄对公司的员工进行升序排序，年龄相同，根据入职时间对公司的员工进行降序排序
select * from emp order by age ,entrydata desc ;
​
-- 分页查询 limit   起始索引= （页码-1）* 每页显示的条数
select * from emp limit 0,10;
​
-- 练习
select * from emp where gender = '女' and age in(20,21,22,23);
​
select * from emp where gender = '男' and age >=20 and age <=40  and name like '___';
​
select gender '性别', count(*) '人数' from emp where age <=60 group by gender;
​
select name '姓名' ,age '年龄', entrydata '入职时间' from emp where age <= 35 order by age ,entrydata desc ;
​
select * from emp where gender = '男' and age between 20 and 40 order by age asc,entrydata asc limit 5;
​
​
-- 字符串函数
-- length(s) 返回字符串s的长度
    select length('hello');
-- char_length(s) 返回字符串s的字符数
    select char_length('hello');
-- substring(s,start,end) 返回字符串s中从start位置开始，到end位置结束的一个子串
    select substring('hello',1,3);
-- concat(s1,s2,s3....) 将多个字符串连接成一个字符串
    select concat('hello','world');
-- lower(s) 将字符串s中所有大写字母转换为小写字母
    select lower('Hello');
-- upper(s) 将字符串s中所有小写字母转换为大写字母
    select upper('world');
-- lpad(s1,len,s2) 用字符串s2对字符串s1进行左填充，直到长度为len
    select lpad('1',6,'0');
-- rpad(s1,len,s2) 用字符串s2对字符串s1进行右填充，直到长度为len
    select rpad('1',6,'0');
-- trim(s)去掉字符串s开始和结尾的空格
    select trim('   hello  word   ');-- 不会清除中间的空格
-- substring(s,start,end) 返回字符串s中从start位置开始，到end位置结束的一个子串
    select substring('hello world',7,11);
-- repeat(s,x) 将字符串s重复x次
    select repeat('好帅',3);
-- replace(s,a,b) 用字符串b替换字符串s中所有出现的字符串a
    select replace('hello world','world','java');
-- strcmp(s1,s2) 比较字符串s1和s2
    select strcmp('hello','hello');
-- reverse(s) 返回颠倒字符串s的字符串
    select reverse('hello');
​
update emp set workno =  lpad(workno,5,'0');
​
​
-- 数值函数
-- ceil(x) 返回  大于等于x  的最小整数值
    select ceil(12.3);
-- floor(x) 返回  小于等于x  的最大整数值
    select  floor(12.3);
-- rand 返回0到1之间的随机值
    select rand();
-- round(x) 返回数值x的四舍五入值
    select round(12.584846);
-- round(x,d) 返回数值x的四舍五入到d位小数的值
    select round(12.584846,2);
-- truncate(x,d) 返回数值x截断到d位小数的值
    select truncate(12.584846,2);
-- mod(x,y) 返回x/y的模
    select mod(10,3);
-- 随机生成一个六位验证码
select lpad(round(rand()*100000,0),6,'0');
​
​
-- 日期函数
-- now 返回当前日期和时间
select now();
-- curdate 返回当前日期
select curdate();
-- curtime 返回当前时间
select curtime();
-- year(d) 返回日期d中的年份
select year(now());
-- month(d) 返回日期d中的月份
select month(now());
-- day(d) 返回日期d中的天数
select day(now());
-- hour(t) 返回时间t中的小时数
-- date_add(d,interval expr type) 返回一个日期/时间值，该值是日期/时间d加上一个时间段
select date_add(now(),interval 1 day);
-- date_sub(d,interval expr type) 返回一个日期/时间值，该值是日期/时间d减去一个时间段
select date_sub(now(),interval 1 day);
​
-- datediff(d1,d2) 返回起始日期d1和结束日期d2之间的天数
select datediff('2020-12-31','2020-12-25');
​
-- 查询员工入职天数
select name , datediff(now(),entrydata) as 'entrydatas'  from emp order by entrydatas desc;
​
​
​
-- 逻辑
-- if(value,t,f) 如果value是真，返回t，否则返回f
-- ifnull(value1,value2) 如果value1不是空，返回value1，否则返回value2
-- case when [value] then [result] ... else [default] end 相当于java中的switch case
-- case [expression] when [value] then [result] ... else [default] end 相当于java中的switch case
​
​
create table score(
    id int comment 'ID',
    name varchar(20) comment '姓名',
    math int comment '数学',
    english int comment '英语',
    physics int comment '物理'
)comment '学生成绩表';
insert into score(id,name,math,english,physics)values
     (1,'张三',80,90,99),(2,'李四',60,70,80),
     (3,'王五',90,80,70),(4,'赵六',80,70,60),
     (5,'钱七',70,60,50),(6,'孙八',60,50,40),
     (7,'周九',50,40,30),(8,'吴十',40,30,20),
     (9,'郑十一',30,20,10),(10,'王十二',20,10,0)  ;
​
select
    id,
    name,
case when math >= 90 then '优秀' when math >= 60 then '及格' else '不及格' end as '数学',
case when score.english >= 90 then '优秀' when english >= 60 then '及格' else '不及格' end as '英语',
case when score.physics >= 90 then '优秀' when physics >= 60 then '及格' else '不及格' end as '物理'
from score;
​
​
-- 字段约束
create table user02(
    id int primary key auto_increment comment '主键',
    name varchar(20) not null unique comment '姓名',
    age int check( age > 0 and age <= 150) ,
    email varchar(50) ,
    status char(1) default '1' comment '状态'
) comment '用户表';
​
​
create table user02(
    id int primary key auto_increment,
    name varchar(20) not null unique,
    age int check( age > 0 and age <= 150),
    email varchar(50),
    status char(1) default '1'
) comment '用户表';
​
​
​
insert into user02(name,age,email) values
    ('张三',20,'zhangsan@qq.com'),
    ('李四',20,'lisi@qq.com'),
    ('王五',20,'wangwu@qq.com'),
    ('赵六',20,'zhaoliu@qq.com')

多表查询

数据准备

-- 部门表
create table dept(
    id int auto_increment primary key comment '部门id',
    name varchar(20)comment '部门名称'
) comment '部门表';
​
insert into dept(name) values ('开发部');
insert into dept(name) values ('市场部');
insert into dept(name) values ('财务部');
insert into dept(name) values ('运维部');
​
​
create table student(
    id int auto_increment primary key comment '学生id',
    name varchar(20)comment '学生姓名',
    age int comment '学生年龄',
    no varchar(20)comment '学号'
)comment '学生表';
​
insert into student(name,age,no) values('张三',20,'001');
insert into student(name,age,no) values('李四',21,'002');
insert into student(name,age,no) values('王五',22,'003');
insert into student(name,age,no) values('赵六',23,'004');
​
create table course(
    id int auto_increment primary key comment '课程id',
    name varchar(20)comment '课程名称'
)comment '课程表';
​
insert into course(name) values('数学');
insert into course(name) values('英语');
insert into course(name) values('物理');
insert into course(name) values('化学');
​
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_course(student_id,course_id) values(1,1),(1,2),(1,3),(2,2),(2,3),(2,4);
​
​
create table teacher(
    id int auto_increment primary key comment '教师id',
    name varchar(20)comment '教师姓名'
)comment '教师表';
​
insert into teacher(name) values('张老师');
insert into teacher(name) values('李老师');
insert into teacher(name) values('王老师');
insert into teacher(name) values('赵老师');
​
-- 薪资表
create table salgrade(
    grade int comment '薪资等级',
    losal int comment '最低薪资',
    hisal int comment '最高薪资'
)comment '薪资等级表';
​
insert into salgrade(grade,losal,hisal) values(1,700,1200);
insert into salgrade(grade,losal,hisal) values(2,1201,1400);
insert into salgrade(grade,losal,hisal) values(3,1401,2000);
insert into salgrade(grade,losal,hisal) values(4,2001,3000);
insert into salgrade(grade,losal,hisal) values(5,3001,9999);

操作步骤

/*
数据库设计三范式：
多表关系 --->  一对多（多对一），多对多，一对一
    1.一对多 ：在多的一方建立外键，指向一的一方的主键
    
    2.多对多 ：需要借助第三张中间表，中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
   
   3.一对一 ：一对一关系在数据库中是很少存在的，一般都是通过其他方式来解决，比如通过唯一主键的方式
​
多表查询 --->  内连接，外连接，子查询，联合查询
   1.内连接：
       隐式内连接: select 查询列表 from 表1 , 表2 where 条件;
       显式内连接: select 查询列表 from 表1 [inner] join 表2 on 连接条件;
       内连接查询结果 = 交集
  
  2.外连接： 左外连接，右外连接，全外连接
       左外连接: select 查询列表 from 表1 left [outer] join 表2 on 条件;
       右外连接: select 查询列表 from 表1 right [outer] join 表2 on 条件;
       全外连接: select 查询列表 from 表1 full [outer] join 表2 on 条件;
   
   3.子查询：子查询的结果是单行单列的，子查询可以作为条件，使用运算符去判断  
   ——》标量子查询，列子查询，行子查询，表子查询
       标量子查询： 子查询的结果是单行单列的
       列子查询： 子查询的结果是一列多行的
       行子查询： 子查询的结果是多行单列的
       表子查询： 子查询的结果是多行多列的
       子查询可以作为条件，使用运算符去判断
       标量子查询： select 查询列表 from 表1 where 列名 = (子查询);  -->单行单列
       列子查询： select 查询列表 from 表1 where 列名 in (子查询);   -->多行单列
       行子查询： select 查询列表 from 表1 where (子查询);          -->单行多列
       表子查询： select 查询列表 from 表1 where (子查询);          -->对行多列
​
   4.联合查询： union，union all
       将多条查询语句的结果合并成一个结果
       要求多条查询语句的查询列数是一致的
       要求多条查询语句的查询的表类型是相同的
       union 去重，union all 包含重复项
 */
​
-- 内连接
-- 1.查询每一个员工的姓名和对应的部门名称（部门表和员工表进行连接）--->隐式内连接
select e.name ,d.name from emp e ,dept d where e.dept_id = d.id;
select emp.name,dept.name from emp ,dept where emp.dept_id = dept.id;
​
-- 2.查询每一个员工的姓名和对应的部门名称（部门表和员工表进行连接）--->显式内连接  ---》inner join ...on...(inner可以省略)
​
select e.name ,d.name from emp e inner join dept d on e.dept_id = d.id;
select emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;
​
​
-- 外连接
-- 1.查询 emp表 所有数据和对应的部门名称（部门表和员工表进行连接）--->左外连接 left outer join ...on...(outer可以省略)
select emp.* , dept.name from emp left outer join dept on emp.dept_id = dept.id;
select e.*, d.name  from emp e left join dept d on e.dept_id = d.id;
​
-- 2.查询 dept表 所有数据和对应的员工名称（部门表和员工表进行连接）--->右外连接 right outer join ...on...(outer可以省略)
select dept.*,emp.* from emp right outer join dept on emp.dept_id = dept.id;
select d.*,e.*  from emp e right join dept d on e.dept_id = d.id;
​
-- 自连接
-- 1.查询员工及其领导的名字（员工表和员工表进行连接）
-- 表结构：emp02
select e.name , m.job from emp02 e join emp02 m on e.managerid = m.id;
select e.name , m.job from emp02 e , emp02 m where e.managerid = m.id;
​
-- 2.查询所有员工 emp02 及其领导的名字emp02，如果员工没有领导，也需要查询出来（员工表和员工表进行连接）
select e.name , m.name from emp02 e left join emp02 m on e.managerid = m.id;
select e.name , m.name from emp02 e , emp02 m where e.managerid = m.id;
​
--
-- 查询所有员工的工资等级
select e.name ,s.grade from emp02 e ,salgrade s where e.salary between s.losal and s.hisal;
​
-- 查询"开发部"所有员工的工资等级
select e.*,s.grade from emp02 e, salgrade s, dept d where dept_id = d.id and (e.salary between  s.losal and s.hisal)  and  d.name = '开发部';
​
-- 查询"财务部"所有员工的平均工资
select avg(e.salary) from emp02 e,dept d where e.dept_id = d.id and d.name = '财务部';
​
-- 查询工资比“卢俊义”高的员工信息
select * from emp02 where salary > (select salary from emp02 where name = '卢俊义');
​
-- 查询比平均工资高的员工信息
select avg(emp02.salary) from emp02 ;
select * from emp02 where salary > (select avg(emp02.salary) from emp02);
​
​
-- 查询所有学生的选课情况，包括姓名，学号、课程名
select s.name '姓名' ,s.no '学号' , c.name '课程名' from student s ,student_course sc, course c where s.id = sc.student_id and sc.course_id = c.id;
​
​

事务

/*
 1.事务简介
     事务是一个不可分割的工作单位，事务中包含的诸操作要么都做，要么都不做。
​
 2.事务操作
 (1)开启事务
     set autocommit = 0;
     start transaction;
 (2)提交事务
     commit;
 (3)回滚事务
     rollback;
 3.事务的特性
     (1)原子性：事务是一个不可分割的工作单位，事务中包含的诸操作要么都做，要么都不做。
     (2)一致性：事务前后数据的完整性必须保持一致。
     (3)隔离性：多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间
     (4)持久性：一个事务一旦被提交，它对数据库中数据的改变就是永久性的。
 4.事务的创建
     (1)隐式事务：事务没有明显的开启和结束的标记
        insert、update、delete语句
     (2)显式事务：事务具有明显的开启和结束的标记
        前提：必须先设置自动提交功能为禁用
             set autocommit = 0;
             start transaction;
             commit;  或者 rollback;
 5.事务的隔离级别
     (1)read uncommitted：读未提交
     (2)read committed：读已提交
     (3)repeatable read：可重复读（默认）
     (4)serializable：串行化
 6.事务的并发问题
     (1)脏读：对于两个事务T1、T2，T1读取了已经被T2更新但还处于未提交状态的数据。
     (2)不可重复读：对于两个事务T1、T2，T1读取了一个字段，然后T2更新了该字段。之后，T1再次读取同一个字段，值就不同了。
     (3)幻读：对于两个事务T1、T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行。之后，如果T1再次读取同一个表，就会多出几行。
 7.事务的保存点
     (1)savepoint 节点名1;
     (2)rollback to 节点名1;
 8.InnoDB自动提交
     (1)查看自动提交状态
        select @@autocommit;
     (2)设置自动提交状态
        set autocommit = 0;
        set autocommit = 1;
 9.事务的隔离级别
     (1)查看事务的隔离级别
        select @@tx_isolation;
     (2)设置事务的隔离级别
        set global transaction isolation level read uncommitted;
        set global transaction isolation level read committed;
        set global transaction isolation level repeatable read;
        set global transaction isolation level serializable;
 10.查看InnoDB支持的锁级别
     (1)查看InnoDB支持的锁级别
        show engines;
        show variables like '%innodb_locks%';
     (2)查看InnoDB的锁等待情况
        show engine innodb status;
​
 */
 
 
 -- 数据准备
create table account(
    id int auto_increment primary key comment '主键',
    name varchar(10) comment '姓名',
    money int comment '金额'
)comment '账户表';
​
insert into account (name, money) values ('张三', 5000), ('李四', 5000);
​
-- 数据恢复
update account set money = 5000 where name = '张三';