MySQL基础

MySQL

操作数据库(了解)

CREATE DATABASE [IF NOT EXISTS] westos; #创建数据库
DROP DATABASE IF EXISTS westos #删除数据库
USE `school` #使用数据库   如果你的表名或字段名是一个特殊字符，就需要带` `
SHOW DATABASE #查看数据库

列的数据类型

数值

int 4个字节
decimal 字符串形式的浮点数金融计算的时候，一般是使用decimal

字符串

varchar 可变字符串 0~65535
text 文本串 2^16-1

时间日期

java.util.Date

date YYYY-MM-DD，日期格式
time HH：mm：ss，时间格式
datetime YYYY-MM-DD HH：mm：ss 最常用的时间格式
timestamp 时间戳 1970.1.1到现在的毫秒数

null

数据库的字段属性（重点）

Unsigned

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

zerofill

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

自增

自动在上一条记录的基础上+1（默认）
通常用来设计唯一的主键，必须是整数类型
可以自定义设计主键的起始值和步长

非空

假设设置为not null，如果不给它赋值，就会报错！
null，如果不填写值，默认就是null!

默认

设置默认的值！

创建数据库表(重点)

CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `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

SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构

MyISAM和InnoDB区别

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为2倍

常规使用操作

MYISAM 节约空间，速度较快
INNODB 安全性高，事务的处理，多表多用户操作

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库

本质还是文件的存储

MySQL引擎在物理文件上的区别

InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
MYISAM对应文件
1. *.frm 表结构的定义文件
2. *.MYD 数据文件（data）
3. *.MYI 索引文件（index）

设置数据库表的字符集编码

CHARSET=utf8

在my.ini中配置默认的编码

character-set-server=utf8

修改和删除数据表字段

修改

ALTER TABLE teacher RENAME AS teacher1 -- 修改表名
ALTER TABLE teacher1 ADD age INT(11) -- 增加表的字段
-- 修改表的字段（重命名，修改约束）
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 重命名

删除

ALTER TABLE teacher1 DROP age1 -- 删除表的字段
DROP TABLE IF EXISTS teacher1 -- 删除表的字段

MySQL数据管理★

外键（了解）

CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `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 '出生日期',
    `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_gradeid` FOREIGN KEY('gradeid') REFERENCES `grade`(`gradeid`);

-- 删除有外键关系的表，必须先删除引用别人的表(从表)，再删除被引用的表(主表)

以上为物理外键，数据库级别的外键，不建议使用（避免数据库过多造成困扰，了解即可）

最佳实践

数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
我们想使用多张表的数据，想使用外键（用程序实现）

DML语言（全部记住）

数据操作语言

insert
update
delete

添加

INSERT INTO `grade`(`gradename`) VALUES('大四')
INSERT INTO `grade` VALUES('大三')
INSERT INTO `grade`(`gradename`) VALUES('大二'),('大一')
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES('张三','aaaaaa','男')
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES('李四','aaaaaa','男'),('王五','aaaaaa','男')

注意事项：

字段和字段之间使用英文逗号隔开
字段可以省略，但是后面的值必须要一一对应
可以同时插入多条数据，VALUES后面的值，需要使用逗号隔开

修改

UPDATE `student` SET `name`='狂神' WHERE id = 1
UPDATE `student` SET `name`='长江7号'
UPDATE `student` SET `name`='狂神',`email`='24736743@qq.com' WHERE id = 1
UPDATE `student` SET `name`='长江7号' WHERE `name`='狂神44' AND sex='女'
UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='狂神44' AND sex='女'

操作符	含义	范围	结果
=	等于	5=6	false
<>或!=	不等于	5<>6	true
>
<
<=
>=
BETWEEN...and...	在某个范围内	[2,5]
AND	&&	5>1and1>2	false
OR	\|\|	5>1or1>2	true

注意事项

colnum_name是数据库的列，尽量带上``
筛选的条件，如果没有指定，则会修改所有的列
value是一个具体的值，也可以是一个变量
多个设置的属性，使用英文逗号隔开

删除

delete命令

DELETE FROM `student` WHERE id = 1
DELETE FROM `student` -- 避免这样写

TRUNCATE命令

完全清空一个数据库表，表的结构和索引约束不会变

TRUNCATE `student`

delete和TRUNCATE区别

