MySQL数据库（良心资料）

阮键

发布于 2019-09-29 16:59:58

1.3K0

发布于 2019-09-29 16:59:58

文章被收录于专栏：JAVA人生/面试技巧JAVA人生/面试技巧

一、MySQL数据库

1、数据库简介

1.1、数据库的概念

数据库就是用来存储和管理数据的仓库。数据库存储数据的优点：

l 可存储大量数据；

l 方便检索；

l 保持数据的一致性、完整性；

l 安全，可共享；

l 通过组合分析，可产生新数据；

1.2、数据库的发展历程

l 没有数据库，使用磁盘文件存储数据；

l 层次结构模型数据库；

l 网状结构模型数据；

l 关系结构模型数据库：使用二维表格来存储数据；

l 关系-对象模型数据库；

MySQL就是关系型数据库。

1.3、常见数据库

l Oracle：甲骨文；

l MySQL：甲骨文；

l DB2：IBM；

l SQL Server：微软；

l Sybase：赛尔斯；

1.4、理解数据库

我们所说的数据库泛指“关系型数据库管理系统（RDBMS-Relational database management system）”，即“数据库服务器”。

当我们安装了数据库服务器后，就可以在数据库服务器中创建数据库，每个数据库中还可以包含多张表。

数据库表就是一个多行多列的表格。在创建表时，需要指定表的列数，以及列名称，列类型等信息。而不用指定表格的行数，行数是没有上限的。下面是tab_student表的结构：

当把表格创建好了之后，就可以向表中添加数据了。向表格添加数据是以行为单位的！下面是s_student表的记录：

s_id	s_name	s_age	s_sex
s_1001	tom	23	m
s_1002	liyi	32	w
1_1003	jack	44	m

数据库的理解：

l RDBMS=管理员（manager）+仓库（database）

l database=N个table

l table：

l 表结构：定义表的列名和列类型

l 表记录：一行一行的记录

1.5、应用程序与数据库

应用程序使用数据库完成对数据的存储。

2、安装MySQL数据库

2.1、安装MySQL

参考：MySQL安装图解.doc

2.2、MySQL目录结构

1、MySQL安装成功后会在两个目录中存储文件：

C:\ProgramData\MySQL\MySQL Server 5.5\data：DBMS数据库文件（卸载MySQL时不会删除这个目录，需要自己手动删除）；
D:\Program Files\MySQL\MySQL Server 5.5：DBMS管理程序

2、MySQL重要文件

D:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe：客户端程序，用来操作服务器。但必须保证服务器已开启才能连接上；
D:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld.exe：服务器程序，必须先启动它，客户端才能连接上服务器；
D:\Program Files\MySQL\MySQL Server 5.5\bin\my.ini：服务器配置文件；

3、C:\ProgramData\MySQL\MySQL Server 5.5\dat

该目录下的每个目录表示一个数据库，例如该目录下有一个mysql目录，那么说明你的DBMS中有一个名为mysql的database；
在某个数据库目录下回有0--n个扩展名为frm的文件，每个frm文件表示一个table，不要用文本编辑器打开它，它是有DBMS来读写的；

4、my.ini，MySQL最为重要的配置文件

配置MySQL的端口，默认为3306，没有必要时不要修改；
配置字符编码：

l [client]下配置客户端编码：default-character-set=gbk

l [mysqld]下配置服务器编码：character-set-server=utf8

配置二进制数据大小上限：

l 在[mysqld]下配置：max_allowed_packet=8M

2.3、服务器操作

l 开启服务器（必须保证mysql为windows服务）：net start mysql

查看进程表中是否存在：mysqld.exe进程（存在）

l 关闭服务器（必须保证mysql为windows服务）：net stop mysql

查看进程表中是否存在：mysqld.exe进程（不存在）

2.4、客户端操作

打开运行窗口输入“cmd”

1、登录服务器：mysql -uroot -proot -hlocalhost

l -u：后面跟随用户名

l -p：后面跟随密码

l -h：后面跟随数据库服务器IP地址，localhost和127.0.0.1表示本机服务器

2、退出服务器：exit或quit

3、SQL语言概述

3.1、SQL简介

1) 什么是SQL

结构化查询语言（Structured Query Language）。

2) SQL的作用

客户端使用SQL来操作服务器；

启动mysql.exe，连接服务器后，就可以使用sql来操作服务器了；

将来会使用Java程序连接服务器，然后使用sql来操作服务器。

3) SQL标准（例如SQL99，即1999年制定的标准）

由国际标准组织（ISO）制定的，对DBMS的统一操作方式（例如相同的语句可以操作：mysql、Oracle等）。

4) SQL方言

某种DBMS不会只支持SQL标准，而且还会有一些自己独有的语法，被称为方言。例如limit语句只在MySQL中可以使用。

3.2、SQL语法

1) SQL语句可以在单行或多行书写，以分号结尾；

2) 可使用空格和缩进来增强语句的可读性；

3) MySQL不区分大小写，建议使用大写

3.3、SQL语句分类（重点）

1) DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：对数据库或表的结构操作（增、删、改）。

2) DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；

对表的记录进行更新（增、删、改）。

3) DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全等级；

对用户的创建和授权。

4) DQL（Data Query Language）：数据查询语言，用来查询记录（数据）；

主要是对表记录的查询。（重点）

4、SQL操作

4.1、DDL语句

1. 数据库

l 查看所有数据库：SHOW DATABASES

l 切换（选择要操作的）数据库：USE 数据库名

l 创建数据库：CREATE DATABASE [IF NOT EXISTS] mydb1 [CHARSET=utf8]

l 删除数据库：DROP DATABASE [IF EXISTS] mydb1

l 修改数据库编码：ALTER DATABASE mydb1 CHARACTER SET utf8

2、数据类型（列类型）

l int：整型

l double：浮点型，例如double(5,2)表示最多5位，其中必须有2为小数，即最大值为999.99；

l decimal：浮点型，在表示钱方面使用该类型，因为不会出现精度缺失问题；

l char：固定长度字符串类型；char(255)，数据的长度不足指定长度时，会补足到指定长度，一般用于存储固定长度的字符串，例如：身份证号，日期时间等；

l varchar：可变长度字符串类型；varchar(65535)，不会自动补足到指定长度，但是会至少占用1个字节来记录字符长度，一般存储姓名等；

l text(clob)：字符串类型，MySQL独有类型

类型	长度
tinytext	28-1B(256B)
text	216-1B(64K)
mediumtext	224-1B(16M)
longtext	232-1B(4G)

l blob：字节类型；

类型	长度
tinyblob	28-1B(256B)
blob	216-1B(64K)
mediumblob	224-1B(16M)
longblob	232-1B(4G)

l data：日期类型，格式为 yyyy-MM-dd；

l time：时间类型，格式为 hh:mm:ss；

l timestamp：时间戳类型；

3、表

l 创建表：

CREATE TABLE [IF NOT EXISTS] 表名(

列名列类型,

……

列名列类型

);

l 查看当前数据库中所有表名称：SHOW TABLES;

l 查看指定表的创建语句：SHOW CREATE TABLE 表名;

l 查看表结构：DESC 表名;

l 删除表：DROP TABLE 表名;

l 修改表：前缀为 ALTER TABLE 表名

a) 添加列：

ALTER TABLE 表名 ADD(

列名列类型,

列名列类型，

……

);

b) 修改类类型（如果被修改的列已存在数据，那么新的类型可能会影响到已存在数据）：ALTER TABLE 表名 MODIFY 列名列类型;

c) 修改列名：ALTER TABLE 表名 CHANGE 原列名新列名列类型;

d) 删除列：ALTER TABLE 表名 DROP 列名;

e) 修改表名称：ALTER TABLE 原表名 RENAME TO 新表名;

4.2、DML语句

1. 插入数据

l INSERT INTO 表名(列名1,列名2,...) VALUES(值1,值2,...);

在表名后给出要插入的列名，其他没有指定的列等同于插入null值。所以插入记录总是插入一行；

在VALUES后给出列值，值的顺序和个数必须与前面指定的列对应；

在数据库中所有的字符类型，必须使用单引号，不能使用双引号，日期类型也要使用单引号

l INSERT INTO 表名 VALUES(列值1,值2);

没有给出要插入的列，那么表示插入的所有列；

值的个数必须是该表列的个数；

值的顺序，必须与表创建时给出的列的顺序相同。

2. 修改数据

l UPDATE 表名 SET 列名1=列值1,列名2=列值2,... [WHERE 条件]

l 条件（条件可选）：

² 条件必须是一个boolean类型的值或表达式：UPDATE t_person SET gender='男', age=age+1 WHERE sid='1';

² 运算符：=，!=，<>，>，<，>=，<=，BETWEEN...AND，IN(...)，IS NULL，NOT，OR，AND

3. 删除数据

l DELETE FROM 表名 [WHERE 条件];

l TRUNCATE TABLE 表名：TRUNCATE是DDL语句，它是先删除drop该表，再create该表，而且无法回滚。

4.3、DCL语句

1. 创建用户

l CREATE USER 用户名@IP地址 IDENTIFIED BY '密码';

用户只能在指定的IP地址上登录

l CREATE USER 用户名@'%' IDENTIFIED BY '密码';

用户可以在任意IP地址上登录

2. 给用户授权

l GRANT 权限1,……,权限n ON 数据库.* TO 用户名@IP地址;

给用户分派在指定的数据库上的指定权限

例如：GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;

给user1用户分派在mydb1数据库上的CREATE、ALTER、DROP、INSERT、UPDATE、DELETE、SELECT权限

l GRANT ALL ON 数据库.* TO 用户名@IP地址;

给用户分派指定数据库上的所有权限

3. 撤销授权

l REVOKE 权限1,……,权限n ON 数据库.* FROM 用户名@IP地址;

撤销指定用户在指定数据库上的指定权限

例如：REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;

撤销user1用户在mydb1数据库上的CREATE、ALTER、DROP权限

4. 查看权限

l SHOW GRANTS FOR 用户名@IP地址;

查看指定用户的权限

5. 删除用户

l DROP USER 用户名@IP地址;

4.4、DQL语句（重点）

DQL语句只会对数据库查询，不会修改数据。

1. 基本查询

l 字段（列）控制

² 查询所有列

SELECT * FROM 表名;

其中“*”表示所有列

l 查询指定列

SELECT 列1[,列2,……列n] FROM 表名;

l 完全重复的记录只显示一次

当查询结果中的多行记录完全一样时，只显示一行，一般用于查询某个字段中一共有几种类型的值。

SELECT DISTINCT * | 列1[,列2,……,列n] FROM 表名;

例如：SELECT DISTINCT sal FROM emp;

查询员工表的工资，如果存在相同的工资只显示一次

l 列运算

u 数量类型的列可以做加、减、乘、除运算

SELECT *,sal*1.5 FROM emp;

字符串做算术运算时，会被当做0来进行运算，在字符串中+号不代表拼接

u 字符串类型可以做连续运算

SELECT CONCAT('$',sal) FROM emp;

u 转换NULL值

有时需要把NULL转换成其他值，例如com+1000时，如果com列存在NULL值，那么NULL+1000还是NULL，而我们这是希望把NULL当做0来运算。

SELECT IFNULL(comm,0)+1000 FROM emp;

IFNULL(comm,0)：如果comm中存在NULL值，那么当成0来运算；

u 给列起别名

当使用列运算后，查询出的结果集中的列名称不好看，这时我们需要给列名起个别名，这样在结果集中列名就显示别名了。

SELECT IFNULL(comm,0)+1000 AS 奖金 FROM emp;

其中AS可以省略

2. 条件控制

l 条件查询

u 与前面介绍的UPDATE和DELETE语句一样，SELECT语句也可以使用WHERE子句来控制记录。

ü SELECT empno,ename,sal,comm FROM emp WHERE sal > 1000 AND comm IS NOT NULL;

ü SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 20000 AND 30000;

