MySQL

MySQL

1.数据库

数据库(DB，DateBase), 数据库是所有软件体系中最核心的存在 DBA（数据库运维）

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，它产生于距今六十多年前，随着信息技术和市场的发展，特别是二十世纪九十年代以后，数据管理不再仅仅是存储和管理数据，而转变成用户所需要的各种数据管理的方式

数据库的分类

关系型数据库：（SQL）

关系型数据库，就是把数据放入表格中，这些表格可以相互关联。它的理论提出于1970年，提出者叫做埃德拉·科德（Edgar Codd），1981年因为在数据库领域的贡献，获得图灵奖。

• MySQL, Oracle, Sql Sever,
• 通过表和表之间，行和列之间的关系进行数据的存储。

非关系型数据库：（No SQL）Not Only sql

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

DBMS (数据库管理软件)

**数据库管理系统（DBMS）是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库。**DBMS 是一个庞大且复杂的产品，几乎都是由软件供应商授权提供的，如 Oracle 公司的 Oracle 和 MySQL、IBM 公司的 DB2、Microsoft 公司的 Access 和 SQL Server，这些 DBMS 占据了大部分的市场份额。

2.MySQL简介

官网：MySQL

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，属于 Oracle旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS应用软件之一。

关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。

3.连接数据库

mysql -uroot -p --连接数据库

	show databases; --查看所有的数据库
	
    use school --切换数据库 use 数据库名
	Database changed  --成功
	
	show tables; --查看数据库中所有的表
	
	describe student; --显示数据库中所有表的信息
	
	create database westos; --创建一个数据库
	
	exit； --退出连接

DDL ：数据库定义语言
DML：数据库操作管理语言
DQL：数据库查询语言
DCL：数据库控制语言

4.操作数据库

mysql 不区分大小写

操作数据库

1. 创建数据库

	CREATE DATABASE [IF NOT EXISTS] westos;

2. 删除数据库

	DROP DATABASE [IF EXISTS] westos;

3. 使用数据库

	--tab 楗的上面，如果你的表名或者字段名是一个特殊字符，就需要带 ``
	use `school`

4. 查看数据库

	SHOW DATABASES --查看所有的数据库

数据类型（列类型）

数值

1.整数型

数据类型	用途	字节大小
TINYINT	十分小的数据	1 字节
SMALLINT	小整数值	2 字节
MEDIUMINT	中等整数值	3 字节
INT	整数值	4 字节
BIGINT	大整数值	8 字节

2.浮点型

数据类型	用途	字节大小
FLOAT	单精度浮点小数值	4 字节
DOUBLE	双精度浮点小数	8 字节

3.定点型

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

decimal(m,d)
DECIMAL 类型不同于FLOAT和DECIMAL，其中DECIMAL 实际是以串存放的。DECIMAL 可能的最大取值范围与DOUBLE 一样，但是其有效的取值范围由M 和D 的值决定。给定的DECIMAL 类型的取值范围取决于MySQL数据类型的版本。
对货币等对精度敏感的数据，应该用定点数表示或存储；

4.字符型

数据类型	用途	字节大小
CHAR	定长字符串	0-255字节
VARCHAR	变长字符串	0-255字节
TINYTEXT	短文本字符串	0-255字节
TEXT	长文本数据	0-65535字节

5.日期时间

数据类型	格式	字节大小
DATE	YYYY-MM-DD	4字节
TIME	HH:MM:SS	3字节
DATETIME	YYYY-MM-DD HH:MM:SS	8字节
TIMESTAMP	YYYYMMDD HHMMSS	4字节
YEAR	YYYY	1字节

创建数据库表

