第一章|狂神说MYSQL基础笔记 MySQL|windows

1 认识MySQL 说明：是B站狂神说Mysql的学习笔记
前端：展示页面数据
后端：连接数据库JDBC，控制视图跳转，和给前端传递数据
数据库：存数据
程序员的分类：

只会CRUD，可以混饭吃=初级
学操作系统，数据结构和算法=中级
离散数学，数字电路，编译原理+实战经验=高级

1.1 为什么学习数据库 1、岗位需求，开源的mysql是必学的
2、现在的世界：大数据时代
1.2 什么是数据库？概念：数据库软件，安装不同操作系统上。500万以上数据需要索引优化
1.3 数据库分类关系型数据库：表与表，数据与数据之间是有关系的

MySQL,Oracle,Sql Server,DB2,SQLite

非关系数据库:对象存储，通过对象的自身属性来决定

Redis，MongDB
Not only：不仅仅是数据库

DBMS：数据库关系系统

就是数据库管理的可视化工具

1.4 MySQL介绍 MySQL是最好的一个关系型数据库关系系统，是一个开源的数据库软件，体积小、速度快，适用于中小型公司，大型数据库（用到集群）
MySQL5.7学习时稳定的，8改变的一些，配置更多
1.5 安装MySQL 1.6 安装SQLyog 1.7 连接数据库

mysql -uroot -p --连接数据库 show databases -- 查看所有的表 use school; -- 使用数据库 show tables; -- 显示该数据库所有的表 describe student; -- 查看该表的信息

CREATE DATABASE schoolCHARACTER SET utf8 COLLATE utf8_general_ci; – 创建数据库
– 这是sql的当行注释
/*
这是sql的多行注释
*/

数据库XXX语言：CRUD业务操作，CV程序员，API程序员
2 DDL定义数据操作数据库，操作数据库中的表，操作数据库中的数据
2.1 操作数据库

关键字需要背

-- 创建数据库 CREATE DATABASE IF NOT EXISTS school1; -- 删除数据库 DROP DATABASE IF EXISTS school1; -- ` 是tab键上面的特殊字符，当出现关键字和名字相同的时候使用 USE `school`;
1
2
3
4
5
6

2.2 数据库的列类型数据类型：

tinyint:十分小的数据，1个字节
smallint：教小的数据，2个字节
mediumint:中等大小的数据，3个字节
int：标准的使用，4个字节
bigint：大数据，8个字节
float:浮点数，4个字节
double：双精度的浮点数。8个字节
deciaml：字符串形式的浮点数，常用

字符串:

char:固定字符串大小，0-255
varchar:可变字符串，2^16 - 1，常用**
text:文本串，2^16 - 1

时间类型：

date：YYYY-MM-DD
time:HH:mm:ss
datetime:YYYY-MM-DD HH:mm:ss 最常用的时间格式
timestamp:时间戳，1970.1.1至今的毫秒数

null：

代表没有值，注意是不要使用null进行计算，因为结果为null

2.3 数据库的字段属性

文章图片

Unsigned

无符号整数，声明了该列不能声明为负数

Zerofill:

0填充，不足的位数，使用0来填充

自增:

自动在上一条记录的基础上+1
通常用来设计唯一的主键
可以在“高级”中更改每次自增的位数

非空：

如果不给他赋值就会报错
如果不写值，就是null值

默认：

如果字段是sex，“默认”为男，就是不写就是男

拓展：每一个表都必须有的字段：

id ：主键

version:乐观锁
is_delete：伪删除
gmt_create：创建时间
gmt_update:修改时间

2.4 创建数据库表

CREATE TABLE IF NOT EXISTS `student`( `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号id', `name` VARCHAR(30) NOT NULL DEFAULT'匿名' COMMENT '姓名', `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', `sex` VARCHAR(2) NOT NULL DEFAULT'女' COMMENT'性别', `birthday` DATETIME DEFAULT NULL COMMENT '出生日期', `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址', `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (`id`) )ENGINE = INNODB DEFAULT CHARSET =utf8 -- 注意PRIMARY KEY (`id`)后需要跟()
1
2
3
4
5
6
7
8
9
10
11

格式：