相同点：都能删除数据，都不会删除表结构
不同点
- TRUNCATE 重新设置自增列计数器会归零
- TRUNCATE 不会影响事务

DELETE删除的问题，重启数据库现象

InnoDB 自增列会从1开始（存在内存中，断电即失）
MyISAM 继续从上一个增量开始（存在文件中的，不会丢失）

DQL查询数据（最重点）

DQL

所有的查询操作都用它 Select
简单复杂都能查
数据库中最核心的语言，最重要的语句
使用频率最高的语句

SELECT * FROM student -- 查询全部的学生
SELECT * `StudentNo`,`StudentName` FROM student -- 查询指定字段
SELECT * `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS s -- 可以给字段起别名，也可以给表起别名
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student

有的时候，列名字不是那么的见明知意。我们起别名 AS

去重 distinct

SELECT FROM * result -- 查询全部的考试成绩
SELECT `StudentNo` FROM result -- 查询有哪些同学参加了考试
-- 发现重复数据 去重
SELECT DISTINCT `StudentNo` FROM result

数据库的列

SELECT VERSION() -- 查询系统版本（函数）
SELECT 100*3-1 AS 计算结果 -- 用来计算 （表达式）
SELECT @@auto_increment_increment -- 查询自增的步长 （变量）
-- 学员考试成绩+1分查看
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM result

数据库中的表达式：文本值，列，NULL，函数，计算表达式，系统变量...

SELECT 表达式 FROM 表

Where条件子句

作用：检索数据中符合条件的值

搜索的条件由一个或者多个表达式组成，结果都为布尔值

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与，两个都为真，结果为真
or \|\|	a or b a\|\|b	逻辑或，其中一个为真，则结果为真
Not !	not a ! a	逻辑非，真为假，假为真！

-- AND
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult>=95 AND StudentResult<=100

-- 模糊查询
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100

-- 除了1000号学生之外的同学的成绩
SELECT studentNo,`StudentResult` FROM result
WHERE studentNo!=1000;

SELECT studentNo,`StudentResult` FROM result
WHERE NOT studentNo=1000;

模糊查询操作符详解（重点）：比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为NULL，结果为真
IS NOT NULL	a is not null	如果操作符为not nll，结果为真
BETWEEN	a between b and c	若a在b和c之间，则结果为真
Like	a like b	SQL匹配，如果a匹配b，则结果为真
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 '刘_'

-- 查询名字中间有嘉字的同学 %嘉%

-- 查询1001，1002，1003号学员
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003)

-- 查询在北京的学生 （IN是具体的一个或多个值）
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN ('安徽','河南洛阳')

-- 查询地址为空的学生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE address='' OR address IS NULL

-- 查询有出生日期的同学 不为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL

联表查询

join

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result
/*思路
1. 分析需求，分析查询的字段来自哪些表
2. 确定使用哪种连接查询？7种
确定交叉点
判断条件：学生表中的 studentNo = 成绩表 studentNo
*/
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 s.studentNO = r.studentNO

-- Left Join
SELECT s.studentNO,studentName,SubjectNO,StudentResult
FROM student s
LEFT JOIN result r
ON s.studentNO = r.studentNO

操作	描述
Inner join	如果表中至少有一个匹配，就返回行
Left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

-- 查询缺考的同学
SELECT s.studentNO,studentName,SubjectNO,StudentResult
FROM student s
LEFT JOIN result r
ON s.studentNO = r.studentNO
WHERE StudentResult IS NULL

-- join on  连接查询
-- where  等值查询

-- 查询参加了考试的同学信息

SELECT s.studentNo,studentName,SubjectName,`StudentResult`
FROM student s
RIGHT JOIN result r
ON r.studentNo = s.studentNo
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo

自连接(了解)

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

父类

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps设计

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父子信息
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`

-- 查询学员所属的年级（学号，学生的姓名，年级名称）
SELECT studentNo,studentName,`GradeName`
FROM student s
INNER JOIN `grade` g
ON s.`GradeID` = g.`GradeID`

-- 查询科目所属的年级（科目名称，年级名称）
SELECT `SubjectName`,`GradeName`
FROM `subject` sub 
INNER JOIN `grade` g
ON sub.`GradeID` = g.`GradeID`

-- 查询参加了数据库结构-1考试的同学信息：学号，学生姓名，科目名，分数
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.`SubjectNo`
WHERE subjectName = '数据库结构-1'

分页和排序

排序

