MySQL学习指南&笔记&经典案例句

作者：BSXY_19计科_陈永跃 BSXY_信息学院 注：未经允许禁止转发任何内容

该文章是一篇关于MySQL的一个学习的笔记或是指南，该文章中有很多的经典的案例可进行相应的练习和参考，后期的话会持续更新关于数据库系统方面的文章。

MySQL学习指南&笔记&经典案例句

• • 0、MySQL综合案例(可不看)
  • 1、数据库操作(DDL)
  • 2、表操作(DDL)
  • 3、数据操作(DML)
  • 4、查询(DQL)
  • • 1、基础查询
    • 2、条件查询
    • 3、聚合函数
    • 4、分组查询
    • 5、排序查询
    • 6、分页查询
    • 7、综合练习
  • 5、用户管理(DCL)
  • 6、用户管理(DCL)
  • 7、函数
  • • 1、字符串函数
    • 2、数值函数
    • 3、日期函数
    • 4、流程控制函数
  • 8、常见约束&案例
  • 9、外键约束&操作
  • 10、多表查询
  • • 1、多表查询
    • 2、内连接
    • 3、外连接
    • 4、自连接
    • 5、联合查询
    • 6、子(嵌套)查询
  • 11、事务的操作
  • 12、存储引擎

0、MySQL综合案例(可不看)

关于综合案例的话可以对该篇文章进行查阅和学习也附加了相应的问题和sql句：
MySQL综合应用练习(直接拷贝到自己空间)
后期的话呢也会更新更多的相应的文章

1、数据库操作(DDL)

查询：

#查询所有数据库：show databases;---------------------------------#查询当前数据库：select database();

创建：

#创建数据库create database [if not exists] 数据库名 [default charset 字符集][collate 排序规则];#:如1：create database if not exists test default charset utf8mb4;#如2：CREATE DATABASE IF NOT EXISTS RUNOOB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

删除：

#删除数据库drop database [if exists] 数据库名;#如：drop database if exists test;

使用：

#使用数据库：use 数据库名;

2、表操作(DDL)

查询：

#查询当前数据库所有表show tables;-------------------------#查询表结构desc 表名;-------------------------#查询指定表的建表语句/表的详细信息show create table 表名;

创建表：

create table 表名(字段1;字段1类型[comment 字段1注释],字段2;字段2类型[comment 字段2注释],................................字段n;字段n类型[comment 字段n注释])[comment 表注释];

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

修改表：

#修改表名：alter table tb_user rename to usm;

删除表：

#删除表drop table [if exists] 表名;-------------------------#删除指定表，并重新创建该表(只删除数据留结构了解)truncate table 表名;

添加字段：

alter table 表名 add 字段名 类型(长度) [comment 注释][约束];#如：alter table tb_user add nickname varchar(20) comment '昵称';

修改字段：

#修改数据类型：alter table 表名 modify 字段名 新数据类型(长度);-------------------------#修改字段名和字段类型alter table 表名 change 旧字段名 新字段名 类型(长度)[comment 注释][约束]#如：alter table tb_user change nickname username vatchar(30) comment '用户名';

删除字段：

alter table 表名 drop 字段名;#如：alter table tb_user drop username;

3、数据操作(DML)

添加数据：

#给指定数据添加数据：insert into 表名(字段名1，字段名2,...)values(值1,值2,...)#如：insert into usm(id,name,age,gender,username)values(1,'张三',19,'男','小三');-------------------------#给全部字段添加数据：insert into 表名 values(值1,值2,...)#如：insert into usm values(1,'张三',19,'男','小三');-------------------------#批量添加数据：insert into 表名 (字段名1,字段名2,...) values(值1,值2,...),(值1,值2...),(值1,值2,...);insert 表名 values (值1,值2,...),(值1,值2...),(值1,值2,...);

修改数据：

