MySQL学习笔记

原创已于 2025-03-17 20:43:49 修改 · 274 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #sql

于 2020-09-04 22:16:54 首次发布

1. SQL简介

SQL——structured query language，其实就是定义了操作所有关系向数据库的规则

每一种数据库操作的方式存在不一样的地方

2. SQL通用语法

SQL语句可以单行或多行书写，以分号结尾
可使用空格和缩进来增强语句的可读性
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
MySQL有三种注释方法：
- 单行注释：
  - # 注释内容
  - -- 注释内容
- 多行注释：
  /* 注释内容 */

3. SQL语句的分类

Data Definition Language (DDL 数据定义语言) 如：建库，建表
Data Manipulation Language(DML 数据操纵语言)，如：对表中的记录操作增删改
Data Query Language(DQL 数据查询语言)，如：对表中的查询操作
Data Control Language(DCL 数据控制语言)，如：对用户权限的设置

4. DDL：操作数据库、表

4.1 操作数据库：CRUD

C(Create)：创建

创建数据库
```
CREATE DATABASE 数据库名称;
```
创建数据库，判断是否存在，不存在才创建
```
CREATE DATABASE IF NOT EXISTS 数据库名称;
```

创建数据库，判断是否存在，并指定字符集

CREATE DATABASE IF NOT EXISTS 数据库名称 CHARACTER SET 字符集名;

R(Retrieve)：查询
- 查询所有数据库的名称
```
SHOW DATABASES;
```
- 查询某个数据库的字符集；查询某个数据库的创建语句
```
SHOW CREATE DATABASE 数据库名称;
```

U(Update)：修改

修改数据库的字符集

ALTER DATABASE 数据库名称 CHARACTER SET 字符集名;

D(Delete)：删除
- 删除数据库
```
DROP DATABASE 数据库名称;
```
- 删除数据库，先判断是否存在，存在则删除
```
DROP DATABASE IF EXISTS 数据库名称; 
```
使用数据库
- 使用数据库
```
USE 数据库名称;
```
- 查询当前正在使用的数据库名称
```
SELECT DATABASE();
```

4.2 操作表

C(Create)：创建

创建表

CREATE TABLE 表名(
	属性1 数据类型1,
    属性2 数据类型2,
    ...
    属性n 数据类型n
);

常用数据类型：

R(Retrieve)：查询
- 查询某个数据库中所有的表的名称
```
SHOW TABLES;
```
- 查询表结构
```
DESC 表名
```
- 查询表的字符集
```
SHOW CREATE TABLE 表名;
```

U(Update)：修改

修改表名

ALTER TABLE 表名 RENAME TO 新的表名;

修改表的字符集

ALTER TABLE 表名 CHARACTER SET 字符集的名称;

添加一列

ALTER TABLE 表名 ADD 列名 数据类型;

修改列的名称、类型

ALTER TABLE 表名 CHANGE 属性名 新属性名 新数据类型;
ALTER TABLE 表名 MODIFY 属性名 新数据类型;

删除列
```
ALTER TABLE 表名 DROP 属性名;
```

D(Delete)：删除
- 删除表
```
DROP TABLE IF EXISTS 表名;
```

5. DML：增删改表中数据

5.1 添加数据

INSERT INTO 表名(列名1, 列名2,...,列名n) VALUES(值1, 值2, ..., 值n);

列名和值要一一对应
如果表明后，不指定列名，则默认给所有列添加值
INSERT INTO 表名 VALUES(值1, 值2, ..., 值n);
除了数字类型，其他类型需要使用引号引起来

5.2 删除数据

DELETE FROM 表名 [WHERE 条件];

如果不加条件，则删除表中所有记录

TRUNCATE TABLE 表名;  -- 丢弃表，然后重新创建

这个在删除表中所有记录时效率高，因为**DELETE是一条记录一条记录删除**

5.3 修改数据

UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2, ... [WHERE 条件];

如果没有指定条件，默认修改所有记录的属性值

6. DQL：查询表中的记录

6.1 基础查询

基本查询语句：
```
select * from 表名;
```

语法：

select
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段
having
	分组之后的条件
order by
	排序
limit
	分页限定

去重查询

SELECT DISTINCT 查询属性 FROM 表名;

别名

