MySQL基础篇

后端

发布日期: 2021-12-18

文章字数: 3.1k

阅读时长: 11 分

阅读次数:

一、基本命令

创建数据库

CREATE DATABASE database;

列出所有数据库

SHOW DATABASES;

使用指定数据库

USE database;

创建表

CREATE TABLE t_user(
    id INT(5) PRIMARY KEY AUTO_INCREMENT COMMENT'id',
    name VARCHAR(20) NOT NULL COMMENT'姓名 非空',
    gender CHAR(1) NOT NULL DEFAULT '男' COMMENT'学生性别',
    birthday DATETIME COMMENT'生日',
    age INT(3) NOT NULL COMMENT '年龄',
    email VARCHAR(255) COMMENT '邮箱'
);

展示表结构

DESC table;

查看数据库中有哪些表

SHOW tables;

查看数据库版本号

SELECT version();

终止一条命令的输入

\c

查看表中的所有数据

SELECT * FROM table;

插入操作

INSERT INTO t_user VALUES ('', '', ...);

删除数据

DELETE FROM t_user WHERE id = '';

删除表

drop table if exists t_user;

修改数据

UPDATE t_user SET name = '' WHERE id = '';

二、约束

主键约束（Primary Key）

什么是主键？

能够唯一确定表中的一条记录。通过给某个字段添加该约束，就可以使得该字段不重复且不为空（not null + unique）。
```
CREATE TABLE t_user (
	id INT(5) PRIMARY KEY,
	name VARCHAR(20)
);
```
联合主键

此处字段id和name一同作为主键，联合主键要求每个字段加起来不同即可（无需每个字段都不同）。实际开发中不建议使用联合主键，建议使用单一主键，因为主键存在的意义就是这行记录的身份证号。
```
CREATE TABLE user2 (
	id INT(5),
	name VARCHAR(20),
    PRIMARY KEY(id, name)
);
```

建表后添加主键

ALERT TABLE t_user ADD PRIMARY KEY (id);

建表后删除主键

ALERT TABLE t_user MODIFY id INT PRIMARY KEY;

主键值建议使用：int, bigint, char,等类型，不建议使用varchar做主键。主键值一般都是数字，一般都是定长的。

自增约束（AUTO_INCREMENT）

自增约束一般与主键搭配使用

CREATE TABLE t_user (
	id INT(5) PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20),
);

INSERT INTO t_user (name) VALUES ('name'); // 插入成功，自动生成id

唯一约束（unique）

约束修饰的字段不可以重复，但是可以为NULL

CREATE TABLE t_user (
	id INT(5) PRIMARY KEY AUTO_INCREAMENT UNIQUE,	// 列级约束
	name VARCHAR(20)
);

或者

CREATE TABLE t_user (
	id INT(5) PRIMARY KEY AUTO_INCREAMENT,
	name VARCHAR(20),
	UNIQUE(id, name)	// 表级约束
);

Q：什么时候使用表级约束？

A：需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。

注意：在MySQL中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段

非空约束（not null）

约束的字段不能为NULL

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

默认约束（Default Key）

如果插入字段时没有传值，就会使用默认值

CREATE TABLE t_user (
	id INT(5) DEFAULT 10,	
	name VARCHAR(20),
    gender VARCHAR(10) DEFAULT '女'	// 默认性别为女
);

外键约束（Foreign Key）

业务背景：设计数据库表，描述班级和学生信息

第一种方案：班级和学生存储在一张表中

id(PK)	name	classId	classname
1001	张三	1	高三一班

缺点：数据冗余，空间浪费

第二种方案：班级一张表，学生一张表（即：t_student和t_class表）

t_student：学生表

id(PK)	name	classId（FK）
1001	张三	1

t_class：班级表

classId(PK)	classname
1	高三一班

当classId字段没有任何约束的时候，可能会导致数据无效

为了保证classId字段中的值都是有效值，需要给classId字段添加外键约束

那么classId字段就是外键字段，classId字段中的每一个值都是外键值

注意：t_class是父表，t_student是子表。

删除表的顺序：先子表
创建表的顺序：先父表
删除数据的顺序：先子表
插入数据的顺序：先父表

CREATE TABLE t_class (
	classId INT PRIMARY KEY, 
	classname VARCHAR(255)
)DEFAULT CHARSET=utf8;