update 表名 set 字段名1=值1,字段名2=2,...[where 条件];#如1：update usm set username='小五' where id=2;#如2：update usm set username='小赵',gender='女' where id=2;#如3：update usm set username='小五';#更新整张表的username字段为小五

删除数据：

delete from 表名 [where 条件];#如1：delete from usm where gender='女';#删除gender为女的数据字段:#如2：delete from usm #删除所有表的数据

查询：

#查询整个表：select * from 表名;#如：select * from usm;

4、查询(DQL)

select字段列表from表名列表where条件列表group by分组字段列表having分组后条件列表order by排序字段列表limit分页参数

1、基础查询

#查询多字字段select 字段1,字段2,... from 表名;select * from 表名;#如：select name,age from usm;--------------------------------#设置别名select 字段1[as 别名1],字段2[as 别名2]...from 表名;#如：select name as '名字' from usm;-------------------------#去除重复记录select distinct 字段列表 from 表名;#如：select distinct name from usm;

2、条件查询

select 字段列表 from 表名 where 条件列表;-------------------------------------------条件：比较运算符功能>大于>=大于等于<小于<=小于等于=等于或!=不等于between..and.. 在某个范围之内in(...)在in之后的列表中的值，多选一link 占位符模糊匹配(_匹配单个字符,%匹配任意个字符)is null是null逻辑运算符：and或&&并且or或||或者not或!非不是------------------------------------------#查询年龄等于80的人信息select * from emp where age=80;#查询没有写age的人的信息select * from emp where idcare is null;#查询写了aged的人的信息select * from emp where idcare is not null;#查询年龄不等于88的年龄的人的信息select * from emp where age !=88;select * from emp where age  88;#查询年龄在15(包含)到20(包含)的年龄的人的信息select * from emp where age >=15 && age<=20;select * from emp where age >=15 and age<=20;select * from emp where between 15 and 20;#查询性别为女且年龄小于25岁的员工信息select * from emp where gender='女' and age<25;#查询年龄等于18或20或40的员工信息select * from emp where age=18 or age=20 or age=40;select * from emp where age in(18,20,40);#查询姓名为2个字的员工信息select * from emp where name like '__';#查询身份证最后一位是X的员工信息select * from emp where idcard like '%X';

3、聚合函数

将一列数据作为一个整体，进行纵向计算函数功能count 统计数量max 最大值min 最小值avg 平均值sum 求和--------------------------------select 聚合函数(字段列表) from 表名;#所有的null是不参与计算的#统计员工数量select count(id) from usm;select count(*) from usm;#统计员工平均年龄select avg(age) from usm;#统计西安地区员工年龄之和select sum(age) from usm where workaddress='西安';

4、分组查询

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]-------------------------------where与having区别1.执行时机不同: where是 分组之前 进行过滤，不满足where条件,不参与分组;而having是 分组之后 对结果进行过滤。2.判断条件不同: where不能对聚合函数进行判断，而having可以。-------------------------------#根据性别分组，统计男性员工和女性员工的数量select gender,count(*) from emp group by gender;#根据性别分组，统计男性员工和女性员工的平均年龄select gender,avg(age) from emp group by gender;#查询年龄小于40的员工，并根据工作地址分组，获取员工数量(count获得的值)大于等于3的工作地址select workaddress,count(*) from emp where age<40 group by workaddress having count(*)>=3;

5、排序查询

select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;排序方式: asc:升序 desc:降序--------------------------#根据年龄对员工进行升序排序select * from emp order by age=asc;#根据年龄对员工进行升序排序，年龄相同，在按照入职时间进行降序排序select * from emp order by age=asc,emdate desc;

6、分页查询

select 字段列表 from 表名 limit 起始索引,查询记录数;-------------------------------------#查询第1页员工数据，每页展示10条记录select * from emp limit 0,10;#查询第2页员工数据，每页展示10条记录---->起始索引计算:(页码-1)*页展示记录数select * from emp limit 10,10;

7、综合练习