ü SELECT empno,ename,job FROM emp WHERE job IN('经理','董事长');

l 模糊查询

u 当你想查询姓张，并且姓名一共两个字的员工时，就可以使用模糊查询

SELECT * FROM emp WHERE ename LIKE '张_';

模糊查询需要使用运算符：LIKE，其中匹配一个任意字符，注意，只匹配一个字符而不是多个。

上面语句查询的是姓张，名字由两个字组成的员工。

u 下划线“_”可以匹配1个字符，如果要匹配0-n个字符，需要用“%”;

SELECT * FROM emp WHERE ename LIKE ‘%刚’;

查询名字结尾是带“刚”字的员工。

3. 排序查询

l 升序

u SELECT * FROM emp ORDER BY sal ASC;

按sal排序，ASC升序，DESC降序，其中ASC是可以省略的

l 降序

u SELECT * FROM emp ORDER BY comm DESC;

按comm降序排序查询

l 使用多列作为排序条件

u SELECT * FROM emp ORDER BY sal ASC, comm DESC;

使用sal升序排序，如果sal相等，再按照comm降序排序

4. 聚合函数

聚合函数用来做某列的纵向运算

l COUNT()函数

SELECT COUNT(*) FROM emp;

计算emp表中所有列都不为NULL的记录的行数

SELECT COUNT(comm) FROM emp;

计算emp表中comm列不为NULL的记录的行数

l MAX()函数

SELECT MAX(sal) FROM emp;

查询最高工资

l MIN()函数

SELECT MIN(sal) FROM emp;

查询最低工资

l SUM()函数

SELECT SUM(sal) FROM emp;

查询所有员工工资的总和

l AVG()函数

SELECT AVG(sal) FROM emp;

查询平均工资

5. 分组查询

l 分组查询是把记录使用某一列进行分组，然后查询组信息。

例如：查看所有部门的记录数。

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

使用deptno分组，查询部门编号和每个部门的记录数

SELECT job,MAX(sal) FROM emp GROUP BY job;

使用job分组，查询每种工作的最高工资

l 组条件

以部门分组，查询每组记录数，条件为记录数大于3

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 3;

6. LIMIT子句（方言）

l LIMIT用来限定查询结果的起始行，以及总行数。

例如：查询起始行为第5行，一共查询3行记录

SELECT * FROM emp LIMIT 4,3;

其中4表示从第5行开始，其中3表示一共查询3行。即第5/6/7行记录。

l 分页查询中计算起始页的公式：（当前页-1）*每页记录数；

例如：每页显示10条记录，查询第3页

SELECT * FROM emp LIMIT 20，10;

4.5、DQL语句练习

l 查询出部门编号为30的所有员工

select * from emp where deptno=30;

l 查询所有销售员的姓名、编号和部门编号

select ename,empno,deptno from emp where job='销售员';

l 查询奖金高于工资的员工

select ename from emp where comm > sal;

l 查询奖金高于工资60%的员工

select ename from emp where comm > sal*0.6;

l 查询部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料

select * from emp where (deptno = 10 and job='经理') or (deptno=20 and job='销售员');

l 查询部门编号为10中所有经理，部门编号为20中所有销售员，还有既不是经理又不是销售员但其工资大于或等于20000的所有员工资料

select * from emp where (deptno = 10 and job='经理') or (deptno=20 and job='销售员') or (job not in ('经理','销售员') and sal>20000);

l 查询无奖金或奖金低于1000的员工

select * from emp where comm is null or comm<1000;

l 查询名字由三个字组成的员工

select * from emp where ename like '___';

l 查询2000年入职的员工

select * from emp where hiredate like '2000-%';

l 查询所有员工详细信息，用编号升序排序

select * from emp order by empno asc;

l 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序

select * from emp order by sal desc,hiredate asc;

l 查询每个部门的平均工资

select deptno,avg(*) 平均工资 from emp group by deptno;

l 查询每个部门的雇员数量

select deptno,count(*) 数量 from emp group by deptno;

l 查询每种工作的最高工资、最低工资、人数

select job,max(*) 最高工资,min(*) 最低工资,count(*) 人数 from emp group by job;

4、MySQL编码问题（了解）

4.1、查看MySQL数据库编码

SHOW VARIABLES LIKE 'char%';

4.2、编码解释

l character_set_client：MySQL使用该编码来解读客户端发送过来的数据，例如该编码为utf8，那么如果客户端发送过来的数据不是utf8，就会出现乱码；

l character_set_results：MySQL会把数据转换成该编码后，在发送给客户端，例如该编码为utf8，那么如果客户端不使用utf8来解读，就会出现乱码；

4.3、控制台乱码问题

l 插入或修改时出现乱码：

这是因为cmd下默认使用GBK，而character_set_client不是GBK的原因，我们只需让这两个编码相同即可；

因为修改cmd的编码不方便，所以我们去设置character_set_client为GBK即可。

l 查询出的数据为乱码：

这是因为character_set_results不是GBK，而cmd默认使用GBK的原因，我们只需让这两个编码相同即可；

因为修改cmd的编码不方便，所以我们去设置character_set_results为GBK即可。

l 设置变量的语句：

set character_set_client=gbk

set character_set_results=gbk

l 这里需要注意的是，设置变量只对当前连接有效，当退出窗口后，再次登录mysql，还需要再次设置变量。为了一劳永逸，可以在my.ini中设置： default-character-set=gbk

l 指定默认编码

我们在安装MySQL时已经指定了默认编码为utf8，所以我们在创建数据库、创建表时，都无需再次指定编码。为了一劳永逸，可以在my.ini中设置：character-set-server=utf8

5、MySQL备份与恢复

5.1、数据库导出SQL脚本（备份数据库内容）

mysqldump -u用户名 -p密码数据库名>生成的脚本文件路径

例如：mysqldump -uroot -proot mydb1>D:\mydb1.sql

注意，不要分号，不要登录mysql，直接在cmd下运行，生成的脚本文件中不包含create database语句

5.2、执行SQL脚本（恢复数据）

l 第一种方式

mysql -u用户名 -p密码数据库<脚本文件路径

先删除mydb1库，再重新创建mydb1库，然后执行语句：

mysql -uroot -proot mydb1<D:\mydb1.sql

注意，不要使用分号，不要登录数据库，直接在cmd下运行

l 第二种方式

登录mysql

source SQL脚本路径

先删除mydb1库，再重新创建mydb1库，切换到mydb1库，然后执行语句：source c:\mydb1.sql

6、MySQL约束

约束是添加在列上的，用来约束列的。

6.1、主键约束

主键约束的特点有：非空、唯一、被引用

当表的某一列被指定为主键后，该列就不能为空，不能有重复值出现。

1. 创建表时指定主键的两种方式：

l 方法一：

CREATE TABLE stu(

sid CHAR(6) PRIMARY KEY,

snam VARCHAR(20),

age INT,

gender VARCHAR(10)

);

l 方法二：

指定sid列为主键列，即为sid列添加主键约束

CREATE TABLE stu(

sid CHAR(6),

sname VARCHAR(20),

gender VARCHAR(10),

PRIMARY KEY(sid)

);

指定sid列为主键列，即为sid列添加主键约束

修改表时指定主键：ALTER TABLE stu ADD PRIMARY KEY(sid);
删除主键：ALTER TABLE stu DROP PRIMARY KEY;

2. 主键自增长

因为主键列的特点是：必须唯一，不能为空，所以我们通常会指定主键类为整型，然后设置其自动增长，这样可以保证在插入数据时主键列的唯一和非空特性。

l 创建表时指定主键自增长

CREATE TABLE stu(

sid INT PRIMARY KEY ATUO_INCREMENT,

sname VARCHAR(20),

age INT,

gender VARCHAR(10)

);

l 修改表时设置主键自增长：ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

l 修改表时删除主键自增长：ALTER TABLE stu CHANGE sid sid INT;

l 测试主键自增长：

INSERT INTO stu VALUES(NULL,'zhangsan',23,'male');

INSERT INTO stu(sname,age,gender) VALUES('zhangsan',23,'male');

6.2、非空约束

因为某些列不能设置为NULL值，所以可以对列添加非空约束。

例如：

CREATE TABLE stu(

sid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20) NOT NULL,

age INT,

gender VARCHAR(10)

);

对sname列设置非空约束

6.3、唯一约束

车库某些列不能设置重复的值，所以可以对列添加唯一约束。

例如：

CREATE TABLE stu(

sid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20) NOT NULL UNIQUE,

age INT,

gender VARCHAR(10)

);

对sname列设置了非空约束

6.4、概念模型

当我们要完成一个软件系统时，需要把系统中的实体抽取出来，形成概念模型。

例如部门、员工都是系统中的实体。概念模型中的实体最终会成为Java中的类、数据库中的表。

对象模型就是java中的class，关系（数据）模型就是指数据库中的表。

在java中的三种关系是：is a、has a、use a

实体之间还存在着关系，关系有三种：

l 一对多：例如每个员工都从属于一个部门，而一个部门可以有多个员工，其中员工是多方，部门是一方；

l 一对一：列如夫妻关系，只能是一夫一妻；

l 多对多：例如老师与学生的关系，一个老师可以有多个学生，一个学生可以有多个老师。

概念模型在Java中成为实体类（JavaBean）

类就使用成员变量来完成关系，一般都是双向关联，在多对一双向中关联，即部门中关联员工，员工中也关联部门。

Java的一对多

class Employee{//多方关联一方 ... private Department department; } class Deparment{//一方关联多方 ... private List<Employee> empList; }

Java的一对一

class Husband{ ... private Wife wife; } class Wife{ ... private Husband husband; }

Java多对多

class Student{ ... private List<Teacher> teaList; } class Teacher{ ... private List<Student> stuList; }

对象模型：可以双向关联，而且引用的是对象，而不是一个主键；

关系模型：只能多方引用一方，而且引用的只是主键，而不是一整行记录。

6.5、外键约束

l 外键必须是另一张表（或本表）的主键的值，外键要引用主键；

l 外键可以重复；

l 外键可以为空；

l 一张表中可以有多个外键；

数据库多对一关系

从表中的外键关联主表的主键

创建主表

CREATE TABLE dept( deptno INT PRIMARY KEY AUTO_INCREMENT, dname VARCHAR(50) );

创建从表

CREATE TABLE emp( empno INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(50), dno INT, CONSTRAINT fk_emp_dept FOREIGN KEY(dno) REFERENCES dept(deptno) );

数据库一对一关系

在表中建立一对一关系比较特殊，需要让从表的主键，即是主键又是外键。

创建主表

CREATE TABLE hasband( hid INT PRIMARY KEY AUTO_INCREMENT, hname VARCHAR(50) );

创建从表

CREATE TABLE wife( wid INT PRIMARY KEY AUTO_INCREMENT, wname VARCHAR(50), CONSTRAINT fk_wife_hasband FOREIGN KEY(wid) REFERENCES hasband(hid) );

数据库多对多关系

在表中建立多对多关系需要使用中间表，即需要三张表，在中间表中使用两个外键，分别引用其他两个表的主键。

学生表

CREATE TABLE student( sid INT PRIMARY KEY AUTO_INCREMENT, ... );

老师表

CREATE TABLE teacher( tid INT PRIMARY KEY AUTO_INCREMENT, ... );

中间表

CREATE TABLE stu_tea( sid INT, tid INT, CONSTRAINT fk_stu_tea_sid FOREIGN KEY(sid) REFERENCES student(sid), CONSTRAINT fk_stu_tea_tid FOREIGN KEY(tid) REFERENCES teacher(tid) );

7、多表查询

7.1、多表查询的分类

l 合并结果集

l 连接查询

l 子查询

7.2、合并结果集

要合并的表的结果集，列类型和列数相同

UNION：去除重复行

UNION ALL：不去除重复行

例如：

SELECT * FROM ab

UNION ALL

SELECT * FROM cd;

7.3、连接查询

分类

l 内连接

l 外连接

² 左外连接

² 右外连接

² 全外连接（MySQL不支持）

l 自然连接

内连接

l 直接查询两张表会得到笛卡尔集表

l 方言：SELECT * FROM 表1 别名1,表2 别名2 WHERE 别名1.xx=别名2.xx

l 标准：SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

l 自然：SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2

l 内连接查询出的所有记录都满足条件

外连接

l 左外：SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

² 左外即左表为主，左表记录无论是否满足条件都会查询出来，而右表只有满足条件才能出来。左表不满足条件的记录，右表部分都为NULL

l 左外自然：SELECT * FROM 表1 别名1 NATURAL LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

l 右外：SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

² 右表记录无论是否满足条件都会查询出来，而左表只有满足条件才能查询出来。右表不满足条件的记录，其左表部分都为NULL

l 右外自然：SELECT * FROM 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

l 全连接：

在其他数据库中使用FULL OUTER JOIN实现全连接，但是MySQL不支持，我们可以使用UNION来完成全连接；

在左外查询语句和右外查询语句之间加上UNION，得到左外和右外的结果集，可以间接实现全连接。

7.4、子查询

子查询即查询中包含了查询

出现的位置

l WHERE后作为条件存在

例如：查询工资最高的员工的详细信息

SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);

