MySQL学习笔记

事先声明：本文为个人在学习“狂神说MySQL系列连载课程”时总结的个人笔记。
数据库版本：MySQL 5.6.35

数据库

三大范式：

注：第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第三范式（3NF）也是在第二范式（2NF）的基础上建立起来的。

1. 第一范式（1NF）：列不可分
   （1）每一列属性都是不可再分的属性值，确保每一列的原子性；

   （2）两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据

2. 第二范式（2NF）：属性完全依赖于主键，每个表只描述一件事

3. 第三范式（3NF）：属性不依赖与其它非主属性，属性直接依赖于主键

​ 数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。像：a–>b–>c 属性之间含有这样的关系，是不符合第三范式的。

JDBC

1. 需要导包

   <dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>8.0.16</version>
   </dependency>
   

2. Java代码

   public 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）

1. 原子性（Atomicity）
   原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2. 一致性（Consistency）
   事务前后数据的完整性必须保持一致。
3. 隔离性（Isolation）
   事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
4. 持久性（Durability）
   持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

事务基本语法

-- 使用set语句来改变自动提交模式
set autocommit = 0;
/*关闭*/
set autocommit = 1;
/*开启*/
-- 注意:
-- 1.MySQL中默认是自动提交
-- 2.使用事务时应先关闭自动提交
-- 开始一个事务,标记事务的起始点
start transaction
-- 提交一个事务给数据库
commit
-- 将事务回滚,数据回到本次事务的初始状态
rollback
-- 还原MySQL数据库的自动提交
set autocommit =1;
-- 保存点
savepoint 保存点名称 -- 设置一个事务保存点
rollback to savepoint 保存点名称 -- 回滚到保存点
release savepoint 保存点名称 -- 删除保存点

例：

/*
课堂测试题目
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）唯一索引可以有多个

create table grade2(
gradeid int(4) auto_increment primary key,
gradename varchar(10) not null unique -- 唯一索引
);

3. 常规索引（index）

作用：快速定位特定数据

-- 创建表时添加索引
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）适合大型数据集

/*增加全文索引*/
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. 创建索引及删除索引总结

/*
#方法一：创建表时
　　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. 索引效率测试

-- 创建一个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 索引；

结构化查询语句的分类

对数据库的操作

1. 创建数据库：

   create database if not exists 数据库名;
   

2. 删除数据库

   drop database if exists 数据库名;
   

3. 查看数据库

   show databases;
   

4. 使用数据库

   use 数据库名;
   

对数据表的操作

1. 创建数据表

create 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

• 默认的
• 用于设置默认值
• 例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值

2. 修改数据表

   1）修改表名

   Alter table 旧表明 rename as 新表名;
   

   2）添加字段

   Alter table 表名 add 字段名 列属性;
   -- 在学生表中添加一个“专业”的属性
   ALTER TABLE student ADD Professional VARCHAR(40) COMMENT '专业';
   

   3）修改字段

   alter 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）删除字段

   Alter table 表名 drop 字段名;
   

3. 删除数据表

   drop table [if exists]表名;
   

DML（数据操作语言）insert、delete、update

1. 添加数据（insert命令）

   -- 语法
   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');
   

2. 修改数据（update命令）

   -- 语法
   update 表名 set 待更改的数据列=修改后的数据[,某字段2=XXX,...] [where 筛选条件];
   -- 注：如果不指定“where 筛选条件”，则修改该表的所有列属性
   -- 例：将student表中Bob的年龄设为15
   update student set age='15' where name='Bob';
   

3. 删除数据（delete命令与truncate命令）

   -- 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

1. as关键字

   作用：可以给经过查询后的数据列或表起一个新的名字

   select studentid as 学号, studentname as 姓名 from studnet;
   select studentid as 学号, studentname as 姓名 from studnet as 学生表;
   -- concat()函数可以拼接字符串
   select concat('名字:',studentname) as 新名字 from student;
   

2. distinct关键字

   作用：去掉select查询返回的记录结果中重复的记录，只返回一条

   -- distinct去除重复项（默认为all）
   select distinct gradeid from student;
   

逻辑操作符

-- 查询成绩在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;

模糊查询

-- 查询姓张的学生的所有信息
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

-- 内连接
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;

排序和分页

1. 排序（order by）

   order by 语句主要用与根据指定的列对与结果集进行排序，默认是以ASC升序排序，可以使用DESC关键字自定义为降序。

   -- 按成绩降序排序
   select s.studentid,studentname,sturesult from student s inner join result r on s.studentid = r.studentid order by sturesult desc;
   

2. 分页（limit）

   -- 每页显示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条件子句中，又嵌套了另一个查询语句，求解方式为“由里到外”。

聚合函数

SELECT 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

设计数据库的大致步骤：

1. 收集信息，充分了解用户需求
2. 标识实体
3. 标识每个实体需要的存储的详细信息
4. 标识实体之间的关系

最后

以上就是纯情星星为你收集整理的MySQL学习笔记的全部内容，希望文章能够帮你解决MySQL学习笔记所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。