MySQL数据库 - MySQL | Peter Pan = 理想净土 = 凭时间赢来的东西，时间肯定会为之作证

认识数据库

数据库(DataBase),即数据存储的仓库，一个数据库由多个表空间组成
用户可在数据库中对库中的表进行增删改查的操作

数据库管理系统（DBMS）

具有管理数据库的一种软件系统，==具有存储、截取、安全保障、数据备份==等功能

数据库分类

什么是关系模式

关系的描述称为关系模式（Relation Schema）它可以形式化地表示为：
- R（U，D，dom，F）
==其中R为关系名，U为组成该关系的属性名集合，D为属性组U中属性所来自的域，dom为属性向域的映象集合，F为属性间数据的依赖关系集合。==
通常简记为：
- R(U)或R(A1，A2，…,An)
- 其中R为关系名，U为属性名集合，A1，A2，…,An为各属性名。

什么是关系模型

关系实际上就是关系模式在某一时刻的状态或内容。也就是说，关系模式是型，关系是它的值。关系模式是静态的、稳定的，而关系是动态的、随时间不断变化的，因为关系操作在不断地更新着数据库中的数据。但在实际当中，常常把关系模式和关系统称为关系，读者可以从上下文中加以区别。

关系型数据库

定义

- ==采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。==

MySQL
Oracle
SQL Server
SQLite
DB2

非关系型数据库

定义

- ==NoSQL，泛指非关系型的数据库。随着互联网web2.0网站的兴起，传统的关系数据库在处理web2.0网站，特别是超大规模和高并发的SNS类型的web2.0纯动态网站已经显得力不从心，出现了很多难以克服的问题，而非关系型的数据库则由于其本身的特点得到了非常迅速的发展。NoSQL数据库的产生就是为了解决大规模数据集合多重数据种类带来的挑战，特别是大数据应用难题。==

Redis
MongoDB

关于MySQL

原开发者为瑞典的MySQL AB公司，该公司于2008年被昇阳微系统（Sun Microsystems）收购。2009年，甲骨文公司（Oracle）收购昇阳微系统公司，MySQL成为Oracle旗下产品。
==开源的数据库系统软件==
==最好的RDBMS(Relational DataBase Manager System)关系型数据库管理系统==
体积小，速度快，开发成本低等优点，多用于中小型网站的开发，大型网站用到集群

MySQL安装

官网下载压缩包

https://www.mysql.com/cn/downloads/
解压
把解压包放在电脑环境目录下
配置环境变量

新建MySQL配置文件ini

[mysqld]
# 目录按个人的来
basedir=E:\software\mysql-5.02.11\
datadir=E:\software\mysql-5.02.11\data\
port=3306
skip-grant-tables

启动管理员运行，运行CMD命令
- 先把命令路径切换至mysql下的bin命令然后输入mysqld -install
- 再输入mysqld --initialize-insecure --user=mysql初始化数据文件
- 然后再次启动mysql，用命令mysql -u root -p进入mysql管理界面
- 进入界面更改root密码
- update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
- 然后输入flush privileges刷新权限
修改my.ini文件删除最后一句skip-grant-tables
重启mysql即可使用
- net stop mysql
- net start mysql

数据库使用

使用命令行连接数据库

mysql -uroot -p123456 --连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; --修改用户密码
flush privileges; --刷新权限

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

mysql> use college --切换数据库+use关键字+数据库名
Database changed

show tables; --查看数据库中所有的表
describe student; --显示数据库中所有表信息
create database stuRepo; --创建一个数据库
exit; --退出连接

操作数据库

创库

CREATE DATABASE [IF NOT EXIST] stuRepo;

删库
```
DROP DATABASE [IF EXIST] stuRepo;
```
使用数据库
```
use 'stuRepo';
```
查看数据库
```
show Databases;
```

数据库的数据类型

数值型

类型	适用范围	大小
`tinyint`	极小的整数	1字节
`smallint`	较小的整数	2字节
`mediumint`	中等的整数	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	存储大文本

时间日期型

出自java.util.Date包
|类型|格式|解释|
|:——:|:——:|:——-:|
|date|YYYY-MM-DD|日期格式|
|time|HH:mm:ss|时间格式|
|datatime|YYYY-MM-DD HH:mm:ss|最常见的时间格式|
|timestamp|即时间戳|1970.1.1至今的毫秒数|
|year||年份表示|
NULL
无数值
不可使用NULL进行运算，结果为NULL

数据库字段属性

