1. Mysql简介
1.1 简介
存储数据的容器:数组,集合,容器,数组和集合都是写到内存中的。文件是存储到硬盘中的。当文件很大时,想要查询相关特性的文件就需要分类,这时候我们引入了数据库这个概念。
DB:数据库(database)存储数据的仓库。它保存了一系列的有组织的数据。
DBMS:数据库管理系统(Database Management System)数据库是通过DBMS创建的和操作的容器。数据库软件,数据库管理系统。
SQL:结构化查询语言(Structure Query Language)专门用来与数据库通信的语言。
1.2 数据模型
关系型数据库(RDBMS)
建立在关系模型基础上,由多张相互连接的二维表组成的数据库。
而所谓二维表,指的是由行和列组成的表,如下图(就类似于Excel表格数据,有表头、有列、有行,
还可以通过一列关联另外一个表格中的某一列数据)。我们之前提到的MySQL、Oracle、DB2、
SQLServer这些都是属于关系型数据库,里面都是基于二维表存储数据的。简单说,基于二维表存储
数据的数据库就成为关系型数据库,不是基于二维表存储数据的数据库,就是非关系型数据库。
特点
- 使用表存储数据,格式统一,便于维护。
- 使用SQL语言操作,标准统一,使用方便。
1.3 SQL
全称 Structured Query Language,结构化查询语言。操作关系型数据库的编程语言,定义了 一套操作关系型数据库统一标准 。
1.4 SQL分类
SQL语句,根据其功能,主要分为四类:DDL、DML、DQL、DCL。
- DDL: 数据定义语言,用来定义数据库对象(数据库,表, 字段)
- DML: 数据操作语言,用来对数据库表中的数据进行增删改
- DQL:数据查询语言,用来查询数据库中表的记录
- DCL: 数据控制语言,用来创建数据库用户、控制数据库的 访问权限
1.5 Mysql数据类型
1.5.1 整数类型(Integer Types):
- TINYINT: 1 字节,范围为 -128 到 127 或 0 到 255 无符号。
- SMALLINT: 2 字节,范围为 -32,768 到 32,767 或 0 到 65,535 无符号。
- MEDIUMINT: 3 字节,范围为 -8,388,608 到 8,388,607 或 0 到 16,777,215 无符号。
- INT: 4 字节,范围为 -2,147,483,648 到 2,147,483,647 或 0 到 4,294,967,295 无符号。
- BIGINT: 8 字节,范围为 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807 或 0 到 18,446,744,073,709,551,615 无符号。
1.5.2 浮点数类型(Floating-Point Types):
- FLOAT: 单精度浮点数,4 字节。
- DOUBLE: 双精度浮点数,8 字节。
1.5.3 定点数类型(Fixed-Point Types):
- DECIMAL: 定点数,用于存储精确小数,例如货币。需指定精度和小数位数。
1.5.4 日期与时间类型(Date and Time Types):
- DATE: 日期,格式为 ‘YYYY-MM-DD’。
- TIME: 时间,格式为 ‘HH:MM’。
- DATETIME: 日期时间,组合了日期和时间,格式为 ‘YYYY-MM-DD HH:MM’。
- TIMESTAMP: 时间戳,用于记录插入或更新行的日期和时间。存储时区无关的 UTC 时间。
- YEAR: 年份,格式为 ‘YYYY’。
1.5.5 字符串类型(String Types):
- CHAR: 固定长度字符串,最多 255 字符。
- VARCHAR: 可变长度字符串,最多 65,535 字符。
- BINARY: 固定长度的二进制字符串。
- VARBINARY: 可变长度的二进制字符串。
- TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB: 存储二进制数据,分别最多 255 字节、16 MB、64 MB 和 4 GB。
2. SQL语句
2.1 DLL
Data Definition Language,数据定义语言,用来定义数据库对象(数据库,表,字段)
2.1.1 DLL 数据库操作
查询所有数据库
show databases ;
查询当前数据库
select database() ;
创建数据库
create database [ if not exists ] 数据库名 [ default charset 字符集 (utf8 MB4)] [ collate 排序
规则 ] ;
删除数据库
drop database [ if exists ] 数据库名 ;
切换数据库
use 数据库名 ;
2.1.1 DLL表操作
2.1.1.1 表操作查询
查询当前数据库所有表
show tables;
查看指定表结构
desc 表名 ;
查询指定表的建表语句
show create table 表名 ;
创建表结构
CREATE TABLE 表名( 字段1 字段1类型 [ COMMENT 字段1注释 ], 字段2 字段2类型 [COMMENT 字段2注释 ], 字段3 字段3类型 [COMMENT 字段3注释 ], ...... 字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;
2.1.1.2 表操作-修改
添加字段
ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
删除字段
ALTER TABLE 表名 DROP 字段名;
修改表名
ALTER TABLE 表名 RENAME TO 新表名;
2.1.1.3 表操作-删除
删除表
DROP TABLE [ IF EXISTS ] 表名;
删除指定表, 并重新创建表
TRUNCATE TABLE 表名;
2.2 DML
DML英文全称是Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进 行增、删、改操作。
添加数据(INSERT)
修改数据(UPDATE)
删除数据(DELETE)
2.2.1 添加数据
添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
给全部字段添加数据
NSERT INTO 表名 VALUES (值1, 值2, ...);
批量添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值
1, 值2, ...) ;
2.2.2 修改数据
修改数据的具体语法为:
UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;
2.2.3 删除数据
删除数据
DELETE FROM 表名 [ WHERE 条件 ] ;
删除所有
delete from xxx;
2.3DQL
DQL英文全称是Data Query Language(数据查询语言),数据查询语言,用来查询数据库中表的记录。
查询关键字: SELECT
在一个正常的业务系统中,查询操作的频次是要远高于增删改的,当我们去访问企业官网、电商网站, 在这些网站中我们所看到的数据,实际都是需要从数据库中查询并展示的。而且在查询的过程中,可能还会涉及到条件、排序、分页等操作。
2.3.1 DQL基础语法
SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT分页参数
2.3.2 基本查询(不带任何条件)
查询多个字段
SELECT 字段1, 字段2, 字段3 … FROM 表名 ;
字段设置别名
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ]
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
2.3.3 条件查询(WHERE)
SELECT 字段列表 FROM 表名 WHERE 条件列表 ;
- AND:同时满足多个条件
- OR:满足任意一个条件
- NOT:排除符合条件的行。
- 用比较运算符:=、<>、!=、>、<、>=、<= 等
- 使用通配符进行模糊查询:LIKE:匹配指定模式的值
- 使用 IN 进行多值匹配:
- 使用 BETWEEN … AND … 进行范围查询:
- 使用 IS NULL 或 IS NOT NULL 检查空值:
- 组合条件查询:
2.3.4 聚合函数(count、max、min、avg、sum)
将一列数据作为一个整体,进行纵向计算 。
- COUNT():计算行数或非NULL值的数量。
- SUM():计算指定列的总和。
- AVG():计算指定列的平均值。
- MAX():找出指定列的最大值。
- MIN():找出指定列的最小值。
注意事项
聚合函数通常会忽略 NULL 值,除非特别指定要包括 NULL 值。
在 SELECT 语句中,聚合函数通常会在列名或者 * 之前使用。
如果没有 GROUP BY 子句,聚合函数将会对整个结果集进行计算;如果有 GROUP BY 子句,则按照分组计算。
2.3.5 分组查询(group by)
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组 后过滤条件 ];
where与having区别 执行时机不同:
where是分组之前进行过滤,不满足where条件,不参与分组;
而having是分组 之后对结果进行过滤。
判断条件不同:where不能对聚合函数进行判断,而having可以。
根据性别分组 , 统计男性员工 和 女性员工的数量
select gender, count(*) from emp group by gender ;
根据性别分组 , 统计男性员工 和 女性员工的平均年龄
select gender, avg(age) from emp group by gender ;
查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from emp where age < 45 group by
workaddress having address_count >= 3;
统计各个工作地址上班的男性及女性员工的数量
select workaddress, gender, count(*) '数量' from emp group by gender , workaddress;
2.3.6 排序查询(order by)
排序在日常开发中是非常常见的一个操作,有升序排序,也有降序排序。
排序方式
ASC : 升序(默认值)
DESC: 降序
据年龄对公司的员工进行升序排序
select * from emp order by age asc;
2.3.7 分页查询(limit)
分页操作在业务系统开发时,也是非常常见的一个功能,我们在网站中看到的各种各样的分页条,后台 都需要借助于数据库的分页操作。
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
- 起始索引从0开始,起始索引 = (查询页码 - 1)* 每页显示记录数。
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
- 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。
查询第1页员工数据, 每页展示10条记录
elect * from emp limit 0,10;
查询第2页员工数据, 每页展示10条记录 --------> (页码-1)*页展示记录数
select * from emp limit 10,10;
2.3.8 执行顺序
SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT分页参数
执行顺序
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT
2.4 DCL 数据控制语言
DCL英文全称是Data Control Language(数据控制语言),用来管理数据库用户、控制数据库的访 问权限。
2.4.1 用户管理
管理用户
select * from mysql.user;
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;
删除用户
DROP USER '用户名'@'主机名' ;
2.4.1 权限控制
MySQL中定义了很多种权限,但是常用的就以下几种
ALL, ALL PRIVILEGES
SELECT
INSERT
UPDATE
DELETE
ALTER
DROP
CREATE
查询 'heima'@'%' 用户的权限
show grants for 'heima'@'%';
B. 授予 'heima'@'%' 用户itcast数据库所有表的所有操作权限
grant all on itcast.* to 'heima'@'%';
C. 撤销 'heima'@'%' 用户的itcast数据库的所有权限 1
revoke all on itcast.* from 'heima'@'%';
2.4.2 函数
2.4.2.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,start,len) 返回从字符串str从start位置起的len个长度的字符串
由于业务需求变更,企业员工的工号,统一为5位数,目前不足5位数的全部在前面补0。比如: 1号员 工的工号应该为00001。
update emp set workno = lpad(workno, 5, ‘0’);
2.4.2.2 数值函数
- CEIL(x) 向上取整
- FLOOR(x) 向下取整
- MOD(x,y)返回x/y的模
- RAND() 返回0~1内的随机数
- ROUND(x,y) 求参数x的四舍五入的值,保留y位小数
通过数据库的函数,生成一个六位数的随机验证码。
获取随机数可以通过rand()函数,但是获取出来的随机数是在0-1之间的,所以可以在其基础 上乘以1000000,然后舍弃小数部分,如果长度不足6位,补0
select lpad(round(rand()*1000000 , 0), 6, '0');
2.4.2.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之间的天数
查询所有员工的入职天数,并根据入职天数倒序排序。 思路: 入职天数,就是通过当前日期 - 入职日期,所以需要使用datediff函数来完成。
select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by
entrydays desc;
2.4.2.4 流程函数
流程函数也是很常用的一类函数,可以在SQL语句中实现条件筛选,从而提高语句的效率。
- IF(value , t , f) 功能如果value为true,则返回t,否则返回 f
- IFNULL(value1 , value2) 如果value1不为空,返回value1,否则 返回value2
- CASE WHEN [ val1 ] THEN [res1] … ELSE [ default ] END 如果val1为true,返回res1,… 否 则返回default默认值
- CASE [ expr ] WHEN [ val1 ] THEN [res1] … ELSE [ default ] END如果expr的值等于val1,返回res1,… 否则返回default默认值
2.4.3 约束
约束是作用于表中字段上的规则,用于限制存储在表中的数据。 目的:保证数据库中数据的正确、有效性和完整性。
非空约束 限制该字段的数据不能为null NOT NULL 唯一约束 保证该字段的所有数据都是唯一 UNIQUE
主键约束 不重复的 主键是一行数据的唯一标识 PRIMARY KEY 默认约束 要求非空且唯一 关键字 外键约束
保存数据时,如果未指定该字段的值,则采用默认值 DEFAULT 保证字段值满足某一个条件
3 多表查询
我们之前在讲解SQL语句的时候,讲解了DQL语句,也就是数据查询语句,但是之前讲解的查询都是单 表查询,而本章节我们要学习的则是多表查询操作,主要从以下几个方面进行讲解。
3.1 多表关系
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结 构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种: 一对多(多对一) 多对多 一对一
3.2 多表查询
多表查询就是指从多张表中查询数据。
原来查询单表数据,执行的SQL形式为:select * from emp; 那么我们要执行多表查询,就只需要使用逗号分隔多张表即可,如: select * from emp , dept
; 具体的执行结果如下:
3.3 分类
连接查询
3.3.1 内连接:相当于查询A、B交集部分数据
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
3.3.2 左外连接:
查询左表所有数据,以及两张表交集部分数据
左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
3.3.3 右外连接:
查询右表所有数据,以及两张表交集部分数据
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 … ;
3.3.4 自连接:
当前表与自身的连接查询,自连接必须使用表别名
自连接查询,顾名思义,就是自己连接自己,也就是把一张表连接查询多次。我们先来学习一下自连接 的查询语法:
查询员工 及其 所属领导的名字 表结构: emp
select a.name , b.name from emp a , emp b where a.managerid = b.id;
3.3.5 联合查询
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来
select * from emp where salary < 5000
union all
select * from emp where age > 50;
3.3.6 子查询
SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
1 SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。
标量子查询(子查询结果为单个值)
列子查询(子查询结果为一列)
行子查询(子查询结果为一行)
表子查询(子查询结果为多行多列)
4. 事务
4.1 事务简介
事务 是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系 统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
提交事务
COMMIT;
回滚事务
ROLLBACK;
4.2 事务四大特性
- 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
- 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
- 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立 环境下运行。
- 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
4.3 并发事务问题
赃读:一个事务读到另外一个事务还没有提交的数据。
不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。
幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据 已经存在,好像出现了 “幻影”。
4.4 事务隔离级别
- 读未提交 出现脏读,不可重复读,幻读
- 读已提交 出现 不可重复度,幻读
- 可重复读 幻读
- 序列化