一份超长的MySQL学习笔记

前言

• 最近系统地学习了一边MySQL数据库的基础知识，巩固了一下以前学习的数据库查询基础，又新学习了关于索引、事务等的新内容，做了一些学习笔记。因为MySQL的学习，实操性比较强，所以笔记内容也比较简单，主要是关于常用语句的操作的整理和练习。

• 本文包括一些学习资源和教程，可以根据个人情况来按照教程学习，学习过程中也可以参考本笔记，如有错误，欢迎指正。

学习资源

• 视频资料：韩顺平讲MySQL

• MySQL安装（Windows）：MySQL安装+初始化操作

• MySQL本地服务配置（Windows）：制作MySQL的Windows服务+创建用户及授权

数据库

备份数据库

mysqldump -u root -p -B 数据库名 > d:\\文件名.sql

恢复数据库

SOURCE d:\\bak.sql

备份数据库表

mysqldump -u root -p  数据库名 表1 表2 > d:\\文件名.sql

数据表

创建

#创建
CREATE TABLE `user`(
	id INT,
	`name` VARCHAR(255),
	`password` VARCHAR(255),
	`birthday` DATE)
	CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

删除

drop table `actor`;

修改

--在password后面添加salary列
ALTER TABLE `user`
	ADD salary VARCHAR(32) NOT NULL DEFAULT '' 
	AFTER PASSWORD;
	
--修改列birthday字段的结构
ALTER TABLE `user`
	MODIFY birthday VARCHAR(60) NOT NULL DEFAULT '';

--删除name字段
ALTER TABLE `user`
	DROP NAME;

--重命名表
RENAME TABLE `user` TO `student`;

--修改表的字符集为utf8
ALTER TABLE student CHARACTER SET utf8;

--修改列名salary为user_name
ALTER TABLE student 
	CHANGE salary user_name VARCHAR(32) NOT NULL DEFAULT '';

DESC `user`;--显示表结构，查看所有的列

CRUD（增删改查）

insert

INSERT INTO `goods`(id,good_name,price)
	VALUES (1,'华为手机',2000);
INSERT INTO `goods`(id,good_name,price)
	VALUES (2,'苹果手机',3000);

update

UPDATE `goods`SET price = 1000;
UPDATE `goods`SET price = 2000 WHERE good_name='华为手机';
UPDATE `goods`SET price=price+5000 WHERE good_name='苹果手机';
#如果需要修改多个字段，可以通过 set 字段1=值1，字段2=值2...

delete

DELETE FROM `goods` WHERE price=2000;

select

#查找表内所有内容
SELECT * FROM student;
#按列查找表内容
SELECT id FROM student;
SELECT `id`,english FROM student;
#去除重复数据（只有这一行每一列的数据相同的时候才会去重）
SELECT DISTINCT english FROM student;
#使用表达式对查询的列进行运算
#在select语句中可使用as语句
SELECT `name`AS`名字`,(chinese+english+math) AS `COUNT` FROM student;
#条件查询
SELECT * FROM student 
	WHERE `name` = '赵云';
SELECT * FROM student 
	WHERE `english` > 90;
SELECT * FROM student 
	WHERE (chinese + english + math) > 200;
#order by
	#升序
SELECT `name`,(chinese + english + math)AS`count` FROM student 
	WHERE (chinese+english+math)>200 
	ORDER BY math ASC;
	#降序
SELECT `name`,(chinese + english + math)AS`count` FROM student 
	WHERE (chinese+english+math)>200 
	ORDER BY math DESC;
#多条件升降序查询
SELECT `name`,(chinese+english+math)AS`count` FROM student 
	WHERE `name` LIKE '张%'
	ORDER BY `count` DESC;

where语句中常用到的运算符

MySQL函数

合计/统计函数

#count（返回查询结果的行数）
#统计满足条件的某列有多少个，但是会排除为null的情况
SELECT COUNT(*) FROM student;

SELECT COUNT(*) FROM student
	WHERE (math+chinese+english)>200;
	
SELECT COUNT(chinese) FROM student
	WHERE chinese>90;
	