Unsigned
- 无符号的整数
- 声明了该列不可为负数
zerofill
- 0填充的
- 不足的位数，使用0来填充
自增(AUTO_INCREMENT)
- 通常理解为自增，自动在上一条记录的属性上加1（默认）
- 通常用来设计唯一的主键(如id、index等)，必须为整数类型
- 可以自定义设计主键自增的起始值和步长
非空(NOT_NULL)
- 若字段设置为非空，则不给该字段赋值，系统报错
- NULL，不填写默认为空

默认(DEFAULT)

表的操作

创表

--业务：创建一个school数据库
--然后创建学生表，使用SQL语句创建
--关键点：
--使用英文的(),表名和字段名尽量使用``括起来
--AUTO_INCREMENT自增
--PRIMARY KEY 主键，一个表只有一个主键
CREATE DATABASE IF NOT EXISTS `school`;
use `school`;
CREATE TABLE IF NOT EXISTS `student` {
`id` int(6) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(22) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(30) NOT NULL DEFAULT '123' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
PRIMARY KEY(`id`)
}ENGINE=INNOOB DEFAULT CHARSET=UTF8

标准格式

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

查看库与表的语句

SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据库中表的定义语句
DESC student -- 显示表的结构

数据表类型

数据库引擎

定义：
- ==数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。==
目前常用的（必记的）
- INNODB 默认使用
- MYISAM 早期使用的
两者区别

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

使用特点
- 对于MYISAM而言，可节约空间，高速运行
- 对于INNODB而言，安全性高，可实现多表多用户操作

在物理环境中存在的位置
索引的数据库文件都存储在data文件目录下，一个文件夹对应一个数据库
本质上是文件的存储

两个数据库引擎在物理上的区别
- INNODB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
- MYISAM对应文件
  |文件后缀|解释|
  |:—-:|:——:|
  |*.frm|表结构定义文件|
  |*.MYD|数据文件data|
  |*.MYI|索引文件index|

编码问题

设置编码(创表的SQL语句中)
```
CHARSET=utf8
```
不设置utf-8，==MYSQL默认编码为Latin1，且不支持中文==
也可在my.ini配置文件中设置默认编码（但不推荐，因为非本机操作数据库会失效）
```
character-set-server=utf8
```
增加与删除表字段
修改表名称
举例：
```
ALTER TABLE person RENAME AS man
```

标准格式

ALTER TABLE [表名称]  RENAME AS [新表的名称]

增加表字段

举例：

ALTER TABLE teacher ADD age INT(11)--增加teacher表的年龄字段

标准格式:

ALTER TABLE [表名称] ADD [字段名] [字段属性]

修改表字段(修改约束与重命名)

举例：

ALTER TABLE JOB MODIFY salary int(12) --修改约束

标准格式：

ALTER TABLE [表名称] MODIFY [字段名] [字段属性]

举例：

ALTER TABLE JOB CHANGE salary salary1 int(12) --字段重命名 salary为原字段名

删除表字段

举例：
```
ALTER TABLE JOB DROP salary1
```

标准格式：

ALTER TABLE [表名称] DROP [字段名]

删除表

标准格式：
```
DROP TABLE IF EXISTS JOB
```

数据管理

外键(Foreign key)

-- 为学生表的gradeid添加年级表的 gradeid外键
CREATE DATABASE IF NOT EXISTS `school`;
use `school`;
CREATE TABLE IF NOT EXISTS `student` {
  `id` int(6) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(22) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(30) NOT NULL DEFAULT '123' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `gradeid` int(15) NOT NULL COMMENT '年级',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  PRIMARY KEY(`id`)
}ENGINE=INNOOB DEFAULT CHARSET=UTF8
--添加外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

标准格式：
```
ALTER TABLE 表名  ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 引用的表名(引用的表的字段);
```
- ==不建议使用外键约束，而是在应用层进行代码的约束==
- ==外键约束对后期程序的开发有很大的影响，对update和delete操作都会有更大的麻烦==

DML 数据管理语言

添加insert

insert into `表名`(`字段名1`,`字段名2`,`字段名3`)values('值1','值2','值3')

==左边字段的个数与右边值的个数要相同，并且数据类型要一一对应==

修改update

update `表名` set `列名1` = `值1`,`列名2` = `值2` where 条件

==没有where子句后的条件，会默认修改所有列==
==value既可以是一个值，也可以是一个变量==

删除delete

delete from 表名 where 条件
--where子句可写可不写，按照实际要求决定，where实际上相当于一个删除的约束
--不写where则会删除整个表的数据

清空truncate

只是清空表里的数据，表的结构和字段不会改变

--清空JOB表
truncate `JOB`

delete与truncate的区别

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

DQL数据查询语言

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：
- SELECT <字段名表>
- FROM <表或视图名>
- WHERE <查询条件>
  
  指定查询字段
