MySQL学习笔记
1、认识MySQL
1.7、连接数据库
命令行连接
mysql -uroot -pxxxx --连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; --修改用户密码
flush privileges; --刷新权限
-----------------------------所有的语句都用分号结尾-------------------------
show databases; -- 查看所有数据库
show tables; -- 查看某个数据下的所有表单
show create database xxxx -- 显示创建xxxx数据的语句
show create table xxxx -- 显示创建xxxx表单的语句
use xxxx; -- 切换xxxx数据库
describe xxxx; -- 查看xxxx表单
show columns from xxxx; -- 查看xxxx表单
create database xxxx; -- 创建xxxx数据库
-- 单行注释
/* （多行注释）
asdasd
*/
数据库xxx语言 CRUD增删改查！ CV程序猿 API程序猿 CRUD程序猿（业务）
DDL 定义
DML 操作
DQL 查询
DCL 控制
2、操作数据库
2.1、操作数据库（了解）
1、创建数据库
CREATE DATABASE [IF NOT EXIST] westos;
2、删除数据库
DROP DATABASE [IF EXIST] westos;
3、使用数据库
-- 如果你的表名或字段有特殊字符，就需要带``
USE `school`;
4、查看数据库
SHOW DATABASES; -- 查看所有数据库
利用可视化软件来进行学习
-
sqlyog或者Navicat
-
对照软件学习语句
-
语法或关键字必须要记住
2.2、数据库的数据类型
数值
- TINYINT 十分小的数 1个字节
- SMALLINT 较小的数 2个字节
- MEDIUMINT 中等大小的数 4个字节
- INT 标准的整数 4个字节
- BIGINT 较大的数 8个字节
- FLOAT 单精度浮点数 4个字节
- DOUBLE 双精度浮点数 8个字节
- DECIMAL 字符串形式浮点数
字符串
- char 1~255个字符的定长串
- varchar 长度可变，<=255字节，可存储0到n个字符的变长串(n<=255)
- tinytext 最大长度为255字节
- text 最大长度为64K
日期和时间
- date 格式：YYYY-MM-DD 表示1000-01-01~9999-12-31的日期
- time 格式：HH:MM:SS （H：表示24小时制 h表示12小时制）
- datetime date与time的结合 YYYY-MM-DD HH:MM:SS
- timestamp 时间戳 1970-01-01到现在的毫秒数
二进制
- BLOB 64KB
- MEDIUMBLOB 16MB
- LONGBLOB 4GB
- TINYBLOB 255Bytes
null
- 没有值，未知
2.3、数据库的字段属性（重点）
Unsigned：
- 无符号的整数
- 声明了该列不能声明为负数
Zerofill：
- 0填充
- 当长度不够时补0
自增：
- 通常用来设计主键，必须是整数类型
- 可以自定义主键的起始和步长
非空 NULL not null
- 如果为NULL，当插入数据不声明时会报错
/* 扩展：每一个表都必须存在以下5个字段
id 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
2.4、创建数据库
-- AUTO_INCREMENT 自增
-- COMMENT '' 注释
-- NOT NULL 非空
-- PRIMARY KEY 主键，一般一个表只能有一个
-- 字符串要用单引号括起来，所有的语句都要加英文逗号(,) 最后一句不用加
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(30) NOT NULL DEFAULT '123456' COMMENT '密码',
`gender` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATE DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '电子邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 `student`
格式
CREATE TABLE IF NOT EXISTS `表名`(
`字段名` [列类型] [属性] [索引] [注释],
`字段名` [列类型] [属性] [索引] [注释],
....,
`字段名` [列类型] [属性] [索引] [注释]
)[表类型] [字符集设置] [表注释]
2.5、数据表的类型
--关于数据库引擎
/*
MyISAM 早些年使用的
InnoDB 默认使用
*/
| MyISAM | InnoDB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大 |
常规使用操作：
-
MyISAM：节省空间，速度较快
-
InnoDB：安全性高，事务处理，多表用户操作
-
Memory：功能等于MyISAM，但是数据存储在内存（不是磁盘），适合于临时表
在物理空间存放的位置
所有的数据库文件都存在data目录下，由于我是exe文件默认路径安装Mysql的，data目录和安装目录是不一样的。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bJXDAzBt-1620037367234)(Mysql精通笔记.assets/image-20210329172522936.png)]
本质还是文件的存储
MySQL引擎在物理文件上的区别
- InnoDB：在数据表只有一个 *.frm文件，以及上级目录下的 ibdata1文件
- MyISAM：
- *.frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
设置数据表的字符集编码
-- 第一种方法（灵活）：
CHARSET=utf8
-- 第二种方法：在my.ini中配置
character-set-server=utf8
不设置的话，MySQL默认的字符集编码Latin1（不支持中文！）
2.6、修改和删除表
修改
-- 修改表名：
-- ALTER TABLE 旧表名 RENAME AS 新表名
-- RENAME TABLE 旧表名 to 新表名
ALTER TABLE teacher RENAME AS teacher1;
-- 增加表的字段： ALTER TABLE 表名 ADD 字段名 列属性 [约束条件] [first|after 字段名]
ALTER TABLE teacher ADD age INT(11);
-- 修改表的字段：
（重命名修改约束！）
-- 1.修改字段名字(CHANGE)
-- ALTER TABLE 表名 CHANGE 旧列名 新列名 列属性
ALTER TABLE teacher1 CHANGE age age1 INT(1)
-- 2.修改字段属性(CHANGE MODIFY)
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 [旧/新]属性
-- ALTER TABLE 表名 MODIFY 列表 列属性
ALTER TABLE teacher1 MODIFY age VARCHAR(11);
ALTER TABLE teacher1 CHANGER age age VARCHAR(11);
/*
change 用来重命名字段名————
modify 只能更改列属性——————只能改字段数据类型完整约束，不能改字段名
总结：重命名用change 更改属性和约束用modify
*/
-- 删除表的字段：ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1
- 修改字段
- 删除字段
- ALTER TABLE 表名 DROP 字段名
- 添加字段
- ALTER TABLE 表名 ADD 字段名 列属性 [约束条件] [first|after 字段名]
- 修改字段名
- Change——ALTER TABLE 表名 CHANGE 旧列名 新列名 列属性
- 修改字段类型
- Change——ALTER TABLE 表名 CHANGE 旧列名 新列名 属性
- Modify——ALTER TABLE 表名 MODIFY 列名 列属性
- 删除字段
- 补充 修改约束条件
- 添加约束条件
- 删除约束条件
删除
-- 删除表（如果表存在再删除）
DROP TABLE IF EXISTS teacher1
所有的创建和删除尽量加上判断，以免报错
注意点：
- 尽量使用`来包裹字段名
- 注释-- /**/
3、MySQL数据管理
3.1、外键（了解即可）
方式一、在创建表的时候，增加约束（麻烦、比较复杂）
-- 创建grade表
CREATE TABLE IF NOT EXISTS `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(20) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生表的 gradeid 字段要去引用年级表的 gradeid
-- 定义外键
-- 给这个外键添加约束（执行引用）
-- 创建student表
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(30) NOT NULL DEFAULT '123456' COMMENT '密码',
`gender` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATE DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生年级', -- 表里要添加这个列
`address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '电子邮箱',
PRIMARY KEY(`id`),
KEY `FK_gradeid` (`gradeid`), --约束名
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
删除有外键关系的表，要先删除引用别人的表（从表），才能删除被引用的表（主表）
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dhDuyhRs-1620037367241)(Mysql精通笔记.assets/image-20210329205601955.png)]
方式二、创建表成功后，添加约束
CREATE TABLE IF NOT EXISTS `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(20) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生表的 gradeid 字段要去引用年级表的 gradeid
-- 定义外键
-- 给这个外键添加约束（执行引用）references
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(30) NOT NULL DEFAULT '123456' COMMENT '密码',
`gender` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATE DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '电子邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 创建的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_id` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
-- ALTER TABLE 表名 CONSTRAINT 约束名 FOERIGN KEY(作为外键的列) REFERENCES 哪个表(哪个字段)
以上操作都是物理外键，数据库级别的外键，不建议使用（避免数据库过多造成困扰）
最佳实践
- 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
- 使用多张表的数据时，想使用外键——一种逻辑（用程序实现）
3.2、DML语言（全部记住）
数据库意义：数据存储，数据管理
DML语言：数据操作语言
- Insert
- Update
- delete
3.3、添加
insert
- 插入完整的行
- 插入行的一部分
- 插入多行
- 插入某些查询的结果——INSERT SELECT语句
-- INSERT INTO 表名([字段名1],[字段名2],[字段名3],...) VALUES('值1','值2','值3',...)
INSERT INTO `grade`(`gradename`) VALUES('大四');
-- 一般写插入语句，要数据和字段要一一对应
-- 插入多行
-- 1.多条INSERT INTO 语句
-- 由于主键自增，可以省略。
INSERT INTO`grade`(`gradename`) VALUES('大三');
INSERT INTO `grade`(`gradename`)VALUES('大一');
-- 2.单条INSERT INTO 语句(前提的是字段名和次序相同)
INSERT INTO `grade`(`gradename`) VALUES('大五'),('大六');
-- 字段是可以省略的
INSERT INTO `student`
VALUES (1,'李四','aaaaa','女','1995-09-24',1,'南京','email@qq.com');
语法：INSERT INTO 表名([字段名1],[字段名2],[字段名3],...) VALUES('值1','值2','值3',...)
注意事项：
- 字段和字段之间要用英文逗号隔开
- 如果不提供列名，则必须给表的每个列提供一个值。
- 如果提供列名，则必须对每一个给出的列一个值。
扩展：
- INSERT SELECT——将一条SELECT语句的结果插入表中。
- SELECT的列名和INSERT的列名不一定要匹配。
- MySQL不关心SELECT返回的列名，它利用的是列的位置
3.4、修改
update
- 更新表中特定行
- 更新表中所有行
语法：UPDATE 表名 SET column_name = value,[column_name = value] where [条件];
--修改学员名字，带了简介
UPDATE `student` SET `name`='李五' where id = 1;
--不指定条件的情况下，会改动所有表！！！
UPDATE `student` SET `name`='长江7号';
--修改多个属性,逗号隔开
UPDATE `student` SET `name`='王六', `email`='1232132@qq.com' where id=1;
过滤数据：where子句 运算符
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <> | 不等于 | ||
| != | 不等于 | ||
| < | 小于 | ||
| <= | 小于等于 | ||
| > | 大于 | ||
| >= | 大于等于 | ||
| BETWEEN…AND… | 在某个范围内 | [2,5]，包括两端 | |
| IS NULL（提一嘴） | 或， | 5>1 or 1>2 | true |
-- 通过多个条件来定位数据
UPDATE `student` SET `name`='长江7号' WHERE id=1 and sex='女';
语法：UPDATE 表名 SET column_name = value,[column_name = value] where [条件];
注意：
- 列为数据库的列，尽量带上``
- 条件要添加上，否则会修改所有的列
- value，是一个具体的值，也可以是一个变量
- NULL 与空字符串或仅仅包含空格不同，在过滤数据时，一定要验证返回数据中确实给出了被过滤列中具有NULL的行
3.5、删除
DELETE
- 从表中删除特定的行
- 从表中删除所有的行
语法：delete from 表名 [where] 条件
--删除数据（避免这样写，会全部删除）
DELETE FROM `student`
-- 删除指定数据
DELETE FROM `student` WHERE id = 1;
-- 删除多行
DELETE FROM 表名 WHERE 字段名 in (值1,值2,值3...)
注意：
- DELETE删除整行而不是列
- DELETE删除的是表的内容，而不是表本身
- 先用SELECT进行测试，保证数据过滤正确后，确保WHERE子句的正确性
TRUNCATE 命令
作用：完全清空一个数据库表，表的结构和索引约束不会变
-- 清空student表
TRUNCATE `student`;
DELETE 和 TRUNCATE 区别
参考文章：https://www.jb51.net/article/21020.htm
- 相同点：都能删除数据，都不会删除表结构
- 不同：
- TRUNCATE，重新设置自增列，计数器会归零
- TRUNCATE，不会影响事务，不能回滚，不会触发触发器
- DELETE是DML语言，TRUNCATE是DDL语言
- 如果和事务有关，或者想触发trigger，还是用DELETE
- 速度上，一般来说TRUNCATE>DELETE
-- 测试delete 和 TRUNCATE 区别
CREATE TABLE `test`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `test` (`coll`) VALUES('1'),('2'),('3');
DELETE FROM `test`; -- 不会影响自增，删除后去查看自动增量
INSERT INTO `test` (`coll`) VALUES('1'),('2'),('3');
TRUNCATE TABLE `test`; -- 自增会归零
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kVzacf2s-1620037367249)(Mysql精通笔记.assets/image-20210330003100633.png)]
了解即可：DELETE删除的问题，重启数据库，现象
- InnoDB 自增列会从1开始，（存在内存当中，断电即失）
- MyISAM 继续从上一个自增量开始（存在文件中的，不会丢失）
4、DQL查询数据（最重点）
4.1、DQL
DQL( Data Query Language 数据查询语言 )
- 查询数据库数据 , 如SELECT语句
- 简单的单表查询或多表的复杂查询和嵌套查询
- 是数据库语言中最核心,最重要的语句
- 使用频率最高的语句
Select完整的语法：
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条
注意 : [ ] 括号代表可选的 , { }括号代表必选得
4.2、指定查询字段
-- 基本语法：SELECT 字段 FROM 表名
--查询全部的列
SELECT * FROM student;
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student;
-- 给查询的列起别名 AS
SELECT `StudentNo` AS 学号, `StudentName` AS 学生姓名 FROM student AS S
-- 函数 Concat(a,b)
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student;
语法：
SELECT 字段 .... FROM 表名字段名 as 别名 表名 as 别名
去重 DISTINCT 和 限制 LIMIT
-- 查询不同的行，DISTINCT要放在列名前面，去重
SELECT DISTINCT 列名 from 表名
-- 限定结果的,LIMIT
SELECT * FROM student LIMIT 5; --只显示前5行
SELECT * FROM student LIMIT 3,4; -- 从行3开始取4行
SELECT * FROM student LIMIT 4 OFFSET 3; --从行3开始取4行
数据库的列（表达式）
SELECT VERSION() -- 查询系统版本（函数）
SELECT 100+3*5 AS 计算结果 -- 用来计算（表达式）
SELECT @@auto_increment_increment -- 查询自增的步长
ALTER TABLE 表名 AUTO_INCREMENT=3; -- 修改表的自动增量值
-- SELECT 表达式 from 表名
数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量
4.3 where子句
组合WHERE子句建立更高级的搜索条件
语法：WHERE 表达式
| 逻辑操作符 | 表达 | 含义 | 结果 |
|---|---|---|---|
| AND | a AND b a && b | a且b | 两个为真，结果才为真 |
| OR | a OR b a || b | a或b | 其中一个为真，结果为真 |
| NOT | NOT a ! a | 非a | 真为假 |
| NOT IN | 否定后面的关键字 | ||
| NOT BETWEEN | |||
| NOT EXISTS |
注意：
- 建议用圆括号来表明计算次序
- 尽量用英文来表达
- 没有括号的情况下： IN > NOT > AND > OR
-- 满足条件的查询(where)
SELECT Studentno,StudentResult FROM result;
-- 查询考试成绩在95-100之间的
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100;
-- AND也可以写成 &&
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 && StudentResult<=100;
-- 模糊查询(对应的词:精确查询)
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult BETWEEN 95 AND 100;
-- 除了1000号同学,要其他同学的成绩
SELECT studentno,studentresult
FROM result
WHERE studentno!=1000;
-- 使用NOT
SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;
模糊查询：比较运算符
| 比较运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | a is null | 如果操作符为NULL，结果为真 |
| IS NOT NULL | a is not null | 如果操作符为NULL，结果为假 |
| BETWEEN | a between b and c | 若a在bc之间，结果为真 |
| LIKE | a like b | a 匹配 b 结果为真 |
| 百分号(%) 通配符 | 表示任何字符出现任意次数 | |
| 下划线(_) 通配符 | 表示任何字符出现1次 | |
| IN | a in (a1,a2,a3…) | 假设a在a1，或者a2 |
使用通配符的技巧：
- 如果其他操作符能达到相同的目的，不要过度使用通配符。
- 把通配符放在搜索的开始处，搜索很比较慢
-- ====================模糊查询======================
-- 查询姓刘的同学的学号及姓名
-- like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘%';
-- 查询姓刘的同学,后面只有一个字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘_';
-- 查询姓刘的同学,后面只有两个字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘__';
-- 查询姓名中含有 嘉 字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%嘉%';
-- ================= in (具体的一个或者多个值) ===========
-- 查询学号为1000,1001,1002的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);
-- 查询地址在北京,南京,河南洛阳的学生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛阳');
-- =================NULL 空======================
-- 查询出生日期没有填写的同学
-- 不能直接写=NULL , 这是代表错误的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;
-- 查询出生日期填写的同学
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;
-- 查询没有写家庭住址的同学
-- NULL 与空字符串或仅仅包含空格不同
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;
4.4、联表查询
-
为什么要使用联结：
有一个关于产品的数据表，
每个产品为一条记录，记录中包含产品的名称，产品的介绍，产品的价格以及供应商的信息。
有一个供应商能够提供多种商品，那么如何存储供应商的信息，如名字，地址，手机号等。
问：那么在何处存储供应商信息?
答：分开存储
1、同一个供应商提供的产品下的供应商信息都是相同，就会造成信息重复浪费存储空间
2、供应商信息的改变，只需要改动一次
3、如果有重复数据，很难保证每次输入数据的方式都相同。- 关系表的设计就是保证把信息分解成多个表，然后通过某些常用的值互相关联
- 联结是一种机制，用来在SELECT语句关联表。
- 联结不是物理物体，它在实际的数据库表中不存在。是根据需要建立，存在于查询的执行中。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tlqlvDO1-1620037367253)(Mysql精通笔记.assets/7e3e6709c93d70cf77d6f685f2dcd100baa12b0d.png)]
连接查询
JOIN对比
-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;
/*思路:
(1):分析需求,分析查询的字段来自哪些表，（联结查询）
(2):确定使用哪种连接查询? 7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：student表中的studenNo = result表中 studentNo
*/
/* studentno这个列，两个表都有，所以要用完全限定列名 */
-- 最普通的查询
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student AS s, result AS r
WHERE s.studentNo = r.studentNo
/* 如果没有联结条件的表关系返回的结果是笛卡儿积，表一行数×表二行数 */
-- where 等值查询
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student AS s
INNER JOIN result as r
WHERE s.studentNo = r.studentNo
-- join on 连接查询，这是一种语法
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student AS s
INNER JOIN result as r
ON s.studentNo = r.studentNo
-- Right Join
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
-- Left Join
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
-- 查一下缺考的同学(左连接应用场景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL
LEFT JOIN, INNER JOIN, RIGHT JOIN的区别：
| 操作 | 描述 |
|---|---|
| INNER JOIN | 在表中存在至少一个匹配时返回行 |
| LEFT JOIN | 会从左表那里返回所有的行，即使在右表中没有匹配的行 |
| RIGHT JOIN | 会从右表那里返回所有的行，即使在左表中没有匹配的行。 |
INNER JOIN关键字
- 在表中存在至少一个匹配时返回行
- 如果 “Persons” 中的行在 “Orders” 中没有匹配，就不会列出这些行。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bqvh0Mb4-1620037367258)(Mysql精通笔记.assets/image-20210330182549955.png)]
-- 谁订购了产品，并且他们订购了什么产品？
-- 等值连接
SELECT P.LastName, P.FirstName, O.OrderNo
FROM Persons AS P, Orders AS O
WHERE P.Id_P = O.Id_P;
-- 内连接
SELECT P.LastName, P.FirstName, O.OrderNo
FROM Persons AS p
INNER JOIN Orders AS O
ON P.Id_P = O.Id_P
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hVYVcQ03-1620037367259)(Mysql精通笔记.assets/image-20210330183027011.png)]
Orders表中 id_O为5的的行的信息并不会出现。因为在Persons里的id_P并没有匹配上
LEFT JOIN关键字：
- LEFT JOIN 关键字会从左表那里返回所有的行，即使在右表 中没有匹配的行
-- 左表为Persons，右表为Orders表
SELECT P.LastName, P.FirstName, O.OrderNo
FROM Persons AS P
LEFT JOIN Orders AS O
ON P.Id_P=O.Id_P
-- 会从左表（Persons表）那里返回所有的行，即使在右表（Orders表）中没有匹配的行
-- 返回所有的行的意思是，Persons里每一行的数据都会有。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7isZh8vd-1620037367263)(Mysql精通笔记.assets/image-20210330183343779.png)]
结果分析：
- 首先左表（Persons）每一行的记录都会被返回（既每一个人名都会有），之所以会有由重复的记录是根据你的ON条件来决定的，P表的id_P为1时，对应的在O表的id_P有两条记录。
- 即使在右表 (Orders) 中没有匹配的行，也会返回。对应的数据是NULL
RIGHT JOIN关键字：
- RIGHT JOIN 关键字会右表那里返回所有的行，即使在左表中没有匹配的行。
SELECT P.LastName, P.FirstName, O.OrderNo
FROM Persons AS P
RIGHT JOIN Orders AS O
ON P.Id_P=O.Id_P
大家可以猜测一下结果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6hV03iE7-1620037367264)(Mysql精通笔记.assets/image-20210330184557590.png)]
ON与WHERE的区别：
- on条件是在生成临时表时使用的条件，条件不为真也会返回数据。
- where条件是对临时表生成后筛选，不满足条件的会被过滤掉。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
- 在结果上：
- 在left jion中使用on，不管on中的条件是否为真，都会返回左表中的记录(右表中不满足的记录返回NULL)。
- 在right jion中使用on，不管on中的条件是否为真，都会返回右表中的记录(左表中不满足的记录返回NULL)。
- 在left jion、right jion中使用where，能将不满足条件的数据筛选掉。
- 在inner join中，就结果而言，条件写在on或者where中二者的结果没有区别。
总结
-- 我要查询哪些数据 select....
-- 从哪个表查 from 表 xxx join 连接的表 on 交叉条件
-- 假设存在一种多张表传，先查询两张表，然后再慢慢增加
-- from a left join b a是左表，b是右表，以a为主
-- from a right join b a是左表，b是右表，以b为主
自连接
自连接
自己的表和自己的表连接，核心：把一张表分成两张表
/*
自连接
数据表与自身进行连接
需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
查询父栏目名称和其他子栏目名称
*/
-- 创建一个表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
-- 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');
-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`
父类
| categoryid | categoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类
| categoryid | pid | categoryName |
|---|---|---|
| 8 | 2 | 办公信息 |
| 4 | 3 | 数据库 |
| 6 | 3 | web开发 |
| 7 | 5 | ps技术 |
操作查询：查询父类与子类的关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| web开发 | |
| 美术设计 | ps技术 |
4.5、分页和排序
排序： ORDER BY
-- 排序： 升序ASC 降序 DESC
-- ORDER BY 通过哪个字段排，怎么排
SELECT s.studentNo, StudentName, SubjectName,StudentResult
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN subject sub
ON r.SubjectNo = sub.StudentNo
where subjectName = '数据库结构-1'
ORDER BY StudentResult DESC
分页：LIMIT ————限制结果
好处：好处 : (用户体验,网络传输,查询压力)
SELECT s.studentNo, StudentName, SubjectName,StudentResult
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN subject sub
ON r.SubjectNo = sub.StudentNo
where subjectName = '数据库结构-1'
ORDER BY StudentResult DESC
LIMIT 5; -- 取前5行 LIMIT 5; == LIMIT 0,5;
-- LIMIT 3,4 -- 从行3开始取4行
-- LIMIT 4 OFFSET 3; -- LIMIT 4 取4行，OFFSET 3 从行3开始
-- 第一页 limit 0,5 (1-1) *5
-- 第二页 limit 5,5 (2-1) *5
-- 第三页 limit 10,5 (3-1) *5
-- 第N页 limit （n-1）*5,5 (n-1)*5
-- 页面大小：Pagesize
-- 起始值：(n-1)*Pagesize n为当前页
-- (数据总数/页面大小)+1 = 总页数 10条数据 页面大小为3， 总页数是4
-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='JAVA第一学年'
ORDER BY StudentResult DESC
LIMIT 0,10;
4.6、子查询
子查询：嵌套在其他查询中的查询
-
在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
-
由内及外
- 可靠的方法是：逐渐进行
-
子查询返回的结果一般都是集合,故而建议使用IN关键字
-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 方法一:使用连接查询
SELECT studentno,r.subjectno,StudentResult -- 查询这些东西 学号,科目编号,成绩
FROM result r -- 从哪些表
INNER JOIN `subject` sub -- 连接查询
ON r.`SubjectNo`=sub.`SubjectNo` -- 交叉点
WHERE subjectname = '数据库结构-1' -- 过滤数据
ORDER BY studentresult DESC; -- 排序
-- 方法二:使用子查询(执行顺序:由里及外)
-- 内查询得到是'数据库结构-1'的subjectno
-- 利用内查询得到的结果当成外层查询的条件
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC;
-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
-- 方法一:使用连接查询
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等数学-2' AND StudentResult>=80
-- 方法二:使用连接查询+子查询
-- 分数不小于80分的学生的学号和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80
-- 在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname = '高等数学-2'
)
-- 方法三:使用子查询
-- 分步写简单sql语句,然后将其嵌套起来
SELECT studentno,studentname FROM student WHERE studentno IN(
SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
)
4.7、分组与分组过滤
GROUP BY
- GROUP BY 出现在WHERE 之后
- GROUP BY 出现在ORDER BY 之前
- GROUP BY 输出可能不是分组的顺序，所以常常跟上 ORDER BY来保证数据的排序
-- 查询不同课程的平均分,最高分,最低分
-- 前提:根据不同的课程进行分组
SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result AS r
INNER JOIN `subject` AS s
ON r.subjectno = s.subjectno
GROUP BY r.subjectno -- 通过什么字段来分组
HAVING 平均分>80;
/*
where写在group by前面.
要是放在分组后面的筛选
要使用HAVING..
因为having是从前面筛选的字段再筛选，而where是从数据表中的>字段直接进行的筛选的
*/
HAVING：过滤分组
- HAVING支持所有WHERE操作符
-
WHERE过滤指定的是行而不是分组
-
WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤
SELECET子句的顺序
| 子句 | 说明 | 是否必须使用 |
|---|---|---|
| SELECT | 要返回的列或表达式 | 是 |
| FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
| WHERE | 行级过滤 | 否 |
| GROUP BY | 分组说明 | 仅在按组计算聚焦时使用 |
| HAVING | 组级过滤 | 否 |
| ORDER BY | 输出排序顺序 | 否 |
| LIMIT | 要检索的行数 | 否 |
5、函数
5.1、常用函数
5.1.1 数值处理函数
SELECT ABS(-8); /*绝对值*/
SELECT CEILING(9.4); /*向上取整*/
SELECT FLOOR(9.4); /*向下取整*/
SELECT RAND(); /*随机数,返回一个0-1之间的随机数*/
SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/
SELECT MOD(m, n) -- m%n m mod n 求余 10%3=1
SELECT Pi() /*返回圆周率，显示3.141593*/
truncate(x, d) -- 截取d位小数
format(x, d) -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
round(x) -- 四舍五入去整
5.1.2 字符串函数
/*返回字符串包含的字符个数*/
SELECT CHAR_LENGTH('狂神说坚持就能成功');
/*返回串的长度*/
SELECT LENGTH('狂神说坚持就能成功')
-- CONCAT(str1, str2,str3....) /*合并字符串,参数可以有多个*/
SELECT CONCAT('我','爱','程序');
-- INSERT(str, pos, len, new_str) /*替换字符串,从某个位置开始替换某个长度*/
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱');
-- REPLACE(str, from_str, to_str) /*替换字符串*/
SELECT REPLACE('狂神说坚持就能成功','坚持','努力');
-- SUBSTR(str,pos,[len]) /*截取字符串,开始和长度*/
-- len如果不写的话，从pos位置含pos，截取后面剩余的
SELECT SUBSTR('狂神说坚持就能成功',4,6);
/*小写*/
SELECT LOWER('KuangShen');
/*大写*/
SELECT UPPER('KuangShen');
/*从左边截取*/
SELECT LEFT('hello,world',5);
/*从右边截取*/
SELECT RIGHT('hello,world',5);
/*反转*/
SELECT REVERSE('狂神说坚持就能成功');
/*去掉串左边的空格*/
SELECT LTRIM(' 你好 世界')
/*去掉串右边的空格*/
SELECT RTRIM(' 你好 世界 ')
charset(str) -- 返回字串字符集
lpad(string, length, pad) -- 重复用pad加在string开头,直到字串长度为length
rpad(string, length, pad) --在str后用pad补充,直到长度为length
repeat(string, count) -- 重复count次
strcmp(string1 ,string2) -- 逐字符比较两字串大小
instr(string ,substring) -- 返回substring首次在string中出现的位置
-- 查询姓周的同学,改成邹
SELECT REPLACE(studentname,'周','邹') AS 新名字
FROM student WHERE studentname LIKE '周%';
5.1.3 日期和时间处理函数
SELECT NOW(); /*获取当前日期和时间*/
SELECT LOCALTIME(); /*获取当前日期和时间*/
SELECT CURRENT_DATE(); /*获取当前日期*/
SELECT CURDATE(); /*获取当前日期*/
SELECT CURTIME(); /*获取当前时间*//
SELECT SYSDATE(); /*获取当前日期和时间*/
SELECT DATE() /*返回日期时间的日期部分*/
SELECT TIME() /*返回日期时间的时间部分*/
SELECT DAYOFWEEK(); -- 对于一个日期，返回对应的星期几
SELECT DATE_FORMAT(); --返回一个格式化的日期或时间串
-- 获取年月日,时分秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
5.1.4 系统函数
SELECT VERSION(); /*版本*/
SELECT USER(); /*用户*/
5.2、聚合函数
确定表中行数（或者满足某个条件或包含某个特定值的行）
获得表中行组的和
找出表列（或所有行或某些特定的行）的最大值、最小值和平均值
| 函数名称 | 描述 |
|---|---|
| COUNT() | 返回某列的行数 |
| SUM() | 返回数字字段或表达式列作统计，返回一列的总和 |
| AVG() | 通常为数值字段或表达列作统计，返回一列的平均值 |
| MAX() | 可以为数值字段，字符字段或表达式列作统计，返回最大的值 |
| MIN() | 可以为数值字段，字符字段或表达式列作统计，返回最小的值 |
-- AVG() 只能作用特定数值列，而且列名必须要给出。
-- 要获取多个列的平均值，必须使用多个AVG函数
-- AVG()函数忽略列值为NULL的行
SELECT AVG(StudentResult) AS 平均分 FROM result;
SELECT MAX(StudentResult) AS 最高分 FROM result;
SELECT MIN(StudentResult) AS 最低分 FROM result;
SELECT COUNT(studentname) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student; /*推荐*/
count(1)、count(*)与count(列名)的执行区别
- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。
- count(字段) 对特定列中具有值的行进行技术，忽略字段为null 的情况。即不统计字段为null 的记录。
- count(*) 对表中行的数目进行计数，不忽略表列中包含NULL的值
- count(1) 用1代表代码行，在统计结果的时候，不忽略字段为null 的记录
- count(1)和count(*)都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。
- 而count(字段)则与前两者不同，它会统计该字段不为null的记录条数。
执行效率：
- 在表没有主键时，count(1)比count(*)快
- 有主键时，主键作为计算条件，count(主键)效率最高；
- 若表格只有一个字段，则count(*)效率较高
5.3、数据库级别的加密MD5（扩展）
MD5即Message-Digest Algorithm 5（信息-摘要算法5），用于确保信息传输完整一致。
MD5不是加密算法，使用 加密算法 加密后的消息是完整的，并且基于解密算法后，可以恢复原始数据。
而MD5算法 得到的消息是不完整的，并且无法通过摘要的数据也无法得到原始数据。
MD5算法 对比 加密算法 缺少了解密过程，因此，MD5算法不是加密算法
CREATE TABLE `testmd5` (
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO testmd5 VALUES(1,'kuangshen','123456'),(2,'qinjiang','456789')
update testmd5 set pwd = md5(pwd);
-- 单独对某个用户加密
INSERT INTO testmd5 VALUES(3,'kuangshen2','123456')
update testmd5 set pwd = md5(pwd) where name = 'kuangshen2';
-- 插入新的数据加密
INSERT INTO testmd5 VALUES(4,'kuangshen3',md5('123456'));
-- 查询用户登录
SELECT * FROM testmd5 WHERE `name`='kuangshen' AND pwd=MD5('123456');
6、事务
ACID：原子性，一致性，隔离性，持久性
原子性（Atomicity）
要么都成功，要么都失败，事务不可分割。
一致性（Consistency）
事务前后数据要完整一致
隔离性（Isolation）
多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离
持久性（Durability）
事务一旦被提交则不可逆，数据就会持久化到数据库。如果数据库发生故障也不会对其有任何影响
隔离所导致的一些问题
脏读：
指一个事务读取了另外一个事务未提交的数据。
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
虚读（幻读）：
在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
隔离级别
set transaction isolation level 设置事务隔离级别
select @@tx_isolation 查询当前事务隔离级别
| 设置 | 描述 |
|---|---|
| Serializable（串行化） | 可避免脏读、不可重复读、虚读情况的发生。 |
| Repeatable read（可重复读） | 可避免脏读、不可重复读情况的发生。 |
| Read committed（读已提交） | 可避免脏读情况发生。 |
| Read uncommitted(读未提交) | 最低级别，以上情况均无法保证。 |
基本语法
-- 使用set语句来改变自动提交模式
set transaction isolation level -- 设置事务隔离级别
select @@tx_isolation -- 查询当前事务隔离级别
SET autocommit = 0; /*关闭*/
SET autocommit = 1; /*开启*/
-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交
-- 开始一个事务,标记事务的起始点
START TRANSACTION
-- 提交一个事务给数据库
COMMIT
-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK
-- 还原MySQL数据库的自动提交
SET autocommit =1;
-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点
案例说明
-- 创建数据库
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
-- 创建表
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入数据
INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)
-- 模拟转账：事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务，一旦提交就会被持久化
ROLLBACK; -- 回滚
SET autocommit = 1; -- 恢复自动提交
7、索引
7.1、索引分类
索引：官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度
索引的作用
- 提高数据查询的效率
- 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间，降低成本
- 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
- 确保数据的唯一性
- 全文检索字段进行搜索优化
索引分类
- 主键索引(Primary Key)
- 唯一的标识，主键不可重复，只能有一个列作为主键。
- 索引列中 的值必须是唯一的，不允许有空值。
- 唯一索引(Unique Key)
- **作用：**避免同一个表中某数据列中的值重复
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
- 与主键索引的区别是：
- 主键索引只能有一个
- 唯一索引可能有多个
- 普通索引(Index)
- 作用 : 快速定位特定数据
- 默认的，index，key关键字来设置
- 全文索引(FullText)
- 作用：快速定位特定数据
- myisam支持 FULLTEXT类型的全文索引
- innodb不支持FULLTEXT类型的全文索引,但是innodb可以使用sphinx插件支持全文索引
- 只能用于CHAR , VARCHAR , TEXT数据列类型
- 适合大型数据集
- 空间索引
- 前缀索引
- 在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。
- 其他
- 单列索引
- 组合索引
- 最左匹配原则，在条件允许的情况下，使用组合索引代替多个单列索引
7.2 测试索引
基础语法
-- 索引使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后，增加索引
-- 显示所有的索引信息
SHOW INDEX FROM 表名;
-- 增加一个全文索引 （索引名） 列名
ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）
-- 删除索引
ALTER TABLE 数据库名.表名 DROP 索引类型 索引名;
-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM student; --非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');
参考文章：关于expain的内容【MySQL优化】——看懂explain
索引测试
- 准备过程
-- 建表
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万条数据
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();
- 无索引测试
SELECT * FROM app_user WHERE NAME = '用户9999'; -- 0.737 sec
SELECT * FROM app_user WHERE NAME = '用户9999'; -- 0.753 sec
SELECT * FROM app_user WHERE NAME = '用户9999'; -- 0.732 sec
EXPLAIN SELECT * FROM app_user WHERE NAME = '用户9999'
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ChnUEGsX-1620037367268)(Mysql精通笔记.assets/image-20210401181031712.png)]
- 有索引测试
CREATE INDEX idx_app_user_name ON app_user(NAME);
EXPLAIN SELECT * FROM app_user WHERE NAME = '用户9999'
SELECT * FROM app_user WHERE NAME = '用户9999'; -- 0.002 sec
SELECT * FROM app_user WHERE NAME = '用户9999'; -- 0.001 sec
SELECT * FROM app_user WHERE NAME = '用户9999'; -- 0.003 sec
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ClhMXqvg-1620037367270)(Mysql精通笔记.assets/image-20210401181104153.png)]
索引在数据量小的时候，用处不大，但是在大数据的时候，区别十分明显
7.3、索引原则
参考文章：关于索引的数据结构：MySQL索引背后的数据结构及算法原理
索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引
8、权限管理和备份
8.1、用户管理
使用SQLyog创建用户，授予权限，看命令
命令
--创建用户
CREATE USER 用户名IDENTIFIED BY '密码'
-- 删除用户
DROP USER 用户名
--重命名用户
RENAME USER old_user TO new_user
--设置密码
--mysql8修改密码命令 ALTER USER "用户"@"主机" IDENTIFIED BY "新密码"
--设置当前用户的密码 SET PASSWORD = PASSWORD('密码')
--设置指定用户密码 SET PASSWORD FOR 用户名 = PASSWORD('密码')
--用户授权/添加用户
GRANT 权限列表 ON 表名 TO 用户名@ip [IDENTIFIED BY [PASSWORD] 'password']
- all privileges 表示所有权限， 这个用户除了给别人授权，都能干
- *.* 表示所有库的所有表
- 库名.表名 表示某库下面的某表
例如：GRANT ALL PRIVILEGES ON *.* 'test_user'@'%' '%'意味任何ip都可以登录
-- 撤消权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 -- 撤销所有权限
-- 查看指定用户的权限
SHOW GRANTS FOR 用户名
SHOW GRANTS FOR root@localhost;
-- 查看当前用户权限
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
-- ROOT用户的权限是：GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 刷新权限
flush privileges;
权限解释
-- 权限列表
ALL [PRIVILEGES] -- 设置除GRANT OPTION之外的所有简单权限
ALTER -- 允许使用ALTER TABLE
ALTER ROUTINE -- 更改或取消已存储的子程序
CREATE -- 允许使用CREATE TABLE
CREATE ROUTINE -- 创建已存储的子程序
CREATE TEMPORARY TABLES -- 允许使用CREATE TEMPORARY TABLE
CREATE USER -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW -- 允许使用CREATE VIEW
DELETE -- 允许使用DELETE
DROP -- 允许使用DROP TABLE
EXECUTE -- 允许用户运行已存储的子程序
FILE -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX -- 允许使用CREATE INDEX和DROP INDEX
INSERT -- 允许使用INSERT
LOCK TABLES -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS -- 允许使用SHOW FULL PROCESSLIST
REFERENCES -- 未被实施
RELOAD -- 允许使用FLUSH
REPLICATION CLIENT -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT -- 允许使用SELECT
SHOW DATABASES -- 显示所有数据库
SHOW VIEW -- 允许使用SHOW CREATE VIEW
SHUTDOWN -- 允许使用mysqladmin shutdown
SUPER -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE -- 允许使用UPDATE
USAGE -- “无权限”的同义词
GRANT OPTION -- 允许授予权限
8.2、MySQL备份
为什么要备份：
- 保证重要的数据不丢失
- 数据转移
MySQL数据备份的方式：
-
直接拷贝物理文件 data文件夹
-
在Sqlyog可视化工具中手动导出
- 在想要导出的表或者库中，右键选择备份，转储
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OG4Y4yzT-1620037367273)(Mysql精通笔记.assets/image-20210401210902317.png)]
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-T9gwuxvI-1620037367275)(Mysql精通笔记.assets/image-20210401210917762.png)]
-
命令行导出 mysqldump
# mysqldump -h主机 -u用户 -p密码 数据库 表名 > 物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school student > D:/a.sql # mysqldump -h主机 -u用户 -p密码 数据库 表1 表2 表3 > 物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school student result > D:/b.sql # mysqldump -h主机 -u用户 -p密码 数据库 > 物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school > D:/c.sql #导入 # 登录的情况下，切换到指定数据库 # source 备份文件 source d:/a.sql
9、规范数据库设计
9.1、为什么要设计
当数据库比较复杂的时候，我们就需要设计了
良好的数据库设计：
- 节省内存空间 ------>数据冗余，浪费空间
- 保证数据的完整性----->数据库插入和删除都会麻烦，异常【屏蔽使用武力外键】
- 方便开发
软件项目开发周期中数据库设计 :
-
需求分析阶段: 分析客户的业务和数据处理需求
-
概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.
设计数据库步骤
-
收集信息
- 与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.
-
标识实体[Entity]
- 标识数据库要管理的关键对象或实体,实体一般是名词
- 标识每个实体需要存储的详细信息[Attribute]
- 标识实体之间的关系[Relationship]
9.2、三大范式
为什么需要数据规范化？
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式
参考文章1：数据库三大范式
参考文章2：数据库三大范式通俗解释
第一范式 (1st NF)
- 原子性：保证每一列不可再分
第二范式(2nd NF)
-
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。
-
二范式就是要有一个主键，要求其他字段必须完全依赖于主键而非部分主键。属性完全依赖于主键
-
一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。一张表中出现数据重复就可以将其拆分成两个表
- 比如我们为了评价一批苹果的好坏，给苹果上了编号1——10000。那么这个【编号】就是我们要描述的对象，其余的【颜色】、【香味】等列名，全部都是用来描述苹果的。
- 同时，随着主键值的变化，其它列名也必须随之而变化。而因为主键可以是几个列名的组合，比如以【食品种类】+【编号】作为一个主键，那么在这种情况下，其它列名（副键）必须同时被这两个列名影响，少一个的话，不行。
- 即一张表格只描述一个对象（主键），其它列名（副键）与对象之间相互完全依赖。
第三范式(3rd NF)
-
如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.
-
三范式就是要消除传递依赖，第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
-
第三范式在第二范式的基础上，非主属性（字段）之间是相互独立的没有关系的，也就是说谁也不能决定谁。
-
比如说大学分了很多系（中文系、英语系、计算机系……），这个系别管理表信息有以下字段组成：
系编号，系主任，系简介，系架构。
那么再回到学生信息表，张三同学的年龄、性别、学号都有了，我能不能把他的系编号，系主任、系简介也一起存着？
如果你问三范式，当然不行，因为三范式不同意。
因为系编号，系主任、系简介已经存在系别管理表中，你再存入学生信息表，就是冗余了。
三范式中说的传递依赖，就出现了。
这个时候学生信息表中，系主任信息是不是依赖于系编号了？而这个表的主键可是学号啊！所以按照三范式，处理这个问题的时候，学生表就只能增加一个系编号字段。这样既能根据系编号找到系别信息，又避免了冗余存储的问题。 -
比如，你想要做【编号】、【颜色】、【香味】、【香味得分】、【香味评级】这种列名，那么你要另开一张专门描述香味的表格。
再比如，你想要做【学号】、【姓名】、【性别】、【班级】、【班主任】这种列名，由于【班主任】受【班级】影响，那么你要另开一张专门描述班级的表格。
即，副键与副键之间，不能存在依赖关系。
-
规范化和性能的关系
-
为满足某种商业目标 , 数据库性能比规范化数据库更重要
-
在数据规范化的同时 , 要综合考虑数据库的性能
-
通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间
-
通过在给定的表中插入计算列,以方便查询
总结：三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。
10、JDBC（重点）
10.1、数据库驱动
程序会通过数据库驱动和数据库打交道
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RI9ZywvD-1620037367277)(Mysql精通笔记.assets/image-20210402001145472.png)]
10.2、JDBC
SUN公司为了简化开发人员（对数据库的统一）操作，提供了一个（Java操作数据库）的规范，俗称JDBC
这些规范的实现由具体的厂商去做~
而对于开发人员来说，我们只需要掌握JDBC接口的操作即可
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-q7BesuRs-1620037367278)(Mysql精通笔记.assets/image-20210402001507223.png)]
java.sql
javax.sql
还需要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar
10.3、第一个JDBC程序
时区问题
报Communicate links failure USESSL=false
serverTimezone=UTC
步骤总结：
1、加载驱动
2、连接数据库 DriverManager
3、获得执行sql的对象 Statement
4、获得返回的结果集
5、释放连接
部分内容参考：
B站狂神说
1187
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
