数据库

一、数据库的基本概念

数据库的英语名称：DataBase 简称：DB
什么是数据库
- 用于存储和管理数据的仓库。
数据库的特点：
1. 持久化存储数据。其实数据库就是一个文件系统
2. 方便存储和管理数据
3. 使用了统一的方式操作数据库 – SQL
常见数据库软件
1. Oracle
2. MySQL
3. Microsoft SQL Server
4. DB2
5. Redis
  
  …

二、安装MySQL数据库

。。。。。。

三、卸载MySQL数据库

四、配置

MySQL服务启动
- 手动。
- cmd --> net start/stop mysql（需要管理员权限）
登录
- mysql -h ip -u root -p
- 输入密码
退出
- exit
- quit
MySQL目录结构：
1. MySQL安装目录
  - 配置文件 my.ini
2. MySQL数据目录
  - 几个概念
    - 数据库：文件夹
    - 表：文件
    - 数据：存储的内容

五、SQL

什么是SQL？
- Structured Query Language：结构化查询语言
- 其实就是定义了操作所有关系型数据库的规则。
- 每一种数据库的操作方式存在不一样的地方，称为“方言”。
SQL通用语法
1. SQL语句可以单行或多行书写，以分号结尾。
2. 可使用空格和缩进来增强语句的可读性。
3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4. 三种注释
  - 单行注释： – 注释内容或 # 注释内容（MySQL特有）
  - 多行注释：/* 注释 */
5. SQL分类
  1. DDL（Data Definition Language）数据定义语言
    - 用于定义数据库对象：如数据库表，列等。关键字：CREATE ，DROP， ALTER，等。
  2. DML（Data Manipulation Language）数据操作语言
    - 用于对数据库中表的数据进行增删改。关键字：INSERT， DELETE， UPDATE，等。
  3. DQL（Data Query Language）数据查询语言
    - 用来查询数据库中表的记录（数据）。关键字：SELECT， WHERE，等。
  4. DCL（Data Control Language）数据控制语言（了解）
    - 用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT，REVOKE，等。

DDL：操作数据库、表

操作数据库：CRUD
1. C：CREATE：创建
  - 创建数据库
```
CERATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET 字符集;
```
2. R：Retrieve：查询
  - 查询所有数据库的名称：
```
SHOW DATABASES;
```
  - 查询创建某数据库的语句：
```
SHOW CREATE DATABASE 数据库名;
```
3. U：Update：修改
  - 修改数据库的字符集
```
ALTER DATABASE 数据库名 CHARACTER SET 字符集;
```
4. D：Delete：删除
  - 删除数据库
```
DROP DATABASE IF EXISTS 数据库名;
```
5. 使用数据库
  - 查询当前正在使用的数据库
```
SELECT DATABASE();
```
  - 使用数据库
```
USE 数据库名;
```
操作表:CRUD
1. C
  - 语法：
```
CREATE TABLE 表名(
	列名1 数据类型1,
    列名2 数据类型2,
    ...
    列名n 数据类型n
);
```
    - 注意：最后一行不用逗号
    - 常用数据类型：
      - tinyint 很小的整数
      - int 普通大小的整数
      - float 单精度浮点数
      - double 双精度浮点数（m,n）最多m位，小数后n位
      - date 时间YYYY-MM-DD HH:MM:SS
      - datetime 日期，yyyy-MM-dd HH:ss:DD
      - timestamp 时间戳类型，如果不给这个字段赋值或null，则默认使用系统当前时间
      - varchar(M) 字符串，M为0-65535之间的整数
  - 复制一张表
```
CREATE TABLE 创建的表名 LIKE 被复制的表名;
```
2. R
  - 查询某个数据库中所有的表名称
```
SHOW TABLES;
```
  - 查询表结构
```
DESC 表名;
```
3. U
  1. 修改表名
```
ALTER TABLE 表名 RENAME TO 新的表名;
```
  2. 修改表的字符集
```
ALTER TABLE 表名 CHARCTER SET 字符集名;
```
  3. 添加一列
```
ALTER TABLE 表名 ADD 列名 数据类型;
```
  4. 修改列名称类型
