MySQL

基础篇

MySQL概述
SQL
函数
约束
多表查询
事务

---

MySQL概述

数据库： DataBase（DB）
存储数据的仓库，数据是有组织的进行存储
数据库管理系统:DataBase Management System(DBMS)
SQL ：Structured Query Language (SQL)
操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准
2.mysql 启动
net stop mysql80 停止
net start mysql80 启动

3.mysql客户端连接
mysql自带的客户端命令行
mysql[-h 127.0.0.1][-p 3306] -u root -p

MySQL数据库
关系型数据库
概念:建立在关系模型基础上，由多张相互连接的二位表组成的数据库
特点：
1.使用表存储数据，格式统一，便于维护
2.使用SQL语言操作，标准统一，使用方便

4.mysql 数据模型
数据库
表

SQL

1.SQL通用语法
2.SQL分类
3.DDL
4.DML
5.DQL
6.DCL

---

一、SQL通用语法

1.SQL语句可以单行或多行书写，以分号结尾
2.SQL语句可以使用空格/缩进来增强语句的可读性
3.MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4.注释：
单行注释：-- 注释内容或#注释内容（MySQL特有）
多行注释：/注释内容/

二、SQL分类

分类 全称 说明
DDL Data Definition Language 数据定义语言，用来定义数据库对象(数据库，表，字段)
DML Data Manipulation Language 数据操作语言，用来对数据库表中的数据进行增删改
DQL Data Query Language 数据查询语言，用来查询数据库中表的记录
DCL Data Control Language 数据控制语言，用来创建数据库用户、控制数据库的访问权限

---

DDL

DDL -数据库操作
查询：
查询所有数据库：show databases;
查询当前数据库：select database(); //查看当前在那个数据库里面
创建：create database [if not exists]数据库名 [字符集]
// if not exists 不存在这个数据库，然后就可以创建
//

删除：drop database；
使用数据库：use 数据库名；

//DDL 表操作 查询
查询当前数据库所有表： show tables；
查询表结构：desc 表名；
查询指定表的建表语句： show create table；

//DDL 表操作 创建
create table 表名（字段1，类型）[comment] //comment 注释；

//DDL 表操作 修改
添加字段：alter table 表名 add 字段名 类型 [注释] [约束]；
修改数据类型：alter table 表名 modlfy 字段名
修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 类型
删除字段：alter table 表名 drop 字段名
修改表名：alter table 表名 rename to 新表名;
//DDL 表操作 删除
删除表：drop table 表名
删除指定表，并重新创建该表：
truncate table 表名

DDL 语句是用来操作数据库表结构 表中字段
DML 完成数据的增删改

---

DML

//DML 添加数据

1、insert into 表 （ 字段1 字段2） values（ 值1 值2）
2、insert into 表 values（值1，值2）
3、批量添加数据 insert into 表 values（值），（值），（值）
注意：插入数据时，指定的字段顺序需要与值一一对应的
字符串和日期型数据应该包含在引号中
插入的数据大小，应该在地钻的规范范围内
//DML-修改数据
update 表 set 字段名1=值1 where 条件
//DML-删除数据
delete from 表 where 条件
delete from 表 //删除整张表，没有加条件就会删除整张表

---

DQL-语法

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

DQL-基本查询 -------
1.查询多个字段
select 字段1 字段2 from 表名
select * from 表
2.设置别名
select 字段 [ as 别名] from 表
3.去除重复记录
select distinct 字段列表 from 表

DQL-条件查询------

between…and …(在某个范围之内（最小、最大）)
in (在in 之后的列表值 多选一)
like 占位符（_匹配单个字符，%任意字符）
is null （是null 的意思）
and 或&& （并且）多个条件同时成立
or 或|| （或者）多个条件任意一个成立
not 或！ 非 不是

例题：查询姓名是两个字的字符
select * from 表 where name like ‘_ _’;
查询 身份证最后一位是x 的员工信息
select * from 表 where 身份证 like ‘%x’；

DQL -聚合函数
count max avg min sum
注意：null 值不参与所有聚合函数运算
语法：
select 聚合函数 from 表
DQL-分组查询------
select 字段 from 表 where group by 分组字段 （hving 分组后过滤条件）
注：先执行 where 后执行 having
分组之后，查询的字段一般为聚合函数和分组字段，查询其他的没有意义

例题：计算所有供应商，供应水果总数。
with rullup
select s_id,count(f_id) from fruits group by s_id with rollup

DQL-排序查询------
select 字段 from 表 order by 字段1 排序方式1 , 字段2 字段方式2
升序 asc （默认值） 降序 desc
注意：如果是多字段排序 当第一个字段值相同时，才会根据第二字段进行排序
SQL-分页查询------
select 字段 from 表 limit 起始索引，查询记录数
注意：
起始索引从0开始，起始索引 =（查询页码-1）每页显示记录
分页查询是数据库的方言，不同的数据库有不同的实现，mysql 中是limit
如果查询的是第一页数据，起始索引可以省略 直接简写为 limit 10
例题：
查询第一页员工数据 每页展示10条记录
select *from 表 limit 0,10; 或者 select *from 表 limit 10；第一页可以简写
查询第二页 员工数据 每页展示10条记录
select * from 表 limit 1，10； // limit 查询页数-1，查询记录数

