MySQL总结（上）

目录

一、SQL语句

1.1、DDL（数据库定义语言）

1.1.1、定义数据库

--创建库createdatabase 库名;--创建库时判断库是否存在，不存在则创建createdatabaseifnotexists 库名;--查看所有数据库showdatabases;--使用指定数据库use 库名;--查看当前指定数据库包含的数据表showtables;--查看数据库的结构定义信息showcreatedatabase 库名;--删除数据库dropdatabase 库名;--修改数据库的字符集为utf8alterdatabase 库名 characterset utf8;

1.1.2、定义数据表

--创建表createtable 表名 (
        字段1 字段1类型[comment 字段1注释],
        字段2 字段2类型[comment 字段2注释],
        字段3 字段3类型[comment 字段3注释],......
        字段n 字段n类型[comment 字段n注释])[comment 表注释];-- 查看表结构desc 表名;-- 查看创建表的SQL语句showcreatetable 表名;-- 修改表名altertable 表名 renameto 新的表名;--添加一个新的字段altertable 表名 add 字段; 字段类型;--修改字段名altertable 表名 renamecolumn 字段名 to 新的字段名;--修改字段类型(注意类型修改前后数据是否兼容)altertable 表名 modifycolumn 字段名 新的字段类型;--删除一个字段altertable 表名 drop 字段名;--删除表droptable 表名;--删除表时判断表是否存在,若存在则删除droptableifexists 表名;

数据类型：数值类型、字符串类型、日期时间类型

• 数值类型
• 字符串类型
• 日期时间类型

1.2、DML（数据库操作语言）

1.2.1、增加 insert into

-- 写全所有列名insertinto 表名(列名1,列名2,...列名n)values(值1,值2,...值n);-- 不写列名（所有列全部添加）insertinto 表名 values(值1,值2,...值n);-- 插入部分数据insertinto 表名(列名1,列名2)values(值1,值2);#insert into Persons values(1, 'Gates', 'Bill', 'Xuanwumen 10', 'Beijing');

1.2.2、删除 delete

-- 删除表中数据deletefrom 表名 where 列名  = 值;-- 删除表中所有数据deletefrom 表名;-- 删除表中所有数据（高效 先删除表，然后再创建一张一样的表。）truncatetable 表名;

1.2.3、修改 update

-- 不带条件的修改(会修改所有行,更新整张表)update 表名 set 字段名1=值1,字段名2=值2;-- 带条件的修改update 表名 set 字段名1=值1,字段名2=值2where 列名=值;

1.3、DQL（数据库查询语言）

1.3.1、基本查询和条件查询（where)

--查询多个字段select 字段1,字段2,字段3…… from 表名;select*from 表名;--设置别名select 字段1[as 别名1],字段2[as 别名2]…… from 表名;#select workadderss as '工作地址' from emp;#select workadderss '工作地址' from emp;--去除重复记录selectdistinct 字段列表 from 表名;#select distinct workadderss '工作地址' from emp;

条件查询

select 字段列表 from 表名 where 条件列表;

BETWEEN…AND （在什么之间）和 IN( 集合)：

-- 查询年龄大于等于20 小于等于30                SELECT*FROM student WHERE age >=20&&  age <=30;SELECT*FROM student WHERE age >=20AND  age <=30;SELECT*FROM student WHERE age BETWEEN20AND30;-- 查询年龄22岁，18岁，25岁的信息SELECT*FROM student WHERE age =22OR age =18OR age =25SELECT*FROM student WHERE age IN(22,18,25);

is not null(不为null值) 与 like（模糊查询）、distinct（去除重复值）

• _:单个任意字符
• %：多个任意字符

-- 查询英语成绩不为nullSELECT*FROM student WHERE english  ISNOTNULL;-- 查询姓马的有哪些？ likeSELECT*FROM student WHERE NAME LIKE'马%';-- 查询姓名第二个字是化的人            SELECT*FROM student WHERE NAME LIKE"_化%";-- 查询姓名是3个字的人SELECT*FROM student WHERE NAME LIKE'___';-- 查询姓名中包含德的人SELECT*FROM student WHERE NAME LIKE'%德%';

1.3.2、聚合函数

将一列数据作为一个整体，进行纵向计算。

注意：null值不参与所有聚合函数运算