l FROM后作为表存在（多行多列）

查询30号部门员工的编号和姓名

SELECT e.empno,e.ename FROM (SELECT * FROM emp WHERE deptno=30) e;

条件

l 单行单列：SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件)；

例如：查询大于平均工资的所有员工信息

SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);

l 多行单列：SELECT * FROM 表1 别名1 WHERE 列1 [IN、ALL、ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件);

例如：查询大于30部门所有人工资的员工信息

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30);

l 单行多列：SELECT * FROM 表1 别名1 WHERE (列1，列2) IN (SELECT 列1,列2 FROM 表2 别名2 WHERE 条件);

例如：查询公司里和某员工在用一个部门并且岗位和工资都相同的员工信息

SELECT * FROM emp WHERE (job,deptno,sal) IN (SELECT job,deptno,sal FROM emp WHERE ename='张三');

l 多行多列：SELECT * FROM 表1 别名1,(SELECT ...) 别名2 WHERE 条件;

二、Oracle数据库

1、Oracle概述

1.1、数据库的概念

数据：在数据库领域看来，数据是存储的基本单位，包含文本、图片，视频，音频等；
数据库：就是数据仓库，存储数据的地方，特指计算机设备中的硬盘，以二进制压缩文本的形式存放。该文件不能直接操作，必须有各数据库公司提供的工具方可操作，该文件的格式是每个数据库公司定义的，不是统一规则。
数据库对象：在Oracle中，例如：表、视图、索引、函数、过程、触发器……
关系型数据库：简单来说，以行列结构的形式，将数据库中的信息表示出来的对象，即二维表。
常见的关系型数据库：Oracle&MySQL/DB2(IBM)/SQLServer……

1.2、Oracle数据库服务器组成

实例：理解为对象，看不见

数据库：理解为类，看得见

使用实例（语句）操作数据库

1.3、Oracle服务器与orcl数据库的关系

一个Oracle数据库服务器中包含多个数据库，例如：orcl、orm、oa、bbs、erp等，在oradate目录下，有多少个文件夹，就有多少个数据库。我们向数据库中存储的所有数据，最终都会存放在对应的库*.DBF文件中，以二进制压缩形式存放

1.4、用户登陆

超级管理员进入：

sqlplus / as sysdba

普通用户进入：

sqlplus scott/tiger

使用超级管理员为scott用户解锁和设置密码：

解锁用户：alter user scott account unlock;

解锁后使用scott用户登陆：connect scott/tiger;

设置密码：alter user scott identified by tiger;

使用普通用户修改密码使用password命令

退出工具：exit

1.5、OracleSQL和Oracle的关系

第四代语言：SQL（结构化查询语言，面向关系的）

第一代：机器语言

第二代：汇编语言

第三代：C/C++/C#/Java/VB……

第四代：SQL

SQL标准的四大分类：

DML（数据操纵语言）：select、insert、update、delete……

DDL（数据定义语言）：create table、alter table、drop table、truncate table……

DCL（数据控制语言）：grant 权限 to scott、revoke 权限 from scott……

事务控制语言：commit、rollback、rollback tosavepoint……

1.6、用户与表空间

系统用户：sys、system sys要以管理员的角色登陆

sysman 操作企业管理器

scott

登陆：[username/password] [@server] [as sysdba/sysoper]

切换用户：connect sys/test sysdba;

查看登陆用户：show user

数据字典：desc dba_users

启用scott用户：alter user scott account unlock;

设置密码：alter user scott identified by tiger;

查看表空间：dba_tablespaces/user_tablespaces

1.7、卸载Oracle

1．开始－>设置－>控制面板－>管理工具－>服务

停止所有Oracle服务。（运行命令：services.msc）

2．找到：C:\app\mac\product\11.2.0\dbhome_1\deinstall

执行：deinstall.bat文件，按照提示操作

3．删除残留文件夹

4．运行regedit，分别删除以下位置的Oracle入口

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\

5．删除环境变量CLASSPATH和PATH中有关Oracle的路径

6．在C盘搜索Oracle，删除相关文件

7．重启计算机

2、Oracle实操

补充

以下代码是对emp表进行显示做设置

col empno for 9999;

col ename for a10;

col job for a10;

col mgr for 9999;

col hiredate for a12;

col sal for 9999;

col comm for 9999;

col deptno for 99;

set pagesize 20;

col tname for a20;

set pagesize 80;

2.1、查询

查看当前用户

show user;

查询scott用户下所有的表：

select * from tab;

查看表结构：

desc 表名;

查询某表的所有数据：

select * from 表名;

或

select 字段名,字段名…… from 表名;

*号是通配符，表示该表中的所有字段，但*号不能和具体的字段一起使用，在SQL语句中，大小写不敏感，但建议大写。

别名

可以使用别名查询，使用别名后显示的字段名为别名，

select empno "编号",ename "姓名”… from emp;

字段名和别名中间可以使用AS或as，使用as之后，别名可以带引号也可以不带引号，如果不带引号，别名中不能有空格，单引号表示字符串类型或日期类型；

设置表样式，设置显示宽度：

a) 字符型、日期型用a表示字符

column 列名 format a12; //a12表示占12个字符

b) 数值型用9表示数字

column 列名 format 9999; //四个9表示占4个字符

如果超过了设置的宽度会出现#号

c) 设置一页显示的记录条数

set pagesize 80;

每次登陆为一次会话，exit命令为退出会话，当退出本次会话后，设置的格式都会被清除；

使用/杠，执行最近的一次SQL语句
清屏 host cls;
查询表中不重复的记录

select distinct 列名 from 表名;

查询时对字段做算术运算

select ename,sal,sal*12 "年薪" from emp; //年薪为别名

查询时字段为空

如果结果为null，在SQLplus客户端中是不显示的，解决null不显示的问题，使用NVL()函数，NVL(a,b)：如果a为null，则用b代替；如果a为非null，则显示a的值，对员工表计算年收入时，可以使用以下语句：

select enamel “姓名”,sal*12+NVL(comm,0) “年收入” from emp;

使用dual哑表或者伪表，可以使用字符串连接符号||，在Oracle中必须使用from

select ‘hello’ || ‘world’ “结果” from dual;

显示如下格式信息：XXX的薪水是XX美元

select ename || ‘的薪水是’ || sal || ‘美元’ from emp;

使用sysdate显示系统当前时间，在默认情况下，oracle只显示日期，而不显示时间，格式：04-2月-15

select sysdate from dual; 或

select sysdate "日期" from dual;

使用spool命令，保存SQL语句（和输出结果）到硬盘的指定位置，并创建sql文件

spool e:/oracle-day01.sql;

使用spool off命令，保存SQL语句到硬盘文件e:/oracle-day01.sql。

使用@命令，将硬盘文件，如e:/crm.sql，读到orcl实例中，并执行文件中的SQL语句

@ e:/crm.sql;

使用 “--” 符号，设置单行注释

--select * from emp;

使用/**/符号，设置多行注释

select * from

emp; */

SQL语句的特点

a) 是SQL92/99的ANSI官方标准，只要按照该标准来写，在任何的关系型数据库中都可以直接执行；

b) SQL语句的关键字不能简写，例如：select、where、from等；

c) 大小写不敏感，提倡大写；

d) 能够对表数据进行增删改查操作；

e) 必须以分号结束；

f) 通常称作语句；

SQLPLUS命令的特点

a) 是oracle自带的一款工具，在该工具中执行的命令叫做SQLPLUS命令；

b) SQLPLUS工具的命令中关键字可以简写，例如：col enamel for a10;

c) 大小写不敏感，提倡大写；

d) 不能对表数据进行增删改查操作，只能完成显示格式控制，例如：设置宽度、清屏、记录执行结果等；

e) 可以不用分号结束，也可以用分号结束，建议都使用分号；

f) 通常称作命令，是SQLPLUS工具中的命令；

2.2、where子句

查询emp表中20号部门的员工信息

select * from emp where deptno = 20;

查询姓名是SMITH的员工，字符串使用’’，内容大小写敏感

select * from emp where ename = ‘SMITH’;

查询1980年12月17日入职的员工，注意oracle默认日期格式（dd-mm-YY表示2位的年份）

select * from emp where hiredate = ’17-12月-80’;

查询工资大于1500的员工

select * from emp where sal > 1500;

查询工资不等于1500的员工【!=或<>】

select * from emp where sal <> 1500;

查询薪水在1300到1600之间的员工，包括1300和1600

select * from emp where (sal>=1300) and (sal<=1600); 或

select * from emp where sal between 1300 and 1600;

查询薪水不在1300到1600之间的员工，不包括1300和1600

select * from emp where sal not between 1300 and 1600;

查询入职时间在“1981-2-20”到“1982-1-23”之间的员工

select * from emp where hiredate between ’20-2月-81’ and ’23-2月-82’; //日期小的在前，数字小的在前

查询20号或30号部门的员工，例如：根据ID号，选中的员工，批量删除

select * from emp where (deptno=20) or (deptno=30); 或

select * from emp where deptno in (20,30); //当条件不存在时不出错

// in中如果是字符串类型的，只能填字符串类型的；如果是数字类型，可以填写数字类型，也可以填写能够转为数字的类型，如‘30’

查询不是20号或30号部门的员工

select * from emp where deptno not in (20,30);

查询姓名以大写字母S开头的员工，使用%表示0个，1个或多个字符

select * from emp where ename like ‘S%’; //不使用%等价于=精确查询

//精确查询用=符号，模糊查询用like

查询姓名以大写字母N结束的员工

select * from emp where ename like ‘%N’;

查询姓名第一个字母是T，最后一个字母是R的员工

select * from emp where ename like ‘T%R’;

查询姓名是4个字符的员工，且第二个字符是I，使用_只能表示1个字符，不能表示0个或多个字符

select * from emp where ename like ‘_ I _ _’;

插入一条姓名为’T_IM’的员工，薪水1200

insert into emp (empno,ename) values (1111,’T_IM’);

查询员工姓名中含有‘_’的员工，使用\转义符，让其后的字符回归本来的意思【like ‘%\_%’ escape ‘\’】

select * from emp where ename like '%\_%' escape '\'; //不一定使用斜杠

插入一个姓名叫 ’ 的员工