CREATE TABLE IF NOT EXISTS `student`( `列名1` 列类型 [属性1] [属性2],-- 最后一行不用加, )ENGINE = INNODB DEFAULT CHARSET =utf8 -- 查看创建数据库的语句 SHOW CREATE DATABASE `school`; -- 查看创建表的语句 SHOW CREATE TABLE `student`; -- 查看表的结构 DESC `student`;
1
2
3
4
5
6
7
8
9

数据库引擎：

ENGINE = INNODB
1

	MYISAM	INNODB
事物支持	不支持	支持
数据行锁定	不支持（它只表锁）	支持
外键约束	不支持	支持
全文索引	不支持	支持
表空间大小	较小	较大，约为MYISAM2倍

在物理空间下的区别：
- 所有的文件都在data目录下
- INNODB：在数据库中，只有一个*.frm文件，以及上级目录下的ibata1文件
- MYISAM：
  - *.frm:表结构的定义文件
  - *.MYD:数据文件（data）
  - *.MYI:索引文件（index）

2.5 修改和删除表

-- 修改表的名字 ALTER TABLE `student1`RENAME AS `student`; -- 增加表的字段 ALTER TABLE `student` ADD age1 INT(11); -- modify修改约束 ALTER TABLE `student` MODIFY `name` VARCHAR(20); -- change重命名：[旧名] [新名] ALTER TABLE `student` CHANGE `age1` `age` INT(1); -- 删除表的字段 ALTER TABLE `student` DROP age; -- 删除表 DROP TABLE IF EXISTS `student`;
1
2
3
4
5
6
7
8
9
10
11
12

3 DML管理数据 3.1 外键（物理）

以下是物理外键（数据库级别的外键），使用麻烦，不推荐使用，了解即可。通常使用逻辑外键。

-- constraint 外键名 foreign key (本表列名) reference 其他表名(其他标列名) ALTER TABLE `student` ADD CONSTRAINT `FK_gradeId` FOREIGN KEY(`gradeId`) REFERENCES `grade`(`gradeId`);
1
2
3

3.2 DML语言（全背）

数据库管理语言：数据存储，数据管理
insert
update
delete

3.3 insert插入数据

语法： insert into 表名（列名1） values （数据1）
省略（列名）的话，后面的values必须一一对应，不能少也不能跳

-- 完成插入一个数据 INSERT INTO `grade`(gradeId,gradeName) VALUES(1,'大一'); -- 不写列名就会一一对应，不对应就会报错 INSERT INTO `grade` VALUES(2,'大二'); -- 插入多个数据:(),() INSERT INTO `grade`(`gradeName`) VALUES('大三'),('大四');
1
2
3
4
5
6

3.4 修改

where必须有，如果没有where，就会改变全部数据
where后面的可以有多个条件

语法	含义	例子
=,!=,>,<
between …and…	在某个范围内	[2,5]
and	添加多个且条件	and sex = 男
or	添加多个或条件	or sex = 男

UPDATE `student` SET `name`='狂神' WHERE `id `=1; -- 如果是当前时间：current_time
1
2

3.5 删除 delete和truncate区别：

delete删除时候
- InnoDB:自增列会从1开始（存到内存中）
- truncate:自增继续从上一个开始(存到文件中)
truncate:重要，自增和计数器会归零

-- 删除单个数据列 DELETE FROM `student` WHERE `name` = '李四'; -- delete from 清空整个表,不会影响自增 DELETE FROM `test`; -- truncate 清空整个表，自增，计数器会归零，不会影响事务 TRUNCATE `test`;
1
2
3
4
5
6

4 DQL查询数据（重点） 4.1 DQL 数据库中最核心，最重要的语句
select完整的语法：顺序不能出错

文章图片

4.2 指定查询字段

-- 查询整张表的数据 SELECT * FROM student; -- 查询指定数据,使用别名，也可以给表使用别名 SELECT `name` AS '学生姓名' FROM student; -- 函数 concat(a,b) SELECT CONCAT('学生姓名：',`name`) AS '拼接的新名字' FROM `student`;
1
2
3
4
5
6

去重和select一些特殊的用法：

select distinct `gradeid` from `student`; -- 查询版本号 SELECT VERSION(); -- 用于计算 SELECT 100*3; -- select可以用来自增某些数据 SELECT `grade`+10 AS '成绩'FROM `student`;
1
2
3
4
5
6
7

