事先声明：本文为个人在学习“狂神说MySQL系列连载课程”时总结的个人笔记。
数据库版本：MySQL 5.6.35
数据库
三大范式：
注：第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第三范式（3NF）也是在第二范式（2NF）的基础上建立起来的。
-
第一范式（1NF）：列不可分
（1）每一列属性都是不可再分的属性值，确保每一列的原子性；（2）两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据
-
第二范式（2NF）：属性完全依赖于主键，每个表只描述一件事
-
第三范式（3NF）：属性不依赖与其它非主属性，属性直接依赖于主键
数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。像：a–>b–>c 属性之间含有这样的关系，是不符合第三范式的。
JDBC
-
需要导包
复制代码1
2
3
4
5
6<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.16</version> </dependency> -
Java代码
复制代码1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28public class TestJDBC1 { public static void main(String[] args) throws Exception { //配置信息 String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8"; String username = "root"; String password = "926zhang"; //1. 加载驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //2. 连接数据库 Connection connection = DriverManager.getConnection(url, username, password); //3.编写sql语句 String sql = "select * from person"; //4. 预编译 PreparedStatement preparedStatement = connection.prepareStatement(sql); //5.执行sql语句 ResultSet resultSet = preparedStatement.executeQuery(); while(resultSet.next()){ System.out.println("id:"+resultSet.getInt(1)); System.out.println("name:"+resultSet.getString(2)); System.out.println("age:"+resultSet.getInt(3)); } //6. 关闭连接，释放资源（一定要做），先开后关 resultSet.close(); preparedStatement.close(); connection.close(); } }
事务的四大特性（ACID）
- 原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。 - 一致性（Consistency）
事务前后数据的完整性必须保持一致。 - 隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。 - 持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
事务基本语法
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21-- 使用set语句来改变自动提交模式 set autocommit = 0; /*关闭*/ set autocommit = 1; /*开启*/ -- 注意: -- 1.MySQL中默认是自动提交 -- 2.使用事务时应先关闭自动提交 -- 开始一个事务,标记事务的起始点 start transaction -- 提交一个事务给数据库 commit -- 将事务回滚,数据回到本次事务的初始状态 rollback -- 还原MySQL数据库的自动提交 set autocommit =1; -- 保存点 savepoint 保存点名称 -- 设置一个事务保存点 rollback to savepoint 保存点名称 -- 回滚到保存点 release savepoint 保存点名称 -- 删除保存点
例：
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15/* 课堂测试题目 A在线买一款价格为500元商品,网上银行转账. A的银行卡余额为2000,然后给商家B支付500. 商家B一开始的银行卡余额为10000 创建表account并插入2条数据 */ set autocommit = 0; -- 关闭自动提交 start transaction; -- 开启事务，标记事务的起始点 update account2 set cash = cash-500 where name = 'A'; update account2 set cash = cash + 500 where name = 'B'; commit; -- 提交事务 # rollback; set autocommit = 1; -- 恢复自动提交
索引
1. 主键索引（primary key）
作用：能够确保数据记录的唯一性
2. 唯一索引（unique）
作用：避免同一个表中某数据列中的值重复
与主键索引的区别：
1）主键索引只能有一个
2）唯一索引可以有多个
复制代码
1
2
3
4
5create table grade2( gradeid int(4) auto_increment primary key, gradename varchar(10) not null unique -- 唯一索引 );
3. 常规索引（index）
作用：快速定位特定数据
复制代码
1
2
3
4
5
6
7
8
9
10
11-- 创建表时添加索引 create table result2( resultid int(4) auto_increment primary key, studentid int(4), subjectid int(4), index ind(studentid,subjectid) -- 常规索引，还可以用“key”关键字 ); -- 创建后添加索引 alter table result add index (studentid,subjectid);
4. 全文索引（fulltext）
作用：快速定位特定数据
注意：1）MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引，
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
2）只能用于char、varchar、text类型的数据列
3）适合大型数据集
复制代码
1
2
3
4
5
6
7
8
9/*增加全文索引*/ alter table `school`.`student` add fulltext index `studentname` (`StudentName`); /*EXPLAIN : 分析SQL语句执行性能*/ explain select * from student where studentno='1000'; /*使用全文索引*/ -- 全文搜索通过 MATCH() 函数完成。 -- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。 EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
5. 创建索引及删除索引总结
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20/* #方法一：创建表时 CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC]) ); #方法二：CREATE在已存在的表上创建索引 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ; #方法三：ALTER TABLE在已存在的表上创建索引 ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ; #删除索引：DROP INDEX 索引名 ON 表名字; #删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY; #显示索引信息: SHOW INDEX FROM student; */
6. 索引效率测试
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36-- 创建一个app_user表用于测试 CREATE TABLE `app_user` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT '' COMMENT '用户昵称', `email` varchar(50) NOT NULL COMMENT '用户邮箱', `phone` varchar(20) DEFAULT '' COMMENT '手机号', `gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）', `password` varchar(100) NOT NULL COMMENT '密码', `age` tinyint(4) DEFAULT '0' COMMENT '年龄', `create_time` datetime DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表' -- 添加100万条数据（走了80多秒） DROP FUNCTION IF EXISTS mock_data; DELIMITER $$ CREATE FUNCTION mock_data() RETURNS INT BEGIN DECLARE num INT DEFAULT 1000000; DECLARE i INT DEFAULT 0; WHILE i < num DO INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`) VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100)); SET i = i + 1; END WHILE; RETURN i; END; SELECT mock_data(); -- 时间: 1.176s select * from app_user where name='用户9999'; -- 创建常规索引 alter table app_user add index (name); -- 时间: 0.01s select * from app_user where name='用户9999';
7. 索引准则
1）索引不是越多越好
2）不要对经常变动的数据加索引
3）小数据量的表尽量不要加索引
4）索引一般加在查找所用到的字段上
8. 索引的数据结构
1）我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
2）不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
结构化查询语句的分类
| 名称 | 解释 | 命令 |
|---|---|---|
| DDL（数据定义语言） | 定义和管理数据对象，如数据库、数据表等 | CREATE、DROP、ALTER |
| DML（数据操作语言） | 用于操作数据对象中所包含的数据 | INSERT、UPDATE、DELETE |
| DQL（数据查询语言） | 用于查询数据库中的数据 | SELECT |
| DCL（数据控制语言） | 用于管理数据库的语言，包括管理权限及数据更改 | GRANT、COMMIT、ROLLBACK |
对数据库的操作
-
创建数据库：
复制代码1
2create database if not exists 数据库名; -
删除数据库
复制代码1
2drop database if exists 数据库名; -
查看数据库
复制代码1
2show databases; -
使用数据库
复制代码1
2use 数据库名;
对数据表的操作
- 创建数据表
复制代码
1
2
3
4
5
6
7
8create table if not exists `表名`( #这里的`表名`所用的为反引号，可以区别与mysql的保留字（键盘Esc下的键） '属性名1' 列类型 [数据字段属性] [索引] [注释]， '属性名2' 列类型 [数据字段属性] [索引] [注释]， ... '属性名n' 列类型 [数据字段属性] [索引] [注释] )
数据字段属性包括：
UnSigned
- 无符号的
- 声明该数据列不允许负数 .
ZEROFILL
- 0填充的
- 不足位数的用0来填充 , 如int(3),5则为005
Auto_InCrement
-
自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)
-
通常用于设置主键 , 且为整数类型
-
可定义起始值和步长
-
- 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
- SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)
NULL 和 NOT NULL
- 默认为NULL , 即没有插入该列的数值
- 如果设置为NOT NULL , 则该列必须有值
DEFAULT
- 默认的
- 用于设置默认值
- 例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值
-
修改数据表
1）修改表名
复制代码1
2Alter table 旧表明 rename as 新表名;2）添加字段
复制代码1
2
3
4Alter table 表名 add 字段名 列属性; -- 在学生表中添加一个“专业”的属性 ALTER TABLE student ADD Professional VARCHAR(40) COMMENT '专业';3）修改字段
复制代码1
2
3
4
5
6
7alter table 表名 modify 字段名 列类型; -- 例：将学生表中的name属性的类型修改为varchar(10) alter table student modify name varchar(10); alter table 表名 change 旧字段名 新字段名 列属性; -- 例：将学生表中的Professional字段名修改为profession alter TABLE student change Professional profession VARCHAR(10);4）删除字段
复制代码1
2Alter table 表名 drop 字段名; -
删除数据表
复制代码1
2drop table [if exists]表名;
DML（数据操作语言）insert、delete、update
-
添加数据（insert命令）
复制代码1
2
3
4
5
6-- 语法 insert into 表名[(字段1,字段2,字段3,...)] values('值1','值2','值3',...); -- 例： insert into student(id,name,age) values ('1','Bob','20'); insert into student(id,name,age) values ('2','Lisa','20'),('3','Lucy','21'),('4','Jhon','18'); -
修改数据（update命令）
复制代码1
2
3
4
5
6-- 语法 update 表名 set 待更改的数据列=修改后的数据[,某字段2=XXX,...] [where 筛选条件]; -- 注：如果不指定“where 筛选条件”，则修改该表的所有列属性 -- 例：将student表中Bob的年龄设为15 update student set age='15' where name='Bob'; -
删除数据（delete命令与truncate命令）
复制代码1
2
3
4
5
6
7
8
9-- 1）语法 delete命令 delete from 表名 [where 筛选条件]; -- 例：将student表中id=4的那行数据删除 delete from student where id = 4; -- 2）语法 truncate命令:用于完全清空表数据 truncate [table] 表名; -- 例：清空student表 truncate student;注：delete与truncate的相同点与不同点
相同点：都能删除数据；不删除表的结构；但truncate速度更快；
不同点：truncate会重置auto_increment的计数器，delete不会；
使用truncate不会对事务产生影响。
DQL（数据查询语言）select
-
as关键字
作用：可以给经过查询后的数据列或表起一个新的名字
复制代码1
2
3
4
5select studentid as 学号, studentname as 姓名 from studnet; select studentid as 学号, studentname as 姓名 from studnet as 学生表; -- concat()函数可以拼接字符串 select concat('名字:',studentname) as 新名字 from student; -
distinct关键字
作用：去掉select查询返回的记录结果中重复的记录，只返回一条
复制代码1
2
3-- distinct去除重复项（默认为all） select distinct gradeid from student;
逻辑操作符
| 操作符名称 | 语法 | 描述 |
|---|---|---|
| and 或 && | A and B 或 A && B | 逻辑与 |
| or 或 || | A or B 或 A or B | 逻辑或 |
| not 或 ！ | not A 或 !A | 逻辑非 |
复制代码
1
2
3
4
5
6
7-- 查询成绩在90到100之间的学生学号和姓名 select studentid,studentname from student where sturesult >= 95 and sturesult <=100; select studentid, studentname,sturesult from student where sturesult between 95 and 100; -- 查询成绩不等于100的学生信息 select studentid, studentname,sturesult from student where not sturesult=100; select studentid, studentname,sturesult from student where sturesult!=100;
模糊查询
| 操作符名称 | 语法 | 描述 |
|---|---|---|
| is null | a is null | 若a为null，则结果为真 |
| is not null | a is not null | 若a不为null，则结果为真 |
| between…and… | a between b and c | 若a在b与c之间，则结果为真 |
| like | a like b | sql模式匹配，若a匹配b，则结果为真 |
| in | a in (a1,a2,a3,…) | 若a等于a1,a2,a3,…中的任意一个，则结果为真 |
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13-- 查询姓张的学生的所有信息 select * from student where studentname like '张%'; -- 查询姓张的并且名字只有两个字的学生所有信息 select * from student where studentname like '张_'; -- 查询姓张的并且名字只有三个字的学生所有信息 select * from student where studentname like '张__'; -- 查询名字里含有“张”字的学生的所有信息 select * from student where studentname like '%张%'; -- 查询学号为1，2，3的学生的所有信息 select * from student where studentid in (1,2,3); -- 查询姓名为空的学生的所有信息（注：不能直接写studentname = null，要用 is null） select * from student where studentname is null;
连接查询
关键词join
| 操作符名称 | 描述 |
|---|---|
| inner join | 查询两个表中的结果的交集 |
| left join | 以左表为基准，右表来一一匹配，匹配不上的返回左表的记录，右表以null填充 |
| right join | 以右表为基准，左表来一一匹配，匹配不上的返回右表的记录，左表以null填充 |
复制代码
1
2
3
4
5
6
7-- 内连接 select s.studentid,studentname,sturesult from student s inner join result r on s.studentid = r.studentid; -- 查询缺考的同学 select s.studentid,studentname,sturesult from student s inner join result r on s.studentid = r.studentid where sturesult is null ; -- 三个表连接查询 select s.studentid,studentname,objectname,sturesult from student s inner join result r on s.studentid = r.studentid left join object o on r.objectid = o.objectid;
排序和分页
-
排序（order by）
order by 语句主要用与根据指定的列对与结果集进行排序，默认是以ASC升序排序，可以使用DESC关键字自定义为降序。
复制代码1
2
3-- 按成绩降序排序 select s.studentid,studentname,sturesult from student s inner join result r on s.studentid = r.studentid order by sturesult desc; -
分页（limit）
复制代码1
2
3-- 每页显示2条数据 select s.studentid,studentname,sturesult from student s inner join result r on s.studentid = r.studentid order by sturesult desc limit 0,2
子查询
在查询语句的中where条件子句中，又嵌套了另一个查询语句，求解方式为“由里到外”。
聚合函数
复制代码
1
2
3
4
5
6
7SELECT SUM(StudentResult) AS 总和 FROM result; SELECT AVG(StudentResult) AS 平均分 FROM result; SELECT MAX(StudentResult) AS 最高分 FROM result; SELECT MIN(StudentResult) AS 最低分 FROM result; select objectname,avg(sturesult) as 平均分,max(sturesult) as 最高分,min(sturesult) as 最低分 from result r inner join object o on r.objectid = o.objectid group by r.objectid having 平均分 > 80; -- where写在group by前面，要是放在分组后面的筛选要使用having
设计数据库的大致步骤：
- 收集信息，充分了解用户需求
- 标识实体
- 标识每个实体需要的存储的详细信息
- 标识实体之间的关系
最后
以上就是纯情星星最近收集整理的关于MySQL学习笔记的全部内容，更多相关MySQL学习笔记内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复