CREATE TABLE t_student(
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(255),
	classId INT,
	FOREIGN KEY(classId) REFERENCES t_class(classId)
)DEFAULT CHARSET=utf8;

补充：

外键值可以为null
子表中的外键引用父表中的某个字段，被引用的这个字段必须是主键吗？ —不一定，但至少具有unique约束

检查约束（MySQL不支持）

三、数据库的三大范式

第一范式

要求任何一张表必须有主键，每一个字段原子性不可再分。

第一范式（最核心、最重要的范式，所有表的设计都需要满足）

CREATE TABLE student(
	id INT(5) PRIMARY KEY AUTO_INCRAEMENT,
	name VARCHAR(20),
	province VARCHAR(20),
	city VARCHAR(20),
	area VARCHAR(20),
	street: VARCHAR(20)
);

第二范式

满足第一范式的条件下，第二范式要求：除主键外的每一列，都必须完全依赖于主键

不满足第二范式的例子

CREATE TABLE order(
	product_id INT,
	customer_id INT,
	product_name VARCHAR(20),
	customer_name VARCHAR(20),
    PRIMARY KEY(product_id, customer_id)
);

此处product_name只依赖于product_id，customer_name只依赖于customer_id，是完全依赖

第三范式

建立在第二范式的基础之上，要求所有非主键字段完全依赖主键，不要产生传递依赖。

不满足第三范式的例子

CREATE TABLE order(
	id INT PRIMARY KEY,
	product_id INT,
	customer_id INT,
	customer_phone INT
);

此处customer_phone又依赖于customer_id，存在传递依赖关系，不满足第三范式

四、连接查询

什么是连接查询？

从一张表中单独查询，称为单表查询。
emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字，这种跨表查询，多张表联合起来查询数据称为连接查询。

内连接

内连接：查询两张表有关联的数据

如上图，有两个表格，第一个表格记录前端、后端和运维三个大方向，第二个表格记录Java、vue、MySQL三门课。Java隶属于后端范畴，所以在第二个表格的最后一列写2，vue类似，MySQL没有隶属关系，所以写null。所以内连接仅能查出来两条数据，运维的数据查不出来

等值连接

INNER JOIN ... ON ... (WHERE ...)

// 案例：查询每个员工所在部门的名称（emp表中只有部门编号，没有部门名称），显示员工名和部门名
SELECT e.ename, d.dname FROM emp e INNER JOIN dept d ON e.DEPTNO = d.DEPTNO;  
// inner可以省略，带着inner可读性更好，一眼就能看出来是内连接

非等值连接

// 案例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级

SELECT e.ename, e.sal,s.grade FROM emp e INNER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

自连接

// 案例：查询每个员工的上级领导，要求显示员工名和对应的领导名（技巧：一张表看成两张表）
// emp a: 员工表
// emp b: 领导表
// emp a表的MGR = emp b表的empno
SELECT a.ename AS '员工名', b.ename AS '领导名' FROM emp a JOIN emp b ON a.mgr = b.empno; 
// 员工的领导编号 = 领导的员工编号

内连接的特点：完全能够匹配上这个条件的数据查询出来

外连接

左外连接（左连接）

左边的表查出所有数据，右边的表只查出有关联的数据（左边表：不管运维能不能关联数据，都必须查询出所有数据，右边表：仅查询出Java和vue的数据）
```
LEFT JOIN ... ON ...
或
LEFT OUTER JOIN ... ON ...
```
```
SELECT e.ename, d.dname FROM dept d LEFT JOIN emp e ON e.deptno = d.deptno;
```
右外连接（右连接）

右边的表查出所有数据，左边的表只查出有关联的数据（右边表：不管MySQL能不能关联数据，都必须查询出所有数据，左边表：仅查询出前端和后端的数据）
```
RIGHT JOIN ... ON ...
或
RIGHT OUTER JOIN ... ON ...
```
```
SELECT e.ename, d.dname FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno;
```
结论：外连接的查询结果条数一定是 >= 内连接的查询结果条数

案例：查询每个员工的上级领导，要求显示所有员工的名字和领导名？

方式一：select a.ename as ‘员工名’,b.ename as ‘领导名’ from emp a join emp b on a.mgr = b.empno;

方式二：select a.ename as ‘员工名’,b.ename as ‘领导名’ from emp a left join emp b on a.mgr = b.empno;

全连接（几乎不用）

图解内连接、左连接、右连接

五、事务（transaction）

