一 、MySQL数据库示例
CREATE TABLE t_user (
uid INT AUTO_INCREMENT COMMENT '用户id',
username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
password CHAR(32) NOT NULL COMMENT '密码',
salt CHAR(36) COMMENT '盐值',
gender INT COMMENT '性别，0-女，1-男',
phone VARCHAR(20) COMMENT '电话',
email VARCHAR(50) COMMENT '邮箱',
avatar VARCHAR(100) COMMENT '头像',
is_delete INT COMMENT '是否标记为删除，0-未删除，1-已删除',
created_user VARCHAR(20) COMMENT '创建人',
created_time DATETIME COMMENT '创建时间',
modified_user VARCHAR(20) COMMENT '最后修改执行人',
modified_time DATETIME COMMENT '最后修改时间',
PRIMARY KEY (uid)
) DEFAULT CHARSET=UTF8;二、约束条件
1、not null：非空限制
2、default：默认赋值
create table t(
id int,sex enum('male','female') not null default 'male');枚举类型和集合类型
枚举类型：enum('男','女')
集合类型：set('a','b','c','d')3、unique：单列唯一、联合唯一
create table t(
id int unique,
ip char(15),
port int,
unique(ip,port));4、primary key
主键的作用：组织表的结构，提高查询速率，不仅仅是不为空且唯一。
一张表必须要有一个主键，你没指定会找一个不为空且唯一的字段为主键，如果再没有会自动设置一个隐藏的主键，隐藏的没有意义，所以你一定要指定。
单列主键：一般为每一张表设置一个ID字段为主键。
复合主键：
create table t(
ip char(15),
port int
primary key(ip,port));5、auto_increment
自增：步长默认为1，起始偏移量为1。
create table t(
id primary key auto_increment,
name char(5));清空表：
delete from t; 只清除记录，一般与where连用，删除指定的记录。
truncate t; 清除记录，并恢复起始偏移量为16、foreign key：外键
foreign key
create table t(
id int primary key,
name char(10),
dep_id int,
foreign key(dep_id) references dep(id)
on delete cascade # 联合删除
on update cascade # 联合更新 );7、事务控制
start transaction/begin;（开启事务）
rollback;(撤销事务） commit(提交事务);三、两张表之间的关系
一对一：该表设置唯一外键，对应另外一张表主键
多对一：在多的一张表建立外键，对应另外一张表主键
多对多：创建第三张表保存两个表之间的对应关系
四、单表查询
1、简单查询
select * from t;
2、数据去重
select distinct * from t;
3、将字段直接进行四则运算
select name, salary*12 as '年薪' from t;
4、定义显示格式
select concat('姓名：',name, '性别：',sex) as info from t;
select concat_ws(':', name, sex, age) from t;
5、where 约束
比较运算符 < > =
between and
in(1,2,3)
like %任意多个字符； _任意一个字符
逻辑运算符 not and or
6、分组
group by 是在 where 之后运行
设置严格分组模式： set global sql_mode="ONLY_FULL_GROUP_BY";
聚合函数：count() sum() avg() max() min()
强调：
不要用unique的字段作为分组字段，没有意义；
如果不分组，那么整体就是一组；
分组之后，只能取分组的字段，以及每个组聚合的结果
group_concat 列举每个组的所有记录
select group_concat(name) from t group by post;
7、having 过滤
在分组之后进行，可以聚合函数作为条件，where 不可以用聚合函数作为条件。
8、order by 排序
在 distinct 之后运行
order by asc 升序，默认的
order by desc 降序
order by age asc id desc 先按照年龄升序，再按照id降序。
9、limit 限制条数
limit 3 从头开始，数三条记录
limit 10,10 从第10条开始，取10条记录
10、正则匹配
regexp '^shi' 匹配以'shi'开头的
总结：单表查询的语法以及执行顺序。
语法顺序：
select distinct 字段一，字段二，字段三 from 库.表名
where 条件
group by 分组条件
having 过滤
order by 排序字段
limit n;
执行顺序：
from 找表
where 条件过滤
group by 分组
having 过滤
distinct 去重
order by 排序
limit 分页
select 打印五、多表查询
在笛卡尔积的基础上去筛选有关系的记录
1、内链接
select * from
tb11 inner join tb12
on tb11.part_id=tb12.department.id;
2、左链接
select * from
tb11 left join tb12
on tb11.part_id=tb12.department.id;
3、右链接
select * from
tb11 right join tb12
on tb11.part_id=tb12.department.id;
4、全外链接
select * from
tb11 left join tb12
on tb11.part_id=tb12.department.id
union
select * from
tb11 right join tb12
on tb11.part_id=tb12.department.id;
5、子查询
一张表的查询结果当作另一张表的条件。
六、完整 select 语句关键字的语法顺序和执行顺序
语法顺序：
select distinct 字段一，字段二，字段三
from t1 <join type> t2
on <join condition>
where 条件
group by 分组条件
having 过滤
order by 排序字段
limit n;
执行顺序：
from t1
on <join condition>
<join type> t2
where 条件
group by 分组条件
having 过滤
select
distinct
order by 排序字段
limit n;
*****************数据库相关控制命令*****************
linux系统：右键终端： mysql -uroot -p 回车输出密码再回车
windows:进入cmd(输入mysql -uroot -proot)
Status：查看数据库的各种格式、状态
？function或者help function（查看mysql所有函数）
？ String functions（例举String的所有函数）
？ Mod(查看mod函数用法）
数据库中右键标记,取中数据,随便点空白处,可以复制数据库中的选中数据;
Set names gbk;(只对打开的数据库有效,重开需重新设置, 保证插入数据不乱码)
source d:/jtsys.sql（导入数据库，例如放在d盘根目录下）
show databases:查看所有库
create database db2 character set gbk:创建指定字符集库
show create database db1:查看db1库详情
use db1:使用数据库db1
drop database db1:删db1库跑路
*******************表相关,先use库********************
show tables:查看所有表
create table t1（字段名 字段类型...）engine=myisam/innodb charset=utf8/gbk:创建表并指定引擎和字符集
引擎：1. innodb：默认，支持事物、外键等高级操作
2. myisam：只支持基础的增删改查操作，不支持事物、外键等高级操作
show create table t1:查看t1表详情
desc t1:查看表字段
insert into 表名（字段1，字段2）values（值1，值2）插入表数据
drop table t1:删除表
delete from 表(where):删除表中所有数据(where xxx)
rename table t1 to t2:修改表名
alter table t1 engine=myisam/innodb charset=utf8/gbk:修改字符集/引擎
alter table t1 add 字段名 字段类型:添加表字段
最前面格式：alter table 表名 add 字段名 字段类型 first;
某字段后面：alter table 表名 add 字段名 字段类型 after 字段名;
alter table t1 drop 字段名:删除表字段
update 表名 set 字段名=xxx where xxx: 修改表字段的值
alter table t1 change id pid int:改字段名/类型
alter table 表名 modify 字段名 新类型 first /after xxx: 修改字段类型/位置
*******************查询相关select********************
select ... from 表名 where（普通字段条件） ... group by ... having（聚合函数条件) ...order by ...limit...
外连接:查询一张表的全部数据和另外一张表的交集数据 select * from A left/right join B on A.x=B.x where A.age<20;
等值连接：select * from A,B where A.x=B.x and A.age<20;
内连接： select * from dept d inner join emp e on d.id=e.dept_id;
自连接: select e1.name,e1.topid, e2.id, e2.name from emp e1, emp e2 where e1.topid = e2.id;
第二次查询的结果作为一张临时表和第一次查询进行关联查询:每个部门薪资最高的员工信息，显示部门编号、员工姓名、薪资 select e1.dept_id,e2.maxsal,e1.name from emp e1,(select dept_id,max(sal) maxsal from emp group by dept_id) e2 where e1.dept_id=e2.dept_id and e1.sal=e2.maxsal;
子查询(嵌套查询): select * from emp where sal=(select max(sal) from emp);
取消sql执行:SQL语句后+ \c
`的作用：用于修饰表名和字段名 可以省略:
create table `4`(`id` int ,`name` varchar(10));
冗余: 由于表设计不够合理导致出现的大量重复数据成为冗余。通过合理分析拆分表的形式解决冗余问题
模糊查询like: %代表多个未知字符 _代表单个未知字符;
and 和 or:同时满足条件用and,任意满足其一用or
去重distinck:把关键字distinct写在需要去重的字段前面
select distinct job from emp;
\G可以把信息整齐展示:
select*from t_item where price=23 \G;
order by 字段名 asc（升序）/desc（降序）:
is null / /is not null:select ename,sal from emp where mgr is null
limit (跳过的条数，请求的条数):select*from emp where deptno=30 order by sal desc limit 0,1;
group by 字段名:
having 后面写聚合函数的条件，having需要结合group by 使用
创建视图格式： create view 视图名 as (子查询);
主键自增约束primary key auto_increment非空约束 not null 唯一约束 uniqu costraint 约束名 foreign key(外键字段名) references 被依赖的表名(被依赖的字段名字),创建表时声明foreign key(dept_id) references dept(id))
创建索引的格式：create index 索引名 on 表名（字段名（？长度））
Avg(求平均)count(计数)max(求最大)min(求最小)sum(求和)
ifnull(列, 值)函数: 判断指定的列是否包含null值, 如果有null值, 用第二个值替换null值(null和任何值作运算都是null)
select md5(string); md5加密参数String
As在查询中可以起别名,也可以省略(where子句中不能使用列别名,因为先执行from,再执行where,最后执行的才是select)
SQL语句的书写顺序：
select...from...where...group by...
having... order by... limit...
SQL语句的执行顺序：
from... -- 确定要查询的是哪张表 (定义表别名)
where... -- 从整张表的数据中进行筛选过滤
select... -- 确定要显示哪些列 (定义列别名)
group by... -- 根据指定的列进行分组
having...
order by... -- 根据指定的列进行排序
Limit m,n ...-- 忽略m条,一页显示n条
.bat后缀文件是批量处理文件,可以设置快捷启动数据库
数据库概述
DBMS：DataBase数据库 Management管理 System系统
数据库管理系统，负责对数据进行增删该查操作，常见的DBMS有：MySQL、Oracle、DB2、SQLServer、Sqlite等什么是数据库：
数据库：英文为Database，简称DB
数据库是按照数据结构来组织、存储和管理数据的仓库
简而言之，数据库就是存储数据的仓库。数据库的分类：
数据库根据存储采用的数据结构的不同可以分为许多种，其中包含早期的层次式数据库、网络式数据库。
目前占市场主流的是关系型数据库。当然还有非关系(NoSQL)型数据库（键值对数据库，例如：MongoDB、Redis）等其他类型的数据库什么是关系型数据库？
底层是以二维表的及其之间的关系所组成的数据库，即是关系型数据库。例如：
1.关系型数据库：经过数学理论验证可以保存现实生活中的任何关系，以表为单位保存数据
2.非关系型数据库：一般用于解决特殊的场景，以键值队的形式在内存中保存数据 如：数据缓存redis之类
常见的关系型数据库:
SQL Server：微软提供（收费、Java中使用不多）
Oracle：甲骨文公司（收费、功能强大、性能优异，Java中使用者很多）
DB2 ：IBM（收费、中型/大型、银行/电信等企业）
MySQL：瑞典MySQL AB（免费开源、小型、性能也不差、适用于中小型项目、可集群）
SQLite ：迷你数据库，嵌入式设备中(轻量级,只有几十k)
Sql：Structured结构化Query查询Language语言，用于程序员和数据库软件交流的语言.
名称解释：
1、数据库服务器
数据库服务器其实就是一个软件，比如我们安装的mysql软件（或者mariadb软件）
mysql服务器软件需要安装在服务器硬件上（就是一台计算机）才可以让外界来访问
2、数据库
在mysql服务器中，可以创建很多的数据库（database）
通常情况下，一个web站点对应一个数据库
3、数据表
在数据库（database）中，可以创建很多张表（table）
通常情况下，一张表用于保存一类数据，例如网站中的所有用户信息会保存在一张表中，所有商品信息会保存在另一张表中。
4、表记录
在数据表（table）中，可以插入很多条表记录
数据表往往保存一类数据，对应java中的一个类。
而一条表记录往往对应java中的一个具体的实例
什么是SQL语言?
Structured Query Language：结构化的查询语言
SQL是操作所有关系型数据库的通用的语言
SQL语言的分类：
a) DDL – （不支持事物 ）Date Definition Language 数据定义语言，指CREATE、ALTER、DROP、truncate等操作（ 即创建、删除、修改数据库和数据表 ）
b) DML – Data Maninpulation Language 数据操作语言包括：（支持事务）INSERT、UPDATE、DELETE等操作（ 即数据表中数据的增、删、改操作 ）
c) DQL – 数据查询语言（只包含SELECT操作，即数据表中数据的查询操作）
D).TCL Transaction Control Language 事物控制语言
包括：begin、commit、rollback、savepoint xxx、rollback to xxx；
E).DCL：Date Control Language 数据控制语言:分配用户权限的相关的SQLMySQL数据类型及字段约束
数值类型
字符串类型
1、char(n) 定长字符串，最长255个字符。n表示字符数，例如：
所谓的定长，是当插入的值长度小于指定的长度10（上面指定的）时, 剩余的空间会用空格填充。（这样会浪费空间）
2、varchar(n) 变长字符串，最长不超过 65535个字节，n表示字符数，一般超过255个字节，会使用text类型，例如：
所谓的不定长，是当插入的值长度小于指定的长度10（上面指定的）时, 剩余的空间可以留给别的数据使用。（节省空间）
3、大文本（长文本）类型
最长65535个字节，一般超过255个字符列的会使用text。
text也分多种，其中bigtext存储数据的长度约为4GB。
日期类型
1、date：年月日
2、time：时分秒
3、datetime：年月日 时分秒 默认值为null
4、timestamp：时间戳(实际存储的是距1970年1月1日 08：00：00毫秒数)，与datetime存储日期格式相同。默认值是当前系统时间
timestamp最大表示2038-1-19，而datetime范围是1000~9999
timestamp在插入数、修改数据时，可以自动更新成系统当前时间字段(列)约束
创建表时, 除了要给每个列指定对应的数据类型, 有时也需要给列添加约束。常见的约束有：主键约束、唯一约束、非空约束、外键约束。
1、主键(primary key)
主键是数据表中，一行记录的唯一标识。比如学生的编号，人的身份证号, 例如:
自增的作用是, 后期往表中插入记录时, 自增的列可以不用插入值(插入null即可), 数据库会自动插入值(1,2,3…), 例如：
提示：如果建表时没有指定主键自增, 也可以后期修改id为主键自增。
2、唯一(unique)
保证所约束的列必须是唯一的，即不能重复出现，例如：用户注册时，保存的用户名不可以重复。
3、非空(not null)
保证所约束的列必须是不为空的，即在插入记录时，该列必须要赋值，例如：用户注册时，保存的密码不能为空。
4、外键约束
外键是一个列,是用于通知数据库两张表数据之间对应关系的这么一个列,从而让数据库帮我们维护两张表之间的对应关系.(例如,当删除某一个部门时,如果部门下还有员工,数据库就会阻止我们删除,创建表时声明foreign key(dept_id) references dept(id))
MySQL建库、建表
- 查看数据库、查看数据表
1、查看mysql服务器中所有数据库
show databases; - 进入某一数据库
use test;
提示，查看已进入的库：select database(); - 查看当前库中的所有表
show tables;
提示：mysql数据库不区分大小写 - 创建数据库、创建数据表
- 创建数据库
• – 删除mydb1库(如果存在)
drop database if exists mydb1;
– 创建mydb1库(如果不存在）
create database if not exists mydb1 charset utf8;– 查看、进入mydb1库
show databases;
use mydb1;
提示：创建库时，要记得指定编码
Sql语句+/c:不执行此行生气了语句
2、创建数据表
– 删除stu学生表(如果存在)
drop table if exists stud;
– 创建stu学生表
create table stud(
id int, – 学生编号
name varchar(20), – 学生姓名
gender char(1), – 学生性别
birthday date, – 出生年月
score double – 考试成绩
);– 查看表结构
desc stud;
提示：# 和 – 是sql语句的注释符号。例如：
– 这是注释内容(要注意的是–和注释之间要有空格)
#这是注释内容
Comment:字段内注释
4更新表记录（insert/update/delete）
4.1insert–插入表记录
插入记录的语法：
insert into 表名(列1,列2…) values(值1, 值2…);
– insert用于向指定的表中插入哪些列, 以及给予哪些值
如果要为所有的列插入值, 可以省略表名后面的列列表，例如：
insert into 表名 values(值1, 值2…);
注意: (1)只能在给所有列插入值时, 才可以省略列列表, 否则会报错
(2)在插入值时, 如果没有省略列列表, 那么列列表和值列表要一一对应
(3)在插入值时, 如果省略了列列表, 那么值列表中值的顺序要和声明列的顺序一致
(4)在插入字符串和日期时, 字符串和日期要用单引号引起来。
– 例如: 往学生表(stu)中插入记录
insert into stu(id,name,gender,birthday,score) values(1, ‘王海涛’,‘男’, ‘1993-1-10’, 78);
insert into stu values(2, ‘齐雷’,‘男’, ‘1995-4-8’, 78);
– 或为指定列插入数据
insert into stu(id, name, gender) values(3, ‘刘沛霞’, ‘女’);
常见问题（FAQ）：在CMD中插入记录时如果报如下错误，
因为这里存在中文的乱码问题。
解决方式：先设置编码GBK “set names gbk”, 再插入记录
我们的cmd是使用的gbk编码,(可以在cmd边框上右键属性查看)因此，在通过CMD访问数据库时，最好一登录就设置 “set names gbk”，set names gbk 的作用是通知数据库使用gbk接收客户端发送过去的数据(库自动转换对应码),返回数据时也是gbk编码;
4.2update–修改表记录
– 修改stu表中王海涛的成绩，加10分特长分。
update stu set score=score+10 where name=‘王海涛’;– 查询stu表中王海涛的信息
select * from stu where name=‘王海涛’;若没有where子句, 则默认修改所有员工
– 修改stu表中所有学生的成绩，加10分特长分。
update stu set score=score+10;
– update stu set score=ifnull(score, 0)+10;注意: null值和任何值计算结果还是null, 因此, 可以通过ifnull函数将null置为零对待.
4.3delete–删除表记录
– 删除stu表中的王海涛信息
delete from stu where name=‘王海涛’;
Truncate table stu;删除表并摧毁,重建一张,主键会重置若没有where子句, 则默认删除所有记录
– 删除emp表中的所有信息
delete from stu;
5查询表记录（select）
- 准备数据：执行《SQL脚本-db10库.txt》中的脚本，创建db10库，创建emp表，并往emp表中插入记录。 5.1基础查询
• – 查询emp表中的所有员工，显示员工姓名、薪资、奖金
select name,sal,bonus from emp;
– 查询emp表中的所有员工，显示所有列。
select * from emp;提示: distinct关键字，用于剔除指定列中的重复值，例如：
– 查询emp表中的所有部门，剔除重复的部门
select distinct dept from emp;试一试: 查询emp表中的所有奖金, 剔除重复的值
5.2where子句查询
对表中的所有记录进行筛选、过滤使用where子句
下面的运算符可以在 WHERE 子句中使用：
SQL练习：
– 查询emp表中薪资大于3000的所有员工，显示员工姓名、薪资
select name,sal from emp where sal>3000;– 查询emp表中总薪资(薪资+奖金)大于3500的所有员工，显示员工姓名、总薪资
select name, sal+bonus from emp where (sal+bonus)>3000;
– 或
select name, sal+bonus as 总薪资 from emp where (sal+bonus)>3000;
提示：(1)as用于定义别名(仅在查询的结果中作为列的表头显示)，也可以省略as
(2)where子句中不能使用列别名（但是可以使用表别名）– 查询emp表中薪资在3000和4500之间的员工，显示员工姓名和薪资
select name,sal from emp where sal between 3000 and 4500;– 查询emp表中姓名中以"刘"开头的员工，显示员工姓名。
select name,sal from emp where name like ‘刘%’;
– 查询emp表中姓名以"刘"开头，字数为两个字的员工，显示员工姓名。
select * from emp where name like ‘刘_’;– 查询emp表中姓名中包含"涛"字的员工，显示员工姓名。
select * from emp where name like ‘%涛%’;
提示："%" 表示任意0或多个任意字符。"_" 表示一个任意字符– 查询emp表中薪资为1400、1600、1800的员工，显示员工姓名和薪资
select name,sal from emp where sal in(1400,1600,1800);– 查询emp表中薪资小于2000和薪资大于4000的员工，显示员工姓名、薪资。
select name,sal from emp where sal<2000 or sal >4000;– 查询emp表中薪资大于3000并且奖金小于600的员工，显示姓名、薪资、奖金。
select name,sal,bonus from emp where sal>3000 and bonus<600;5.3排序查询
对查询的结果进行排序使用 order by关键字。
order by 排序的列 asc 升序 ↑
order by 排序的列 desc 降序 ↓
– 对emp表中所有员工的薪资进行升序(从低到高)排序，显示姓名、薪资。
select name,sal from emp order by sal asc;– 对emp表中所有员工的总薪资进行降序(从高到低)排序，显示姓名、总薪资。
select name, sal+bonus as 总薪资 from emp order by (sal+bonus) desc;5.4分组查询
对所查询的记录可以根据某一列进行分组, 分组使用group by。
– 将员工按照部门进行分组
select * from emp group by dept;– 对emp表按照部门进行分组, 并统计每个部门的人数, 显示部门和对应人数
select dept 部门名称, count(*) 部门人数 from emp group by dept;– 对emp表按照部门进行分组, 求每个部门的最高薪资(不包含奖金)
select max(sal) 总薪资 from emp group by dept;注意：分组之前使用聚合函数表示对查询的所有记录进行统计计算
分组之后使用聚合函数表示对每一个组中的所有记录进行统计计算。5.5聚合函数查询
1、max()或min() – 返回某列的最大值或最小值
2、count() – 返回某列的行数
3、sum() – 返回某列值之和
4、avg() – 返回某列的平均值
!!重要提示：
a) 可以使用count(*)统计记录行数
b) 多个聚合函数可以一起查询
c) 聚合函数不能用在where子句中
d) 在没有分组的情况下，聚合函数不能和其他普通字段一起查询
5.6其他函数
1、数值函数
(1)ceil(数值) – 向上取整
(2)floor(数值) – 向下取整 select floor(3.85);
(3)round(数值) – 四舍五入 select round(3.8);
(4) round(num,m)–四舍五入select round(23.879,2); m代表小数位数(5)rand(数值) – 随机数 select floor(rand()*4);//0 1 2 3
获取3-5的随机整数select floor(rand()3+3);
(6). 非四舍五入 truncate(num,m) select truncate(23.879,2);
– emp表中所有员工薪资上涨15.47%, 向上取整。
select name,sal, ceil(sal1.1547) from emp;
2、字符串函数
1.字符串拼接 concat（s1,s2） s1s2
select concat(“ab”,“mm”)
查询每个员工的姓名和工资 工资以元为单位
select ename,concat(sal,“元”) from emp;
2.获取字符串长度 char_length(str);
查询每个员工的姓名和名字长度
select ename,char_length(ename) from emp;3.获取字符串出现的位置 instr(str,substr)
select instr(“abcdefgh”,“e”);4.转大写和小写 upper() lower()
select upper(‘nba’),lower(‘ABC’);5.截取字符串
从左边截取
select left (“abcdefg”,2);
从右边截取
select right(“abcdefg”,2);
自由截取substring(str,位置,长度)
select substring(“abcdefg”,2,3);6.去空白trim()
select trim(" a b ");7.重复repeat()
select repeat(‘abc’,2);8.替换replace()
select replace(‘abcdefg’,‘b’,‘m’);9.反转reverse()
select reverse(“abc”);
2、日期函数
(1)curdate() – 返回当前日期(年月日)
(2)curtime() – 返回当前时间(时分秒)
(3)now() – 返回当前日期+时间(年月日 时分秒)
(4)date_add()、date_sub() – 增加/减少日期
(5)year()、month()、day()、hour()、minute()、second()，分别用来获取日期中的年、月、日、时、分、秒– 查询系统当前时间。
select now(); insert into t_date values(‘刘德华’,now());
– 查询emp表中所有员工的年龄，显示姓名、年龄。
select name,year(curdate()) - year(birthday) 年龄 from emp;– 查询emp表中所有在1993和1995年出生的，显示姓名、出生日期。
select name,birthday from emp where year(birthday) between 1993 and 1995;
– 查询emp表中本月过生的员工
select name,birthday from emp where month(now())=month(birthday);1. SQL的helloworld
select “helloworld”;2. 获取当前系统时间 now()
select now();
create table t_date(name varchar(10),created_time datetime);
insert into t_date values(‘刘德华’,now());3. 获取当前年月日 当前时分秒 current当前
select curdate(),curtime();4. 从年月日时分秒中提取年月日和提取时分秒
select date(now()),time(now());
4.1查询每个商品的创建时间(年月日)
select created_time, date(created_time) from t_item;
select created_time, time(created_time) from t_item;5.从完整的年月日时分秒中提取时间分量
select extract(year from now());
select extract(month from now());
select extract(day from now());
select extract(hour from now());
select extract(minute from now());
select extract(second from now());5.1查询每个员工的姓名和入职年份
select ename,extract(year from hiredate) from emp;
5.2查询员工表中有员工入职的年份
select distinct extract(year from hiredate)from emp;6.日期格式化
格式： date_format(时间,格式);
%Y 4位年 %y 2位年
%m 2位月 05
%c 1位月 5
%d 日
%H 24小时
%h 12小时
%i 分
%s 秒
测试：
把now() 转成 2019年5月20号 15点15分15秒
select date_format(now(),’%Y年%c月%d号 %H点%i分%s秒’);7.把非标准时间格式转成标准时间格式
格式：str_to_date(时间,格式)
测试：把1904.2019 15点36分20秒 转成标准时间
select str_to_date(“19.04.2019 15点36分20秒”,"%d.%m.%Y %H点%i分%s秒");十二、ifnull()
age = ifnull(x,y)
如果x值为null则age=y, 如果不为null则age=x
把员工表中奖金为null的改成0 不为null的不变
update emp set comm=ifnull(comm,0);
6外键和表关系
6.1外键
外键：唯一标识其他表中的一条记录，用来通知数据库两张表列与列之间的对应关系, 并让数据库帮我们维护这样关系的键就叫做外键。
例如：员工表的部门id列（dept_id）和部门表的id列具有一 一对应的关系, 其中dept_id就是外键。
外键作用: 确保数据库数据的完整性和一致性
添加外键: 例如：foreign key(dept_id) references dept(id)
- 准备数据：执行《SQL脚本-db20库.txt》中的脚本，创建db20库，创建dept和emp表，并往两表中插入记录。
练习：
(1)执行上面的SQL语句，创建两张表（dept和emp），在创建时先不指定dept_id为外键，尝试删除部门表中的某一个部门。（删除具有员工的部门会失败）
(2)将dept和emp表删除重建，在创建时指定dept_id为外键，再次尝试删除部门表中的某一个部门。
6.2表关系
6.2.1一对多
一对多，反过来就是多对一，以班级和学生为例：
(1)一个班级中可能会有多个学生(1~)
(2)一个学生只能属于一个班级(11)，两者合并结果还是1
因此，班级表和学生表是一对多的关系
对于一对多的两张表，可以在多的一方添加列，保存一的一方的主键，从而保存两张表之间的关系
6.2.2一对一
以班级和教室为例：
(1)一个班级对应一个教室(1~1)
(2)一个教室也只对应一个班级(11)，两者合并结果还是11
因此，班级表和教室表是一对一的关系
对于一对一关系的两张表，可以在任意一张表中添加列，保存另一张表的主键，从而保存两张表之间的关系
6.2.3多对多
以学生和老师为例：
(1)一个学生对应多个老师(1~)
(2)一个老师也对应多个学生(1*)，两者合并结果是*
因此，学生表和老师表是多对多的关系
对于多对多的关系，可以拆分成两张一对多的关系，无法在两张表中添加列保存关系，但我们可以添加一张第三方的表（专门保存两张表的关系），保存两张表的主键，从而保存两张表的关系。
7多表连接查询
7.1连接查询
- 准备数据：执行《SQL脚本-db30库.txt》中的脚本，创建db30库，创建dept和emp表，并往两表中插入记录。
连接查询：将两张或者两张以上的表，按照指定条件查询，将结果显示在一张表中。
多张表查询的语法：
select…
from A, B…
where…如果表名过长，可以为表添加别名以方便书写
select…
from A a, B b…
where…上面小写的a和b就是A和B表的别名:
– 查询部门和员工两张表
select *
from dept,emp;上面查询的结果中存在大量错误的数据, 如果想正确显示部门及部门对应的员工，可以通过where子句从中筛选正确的数据.
– 查询部门和部门下的员工。
select * from dept d,emp e
where d.id=e.dept_id;
或
select *
from dept d inner join emp e on d.id=e.dept_id; 上面的查询(inner join…on…)方式也叫做内连接查询
7.2外连接查询
7.2.1左外连接查询:左边表所有数据查出来,右边表只查和左边关联的数据;
显示左侧表中的所有记录，如果在右侧表中没有对应的记录，则显示为null
语法：
select …
from a left join b on (a.id=b.xid)– 查询所有部门和部门下的员工，如果部门下没有员工，显示null
select *
from dept d left join emp e on d.id=e.dept_id;以上结果会显示（左侧表）所有部门，如果某部门下没有员工，（右侧表）则显示为null
7.2.2右外连接查询:右边表所有数据查出来,左边表只查和右边关联的数据;
显示右侧表中的所有记录，如果在左侧表中没有对应的记录，则显示为null
语法：
select …
from a right join b on (a.id=b.xid)– 查询部门和所有员工，如果员工没有所属部门，显示null
select *
from dept d right join emp e on d.id=e.dept_id;
以上结果会显示（右侧表）所有员工，如果员工没有所属部门，（左侧表）则显示为null
7.2.3group_concat()方法 组连接
1.查询每个部门的员工姓名和工资，要求每个部门的数据显示在一行
select deptno,group_concat(ename,’:’,sal) from emp group by deptno;2.查询每种工作的员工性名，要求员工姓名一行显示
select job,group_concat(ename)from emp group by job; 7.3子查询:将一条sql语句的查询结果当做另外一条sql的查询条件;
所谓的子查询，其实就是将一个查询得出的结果，作为另外一个查询的条件。
格式：
select…
from…
where…(select…from…)1、列出薪资比’王海涛’高的所有员工，显示姓名、薪资
– 先查询出’王海涛’的薪资
select sal from emp where name=‘王海涛’;– 再查询比王海涛薪资(2450)高的员工
select name, sal
from emp
where sal>( select sal from emp where name=‘王海涛’);2、列出与’刘沛霞’从事相同职位的所有员工，显示姓名、职位、部门。
– 先关联, 查询员工及员工对应的部门
select e.name, e.job, d.name from emp e, dept d where e.dept_id=d.id;– 再查询’刘沛霞’的职位
select name, job from emp where name=‘刘沛霞’;– 最后筛选, 筛选出和’刘沛霞’相同职位的员工
select e.name, e.job, d.name from emp e, dept d where e.dept_id=d.id and job=(select job from emp where name=‘刘沛霞’);3、列出薪资高于在’大数据部’(已知部门编号为30)就职的所有员工的薪资的员工姓名和薪资、部门名称。
– 查询出’大数据部’的最高薪资
select max(sal) from emp where dept_id=30;
– 关联查询, 查询员工的姓名,薪资, 部门名称
select e.name, e.sal, d.name from emp e, dept d where e.dept_id=d.id and sal>(select max(sal) from emp where dept_id=30);7.4多表查询
1、（左外连接）列出所有部门和部门下的员工，如果部门下没有员工, 显示为null。
select d.id, d.name, e.name, e.dept_id
from dept d left join emp e on e.dept_id=d.id;2、（关联查询）列出在’培优部’任职的员工，假定不知道’培优部’的部门编号。
– 先查询员工及员工所属部门
select e.name, e.dept_id, d.id, d.name
from emp e, dept d
where e.dept_id=d.id;– 再筛选过滤，查询部门名称为’培优部’的员工
select e.name, d.id, d.name
from emp e, dept d
where e.dept_id=d.id and d.name=‘培优部’;3、（自连接查询）列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名
– 先查询员工表（emp e1）
select name, topid from emp e1;
– 再查询上级表（还是员工表，emp e2）
select id, name from emp e2;
– 最后查询员工及其员工的直接上级
select e1.name,e1.topid, e2.id, e2.name
from emp e1, emp e2
where e1.topid = e2.id;4、（分组、聚合函数）列出最低薪资大于1500的各种职位，显示职位和该职位最低薪资
– 先查询出各种职位的最低薪资
select job, min(sal) 最低薪资
from emp group by job;
提示：对分组后的记录筛选过滤请使用having替换where，并且having书写在最后
– 再查询出最低薪资>1500的职位
select job, min(sal) 最低薪资
from emp group by job
having min(sal)>1500;5、（分组、聚合函数查询）列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资。
select dept_id, count(*) 员工数量, avg(sal) 平均薪资
from emp group by dept_id;6、（分组、关联、聚合函数查询）查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
– 先关联查询, 查询出员工和员工对应的部门
select d.id, d.name, d.loc, e.name
from emp e, dept d
where e.dept_id=d.id;
– 再根据部门进行分组, 统计每个部门的员工数量
select d.id, d.name, d.loc, count(*) 员工数量
from emp e, dept d
where e.dept_id=d.id group by e.dept_id;7、（自连接查询）列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
– 关联查询
select e1.id, e1.name, d.name
from emp e1, emp e2, dept d
where e1.dept_id=d.id and e1.topid=e2.id and e1.hdate < e2.hdate;7.5视图view
什么是视图：数据库中表和视图都是其内部的对象，视图可以理解成一张虚拟的表，视图的数据来自于原表，视图的本质实际上就是取代了一段SQL语句;
视图的作用：
- 重用SQL,提高开发效率
- 可以隐藏敏感信息
- 创建视图格式： create view 视图名 as (子查询);
创建一个10号部门的视图：
create view v_emp_10 as (select * from emp where deptno=10);
select * from v_emp_10;(查看)创建一个没有工资的员工表视图(隐藏敏感信息)
create view v_emp_nosal as(select empno,ename,mgr,job,deptno from emp);
select * from v_emp_nosal;(查看)创建查看每个部门的平均工资，最高工资，最低工资，工资总和，部门人数的视图
create view v_emp_info as (select deptno,avg(sal),max(sal),min(sal),sum(sal),count(*) from emp group by deptno);
对简单视图进行增删,操作方式和table一样
insert into v_emp_10 (empno,ename,deptno)values(10010,‘Tom’,10);insert into v_emp_10 (empno,ename,deptno)values(10010,‘Jerry’,20);//数据污染
数据污染：当往视图中添加一条视图中不可见原表中却可见的数据称为数据污染，可以通过数据创建视图时添加with check option 解决。
create view v_emp_20 as (select*from emp where deptno=20) with check option;insert into v_emp_20 (empno,ename,deptno)values(10012,‘刘备’,10);//数据污染
insert into v_emp_20 (empno,ename,deptno)values(10012,‘刘备’,20);
修改和删除只能操作视图中存在的数据
修改：
update v_emp_10 set ename=‘TTT’ where ename =‘Tom’;//成功
update v_emp_10 set ename=‘xxx’ where ename =‘Jerry’;//失败
删除：
delete from v_emp_10 where empno=10010;//成功
delete from v_emp_10 where empno=10011;//失败创建或替换视图
create or replace view v_emp_10 as (select ename,sal from emp where deptno=10);删除视图
drop view v_emp_10;别名：创建视图时使用了别名，则对视图操作时只能使用别名
create view v_emp_30 as (select ename name from emp where create view v_emp=30);select ename from v_emp_30;//报错部认识ename 应该name
7.6索引
索引：索引是数据库中提高查询效率的技术，类似与字典的目录
创建索引的格式：
create index 索引名 on 表名（字段名（？长度））；1.创建索引前先查询一次 看耗费时间
select*from item2 where title =‘100’;2.创建索引
create index i_item_title on item2(title);(哈哈)3.验证 耗费时间0.01秒
select*from item2 where title=‘100’;查看索引：
如果给表添加
格式：show index from 表名;
show index from item2;删除索引：
drop index 索引名 on 表名;
drop index i_item_title on item2;复合索引：
通过多个字段创建索引
create index i_item_title on item2(title,price);show index from item2;//创建了两个索引总结：
- 索引是数据库中提高查询效率的技术类似于目录
- 因为索引会占用磁盘空间不是越多越好，只针对查询时频繁使用的字段创建索引。
- 数据量越大索引效果越明显，如果数据很少可能会降低查询效率。
8扩展内容
8.1使用CMD连接MySQL服务器
通过命令行工具可以登录MySQL客户端，连接MySQL服务器，从而访问服务器中的数据。
1、登录mysql客户端命令：mysql -uroot -proot
-u：后面的root是用户名，这里使用的是超级管理员root；
-p：(小写的p)后面的root是密码，这是在安装MySQL时就已经指定的密码；
2、指定主机和端口连接: mysql -uroot -proot -h127.0.0.1 -P3306
-h：后面给出的127.0.0.1是服务器主机名或ip地址，可以省略的，默认连接本机；
-P：(大写的P)后面的3306是连接端口，可以省略，默认连接3306端口；
3、退出客户端命令：quit或exit或 \q
FAQ：常见问题:
解决方法：复制mysql安装目录下的bin目录, 将bin目录的路径添加到path环境变量中!!
8.2使用Navicat连接MySQL服务器
1、运行navicat客户端，按照下面的图示连接mysql服务器
2、通过navicat发送SQL到mysql，打开所要操作的库（或任意打开一个库），再点击"查询" —> “新建查询”
3、在弹出的窗口中，直接书写sql运行。
4、如果要运行指定的SQL，使用鼠标选中执行的SQL语句，右键–>“运行已选择的”
8.3备份和恢复数据库
1、打开CMD窗口(不要登录)，通过命令备份mydb1数据库
备份命令: mysqldump -u用户名 -p 数据库名字 > 数据文件的位置
例如: mysqldump -uroot -p mydb1 > d:/mydb1.sql
输入密码, 如果没有提示错误, 即备份成功, 查询d盘的mydb1.sql文件
提示: (1)备份数据库的命令后面不要添加分号(;)
(2)备份数据库只是备份数据库中的表, 不会备份数据库本身2、登录到MySQL客户端，删除mydb1库
– 删除mydb1库
drop database mydb1;
– 查询所有库, 是否还存在mydb1库
show databases;
由于备份时, 没有备份数据库本身, 所以在恢复库前, 需要先创建好要恢复的库
– 创建mydb1数据库
create database mydb1 charset utf8;
提示: 此时的mydb1库是空的, 没有任何表
3、在CMD窗口中(不要登录)，通过命令恢复mydb1数据库
– 在CMD窗口中(不要登录)
恢复命令: mysql -u用户名 -p 数据库名字 < 数据文件的位置
例如: mysql -uroot -p mydb1 < d:/mydb1.sql 回车
输入密码, 如果没有提示错误, 即恢复成功, 下面进行验证
– 在登录状态下, 选择mydb1库, 查询其中的表是否恢复了回来
use mydb1;
show tables; 4、导入到数据库.windows放到D盘根目录(随便放哪都可以)
在命令行里面执行：source d:/数据库名;
2.linux：把文件放到桌面，再执行命令：
source /home/soft01/桌面/tables.sql;
3.验证：导入后执行 show databases;
8.4SQL中的特殊字符
8.4.1SQL语句的注释符号
在MySQL客户端中，-- 和 # 是注释符号
– 注释内容（注意–后面的空格不能省略）
#注释内容
8.4.2取消SQL语句的执行—\c
取消当前SQL语句的执行，例如：
当在cmd中书写了SQL语句，又想取消执行，在SQL语句的后面添加一个 \c 可以取消当前SQL语句的执行!!
8.4.3SQL语句无法结束执行的问题
有时在cmd中编写的SQL，后面结尾添加了分号，但是当回车时却怎么也执行不了。
仔细查看，才发现是SQL语句中的引号没有结束的原因。解决方法：
在下面敲一个单引号’和\c，回车即可结束
8.5修改表操作
现创建学生表：
use test; – 进入test库
drop table if exists stu; – 删除学生表(如果存在)
create table stu( – 创建学生表
id int, – 学生id
name varchar(20), – 学生姓名
gender char(1), – 学生性别
birthday date – 出生年月
);8.5.1新增列
语法：ALTER TABLE tabname ADD col_name datatype [DEFAULT expr][,ADD col_name datatype…];
1、往stu表中添加score列，double类型
alter table stu add score double;
8.5.2修改列
语法：ALTER TABLE tabname MODIFY (col_name datatype [DEFAULT expr][,MODIFY col_name datatype]…);
1、修改id列，将id设置为主键
alter table stu modify id int primary key;
2、修改id列，将id主键设置为自动增长
alter table stu modify id int auto_increment;
8.5.3删除列
语法：ALTER TABLE tabname DROP [COLUMN] col_name;
1、删除stu表中的score列
alter table stu drop score;
8.6添加或删除主键及自增长
思考：a) 在建表时，如何为id指定主键约束和自增?
b) 建好的表，如何通过修改添加主键约束和自增?
c) 如何删除表中的主键约束和自增?
1、创建stu学生表, 不添加主键自增, 查看表结果
use mydb1; – 切换到mydb1库
drop table if exists stu; – 删除stu学生表(如果存在)
create table stu( – 重建stu学生表, 没有主键自增
id int,
name varchar(20),
gender char(1),
birthday date
); desc stu; – 查看表结构
表结构如下: 没有主键约束和自增。
2、如果表没有创建, 或者要删除重建, 在创建时可以指定主键或主键自增
drop table if exists stu; – 删除stu表
create table stu( – 重新创建stu表时, 指定主键自增
id int primary key auto_increment,
name varchar(20),
gender char(1),
birthday date
);desc stu; – 查看表结构
表结构如下: 已经添加了主键约束和自增。
3、如果不想删除重建表，也可以通过修改表添加主键或主键自增
再次执行第1步, 创建stu学生表, 不添加主键自增, 查看表结果
– 例如: 将stu学生表中的id设置为主键和自动增长
alter table stu modify id int primary key auto_increment;
desc stu; – 查看表结构如果只添加主键约束, 不设置自增
alter table stu modify id int primary key;如果已经添加主键约束, 仅仅设置自增，但需注意:
(1)如果没有设置主键, 不可添加自增
(2)只有当主键是数值时, 才可以添加自增
alter table stu modify id int auto_increment;4、如果想删除主键自增
– 删除主键自增时, 要先删除自增
alter table stu modify id int;
– 再删除主键约束
alter table stu drop primary key;
desc stu; – 查看表结构8.7添加或删除外键约束
8.7.1添加外键方式一：建表时添加外键
现有部门表如下：
– 创建部门表
create table dept(
id int primary key auto_increment, – 部门编号
name varchar(20) – 部门名称
); 要求创建员工表，并在员工表中添加外键关联部门主键
– 创建员工表
create table emp(
id int primary key auto_increment, – 员工编号
name varchar(20), – 员工姓名
dept_id int, – 部门编号
foreign key(dept_id) references dept(id) – 指定dept_id为外键
);
8.7.2添加外键方式二：建表后添加外键
现有部门表和员工表：
– 创建部门表
create table dept(
id int primary key auto_increment, – 部门编号
name varchar(20) – 部门名称
);– 创建员工表
create table emp(
id int primary key auto_increment, – 员工编号
name varchar(20), – 员工姓名
dept_id int – 部门编号
);
如果表已存在，可以使用下面这种方式：
alter table emp add constraint fk_dept_id foreign key(dept_id) references dept(id);其中 add constraint fk_dept_id 表示新增列，列名为fk_dept_id (名字由自己定义)
foreign key(dept_id)中的dept_id为外键8.7.3删除外键
1、首先通过 “show create table 表名”语法，查询含有外键表的建表语句，例如：
show create table emp;
显示结果如下：其中，emp_ibfk_1是在创建表时，数据库为外键指定的一个名字，删除这个名字即可删除外键关系，例如：
alter table emp drop foreign key emp_ibfk_1;外键删除成功！
8.8where中不能使用列别名
SQL语句的书写顺序:
select * | 列名 – 确定要查询的列有哪些
from 表名 – 确定查询哪张表
where 条件 – 通过筛选过滤, 剔除不符合条件的记录
group by 分组的列 – 指定根据哪一列进行分组
having 条件 – 通过条件对分组后的数据进行筛选过滤
order by 排序的列 – 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount – 指定返回第几页记录以及每页显示多少条SQL语句的执行顺序:
from 表名 – 确定查询哪张表
where 条件 – 通过筛选过滤, 剔除不符合条件的记录
select * | 列名 列别名 – 确定要查询的列有哪些,
group by 分组的列 – 指定根据哪一列进行分组
having 条件 – 通过条件对分组后的数据进行筛选过滤
order by 排序的列 – 指定根据哪一列进行排序
limit (countPage-1)*rowCount, rowCount当数据库爆出 ambiguous关键字错误时,是没有指定相同字段属于关联的哪张表;
8.8.1附1：六道sql面试题
- 面试题1: char(n)、varchar(n)、text都可以表示字符串类型，其区别在于：
(1)char(n)在保存数据时, 如果存入的字符串长度小于指定的长度n，后面会用空格补全，因此可能会造成空间浪费，但是char类型的存储速度较varchar和text快。
因此char类型适合存储长度固定的数据，这样就不会有空间浪费，存储效率比后两者还快！
(2)varchar(n)保存数据时, 按数据的真实长度存储, 剩余的空间可以留给别的数据用，因此varchar不会浪费空间。
因此varchar适合存储长度不固定的数据，这样不会有空间的浪费。
(3)text是大文本类型，一般文本长度超过255个字符，就会使用text类型存储。 - 面试题2: 关于where中不能使用列别名但是可以使用表别名?
是因为, 表别名是声明在from中, from先于where执行, 先声明再使用没有问题, 但是列别名是声明在select中, where先于select执行, 如果先使用列别名, 再声明, 这样执行会报错!! - 面试题3:datetime和timestamp的区别是：
(1)datetime中存入的就是一个日期时间字符串，而timestamp存入是从1970-1-1到指定日期的时间毫秒值(时间戳)。
(2)两者的范围不同：datetime范围是，10009999,timestamp范围是：19702038
(3)datetime类型的列无法指定自动更新为当前时间，而timestamp类型的列可以指定自动更新为当前时间。 - 面试题4:drop、delete、truncate之间的区别：
drop是删除数据库和删除表时用的;
delete和truncate是删除表记录时用的;
delete删除表记录时,仅仅是删除表记录本身, 对表没有影响;
truncate删除表记录时, 默认是将表摧毁再重建, 对表有影响(比如会重置AUTO_INCREMENT的值); - 面试题5:什么是笛卡尔积查询?：
当同时查询两张表时，查询的结果默认是笛卡尔积查询(select * from dept,emp;)
所谓的笛卡尔积查询，就是左边表有n条记录，右边表有m条记录，笛卡尔积查询的结果就是 m*n条。
由于笛卡尔积查询中存在大量错误的数据，所以我们一般不会直接使用笛卡尔积查询。
可以在笛卡尔积查询的基础上通过where子句（查询条件）将错误的数据剔除，保留正确的数据即可！(这种查询又叫关联查询) - 面试题6:where和having的区别?： where和having都是用于筛选过滤。将不符合条件的记录剔除，只保留符合条件的记录。其中： （1）where是在分组之前对表中的记录进行筛选过滤，写在from之后，group by之前。 （2）having是在分组之后对表中的记录进行筛选过滤，写在group by之后，order by之前。 （3）where子句中不能使用列别名和聚合函数(count，sum，avg，min，max) （4）having子句中可以使用列别名和聚合函数。where不可以使用这二者; 8.8.2附1：关于事务(Transaction) 事务是数据库技术领域中的概念。 数据库（例如MySQL）在执行增、删、改这些“写”操作时，都会先在数据库服务器内存中执行数据操作，然后，更新到数据库存储的文件中，默认情况下，数据是自动提交的！所以，每次执行增、删、改操作结束后，相应的数据就会发生变化！ 当某些业务涉及多次增、删、改操作时，例如“银行转账”时，假设存在原始数据： 小刘同学 10000 大刘同学 1000 如果需要实现“小刘同学向大刘同学转账5000元”，则需要执行：
update xx set money = monty - 5000 where user=‘小刘同学’;
update xx set money = monty + 5000 where user=‘大刘同学’;- 如果在执行过程中，只执行了第1条语句后，程序就出错崩溃，或者服务器停电等其它不可控因素导致第2条语句无法执行，最终，就会出现数据安全问题（数据并不是依据程序所设定的规则而产生或变化）。 针对这个问题的解决方案就是使用事务(Transaction)，其核心操作是：先关闭数据库的自动提交功能，使得即使执行SQL语句，也只是在内存中执行，并不会写入到文件中，只有所有需要执行的SQL语句全部执行结束，才会提交(commit)，即写入到文件中，如果在执行过程中，出现任何意外，可以执行回滚(rollback)，即撤消以前在内存中的数据操作，更不会写入到文件中。最终的执行效果就是所涉及的数据操作要么全部成功，要么全部失败。 在基于Spring-JDBC的开发过程中，并不需要在代码中显式的编写“关闭自动提交”、“执行提交”、“执行回滚”这些操作，只需要在业务方法之前添加@Transactional注解即可！框架的处理流程大致是：
关闭自动提交
try {
执行若干个数据操作
执行提交(commit)
} catch (RuntimeException e) {
执行回滚(rollback)
}
也就是说，在整个执行过程中，只要出现了RuntimeException，就会执行回滚，如果一直没有出现RuntimeException（即使出现了例如IOException），就会执行提交！
另外，关于@Transactional注解，还可以添加在类的声明之前，表示当前类中所有业务方法都是以事务的方式来运行的，一般来说，没有这个必要性，所以，推荐只在需要的业务方法之前添加该注解！
小结：如果某个业务方法(在Service中的方法)涉及超过1条增、删、改操作(例如需要执行2次Update操作，或1次Insert加1次Update操作等，与查询无关)时，就需要使用事务，使用的方法就是在业务方法之前添加@Transactional注解；
在业务方法内部，每次执行增、删、改操作时，都必须获取受影响的行数，并进行判断，如果受影响的行数不是预期值，必须抛出RuntimeException或其子孙类异常。
数据库事务
！了解事务的作用
!!掌握事务的四大特性(面试)
了解事务的三个读问题
了解事物的四个隔离级别
9事务及四大特性
9.1什么是事务
数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。
简单的说：事务就是将一堆SQL(通常是增删改操作)的执行绑在一起，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。
在事务开启之后，在commit之前。数据库中的数据并没有真正发生改变。而是先记录一下，在提交之后才会一致提交到数据库；如果执行失败则rollback（删掉记录而已 ）
下面以银行转账为例，张三转100块到李四的账户，这至少需要两条SQL语句：
给张三的账户减去100元；
update 账户表 set money=money-100 where name=‘张三’;
给李四的账户加上100元。
update 账户表 set money=money+100 where name=‘李四’;
如果在第一条SQL语句执行成功后，在执行第二条SQL语句之前，程序被中断了（可能是抛出了某个异常，也可能是其他什么原因），那么李四的账户没有加上100元，而张三却减去了100元。这肯定是不行的。
你现在可能已经知道什么是事务了吧！在上面整个转账过程中执行的所有SQL语句会在一个事务中，而事务中的多个操作，要么全都成功，要么全都失败，不可能存在成功一半的情况。
也就是说给张三的账户减去100元如果成功了，那么给李四的账户加上100元的操作也必须是成功的；否则，给张三减去100元以及给李四加上100元都是失败的。
9.2事务的四大特性
事务的四大特性(ACID)是：ACID特性是保证事务正确执行的四大基本要素
(1)原子性（Atomicity(最小不可拆分，保证全部成功或全部失败)）：事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。
(2)一致性（Consistency(从前一个一致状态到另一个一致状态)）：事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号金额之和应该是不变的。
(3)隔离性（Isolation(多个事务之间互不影响)）：隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。也就是说，在事中务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。例如：在A事务中，查看另一B事务(正在修改张三的账户金额)中张三的账户金额，要查看到B事务之前的张三的账户金额，要么查看到B事务之后张三的账户金额。
(4)持久性（Durability(事务提交后数据保存到磁盘中持久生效)）：一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。
9.3MySQL中的事务
在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要开启事务和结束事务。
开启事务：start transaction； 或者用begin开启
结束事务：commit（提交事务:改动提交磁盘）或rollback（回滚事务:改动清除）。
在执行SQL语句之前，先执行strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了！
下面演示A账户给B账户转账的例子：
** 准备数据：
– 1、创建数据库jt_db数据库(如果不存在才创建)
create database if not exists jt_db charset utf8;
use jt_db; – 选择jt_db数据库
– 2、在 jt_db 库中创建 acc 表(银行账户表), 要求有id(主键),name(姓名),money(账户金额)
drop table if exists acc;
create table acc(
id int primary key auto_increment,
name varchar(50),
money double
);
– 3、往 acc 表中, 插入2条记录
insert into acc values(null,‘A’,1000);
insert into acc values(null,‘B’,1000);
– 查询acc表中的所有记录
select * from acc;
下面分别演示事务开启及执行一系列SQL之后，回滚事务、提交事务及中断操作的效果。
rollback（回滚事务）：
– 查询acc账户表中A和B的金额
select * from acc;
– 开启事务
start transaction;
– 开始转账，A账户减去100元
update acc set money=money-100 where name=‘A’;
– 查询acc账户表中A和B的金额
select * from acc;
– B账户增加100元
update acc set money=money+100 where name=‘B’;
– 查询acc账户表中A和B的金额
select * from acc;
– 回滚事务（失败关事务）
rollback;
– 再次查询acc账户表中A和B的金额
select * from acc;commit（提交关事务）：
– 将上面的操作再做一次，最后将rollback替换为commit，即提交事务
commit;中断操作
– 将上面的操作再做一次，最后将rollback替换为quit，即中断操作
quit;
有事物保护的情况下回滚的流程：
5.1. 开启事物 begin
5.2.蝙蝠侠减去2000
update user set money=money-2000 where id=2 and status=‘正常’;
5.3.此时当前终端查询数据已经发生改变（因为查询到的是内存中的数据），开启另外一个终端查询会发现数据并未改变；
5.4.超人+2000
update user set money=money+2000 where id=1 and status=‘正常’;
5.5.当判断出有一条执行失败，则需要回滚；
rollback;有事物保护下提交的流程：
insert into user values(null ,‘灭霸’,20,‘正常’);
6.1.开启事物：begin;
6.2.蝙蝠侠-5000
update user set money=money-5000 where id=2 and status=‘正常’;
6.3.灭霸+5000
update user set money=money+5000 where id=3 and status=‘正常’;
4.当判断出两条全部执行成功则执行提交事物命令
commit;保存回滚点
begin;
update user set money=200 where id=3;
savepoint s1;
update user set money=200 where id=3;
savepoint s2;
update user set money=200 where id=3;
rollback to s2;10事务并发读问题
10.1事务并发读问题
多个事务对相同的数据同时进行操作，这叫做事务并发。
set tx_isolation=‘read-uncommitted’;设置数据库事务的隔离级别，最差级别（允许出现一切问题），性能但是最好的；
在事务并发时，如果没有采取必要的隔离措施，可能会导致各种并发问题，破坏数据的完整性等。这些问题中，其中有三类是读问题，分别是：脏读、不可重复读、幻读。(1)脏读（dirty read）：读到另一个事务的未提交更新数据，即读取到了脏数据；
例如：A给B转账100元但未提交事务，在B查询后，A做了回滚操作，那么B查询到了A未提交的数据，就称之为脏读。
(2)不可重复读（unrepeatable read）：对同一记录的两次读取不一致，因为另一事务对该记录做了修改（是针对修改操作）
例如：在事务1中，前后两次查询A账户的金额，在两次查询之间，另一事物2对A账户的金额做了修改，此种情况可能会导致事务1中，前后两次查询的结果不一致。这就是不可重复度
(3)幻读（虚读）（phantom read）：对同一张表的两次查询不一致，因为另一事务插入了一条记录(是针对插入或删除操作)；注意:mysql默认的是不允许出现脏读和不可重复读，所以在下面演示之前需要设置mysql允许出现脏读、不可重复读等。
set tx_isolation=‘read-uncommitted’;脏读示例：
– 在窗口1中，开启事务，执行A给B转账100元
set tx_isolation=‘read-uncommitted’; – 允许脏读、不可重复读、幻读
use jt_db; – 选择jt_db库
start transaction; – 开启事务
update acc set money=money-100 where name=‘A’;
update acc set money=money+100 where name=‘B’;– 在窗口2中，开启事务，查询B的账户金额
set tx_isolation=‘read-uncommitted’; – 允许脏读、不可重复读、幻读
use jt_db; – 选择jt_db库
start transaction; – 开启事务
select * from acc where name=‘B’; – 出现脏数据– 切换到窗口1，回滚事务，撤销转账操作。
rollback; – 回滚事务– 切换到窗口2，查询B的账户金额
select * from acc where name=‘B’;
在窗口2中，B看到自己的账户增加了100元（此时的数据A操作事务并未提交），此种情况称之为"脏读"。不可重复读示例：
– 在窗口1中，开启事务，查询A账户的金额
set tx_isolation=‘read-uncommitted’; – 允许脏读、不可重复读、幻读
use jt_db; – 选择jt_db库
start transaction; – 开启事务
select * from acc where name=‘A’;– 在窗口2中，开启事务，查询A的账户金额减100
set tx_isolation=‘read-uncommitted’; – 允许脏读、不可重复读、幻读
use jt_db; – 选择jt_db库
start transaction; – 开启事务
update acc set money=money-100 where name=‘A’; – A账户减去100
select * from acc where name=‘A’;
commit; – 提交事务– 切换到窗口1，再次查询A账户的金额。
select * from acc where name=‘A’; – 前后查询结果不一致
在窗口1中，前后两次对同一数据(账户A的金额)查询结果不一致，是因为在两次查询之间，另一事务对A账户的金额做了修改。此种情况就是"不可以重复读"幻读示例：
– 在窗口1中，开启事务，查询账户表中是否存在id=3的账户
set tx_isolation=‘read-uncommitted’; – 允许脏读、不可重复读、幻读
use jt_db; – 选择jt_db库
start transaction; – 开启事务
select * from acc where id=3;– 在窗口2中，开启事务，往账户表中插入了一条id为3记录，并提交事务。
– 设置mysql允许出现脏读、不可重复度、幻读
set tx_isolation=‘read-uncommitted’;
use jt_db; – 选择jt_db库
start transaction; – 开启事务
insert into acc values(3, ‘C’, 1000);
commit; – 提交事务– 切换到窗口1，由于上面窗口1中查询到没有id为3的记录，所以可以插入id为3的记录。
insert into acc values(3, ‘C’, 1000); – 插入会失败!
在窗口1中，查询了不存在id为3的记录，所以接下来要执行插入id为3的记录，但是还未执行插入时，另一事务中插入了id为3的记录并提交了事务，所以接下来窗口1中执行插入操作会失败。
探究原因，发现账户表中又有了id为3的记录（感觉像是出现了幻觉）。这种情况称之为"幻读"以上就是在事务并发时常见的三种并发读问题，那么如何防止这些问题的产生？
可以通过设置事务隔离级别进行预防。
10.2事务隔离级别
事务隔离级别分四个等级，在相同数据环境下，对数据执行相同的操作，设置不同的隔离级别，可能导致不同的结果。不同事务隔离级别能够解决的数据并发问题的能力也是不同的。（一般情况下是不用使用的）
设置隔离级别：set tx_isolation=‘read-uncommitted’;
1、READ UNCOMMITTED（读未提交数据）
安全级别最低, 可能出现任何事务并发问题(比如脏读、不可以重复读、幻读等)
性能最好（不使用!!）2、READ COMMITTED（读已提交数据）（Oracle默认）
防止脏读，没有处理不可重复读，也没有处理幻读；
性能比REPEATABLE READ好3、REPEATABLE READ（可重复读）（MySQL默认）
防止脏读和不可重复读，不能处理幻读问题；
性能比SERIALIZABLE好4、SERIALIZABLE（串行化：一个一个执行）
不会出现任何并发问题，因为它是对同一数据的访问是串行的，非并发访问的；
性能最差；MySQL的默认隔离级别为REPEATABLE READ，即可以防止脏读和不可重复读
10.3设置隔离级别
0、MySQL查询当前的事务隔离级别
select @@tx_isolation;1、MySQL设置事务隔离级别（了解）
(1) set tx_isolation=‘read-uncommitted’;
安全性最差，容易出现脏读、不可重复读、幻觉读，但性能最高
(2) set tx_isolation=‘read-committed’;
安全性一般，可防止脏读，但容易出现不可重复读、幻觉读
(3) set tx_isolation=‘repeatable-read’;
安全性较好，可防止脏读、不可重复读，但是容易出现幻读
(4) set tx_isolation=‘serialiable’;
安全性最好，可以防止一切事务并发问题，但是性能最差。2、JDBC设置事务隔离界别
JDBC中通过Connection提供的方法设置事务隔离级别：
Connection. setTransactionIsolation(int level)
参数可选值如下：
Connection.TRANSACTION_READ_UNCOMMITTED 1（读未提交数据）
Connection.TRANSACTION_READ_COMMITTED 2（读已提交数据）
Connection.TRANSACTION_REPEATABLE_READ 4（可重复读）
Connection.TRANSACTION_SERIALIZABLE 8（串行化）
Connection.TRANSACTION_NONE 0（不使用事务）
提示：在开发中，一般情况下不需要修改事务隔离级别3、JDBC中实现转账例子
提示：JDBC中默认是自动提交事务，所以需要关闭自动提交，改为手动提交事务
也就是说, 关闭了自动提交后, 事务就自动开启, 但是执行完后需要手动提交或者回滚!!
(1)执行下面的程序，程序执行没有异常，转账成功！A账户减去100元，B账户增加100元。
(2)将第4步、5步中间的代码放开，再次执行程序，在转账过程中抛异常，转账失败！由于事务回滚，所以A和B账户金额不变。
public static void main(String[] args) throws SQLException {
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
//1.获取连接
conn = JDBCUtil.getConn();
//2.关闭JDBC自动提交事务（默认开启事务）
conn.setAutoCommit(false);
//3.获取传输器
stat = conn.createStatement();/* ***** A给B转账100元 ***** */
//4.A账户减去100元
String sql = "update acc set money=money-100 where name='A'";
stat.executeUpdate(sql);
//int i = 1/0; // 让程序抛出异常，中断转账操作
//5.B账户加上100元
sql = "update acc set money=money+100 where name='B'";
stat.executeUpdate(sql);
//6.手动提交事务
conn.commit();
System.out.println("转账成功！提交事务...");
} catch (Exception e) {
e.printStackTrace();
//一旦其中一个操作出错都将回滚，使两个操作都不成功
conn.rollback();
System.out.println("执行失败！回滚事务...");
} finally{
JDBCUtil.close(conn, stat, rs);
}}
Day01.JDBC
目录
1 课程计划 1
2 JDBC概述 1
2.1 什么是JDBC 1
2.2 为什么要学习JDBC 1
2.3 如何使用JDBC访问数据库 1
2.4 JDBC开发细节 1
3 JDBC增删改查 1
4 PreparedStatement对象 1
4.1 模拟用户登陆案例 1
4.2 SQL注入攻击 1
4.3 防止SQL注入攻击 1
5 数据库连接池 1
5.1 什么是连接池 1
5.2 为什么要使用数据库连接池 1
5.3 如何使用C3P0连接池 1
6 事务及四大特性 1
6.1 什么是事务 1
6.2 事务的四大特性 1
6.3 MySQL中的事务 1
7 事务并发读问题 1
7.1 事务并发读问题 1
7.2 事务隔离级别 1
7.3 设置隔离级别 1
8 扩展内容 1
8.1 JDBC小结 111JDBC:java操作数据库技术
1、了解JDBC的作用
2、掌握使用JDBC查询和更新数据
3、了解SQL注入及掌握PreparedStatement的使用
4、掌握连接池的原理及c3p0连接池的使用
5、了解事务的作用及掌握事务的四大特性
6、熟悉事务的三个读问题及四个隔离级别
11.1什么是JDBC:通过Java语言操作数据库的一门技术
JDBC( Java DataBase Connectivity ) 翻译过来就是Java数据库连接，其实就是通过Java语言操作数据库的一门技术。
11.2为什么要学习JDBC
在Java开发中，我们不是通过CMD窗口来操作数据库，更多的是通过Java程序来操作数据库，而JDBC就可以实现这样一个操作.11.3如何使用JDBC访问数据库
1.提出需求:
创建一个 jt_db 数据库，在库中创建一个Account表，并插入三条记录，然后利用Java程序查询出Account表中所有的记录，并打印在控制台上.
2.开发步骤:
(1)准备数据, 创建jt_db库, 创建account表
drop database if exists jt_db;
create database jt_db charset utf8;
use jt_db;
create table account(
id int primary key auto_increment,
name varchar(50),
money double
);
insert into account values(null, ‘tom’, 1000);
insert into account values(null, ‘andy’, 1000);
insert into account values(null, ‘tony’, 1000);
(2)创建JAVA工程(3)导入jar包
(4)创建类并实现JDBC程序(六个步骤)
代码实现:
//1.注册数据库驱动
Class.forName(“com.mysql.jdbc.Driver”);//2.获取数据库连接
Connection conn = DriverManager.getConnection(“jdbc:mysql://localhost:3306/jt_db?characterEncoding=utf-8”, “root”, “root”);//3.获取传输器
Statement stat = conn.createStatement();//4.利用传输器发送SQL到数据库执行，并返回执行结果
ResultSet rs = stat.executeQuery(“select * from account”);//5.处理结果:将表中所有的记录输出在控制台
while (rs.next()) {
int id = rs.getInt(“id”);
String name = rs.getString(“name”);
double money = rs.getDouble(“money”);
System.out.println(id+" : “+name+” : "+money);
}//6.释放资源
rs.close();
stat.close();
conn.close();3.执行结果:
11.4JDBC开发细节
1、注册数据库驱动
Class.forName(“com.mysql.jdbc.Driver”);
所谓的注册驱动，就是让JDBC程序加载mysql驱动程序，并管理驱动
驱动程序实现了JDBC API定义的接口以及和数据库服务器交互的功能，加载驱动是为了方便使用这些功能。2、获取连接之数据库URL
Connection conn = DriverManager.getConnection(
“jdbc:mysql://localhost:3306/jt_db?characterEncoding=utf-8”,
“root”, “root”
);
DriverManager.getConnection() 用于获取数据连接, 返回的Connection连接对象是JDBC程序连接数据库至关重要的一个对象。参数2和参数3分别是所连接数据库的用户名和密码。
参数1：“jdbc:mysql://localhost:3306/jt_db” 是连接数据库的URL，用于指定访问哪一个位置上的数据库服务器及服务器中的哪一个数据库，其写法为:当连接本地数据库，并且端口为3306，可以简写为如下形式：
jdbc:mysql:///jt_db3、Statement传输器对象
Statement stat = conn.createStatement();
Statement传输器对象用于向数据库服务器发送sql语句，该对象上提供了发送sql的方法：
executeQuery(String sql) – 用于向数据库发送查询类型的sql语句，返回一个ResultSet对象中
executeUpdate(String sql) – 用于向数据库发送更新(增加、删除、修改)类型的sql语句，返回一个int值，表示影响的记录行数4、ResultSet结果集对象
ResultSet对象用于封装sql语句查询的结果，也是一个非常重要的对象。该对象上提供了遍历数据及获取数据的方法。
(1)遍历数据行的方法
next() – 使指向数据行的索引向下移动一行(2)获取数据的方法
getInt(int columnIndex)
getInt(String columnLable)
getString(int columnIndex)
getString(String columnLable)
getDouble(int columnIndex)
getDouble(String columnLable)
getObject(int columnIndex)
getObject(String columnLable)…
5、释放资源
rs.close();
stat.close();
conn.close();
此处释放资源必须按照一定的顺序释放，越晚获取的越先关闭。所以先关闭 rs对象，再关闭stat对象，最后关闭conn对象。
另，为了避免上面的程序抛出异常，释放资源的代码不会执行，应该把释放资源的代码放在finally块中.
try{
…
}catch(Exception e){
…
}finally{
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs = null;
}
}
if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
stat = null;
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
conn = null;
}
}
}12JDBC增删改查
完成JDBC对数据库中数据的增删改查操作!!
12.1新增数据
往account表中插入一条新的记录，name为 “john”，money为30000
public static void add(){
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
//注册驱动
Class.forName(“com.mysql.jdbc.Driver”);
//获取连接
conn = DriverManager.getConnection(
“jdbc:mysql:///jt_db”,
“root”,
“root”
);
//获取传输器
stat = conn.createStatement();
//利用传输器发送SQL到数据库执行，返回执行结果
String sql = "insert into account values "
+ “(null, ‘john’, 20000)”;
int row = stat.executeUpdate(sql);System.out.println("影响了"+row+"行。。。");
} catch (Exception e) {
e.printStackTrace();
}finally{
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs = null;
}
}
if(stat != null){
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
stat = null;
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}
}}
12.2修改数据
(自己完成)修改：修改account表中name为 “john” 的记录，将金额改为2500
public static void update(){
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
//注册驱动
Class.forName(“com.mysql.jdbc.Driver”);
//获取连接
conn = DriverManager.getConnection(
“jdbc:mysql:///jt_db”,
“root”,
“root”
);
//获取传输器
stat = conn.createStatement();
//执行SQL，返回结果
String sql = "update account set money "
+ “=2500 where name=‘john’”;
int row = stat.executeUpdate(sql);
System.out.println(“影响了”+row+“行。。。”);
} catch (Exception e) {
e.printStackTrace();
} finally{
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs = null;
}
}
if(stat!=null){
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
stat = null;
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}
}}
12.3删除数据
(自己完成)删除：删除account表中name为 “john” 的记录
public static void delete(){
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
//通过JDBCUtils工具类获取连接
conn = JDBCUtils.getConnection();//获取传输器
stat = conn.createStatement();
//执行SQL，返回结果
String sql = "delete from account "
+ "where name='john'";
int row = stat.executeUpdate(sql);
System.out.println("影响了"+row+"行。。。");
} catch (Exception e) {
e.printStackTrace();
} finally{
//通过调用JDBCUtils类中的close方法将资源关闭
JDBCUtils.close(conn, stat, rs);
}}
12.4查询数据
查询：查询account表中id为1的记录
public static void query(){
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
stat = conn.createStatement();
String sql = “select * from "
+ “account where id=1”;
rs = stat.executeQuery(sql);
if(rs.next()){
System.out.println(
rs.getInt(“id”)+” : " +
rs.getString(“name”)+" : "+
rs.getDouble(“money”)
);
}} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCUtils.close(conn, stat, rs);
}}
13PreparedStatement对象
在上面的增删改查的操作中，使用的是Statement传输器对象，而在开发中我们用的更多的传输器对象是PreparedStatement对，PreparedStatement是Statement的子接口，比Statement更加安全，并且能够提高程序执行的效率。
13.1模拟用户登陆案例
(1)准备数据
use jt_db;
create table user(
id int primary key auto_increment,
username varchar(50),
password varchar(50)
);
insert into user values(null,‘张三’,‘123’);
insert into user values(null,‘李四’,‘234’);
(2)创建LoginUser 类，提供 main 方法 和 login 方法。执行时，输入： 或输入:
13.2SQL注入攻击
通过上面的案例，我们发现在执行时，不输入密码只输入用户名也可以登陆成功。这就是SQL注入攻击。SQL注入攻击：由于后台的SQL语句是拼接而来的。其中的参数是由用户提交的，如果用户在提交参数时，在其中掺杂了一些SQL关键字或者特殊符号(比如#、or)，就可能会导致SQL语句的语意发生变化。从而执行一些意外的操作。(通过正则表达式对提交过来的参数进行效验,检查参数中是否包含特殊符号或者关键字,还可以使用preparedStatement效率更高)
13.3防止SQL注入攻击
如果防止SQL注入攻击？ 使用PreparedStatement对象来替代Statement对象。
添加loginByPreparedSatement方法，在方法中，使用PreparedStatement来代替Statement作为传输器对象使用！再次执行程序，按照上面的操作登录。此时，已经成功的防止了SQL注入攻击问题了。
使用PreparedStatement对象可以防止SQL注入攻击
而且通过方法设置参数更加的方便且不易出错!
还可以从某些方面提高程序执行的效率!
14数据库连接池
14.1什么是连接池
所谓的池就是一个容器，来存储程序的中的数据.(常量池、线程池、连接池)
而数据库连接池就是用来存储数据库连接的池子，用于在整个程序中共享连接，减少连接开关的次数，实现连接的复用，从而提高程序执行的效率.提示：连接池还有另外一个名字，即DataSource，翻译为数据源，因此连接池也叫做数据源。
14.2为什么要使用数据库连接池
对于数据库来说，频繁的开关连接会非常的耗费资源，也会导致程序执行效率的低下。
我们可以在程序中创建一个池子，在程序启动时就初始化一批连接放在连接池中，当用户需要连接时，就直接从池子中拿一个连接使用，当用完连接后，也不要将连接关闭，而是将连接还回池中，下一个用户需要连接时也是如此。
这样可以减少链接开关的次数，从而提供程序执行的效率.
1、传统方式操作数据库2、使用连接池操作数据库
14.3如何使用C3P0连接池（也叫数据源）Dbcp/c3p0/druid
使用C3P0连接池开发步骤：
1、导入开发包2、创建数据库连接池
ComboPooledDataSource cpds = new ComboPooledDataSource();3、设置数据库连接的基本信息
(1)方式一：(不推荐)：这种方法将连接参数写死在程序中,如果后期需要修改参数,就需要改程序源码,改完后还得重新编译程序,还得重复发布项目.非常麻烦.因此最好将这些可能经常发生变化的参数,提取到配置文件中,因为配置文件不是java文件,改完后不需要编译,也不会重新发布项目,比如只需重启服务器就读取到最新配置了;
cpds.setDriverClass(“com.mysql.jdbc.Driver”);
cpds.setJdbcUrl(“jdbc:mysql:///jt_db?useUnicode=true&characterEncoding=utf-8”);
cpds.setUser(“root”);
cpds.setPassword(“root”);
(2)方式二：(推荐):在类目录下(开发时可以放在src或者类似的源码目录下), 添加一个c3p0-config.xml文件, 配置内容如下：这种模式c3p0会自动set这四个属性值 <?xml version="1.0" encoding="UTF-8"?> com.mysql.jdbc.Driver jdbc:mysql:///jt_db?useUnicode=true&characterEncoding=utf-8 root root
(3)方式三：(推荐)c3p0.properties文件的位置必须是源码目录src下,编译后会输出到类目录,将来程序执行时会到类的根目录下找指定名称(c3p0.properties)的文件,所以不仅仅位置是固定的,名字也是固定的.如果位置不对/名字不对,c3p0程序找不到该配置文件(这种模式c3p0也会自动set四个属性值)
c3p0.driverClass=com.mysql.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql:///jt_db
c3p0.user=root
c3p0.password=root
* 如果是自己创建的连接对象,连接对象没有被修改过,调用conn.close()方法,就是将连接关闭;
* 如果是从连接池中获取的一个连接对象,连接对象其实已经被改造了,其中的close方法内部功能被改造成了还连接到连接池中;
14.4JDBC小结
1.JDBC的由来
Java程序都是通过JDBC(Java DataBase Connectivity)连接数据库的，这样我们可以通过Java程序发送SQL语句到数据库服务器中执行，从而访问数据库。
JDBC是由SUN公司提出一系列规范，但是它只定义了接口规范（也就是说SUN公司提出的其实只是一堆的接口和方法声明），而接口中具体的实现是交给各个数据库厂商去实现的（因为每个数据库都有其特殊性，这些是Java规范和SUN公司都无法确定的，所以接口只能由各厂商实现）。
因此SUN公司提供的JDBC这套规范，使得我们无需关心各厂商如何进行实现，只需要学会这套接口（其实就是知道接口中定义了什么功能），就可以使用各个厂商提供的数据库驱动了
因此开发人员只要学会JDBC这套接口，所有的数据库驱动就都会使用了。
2.JDBC包的介绍
JDBC主要是由java.sql 和javax.sql包组成的，并且这两个包已经被集成到J2SE的规范中了，这意味着，只要一个普通的java程序就可以使用JDBC。
需要注意的是，JDBC包中大部分都是接口，因此在开发数据库程序时，除了如上的两个包，还需要手动的导入具体的数据库驱动。
14.5关于Connection对象导包问题
在Jdbc程序中，我们需要获取连接对象（Connection）、传输器对象（Statement）和结果集对象（ResultSet），并且在导包时，一直在强调，使用java.sql包中的相应接口来接收。到底是为什么？
下面以为什么使用接口接收Connection对象进行举例说明，其他两个Statement和ResultSet原因相同：
现在我们连接的是mysql数据库，因此下面传入的是连接mysql的4个参数信息：
Class.forName(“com.mysql.jdbc.Driver”);
Connection conn = DriverManager.getConnection(
“jdbc:mysql://localhost:3306/jt_db?characterEncoding=utf-8”,
“root”, “root”);
如果在获取连接对象时，上面传输的4个参数是连接mysql的参数信息
则上面等号(=)后面返回的就是mysql的Connection对象（即，是由mysql的Connection实现创建的对象）
此时 等号(=)前面，可以使用mysql的Connection类型的变量来接收。
或者使用 mysql的Connection类的父接口来接收（父接口接收子类对象）但，如果考虑到以后，上面连接数据的4个参数可能会替换成别的数据库的参数，比如Oracle。
那么上面等号(=)后面返回的就是Oracle的Connection对象（即，是由Oracle的Connection实现创建的对象）此时 等号(=)前面，可以使用Oracle的Connection类型的变量来接收。
或者使用 Oracle的Connection类的父接口来接收（父接口接收子类对象）
而mysql和Oracle中的Connection类的父接口，其实是同一个接口
因此，为了达到一劳永逸的效果，此处，可以使用父接口来接收，不管等号(=)后面返回的mysql的连接对象，还是返回oracle的连接对象，都可以使用两者的父接口来进行接收。下面是连接Oracle示例：
Class.forName(“oracle.jdbc.OracleDriver”);
Connection conn = DriverManager.getConnection(
"String url = "jdbc:oracle:thin:@localhost:1521:orcl ",
“admin”, “123”);
