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

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的多行注释
*/
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
数据库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 数据库的字段属性 第一章|狂神说MYSQL基础笔记
文章图片

Unsigned
  • 无符号整数,声明了该列不能声明为负数
Zerofill:
  • 0填充,不足的位数,使用0来填充
自增:
  • 自动在上一条记录的基础上+1
  • 通常用来设计唯一的主键
  • 可以在“高级”中更改每次自增的位数
非空:
  • 如果不给他赋值就会报错
  • 如果不写值,就是null值
默认:
  • 如果字段是sex,“默认”为男,就是不写就是男
拓展:每一个表都必须有的字段:
id :主键

version:乐观锁
is_delete:伪删除
gmt_create:创建时间
gmt_update:修改时间
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
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完整的语法:顺序不能出错
第一章|狂神说MYSQL基础笔记
文章图片

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 联表查询 常见的三种:
第一章|狂神说MYSQL基础笔记
文章图片

最全的七种:
第一章|狂神说MYSQL基础笔记
文章图片

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

自连接:
第一章|狂神说MYSQL基础笔记
文章图片

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查询语句)
第一章|狂神说MYSQL基础笔记
文章图片

4.8 分组和过滤 函数分组后使用having
第一章|狂神说MYSQL基础笔记
文章图片

4.9 select小结 第一章|狂神说MYSQL基础笔记
文章图片

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

未使用索引:
第一章|狂神说MYSQL基础笔记
文章图片

使用索引:在大数据情况,使用索引速度优势很明显
第一章|狂神说MYSQL基础笔记
文章图片

-- 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基础笔记】第一章|狂神说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备份

    推荐阅读