mysql与jdbc

MySQL快速入门

SQL语句

SQL语句概述

1.SQL 是用于访问和处理数据库的标准的计算机语言。

2.SQL指结构化查询语言，全称是 Structured Query Language。

3.SQL 可以访问和处理数据库。

4.SQL 是一种 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言。

SQL语句特点

1. 具有综合统一性，不同数据库的支持的sql稍有不同

为许多任务提供了统一的命令，这样方便用户学习和使用，基本的 SQL 命令只需很少时间就能学会，甚至最高级的命令也可以在几天内掌握。数据库的操作任务通常包括以下几方面：增、删、改、查

常见数据库（mysql、sqlserver、oracle、db2等）

2. 非过程化语言

3.语言简捷，用户容易接受 select , drop, alter, create, insert, update ,delete

select * from mayikt_users ----查询 mayikt_users 的数据

4.集合性

SQL 可以在高层的数据结构上进行工作，工作时不是单条地处理记录，而对数据进行成组的处理语句都接受集合作为输入，并且返回集合作为输出

SQL语法特点

1.SQL 对关键字大小不铭感;

2.SQL语句可以单行或者多行数据，以分行结束;

3.SQL语句注释：

-- 单行注释 （后面一定要加一个空格）

#单行注释 #后面可以不用加空格