```sql
— 查询全部学生
select * from student

— 查询指定字段
select id,name from student

— 别名，为某个表或字段起一个别名
select employeeName AS 员工姓名 from employee AS e

— 函数 Concat（a,b）
select CONCAT(‘员工姓名：’,employeeName) as 新列名 from employee
— 输出的格式会为员工姓名：张三


#### 去重distinct
- 筛选出表中重复的数据，只显示一个
```sql
select distinct `employeeNo` from employee

Where条件子句

作用：为了筛选出需要的数据，即添加条件
==通过逻辑运算符配合使用！！！==
|运算符|语法|描述|
|:—-:|:—-:|:——:|
|and &&| a and b  a&&b|逻辑与，两个都为真，结果为真|
|or |||a or b  a || b|逻辑或，其中一个为真，则结果为真|
|not !|not a  ! a|逻辑非，真为假，假为真|

模糊查询

在 MySQL 中，LIKE 关键字主要用于搜索匹配字段中的指定内容。其语法格式如下：
- [NOT] LIKE '字符串'
其中：
- NOT ：可选参数，字段中的内容与指定的字符串不匹配时满足条件。
  字符串：指定用来匹配的字符串。“字符串”可以是一个很完整的字符串，也可以包含通配符。
- LIKE 关键字支持百分号“%”和下划线“_”通配符。
==通配符是一种特殊语句，主要用来模糊查询。当不知道真正字符或者懒得输入完整名称时，可以使用通配符来代替一个或多个真正的字符。==
模糊查询通常会使用到比较运算符
|运算符|语法|描述|
|:——:|:——:|:——:|
|IS NULL|a is null|如果操作符为NULL,则结果为真|
|IS NOT NULL|a is not null|如果操作符不为NULL,则结果为真|
|BETWEEN|a between b and c|若a在b与c之间，则结果为真|
|LIKE|a like b|如果a匹配b，则结果为真|
|IN|a in (a1,a2,a3…)|假设a在a1，或a2…其中的某一个值中，则结果为真|

模糊查询的SQL语句实例

-- 查询姓刘的同学
-- like结婚 %（岱庙0到任意个字符） _（代表一个字符）
SELECT `studentId`,`name` FROM `student`
WHERE `name` LIKE '刘%'

-- 查询姓刘的同学，姓后面为一个字的
SELECT `studentId`,`name` FROM `student`
WHERE `name` LIKE '刘_'

-- 查询姓刘的同学，姓后面为两个字的
SELECT `studentId`,`name` FROM `student`
WHERE `name` LIKE '刘__'

-- 查询名字中间为欣字的同学 %欣%
SELECT `studentId`,`name` FROM `student`
WHERE `name` LIKE '%欣%'

-- 查询1001、1002、1003号学员
SELECT `studentId`,`name` FROM `student`
WHERE `studentId` IN (1001,1002,1003)

-- 查询地址为空的学生 null
SELECT `studentId`,`name` FROM `student`
WHERE `address`='' OR `address` IS NULL

-- 查询有出生日期的同学 not null
SELECT `studentId`,`name` FROM `student`
WHERE `birthday` IS NOT NULL

联表查询（重要！）

联表查询的种类

joinlist

inner join 内连接 ，两个表中都匹配时返回行
left join 左连接 ，即使左侧表中没有匹配项，也返回左侧表中的所有行。
RIGHT JOIN 右连接 ，即使左侧表中没有匹配项，也返回右侧表中的所有行。
FULL JOIN 全连接，它合并了左右外部联接的结果。
==联接的表将包含两个表中的所有记录，并为任一侧缺少的匹配项填充NULL。==
SELF JOIN 自连接，用于将一个表连接到自身，就好像该表是两个表一样，在SQL语句中临时重命名至少一个表。
CARTESIAN JOIN 笛卡尔连接 从两个或多个联接表中返回记录集的笛卡尔积。
联表图解

joindetails

代码示例：

-- inner join
SELECT s.studentId ,studentName,subNo,stuRes FROM student AS s 
INNER JOIN result AS r 
ON s.studentId = r.studentId

--right join
SELECT s.studentId ,studentName,subNo,stuRes FROM student AS s 
RIGHT JOIN result AS r 
ON s.studentId = r.studentId  

--left join
SELECT s.studentId ,studentName,subNo,stuRes FROM student AS s 
LEFT JOIN result AS r 
ON s.studentId = r.studentId

分页和排序

排序：