```
ALTER TABLE 表名 CHANGE 列名 新列名 新的数据类型;
ALTER TABLE 表名 MODIFY 列名 新的数据类型;
```
  5. 删除列
4. D
  - 删除表
```
DROP TABLE 表名 IF EXISTS;
```

DML：增删改查表中的数据

增加数据
- 语法：
```
INSERT INTO 表名(
	列名1,列名2,...,列名n
) VALUES(
	值1,值2,...,值n
);
```
- 注意：
  - 列名和值要一一对应。
  - 如果表名后不定义列名，则默认给所有列添加值。
  - 除了数据类型，其他数据类型都需要引号引起来（单双都可）。
  - 日期中数字以-区分。
删除数据
- 语法：
```
DELETE FROM 表名 [WHERE 条件];

TRUNCATE TABLE 表名;   删除表，同时创建一个一模一样的空表。
```
- 注意：
  - 如果不加条件，则会将所有记录删除！
  - 如果要删除所有的记录，建议使用第二条命令，效率更高。
修改数据
- 语法
```
UPDATE 表名 SET 列名1=值1, 列名2=值2, 列名3=值3, ... [WHERE 条件];
```
- 注意：
  - 如果不加任何条件，将会修改表中的所有记录！

DQL：查询表中的记录（最重要）

语法

SELECT 
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段
HAVING
	分组后条件
ORDER BY
	排序
LIMIT
	分页限定

基础查询

多个字段的查询
```
SELECT
	name,age
FROM
	student;
```
去除重复
```
SELECT DISTINCT address FROM student;
```

计算列

SELECT 
	name,math,english,
	math + english AS 分数和
FROM student;
-- 如果有null参与的计算，结果都为null
-- 可以使用函数：IFNULL(原先的值，之后的值)

起别名

SELECT math AS 数学 FROM student;
SELECT math  数学 FROM student;
-- 一个或多个空格或者as关键字都可以起别名，一般使用as关键字

条件查询

WHERE子句后面跟条件

运算符

> 、 < 、<= 、 >= 、 = 、 <>

BETWEEN...AND
IN(集合)
LIKE '张%'  -- 模糊查询
IS NULL

-- 查询年龄在20-30岁之间的
SELECT * FROM student WHERE age BETWEEN 20 AND 30;

-- 查询年龄在特定集合内的人
SELECT * FROM student WHERE age IN (22, 18, 25);

占位符
- 在模糊查询时，可以使用占位符
  - “_”：单个任意字符
  - “%”：多个任意字符
```
SELECT * FROM student WHERE name LIKE '马%';
```

DQL进阶

内容简介：

查询：
1. 排序查询
  - 语法：ORDER BY 子句
```
ORDER BY 排序字段1 排序方式1 排序字段2 排序方式2...;
```
  - 排序方式：
    - 升序：ASC：默认
    - 降序：DESC
    - 按照第一个排序，如果相同则按照第二个排序（主次排序）
2. 聚合函数：将一列数据作为一个整体，进行纵向计算。
  1. COUNT()：计算个数
  2. MAX()：计算最大值
  3. MIN()：计算最小值
  4. SUM()：计算求和
  5. AVG()：计算平均值
  - 注意：聚合函数的计算会排除null值。
```
-- 不排除null
-- 解决方案：
SELECT COUNT(IFNULL(english,0)) FROM student;
-- 或者选择非空列进行计算，一般选择主键。
-- 使用COUNT(*) 只要有记录，就不为null。
```
3. 分组查询：统计一个具有相同特征的数据（作为整体）
  1. 语法：GROUP BY 分组字段;
```
-- 按照性别分组，分别查询男女同学的平均分。
SELECT sex , AVG(math) FROM student GROUP BY sex;
-- 统计人数
SELECT sex , COUNT(*) FROM student GROUP BY sex;
-- 限定分组条件，排除低分。
SELECT sex, AVG(math) FROM student WHERE math >= 70 GROUP BY sex;
-- 分组之后人数要大于两个人，否则不显示。
SELECT sex, AVG(math) FROM student WHERE math >= 70 GROUP BY sex HAVING COUNT(id) > 2;
-- 还可以起别名，在HAVING中使用别名。
SELECT sex, AVG(math), COUNT(id) AS 人数 FROM student WHERE math >= 70 GROUP BY sex HAVING 人数 > 2;
```
  2. 注意：
    1. 因为作为整体，所以不应该出现个人信息，而是共性的内容。
    2. 要么使用分组字段，要么使用聚合函数。
    3. WHERE和HAVING的不同点：
      1. WHERE在分组前进行限定，如果不满足条件，则不参与分组。
      2. HAVING在分组后进行限定，如果不满足条件，则不被查询出来。
      3. WHERE后不可以跟聚合函数，HAVING可以进行聚合判断。
