MySQL学习

1、MySQL

数据库分类

关系型数据库：(SQL)

MySQL, Oracle, Sql Server, DB2, SQLlite
通过表和表之间，行和列之间的关系进行数据的存储，学员信息表，考勤表, .

非关系型数据库：(No SQL) Not Only

Redis, MongDB
非关系型数据库，对象存储，通过对象的自身的属性来决定，

SQL命令

-- 连接数据库
mysql -u root -proot  
-- 修改密码
set password for root@localhost = password('root'); -- 方法1
mysqladmin -u用户名 -p旧密码 password 新密码; -- 方法2
update user set password=password("root") where user="root"; -- 方法3
-- 刷新权限（执行修改用户密码等操作的必须步骤）
flush privileges;

mysql> show databases;  #查看所有数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| pinyougoudb        |
| ssmbuild           |
| sys                |
| vgshop             |
| zt                 |
| zt-share           |
+--------------------+
9 rows in set (0.00 sec)

mysql> use ssmbuild  #切换数据库
Database changed
mysql> show tables;  #查看数据库中所有的表
+--------------------+
| Tables_in_ssmbuild |
+--------------------+
| books              |
+--------------------+
1 row in set (0.00 sec)

mysql> desc books;  ##显示数据库中所有的表的信息
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| bookID     | int(11)      | NO   | MUL | NULL    | auto_increment |
| bookName   | varchar(100) | NO   |     | NULL    |                |
| bookCounts | int(11)      | NO   |     | NULL    |                |
| detail     | varchar(200) | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.09 sec)
mysql> create database database1;  #创建数据库
Query OK, 1 row affected (0.00 sec)

2、操作数据库

操作数据库

操作数据库>操作数据库中的表>操作数据库中表的数据

mysql关键字不分大小写

1、创建数据库

create database if not exists db1;  --判断数据库是否存在再创建  exists

2、删除数据库

drop database if exists db1;

3、使用数据库

use `zt`  --使用zt数据库，如果表名或者字段名是特殊字段，就需要使用``引起来

4、查看数据库

show databases --查看所有的数据库

数据库的列类型

数字

tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
medjumint 中等大小的数据 3个字节
int 标准的整数 4个字节常用的 int
bigint 较大的数据 8个字节
float 浮点数 4个字节
double 浮点数 8个字节(精度问题! )
decimal 字符串形式的浮点数金融计算的时候，一般是使用decimal

字符串

char 字符串固定大小的0~255
varchar 可变字符串 0-65535 常用的变量String
tinytext 微型文本 2^8- 1
text 文本串 2^16 -1 保存大文本

时间日期

date YY-MM-DD 日期格式
time HH: mm: ss 时间格式
datetime YYYY-MM-DD HH: mm: ss 最常用的时间格式
timestamp时间戳，1970.1.1 到现在的毫秒数!也较为常用!
year年份表示

null

没有值，未知
注意，不要使用NULL进行运算，结果为NULL

数据库的字段属性（*）

Unsigned

无符号的整数
声明了该列不能声明为负数

zerofill

0填充的
不足的位数，使用0来填充

AUTO_INCREMENT 自增

通常理解为自增，自动在上一条记录的基础上+ 1 (默认)
通常用来设计唯一的主键~ index, 必须是整数类型
可以自定义设计主键自增的起始值和步长

**NULL 空 NOT NULL 非空 **

假设设置为not null，如果不给它赋值，就会报错!
NUII，如果不填写值，默认就是nul!

DEFAULT 默认

设置默认的值!
sex，默认值为男，如果不指定该列的值，则会有默认的值!

COMMENT 注释

一个字段代表什么

拓展

每一个表，都必须存在以下五个字段!未来做项目用的，表示一个记录存在意义!

id 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间

创建数据库表（**）

目标

