学习笔记(二)"/>
MySQL 学习笔记(二)
MySQL 学习笔记(二)
1、MySQL 数据管理
1.1、 外键
方式一:在创建表的时候,增加约束
CREATE TABLE `grade`(`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8-- 学生表的gradeid 字段要去引用年级表的gradeid字段
-- 1.定义外键key
-- 2.给这个外键添加约束(执行引用)
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`),
KEY `FK_gradeid`(`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
方式二:
或者也可以直接在UI上添加
方式三:
或者也可以在创建表的时候不添加,创建之后再添加约束
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=utf8ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 那个表(那个字段)
以上的操作都是物理外键,数据库级别的外键,不建议使用
最佳实践
- 数据库就是张单纯的表,只用来存数据,只有行数据和列字段
- 我们想使用多张表的数据,想使用外键(程序实现)
1.2、 DML语言
数据库意义:数据存储和数据管理
DML 语言: 数据操作语言
- Insert
- Update
- Delete
1.3、 添加
-- 插入语句(添加)
-- insert into 表名([字段名1,字段名2,字段名3]) values ('值1'),('值2'),('值3'),(....) 其中值1 代表的是[字段名1,字段名2,字段名3]
INSERT INTO `grade` (`gradename`) VALUES ('大一')-- 主键自增可以省略 如果不写表的字段,他就会一一匹配
INSERT INTO `grade` VALUES ('大三')
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES ('大三','NULL')-- 一般写插入语句,一定要数据和字段一一对应-- 插入多个数据
INSERT INTO `grade` (`gradename`) VALUES ('大四'),('大五') INSERT INTO `student` (`name`,`pwd`,`sex`,`gradeid`) VALUE ('张三','aaaaaaa','男','2')
1.4、 修改
update 条件 set 原值=修改值
-- 修改学员名字 带条件会修改条件对应 否则会默认将所有记录都改掉
UPDATE `student` SET `name` ='杨杨' WHERE id=1;
UPDATE `student` SET `name` ='杨杨';
-- 语法 update table_name set column_name='value' where condition='value'-- 修改多个属性,逗号隔开
UPDATE `student` SET `name`='杨',`email`='xx@...' WHERE id=1;
条件:where 子句 运算符 id等于某个值
操作符会返回boolean
操作符 | 意义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | id=1 | 仅修改id等于1的行 |
<> 或!= | 不等于 | id<>1 | 仅不修改id等于1的行 |
BETWEEN | 区间 | BETWEEN 2 AND 3 | 2,3之间 |
AND | 与 | id>1 AND id ❤️ | 仅修改2 |
OR | 或 | id>3 OR id<2 | 修改4以上和1 |
-- 通过多个条件定位数据 AND连接或者OR连接
UPDATE `student` SET `name`='长江七号' WHERE `name`='杨杨' AND `sex`='男'
注意:
- value 可以是一个具体的值,也可以是一个变量 比如CURRENT_TIME
- 多个value之间 逗号隔开
1.5、 删除
delete 命令
语法:delete from table_name [where condition=values]
-- 删除数据
DELETE FROM `student` WHERE id=1;
TRUNCATE 命令
清空数据表
- 相同点:都能删除数据,都不会删除表结构
- 不同
- TRUNCATE 重新设置自增列 计数器会归0
- TRUNCATE 不会影响事务
-- 测试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` -- 不会改变自增
TRUNCATE TABLE `test`
DELETE删除的问题:重启数据库,现象
- INNODB 自增列会从1开始(存在内存当中,断电即失)
- MyISAM 继续从上一个自增量开始 (存在文件当中,不会丢失)
2、 DQL查询数据
data query language 数据查询语言
- 所有的查询操作都用它 Select
- 简单的查询,复杂的查询都实现
- 数据库中最核心的语言
2.1、指定查询字段
-- 查询全部的学生 SELECT 字段 FROM 表
SELECT * FROM student
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
-- 别名,给结果起一个名字 AS 也可以给字段起别名 也可以给表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 姓名 FROM student
-- 函数 Concat(a,b)
SELECT CONCAT('姓名 :' ,`StudentName`) AS 新名字 FROM student
语法: SELECT 字段… FROM 表
有时候,列名字不是那么的明显,我们可以取别名 AS
去重 distinct
-- 查询一下那些同学参加了考试,(从成绩)
SELECT * 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 表达式
2.2、where条件子句
作用:检索数据中符合条件的值
搜索的条件:由一个或多个表达式组成
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b (a&&b) | 与,两个都为真,结果为真 |
or || | a or b(a||b) | 或,其中一个为真,结果为真 |
not ! | not a (!a) | 非,真为假,假为真 |
尽量使用英文字母
-- 查询考试成绩在95 到100之间的
SELECT studentNo,studentresult FROM result
WHERE StudentResult>=95 AND Studentresult<=100-- 查询除了1000号学生之外的同学的成绩
SELECT studentNo,`StudentResult` FROM result
WHERE studentNo!=1000;
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
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 `StudentNo`,`Studentname` FROM `student`
WHERE StudentgName LIKE '刘%'-- 查询姓张的同学 名字后面只有一个字符SELECT `StudentNo`,`Studentname` FROM `student`
WHERE StudentName LIKE '张_'-- 查询姓张的同学 名字后面只有两个字的
SELECT `StudentNo`,`Studentname` FROM `student`
WHERE StudentName LIKE '张__'-- in 一个具体或者多个的值 没有正则表达式匹配 是完全匹配
-- 查询1001,1002,1003 号学员
SELECT `StudentNo` ,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);-- 查询在北京的学生
SELECT `StudentNo` ,`StudentName` FROM `student`
WHERE `Address` IN ('北京');-- null not null
-- 查询地址为NULL的学生SELECT `StudentNo` ,`StudentName` FROM `student`
WHERE `Address` ='' OR `Address` IS NULL;-- 查询有出生日期的同学 不为空SELECT `StudentNo` ,`StudentName` FROM `student`
WHERE `BornDate` ='' IS NOT NULL;
2.3、联表查询
-- ============联表查询 join================
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
SELECT * FROM student
SELECT * FROM result/* 思路
1. 分析需求,分析查询的字段来自那些表
2. 确定使用那种连接查询 7中
确定交叉点 (这两个表中那个数据是相同的)
判断的条件: 学生表中的 studentNo= 成绩表 studentgNo*/
-- join on 连接查询
-- where 等值查询-- INNER JOIN
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNO=r.studentNO-- Right Join
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNO=r.studentNO-- Left Join
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNO=r.studentNO
-- 查询缺考的同学
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNO=r.studentNO
WHERE Studentresult IS NULL
操作 | 描述 |
---|---|
inner join | 两个表中都要有才会返回 |
left join | 返回左表中所有的值,即使右表中没有匹配(以左表为基准) |
right join | 返回右表中所有的值,即使左表中没有匹配(以右表为基准) |
-- 查询参加了考试的同学信息:学号 学生姓名 科目名称 分数
-- 这里需要注意join 和on是固定的 中间不能插入where where最好放在整个的最后面
/* 思路
1. 分析需求,分析查询的字段来自那些表 student/result/subject 三表联查
2. 确定使用那种连接查询 7中
确定交叉点 (这两个表中那个数据是相同的)
判断的条件: 学生表中的 studentNo= 成绩表 studentgNo
*/
SELECT s.studentNO,studentName,SubjectName,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNO=r.studentNO
INNER JOIN `subject` AS ss
ON r.subjectNO=ss.subjectNO
-- 考虑需要查那些数据
-- 从那几个表里查 找到基准
自连接
自己和自己连接:一张表拆成两张一样的表
父类:
categoryid | categoryid |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类
pid | categoryid | categoryid |
---|---|---|
3 | 4 | 数据库 |
8 | 2 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | PS技术 |
操作:查询父母对应的子类关系
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库 |
美术设计 | PS技术 |
软件开发 | web 开发 |
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE b.pid=a.`categoryid`
ORDER BY a.`categoryid`
一些联表查询的练习
-- 查询学员所属的年级 (学号,学生的姓名,年级名称)
SELECT studentNo,studentNAME,`gradename`
FROM student AS s
INNER JOIN `grade` AS g
ON s.`gradeid`= g.`gradeid`-- 查询科目所属的年级 科目名称 年级名称
SELECT `subjectname`,`gradename`
FROM `subject` AS s
INNER JOIN `grade` AS g
ON s.gradeid=g.gradeid
-- 查询参加了 高等数学考试的同学 信息 学号 学生姓名 科目名 分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
INNER JOIN `result` r
ON s.studentno=r.studentno
INNER JOIN `subject` AS sub
ON r.subjectno=sub.subjectno
WHERE subjectname='高等数学-1'
2.4、分页和排序
-- 排序: 升序ASC, 降序DESC
-- 查询的结构根据成绩 降序排序SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
INNER JOIN `result` r
ON s.studentno=r.studentno
INNER JOIN `subject` AS sub
ON r.subjectno=sub.subjectno
WHERE subjectname='高等数学-1'
ORDER BY studentresult DESC -- ASC是升序 DESC是降序
-- 为什么要分页?
-- 缓解数据库压力,给人的体验更好
-- 分页,每页只显示5条数据
-- 网页应用:
-- LIMIT 0,5
-- LIMIT 5,5
-- pagesize: 页面大小
-- (n-1)*pagesize: 起始值
-- n: 当前页
-- 数据总数/页面大小=总页数
-- 语法: limit (查询起始值,pagesize)
2.5、子查询
where (整个值是计算出来的)
本质: 在where语句中嵌套一个子查询语句
where(select *)
注意:子查询的内容必须只有一行,如果存在多行会报错,需要加any修饰
-- ===================where=====
-- 1、查询高等数学-1的所有考试结果(学号,科目编号,成绩),降序排列-- 方式一:通过连接查询
SELECT r.`StudentNo`,`SubjectName`,`StudentResult`
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE Subjectname='高等数学-1'
ORDER BY studentresult DESC-- 方式二:适用子查询 由里及外SELECT r.`StudentNo`,`subjectno`,`StudentResult`
FROM `result` AS r
WHERE subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname='高等数学-1'
)-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT `StudentNo`,`StudentName`
FROM Student AS s
WHERE studentno=ANY(
SELECT studentno FROM `result`
WHERE studentresult>=80
) -- 这个地方必须要加any 不然会报错-- 采用联表查询的方式
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM Student AS s
INNER JOIN `result` AS r
ON s.studentno=r.`studentno` AND r.`studentresult`>80-- 在这个基础上限定科目是高等数学-1
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM Student AS s
INNER JOIN `result` AS r
ON s.studentno=r.`studentno` AND r.`studentresult`>80
WHERE `subjectno`=(
SELECT subjectno FROM `subject`
WHERE `subjectname`='高等数学-1'
)-- inner join
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM Student AS s
INNER JOIN `result` AS r
ON r.`studentresult`>=80 AND r.`studentno`=s.`studentno`
INNER JOIN `subject` AS sub
ON sub.`subjectname`='高等数学-1'-- 嵌套!
SELECT DISTINCT `StudentNo`,`StudentName`
FROM Student
WHERE studentno IN ( -- 这里要注意是in
SELECT studentno FROM `result`
WHERE studentresult>=80 AND subjectNo=ANY( -- 这里由于是=要加any 嵌套再嵌套实在精彩!
SELECT subjectno FROM `subject`
WHERE subjectname='高等数学-1'
)
)-- 查询 C语言-1 前5名同学的成绩的信息 (学号姓名分数) SELECT DISTINCT s.`studentno`,`studentname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.studentno=r.`studentno` AND r.`subjectno`=(
SELECT subjectno FROM `subject`
WHERE `subjectname`='C语言-1'
)
ORDER BY `studentresult`
LIMIT 0,5
3、 MySQL函数
3.1、 常用函数
-- ==============常用函数=============---- 数学运算
SELECT ABS(-8)
SELECT CEIL(6.2)
SELECT FLOOR(6.2)
SELECT RAND()
SELECT SIGN() -- 判断一个数的符号
-- 字符串函数
SELECT CHAR_LENGTH ('杨杨') -- 字符串长度
SELECT CONCAT('我','爱数据库') -- 拼接字符串
SELECT INSERT('我爱数据库',2,2,'超级') -- 查询,替换
SELECT LOWER('A')
SELECT UPPER('a')SELECT INSTR('yangyang','s') -- 第一次出现的位置
SELECT REPLACE('yangyang','yang','young')-- 替换
SELECT SUBSTRING('yangyang',2)SELECT REVERSE('YOUNG') -- 反转-- 查询姓张 的同学,名字 赵
SELECT REPLACE(studentname,'赵','张') FROM student
WHERE studentname LIKE '张%'-- 时间函数
SELECT CURRENT_DATE()-- 获取当前日期
SELECT CURDATE() -- 获取当日期
SELECT NOW() -- 获取当前时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()
3.2、聚合函数
-- ====聚合函数========
SELECT COUNT(studentname) FROM student; -- 查出有多少条记录 会忽略掉所有的NULL值SELECT COUNT(*) FROM student; -- 不会忽略NULL值
SELECT COUNT(1) FROM result; -- 不会忽略NULL值SELECT SUM(`studentresult`) AS 总和 FROM result
SELECT AVG(`studentresult`) AS 平均分 FROM result
SELECT MIN(`studentresult`) AS 最低分 FROM result-- 查询不同课程的平均分、最高分和最低分
-- 核心: 根据不同的课程分组SELECT `subjectname`,AVG(studentresult)AS 平均分,MAX(studentresult),MIN(studentresult)
FROM result AS r
INNER JOIN `subject` AS sub
ON r.subjectno=sub.subjectno
GROUP BY r.subjectno -- 通过什么字段来分组
HAVING 平均分>80 -- 这个地方不能使用where!!! 聚合函数分组过滤用HAVING
函数名称 | 描述 |
---|---|
count | 计数 |
sum | 求和 |
avg | 平均 |
$\cdots $ | ⋯ \cdots ⋯ |
3.3、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=utf8ALTER TABLE `testmd5` MODIFY `pwd` VARCHAR(50)
-- 明文密码
INSERT INTO testmd5 VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd)
-- 插入地时候加密INSERT INTO testmd5 VALUES(4,'张三',MD5('123456')),(5,'李四','123456'),(6,'王五','123456')
-- 如何校验?将用户传递进来的密码,进行md5加密,然后比对加密后的值SELECT * FROM testmd5 WHERE `name`='张三' AND pwd=MD5(MD5('123456'))
3.4、Select总结
SELECT (ALL | DISTINCT)
{* | table.* | {table.field1{AS a} {table.field2 AS b}}
FROM TABLE_NAME{AS table_alias}
{LEFT |RIGHT|INNER JOIN table_name2} -- 联合查询
[WHERE ..] -- 指定结果需要满足的条件
[GROUP BY ..] -- 通过那个字段来分组
[HAVING ..] -- 过滤分组后的信息 同where 一样 只是位置不同
[ORDER BY ..] -- 通过那个字段来排序 DESC ASC
[LIMIT startindex pagesize] -- 分页
4、事务
将一组SQL放在一个批次中进行
事务原则:ACID 原子 原子性 一致性 隔离性 持久性
- 原子性:
要么都成功,要么都失败
- 一致性:
事务前后数据的完整性要保持一致
- 持久性:
事务一旦提交则不可逆,被持久化到数据库中
- 隔离性:
多个用户并发访问数据库时,数据库为每一个用户开启的服务,不能被其他事务的操作数据所干扰,事务之间要相互隔离
隔离所导致的一些问题:
- 脏读
- 不可重复度
- 虚读
执行事务
-- ================事务===================
-- mysql 是默认开启事务自动提交的
SET autocommit=0 /*关闭*/
SET autocommit=1 /*开启*/-- 手动处理事务
SET autocommit=0 /*关闭*/ -- 先关
-- 事务开启
START TRANSACTION -- 标记一个事务的开始,从这个之后的sql都在同一个事务内
INSERT xx
INSERT xx-- 提交: 持久化 成功就提交COMMIT
-- 回滚: 回到原来的样子
ROLLBACK -- 事务结束
SET autocommit=1 /*开启*/-- 再打开
SAVEPOINT 保存点名 -- 设置事务的一个保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE 保存点名 -- 删除保存点名
模拟银行转账事务
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(8,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8INSERT 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; -- 恢复
5、索引
5.1、索引分类
提取句子主干,就可以得到索引的本质;索引是数据结构
- 主键索引 primary key
- 唯一标识,主键不可重复,只能有一个列作为主键
- 唯一索引 unique key
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识为唯一索引
- 常规索引 key/index
- 默认的,index、key关键字来设置
- 全文索引 Fulltext
- 在特定的数据库引擎下才有
-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后,增加索引
-- 显示所有的索引信息
USE school
SHOW INDEX FROM student
-- 增加一个索引
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`)
SHOW INDEX FROM student-- 分析 sql 执行的情况
EXPLAIN SELECT * FROM student; -- 常规索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('周');
5.2 测试索引
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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'-- 插入 100万条数据
DELIMITER $$ -- 写函数之前必须要写
CREATE FUNCTION mock_data()
RETURNS INT DETERMINISTIC -- 8.0版本需要添加DETERMINISTIC
BEGINDECLARE 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),'123456@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`= '用户999999';
-- id_表明_字段名
-- create index 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);
EXPLAIN SELECT * FROM app_user WHERE `name`='用户999999';
索引在小数据量时,用处不大,但是在大数据时十分明显!
5.3、索引原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
Hash类型的数据结构
B树:InnoDB所对应
6、权限管理
6.1、用户管理
SQLyog 可视化操作
SQL命令操作
用户表:mysql.user
本质:对用户表进行增删改查
-- 创建用户
CREATE USER young IDENTIFIED BY '123456'-- 用户授权
-- 除了给别人授权都能干
GRANT ALL PRIVILEGES ON *.* TO young-- 查看权限
SHOW GRANTS FOR young
SHOW GRANTS FOR root@localhost-- 撤销权限 revoke 在那个库撤销
REVOKE ALL PRIVILEGES ON *.* FROM young
7、MySQL备份
- 保证重要的数据不丢失
- 数据转移A->b
备份方式
- 直接拷贝物理文件
- 在sqlyog这种可视化工具中手动导出
- 使用命令行 mysqldump
mysqldump -hlocalhost -uroot -p"password" database [table_name] > {directory}
导入先登入
use school
source 备份文件
8、规范数据库设计
当数据库比较复杂的时候,我们就需要设计了
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据库插入和删除都会麻烦,异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计:
- 节省内存空间
- 保证数据库的完整性
- 方便开发系统
软件开发中,关于数据库的设计
- 分析需求:分析业务和需要处理的数据库的需求
- 概要设计:设计关系图E-R图
8.1、 三大范式
为什么需要数据规范化?
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式
第一范式(1NF)
原子性:保证每一列都不可再分
第二范式(2NF)
前提:满足第一范式
每张表只描述一件事情
第三范式(3NF)
前提:满足第一范式和第二范式
确保数据表中每一列数据和主键直接相关,二不能间接相关
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标,数据库的性能更加重要
- 在规范性能的问题的时候,需要适当的考虑一下规范性
- 故意给某些表增加一些冗余的字段,从多表查询变为单表查询
- 故意增加一些计算列 (从大数据量降低为小数据量的查询:索引)
更多推荐
MySQL 学习笔记(二)
发布评论