insert into emp (empno,ename) values (2222,''''); //两个单引号表示1个单引号

插入一个姓名叫 ‘’ 的员工

insert into emp (empno,ename) values (3333,'''''');

查询所有员工信息，使用%或%%

select * from emp ;

select * from emp where ename like ‘%’;

select * from emp where ename like ‘%_%’;

查询佣金为null的员工

select * from emp where comm is null;

//null不能参与等号运算，null能参与number/date/varchar2类型运算

查询佣金为非null的员工

select * from emp where comm is not null;

查询无佣金且工资大于1500的员工

select * from emp where (comm is null) and (sal > 1500);

查询工资是1500或3000或5000的员工

select * from emp where sal in (1500,3000,5000);

查询职位是“MANAGER”或职位不是“ANALYST”的员工（方式一，使用!=或<>）

select * from emp where (job=’MANAGER’) or (job<>’ANALYST’); //性能优于方式二

查询职位是“MANAGER”或职位不是“ANALYST”的员工（方式二，使用not）

select * from emp where (job=’MANAGER’) or (not(job=’ANALYST’));

2.3、order by子句

查询员工信息（编号，姓名，月薪，年薪），按月薪升序排序，如果月薪相同，按oracle内置的校验规则排序

select empno,ename,sal,sal*12 from emp order by sal asc; 或

select empno,ename,sal,sal*12 from emp order by sal; //默认是升序排序

查询员工信息（编号，姓名，月薪，年薪），按月薪降序排序

select empno,ename,sal,sal*12 from emp order by sal desc;

查询员工信息，按入职日期降序排序，使用列名

select empno,ename,hiredate,sal*12 "年薪"

from emp order by hiredate desc;

order by后面可以跟列名、别名、表达式、列号（从1开始，在select子句中的列号）

select empno,ename,hiredate,sal*12 "年薪" from emp order by “年薪” desc;

select empno,ename,hiredate,sal*12 "年薪" from emp order by sal*12 desc;

select empno,ename,hiredate,sal*12 "年薪" from emp order by 4 desc;

查询员工信息，按佣金升序或降序排列，null值看成最大值

select * from emp order by comm desc;

查询员工信息，对有佣金的员工，按佣金降序排列，当order by和where同时出现时，order by在最后

select * from emp where comm is not null order by comm desc;

查询员工信息，按工资降序排列，相同工资的员工再按入职时间降序排列

select * from emp order by sal desc,hiredate desc;

查询20号部门，且工资大于1500，按入职时间降序排列

select * from emp where (deptno=20) and (sal>1500)

order by hiredate desc;

2.4、单行函数

单行函数：只有一个参数输入，只有一个结果输出。

多行函数/分组函数：可以有多个参数输入，只有一个结果输出。

lower/upper/initcap函数，使用dual哑表

select lower(‘HELLO’) from dual; //转小写

select upper(‘hello’) from dual; //转大写

select initcap(‘hello’) from dual; //首字母大写

concat/substr函数

select concat(‘hello’,’world’) from dual; //连接字符串，只能有两个参数，连接多个使用管道符号

select ‘hello’ || ‘你好’ || ‘世界’ from dual; 或

select concat(‘hello’,concat(‘你好’,’世界’)) from dual;

select substr(‘hello你好’,5,3) from dual; //从第5个字符向后截取3个字符，从1开始，表示字符，不论中英文

length/lengthb函数

select length(‘hello你好’) from dual; //返回字符串的长度

select lengthb(‘hello你好’) from dual; //返回字符串的字节长度编码方式为UTF8/GBK，一个中文占3/2个字节，一个英文占一个字节

instr/lpad/rpad函数，

select instr(‘helloworld’,’o’) from dual; //字符o从左向右找第一次出现的位置，从1开始，大小写敏感，找不到返回0

select lpad(‘hello’,10,’#’) from dual; //输出长度为10，不足10位，向hello左边补充#，长度小于字符串长度，输出指定长度字符串（截取后的）

select rpad(‘hello’,10,’#’) from dual; //向右边补充#

trim/replace函数

select trim(‘H’ from ‘hello’) from dual; //清除字符串两边的H，返回清除后的字符串，大小写敏感

select replace(‘hello’,’l’,’L’) from dual; //把字符串中的l替换为L

round/trunc/mod函数作用于数值型

select round(3.1415,3) from dual; // 四舍五入保留3位小数

select trunc(3.1415,3) from dual; // 截取3位小数

select mod() from dual; //取余

round作用于日期型（month）

select round(sysdate,’month’) from dual; //四舍五入到月，15日之前日为1,15日之后，月进1，日为1

round作用于日期型（year）

select round(sysdate,’year’) from dual; //四舍五入到年，6月之前，月为1，日为1,6月之后，年进1，月为1，日为1

测试trunc作用于日期型（month）

select trunc(sysdate,’month’) from dual; //截取到月，日为1

测试trunc作用于日期型（year）

select trunc(sysdate,’year’) from dual; //截取到年，月为1，日为1

显示昨天，今天，明天的日期，日期类型 +/- 数值 = 日期类型

select sysdate-1 “昨天”,sysdate “今天”,sysdate+1 “明天” from dual;

以年和月形式显示员工近似工龄，日期-日期=数值

select ename “姓名”,sysdate – hiredate “入职天数” from emp;

select ename "姓名",round(sysdate - hiredate) "入职天数" from emp;

select ename "姓名",round(sysdate - hiredate,0) "入职天数" from emp;

使用months_between函数，精确计算到年底还有多少个月

select months_between(’31-12月-17’,sysdate) from dual; //大日期在前

使用months_between函数，以精确月形式显示员工工龄

select ename “姓名”,

months_between(sysdate,hiredate) “入职月数” from emp;

测试add_months函数，下个月今天是多少号

select add_months(sysdate,1) from dual;

测试add_months函数，上个月今天是多少号

select add_months(sysdate,-1) from dual;

测试next_day函数

a) 从今天开始算，下一个星期三是多少号

select next_day(sysdate,’星期三’) from dual;

b) 从今天开始算，下下个星期三是多少号

select next_day(next_day(sysdate,’星期三’),’星期三’) from dual;

c) 从今天开始算，下一个星期三的下一个星期日是多少号

select next_day(next_day(sysdate,’星期三’),’星期日’) from dual;

测试last_day函数

d) 本月最后一天是多少号

select last_day(sysdate) from dual;

e) 本月倒数第二天是多少号

select last_day(sysdate)-1 from dual;

f) 下一个月最后一天是多少号

select last_day(add_months(sysdate,1)) from dual;

g) 上一个月最后一天是多少号

select last_day(add_months(sysdate,-1)) from dual;

注意：

日期 - 日期 = 天数

日期 +/- 天数 = 日期

2.5、三大类型转换

隐式转换：

varchar2 à number，例如：’123’ à 123

varchar2 à date，例如：’25-4月-15’ à ’25-4月-15’

number à varchar2，例如：123 à ‘123’

date à varchar2，例如：’25-4月-15’ à ’25-4月-15’

oracle如何隐式转换：

a) =号两边的类型是否相同

b) 如果=号两边的类型不同，尝试的去做转换

c) 在转换时，要确保合法合理，否则转换会失败，例如：12月不会有32天，一年中不会有13月

查询1980年12月17日入职的员工（方式一：日期隐式转换）

select * from emp where hiredate = ’17-12月-80’;

日期格式的元素：

yyyy 表示年份

mm 表示月份，08

month 表示月的全称，8月

day 表示星期几

dd 表示日，02

时间格式 HH24:MI:SS 24小时制，12小时制在后面添加 AM/PM

使用to_char（日期，’格”常量”式’）函数将日期转为字符串，显示如下格式：2015年04月25日星期六

select to_char(sysdate,'yyyy"年"mm"月"dd"日" day') from dual;

使用to_char(日期，‘格式’)函数将日期转为字符串，显示格式：2015-04-25今天是星期六 15:15:15

select to_char(sysdate,'yyyy-mm-dd"今天是"day hh24:mi:ss') from dual; 或

select to_char(sysdate,'yyyy-mm-dd"今天是"day hh12:mi:ss pm') from dual;

使用to_char(数值，‘格式’)函数将数值转为字符串，显示格式：$1,234.00

select to_char(1234,'$9,999.00') from dual;

使用to_char(数值，‘格式’)函数将数值转为字符串，显示格式：￥1234.00

select to_char(1234,'L9999.00') from dual;

使用to_date(‘字符串’，‘格式’)函数，查询1980年12月17日入职的员工（方式二：日期显示转换）

select * from emp where

hiredate=to_date('1980年12月17日','yyyy"年"mm"月"dd"日"');

或

select * from emp where hiredate=to_date('1980-12-17','yyyy"-"mm"-"dd');

使用to_number(‘字符串’)函数将字符串‘123’转为数字123

select to_number('123')+123 from dual; //显示转换，结果为246

select '123'+123 from dual; //隐式转换，结果为246

select ‘123’ || 123 from dual; // ||符号为拼接符，结果为123123

2.6、通用函数和条件判断

使用NVL(a,b)通用函数，统计员工年收入，NVL()作用于任何类型，即(number/varchar2/date)，通用函数：参数类型可以是number或varchar2或date类型

select ename,sal*12+NVL(comm,0) from emp;

使用NVL2(a,b,c)通用函数，如果a不为NULL，取b值，否则取c值，统计员工年收入

select ename,sal*12+NVL2(comm,comm,0) from emp;

使用NULLIF(a,b)通用函数，在类型一致的情况下，如果a与b相同，返回NULL，否则返回a，比较10和10.0是否相同

select NULLIF(10,10.0) from dual; //结果为null，在SQLPLUS中NULL不显示

使用SQL99标准通用语法中的case表达式，将职位是分析员的，工资+1000；职位是经理的，工资+800；职位是其它的，工资+400

case 字段

when 条件 then 表达式1

when 条件 then 表达式2

else 表达式n

end

select ename “姓名”,job “职位”,sal “原工资”,

case job

when ‘ANALYST’ then sal+1000

when ‘MANAGER’ then sal+800

else sal+400

end “涨后工资”

from emp;

使用oracle专用语法中的decode()函数，职位是分析员的，工资+1000；职位是经理的，工资+800；职位是其它的，工资+400

decode(字段,条件1,表达式1,条件2,表达式2,……表达式n)

select ename “姓名”,job “职位”,sal “原工资”,

decode(job,’ANALYST’,sal+1000,’MANAGER’,sal+800,sal+400)

“涨后工资”

from emp;

2.7、多行函数

统计emp表中员工总人数

select count(*) from emp; //*号适用于表字段较少的情况下，如果字段多，扫描效率低，建议使用非NULL的字段，一般用主键

统计公司有多少个不重复的部门

select count(distinct deptno) from emp;

统计有佣金的员工人数

select count(comm) from emp; //多行函数不统计NULL值

员工总工资，平均工资，四舍五入，保留小数点后0位

select sum(sal) "总工资",avg(sal) "平均工资" from emp; //未保留指定小数

select sum(sal) "总工资",round(avg(sal),0) "平均工资" from emp;//保留小数

查询员工表中最高工资，最低工资

select max(sal) "最高工资",min(sal) "最低工资" from emp;

入职最早，入职最晚员工

select min(hiredate) "入职最早",max(hiredate) "入职最晚" from emp;

2.8、分组函数

按部门求出该部门平均工资，且平均工资取整数，采用截断

select deptno "部门编号",trunc(avg(sal),0) "部门平均工资"

from emp group by deptno;

（继续）查询部门平均工资大于2000的部门

select deptno “部门编号”,trunc(avg(sal),0) “部门平均工资”

from emp group by deptno having trunc(avg(sal),0) > 2000;

（继续）按部门平均工资降序排列

select deptno "部门编号",trunc(avg(sal),0) "部门平均工资"

from emp group by deptno

having trunc(avg(sal),0) > 2000

order by 2 desc;

除10号部门外，查询部门平均工资大于2000元的部门，方式一【having depton <> 10】

select deptno,avg(sal) from emp group by deptno having deptno <> 10;// 先分组后排出，执行效率低

除10号部门外，查询部门平均工资大于2000元的部门，方式二【where deptno <> 10】

select deptno,avg(sal) from emp where deptno <> 10 group by deptno; //先排除后分组，执行效率高

显示部门平均工资的最大值

select max(avg(sal)) "结果" from emp group by deptno;

思考：显示部门平均工资最大的值和该部门的编号
总结：

group by子句的细节：

a) 在select子句中出现的非多行函数的所有列，必须出现在group by子句中；

b) 在group by子句中出现的所有列，可出现可不出现在select子句中；

where和having的区别：

where：

a) 行过滤器；

b) 针对原始的记录；

c) 跟在from后面；

d) where可省；

e) 先执行；

having：

a) 组过滤器；

b) 针对分组后的记录；

c) 跟在group by后面；

d) having可省；

e) 后执行；

单引号出现的地方：

a) 字符串，例如：’hello’

b) 日期型，例如：’25-3月-15’

c) to_char/to_date(日期,’yyyy-mm-dd day’)

双引号出现的地方：

a) 列别名，例如：select ename “姓名” from emp;

b) to_char/to_date(日期,’yyyy”年”mm”月”dd”日”’);

2.9、多表查询

员工表emp和部门表的笛卡尔集（笛卡尔集表=列数之和，行数之积，笛卡尔集表内中有些数据是不符合要求的）

select * from emp,dept; 或

select emp.*,dept.* from emp,dept; 或

select emp.ename,dept.dname from emp,dept;

使用等值连接/内连接（只能使用=号），显示员工的编号，姓名，部门名

select emp.empno,emp.ename,dept.dname,dept.deptno

from emp,dept where emp.deptno = dept.deptno;

使用非等值连接/内连接查询（不能使用=号，其他符号可以，例如：>=，<=，<>，between and等），显示员工的编号，姓名，月薪，工资级别

select e.empno "编号",e.ename "姓名",e.sal "月薪",s.grade "工资级别"

from emp e,salgrade s where e.sal between s.losal and s.hisal;

// 使用表别名简化

使用外连接，按部门10,20,30,40号，统计各部门员工人数，要求显示部门号，部门名，人数

a) 左外连接查询：

select dept.deptno "部门号",dept.dname "部门名",count(emp.empno) "人数"

from dept,emp

where dept.deptno = emp.deptno (+)

group by dept.deptno,dept.dname;

a) 右外连接查询：

select dept.deptno "部门号",dept.dname "部门名",count(emp.empno) "人数"

from dept,emp

where emp.deptno (+) = dept.deptno

group by dept.deptno,dept.dname;

使用左外连接，按部门10,20,30,40号，统计各部门员工人数，要求显示部门号，部门名，人数，且按人数降序排列

select dept.deptno "部门号",dept.dname "部门名",count(emp.empno) "人数"

from dept,emp

where dept.deptno = emp.deptno (+)

group by dept.deptno,dept.dname

order by count(emp.empno) desc;

总结：

a) 等值连接/非等值连接/内连接：只会查询出多张表中，根据某个字段匹配，符合条件的记录，不符合条件的记录是不会存在的；

b) 外连接查询：既能查询出符合条件的记录，也能根据一方强行将另一方查询出来

自连接查询（针对单张表查询）

a) 使用自连接，显示”SMITH的上级是FORD”这种格式

select users.ename || '的上级是' || boss.ename

from emp users,emp boss

where users.mgr = boss.empno;

b) 基于上述问题，将KING的上级是” ”显示出来

select users.ename || '的上级是' || boss.ename

from emp users,emp boss

where users.mgr = boss.empno (+);

2.10、子查询

子查询的作用：查询条件未知的事物

查询条件已知问题，例如：查询工资为800的员工信息

查询条件未知的问题，例如：查询工资为20号部门平均工资的员工信息

一个条件未知的问题，可以分解为多个条件已知的问题

查询工资比WARD高的员工信息

select * from emp

where sal > (

select sal from emp where ename = 'WARD'

);

查询部门名为‘SALES’的员工信息（使用子查询）

select * from emp

where deptno = (

select deptno from dept where dname = 'SALES'

);

子查询细节：

ü 子查询与父查询可以针对同一张表；

ü 子查询和父查询可以针对不同张表；

ü 子查询与父查询在传统参数时，数量要相同

ü 父查询的条件要与子查询的结果，在参数数量、类型、含义上要相同

查询部门名为‘SALES’的员工信息（多表查询）

select emp.* from dept,emp

where (dept.deptno=emp.deptno) and (dept.dname='SALES');

查询每个员工编号，姓名，部门名，工资等级（三表查询，三张表无外键关联）

select emp.empno "编号",emp.ename "姓名",

dept.dname "部门名",salgrade.grade "工资等级"

from emp,dept,salgrade

where (emp.deptno = dept.deptno)

and (emp.sal between salgrade.losal and salgrade.hisal);

查询工资最低的员工信息（单行子查询，使用=号）

select * from emp where sal = (

select min(sal) from emp

);

单行子查询：子查询只返回一个结果，例如800，父查询用=/<>/>=/<=来比较

多行子查询：子查询会返回多个结果，例如20,30，父查询用in/any/all来比较

查询部门名为‘ACCOUNTING’或‘SALES’的员工信息（多行子查询，使用in关键字）

select * from emp

where deptno in(

select deptno from dept where dname in ('ACCOUNTING','SALES')

);

查询工资比20号部门【任意any】一个员工工资【低<】的员工信息（多行子查询，使用any关键字）

select * from emp

where sal <any (

select sal from emp where deptno = 20

); //在oracle看来，<any就等同于<集合中最大的值

查询工资比30号部门【所有all】员工【低<】的员工信息（多行子查询，使用all关键字）

select * from emp

where sal <all (

select sal from emp where deptno = 30

);

当多表查询、子查询同时能解决问题时，优先选择多表查询，但是在某些情况下，只能用子查询，例如Oracle分页。

2.11、集合查询

使用并集运算，查询20号部门或30号部门的员工信息

select * from emp where deptno = 20

union

select * from emp where deptno = 30

//union两个集合中，如果都有相同的，取其一；union all两个集合中，如果都有相同的，都取

Oracle执行时间操作

set time on 打开时间

set time off 关闭时间

set timing on 显示当前命令执行的时间

set timing off 关闭执行命令时间的显示

使用交集运算[intersect]，查询工资在1000-2000和1500-2500之间的员工信息（方式一）

select * from emp where sal between 1000 and 2000

intersect

select * from emp where sal between 1500 and 2500;

用where行过滤，查询工资在1000-2000和1500-2500之间的员工信息（方式二）

select * from emp

where (sal between 1000 and 2000)

and (sal between 1500 and 2500);

使用差集运算[minus]，查询工资在1000-2000，但不在1500-2500之间的员工信息（方式一）

select * from emp where sal between 1000 and 2000

minus

select * from emp where sal between 1500 and 2500;

使用where行过滤，查询工资在1000-2000，但不在1500-2500之间的员工信息（方式二）

select * from emp

where (sal between 1000 and 2000) and

(sal not between 1500 and 2500);

集合查询的细节：

a) 集合操作时，必须确保集合列数是相等；

b) 集合操作时，必须确保集合列类型对应相等

c) A union B union C = C union B union A，如果有重复，去重复

d) 当多个集合操作时，结果的列名由第一个集合列名决定

当多表查询、子查询、集合查询都能完成同样的任务时，优先选择：

多表查询 à 子查询 à 集合查询（优先级高 à 低）

2.12、Oracle分页

MySQL分页语法：

select * from 表名 limit 0,2;

查询users表中前两条记录

select * from users limit 0,2; 或

select * from users limit 2;

//0表示第一条记录的索引号，索引号从0开始

2表示最多选取2个记录

查询出users前三条记录

select * from users limit 0,3; 或

select * from users limit 3;

B . Oracle分页

a) 在Oracle中使用rownum，是oracle中特有的关键字

b) rownum与表在一起，表亡它亡，表在它在

c) rownum在默认情况下，从表中是查不出来的

d) 只有在select子句中，明确写出rownum才能显示出来

e) rownum是number类型，且唯一连续

f) rownum最小值是1，最大值与记录条数相同

g) rownum也能参与关系运算

rownum = 1 有值

rownum < 5 有值

rownum <= 5 有值

rownum > 2 无值

rownum >= 2 无值

rownum <> 2 有值与 rownum < 2 相同

rownum = 2 无值

h) 基于rownum的特性，我们通常rownum只用于<或<=关系运算

显示emp表中3-8条记录（方式一：使用集合减运算）

select rownum,emp.* from emp where rownum <= 8

minus

select rownum,emp.* from emp where rownum <= 2;

显示emp表中3-8条记录（方式二：使用子查询，在from子句中使用，重点）

select * from

(select rownum id,emp.* from emp where rownum <= 8)

where id > 2; // id是子查询中的别名，不可以加””双引号

或

select users.* from

(select rownum id,emp.* from emp where rownum <= 8) users

where id > 2; //users是表的别名

显示emp表中5-9条记录

select * from

(select rownum id,emp.* from emp where rownum <= 9)

where id > 4;

2.13、操作表

创建用户表users（id整型/name字符串/birthday日期/sal整型，默认今天）

create table users(

id number(5) primary key, //主键

name varchar2(8) not null unique, //非空，唯一

sal number(6,2) not null,

birthday date default sysdate // 默认

);

删除表，进入回收站

drop table users;

查询回收站中的对象

show recyclebin;

闪回，即将回收站还原

flashback table 表名 to before drop;

flashback table 表名 to before drop rename to 新表名;

彻底删除users表

drop table users purge;

清空回收站

purge recyclebin;

为emp表增加image列，alter table 表名 add 列名类型(宽度)

修改ename列的长度为20个字符，alter table 表名 modify 列名类型(宽度)

删除image 列，alter table 表名 drop column 列名

重名列名ename为username，alter table 表名 rename column 原列名 to 新列名

将emp表重命名emps，rename 原表名 to 新表名

注意：修改表时，不会影响表中原有的数据

三、JDBC基础

1、什么是JDBC

JDBC（Java DataBase Connectivity）就是Java数据库连接，简单来说，就是用Java语言来操作数据库。

2、JDBC原理

早期Sun公司想编写一套可以连接世界上所有数据库的API，但是当他们刚开始就发现这是一个不可能完成的任务，因为各个厂商的数据库服务器差异太大了。后来Sun开始与数据库厂商们讨论，最终得出的结论是，由Sun提供一套访问数据库的规范（就是一组接口），并提供连接数据库的协议标准，然后各个数据库厂商会遵循Sun的规范提供一套访问自己公司的数据库服务器的API。Sun提供的规范命名为JDBC，而各个厂商提供的，遵循了JDBC规范的，可以访问自己数据库的API被称为驱动。

3、JDBC的实现

3.1、创建数据库连接

先导入mysql的jdbc驱动jar包：

创建数据库连接类

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class JDBCDemo { public static void main(String[] args) throws ClassNotFoundException, SQLException { /* * 动态加载驱动 */ Class.forName("com.mysql.jdbc.Driver"); /* * 声明连接数据库的配置信息 */ //连接mysql的url String url = "jdbc:mysql://localhost:3306/test"; //登录mysql的用户名 String user = "root"; //登录mysql的密码 String password = "root"; /* * 获得数据库连接 */ Connection conn = DriverManager.getConnection(url, user, password); } }

3.2、执行SQL语句

/* * 创建声明 */ Statement stmt = conn.createStatement(); //创建表格 String sql = "create table stu(" + "id int primary key auto_increment," + "name varchar(20) not null," + "age int(2)," + "sex varchar(4)"+ ")"; boolean rel = stmt.execute(sql); System.out.println(rel);
//执行添加 String sql = "insert into stu values(null,'张三',20,'男') "; boolean rel = stmt.execute(sql); System.out.println(rel);
//执行查询 ResultSet rs = stmt.executeQuery("select * from stu"); while(rs.next()){ int id = rs.getInt("id"); String name = rs.getString("name"); int age = rs.getInt("age"); String sex = rs.getString("sex"); }
//关闭资源 rs.close(); stmt.close(); conn.close();

3.3代码的规范化

所谓代码规范化就是无论是否出现异常，都要关闭ResultSet、Statement，以及Connection。

public class JDBCDemo { private String url = "jdbc:mysql://localhost:3306/test"; private String user = "root"; private String password = "root"; /* * 获取数据库连接的方法 */ public Connection getConnection() throws Exception{ Class.forName("com.mysql.jdbc.Driver"); return DriverManager.getConnection(url, user, password); } /* * 查询的方法 */ public void query(){ Connection conn = null; Statement stmt = null; ResultSet rs = null; try { conn = getConnection(); stmt = conn.createStatement(); String sql = "select * from stu"; rs = stmt.executeQuery(sql); while(rs.next()){ String name = rs.getString("name"); String pwd = rs.getString("pwd"); } } catch (Exception e) { e.printStackTrace(); } finally { try { if(conn!=null) conn.close(); if(stmt!=null) stmt.close(); if(rs!=null) rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } }

4、JDBC主要对象介绍

4.1、DriverManager

我们只需要会用DriverManager的getConnection()方法即可：

1. Class.forName("com.mysql.jdbc.Driver"); 2. String url = "jdbc:mysql://localhost:3306/test"; 3. String user = "root"; 4. String pwd= "root"; 5. Connection conn = DriverManager.getConnection(url, user, pwd);

上面的代码可能出现的两种异常：

ClassNotFoundException：这个异常是在第1句上出现的，异常原因是没有找到对应的类，出现这个异常有两种可能：

l 你没有给出mysql的jar包；

l 你把类名称打错了，查看类名是不是com.mysql.jdbc.Driver;

SQLException：这个异常出现在第5句，出现这个异常的原因是三个参数内容有误，重点看一下URL是否书写错误。

4.2、Connection

l Connection最为重要的方法就是获取Statement：

Statement stmt = conn.createStatement();

l 在学习ResultSet方法时，还需要学习一下的方法：

Statement stmt = conn.createStatement(int,int);

其中的两个参数是用来确定创建的Statement能生成什么样的结果集。

4.3、Statement

Statement最为重要的方法是：

l int executeUpdate(String sql)：可以执行DDL和DML语句，即可执行insert、update、delete语句和create table、alter table、drop table等语句，返回成功执行的记录数；

l ResultSet executeQuery(String sql)：执行DQL查询语句，执行查询操作会返回ResultSet结果集。

l boolean execute()：可以执行前两个方法能执行的SQL语句，该方法用来执行增、删、改、查所有的SQL语句，返回值为boolean类型，表示执行的SQL语句是否有结果。

² 如果使用execute()方法执行的是更新语句，那么还要调用int getUpdateCount()来获取insert、update、delete语句所影响的行数；

² 如果使用execute()方法执行的是查询语句，那么还要调用ResultSet getResultSet()来获取select语句的查询结果。

4.4、ResultSet之滚动结果集

ResultSet表示结果集，它是一个二维的表格。ResultSet内部维护一个行光标（游标），光标位置从1开始。Result提供了一系列的方法来移动游标：

l 移动游标的方法：

² void beforeFirst()：把光标放到第一行的前面，这也是光标默认的位置；

² void afterLast()：把光标放到最后一行的后面；

² boolean first()：把光标放到第一行的位置上，返回值表示调控光标是否成功；

² boolean last()：把光标放到最后一行的位置上；

² boolean previous()：把光标向上挪一行；

² boolean next()：把光标向下挪一行；

² boolean relative(int row)：相对位移，当row为正数时，表示向下移动row行，为负数时表示向上移动row行；

² boolean absolute(int row)：绝对位移，把光标移动到指定的行上；

l 判断游标的位置方法：

² boolean isBeforeFirst()：当前光标位置是否在第一行前面；

² boolean isAfterLast()：当前光标位置是否在最后一行的后面；

² boolean isFirst()：当前光标位置是否在第一行上；

² boolean isLast()：当前光标位置是否在最后一行上；

² int getRow()：返回当前光标所在位置；

l 获取当前结果集的总行数：

² 先执行rs.last();把光标移动到最后一行，在执行rs.getRow();获得当前光标所在行，可以得到结果集一共有多少行；

l 获取结果集的总列数：

² 先获取结果集的元数据 ResultSetMetaData rsmd = rs.getMetaData();

² 获取结果集列数：int len = rsmd.getColumnCount();

² 获取指定列的列名：String name = rsmd.getColumnName(int colIndex);

/** * 动态获取结果 */ rs = stmt.executeQuery(sql); //获得列数 int len = rs.getMetaData().getColumnCount(); while(rs.next()){//遍历行 //遍历列 for (int i = 0; i < len; i++) { System.out.print(rs.getString(i)); if(i<len){ System.out.print(","); } } System.out.println("");//换行 }

结果集的特性：

² 是否可滚动

² 是否敏感

² 是否可更新

当使用Connection的createStatement()方法创建Statement对象时，就已经决定了Statement生成的结果集是什么特性。

使用conn.createStatement()方法生成的结果集不可滚动、不敏感、不可更新，

如果结果集是不可滚动的，那么只能使用next()方法来移动游标，表示返回下一行。二beforeFirst()、afterLast()、first()、last()、previous()、relative()等方法都不能使用。

如果想要结果集支持滚动，要使用

createStatement(int resultSetType,int resultSetConcurrency)方法来创建对象，其中两个参数分别表示结果集的类型和并发类型。

resultSetType的可选值：

² ResultSet.TYPE_FORWARD_ONLY：不滚动结果集；（MySQL默认值，但是支持滚动）

² ResultSet.TYPE_SCROLL_INSENSITIVE：滚动结果集，不敏感；

² ResultSet.TYPE_SCROLL_SENSITIVE：滚动结果集，敏感；（没有数据库支持）

esultSetConcurrency的可选值：

² CONCUR_READ_ONLY：结果集是只读的，不能通过修改结果集二反向影响数据库；

² CONCUR_UPDATABLE：结果集是可更新的，对结果集的更新可以反向影响数据库；

4.5、ResultSet之获取列数据

可以通过next()方法使ResultSet的游标向下移动，当游标移动到你需要的行时，就需要来获取该行的数据了，ResultSet提供了一系列的获取列数据的方法：

² String getString(int columnIndex)：获取指定列的String类型数据；

² int getInt(int columnIndex)：获取指定列的int类型数据；

² double getDouble(int columnIndex)：获取指定列的double类型数据；

² boolean getBoolean(int columnIndex)：获取指定的boolean类型数据；

² Object getObject(int columnIndex)：获取指定列的Object类型的数据

5、PreparedStatement

5.1、什么是SQL攻击

在需要用户输入的地方，用户输入的是SQL语句的片段，最终用户输入的SQL片段与我们DAO中写的SQL语句合成一个完整的SQL语句。例如用户在登录时输入的用户名和密码都是为SQL语句的片段。

5.2、演示SQL攻击

5.3、PreparedStatement用法

PreparedStatement是Statement接口的子接口，

其特点是：

防SQL攻击；
提高代码的可读性、可维护性；
提高效率；

用法：

声明SQL模板；
调用Connection的preparedStatement(String sql)方法；
调用pstmt的setXXX()系列方法，为SQL模板中的“?”占位符赋值；
调用pstmt的executeUpdate()或executeQuery()方法，这些方法都没有参数；

6、JDBCUtils工具类

6.1、JDBCUtils的作用

连接数据库的四大参数是：驱动类、URL、用户名、密码，这些参数都是与特定数据库关联，如果将来想要更改数据库，那么就要去修改这四大参数，那么为了不去修改代码，我们需要写一个JDBCUtils类，让它从配置文件中读取配置参数，然后创建连接对象。

6.2、JDBCUtils类的实现

1、创建.properties配置文件

2、编写配置文件的键值

driver=com.mysql.jdbc.Driver url=jdbc\:mysql\://127.0.0.1\:3306/test user=root password=root

3、编写JDBCUtils.java类

public class JDBCUtil { private static Properties prop = null; private static Connection conn = null; static { try { // 读取配置文件 InputStream is = JDBCUtil.class.getClassLoader() .getResourceAsStream("dbconfig.properties"); prop = new Properties(); prop.load(is); //动态加载驱动 Class.forName(prop.getProperty("driver")); //获取数据库连接对象 conn = DriverManager.getConnection(prop.getProperty("user"), prop.getProperty("user"), prop.getProperty("password")); } catch (Exception e) { e.printStackTrace(); } } public static Connection getConnection(){ return conn; } }

7、UserDao

7.1、DAO模型

DAO（Data Access Object）模型就是写一个类，把访问数据库的代码封装起来，DAO在数据库与业务逻辑（Service）之间。

l 实体域，即操作的对象，例如我们操作的表是user表，那么就需要先写一个User类；

l DAO模式需要先提供一个DAO接口；

l 然后再提供一个DAO接口的实现类；

l 再编写一个DAO工厂，Service通过工厂来获取DAO实现。

7.2、面向接口编程的代码实现

1、UserDAO接口

public interface UserDao { //登录方法 public boolean login(Object obj); //注册方法 public boolean reg(Object obj); }

2、创建UserDaoImpl类实现UserDao接口

public class UserDaoImpl implements UserDao { public boolean login(Object obj) { //对数据库操作的方法 //..... return false; } public boolean reg(Object obj) { //对数据库操作的方法 //..... return false; } }

3、创建UserDao.properties配置文件

配置信息：

UserDao=com.jdbc.demo.UserDao

4、创建UserDaoFactory.java类，用来创建UserDao对象

public class UserDaoFactory { private static Properties prop = null; static{ try { //加载配置文件 InputStream is = UserDaoFactory.class.getClassLoader().getResourceAsStream("UserDao.properties"); prop = new Properties(); prop.load(is); } catch (IOException e) { e.printStackTrace(); } } public static UserDao getUserDao(){ try { //通过反射获取UserDao对象 Class c = Class.forName(prop.getProperty("UserDao")); return (UserDao)c.newInstance(); } catch (Exception e) { e.printStackTrace(); } return null; } }

5、在UserService.java业务逻辑层，通过UserDaoFactory获取UserDao对象

public class UserService { //通过UserDaoFactory获取UserDao对象 private UserDao userDao = UserDaoFactory.getUserDao(); //登录方法 public boolean login(Object obj){ return userDao.login(obj); } //注册方法 public boolean reg(Object obj){ return userDao.reg(obj); } }

8、时间类型

8.1、时间类型的问题

数据库类型与Java中类型的对应关系如下：

l DATE --> java.sql.Date

l TIME --> java.sql.Time

l TIMESTAMP --> java.sql.Timestamp

需要注意的是：

l 领域对象（例如实体类User）中的所有属性不能出现java.sql包下的东西，即不能使用java.sql.Date；

l ResultSet.getDate()方法返回的是java.sql.Date；

l PreparedStatement.setDate(int,Date)方法中第二个参数也是java.sql.Date；

那么我们就面临这两次时间类型转换的问题，一次是存数据，一次的取数据，要完成以下转换：

l java.util.Date --> java.sql.Date/Time/Timestamp

l java.sql.Date/Time/Timestamp --> java.util.Date

8.2、时间类型的转换

因为java.util.Date是java.sql.Date/Time/Timestamp的父类，所以我们可以使用以下方式完成转换：

l 从数据库取Date值是可以完成自动类型转换；

l 往数据库中存值时，先把java.util.Date转为毫秒数，通过java.sql.Date(long Date)的构造方法，传入一个时间戳的方式完成类型转换；

9、批处理

9.1、Statement批处理

批处理就是一批一批的处理，而不是一个一个的处理。当有10条SQL语句要执行时，一次向服务器发送一条SQL语句，这么做效率很差，处理的方案是使用批处理，即一次向服务器发送多条SQL语句，然后由服务器一次性处理。

批处理只针对更新（增、删、改）语句，批处理不包含查询。

可以多次调用Statement类的addBatch(String sql)方法，把需要执行的所有SQL语句添加到一个“批”中，然后调用Statement类的executeBatch()方法来执行当前“批”中的语句。

l void addBatch(String sql)：添加一条语句到“批”中；

l int[] executeBatch()：执行“批”中所有语句，返回值表示每条语句所影响的行数据；

l void clearBatch()：清空“批”中的所有语句。

9.2、PreparedStatement批处理

PreparedStatement的批处理有所不同，因为每个PreparedStatement对象都绑定一条SQL模板。所以想PreparedStatement中添加的不是SQL语句，而是“?”赋值。

9.3、执行批处理

1、打开MySQL批处理，在连接数据库的url后面添加参数

jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true

2、编写批处理代码

public void add() throws SQLException{ //获得数据库连接 Connection conn = JDBCUtil.getConnection(); String sql = "insert into stu(id,name,sex) values(?,?,?)"; PreparedStatement pstm = conn.prepareStatement(sql); //批量添加 for (int i = 1; i <= 10000; i++) { pstm.setInt(1, i); pstm.setString(2, "name_"+i); pstm.setString(3, i%2==0?"男":"女"); //添加批处理，把一组数据添加到集合中 pstm.addBatch(); } //执行批处理 pstm.executeBatch(); }

四、JDBC事务

1、事务概述

1.1、什么是事务

以银行转账为例，张三转1000元到李四的账户，其完成转账过程需要执行两条SQL语句：

l 给张三的账户减去1000元；

l 给李四的账户加上1000元。

如果在第一条SQL语句执行成功后，在执行第二条SQL语句之前，程序被中断了（抛出异常或其他原因），那么李四的账户上没有加上1000元，而张三却减去1000元，这样就造成了很大的安全漏洞。

事务简单来说，就是在多个操作中，要么完全成功，要么完全失败，不可能出现只成功一半的情况。

1.2、事务的四大特性（ACID）

事务的四大特性是：

l 原子性（Atomicity）：事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。

l 一致性（Consistency）：事务执行后，数据库状态与其他业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的。

l 隔离性（Isolation）：隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。

l 持久性（Durability）：一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

1.3、MySQL中的事务

在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要开启事务和结束事务。

l 开启事务：start transaction；

l 结束事务：commit或rollback；

在执行SQL语句之前，先执行strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤销了。

以转账为例，演示代码如下：

START TRANSACTION; UPDATE account SET balance=balance-1000 WHERE id=1; UPDATE account SET balance=balance+1000 WHERE id=2; ROLLBACK; #回滚结束，事务执行失败
START TRANSACTION; UPDATE account SET balance=balance-1000 WHERE id=1; UPDATE account SET balance=balance+1000 WHERE id=2; COMMIT; #提交结束，事务执行成功
START TRANSACTION; UPDATE account SET balance=balance-1000 WHERE id=1; UPDATE account SET balance=balance+1000 WHERE id=2; quit; #退出，MySQL会自动回滚事务

2、JDBC事务

在jdbc中处理事务，都是通过Connection完成的, 同一事务中所有的操作，都在使用同一个Connection对象。

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

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

l commit()：提交结束事务，conn.commit()表示提交事务；

l rollback()：回滚结束事务，conn.rollback()表示回滚事务；

JDBC处理事务的代码格式：

Connection conn = JDBCUtil.getConnection(); try{ conn.setAutoCommit(false); //开启事务 //执行代码 conn.commit();//提交事务 }catch(Exception e){ conn.rollback();//回滚 }

3、事务隔离级别

当多个事务并发执行时才需要考虑并发事务的处理。

3.1、事务的并发读问题

l 脏读：读取到另一个事务未提交数据；

l 不可重复读：两次读取不一致；

l 幻读（虚读）：读到另一个事务已提交数据

3.2、并发事务问题

因为并发事务导致的问题大致有5类，其中两类是更新问题，三类是读问题。

l 脏读（dirty read）：读到另一个事务的未提交更新数据，即读取到了脏数据；

l 不可重复读（unrepeatable read）：对同一记录的两次读取不一致，因为另一事务对该记录做了修改；

l 幻读（phantom read）：对同一张表的两次查询不一致，因为另一事务插入了一条记录；

不可重复读和幻读的区别：

l 不可重复读是读取到了另一个事务的更新；

l 幻读是读取到了另一个事务的插入（MySQL中无法测试到幻读）；

3.3、四大隔离级别

4个等级的事务隔离级别，在相同数据环境下，使用相同的输入，执行相同的工作，根据不同的隔离级别，可以导致不同的结果。不同事务隔离级别能够解决的数据并发问题的能力是不同的。

SERIALIZABLE（串行化）

² 不会出现任何并发问题，因为它是对同一数据的访问是串行的，非并发访问的；

² 性能最差；

REPEATABLE READ（可重复读），MySQL默认的隔离级别

² 防止脏读和不可重复读，不能处理幻读问题；

² 性能比SERIALIZABLE好；

READ COMMITTED（读已提交数据），Oracle默认的隔离级别

² 防止脏读，不能处理不可重复读和幻读问题；

² 性能比REPEATABLE READ；

READ UNCOMMITTED（读未提交数据）

² 可能出现任何事务并发问题；

² 性能最好；

3.4、MySQL的隔离级别

l MySQL的默认隔离级别为REPEATABLE READ，可以通过下面语句查看：

select @@tx_isolation

l 也可以通过下面语句来设置当前连接的隔离级别：

set transaction isolationlevel [4选1]

3.5、JDBC设置隔离级别

conn.setTransactionIsolation(int level)

参数可选值有：

² Connection.TRANSACTION_READ_UNCOMMITTED;

² Connection.TRANSACTION_READ_COMMITTED;

² Connection.TRANSACTION_REPEATABLE_READ;

² Connection.TRANSACTION_SERIALIZABLE

五、数据连接池

1、数据连接池的概念

用池来管理Connection，这样就可以重复使用Connection。有了池，所以我们就不用自己来创建Connection，而是通过池来获取Connection对象。当使用完Connection后，调用Connection的close()方法也不会真的关闭Connection，而是把Connection“归还”给池。池就可以再利用这个Connection对象了。

数据连接池的参数有：

l 初始大小（10个连接）；

l 最小空闲连接数（3个连接）；

l 增量：即一次创建的最小单位（5个连接）；

l 最大空闲连接数（12个连接）；

l 最大连接数（20个）；

l 最大等待时间（1000毫秒）。

2、C3P0数据连接池

需要导入的jar包：

2.1、使用类实现

//创建连接池对象 ComboPooledDataSource dataSource = new ComboPooledDataSource(); //对池进行四大参数配置 dataSource.setDriverClass("com.mysql.jdbc.Driver"); dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test"); dataSource.setUser("root"); dataSource.setPassword("root"); //池配置 //配置初始化连接 dataSource.setInitialPoolSize(20); //配置增量 dataSource.setAcquireIncrement(5); //配置最小连接数 dataSource.setMinPoolSize(3); //配置最大连接数 dataSource.setMaxPoolSize(50); //获取数据库连接 Connection conn = dataSource.getConnection(); //关闭连接，其实是把连接归还给池 conn.close();

2.2、使用配置文件实现

使用配置文件实现C3P0的要求：

l 文件名称必须为c3p0-config.xml

l 文件位置必须在src下

1、默认配置

c3p0-config.xml配置文件

<?xml version="1.0" encoding="UTF-8"?> <c3p0-config>  <default-config>  <property name="jdbcUrl">jdbc:mysql://localhost:3306/xm</property> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="user">root</property> <property name="password">root</property>  <property name="aoquireInorement">3</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">2</property> <property name="maxPoolSize">10</property> </default-config> </c3p0-config>

直接在java代码中创建数据连接池对象

//创建连接池对象 ComboPooledDataSource dataSource = new ComboPooledDataSource(); //获取数据库连接 Connection conn = dataSource.getConnection();

2、使用命名配置

c3p0-config.xml配置文件

<?xml version="1.0" encoding="UTF-8"?> <c3p0-config>  <named-config name="oracle-config"> <property name="jdbcUrl">jdbc:mysql://localhost:3306/xm</property> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="user">root</property> <property name="password">root</property> <property name="aoquireInorement">3</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">2</property> <property name="maxPoolSize">10</property> </named-config> </c3p0-config>

在java代码中创建池对象

//构造器的参数为指定命名配置元素的名称 ComboPooledDataSource dataSource = new ComboPooledDataSource("oracle-config"); //获取数据库连接 Connection conn = dataSource.getConnection();

2.3、优化JDBCUtils工具类

public class JDBCUtil { //创建c3p0数据连接池对象 private static ComboPooledDataSource dataSource = new ComboPooledDataSource(); /** * 使用数据连接池返回一个数据连接对象 * @throws SQLException */ public static Connection getConnection() throws SQLException{ return dataSource.getConnection(); } /** * 返回数据连接池对象 */ public static ComboPooledDataSource getDataSources(){ return dataSource; } }

3、ThreadLocal

ThreadLocal通常用在一个类的成员上，多个线程访问它时，每个线程都有自己的副本，互不干扰。在Spring中把Connection放到ThreadLocal中。

3.1、ThreadLocal API

ThreadLocal类只有三个方法：

l void set(T value)：保存值；

l T get()：获取值；

l void remove()：移除值

3.2、ThreadLocal的内部是Map

ThreadLocal内部其实是个Map来保存数据，虽然在使用ThreadLocal时只给出了值，没有给出键，但是在它内部使用了当前线程作为键。

代码实现：

//实例化ThreadLocal类 ThreadLocal<String> tl = new ThreadLocal<String>(); tl.set("hello");//存值，在ThreadLocal中不能存多个值 String str = tl.get();//取值 tl.remove();//删除值

ThreadLocal原理：

class ThreadLocal<T> { private Map<Thread,T> map = new HashMap<Thread, T>(); //存值方法 public void set(T value){ //把当前线程作为键 map.put(Thread.currentThread(), value); } //取值方法 public T get(){ return map.get(Thread.currentThread()); } //删除值方法 public void remove(){ map.remove(Thread.currentThread()); } }

演示ThreadLocal并发访问共享资源的问题

public void test(){ //实例化ThreadLocal类 final ThreadLocal<String> tl = new ThreadLocal<String>(); tl.set("hello");//存值，在ThreadLocal中不能存多个值 //内部线程类 new Thread(){ public void run() { //在内部类的线程中获取不到主线程的ThreadLocal中的值 System.out.println(tl.get()); }; }.start(); }

六、dbUtils工具

1、优化Dao模型层

1.1、数据更新

创建QR类，动态更新数据

public class QR { private Connection conn ; public QR(Connection conn) { this.conn = conn; } public QR() { } /** * 实现增、删、改的方法 */ public int update(String sql,Object... params){ PreparedStatement ps = null; try{ //使用SQL创建ps对象 ps = conn.prepareStatement(sql); //设置参数 initStatement(ps,params); //执行SQL return ps.executeUpdate(); } catch (Exception e){ e.printStackTrace(); } finally { try { if(ps!=null) ps.close(); if(conn!=null) conn.close(); } catch (Exception e2) { e2.printStackTrace(); } } return -1; } //动态获取参数的方法 public void initStatement(PreparedStatement ps,Object... params){ try { for (int i = 0; i < params.length; i++) { ps.setObject(i+1, params[i]); } } catch (Exception e) { e.printStackTrace(); } } }

使用QR对象，动态更新数据

//获得Stu对象 Stu stu = new Stu(100,"tom",20,"男"); QR qr = new QR(JDBCUtil.getConnection()); //创建SQL模板 String sql = "insert into stu values(?,?,?,?)"; //声明要动态赋值的参数 Object[] params = {stu.getId(),stu.getName(),stu.getAge(),stu.getSex()}; //调用更新方法 int i = qr.update(sql, params); System.out.println(i);

1.2、数据查询

ResultHandler接口

public interface ResultHandler<T> { public T handler(ResultSet rs) throws SQLException; }

QR.java类

public class QR<T> { private Connection conn ; public QR(Connection conn) { this.conn = conn; } public QR() { } //动态获取参数的方法 public void initStatement(PreparedStatement ps,Object... params){ try { for (int i = 0; i < params.length; i++) { ps.setObject(i+1, params[i]); } } catch (Exception e) { e.printStackTrace(); } } /** * 动态查询数据（查询对象） */ public T query(String sql,ResultHandler rh,Object... params){ PreparedStatement ps = null; ResultSet rs = null; try{ //使用SQL创建ps对象 ps = conn.prepareStatement(sql); //设置参数 initStatement(ps,params); //执行SQL rs = ps.executeQuery(); return (T) rh.handler(rs); } catch (Exception e){ e.printStackTrace(); } finally { try { if(ps!=null) ps.close(); if(conn!=null) conn.close(); } catch (Exception e2) { e2.printStackTrace(); } } return null; } }

使用动态查询

//获得Stu对象 Stu stu = new Stu(100,"tom",20,"男"); QR qr = new QR(JDBCUtil.getConnection()); //创建SQL模板 String sql = "select * from stu where id=?"; //声明要动态赋值的参数 Object[] params = {stu.getId()}; //创建ResultHandler对象 ResultHandler<Stu> rh = new ResultHandler<Stu>() { public Stu handler(ResultSet rs) throws SQLException { if(!rs.next()) return null; Stu s = new Stu(); s.setId(rs.getInt("id")); s.setName(rs.getString("name")); s.setAge(rs.getInt("age")); s.setSex(rs.getString("sex")); return s; } }; //执行查询 Stu s = (Stu) qr.query(sql, rh, params);

2、dbUtils工具

需要导入的jar包：

2.1、dbUtils工具的使用

dbUtils中对数据的动态操作使用的是QueryRunner对象

//获得Stu对象 Stu stu = new Stu(100,"tom",20,"男"); //创建QR对象，参数为数据连接池对象 QueryRunner qr = new QueryRunner(JDBCUtil.getDataSources()); //创建SQL模板 String sql = "select * from stu where id=?"; Object[] params = {stu.getId()}; //执行查询 Stu s = qr.query(sql, new BeanHandler<Stu>(Stu.class),params); System.out.println(s.getName());

2.2、QueryRunner对象介绍

update()方法

u int update(String sql,Object... params)：可执行增、删、改语句；

u int update(Connection conn,String sql,Object... params)：需要调用者提供Connection，这说明本方法不再管理Connection了，支持事务；

query()方法

u T query(String sql,ResultSetHandler rsh,Object... params)：可执行查询,它会先得到ResultSet，然后调用rsh的handle()，把rs转换成需要的类型；

u T query(Connection conn,String sql,ResultSetHandler rsh,Object... params)：支持事务；

ResultSetHandler接口：

u BeanHandler（单行）：构造器需要一个Class类型的参数，用来把一行结果转换成指定类型的JavaBean对象；

u BeanListHandler（多行）：构造器也是需要一个Class类型的参数，用来把一行结果集转换成一个Javabean，那么多行就是转换成List对象，一堆Javabean；

u MapHandler（单行）：把一行结果集转换成Map对象，如

² 一行记录

sid sname age sex

100 tom 20 男

² 一个Map

{sid:100,sname:tom,age:20,sex:男}

u MapListHandler（多行）：把一行记录转换成一个Map，多行就是多个Map，即List<Map>；

u ScalarHandler（单行单列）：通常用于select count(*) from tbname;语句，结果集是单行单列的，返回一个Object；

3、ResultSetHandler接口

3.1、BeanListHandler的使用

//创建QR对象，参数为数据连接池对象 QueryRunner qr = new QueryRunner(JDBCUtil.getDataSources()); //创建SQL模板 String sql = "select * from stu"; //BeanListHandler多行处理器 List<Stu> list = qr.query(sql, new BeanListHandler<Stu>(Stu.class));

3.2、MapHandler的使用

//获得Stu对象 Stu stu = new Stu(100,"tom",20,"男"); //创建QR对象，参数为数据连接池对象 QueryRunner qr = new QueryRunner(JDBCUtil.getDataSources()); //创建SQL模板 String sql = "select * from stu where id=?"; Object[] params = {stu.getId()}; //MapHandler单行处理器，把每一行都转换成一个Map Map map = qr.query(sql, new MapHandler(),params);

3.3、MapListHandler的使用

//获得Stu对象 Stu stu = new Stu(100,"tom",20,"男"); //创建QR对象，参数为数据连接池对象 QueryRunner qr = new QueryRunner(JDBCUtil.getDataSources()); //创建SQL模板 String sql = "select * from stu"; //MapListHandler多行处理器，把每行都转成一个Map，即List<Map> List<Map<String,Object>> mapList = qr.query(sql, new MapListHandler());

3.4、ScalarHandler的使用

//获得Stu对象 Stu stu = new Stu(100,"tom",20,"男"); //创建QR对象，参数为数据连接池对象 QueryRunner qr = new QueryRunner(JDBCUtil.getDataSources()); //创建SQL模板 String sql = "select count(*) from stu"; //执行查询 Object obj = qr.query(sql, new ScalarHandler());

七、Service层的事务处理

如果是在DAO中来处理事务是非常简单的，事务属于业务逻辑，我们必须放到Service层中来处理事务，在前面的章节中学习到，JDBC处理事务是用Connection对象来调用事务的相关方法，但是在Service中不能出现Connection，它只能出现在DAO中，这时，我们就会遇到事务处理的问题。

1、优化JDBCUtil工具类

我们把对事务的开启和关闭放到JDBCUtil中，在Service中调用JDBCUtil的方法来完成事务的处理，但在Service中就不会再出现Connection了。

DAO中的方法不用再让Service来传递Connection了，DAO会主动从JDBCUtil中获取Connection对象，这样，JDBCUtil称为了DAO和Service的中介。

在JDBCUtil类中添加三个方法：

l beginTransaction()：开启事务的方法，获取一个Connection，设置它的setAutoCommit(false)，还要保证DAO中使用的连接也是该方法中的Connection对象；

l commitTransaction()：提交事务的方法，获取beginTransaction提供的Connection，然后调用commit()方法；

l rollbackTransaction()：回滚事务，调用Connection对象的rollback()方法；

在Service中调用这三个方法来完成事务的处理：

public class DemoService { private DemoDao demoDao = new DemoDao(); public void method(){ try { //开启事务 JDBCUtil.beginTransaction(); demoDao.update(...); demoDao.update(...); //提交事务 JDBCUtil.commitTransaction(); } catch (SQLException e) { //事务回滚 JDBCUtil.rollbackTransaction(); } } }

JDBCUtil.java类的优化

public class JDBCUtil { //数据连接池对象 private static ComboPooledDataSource dataSource = new ComboPooledDataSource();; //事务专用连接 private static Connection conn = null; /** * 通过数据连接池获取连接 * @throws SQLException */ public static Connection getConnection() throws SQLException{ //如果conn不为空，证明一件调用了beginTransaction方法，事务已经开启 if(conn != null) return conn; return dataSource.getConnection(); } /** * 获取数据连接池对象 */ public static DataSource getDataSource(){ return dataSource; } /** * 开启事务 * 获取Connection，设置它的setAutoCommit(false) * @throws SQLException */ public static void beginTransaction() throws SQLException{ if(conn!=null){ throw new SQLException("事务已经开启，不能重复开启事务！"); } //给conn赋值 conn = getConnection(); //把conn的事务设置为手动提交 conn.setAutoCommit(false); } /** * 提交事务 * @throws SQLException */ public static void commitTransaction() throws SQLException{ if(conn == null){ throw new SQLException("还没有开启事务，不能提交！"); } //提交事务 conn.commit(); //把连接归还，并且赋值为NULL，是为了保证下次获取，不再是开始事务的连接 conn.close(); conn = null; } /** * 事务回滚 * @throws SQLException */ public static void rollbackTransaction() throws SQLException{ if(conn == null){ throw new SQLException("还没有开启事务，不能回滚！"); } conn.rollback(); conn.close(); conn = null; } /** * 释放连接 * @throws SQLException */ public static void closeConnection(Connection connection) throws SQLException{ //判断参数连接是否为事务连接，是就不关闭，不是再关闭 //如果conn为NULL，证明没有开启事务，那么就关闭参数的connection if(conn == null){ connection.close(); }else if(conn != connection){ //如果conn不为NULL，但是与connection不是同一个连接，证明参数不是事务专用连接，那么也关闭connection connection.close(); } } }

创建TxQueryRunner.java类基础QueryRunner，把数据库连接封装起来

public class TxQueryRunner extends QueryRunner { @Override public int[] batch(String sql, Object[][] params) throws SQLException { // 获取连接 Connection conn = JDBCUtil.getConnection(); // 调用父类方法 int[] result = super.batch(conn, sql, params); // 释放连接 JDBCUtil.closeConnection(conn); return result; } …… @Override public int update(String sql, Object... params) throws SQLException { // 获取连接 Connection conn = JDBCUtil.getConnection(); // 调用父类方法 int result = super.update(conn, sql, params); // 释放连接 JDBCUtil.closeConnection(conn); return result; } …… }

在DAO层中使用TxQueryRunner对象来完成数据库的操作

public class DemoDao { public void demo(String s1,String s2) throws SQLException { QueryRunner qr = new TxQueryRunner(); //SQL模板 String sql = "update user set ......."; Object[] params = {s1,s2}; //获取数据库连接 qr.update(sql, params); } }

2、JDBCUtil处理多线程并发问题

当有多个线程操作JDBCUtil中的Connection对象时，就会出现并发问题，可以使用ThreadLocal来解决这个问题。

JDBCUtil工具类优化

public class JDBCUtil { //数据连接池对象 private static ComboPooledDataSource dataSource = new ComboPooledDataSource();; //事务专用连接，把事务存入到ThreadLocal中 private static ThreadLocal<Connection> tlConn = new ThreadLocal<Connection>(); /** * 通过数据连接池获取连接 * @throws SQLException */ public static Connection getConnection() throws SQLException{ //如果conn不为空，证明一件调用了beginTransaction方法，事务已经开启 Connection conn = tlConn.get(); if(conn != null) return conn; return dataSource.getConnection(); } /** * 获取数据连接池对象 */ public static DataSource getDataSource(){ return dataSource; } /** * 开启事务 * 获取Connection，设置它的setAutoCommit(false) * @throws SQLException */ public static void beginTransaction() throws SQLException{ Connection conn = tlConn.get(); if(conn!=null){ throw new SQLException("事务已经开启，不能重复开启事务！"); } //给conn赋值 conn = getConnection(); //把conn的事务设置为手动提交 conn.setAutoCommit(false); //把连接存入到ThreadLocal中 tlConn.set(conn); } /** * 提交事务 * @throws SQLException */ public static void commitTransaction() throws SQLException{ Connection conn = tlConn.get(); if(conn == null){ throw new SQLException("还没有开启事务，不能提交！"); } //提交事务 conn.commit(); //把连接归还，并且赋值为NULL，是为了保证下次获取，不再是开始事务的连接 conn.close(); tlConn.remove(); } /** * 事务回滚 * @throws SQLException */ public static void rollbackTransaction() throws SQLException{ Connection conn = tlConn.get(); if(conn == null){ throw new SQLException("还没有开启事务，不能回滚！"); } conn.rollback(); conn.close(); tlConn.remove(); } /** * 释放连接 * @throws SQLException */ public static void closeConnection(Connection connection) throws SQLException{ //判断参数连接是否为事务连接，是就不关闭，不是再关闭 //如果conn为NULL，证明没有开启事务，那么就关闭参数的connection Connection conn = tlConn.get(); if(conn == null){ connection.close(); }else if(conn != connection){ //如果conn不为NULL，但是与connection不是同一个连接，证明参数不是事务专用连接，那么也关闭connection connection.close(); } } }

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2019-09-14 ，如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法