--目标:创建一个schoo1数据库
--创建学生表(列，字段) 使用SQL创建
--学号int登录密码varchar(20) 姓名,性别varchar(2) ,出生日期(datatime)，家庭住址，email
--注意点，使用英文()，表的名称和字段尽量使用“ 括起来
-- AUTO_ INCREMENT 自增
--字符串使用单引号括起来!
--所有的语句后面加，(英文的)，最后一个不用加
--PRIMARYKEY主键，一般一个表只有一个唯一的主键!

操作

mysql> create database if not exists school;  #创建一个school数据路
Query OK, 1 row affected

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(20) 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(200) DEFAULT NULL COMMENT '住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式

CREATE TABLE [IF NOT EXISTS]
`表名`(
	`字段名` 列类型 [属性] [索引] [注释],
	`字段名` 列类型 [属性] [索引] [注释],
     .....
	`字段名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]

常用命令

DESC student  -- 显示表结构
SHOW CREATE TABLE `student`  -- 查看数据库建表语句
SHOW CREATE DATABASE school  -- 查看数据库建库语句

数据表的类型

-- 关于数据库引擎
/*
INNODB  默认使用
MYISAM  早些年使用
*/

常规使用操作：

MYISAM 节约空间，速度较快
INNODB 安全性高，事物的处理，多表用户操作

设置数据库表的字符集编码

CHARSET=utf8

不设置的话，会是mysql默认的字符集编码~ (不支持中文! )

MySQL的默认编码是Latin1,不支持中文

也可以在my.ini中配置默认的编码，不过通用性较低，最好在建表的时候声明字符编码格式

character-set-server=utf8

修改删除表

修改

-- 修改表名ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS student1

-- 增加表的字段
ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE student1 ADD age INT(4) COMMENT '年龄'

-- 修改表的字段( 重命名，修改约束! )
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE student1 MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名CHANGE 旧名字 新名字 列属性[]
ALTER TABLE student1 CHANGE age age1 INT(1) -- 字段重命名

-- 删除表的字段
ALTER TABLE student1 DROP age

modify与change的区别

change用来字段重命名，不能修改字段类型和约束；
modify不用来字段重命名，只能修改字段类型和约束；

删除

-- 删除表(如果表存在再删除)
DROP TABLE IF EXISTS student1

所有的创建和删除操作尽量加上判断，以免报错

3、MySQL数据管理

外键(了解)

方式一：建表的时候就添加外键约束

删除有外键关系的表的时候，必须要先删除引用别人的表(从表)，再删除被弓|用的表(主表)

方式二：创建表成功后，再添加外键约束

以上的操作都是物理外键,数据库级别的外键，我们不建议使用!(避免数据库过多造成困扰)

最佳实践

数据库就是单纯的表，只用来存数据，只有行(数据)和列(字段)
我们想使用多张表的数据，想使用外键(程序去实现)

DML语言

数据库的意义：数据储存，数据管理

DML语言:数据操作语言

insert
update
delete

添加

insert into 表名([字段1]，[字段2]，[字段3]，...) values('值1'，'值2'，'值3'，...)

字段是可以省略的，但是后面的值必须要与数据库表中的字段一一对应

可以同时插入多条数据，values后面的值用,隔开即可，values(),(),...

修改

update 表名 set 字段1=值1，[字段2=值2]，... where[条件]

删除

delete from 表名 [where 条件]

-- 删除数据 （避免这样写，会全部删除）
delete from `表名`

-- 删除指定数据
delete from `表名` where [条件]

truncate命令

作用：完全清空一个数据库表，表的结构和索引约束不会变

-- 清空数据库表
truncate `表名`

delete与truncate的区别

相同点：都是删除表中的数据，不会破坏表的结构

不同点：truncate清空表会重新设置自增列，计数器会归零，相当与恢复出厂设置，delete只是删除的数据，自增列还会接着上次的增量自增

4、DQL查询数据（***）

DQL(Data Query Language 数据库查询语言)

SELECT语句的完整语法

SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[LEFT | RIGHT | INNER join table_name]  -- 联合查询
[WHERE…]  -- 指定结果需满足的条件
[GROUP BY…]  -- 指定结果按照那几个字段来分组
[HAVING…]  -- 过滤分组记录必须满足的次要条件
[ORDER BY…]  -- 指定查询结果按一个或多个条件进行排序
[LIMIT {[rowcount,pagesize]}]  -- 指定查询记录哪条到哪条 分页

说明：
用中括号([])括起来的部分表示是可选的，用大括号({})括起来的部分是表示必须从中选择其中的一个。

指定查询字段

-- 查询全部的学生
SELECT * FROM student

-- 查询指定字段
SELECT `studentno`,`studentname` FROM student

-- 别名 给结果起一个别名 AS,可以给字段起别名，也可以给表起别名
SELECT `studentno` AS '学号',`studentname` AS '名字' FROM student

-- 函数 CONCAT(str1,str2,...) 拼接字符串
SELECT CONCAT('姓名:',studentname) AS '新名字' FROM student

有的时候，列名字不是那么的见名知意。我们起别名 AS 字段名 as 别名 表名 as 别名

distinct

作用:去除SELECT查询出来的结果中重复的数据，重复的数据只显示

-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM result  -- 查询全部的考试成绩
SELECT studentno FROM result  -- 查询那些同学参加了考试
SELECT DISTINCT studentno FROM result  -- 发现重复数据，去重

数据库的列（表达式）

SELECT VERSION()  -- 查询系统版本
SELECT 100*2-1 AS 计算结果  -- 用来计算
SELECT @@auto_increment_increment  -- 查询自增的步长
-- 考试成绩+1查看
SELECT studentno,studentresult+1 AS 提分后 FROM result

数据库中的表达式：文本值，列，NULL，函数，计算表达式，系统变量......

select 表达式 from 表

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

-- and &&
SELECT studentno,studentresult FROM result
WHERE studentresult>=95 && studentresult<=100

-- BETWEEN AND 模糊查询 区间
SELECT studentno,studentresult FROM result
WHERE studentresult BETWEEN 95 AND 100

-- 除了1000号学生之外的成绩
-- !=
SELECT * FROM result 
WHERE studentno!=1000
-- NOT
SELECT * FROM result
WHERE NOT studentno=1000

模糊查询

运算符	语法	描述
IS NULL	a is null	如果操作符为NUII,结果为真
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,3...)	假设a在a1,或者2.... 其中的某一个值中, 结果为真

-- 查询姓张的同学
SELECT studentno, studentname FROM student
WHERE studentname LIKE '张%'

-- 查询姓张的同学，名字只有一个字的
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
-- 查询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
-- 查询地址为空的学生
SELECT studentno, studentname FROM student
WHERE address="" OR address IS NULL

-- 查询地址为不空的学生
SELECT studentno, studentname FROM student
WHERE address IS NOT NULL

连表查询

-- 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

-- left join
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student s
LEFT JOIN result r
ON s.studentno=r.studentno

-- right join
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student s
RIGHT JOIN result r
ON s.studentno=r.studentno

-- 查询缺考的同学
-- 成绩为NULL
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student s
LEFT JOIN result r
ON s.studentno=r.studentno
WHERE studentresult IS NULL

-- 思考题(查询了参加考试的同学信息: 学号，学生姓名，科目名，分数)
/*思路
1.分析需求，分析查询的字段来自哪些表， student、 result、 subject (连接查询)
2.确定使用哪种连接查询? 7种
确定交叉点(这两个表中哪个数据是相同的)
判断的条件:学生表的中 studentNo =成绩表studentNo
*/
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

-- 我要查询哪些数据select
-- 从那几个表中查FROM 表xxx Join 连接的表 on 交叉条件
-- 假设存在一-种多张表查询，慢慢来，先查询两张表然后再慢慢增加

自连接

自己的表和自己的表连接，核心：一张表拆成两张一样的表

建表语句

CREATE TABLE `school`.`category`( 
    `categoryid` INT(3) NOT NULL COMMENT 'id', 
    `pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', 
    `categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', 
    PRIMARY KEY (`categoryid`) 
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
insert into `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) values ('3', '1', '软件开发');
insert into `school`.`category` (`categoryid`, `PId`, `categoryname`) values ('5', '1', '美术设计');
insert iNTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VAlUES ('4', '3', '数据库'); 
insert into `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) values ('8', '2', '办公信息');
insert into `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) values ('6', '3', 'web开发'); 
inserT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) valueS ('7', '5', 'ps技术');