CREATE TABLE IF NOT EXISTS `student`(
    -- 所有的语句后面加,最后一句不需要加
	`id` VARCHAR(10) NOT NULL COMMENT '编号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456'COMMENT'密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT'性别',
	`birthday` DATE	DEFAULT NULL COMMENT '出生日期',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR (50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

SHOW CREATE DATABASE westos  --查看创建数据库的语句
show create table student --查看student数据表的定义结构
desc student --显示表的结构

两种存储引擎

MyISAM是MySQL的默认数据库引擎（5.5版之前），由早期的 ISAM （Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。

不过，5.5版本之后，MySQL引入了InnoDB（另一种数据库引擎），以强化参考完整性与并发违规处理机制，后来就逐渐取代MyISAM。与传统的ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务（Transaction）功能，类似于PostgreSQL。目前InnoDB采用双轨制授权，一是GPL授权，另一是专有软件授权。

MySQL存储引擎InnoDB与Myisam的区别

	Myisam	InnoDB
事务	不支持	支持
外键	不支持	支持
数据行锁定	不支持	支持
全文索引	支持	不支持（MySQL 5.6 开始支持）
存储结构	每个MyISAM在磁盘上存储成三个文件。	所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件）
存储空间	可被压缩，存储空间较小。	较大
崩溃后的安全恢复	不支持	支持
表主键	允许没有任何索引和主键的表存在	如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)
表的具体行数	保存	不保存
存储文件	Myisam：frm是表定义文件，myd是数据文件，myi是索引文件	Innodb：frm是表定义文件，ibd是数据文件

修改和删除数据库表

重命名表

-- RENAME TABLE 原名 TO 新名;
RENAME TABLE `student` TO `student1`;
-- ALTER TABLE 原名 RENAME as 新名;
ALTER TABLE `student1` RENAME as`student`;

删除表

DROP TABLE 表名;

添加字段

-- ALTER TABLE 表名 ADD 列名 数据类型 约束;
ALTER TABLE `student` ADD `age` INT(10);

修改字段

-- ALTER TABLE 表名 MODIFY 字段名 数据类型;    修改字段数据类型
ALTER TABLE `student` MODIFY `age` VARCHAR(10);

-- ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型;    字段重命名
ALTER TABLE `student` CHANGE `age` `age1` INT(10);

-- change 可以更改列名 和 列类型 ，modify 只能更改列属性

删除字段

-- ALTER TABLE 表名 DROP 列名字
ALTER TABLE `student` DROP `age1`

外键

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。

5.DML语言

insert

-- insert into 表名（[字段名1，字段2，子段3]）values('值1','值2','值3'),('值1','值2','值3')
INSERT INTO `student`(`id`,`name`,`age`)
VALUE ('001','快乐小子',20)

insert into `student`(`id`,`name`,`age`) values('002','快乐',25),('003','小子',26)

VALUE与VALUES都是正确的，只是两者对不同语句插入数量的执行效率各不相同。

在插入单行时，使用VALUES比较快。插入多行时，用VALUE比较快

Update

-- UPDATE 表名 set 字段名 = 值 WHERE 条件
UPDATE `student` set `name` = '乐乐' WHERE `id` = '001'

delete

-- DELETE FROM 表名 WHERE 条件
DELETE FROM `student` WHERE `id` = '003'


-- 清空数据库  表的结构和索引不会变
TRUNCATE `student`

区别：

TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。

SQL 性质的不同

delete：属于 DML(数据操作语言) 范畴

truncate：属于 DDL(数据定义语言) 范畴

TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。

TRUNCATE之后的自增字段从头开始计数了，而DELETE的仍保留原来的最大数值

了解即可:

DELETE删除的问题，重启数据库，现象

lnnoDB自增列会重1开始(存在内存当中的，断电即失)

MyISAM继续从上一个自增量开始(存在文件中的，不会丢失)

6.DQL查询数据（重点）

指定查询

-- 查询全部数据
SELECT * FROM `student`

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

DISTINCT 去重

-- 作用 : 去掉SELECT查询返回的记录结果中重复的记录
SELECT DISTINCT studentno FROM result; -- 了解:DISTINCT 去除重复项 , (默认是ALL)

逻辑操作符

作用：检索数据中符合条件的值

操作符	语法	描述
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;

-- 除了1000号同学,要其他同学的成绩
SELECT studentno,studentresult
FROM result
WHERE studentno!=1000;

-- 使用NOT
SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;

模糊查询(比较运算符)

操作符	语法	描述
IS NULL	a lS NULL	若操作符为NULL，则结果为真
IS NOT NULL	a lS NOT NULL	若操作符不为NULL，则结果为真
BETWEEN…AND…	a BETWEEN b AND c	若a范围在b与c之间，则结果为真
LIKE	a LlKE b	SQL模式匹配，若a匹配b，则结果为真
IN	a lN(a1 , a2， a3，…)	若a等于a1,a2…中的某一个，则结果为真

-- LIKE
-- 查询姓刘的同学的学号及姓名
-- 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 '刘__';

-- 查询姓名中含有 嘉 字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%嘉%';

-- IN
-- 查询学号为1000,1001,1002的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);

-- 查询地址在北京,南京,河南洛阳的学生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛阳');

-- NULL 空
-- 查询出生日期没有填写的同学
-- 不能直接写=NULL , 这是代表错误的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;

-- 查询出生日期填写的同学
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;

联表查询

-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)

-- 内连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno

-- 右连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno

-- 左连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno

