MYSQL
1.概念讲解
2.数据库基本属性
2.1 数据库列类型
数值:
- tinyint 比较小的数据 1个字节
- smallint 小的数据 2个字节
- midiumint 中等的数据 3个字节
- int 一般的数据 4个字节 (常用)
- bigint 较大的数据 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节(精度)
- decimal 字符串浮点数 金融计算使用
字符串
- char 字符串固定大小 0~255
- varchar 可变字符串 0~65535 String类型
- tinytext 小型文本 2^8-1
- text 大型文本 2^16-1
时间日期
- date YYYY-MM-DD 日期格式
- time HH:mm:ss 时间格式
- datetime YYYY-MM-DD HH:mm:ss 日期时间格式 较为常用
- timestramp 1970.1.1到现在时间经过的毫秒数**
- year 年份
null
- 尽量不使用NULL进行计算,结果仍为NULL(无意义)
2.2 数据库的字段属性
Unsigned:
- 无符号整数
- 声明该列不能为负数
zerofill:
- 0填充
- 数据 不足的位数使用0进行填充 int(2) 3 003
自增:
- 通常是在上一条记录加1
- 通常设置为唯一的主键 例如:index id
- 可以自定义起始值和步长
非空 NULL not null
- 如果设置为 not null ,则不赋值就会报错
- NULL, 如果未填写值,默认就是Null
拓展:阿里巴巴规范,之后做项目,实际开发中每一个表都要有一下5个字段
id 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
3.创建数据库表
3.1 创建school表
CREATE TABLE IF NOT EXISTS `school` (
`id` INT 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
基本格式:
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性][索引][注释],
`字段名` 列类型 [属性][索引][注释],
......
字段名` 列类型 [属性][索引][注释],
)[引擎][字符集][注释]
3.2 查看建表语句
- 常用命令
show create database myl --查看创建数据库的语句
show create table tb_myl; --查看创建数据库表的语句
desc tb_myl --显示表的结构
-
数据表的类型
-- 关于数据库引擎 INNODB 默认使用 MYISAM 早期使用
MYISAM | INNODB | |
---|---|---|
事务 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为2倍 |
常规使用操作
- MYISAM 节约空间,速度较快。
- INNODB 支持事务,事务处理,夺表多用户操作。
物理空间存在的位置: data\
所有的数据库文件都存放在mysql安装目录下的data文件夹下,
Mysql引擎在物理文件上的区别:
- InnoDB在数据库表中只有一个*.frm文件,以及上级目录下的ibdatda1文件
- MYISAM对应文件:
- *.frm 表结构的定义文件
- *.MYD 数据文件(data)
- *.MYI 索引文件(index)
本质上数据库表还是文件。
设置数据库默认字符集编码
- 在mysql安装目下的 my.ini中修改编码方式(以后都是这个编码方式)
- 语句加 charset=utf8
3.3修改删除表
修改
/*修改表名 LTER TABLE 原表名 RENAME AS 新表名 */
ALTER TABLE teacher RENAME AS teacher1
/* 增加表的字段 ALTER TABLE 表名 ADD 字段名 列类型 */
ALTER TABLE teacher1 ADD age INT(11)
/* 修改表的字段 (重命名 ALTER TABLE 表名 MODIFY 字段名 列类型
约束) ALTER TABLE 表名 CHANGE 原字段名 新字段名 列类型 */
ALTER TABLE teacher1 MODIFY age VARCHAR(11) /*修改约束*/
ALTER TABLE teacher1 CHANGE age age1 INT(2) /*字段重命名*/
/*删除表的字段 ALTER TABLE 表名 DROP 字段名*/
ALTER TABLE teacher1 DROP age1
删除
/*删除表*/
DROP TABLE IF EXISTS teacher1
注意:
- 所有的创建和删除操作,尽量加上判断(IF EXISTS)
- `` 字段名使用这个符号包裹
- sql关键字大小写都可以,建议小写
- 所有的符号都用英文
4.Mysql数据管理
4.1 外键(了解)
/*外键:
创建年级表
创建学生表 外键(gradeid)
1.在创建表语句时设置外键
2.在已有创建表上设置外键
*/
CREATE TABLE `grade`(
`gradeid` INT(4) NOT NULL,
`gradename` VARCHAR(20) NOT NULL,
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
/*student表增加字段 gradeid */
ALTER TABLE student ADD gradeid INT(4)
/* 2.在已有创建表上设置外键*/
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
注意:
删除带有外键的关系表时,必须先删除从表(引用的表),再删除主表(被引用的表)
上面所述都是物理外键,数据库级别的外键(不建议使用),避免数据库过多造成问题。
最佳实践:
- 数据库就是只存取数据,只有行和列。
- 想使用多张表的数据,通过程序去实现即可。
4.2 DML语言
数据库操作语言
INSERT
/* 给数据库表插入数据
格式:insert into 表名([字段名1,字段名2,字段名3]) values('值1'),('值2')......
1.插入数据时,字段名与插入数据一一对应
2.当插入数据为全部字段时,可以省略字段名的书写
3.一次可以插入多条数据,一行完整的数据用,隔开
4.当ID为自增时,可以用NULL占位
*/
INSERT INTO `student`(`name`,`pwd`) VALUES('张三','54321')
INSERT INTO `student` VALUES(2,'李四','654321','男','2000-09-04','咸阳','1025483534@qq.com')
INSERT INTO `student`
VALUES(3,'李四','654321','男','2000-09-04','咸阳','1025483534@qq.com'),
(NULL,'王五','23456','男','2020-01-01','西安','205382553@qq.com')
语法:
insert into 表名([字段名1,字段名2,字段名3]) values(‘值1’),(‘值2’)…
UPDATE
/* UPDATE 更新数据
语法: UPDATE 表名 set 字段名的值=新值 where 条件
1. 根据条件去修改数据
2.不指定条件下会修改所有的数据!(删库跑路)
3.修改多个数据时,使用逗号,隔开
4.条件中value可以是一个值,也可以是一个变量(例如 )
*/
UPDATE `student` SET `name`='孟永龙' WHERE id = 1
UPDATE `student` SET `name`='孟永龙'
UPDATE `student` SET `pwd`='11111',sex='男' WHERE id =2
UPDATE `student` SET `pwd`='11111',sex='男',birthday=CURRENT_TIME WHERE id =2
UPDATE `student` SET `name`='张三',sex='女' WHERE `id` BETWEEN 2 AND 5
where 条件的取值操作符(返回值为 boolean)
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 2=3 | false |
<>或!= | 不等于 | 2<>3 | true |
> | |||
< | |||
>= | |||
<= | |||
Between…And… | 范围之间 | [2,5] | |
And | 与 | 4>3 And 3<2 | false |
OR | 或 | 2>1 OR 1<1 | true |
语法:
update 表名 set 字段名=值,[字段名=值,…] where 条件
DELETE
语法:delete from 表名 where 条件
/* DELETE:删除数据
语法:delete from 表名 where 条件
1.根据条件去删除
2.如果未指定条件,则会清空表
清空表:
delete from 表名
*/
CREATE TABLE `stn`(
`id` INT(4),
`name` VARCHAR(20)
)
DELETE FROM `student` WHERE `pwd`='23456'
清空表的两种操作:
DELETE FROM 表名
TRUNCATE 表名相同点:都会清空数据,不改变表结构和索引
不同点:
TRUNCATE 重新设置自增序列,计数器归零
TRUNCATE 不会影响事务
关于delete的问题(重启Mysql服务,现象):
- InnoDB: 自增列会从1开始,(断电即失) mysql8.0修复此现象
- MyISAM: 继续从上一个自增列开始,(存在文件中,不会丢失)
5.DQL查询数据
5.1 指定查询字段
-- 查询全部学生
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 关键字 给字段名或者表名起别名
数据库中的列:
去重
/* 去重:
去掉查询结果中相同的数据项
*/
-- 查询哪些同学参加了考试,成绩
-- 查询全部的考试成绩
SELECT * FROM `result`
-- 查询有哪些同学参加了考试
SELECT `studentno` FROM `result`
-- 有重复数据,去重
SELECT DISTINCT `studentno` FROM `result`
数据库中的列
/* 数据库中的列(表达式)*/
-- 查询系统版本
SELECT VERSION()
-- 实现计算
SELECT 50*2-2 AS 计算结果
-- 查询自增的步长
SELECT @@auto_increment_increment
-- 数据项 加1
SELECT `studentno`,`studentresult`+1 AS 加分后 FROM `result`
5.2 where条件字句
where 逻辑语句
/* where条件字句 */
SELECT `studentno`,`studentresult` FROM `result`
-- 查询成绩在50 - 90之间 AND && BETWEEN...AND...
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=60 AND `studentresult`<=90
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=60 && `studentresult`<=90
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` BETWEEN 60 AND 90
-- 查询成绩不等于85的成绩 != not
SELECT`studentno`,`studentresult` FROM `result`
WHERE `studentresult`!= 85
SELECT`studentno`,`studentresult` FROM `result`
WHERE NOT `studentresult` = 85
where 模糊查询
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | A is null | 操作符为null,结果为真 |
IS NOT NULL | A is not null | 操作符为not 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,A3…中,结果为真 |
-- like查询
-- 查询姓张的同学 like 占位符(% 表示0到多个字符,_ 表示一个字符)
SELECT * FROM `student`
WHERE `studentname` LIKE '张%'
SELECT * FROM `student`
WHERE `studentname` LIKE '张_'
-- 查询名字中含有 三 的同学
SELECT * FROM `student`
WHERE `studentname` LIKE '%三%'
-- in查询 in(多个查询的条件,注意:这里不能用占位符 % _)
-- 查询1001,1002,1003号学生
SELECT * FROM `student`
WHERE `studentno` IN(1001,1002)
-- 查询在西安的学生
SELECT * FROM `student`
WHERE `address` IN('西安','北京朝阳')
-- null not null
-- 查询地址为空的学生 这里是空字符串 不是 null
SELECT * FROM `student`
WHERE `address` ='' OR `address` IS NULL
-- 查询有出生日期的同学,不为null
SELECT * FROM `student`
WHERE `borndate` IS NOT NULL
-- 查询出生日期为空的同学 null
SELECT * FROM `student`
WHERE `borndate` IS NULL
5.3 联表查询
联表查询:
/*
查询思路:
1.分析需求,分析所需查询的字段来自于哪几个表
2.确定使用哪种连接查询 7种
3.确定交叉点 (这两个表中哪个数据是相同的)
4.利用相同字段形成连接条件
注意: 查询结果中的字段如果是两个表的相同字段,必须指定哪个表中的字段。
否则报错:Column 'studentno' in field list is ambiguous
*/
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
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
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回行 |
left join | 从左表中返回所有的值,即使右表中没有(null填充) |
right join | 从右表中返回所有的值,即使左表中没有(null填充) |
-- 查询缺考的同学
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno
WHERE `studentresult` IS NULL
/*
查询参加考试同学的信息 (学号,学生姓名,科目名,分数)
所需查询的表 student、result、subject 分步查询
条件 student.sno = result.sno result.subjectno=subject.subjectno
*/
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON s.studentno = r.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技术 |
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','办公信息');
-- 查询父子信息:把一张拆分成两张一模一样的表
SELECT a.`categoryName` AS '父列',b.`categoryName` AS '子列'
FROM `category` AS a,`category` AS b
WHERE a.categoryid = b.pid
5.4 分页和排序
排序
-- 排序 : 升序 ASC 降序:DESC
-- ORDER BY 通过哪个字段排序,排序规则
-- 根据成绩升序排序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
ORDER BY studentresult ASC
分页
-- 分页
-- 语法 limit(放在语句最后) 起始下标,页面大小
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
ORDER BY studentresult ASC
LIMIT 0,3
/*
第一页 limit 0,3 (1-1)*3
第二页 limit 3,3 (2-1)*3
第三页 limit 6,3 (3-1)*3
第n页 limit (n-1)*pagesize,pagesize
总页数= 数据总数/页面大小
*/
语法:limit 查询起始下标,页面大小
5.5 子查询
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 r.`studentno`,`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'
-- 查询 科目为高等数学-1 且分数不小于80分的学生学号和姓名
-- 子查询
SELECT s.`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`='高等数学-1'
)
-- 联表查询
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`='高等数学-1' AND `studentresult`>=80
-- 子查询改造
SELECT s.`studentno`,`studentname` FROM `student` s WHERE `studentno` IN (
SELECT `studentno` FROM `result` r WHERE `studentresult`>=80 AND `subjectno`= (
SELECT `subjectno` FROM `subject` WHERE `subjectname` = '高等数学-1'
)
)
5.6 分组和过滤
-- 查询不同课程的平均分,最高分,最低分,平均分>70
-- 核心;根据不同的课程分组
SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`) AS 最高分,MIN(`studentresult`) AS 最低分
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY `subjectname`
HAVING 平均分>70
5.7 MD5加密
CREATE TABLE `MD5test`(
`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 `MD5test` VALUES(1,'张三','12345'),(2,'李四','23456'),(3,'王五','34567')
-- 已有数据加密
UPDATE `MD5test` SET pwd=MD5(pwd) WHERE id =1
UPDATE `MD5test` SET pwd=MD5(pwd)
-- 插入数据时加密
INSERT INTO `MD5test` VALUES(4,'孟哈哈',MD5('111111'))
-- 校验:将用户传递过来的密码进行加密,比对加密后的值
SELECT * FROM `MD5test` WHERE `name` ='孟哈哈' AND `pwd`=MD5('111111')
-- 通常来说,解密现在通过在线解密网站进行数据库查找,得到对应的密码
6.函数
6.1 常用函数
-- 常用函数 官方文档查询即可(了解)
-- 查询姓张的同学 替换姓为 孟
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() -- 获取当前版本
6.2 聚合函数(重点)
函数 | 描述 |
---|---|
COUNT() | 计数 |
SUM() | 和 |
AVG() | 平均数 |
MAX() | 最大值 |
MIN() | 最小值 |
-- 统计表中数据的格式
SELECT COUNT(`studentno`) FROM `student` -- 会忽略所有的Null值
SELECT COUNT(*) FROM `student` -- 不会忽略所有的Null值 本质:计算行数
SELECT COUNT(1) FROM `student` -- 不会忽略所欲的Null值
SELECT SUM(`studentresult`) AS 总和 FROM `result`
SELECT MAX(`studentresult`) AS 最大数 FROM `result`
SELECT MIN(`studentresult`) AS 最小数 FROM `result`
SELECT AVG(`studentresult`) AS 平均分 FROM `result`
7.select小结
7.1注意: 顺序很重要
select 去重 查询字段 from 表名 (表名和字段可以通过 as取别名)
xxx join 连接的表名 on 等值判断
where (具体的值,子查询语句)
group by(通过哪个字段来分组)
having (过滤分组后的信息,和where条件相同,位置不同而已)
order by (通过哪个字段排序,升序或者降序)
limit startindex,pagesize (起始数据号,页面大小)
8.事务(ACID)
8.1 概念
要么都成功,要么都失败
例子:
银行转账:
A 1000元 B 200元 C:800元 D:400 E:300元
A ——>B 200元: A:1000-200=800 B:200+200=400
C——>B 100元: C:800-100=700 B:200+100=300
D——>B 200元
原子性: 说明转账是要实时同步的
一致性: 说明这里的金额总数是不变的 起始:1000+200=1200 最终:800+400=1200
隔离性: 说明如果有其他用户也给B转账是互不影响的
持久性: 说明事务一旦提交,数据库则会立即更新,如果未提交,则保留原始数据
脏读: 说明了C给B转账100时,读取到的数据为B 200 然后 B:200+100=300
不可重复读:说明A B C之间在完成事务,提交事务的时候(已经提交),D突然向B转了200元,则结果会和预期不一样
虚读(幻读):说明提交事务的时候,有一个事务插入了D数据,导致数据前后不一致
四个基本原则
1.原子性(atomicity):
一个事务是一个不可分割的工作单位,事务中包括的操作要么都做,要么都不做。
2.一致性(consistency):
事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
3.隔离性(isolation):
一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
4.持久性(durability):
持久性也称永久性(permanence),指一个事务一旦提交,它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
隔离所导致的一些问题
脏读:
指一个事务读取了另外一个事务未提交的数据。
不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同。(不一定是错误,只是场合不对)。
虚读(幻读):
指在一个事务内读取到了其他事务插入的数据,导致前后读取不一致(通常是多了一行数据)。
8.2 语法
-- mysql是默认开启事务提交的
SET autocommit = 0; -- 关闭事务提交
SET autocommit = 1; -- 开启事务提交
-- 手动处理事务需先关闭自动提交
SET autocommit = 0;
-- 事务开启
START TRANSACTION -- 标记一个事务的开始,从这个语句开始,后面的SQL语句都在同一个事务内
-- 提交
COMMIT;
-- 回滚
ROLLBACK;
-- 事务结束记得开启自动提交
SET autocommit = 1;
-- 保存点
SAVEPOINT 保存点名; -- 设置一个保存点(存档)
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 删除保存点
8.3 转账测试
-- 测试
CREATE TABLE `acount`(
`id` INT(4) 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 `acount`(`name`,`money`) VALUES('A','400.00'),('B','1000.00')
-- 模拟转账事务
-- 关闭自动提交
SET autocommit = 0;
START TRANSACTION; -- 开启一个事务
UPDATE `acount` SET `money`=`money`-200 WHERE `name`='A'
UPDATE `acount` SET `money`=`money`+200 WHERE `name`='B'
COMMIT; -- 提交
ROLLBACK; -- 假设有语句执行出错,事务回滚到原来的数据
SET autocommit = 1; -- 开启自动提交事务
9.索引
9.1 索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
- 主键索引 (PRIMARY KEY)
- 唯一标识,主键索引只能有一个,只能有一个列作为主键
- 唯一索引 (UNIQUE KEY)
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识为 唯一索引
- 常规索引 (KEY/INDEX)
- 默认,index key来设置
- 全文索引 (FULLTEXT)
- 在特定的数据库引擎下才有 MyISAM
- 快速定位数据
9.2 语法
SHOW INDEX FROM `student` -- 显示表的所有索引
-- 列名增加一个全文索引 在数据非常多的时候生效
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`)
-- EXPLAIN分析sql的执行情况
EXPLAIN SELECT * FROM `student` -- 非全文索引
EXPLAIN SELECT * FROM `student` WHERE MATCH(studentname) AGAINST('张')
9.3 测试索引
-- 测试
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 inserts_data ()
RETURNS INT DETERMINISTIC -- 确定性函数用关键词Deterministic标识,表示函数的返回值完全由输入参数决定。
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),'1025483534@qq.com', CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
SET i=i+1;
END WHILE;
RETURN i;
END;
SELECT inserts_data() -- 执行此函数 生成一百万条数据 1 min 48 sec
SELECT * FROM app_user WHERE `name`='用户10000' -- 1.169 sec
SELECT * FROM app_user WHERE `name`='用户10000' -- 1.337 sec
SELECT * FROM app_user WHERE `name`='用户10000' -- 1.238 sec
EXPLAIN SELECT * FROM app_user WHERE `name`='用户10000' -- rows = 992803
-- 创建索引,再进行查询
-- 语法规范: 索引名(id_表名_字段名)
-- CREATE INDEX 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`)
SELECT * FROM app_user WHERE `name`='用户10000' -- 0.003 sec
SELECT * FROM app_user WHERE `name`='用户10001' -- 0.001 sec
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bMddkIgP-1615810632690)(C:\Users\10254\AppData\Roaming\Typora\typora-user-images\image-20201113183506633.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2noigPz7-1615810632693)(C:\Users\10254\AppData\Roaming\Typora\typora-user-images\image-20201113183030885.png)]
这里相当于键值对 ,创建索引后则会给每一行元组的主键创建一个 key:value ,要查value的值,只需定位到key即可
9.4 索引原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量不需要加索引
- 索引一般加在常用查询字段上
索引的数据结构
Hash类型的索引
BTREE:InnoDB默认的数据结构
阅读博客:http://blog.codinglabs.org/articles/theory-of-mysql-index.html
10.权限管理和备份
10.1 权限管理
-- 创建一个新用户
CREATE USER menghaha IDENTIFIED BY '11111'
-- 修改指定用户密码
ALTER USER 'menghaha' IDENTIFIED BY '123';
-- 重命名 RENAME USER 原来名字 TO 新名字
RENAME USER menghaha TO mengha
-- 用户授权 ALL privileges 全部的授权
-- 除了 授权给其他人
GRANT ALL PRIVILEGES ON *.* TO mengha
-- 查看指定用户权限
SHOW GRANTS FOR mengha
SHOW GRANTS FOR root@localhost
-- ROOT用户权限(最高权限)
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
10.2 备份
方式:
-
直接拷贝物理文件(data)
-
在可视化工具(SQLyog)手动导出
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LLIB3akn-1615810632697)(C:\Users\10254\AppData\Roaming\Typora\typora-user-images\image-20201113212459626.png)]
-
使用mysqldump命令行导出
导出: # mysqldump -h主机 -u用户名 -p密码 数据库名 表名 > 导出路径 mysqldump -hlocalhost -uroot -proot school acount >D:/a.sql # mysqldump -h主机 -u用户名 -p密码 数据库名 > 导出路径 mysqldump -hlocalhost -uroot -proot school >D:/b.sql 导入: # 登录的情况下,切换到指定数据库 # source 备份文件 mysql -uroot -proot mysql> use 数据库名 mysql> source D:/a.sql #未登录 mysql -u用户名 -p密码 数据库名 <D:/a.sql
:InnoDB默认的数据结构
阅读博客:http://blog.codinglabs.org/articles/theory-of-mysql-index.html
10.权限管理和备份
10.1 权限管理
-- 创建一个新用户
CREATE USER menghaha IDENTIFIED BY '11111'
-- 修改指定用户密码
ALTER USER 'menghaha' IDENTIFIED BY '123';
-- 重命名 RENAME USER 原来名字 TO 新名字
RENAME USER menghaha TO mengha
-- 用户授权 ALL privileges 全部的授权
-- 除了 授权给其他人
GRANT ALL PRIVILEGES ON *.* TO mengha
-- 查看指定用户权限
SHOW GRANTS FOR mengha
SHOW GRANTS FOR root@localhost
-- ROOT用户权限(最高权限)
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
10.2 备份
方式:
-
直接拷贝物理文件(data)
-
在可视化工具(SQLyog)手动导出
- [外链图片转存中…(img-LLIB3akn-1615810632697)]
-
使用mysqldump命令行导出
导出: # mysqldump -h主机 -u用户名 -p密码 数据库名 表名 > 导出路径 mysqldump -hlocalhost -uroot -proot school acount >D:/a.sql # mysqldump -h主机 -u用户名 -p密码 数据库名 > 导出路径 mysqldump -hlocalhost -uroot -proot school >D:/b.sql 导入: # 登录的情况下,切换到指定数据库 # source 备份文件 mysql -uroot -proot mysql> use 数据库名 mysql> source D:/a.sql #未登录 mysql -u用户名 -p密码 数据库名 <D:/a.sql