-- 排序order by  升序ASC 降序DESC
-- 查询的结果根据成绩降序排列
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.`SubjectNo`
WHERE subjectName = '数据库结构-1'
ORDER BY StudentResult DESC

分页

缓解数据库压力，给人的体验更好，和瀑布流相对

-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第N页  limit  (n-1)*pageSize, pageSize
-- pageSize:页面大小
-- (n-1)*pageSize:起始值
-- n:当前页
-- 数据总数/页面大小 = 总页面

-- 查询JAVA第一学年 课程成绩排名前十，并且分数要大于80的学生信息(学号，姓名，课程名称，分数)
SELECT `StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE SubjectName = 'JAVA第一学年' AND StudentResult>=80
ORDER BY StudentResult DESC
LIMIT 0,10

子查询

where(值是固定的，这个值是计算出来的)

本质：在where语句中嵌套一个子查询语句

-- 查询数据库结构-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

-- 方式二：使用子查询
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM `result` r
WHERE SubjectNo = (
    SELECT SubjectNo FROM `subject` 
    WHERE SubjectName = '数据库结构-1'
)
ORDER BY StudentResult DESC

-- 查询所有数据库结构-1的学生学号
SELECT SubjectNo FROM `subject` WHERE SubjectName = '数据库结构-1'

-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE `StudentResult`>=80

-- 在这个基础上增加一个科目，高等数学-2
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE `StudentResult`>=80 AND `SubjectNo` = (
    SELECT SubjectNo FROM `subject` 
    WHERE `SubjectName` = '高等数学-2'
)

-- 再改造 嵌套查询
SELECT StudentNo,StudentName FROM student WHERE StudentNo IN(
    SELECT StudentNo FROM result WHERE StudentResult>=80 AND SubjectNo = (
        SELECT SubjectNo FROM `subject` WHERE `SubjectName` = '高等数学-2'
    )
)

MySQL函数

常用函数

-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整  10
SELECT FLOOR(9.4) --向下取整 9
SELECT RAND() -- 返回一个0~1之间的随机数
SELECT SIGN(-10) -- 判断一个数的符号 -1

-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 10
SELECT CONCAT('我','爱','你们') -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') 
-- 超级热爱编程helloworld 从某个位置开始替换某个长度
SELECT LOWER('KuangShen') -- kuangshen
SELECT UPPER('KuangShen') -- KUANGSHEN
SELECT INSTR('kuangshen','h') -- 7
SELECT REPLACE('狂神说坚持就能成功','坚持','努力') --替换出现的指定字符串
SELECT SUBSTR('狂神说坚持就能成功',4,6) -- 坚持就能成功
SELECT REVERSE('狂神说坚持就能成功') -- 反转

-- 查询姓周的同学，改为邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE studentname LIKE '周%'

-- 时间和日期函数（记住）
SELECT CURRENT_DATE() -- 获取当前日期 2021-03-09
SELECT NOW() -- 2021-03-09 13:14:22
SELECT LOCALTIME()
SELECT SYSDATE()
SELECT YEAR(NOW())

-- 系统
SELECT SYSTEM_USER()
SELECT USER() -- root@localhost
SELECT VERSION()

聚合函数（常用）

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()
MIN()
......

-- 聚合函数
SELECT COUNT(studentname) FROM student  -- 会忽略所有的null值
SELECT COUNT(*) FROM student -- 不会忽略所有的null值
SELECT COUNT(1) FROM student -- 不会忽略所有的null值

SELECT SUM(`StudentResult`) AS 总和 FROM result
SELECT AVG(`StudentResult`) AS 平均分 FROM result

-- 查询不同课程的平均分，最高分，最低分， 平均分大于80
SELECT SubjectName, AVG(StudentResult) AS 平均分,MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNO
GROUP BY r.SubjectNo  -- 通过什么字段来分组
HAVING 平均分>80
 
-- 分组和过滤

数据库级别的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,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1

-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))

-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')

事务

事务原则：ACID原则（原子性，一致性，隔离性，持久性）（脏读，幻读...）

原子性：两个步骤要么一起成功，要么一起失败

一致性：针对一个事务操作前与操作后的状态一致

持久性：事务结束后的数据不随着外界原因导致数据丢失（操作前后服务器宕机或者断电）

隔离性：针对多个用户同时操作，主要是排除其他事务对本次事务的影响

脏读：一个事务读取了另外一个事务未提交的数据

不可重复读

幻读（虚读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致（一般是行影响，多了一行）

执行事务

-- mysql 是默认开启事务自动提交的
SET autocommit = 0 /* 关闭 */
SET autocommit = 1 /* 开启（默认的） */

-- 手动处理事务
SET autocommit = 0
-- 事务开启
START TRANSACTION 
INSERT xx
INSERT xx
-- 提交：持久化(成功)
COMMIT 
-- 回滚：回到原来的样子(失败)
ROLLBACK
-- 事务结束
SET autocommit = 1

SAVEPOINT -- 设置保存点（了解）
ROLLBACK TO SAVEPOINT
RELEASE SAVEPOINT

模拟场景

-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf_8_general_ci
USE shop

CREATE TABLE `account`(
    `id` INT(3) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(30) NOT NULL, 
    `money` DECIMAL(9,2) NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account(`name`,`money`)
VALUES ('A',2000.00),('B',10000.00)

-- 模拟转账

SET autocommit = 0
START TRANSACTION 
UPDATE account SET money=money-500 WHERE NAME = 'A'
UPDATE account SET money=money+500 WHERE NAME = 'B'
COMMIT;
ROLLBACK;
SET autocommit = 1

索引

索引是帮助MySQL高效获取数据的数据结构

主键索引（PRIMARY KEY）
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引 (UNIQUE KEY)
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
常规索引 (KEY/INDEX)
- 默认的
全文索引 (FullText)
- 在特定的数据库引擎下才有，MyISAM
- 快速定位数据

-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个索引
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`);