父类

categoryid	categoryname
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryname
3	4	数据库
2	8	办公信息
3	7	web开发
5	6	美术设计

操作：

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父子信息：把一张表看为两个一摸一样的表
SELECT a.categoryname 父栏目,b.categoryname 子栏目
FROM category a,category b
WHERE a.pid=b.categoryid

练习

-- 查询学员所属的年级(学号， 学生的姓名，年级名称)
SELECT studentno,studentname,gradename
FROM student s
INNER JOIN grade g
ON s.gradeid=g.gradeid

-- 查询科目所属的年级(科目名称，年级名称)
SELECT subjectname,gradename
FROM `subject` s,grade g
WHERE s.gradeid=g.gradeid

-- 查询了参加 C语言-1 考试的同学信息:学号， 学生姓名，科目名，分数
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
LEFT JOIN subject sub
ON s.gradeid=sub.gradeid
INNER JOIN result r
ON s.studentno=r.studentno
WHERE sub.subjectname='C语言-2'

分页和排序

排序

-- 查询了参加 C语言-1 考试的同学信息:学号， 学生姓名，科目名，分数
-- 查询的结果按照成绩进行排序
-- order by : 通过哪个字段进行排序
-- 升序 : ASC(默认)
-- 降序 ：DESC
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
LEFT JOIN subject sub
ON s.gradeid=sub.gradeid
INNER JOIN result r
ON s.studentno=r.studentno
WHERE sub.subjectname='C语言-2'
ORDER BY studentresult DESC