-- 查一下缺考的同学(左连接应用场景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

排序

-- 升序：ASC  降序：DESC
-- ORDER BY
-- 按成绩降序排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC

分页

-- limit 起始值，页面大小
/*
   第一页 : limit 0,5
   第二页 : limit 5,5
   第三页 : limit 10,5
   ......
   第N页 : limit (pageNo-1)*pageSzie,pageSzie
   [pageNo:页码,pageSize:单页面显示条数]
   */
-- 每页显示5条数据
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5

分组过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于802 --核心:（根据不同的课程分组)
SELECT subjectName，AvG(studentResu1t)AS 平均分,MAX(StudentResu1t)AS最高分,MIN(StudentResult) AS最低分
 FROM result r
INNER JOIN `subject` sub
oN r.`subjectNo` = sub.`subjectNo`
GRoUP BY r.subjectNo --通过什么字段来分组
HAVING 平均分>80

7.MySQL函数

数值型函数

函数名称	作 用
ABS	求绝对值
SQRT	求二次方根
MOD	求余数
CEIL 和 CEILING	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR	向下取整，返回值转化为一个BIGINT
RAND	生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND	对所传参数进行四舍五入
SIGN	返回参数的符号
POW 和 POWER	两个函数的功能相同，都是所传参数的次方的结果值
SIN	求正弦值
ASIN	求反正弦值，与函数 SIN 互为反函数
COS	求余弦值
ACOS	求反余弦值，与函数 COS 互为反函数
TAN	求正切值
ATAN	求反正切值，与函数 TAN 互为反函数
COT	求余切值

字符串函数

函数名称	作 用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE	字符串替换函数，返回替换后的新字符串
SUBSTRING	截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

日期和时间函数

函数名称	作 用
CURDATE 和 CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW 和 SYSDATE	两个函数作用相同，返回当前系统的日期和时间值
UNIX_TIMESTAMP	获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
FROM_UNIXTIME	将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数
MONTH	获取指定日期中的月份
MONTHNAME	获取指定日期中的月份英文名称
DAYNAME	获取指定曰期对应的星期几的英文名称
DAYOFWEEK	获取指定日期对应的一周的索引位置值
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR	获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH	获取指定日期是一个月中是第几天，返回值范围是1~31
YEAR	获取年份，返回值范围是 1970〜2069
TIME_TO_SEC	将时间参数转换为秒数
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE	两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE	两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME	时间加法运算，在原始时间上添加指定的时间
SUBTIME	时间减法运算，在原始时间上减去指定的时间
DATEDIFF	获取两个日期之间间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT	格式化指定的日期，根据参数返回指定格式的值
WEEKDAY	获取指定日期在一周内的对应的工作日索引

聚合函数

函数名称	作用
MAX	查询指定列的最大值
MIN	查询指定列的最小值
COUNT	统计查询结果的行数
SUM	求和，返回指定列的总和
AVG	求平均值，返回指定列数据的平均值

 -- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。
 -- count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
 -- count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录；
 -- count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 。
 /*
对比： 
 1）在表没有主键时，count(1)比count(*)快
 2）有主键时，主键作为计算条件，count(主键)效率最高；
 3）若表格只有一个字段，则count(*)效率较高。
 */

SELECT COUNT(studentname) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;  /*推荐*/

8.事务

事务（Transaction），一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。

1. 事务原则：ACID原则

参考博客连接：https://blog.csdn.net/dengjili/article/details/82468576

• 原子性（Atomicity）

事务中的操作要么都发生，要么都不发生。

• 一致性（Consistency）

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

• 隔离性（Isolation）

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

• 持久性（Durability）

持久性是指一个事务一旦被提交则不可逆，被持久化到数据库中

2. 隔离所导致的一些问题：

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

不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

-- 使用set语句来改变自动提交模式
SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启*/

-- 注意:
-- 1.MySQL中默认是自动提交
-- 2.使用事务时应先关闭自动提交

-- 开始一个事务,标记事务的起始点
START TRANSACTION  

-- 提交一个事务给数据库
COMMIT

-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK

-- 还原MySQL数据库的自动提交
SET autocommit =1;

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

9.索引

什么是索引？

索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

通俗点的说，数据库索引好比是一本书的目录，可以直接根据页码找到对应的内容，目的就是为了加快数据库的查询速度。

原理：索引的存储原理大致可以概括为一句话：以空间换时间。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

索引的分类：

主键索引：primary key

• 设定为主键后，数据库自动建立索引，InnoDB为聚簇索引，主键索引列值不能为空（Null）。

唯一索引：

• 索引列的值必须唯一，但允许有空值（Null），但只允许有一个空值（Null）。

复合索引：

• 一个索引可以包含多个列，多个列共同构成一个复合索引。

全文索引：

• Full Text（MySQL5.7之前，只有MYISAM存储引擎引擎支持全文索引）。
• 全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引可以在Char、VarChar 上创建。

空间索引：

• MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型，MySQL在空间索引这方年遵循OpenGIS几何数据模型规则。

前缀索引：

• 在文本类型为char、varchar、text类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。