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)
前提:满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
规范数据库的设计
规范性和性能的问题
关联查询的表不得超过三张表
-
考虑商业化的需求和目标(成本,用户体验)数据库的性能更加重要
-
在规范性能的问题的时候,需要适当的考虑一下规范性
-
故意给某些表增加一些冗余的字段。(从多表查询中变为单表查询)
-
故意增加一些计算列(从大数据库降低为小数据量的查询:索引)