select 聚合函数(字表列段)from 表名;# 聚合函数# 统计企业的员工数量selectcount(*)from emp;# 统计该企业员工的平均年龄selectavg(age)from emp;# 统计该企业员工的最大年龄selectmax(age)from emp;# 统计该企业员工的最小年龄selectmin(age)from emp;# 统计西安地区员工的年龄之和selectsum(age)from emp where workadderss ='西安';

1.3.3、分组查询（group by、having）

select 字段列表 from 表名 [where 条件]groupby 分组字段名 [having 分组后过来条件];# 分组查询# 根据性别分组，统计男性员工和女性员工的数量select gender,count(*)from emp groupby gender;# 根据性别分组，统计男性员工和女性员工的平均年龄select gender,avg(age)from emp groupby gender;# 查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址select workadderss ,count(*)from emp where age <45groupby workadderss havingcount(*)>3;

where和having的区别:

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断，而having可以。

注意：

• 执行顺序：where > 聚合函数 > having。
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

1.3.4、排序查询（order by）

select 字段列表 from 表名 orderby 字段1 排序方式,字段2 排序方式2;

1.3.5、分页查询（limit）

select 字段列表 from 表名 limit 起始索引,查询记录数;# 分页查询# 查询第1页员工数据，每页展示10条记录select*from emp limit0,10;select*from emp limit10;# 查询第2页员工数据，每页展示10条记录 ------>（页码 - 1）* 页展示记录数select*from emp limit10,10;

注意：

• 起始索引从0开始，起始索引 = (查询页码 - 1)*每页显示记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是limit。
• 如果查询的第一页数据，起始索引可以省略，直接简写为limit 10。

1.4、DCL（数据库控制语言）

1.4.1、管理用户

---查询用户use mysql;select*fromuser;---添加用户CREATEUSER'用户名'@'主机名' IDENTIFIED BY'密码';---删除用户DROPUSER'用户名'@'主机名';

1.4.2、权限管理

-- 查询权限SHOW GRANTS FOR'用户名'@'主机名';SHOW GRANTS FOR'lisi'@'%';-- 授予权限grant 权限列表 on 数据库名.表名 to'用户名'@'主机名';-- 给张三用户授予所有权限，在任意数据库任意表上GRANTALLON*.*TO'zhangsan'@'localhost';-- 撤销权限：revoke 权限列表 on 数据库名.表名 from'用户名'@'主机名';REVOKEUPDATEON db3.`account`FROM'lisi'@'%';

二、函数、约束、事务

2.1、函数

2.1.1、字符串函数

# concatselect concat('Hello',' MySql');# lowerselect lower('Hello');# upperselect upper('Hello');# lpadselect lpad('01',5,'-');# rpadselect rpad('01',5,'-');# trimselect trim(' Hello MySql ');# substringselect substring('Hello MySql',1,5);

eg：用于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0,比如：1号员工的工号应该为00001.

update emp set workno = lpad(workno,5,'0');

2.1.2、数值函数

# ceilselect ceil(1.1);# floorselect floor(1.9);# modselectmod(2,4);selectmod(6,4);# randselect rand();# roundselectround(2.345,2);selectround(2.344,2);

eg:# 通过数据库的数据，生成一个六位数的随机验证码

select lpad(round(rand()*1000000,0),6,'0');

2.1.3、日期函数

# curdateselect curdate();# curtimeselect curtime();# nowselectnow();# year(date)selectyear(curtime());# month(date)selectmonth(curtime());# day(date)selectday(curtime());# data_add(date, interval expr type)select date_add(curtime(),interval70year);select date_add(curtime(),interval70month);select date_add(curtime(),interval70day);# datediff(date1, date2)select datediff('2020-12-01','2020-11-01');

eg：查询所有员工的入职天数，并根据入职天数倒叙排序

select name ,datediff(curdate(), entrydate)as entryday from emp orderby entryday desc;

2.1.4、流程函数

# ifselectif(true,'ok','error');selectif(false,'ok','error');# ifnullselect ifnull('ok','default');select ifnull('','default');select ifnull(null,'default');# case when then else end# 查询emp表的员工姓名和工作地址(北京/上海--->一线城市，其他 ----> 二线城市)select
    name,(case workadderss when'北京'then'一线城市'when'上海'then'一线城市'else'二线城市'end)as'工作地址'from emp;

select
    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;

2.2、约束

1. 概念：约束是作用于表字段上的规则，用于限制存储在表中的数据。
2. 目的：保证数据库中数据的正确、有效性和完整性。
3. 分类：注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

举例：

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

2.2.1、外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