SELECT 属性名 AS 别名 FROM 表名;

6.2 条件查询

where 子句后跟条件

运算符

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

-- 查询年龄大于20岁
SELECT * FROM student WHERE age > 20;

-- 查询年龄等于20岁
SELECT * FROM student WHERE age = 20;

-- 查询年龄不等于20岁
SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20;

BETWEEN … AND …

-- 查询年龄大于等于20且小于等于30
SELECT * FROM student WHERE age BETWEEN 20 AND 30;

-- 查询年龄22岁，18岁，25岁的信息
SELECT * FROM student WHERE age IN (22,18,25);

LIKE
占位符：

_：单个任意字符
%：多个任意字符

-- 查询姓马的有哪些？ like
SELECT * FROM student WHERE NAME LIKE '马%';

-- 查询姓名第二个字是化的人
SELECT * FROM student WHERE NAME LIKE "_化%";
			
-- 查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE '___';

-- 查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';

IS NULL

-- 查询英语成绩为null
-- null值不能使用 = （!=） 判断
SELECT * FROM student WHERE english IS NULL;

-- 查询英语成绩不为null
SELECT * FROM student WHERE english  IS NOT NULL;

AND 或 &&

-- 查询年龄大于等于20 小于等于30		
SELECT * FROM student WHERE age >= 20 && age <=30;
SELECT * FROM student WHERE age >= 20 AND age <=30;

OR 或 ||

-- 查询年龄22岁，18岁，25岁的信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25

NOT 或 !

-- 查询英语成绩不为null
SELECT * FROM student WHERE english  IS NOT NULL;

-- 查询年龄不等于20岁
SELECT * FROM student WHERE age != 20;

6.3 排序查询

语法
```
ORDER BY 排序字段1 排序方式1, 排序字段2, 排序方式2...;
```
- 排序方式分为升序(ASC)和降序(DESC)，默认升序排序
- 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件

6.4 聚合函数

将一列数据作为一个整体，进行纵向的计算

count：计算个数
```
SELECT COUNT(NAME) FROM student;
```
- 一般选择非空的列：主键
- count(*)
max：计算最大值
```
SELECT MAX(math) FROM student;
```
min：计算最小值
```
SELECT MIN(math) FROM student;
```
sum：计算和
```
SELECT SUM(math) FROM student;
```
avg：计算平均值
```
SELECT AVG(math) FROM student;
```

聚合函数的计算会排除 null 值，解决方案：

选择不包含非空的列进行计算
IFNULL函数

6.5 分组查询

语法：
```
GROUP BY 分组字段
```

【注意】

分组之后查询的字段：要么是分组字段、要么是聚合函数，不能有其他的

-- 按照性别分组。分别查询男、女同学的平均分
SELECT sex , AVG(math) FROM student GROUP BY sex;
		
-- 按照性别分组。分别查询男、女同学的平均分,人数
SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;

where 和 having 的区别

where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
where 后不可以跟聚合函数，having可以进行聚合函数的判断。

--  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
		
--  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;

6.6 分页查询

语法

LIMIT 开始的索引, 每页查询的条数;

公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数

-- 每页显示3条记录 

SELECT * FROM student LIMIT 0,3; -- 第1页
SELECT * FROM student LIMIT 3,3; -- 第2页
SELECT * FROM student LIMIT 6,3; -- 第3页

此语句是MySQL特有的

7. 约束

7.1 非空约束

NOT NULL，值不能为null

创建表时添加约束

CREATE TABLE stu(
    id INT,
    NAME VARCHAR(20) NOT NULL -- name为非空
);

创建表完后，添加非空约束

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

删除name的非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20);

7.2 唯一约束

UNIQUE，值唯一

创建表时，添加唯一约束

CREATE TABLE stu(
	id INT,
	phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束		
);

注意mysql中，唯一约束限定的列的值可以有多个null

删除唯一约束

ALTER TABLE stu DROP INDEX phone_number;

在创建表后，添加唯一约束

ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

7.3 主键约束

PRIMARY KEY

非空且唯一
一张表只能有一个字段为主键
主键就是表中记录的唯一标识

在创建表时，添加主键约束

CREATE TABLE stu(
    id INT PRIMARY KEY,-- 给id添加主键约束
    name VARCHAR(20)
);