DQL-执行顺序
编写顺序 执行顺序
select 1 select 4
from 2 from 1
where 3 where 2
group by 4 group by 3
having having
order by 5 order by 5
limit 6 limit 6

DCL

DCL-管理用户------
一、查询用户
use mysql：
select * from user；
二、创建用户
create user ‘用户名’@‘主机名’ identified by ‘密码’;

例题：heima，可以在任意主机访问数据库，密码123456
create user ‘heima’ ‘%’ identified by ‘123456’;

三、修改密码
alter user ‘用户名’@‘主机名’ identified with mysql_native_password by ‘密码’;

四、删除用户
drop user ‘用户名’@‘主机名’；

注意：主机名可以使用%通配
DCL-权限控制------
all，all privileges 所有权限
select 查询数据
insert 插入数据
update 修改数据
delete 删除数据
alter 修改表
drop 删除数据库/表/试图
create 创建数据库/表

1.查询权限
show grants for ‘用户名’‘主机名’
2.授予权限
grant 权限列表 on 数据名.表名 to ‘用户名’@‘主机名’
3.撤销权限
revoke 权限列表 on 数据名.表名 from ‘用户名’@‘主机名’
注意：多个权限之间，使用逗号分隔
授权时，数据名 和表名 可以使用* 进行通配 代表所有。

函数

函数：是指一段可以直接被另一端程序调用的程序或代码
字符串函数
数值函数
日期函数
流程函数

---

字符串函数------
concat 字符串拼接
lower(str) 全部转换为小写
upper(str) 全部转换为大写
lpad(str,n,pad) 右填充
rpad(str,n,pad) 左填充
trim (str) 去掉字符串头部和尾部的空格
substring 返回从字符串str 从start 位置起len 的长度位置（截取）

---

