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

作者：BSXY_19计科_陈永跃

BSXY_信息学院

注：未经允许禁止转发任何内容

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

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

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

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

1、数据库操作(DDL)

查询：

#查询所有数据库：showdatabases;---------------------------------#查询当前数据库：selectdatabase();

创建：

#创建数据库createdatabase[ifnotexists] 数据库名 [defaultcharset 字符集][collate 排序规则];#:如1：createdatabaseifnotexists test defaultcharset utf8mb4;#如2：CREATEDATABASEIFNOTEXISTS RUNOOB DEFAULTCHARSET utf8 COLLATE utf8_general_ci;

删除：

#删除数据库dropdatabase[ifexists] 数据库名;#如：dropdatabaseifexists test;

使用：

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

2、表操作(DDL)

查询：

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

创建表：

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

idnameagegender1张三28男2李四60男3王五32女

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

修改表：

#修改表名：altertable tb_user renameto usm;

删除表：

#删除表droptable[ifexists] 表名;-------------------------#删除指定表，并重新创建该表(只删除数据留结构了解)truncatetable 表名;

添加字段：

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

修改字段：

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

删除字段：

altertable 表名 drop 字段名;#如：altertable tb_user drop username;

3、数据操作(DML)

添加数据：

#给指定数据添加数据：insertinto 表名(字段名1，字段名2,...)values(值1,值2,...)#如：insertinto usm(id,name,age,gender,username)values(1,'张三',19,'男','小三');-------------------------#给全部字段添加数据：insertinto 表名 values(值1,值2,...)#如：insertinto usm values(1,'张三',19,'男','小三');-------------------------#批量添加数据：insertinto 表名 (字段名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字段为小五

删除数据：

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

查询：

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

4、查询(DQL)

select
    字段列表
from
    表名列表
where
    条件列表
groupby
    分组字段列表
having
    分组后条件列表
orderby
    排序字段列表
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;-------------------------#去除重复记录selectdistinct 字段列表 from 表名;#如：selectdistinct name from usm;

2、条件查询

select 字段列表 from 表名 where 条件列表;-------------------------------------------
    条件：
比较运算符                功能
>                        大于
>=                        大于等于
<                        小于
<=                        小于等于
=                        等于
<>或!=                    不等于
between..and..             在某个范围之内
in(...)                    在in之后的列表中的值，多选一
link 占位符              模糊匹配(_匹配单个字符,%匹配任意个字符)isnull                    是null

逻辑运算符：
and或&&                    并且
or或||                    或者
not或!                    非不是
------------------------------------------#查询年龄等于80的人信息select*from emp where age=80;#查询没有写age的人的信息select*from emp where idcare isnull;#查询写了aged的人的信息select*from emp where idcare isnotnull;#查询年龄不等于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 >=15and age<=20;select*from emp wherebetween15and20;#查询性别为女且年龄小于25岁的员工信息select*from emp where gender='女'and age<25;#查询年龄等于18或20或40的员工信息select*from emp where age=18or age=20or 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是不参与计算的#统计员工数量selectcount(id)from usm;selectcount(*)from usm;#统计员工平均年龄selectavg(age)from usm;#统计西安地区员工年龄之和selectsum(age)from usm where workaddress='西安';

4、分组查询

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

5、排序查询

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

6、分页查询

select 字段列表 from 表名 limit 起始索引,查询记录数;-------------------------------------#查询第1页员工数据，每页展示10条记录select*from emp limit0,10;#查询第2页员工数据，每页展示10条记录---->起始索引计算:(页码-1)*页展示记录数select*from emp limit10,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 between20and40and name like'___';#统计员工表中，年龄小于60岁的，男性员工和女性员工的人数select gender,count(*)from emp where age<60groupby gender;#根据年龄小于等于35岁员工的姓名和年龄，对年龄进行升序排序，年龄相同，在按照入职时间进行降序排序select name,age from emp where age<=35orderby age=asc,emdate desc;#查询性别为男，且年龄在20-40(含)岁以内的前5个员工信息，对查询的结果按照年龄升序排序，年龄相同按入职时间升序排序select*from emp where gender='男'and age between20and40orderby age aes,emdate ses limit5;

5、用户管理(DCL)

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

6、用户管理(DCL)

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

7、函数

1、字符串函数

函数功能concat(s1,s2,…sn)字符串拼接，将s1,s2…sn拼接成一个字符串lower(str)将字符串str全部转为小写upper(str)将字符串str全部转为大写lpad(str,n,pad)左填充，用字符串pad对str的左边进行填充，达到n个字符串长度rpad(str,n,pad)右填充，用字符串pad对str的右边进行填充，达到n个字符串长度trim(str)去掉字符串 头部 和 尾部 的空格substring(str,star,len)返回从字符串str从start位置起的len个长度的字符串

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、数值函数

函数功能celt(x)向上取整floor(x)向下取整mod(x,y)返回x/y的模rand()返回0-1内的随机数round(x,y)求参数x的四舍五入的值，保留y为小数

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

3、日期函数

函数功能curdate()返回当前日期curtime()返回当前时间now()返回当前日期和时间year(date)获得指定date的年份month(date)获取指定date的月份day(date)获取指定date的日期date_add(date,interval expr type)返回一个日期/时间值加上一个时间间隔expr后的时间值datediff(date1,date2)返回起始时间date1和结束时间date2之间的天数

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