分页

-- 对查询结果进行分页显示
-- limit 查询起始下标,页面大小
-- 第一页 ： limit 0,3 = limit（1-1）*3,3
-- 第二页 ： limit 3,3 = limit（2-1）*3,3
-- 第三页 ： limit 6,3
-- 弟N页 ： limit (n-1)*pagesize,pagesize
-- pagesize:页面大小
-- (n-1)*pagesize:起始值
-- n:当前页
-- 数据总数 / pagesize = 总页数 
SELECT * from student
LIMIT 0,3

语法：limit 查询起始下标 , pagesize

练习

-- 查询C语言-2课程成绩排名前十的学生，并且分数要大于60的学生信息(学号，姓名，课程名称，分数)
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno=r.studentno
INNER JOIN subject sub
ON r.subjectno=sub.subjectno
WHERE r.studentresult>60
AND subjectname='C语言-2'
LIMIT 0,10

子查询

select ... from 表1 where 字段 = （select 字段 from 表2）

-- 查询 C语言-2 分数不小于80分的学生的学号和姓名
-- 方法1 表连接+子查询
SELECT DISTINCT s.studentno,studentname 
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
WHERE r.studentresult>60
AND subjectno=(SELECT subjectno FROM `subject` WHERE subjectname='C语言-2')

-- 方法2  表连接
SELECT DISTINCT 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 r.studentresult>60
AND subjectname='C语言-2'

-- 方法3  子查询
SELECT studentno,studentname FROM student WHERE studentno IN (
SELECT studentno FROM result WHERE studentresult>60 AND subjectno=(
SELECT subjectno FROM `subject` WHERE subjectname='C语言-2'))

分组和过滤

-- 查询不同课程的平均分>80、最高分、最低分
-- 核心： 根据不同课程分组
SELECT subjectname,AVG(studentresult) 平均分,MAX(studentresult),MIN(studentresult)
FROM result r
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
GROUP BY subjectname  -- 通过什么字段来分组
HAVING 平均分>80  -- 分组后过滤

MySQL函数