#查询年龄为20,21,23岁的女性员工select * from emp where gender='女' and age in(20,21,23);#查询性别为男，且年龄在20-40(含)以后的姓名为三个字的员工select * from emp where gender='男' and age between 20 and 40 and name like '___';#统计员工表中，年龄小于60岁的，男性员工和女性员工的人数select gender,count(*) from emp where age<60 group by gender;#根据年龄小于等于35岁员工的姓名和年龄，对年龄进行升序排序，年龄相同，在按照入职时间进行降序排序select name,age from emp where age<=35 order by age=asc,emdate desc;#查询性别为男，且年龄在20-40(含)岁以内的前5个员工信息，对查询的结果按照年龄升序排序，年龄相同按入职时间升序排序select * from emp where gender='男' and age between 20 and 40 order by age aes,emdate ses limit 5;

5、用户管理(DCL)

#查询mysql数据库中的用户use mysql;select * from user;------------------------------------#创建用户create user '用户名'@'主机名' identified by '123456';#@前后不能有空格#如：create user 'it'@'localhost' identified by '123456';------------------------------------#修改用户alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';#如：alter user 'it'@'localhost' identified with mysql_native_password by '1234';------------------------------------#删除用户drop user '用户名'@'主机名';

6、用户管理(DCL)

---------------------------------权限列表 说明all,all privileges所有权限select 查询数据insert 插入数据update 修改数据delete 删除数据alter 修改表drop 删除数据库/表/视图create 创建数据库/表---------------------------------#查询权限show grants for '用户名'@'主机名';#如：show grants for 'it'@'localhost';---------------------------------#授予权限,多个权限用逗号分隔grant 权限列表 on 数据库.表名 to '用户名'@'主机名';#如：*表示所有的grant all on test.* to 'it'@'localhost';---------------------------------#撤销权限revoke 权限列表 on 数据库.表名 from '用户名'@'主机名';#如：revoke all on test.* from 'it'@'localhost';

7、函数

1、字符串函数

select 函数;#如：select concat('hello','mysql');select lower('Hello');select lpad('01',5,'-');#---01select trim(' Hello mysql ');select substring('Hello mysql',1,5);#案例如：#需求变更，员工工号统一为5位数，目前不到5位数的全部在前面补0，比如：1号员工的工号就是00001update emp set workno=lpad(workno,5,'0');

2、数值函数

select ceil(1.1);#2select mod(7,4)select round(2.345,2)#2.35#如：通过数据库函数，生成一个六位数的随机验证码select lpad(round(rand()*1000000,0),6,'0');

3、日期函数

select curdate();#2023-10-11select year(now());#2023select date_add(now(),interval 70 day);#往后推70天的日期select datediff('2021-12-01','2021-11-01')#两个日期的间隔，第一个时间减第二个时间#如：查询所有员工的入职天数，并根据入职天数倒序排序select name,datediff(curdate(),emdate) as 'bieming' from emp order by bieming desc;

4、流程控制函数

select if(true,'OK','ERROR')#如：查询emp表的员工的姓名和工作地址(北京/上海-->一线城市)，其他-->二线城市selectname,(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'from emp;#统计班级学员的成绩，>=85展示优秀，>=60展示及格，否则展示不及格selectid,name,(case when math>=85 then '优秀' when math>=60 then '及格' else '不及格' end) as '数学',(case when english>=85 then '优秀' when english>=60 then '及格' else '不及格' end) as '英语',(case when chinese>=85 then '优秀' when chinese>=60 then '及格' else '不及格' end) as '语文'from score;

8、常见约束&案例

案例(创建下表)如：

create table user(id int primary key auto_increment comment '主键',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 '性别')comment '用户表';

9、外键约束&操作

#添加外键：alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);#如：alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);--------------------------------------------#删除外键：alter table 表名 drop foreign key 外键名称;#如：alter table emp drop foreign key fk_emp_dept_id;--------------------------------------------

