2019大部分同学只是理论上知道tomcat几个目录的作用，具体需要在项目实践中慢慢体会
本学期需要掌握的内容：
基础知识的：Mysql数据库、Maven、JDBC基础、DBUtils、JDBC项目。
跑得快的同学：另外JavaWeb、SSM、SpringBoot快速入门
本学期学习目标：
最起码的：能够独立开发一个使用Mysql的Java B/S管理系统，能够对数据进行增删改查操作。
跑得快的同学：不仅能使用B/S，还能使用JavaWeb、SSM、SpringBoot完成B/S项目开发。毕竟JavaEE才是Java的主力。
MySQL版本选择：
主要有两个版本：
* [8.0](https://dev.mysql.com/downloads/mysql/8.0.html)
* [5.7](https://dev.mysql.com/downloads/mysql/5.7.html)
* Mysql8发生了很大改变，以致于代码层次都需要相应变化，不过性能有很大提升。
# MySQL8.0.11 MSI版 安装图解说明
官方下载地址：https://dev.mysql.com/downloads/windows/installer/
第一步：
第二步：
下载完成，点击打开 mysql-installer-community-8.0.11.0
在使用Navicat连接数据库的时候可能会报错误
上网搜索解决方案，网上说出现这种情况的原因是：mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password,
2.然后输入
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; #更新一下用户的密码，password为自定义密码
FLUSH PRIVILEGES; #刷新权限
（温馨提示：ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; #这行命令实际上应该为设置密码永不过期，而不是修改加密规则，具体可参考下方截图）
**3.输入刚刚修改的密码，再次测试连接，惊喜地发现连接成功**
解决问题方法有两种：
方法1.升级navicat驱动；
方法2.把mysql用户登录密码还原成mysql_native_password.
这里采用方法2解决，具体操作步骤如下：
1.打开命令行小黑屏，进入MySQL的bin目录，然后输入mysql -u root -p，输入密码
### Mysql安装教程
Mysql到现在已经有很多种安装方式，选择一种安装即可，Mysql也朝向“默认大于配置”的方向发展，安装越来越便利。
安装前请阅读一遍教程，安装过程中请确保每一步进行成功，不要盲目下一步。如果出现问题，很难排查，建议重装。
#### 1) 安装
mysql安装方式有如下几种：
这里也收集了一些不错的安装教程：
1. MSI版本安装
MySQL8.0.11 MSI版 安装图解说明
2. ZIP版本安装
[mysql-8.0.11-winx64.zip安装教程详解](https://blog.csdn.net/zbajie001/article/details/80407944)[官方指南](https://dev.mysql.com/doc/refman/en/windows-start-service.html)
3. brew安装（mac）
若不考虑版本直接执行命令`brew install mysql`。
**重要：安装后请详细查看输出信息，这里会告诉你怎么启动mysql，以及一些其他说明！！！**
~~~
# 通常这些输出信息会告诉你如何启动mysql。
To have launchd start mysql now and restart at login:
brew services start mysql # 设置为自动启动
Or, if you don't want/need a background service you can just run:
mysql.server start # 手动启动一次
~~~
4. scoop安装（win）
使用[scoop(opens new window)](https://jiayaoo3o.github.io/2019/01/30/Windows%E4%B8%8B%E7%9A%84%E8%BD%AF%E4%BB%B6%E7%AE%A1%E7%90%86%E7%A5%9E%E5%99%A8-scoop/)直接安装mysql:`scoop install mysql`。
**重要：安装后请详细查看输出信息，这里会告诉你怎么启动mysql，以及一些包含初始化密码等其他说明！！！**
启动命令`net start mysql`，也可在服务界面点击鼠标启动。
进入后请修改初始化密码。
5. docker安装
~~~
# 拉取 MySQL 镜像
docker pull mysql:latest
# 运行容器
docker run -itd --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql
# 参数说明：
#-p 3306:3306 ：映射容器服务的 3306 端口到宿主机的 3306 端口，外部主机可以直接通过 宿主机ip:3306 访问到 MySQL 的服务。
#MYSQL_ROOT_PASSWORD=123456：设置 MySQL 服务 root 用户的密码。
~~~
##### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#%E9%85%8D%E7%BD%AE%E6%96%87%E4%BB%B6%E8%AF%B4%E6%98%8E)配置文件说明：
关于my.ini或着my.etc配置文件，大部分教程都是直接丢给你，我们以下面这个一一说明。 如果你使用mysql8，则如果不需要修改什么，直接不用配置文件即可，“约定大于配置”。 如果你使用mysql5.7，建议修改字符集相关设置。
~~~
[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=C:/Users/w/scoop/apps/mysql/current
# 设置mysql数据库的数据的存放目录
datadir=C:/Users/w/scoop/persist/mysql/data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8
~~~
上面的配置文件并不是都需要设置！！！
##### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#%E4%B8%BE%E4%BE%8B)举例：
我们以mysql zip 5.7版本举例说明，
这下面是我使用的配置文件以及安装命令。
~~~
[mysqld]
port = 33066
explicit_defaults_for_timestamp=true
character-set-server=utf8
[client]
default-character-set=utf8
~~~
~~~
# 安装命令
mysqld --initialize --console
# 安装服务命令
mysqld --install mysql57
# 启动服务命令
net start mysql57
# 停止服务命令
net stop mysql57
# 删除服务命令
sc delete mysql57
~~~
Mysql客户端
客户端也有很多种。有收费的，像大名鼎鼎的Navicat、Navicat for MySQL、SQLyog等。也有免费，像HeidiSQL、[MySQL Workbench(opens new window)](https://dev.mysql.com/downloads/workbench/)等。
收费的我也不想出钱，也不想在破解道路上斗智斗勇了。
所以选择免费的可以了。
直接使用[MySQL Workbench(opens new window)](https://dev.mysql.com/downloads/workbench/)，这属于mysql官方出品，免费，功能强大，缺点英文界面。
#### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_2-%E6%B3%A8%E6%84%8F%E4%BA%8B%E9%A1%B9)2) 注意事项
1. windows下需要VC++依赖，如果你没有安装，请下载安装。 Windows下可能提示缺少VC++201X，需要去[微软网站(opens new window)](https://visualstudio.microsoft.com/zh-hans/downloads)进行下载，在网页下面，不要傻乎乎的把VS2019下下来了。
2. 密码没有显示或者没看到。
在安装目录data文件中的.err文件中可以找到初始密码。对于scoop来说是scoop的persist\\MySQL路径下。
#### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_3-%E4%BD%BF%E7%94%A8%E8%AF%B4%E6%98%8E)3) 使用说明
1. 连接的驱动地址为"com.mysql.cj.jdbc.Driver" Mysql和mysql-connector都为8.0以上的版本时， 连接驱动就 不要再用: com.mysql.jdbc.Driver 要引用: com.mysql.cj.jdbc.Driver
2. 建立连接时url的一些参数 jdbc:mysql://localhost:3306/，这个东西后面跟的是数据库的名字，8.0.X以上的版本还需在数据库名后面加上这些东西 ?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_1-2-%E4%B9%B1%E7%A0%81%E4%B9%8B%E8%B0%9C)1.2. 乱码之谜
一般由下面原因： 1）客户机没有正确地设置client字符集，导致原先的SQL语句被转换成connection所指字符集，而这种转换，是会丢失信息的，如果client是utf8格式，那么如果转换成gb2312格式，这其中必定会丢失信息，反之则不会丢失。一定要保证connection的字符集大于client字符集才能保证转换不丢失信息。 2）数据库字体没有设置正确，如果数据库字体设置不正确，那么connection字符集转换成database字符集照样丢失编码，原因跟上面一样。
乱码主要的原因就是编码字符集不兼容，默认情况下，我们只需要统一使用UTF8就可以了。
#### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_1-%E8%83%8C%E6%99%AF%E7%9F%A5%E8%AF%86)1) 背景知识：
1. 字符集和校验规则
字符集是一套符合和编码，校验规则（collation）是在字符集内用于比较字符的一套规则，即字符集的排序规则。MySQL可以使用对种字符集和检验规则来组织字符。
MySQL服务器可以支持多种字符集，在同一台服务器，同一个数据库，甚至同一个表的不同字段都可以指定使用不同的字符集。
每种字符集都可能有多种校对规则，并且都有一个默认的校对规则，并且每个校对规则只是针对某个字符集，和其他的字符集么有关系。
在MySQL中，字符集的概念和编码方案被看做是同义词，一个字符集是一个转换表和一个编码方案的组合。
Unicode（Universal Code）是一种在计算机上使用的字符编码。Unicode 是为了解决传统的字符编码方案的局限而产生的，以满足跨语言、跨平台进行文本转换、处理的要求。
Unicode存在不同的编码方案，包括Utf-8，Utf-16和Utf-32。Utf表示Unicode Transformation Format。
2. 字符集选择规则
(1)编译MySQL 时，指定了一个默认的字符集; (2)安装MySQL 时，可以在配置文件 (my.cnf) 中指定一个默认的的字符集，如果没指定，这个值继承自编译时指定的; (3)启动mysqld 时，可以在命令行参数中指定一个默认的的字符集，如果没指定，这个值继承自配置文件中的配置,此时character\_set\_server被设定为这个默认的字符集; (4)当创建一个新的数据库时，除非明确指定，这个数据库的字符集被缺省设定为character\_set\_server; (5)当选定了一个数据库时，character\_set\_database被设定为这个数据库默认的字符集; (6)在这个数据库里创建一张表时，表默认的字符集被设定为character\_set\_database，也就是这个数据库默认的字符集; (7)当在表内设置一栏时，除非明确指定，否则此栏缺省的字符集就是表默认的字符集;
3. MySQL数据库中字符集转换流程
1、MySQL Server收到请求时将请求数据从character\_set\_client转换为character\_set\_connection； 2、进行内部操作前将请求数据从character\_set\_connection转换为内部操作字符集，其确定方法如下： 使用每个数据字段的CHARACTER SET设定值； 若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展，非SQL标准)； 若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值； 若上述值不存在，则使用character\_set\_server设定值。 3、将操作结果从内部操作字符集转换为character\_set\_results。
#### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_2-%E5%B7%A5%E5%85%B7)2) 工具：
1. 查看当前数据库的字符集命令：`show variables like '%char%';`这个命令会查看数据库元数据的编码信息，如果你看到存在latin1，则需要修改配置。
变量名解释：
character\_set\_client：客户端请求数据的字符集 character\_set\_connection：客户机/服务器连接的字符集 character\_set\_database：默认数据库的字符集这个变量建议由系统自己管理，不要人为定义。 character\_set\_filesystem：把os上文件名转化成此字符集，即把 character\_set\_client转换character\_set\_filesystem， 默认binary是不做任何转换的character\_set\_results：结果集，返回给客户端的字符集 character\_set\_server：数据库服务器的默认字符集 character\_set\_system：系统字符集，这个值总是utf8，不需要设置。这个字符集用于数据库对象（如表和列）的名字，也用于存储在目录表中的函数的名字。
2. 查看当前数据库的校对规则命令：`show variables like 'collation%';`
变量名解释：
collation\_connection 当前连接的字符集。 collation\_database 当前日期的默认校对。每次用USE语句来“跳转”到另一个数据库的时候，这个变量的值就会改变。如果没有当前数据库，这个变量的值就是collation\_server变量的值。 collation\_server 服务器的默认校对。
排序方式的命名规则为：字符集名字\_语言\_后缀，其中各个典型后缀的含义如下： 1）\_ci：不区分大小写的排序方式 2）\_cs：区分大小写的排序方式 3）\_bin：二进制排序方式，大小比较将根据字符编码，不涉及人类语言，因此\_bin的排序方式不包含人类语言
#### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_3-%E6%9F%A5%E7%9C%8B%E6%95%B0%E6%8D%AE%E5%AD%97%E7%AC%A6%E9%9B%86)3) 查看数据字符集：
1. 确定数据database、table、column字符集是不是正确的：
~~~
SELECT character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = your_database_name
AND table_name = your_table_name
AND column_name = your_column_name;
~~~
2. 确定数据库存进去的数据是不是正确的：
~~~
SELECT HEX(column_name)
FROM table_name;
~~~
如果查出来3F，3F是字符'?'的16进制表示，这表明数据库里储存的是？。这种情况最常见的原因是在将特定字符从客户端字符集转换为目标字符集时出现了问题。
3. 确定往返是可能的。
~~~
SELECT 'ペ' AS `ペ`; /* or SELECT _ucs2 0x30da; */
~~~
如果结果也不为ペ，则往返失败。
4. 确保问题出在浏览器或其他应用程序上，而不是MySQL。
使用mysql自带的命令行客户端程序完成上述步骤。如果mysql正确显示字符，但您的应用程序不能正确显示，则可能是由于系统设置引起的。
使用SHOW VARIABLES LIKE 'char%';查看系统设置：
~~~
mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
~~~
character\_set\_client:我们要告诉服务器，我给你发送的数据是什么编码？ character\_set\_connection:告诉字符集转换器，转换成什么编码？ character\_set\_results:查询的结果用什么编码？ 如果以上三者都为字符集N,可简写为set names 'N';
1. 临时设置：SET NAMES 'utf8'
2. 永久设置：
~~~
# 修改数据的配置文件my.cnf
[mysqld]
character-set-server=utf8
[client]
default-character-set=utf8
~~~
* * *
#### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_4-%E4%B9%B1%E7%A0%81%E7%BB%88%E6%9E%81%E8%A7%A3%E5%86%B3%E6%96%B9%E6%A1%88)4) 乱码终极解决方案：
1）首先要明确你的客户端时候何种编码格式，这是最重要的，这里我们将代码开发工具统一使用utf8 2）确保你的数据库使用utf8格式，很简单，所有编码通吃。 3）一定要保证connection字符集大于等于client字符集，不然就会信息丢失，比如： latin1 < gb2312 < gbk < utf8，若设置set character\_set\_client = gb2312，那么至少connection的字符集要大于等于gb2312，否则就会丢失信息 4）以上三步做正确的话，那么所有中文都被正确地转换成utf8格式存储进了数据库，为了适应不同的浏览器，不同的客户端，你可以修改character\_set\_results来以不同的编码显示中文字体，由于utf8是大方向，因此web应用是我还是倾向于使用utf8格式显示中文的。
### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_1-3-%E5%88%9D%E8%AF%86mysql)1.3. 初识Mysql
#### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_1-%E5%8F%82%E8%80%83%E8%B5%84%E6%96%99)1) 参考资料：
[官网(opens new window)](https://dev.mysql.com/doc/refman/8.0/en/tutorial.html)
[w3school(opens new window)](https://www.w3school.com.cn/sql/sql_syntax.asp)
#### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_2-menagerie%E5%8A%A8%E7%89%A9%E5%9B%AD)2) menagerie动物园
1. **连接到服务器和从服务器断开连接**
使用Navicat或者Workbench
* * *
2. **输入查询**
[SQL Function and Operator Reference(opens new window)](https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html)
~~~
SELECT VERSION(), CURRENT_DATE;
-- 该查询说明了有关mysql的几件事：
-- - 查询通常由一个SQL语句和一个分号组成。
-- - mysql以表格形式（行和列）显示查询输出。第一行包含各列的标签。下面的行是查询结果。
-- - mysql显示返回了多少行以及查询执行了多长时间，这使您对服务器性能有了一个大概的了解。这些值不精确。
-- 可以在任何大写字母中输入关键字。 以下查询是等效的：
SELECT VERSION(), CURRENT_DATE;
select version(), current_date;
SeLeCt vErSiOn(), current_DATE;
-- 可以输入表达式
SELECT SIN(PI()/4), (4+1)*5;
SELECT VERSION(); SELECT NOW();
~~~
* * *
3. **创建和使用数据库**
[CREATE DATABASE Statement(opens new window)](https://dev.mysql.com/doc/refman/5.7/en/create-database.html)
* 创建和选择数据库
~~~
-- 创建数据库DATABASE 和SCHEMA都可以
CREATE DATABASE menagerie;
-- 切换数据库（Workbench需要，Navicat手动点击即可）
USE menagerie
~~~
[CREATE TABLE Statement(opens new window)](https://dev.mysql.com/doc/refman/5.7/en/create-table.html)
* 创建表
| 列类型 | 说明 |
| --- | --- |
| DATE | YYYY-MM-DD |
| VARCHAR | 变长字符串 |
| CHAR | 字符串 |
~~~
-- 创建表，默认引擎为InnoDB、字符集为utf8mb4
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
-- 查看
DESCRIBE pet;
~~~
[INSERT Statement(opens new window)](https://dev.mysql.com/doc/refman/5.7/en/insert.html)
* 将数据加载到表中
~~~
-- 使用insert into语句
insert into pet values('Fluffy','Hrold','cat','f','1993-02-04',null);
-- 可以插入多条记录
insert into pet values
('Fluffy','Hrold','cat','f','1993-02-04',null),
('Fluffy','Hrold','cat','f','1993-02-04',null);
-- 可以指定插入的列
insert into pet(name) values ('Fluffy');
~~~
* 练习
将下表中的数据插入创建的pet表中。
| name | owner | species | sex | birth | death |
| --- | --- | --- | --- | --- | --- |
| Fluffy | Harold | cat | f | 1993-02-04 | |
| Claws | Gwen | cat | m | 1994-03-17 | |
| Buffy | Harold | dog | f | 1989-05-13 | |
| Fang | Benny | dog | m | 1990-08-27 | |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | |
| Whistler | Gwen | bird | | 1997-12-09 | |
| Slim | Benny | snake | m | 1996-04-29 | |
* * *
4. **获取有关数据库和表的信息**
[SELECT Statement(opens new window)](https://dev.mysql.com/doc/refman/5.7/en/select.html)
[DELETE Statement(opens new window)](https://dev.mysql.com/doc/refman/5.7/en/delete.html)
[UPDATE Statement(opens new window)](https://dev.mysql.com/doc/refman/5.7/en/update.html)
* **查询所有**
~~~
-- 查询所有
select * from pet;
~~~
* **修改数据**
~~~
-- 至少有两种方法可以解决此问题
-- 1. 删除数据重新导入
DELETE FROM pet;
-- 2. 使用update
UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
~~~
* **选择特定行**
~~~
-- 如果想验证对Bowser生日的更改，请选择这样的Bowser记录：
SELECT * FROM pet WHERE name = 'Bowser';
-- 查询在1998年或之后出生的动物
SELECT * FROM pet WHERE birth >= '1998-1-1';
-- 您可以组合条件，例如查找雌狗：
SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
-- 前面的查询使用AND逻辑运算符。 还有一个OR运算符：
SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
-- AND的优先级高于OR。 如果同时使用这两个运算符，则最好使用括号来明确指示应如何对条件进行分组：
SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');
~~~
* **选择特殊列**
~~~
-- 想知道动物何时出生
SELECT name, birth FROM pet;
-- 找出谁养宠物
SELECT owner FROM pet;
-- 上面查询只是从每个记录中检索所有者列，并且其中一些出现多次。添加关键字DISTINCT去重：
SELECT DISTINCT owner FROM pet;
-- 可以使用WHERE子句来组合行选择和列选择。例如，要仅获取狗和猫的出生日期，请使用以下查询：
SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';
~~~
* **排序行**
~~~
-- 按日期排序
SELECT name, birth FROM pet ORDER BY birth;
~~~
与所有其他比较操作一样，在字符类型列上，排序通常以不区分大小写的方式执行。 这意味着除了大小写相同以外，其他列均未定义顺序。 可以通过使用BINARY强制对列进行区分大小写的排序，如下所示：ORDER BY BINARY col\_name。
~~~
-- 默认的排序顺序是升序，先是最小值。 要以降序排序，需添加DESC关键字到要排序的列的名称上：
SELECT name, birth FROM pet ORDER BY birth DESC;
-- 可以在多列上排序
SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
-- DESC关键字仅适用于紧接其前（birth）的列名； 它不影响species列的排序顺序。
~~~
* **日期计算**
MySQL提供了一些函数，可用于对日期执行计算，例如，计算年龄或提取部分日期。
| 常见函数 | 说明 |
| --- | --- |
| [TIMESTAMPDIFF()(opens new window)](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_timestampdiff) | 从日期时间表达式中减去一个间隔 |
| [DATE\_ADD()(opens new window)](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-add) | 将时间值（INTERVAL ）添加到日期值 |
| [CURDATE()(opens new window)](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_curdate) | 返回当前日期 |
| [MONTH()(opens new window)](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_month) | 返回经过日期的月份 |
~~~
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name;
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY age;
SELECT name, birth, death, TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;
SELECT name, birth, MONTH(birth) FROM pet;
SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
SELECT '2018-10-31' + INTERVAL 1 DAY;
SELECT '2018-10-32' + INTERVAL 1 DAY;
SHOW WARNINGS;
~~~
* **使用NULL值**
要测试NULL，请使用IS NULL和IS NOT NULL运算符。不能使用算术比较运算符（例如=，）测试NULL。
~~~
-- 如何判断NULL。
SELECT 1 IS NULL, 1 IS NOT NULL;
SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
-- 在MySQL中，0或NULL表示false，其他表示true。 布尔运算的默认真值是1。
SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
~~~
* **模式匹配**
~~~
SELECT * FROM pet WHERE name LIKE 'b%';
SELECT * FROM pet WHERE name LIKE '%fy';
SELECT * FROM pet WHERE name LIKE '%w%';
SELECT * FROM pet WHERE name LIKE '_____';
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b')
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs);
SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b');
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');
SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$');
SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$');
~~~
* **计数行**
~~~
SELECT COUNT(*) FROM pet;
SELECT owner, COUNT(*) FROM pet GROUP BY owner;
SELECT species, COUNT(*) FROM pet GROUP BY species;
SELECT sex, COUNT(*) FROM pet GROUP BY sex;
SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;
~~~
* **使用多个表**
如果您想记录有关他们的其他信息，例如他们生活中的事件，例如看兽医或出生时产仔，则需要另一张table。
~~~
-- 创建表
CREATE TABLE event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255));
-- 初始化数据
insert into event values
('Fluffy', '1995-05-15', 'litter', '4 kittens, 3 female, 1 male'),
('Buffy', '1993-06-23', 'litter', '5 puppies, 2 female, 3 male'),
('Buffy', '1994-06-19', 'litter', '3 puppies, 3 female'),
('Chirpy', '1999-03-21', 'vet', 'needed beak straightened'),
('Slim', '1997-08-03', 'vet', 'broken rib '),
('Bowser', '1991-10-12', 'kennel', null),
('Fang', '1991-10-12', 'kennel', null),
('Fang', '1998-08-28', 'birthday', 'Gave him a new chew toy'),
('Claws', '1998-03-17', 'birthday', 'Gave him a new flea collar'),
('Whistler', '1998-12-09', 'birthday', 'First birthday');
-- 测试数据，为了体现左右连接的区别
insert into pet(name,owner,species,birth)
values
('小强','周星星','小强','1998-8-8'),
('小白','小新','dog','1998-8-8');
~~~
| name | date | type | remark |
| --- | --- | --- | --- |
| Fluffy | 1995-05-15 | litter | 4 kittens, 3 female, 1 male |
| Buffy | 1993-06-23 | litter | 5 puppies, 2 female, 3 male |
| Buffy | 1994-06-19 | litter | 3 puppies, 3 female |
| Chirpy | 1999-03-21 | vet | needed beak straightened |
| Slim | 1997-08-03 | vet | broken rib |
| Bowser | 1991-10-12 | kennel | |
| Fang | 1991-10-12 | kennel | |
| Fang | 1998-08-28 | birthday | Gave him a new chew toy |
| Claws | 1998-03-17 | birthday | Gave him a new flea collar |
| Whistler | 1998-12-09 | birthday | First birthday |
* * *
SQL 连接（JOIN） 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。连接的结果可以在逻辑上看作是由SELECT语句指定的列组成的新表。
左连接与右连接的左右指的是以两张表中的哪一张为基准，它们都是外连接。外连接就好像是为非基准表添加了一行全为空值的万能行，用来与基准表中找不到匹配的行进行匹配。假设两个没有空值的表进行左连接，左表是基准表，左表的所有行都出现在结果中，右表则可能因为无法与基准表匹配而出现是空值的字段。
在我们继续讲解实例之前，我们先列出您可以使用的不同的 SQL JOIN 类型：
* INNER JOIN：如果表中有至少一个匹配，则返回行
* LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
* RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
* FULL JOIN：只要其中一个表中存在匹配，则返回行（MySQL不支持FULL JOIN,可以使用union替代，将两个查询结果合起来。）
~~~
-- 内连接
-- INNER JOIN 与 JOIN 是相同的。
select * from pet,event where pet.name = event.name;
select * from pet join event on pet.name = event.name;
select * from pet inner join event on pet.name = event.name;
-- 外连接
-- outer这个关键字 ，可以省略 。LEFT JOIN 等同 LEFT OUTER JOIN ，RIGHT JOIN 等同 RIGHT OUTER JOIN。
select * from pet left join event on pet.name = event.name;
select * from pet left outer join event on pet.name = event.name;
select * from pet right join event on pet.name = event.name;
-- mysql不支持全连接，可使用left join + union+ right join替代，使用较少，不再啰嗦。
select * from A left join B on A.id = B.id （where 条件）
union
select * from A right join B on A.id = B.id （where条件);
-- union 将两个查询结果并起来，列的数量以及类型需要一致
-- 默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
-- (select name from pet) union all (select name from event);
-- 找出每个宠物产仔的年龄
SELECT pet.name,
TIMESTAMPDIFF(YEAR,birth,date) AS age,
remark
FROM pet INNER JOIN event ON pet.name = event.name
WHERE event.type = 'litter';
-- 该查询使用INNER JOIN组合表。 当且仅当两个表均符合ON子句中指定的条件时，INNER JOIN才允许其中一个表中的行出现在结果中。
-- 要在您的宠物中查找繁殖对，您可以将宠物表自身与宠物表结合起来，以产生相似物种的活体雄性和雌性候选对：
SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
FROM pet AS p1 INNER JOIN pet AS p2
ON p1.species = p2.species
AND p1.sex = 'f' AND p1.death IS NULL
AND p2.sex = 'm' AND p2.death IS NULL;
~~~
* * *
5. **在批处理模式下使用mysql**
~~~
shell> mysql < batch-file > mysql.out
mysql> source filename;
mysql> \. filename
~~~
* * *
6. **常见查询示例**
* 列的最大值
* 保留某个列的最大值的行
* 每组的列的最大值
* 保留某个列的组的最大值的行
* 使用用户定义的变量
* 使用外键
* 搜索两个键
* 计算每天的访问量
* 使用AUTO\_INCREMENT
* * *
### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_1-4-mysql%E5%A4%8D%E6%9D%82%E6%9F%A5%E8%AF%A2)1.4. Mysql复杂查询
现在你已经简单了解mysql的使用。不过就像考察一个人会不会编程时，不会考察他能不能写helloworld，尽管会写helloworld也算是会写程序。当笔试/面试时，遇到mysql通常会和复杂查询联系在一起，这些复杂查询会让你把mysql的参考手册翻烂，然后写出一段很长的sql语句，最后告诉你执行结果不正确。
#### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_1-%E7%BB%83%E4%B9%A01-spj%E6%95%B0%E6%8D%AE%E5%BA%93)1) 练习1-spj数据库
我们以pta的一组经典题目开始这种复杂查询的练习。
**题目集**
[18级SQL练习1(opens new window)](https://pintia.cn/problem-sets/1247724379845836800/problems/type/10)
Spj**数据库**
> 工欲善其事，必先利其器
~~~
-- 在本地创建数据库
CREATE TABLE `j` (
`jno` char(3) NOT NULL, -- 工程项目号
`jname` varchar(10) DEFAULT NULL,
`city` varchar(10) DEFAULT NULL,
PRIMARY KEY (`jno`)
);
CREATE TABLE `p` (
`pno` char(3) NOT NULL, -- 零件号
`pname` varchar(10) DEFAULT NULL,
`color` char(2) DEFAULT NULL,
`weight` smallint(6) DEFAULT NULL,
PRIMARY KEY (`pno`)
);
CREATE TABLE `s` (
`sno` char(3) NOT NULL, -- 供应商号
`sname` varchar(10) DEFAULT NULL,
`status` char(2) DEFAULT NULL,
`city` varchar(10) DEFAULT NULL,
PRIMARY KEY (`sno`)
);
CREATE TABLE `spj` (
`sno` char(3) NOT NULL,
`pno` char(3) NOT NULL,
`jno` char(3) NOT NULL,
`qty` smallint(6) DEFAULT NULL,
PRIMARY KEY (`sno`,`pno`,`jno`),
CONSTRAINT `fk_jno` FOREIGN KEY (`jno`) REFERENCES `j` (`jno`),
CONSTRAINT `fk_pno` FOREIGN KEY (`pno`) REFERENCES `p` (`pno`),
CONSTRAINT `fk_sno` FOREIGN KEY (`sno`) REFERENCES `s` (`sno`)
);
INSERT INTO s VALUES
('s1','精益','20','天津'),
('s2','登锡','10','北京'),
('s3','东方红','30','北京'),
('s4','丰泰盛','20','天津'),
('s5','为民','30','上海');
INSERT INTO p VALUES
('p1','螺母','红','12'),
('p2','螺栓','绿','17'),
('p3','螺丝刀','蓝','14'),
('p4','螺丝刀','红','14'),
('p5','凸轮','蓝','40'),
('p6','齿轮','红','30');
INSERT INTO j VALUES
('j1','三建','北京'),
('j2','一-汽','长春'),
('j3','弹簧厂','天津'),
('j4','造船厂','天津'),
('j5','机车厂','唐山'),
('j6','无线电厂','南京'),
('j7','半导体厂','南京'),
('j8','电子元件厂','南京');
INSERT INTO spj VALUES
('s1','p1','j1','200'),
('s1','p1','j2','700'),
('s1','p1','j4','700'),
('s1','p2','j4','100'),
('s1','p2','j5','400'),
('s1','p3','j2','200'),
('s1','p3','j4','100'),
('s2','p3','j5','50'),
('s2','p3','j6','400'),
('s3','p1','j1','100'),
('s3','p3','j1','200'),
('s4','p1','j1','200'),
('s4','p2','j1','100'),
('s4','p3','j1','300'),
('s4','p5','j4','200'),
('s4','p6','j2','100'),
('s5','p2','j2','154'),
('s5','p3','j1','20'),
('s5','p4','j2','50'),
('s5','p6','j3','125'),
('s5','p6','j4','52');
~~~
#### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_2-%E7%BB%83%E4%B9%A02-%E5%AD%A6%E7%94%9F%E6%95%99%E5%B8%88%E7%A7%91%E7%9B%AE%E6%88%90%E7%BB%A9%E8%A1%A8)2) 练习2-学生教师科目成绩表
> 谜语让人着迷，不是吗？
~~~
-- 新建数据表—学生表 student：
create table student(
s_id varchar(10),
s_name varchar(10),
s_age date,
s_sex varchar(10),
PRIMARY KEY (s_id)
);
insert into student values('01' , '赵雷' , '1990-01-01' , '男');
insert into student values('02' , '钱电' , '1990-12-21' , '男');
insert into student values('03' , '孙风' , '1990-12-20' , '男');
insert into student values('04' , '李云' , '1990-12-06' , '男');
insert into student values('05' , '周梅' , '1991-12-01' , '女');
insert into student values('06' , '吴兰' , '1992-01-01' , '女');
insert into student values('07' , '郑竹' , '1989-01-01' , '女');
insert into student values('09' , '张三' , '2017-12-20' , '女');
insert into student values('10' , '李四' , '2017-12-25' , '女');
insert into student values('11' , '李四' , '2012-06-06' , '女');
insert into student values('12' , '赵六' , '2013-06-13' , '女');
insert into student values('13' , '孙七' , '2014-06-01' , '女');
-- 新建数据表—教师表 teacher：
create table teacher(
t_id varchar(10),
t_name varchar(10),
PRIMARY KEY (t_id)
);
insert into teacher values('01' , '张三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');
-- 新建数据表—科目表 course：
create table course(
c_id varchar(10),
c_name varchar(10),
t_id varchar(10),
PRIMARY KEY (c_id),
CONSTRAINT `fk_c_t_id` FOREIGN KEY (`t_id`) REFERENCES `teacher` (`t_id`)
);
insert into course values('01' , '语文' , '02');
insert into course values('02' , '数学' , '01');
insert into course values('03' , '英语' , '03');
-- 新建数据表—成绩表 sc：
create table sc(
sc_id varchar(10),
c_id varchar(10),
score decimal(18,1),
PRIMARY KEY (sc_id),
CONSTRAINT `fk_sc_t_id` FOREIGN KEY (`c_id`) REFERENCES `course` (`c_id`)
);
insert into sc values('01' , '01' , 80);
insert into sc values('01' , '02' , 90);
insert into sc values('01' , '03' , 99);
insert into sc values('02' , '01' , 70);
insert into sc values('02' , '02' , 60);
insert into sc values('02' , '03' , 80);
insert into sc values('03' , '01' , 80);
insert into sc values('03' , '02' , 80);
insert into sc values('03' , '03' , 80);
insert into sc values('04' , '01' , 50);
insert into sc values('04' , '02' , 30);
insert into sc values('04' , '03' , 20);
insert into sc values('05' , '01' , 76);
insert into sc values('05' , '02' , 87);
insert into sc values('06' , '01' , 31);
insert into sc values('06' , '03' , 34);
insert into sc values('07' , '02' , 89);
insert into sc values('07' , '03' , 98);
-- 练习
-- 1、查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
-- 1.1 查询同时存在" 01 "课程和" 02 "课程的情况
-- 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
-- 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
-- 2 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
-- 3 查询在 SC 表存在成绩的学生信息
-- 4 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
-- 4.1 查有成绩的学生信息
-- 5.查询「李」姓老师的数量
-- 6.查询学过「张三」老师授课的同学的信息
-- 7.查询没有学全所有课程的同学的信息
-- 8.查询至少有一门课与学号为" 1 "的同学所学相同的同学的信息
-- 9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
-- 10.查询没学过"张三"老师讲授的任一门课程的学生姓名
-- 11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
-- 12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
-- 13.按平均成绩从高到低显示所有学生/的所有课程的成绩以及平均成绩
-- 14.查询各科成绩最高分、最低分和平均分：
-- 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
-- 15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
-- 数值的排名方法：#用sc中的score和自己进行对比，来计算“比当前分数高的分数有几个”。#
-- 16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
-- 17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
-- 知识点：#group by 之后就不能在关联其他表了，所以left join要写在group by 之前#
-- 18.查询各科成绩前三名的记录
-- 知识点：#依然使用left join比较得出排名#
-- 19.查询每门课程被选修的学生数
-- 20.查询出只选修两门课程的学生学号和姓名
-- 21.查询男生、女生人数
-- 22.查询名字中含有「风」字的学生信息
-- 23.查询同名同性学生名单，并统计同名人数
-- 24.查询 1990 年出生的学生名单
-- 25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
-- 26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
-- 知识点：#感觉left join 后，再加上group by 之后，select只能选择一张表了，不能选在被join的那张表#
-- 27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
-- 28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
-- 29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
-- 30.查询不及格的课程
-- 知识点：#仔细阅读题目，是要去掉重复的，使用distinct；#
-- 31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
-- 32.求每门课程的学生人数
-- 33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
-- 34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
-- 知识点：#这道题加了一个条件，就是如果有重复的情况下；答案给了一种思路：就是算出最高分数，然后查询这个门课成绩=最高分数的#
-- 35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
-- 36.查询每门功成绩最好的前两名
-- 37.统计每门课程的学生选修人数（超过 5 人的课程才统计）
-- 38.检索至少选修两门课程的学生学号
-- 39.查询选修了全部课程的学生信息
-- 40.查询各学生的年龄，只按年份来算
-- 知识点：#主要考察时间函数的使用#
-- 41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
-- 知识点：#curdate():返回当前的日期#
-- 42.查询本周过生日的学生
-- 43.查询下周过生日的学生
-- 44.查询本月过生日的学生
-- 45.查询下月过生日的学生
~~~
- Java Web项目开发学习手册
- 一、B/S开发环境搭建
- 1.1 tomcat服务器目录结构及作用
- 1.2 在IDE开发工具上配置tomcat服务器
- 1.3 简单web项目在tomcat服务器上运行的方法
- 1.4 开发工具设置
- 1.5 总结
- 二、Servlet技术应用
- 2.1 HttpServlet中的主要方法及应用
- 2.1.1 基于Eclipse完成一个JavaWeb项目
- 2.2 HttpRequest，HttpResponse的应用
- 2.2.1客户端请求
- 2.2.2服务器响应
- 2.2.3Servlet HTTP 状态码
- 2.2.4图片验证码类
- 2.2.5注册模拟实现（带验证码）
- 2.3 ServletConfig对象和ServletContext对象的概念
- 2.4 总结
- 三、JSP技术应用
- 3.1 JSP基本语法
- 3.2 JSP标签和指令
- 3.3 JSP中的隐式对象
- 3.4 常用应用操作
- 3.4.1 JSP客户端请求
- 3.4.2 JSP服务器响应
- 3.4.3 HTTP状态码
- 3.4.4 表单处理
- 3.4.5 过滤器
- 3.4.6 Cookie处理
- 3.4.7 Session处理
- 3.4.8 文件上传
- 3.4.9 日期处理
- 3.4.10 页面重定向
- 3.4.11 点击量统计
- 3.4.12 自动刷新
- 3.4.13 发送邮件
- 3.5 JSP高级应用
- 3.5.1 JSP标准标签库（JSTL）
- 3.5.2 JSP连接数据库
- 3.5.3 JSP XML数据处理
- 3.5.4 JSP JavaBean
- 3.5.5 自定义标签
- 3.5.6 表达式语言
- 3.5.7 异常处理
- 3.5.8 调试
- 3.5.9 JSP国际化
- 3.6 实践代码
- 3.6.1 实践代码
- 3.6.2 项目实战
- 3.7 总结
- 四、MVC思想的理解和搭建MVC
- 4.1 MVC设计模式的思想
- 4.2 MVC设计模式的实现步骤
- 4.3 项目实践
- 4.4 总结
- 五、EL表达式和JSTL技术
- 5.1 EL表达式及其应用
- 5.2 常用的JSTL标签的应用
- 5.3 项目实践
- 5.4 总结
- 六、Cookie和Session
- 6.1 cookie对象的概念和应用
- 6.2 session对象的概念和应用
- 6.3 项目实践
- 6.4 总结
- 七、过滤器技术应用
- 7.1 Filter的概念及应用
- 7.2 Filter、FilterChain、FilterConfig 介绍
- 7.3 用户登录过滤案例
- 7.4 项目实战
- 7.5总结
- 八、异步请求技术
- 8.1 JSON数据格式
- 8.2 使用AJAX实现异步请求
- 8.3 用户名校验案例
- 8.4小结
- 综合项目技术实训
- 1.BS项目开发项目实战
- 2.项目需求分析和系统设计
- 2.1需求分析
- 2.2类型模型设计
- 2.3原型设计
- 3.项目数据库分析和系统设计
- 4.BS项目编程实现
- 4.1搭建框架和命名规约
- 4.2实现步骤
- 4.2.1创建实体类
- 4.2.2创建过滤器类
- 4.2.3创建工具类
- 4.2.4创建DAO接口及其实现类
- 4.2.5创建Service接口及其实现类
- 4.2.6创建测试类
- 4.2.7创建控制器类
- 5.企业开发流程规范
- 6.总结
- 九、练习题及答案
- 企业开发常用技术
- 1.Maven技术
- Java命名规范解读
- 参考资料
- 开发中常用的应用服务器和Web服务器