4. 分页查询
  1. 语法：LIMIT 开始索引，每页查询的条数;
```
-- 每一页显示三条记录，查询第一页
SELECT * FROM student LIMIT 0,3;
-- 查询第二页记录（从第3条记录开始）
SELECT * FROM student LIMIT 3,3;
```
  2. 注意：
    1. 开始索引不是页码，而是第几条记录：开始索引=（当前的页码 - 1）* 每页条数
    2. LIMIT关键字是方言，只能在MySQL中使用，其他数据库有其他特有的方式。

约束

概念：对表中的数据进行限定，从而保证数据的正确性、有效性和完整性。

分类：

主键约束：PRIMARY KEY
1. 注意：
  1. 含义：非空且唯一
  2. 一张表只能有一个字段为主键
  3. 主键就是一张表中记录的唯一表示。
2. 在创建表时，添加主键约束
```
CREATE TABLE stu(
	id INT PRIMARY KEY,
    name VARCHAR(20)
);
```
3. 删除主键
```
-- 不能使用MODIFY
ALTER TABLE stu DROP PRIMARY KEY;
```
4. 创建完表之后添加主键
```
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
-- 在添加主键前不能有重复字段。
```
5. 自动增长*
  - 概念：如果每一列是数值类型，使用AUTO_INCREMENT可以完成值的自动增长
```
CREATE TABLE stu(
	id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);
```
  - 删除自动增长
```
ALTER TABLE stu MODIFY id INT;
-- 无法删除主键，但是可以删除自动增长。
```
  - 添加自动增长
```
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
```
  - 注意：
    - 自动增长不一定要搭配主键，只是这样更加常见。

非空约束：NOT NULL

在创建表时添加约束

CREATE TABLE stu(
	id INT,
    name VARCHAR(20) NOT NULL
);

删除非空约束

ALTER TABLE stu MODIFY name VARCHAR(20); -- 不添加约束

表创建完后添加约束

ALTER TABLE stu MODIFY name VARCHAR(20) NOT NULL;

唯一约束：UNIQUE

创建表时添加约束

CREATE TABLE stu(
	id INT UNIQUE,
    name VARCHAR(20)
);

删除约束

ALTER TABLE stu DROP INDEX id; -- 这个语法比较特殊

表创建完后添加约束

ALTER TABLE stu MODIFY id INT UNIQUE;
-- 在添加约束前必须保证现有记录不重复！

注意：
- 唯一约束可以有null，但是只能有一条记录为null

外键约束：FOREIGN KEY

在创建表时，可以添加外键

语法

CREATE TABLE 表名(
	....
    外键列,
    CONSTRAINT 外键名称 FOREIGN KEY 外键列名称 REFERENCES 主表名称(主表列名称)
);

CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    age INT,
    dep_id INT,
    CONSTRAINT emp_dept FOREIGN KEY dep_id REFERENCES department(id)
);

让表和表产生关系，从而保证数据的正确性。

删除外键

ALTER TABLE employee DROP FOREIGN KEY emp_dept;

创建表之后，添加外键

ALTER TABLE employee ADD CONSTRAINT 外键名称 FOREIGN KEY 外键列名称 REFERENCES 主表名称(主表列名称);

创建外键时要保证数据无误！