/*

多行注释

多行注释

*

/

数据库系统简介

数据库是一个以某种有组织的方式存储在硬盘上数据集合；

id	name（名称）	age(年龄)
1	mayikt	23
2	meite	28
3	zhangsan	18
4	lisi	19

数据库应用场景 以后开发JavaWeb项目 背后的数据都是存放在数据库（互联网）中。

数据库系统的分类

A.关系型数据库(RDBMS)

1.Oracle数据库 （甲骨文公司） 收费的

2.MySQL数据库（最流行的数据库） 免费版本 源代码开源

3.SQLServer 数据库 （微软开发的数据库）c#（微软公司） windows

4.Sqlite（嵌入式关系数据库） 学习 安卓手机端程序开发

5.db2

B.非关系型数据库(NoSQL)

1.Redis（缓存数据库）

2.Mongodb（文档数据库）

3.Elasticsearch(搜索服务)

4.Hbase（分布式、列示数据库）

SQL与数据库的关系

1.SQL是一种用于操作数据库的语言，SQL适用于所有关系型数据库

2.MySQL、Oracle、SQLServer、DB2 是一个数据库软件，这些数据库软件支持标准SQL，也就是通过SQL可以使用这些软件，不过每一个数据库系统会在标准SQL的基础上扩展自己的SQL语法，大部分的3.NoSQL数据库有自己的操作语言，对SQL支持的并不好。

SQL 属于 数据库编程语言 数据库 MySQL、Oracle、SQLServer、DB2 数据库软件

MySQL简介

简介：

1.MySQL数据库管理系统由瑞典的DataKonsultAB公司研发，该公司被Sun公司收购，现在Sun公司又被Oracle公司收购，因此MySQL目前属于 Oracle 旗下产品。

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

特点：

1.MySQL数据库是用C和C++语言编写的，以保证源码的可移植性

2.支持多个操作系统例如：Windows、Linux、Mac OS等等

3.支持多线程，可以充分的利用CPU资源

4.为多种编程语言提供API，包括C语言，Java，PHP、Python、go语言等

5.MySQL优化了SQL算法，有效的提高了查询速度

6.MySQL开放源代码且无版权制约，自主性强、使用成本低。

7.MySQL历史悠久、社区及用户非常活跃，遇到问题，可以很快获取到帮助。

MySQL的安装与卸载

安装

解压安装

MySQL的安装两种方式（MySQL8.0）

1.解压版本 或者直接安装方式

MySQL :: Download MySQL Community Server

1.解压mysql-8.0.27-winx64

注意：不要放在有中文名字和空格的的目录下

2.在mysql-8.0.27-winx64文件夹下面新建一个my.ini文件和一个data文件夹

使用mysql 数据 ----data文件夹中

3.my.ini 添加以下内容：

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\\mysql-8.0.27-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\\mysql-8.0.27-winx64\\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

# 设置mysql的安装目录

basedir=D:\\mysql-8.0.27-winx64

# 设置mysql数据库的数据的存放目录

datadir=D:\\mysql-8.0.27-winx64\\data

改成自己 路径

4.配置系统环境

我的电脑→属性→高级→环境变量→系统变量→新建

变量名：MYSQL_HOME

变量值：D:\path\mysql\mysql-8.0.27-winx64 （也就是刚解压的地方）

Path 中新建一段：%MYSQL_HOME%\bin

5.以管理员的身份打开cmd窗口跳转路径到D:\path\mysql\mysql-8.0.27-winx64，依次输入以下命令： 注意：是以管理员的身份打开cmd

1.初始化：mysqld --initialize --user=mysql --console

初始化 mysql 成功之后 账户和密码

账户：root

密码：随机密码 ,8s5%IoDpgu,

2.添加服务 mysqld -install

3.启动服务 net start mysql

4.登进数据库 （密码就输入刚才的随机密码） mysql -u root -p

就是为临时密码 ;tTqBrpRs3#d

测试

root 账户的密码 错误

5.ALTER USER root@localhost IDENTIFIED BY 'root'; # 修改密码为：root

退出：exit

直接安装

2.直接安装方式

MySQL :: Download MySQL Installer (Archived Versions)

一、双击运行安装包执行安装

1、选择Custom，该种方式可以设置安装位置，仅安装所需的组件，点击Next

2、选择需要的组件，点击Advanced Options

3、设置安装位置，点击OK

4、点击Next

5、点击Execute

6、点击Next

7、点击Next

8、选择配置类型，端口号等，直接默认即可，勾选Advanced Configuration下面的框，可以设置日志、服务器id等参数，点击Next

9、设置认证方式，选择第二项，可以兼容以前版本，点击Next

10、设置root账户，也可添加其他账户，并设置它们的角色，点击Next

11、配置mysql服务名，直接默认就好，点击Next

12、配置日志选项等，直接默认，点击Next

13、配置服务器id，该项一般用在主从服务器设置，本次直接默认，点击Next

14、应用配置，点击Execute

15、点击Finish

二、双击运行安装包执行卸载

1、点击Remove

2、勾选要卸载的产品，点击Next

3、勾选是否删除数据，本次勾选，点击Next

4、执行卸载，点击Execute

5、勾选卸载安装包，点击Finish，电脑进行重启，卸载完成！

MySQL客户端工具

Navicat

Navicat for MySQL 是管理和开发 MySQL 或 MariaDB 的理想解决方案。它是一套单一的应用程序，能同时连接 MySQL 和 MariaDB 数据库，并与 Amazon RDS、Amazon Aurora、Oracle Cloud、Microsoft Azure、阿里云、腾讯云和华为云等云数据库兼容。这套全面的前端工具为数据库管理、开发和维护提供了一款直观而强大的图形界面。

Navicat | 下载 Navicat Premium 14 天免费 Windows、macOS 和 Linux 的试用版

在使用 Navicat for Mysql连接mysql 8.0时会报如下错误：

mysql8.0 引入了新特性 caching_sha2_password；这种密码加密方式客户端不支持；客户端支持的是mysql_native_password 这种加密方式；

1.mysql -u root -p

2.USE mysql;

3.ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

4.FLUSH PRIVILEGES;

SQLyog

SQLyog 是一个快速而简洁的图形化管理MYSQL数据库的工具，它能够在任何地点有效地管理你的数据库，由业界著名的Webyog公司出品。

使用SQLyog可以快速直观地让您从世界的任何角落通过网络来维护远端的MySQL数据库。

Dbeaver

dbeaver是免费和开源（GPL）为开发人员和数据库管理员通用数据库工具。

易用性是该项目的主要目标，是经过精心设计和开发的数据库管理工具。免费、跨平台、基于开源框架和允许各种扩展写作（插件）。

它支持任何具有一个JDBC驱动程序数据库。

它可以处理任何的外部数据源。

MySQL连接不上怎么办？

我们如果在使用Navicat 连接 mysql服务器端 报如下错误：

解决办法：

1.检查mysql ip和端口号码是否正确

2.检查mysql服务是否启动（例如杀毒软件优化电脑过程中 会停止mysql服务）

数据库基本操作

DDL

数据库模式定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。

1.对数据库创建、删除、修改操作

2.对表结构创建、删除、修改操作

创建数据库（database）

n多张不同的表结构

mayiktuser1

mayiktuser2

.......

数据库操作

查询

查询所有数据库的名称:
show databases;
查询某个数据库的字符集:查询某个数据库的创建语句
show create database 数据库名称;
查询当前正在使用的数据库名称
select database();
使用数据库
use 数据库名称;

创建

 创建数据库：
create database 数据库名称;
创建数据库，判断不存在，再创建：
create database if not exists 数据库名称;
创建数据库，并指定字符集
create database 数据库名称 character set 字符集名;

删除

语法： 删除数据库
drop database 数据库名称;
判断数据库存在，存在再删除
drop database if exists 数据库名称;

修改

注意：一般不对数据库进行修改操作，因为修改的安全性低，容易导致数据的缺失。
基本操作：修改数据库的字符集
alter database 数据库名称 character set 字符集名称;

表操作

创建表

 create table if not exists 表名(
                列名1 数据类型1[长度] 【字段约束】,
                列名2 数据类型2[长度] 【字段约束】,
                ....
                列名n 数据类型n[长度] 【字段约束】
            );
    * 注意：最后一列，不需要加逗号（,）

use mayiktmeite;
CREATE TABLE if not exists mayikt_user(
	id INT,
	name VARCHAR(20),
    age int,
    create_time date,
    address VARCHAR(100)
);

修改

1.修改表名称：

语法：alter table 表名 rename to 新的表名;

ALTER TABLE mayikt_user RENAME TO meite_user ;

删除表

drop table 表名;

drop table if exists 表名;

数据类型

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-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的值	小数值

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

类型	大小(bytes)	范围	格式	用途
YEAR	1	1901/2155	YYYY	年份值
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
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	混合日期和时间值，时间戳

字符串类型

类型	大小	用途
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 个字符

DML

数据操作语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和select 等。(增添改查）

DML与DDL区别？

DDL 对数据库 表结构 增加、修改 删除操作

DML 表结构中的数据 增加（insert）、修改（update） 删除（delete） 查询（select ）

insert

INSERT INTO 语句用于向表格中插入新的行。

语法格式：

INSERT INTO 表名称 (列名1，列名2 ...) VALUES (值1, 值2,....) // 插入数据列与值的顺序需要一一对应。

INSERT INTO 表名称 VALUES (值1, 值2,....) // 向表中插入所有列

例子：

INSERT INTO `mayikt_user` (`id`, `name`, `age`, `addres`, `create_time`) VALUES (1, 'mm', 1, '1', '2022-01-20 03:51:54');

INSERT INTO `mayikt_user` VALUES (1, 'mm', 1, '1', '2022-01-20 03:51:54');

update

语法格式：

update 表名称 set 字段=值，字段=值，...； ---直接修改整张表的所有行数据

update 表名称 set 字段=值，字段=值，... where 条件 ---- 根据条件查找到对应行数据 修改

例子:

UPDATE mayikt_user ` SET `name` = 'mm', `age` = 25 WHERE `id` = 1;

UPDATE mayikt_user ` SET `name` = 'mm', `age` = 25 ---修改所有

delete

语法格式：

delete from 表名称 where 条件 根据条件删除表中的数据

TRUNCATE mayikt_user --清空表中所有的数据

例子：

delete from mayikt_user where where id=1; 根据条件id=1 删除id=1 行数据

delete from mayikt_user ---清空表中所有的数据

TRUNCATE mayikt_user --清空表中所有的数据

TRUNCATE 与delete 不同？

truncate:会清空表中所有的数据，速度快，不可回滚；实质是删除整张表包括数据再重新创建表；

delete:逐行删除数据，每步删除都是有日志记录的，可以回滚数据；实质是逐行删除表中的数据；

作业题

dml 操作练习题

1.创建一个员工表employees 字段如下：

id（员工编号）	name（员工名称）	sex(员工性别) 1男 0 女	(salary)员工薪资
1	mayikt	1	3600

CREATE TABLE IF NOT EXISTS `mayikt_employees` (

id int ,

name varchar(20) ,

sex tinyint ,

salary double

);

2.插入一条数据

INSERT INTO `mayikt_employees` (`id`, `name`, `sex`, `salary`) VALUES (1, 'mayikt', 1, 3600);

3.修改 id=1 薪水 为8000

update mayikt_employees set salary='8000' where id=1

4.将表中的所有数据 名称为mayikt 改为meite

update mayikt_employees set name='meite'

5.修改 id=1 当前薪水+ 3000

update mayikt_employees set salary=salary+3000 where id=1

6.删除id=1 行数据

delete from mayikt_employees where id=1;

约束

什么是约束

用于限制表中的数据，为了保证表中数据的准确性和可靠性,不符合约束的数据，插入时就会失败。

约束条件在创建表时可以使用， 也可以修改表的时候添加约束条件

例如 用户信息表中 手机号码不允许为空，身份证号码不允许重复。

id	姓名(name)	手机号码(phone)	身份证号码(idCard)	性别	地址
1	mayikt	18140663385	420923111111111111111	男
2	mayikt	18140663385	420923111111111111112

约束作用

用于限制表中的数据，为了保证表中数据的准确性和可靠性,不符合约束的数据，插入时就会失败。

约束分类

1.NOT NULL ：非空，用于保证该字段的值不能为空。例如学生表的学生姓名及学号等等。

2.DEFAULT：默认值，用于保证该字段有默认值。例如学生表的学生性别

3.PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。例如学生表的学生学号等。

4.UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空。例如注册用户的手机号，身份证号等。

5.CHECK：检查约束（MySql不支持），检查字段的值是否为指定的值。

6.FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。例如学生表的专业编号

主键约束

1.MySQL 主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行，这样的一列或多列称为表的主键，通过它可以强制表的实体完整性，同时可以方便根据主键查询该行数据。

2.选取设置主键约束的字段 主键约束即在表中定义一个主键来唯一确定表中每一行数据的标识符,主键可以是表中的某一列或者多列的组合，其中由多列组合的主键称为复合主键,主键应该遵守下面的规则

3.每个表只能定义一个主键，主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在两行数据有相同的主键值，这是唯一性原则

4.在创建表时设置主键约束 在 CREATE TABLE 语句中，主键是通过 PRIMARY KEY 关键字来指定的

5.当创建主键的约束时，系统会默认所在的列和列组合 建立对应的索引，方便提高查询效率。

id	姓名	年龄	身份证号码(idCard)
1	mayikt	22	420923111111111111111
2

id	姓名(name)	手机号码(phone)	身份证号码(idCard)	性别	地址
1	mayikt	18140663385	420923111111111111111	男
2	mayikt	18140663385	420923111111111111112	女

如果使用表中的 一列 主键-------单列主键

使用表中的 多个列（id，手机号码、身份证号码）多列-多列主键（复合主键）

条件：一张表中只能够允许有一个主键、主键值 不允许是为空 主键保证 每行数据完整唯一性

不允许重复的。

mysql 查询高级知识 索引--- 索引 方便提高查询效率

1.添加单列主键

2.添加多列联合主键

使用主键约束 PRIMARY KEY。

单列主键

1.定义字段时，指定单列主键

语法格式：

CREATE TABLE 表的名称 (

<字段名> <数据类型> PRIMARY KEY

}

CREATE TABLE `mayikt_users` (
  `id` int    PRIMARY KEY ,
  `name` varchar(255) ,
  `age` int 
) ;

1. 定义完字段之后，指定主键列（复合主键）

CREATE TABLE 表的名称 (

....

CONSTRAINT 主键名称 PRIMARY key (主键列);

}

CREATE TABLE `mayikt_users` (
  `id` int ,
  `name` varchar(255) ,
  `age` int,
  CONSTRAINT pk1 PRIMARY key (id,phone)
) ;

联合主键

联合主键（复合主键），由多个列（字段组成）。

注意事项：

1.当主键是有一个列组成时，不能够直接在字段名称后面声明主键约束；

2.一张表只能够允许一个主键

CREATE TABLE `mayikt_users` (
  `id` int,
  `name` varchar(255),
  `age` int ,
  `phone` varchar(11) ,
  CONSTRAINT pk1 PRIMARY KEY (id,phone)
);

自定增长约束

设置自动增长

1.在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值，每增加一条记录，主键自动增加。

2.通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长

3.语法格式如下：

字段名 数据类型 AUTO_INCREMENT

4.默认情况下，AUTO_INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。

一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复(即为主键或主键的一部分)。

AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性。

AUTO_INCREMENT 约束的字段只能是整数类型(TINYINT、SMALLINT、INT、BIGINT 等)。

AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束，如果达到上限，AUTO_INCREMENT 就会失效。

注意：

1.一张表中只能有一个自动增长的字段
2.配合主键一起使用 并且只适用于整数类型
3.自动增长默认的初始值1，每增加一条记录，该字段的值会增加1

drop table mayikt_users;
CREATE TABLE `mayikt_users` (
  `id` int PRIMARY KEY AUTO_INCREMENT  ,
  `name` varchar(255),
  `age` int ,
  `phone` varchar(11) 
);

自动增长设置初始值

默认自动增长初始值是从1开始

mysql 指定自动增长字段初始值 1000 2000

1.创建表的时候指定

CREATE TABLE `mayikt_users` (
  `id` int PRIMARY KEY AUTO_INCREMENT  ,
  `name` varchar(255),
  `age` int ,
  `phone` varchar(11) 
)AUTO_INCREMENT=1000;

2.创建表之后修改自定增长开始值

alter table mayikt_users AUTO_INCREMENT 2000;

delete与truncate 删除数据区别

delete 删除数据之后，自动增长还是从最后一个删除数据的id基础上做自增;

truncate 清空数据之后 自动增长是从初始值1开始

delete from mayikt_users ---清空表数据

truncate mayikt_users ---清空表数据

TRUNCATE 与delete 不同？

truncate:会清空表中所有的数据，速度快，不可回滚；实质是删除整张表包括数据再重新创建表；

自动设定初始值 失效

delete:逐行删除数据，每步删除都是有日志记录的，可以回滚数据；实质是逐行删除表中的数据；

非空约束

1.MySql -- not null 非空约束用于确保当前列的值不为空；在创建表时，如果指定 not null 该字段在插入数据时

不允许为空;

2.语法格式；

2.1 创建表的时候 <字段名><数据类型>NOT NULL;

CREATE TABLE `mayikt_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int NOT NULL,
  `phone` varchar(11) NOT NULL,
  PRIMARY KEY (`id`)
);

2.2 alter table 【数据库名.】表名称 modify 字段名 数据类型 not null;

唯一约束

MySQL 唯一约束（Unique Key）是指所有记录中该字段的值不能重复出现。例如为 phone 字段加上唯一性约束后，每条记录的 phone 值都是唯一的，不能出现重复的情况。如果其中一条记录的 phone 值为‘1880663389’，那么该表中就不能出现另一条记录的 phone 值也为‘1880663389’，唯一约束值可以允许null。、

主键约束 满足唯一性且值不允许为null 而我们的唯一约束 值允许为null

一张表结构中只允许有一个主键约束但是可以有多个唯一约束。

例如手机号码是唯一约束，每条记录的手机号码是不允许重复且保证唯一。

1.在创建表时设置唯一约束

语法规则:

UNIQUE KEY 约束名称 (约束字段)

CREATE TABLE `mayikt_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255),
  `age` int DEFAULT NULL,
  `phone` varchar(11) ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_phone` (`phone`)  -- 指定约束名称和约束字段
);

2.在修改表时添加唯一约束

语法规则如下：

ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE (<列名>)；

ALTER TABLE mayikt_users ADD CONSTRAINT unique_phone UNIQUE(phone);

[SQL]ALTER TABLE mayikt_users ADD CONSTRAINT unique_phone UNIQUE(phone);

[Err] 1062 - Duplicate entry '1880663389' for key 'mayikt_users.unique_phone'

该表中手机号码数据已经重复呢，

3.删除约束

ALTER TABLE mayikt_users DROP INDEX unique_phone;

默认约束

默认值（Default）的完整称呼是“默认值约束（Default Constraint）”，用来指定某列的默认值。在表中插入一条新记录时，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。其中，“默认值”为该字段设置的默认值，如果是字符类型的，要用单引号括起来。

1.在创建表时设置默认值约束

<字段名> <数据类型> DEFAULT <默认值>;

CREATE TABLE `mayikt_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT 'mayikt',
  `age` int DEFAULT 1,
  `phone` varchar(11) ,
  PRIMARY KEY (`id`)
);

2.在修改表时添加默认值约束

ALTER TABLE <数据表名>

CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;

ALTER TABLE mayikt_users

CHANGE COLUMN phone phone varchar(11) DEFAULT '1111';

零填充约束

定义了数据类型的长度，如果实际位数小于定义的长度，显示时会在左边用0填充

语法:

create table 表名称
(
    字段名称 int zerofill,
    ...
);

id int(10)

id=1

0000000001

id=1000

0000001000

DQL

什么是DQL

1.DQL（Data Query Language）即数据库查询语言，用来查询所需要的信息，在查询的过程中，需要判断所查询的数据与表之间的关，我们可以使用select语句来查询数据。

select * from 表的名称 where 查询的条件

DML与DDL区别？

DDL 对数据库 表结构 增加、修改 删除操作

DML 表结构中的数据 增加（insert）、修改（update） 删除（delete）

2.查询语句语法格式

SELECT [ALL|DISTINCT]
<目标列表达式> [别名] [ ,<目标列表达式> [别名]] …
FROM <表名或视图名> [别名] [ ,<表名或视图名> [别名]] …
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]
[limit <数字或者列表>]

分页、分组、排序

select *(列名称) from 表名称 where 条件

1.查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件;

2.SELECT 命令可以读取一条或者多条记录;

3.使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据

4.使用 WHERE 语句来包含任何条件。

5.使用 LIMIT 属性来设定返回的记录数。

6.OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

3.数据的初始化

-- 创建数据库 ddl
create DATABASE if not EXISTS   mayikt;
-- 使用mayikt数据库
use mayikt;
drop table mayikt_student;
-- 创建mayikt_student学生表
CREATE TABLE `mayikt_student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) not null COMMENT '姓名',
  `age` tinyint  COMMENT '年龄',
  `address` varchar(255) COMMENT '地址',
  `class_id` int  COMMENT '班级id',
  PRIMARY KEY (`id`)
);
-- 新增测试数据 dml
INSERT INTO mayikt_student VALUES(NULL,'余胜军',28,'湖北武汉','01');
INSERT INTO mayikt_student VALUES(NULL,'小哈',21,'上海','01');
INSERT INTO mayikt_student VALUES(NULL,'张三',17,'北京','02');
INSERT INTO mayikt_student VALUES(NULL,'李四',22,'山东','02');
INSERT INTO mayikt_student VALUES(NULL,'王麻子',11,'四川','02');

基本查询

-- 1.查询所有的学生
select * from mayikt_student;
-- 2.查询学生的姓名和年龄
select name,age from mayikt_student;
-- 3.别名称查询 使用关键字as 
select * from mayikt_student as student;
-- 4.列别名称
select name as 姓名,age 年龄  from mayikt_student as student;
-- 5.去重复值
select DISTINCT class_id from mayikt_student;
-- 6.查询结果是表达式(运算值)；将所有的学生年龄+5岁
select name,age+5 as age  from mayikt_student;

-- 1.查询所有的学生
-- 2.查询学生的姓名和年龄
-- 3.别名称查询 使用关键字as 
-- 4.列别名称
-- 5.去重复值
-- 6.查询结果是表达式(运算值)；将所有的学生年龄+5岁

运算符

数据库中的表结构确立后，表中的数据代表的意义就已经确定。而通过MySQL运算符进行运算，就可以获取到表结构以外的另一种数据。例如，学生表中存在一个birth（出生日期）字段，这个字段表示学生的出生年份。而运用MySQL的算术运算符用当前的年份减学生出生的年份，那么得到的就是这个学生的实际年龄数据。这就是MySQL的运算符，所以熟悉并掌握运算符的应用，我们需要熟悉一下MySQL支持的4种运算符都具备哪些功能。

name	birth
余胜军	1997.10.7

算术运算符

比较运算符

逻辑运算符

位运算符

算术运算符

算术运算符是MySQL中最常用的一类运算符。MySQL支持的算术运算符包括：加、减、乘、除、求余。

运算符	作用
+	加法
-	减法
*	乘法
/ 或 DIV	除法
% 或 MOD	取余

select 6+2;
select 6-2;
select 6*2;
select 6/2;
select 6%2;
-- 将每位学生的年龄+10
SELECT `name`,age +10 as age from mayikt_student;
-- 将每位学生的年龄乘以5
SELECT `name`,age *5 as age from mayikt_student;

比较运算符

比较运算符是查询数据时最常用的一类运算符。SELECT语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。

符号	描述	备注
=	等于
<>, !=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN	在两值之间	>=min&&<=max
NOT BETWEEN	不在两值之间
IN	在集合中
NOT IN	不在集合中
<=>	严格比较两个NULL值是否相等	两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
LIKE	模糊匹配
REGEXP 或 RLIKE	正则式匹配
IS NULL	为空
IS NOT NULL	不为空

逻辑运算符

逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回1。如果表达式是假，结果返回0。逻辑运算符又称为布尔运算符。MySQL中支持4种逻辑运算符，分别是与、或、非和异或。

运算符号	作用
NOT 或 !	逻辑非
AND	逻辑与
OR	逻辑或
XOR	逻辑异或

位运算符

参与运算符的操作数，按二进制位进行运算。包括位与（&）、位或（|）、位非（~）、位异或（^）、左移（<<）、右移(>>)6种。

运算符号	作用
&	按位与
|	按位或
^	按位异或
!	取反
<<	左移
>>	右移

比较和逻辑运算符使用

--查询学生的名称是为余胜军
SELECT * from mayikt_student where name ='余胜军'
--查询学生的名称不是为余胜军
SELECT * from mayikt_student where name !='余胜军'
SELECT * from mayikt_student where name <>'余胜军'
SELECT * from mayikt_student where not (name ='余胜军')
--查询学生年龄是为17岁
SELECT * from mayikt_student where age=17
--查询学生年龄是大于17岁
SELECT * from mayikt_student where age>17
--查询学生年龄是小于17岁
SELECT * from mayikt_student where age<17
--查询学生年龄是18岁-40岁之间
SELECT * from mayikt_student where age>17 and age<41
SELECT * from mayikt_student where age>17 && age<41
SELECT * from mayikt_student where age  BETWEEN  18 and 40
-- 查询年龄是在17或者 28岁的学生
SELECT * from mayikt_student where age=17 or age=28;
SELECT * from mayikt_student where age=17 || age=28;
SELECT * from mayikt_student where  age in (17,28);

--查询名称含有“军”
SELECT * from mayikt_student where name like '%军%'
--查询名称开头“小”
SELECT * from mayikt_student where name like '小%'
--查询名称第二字“汉”
SELECT * from mayikt_student where name like '_汉%'
--查询地址是为null学生
SELECT * from mayikt_student where address is null;
--查询地址不是为null的学生
SELECT * from mayikt_student where address is not null;

--查询学生的名称是为余胜军
--查询学生的名称不是为余胜军
--查询学生年龄是为17岁
--查询学生年龄是大于17岁
--查询学生年龄是小于17岁
--查询学生年龄是18岁-40岁之间
-- 查询年龄是在17或者 28岁的学生

--查询名称含有“军”
--查询名称开头“小”
--查询名称第二字“汉”
--查询地址是为null学生
--查询地址不是为null的学生

排序

如果我们需要对读取的数据进行排序，可以利用 order by 根据字段来进行升序或者降序排列 再返回结果。

升序： 从小到大

降序：从大到小

order by 根据字段 数字、字母、汉字

语法格式：

以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：
SELECT field1, field2,…fieldN table_name1, table_name2…
ORDER BY field1, [field2…] [ASC [DESC]]

1.asc代表 升序排列 desc代表降序排列 默认是为升序;

升序就是从小到大、降序就是从大到小

2.order by 可以支持单个字段，多个字段 ；

3.order by 放在查询语句最后面，limit 分页除外；

1.根据学生年龄从小到大;
SELECT * from mayikt_student  order by age;
2.根据学生年龄从大到小;
SELECT * from mayikt_student  order by age desc;
3.判断学生的年龄大于18岁，在从小到大排序
SELECT * from mayikt_student where age>18  order by age ;
4.根据学生的年龄从大到小排序，以班级id 从小到大排序  当年龄相同 则根据 班级id从小到大排序
SELECT * from mayikt_student order by age desc ,class_id desc ;
5.根据班级id去重，根据班级id从大到小排序
SELECT DISTINCT class_id from mayikt_student ORDER BY class_id desc;

1.根据学生年龄从小到大;
2.根据学生年龄从大到小;
3.判断学生的年龄大于18岁，在从小到大排序
4.根据学生的年龄从大到小排序，以班级id 从小到大排序  当年龄相同 则根据 班级id从小到大排序
5.根据班级id去重，根据班级id从大到小排序

一般的情况下我们使用数字排序，从小到大 或者从大到小；

如果是字母排序 则 根据字母的顺序 从A到Z排序 或者Z到A顺序

如果是汉字的拼音排序，用的比较多是在人名的排序中，按照姓氏的拼音字母，从A到Z排序

分页

概述：limit

在mysql中当数据量很大时，显示屏长度有限，我们可以对数据进行分页显示，例如数据总共

100条，每页10条数据，可以分成10页。

格式：

方式1：显示前n条
select 字段1，字段2 ... from  mayikt_student limit n 
方式2：分页显示
select 字段1，字段2 ... from  mayikt_student limit m,n

m: 整数，表示从第几条索引开始 计算方式(当前页-1)*每页显示条数

n：整数，表示查询多少条数据

案例：

SELECT * from mayikt_student limit 5;
SELECT * from mayikt_student limit 5,5;

需求

1.查询用户表中前5条数据
SELECT * from mayikt_student limit 5;
2.从第6条开始显示 显示5条
SELECT * from mayikt_student limit 5,5;

聚合查询

我们在之前的查询是根据条件一行一行的判断，而使用聚合查询，它是对列的值进行计算，让后返回

一个单一的值。

聚合函数	作用
count(age)	统计指定列不为null的记录行数
sum()	计算指定列的数据和
max()	计算指定列的数据最大值
min()	计算指定列的数据最小值
avg()	计算指定列的数据平均值

1.查询学生表的总人数
SELECT count(*) from mayikt_student 
2.查询学生年龄大于18的 总人数
SELECT count(*) from mayikt_student where age>18
3.查询classid=1 所有学生年龄总和
SELECT SUM(age) from mayikt_student where class_id='1'
4.查询学生最大年龄
SELECT max(age) from mayikt_student 
5.查询学生最小年龄
SELECT max(age),min(age) from mayikt_student
6.求学生年龄的平均值
SELECT avg(age) from mayikt_student 

1.查询学生表的总人数
2.查询学生年龄大于18的 总人数
3.查询classid=1 所有学生年龄总和
4.查询学生最大年龄
5.查询学生最小年龄
6.求学生年龄的平均值

聚合查询 null的处理

1.count函数对null值的处理

如果count函数的参数(*),则统计所有记录的行数，如果参数是为某字段，不统计含null值记录行数

2.sum、avg、max、min 函数对null 也是做忽略

1.查询学生年龄为null总人数
SELECT  count(*) from mayikt_student where age is null;

分组查询

分组查询是指使用 group by 字句对查询信息进行分组。

湖北省

山东省

广东省

格式：

select 字段1，字段2 ... from 表名称 group by 分组字段 having 分组条件

分组的条件使用 having 不是where

案例:

SELECT class_id ,count(*) from mayikt_student group by class_id;

1.统计class_id 分类对应学生的个数
SELECT class_id ,count(*) from mayikt_student group by class_id; 

注意分组 返回列 只能为 分组的字段或者聚合函数；

分组之后的条件筛选

1.分组之后对统计结果进行分组条件筛选 必须使用having，不能够使用where

2.HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集

语法格式：

SELECT 字段1，字段2 ... 
from mayikt_student GROUP BY 分组字段  having 分组条件;

案例需求：

统计每个班级学生人数大于1人以上的班级

SELECT class_id as 班级, count(*) as 人数 from mayikt_student GROUP BY class_id HAVING 
count(*) >1

先from 、 GROUP BY 、count 、HAVING

注意事项 ：

1、where 后不能跟聚合函数，因为where执行顺序大于聚合函数。

2、where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

3、having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

作业题

练习题1

CREATE TABLE `meite_student` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `chinese` int DEFAULT NULL,
  `english` int DEFAULT NULL,
  `maths` int DEFAULT NULL
) ;

测试数据
INSERT INTO `mayikt`.`meite_student`
(`id`, `name`, `sex`, `chinese`, `english`, `maths`) 
VALUES ('1', '余胜军', '男', '98', '99', '100');
INSERT INTO `mayikt`.`meite_student`
(`id`, `name`, `sex`, `chinese`, `english`, `maths`) 
VALUES ('2', '刘文', '女', '99', '87', '88');
INSERT INTO `mayikt`.`meite_student`
(`id`, `name`, `sex`, `chinese`, `english`, `maths`) 
VALUES ('3', '王麻子', '女', '22', '55', '33');
INSERT INTO `mayikt`.`meite_student`
(`id`, `name`, `sex`, `chinese`, `english`, `maths`)
VALUES ('4', '黄红军', '男', '55', '33', '22');
INSERT INTO `mayikt`.`meite_student` 
(`id`, `name`, `sex`, `chinese`, `english`, `maths`) 
VALUES ('5', '张玲', '女', '95', '95', '88');

1.查询表中所有学生信息
2.查询表中所有学生信息 只返回学生名称和数学成绩 字段
3.过滤表中重复数据
4.统计每个学生的总分
5.所有学生总分 加5分
6.查询语文成绩大于80分以上同学
7.查询总分大于180分以上同学
8.查询英语成绩80-90 之间的同学
9.查询英语成绩不在80-90 之间的同学
10.查询英语成绩22,99,33学生 信息
11.查询所有姓余的学生信息 like
12.查询语文、数学、外语 大于等于72分的学生信息
13.查询英语大于等于72分或者总分大于等于180分以上学生信息 or
14.根据英语成绩字段排序 升序和降序
15.根据总分成绩 从高到低排列
16.统计学生表中 性别男和女 分别有多少学生
17.根据英语成绩字段排序 (从高到低排序 取前3名)
SELECT name, chinese+english+maths  as  总分 
FROM meite_student ORDER BY  chinese+english+maths desc 
limit 3;

1.查询表中所有学生信息
select * from meite_student;
2.查询表中学生名称和数学成绩
select `name`,maths from meite_student;
3.过滤表中重复数据
select DISTINCT *  from meite_student;
4.统计每个学生的总分
select name 姓名, chinese+english+maths 总分  from meite_student 
5.所有学生总分 加5分
select name 姓名, (chinese+english+maths)+5 总分  from meite_student 
6.查询语文成绩大于80分以上同学
select *  from meite_student  where chinese>80
7.查询总分大于180分以上同学
select name 姓名, (chinese+english+maths) as 总分  from meite_student 
where chinese+english+maths>180
8.查询英语成绩80-90 之间的同学
select  *  from meite_student 
where english >80 and english<90
select  *  from meite_student 
where english  BETWEEN 80 and 90  
9.查询英语成绩不在80-90 之间的同学
select  *  from meite_student 
where english  not BETWEEN 80 and 90  
10.查询英语成绩22,99,33学生 信息
select  *  from meite_student 
where english in(22,99,33)
11.查询所有姓余的学生信息
SELECT * from meite_student where name like  '%余%'
12.查询语文、数学、外语 大于等于72分的学生信息
SELECT * from meite_student where chinese>=72 
and english>=72 and maths>=72
13.查询语文和数学大于等于72分或者总分大于180分以上学生信息
SELECT * from meite_student where
 (english>=72 and maths>=72) or   (chinese+english+maths)>180
14.根据英语成绩字段排序 升序和降序
SELECT * from meite_student ORDER BY english desc  
15.根据总分成绩 从高到低排列
SELECT name as  姓名,(chinese+english+maths) as 总分
 from meite_student order by  (chinese+english+maths) asc  
16.统计学生表中 姓名男和女 分表有多少学生
SELECT sex ,count(*) from meite_student GROUP BY sex 

练习题2

需求：

-- 1. 根据员工的薪资升序或者降序排列
-- 2.根据员工的薪资升序排列，且不查询到财务部门的员工信息
-- 3.查询姓名第二字非“麻”的且薪资>=15000的员工信息，根据薪资升序排列
-- 4.查询每位员工综合年薪 根据年薪降序排列
-- 5.求每个不同部门的平均薪水
-- 6.求每个不同部门的平均薪水且平均薪资大于13000的部门
-- 7.求每个部门中最高薪水
-- 8.求每个部门有多少员工
-- 9.查询员工最高薪资和最低薪资员工信息
-- 10.查询员工最高薪资和最低薪资的差距

初始化sql：

CREATE TABLE `employee` (
  `number` int DEFAULT NULL COMMENT '员工编号',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '姓名',
  `hiredate` date DEFAULT NULL COMMENT '入职时间',
  `salary` double DEFAULT NULL COMMENT '薪资',
  `bonus` double DEFAULT NULL COMMENT '奖金',
  `department` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门编号'
)
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1001', '余胜军', '2020-06-01', '18000', '3000', '研发部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1002', '刘军', '2019-06-01', '16000', '1000', '研发部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1003', '张三', '2018-02-10', '6000', '500', '财务部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1004', '王麻子', '2022-02-10', '27000', '500', '财务部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1005', '刘软', '2013-02-10', '6000', '500', 'UI部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1006', '王麻子', '2022-02-10', '6000', '500', 'UI部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1007', '李四', '2022-02-25', '5000', '100', '财务部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1008', '余国军', '2022-02-25', '10000', '50', '研发部门');

-- 1. 根据员工的薪资升序或者降序排列
select * from employee order by salary desc ;
select * from employee order by salary asc ;
-- 2.根据员工的薪资升序排列，且不查询到财务部门的员工信息
select * from employee where department!='财务部门' order by salary asc 
-- 3.查询姓名第二字非“麻”的且薪资>=15000的员工信息，根据薪资升序排列
select * from employee where name not  like '_麻%' and salary>=15000
order by salary asc ;
-- 4.查询每位员工综合年薪 根据年薪降序排列
select name as 姓名 ,(salary*12)+bonus as 年薪 from employee
order by (salary*12)+bonus desc 
-- 5.求每个不同部门的平均薪水
select department,avg(salary)  from employee GROUP BY  department
-- 6.求每个不同部门的平均薪水且平均薪资大于13000的部门
select department,avg(salary)  from employee GROUP BY  department 
HAVING avg(salary)>13000
-- 7.求每个部门中最高薪水
select max(salary)   from employee GROUP BY  department
-- 8.求每个部门有多少员工
select  department, count(*)  from employee GROUP BY  department
-- 9.查询员工最高薪资和最低薪资员工信息
select  *  from employee order by  salary desc  limit 1 
select  *  from employee order by  salary asc  limit 1 
-- 10.查询员工最高薪资和最低薪资的差距
select  max(salary)-min(salary)  from employee 

多表关系

实际的项目开发中，一个项目通常需要很多张表才能够完成，例如在学生系统中 有学生班级表(stu_class)、学生信息表(stu_info)等多张表。这些表存在一定的关系。

会员信息--会员信息表

课程信息---课程详细内容

例如

1.一对一 使用的比较少

2.一对多

3.多对多

一对一

1.一个学生只有一张身份证，一个身份证只能够对应一个学生

2.一般一对一关系使用的比较少

一对多

例如 学生班级表(stu_class)、学生信息表(stu_info)等多张表

1.一个学生只要一个班级，学生信息表与班级表一一对应；

2.在表中添加一个外键，指向另一方主键，确保一对一关系;

部门与员工

例如：一个部门有多个员工，一个员工只能够对应一个部门

多对多

学生和课程

1.一个学生可以选择多门课程学习 ，一个课程也可以被很多学生选择

原则：多对多关系实现需要借助第三章中间表实现，中间表至少需要包含两个字段，

将多对多的关系拆分一对多的关系。

外键约束

MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性

注意事项：主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。

定义外键时，需要遵守下列规则：

1.主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。

2.必须在主表定义主键。

3.主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中；

4.外键中列的数据类型必须和主表主键中对应列的数据类型相同。

创建主外键

在 CREATE TABLE 语句中，通过 FOREIGN KEY 关键字来指定外键，具体的语法格式如下：

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

例如：

CREATE TABLE `mayikt_class` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `mayikt_student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` tinyint DEFAULT '0',
  `address` varchar(255) DEFAULT NULL,
  `class_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT mayikt_class_id  FOREIGN KEY (class_id)  REFERENCES  mayikt_class(id) -- 外键约束
); 
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

[CONSTRAINT <mayikt_class_id>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]
CONSTRAINT mayikt_class_id  FOREIGN KEY (class_id)  REFERENCES  mayikt_class(id)

删除外键约束

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
ALTER TABLE mayikt_student DROP FOREIGN KEY mayikt_class_id;

验证：外键约束

1.先向主表新增数据，在向从表新增数据

2.外键列的值必须要在主表存在 或者是为空

3.主表的数据不能够随便删除，从表数据可以随便删除 或者先

删除从表所有数据没有任何关联主表的字段 在可以删除主表的对应的数据

联表查询

多表查询是指多张表联合一起查询，例如学生信息与学生班级表、部门与员工表。

1.交叉连接查询 (笛卡尔积)

2.内连接查询

3.外连接查询

4.子查询

5.表自关联

数据的准备：

CREATE TABLE `mayikt_class` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `mayikt_student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` tinyint DEFAULT '0',
  `address` varchar(255) DEFAULT NULL,
  `class_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT mayikt_class_id  FOREIGN KEY (class_id)  REFERENCES  mayikt_class(id) -- 外键约束
); 


INSERT INTO `mayikt`.`mayikt_class` (`id`, `name`) VALUES ('1', '第一期');
INSERT INTO `mayikt`.`mayikt_class` (`id`, `name`) VALUES ('2', '第二期');

INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`, `class_id`) VALUES ('1', 'mayikt', '18', '武汉市', '1');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`, `class_id`) VALUES ('2', 'meite', '23', '上海市', '2');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`, `class_id`) VALUES ('3', '李思', '12', '孝感市', '1');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`, `class_id`) VALUES ('4', '刘流', '27', '武汉市', '1');

交叉连接查询 (笛卡尔积)

语法：select  * from mayikt_class,mayikt_student

得到的查询结果是两张表的笛卡尔积，也就是用A表中的每条数据都去匹配B表中的所有数据，获得的结果往往不是我们需要的，一般很少使用交叉连接,缺点数据比较冗余。

内连接

显示内连接:

SELECT * FROM A INNER JOIN B ON 条件;

隐示内连接:

SELECT * FROM A,B WHERE 条件;

案例

-- 1.查询每个班级下所有学生信息
-- 显示内连接
select * from mayikt_class  INNER JOIN  mayikt_student 
on mayikt_class.id =mayikt_student.class_id;
-- 隐士内连接
SELECT * from mayikt_class ,mayikt_student where mayikt_student.class_id=
mayikt_class.id


-- 2.需求查询第一期所有学生

SELECT * from mayikt_class ,mayikt_student where mayikt_student.class_id=
mayikt_class.id  and mayikt_class.id='1'

select * from mayikt_class  INNER JOIN  mayikt_student 
on mayikt_class.id =mayikt_student.class_id and mayikt_class.id='1'

-- 3.查询第一期和第二期所有的学生
select * from mayikt_class  INNER JOIN  mayikt_student 
on mayikt_class.id =mayikt_student.class_id and (mayikt_class.id='1'
or  mayikt_class.id='2')
select * from mayikt_class  INNER JOIN  mayikt_student 
on mayikt_class.id =mayikt_student.class_id and mayikt_student.class_id  in 
(1,2)

-- 4.查询每个班级下的学生总数 并且学生总数升序排列
select mayikt_class.`name` , count(*) from mayikt_class  INNER JOIN  mayikt_student 
on mayikt_class.id =mayikt_student.class_id  GROUP BY  mayikt_class.id 
order by count(*) asc  
-- 5.查询班级总人数>2的班级，并且人数降序排列
select mayikt_class.`name` , count(*) from mayikt_class  INNER JOIN  mayikt_student 
on mayikt_class.id =mayikt_student.class_id  GROUP BY  mayikt_class.id  HAVING
count(*)>2
order by count(*) asc  

2.需求查询第一期所有学生

3.查询第一期和第二期所有的学生

4.查询每个班级下的学生总数 并且学生总数升序排列

5.查询班级总人数>2的班级，并且人数降序排列

外连接

外连接:左外连接、右外连接、全外连接（union）。

1.左外链接（left outer join，outer可以省略）

语法：SELECT * FROM A LEFT OUTER JOIN B ON 条件;

左外连接获得的查询结果是左边的表A的全部信息和A，B两张表的交集，左边A表的全部包含A表中在B表中没有对应关系的信息

2.右外连接（right outer join，outer可以省略）

语法：SELECT * FROM A RIGHT OUTER JOIN B ON 条件;

总结：右外连接获得的查询结果是右边的表B的全部信息和A，B两张表的交集，右边B表的全部包含B表中在A表中没有对应关系的信息

3.全外连接

select * from mayikt_class left join mayikt_student
on mayikt_class.id=mayikt_student.class_id
union
select * from mayikt_class right join mayikt_student
on mayikt_class.id=mayikt_student.class_id;

左连接 以左边为准 左变有该数据 就会返回 右变没有匹配上则直接返回为null

右连接 以右边为准 右变有该数据 就会返回 左变没有匹配上则直接返回为null

内连接左边与右边都是必须匹配才会返回

需求:

1.查询哪些班级是有学生 哪些班级是没有学生
select * from mayikt_class left join mayikt_student
on mayikt_class.id=mayikt_student.class_id;
2.查询哪些学生是有班级，哪些学生是没有班级
select * from mayikt_class right join mayikt_student
on mayikt_class.id=mayikt_student.class_id;
3.使用union关键字实现左连接和右连接的并集 让去重复数据

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

子查询

基本子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

通俗易懂也就是SELECT 嵌套的查询

子查询中常用的操作符有 ANY（SOME）、ALL、IN 和 EXISTS。

需求：

1.查询年龄最大的学生信息，显示信息包含 学生的id、学生的名称、学生的年龄

select * from mayikt_student order by age desc limit 1

select * from mayikt_student 
where age in (select max(age) from mayikt_student)

2.查询第一期和第三期学生信息

select * from mayikt_student join mayikt_class
on mayikt_student.class_id=mayikt_class.id where class_id='1' 
or class_id='3'

select * from mayikt_student where class_id in (select id from mayikt_class where id='1' or id='3'
)

3.查询第一期学生年龄大于18岁 学生信息

select * from mayikt_student  join mayikt_class
on mayikt_student.class_id=mayikt_class.id
and mayikt_student.class_id='1'
and mayikt_student.age>18;

select * from mayikt_student where age >18 and class_id in (
select id from mayikt_class where id='1')

select * from (select * from mayikt_class where id='1') a1 join 
(select * from mayikt_student where age >18) a2
on a1.id=a2.class_id;

子查询关键字之all

all关键字用在比较操作操符的后面，表示查询结果的多个数据中的所有都满足该比较操作符才算满足

比较操作符：= 、>、!=、>=、<=等

select ... from mayikt_user（表的名称） where age（字段）  > all(查询语句)
相当于：
select ... from mayikt_user（表的名称） where age> result1 and age >result2

需求1：查询年龄大于第一期所有年龄的学生信息

select * from mayikt_student where age> all(
select age from mayikt_student where class_id='1'
)

需求2：查询没有班级的学生信息

select * from mayikt_student
where class_id!= all(
select id from mayikt_class 
)

子查询关键字之any

select ... from mayikt_user（表的名称） where age（字段）  > any(查询语句)
相当于：
select ... from mayikt_user（表的名称） where age> result1 or age >result2

需求：查询学生年龄大于第一期任意一个学生年龄的信息

select * from mayikt_student where age> any(
select age from mayikt_student where class_id='1'

)
and class_id!=1;

all 底层 多个 and 比较

any 底层 多个 or 或者比较

子查询关键字之not in和in

语法格式：

select * from mayikt_student where 字段  in（查询语句）
select * from mayikt_student where 字段 =result1  or   字段 =result2

特点：

in关键字 用于判断某个记录的值 是否在指定的集合中

使用 not in 可以实现取反

需求：

查询第一期和第三期所有学生信息

select * from mayikt_student where class_id
 in (select id from mayikt_class where name='第一期' 
 or name='第二期' 
)

in 子查询语句中 等于 or 连接比较

SELECT * from mayikt_student

where class_id=1 or class_id =3

all 子查询语句中 and 比较符

any 子查询语句中 or 比较符

子查询关键字之exists

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False

EXISTS比in关键字运算效率高，实际开发中 如果是数据量大的情况下 推荐使用EXISTS关键字。

语法：

select * from mayikt_student where EXISTS
(查询语句---查询到结果 就返回true  没有查询到结果 就返回 false)

需求1:

1.查询学生年龄大于18岁 学生信息

select * from mayikt_student s1
where EXISTS(select * from mayikt_student s2
             where  s1.age >18
 )

2.查询班级下 有学生的班级

select * from mayikt_class a
 where  EXISTS(
select * from mayikt_student  b where b.class_id=a.id
)

3.查询有班级的学生信息

select * from mayikt_student
s1 where EXISTS(
select * from mayikt_class b where s1.class_id =
b.id
)

子查询之自关联查询

对mysql数据自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。

注意 关联查询时必须给表取别名

select 字段列表 from 表名称 a ,表 名称b where 条件;
或者
select 字段列表 from 表名称 a left join 表名称 b on 条件

以京东电商为例子：

京东(JD.COM)-正品低价、品质保障、配送及时、轻松购物！

表结构

CREATE TABLE `commodity_type` (
  `id` int NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '商品类型名称',
  `parent_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

select * from   commodity_type as a ,commodity_type
as b where a.id=b.parent_id;

多表综合练习题1

1.创建表结构

CREATE TABLE `mayikt_dept` (
  `dept_no` int NOT NULL COMMENT '部门id',
  `dept_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门名称',
  `dept_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门地址',
  PRIMARY KEY (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
CREATE TABLE `mayikt_emp` (
  `emp_number` int DEFAULT NULL COMMENT '员工编号',
  `emp_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工编号',
  `emp_post` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工职务',
  `emp_leader_number` int DEFAULT NULL COMMENT '员工领导编号',
  `emp_hiredate` datetime DEFAULT NULL COMMENT '员工入职时间',
  `emp_salary` double(10,0) DEFAULT NULL COMMENT '员工薪水',
  `emp_bonus` int DEFAULT NULL COMMENT '员工奖金',
  `emp_deptno` int DEFAULT NULL COMMENT '员工对外部门表外键'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `mayikt`.`mayikt_dept` (`dept_no`, `dept_name`, `dept_address`) VALUES ('1', '技术部门', '湖北武汉市');
INSERT INTO `mayikt`.`mayikt_dept` (`dept_no`, `dept_name`, `dept_address`) VALUES ('2', '财务部门', '中国上海市');
INSERT INTO `mayikt`.`mayikt_dept` (`dept_no`, `dept_name`, `dept_address`) VALUES ('3', '设计部门', '湖北孝感市');
INSERT INTO `mayikt`.`mayikt_emp` (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1001', '余胜军', 'CEO', NULL, '2021-11-01 11:32:46', '20000', '10000', '1');
INSERT INTO `mayikt`.`mayikt_emp` (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1002', '小薇', 'CFO', '1001', '2021-10-01 11:32:46', '5000', '10000', '2');
INSERT INTO `mayikt`.`mayikt_emp` (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1004', '张三', 'CTO', NULL, '2021-11-01 11:32:46', '80000', '10000', '1');
INSERT INTO `mayikt`.`mayikt_emp` (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1005', '李四', '技术总监', '1004', '2021-11-01 11:32:46', '20000', '10000', '1');
INSERT INTO `mayikt`.`mayikt_emp` (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1006', '王麻子', '客服', NULL, '2022-03-02 11:49:45', '3500', NULL, NULL);

1.返回员工拥有部门的 员工信息含员工部门
select * from mayikt_emp as a join mayikt_dept as b
on  a.emp_deptno=b.dept_no;
2.查询员工薪资大于小薇的 员工信息
select * from mayikt_emp where emp_salary>(
select emp_salary from mayikt_emp where emp_number='1002'
)
3.返回员工所属领导信息
select * from mayikt_emp as a, mayikt_emp as b
where a.emp_number=b.emp_leader_number

4.返回入职时间早于领导 入职时间
select * from mayikt_emp as a, mayikt_emp as b
where a.emp_number=b.emp_leader_number
and a.emp_hiredate>b.emp_hiredate
5.返回从事财务工作的员工信息
select * from mayikt_emp as a join mayikt_dept as b
on  a.emp_deptno=b.dept_no
and b.dept_name='财务部门'
6.求每个部门 最低员工薪资
select  emp_deptno,min(emp_salary)
 from mayikt_emp GROUP BY emp_deptno 
7.返回员工薪资大于 平均薪资员工
select  *
 from mayikt_emp where emp_salary >(
select  avg(emp_salary)
 from mayikt_emp
)

多表综合练习题2

单独创建员工等级表

CREATE TABLE `mayikt_salgrade` (
  `grade` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工等级',
  `losal` double(255,0) DEFAULT NULL COMMENT '最低工资',
  `hisal` double DEFAULT NULL COMMENT '最高工资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

1.查询工资大于财务部所有员工的 信息
select *
 from  mayikt_emp where emp_salary > all(
select emp_salary
 from  mayikt_emp where emp_salary and  emp_deptno='2'
)

2.求从事相同职务工作最低工资和最高工资
select emp_post,min(emp_salary) as 最低工资,max(emp_salary)  as 最高工资
 from  mayikt_emp  GROUP BY emp_post
3.计算每位员工的年薪，并且以年薪降序排列
select ((emp_salary *12)+emp_bonus) as 年薪
 from  mayikt_emp  ORDER BY 年薪 desc 
 4.返回工资处于P4级别员工的信息
 select *
 from  mayikt_emp where emp_salary BETWEEN (
select losal
 from  mayikt_salgrade where grade='P4'
) and (
select hisal
 from  mayikt_salgrade where grade='P4'
)
 5.返回工资处于P1员工信息含部门名称
 select *
 from  mayikt_emp as e join 
mayikt_dept as d on e.emp_deptno=d.dept_no
join mayikt_salgrade s 
on s.grade='p1'  and e.emp_salary>=s.losal and e.emp_salary
<=s.hisal

1.查询工资大于财务部所有员工的 信息

2.求从事相同职务工作最低工资和最高工资

3.计算每位员工的年薪，并且以年薪降序排列

4.返回工资处于P4级别员工的信息

5.返回工资处于P1员工信息含部门名称

JDBC快速入门

jdbc的概念

Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。通俗易懂说：jdbc就是java操作数据库

jdbc的本质就是：java官方提供的一套规范接口，用于帮助程序员开发者操作不同的关系型数据库(mysql/Oracle/SQLServer)

jdbc快速入门

1.创建一张表

CREATE TABLE `mayikt_users` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `pwd` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `mayikt`.`mayikt_users` (`id`, `name`, `pwd`) VALUES ('1', 'mayikt', '123');
INSERT INTO `mayikt`.`mayikt_users` (`id`, `name`, `pwd`) VALUES ('2', 'meite', '666');

2.在java官方只是提供JDBC规范的接口，如果需要连接到具体的数据库 例如

mysql ，我们就需要导入mysql的依赖jar包，具体实现是有不同的数据库

产商实现的。

1.导入mysql驱动jar包;

2.注册驱动 javase 反射机制Class.forName()

3.获取数据库连接

4.获取执行者对象

5.执行sql语句并获取返回结果

6.对结果进行处理

7.释放jdbc资源

jdbc驱动依赖下载：有道云笔记

编写代码

/**
* @author 余胜军
* @ClassName JdbcDemo01
* @qq 644064779
* @addres www.mayikt.com
* 微信:yushengjun644
*/
public class JdbcDemo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1.导入jar包
        // 2.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 3.获取执行者
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mayikt?serverTimezone=UTC",
                                                            "root", "root");
        // 4.获取执行者对象
        Statement statement = connection.createStatement();
        // 5.执行sql语句获取返回结果
        ResultSet resultSet = statement.executeQuery("select * from mayikt_users");
        // 6.对返回结果进行处理
        while (resultSet.next()) {
            System.out.println(resultSet.getInt("id") + "," + resultSet.getString("name") + "," +
                               resultSet.getString("pwd"));
        }
        // 7.释放资源
        connection.close();
        statement.close();
    }
}