4.3 where后逻辑表达式 and &&; or ||; not !
4.4 模糊查询

运算符	语法
is null	a is null
is not null	a is not null
between	a between c and d
like	a like b:a像b
in	a in (a1,a2,a3)：a=a1或a2或a3

重点是like结合%和_:

% : 表示0到任意一个字符
_：表示一个字符

-- 查询导演名字含有王 SELECT `actor_name` FROM `mtime_actor_t` WHERE `actor_name` LIKE '%王%'; -- 查询导演名字姓徐开头的 SELECT `actor_name` FROM `mtime_actor_t` WHERE `actor_name` LIKE '徐_';
1
2
3
4
5
6

in的使用：

SELECT * FROM `mtime_field_t` WHERE `hall_id` IN (1,2);
1
2

4.5 联表查询常见的三种：

文章图片

最全的七种：

文章图片

inner join	表中至少有一个匹配，就会返回
left join	会返回左表中所有的值，即使右表没有匹配
right join	会返回右表中所有的值，即使左表没有匹配

-- 左查询，返回左边查询的所有值，即使右表没有匹配 SELECT `film_name`,`biography` FROM `mtime_film_info_t` AS it LEFT JOIN `mtime_film_t` AS ft ON ft.`UUID`=it.`film_id`; -- 右查询，返回右边查询的所有值，即使左表没有匹配 SELECT `film_name`,`biography` FROM `mtime_film_info_t` AS it RIGHT JOIN `mtime_film_t` AS ft ON ft.`UUID`=it.`film_id`; -- from a left join b -- from a right join b -- 永远都是：a是左表，b是右边
1
2
3
4
5
6
7
8
9
10
11
12
13

自连接：

文章图片

4.6 分页排序

第一页：limit 0,5
第二页：limit 5,5
第n页：limit （n-1）*5，5
分页总结：
- n : 当前页
- 页面大小pageSize ： 5
- 初始下标：（n-1） pageSize*
- 总页数：查询出来的数据总数/pageSize

-- 排序：order by 字段名 asc/dec; -- 分页：limit 起始下标,页数
1
2

4.7 子查询 where里面嵌套一个（select查询语句）

文章图片

4.8 分组和过滤函数分组后使用having

文章图片

4.9 select小结

文章图片

5 函数 5.1 常用函数

-- 绝对值 select abs(-19); -- 向上取整 select ceiling(2.3); -- 向上取整 -- 向下取整 select floor(2.1); -- 向下取整 -- 生成随机数 select rand(); select sign(-10); -- 判断一个数的符号正数返回1，负数返回-1 -- 字符串长度: 9 select char_length('好好学习，天天向上'); -- 拼接字符串：学习 select concat('学','习')； -- 替换字符串,起始位置是1，长度是2 select insert('我爱学习',1,2,'不爱'); -- 替换函数：我爱java select replace('我爱学习','学习','java'); -- 反转函数 select reverse('习学爱我'); -- 三种获取当前日期 select current_Date(); select curdate(); select now(); -- 本地时间 select LocalTime(); -- 系统时间 select SYSdate(); -- 年 select year(now()); -- 系统 select system_user(); select user(); select version();
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

5.2聚合函数常见的聚合函数：count(),sum(),max(),min(),avg()

-- 指定列名去查，是会忽略null值：13 SELECT COUNT(seat_address) FROM`mtime_hall_dict_t`; -- count(*)和count(1)是不会忽略null值：14 -- 查询条件中没有索引时，count(*)比count(1)查询速度要快些。 -- 查询条件中有索引时，count(1)比count(*)查询速度要快些。 SELECT COUNT(1) FROM`mtime_hall_dict_t`; SELECT COUNT(*) FROM`mtime_hall_dict_t`;
1
2
3
4
5
6
7

5.3 MD5加密

-- 修改明文密码为md5加密 update md5_test set pwd= md5(pwd) where id = 1; -- 插入数据时候，使用md5() insert into md5_test values(3,'王五',md5('abcdefg')); -- 如果是用户传入的明文密码，就要先加密成md5()然后与数据库的加密密码比对 select * from md5_test where id =1 and pwd = md5('123456');
1
2
3
4
5
6

6 事务 6.1 什么是事务？概念：要么成功，要么都失败。将一组SQL放在一个批次中去执行。
事务原则：ACID原则（面试必问）