级联操作
- 当我们需要在外键中修改主键时，会出现很多麻烦，我们需要将外键处改为null，修改外键表后重新添加值。（外键不能为外键指定的表中不存在的值！）
- 假设我们需要级联操作，则需要在添加外键的时候设置级联更新。( ON UPDATE CASCADE )
```
ALTER TABLE employee ADD CONSTRAINT 外键名称 FOREIGN KEY 外键列名称 REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE;
```
- 级联更新+级联删除
```
ALTER TABLE employee ADD CONSTRAINT 外键名称 FOREIGN KEY 外键列名称 REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;
```
- 分类：
  1. 级联更新：ON UPDATE CASCADE
  2. 级联删除：ON DELETE CASCADE
- 注意：实际开发中对级联的使用非常谨慎，级联会影响安全性和性能！

多表之间的关系
1. 一对一：
  - 人和身份证：一个人只有一个身份证，一个身份证只能对应一个人。
2. 一对多（多对一）：
  - 部门和员工：一个部门有多个员工，一个员工只能队友一个部门。
3. 多对多：
  - 学生和课程：一个学生可以选择多个课程，一个课程也可以被多个学生选择。
4. 实现：
  1. 一对多：
    - 在n的表中添加一个外键，指向1的主键。
  2. 多对多：
    - 建立一个中间表，中间表至少包含两个外键字段，分别指向两张表的主键。
  3. 一对一：
    - 在任意一方添加唯一外键指向另一方的主键。
5. 案例
范式
- 概念：
  - 在设计数据库时，需要遵循的一些规范。
  - 设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
  - 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
- 分类（一般学习前三个范式）：
  1. 第一范式（1NF）：每一列都是不可分割的原子数据项
    - 在这里需要分割系，分为系名列和系主任列
    - 存在的问题：
      1. 存在非常严重的数据冗余（姓名，学号等）
      2. 数据添加存在问题，添加新开设的系时，数据不合法。
      3. 删除数据存在问题，如果一个同学退选，删除数据会将系的数据一起删除。
  2. 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
    - 几个概念：
      1. 函数依赖：A–>B，如果通过A的属性，可以切丁唯一B属性，则B依赖于A。例如姓名依赖于学号。（一对一或多对一）又例如（学号，课程名称） --> 分数。
      2. 完全函数依赖：A–>B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中的所有的属性值。例如（学号，课程名称） --> 分数。
      3. 部分函数依赖：A–>B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中的部分属性。例如（学号，课程名称） --> 姓名。
      4. 传递函数依赖：A–>B–>C，如果通过A的属性，可以确定唯一B属性的值，再通过B属性，可以确定唯一C属性的值，则称C传递函数依赖于A。例如：学号–>系名，系名–>系主任。
        
        依赖关系类似于充分必要之间的关系。
      5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性或属性组为该表的码。例如在上表中，码为（学号，课程名称）这个属性组。
        
        主属性：码这个属性组中的所有属性。
        
        非主属性：除码属性组内的其他属性。
    - 虽然消除了数据冗余，但添加和删除数据依然存在问题。
  3. 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
数据库的备份和还原
1. 命令行：
  - 备份：mysqldump -u用户名 -p密码数据库名称 > 保存的路径
  - 还原：
    1. 登录数据库
    2. 创建数据库 CREATE TABLE
    3. 使用数据库 USE
    4. 执行文件。 SOURCE 文件路径
2. 图形化操作。
多表查询
1. 查询语法：
```
SELECT * FROM 表1, 表2;
-- 返回的结果为迪卡尔积（AB集合中所有元素的所有任意组合）
```
  完成多表查询需要消除无用数据！
