MySQL

1、数据库分类

关系型数据库（SQL）

• Oracle、Mysql、Sql Server
• 通过表和表之间，行和列之间的关系进行数据的存储，信息表，考勤表，…

非关系型数据库（NoSQL）Not Only

• Redis，MongDB
• 对象存储，通过对象的自身属性来决定

DBMS数据库管理系统

• 数据库管理软件，科学有效的管理我们的数据。维护和获取数据；
• MySQL，数据库管理系统

2、SQLyog

1.创建数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7aWw5mMi-1594363059712)(C:\Users\42084\AppData\Roaming\Typora\typora-user-images\image-20200706183542454.png)]

2.创建表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e4M7ceQE-1594363059719)(C:\Users\42084\AppData\Roaming\Typora\typora-user-images\image-20200706183834881.png)]

3、连接数据库

使用命令行操作数据库

mysql -uroot -ppassword --连接数据库
show databases; --查看所有数据库
use school --切换数据库
show tables; --展示所有的表
describe student; --查看表结构
create database westors; --创建数据库
exit; --退出链接

数据库的语言

DDL 数据库定义语言

DML 数据库操作语言

DQL 数据库查询语言

DCL 数据库控制语言

4、操作数据库

mysql不区分大小写

1.操作数据库

1.创建数据库

创建westos数据库如果，[如果存在就退出]

CREATE DATABASE[IF NOT EXISTS] westos

2.移除数据库

删除westos数据库，[如果存在再删除]

DROP DATABASE [IF EXISTS] westos

3.使用数据库

切换数据库，（如果字段名是特殊字符需要使用``修饰）

USE `mysql`

4.查看数据库

SHOW DATABASES

2.数据库的数据类型

数值

• tinyint 1个字节
• smallint 2个字节
• mediumint 3个字节
• int 4个字节
• bigint 8个字节
• float 4个字节
• double 8个字节
• decimal 字符串形式的浮点数，金融计算

字符串

• char 固定大小 0-255

• vachar 可变字符串 0-65535 String

• tinytext 微型文本 2^8-1

• text 文本串 2^16-1

时间日期

java.util.Date

• date YYYY-MM-DD，日期格式
• time HH：mm：ss，时间格式
• datetime YYYY-MM-DD HH：mm：ss 最常用的时间格式
• timestamp 时间戳 1970.1.1到现在的毫秒数 较常用
• year 年份

null

• 没有值，未知
• 不要用null进行运算，结果为null

3.数据库的字段属性

Unsigned：

• 无符号整数
• 不能声明为负数

Zerofill：

• 0填充
• 不足的位数使用0填充

自增：

• 自动再上一条的基础上+1（默认）
• 通常设置为主键，必须是int类型
• 可以自定义自增的起始值和步长

非空：

• 不给他赋值，就会报错

默认：

• 设置默认的值