删除主键

-- 错误示例： ALTER TABLE stu MODIFY id INT;
ALTER TABLE stu DROP PRIMARY KEY;

创建完表后，添加主键

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

自动增长
如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长

在创建表时，添加主键约束，并且完成主键自增长

CREATE TABLE stu(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 给id添加主键约束
    name VARCHAR(20)
);

删除自动增长
```
ALTER TABLE stu MODIFY id INT;
```

添加自动增长

ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

7.4 外键约束

FOREIGN KEY，,让表与表产生关系，从而保证数据的正确性

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

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

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

创建表之后，添加外键

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

级联操作

ALTER TABLE 表名 
	ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) 
		[ON UPDATE CASCADE] [ON DELETE CASCADE];

级联更新：ON UPDATE CASCADE

级联删除：ON DELETE CASCADE

8. 数据库的设计

8.1 多表之间的关系

分类
- 一对一：
  如：人和身份证，一个人只有一张身份证，一张身份证只对应一个人
- 一对多（多对一）：
  如：部门和员工，一个部门有多名员工，一个员工只能对应一个部门
- 多对多：
  如：学生和课程，一个学生可以选择多门课程，一门课程也可以被多名学生选择
实现关系
- 一对多(多对一)：
  如：部门和员工
  实现方式：在多的一方建立外键，指向一的一方的主键。
- 多对多：
  
  如：学生和课程
  
  实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
- 一对一(了解)：
  
  如：人和身份证
  
  实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。

8.2 数据库设计的范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

1 NF：属性不可分

关系里面每个属性必须是原子的
2 NF：符合1NF，并且，非主属性完全依赖于主键

满足 1 NF，并且不存在属性对主键的部分函数依赖(例如一般属性由主键的一部分决定，存在部分主键对某些属性没有决定性)，如：

学号，姓名，年龄，地址，课程号，成绩，显然主键是(学号，课程号)，这里单独根据学号就能一些属性，这就是部分函数依赖

应当避免这种部分函数依赖，否则会出现很麻烦的情况，例如学生才开学，没有选课时，基本信息就无法录入
3 NF：符合2NF，并且，消除传递依赖

满足 2 NF，并且不存在属性对主键的传递依赖，如：

职工编号，薪水等级，薪水，主键是职工编号，这里职工编号决定了薪水等级，薪水等级决定了薪水，形成了传递依赖

例如，已经定好了每一等级对应多少钱，但是员工没定级，这就无法录入数据

还有一种 BCNF 范式，和 3 NF 范式基本相当，比 3 NF 稍微严格，BCNF 中关系模式里属性之间的函数依赖关系里的决定因子必须是主键，这里举一个例子，满足 3 NF 但是不满足 BCNF：

城市，街道，邮政编码

这里主键是城市和街道，它们唯一地决定了一个邮政编码，但是邮政编码又能唯一地决定一个城市

一般设计数据库时到 3 NF 就行
4 NF：要求把同一表内的多对多关系删除
5 NF：将表分割成尽可能小的块，为了排除在表中所有的冗余

9. 数据库的备份和还原

备份

mysqldump -u用户名 -p密码 数据库名 > 保存的路径

还原

登录数据库
```
mysql -u用户名 -p密码;
```
创建数据库
```
CREATE DATABASE 数据库名;
```
使用数据库
```
USE 数据库名;
```
执行文件（备份时保存的文件）
```
SOURCE 备份文件路径;
```

10. 多表查询

10.1 内连接查询

隐式内连接：使用 where 条件消除无用数据

-- 查询所有员工信息和对应的部门信息
SELECT
	*
FROM
	emp,
	dept
WHERE
	emp.dept_id = dept.id;

-- 查询员工表的名称，性别,部门表的名称
SELECT
	emp.NAME,
	emp.gender,
	dept.NAME
FROM
	emp,
	dept
WHERE
	emp.dept_id = dept.id;
	
-- 或使用别名
SELECT
	t1.NAME, -- 员工表的姓名
	t1.gender, -- 员工表的性别
	t2.NAME -- 部门表的名称
FROM
	emp t1,
	dept t2
WHERE
	t1.dept_id = t2.id;

显示内连接
语法：

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;

10.2 外连接查询