#添加外键且更新时行为，如：alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名) on update cascade on delete cascade;alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名) on update set null on delete set null;

10、多表查询

1、多表查询

#如：select * from emp,dept where emp.dept_id=dept.id

2、内连接

内连接查询的是两张表交集的部分#隐式内连接select 字段列表 from 表1，表2, where 条件...;#如：查询每一个员工的姓名，及关联的部门的名称(隐式内连接)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;#如：查询拥有员工的部门ID、部门名称select distinct d.id,d.name from emp e,dept d where e.dept_id=d.id;#distinct:去重#如：查询所有员工的工资等级select e.*,s.grade,s.losal,s.hisal from emp e,salgrade s where e.salary>=s.losal and e.salary<=s.hisal;-----------------------------------#如：查询"研发部"所有员工的信息及工资等级#表：emp,salgrade,dept#连接条件：emp.salary between salgrade.losal and salgrade.hisal,emp.dept_id=dept.id#查询条件：dept.name='研发部'select e.*,s.grade from emp e,dept d,salarade s where e.dept_id=d.id and (e.salary between s.losal and s.hisal) and d.name ='研发部';-----------------------------------#如：查询所有学生的选课情况，展示出学生名称，学号，课程名称#表：student,course,student_course#连接条件：student.id=student_course.studentid,course.id=student_course.courseidselect s.name,sno,c.name from student s,student_course sc,course c where s.id=sc.studentid and sc.courseid=c.id;-----------------------------------#显示内连接select 字段列表 from 表1 [inner] join 表2 on 连接条件...;#如：查询每一个员工的姓名，及关联的部门的名称(显式内连接)inner可以省略select e.name,d.name from emp e inner join dept d on e.dept_id=d.id; #如：查询年龄小于30岁的员工姓名、年龄、职位、部门信息(显示内连接)select e.name,e.age,e.job,d.name from emp e inner join dept d on e.dept_id=d.id where e.age < 30;

3、外连接

#左外连接：select 字段列表 from 表1 left [outer] join 表2 on 条件...;#相当于查询表1的所有数据包含表1和表2的交集部分的数据#如：查询emp表中的所有数据，和应的部门信息(左连接)select e.* d.name from emp e left outer join dept d on e.dept_id=d.id;-----------------------------------------#右外连接:select 字段列表 from 表1 left [outer] join 表2 on 条件...;#如：查询dept表的所有数据，和对应的员工信息(右连接)select d.*,e.* from emp e right outer join dept d on e.dept_id=d.id;#改为左连接：select d.*,e.* from dept d left outer join emp e on e.dept_id=d.id;#一般都用左连接

4、自连接

#可以理解为自己查自己，可以是 内连接查询 也是可以是 外连接查询select 字段列表 from 表A 别名A join 表A 别名B on 条件...;#如：查询员工 及其 所有领导的名字select a.name, b.name from emp a,emp b where a.managerid=b.id;#如：查询所有员工(emp)及其领导的名字(emp),如果员工没有领导，也需要查询出来;select a.name '员工',b.name '领导' from emp a left join emp b on a.managerid=b.id;

5、联合查询

#就是把多次查询的结果合并起来，合并形成一个新的查询结果集#1、多张表的列数必须保持一致#2、union all会将全部的数据合并在一起，union会对合并之后的数据去重select 字段列表 from 表A...union [all]select 字段列表 from 表B...;#如：在emp中查询薪资低于5000的员工，和年龄大于50岁的员工全部查询出来select * from emp where salary<5000union allselect * from emp where age>50;#如果需要合并的结果去重的话就把all去了

6、子(嵌套)查询

根据子查询结果不同分为：
1、标量子查询(子查询结果为单个值)
2、列子查询(子查询结果为一列)
3、行子查询(子查询结果为一行)
4、表子查询(子查询结果为多行多列)
根据子查询的位置，分为：where之后，from之后，select之后
标量查询：

