锐客网

  1. 首页 > 睿知 > it技术 > >

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

windowsmysql第一章

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

    推荐阅读


    上一篇：狂神mysql笔记|狂神mysql笔记 md,MySQL基础学习笔记

    下一篇：如何在Java中使用Iterator（）