#SUM函数
#仅对数值起作用，否则会报错
SELECT SUM(math) FROM student;
SELECT SUM(math),SUM(chinese),SUM(english) FROM student;
SELECT SUM(chinese+english+math) FROM student;
SELECT SUM(chinese)/COUNT(*) FROM student;

#AVG函数
SELECT AVG(math) FROM student;
SELECT AVG(math+english+chinese) FROM student;

#MAX函数
SELECT MAX(math) FROM student;
#MIN函数
SELECT MIN(math) FROM student;

分组统计

--按照部门查询工资平均值和最大值
SELECT AVG(sal),MAX(sal),deptno FROM emp GROUP BY deptno; 

--多条件分组
SELECT AVG(sal),MAX(sal),deptno , job FROM emp GROUP BY deptno , job;

--查找平均工资低于2000的部门
SELECT  AVG(sal) , deptno FROM emp GROUP BY deptno HAVING AVG(sal) < 2000;

字符串函数

加密和系统函数

--演示加密函数和系统函数

查询增强

WHERE

-- 查询增强
-- 	使用where子句
-- 	在MySQL中，日期类型可以直接比较
SELECT * FROM emp WHERE hiredate <= '1991-12-11'; -- 需要注意格式

-- 模糊查询like
--	%表示0~多个任意字符
--	_表示单个任意字符
SELECT ename,sal FROM emp WHERE ename LIKE 'S%' ;
SELECT ename,sal FROM emp WHERE ename LIKE '__o%';

-- 显示没有上级的员工信息
SELECT * FROM emp WHERE mgr IS NULL;

-- 查询表结构
DESC emp;

ORDER BY

-- 使用ORDER BY
--	按照工资从低到高显示信息
SELECT * FROM emp ORDER BY sal; -- 默认降序
SELECT * FROM emp ORDER BY deptno ASC; -- 升序
SELECT * FROM emp ORDER BY deptno ASC , sal DESC;

分页查询

-- 分页查询
SELECT * FROM emp ORDER BY empno LIMIT 0,3;-- 第一页
SELECT * FROM emp ORDER BY empno LIMIT 3,3;-- 第二页

分组增强

-- 增强 GROUP BY 的使用
SELECT COUNT(*) FROM emp GROUP BY job;-- 各个岗位的人数
SELECT COUNT(*),COUNT(comm) FROM emp;-- 雇员总数、获得补助的雇员数

多子句查询

--统计各部门平均工资，并且大于1000的按照平均工资降序排序，取出前两行数据
SELECT deptno,AVG(sal) AS avg_sal
	FROM emp
	GROUP BY deptno
	HAVING avg_sal > 1000
	ORDER BY avg_sal DESC
	LIMIT 0,2;

多表查询

多表笛卡尔集

-- 多表查询
SELECT * FROM emp,dept; 
SELECT * FROM emp;
SELECT * FROM dept;

-- 显示雇员名称和雇员所在部门的名称
-- 从第一张表取出一行，与第二张表中的每一行进行组合，返回结果包含两张表的所有列
SELECT ename,sal,dname,emp.deptno FROM emp,dept WHERE emp.deptno = dept.deptno;

-- 显示部门10的部门名、员工名、工资
SELECT ename,sal,dname,emp.deptno FROM emp,dept WHERE emp.deptno = dept.deptno AND emp.deptno = 10;

-- 显示各个员工的姓名、工资和工资级别
SELECT ename,sal,grade FROM emp,salgrade WHERE sal BETWEEN losal AND hisal;

自连接

自连接是指在同一张表的连接查询，即将同一张表看成两张表

-- 自连接
SELECT worker.ename AS '职员名',boss.ename AS '上级名' 
	FROM emp worker,emp boss
	WHERE worker.mgr = boss.empno;

子查询

单/多行子查询

子查询是指嵌入在其他SQL语句中的select语句，也叫嵌套查询

• 单行子查询

  单行子查询是指只返回同一行数据的子查询语句

  例：如何显示与SMITH同一部门的所有员工

• 多行子查询

  多行子查询指返回多行数据的子查询，使用关键字in

-- 子查询

-- 单行子查询
SELECT deptno FROM emp WHERE ename = 'SMITH'; -- 子查询语句