左外连接
语法：

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件；

查询的是左表所有数据以及其交集部分。

-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
SELECT
	t1.*, 
	t2.NAME
FROM
	emp t1
LEFT JOIN dept t2 ON t1.dept_id = t2.id;

右外连接
语法：

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件；

查询的是右表所有数据以及其交集部分。

SELECT
	*
FROM
	dept t2
RIGHT JOIN emp t1 ON t1.dept_id = t2.id;

10.3 子查询

查询中嵌套查询，称嵌套查询为子查询。

-- 查询工资最高的员工信息
-- 1 查询最高的工资是多少 9000
SELECT MAX(salary) FROM emp;
			
-- 2 查询员工信息，并且工资等于9000的
SELECT * FROM emp WHERE emp.salary = 9000;
			
-- 一条sql就完成这个操作。子查询
SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);

子查询不同情况：

子查询的结果是单行单列的：
子查询可以作为条件，使用运算符去判断。运算符： >、 >= 、< 、<=、 =
```
-- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
```

子查询的结果是多行单列的：
子查询可以作为条件，使用运算符 in 来判断

-- 查询'财务部'和'市场部'所有的员工信息
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;

-- 子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

子查询的结果是多行多列的
子查询可以作为一张虚拟表参与查询

-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
-- 子查询
SELECT
	*
FROM
	dept t1,
	(
		SELECT
			*
		FROM
			emp
		WHERE
			emp.join_date > '2011-11-11'
	) t2
WHERE
	t1.id = t2.dept_id;
				
-- 普通内连接
SELECT * FROM emp t1,dept t2 WHERE t1.dept_id = t2.id AND t1.join_date > '2011-11-11';

11. 事务

概念

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
操作
- 开启事务： start transaction;
- 回滚：rollback;
- 提交：commit;
事务提交的两种方式
- 自动提交
  mysql就是自动提交的
  一条DML(增删改)语句会自动提交一次事务
- 手动提交
  Oracle 数据库默认是手动提交事务
  需要先开启事务，再提交
MySQL修改事务的默认提交方式
- 查看事务的默认提交方式：
```
SELECT @@autocommit; -- 1 代表自动提交  0 代表手动提交
```
- 修改默认提交方式：
```
SET @@autocommit = 0;
```
事务的四大特征

事务是对数据库的操作的集合，这组操作具有下面的一些性质：
- 原子性(Atomic action)
  
  组成一个事务的若干操作，要么全部成功，要么全部失败
- 存储一致性(Consistency preservation)
  
  数据库本来状态是一致的，经过一个事务的运行，数据库达到另外一个一致性
- 隔离性(Isolation)
  
  同时运行的事务互相之间不能干扰
- 持久性(Durability)
  
  一个事务只要成功完成，那么它对数据库产生的影响应该永久反映在数据库里的，哪怕将来出现故障也是可恢复的
传说中的ACID

一个事务有两种结束方式：commit 或 rollback

事务有以下 2 个规则：
- 提交规则
  
  在提交事务之前，修改必须写到硬盘上
- 先记后写规则
  
  如果直接修改数据库的话，必须先对原来数据进行记录

12. DCL：管理用户，授权

12.1 管理用户

添加用户

-- 只允许指定ip连接
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-- 允许所有ip连接（用通配符%表示）
CREATE USER '用户名'@'%' IDENTIFIED BY '密码';

-- 例如
CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'test'@'%' IDENTIFIED BY '123456';

删除用户

DROP USER '用户名'@'主机名';

DROP USER 'test'@'localhost';

修改用户密码
```
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
			
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
```
mysql中忘记了root用户的密码？
1. cmd – > net stop mysql 停止mysql服务
  需要管理员运行该cmd
2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
4. use mysql;
5. update user set password = password('你的新密码') where user = 'root';
6. 关闭两个窗口
7. 打开任务管理器，手动结束mysqld.exe 的进程
8. 启动 mysql 服务
9. 使用新密码登录。

查询用户

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

12.2 权限管理

查询权限

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

授予权限

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

-- 给张三用户授予所有权限，在任意数据库任意表上
GRANT ALL ON *.* TO 'zhangsan'@'localhost';

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
REVOKE UPDATE ON db3.account FROM 'lisi'@'%';