-- 排序： 升序ASC，降序DESC
-- ORDER BY 通过字段排序
-- 查询的结构根据成绩降序
SELECT s.`studentId`,`name`,`subName`,`subRes` FROM 
student s
INNER JOIN `result` r
ON s.`studentId` = r.`studentId`
INNER JOIN `subject` sub
ON r.`subId` = sub.`subId`
WHERE `subName` = `数据库`
ORDER BY subRes ASC

分页：

-- 分页的目的：让用户观看更加舒服，也减轻数据库的压力，无需一次性输出所有数据
-- 分页的效果：在一个网页中显示部分数据，给数据分页显示
-- 语法：limit 起始值,页面大小
-- 例如：limit 0,3 即从第一个数据到第四个数据
SELECT s.`studentId`,`name`,`subName`,`subRes` FROM 
student s
INNER JOIN `result` r
ON s.`studentId` = r.`studentId`
INNER JOIN `subject` sub
ON r.`subId` = sub.`subId`
WHERE `subName` = `数据库`
ORDER BY subRes ASC
LIMIT 0,8

-- 第一页 limit 0,5 (1-1)*5
-- 第二页 limit 5,5 (2-1)*5
-- 第n 页 limit     (n-1)*pagesize,pagesize
-- pageSize:页面大小
-- (n-1)*pageSize:起始值
-- n：当前值
-- 数据总数/页面大小 =总页数

分组和过滤

ORDER BY()
HAVING()

子查询

子查询是将一个查询语句嵌套在另一个查询语句中；
在特定情况下，一个查询语句的条件需要另一个查询语句来获取，==内层查询（inner query）语句==的查询结果，可以为==外层查询（outer query）语句==提供查询条件。

代码示例

SELECT DISTINCT s.`studentId`,`name`
FROM student s
INNER JOIN result r
ON r.studentId = s.studentId
WHERE `studentRes`>=80 AND `subId` =(
  SELECT subId FROM `subject`
  WHERE `subName` ='高数'
)

MySQL常用函数

数学运算

SELECT ABS(-10) --绝对值
SELECT CEILING(8.4) --向上取整
SELECT FLOOR(8.4) --向下取整
SELECT RAND() --返回一个0~1内的随机数
SELECT SIGN(10) --判断一个数的符号  负数返回-1 正数返回1

字符串函数

SELECT CHAR_LENGTH('adhsakdajsx') --求字符串长度
SELECT CONCAT('We','are','family') --拼接字符串 Wearefamily
SELECT INSERT('这是一个美好的故事',5,6,7,'动人的') --查询，从某个位置开始替换长度 这是一个动人的故事
SELECT LOWER('PETERPAN') --小写字母
SELECT UPPER('peterpan') --大写字母
SELECT INSTR('peterpan','e') --返回第一个出现的子串的索引
SELECT REPLACE('未上岸','未','已') --替换出现指定的字符串 已上岸
SELECT SUBSTR('只要努力就能成功',3,8) --返回指定的子字符串 努力就能成功
SELECT REVERSE('大家早上好') --反转 好上早家大

时间与日期函数

SELECT CURRENT_DATE() --获取当前日期
SELECT CURDATE() --获取当前日期
SELECT NOW() --获取当前时间
SELECT LOCALTIME() --本地时间
SELECT SYSDATE() --系统时间

SELECT YEAR(NOW()) --当前年份
SELECT MONTH(NOW()) --当前月份

系统

SELECT SYSTEM_USER() -- 获取系统管理者
SELECT USER()  --获取用户
SELECT VERSION()  --获取版本

常用的聚合函数

函数	解释
`count()`	计数
`sum()`	求和
`avg()`	求平均值
`max()`	求最大值
`min()`	求最小值

具体参考MYSQL官方文档：https://dev.mysql.com/doc/refman/8.0/en/function-resolution.html

MySQL

认识数据库

数据库管理系统（DBMS）

数据库分类

什么是关系模式

什么是关系模型

关系型数据库

定义

- ==采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。==

非关系型数据库

定义

关于MySQL

MySQL安装

数据库使用

使用命令行连接数据库

操作数据库

数据库的数据类型

数值型

字符串

时间日期型

NULL

数据库字段属性

表的操作

创表

标准格式

查看库与表的语句

数据表类型

数据库引擎

编码问题

增加与删除表字段

修改表名称

增加表字段

修改表字段(修改约束与重命名)

删除表字段

删除表

数据管理

外键(Foreign key)

DML 数据管理语言

添加insert

修改update

删除delete

清空truncate

delete与truncate的区别

DQL数据查询语言

指定查询字段

Where条件子句

模糊查询

联表查询（重要！）

分页和排序

分组和过滤

子查询

MySQL常用函数

数学运算

字符串函数

时间与日期函数

系统

常用的聚合函数

配置与管理DNS服务器

MySQL进阶