启动该程序报错

Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

需要在jdbc连接后面加上该参数jdbc:mysql://127.0.0.1:3306/mayikt?serverTimezone=UTC

在Java中使用JDBC操作数据库，该数据库版本为8.0.15属于高版本(如果是低版本的话，通常是不会出现这些问题的)

import java.sql.*;

/**
 * @author 余胜军
 * @ClassName JdbcDemo01
 * @qq 644064779
 * @addres www.mayikt.com
 * 微信:yushengjun644
 */
public class JdbcDemo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1.导入jar包
        // 2.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 3.获取执行者
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mayikt?serverTimezone=UTC",
                "root", "root");
        // 4.获取执行者对象
        Statement statement = connection.createStatement();
        // 5.执行sql语句获取返回结果
        ResultSet resultSet = statement.executeQuery("select * from mayikt_users");
        // 6.对返回结果进行处理
        while (resultSet.next()) {
            System.out.println(resultSet.getInt("id") + "," + resultSet.getString("name") + "," +
                    resultSet.getString("pwd"));
        }
        // 7.释放资源
        connection.close();
        statement.close();
    }
}

常见问题

1.Exception in thread "main" java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

原因是没有引入mysql 驱动jar包

2. No suitable driver found for jdbc:mysql//127.0.0.1:3306/mayikt1?serverTimezone=UTC