官方文档：https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html

常用函数

-- 数学运算
SELECT ABS(-2)  -- 绝对值
SELECT CEILING(5.5)  -- 向上取整
SELECT FLOOR(5.5)  -- 向下取整
SELECT RAND()  -- 返回一个0~1之间的随机数
SELECT SIGN(0)  -- 判断一个数的符号 负数返回-1 0返回0 正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('123456')  -- 字符串的长度
SELECT CONCAT('我','爱','你')  -- 拼接字符串
SELECT INSERT('我爱编程',2,1,'超级热爱')  -- 字符串替换 返回 我超级热爱编程 
SELECT LOWER('GodTao')  -- 大写转小写
SELECT UPPER('GodTao')  -- 小写转大写
SELECT INSTR('zt','h')  -- 返回第一次出现字串的索引
SELECT REPLACE('你在干什么','干什么','么')  -- 替换出现的指定字符串
SELECT SUBSTR('你好呀',2,2)  -- 返回指定的字符串 （源字符串  截取位置  截取长度）
SELECT REVERSE('ABCDE')  -- 字符串反转

-- 时间和日期函数（*）
SELECT CURDATE()  -- 获取当前日期 2020-11-24
SELECT CURRENT_DATE()  -- 获取当前日期 2020-11-24
SELECT NOW()  -- 获取当前的时间  2020-11-24 12:14:22
SELECT LOCALTIME()  -- 获取本地时间  2020-11-24 12:14:08
SELECT SYSDATE()  -- 获取系统时间  2020-11-24 12:13:56
-- 获取当前的年月日 时分秒
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系统
SELECT SYSTEM_USER()  -- root@localhost
SELECT USER()  -- root@localhost
SELECT VERSION()  -5.7.29

聚合函数

函数名称	描述
count()	计数
sum()	求和
avg()	平均值
max()	最大值
min()	最小值

-- count() 能够统计表中的数据
SELECT COUNT(studentname) FROM student  -- count(指定列)  会忽略所有的null值

SELECT COUNT(*) FROM student  -- count(*)  不会忽略null

SELECT COUNT(1) FROM student  -- count(1)  不会忽略null

SELECT SUM(studentresult) 总和 FROM result
SELECT AVG(studentresult) 平均分 FROM result
SELECT MAX(studentresult) 最高分 FROM result
SELECT MIN(studentresult) 最低分 FROM result

count(1)与count(*)区别

1.如果在开发中确实需要用到count()聚合，那么优先考虑count()，因为mysql数据库本身对于count()做了特别的优化处理。

有主键或联合主键的情况下，count(*)略比count(1)快一些。

没有主键的情况下count(1)比count(*)快一些。

如果表只有一个字段，则count(*)是最快的。

2.使用count()聚合函数后，最好不要跟where age = 1；这样的条件，会导致不走索引，降低查询效率。除非该字段已经建立了索引。使用count()聚合函数后，若有where条件，且where条件的字段未建立索引，则查询不会走索引，直接扫描了全表。

3.count(字段),非主键字段，这样的使用方式最好不要出现。因为它不会走索引。

数据库级别的MD5加密（扩展）

MD5()函数