SELECT * FROM emp
	WHERE deptno = (
		SELECT deptno FROM emp WHERE ename = 'SMITH'
	);


-- 多行子查询
SELECT DISTINCT job FROM emp WHERE deptno = 10; -- 子查询语句

SELECT ename,job,sal,deptno FROM emp 
	WHERE job IN (
		SELECT DISTINCT job FROM emp WHERE deptno = 10
	) AND deptno != 10;

子查询临时表

可以将子查询结果当做一张临时表使用

all any

-- all 和 any

-- 显示工资比部门30所有员工的工资都高的员工的姓名、工资和部门号
SELECT ename,sal,deptno FROM emp 
	WHERE sal > ALL(
		SELECT sal FROM emp WHERE deptno = 30
		);

SELECT ename,sal,deptno FROM emp 
	WHERE sal > (
		SELECT MAX(sal) FROM emp WHERE deptno = 30
		);
		
-- 显示工资比30号部门的其中一个员工工资高的员工的姓名、工资和部门号
SELECT ename,sal,deptno FROM emp 
	WHERE sal > ANY(
		SELECT sal FROM emp WHERE deptno = 30
		);
		
SELECT ename,sal,deptno FROM emp 
	WHERE sal > (
		SELECT MIN(sal) FROM emp WHERE deptno = 30
		);ss

多列子查询

多列子查询是指查询返回多个列数据的子查询语句

-- 多列子查询

-- 查询与 SMITH 的部门和和岗位完全相同的所有雇员（不包含SMITH本人）
SELECT deptno,job FROM emp
	WHERE ename = 'SMITH'; -- 子查询语句

-- 把上面的子查询语句与下面的多列子查询语句进行匹配
SELECT ename FROM emp
	WHERE (deptno,job) = (
		SELECT deptno,job FROM emp
		WHERE ename = 'SMITH'
	) AND ename != 'SMITH';

表复制和去重

• 自我复制数据（蠕虫复制）

  为了对某个SQL语句进行效率测试，我们需要海量数据时，可以使用此方法为表创建海量数据

-- 表复制

CREATE TABLE my_tab01
	(
		id INT,
		`name` VARCHAR(32),
		sal DOUBLE,
		job VARCHAR(32),
		deptno INT
	);

DESC my_tab01;

-- 演示如何自我复制
-- 先把emp表的记录复制进 my_tab01
INSERT INTO my_tab01 
	(id,`name`,sal,job,deptno)
	SELECT empno,ename,sal,job,deptno FROM emp; -- 相当于数据迁移
	
-- 自我复制
INSERT INTO my_tab01
	SELECT * FROM my_tab01;
	
-- 去重
DELETE FROM my_tab01;

合并查询

-- 合并查询
SELECT ename,sal,job FROM emp WHERE sal > 2500;
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER';

-- UNION不会去重
SELECT ename,sal,job FROM emp WHERE sal > 2500
UNION ALL
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER';

外连接

-- 左外连接
-- 右外连接

主键

MySQL约束

约束用于确保数据库满足特定的商业规则，在MySQL中，约束包括：

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK

主键的指定

-- 主键的使用

CREATE TABLE t17 (
	id INT PRIMARY KEY,
	`name` VARCHAR(32),
	email VARCHAR(32)	
	);
	
INSERT INTO t17
	VALUE(1,'jack','shuaiwang2019@126.com');
INSERT INTO t17
	VALUE(2,'wangshuai','jack2019@126.com');
INSERT INTO t17
	VALUE(3,'jack','shuaiwang2019@126.com');
	
-- 主键使用的细节

-- PRIMARY KEY 不能重复，而且不能为 NULL
-- 一张表中只能有一个主键，但可以是复合主键
CREATE TABLE t18 (
	id INT,
	`name` VARCHAR(32),
	email VARCHAR(32),
	PRIMARY KEY (id,`name`) -- 复合主键	
	);
-- 主键指定方式有两种：
-- 在字段名后指定
-- 在SQL语句后面指定

DESC t18;

UNIQUE

-- unique的使用

CREATE TABLE t21 (
	id INT UNIQUE, -- 表示id列是唯一的
	`name` VARCHAR(32),
	email VARCHAR(32)	
	);
	