原因jdbc地址填写是错误的

3. Access denied for user 'root1'@'localhost' (using password: YES)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.jav

mysql 连接账户和密码 错误

4. Table 'mayikt.mayikt_users11' doesn't exist 在我们的mayikt数据库

是没有mayikt_users11

jdbcAPI详解

DriverManager 驱动程序管理器是负责管理驱动程序的，驱动注册以后，会保存在DriverManager中的已注册列表中后续的处理就可以对这个列表进行操作.

注册驱动方式

1.DriverManager.registerDriver();

2.写代码实现

Class.forName("com.mysql.jdbc.Driver");

3.com.mysql.jdbc.Driver类中存在静态代码快

public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    //
    // Register ourselves with the DriverManager
    //
    static {
        try {
            java.sql.DriverManager.registerDriver(new Driver());
        } catch (SQLException E) {
            throw new RuntimeException("Can't register driver!");
        }
    }

4.开发者是不需要调用DriverManager.registerDriver();方法，因为我们在使用class.forName 会加载到我们的

com.mysql.jdbc.Driver 通过Driver静态代码快 注册我们的Driver驱动。

5.mysql5之后，在jar包中存在一个java.sql.Driver配置文件，文件指定加载com.mysql.cj.jdbc.Driver

通过SPI机制实现。

