一、初涉MySQL
https://dev.mysql.com/doc/refman/5.7/en/locale-support.html mysql5.7参考手册
1、数据库系统 数据库DB 数据库管理系统DBMS SQL
你可能还没有意识到，其实你自己一直在使用数据库。每当你从自己的电子邮件地址簿里查找名字时，你就在使用数据库。如果你在某个因特网搜索站点上进行搜索，也是在使用数据库。如果你在工作中登录网络，也需要依靠数据库验证自己的名字和密码。即使是在自动取款机上使用ATM卡，也要利用数据库进行PIN码验证和余额检查。
数据库DB是一个以某种有组织的方式存储的数据集合。
数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。
在数据库领域中， 表是一种结构化的文件，可用来存储某种特定类型的数据。
数据库系统（Database System），数据库系统是为适应数据处理的需要而发展起来的一种较为理想的数据处理系统，也是一个为实际可运行的存储、维护和应用系统提供数据的软件系统，是存储介质 、处理对象和管理系统的集合体，数据库及其管理软件是数据库系统的组成部分。
SQL（structured query language发音为字母S-O-L或sequel）是结构化查询语言（Structured Query Language）的缩写。SOL是一种专门用来与数据库通信的语言。
SQL语句是由简单的英语单词构成的。这些单词称为关键字，每个SQL语句都是由一个或多个关键字构成的。
多条SQL语句必须以分号；分隔，SQL语句不区分大小写，一般关键字大写，表和列小写
2、MySQL概述
现在大部分互联网公司使用的数据库是MySQL，很少有使用微软的mssql或甲骨文的oracal数据库的，这是为什么呢？
1）mysql是开源免费的。
这应该是最主要的原因吧，使用mysql数据库是不需要花钱的，所以，大多数公司为了节省公司的成本，就会首选mysql数据库。不管是微软的mssql数据库，还是甲骨文的oracal数据库都是需要收费。当然，在安全级别上，oracal数据库是最高的，是一些数据公司的首选（如：电商平台对安全要求最高），但是，对于一般的公司来说，mysql数据库的安全级别就够了，没必要花大价钱购买oracal。
2）很多网站程序都是基于mysql数据库的。
3）MySQL由于开源可以带来两大优势：
a.可以更加了解软件运作的原理，更好的设置MySQL。一旦出了故障也可以准确定位。
b.可以更容易开发周边产品。
Oracle现在是越来越封闭，在不使用调试技术的情况下，研究Oracle日渐艰难。许多性能资料、等待事件的本意无从知晓，问题的定位比较困难。开源的MySQL不存在这样的问题。
3、 MySQL的安装
详见安装和配置文档
4、启动与停止MySQL服务
- 启动与停止:
计算机管理–服务–mysql57
c:>net stop mysql57
MySQL57 服务正在停止…
MySQL57 服务已成功停止。
c:>net start mysql57
MySQL57 服务正在启动 .
MySQL57 服务已经启动成功。
5、登录与退出
c:>mysql -uroot -p123456
mysql> exit
6、连接数据库
使用Navicat连接 、在IDEA中连接MySQL
二、操作数据库
在你最初连接到MySQL时，没有任何数据库打开供你使用。在你能执行任意数据库操作前，需要选择一个数据库。为此，可使用USE关键字。
show databases;
use 数据库名;
show tables;
show columns from 表名;
show create databae library;
use library;
show create table borrow;
show grants;
show errors; 显示服务器错误或警告信息。
show warnings;
help show;
create database 数据库名;
drop database 数据库名；
查看当前使用的是哪个数据库：
select database();
动手操作：
创建一个 mydb1数据库。
create database mydb1 ；
显示库的创建信息
show create database mydb1;
删除前面创建的mydb1数据库
drop database mydb1;
备份库
1、准备库的数据
use mydb1;
create table test
(
id int
);
insert into test(id) values(1);
select * from test;
2、备份库(导出库)
2.1 退出mysql客户端:exit
2.2 在windows命令行窗口中下执行：mysqldump -uroot -p123456 -d 要导出的数据名 > d:\test.sql
有参数 -d 代表只导出数据库和表和创建语句
没有 -d 会导出数据库和表的创建语句以及表中的数据
3、删除库：drop database mydb1;
4、恢复库（导入库）(1)： 登录到数据库中后操作
4.1 创建库：
mysql>create database mydb1;
4.2 使用库
mysql> use mydb1;
Database changed
4.3 通过执行脚本文件实现导入
mysql> source d:\test.sql
5、恢复库(2)：mysql -uroot -p123456 < c:\test.sql (window命令)
三、操作表
关系型数据库—所有的数据都以二维表的方式进行存储
数据类型
表由列（column）组成，列中存储着表中某部分的信息。
数据库中每个列都有相应的数据类型。数据类型定义列可以存储的数据种类。例如，如果列中存储的为数字（或许是订单中的物品数），则相应的数据类型应该为数值类型。如果列中存储的是日期、文本、注释、金额等，则应该用恰当的数据类型规定出来。
每个表列都有相应的数据类型，它限制或容许该列中存储的数据
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
1、数值类型
MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。
作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
2、日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性，将在后面描述。
| 类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
3、字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。
4、创建数据表
创建一个员工表
use mydb1; 进入库
create table employee
(
id int,
name varchar(20),
gender varchar(4),
birthday date,
entry_date date,
job varchar(4),
salary double,
resume text
);
说明：entry_date date, --入职日期 resume text --摘要
查看库中所有表
show tables;
查看表的创建细节
show create table employee;
查看表的结构
desc employee;
在上面员工表的基本上增加一个image列。
alter table employee add image blob;
修改job列，使其长度为60。
alter table employee modify job varchar(60);
show create table employee;
删除sex列。
alter table employee drop gender;
表名改为user。
rename table employee to user;
列名name修改为username
alter table user change column name username varchar(20);
使用insert语句向表中插入一个员工的信息。
insert into user(id,username,birthday,entry_date,job,salary,resume) values(1,‘aaa’,‘1980-09-09’,‘2020-09-09’,‘bbb’,1000,‘bbbbbbbb’);
查看插入的数据
select * from user;
使用insert语句向表中插入一个员工的信息(含中文)。
insert into user(id,username,birthday,entry_date,job,salary,resume) values(2,‘小李子’,‘1980-09-09’,‘2020-09-09’,‘讲师’,20000,‘好人一个’);
会报错：ERROR 1366 (HY000): Incorrect string value: ‘\xD0\xA1\xC0\xEE\xD7\xD3’ for column ‘username’ at row 1
插入失败后的信息查看，用了什么字符集：
show variables like ‘chara%’; 可看可用的字符集
show create table user; 可看user表用了什么字符集
如何处理变成可以插入中文：
创建一个使用utf-8字符集的mydb2数据库。
create database mydb2 character set utf8;
显示库的创建信息
show create database mydb2;
创建一个使用utf-8字符集，并带校对规则的mydb3数据库。
create database mydb3 character set utf8 collate utf8_general_ci;
显示库的创建信息
show create database mydb3;
在mydb3中创建表stu
use mydb3;
create table stu(sid int,sname varchar(20));
查看表的字符集:show create table stu;
插入有中文的值，成功：
insert into stu values(1,‘老师’);
查看服务器中的数据库，并把其中某一个库的字符集修改为gb2312;
alter database mydb1 character set gb2312;
show create database mydb1;
5、基本的CURD操作：
新原先的表改名回来employee:
rename table user to employee ;
（1）插入数据：
MySQL 表中使用 INSERT INTO SQL语句来插入数据。
/*
添加数据格式,不考虑主键
格式:
insert into 表名 (列名) values (值)
*/
/*
添加数据格式,所有值全给出
格式:
insert into 表名 values (全列值)
*/
/*
添加数据格式,批量写入
格式:
insert into 表名 (列名1,列名2,列名3) values (值1,值2,值3),(值1,值2,值3)
*/
insert into book2(name,publishdate) values(‘aaaa’,‘2021-8-5’);
验证数据类型TINYINT，插入超出类型的范围值会报错：
mysql> create table book3( id TINYINT,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into book3 values(12,‘aa’);
Query OK, 1 row affected (0.01 sec)
mysql> insert into book3 values(200,‘aa’);
ERROR 1264 (22003): Out of range value for column ‘id’ at row 1
mysql>
插入日期格式数据：str_to_date(‘05-06-2018’, ‘%d-%m-%Y’)
（2）更新数据
update 表名 set 列名=值,列名=值 where 条件
将所有员工薪水修改为5000元。
update employee set salary=5000;
将姓名为’aaa’的员工薪水修改为3000元。
update employee set salary=3000 where username=‘aaa’;
将姓名为’aaa’的员工薪水修改为4000元,job改为ccc
update employee set salary=4000,job=‘ccc’ where username=‘aaa’;
将aaa的薪水在原有基础上增加1000元。
update employee set salary=salary+1000 where username=‘aaa’;
（3）删除数据
- 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
- 你可以在 WHERE 子句中指定任何条件
- 您可以在单个表中一次性删除记录。
删除表中名称为’小李子’的记录。
delete from employee where username=‘小李子’;
删除表中所有记录。
delete from employee;
四、Select语句
备注：使用library数据库中的book来操作。
大概，最经常使用的SQL语句就是SELECT语句了。它的用途是从一个或多个表中检索信息。
为了使用SELECT检索表数据，必须至少给出两条信息一—想选择什么，以及从什么地方选择。
检索单个列：
检索多个列：
检索所有列：
去掉重复值（检索不同的行）：select distinct pages from book;
限制结果：limit 5，返回不多于5行 limit 2,4 .带一个值的LIMIT总是从第一行开始，给出的数为返回的行数。 带两个值的LIMIT可以指定从行号为第一个值的位置开始。
使用限定表名和列名：select book.pages from library.book;
五、排序数据
ORDER BY :
单列升或降： order by
多个列升或降：
指定排序方向：ASC 默认升序排 DESC降序排
使用order by与limit配合，找出最贵的书：
六、过滤数据
where子句：
where操作符： = <> != < <= > >= between
查询单个值：
查询价格> < = <> !=
范围值查询：between
查询空值：NULL 无值no val，与字段包含0，空字符串或空格不同。
查询非空值 NOT NULL
.SELECT语句有一个特殊的WERE子句，可用来检查具有NULL值的列。这个WERE子句就是IS NULL子句。
and操作符：满足所有给定的条件
or操作符：只满足其中一个条件
and与or联合使用： select pages,price from book where pages=300 or pages=100 and price>=60;
为啥？如何改进？
in操作符。圆括号在WHERE子句中还有另外一种用法。IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单，全都括在圆括号中。下面的例子说明了这个操作符：
in与or :
not in :找不匹配的行
like操作符：为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySOL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。
最常使用的通配符是百分号（%）。在搜索串中，%表示任何字符出现任意次数。
有区分大小写。
重要的是要注意到，除了一个或多个字符外，%还能匹配0个字符。%
代表搜索模式中给定位置的0个、1个或多个字符。%不能匹配NULL
_ 下划线匹配单个字符。
MySQL的通配符很有用。但这种功能是有代价的：难能符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧。
不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
在确实需要使用通配符时，除非绝对有必要，否则不要把它们用全搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
七、字符串拼接+单行数据处理（字符串+时间处理)：
CONCAT（）函数用于将多个字符串连接成一个字符串
select concat(‘my’,‘s’,‘oo’);
mysql CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
mysql CONCAT_WS(separator,str1,str2,…)
CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。 第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。
mysql> select concat_ws(‘[’,author,publish) from book;
mysql> select concat_ws(‘NULL’,author,publish) from book;
使用别名：
select concat(‘my’,‘s’,‘oo’) as haha;
select concat_ws(‘NULL’,author,publish) as 名字 from book;
单行数据处理函数：
常用字符串处理函数：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kz0Xzi7C-1629731294360)(C:\Users\pkr\AppData\Roaming\Typora\typora-user-images\image-20210805071918096.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6Cl714Sh-1629731294362)(C:\Users\pkr\AppData\Roaming\Typora\typora-user-images\image-20210805072454673.png)]
常用日期转换函数：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-v5iZeR8G-1629731294367)(C:\Users\pkr\AppData\Roaming\Typora\typora-user-images\image-20210805072017609.png)]
STR_TO_DATE(str,format)函数是将时间格式的字符串（str），按照所提供的显示格式（format）转换为DATETIME类型的值。
DATE_FORMAT(date,format)函数则是把数据库的日期转换为对应的字符串格式
date_format(date,‘%Y-%m-%d’) -------------->oracle中的to_char();
str_to_date(str,‘%Y-%m-%d’) -------------->oracle中的to_date();
%Y：代表4位的年份
%y：代表2为的年份
%m：代表月, 格式为(01……12)
%c：代表月, 格式为(1……12)
%d：代表月份中的天数,格式为(00……31)
%e：代表月份中的天数, 格式为(0……31)
%H：代表小时,格式为(00……23)
%k：代表 小时,格式为(0……23)
%h： 代表小时,格式为(01……12)
%I： 代表小时,格式为(01……12)
%l ：代表小时,格式为(1……12)
%i： 代表分钟, 格式为(00……59)
%r：代表 时间,格式为12 小时(hh:mm:ss [AP]M)
%T：代表 时间,格式为24 小时(hh:mm:ss)
%S：代表 秒,格式为(00……59)
%s：代表 秒,格式为(00……59)
SELECT DATE_FORMAT(20200111191640,‘%Y-%m-%d %H:%i:%s’);
SELECT STR_TO_DATE(‘2019-01-06 10:20:30’,‘%Y-%m-%d %H:%i:%s’) AS result;
FOMRAT(N,D,locale);
FORMAT函数将数字N格式化为格式，如"#,###,###.##"，舍入到D位小数。它返回一个值作为字符串。
FORMAT函数接受三个参数：
-
N是要格式化的数字。 -
D是要舍入的小数位数。 -
locale是一个可选参数，用于确定千个分隔符和分隔符之间的分组。如果省略locale操作符，MySQL将默认使用en_US。SELECT FORMAT(14500.2018, 2);
以下语句使用
FORMAT函数，第二个参数为0，因此，结果没有任何小数位:SELECT FORMAT(12500.2015, 0);
以下语句使用
de_DE语言环境而不是en_US语言环境(de_DE语言环境使用点(.)来分隔千位和逗号(，)来分隔小数点。)：SELECT FORMAT(12500.2015, 2,‘de_DE’);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Db4H7z6F-1629731294369)(C:\Users\pkr\AppData\Roaming\Typora\typora-user-images\image-20210805073322616.png)]
select * from book;
select * from bookcase;
case…then…else…end用法
select name,bookcaseid,(case bookcaseid when 3 then ‘国学’ when 1 then ‘社会’ else ‘bb’ end) as 类型 from book;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ThqzkgU5-1629731294369)(C:\Users\pkr\AppData\Roaming\Typora\typora-user-images\image-20210805073120542.png)]
八、分组函数（多行处理函数）
输入多行，最多输出一行
聚合函数有5个：
count 求和,对表中的数据的个数求和 count(列名)
sum求和,对一列中数据进行求和计算 sum(列名)
max 函数,对某列数据,获取最大值
min函数,对某列数据,获取最小值
avg 函数,计算一个列所有数据的平均数
分组函数自动忽略NULL，
但count(字段名) 不统计NULL值，count（*）统计NULL值
group by语句对查询结果分组
分组查询: group by 被分组的列名
必须跟随聚合函数
select 查询的时候,被分组的列,要出现在select 选择列的后面•
统计每一类书的数量：
select bookcaseid,sum(bookcaseid) from book group by bookcaseid;
having语句设置分组条件
结果集是分组查询后,再次进行筛选,不能使用where, 分组后再次过滤,关键字 having•
order by语句对查询结果排序
limit语句限制查询数量
统计每一类书的数量，但不含类别为3的：
select bookcaseid,sum(bookcaseid)
from book
where bookcaseid!=3
group by bookcaseid;
统计每一类书的数量，且数量大于10的：
select bookcaseid,sum(bookcaseid)
from book
group by bookcaseid
having sum(bookcaseid) >10;
统计每一类书的数量，且数量大于5，结果按升序排：
select bookcaseid,sum(bookcaseid)
from book
group by bookcaseid
having sum(bookcaseid) >5
order by sum(bookcaseid) ;
九、约束
1、主键约束 不可以为空，不可以重复
主键约束是一个列或者列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可以强制表的实体完整性。主键约束即在表中定义一个主键来唯一确定表中每一行数据的标识符。主键可以是表中的某一列或者多列的组合，其中由多列组合的主键称为复合主键。主键应该遵守下面的规则：
每个表只能定义一个主键。
主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在两行数据有相同的主键值。这是唯一性原则。
一个列名只能在复合主键列表中出现一次。
复合主键不能包含不必要的多余列。当把复合主键的某一列删除后，如果剩下的列构成的主键仍然满足唯一性原则，那么这个复合主键是不正确的。这是最小化原则。
- 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
- AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
- PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
alter table book3 add primary key(id);
alter table book3 drop primary key;
2、唯一约束
（Unique Key）要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。
create table book4(id int unique,name varchar(40));
alter table book4 add constraint book4_id_unique unique(id);
alter table book4 drop index book4_id_unique;
主键和唯一约束很像，但它们的区别如下：一个表可以有多个字段声明为 UNIQUE，但只能有一个 PRIMARY KEY 声明；声明为 PRIMAY KEY 的列不允许有空值，但是声明为 UNIQUE 的字段允许空值的存在。
3、默认约束
默认值约束用来指定某列的默认值。
<字段名> <数据类型> DEFAULT <默认值>;
sex CHAR (2) DEFAULT ‘女’,
新插入的记录如果没有指定时，则默认都为 女。
修改表中的默认值：
ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;
删除默认值约束：把默认值设为空就好了
修改表时删除默认值约束的语法规则如下：
ALTER TABLE <数据表名>modify <字段名> <数据类型> DEFAULT NULL;
4、外键约束
外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。
外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。
在 CREATE TABLE 语句中，通过 FOREIGN KEY 关键字来指定外键，具体的语法格式如下：
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]
建表时在最后写
CONSTRAINT fk_emp_dept1
FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
建表后添加外键：
ALTER TABLE <数据表名> ADD CONSTRAINT <外键名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);
ALTER TABLE tb_emp2
-> ADD CONSTRAINT fk_tb_dept1
-> FOREIGN KEY(deptId)
-> REFERENCES tb_dept1(id);
删除外键约束：
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
十、多表查询
在关系型数据库中，表与表之间是有联系的，所以在实际应用中，经常使用多表查询。多表查询就是同时查询两个或两个以上的表
在 MySQL 中，多表查询主要有 交叉连接，内连接，外连接，子查询
1、交叉查询
交叉连接 ( CROSS JOIN ) 一般用来返回连接表的笛卡尔积
语法格式如下：
多表交叉连接时，使用 cross join 或者 , 皆可，前者是官方建议的标准写法
select <字段名> from <tab_name> cross join <tab_name> [where子句]
select <字段名> from <tab_name>,<tab_name> [where子句]
当连接的表之间没有关系时，我们会省略掉 where子句，这时返回结果就是两个表的笛卡尔积，返回结果数量就是两个表的数据行相乘，如果每个表有 1000 行，那么返回数量就有 1000*1000=1000000 行，数据量是非常巨大的！
select * from book cross join bookcase; //返回140行
笛卡尔积
如果在交叉连接时使用 where子句，MySQL会先生成两个表的笛卡尔积，然后再选择满足 where条件的记录。因此，表的数量较多时，交叉连接会非常非常慢，一般情况下不建议使用交叉连接
select * from book cross join bookcase where book.bookcaseid = bookcase.id; 返回14行.
2、内连接
内连接 ( inner join ) 使用 inner join 关键字连接两张表，并使用 on 子句来设置连接条件 .
select * from book inner join bookcase on book.bookcaseid = bookcase.id; 相当于是等值连接。
3.外连接
注意：当对多个表进行查询时，要在 select 语句后面指定字段是来源自哪一张表
语法为 表名.列名 ，如果表名较长，可以给表设置别名，这样就可以直接在 select 后写 表的别名.列名
左外连接
内连接查询的结果都是符合连接条件的结果，外连接会先将连接的表分为基表和参考表，再以基表为依据返回满足与不满足条件的记录
外连接区分为 左外连接 和 右外连接
左外连接
左外连接又称为左连接，使用 left outer join 关键字连接两个表，也可以简写为 left join，并使用 on 设置连接条件
语法格式如下：
select <字段名> from left join [on子句]
上述语法中，tab1 为基表，tab2 为参考表，左连接查询时，可以查询出 tab1 中的所有记录和 tab2 中匹配连接条件的记录。如果 tab1 的某行在 tab2 中没有匹配行，那么在返回结果中，tab2 的字段值均为空值 NULL
右外连接：
select <字段名> from right join [on子句]
与左连接相反，右连接以 tab2 为基表，tab1 为参考表，可以查询出 tab2 中所有记录和 tab1 中匹配连接条件的记录。如果 tab2 的某行在 tab1 中没有匹配项，那么在返回结果中，tab1 的字段值均为 NULL
测试，先在bookcase表中插入一个新类别，再在book表中插入一条数据未指明类别：
mysql> insert into bookcase(name) values(‘无类别’);
mysql> insert into book(name) values(‘物联网java’);
这样的话，bookcase表中有一个类别在book表中没有，book表中有一条记录类别为NULL;
左外连接:
select b.name,b.bookcaseid,bs.name
from book b left join bookcase bs
on b.bookcaseid = bs.id;
右外连接：
select b.name,b.bookcaseid,bs.name
from book b right join bookcase bs
on b.bookcaseid = bs.id;
4.子查询
子查询是 MySQL 中比较常用的查询方法，通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 select,update 和 delete 语句中使用，而且可以进行多层嵌套，实际开发中，子查询经常出现在 where 语句中
语法格式如下：
select <字段> from <tab_name>
where <字段><操作符>(子查询)
);
其中，操作符可以是 in，not in，exists，not exists 等关键字
1、先模拟emp表和dept表，做初始数据：
create table DEPT
(
deptno int not null,
dname VARCHAR(14),
loc VARCHAR(13)
);
alter table DEPT
add constraint PK_DEPT primary key (DEPTNO);
create table EMP
(
empno int,
ename VARCHAR(10),
job VARCHAR(9),
mgr int,
hiredate date,
sal double(7,2),
comm double(7,2),
deptno int
);
insert into DEPT (deptno, dname, loc)
values (10, ‘ACCOUNTING’, ‘NEW YORK’);
insert into DEPT (deptno, dname, loc)
values (20, ‘RESEARCH’, ‘DALLAS’);
insert into DEPT (deptno, dname, loc)
values (30, ‘SALES’, ‘CHICAGO’);
insert into DEPT (deptno, dname, loc)
values (40, ‘OPERATIONS’, ‘BOSTON’);
commit;
str_to_date(str,‘%Y-%m-%d’)
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (1, ‘dog’, ‘code’, 7369, str_to_date(‘05-06-2018’, ‘%d-%m-%Y’), 5000, 5000, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, ‘SMITH’, ‘CLERK’, 7902, str_to_date(‘17-12-1980’, ‘%d-%m-%Y’), 800, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, ‘ALLEN’, ‘SALESMAN’, 7698, str_to_date(‘20-02-1981’, ‘%d-%m-%Y’), 1600, 300, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, ‘WARD’, ‘SALESMAN’, 7698, str_to_date(‘22-02-1981’, ‘%d-%m-%Y’), 1250, 500, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, ‘JONES’, ‘MANAGER’, 7839, str_to_date(‘02-04-1981’, ‘%d-%m-%Y’), 2975, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, ‘MARTIN’, ‘SALESMAN’, 7698, str_to_date(‘28-09-1981’, ‘%d-%m-%Y’), 1250, 1400, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, ‘BLAKE’, ‘MANAGER’, 7839, str_to_date(‘01-05-1981’, ‘%d-%m-%Y’), 2850, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, ‘SCOTT’, ‘ANALYST’, 7566, str_to_date(‘19-04-1987’, ‘%d-%m-%Y’), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, ‘KING’, ‘PRESIDENT’, null, str_to_date(‘17-11-1981’, ‘%d-%m-%Y’), 5000, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, ‘TURNER’, ‘SALESMAN’, 7698, str_to_date(‘08-09-1981’, ‘%d-%m-%Y’), 1500, 0, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, ‘ADAMS’, ‘CLERK’, 7788, str_to_date(‘23-05-1987’, ‘%d-%m-%Y’), 1100, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, ‘JAMES’, ‘CLERK’, 7698, str_to_date(‘03-12-1981’, ‘%d-%m-%Y’), 950, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, ‘FORD’, ‘ANALYST’, 7566, str_to_date(‘03-12-1981’, ‘%d-%m-%Y’), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, ‘MILLER’, ‘CLERK’, 7782, str_to_date(‘23-01-1982’, ‘%d-%m-%Y’), 1300, null, 10);
commit;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PTTnBqvB-1629731294370)(C:\Users\pkr\AppData\Roaming\Typora\typora-user-images\image-20210807214321843.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Thuui5nv-1629731294370)(C:\Users\pkr\AppData\Roaming\Typora\typora-user-images\image-20210807214345628.png)]
操作符是in，not in，exists，not exists 等关键字
查询与7369员工工作相同的人的信息：
查询有部门的员工：
select deptno from dept where deptno in(select distinct deptno from emp);
查询没有部门的员工：
插入一条部门为空的记录会如何？
insert into emp(ename) values(‘bbbb’);
distinct
查询出每个部门的编号，名称，位置，部门人数，平均工资：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ki5rnnYl-1629731294371)(C:\Users\pkr\AppData\Roaming\Typora\typora-user-images\image-20210807223020962.png)]
select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal) from emp e right join dept d on e.deptno=d.deptno group by d.deptno,d.dname,d.loc;
用子查询改写：
select d.deptno,d.dname,d.loc,temp.count,temp.avg from dept d left join(select deptno dtno,count(empno) count,avg(sal) avg from emp group by deptno) temp on d.deptno = temp.dtno;
十一、MySQL创建用户的三种方法：
前言：mysql创建用户的方法分成三种：INSERT USER表的方法、CREATE USER的方法、GRANT的方法。
一、账号名称的构成方式
账号的组成方式：用户名+主机（所以可以出现重复的用户名，跟其他的数据库不一样）
用户名：16字符以内.
主机名：可以用主机名和IP地址，也可以用通配符
通配符说明：172.18.10.%（IP地址为172.18.10段的所有IP地址都可以访问）
二、通过CREATE USER命令进行创建用户
脚本：CREATE USER ‘username@host’ [IDENTIFIED BY ‘PASSWORD’] 其中密码是可选项；
例子：CREATE USER ‘john@192.168.189.71’ IDENTIFIED BY “123”;
CREATE USER ‘john@192.168.189.%’ IDENTIFIED BY “123”;
CREATE USER ‘john@’ ;
说明：该方法创建出来的用户只有连接数据库的权限，需要后续继续授权；
注意：用户与@后主机地址是一体的，用一个分号连接，否则会报错，ERROR 1396 (HY000): Operation CREATE USER failed for ‘remote’@‘%’
三、通过GRANT命令创建用户
当数据库存在用户的时候GRANT会对用户进行授权，但当数据库不存在该用户的时候，就会创建相应的用户并进行授权。（说明上面那步是多余的）
脚本：
GRANT <ALL|priv1,priv2,…privn> ON
[object] [IDENTIFIED BY ‘password’]
[WITH GRANT OPTION];
MAX_QUERIES_PER_HOUR count
MAX_UPDATES_PER_HOUR count
MAX_CONNECTIONS_PER_HOUR count
MAX_USER_CONNECTIONS count
说明：priv代表权限select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限
例子：mysql>grant select,insert,update,delete,create,drop on test.hr to john@192.168.10.1 identified by ‘123’;
说明：给主机为192.168.10.1的用户john分配可对数据库test的hr表进行select,insert,update,delete,create,drop等操作的权限，并设定口令为123。
mysql>grant all privileges on test.* to joe@192.168.10.1 identified by ‘123’;
说明：给主机为192.168.10.1的用户john分配可对数据库test所有表进行所有操作的权限，并设定口令为123。
mysql>grant all privileges on . to john@192.168.10.1 identified by ‘123’;
说明：给主机为192.168.10.1的用户john分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123。
mysql>grant all privileges on . to john@localhost identified by ‘123’;
说明：用户john分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123。
查看权限：
show grants for你的用户;
show grants forroot@‘localhost’;
show grants for webgametest@10.3.18.158;
show createdatabase dbname; 这个可以看到创建数据库时用到的一些参数。
showcreatetabletickets; 可以看到创建表时用到的一些参数
撤销权限：
revoke all on . from dba@localhost;
四、直接向mysql.user表插入记录（该方法个人很少用）
因为数据库的用户信息都是保存在mysql.user这张表的，所以直接对该表进行插入语句，即可完成用户的创建；
mysql> insert into user (host,user,password) values (‘%’,‘john’,password(‘123’));
五、完成用户的创建后，请记得刷新系统权限表；
mysql>flush privileges;
总结：虽然创建用户的方法有三种，个人还是倾向于第二种方法，一步到位，简单明了；
其他的两种方法只是有助于理解数据库的原理而已。
十二、JDBC
十三、综合实战
查列：
子查询与连接
表准备：查询时有乱码，可以暂时用
表示客户端以gbk编码显示数据，不会影响原有表中的数据的编码
ANY,SOME只要符合其中一个，ALL要符合所有
update tdb_goods inner join tdb_goods_cates on tdb_goods.goods_cate=tdb_goods_cates.cate_name set tdb_goods.goods_cate=tdb_goods_cates.cate_id;
d by ‘123’;
说明：给主机为192.168.10.1的用户john分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123。
mysql>grant all privileges on . to john@localhost identified by ‘123’;
说明：用户john分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123。
查看权限：
show grants for你的用户;
show grants forroot@‘localhost’;
show grants for webgametest@10.3.18.158;
show createdatabase dbname; 这个可以看到创建数据库时用到的一些参数。
showcreatetabletickets; 可以看到创建表时用到的一些参数
撤销权限：
revoke all on . from dba@localhost;
四、直接向mysql.user表插入记录（该方法个人很少用）
因为数据库的用户信息都是保存在mysql.user这张表的，所以直接对该表进行插入语句，即可完成用户的创建；
mysql> insert into user (host,user,password) values (‘%’,‘john’,password(‘123’));
五、完成用户的创建后，请记得刷新系统权限表；
mysql>flush privileges;
总结：虽然创建用户的方法有三种，个人还是倾向于第二种方法，一步到位，简单明了；
其他的两种方法只是有助于理解数据库的原理而已。
十二、JDBC
十三、综合实战
查列：
子查询与连接
表准备：查询时有乱码，可以暂时用
表示客户端以gbk编码显示数据，不会影响原有表中的数据的编码
ANY,SOME只要符合其中一个，ALL要符合所有
update tdb_goods inner join tdb_goods_cates on tdb_goods.goods_cate=tdb_goods_cates.cate_name set tdb_goods.goods_cate=tdb_goods_cates.cate_id;
803
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