-- unique的使用细节
-- 如果没有指定not null，则unique字段可以有多个null
-- 一张表可以有多个unique字段

FOREIGN KEY（外键）

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须有主键约束或是unique约束，当定义外键约束之后，要求外键列数据必须在主表的主键列存在或是为null

-- 外键的使用

-- 创建班级表
CREATE TABLE class(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT ''
)
-- 创建学生表
CREATE TABLE stu(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	class_id INT,
	-- 下面指定外键关系
	FOREIGN KEY (class_id) REFERENCES class (id)
);
DESC stu;

-- 测试数据
INSERT INTO class
	VALUES (100,'java'),(200,'web');
INSERT INTO stu
	VALUES (1,'张三',100),(2,'张三',200);
	
	
-- 外键细节
-- 外键指向的表的字段，要求是primary key或者是unique
-- 表的类型是innodb，这样的表才支持外键
-- 外键字段类型主要和主键字段类型一致（长度可以不同）
-- 外键字段的值，必须在之间字段中出现过，或者为null（前提是外键字段允许null）
-- 一旦建立主外键的关系，数据就不能随意删除了

CHECK

用于强制型数据必须满足的条件，假如在sal列上定义了check约束，并要求sal列值在100~200之间，不在的话就会报错

-- CHECK的使用
CREATE TABLE t23 (
	id INT PRIMARY KEY,
	`name` VARCHAR(32),
	sex VARCHAR(6) CHECK(sex IN ('man','woman')),
	sal DOUBLE CHECK(sal>1000 AND sal<2000)
	);
	
INSERT INTO t23 VALUES
	(1,'jack','man',1500);

练习

-- 商店表设计
CREATE TABLE goods(
	goods_id INT PRIMARY KEY,
	goods_name VARCHAR(32) NOT NULL DEFAULT '',
	unitprice DOUBLE CHECK(unitprice>1.0 AND unitprice<9999.99)
);

CREATE TABLE customer(
	customer_id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	address VARCHAR(32),
	email VARCHAR(32) UNIQUE,
	sex VARCHAR(6) CHECK(sex IN ('man','woman')),
	card_id INT
);

CREATE TABLE purchase(
	order_id INT PRIMARY KEY,
	customer_id INT NOT NULL,
	goods_id INT NOT NULL,
	nums INT NOT NULL DEFAULT 0,
	FOREIGN KEY (customer_id) REFERENCES customer (customer_id),
	FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
);

自增

-- 自增使用
CREATE TABLE t24(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(32) NOT NULL DEFAULT''
);

INSERT INTO t24 VALUES (NULL,'jack');

MySQL索引

索引优化速度

说起提高数据库性能，索引最为物美价廉，不需要加内存，不需要改程序，不需要调SQL，查询速度就可能提高百倍千倍

索引机制

索引原理

• 当表中没有索引的时候，SQL语句查询过程是按照 WHERE 条件进行全表扫描，与每一条数据进行对比，效率非常低下；
• 在某一个字段的列创建索引之后，会创建二叉树，提高查找数据的效率；
• 生成的索引会占用磁盘；
• 对表进行dml操作（修改，删除，添加），会对索引进行维护，对速度有影响。

创建索引

主键索引

主键就是一种索引 PRIMARY KEY，即主键索引。

唯一索引

UNIQUE 的字段的索引被称为唯一索引。

普通索引

全文索引

适用于 MyISAM 存储引擎，开发中考虑使用：全文索引 Solr 和 ElasticSearch（ES）

-- 索引的使用

-- 主键索引
CREATE TABLE t25(
	id INT,
	`name` VARCHAR(32)
);

-- 查询表是否有索引
SHOW INDEXES FROM t25;

-- 添加索引
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t25 (id);
-- 添加普通索引
CREATE INDEX id_index ON t25 (id);
ALTER TABLE t25 ADD INDEX id_index (id);
-- 添加主键索引
ALTER TABLE t25 ADD PRIMARY KEY id_index (id);

-- 删除索引
DROP INDEX id_index ON t25;
-- 删除主键索引
ALTER TABLE t25 DROP PRIMARY KEY;

-- 修改索引（先删除，再添加新的索引）