---添加外键altertable 表名 addconstraint 外键名称 foreignkey(外键字段名)references 主表（主表列名）;#alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);---删除外键altertable 表名 dropforeignkey 外键名称;#alter table emp drop foreign key fk_emp_dept_id;

具有外键的称为子表，外键关联的表称为父表

2.2.2、外键删除、更新行为

altertable 表名 addconstraint 外键名称 foreignkey(外键字段)references 主表名(主表字段名)onupdatecascadeondeletecascade;---举例（级联更新）altertable emp addconstraint fk_emp_dept_id foreignkey(dept_id)references dept(id)onupdatecascadeondeletecascade;

2.3、事务

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

要么同时成功，要么同时失败。

默认MySQL的事务时自动提交的，也就是说，当执行一条 DML语句马，MySQL会立即隐式的提交事务。

此时执行完语句，数据没有发生变化。需要执行commit语句

执行错误，执行回滚事务。（rollback）

事务的四大特性

并发事务问题

事务的隔离级别

三、多表查询

多表关系

从A表找B表内容，就在A中设置外键

多表查询

select*from emp, dept;# 笛卡尔积select*from emp, dept where emp.dept_id = dept.id;

3.1、连接查询

3.1.1、内连接（A、B交集）（join）

相当于查询A、B交集部分数据
1.隐式内连接：使用where条件消除无用数据

-- 语法： select 字段列表 from 表名1,表名2where 条件;-- 查询员工表的名称，性别。部门表的名称SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.dept_id= dept.id;

2.显式内连接

-- 语法： select 字段列表 from 表名1[inner]join 表名2on 连接条件;-- 例如：SELECT*FROM emp INNERJOIN dept ON emp.dept_id= dept.id;SELECT*FROM emp JOIN dept ON emp.dept_id = dept.id;

3.1.2、外连接（[outer] join）

左外连接：查询左表所有数据，以及两张表交集部分数据
相当于查询表1的所有数据包含表1和表2交际部分的数据

-- 语法：select 字段列表 from 表1left[outer]join 表2on 条件；
-- 例子：-- 查询员工表的所有数据，和对应的部门信息SELECT  e.*,d.name FROM emp e LEFTJOIN dept d ON e.dept_id = d.id;

右外连接：查询右表所有数据，以及两张表交集部分数据
相当于查询表2的所有数据包含表1和表2交际部分的数据

-- 语法：select 字段列表 from 表1right[outer]join 表2on 条件；
-- 例子：-- 查询部门表的所有数据，和对应的员工信息SELECT  d.*,e.*FROM emp e RIGHTJOIN dept d ON e.dept_id = d.id;

3.1.3、自连接和联合查询（union）

1、自连接

--语法:select 字段列表 from 表A 别名A join 表名A 别名B on 条件……;--例子select a.name '员工', b.name '领导'from emp a join emp b on a.managerid = b.id;

2、联合查询
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集

--语法：select 字段列表 from 表A …… 
union[all]select 字段列表 from 表B ……;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

• union all会将全部的数据直接合并在一起
• union会对合并之后的数据去重

3.2、子查询(嵌套select语句)

SQL语句中嵌套select语句，称为嵌套语句，又称子查询

--语法：select*from t1 where column1 =(select column1 from t2);

1、标量子查询
子查询返回的结果是单个值（数字、字符串、日期等），最简单地形式，这种子查询称为标量子查询
常用的操作符：= <> > >= < <=

# 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 ='销售部');

2、列子查询
子查询返回的结果是一列（可以是多行），这种查询称为列子查询

常用的操作符： in、 not in、 any 、some 、all

# 查询 销售部 和 市场部 的部门IDselect id from dept where name ='销售部'or name ='市场部';# 根据部门ID，查询员工信息select*from emp where dept_id in(select id from dept where name ='市场部'or name ='销售部');

3、行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符：= 、 <> 、 in、 not in

# 行子查询# 查询 张无忌 的薪资及直属领导相同的员工信息select salary, managerid from emp where name ='张无忌';# 查询与 张无忌 的薪资及直属领导相同的员工信息select*from emp where(salary, managerid)=(select salary, managerid from emp where name ='张无忌');

4、表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询。

常用的操作符：in

# 表子查询# 查询 鹿杖客、宋远桥 的职位和薪资select job, salary from emp where name ='鹿杖客'or name ='宋远桥';# 查询与 鹿杖客、宋远桥 的职位和薪资相同的员工信息select*from emp where(job, salary)in(select job, salary from emp where name ='鹿杖客'or name ='宋远桥');