类加载器、spi 反射技术 javase进阶基础

6.获取连接Connection 连接对象

Connection connection = DriverManager.getConnection("数据库连接地址",
"用户名称", "用户密码");

参数：指定连接的路径 语法：jdbc://mysql://ip地址:端口号码/数据库名称

user:用户名称

pwd:用户的密码

ResultSet结果集对象

1.判断结果集是否有数据: boolean next();

1.1.有数据返回true 并将索引向下移动一行

1.2.没有数据返回false

2.获取结果集中的数据：xxx.getxx(列名称) 注意与 数据库数据类型需要对应

jdbc案例

需求讨论与分层架构

使用jdbc技术访问到mysql数据库 对 student学生数据做增删改查操作

1.初始化数据库

CREATE TABLE `mayikt_student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` tinyint DEFAULT '0',
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb3;

INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`) VALUES ('1', 'mayikt', '18', '武汉市');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`) VALUES ('2', 'meite', '23', '上海市');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`) VALUES ('3', '李思', '12', '孝感市');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`) VALUES ('4', '刘流', '27', '武汉市');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`) VALUES ('5', '王麻子', '12', '孝感市');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`) VALUES ('6', 'KKK', '11', '成都');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`) VALUES ('7', 'xiaokey', '22', '武汉市');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`) VALUES ('8', '999', '33', '孝感市');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`) VALUES ('9', 'wang', '55', '武汉市');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`) VALUES ('10', 'lisi', '38', '孝感市');

2.定义实体类层

/**
 * @author 余胜军
 * @ClassName Student
 * @qq 644064779
 * @addres www.mayikt.com
 * 微信:yushengjun644
 */
public class Student {
    private Long id;
    private String name;
    private Integer age;
    private String address;
}