-- 查询索引
SHOW INDEX FROM t25;
SHOW INDEXES FROM t25;
SHOW KEYS FROM t25;
#不推荐
DESC t25;

创建索引规则

在哪些列上适合使用索引？

• 较频繁的作为查询条件字段应该创建索引；
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
• 更新非常频繁的字段不适合创建索引；
• 不会出现在WHERE子句中的字段不该创建索引。

事务

事务概念

事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。如：转账就要用事务来处理，用以保证数据的一致性。

事务和锁

当执行事务操作时（dml语句），MySQL会在表上加锁，防止其他用户修改表的数据，这对用户来讲是非常重要的。

MySQL数据库控制台事务的几个重要操作

-- 创建测试表
CREATE TABLE t27(
	id INT,
	`name` VARCHAR(32)
);

-- 开启事务
START TRANSACTION;
-- 设置保存点
SAVEPOINT a;
-- 执行dml操作
INSERT INTO t27 VALUES (100,'jack');
SELECT * FROM t27;
-- 设置保存点
SAVEPOINT b;
INSERT INTO t27 VALUES(200,'ben');

-- 数据回滚
ROLLBACK TO b;
ROLLBACK TO a;

-- 回退全部事务
ROLLBACK;

-- 提交事务，所有的操作生效，不能回退
COMMIT

事务注意事项

-- 事务注意事项

-- 如果不开启事务，默认情况下，dml操作是自动提交的，不能回滚；
-- 如果开启一个事务，没有创建保存点，可以执行rollback，默认就是回退到事务开始的状态；
-- 可以在事务没有提交的时候，创建多个保存点；
-- 可以在事务没有提交之前，选择回退到哪个保存点；
-- MySQL的事务机制需要innodb的存储引擎才能使用；
-- 开始一个事务 
START TRANSACTION ,
SET autocommit = off;

隔离级别

4种隔离级别

• 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性
• 如果不考虑隔离性，可能会引发如下问题
  • 脏读
  • 不可重复读
  • 幻读

-- 脏读：当一个事务读取另一个事务尚未提交的修改时，会产生脏读

-- 不可重复读：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读

-- 幻读：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读

-- 演示MySQL的事务隔离级别

-- 开启两个MySQL控制台
-- 查看当前MySQL的隔离级别
SELECT @@tx_isolation;
SELECT @@transaction_isolation;
-- mysql> select @@transaction_isolation;
-- +-------------------------+
-- | @@transaction_isolation |
-- +-------------------------+
-- | REPEATABLE-READ         |
-- +-------------------------+
-- 1 row in set (0.00 sec)

-- 把其中一个控制台的隔离级别设置 Read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 创建表
CREATE TABLE ACCOUNT(
	id INT,
	`name` VARCHAR(32),
	money INT
);

-- 把其中一个控制台的隔离级别设置 Read committed
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- mysql> SELECT @@transaction_isolation;
-- +-------------------------+
-- | @@transaction_isolation |
-- +-------------------------+
-- | READ-COMMITTED          |
-- +-------------------------+
-- 1 row in set (0.00 sec)

-- 把其中一个控制台的隔离级别设置 Repeatable read
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 把其中一个控制台的隔离级别设置 Serializable
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

设置隔离

-- 设置隔离

-- 查看当前会话隔离级别
SELECT @@transaction_isolation;
-- 查看系统当前隔离级别
SELECT @@global.transaction_isolation;
-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL [级别];
-- MySQL的默认级别
REPEATABLE READ;

-- 事务的acid特性

-- 原子性：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
-- 一致性：事务必须使数据库从一个一致性状态变换到另外一个一致性状态
-- 隔离性：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的一个事务，不能被其他事务的操作数据所干	 扰，多个并发事务之间要相互隔离
-- 持久性：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其    有任何影响

MySQL表类型和存储引擎

• MySQL的表类型由存储引擎（Storage Engines）决定，主要包括MyISAM、innoDB、Memory等
• MySQL数据表主要支持6种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoBDB。
• 这六种类型分为两类，一类是“事务安全型”（transaction-safe）：InnoDB；其余都属于第二类，称为“非事务安全型”（non-transaction-safe）