Mysql使用
1.mysql命令行
mysql -uroot -p123456 -- 连接数据库 update mysql.user set authentication_string=password(‘123456’) where user=‘root’ and Host = ‘localhost’;-- 修改用户密码 flush privileges;-- 刷新权限 --------------------------------- -- 所有的语句都使用;结尾 show databases;-- 查看所有的数据库 mysql> use school--切换数据库：use 数据库名 如果表名特殊需要加` ` Database changed show tables; -- 查看数据库中所有的表 describe student; -- 显示数据库中表的信息 create database westos; --创建一个数据库 exit;-- 退出链接 -- 单行注释（SQL本来的注释） /*(SQL的多行注释) hello */
创建数据库
`create database if not exists `school`--如果不存在就创建 `AUTO_INCREMENT`--自增主键
删除数据库
drop database if exists `school`--如果存在就删除
查看数据库
1.show databases --查看所有的数据库
修改添加删除表名字段
-- ALTER TABLE 旧表名 RENAME AS 新表名 ALTER TABLE school RENAME AS schools; --修改表名 -- ALTER TABLE 表名 ADD 字段名 列属性 ALTER TABLE schools ADD age INT(11); --增加表的字段 -- 修改表的字段 (重名名,修改约束!) -- ALTER TABLE 表名 MODIFY 字段名 列属性 ALTER TABLE schools MODIFY age VARCHAR(11) --修改约束 -- ALTER TABLE 表名 CHANGE 字段名 新字段名 列属性 ALTER TABLE school CHANGE age age1 int(1) --字段重命名 -- 删除表的字段 drop删除 --ALTER TABLE 表名 drop 字段名; ALTER TABLE schools drop age; -- 删除表 (如果存在在删除) DROP TABLE if exists schools;
查询数据库表
去重: distinct
作用: 去除select查询出来的结果中重复的数据, 重复的数据只显示一条
select distinct class_hour from `subject`;数据库的列(表达式)
select class_hour+1 from `subject`; -- 直接+就行select 100*6-6 AS 计算结果 -- 可以用来计算 (表达式)select version() -- 查询系统版本(函数) select @@auto_increment_increment -- 查询自增的步长(增量)where 条件子句
作用:检索数据中
符合条件的值逻辑运算符
select 字段 from 表 where ? ? ?模糊查询
运算符 语法 描述 is null a is null 如果操作符为null, 结果为真 is not null a is not null 如果操作符不为null, 结果为真 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 `student_name` from student where `student_name` like '赵%'; -- 查询名字是赵, 后面只有一个字的同学 select `student_name` from student where `student_name` like '赵_'; -- 查询名字是赵, 后面有三个字的同学 select `student_name` from student where `student_name` like '赵___'; -- 查询名字中间有夹字的同学 select `student_name` from student where `student_name` like '%夹%';in是子查询中查询具体的值
select `student_name`,student_no,`sex` from student where `student_name` in ('啊夹啊','赵滋滋滋','门三');-- 查询不是不是空置的名字 select `student_name`,student_no,`sex` from student where `student_name` is not null;简单的模糊查询
select * from tray_info where addtime like "'%" + trayinfo.addtime +"'"连表查询
-- inner join 内连接 -- right join 右连接 -- left join 左连接
-- concat(字段名:???) 是个函数
select CONCAT('姓名: ',student_name)AS 名字 from student; -- concat是把名字加到查询的数据里面
插入数据库表
insert into `表名`(列) values ('值'),
insert into `表名` values('值')
删除数据库表
-- 清空这张表的数据 delete from `school` -- 删除id为3的用户 delete from `school` where id = 3 -- 清空这张表的数据 truncate truncate school; drop table if txists `表名`--如果存在就删除 创表的时候会用
delete 和 truncate 的区别?
相同点是: 都能删除数据,都不会删除表结构
不同点是: delete自增的计数器不会为归零
truncate的计数器会归零, 重新设置自增列, 不会影响事务
delete删除的问题,重启数据库的 现象
innoDB 自增列会重1开始(存在内存当中的,断点即失)
mylsam 继续从上一个自增量开始(存在文件中的,不会丢失)
修改数据库表
update `表名` set `字段名` ='值' where id= 操作哪个用户 update `表名` set `字段名` ='值' where id= Between 3 and 6 set 时间值 可以=CURRENT_TIME(当前时间)这个变量 当前时间戳
| 操作符 | 含义 | 范围 |
|---|---|---|
| = | 等于 | |
| <>或者!= | 不等于 | |
| > | 小于 | |
| => | 等于小于 | |
| < | 大于 | |
| =< | 等于大于 | |
| or | 或者 || | |
| and | 我和你 && | |
| between...and.... | 在某个范围内 |
数值
tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等大小的数据 3个字节
int 标准的整数 4个字节
bigint 较大的数据 8个字节
float 浮点数 4个字节
double 浮点数 8个字节
decimal 字符串形式的浮点数 电商的用这个
字符串
char 字符串类型 0-255
varchar 可变字符串 0-65535
tinytext 微型文本 小文本
text 文本串 大文本
时间日期
java.util.Date
date: YYYY-MM-DD 日期格式
time : HH:mm:ss 时间格式
datetime: YY-MM-DD HH:mm:ss 最常用的时间格式
timestamp 时间戳 1999-10-12到现在的毫秒值
year 年份表示
数据库的字段属性
unsigned: 无符号的整数 生命了该列不能声明为负数
zerofill: 0填充 不足的为数 使用0来填充 (int)3, 5 ---005 宽度为3
每个表都应该有以下字段 /* id version --乐观锁 delete --删除 gmt_create --创建时间 gmt_update --修改时间 */
-- 创建school的sql语句 create table if not EXISTS `school`( `id` int(10) not null AUTO_INCREMENT comment 'id', `name` varchar(30) default null comment '名字', `email` varchar(30) DEFAULT null comment '邮箱', `sex` varchar(3) not null default '男' comment '性别', `age` int(3) default null comment '年龄', `version` int(3) default '0' comment '乐观锁', PRIMARY KEY(`id`) )
格式
create table [if not EXISTS] `school`( `字段名` 类型() 是否为空 索引 注解 )表类型 字符集设置 注解 auto_increment 自增 if not exists 是否存在, default 设置默认值,comment 注解,primary key 设置主键,engine=innodb default=utf8 charset=utf8 不设置的话就会出现乱码
查看数据库的语句
SHOW CREATE DATABASE test;--查看数据库语句 SHOW CREATE TABLE school;--查看数据表语句 desc school;--查看表的结构
数据库表的类型
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据化锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大 约MYISAM的2倍 |
常规使用操作:
MYISAM 节约空间, 速度较快
INNODB 安全性高, 事物的处理, 连表多用户操作
在物理空间存储的位置
所有的数据文件都存在data目录下 一个文件夹一个数据库
数据库本质还是文件的存储!
MQSQL引擎在物理文件上的区别
INNODB: 在数据库表中只有一个*.frm文件,以及上级目录下的 ibdata1文件
MYISAM: -.frm表结构的定义文件, *.MYD数据文件 *.MYI索引文件index
外键
物理外键：删除有外键关系的表的时候， 必须先删除引用的表（从表），在删除被引用的表（主表）
-- 创建外键的方式二：数据表创建完后，修改从表属性，添加外键，关联主表 -- ALTER TABLE 表 ADD CONSTRAINT `约束名` FOREIGN KEY (`作为外键的列`) REFERENCES `哪个表` (`哪个字段`); ALTER TABLE student ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
DML语言就是数据库
数据库字段的意思
select ---从哪查 where ---指定结果需满足的条件 group by ---指定结果按照哪几个字段来分组 having ---过滤分组的记录必须满足的次要条件 order by ---指定查询记录按一个或者多个条件倒排序 limit ---指定查询的记录从哪条至哪条 asc ---升序 desc ---降序 distinct ---（抵死真可特）去重 join --外连接(outer join) 笛卡尔积 内连接用于返回满足连接条件的记录；而外连接则是内连接的扩展，它不仅会满足连接条件的记录，而且还会返回不满足连接条件的记录。 外连接的“OUTER”关键字可以省略不写。 PRIMARY KEY --设置主键iD DECIMAL(6,2) --amount列最多可以存储6位数字，小数位数为2位; 因此，amount列的范围是从-9999.99到9999.99 explain --select前面加这个 可以看到索引的查询多少条 查询索引执行情况
自连接及联表查询 小案例
自连接
自己的表和自己的表连接，核心：==一张表拆为两张一样的表即可==
-- 创建表
-- unsigned 无符号
-- auto_increment=9 自增的起始值
DROP TABLE IF EXISTS `category` ;
CREATE TABLE `category` (
`category_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`category_name` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`category_id`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
-- 插入值
INSERT INTO `category`(`category_id`,`pid`,`category_name`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');
SELECT * FROM `category`;
父类
| pid | category_id | category_name |
|---|---|---|
| 1 | 2 | 信息技术 |
| 1 | 3 | 软件开发 |
| 1 | 5 | 美术设计 |
子类
| pid | category_id | category_name |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | ps技术 |
操作：查询父类对应的子类关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
-- 查询父子信息，把一张表看为两个一模一样的表 SELECT a.`category_name` AS '父栏目',b.`category_name` AS '子栏目' FROM `category` AS a, `category` AS b WHERE a.`category_id`=b.`pid`;
分页和排序
排序
-- 排序： 升序 ASC 降序 DESC -- ORDER BY 通过那个字段排序，怎么排 -- 查询的结果根据成绩降序 排序 SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result` FROM student stu INNER JOIN `result` res ON stu.`student_no` = res.`student_no` INNER JOIN `subject` sub ON res.`subject_no`=sub.`subject_no` WHERE sub.`subject_name`='数据结构-1' ORDER BY `student_result` DESC; -- 100w -- 为什么要分页 -- 缓解数据库压力，给人更好的体验 瀑布流 -- 分页，每页只显示五条数据 -- 语法 ： limit 起始值，页面的大小 -- 网页应用：当前，总的页数，每页大小 -- LIMIT 0,5 1~5 -- LIMIT 1,5 2~6 -- LIMIT 6,5 SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result` FROM student stu INNER JOIN `result` res ON stu.`student_no` = res.`student_no` INNER JOIN `subject` sub ON res.`subject_no`=sub.`subject_no` WHERE sub.`subject_name`='数据结构-1' ORDER BY `student_result` DESC LIMIT 1,5; -- 第一页 limit 0,5 (1-1)*5 -- 第二页 limit 5,5 (2-1)*5 -- 第三页 limit 10,5 (3-1)*5 -- 第N页 limit 10,5 (n-1)*pageSize,pageSize -- pageSize,页面大小 -- (n-1)*pageSize，起始值 -- n，当前页 -- 总页数 = (数据总数%页面大小==0)? (数据总数/页面大小) : (数据总数/页面大小 + 1) -- 查询科目高等数学-2，课程成绩排名前十的学生，并且分数要大于60的学生信息（学号，姓名，课程名称，分数） SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result` FROM student stu INNER JOIN `subject` sub ON stu.`grade_id`=sub.`grade_id` INNER JOIN `result` res ON sub.`subject_no`=res.`subject_no` WHERE sub.`subject_name`='高等数学-2' AND res.`student_result`>60 ORDER BY res.`student_result` LIMIT 0,10;
mysql常用函数
官网：MySQL :: MySQL 8.0 Reference Manual
SELECT ABS(-8); -- 绝对值
SELECT CEILING(9.4) ;-- 向上取整
SELECT FLOOR(9.4);-- 向下取整
SELECT RAND(); -- 返回一个0~1之间的随机数
SELECT SIGN(-10); -- 判断一个数的符号，0 返回0 负数返回-1 正数返回1
-- 字符串函数
SELECT CHAR_LENGTH('哈哈'); -- 字符串长度
SELECT CONCAT('我','爱','你'); -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱'); -- 插入，替换
SELECT LOWER('ZYY'); -- 小写字母
SELECT UPPER('zyy'); -- 大写字母
SELECT INSTR('zyy','y'); -- 返回第一次出现的子串的索引
SELECT REPLACE('坚持就能成功','坚持','努力'); -- 替换出现的指定字符串
SELECT SUBSTR('坚持就能成功', 5, 2); -- 返回指定的子字符串（源字符串，截取的位置，截取的长度）
SELECT REVERSE('清晨我上马'); -- 反转
-- 时间和日期函数（记住！）
SELECT CURRENT_DATE(); -- 获取当前日期
SELECT CURDATE(); -- 获取当前日期
SELECT NOW(); -- 获取当前的时间
SELECT LOCALTIME(); -- 获取本地时间
SELECT SYSDATE(); -- 获取系统时间
SELECT YEAR(NOW()); -- 年
SELECT MONTH(NOW()); -- 月
SELECT DAY(NOW()); -- 日
SELECT HOUR(NOW()); -- 时
SELECT MINUTE(NOW()); -- 分
SELECT SECOND(NOW()); -- 秒
-- 系统
SELECT SYSTEM_USER();
SELECT USER();
SELECT VERSION();
聚合函数及分组过滤
| 函数名称 | 描述 |
|---|---|
| count() | 计数 |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
-- 聚合函数 -- 都能统计 表中数据 -- count(字段) 会忽略所有的null值(想查询一个表中有多少个记录，就使用这个count()) 没有主键也用这个 SELECT COUNT(student_name) FROM student; -- COUNT(*) 不会忽略所有的null值 本质计算行数 SELECT COUNT(*) FROM student; -- COUNT(1) 不会忽略所有的null值 本质计算行数 SELECT COUNT(1) FROM student; SELECT SUM(student_result) AS '总和' FROM result; SELECT AVG(student_result) AS '平均分' FROM result; SELECT MAX(student_result) AS '最高分' FROM result; SELECT MIN(student_result) AS '最低分' FROM result; -- 查询不同课程的平均分，最高分，最低分 SELECT sub.subject_name AS '课程', AVG(res.student_result) AS '平均分', MAX(res.student_result) AS '最高分', MIN(res.student_result) AS '最低分' FROM result res INNER JOIN `subject` sub ON res.`subject_no`=sub.`subject_no` GROUP BY res.`subject_no` HAVING AVG(res.student_result) >80;
拓展之数据库级别的md5加密
什么是MD5?
主要增加算法复杂度和不可逆性。
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`(`id`,`name`,`pwd`)
VALUES
(1,'张三','123456'),
(2,'李四','123456'),
(3,'王五','123456');
SELECT * FROM `testmd5`;
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=2;
-- 插入的时候加密
INSERT INTO `testmd5`(`id`,`name`,`pwd`)
VALUES
(4,'小明',MD5('123456'));
-- 如何校验，将用户传递进来的密码，进行MD5加密，然后比对加密后的值
SELECT * FROM `testmd5` WHERE `name`='小明' AND pwd = MD5('123456');
事务ACID原则、脏读、不可重复读、幻读
参考博客:事务ACID理解_dengjili的专栏-CSDN博客_acid
1.什么是事务
==要么都成功，要么都失败==
将一组sql放到一个批次中取执行
事务原则：ACID原则 原子性 、一致性、隔离性、持久性 （脏读，幻读。。。）
1.原子性（Atomicity）
要么都成功，要么都失败
2.一致性（Consistency）
事务前后的数据完整性要保持一致
下图操作前和操作后的总和都是1000
3.持久性（Durability）
事务一旦移交不可逆，被持久化到数据库中
4.隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
隔离所导致的一些问题
4.1脏读：
1、在事务A执行过程中，事务A对数据资源进行了修改，事务B读取了事务A修改后的数据。
2、由于某些原因，事务A并没有完成提交，发生了RollBack（回滚）操作，则事务B读取的数据就是脏数据。
这种读取到另一个事务未提交的数据的现象就是脏读(Dirty Read)。
4.2不可重复读：
事务B读取了两次数据资源，在这两次读取的过程中事务A修改了数据，导致事务B在这两次读取出来的数据不一致。
这种**==在同一个事务中==，前后两次读取的数据不一致的现象就是不可重复读(Nonrepeatable Read)
4.3虚读(幻读)
事务B前后两次读取同一个范围的数据，在事务B两次读取的过程中事务A新增了数据，导致事务B后一次读取到前一次查询没有看到的行。
幻读和不可重复读有些类似，但是幻读强调的是集合的增减，而不是单条数据的更新
事务的隔离级别
读未提交
读未提交(Read Uncommitted)，是最低的隔离级别，所有的事务都可以看到其他未提交的事务的执行结果。只能防止第一类更新丢失，不能解决脏读，可重复读，幻读，所以很少应用于实际项目。
读已提交
读已提交(Read Committed)，在该隔离级别下，一个事务的更新操作只有在该事务提交之后，另外一个事务才可能读取到同一笔数据更新后的结果。可以防止脏读和第一类更新丢失，但是不能解决可重复和幻读的问题。
可重复读（重要）
可重复读(Repeatable Read)，mysql默认的隔离级别。在该隔离级别下，一个事务多次读同一个数据，在这个事务还没有结束时，其他事务不能访问该数据（包括了读写），这样就可以在同一个事务内两次读到的数据是一样的。可以防止脏读、不可重复读、第一类更新丢失，第二类更新丢失的问题，不过还是会出现幻读。
串行化
串行化(Serializable)，这是最高的隔离级别。它要求事务序列化执行，事务只能一个接着一个的执行，不能并发执行。在这个级别，可以解决上面提到的所有并发问题，但是可能导致大量的超时现象和锁竞争，通常不会用这个隔离级别。
扩展：回滚机制
在mysql中，恢复机制是通过回滚日志（undo log）实现的，所有的事务进行的修改都会先记录到这个回滚日志中，然后在堆数据库中的对应进行写入。
mysql的事务是由redo和undo的，redo操作的所有信息都是记录到重做日志（redo_log）中，也就是说当一个事务做commit操作时，需要先把这个事务的操作写到redo_log中，然后在把这些操作flush到磁盘上，当出现故障时，只需要读取redo_log，然后在重新flush到磁盘就行了。
而对于undo就比较麻烦，mysql在处理事务时，会在数据共享表空间里申请一个段就做segment段，用保存undo信息，当在处理rollback，不是完完全全的物理undo，而是逻辑undo，也就是说会之前的操作进行反操作（对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。），但是这些共享表空间是不进行回收的。这些表空间的回收需要由mysql的master thread进程进行回收。
总结
模拟场景
-- 转账
-- 创建数据库
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 使用shop数据库
USER `shop`;
-- 建表
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) 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'; -- A减500
UPDATE account SET `money`=`money`+500 WHERE `name`='B'; -- B加500
COMMIT; -- 提交事务，就会被持久化了
ROLLBACK; -- 回滚
SET autocommit = 1; -- 恢复自动提交
索引介绍及索引的分类
Msql官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。
提取句子主干，就可以得到索引的本质：索引是数据结构。
索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
-
主键索引（primary key）
-
唯一的标识，主键不可重复，只能有一个列作为主键
-
-
唯一索引 （unique key）
-
避免重复的列出现，可以重复，多个列都可以标示为唯一索引
-
-
常规索引（key/index）
-
默认的 index 或者key关键字来设置 NORMAL
-
-
全文索引（FullText）
-
在特定的数据库引擎下才有，myisam
-
快速定位数据
-
基础语法
-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student;
-- 新增一个索引 (索引名) 列名
ALTER TABLE `student` ADD UNIQUE KEY `UK_IDENTITY_CARD` (`identity_card`);
ALTER TABLE `student` ADD KEY `K_STUDENT_NAME`(`student_name`);
ALTER TABLE `student` ADD FULLTEXT INDEX `FI_PHONE` (`phone`);
-- explain 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(`phone`) AGAINST('138'); -- 全文索引
MYsql优化文章
【MySQL优化】——看懂explain_漫漫长途，终有回转；余味苦涩，终有回甘-CSDN博客_explain
SQL编程创建100万条数据测试索引
CREATE TABLE app_user (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
`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 COMMENT '创建时间',
`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
-- 插入100万数据b (函数)
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),'123345@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;
-- 函数中间的插入脚本
INSERT INTO app_user(`name`,
`email`,
`phone`,
`gender`,
`password`,
`age`)
VALUES(CONCAT('用户X'),
'123345@qq.com',
CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),
FLOOR(RAND()*2),
UUID(),
FLOOR(RAND()*100));
测试
-- 加索引前 SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.440 sec EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'; -- 创建索引 -- id_表名_字段名 索引名 -- CREATE INDEX 索引名 ON 表名(`字段名`); CREATE INDEX id_app_user_name ON app_user(`name`); -- 加索引后 SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.002 sec EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';
索引原则
-
索引不是越多越好
-
不要对经常变动的数据加索引
-
小数据量的表不需要加索引
-
索引一般加载常用来查询的字段上
索引的数据结构
Hash类型的索引
bree ：innodb的默认数据结构
CodingLabs - MySQL索引背后的数据结构及算法原理
数据库用户管理
sql 命令操作
用户表：mysql.user
本质:读这张表进行增删改查
-- 创建用户
CREATE USER zyy IDENTIFIED BY '123456';
-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('123456');
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR zyy = PASSWORD('123456');
-- 重命名 RENAME 原名子 zyy TO 新名字;
RENAME USER zyy TO newzyy;
-- 用户授权 ALL PRIVILEGES 全部的权限，库，表
-- ALL PRIVILEGES 除了给别人授权不行，其他都能干
GRANT ALL PRIVILEGES ON *.* TO newzyy;
-- 查询权限
SHOW GRANTS FOR newzyy; -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost; -- 查看root用户的权限
-- 撤销权限 REVOKE哪些权限，在哪个库，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM newzyy;
-- 删除用户
DROP USER newzyy;
mysql备份
使用命令行导出 mysqldump 命令行使用
# 一张表 mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql # 多张表 mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表名2 >物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school student result >D:/a.sql # 数据库 mysqldump -h主机 -u用户名 -p密码 数据库 >物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql # 导入 # 登录的情况下，切换到指定的数据库 # source 备份文件 # 也可以这样 mysql -u用户名 -p密码 库名<备份文件
如何设计一个项目的数据库
==当数据库比较复杂的时候，我们就需要设计了==
糟糕的数据库设计
-
数据冗余，浪费空间
-
数据库插入和删除都会麻烦、异常（屏蔽使用物理外键）
-
程序的性能差
良好的数据库设计
-
节省内存空间
-
保证数据库的完整性
-
方便我们开发系统
软件开发中，关于数据库的设计
-
分析需求，分析业务和需要处理的数据库的需求
-
概要设计：设计关系图E-R图
设计数据库的步骤（个人博客）
-
收集信息，分析需求
-
用户表（用户登录注销，用户的个人信息，写博客，创建分类）
-
-
分类表（文章分类，谁创建的）
-
文章表（文章信息）
-
评论表
-
友链表（友情链接信息）
-
自定义表（系统信息，某个关键的字，或者一些主字段）
key:value -
关注表(粉丝数)
-
说说表（发表心情， id...content...create_time）
-
标识实体（把需求落到每个字段）
-
标识实体之间的关系
-
写博客：user --> blog
-
创建分类：user --> category
-
关注：user --> user
-
友链：links
-
评论：user --> user --> blog
-
前端页面直接拿 (bbs / crm) 百度搜
数据库三大范式
为什么需要数据规范化？
-
信息重复
-
更新异常
-
插入异常
-
无法正常显示信息
-
-
删除异常
-
丢失有效的信息
-
三大范式
第一范式（1NF）
原子性：保证每一列不可再分
第二范式（2NF）
前提：满足第一范式
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
每张表只描述一件事情
第三范式（3NF）
前提：满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
规范数据库的设计
规范性和性能的问题
关联查询的表不得超过三张表
-
考虑商业化的需求和目标（成本，用户体验）数据库的性能更加重要
-
在规范性能的问题的时候，需要适当的考虑一下规范性
-
故意给某些表增加一些冗余的字段。（从多表查询中变为单表查询）
-
故意增加一些计算列（从大数据库降低为小数据量的查询：索引）
2648
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