#标量子查询返回的结果是单个值(数字、字符串、日期等)，常用的操作符:=  > >= < <=#如：查询"销售部"的所有员工信息#a.查询"销售部"部门IDselect id from dept where name='销售部';#b.根据销售部部门ID，查询员工信息select * from emp where dept_id=4;#合并为：select * from emp where dept_id=(select id from dept where name='销售部');------------------------------------------#如：查询在"小明"入职之后的员工信息#a.查询小明的入职日期select emdate from emp where name='小明';#b.查询入职日期之后入职的员工select * from emp where emdate >'XXXX-XX-XX';#合并：select * from emp where emdate > (select emdate from emp where name='小明');

列子查询：

#常用的操作符：in、not in、any、some、all操作符描述in在指定的集合范围之内，多选一not in不在指定的集合范围之内any子查询返回列表中，有任意一个满足即可some与any等同，使用some的地方都可以使用anyall子查询返回列表的 所有值都必须满足 --------------------------------------------------#如：查询"销售部"和"市场部"的所有员工信息#a.查询"销售部"和"市场部"的部门IDselect id from dept where name = '销售部' or name ='市场部';#b.根据部门ID，查询员工信息select * from emp where dept_id in(2,4);#合并：select * from emp where dept_id in(select id from dept where name = '销售部' or name ='市场部');------------------------------------------#如：查询比 财务部 所有人工资都高的员工信息#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='财务部'));------------------------------------------#如：查询比研发部其中任意一人工资高的员工信息#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 > any (select salary from emp where dept_id=(select id from dept where name='研发部'));

行子查询：

#子查询返回结果是一行(可以是多列)#常用的操作符：= ,  , in , not in#如：查询与"小明"的薪资和直属领导相同的员工信息#a.查询小明的薪资和直属领导select salary,managerid from emp where name = '小明';#假设查到 125001#b.查询与小明的薪资和直属领导相同的员工信息select * from emp where (salary,managerid)=(12500,1);#合并：select * from emp where (salary,managerid)=(select salary,managerid from emp where name = '小明');

表子查询：

#常见的操作符：in#如：查询与“小明”，“小红”的职位和薪资相同的员工信息#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 ='小红');------------------------------------------#如：查询入职日期在“2006-01-01" 之后 的员工信息，及部门信息#a.入职日期是"2006-01-01"之后的员工信息select * from emp where emdate > "2006-01-01";#b.查询这部分员工，对应的部门信息select e.*,e.* from (select * from emp where emdate > "2006-01-01") e left join dept d on e.dept_id=d.id------------------------------------------#如：查询低于本部门平均工资的员工#a.查询指定部门平均薪资select avg(e1.salary) from emp e1 where e1.dept_id=1;select avg(e1.salary) from emp e1 where e1.dept_id=2;#b.查询低于本部门平均工资的员工select * from emp e2.salary where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id=e2.dept_id);#也可以查出平均薪资作为对比select *,select avg(e1.salary) from emp e1 where e1.dept_id=e2.dept_id '平均' from emp e2.salary where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id=e2.dept_id);

11、事务的操作

#实例如：转账操作(张三给李四转账1000)#1、查询张三账户余额select * from account where name = '张三';#2、将张三账户余额-1000update account set money = money - 1000 where name ='张三'；#3、给李四账余额+1000update account set money = money + 1000 where name ='李四';--------------------------------#事务操作(手动)#查看/设置事务提交方式select @@autocomment;#1代表自动提交 0 代表手动提交set @@autocomment=0;#设置为手动提交#提交事务commit;#回滚事务rollback;--------------------------------#事务操作(自动)#开启事务start transaction 或 begin;#提交事务commit;#回滚事务rollback;

#查看事务隔离级别select @@transaction_isolation;#设置事务隔离级别set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable}

12、存储引擎

#查看建表语句show create table 表名;#查看当前数据库支持的存储引擎show engines;