数据库MySQL
一、 数据库相关概念
1. MySQL概述
(1)数据库相关概念
通过SQL可以操作数据库管理系统,通过数据库管理系统进而操作数据库以及数据库中的数据。
(2)MySQL安装及启动
- MySQL的启动与停止
启动MySQL:
net start mysql80
停止MySQL:
net stop mysql80
- 客户端连接
方式一:MySQL提供的客户端命令行工具。输入密码即可。
方式二:系统自带的命令行工具执行指令。但需要配置PATH环境变量。
(3)数据模型
- 关系型数据库(RDBMS)
概念:建立在关系模型基础上,由多张相互连接的二维表组成的数据库。
特点:使用表存储数据,格式统一,便于维护;使用SQL语言操作,标准统一,使用方便。
二、 SQL基础
1. SQL通用语法
- SQL语句可以单行或多行书写,以分号结尾。
- SQL语句可以使用空格/缩进来增强语句的可读性。
- MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。
- 注释:单行注释: —注释内容或#注释内容;多行注释/* 注释内容 */
2. SQL分类
3. DDL语句
(1) 数据库操作
- 查询
查询所有数据库 SHOW DATABASE;
查询当前数据库 SELECT DATABASE();
- 创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
- 删除
DROP DATABASE [IF EXISTS] 数据库名;
- 使用
USE 数据库名;
(2)表操作
- 查询
查询当前数据库所有表 SHOW TABLES;
查询表结构 DESC 表名;
查询指定表的建表语句 SHOW CREATE TABLE 表名;
- 创建
CREATLE TABLE 表名(
字段1 字段1类型[COMMENT 字段1注释],
字段2 字段2类型[COMMENT 字段2注释],
字段3 字段3类型[COMMENT 字段3注释],
...
字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];
- 修改
1. 添加字段 ALTER TABLE 表名 ADD 字段名 类型(长度)[COMMENT 注释][约束];
2. 修改字段名和字段类型 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 注释][约束];
3. 删除字段 ALTER TABLE 表名 DROP 字段名;
4. 修改表名 ALTER TABLE 表名 RENAME TO 新表名;
5. 删除表 DROP TABLE [IF EXISTS] 表名;
6. 删除指定表,并重新创建该表 TRUNCATE TABLE 表名;
4. DML语句
(1) 添加数据
1. 给指定的字段添加数据 INSERT INTO 表名(字段1, 字段2, ...) VALUES(值1, 值2, ...);
2. 给全部字段添加数据 INSERT INTO 表名 VALUES(值1, 值2, ...);
3. 批量添加数据 INSERT INTO 表名(字段1, 字段2, ...) VALUES(值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
INSERT INTO 表名 VALUES(值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
(2) 修改数据
UPDATE 表名 SET 字段名1 = 值1, 字段名 = 值2, ...[WHERE 条件];
(3) 删除数据
DELETE FROM 表名[ WHERE 条件];
3. DQL
(1) 基本查询
- 查询多个字段
SELECT 字段1, 字段2, 字段3, ... FROM 表名;
SELECT * FROM 表名;
- 设置别名
SELECT 字段1[AS 别名1], 字段2[AS 别名2]... FROM 表名;
- 去除重复记录
SELECT DISTINCT 段列表 FROM 表名;
(2) 条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表;
(3) 聚合函数
- 聚合函数
count:统计数量;max:最大值;min:最小值;avg:平均值;sum:求和。
SELECT 聚合函数(字段列表) FROM 表名;
(4) 分组查询
SELECT 字段列表 FROM 表名[WHERE 条件] GROUP BY 分组字段名[HAVING 分组后过滤条件];
-
where与having的区别
执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
判断条件不同:where不能对聚合函数进行判断,而having可以。 -
执行顺序
where > 聚合函数 > having。
(5)排序查询
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
- 排序方式:ASC(升序);DESC(降序)。
(6) 分页查询
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
- 起始索引从0开始,起始索引 = (查询页码 - 1) * 每页显示记录数。
4. DCL
(1)用户管理
- 查询用户
USE mysql;
SELECT * FROM user;
- 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; // 所有主机使用 % 代替
- 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY ' 新密码';
- 删除用户
DROP USER '用户名'@'主机名';
(2)权限控制
- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
三、 函数
1. 字符串函数
- 采用的字符串函数
2. 数值函数 - 常用的数值函数
3. 日期函数 - 常见的日期函数
4. 流程函数 - 常见的流程函数
四、 约束
1. 概述
-
概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据。
-
目的:保证数据库中数据的正确、有效性和完整性。
-
分类:
2. 外键约束
(1)概念:外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
(2)语法- 添加外键
CREATE TABLE 表名( 字段名 数据类型, ... [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCE 主表(主表列表) ); ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCE 主表(主表列表);
- 删除/更新行为
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCE 主表(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
五、 多表查询
1. 多表关系
- 一对一:用户与用户详情的关系。
在任意一方加入外键,关联另一方的主键,并设置外键为唯一的。
- 一对多:一个部门对应对个员工,一个员工对应一个部门。
实现:在多的一方建立外键,指向一的一方的主键。
- 多对多:一个学生可以选修多门课程,一门课程也可以供多个学生选择。
建立第三张中间表,中间表至少包含两个外键,分别关联两方主键。
2. 多表查询
(1) 连接查询
- 内连接
相当于查询A、B交集部分数据。
- 隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件...;
- 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
-
外连接
左外连接:查询左表所有数据,以及两张表交集部分的数据。SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
右外连接:查询右表所有数据,以及两张表交集部分的数据。
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;
-
自连接
当前表与自身的连接查询,自连接必须使用表别名。SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
-
联合查询
把多次查询结果合并起来,形成一个新的查询结果集。SELECT 字段列表 FROM 表A... UNION[ALL] SELECT 字段列表 FROM 表B...;
- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
- UNION ALL 会将全部的数据直接合并在一起,UNION会对合并之后的数据去重。
(2)子查询
SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
-
标量子查询(子查询结果为单个值)
常用的操作符: = <> > >= < <= -
列子查询(子查询结果为一列)
常用的操作符: IN、 NOT IN、 ANY、 SOME、 ALL
-
行子查询(子查询结果为一行)
常用操作符:=、<>、IN、NOT IN -
表子查询(子查询结果为多行多列)
常用操作符:IN
六、 事务
1. 事务简介
事务是一组操作的集合,它是不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
2. 事务操作
(1) 查看/设置事务提交方式
SELECT @@autocommit; // 查看事务提交方式,为1是自动提交,为0是手动提交
SET @@autocommit = 0;
(2) 提交事务
COMMIT;
(3)回滚事务
ROLLBACK;
(4)开启事务
START TRANSACTION 或 BEGIN;
事务的控制有两种方式:
- 第一种,关闭掉事务的自动提交,通过COMMIT去提交事务,通过ROLLBACK去回滚事务。
- 第二种,通过指令START TRANSACTION或BEGIN显示地来开启事务,事务执行完成操作之后,如果事务执行成功,执行COMMIT指令;若失败,执行ROLLBACK。
3. 事务的四大特性
(1)原子性
事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
(2)一致性
事务完成时,必须使所有的数据都保持一致状态。
(3) 隔离性
数据库系统提供隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
(4)持久性
事务一旦提交或回滚,它对数据库中的数据改变就是永久的。
4. 并发事务问题
5. 事务隔离级别
// 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
// 设置事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
注意:事务隔离级别越高,数据越安全,但是性能越低。