2. 多表查询的分类：
  1. 内连接查询：
    1. 隐式内连接：使用WHERE条件消除无用数据
      - 例子：
        
        SELECT t1.name, t1.gender, t2.name FROM emp t1, dept t2, WHERE t1.`dept_id` = t2.`id`;
    2. 显式内连接
      - 语法：SELECT 字段列表 FROM 表名1 [INNER] JOIN 表名2 ON 条件;
      - 例如：
        
        SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`; SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;
    3. 内连接查询需注意：
      1. 从哪些表中查询？
      2. 查询条件是什么？
      3. 要查询哪些字段？
  2. 外连接查询：
    1. 左外连接：
      - 语法：SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件
      - 注意：查询的是左表所有数据以及其交集部分。
    2. 右外连接：
      - 语法：SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件
      - 注意：查询的是右表所有数据以及其交集部分。
  3. 子查询：
    - 概念：子查询就是查询中嵌套查询，称嵌套的查询为子查询。
    - 例子：查询工资最高的员工信息
```
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
```
    - 子查询的不同情况：
      1. 子查询的结果是单行单列的：
        
        子查询可以作为条件，使用运算符去判断。
      2. 子查询的结果是多行单列的：
        
        子查询可以作为条件，使用集合IN判断
      3. 子查询的结果是多行多列的：
        
        子查询作为一张虚拟表，在两个表中查询
        
        也可以直接使用普通内连接查询。
3. 多表查询练习
事务
1. 基本介绍
  1. 概念：
    - 如果一个包含多个步骤的业务操作被事务管理，那么这些事务要么同时成功，要么同时失败。
    - 例如：张三给李四转账500元
      1. 查询张三账户余额是否大于500
      2. 张三账户金额 -500
      3. 李四账户金额 +500
    - 假如其中的步骤失败了，如果没有被事务管理，则会出现异常。如果被事务管理，则会进行回滚。
    - 假如没有出现异常，则提交事务。
  2. 操作：
    1. 开启事务：
```
start transaction;
```
    2. 回滚：
```
rollback;
```
    3. 提交：
```
commit;
```
  3. MySQL数据库中事务默认自动提交
    - 一条DML语句会自动提交一次事务。
    - 事务提交的两种方式：
      - 自动提交：MySQL中会自动提交DML
      - 手动提交：需要先开启事务，再提交
        
        Oracle数据库需要手动提交。
    - 修改事务的默认提交方式
      - 查看事务的默认提交方式：
        
        SELECT @@autocommit;
        
        1代表自动，0代表手动。
      - 设置提交方式：
        
        SET @@autocommit = 0; 设置手动提交
2. 四大特征
  1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
  2. 持久性：当事务提交或者回滚后数据库会持久化保存数据。
  3. 隔离性：多个事务之间。相互独立。（实际上会有一定的相互影响）
  4. 一致性：事务操作前后，数据总量不变。
3. 隔离级别
  - 概念：多个事务之间相互隔离，相互独立。但如果多个事务操作同一批数据，则会引发问题，设置不同的隔离级别，就可以解决这些问题。
  - 存在的问题：
    1. 脏读：一个事务读取到另一个事务中没有提交的数据。
    2. 不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样。
    3. 幻读：一个事务操作（DML）数据表中所有的记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
  - 隔离级别：
    1. read uncommitted：读未提交
      - 产生的问题：脏读，不可重复读，幻读
    2. read commited：读已提交（Oracle）
      - 产生的问题：不可重复读，幻读
    3. repeatable read：可重复读（MySQL默认）
      - 产生的问题：幻读
    4. serializable：序列化
      - 可以解决所有的问题
    5. 注意：
      - 隔离级别从小到大，安全性越来越高，但是效率越来越低。
    6. 数据库查询隔离级别：
      - SELECT @@tx_isolation;
    7. 数据库设置隔离级别：
      - SET GLOBAL transaction isolation level 级别字符串;

DCL

DBA：数据库管理员

DCL学习内容：

管理用户

添加用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

删除用户
```
DROP USER '用户名'@'主机名';
```
修改密码
```
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';

SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
```
- 如果忘记了root账户的密码
  1. 停止服务cmd -> net stop mysql
  2. 开启无认证：mysqld --skip-grant-tables
  3. 进入mysql，修改root的密码
  4. 关闭mysqld服务
  5. 打开mysql服务

查询用户

-- 切换到mysql数据库
USE mysql;
-- 查询user表
SELECT * FROM USER;

通配符：%表示可以从任意主机访问数据库

授权

查询权限：

SHOW GRANTS FOR '用户名'@'主机名';

授予权限：

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
-- 例如
GRANT ALL ON *.* TO 'zhangsan'@'localhost';

撤销权限：

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

如果觉得文章对你有用，请随意赞赏

javaStudy 教程学习总结

数据库基础入门

https://qc-blog.zust.online/archives/databaseintroduction

作者

前程QCQCQC

发布于

2023-06-02

更新于

2024-01-28

许可协议

CC BY 4.0