前言
- 最近系统地学习了一边MySQL数据库的基础知识,巩固了一下以前学习的数据库查询基础,又新学习了关于索引、事务等的新内容,做了一些学习笔记。因为MySQL的学习,实操性比较强,所以笔记内容也比较简单,主要是关于常用语句的操作的整理和练习。
- 本文包括一些学习资源和教程。您可以根据您的个人情况按照教程进行操作。您也可以在学习过程中参考此注意事项。如果有任何错误,请改正。
[En]
this article includes some learning resources and tutorials. You can follow the tutorials according to your personal situation. You can also refer to this note during the learning process. If there are any mistakes, please correct them.*
学习资源
- 视频资料:韩顺平讲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
price = 1000;
UPDATE goods
price = 2000 WHERE good_name='华为手机';
UPDATE goods
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
名字
,(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)AScount
FROM student
WHERE (chinese+english+math)>200
ORDER BY math ASC;
#降序
SELECT name
,(chinese + english + math)AScount
FROM student
WHERE (chinese+english+math)>200
ORDER BY math DESC;
#多条件升降序查询
SELECT name
,(chinese+english+math)AScount
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
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;
自连接
自联接是指同一个表中的联接查询,即将同一个表视为两个表。
[En]
Self-join refers to a join query in the same table, that is, the same table is treated as two tables.
-- 自连接
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;
子查询临时表
您可以将子查询的结果用作临时表
[En]
You can use the results of a subquery as a temporary table
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
多列子查询
多列子查询是一个子查询语句,其中一个查询返回多列数据。
[En]
A multi-column subquery is a subquery statement in which a query returns multiple column data.
-- 多列子查询
-- 查询与 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
练习
-- 商店表设计
CREATE TABLE goods(
goods_id INT PRIMARY KEY,
goods_name VARCHAR(32) NOT NULL DEFAULT '',
unitprice DOUBLE CHECK(unitprice>1.0 AND unitprice
自增
-- 自增使用
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 条件进行全表扫描,与每一条数据进行对比,效率非常低下;
- 对字段的列进行索引后,会创建二叉树,以提高查找数据的效率
[En]
after the column of a field is indexed, a binary tree is created to improve the efficiency of finding data*
- 生成的索引会占用磁盘;
- 对表进行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;
创建索引规则
哪些列适合使用索引?
[En]
On which columns are appropriate to use indexes?
- 应更频繁地创建索引作为查询条件字段
[En]
indexes should be created more frequently as query condition fields*
- 唯一性较差的字段不适合创建单独的索引,即使它们经常用作查询条件
[En]
fields with poor uniqueness are not suitable for creating separate indexes, even if they are frequently used as query conditions*
- 更新非常频繁的字段不适合创建索引
[En]
fields that are updated very frequently are not suitable for index creation*
- 不会出现在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种隔离级别
- 当多个连接打开各自交易操作数据库中的数据时,数据库系统负责隔离操作,以确保每个连接获取数据的准确性。
[En]
when multiple connections open the data in their respective transaction operation database, the database system shall be responsible for isolating the operation to ensure the accuracy of each connection in obtaining data.*
- 如果不考虑隔离,可能会出现以下问题
[En]
if isolation is not considered, the following problems may arise*
- 脏读
- 不可重复读
- 幻读
-- 脏读:当一个事务读取另一个事务尚未提交的修改时,会产生脏读-- 不可重复读:同一查询在同一事务中多次进行,由于其他提交事务所做的修改或删除,每次返回不同的结果集,此时发生不可重复读-- 幻读:同一查询在同一事务中多次进行,由于其他提交事务所做的插入操作,每次返回不同的结果集,此时发生幻读
-- 演示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 uncommittedSET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;-- 创建表CREATE TABLE ACCOUNT( id INT, name
VARCHAR(32), money INT);-- 把其中一个控制台的隔离级别设置 Read committedSET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;-- mysql> SELECT @@transaction_isolation;-- +-------------------------+-- | @@transaction_isolation |-- +-------------------------+-- | READ-COMMITTED |-- +-------------------------+-- 1 row in set (0.00 sec)-- 把其中一个控制台的隔离级别设置 Repeatable readSET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;-- 把其中一个控制台的隔离级别设置 SerializableSET 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)
Original: https://www.cnblogs.com/nanfengashuai/p/15579455.html
Author: Blueshadow_
Title: 一份超长的MySQL学习笔记
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/508008/
转载文章受原作者版权保护。转载请注明原作者出处!