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 </span>school<span class="token punctuation">
CHARACTER 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 数据库的字段属性
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完整的语法:顺序不能出错
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语句
INSERT INTO `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<num DO
INSERT INTO app_user(`name`,`eamil`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'1234567@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+10000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i = i+1;
END WHILE;
RETURN i;
END;
-- 执行定义的100万条数据函数, 用时48.795 sec
SELECT mock_data();
- 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 用户管理
-- 创建用户
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