-- EXPLAIN 分析SQL执行的状况

EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');

测试索引

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万数据
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	
	WHILE i<num DO
		-- 插入语句 如下
		SET i = i+1;
	END WHILE;
	RETURN i;
END;

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))

SELECT mock_data();

SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.993 sec
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 1.098 sec
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.788 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';  -- rows 991749

CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.001 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';  -- rows 1

索引原则

索引不是越多越好
不要对进程变动数据加索引
小数据量的表不需要加索引
索引一般加在常用来查询的字段上

索引的数据结构

权限管理和备份

用户管理

SQL命令操作

用户表：mysql.user

本质：对这张表进行增删改查

CREATE USER kuangshen IDENTIFIED BY '123456' -- 创建用户

-- 修改当前用户密码
SET PASSWORD = PASSWORD('123456')

-- 修改指定用户密码
SET PASSWORD FOR kuangshen = PASSWORD('111111')

-- 重命名
RENAME USER kuangshen TO kuangshen2

-- 用户授权(全部的权限，除了给别人授权)
GRANT ALL PRIVILEGES ON *.* TO kuangshen2

-- 查看权限
SHOW GRANTS FOR kuangshen2
SHOW GRANTS FOR root@localhost

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

-- 删除用户
DROP USER kuangshen

MySQL备份

为什么要备份：

保证重要的数据不丢失
数据转移

MySQL 数据库备份方式

直接拷贝物理文件
在Sqlyog这种可视化工具中手动导出
使用命令行导出 mysqldump 命令行

mysqldump -hlocalhost -uroot -p123456 school student result >D:/a.sql

# 导入
# 登录的情况下，切换到指定的数据库
source  d:/a.sql

规范数据库设计

设计数据库步骤（个人博客）

收集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 友链表（友链信息）
- 自定义表（系统信息，某个关键的字或者一些主字段） key value
标识实体（把需求落地到每个字段）
标识实体之间的关系
- 写博客：user --> blog
- 创建分类：user --> category
- 关注：user --> user
- 友链：links
- 评论：user --> user --> blog

三大范式（了解）

为什么需要数据规范化

信息重复
更新异常
插入异常
删除异常

三大范式

第一范式

要求数据库表的每一列都是不可分割的原子数据项

第二范式（前提：满足第一范式）

第三范式

规范性和性能的问题

关联查询的表不得超过三张表

考虑商业化的需求和目标（成本、用户体验！）数据库的性能更加重要
在规范性能的问题的时候，需要适当的考虑一下规范性！
故意给某些表增加一些冗余的字段（从多表查询变为单表查询）
故意增加一些计算列（从大数据量降低为小数据量的查询）

posted @ 2021-03-11 16:03 Barrymeng 阅读(92) 评论(0) 收藏举报

刷新页面返回顶部

Barrymeng