原子性（Atomicity）：针对同一个事务。要么都成功，要么都失败
一致性（Consistency）：一个事务前后数据一致。无论怎么转账，总数1000不会变
隔离性（Isolation）：两个事务间的操作不影响。互不干扰
- 脏读：一个事务读取到了另一个事务未提交的数据
- 不可重复读：同一读取操作中，数据不一致
- 虚读：读取到了别人刚提交的数据，导致前后读取不一致
持久性（Durability）：表示事务结束后的数据不会随着外界原因导致数据丢失。事务没有提交，就恢复到原状，事务一旦提交，就被持久化到数据库中，不可逆。

6.2 常用语法

-- 关闭自动提交 SET autocommit = 0; -- 一个事务开启 START TRANSACTION; -- 写sql：crud -- 提交事务 COMMIT; -- 回滚事务 ROLLBACK; -- 开启自动提交 SET autocommit = 1; -- 存档 SAVEPOINT 保存名; -- 回滚到保存点 ROLLBACK TO SAVEPOINT 保存名; -- 撤销保存点 RELEASE SAVEPOINT;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

模拟事务：

-- 创建数据库shop CREATE DATABASE shop CHARACTER SET utf8 ; -- 创建数据库表 account CREATE TABLE account( `id`INT(3) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) NOT NULL, `money` DECIMAL(9,2) NOT NULL, PRIMARY KEY (`id`) )ENGINE = INNODB DEFAULT CHARSET = utf8; -- sql语句 INSERTINTO `account` VALUES(1,'张三',2000.00),(2,'李四',10000.00); -- 模拟转账： SET autocommit = 0; -- 开启事务 START TRANSACTION; UPDATE account SET money = money -500 WHERE id = 1; UPDATE account SET money = money +500 WHERE id = 2; -- 提交，数据永久被保存 COMMIT; -- 回滚，回复默认值 ROLLBACK; -- 开启自动提交 SET autocommit = 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

7 索引索引（index）是可以快速提取数据的一种数据结构
7.1 索引的分类

主键索引（primary key）：primary key(字段名)
- 唯一标志。主键索引只能有一个，不可重复，只能有一列作为主键
唯一索引（unique key）：unique key 索引名（字段名）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标注为唯一索引
常规索引 (key/index):key 索引名（字段名）
- 默认的，index/key来设置
全文索引（FullText）FullText index 索引名（字段名）
- 在特定的数据库才有，快速定位数据

基础语法：

/* 索引的使用: 1.在创建表的时候给字段提交索引 2.创建完毕后，添加索引 */ -- 显示一个表中的全部索引信息 SHOW INDEX FROM school; -- 添加一个全文索引，大数据量下提高访问速度 ALTER TABLE student ADD FULLTEXT INDEX `index_name`(`name`); -- explain 分析sql执行的状况 EXPLAIN SELECT * FROM student; -- 非全文索引 EXPLAIN SELECT * FROM student WHERE MATCH(`name`) AGAINST('张三');
1
2
3
4
5
6
7
8
9
10
11
12

7.2 测试索引创建100万数据

CREATE TABLE `app_user` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) DEFAULT '', `eamil` VARCHAR(50) NOT NULL, `phone` VARCHAR(20) DEFAULT '', `gender` TINYINT(4) UNSIGNED DEFAULT '0', `password` VARCHAR(100) NOT NULL DEFAULT '', `age` TINYINT(4) DEFAULT '0', `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP, `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 -- 插入一百万条数据,$$写函数前必写 DELIMITER $$ CREATE FUNCTION mock_data() RETURNS INT BEGIN DECLARE num INT DEFAULT 1000000; DECLARE i INT DEFAULT 0; WHILE i 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



 未使用索引：
  

文章图片 



 使用索引：在大数据情况，使用索引速度优势很明显

  

文章图片 



 
-- 0.005 sec
SELECT * FROM app_user ;

-- 0.675 sec
SELECT * FROM app_user WHERE `name` = '用户10';

-- 0.611 sec
SELECT * FROM app_user WHERE `name` = '用户19999';

-- 分析 查了992786条数据才查到
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户19999';

-- 创建索引语法： create index 索引名 on 表名(字段名)
CREATE INDEX id_app_user_name ON app_user(`name`);