select concat (‘hello’,‘mysql’) //输出 hello mysql
select lower（‘HELLO’）//输出 hello
select upper（‘hello’）//输出 HELLO
select lpad（‘01’,5,‘-’）//输出 —01 // 01 是字符串 5是字符串长度。//第三个是拿什么填充 左填充
select rpad（‘01’,5,‘-’）//输出 01— // 01 是字符串 5是字符串长度。//第三个是拿什么填充 右填充
select trim(’ Hello mysql ')//hello mysql 中间空格还在 前面和后面空格没有了
select concat (‘hello mysql’,1,5) / /输出 hello 从第一个开始截取 第一个，然后截取5个

---

//案例 由于业务变更 ，企业员工 工号 统一为5位数 不足前面补0 比如一号员工应该为00001.
update 表 set 工号字段 = lpad(‘工号字段’，5，‘0’);

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

---

select ceil(1.1) //输出2
select floor(1.8) //输出1
select mod(7,4) //输出 3
select rand()；//输出随机数
select round(2.3456,2) //输入 2.35 保留2位小数，四舍五入

---

//案例 通过数据库的函数，生成一个六位数的随机验证码
select lpad(round(rand()*1000000,0),6,‘0’);

日期函数
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 //当前日期
select curtime //当前时间
select now //当前年月 日时分秒
select year(now()) //当前日期 2022
select month(now()) //当前月份 3
select day (now()) //当前日期 19号
select date_add（now(),interval 70 day）//往后推 70天
select datediff('2021-12-01,‘2021-10-01’); //相差多少天61天 /注意：求取的时间是第一个减去第二个

---

案例 查询所有员工的入职天数 并根据入职 天数 倒序排序
select name ，datediff(curdate(), entrydate) as ‘entrydaya’ from emp order by entrydays desc;
// ebtrtdate 是入职日期字段 // curdate() 当前日期 //order by 排序 desc 倒序//

流程控制函数
if(value,t,f) 如果value 为true 则返回t，否则返回f
ifnull(value1,value2) 如果value 不为空，返回value1 否则value2
case when[val1] then [res1]…else [default] end
如果value1为true 返回 res1 否则 返回default 默认值

case [expr] when [val1] then [res1]…else [default] end
如果expr的值等于val1 返回 res1 否则 返回default 默认值

---

select if(true,‘ok’,‘error’); // true 就是返回ok false 就返回 error
select ifnull(‘ok’,‘Default’)//不为空 返回 ok
//select ifnull(’ ‘,‘Default’) //不为空 返回 空格
//select ifnull(’ null’,‘Default’) //为 空，返回Default
–查询 emp 员工姓名和工作地址(北京/上海–>一线城市，其他—>二线城市)
select name，
(case address when ‘北京’ then ‘一线城市’ when ‘上海’ then ‘一线城市’
else ‘二线城市’ end ) as ‘工作地址’
from emp;

---

案例统计各班个学员的成绩 展示规则

=85 展示优秀
=60 展示及格
否组 展示不及格
select id name
(case when math >=85 then ‘优秀’ when math >-60 ‘及格’ else ‘不及格’ end) ‘数学’
(case when english >=85 then ‘优秀’ when englise >-60 ‘及格’ else ‘不及格’ end) ‘英语’
(case when chinese >=85 then ‘优秀’ when chinese >-60 ‘及格’ else ‘不及格’ end) ‘语文’
from score；

约束

约束：
概述
1.约束是作用于表中字段上的规则，用于限制存储在表中的数据
not null 非空约束
unique 唯一约束
primary key 主键约束
default 默认约束
check 检查约束
注：非空+ 唯一 =主键

foreign key 外键约束

外键约束 添加外键
create 表 字段 constraint 外键名称 foreign key 外键字段名 references 主表（主表列名）
alter table 表 add constraint 外键名称 foreign key 外键字段名 references 主表
删除外键
alter table 表 drop foreign key 外键名称：

外键的删除 更新行为
on action 当在父表中删除/更新对应记录时，首先检查记录是否有对应外键，如果有则不允许删除/更新
restrict 当在父表中删除/更新对应记录时，首先检查记录是否有对应外键，如果有则不允许删除/更新
cascade 当在夫表中删除/更新对应记录时，首先检查记录是否有对应外键，如果有，则删除/更新外键在子表中的记录
set null 当在夫表中删除/更新对应记录时，首先检查记录是否有对应外键，如果有则设置表中该外键值为null
set default 父表有更时，子表将外键列设置成一个默认的值
create 表 字段 constraint 外键名称 foreign key 外键字段名 references 主表（主表列名）
on update cascade on delete cascade;

多表查询

多表关系
多表查询概述
内连接
外连接
自连接
子查询
多表查询案例

---

多表关系
概述：一对多 多对多 一对一
多表查询：
笛卡尔积

多表查询分类
内连接：相当于查询a b 交际部分数据
外连接：左外 、右外
自连接
子查询

---

内连接 ： 交际的部分
隐士内连接：
select 字段列表 from 表1，表2 where
显示内连接：
select 字段列表 from 表1 inner join 表2 连接条件

---

外连接
左外连接
select 字段列表 from 表1 left outer join 表2 条件
右外连接
select 字段列表 from 表1 right outer join 表2 条件

---

自连接
select 字段列表 from 表a 别名A 表A 别名B on 条件
自连接查询可以是内连接查询、也可以是外连接查询

---

联合查询
union ，union all
select 字段列表 from 表1
union [all]
select 字段列表 from 表2
多张表的列数保持一致，字段类型也保持一致
union all 会将全部数据直接合并在一起，union 会对合并之后的数据去重

---

子查询
sql语句中嵌套 select 语句 称为嵌套语句，又称为子查询
select * from t1 where column1 =（select column1 from t2）
子查询 外部 可以说/insert/update/delete /select 任何一个
根据子查询结构不同分为：
标量子查询
列子查询
行子查询
表子查询
根据子查询位置：分为 where 之后、from之后、select之后

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

列子查询：
子查询返回的结果是一列（可以是多列），像这种子查询称为 列子查询
常用操作符：in ，not in ，any ，some ，all

行子查询：
子查询返回的结果是一行（可以是多列），像这种子查询称为 行子查询
常用的操作符： =，<>, in , not in

表子查询：
子查询返回的结果是多行多列，这种子查询称为表子查询
常用操作符：in

exists
exists 存在
not exists 不存在

虚表和exists 存不存在

INSERT INTO user (mobile) SELECT ‘18515697037’ FROM DUAL
WHERE NOT EXISTS
(SELECT mobile FROM user WHERE mobile = ‘18515697037’);

事务

事务：
查看/设置事务的提交方式
select @@autocommit； 自动提交
set @@autocommit=(); 手动提交
提交事务：
commit
回滚事务：
rollback；

开启事务：
start transaction 或 begin；

---

事务的四大特性：（acid）
原子性：事务是不可分割的最小单元，要么成功，要不全部失败
一致性：事务完成时，必须使所有的数据都保持一致
隔离性：数据库提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性：事务一段提交或回滚，他对数据库中的数据的改变是永久的

---

并发事务问题：
脏读：一个事务读到另一个事务还没有提交的数据
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可复读
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这
行数据已经存在，好像出现了幻影。

---

事务的隔离级别
脏读 不可重复 幻读
read uncommitted √ √ √
read committed x √ √
repeatable read(默认) x x √
serializable x x x

查看事务隔离级别
select @@transaction_isolation
设置事务隔离级别
set [session /global] transaction isolation level
{read uncommitted/ read committed/reoeatabke read/seruakuzabke};