什么是事务

一个事务是一个完整的业务逻辑，不可再分、最小的工作单元。要么同时成功，要么同时失败。

本质上：一个事务就是多条DML语句同时成功或同时失败。

只有DML语句（insert、delete、update）才有事务，其他语句和事务无关。

事务是如何实现的？

在事务的执行过程中，每一条DML语句的操作都会记录在”事务性活动的日志文件”中

在事务的执行过程中，可以提交事务，也可以回滚事务

提交事务：清空事务性活动的日志文件，将数据全部持久化到数据库的表中。提交事务标志着事务的结束，并且是一种全部成功的结束。
回滚事务：将之前的所以DML操作全部撤销，并且清空事务性活动的日志文件。回滚事务标志着事务的结束，并且是一种全部失败的结束。

MySQL中默认的事务行为是支持自动提交事务的（自动提交）

什么是自动提交？— 每执一条DML语句提交一次

将MYSQL的自动提交机制关闭掉：start transaction;或Set autocommit = 0

怎么提交事务？怎么回滚事务？

提交事务：commit语句

回滚事务：rollback语句（回滚只能回滚到上一次的提交点）

四大特征：ACID

原子性：事务是最小的单位，不可再分
一致性：同一事务中的sql语句要么同时成功，要么同时失败
隔离性：事务1和事务2之间具有隔离性，教室A和教室B之间有一道墙，这道墙就是隔离性。
持久性：事物一旦结束（commit），就不可返回（rollback）。事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上。

事务的隔离级别

读未提交（read uncomitted）：最低的隔离级别
- 事务A可以读取到事务B未提交的数据
- 这种隔离级别存在的问题就是：脏读（dirty read）
- 这种隔离级别一般都是理论上的，大多数的数据库的隔离级别都是二档起步
读已提交（read committed）：提交之后才能读到
- 事务A只能读取到事务B提交之后的数据。
- 这种隔离级别解决了脏读的问题
- 这种隔离级别存在的问题：不可重复读取数据
- 不可重复读取数据：在事务开启之后，第一次读到的数据是三条。在事务还未结束时，可能第二次读到四条数据（事务B在不断提交，事务A不断读取事务B的数据，每次都不一样）
- 这种隔离级别是比较真实的数据，每一次读到的数据是绝对真实的。
- Oracle数据库默认的隔离级别就是：读已提交（read committed）
可重复读（repeatable read）：提交之后也读不到
- 事务A开启之后，不管过了多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改并且提交了，事务A读到的数据还是没有改变。
- 可重复读解决的问题：不可重复读
- 可重复读存在的问题：幻读，即每次读取到的数据都是幻象，不够真实！
- MySQL中默认的事务隔离级别就是可重复读
序列化/串行化（serializable）：最高的隔离级别
- 效率最低，解决了所有的问题。
- 这种隔离级别表示事务排队，不能并发。
- 有些类似于synchronized，线程同步（事务同步）
- 每一次读取到的数据都是最真实的，并且效率是最低的。

注意：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件就行，解决幻读需要锁表

查看事务的隔离级别：

// 版本 5.x
SELECT @@global.tx_isolation;

// 版本 8.0
SELECT @@global.transaction_isolation;

六、存储引擎

见这篇文章：MySQL存储引擎

Prannt

https://prannt99.github.io/posts/7e46.html

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 Prannt !

MySQL

多线程与并发基础

2021-12-18 后端

多线程与并发

面试官：在浏览器上输入URL后发生了什么？

从输入一个网址到网页出现的全过程

2021-12-17 网络编程

计网面试

一、基本命令

创建数据库

列出所有数据库

使用指定数据库

创建表

展示表结构

查看数据库中有哪些表

查看数据库版本号

终止一条命令的输入

查看表中的所有数据

插入操作

删除数据

删除表

修改数据

二、约束

主键约束（Primary Key）

自增约束（AUTO_INCREMENT）

唯一约束（unique）

非空约束（not null）

默认约束（Default Key）

外键约束（Foreign Key）

检查约束（MySQL不支持）

三、数据库的三大范式

第一范式

第二范式

第三范式

四、连接查询

什么是连接查询？

内连接

外连接

五、事务（transaction）

什么是事务

事务是如何实现的？

怎么提交事务？怎么回滚事务？

四大特征：ACID

事务的隔离级别

六、存储引擎

你的赏识是我前进的动力