4、流程控制函数

函数功能if(value,t,f)如果value为true，返回t,否则返回fifnull(value1,value2)如果value1不为空，返回value1,否则返回value2case when [val1] then [res1]…else [default] end如果val1为true,返回res1,…否则返回default默认值case [expr] when [val1] then [res1]…else [default] end如果expr的值等于val1，返回res1,…否则返回default默认值

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

8、常见约束&案例

约束描述关键字非空约束限制该字段的数据不能为nullnot null唯一约束保证该字段的所有数据都唯一，不重复unique主键约束主键是一行数据的唯一标识，要求非空且唯一primary key默认约束保存数据时，如果未指定该字段的值，则采用默认值default检查约束保证字段值满足某一个条件check外键约束用来让两张表的数据之间建立连接，保证数据的一致性和完整性foreign key
案例(创建下表)如：
字段名字段含义字段类型约束条件约束关键字idid唯一标识int主键，并且自动增长primary key,auto_incrementname姓名varchar(10)不为空，并且唯一not null,uniqueage年龄int大于0，且小于等于120checkstatus状态char(1)如果没有指定该值，默认为1defaultgender性别char(1)无

createtableuser(
id intprimarykeyauto_incrementcomment'主键',
name varchar(10)notnulluniquecomment'姓名',
age intcheck(age>0&& age<=120)comment'年龄',statuschar(1)default'1'comment'状态',
gender char(1)comment'性别')comment'用户表';

9、外键约束&操作

#添加外键：altertable 表名 addconstraint 外键名称 foreignkey(外键字段名)references 主表(主表列名);#如：altertable emp addconstraint fk_emp_dept_id foreignkey(dept_id)references dept(id);--------------------------------------------#删除外键：altertable 表名 dropforeignkey 外键名称;#如：altertable emp dropforeignkey fk_emp_dept_id;--------------------------------------------

行为说明no action在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与 RESTRICT一致)(默认)restrict当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与 NO ACTION一致)(默认)cascade当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录(改父表那子表也跟着改)set null当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null (前提要求外键允许取null)(父表改那子表就改为null)

#添加外键且更新时行为，如：altertable 表名 addconstraint 外键名称 foreignkey(外键字段名)references 主表(主表列名)onupdatecascadeondeletecascade;altertable 表名 addconstraint 外键名称 foreignkey(外键字段名)references 主表(主表列名)onupdatesetnullondeletesetnull;

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、部门名称selectdistinct 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 表2on 连接条件...;#如：查询每一个员工的姓名，及关联的部门的名称(显式内连接)inner可以省略select e.name,d.name from emp e innerjoin dept d on e.dept_id=d.id;#如：查询年龄小于30岁的员工姓名、年龄、职位、部门信息(显示内连接)select e.name,e.age,e.job,d.name from emp e innerjoin dept d on e.dept_id=d.id where e.age <30;

3、外连接

#左外连接：select 字段列表 from 表1left[outer]join 表2on 条件...;#相当于查询表1的所有数据包含表1和表2的交集部分的数据#如：查询emp表中的所有数据，和应的部门信息(左连接)select e.* d.name from emp e leftouterjoin dept d on e.dept_id=d.id;-----------------------------------------#右外连接:select 字段列表 from 表1left[outer]join 表2on 条件...;#如：查询dept表的所有数据，和对应的员工信息(右连接)select d.*,e.*from emp e rightouterjoin dept d on e.dept_id=d.id;#改为左连接：select d.*,e.*from dept d leftouterjoin 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 leftjoin 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<5000unionallselect*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                在指定的集合范围之内，多选一
notin            不在指定的集合范围之内
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 ='小明';#假设查到 12500  1#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 leftjoin dept d on e.dept_id=d.id
------------------------------------------#如：查询低于本部门平均工资的员工#a.查询指定部门平均薪资selectavg(e1.salary)from emp e1 where e1.dept_id=1;selectavg(e1.salary)from emp e1 where e1.dept_id=2;#b.查询低于本部门平均工资的员工select*from emp e2.salary where e2.salary <(selectavg(e1.salary)from emp e1 where e1.dept_id=e2.dept_id);#也可以查出平均薪资作为对比select*,selectavg(e1.salary)from emp e1 where e1.dept_id=e2.dept_id '平均'from emp e2.salary where e2.salary <(selectavg(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 -1000where name ='张三'；
#3、给李四账余额+1000update account set money = money +1000where name ='李四';--------------------------------#事务操作(手动)#查看/设置事务提交方式select @@autocomment;#1代表自动提交 0 代表手动提交set @@autocomment=0;#设置为手动提交#提交事务commit;#回滚事务rollback;--------------------------------#事务操作(自动)#开启事务starttransaction 或 begin;#提交事务commit;#回滚事务rollback;

隔离级别脏读不可重复读幻读read uncommitted有有有read committed无有有repeatable read(默认)无无有serializable无无无

#查看事务隔离级别select @@transaction_isolation;#设置事务隔离级别set[session|global]transactionisolationlevel {readuncommitted|readcommitted|repeatableread|serializable}

12、存储引擎

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