数据库中的数据类型需要与db对应 实体类中的 基本数据类型 建议用包装类 默认是为null

分层架构

com.mayikt.entity---实体类----创建实体类与数据库表结构字段一一对应的

com.mayikt.dao----数据库访问层----db打交道

com.mayikt.serivce---业务逻辑层

com.mayikt.controller---控制层

如果在db数据类型是为varchar 对应 string

如果在db数据类型是为int对应 Integer

需求1：查询所有学生信息

需求2：根据id查询学生信息

需求3：新增学生信息 ----insert into

需求4：根据主键id修改学生信息

需求5：根据主键id删除学生信息 企业实际开发中 不会真正物理删除数据 而是隐藏update的形式。---javaweb开发。

1.导入mysql驱动jar包;

2.注册驱动 javase 反射机制Class.forName()

3.获取数据库连接

4.获取执行者对象

5.执行sql语句并获取返回结果

6.对结果进行处理

7.释放jdbc资源

executeUpdate----执行insert 、update、delete sql语句

返回影响行数

相关代码

实体类层

public class StudentEntity {
    /**
    * 学生对象
    * 在java中定义数据库实体类层
    * 不建议使用基本数据类型 使用包装类
    */
    /**
    * 学生的id
    */
    private Long id; // 默认值null
    /**
    * 学生姓名
    */
    private String name;
    /**
    * 学生年龄
    */
    private Integer age;
    /**
    * 学生的地址
    */
    private String address;
    
    public StudentEntity(Long id, String name, Integer age, String address) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.address = address;
    }
    public StudentEntity( String name, Integer age, String address) {
        this.name = name;
        this.age = age;
        this.address = address;
    }
    
    public Long getId() {
        return id;
    }
    
    public void setId(Long id) {
        this.id = id;
    }
    
    public String getName() {
        return name;
    }
    
    public void setName(String name) {
        this.name = name;
    }
    
    public Integer getAge() {
        return age;
    }
    
    public void setAge(Integer age) {
        this.age = age;
    }
    
    public String getAddress() {
        return address;
    }
    
    public void setAddress(String address) {
        this.address = address;
    }
    
    @Override
    public String toString() {
        return "StudentEntity{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", age=" + age +
            ", address='" + address + '\'' +
            '}';
    }
}

Dao层

package com.mayikt.dao;

import com.mayikt.entity.StudentEntity;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * @author 余胜军
 * @ClassName StudentDao
 * @qq 644064779
 * @addres www.mayikt.com
 * 微信:yushengjun644
 */
public class StudentDao {
    /**
     * 学生对象数据库访问层
     */

    /**
     * 查询所有的学生信息
     *
     * @return
     */
    public ArrayList<StudentEntity> allStudent() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //A.java连接mysql数据库查询所有数据
            //1.导入mysql驱动jar包;
            //2. 注册驱动 javase 反射机制Class.forName()
            Class.forName("com.mysql.cj.jdbc.Driver");
            //3. 获取数据库连接
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mayikt?serverTimezone=UTC", "root", "root");
            //4. 获取执行者对象
            statement = connection.createStatement();
            //5. 执行sql语句并获取返回结果
            resultSet = statement.executeQuery("select  * from mayikt_student");
            ArrayList<StudentEntity> studentEntities = new ArrayList<>();
            //6. 对结果进行处理
            while (resultSet.next()) { // 如果false结束该循环
                // 获取该行数据的第一列 id
                Long id = resultSet.getLong("id");
                // 获取该行数据的第二列 name
                String name = resultSet.getString("name");
                // 获取该行数据的第三列 age
                Integer age = resultSet.getInt("age");
                // 获取该行数据的第四列 address
                String address = resultSet.getString("address");
                // 将db中查询到数据封装成java学生对象
                StudentEntity studentEntity = new StudentEntity(id, name, age, address);
                // 将该对象存入到集合中
                studentEntities.add(studentEntity);
            }
            return studentEntities;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            //  7. 释放jdbc资源
            try {
                if (resultSet != null)
                    resultSet.close();
                if (statement != null)
                    statement.close();
                if (connection != null)
                    connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }


        }

    }

    /**
     * 根据学生id 查询学生信息 学生的id
     *
     * @return
     */
    public StudentEntity getByIdStudent(Long stuId) {
        /**
         * 判断用户是否传递学生id的值
         */
        if (stuId == null) {
            return null;
        }
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //A.java连接mysql数据库查询所有数据
            //1.导入mysql驱动jar包;
            //2. 注册驱动 javase 反射机制Class.forName()
            Class.forName("com.mysql.cj.jdbc.Driver");
            //3. 获取数据库连接
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mayikt?serverTimezone=UTC", "root", "root");
            //4. 获取执行者对象
            statement = connection.createStatement();
            //5. 执行sql语句并获取返回结果 自己拼接 查询sql语句
            resultSet = statement.executeQuery("select  * from mayikt_student where id=" + stuId);
            boolean result = resultSet.next(); // 查询不到数据 false
            // 判断如果查询不到数据 则不会取值
            if (!result) {
                return null;
            }
            //6. 对结果进行处理
            // 获取该行数据的第一列 id
            Long id = resultSet.getLong("id");
            // 获取该行数据的第二列 name
            String name = resultSet.getString("name");
            // 获取该行数据的第三列 age
            Integer age = resultSet.getInt("age");
            // 获取该行数据的第四列 address
            String address = resultSet.getString("address");
            // 将db中查询到数据封装成java学生对象
            StudentEntity studentEntity = new StudentEntity(id, name, age, address);
            return studentEntity;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            //  7. 释放jdbc资源
            try {
                if (resultSet != null)
                    resultSet.close();
                if (statement != null)
                    statement.close();
                if (connection != null)
                    connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }


        }

    }

    /**
     * 插入我们的学生
     *
     * @param stu
     * @return
     */
    public int insertStudent(StudentEntity stu) {
        Connection connection = null;
        Statement statement = null;
        try {
            //A.java连接mysql数据库查询所有数据
            //1.导入mysql驱动jar包;
            //2. 注册驱动 javase 反射机制Class.forName()
            Class.forName("com.mysql.cj.jdbc.Driver");
            //3. 获取数据库连接
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mayikt?serverTimezone=UTC", "root", "root");
            //4. 获取执行者对象
            statement = connection.createStatement();
            //5. 执行sql语句并获取返回结果 executeUpdate执行 insert sql语句
            String insertStudentSql = "INSERT INTO mayikt_student values(null,'" + stu.getName() + "'," + stu.getAge() + ",'" + stu.getAddress() + "')";
            System.out.println("insertStudentSql:" + insertStudentSql);
            // log输出
            int result = statement.executeUpdate(insertStudentSql);
            // 执行该sql语句 影响行数
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            //  7. 释放jdbc资源
            try {
                if (statement != null)
                    statement.close();
                if (connection != null)
                    connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }


        }
    }

    /**
     * 修改学生的信息
     *
     * @param stu
     * @return
     */
    public int updateStudent(StudentEntity stu) {
        Connection connection = null;
        Statement statement = null;
        try {
            //A.java连接mysql数据库查询所有数据
            //1.导入mysql驱动jar包;
            //2. 注册驱动 javase 反射机制Class.forName()
            Class.forName("com.mysql.cj.jdbc.Driver");
            //3. 获取数据库连接
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mayikt?serverTimezone=UTC", "root", "root");
            //4. 获取执行者对象
            statement = connection.createStatement();
            //5. 执行sql语句并获取返回结果 executeUpdate执行 update sql语句
            String updateStudentSql = "update mayikt_student  set name='" + stu.getName() + "' ,age=" + stu.getAge() + "," +
                    "address='" + stu.getAddress() + "' where id=" + stu.getId() + "";
            // log输出
            System.out.println("updateStudentSql:" + updateStudentSql);
            int result = statement.executeUpdate(updateStudentSql);
            // 执行该sql语句 影响行数
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            //  7. 释放jdbc资源
            try {
                if (statement != null)
                    statement.close();
                if (connection != null)
                    connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }


        }
    }

    /**
     * 根据主键id删除学生信息
     *
     * @param id
     * @return
     */
    public int delStudent(Long id) {
        // 判断id是否为null
        if (id == null) {
            return 0;
        }
        Connection connection = null;
        Statement statement = null;
        try {
            //A.java连接mysql数据库查询所有数据
            //1.导入mysql驱动jar包;
            //2. 注册驱动 javase 反射机制Class.forName()
            Class.forName("com.mysql.cj.jdbc.Driver");
            //3. 获取数据库连接
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mayikt?serverTimezone=UTC", "root", "root");
            //4. 获取执行者对象
            statement = connection.createStatement();
            //5. 执行sql语句并获取返回结果 executeUpdate执行 delete sql语句
            String delSQL = "delete from  mayikt_student where id=" + id;
            System.out.println("delSql:" + delSQL);
            // log输出
            int result = statement.executeUpdate(delSQL);
            // 执行该sql语句 影响行数
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            //  7. 释放jdbc资源
            try {
                if (statement != null)
                    statement.close();
                if (connection != null)
                    connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }


        }
    }

}

Service

package com.mayikt.serivce;

import com.mayikt.dao.StudentDao;
import com.mayikt.entity.StudentEntity;

import java.util.ArrayList;

/**
 * @author 余胜军
 * @ClassName StudentService
 * @qq 644064779
 * @addres www.mayikt.com
 * 微信:yushengjun644
 */
public class StudentService {

    /**
     * new 学生对象dao层
     */
    private StudentDao studentDao = new StudentDao();

    /**
     * 查询所有的学生信息
     *
     * @return
     */
    public ArrayList<StudentEntity> allStudent() {
        // 在通过业务逻辑层调用dao层代码
        ArrayList<StudentEntity> studententities = studentDao.allStudent();
        return studententities;
    }

    public StudentEntity getByIdStudent(Long stuId) {
        return studentDao.getByIdStudent(stuId);
    }

    public int insertStudent(StudentEntity stu) {
        return studentDao.insertStudent(stu);
    }

    public int updateStudent(StudentEntity stu) {
        return studentDao.updateStudent(stu);
    }

    public int delStudent(Long id) {
        return studentDao.delStudent(id);
    }

}

jdbc工具类封装

1.编写配置文件

在src目录下创建config.properties配置文件

driverClass=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mayikt?serverTimezone=UTC
user=root
password=root

2.编写jdbc工具 获取连接 释放连接

类名称.方法名称---

package com.mayikt.utils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author 余胜军
 * @ClassName MayiktJdbcUtils
 * @qq 644064779
 * @addres www.mayikt.com
 * 微信:yushengjun644
 */
public class MayiktJdbcUtils {
    /**
     * 1.需要将我们的构造方法私有化 ---工具类 不需要 new出来 是通过类名称.方法名称访问
     */
    private MayiktJdbcUtils() {

    }

    /**
     * 2.定义工具类 需要 声明 变量
     */
    private static String driverClass;
    private static String url;
    private static String user;
    private static String password;