4.创建数据库表

 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 '出生日期', 
     `address` VARCHAR(100) DEFAULT NULL COMMENT '地址', 
     `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
     PRIMARY KEY(`id`) 
 )ENGINE=INNODB CHARSET = utf8; 

SHOW CREATE DATABASE school --查询用来创建school数据库的语句
SHOW CREATE TABLE student   --查询用来创建student表的语句
DESC student                --查询student表的结构

5.数据库引擎

INNODB  --默认使用
MYISAM  --早些年使用		

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为MYISAM的两倍

常规使用：

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

物理空间的位置

• 所有数据库文件都存在data目录下，一个文件夹就对应一个数据库

• 本质还是文件存储！

MySQL引擎再物理文件上的区别

• InnoDB 再数据库表种只有一个 *.frm文件，一级上级目录下的 ibata1 文件
• MYISAM对应的文件
  • *.frm -表结构的定义文件
  • *.MYD - 数据文件（data）
  • *.MYI - 索引文件（index）

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

CHARSET=utf8

可以再my.ini中配置默认的编码

character-set-server=utf8

6.修改删除表

ALTER TABLE `teacher` RENAME AS `teacher1`		 --修改表名
ALTER TABLE `teacher1` ADD age INT(11)    		 --添加字段
ALTER TABLE `teacher1` MODIFY age VARCHAR(11) 	 --修改约束
ALTER TABLE `teacher1` CHANGE age age1 INT(1)    --修改字段名和约束
ALTER TABLE `teacher1` DROP age1                 --删除字段
DROP TABLE IF EXISTS `teacher1`                  --删除表

5、MySQL数据管理

1、外键（了解即可，不建议使用）

方式一：创建表时就加外键

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 '年级id',
     `address` VARCHAR(100) DEFAULT NULL COMMENT '地址', 
     `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
     PRIMARY KEY(`id`) ,
     KEY `FK_gradeid`(`gradeid`) ,
     CONSTRAINT `FK_gradedid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
 )ENGINE=INNODB CHARSET = utf8;

删除有外键关系的表的时候，必须先删除引用别人的表（从表），再删除被引用的表（主表）

方式二：创建表之后加外键

 ALTER TABLE `student`
 ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)

2、DML语言

DML语言：数据操作语言

• insert
• update
• delete

3、添加

--同时插入多条数据
INSERT INTO `grade`(`gradename`) VALUES('大四'),('大三'),('大热')

4、修改

UPDATE `grade` SET `gradename` = '大二',`gradeid`=5 WHERE `gradeid`=4

运算符	含义
=	等于
<> 或 !=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN a AND b	[ a , b ]
AND	且
OR	或

--插入当前日期 2020-07-07 18:15:49
UPDATE student SET `birthday`=CURRENT_TIME WHERE id  =1 

5、删除

delete

DELETE FROM `student` WHERE id = 1

truncate（清空表数据）

TRUNCATE `student`

truncate 和 delete

• 相同点：都会删除数据，都不会删除表结构
• 不同点：
  • truncate 重新设置自增列，计数器归零
  • truncate 不会影响事务

Delete删除的问题，重启数据库后出现现象

• InnoDB 自增会从1开始（存在内存中的，断电即失）
• MylSAM 继续从上一个自增量开始（存在文件中，不会丢失）

6、DQL查询数据

1、DQL语言

数据查询语言

2、别名展示查询结果

SELECT `id`+1 AS 学号,`contact` AS 姓名 FROM smbms_address AS s

--拼接字符串，所有的查询结果前面都有ID:
SELECT CONCAT('ID:',id) FROM smbms_address 

3、去重查询

SELECT  DISTINCT`userPassword` FROM smbms_user

4、模糊查询

运算符	语法	描述
IS NULL	a is null	判断a是null
IS NOT NULL	a is not null	判断a不是null
BETWEEN	a between b and c	判断a在b和c之间
Like	a like b	判断 a 匹配 b
In	a in(a1,a2,a3 )	判断a是否在集合中
%	%红%	判断文字中是否有红
_	_红	判断第二个字是不是红

--查询名字中含有张的
SELECT  * FROM smbms_user WHERE userName LIKE '%张%'
--查询以张开头且只有两位的名字
SELECT  * FROM smbms_user WHERE userName LIKE '张_'
--查询名字是张华的或者是张三的
SELECT  * FROM smbms_user WHERE userName IN ('张华','张三')

5、联表查询

SELECT b.`id`,`billCode`,`productName`,`productDesc` 
FROM `smbms_bill` AS b
INNER JOIN `smbms_provider` AS p
WHERE b.`providerId` = p.`id`

操作	描述
Inner Join	表中至少有一个匹配，就返回行
Left Join	会从左表中返回所有的值，即使右表没有匹配
Right Join	会从右表中返回所有的值，即使左表表没有匹配

6、分页和排序

排序

--根据productCount降序排 DESC：降序 ASC：升序
SELECT * FROM`smbms_bill` ORDER BY `productCount` DESC

分页

--从第0条数据往后插叙5个
SELECT * FROM`smbms_bill` LIMIT 0,5

7、子查询

SELECT `id`,`productName`,`productCount`
FROM `smbms_bill`
WHERE `providerId` = (
SELECT`id`
FROM`smbms_provider`
WHERE `proName` = '北京三木堂商贸有限公司'
)
ORDER BY `productCount` DESC

7、MySQL函数

1.常用函数

SELECT ABS(-8)  				--绝对值
SELECT CEILING(9.4) 			--向上取整
SELECT FLOOR(9.4)   			--向下取整
SELECT RAND()					--0~1随机数
SELECT SIGN(-15)				--返回参数的符号
SELECT CHAR_LENGTH("123456")	--字符串的长度
SELECT CONCAT("wo","ai","ni")	--拼接字符串
SELECT INSERT("我爱你helloword",1,2,"超级热爱")  
--从第一个开始，替换两个 结果：超级热爱你helloword
SELECT LOWER('XieDong')			--转小写
SELECT UPPER('XieDong')			--转大写
SELECT INSTR("xiedong",'e')		--查询e在xiedong中出现的索引
SELECT REPLACE ('坚持就能成功','坚持','努力')
--把坚持换为努力
SELECT SUBSTR('坚持就能成功',4,2)	--截取字符串，从第四个开始，截取两个
SELECT REVERSE("坚持就能成功")	--反转字符串
SELECT USER()					--查看root用户

--查询所有姓王的，并把王改成赵
SELECT REPLACE(`contact`,'王','赵') FROM`smbms_address` WHERE `contact` LIKE '王%'

事件日期函数

SELECT CURRENT_DATE()			--当前日期  YYYY-MM-DD
SELECT CURDATE()				--当前日期  YYYY-MM-DD
SELECT NOW()					--当前时间  YYYY-MM-DD HH:mm:ss
SELECT LOCALTIME()				--本地时间  YYYY-MM-DD HH:mm:ss
SELECT SYSDATE()				--系统时间  YYYY-MM-DD HH:mm:ss
--当前时间得 年 月 日 时 分 秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

2、聚合函数

函数名	描述
COUNT( )	计数
SUM( )	求和
AVG( )	平均值
MAX( )	最大值
MIN( )	最小值

分组

GROUP BY `列名`
HAVING 分组后满足的条件

--分别查询不同productName且平均值大于500的最大值，最小值，平均值，总和
SELECT MAX(`productCount`),MIN(`productCount`),AVG(`productCount`),SUM(`productCount`)
FROM `smbms_bill`
GROUP BY `productName`
HAVING AVG(`productCount`)>500

8、MD5数据库级别加密

MD5：主要增强算法复杂度和不可逆性

MD5：破解网站的原理，背后有一个字典，MD5加密后的值和加密前的值

--将所有明文密码进行MD5加密
UPDATE testmd5 SET pwd=MD5(pwd)
--插入时直接进行MD5加密
INSERT INTO `testmd5` VALUES(5,"小明",MD5('789'))
--查询时先使用MD5加密，然后再比对加密后的值
SELECT * FROM testmd5 WHERE pwd = MD5(123456)

9、事务（Transaction）

事务原则：ACID原则

1. 原子性
2. 一致性
3. 隔离性
4. 持久性

1.原子性（Atomicity）

指事务是一个不可分割的工作单位，事务中的操作要么都成功，要么都失败

2.一致性（Consistency）

数据前后的完整性必须保持一致

3.持久性（Durability）

一个事务一旦被提交，它对数据库中数据的交换就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

4.隔离性（Isolation）

多个用户并发访问数据库时，数据库为每个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离

隔离会产生的问题

• 脏读
  指一个事务读取了另一个事务未提交的数据

• 不可重复读

  在一个事务内读取表中的某一行数据，多次读取结果不同（不一定是错误）

• 幻读（虚读）

  在一个事务内读取到了别的事务插入的数据，导致前后读取不一致

SET autocommit = 0;					--关闭自动提交
START TRANSACTION;					--开启事务
COMMIT;								--提交
ROLLBACK;							--回滚
SET autocommit = 1;					--开启自动提交
SAVEPOINT '保存点名';				--定义一个保存点
ROLLBACK SAVEPOINT '保存点名';		--回滚到指定保存点
RELEASE SAVEPOINT '保存点名';		--删除指定保存点

10、索引

1、索引分类

• 主键索引 （PRIMARY KEY）
  • 唯一的标识，主键不可重复，只能有一个列
• 唯一索引（UNIQUE KEY）
  • 避免重复数据出现，唯一索引可以重复
• 常规索引（KEY / INDEX）
  • 默认的，index，key关键字设置
• 全文索引（FullText）
  • 在特定的数据库引擎下才有
  • 快速定位数据

--查看表中的所有索引
SHOW INDEX FROM smbms_provider
--为smbms_provider添加一个全文索引，索引名proname，索引列proName
ALTER TABLE`smbms_provider` ADD FULLTEXT INDEX `proname`(`proName`)
--创建一个普通索引
CREATE INDEX `id_smbms_provider_proName` on smbms_provider(`proName`)
--EXPLAIN可以监测sql的执行状态
EXPLAIN SELECT * FROM smbms_provider
--检测使用全文索引的sql状态
EXPLAIN SELECT * FROM smbms_provider WHERE MATCH(`proName`) AGAINST('公司')

2、原则

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

11、权限管理和备份

1、用户管理

--创建一个用户
CREATE USER dong IDENTIFIED BY '123456'
--修改当前用户密码
ALTER USER USER() IDENTIFIED BY '123456';
--修改指定用户的密码
ALTER USER 'dong'@'localhost' IDENTIFIED BY '123456';
--用户重命名
RENAME USER dong TO dongdong
--给'dongdong'@'localhost'用户授予对 *.* 所有库所有表的
--ALL PRIVILEGES所有权限
GRANT ALL PRIVILEGES ON *.* TO 'dongdong'@'localhost';
--查看指定用户的所有权限
SHOW GRANTS FOR 'dongdong'@'localhost';
--撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM 'dongdong'@'localhost';
--删除用户
DROP USER 'dongdong'@'localhost'

2.数据库备份

• 保证重要的数据不丢失
• 数据转移

MySQL 数据库备份的方式

• 直接拷贝物理文件

• 在可视化工具中手动导出

• 使用命令行 mysqldump（在命令行）

  #mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1名 表2名。。。。 > 物理磁盘位置/文件名
  mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
  #导出school数据库
  mysqldump -hlocalhost -uroot -p123456 school >D:/b.sql
  #登录数据库
  mysql -uroot -p123456
  #登录后导入sql文件
  source D:/b.sql
  #
  

12、三大范式

1、第一范式 （1NF）

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

2、第二范式（2NF）

前提：满足第一范式

第二范式：在第一范式的基础上，所有列都要与主键相关

每张表只描述一件事情

3、第三范式（3NF）

前提：满足第二范式

第三范式：在第二范式的基础上，所有列都要与主键直接相关

表中的每一列数据都和主键直接相关，不能间接相关

规范性和新能的问题

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

• 考虑商业话的需求和目标，（成本，用户体验）数据库的性能更加重要

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

工具中手动导出

• 使用命令行 mysqldump（在命令行）

  #mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1名 表2名。。。。 > 物理磁盘位置/文件名
  mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
  #导出school数据库
  mysqldump -hlocalhost -uroot -p123456 school >D:/b.sql
  #登录数据库
  mysql -uroot -p123456
  #登录后导入sql文件
  source D:/b.sql
  #
  

12、三大范式

1、第一范式 （1NF）

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

2、第二范式（2NF）

前提：满足第一范式

第二范式：在第一范式的基础上，所有列都要与主键相关

每张表只描述一件事情

3、第三范式（3NF）

前提：满足第二范式

第三范式：在第二范式的基础上，所有列都要与主键直接相关

表中的每一列数据都和主键直接相关，不能间接相关

规范性和新能的问题

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

• 考虑商业话的需求和目标，（成本，用户体验）数据库的性能更加重要

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