-- 使用索引后的速度：查了1条数据，速度巨快
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户19999';


  1
2
3
4
5
6
7
8
9
10
11
12


 
7.3 索引原则 
 
索引不是越多越好，索引在百万级以下就不使用 
索引一般加载常用来查询的字段上。 
索引是一个数据结构 
 
INNODB：默认是Btree 
推荐自己去查找专题观看，学习这个数据结构 
  
 
8 权限管理和备份 
8.1 用户管理 【第一章|狂神说MYSQL基础笔记】 

文章图片 



 
-- 创建用户
CREATE USER laosong IDENTIFIED BY '123456';

-- 修改密码（修改当前用户密码）
SET PASSWORD =PASSWORD('111111');

SET PASSWORD =PASSWORD('123456');

-- 修改制定用户密码
SET PASSWORD FOR root = PASSWORD('123456');

-- 用户重命名
RENAME USER lasong TO lasong2;

-- 删除用户
DROP USER laosong;

-- 用户全授权 除了root以外的全授权，除了给别的用户授权都能被授权
GRANT ALL PRIVILEGES ON *.* TO laosong;

-- 查看权限
SHOW GRANTS FOR laosong;

SHOW GRANTS FOR root@localhost;

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM laosong;


  1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18


 
8.2 MySQL备份

		  	

    
    




    
    
    


推荐阅读

           
                  
              
                  如何科学坐月子 春季坐月子这6件事千万别做 
                
                   
                
              
            

                  
              
                  广汽本田广汽本田皓影三大件是进口的吗 
                
                   
                
              
            

                  
              
                  小森生活抗寒衣服怎么做 小森生活抗寒套装制作攻略 
                
                   
                
              
            

                  
              
                  冬枣泡了糖精水的怎么看 
                
                   
                
              
            

                  
              
                  怎么用炉甘石治荨麻疹 
                
                   
                
              
            

                  
              
                  警察是否有权上门抓狗 
                
                   
                
              
            

                  
              
                  伊利鲜奶好还是蒙牛纯牛奶好 带你了解伊利纯牛奶和蒙牛纯牛奶有什么区别 
                
                   
                
              
            

                  
              
                  正常胸和下垂胸对比 如何揉胸才能变大 
                
                   
                
              
            

                  
              
                  ps快速替换靠山颜色 
                
                   
                
              
            

                  
              
                  夏天发面要几个小时才会发的好 夏天发面是用冷水还是用温水 
                
                   
                
              
            

                  
              
                  圆点|每日好价：大牌小圆点机械键盘开售 颜值超高 
                
                   
                
              
            

                  
              
                  臀部|臀部引发各种疼痛，令人很苦恼，这些锻炼方法，您会吗？尝试看一看 
                
                   
                
              
            

                  
              
                  接触区域按接触还是挤压强度分析 
                
                   
                
              
            

                  
              
                  NLP教程（Python NLTK用法示例和完整指南） 
                
                   
                
              
            

                  
              
                  三国志战略版震慑状态是什么 九大震慑类战法使用攻略 
                
                   
                
              
            

                  
              
                  如何为企业选择合适的云服务器？ 企业要用云服务器怎么搞 
                
                   
                
              
            

                  
              
                  lol2018战斗之夜奖励重随在哪领取 战斗之夜奖励重随领取地址 
                
                   
                
              
            

                  
              
                  果树环剥的方法及作用 果树环剥注意事项 
                
                   
                
              
            

                  
              
                  考研什么时候出成绩 考研是什么 
                
                   
                
              
            

                  
              
                  瓜蒌皮的功效与作用及禁忌 
                
                   
                
              
            

          

MySQL|MySQL(狂神笔记) 
 笔记|MySQL狂神笔记（不全） 
 二橘的java之始|Mysql 狂神学习笔记（一） 
 Java毕业设计项目实战篇|Java项目:生鲜水果商城(java+SpringBoot+HTML+Thymeleaf+easyUI+bootstrap+mysql) 
 datax|DBSwitch阉割版实现异构数据库表结构同步 
 mysql数据库|MySQL Like模糊查询速度太慢如何进行优化 
 如何删除win10的windows.old 
 面试|Mysql高频面试题（后端大数据面试必备） 
 后端|Mybatis-plus实现数据库的增删改查操作