狂神说MySQL
1.初识MySQL
JavaEE:企业级Java开发 Web
前端(页面:展示:数据)
后台 (连接点:连接数据库JDBC,连接前端(控制视图跳转,给前端传递数据))
数据库(存数据,Txt,Excel,Word)
1.1为什么学数据库
1、岗位需求
2、现在的世界,大数据时代,得数据者得天下
3、被迫需求:存数据
4、数据库是所有软件体系中最核心的存在 DBA
1.2 什么是数据库
数据库:(DB,DataBase)
概念: 数据仓库,软件,安装在操作系统之(windows,Linux。mac)上的!SQL,可以存储大量的数据,500万!
作用:存储数据,管理数据 Excel
1.3 数据库分类
关系型数据库:(SQL)
- MySQL, Oracle, sql Server, DB2, SQLite
- 通过表和表之间,行和列之间的关系进行数据的存储
非关系型数据库:(NOSQL) Not Only SQL
- Redis, MongDB
- 非关系型数据库,对象存储,通过对象自身的属性来决定。
DBMS(数据库管理系统)
- 数据库的管理软件,科学有效的管理我们的数据,维护和获取
- MySQL ,数据管理系统!
1.4 MySQL简介
MySQL是一个关系型数据库管理系统
前世: 瑞典MySQL AB 公司
今身: 属于 Oracle 旗下产品
MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。
- 开源的数据库软件
- 体积小,速度快,总体拥有成本低,招人成本比较低。
- 中小型网站,或者大型网站,集群
- 官网: https://www.mysql.com/
2.sqlyog的使用
2.1建一个数据库school
说明:
每个sqlyog的执行操作,本质上是sql语句的执行
2.2建一张表student
2.3查看表student
保存信息
删除信息
3.基本的命令行的操作
--注释
mysql -uroot -p123456 --连接数据库 自己设得密码是123456
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; --修改密码
flush privileges; --刷新权限
-----------------------------------------------------------------------------------------------------
所有的语句都是用;结尾
所有的命令不用;结尾
show databases; -- 查看所有的数据库
use school -- 切换数据库 use 数据库名
show tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中表的结构
create database library; -- 创建一个数据库
exit --退出连接
quit --退出连接
4.操作数据库
① 操作数据库->操作数据库中的表->操作数据库中的表的数据
② mysql关键字不区分大小写
4.1操作数据库
1.创建数据库
CREATE DATABASE [IF NOT EXISTS] school //[]内可做选择,判断创建的数据库是否已经存在
2.删除数据库
DROP DATABASE [IF EXISTS] school //[]内可做选择,判断删除的数据库是否已经存在
3.使用数据库
USE `school` //如果你的表名或字段名是一个特殊字符,就需要带` `
4.查看数据库
SHOW DATABASES //查看所有的数据库
举例
4.2数据库的数据类型
1.数值
2.字符串
3.时间日期
4.null
4.3数据库的字段属性(重点)
1. Unsigned:
-
无符号的整数
-
表示该列不能声明为负数
2. zerofill:
- 不足的位数,使用0来填充,例:int(3) ,5 ----005
3. 自增:
- 自动在上一条的纪录上加一
- 通常用来设计唯一的主键,必须是整数类型
- 可以自定义自增的初始值和步长
4. 非空
- 非空,如果不给它赋值,就会报错
- 空,如果不填写值,默认为null
5. 默认
- 设置默认的值
4.4创建数据库表(重点)
格式:
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 数据类型 [属性] [索引] [注释],
`字段名` 数据类型 [属性] [索引] [注释],
`字段名` 数据类型 [属性] [索引] [注释],
.......
`字段名` 数据类型 [属性] [索引] [注释],
PRIMARY KEY(`字段名`) -- 主键
)[表类型][字符集设置][注释]
举例:
-- NOT NULL 非空
-- AUTO_INCREMENT 自增
-- DEFAULT 默认
-- COMMENT 注释
-- PRIMARY KEY(`id`) 主键 一般一个表只有一个唯一主键
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
4.5数据表的类型
4.6修改删除表字段
修改
-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 属性
ALTER TABLE teacher1 ADD age INT(2)
-- 修改表的字段(重命名,修改约束)
ALTER TABLE teacher1 MODIFY age VARCHAR(5) -- 修改约束
ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 重命名
删除
-- 删除表的字段
ALTER TABLE teacher1 DROP age1
-- 删除表
DROP TABLE [IF EXISTS] teacher1
5.MySQL数据管理
5.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
-- 定义外键KEY
-- 给这个外键添加约束(执行引用) references 引用
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_gardeid` (`gradeid`),
CONSTRAINT `FK_gardeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (gradeid)
)ENGINE=INNODB DEFAULT CHARSET=utf8
方式二:在创建表后声明外键
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
-- 定义外键KEY
-- 给这个外键添加约束(执行引用) references 引用
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`)
-- ALTER TABLE `表` ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列)REFERENCES `grade`(`gradeid`)引用哪个表的哪个字段
总结:
以上的操作都是物理外键,数据库级别外键,不建议使用。(避免数据库过多造成困扰)
最佳实践
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
- 我们想使用多张表的数据,想使用外键(程序去实现)
5.2DML语言(全部记住)
数据库意义:数据存储,数据管理
DML语言:数据操作语言
- Insert
- update
- delete
5.2.1 添加
Insert
格式:insert into表名
(字段名1
,字段名2
,字段名3
) values (‘值1’…),(‘值2’…),(‘值3’…)
-- 插入语句
-- insert into `表名` (`字段名1`....) values ('值1'...),('值2'...),('值3'...)
INSERT INTO `grade` (`gradename`) VALUES ('大三')
INSERT INTO `grade` (`gradename`) VALUES ('大三'),('大一'),('大二')
INSERT INTO `grade` (`gradename`,`gradeid`) VALUES ('大三','22')
INSERT INTO `student` (`name`,`age`,`finally`)
VALUES ('吴彦祖','88','5'),('刘德华','14','3'),('刘胜','6','1')
5.2.2 修改
update
格式:UPDATE表名
SET字段名
= ‘林允儿’ WHERE 条件
-- 修改
UPDATE `student` SET `name`='林允儿' WHERE age=6
UPDATE `student` SET `name`='裴秀智'
UPDATE `student` SET `name`='金智秀',`finally`='5' WHERE age=14
UPDATE `student` SET `name`='林允儿',`age`='18' WHERE id=2 OR finally=5
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | ||
!=或<> | 不等于 | ||
> | 大于 | ||
< | 小于 | ||
>= | |||
<= | |||
between and | 闭合区间 | ||
and | && | ||
or | || |
-
条件,是筛选的条件,如果没有指定,则会修改所有的列
-
字段值是一个具体的值,也可以是一个变量
-
多个设置的属性之间,使用英文逗号隔开
5.2.3 删除
delete命令
格式:DELETE FROM表名
WHERE 条件
-- 删除数据 (避免这样写,会全部删除)
DELETE FROM `student`
-- 删除指定数据
DELETE FROM `student` WHERE id = 2
truncate 命令
作用:完全清空一个数据库表,表的结构和索引约束不会变。
格式:TRUNCATE表名
-- 清空表
TRUNCATE `student`
delete 与 truncate 区别
- 相同点:都能删除数据,都不会改变表结构
- 不同点:
- truncate 不会影响事务
- truncate 重新设置 自增列 计数器回归零
了解即可:delete删除的问题 重启数据库,现象
- innoDB 自增列会从1开始(存在内存当中,断电即失)
- MyISAM 继续从上一个自增量开始(存在文件中,不会丢失)
6.DQL查询数据(最重点)
6.1DQL
Data Query Language : 数据查询语言
- 所有的查询操作都用它: Select
- 数据库中最核心的语言,最重要的语句。
- 使用频率最高的语句。
6.2查询指定字段
选择:select
-- 查询所有的学生 SELECT 字段 FROM 表名
SELECT * FROM student
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
-- 别名,给查询结果的列名(表名)起一个新的名字
SELECT `StudentNo` AS 学号,`StudentName` AS 姓名 FROM student AS s
-- 函数 Concat(a,b) 拼接
SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM student
去重:diatinct
作用:去除查询结果中的重复数据,只显示一条
-- 查询一下有哪些同学参加了考试,成绩
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
6.3where条件子句
作用:检索数据中符合条件的值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
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
SELECT `StudentNo`,`StudentResult` FROM result
WHERE StudentResult >=95 && 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 null 结果为真 |
between and | 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 结合 %(代表0到任一个字符) _(一个字符)
-- 查询姓张的同学
SELECT `studentno`,`studentname` FROM student
WHERE studentname LIKE '张%'
-- 查询姓张的同学,名字后面有两个字
SELECT `studentno`,`studentname` FROM student
WHERE studentname LIKE '张__'
-- 查询名字中间有嘉字的同学 %威%
SELECT `studentno`,`studentname` FROM student
WHERE studentname LIKE '%威%'
-- ========in(后面是具体的值)========
-- 查询1000,1001号学生
SELECT `studentno`,`studentname` FROM student
WHERE `studentno` IN (1000,1001)
-- 查询在北京的学生
SELECT `studentno`,`studentname` FROM student
WHERE `address` IN ('北京朝阳')
-- =======null not null ======
-- 查询地址为空的学生 null ''
SELECT `studentno`,`studentname` FROM student
WHERE `address` IS NULL OR address = ''
-- 查询地址不为空的学生
SELECT `studentno`,`studentname` FROM student
WHERE `address` IS NOT NULL AND address != ''
6.4联表查询
JOIN 对比
-- ==========联表查询 join======
-- 查询参加考试的同学(学号,姓名,科目编号,成绩)
SELECT * FROM student
SELECT * FROM result
/*思路
1.分析需要哪些表
2.确定使用哪种连接查询? 7种
确定交叉点(两表中相同的字段)
3. 判断条件:学生表中的 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 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 | 会从左表中返回所有的值,即使右表中没有匹配 |
right jion | 会从右表中返回所有的值,即使左表中没有匹配 |
-
left join 是做左外关联,主表内容都会显示;符合关联条件的附表内容才会显示出来。
-
inner join 是内关联,没有主表附表的概念;两个表中,同时符合关联条件的数据才会显示出来。
-- 查询缺考的同学
SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno
WHERE studentresult IS NULL
-- 查询参加考试的同学的信息:学号,姓名,科目名,分数
SELECT r.studentno,studentname,subjectname,studentresult
FROM result AS r
RIGHT JOIN student AS s
ON r.studentno = s.studentno
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno
自连接:自己的表和自己的表连接。 核心:一张表拆为两张即可
用例
-- 储存:树形
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','软件开发'),
('5','1','美术设计'),
('4','3','数据库'),
('8','2','办公信息'),
('6','3','web开发'),
('7','5','ps技术');
-- 查询父子信息:把一张表看作两个一模一样的表
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM category AS a , category AS b
WHERE a.`categoryid` = b.`pid`
6.5分页和排序
排序
-- 排序: 升序ASC , 降序DESC
-- 格式:order by 字段名 ASC/DESC
-- 成绩排序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
INNER JOIN `result` AS r
ON s.studentno=r.studentno
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE subjectname='高等数学-1'
ORDER BY studentresult DESC
分页
-- 为什么要分页
-- 缓解数据库压力,给人体验更好。 瀑布流
-- 分页,每页只显示两条数据
-- 语法:limit 下标,页面大小
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
INNER JOIN `result` AS r
ON s.studentno=r.studentno
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE subjectname='高等数学-1'
ORDER BY studentresult DESC
LIMIT 4,2
6.6子查询
-- ===========子查询(由里即外)==========
-- 查询 高等数学-1 的所有考试结果(学号,科目编号,成绩),降序排列
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result` AS r
WHERE subjectno=(
SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-1'
)
-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`studentno`,`studentname`
FROM student AS s
INNER JOIN result AS r
ON r.studentno= s.studentno
WHERE `studentresult` >=80
SELECT `studentno`,`studentname`
FROM student
WHERE studentno IN(
SELECT DISTINCT studentno FROM result WHERE studentresult >= 80
)
-- 高等数学-1 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`studentno`,`studentname`
FROM student AS s
INNER JOIN result AS r
ON r.studentno= s.studentno
WHERE `studentresult` >=80 AND `subjectno`=(
SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-1'
)
7.MySQL函数
7.1常用函数
1.数据函数
SELECT ABS(-8); /*绝对值*/
SELECT CEILING(9.4); /*向上取整*/
SELECT FLOOR(9.4); /*向下取整*/
SELECT RAND(); /*随机数,返回一个0-1之间的随机数*/
SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/
2.字符串函数
SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/
SELECT CONCAT('我','爱','程序'); /*合并字符串,参数可以有多个*/
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱'); /*替换字符串,从某个位置开始替换某个长度*/
SELECT LOWER('KuangShen'); /*小写*/
SELECT UPPER('KuangShen'); /*大写*/
SELECT LEFT('hello,world',5); /*从左边截取*/
SELECT RIGHT('hello,world',5); /*从右边截取*/
SELECT REPLACE('狂神说坚持就能成功','坚持','努力'); /*替换字符串*/
SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/
SELECT REVERSE('狂神说坚持就能成功'); /*反转
-- 查询姓周的同学,改成邹
SELECT REPLACE(studentname,'周','邹') AS 新名字
FROM student WHERE studentname LIKE '周%';
3.时期和时间函数
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());
4.系统信息函数
SELECT VERSION(); /*版本*/
SELECT USER(); /*用户*/
7.2聚合函数(常用)
函数名称 | 描述 |
---|---|
count() | 计数 |
sum() | 求和 |
avg() | 平均值 |
max() | 最大值 |
min() | 最小值 |
···· | ····· |
-- ======聚合函数=====
-- 查询一个表中有多少个记录
SELECT COUNT(studentname) -- count(字段),会忽略所有的null值
FROM student
SELECT COUNT(*) -- count(*),不会忽略null值 本质:计算行数
FROM student
SELECT COUNT(1) -- count(1),不会忽略ull值 本质:计算行数
FROM student
SELECT SUM(`studentresult`) AS 总和
FROM `result`
SELECT AVG(`studentresult`) AS 平均值
FROM `result`
SELECT MAX(`studentresult`) AS 最大值
FROM `result`
SELECT MIN(`studentresult`) AS 最小值
FROM `result``subject`
-- 查询不同课程的平均分 最高分 最低分
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 -- 过滤分组记录必须满足的次要条件
7.3MD5 加密
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 VALUES(1,'张三','123456'),(2,'麻子','123456'),(3,'李四','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1
UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部的密码
-- 应用时,一般插入时加密
INSERT INTO testmd5 VALUES(4,'张三',MD5('123456'))
-- 如何校验:将用户传递进来的密码,进行md5加密,比对加密后得知
SELECT *
FROM testmd5
WHERE `name` = '张三' AND pwd=MD5('123456')
8.事务
要么都成功,要么都失败。
理解:将一组SQL放在一个批次中去执行
第一条sql语句 A给B转钱 A1000 --->200 B300
第二条sql语句 B收到A的钱 A800 ---> B400
事务原则: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 SAVEPOINT 保存点名 -- 撤销保存点
应用:转账
-- 转账
-- 创建数据库
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(9,2) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
INSERT INTO account(`name`,`money`)
VALUES('a',2000.00),('b',1000.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' -- a加500
-- 提交事务
COMMIT;
-- 回滚
ROLLBACK;
SET autocommit = 1 ; -- 恢复默认值
9.索引
索引是帮助Mysql高效获取数据的数据结构。
提取句子主干,就可以得到索引的本质:索引是数据结构
9.1索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
- 主键索引(PRIMARY KEY)
- 唯一的标识,不可重复,只能有一列作为主键索引
- 唯一索引(UNIQUE KEY)
- 避免重复的列出现,唯一索引可以重复,多个列都可以都标识为唯一索引
- 常规索引(KEY/INDEX)
- 默认的,index/key关键字来设置
- 全文索引(FullText)
- 在特定的数据库引擎下才有,MyISAM
- 快速定位数据
基础语法
-- ===============索引==============
-- 使用;
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后,增加索引
-- 显示所有的索引信息
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('张')
9.2测试索引
-- 加索引前
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';
索引在小数据量的时候,用处不大,但是再大数据的时候,区分十分明显
9.3索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加载常用来查询的字段上
索引的数据结构(自行搜索)
10权限管理和备份
10.1用户管理
SQL yog可视化
sql 命令操作
用户表:mysql.user
本质:对这张表进行增删改查
-- 创建用户
-- 格式:CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER kuangshen IDENTIFIED BY '123456'
-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('123456')
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR kuangshen = PASSWORD('123456')
-- 重命名
-- 格式:RENAME USER 原名字 TO 新名字
RENAME USER kuangshen TO kuangshen2
-- 用户授权 ALL PRIVILEGES 全部的权限 库.表
-- ALL PRIVILEGES 全部的权限 除了给它人授权
GRANT ALL PRIVILEGES ON *.* TO kuangshen2
-- 查询权限
SHOW GRANTS FOR kuangshen2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost -- 查看当前用户的权限
-- 撤销权限 REVOKE 哪些权限,在哪个库撤销,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2
-- 删除用户
DROP USER kuangshen
10.2MySQL备份
为什么要备份:
- 保证重要的数据不丢失
- 数据专业
MySQL数据库备份的方式
- 直接拷贝物理文件
- 在sqlyog这种可视化工具中手动导出
(选中要导出的数据库或表,然后右键,选择备份) - 使用命令行导出 mysqldump 命令行使用
备份: 格式:mysqldump -h主机 -u用户名 -p密码 库名 表名 >物理磁盘地址/文件名 mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql 格式:mysqldump -h主机 -u用户名 -p密码 库名 表名1 表名2 表名3 >物理磁盘地址/文件名 mysqldump -hlocalhost -uroot -p123456 school student result subject >D:/a.sql 导出: 先登录 mysql -uroot -p123456 在使用表 use school; 导出: source D:/a.sql
11.规范数据库设计
但数据库比较复杂的时候,我们就需要设计了
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据库插入和删除都会麻烦,异常(因为使用了物理外键)
- 程序的性能差
良好的数据库设计:
- 节省内存空间
- 保证数据库的完整性
- 方便我们们开发系统
软件开发中,关于数据库的设计:
- 分析需求:分析业务和需要处理的数据库的需求
- 概要设计:设计关系图E-R图
设计数据库的步骤:(个人博客)
-
收集信息,分析需求
- 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
- 分类表(文章分类,谁创建的)
- 文章表(文章的信息)
- 友链表(友链信息)
- 自定义表(系统信息,某个关键的字,或者某些主字段)
- 说说表(发表心情…id ,content ,time)
-
标识实体(把需求落地到每个字段)
-
标识实体之间的关系
- 写博客 user–>blog
- 创建分类 user–>category
- 关注 user–>user
- 友链:links
- 评论 user–>user->blok
11.2三大范式
为什么需要数据规范?
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式
第一范式
原子性:保证每一列不可再分
第二范式
前提:满足第一范式
每张表只描述一件事情(保证每张表最简化)
第三范式
前提:满足第二范式
第三范式需要确保数据库中每一列数据都与主键直接相关,而不能间接相关。
规范性和性能的问题
关联查询的表,不得超过三张表
- 考虑商业化的需求和目标(成本和用户体验)数据库的性能更加重要
- 再规范性能的问题的时候,需要适当的考虑一下,规范性
- 故意给某些表加一些冗余的字段(从多表,变成单表)
- 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)