    /**
     *3.使用静态代码快 来给我们声明好 jdbc变量赋值（读取config.properties）
     */
    static {
        try {
            // 1.读取config.properties  IO 路径 相对路径
            InputStream resourceAsStream = MayiktJdbcUtils.class.getClassLoader().
                    getResourceAsStream("config.properties");
            // 2.赋值给我们声明好的变量
            Properties properties = new Properties();
            properties.load(resourceAsStream);
            driverClass = properties.getProperty("driverClass");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            // 3.注册驱动类
            Class.forName(driverClass);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 4.封装连接方法
     */
    public static Connection getConnection() {
        try {
            Connection connection = DriverManager.getConnection(url, user, password);
            return connection;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 5.封装释放连接方法 (重载)
     */
    public static void closeConnection(ResultSet resultSet, Statement statement, Connection connection) {
        // 1.查询 释放连接 resultSet  statement connection
        try {
            if (resultSet != null)
                resultSet.close();
            if (statement != null)
                statement.close();
            if (connection != null)
                connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 2.增删改 释放连接 statement connection
    }

    /**
     * 增删改---释放jdbc资源
     *
     * @param statement
     * @param connection
     */
    public static void closeConnection(Statement statement, Connection connection) {
        // 1.查询 释放连接 resultSet  statement connection
        closeConnection(null, statement, connection);
    }


}

封装主菜单系统

package com.mayikt.test;

import com.mayikt.entity.StudentEntity;
import com.mayikt.serivce.StudentService;

import java.util.ArrayList;
import java.util.Scanner;

/**
* @author 余胜军
* @ClassName IndexTest
* @qq 644064779
* @addres www.mayikt.com
* 微信:yushengjun644
*/
public class IndexTest {
    private static StudentService studentService = new StudentService();
    
    /**
    * new对象存放 堆内存
    *
    * @param args
    */
    public static void main(String[] args) {
        mainMenu();
    }
    
    /**
    * 1.需要定义主菜单程序的入口
    */
    public static void mainMenu() {
        while (true) {
            // 1.提示语
            System.out.println("欢迎来到我们每特教育蚂蚁课堂学生管理系统");
            System.out.println("1.查询所有的学生信息");
            System.out.println("2.根据学生id查询学生信息");
            System.out.println("3.新增学生信息");
            System.out.println("4.根据学生id修改学生信息");
            System.out.println("5.根据学生id删除学生信息");
            System.out.println("请选择对应序号:");
            // 2.接受用户输入的序号 int
            Scanner scanner = new Scanner(System.in);
            int result = scanner.nextInt();
            // 3.在根据用户选择的序号 判断
            switch (result) {
                case 1:
                    showAllStudent();
                    break;//直接让我们while退出了
                case 2:
                    findByIdStudent();
                    break;
                case 3:
                    insertStudent();
                    break;
                case 4:
                    updateIdStudent();
                    break;
                case 5:
                    delIdStudent();
                    break;
            }
        }
    }
    
    /**
    * 查询所有学生信息
    */
    public static void showAllStudent() {
        System.out.println("查询到的所有学生信息");
        ArrayList<StudentEntity> studentEntities = studentService.allStudent();
        for (StudentEntity stu : studentEntities) {
            System.out.println(stu);
        }
    }
    
    /**
    * 根据学生id查询学生信息
    */
    public static void findByIdStudent() {
        System.out.println("请输入学生的id：");
        Scanner scanner = new Scanner(System.in);
        Long stuId = scanner.nextLong();
        // 根据用户输入的学生id查询学生信息
        // 根据用户输入的学生id查询学生信息 查询不到  查询得到
        StudentEntity student = studentService.getByIdStudent(stuId);
        if (student == null) {
            System.out.println("该学生id" + student + ",不存在的");
            return;// 后面代码不会继续执行
        }
        // 查询到了学生信息
        System.out.println("学生信息：" + student);
    }
    
    /**
    * 根据学生id删除学生信息
    */
    public static void delIdStudent() {
        System.out.println("请输入学生的id：");
        Scanner scanner = new Scanner(System.in);
        Long stuId = scanner.nextLong();
        int result = studentService.delStudent(stuId);
        if (result > 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }
    }
    
    /**
    * 插入我们的学生信息
    */
    public static void insertStudent() {
        Scanner scanner = new Scanner(System.in);
        //        System.out.println("请输入学生的id：");
        //        Long stuId = scanner.nextLong();
        System.out.println("请输入学生的名称：");
        String name = scanner.nextLine();
        System.out.println("请输入学生的年龄：");
        int age = scanner.nextInt();
        scanner.nextLine(); // 跳过
        System.out.println("请输入学生的地址：");
        String address = scanner.nextLine();
        StudentEntity studentEntity = new StudentEntity(name, age, address);
        int result = studentService.insertStudent(studentEntity);
        if (result > 0) {
            System.out.println("插入学生信息成功");
        } else {
            System.out.println("插入学生信息失败");
        }
    }
    
    public static void updateIdStudent() {
        // 需要先根据学生的id查询该学生信息 如果查询得到的情况下 才会修改学生信息
        // 查询不到的情况下 就不会修改学生信息
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入学生的id：");
        Long stuId = scanner.nextLong();
        // 根据学生id查询学生信息
        StudentEntity student = studentService.getByIdStudent(stuId);
        if (student == null) {
            System.out.println("没有查询该学生的id对应学生信息");
            return;
        }
        scanner.nextLine();
        System.out.println("请输入学生的名称：");
        String name = scanner.nextLine();
        System.out.println("请输入学生的年龄：");
        int age = scanner.nextInt();
        scanner.nextLine(); // 跳过
        System.out.println("请输入学生的地址：");
        String address = scanner.nextLine();
        // 封装接受的参数 变成学生对象
        StudentEntity studentEntity = new StudentEntity(stuId, name, age, address);
        int result = studentService.updateStudent(studentEntity);
        if (result > 0) {
            System.out.println("修改成功");
        } else {
            System.out.println("修改失败");
        }
    }
}

Exception in thread "main" java.util.InputMismatchException

at java.util.Scanner.throwFor(Scanner.java:864)

at java.util.Scanner.next(Scanner.java:1485)

at java.util.Scanner.nextInt(Scanner.java:2117)

at java.util.Scanner.nextInt(Scanner.java:2076)

at com.mayikt.test.IndexTest.mainMenu(IndexTest.java:43)

at com.mayikt.test.IndexTest.main(IndexTest.java:25)

in.nextLine();不能放在in.nextInt();代码段后面

他不是跳过你了，而是他已经有内容了，内容就是‘\n’。因为nextInt();接收一个整型字符,不会读取\n，nextline();读入一行文本，会读入"\n"字符

解决办法in.nextInt() 中间(in.nextLine();) in.nextLine();

jdbc实现用户登录与注册

CREATE TABLE `mayikt_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `pwd` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3;

package com.mayikt.entity;

/**
 * @author 余胜军
 * @ClassName UserEntity
 * @qq 644064779
 * @addres www.mayikt.com
 * 微信:yushengjun644
 */
public class UserEntity {
    /**
     * 用户id
     */
    private Long id;
    /**
     * 用户手机号码
     */
    private String phone;
    /**
     * 用户的密码
     */
    private String pwd;

    public UserEntity(Long id, String phone, String pwd) {
        this.id = id;
        this.phone = phone;
        this.pwd = pwd;
    }

    public UserEntity(String phone, String pwd) {
        this.phone = phone;
        this.pwd = pwd;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }
}

package com.mayikt.dao;

import com.mayikt.entity.StudentEntity;
import com.mayikt.entity.UserEntity;
import com.mayikt.utils.MayiktJdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

/**
* @author 余胜军
* @ClassName UserDao
* @qq 644064779
* @addres www.mayikt.com
* 微信:yushengjun644
*/
public class UserDao {
    /**
    * 用户的注册
    * 思考点？如果用户一直使用相同的手机号码注册？
    * 该手机号码存在 不需要重复注册？给手机号码----在数据库中不存在
    */
    public int registerUser(UserEntity userEntity) {
        Connection connection = null;
        Statement statement = null;
        try {
            //A.java连接mysql数据库查询所有数据
            //1.导入mysql驱动jar包;
            //2. 注册驱动 javase 反射机制Class.forName()
            connection = MayiktJdbcUtils.getConnection();
            //4. 获取执行者对象
            statement = connection.createStatement();
            //5. 执行sql语句并获取返回结果 executeUpdate执行 insert sql语句
            String insertUserSql = "INSERT INTO `mayikt`.`mayikt_users` (`id`, `phone`, `pwd`) VALUES (null, '" + userEntity.getPhone() + "', '" + userEntity.getPwd() + "');";
            System.out.println("insertStudentSql:" + insertUserSql);
            // log输出
            int result = statement.executeUpdate(insertUserSql);
            // 执行该sql语句 影响行数
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            //  7. 释放jdbc资源
            MayiktJdbcUtils.closeConnection(statement, connection);
        }
    }
    
    /**
    * 根据手机号码查询用户的信息
    *
    * @param phone
    * @return
    */
    public UserEntity getByPhoneUser(String phone) {
        // 判断用户输入的手机号码是否是为null(正则表达式)
        //phone="";
        if (phone == null || phone.length() == 0) {
            return null;
        }
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //A.java连接mysql数据库查询所有数据
            //1.导入mysql驱动jar包;
            //2. 注册驱动 javase 反射机制Class.forName()
            connection = MayiktJdbcUtils.getConnection();
            //4. 获取执行者对象
            statement = connection.createStatement();
            //5. 执行sql语句并获取返回结果 自己拼接 查询sql语句
            String getByPhoneUserSql = "select * from mayikt_users where phone='" + phone + "'";
            System.out.println(getByPhoneUserSql);
            resultSet = statement.executeQuery(getByPhoneUserSql);
            boolean result = resultSet.next(); // 查询不到数据 false
            // 判断如果查询不到数据 则不会取值
            if (!result) {
                return null;
            }
            //6. 对结果进行处理
            // 获取该行数据的第一列 id
            Long dbId = resultSet.getLong("id");
            // 获取该行数据的第二列 phone
            String dbPhone = resultSet.getString("phone");
            // 获取该行数据的第三列 pwd
            String dbPwd = resultSet.getString("pwd");
            // 将db中查询到数据封装成user对象
            return new UserEntity(dbId, dbPhone, dbPwd);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            //  7. 释放jdbc资源
            MayiktJdbcUtils.closeConnection(resultSet, statement, connection);
            
        }
    }
    
    /**
    * 用户登录的方法
    *
    * @return
    */
    public UserEntity login(UserEntity userEntity) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //A.java连接mysql数据库查询所有数据
            //1.导入mysql驱动jar包;
            //2. 注册驱动 javase 反射机制Class.forName()
            connection = MayiktJdbcUtils.getConnection();
            //4. 获取执行者对象
            statement = connection.createStatement();
            //5. 执行sql语句并获取返回结果 自己拼接 查询sql语句
            String loginSql = "select * from mayikt_users where phone='" + userEntity.getPhone() + "' and pwd='" + userEntity.getPwd() + "';";
            System.out.println(loginSql);
            resultSet = statement.executeQuery(loginSql);
            boolean result = resultSet.next(); // 查询不到数据 false
            // 判断如果查询不到数据 则不会取值
            if (!result) {
                return null;
            }
            //6. 对结果进行处理
            // 获取该行数据的第一列 id
            Long dbId = resultSet.getLong("id");
            // 获取该行数据的第二列 phone
            String dbPhone = resultSet.getString("phone");
            // 获取该行数据的第三列 pwd
            String dbPwd = resultSet.getString("pwd");
            // 将db中查询到数据封装成user对象
            return new UserEntity(dbId, dbPhone, dbPwd);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            //  7. 释放jdbc资源
            MayiktJdbcUtils.closeConnection(resultSet, statement, connection);
            
        }
    }
    
}

package com.mayikt.test;

import com.mayikt.entity.UserEntity;
import com.mayikt.serivce.UserSerivce;

import java.util.Scanner;

/**
 * @author 余胜军
 * @ClassName UserTest
 * @qq 644064779
 * @addres www.mayikt.com
 * 微信:yushengjun644
 */
public class UserTest {
    private UserSerivce userSerivce = new UserSerivce();

    /**
     * 该登录代码 bug ----sql注入破解登录  手机号码和密码输入错误的 但是可以登录
     *
     * @param args
     */
    public static void main(String[] args) {
        UserTest userTest = new UserTest();
        userTest.index();
    }

    public void index() {
        Scanner scanner = new Scanner(System.in);
        System.out.println("输入数字1：用户注册");
        System.out.println("输入数字2：用户登录");
        int number = scanner.nextInt();
        switch (number) {
            case 1:
                registerUser();
            case 2:
                login();
        }
    }

    public void registerUser() {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户的手机号码");
        String phone = scanner.nextLine();
        System.out.println("请输入用户的密码");
        String pwd = scanner.nextLine();
        int result = userSerivce.registerUser(new UserEntity(phone, pwd));
        if (result > 0) {
            // 用户注册成功
            System.out.println("用户注册成功");
        } else {
            System.out.println("用户注册失败啦");
        }
    }

    public void login() {
        //给用户输入手机号码或者是密码错误机会 三次 直接退出程序
        for (int i = 1; i <= 3; i++) {
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入用户的手机号码");
            String phone = scanner.nextLine();
            System.out.println("请输入用户的密码");
            String pwd = scanner.nextLine();
            // 调用 登录业务逻辑方法
            UserEntity dbUserEntity = userSerivce.login(new UserEntity(phone, pwd));
            if (dbUserEntity != null) {
                System.out.println("登录成功");
                return;//退出循环
            } else {
                System.out.println("用户输入的手机号码或者密码不正确! 错误的次数:" + i);
            }
        }

    }
}

什么是JDBC SQL注入漏洞

什么是SQL注入攻击：

就是利用SQL语句的漏洞实现对系统攻击

底层原理就是 通过传递参数(or 1=1 )拼接的SQL语句 导致其成立可以查询到数据。

登录SQL语句：

select * from mayikt_users where phone='18140668751' and pwd='12';

但是黑客传递参数：

黑客传递的参数 ' or 1='1

select * from mayikt_users where phone='' and pwd='' or 1='1';

如果我们使用sql语句拼接的情况下 很容易导致 被黑客sql注入

如何解决SQL注入漏洞

解决办法：

使用PreparedStatemnet（预编译执行者对象）

在sql语句执行之前，将sql语句进行提前编译，明确SQL语句的格式后，传递的参数 就是参数 不会拼接sql语句。

用？占位符赋值的方式 setxxx(参数1，参数2)

xxx: 数据类型

参数1：？的位置从编号1开始

参数2：？的实际参数

String loginSql = "select * from mayikt_users where phone=? and pwd=?;";
statement = connection.prepareStatement(loginSql);
statement.setString(1, userEntity.getPhone());
statement.setString(2, userEntity.getPwd());

jdbc事务管理器

mysql中的事务

事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

1.原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

2.一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的数据必须完全符合所有的预设规则，这包含数据的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

3.隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

4.持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

开启事务-----

{

sql写的操作

update、insert、delete

}

提交事务或者回滚事务

提交事务----事务里面做的写操作 可以查询到 写完之后的数据

回滚事务----事务里面做的写操作 直接回滚了 反悔了 查询不到

如果开启了事务，但是没有提交或者回滚事务 我们是查询不到未提交的数据。

回滚事务----查询不到数据

1.直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交

SET AUTOCOMMIT=1 开启自动提交

2.用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认

演示：

select * from mayikt_users;
SET AUTOCOMMIT=0;
begin;
insert into mayikt_users values(
null,'15527339852','124'
)
commit;

注意：我们在使用手动提交事务，如果长期不提交数据也不回滚数据 容易引发行锁问题 导致该行数据一直被锁住，无法被其他线程修改。

事务如果不提交、不回滚，会发生哪些问题？

我们在使用手动提交事务，如果不提交数据也不回滚数据 容易引发行锁问题 导致该行数据一直被锁住，无法被其他线程修改。

BEGIN;
UPDATE `mayikt`.`mayikt_users` SET `id`='1',
 `phone`='15523733967', `pwd`='2221' WHERE (`id`='1');

select * from information_schema.innodb_trx

kill 17; 手动释放行锁

kill 19;手动释放行锁

在mysql InnoDB存储引擎中 多个线程如果同时修改同一行数据 最终只会有一个线程修改成功。

InnoDB存储引擎---行锁

jdbc中的事务管理器

同一事务中所有的操作，都在使用同一个Connection对象。

①JDBC中的事务

Connection的三个方法与事务有关：

setAutoCommit（boolean）:设置是否为自动提交事务，如果true（默认值为true）表示自动提交，也就是每条执行的SQL语句都是一个单独的事务，如果设置为false，那么相当于开启了事务了；con.setAutoCommit(false) 表示开启事务。

commit（）：提交结束事务。

rollback（）：回滚结束事务。

jdbc整合mysql中事务呢？手动事务呢？

事务呢？

增加、删除、修改数据---写操作 加上事务

查询操作---不需要加上事务的？

开启事务

提交事务

回滚事务

MySQL多表联合查询操作

jdbc数据库连接池

什么是数据库连接池

1.我们在JDBC编程中，每次创建和断开Connection对象都会消耗一定的时间和IO资源，如果需要频繁的与数据库打交道，该过程效率非常低。因为在Java程序与数据库之间建立连接时，数据库端要验证用户名和密码，并且要为这个连接分配资源,Java程序则要把代表连接的java.sql.Connection对象等加载到内存中,所以建立数据库连接的开销很大。

2.为了避免频繁的创建数据库连接，与时我们可以通过数据库连接池负责分配、管理和释放数据库连接,它允许应用程序重复使用现有的数据库连接，而不是重新建立。

3.数据库连接池大致实现原理：

数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，当应用程序访问数据库时并不是直接创建Connection,而是向连接池“申请”一个Connection。如果连接池中有空闲的Connection,则将其返回，否则创建新的Connection。使用完毕后,连接池会将该Connection回收，并交付其他的线程复用使用，以减少创建和断开数据库连接的次数，提高数据库的访问效率。

整合c3p0数据库连接池

快速入门

1.导入依赖Jar包

c3p0-0.9.5.2

mchange-commons-java-0.2.12.jar

mysql-connector-java-8.0.13.jar

jar包相关下载：

链接：有道云笔记

2.使用c3p0连接池

  ComboPooledDataSource pool = new ComboPooledDataSource();// 创建c3p0数据库连接池
  pool.setUser("root");// 用户名称
  pool.setPassword("root");// 用户密码
  pool.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mayikt?zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai");// MySQL数据库连接url
  pool.setDriverClass("com.mysql.jdbc.Driver"); // 加载驱动

   public UserEntity getUserEntity(Long id) {
        ResultSet resultSet = null;
        Connection connection = null;
        ComboPooledDataSource pool = new ComboPooledDataSource();// 创建c3p0数据库连接池
        try {
            pool.setUser("root");// 用户名称
            pool.setPassword("root");// 用户密码
            pool.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mayikt?zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai");// MySQL数据库连接url
            pool.setDriverClass("com.mysql.jdbc.Driver"); // 加载驱动
            //2.获取连接对象
            connection = pool.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement("select * from  mayikt_users where id=?");
            preparedStatement.setLong(1, id);
            resultSet = preparedStatement.executeQuery();
            if (!resultSet.next()) {
                return null;
            }
            // 获取该行数据的第一列 id
            Long dbId = resultSet.getLong("id");
            // 获取该行数据的第二列 phone
            String dbPhone = resultSet.getString("phone");
            // 获取该行数据的第三列 pwd
            String dbPwd = resultSet.getString("pwd");
            // 将db中查询到数据封装成user对象
            return new UserEntity(dbId, dbPhone, dbPwd);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

改成配置文件形式

1.在src目录创建c3p0.properties 或者c3p0-config.xml

2.特别的名称要求，程序会自动去找寻这个配置文件

<c3p0-config>
    <!-- 使用默认的配置读取连接池对象 -->
    <default-config>
        <!--  连接参数 -->
        <!--需要修改自己数据库路径、用户账号、密码-->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/mayikt?serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">root</property>
    </default-config>

</c3p0-config>

在jdbc自己代码配置jdbc连接信息 注释掉。

自动就可以找到c3p0-config.xml

我们也可以在c3p0-config.xml 配置多个jdbc数据库连接信息

代码指定使用数据库连接配置信息

ComboPooledDataSource pool = new ComboPooledDataSource("mayikt-otherc3p0");

不指定就使用默认的。

<c3p0-config>
    <!-- 使用默认的配置读取连接池对象 -->
    <default-config>
        <!--  连接参数 -->
        <!--需要修改自己数据库路径、用户账号、密码-->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/mayikt?serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">root</property>
    </default-config>
    <named-config name="mayikt-otherc3p0">
        <!--  连接参数 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/meite?serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">root</property>
    </named-config>

</c3p0-config>

核心配置文件说明

1.连接池核心参数

    <!--初始化申请的连接数量-->
    <property name="initialPoolSize">5</property>
    <!--最大的连接数量-->
    <property name="maxPoolSize">10</property>
    <!--超时时间(单位毫秒)-->
    <property name="checkoutTimeout">3000</property>

2.基本原理是在内部对象池中维护一定数量的数据库连接，并对外暴露数据库连接获取和返回方法。用户可通过getConnection()方法获取连接，使用完毕后再通过Connection.close()方法将连接返回，注意此时连接并没有关闭，而是由连接池管理器回收，并为下一次使用做好准备。

数据库连接池的好处：

1. 节约资源

2. 用户访问高效

        ComboPooledDataSource pool = new ComboPooledDataSource();
        for (int i = 1; i <=20; i++) {
            Connection connection = pool.getConnection();
            System.out.println(i + "," + connection);
            if(i==10){
                // 归还
                connection.close();
            }

        }

线程池 底层原理与数据库连接池基本是相同的

整合druid数据库连接池

Druid（德鲁伊）：数据库连接池实现技术，由阿里巴巴提供的， 与C3P0数据库连接池 底层实现原理一样。

1.需要导入druid-1.2.8.jar 依赖jar包

依赖jar包下载：有道云笔记

2.定义配置文件

是properties形式的

可以叫任意名称，可以放在任意目录下。程序不会自动寻找，因此在使用时需要手动加载相应的配置文件。

druid.properties

# 加载数据库驱动
driverClassName=com.mysql.jdbc.Driver
# 连接数据库的url,db1表示数据库名,useSSL=false表示不使用SSL规范
url=jdbc:mysql://127.0.0.1:3306/mayikt?useSSL=false&characterEncoding=UTF-8&serverTimezone=UTC
# 用户登录数据库的账号和密码
username=root
password=root
# 初始化连接数量
initialSize=5
# 最大连接数量
maxActive=10
# 最大等待时间
maxWait=3000

3.加载配置文件。使用Properties集合

4.获取数据库连接池对象：通过工厂来获取 DruidDataSourceFactory

5.获取连接：getConnection()

        //读取druid.properties
        Properties properties = new Properties();
        InputStream resourceAsStream =
                Test03.class.getClassLoader().getResourceAsStream("druid.properties");
        properties.load(resourceAsStream);
        // 创建druid.properties数据库连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        // 4.获取预编译执行者对象 防止 sql注入的问题
        PreparedStatement preparedStatement =
                connection.prepareStatement("select * from mayikt_users  where id=? ");
        // 5.设置参数
        preparedStatement.setLong(1, 2);
        // 6.执行sql语句
        ResultSet resultSet = preparedStatement.executeQuery();
        if (!resultSet.next()) {
            return;
        }
        //6. 对结果进行处理
        // 获取该行数据的第一列 id
        Long dbId = resultSet.getLong("id");
        // 获取该行数据的第二列 phone
        String dbPhone = resultSet.getString("phone");
        // 获取该行数据的第三列 pwd
        String dbPwd = resultSet.getString("pwd");
        // 将db中查询到数据封装成user对象
        UserEntity userEntity = new UserEntity(dbId, dbPhone, dbPwd);
        System.out.println(userEntity);
        resultSet.close();
        connection.close();

druid数据库连接池工具类

public class DataSourceUtils {
    /**
     * DataSource 数据库连接池对象
     */
    private static DataSource dataSource;

    /**
     * 私有化构造方法
     */
    private DataSourceUtils() {
    }

    /**
     * 使用静态代码块加载配置文件
     */
    static {
        try {
            // 读取配置文件
            Properties properties = new Properties();
            InputStream resourceAsStream = DataSourceUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            properties.load(resourceAsStream);
            // 获取数据库连接池
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 封装获取连接方法
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 封装关闭连接方法
     */
    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

常见问题

问题1

  connection = JdbcUtils.getConnection();//创建数据库连接
            String findUserByPhoneSQL = "SELECT * FROM mayikt_users where phone = ?";//明确SQL语句格式
            statement = connection.prepareStatement(findUserByPhoneSQL); //不要用拼接MySQL, 而是用调用预编译
            statement.setString(1, userPhone); //设置第一个参数
            System.out.println(findUserByPhoneSQL);
            resultSet = statement.executeQuery(findUserByPhoneSQL); //执行SQL语句,获得结果 --resultSet

报错：java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax;

check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

原因：将ppstm.executeQuery(sql);中的sql去掉，改成ppstm.executeQuery();

问题2

closing inbound before receiving peer s close_notify

jdbc地址连接后面加上 &useSSL=false 就好了

问题3

在执行 预编译sql时，报错

错误案例：

 Connection connection = null;
        PreparedStatement statement = null;
        try {
            String SQL = "insert into mayikt_student values(null,?,?,?)";
            connection = JdbcUtils.getConnection();
            statement = connection.prepareStatement(SQL);
            statement.setString(1, name);
            statement.setInt(2, age);
            statement.setString(3, address);
            System.out.println(SQL);
            int i = statement.executeUpdate(sql);
            if (i == 1) {
                System.out.println("插入成功");
            } else if (i == 0) {
                System.out.println("插入失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeConnection(statement, connection);
        }

报错内容：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?,?,?)'

改成

int i = statement.executeUpdate(); 去掉在executeUpdate执行 sql语句