-- 测试MD5加密 --
CREATE TABLE `testMD5`(
	`id` INT(4) auto_increment PRIMARY KEY,
	`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 明文密码
INSERT INTO testMD5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')
 
-- 加密
UPDATE testMD5 SET pwd=MD5(pwd)

-- 插入的时候加密
INSERT INTO testMD5 VALUE(4,'jy',MD5('666666'))

-- 如何进行校验
SELECT * FROM testMD5 WHERE NAME='zhangsan' AND pwd=MD5('123456')

5、事物

事物原则 ACID 原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

原子性

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，一个都不执行

一致性

事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。

隔离性

一个事务的执行不能被其他事务所影响

持久性

一旦事务提交，则其所做的修改会永久保存到数据库。

事物隔离所导致的一些问题

脏读：一个事务读取了另外一个事务未提交的数据。

不可重复读：在一个事务内部读取表中的某一行数据，多次读取结果不同。

虚读（幻读）：在一个事务内读取到了其他事务插入的数据，导致前后读取不一致。

-- mysql默认是开启事务自动提交的
SET autocommit=0  -- 关闭
SET autocommit=1  -- 开启（默认）

-- 手动处理事务
SET autocommit=0  -- 关闭自动提交
 
-- 事务开启
START TRANSACTION  -- 标记一个事务的开启

-- INSERT XXX
-- INSERT XXX

-- 提交 持久化 成功
COMMIT

-- 回滚  回到原来的样子  失败
ROLLBACK

-- 事务结束
SET autocommit=1  -- 开启自动提交

SAVEPOINT 保存点名称  -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名称  -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称  -- 删除保存点

6、索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

索引的分类

主键索引（primary key）
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引（unique key）
- 避免重复的列出现，唯一索引可重复，多个列都可以表示为唯一索引
常规索引（key/index）
- 默认的，index、key等关键字来设置
全文索引（fulltext）
- 在特定的数据库引擎下才有
- 快速定位数据

基本语法

-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student

-- 创建索引 alter\create
ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）
-- 增加一个全文索引 (索引名) 列名
ALTER TABLE schoo1 . student ADD FULLTEXT INDEX `studentName` (`studentName`)

CREATE INDEX index_name ON table_name(username(length)); 
-- create只能添加这两种索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

-- 删除索引
-- dorp删除
drop index index_name on table_name
-- alter删除
alter table table_name drop index index_name
alter table table_name drop primary key 

-- EXPLAIN分析sq1执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');

索引原则

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表不需要加索引
索引一般用在经常查询的字段上

索引的数据结构

参考：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

7、权限管理和备份

用户权限管理

-- 创建用户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
-- ROOT用户权限: GRANT ALL PRIVILEGES ON *.* TO ' root'@ 'localhost' WITH GRANT OPTION

-- 撤销权限REVOKE 哪些权限，在哪个库撤销， 给谁撤销
REVOKE ALL PRIVILEGES ON *. * FROM kuangshen2
--删除用户
DROP USER kuangshen

数据库备份

为什么要备份？

保证数据库数据不丢失
数据库转移

mysql数据库备份的方式？

直接拷贝物理文件 data文件夹
在可视化数据库管理工具中手动导出
使用命令行导出 mysqldump

使用命令行导出 mysqldump命令的使用

# 导出
# mysqldump -h 主机 -u 用户名 -p密码 数据库表名 > 物理磁盘位置/文件名
mysq1 dump -h1oca1host -uroot -p123456 schoo1 student >D:/a.sql

# mysqldump -h 主机 -u 用户名 -p密码 数据库表1 表2 表3 > 物理磁盘位置/文件名
mysq1 dump -h1oca1host -uroot -p123456 schoo1 student >D:/b.sql

# mysqldump -h 主机-u 用户名 -p密码 数据库 > 物理磁盘位置/文件名
mysq1dump -h1ocalhost -uroot -p123456 schoo1 >D:/c.sql

# 导入
# 登录的情况 下，切换到指定的数据库
# source 备份文件
source d: /a.sq1
# 不登录情况
mysql -u 用户名 -p密码 库名< 备份文件

8、三大范式

第一范式(1NF)

原子性：保证每一列不可再分

第二范式(2NF)

前提：满足第一范式
每张表之描述一件事情

第三范式(3NF)

前提：满足第一范式和第二范式
确保数据表中每一列数据都和主键直接相关，而不能间接相关

规范性和性能的问题

阿里规范：关联查询的表不得超过三张表

考虑商业化的需求和目标，数据库的性能更加重要

在规范性能问题的时候，需要适当考虑一下规范性。

详细理解参考：https://www.cnblogs.com/wsg25/p/9615100.html

posted @ 2020-11-25 14:09 GodTao 阅读(155) 评论(0) 收藏举报

刷新页面返回顶部

GodTao