mysql数据库的简单基本操作以及连接数据库步骤以及mysql的安装等等

第一章 数据库简介

1.1 简介

数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。

数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。

数据库软件应该为数据库管理系统，数据库是通过数据库管理系统创建和操作的。

数据库：存储、维护和管理数据的集合。

1.2 常见数据库管理系统

• Oracle：Oracle数据库被认为是业界目前比较成功的关系型数据库管理系统。Oracle数据库可以运行在UNIX、Windows等主流操作系统平台，完全支持所有的工业标准，并获得最高级别的ISO标准安全性认证。
• MySQL：MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。
• DB2：DB2是IBM公司的产品，DB2数据库系统采用多进程多线索体系结构，其功能足以满足大中公司的需要，并可灵活地服务于中小型电子商务解决方案。
• Microsoft SQL Server：SQL Server 是Microsoft 公司推出的关系型数据库管理系统。具有使用方便可伸缩性好与相关软件集成程度高等优点。

1.3 三大范式（规范） 什么是三大范式：

第一范式：无重复的列。当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式：属性完全依赖于主键 [ 消除部分子函数依赖 ]。如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。

第三范式：属性不依赖于其它非主属性 [ 消除传递依赖 ]。设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF. 满足第三范式（3NF）必须先满足第二范式（2NF）。第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性
第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

1.4 MySQL安装和卸载

• 安装和配置步骤：
  MySQL安装图
  解.doc
  1.03MB

• 验证是否安装成功。
  开始菜单->mysql->mysql command line client客户端界面->输入密码，看到mysql命令操作，表示安装成功

• 卸载MySQL
  1.停止MySQL服务
  (在开始菜单下输入命令：services.msc或者"服务")
  2.添加删除程序中卸载MySQL
  3.到安装目录删除MySQL(可省略)
  4.删除：C:\Documents and Settings\All Users\Application Data\MySQL(重要)
  5.查看注册表：(在开始菜单下输入regedit命令)
  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
  HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services
  HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services
  搜索mysql，找到一律干掉！
  ============================
  最后确认:在服务界面看一下是否还存在mysql服务
  不需要重启电脑，直接重新安装即可

1.5 MySQL5.5.x 版本安装

见操作

请注意安装需要的依赖环境,如果想安装的功能缺少对应环境是无法安装的,需先安装环境后才可以安装，常见错误，需要安装.NET Framework 4.0系统环境

第二章 SQL语言

2.1 概述

SQL：Structure Query Language（结构化查询语言），SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准。

各数据库厂商都支持ISO的SQL标准，普通话
各数据库厂商在标准的基础上做了自己的扩展，方言
SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建项目，查询内容，更新内容，并删除条目等操作。

Create, Read, Update, and Delete 通常称为CRUD操作。

2.2 SQL语句分类

• DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。
• DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）增删改。
• DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。
• DQL（Data Query Language）：数据查询语言，用来查询记录（数据）查询。

注意sql语句以;结尾
mysql中的关键字不区分大小写

2.3 DDL操作数据库

1创建：CREATE DATABASE语句用于创建新的数据库：
编码方式:gb2312,utf-8,gbk,iso-8859-1
CREATE DATABASE 数据库名;
CREATE DATABASE 数据库名 character set 编码方式;
//create database 数据库名
CREATE DATABASE mydb1;
//create database 数据库名 character set 编码方式
CREATE DATABASE mydb2 character SET GBK;
//create database 数据库名 set 编码方式 collate 排序规则
CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;

2 查看数据库

查看当前数据库服务器中的所有数据库
show databases;
SHOW DATABASES;

查看前面创建的mydb2数据库的定义信息
show create database 数据库名;
Show CREATE DATABASE mydb2;

## 3 修改数据库

alter database 数据库名 character set 编码方式
查看服务器中的数据库，并把mydb2的字符集修改为utf8;
ALTER DATABASE mydb2 character SET utf8;

## 4 删除数据库:

drop database 数据库名
DROP DATABASE mydb3;

5其他语句

查看当前使用的数据库
Select database();
切换数据库: use 数据库名
USE mydb2;

2.4 DDL操作表

CREATE TABLE语句用于创建新表。
语法：
CREATE TABLE 表名(
列名1 数据类型 [约束],
列名2 数据类型 [约束],
列名n 数据类型 [约束]
);
说明:表名，列名是自定义，多列之间使用逗号间隔，最后一列的逗号不能写
[约束] 表示可有可无
示例：
CREATE TABLE Employees(
id INT ,
age INT ,
first VARCHAR(255),
last VARCHAR(255)
);

常用数据类型：
int：整型
double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；默认支持四舍五入
char：固定长度字符串类型； char(10) 'aaa ’ 占10位
varchar：可变长度字符串类型； varchar(10) ‘aaa’ 占3位
text：字符串类型，比如小说信息；
blob：字节类型，保存文件信息(视频，音频，图片)；
date：日期类型，格式为：yyyy-MM-dd；
time：时间类型，格式为：hh:mm:ss
timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值
datetime:日期时间类型 yyyy-MM-dd hh:mm:ss
常用约束：
数据库的完整性:用来保证存放到数据库中的数据是有效的
即数据的有效性和准确性
主键约束：primary key
唯一约束：unique [key]
非空约束：not null
默认约束：default
自动增长：auto_increment

建议这些约束应该在创建表的时候设置
(1)主键约束:primary key
主键列:设置了主键的列
特点:不允许重复,非空(不允许为null/必填)
注:通常情况下,每张表都会有一列主键列
(2)自增长: auto_increment
注:适用于int类型的列,和主键列一起使用
(3)非空约束:not null
特点:不允许为null(必填)
(4)默认值:default
语法: default 默认值 //默认值如果是日期或字符，需要加单引号
注:当用户不指定值的时候,执行默认值
(5)唯一约束:unique
特点:列值不允许重复
唯一约束的自定义名称结构:uq_当前表名_唯一列列名

多个约束条件之间使用空格间隔
create table student(
studentno int primary key auto_increment,
loginPwd varchar(20) not null default ‘123456’,
studentname varchar(50) not null,
sex char(2) not null,
gradeid int not null,
phone varchar(255) not null,
address varchar(255) default ‘学生宿舍’,
borndate datetime,
email varchar(50)
);
DROP TABLE语句用于删除现有表。
drop table 表名;
DROP TABLE table_name;

当前数据库中的所有表
show tables;
SHOW TABLES;
查看表的字段信息
desc 表名；
DESC employee;

增加列:在上面员工表的基本上增加一个image列。
alter table 表名 add 新列名 新的数据类型
ALTER TABLE employee ADD image blob;

修改job列，使其长度为60。
alter table 表名 change 旧列名 新列名 新的数据类型
ALTER TABLE employee MODIFY job varchar(60);

ALTER TABLE employee change job job varchar(60);

列名name修改为username
ALTER TABLE user CHANGE name username varchar(100);

删除image列,一次只能删一列。
alter table 表名 drop 列名
ALTER TABLE employee DROP image;

修改表名,表名改为user。
alter table 旧表名 rename 新表名;
alter table user rename users;

查看表格的创建细节
show create table 表名;
SHOW CREATE TABLE user;

修改表的字符集为gbk
alter table 表名 character set 编码方式
ALTER TABLE user CHARACTER SET gbk;

删除表:
drop table 表名;
drop table student;

练习：
表名 card(会员卡表)
列名 数据类型
cardid int
cardnum varchar(20)
regDate date
需求：
(1)创建该表
(2)将card表名修改为CardInfo
(3)添加delDate(注销时间) 列到表中
(4)将cardnum改为varchar(30),必填列
(5)删除regDate列
(6)删除cardInfo表

2.5 DML操作

DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了。
主要包括：INSERT 、UPDATE、 DELETE
小知识：
在mysql中，字符串类型和日期类型都要用单引号括起来。
空值：null
（1）插入操作：INSERT:
insert into 表名(列名) values(数据值);
实例:insert into student(stuname,stuage,stusex,birthday)
values(‘张三1’,18,‘a’,‘2000-1-1’);
– 注意:1多列和多个列值之间使用逗号隔开 2.列名要和列值一一对应
– 非数值的列值两侧需要加单引号
常见错误: Data too long for column ‘stusex’ at row 1

– 添加数据的时候可以将列名省略->当给所有列添加数据的时候
– 此时列值的顺序按照数据表中列的顺序执行
insert into student values(‘李四’,12,‘1111’,189.98,‘2000-1-1’,‘男’,‘2007-1-1’);

– 同时添加多行
insert into 表名(列名) values(第一行数据),(第二行数据),(),();
insert into student(stuname,stuage,stusex,birthday)
values(‘张三3’,18,‘a’,‘2000-1-1’),
(‘张三4’,18,‘a’,‘2000-1-1’),
(‘张三5’,18,‘a’,‘2000-1-1’),
(‘张三6’,18,‘a’,‘2000-1-1’),
(‘张三7’,18,‘a’,‘2000-1-1’),
(‘张三8’,18,‘a’,‘2000-1-1’);

注意：列名与列值的类型、个数、顺序要一一对应。
可以把列名当做java中的形参，把列值当做实参。
参数值不要超出列定义的长度。
如果插入空值，请使用null
插入的日期和字符一样，都使用引号括起来。

练习 ：
create table emp(
id int primary key,
name varchar(100) not null,
gender varchar(10) not null,
birthday date,
salary float(10,2),
entry_date date,
resume text
);

INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(1,‘zhangsan’,‘female’,‘1990-5-10’,10000,‘2015-5-5-’,‘goodgirl’);

INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(2,‘lisi’,‘male’,‘1995-5-10’,10000,‘2015-5-5’,‘good boy’);

INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(3,‘你好’,‘male’,‘1995-5-10’,10000,‘2015-5-5’,‘good boy’);

小知识：
查看数据库编码的具体信息
Show variables like ‘character%’;

临时更改客户端和服务器结果集的编码
Set character_set_client=gbk;
Set character_set_results=gbk;

或者
SET NAMES ‘gbk’;

sql中的运算符:
（1）算术运算符:+，-，*，/(除法),求余(%)
示例：
5/2=2
5%2=1
2/5=0
2%5=2
（2）赋值运算符:=
注：赋值方向：从右往左赋值
示例： name=‘张三’
(3) 逻辑运算符：
and(并且),or(或者),not（取非）
作用:用于连接多个条件时使用
(4) 关系运算符：

,<,>=,<=,!=(不等于),=(等于),<>(不等于)

（2）修改（更新）操作：UPDATE:
语法：UPDATE 表名 SET 列名1=列值1,列名2=列值2 … WHERE 列名=值
练习：
将所有员工薪水修改为5000元。
UPDATE emp SET salary=5000
将姓名为’zs’的员工薪水修改为3000元。
UPDATE emp SET salary=3000 WHERE name=’ zhangsan’;
将姓名为’aaa’的员工薪水修改为4000元,job改为ccc。
UPDATE emp SETsalary=4000,gender=‘female’ WHERE name=‘lisi’;
将wu的薪水在原有基础上增加1000元。
UPDATE emp SETsalary=salary+1000 WHERE gender=‘male’;

（3）删除操作：DELETE:
语法 ： DELETE from 表名 【WHERE 列名=值】
练习 ：
删除表中名称为’zs’的记录。
DELETE FROM emp WHERE name=‘zs’;
删除表中所有记录。
DELETE FROM emp;
使用truncate删除表中记录。
TRUNCATE TABLE emp;

• DELETE 删除表中的数据，表结构还在;删除后的数据可以找回
• TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。
• 删除的数据不能找回。执行速度比DELETE快。

练习题:
Manager(管理员表)：
mid 编号 int （主键）
mname 名字 varchar(20)
age 年龄 int
sex 性别 char(2)
password 密码 varchar(20)
address 地址 varchar(20)
phone 电话 varchar(20)
数据：
1 王子 18 男 123 北京 110
2 公主 20 女 456 上海 220
3 太子 23 男 789 南京 330
需求：
(1)创建表
(2)将数据插入到表中
(3)将王子的年龄修改为24
(4)将地址是南京的管理员改为天津
(5)将性别是女,并且年龄大于30的用户密码改为888888
(6)将所有用户的密码恢复最初设置111111
(7)将员工的电话中不是110的电话号码改为7654321
(8)将王子的年龄改为18，地址改为承德，性别改为女
(9) 删除王子的信息
(10)删除地址在南京并且年龄大于60的员工信息
(11)删除不在北京的员工信息
(12)删除地址在北京或上海的员工信息
(13)删除电话号码是空的员工信息
小结:
为空的条件：列名 is null or 列名=’’
注:两个单引号表示空字符串
日期类型值的区别:
date：yyyy-MM-dd （年月日）
time：hh:mm:ss (时分秒)
datetime:yyyy-MM-dd hh:mm:ss (年月日时分秒)
获取当前系统时间:now()
select now();

v
1.txt
1.42KB

2.6 DCL

1、创建用户：
create user 用户名@指定ip identified by 密码;
create user test123@localhost IDENTIFIED by ‘test123’
create user 用户名@客户端ip identified by 密码; 指定IP才能登陆
create user test456@10.4.10.18 IDENTIFIED by ‘test456’
create user 用户名@‘% ’ identified by 密码 任意IP均可登陆
create user test7@’%’ IDENTIFIED by ‘test7’

2、用户授权：

grant 权限1,权限2,…,权限n on
数据库名.* to 用户名@IP; 给指定用户授予指定指定数据库指定权限

grant select,insert,update,delete,create on
chaoshi.* to ‘test456’@‘127.0.0.1’;

grant all on . to 用户名@IP 给指定用户授予所有数据库所有权限

grant all on . to ‘test456’@‘127.0.0.1’

3、用户权限查询：
show grants for 用户名@IP;
show grants for ‘root’@’%’;

4、撤销用户权限：

revoke 权限1,权限2,…,权限n on 数据库名.* from 用户名@IP;
REVOKE SELECT ON . FROM ‘root’@’%’ ;

5、删除用户：
drop user 用户名@IP;
drop user test123@localhost;

第三章 DQL数据查询

DQL数据查询语言（重要）
数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。
查询返回的结果集是一张虚拟表。
查询关键字：SELECT
语法： SELECT 列名 FROM表名 【WHERE --> BROUP BY–>HAVING–> ORDER BY】

• 表示所有列
  SELECT 要查询的列名称
  FROM 表名称
  WHERE 限定条件 /行条件/
  GROUP BY grouping_columns /对结果分组/
  HAVING condition /分组后的行条件/
  ORDER BY sorting_columns /对结果分组/
  LIMIT offset_start, row_count /结果限定/

示例操作：
1>创建学生表并添加数据
#创建表stu
CREATE TABLE stu (
sid CHAR(6),
sname VARCHAR(50),
age INT,
gender VARCHAR(50)
);
#添加数据
INSERT INTO stu VALUES(‘S_1001’, ‘liuYi’, 35, ‘male’);
INSERT INTO stu VALUES(‘S_1002’, ‘chenEr’, 15, ‘female’);
INSERT INTO stu VALUES(‘S_1003’, ‘zhangSan’, 95, ‘male’);
INSERT INTO stu VALUES(‘S_1004’, ‘liSi’, 65, ‘female’);
INSERT INTO stu VALUES(‘S_1005’, ‘wangWu’, 55, ‘male’);
INSERT INTO stu VALUES(‘S_1006’, ‘zhaoLiu’, 75, ‘female’);
INSERT INTO stu VALUES(‘S_1007’, ‘sunQi’, 25, ‘male’);
INSERT INTO stu VALUES(‘S_1008’, ‘zhouBa’, 45, ‘female’);
INSERT INTO stu VALUES(‘S_1009’, ‘wuJiu’, 85, ‘male’);
INSERT INTO stu VALUES(‘S_1010’, ‘zhengShi’, 5, ‘female’);
INSERT INTO stu VALUES(‘S_1011’, ‘xxx’, NULL, NULL);

2>创建雇员表并添加数据

#创建雇员表
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
deptno INT
) ;
#添加数据
INSERT INTO emp values(7369,‘SMITH’,‘CLERK’,7902,‘1980-12-17’,800,NULL,20);
INSERT INTO emp values(7499,‘ALLEN’,‘SALESMAN’,7698,‘1981-02-20’,1600,300,30);
INSERT INTO emp values(7521,‘WARD’,‘SALESMAN’,7698,‘1981-02-22’,1250,500,30);
INSERT INTO emp values(7566,‘JONES’,‘MANAGER’,7839,‘1981-04-02’,2975,NULL,20);
INSERT INTO emp values(7654,‘MARTIN’,‘SALESMAN’,7698,‘1981-09-28’,1250,1400,30);
INSERT INTO emp values(7698,‘BLAKE’,‘MANAGER’,7839,‘1981-05-01’,2850,NULL,30);
INSERT INTO emp values(7782,‘CLARK’,‘MANAGER’,7839,‘1981-06-09’,2450,NULL,10);
INSERT INTO emp values(7788,‘SCOTT’,‘ANALYST’,7566,‘1987-04-19’,3000,NULL,20);
INSERT INTO emp values(7839,‘KING’,‘PRESIDENT’,NULL,‘1981-11-17’,5000,NULL,10);
INSERT INTO emp values(7844,‘TURNER’,‘SALESMAN’,7698,‘1981-09-08’,1500,0,30);
INSERT INTO emp values(7876,‘ADAMS’,‘CLERK’,7788,‘1987-05-23’,1100,NULL,20);

3>创建部门表并添加数据

#创建部门表
CREATE TABLE dept(
deptno INT,
dname varchar(14),
loc varchar(13)
);
#添加数据
INSERT INTO dept values(10, ‘ACCOUNTING’, ‘NEW YORK’);
INSERT INTO dept values(20, ‘RESEARCH’, ‘DALLAS’);
INSERT INTO dept values(30, ‘SALES’, ‘CHICAGO’);
INSERT INTO dept values(40, ‘OPERATIONS’, ‘BOSTON’);

3.1 简单查询

查询所有列
SELECT* FROM stu;
查询指定列
SELECT sid, sname, age FROM stu;

3.2 条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
=、!=、<>、<、<=、>、>=; BETWEEN…AND； IN(set)； IS NULL； AND；OR； NOT；

(1)查询性别为女，并且年龄50的记录
SELECT * FROM stu WHERE gender=‘female’ AND ge<50;

(2)查询学号为S_1001，或者姓名为liSi的记录
SELECT * FROM stu WHERE sid =‘S_1001’ OR sname=‘liSi’;

(3)查询学号为S_1001，S_1002，S_1003的记录
列名 in (列值1,列值2)
SELECT * FROM stu
WHERE sid IN (‘S_1001’,‘S_1002’,‘S_1003’);

(4)查询学号不是S_1001，S_1002，S_1003的记录
SELECT * FROM tab_student
WHERE sid NOT IN(‘S1001’,‘S1002’,‘S_1003’);

(5)查询年龄为null的记录
SELECT * FROM stu WHERE age IS NULL;
(6)　查询年龄在20到40之间的学生记录
SELECT * FROM stu WHERE age>=20 AND age<=40;

或者:列名 between 开始值 and 结束值；//注意:1.开始值<结束值 2.包含临界值的
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
(7)　查询性别非男的学生记录
SELECT * FROM stu WHERE gender!=‘male’;
或者
SELECT * FROM stu WHERE gender<>‘male’;

或者
SELECT * FROM stu WHERE NOT gender=‘male’;

(8) 查询姓名不为null的学生记录
SELECT * FROM stu WHERE NOT sname IS NULL;

或者
SELECT * FROM stu WHERE sname IS NOT NULL;

3.3 模糊查询

当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。
语法: 列名 like ‘表达式’ //表达式必须是字符串
通配符:
_(下划线): 任意一个字母
%：任意0~n个字母,‘张%’

(1)查询姓名由3个字构成的学生记录
SELECT * FROM stu WHERE sname LIKE ‘___’;

模糊查询必须使用LIKE关键字。其中 “”匹配任意一个字，3个“”表示3个任意字。

(2)查询姓名由5个字母构成，并且第5个字母为“i”的学生记录
SELECT * FROM stu WHERE sname LIKE ‘____i’;

(3)查询姓名以“z”开头的学生记录
SELECT * FROM stu WHERE sname LIKE ‘z%’;

其中“%”匹配0~n个任何字母。

(4)查询姓名中第2个字母为“i”的学生记录
SELECT * FROM stu WHERE sname LIKE ‘_i%’;

(5)查询姓名中包含“a”字母的学生记录

SELECT * FROM stu WHERE sname LIKE ‘%a%’;

3.4 字段控制查询

(1)去除重复记录
去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT：
SELECT DISTINCT sal FROM emp;

(2)查看雇员的月薪与佣金之和
　 因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。
SELECT *,sal+comm FROM emp;

comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

SELECT *,sal+IFNULL(comm,0) FROMemp;

(3)给列名添加别名

在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为total：

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

给列起别名时，是可以省略AS关键字的：
SELECT *,sal+IFNULL(comm,0) total FROM emp;

3.5 排序

(1)　查询所有学生记录，按年龄升序排序

SELECT *
FROM stu
ORDER BY sage ASC;

或者
SELECT *
FROM stu
ORDER BY sage;

(2)　查询所有学生记录，按年龄降序排序

SELECT *
FROM stu
ORDER BY age DESC;

(3)　查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
多列排序：当前面的列的值相同的时候，才会按照后面的列值进行排序
SELECT * FROM emp
ORDER BY sal DESC,empno ASC;

3.6 聚合函数

聚合函数是用来做纵向运算的函数：
COUNT(列名)：统计指定列不为NULL的记录行数；
MAX(列名)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN(列名)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM(列名)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG(列名)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

(1)　COUNT
当需要纵向统计时可以使用COUNT()。
查询emp表中记录数：
SELECT COUNT(*) AS cnt FROM emp;

查询emp表中有佣金的人数：
SELECT COUNT(comm) cnt FROM emp;

注意，因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。
l 查询emp表中月薪大于2500的人数：
SELECT COUNT(*) FROM emp
WHERE sal > 2500;

l 统计月薪与佣金之和大于2500元的人数：
SELECT COUNT(*) AS cnt FROM empWHERE sal+IFNULL(comm,0) > 2500;

l 查询有佣金的人数，以及有领导的人数：
SELECT COUNT(comm), COUNT(mgr)FROM emp;
(2)　SUM和AVG
当需要纵向求和时使用sum()函数。
l 查询所有雇员月薪和：
SELECT SUM(sal) FROM emp;

l 查询所有雇员月薪和，以及所有雇员佣金和：
SELECT SUM(sal), SUM(comm) FROM emp;

l 查询所有雇员月薪+佣金和：
SELECT SUM(sal+IFNULL(comm,0))FROM emp;
l 统计所有员工平均工资：
SELECT AVG(sal) FROM emp;

(3)　MAX和MIN

l 查询最高工资和最低工资：
SELECT MAX(sal), MIN(sal) FROMemp;

3.7 分组查询

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。
注意:如果查询语句中有分组操作，则select后面能添加的只能是聚合函数和被分组的列名

3.7.1 分组查询

l 查询每个部门的部门编号和每个部门的工资和：
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;

l 查询每个部门的部门编号以及每个部门的人数：

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

l 查询每个部门的部门编号以及每个部门工资大于1500的人数：
SELECT deptno,COUNT(*)
FROM emp
WHERE sal>1500
GROUP BY deptno;

3.7.2 HAVING子句

l 查询工资总和大于9000的部门编号以及工资和：

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 9000;

注：having与where的区别:

1.having是在分组后对数据进行过滤,where是在分组前对数据进行过滤
2.having后面可以使用分组函数(统计函数)
where后面不可以使用分组函数。
WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

补充: 多列分组
– 统计出stu表中每个班级的男女生各多少人
select gradename,gender ,count(*) from stu
group by gradename,gender

3.8 LIMIT

LIMIT用来限定查询结果的起始行，以及总行数。
limit 开始下标,显示条数；//开始下标从0开始
limit 显示条数；//表示默认从0开始获取数据
1.查询5行记录，起始行从0开始
SELECT * FROM emp LIMIT 0, 5;
注意，起始行从0开始，即第一行开始！
2.查询10行记录，起始行从3开始
SELECT* FROM emp LIMIT 3, 10;

3.8.1 分页查询

如果一页记录为10条，希望查看第3页记录应该怎么查呢？
l 第一页记录起始行为0，一共查询10行；
l 第二页记录起始行为10，一共查询10行；
l 第三页记录起始行为20，一共查询10行；
pageIndex 页码值 pageSize 每页显示条数
limit (pageindex-1)*pagesize,pagesize;
查询语句书写顺序：select – from- where- groupby- having- order by-limit
查询语句执行顺序：from - where -group by -having - select - order by-limit

第四节 外键约束（foreign key）

check约束(检查约束)-> age>0 and age<120

外键:某张表的主键值在其他表中存在，则在其他表中称为外键,对应的列称为外键列
外键列所在的表称为外键表
如何维护外键约束?
方式:在外键表中创建外键约束
在外键表(有外键列的表)中添加外键约束
外键名的命名格式:fk_当前表名_外键列名
mysql-02主外键练习
题.docx
96.26KB
思考?
外键列是用来维护两表关系的，但是外键约束是必须的吗？
解析:外键约束不是必须的，没有外键约束，只会影响到数据的安全性.但是外键列是一定要有的。
思考?
在创建外键列的时候，如何选择在那张表中添加?
语句添加外键约束:
ALTER TABLE 表名 ADD CONSTRAINT 自定义的约束名称 FOREIGN KEY (当前表的外键列名) REFERENCES 关联表名 (主键列名`);

ALTER TABLE student1 ADD CONSTRAINT fk_student1_did FOREIGN KEY (did) REFERENCES department (deptno);

练习1:
Manager(管理员表)：
mid 编号 int （主键）
mname 名字 varchar(20)
age 年龄 int
sex 性别 char(2)
password 密码 varchar(20)
address 地址 varchar(20)
phone 电话 varchar(20)
数据：
1 王子 18 男 123 北京 110
2 公主 20 女 456 上海 220
3 太子 23 男 789 南京 330
(14)查询公主的所有信息
(15)查询年龄在18-30之间的管理员姓名
(16)查询表中所有的用户名和电话
(17)查询性别是男，名字是王子的个人信息
(18)查询出地址在北京和上海的员工信息

练习2：
scores
stuid int 学生id
java int java成绩
mysql int mysql成绩
stuname varchar(20) 学生姓名
数据：
1 67 78 张三
2 87 55 李四
3 66 90 王五
4 98 78 赵六
5 80 88 田七
需求：
(1)对java成绩进行降序排序
(2)得到mysql成绩前三名
(3)得到java学生中最后一名的学生信息
(4)查询出两门成绩都优秀(>=80)的学生姓名
(5)查询出成绩在90分以上(>=90)的学生信息
(6)查询出每名学员的java,mysql,总成绩
(7)显示出每名学生的总分以及姓名

练习3：
测试数据：
郭敬明 1371234567 北京 java S1101 89 1979-04-05
张三丰 1372839201 上海 数据库 S1102 67 1967-09-07
赵敏 1387839201 山东 mysql S1103 99 1987-09-07
Student2
stuname 姓名 varchar(20)
telphone 电话 varchar(20)
address 住址 varchar(20)
subject 科目 varchar(20)
stuNo 学号 varchar(20)
score 成绩 int
birthday 出生日期 date
//1.要查询列 2.条件
a.查询住址为“山东”的学生姓名、电话、住址
b.查询名称中含有“数据库”字样科目信息
c.查询电话中以“1387”开头的学生信息
d.查询姓姜的，三个字的学生信息
e.查询学号为S1101的指定java,mysql科目考试成绩
f.查询出80后学员信息
g.查询出家庭住址在北上广的学生名字
h.显示成绩在第5-10名的学生名字和电话
i.查询分数在80-90之间并且在北京的学生

练习4:聚合函数练习
表：scores2
年级 grade varchar(10)
学号 stuno varchar(20)
考试时间 examDate date
科目 subject varchar(20)
成绩 score int
学期 xueqi int
数据:
S1 S1101 2015-02-03 C 89 1
S2 S1103 2015-03-03 JAVA 90 2
S3 S1102 2015-07-03 C 100 1
1.查询学生总人数
2.学号为S1101的学生第一学期考试总成绩,平均分
3.查询2013年3月22日科目“C”的最高分、最低分、平均分
4.查询2013年3月22日科目“C”及格学生的平均分
5.查询所有参加“C”科目考试的平均分
6.查看考java的人数

练习5:分组练习
表名：student
年级(grade) varchar(10)
学生姓名(name) varchar(10)
学时(xueshi) int --每人单个学时
参加考试(isexam) char(1) 是/否、
课程(subject) varchar(10)
分数(score) int
数据：
1 张三 10 是 java 99
1 李四 10 否 java 0
2 王五 20 是 mysql 88
2 赵六 20 是 mysql 77
2 王五 20 是 java 99
2 赵六 20 否 java 0
1 张三 10 是 mysql 88
练习：
a:查询每个年级的总学时数，并按照升序排列
b:查询每个参加考试的学员的平均分
c:查询每门课程的平均分，并按照降序排列

练习6:综合练习
Student
科目名称 subjectName varchar(20)
学生姓名 stuname varchar(20)
学生地址 address varchar(20)
学生性别 sex char(2)
电子邮件 email varchar(30)
年级 grade varchar(10)
出生日期 birthday date
参考日期 examDate date
成绩 scores int
数据：
JAVA 张三 北京 男 123@qq.com S1 1990-03-04 2013-5-6 89
html 李四 上海 男 S2 1993-08-04 2014-5-6 87
html 王五 北京 男 123@qq.com S2 1990-03-04 2015-4-6 90

1.查询S2的科目名称
2.查询S2男同学的姓名和住址
3.查询无电子邮件的学生姓名和年级信息
4.查询出生日期在1993年之后的S2的学生姓名和年级信息
5.查询参加了日期为2013年2月15日的“HTML”
科目考试的成绩信息

练习7:

mysql数据库查询练习
3.doc
17.5KB

作业题

1.设有成绩表如下所示
编号 姓名 科目 分数
1 张三 数学 90
2 张三 语文 50
3 张三 地理 40
4 李四 语文 55
5 李四 政治 45
6 王五 政治 30
7 李四 数学 80
8 王五 语文 70
1.查询两门及两门以上不及格的学生姓名
2.查询学生平均分
3.查询姓名是张三的学生 成绩和
4.将学生信息按照 分数倒序
5.获取学生信息中 分数最低的学生姓名和分数最高的学生姓名
6.查询两门及两门以上不及格同学的平均分。

2.写出 SQL语句的格式 : 插入 ，更新 ，删除
表名 user
name tel content date
张三 13333663366 大专毕业 2006-10-11
张三 13612312331 本科毕业 2006-10-15
张四 021-55665566 中专毕业 2006-10-15
(a).有一新记录(小王 13254748547 高中毕业 2007-05-06)请用SQL语句新增至表中
(b).请用sql语句把张三的时间更新成为当前系统时间
©.请写出删除名为张四的全部记录

3.写出 SQL语句的格式 :对emp表进行查询操作
1.找出奖金高于工资的雇员
2.找出奖金高于工资60%的雇员
3.找出部门10中所有经理和部门20中所有店员的信息
4.找出部门10中所有经理(MANAGER)，部门20中所有店员，既不是经理又不是店员但其5.薪资大于或等于2000的所有员工的信息。
6.查询没有奖金或者奖金低于100的员工信息
7.查询姓名不带”R”的员工姓名
8.显示员工的姓名和入职时间，根据入职时间，将最老的员工排放在最前面。
9.显示所有员工的姓名、工作和工资，按照工作的降序排序，若工作相同则按工资升序排序。

面试题
有3 个表（15 分钟）：【多表查询基础】
Student 学生表(学号，姓名，性别，年龄，组织部门)
Course 课程表(编号，课程名称)
Sc 选课表(学号，课程编号，成绩)
表结构如下：
1）写一个SQL 语句，查询选修了’计算机原理’的学生学号和姓名（3 分钟）
2）写一个SQL 语句，查询’周星驰’同学选修了的课程名字（3 分钟）
3）写一个SQL 语句，查询选修了5 门课程的学生学号和姓名（9 分钟）

第一节 再看数据完整性

作用：保证用户输入的数据保存到数据库中是正确的。
确保数据的完整性 = 在创建表时给表中添加约束
完整性的分类：

• 实体完整性(行完整性):
• 域完整性(列完整性):
• 引用完整性(关联表完整性):

1.1 实体完整性

实体：即表中的一行(一条记录)代表一个实体（entity）
实体完整性的作用：标识每一行数据不重复。

约束类型：

主键约束（primary key）
唯一约束(unique)
自动增长列(auto_increment)

1.1.1 主键约束（primary key）

注：每个表中要有一个主键。
特点：数据唯一，且不能为null

示例：
第一种添加方式：
CREATE TABLE student(
id int primary key,
name varchar(50)
);

第二种添加方式：此种方式优势在于，可以创建联合主键

CREATE TABLE student(
id int,
name varchar(50),
primary key(id)
);

CREATE TABLE student(
classid int,
stuid int,
name varchar(50),
primary key(classid,stuid)
);

第三种添加方式：
CREATE TABLE student(
id int,
name varchar(50)
);
ALTER TABLE student ADD PRIMARY KEY (id);

1.1.2 唯一约束(unique)

特点：数据不能重复。
CREATE TABLE student(
Id int primary key,
Name varchar(50) unique
);

1.1.3 自动增长列(auto_increment)

sqlserver数据库 (identity-标识列) oracle数据库(sequence)
给主键添加自动增长的数值，列只能是整数类型

CREATE TABLE student(
Id int primary key auto_increment,
Name varchar(50)
);
INSERT INTO student(name) values(‘tom’);

1.2 域完整性

域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较
域代表当前单元格
域完整性约束：数据类型 非空约束（not null） 默认值约束(default)

check约束（mysql不支持）check(sex='男’or sex=‘女’)

1.2.1 数据类型

数值类型

| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
| --------- – | ---------------- | ----------------------- | ---------------------------- | ---------- |
| tinyint | 1 字节 | (-128，127) | (0，255) | 小整数值 |
| smallint | 2 字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| mediumint | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT| 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| bigint | 8 字节 | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| float | 4 字节 | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度浮点数值 |
| double | 8 字节 | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度浮点数值 |

日期类型:

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性

类型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳 ,当更新数据的时候自动添加更新时间

字符串类型:
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

1.2.2 非空约束

not null

CREATE TABLE student(
Id int pirmary key,
Name varchar(50) not null,
Sex varchar(10)
);

INSERT INTO student values(1,’tom’,null);

1.2.3 默认值约束

default

CREATE TABLE student(
Id int pirmary key,
Name varchar(50) not null,
Sex varchar(10) default ‘男’
);
insert intostudent1 values(1,‘tom’,‘女’);

insert intostudent1 values(2,‘jerry’,default);

1.3 引用完整性

（参照完整性）

外键约束：FOREIGN KEY

示例：

CREATE TABLE student(
id int primary key,
name varchar(50) not null,
sex varchar(10) default ‘男’
);

create table score(
id int primary key,
score int,
sid int ,
constraint fk_score_sid foreign key(sid) references student(id)
);
constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)
外键列的数据类型一定要与主键的类型一致

第二种添加外键方式。

ALTER TABLEscore1 ADD CONSTRAINT
fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);

第二章 多表查询

多个表之间是有关系的，那么关系靠谁来维护?
多表约束：外键列

2.1 多表的关系

2.1.1 一对多/多对一关系

客户和订单，分类和商品，部门和员工.
一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.

2.1.2 多对多关系

学生和课程

多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.

2.1.3 一对一关系

在实际的开发中应用不多.因为一对一可以创建成一张表.
两种建表原则：
唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique.

主键对应：让一对一的双方的主键进行建立关系.

2.2 多表查询

多表查询有如下几种：

1 合并结果集:UNION 、 UNION ALL
2 连接查询
2.1内连接 [INNER] JOIN ON
2.2外连接 OUTER JOIN ON

• 左外连接 LEFT [OUTER] JOIN
• 右外连接 RIGHT [OUTER] JOIN
• 全外连接（MySQL不支持）FULL JOIN
  2.3自然连接 NATURAL JOIN

3 子查询

2.2.1 合并结果集

作用：合并结果集就是把两个select语句的查询结果合并到一起！

合并结果集有两种方式：
l UNION：去除重复记录，例如：SELECT* FROM t1 UNION SELECT * FROM t2；
l UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。

注意：被合并的两个结果：列数、列类型必须相同。

2.2.2 连接查询

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

示例 1：现有两张表

emp表
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
deptno INT
);

dept表
CREATE TABLE dept(
deptno INT,
dname varchar(14),
loc varchar(13)
);

select * from emp,dept;

使用主外键关系做为条件来去除无用信息
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列了。
SELECT emp.ename,emp.sal,emp.comm,dept.dname
FROM emp,dept
WHERE emp.deptno=dept.deptno;

一：内连接

上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！

语法：
select 列名
from 表1
inner join 表2
on 表1.列名=表2.列名 //外键列的关系
where…
等价于：
select 列名
from 表1,表2
where 表1.列名=表2.列名 and …(其他条件)
注:<1>表1和表2的顺序可以互换
<2>找两张表的等值关系时,找表示相同含义的列作为等值关系。
<3>点操作符表示“的”,格式:表名.列名
<4>可以使用as,给表名起别名,注意定义别名之后,统一使用别名
示例：
//查询学生表中的学生姓名和分数表中的分数
select name,score
from student as s
inner join scores as c
on s.studentid=c.stuid
等价于：
select name,score
from student as s,scores as c
where s.studentid=c.stuid
三表联查：
语法：
select 列名 from 表1
inner join 表2 on 表1.列名=表2.列名
inner join 表3 on 表1或表2.列名=表3.列名
等价于:
select 列名 from 表1,表2,表3
where 表1.列名=表2.列名 and 表1/表2.列名=表3.列名

SQL标准的内连接为：
SELECT *
FROM emp e
INNER JOIN dept d
ON e.deptno=d.deptno;

内连接的特点：查询结果必须满足条件。

练习:
student2
stuid 学员id int 主键，自增长
stuname 学员名字 varchar(10)
password 登录密码 varchar(10)
birthday 出生日期 date
数据：
1 花儿 111111 1990-02-09
2 少年 222222 1989-03-12
3 小胡 333333 1989-09-12
subject2 —科目表
subjectid 科目编号 int 主键，自增长
subjectName 科目名称 varchar(10)
数据：
1 java
2 mysql student2.stuid=scores2.studentid
3 html subject2.subjectid=scores2.subject
scores2
sid 分数主键 int 主键，自增长
score 分数 int
subject 科目 int
studentid 学生编号 int
数据：
1 89 1 1
2 90 2 1
3 87 2 2
4 98 3 3
需求：1.显示出花儿的考试成绩以及对应科目
2.显示出所有考试学生的信息
3.查询出mysql的考试信息
4.查询出考试学员的总分
5.查询每科的平均分

二：外连接

包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。

– 显示还没有员工的部门名称?
– 外联查询
– 左外联:select 列名 from 主表 left join 次表 on 主表.列名=次表.列名
– 1.主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null
– 2.主表和次表不能随意调换位置
select depname,name from
(select e.*,d.depname from department d left join employee e
on e.depid=d.depid
) aa where aa.name is null;
– 右外联:select 列名 from 次表 right join 主表 on 主表.列名=次表.列名

a.左外连接
SELECT * FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

我们还是用上面的例子来说明。其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。

b.右外连接

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

SELECT * FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;

连接查询心得：

连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。

两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件*。*

c.自然连接
自然连接（NATURAL INNER JOIN）：说真的，这种连接查询没有存在的价值，自然连接无需指定连接列，SQL会检查两个表中是否相同名称的列，且假设他们在连接条件中使用，并且在连接条件中仅包含一个连接列。不允许使用ON语句，不允许指定显示列，显示列只能用*表示（ORACLE环境下测试的）。下面给出几个例子。
语句：
select * from emp e natural join dept d;

mysql查询练习题-
2.doc
45.5KB

2.2.3 子查询

一个select语句中包含另一个完整的select语句。

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

l 子查询出现的位置：

a. where后，作为条为被查询的一条件的一部分；
b. from后，作表；

l 当子查询出现在where后作为条件时，还可以使用如下关键字：

a. any
b. all

l 子查询结果集的形式：

a. 单行单列（用于条件）
b. 单行多列（用于条件）
c. 多行单列（用于条件）
d. 多行多列（用于表）

示例：

1. 工资高于JONES的员工。

分析：

查询条件：工资>JONES工资，其中JONES工资需要一条子查询。
第一步：查询JONES的工资
SELECT sal FROM emp WHERE ename=‘JONES’;

第二步：查询高于甘宁工资的员工

SELECT * FROM emp WHERE sal > (第一步结果);

结果：

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename=‘JONES’);

2. 查询与SCOTT同一个部门的员工。

l 子查询作为条件
l 子查询形式为单行单列

分析：

查询条件：部门=SCOTT的部门编号，其中SCOTT 的部门编号需要一条子查询。
第一步：查询SCOTT的部门编号
SELECT deptno FROM emp WHERE ename=‘SCOTT’;

第二步：查询部门编号等于SCOTT的部门编号的员工

SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename=‘SCOTT’);

3. 工资高于30号部门所有人的员工信息

分析：
SELECT * FROMemp WHERE sal>(SELECT MAX(sal)FROM emp WHERE deptno=30);

查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。

第一步：查询30部门所有人工资
SELECT sal FROM emp WHERE deptno=30;

第二步：查询高于30部门所有人工资的员工信息

SELECT * FROM emp WHERE sal > ALL (第一步)

结果：
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

l 子查询作为条件
l 子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）

第三节 综合练习

某网上商城数据库如下图所示

#一对多的实现
#创建分类表
create table category(
cid varchar(32) PRIMARY KEY ,
cname varchar(100) #分类名称
);

商品表

CREATE TABLE products (
pid varchar(32) PRIMARY KEY ,
name VARCHAR(40) ,
price DOUBLE
);

#添加外键字段
alter table products add column category_id varchar(32);

#添加约束
alter table products add constraint product_fk foreign key (category_id) references category (cid);

#多对多的实现
#订单表
create table orders(
oid varchar(32) PRIMARY KEY ,
totalprice double #总计
);

订单项表

create table orderitem(
oid varchar(50), #订单id
pid varchar(50) #商品id
);
#联合主键（可省略）
alter table orderitem add primary key (oid,pid);

订单表和订单项表的主外键关系

alter table orderitem add constraint orderitem_orders_fk foreign key (oid) references orders(oid);

商品表和订单项表的主外键关系

alter table orderitem add constraint orderitem_product_fk foreign key (pid) references products(pid);

#初始化数据
#给商品表初始化数据
insert into products(pid,name,price,category_id) values(‘p001’,‘联想’,5000,‘c001’);
insert into products(pid,name,price,category_id) values(‘p002’,‘海尔’,3000,‘c001’);
insert into products(pid,name,price,category_id) values(‘p003’,‘雷神’,5000,‘c001’);
insert into products(pid,name,price,category_id) values(‘p004’,‘JACK JONES’,800,‘c002’);
insert into products(pid,name,price,category_id) values(‘p005’,‘真维斯’,200,‘c002’);
insert into products(pid,name,price,category_id) values(‘p006’,‘花花公子’,440,‘c002’);
insert into products(pid,name,price,category_id) values(‘p007’,‘劲霸’,2000,‘c002’);
insert into products(pid,name,price,category_id) values(‘p008’,‘香奈儿’,800,‘c003’);
insert into products(pid,name,price,category_id) values(‘p009’,‘相宜本草’,200,‘c003’);
insert into products(pid,name,price,category_id) values(‘p010’,‘梅明子’,200,null);

#给分类表初始化数据
insert into category values(‘c001’,‘电器’);
insert into category values(‘c002’,‘服饰’);
insert into category values(‘c003’,‘化妆品’);
insert into category values(‘c004’,‘书籍’);

3.1 综合练习-【多表查询】

1>查询用户的订单,没有订单的用户不显示

2>查询所有用户的订单详情

3>查询所有订单的用户详情

3.2 综合练习2-【子查询】

1>查看用户为张三的订单详情

2>查询出订单的价格大于300的所有用户信息。

3>查询订单价格大于300的订单信息及相关用户的信息。

3.2 综合练习3-【分页查询】

1>查询所有订单信息，每页显示5条数据

第四节:扩展

4.1 多行新增

insert into 表名(列名) values (列值),(列值),(列值)；

4.2 多表更新

(1)update 表1,表2 set 列名=列值 where 表1.列名=表2.列名 and 其他限定条件
(2)update 表1
inner join 表2 on 表1.列名=表2.列名
set 列名=列值
where 限定条件
示例：update employee e,salary s
set title=‘助工’,salary=1000
where e.empid=s.empid and name=‘李四’

4.3 多表删除

语法：
delete 被删除数据的表 from 删除操作中使用的表
where 限定条件
注:多张表之间使用逗号间隔
示例：
//删除人事部的信息
delete d,e,s from department d,employee e,salary s
where d.depid=e.depid and s.empid=e.empid and depname=‘人事部’

4.4 日期运算函数

now() 获得当前系统时间
year(日期值) 获得日期值中的年份

date_add(日期,interval 计算值 计算的字段);
注：计算值大于0表示往后推日期，小于0表示往前推日期
示例：
date_add(now(),interval -40 year);//40年前的日期

第五节：数据库优化

1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。
3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or Name = ‘admin’
可以这样查询：
select id from t where num = 10
union all
select id from t where Name = ‘admin’
5.in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
很多时候用 exists 代替 in 是一个好的选择

第六节:导入导出数据库

作业题

数据库结构
创建四张表 分别存储 学生信息 课程信息 分数 讲师信息表 存储相应数据
学生信息表 Student
字段名 字段类型 字段约束 / 含义
Sno Varchar(3) Not null / 学员编号
Sname Varchar(4) Not null / 学员姓名
Ssex Varchar(2) Not null / 性别
Sbirthday Datetime 生日
Classnum Varchar(5) 班级号

CREATE TABLE STUDENT
(
SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5)
)
课程信息表 course
字段名 字段类型 字段约束 / 含义
Cno Varchar(5) Not null / 课程编号
Cname Varchar(10) Not null / 课程名称
Tno Varchar(10) Not null / 授课老师编号

CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL)
成绩表score
字段名 字段类型 字段约束 / 含义
Sno Varchar(3) Not null / 学员编号
Cno Varchar(5) Not null / 课程编号
Degree Double(3,1) Not null / 分数

CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL)
讲师表teacher
字段名 字段类型 字段约束 / 含义
Tno Varchar(3) Not null / 讲师编号
Tname Varchar(4) Not null / 讲师姓名
Tsex Varchar(2) Not null / 讲师性别
Tbirthday Datetime Not null / 出生日期
Prof Varchar(6) 等级
Depart Varchar(10) 所属院系

CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL)

向表中存储数据

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,‘曾华’ ,‘男’ ,‘1977-09-01’,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,‘匡明’ ,‘男’ ,‘1975-10-02’,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,‘王丽’ ,‘女’ ,‘1976-01-23’,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,‘李军’ ,‘男’ ,‘1976-02-20’,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,‘王芳’ ,‘女’ ,‘1975-02-10’,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,‘陆君’ ,‘男’ ,‘1974-06-03’,95031);

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (‘3-105’ ,‘计算机导论’,825);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (‘3-245’ ,‘操作系统’ ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (‘6-166’ ,‘数据电路’ ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (‘9-888’ ,‘高等数学’ ,100);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,‘3-245’,86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,‘3-245’,75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,‘3-245’,68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,‘3-105’,92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,‘3-105’,88);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,‘3-105’,76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,‘3-105’,64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,‘3-105’,91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,‘3-105’,78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,‘6-166’,85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,‘6-106’,79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,‘6-166’,81);

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (804,‘李诚’,‘男’,‘1958-12-02’,‘副教授’,‘计算机系’);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (856,‘张旭’,‘男’,‘1969-03-12’,‘讲师’,‘电子工程系’);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (825,‘王萍’,‘女’,‘1972-05-05’,‘助教’,‘计算机系’);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (831,‘刘冰’,‘女’,‘1977-08-14’,‘助教’,‘电子工程系’);

1、 查询Student表中的所有记录的Sname、Ssex和Class列。
2、 查询教师所有的单位即不重复的Depart列。
3、 查询Student表的所有记录。
4、 查询Score表中成绩在60到80之间的所有记录。
5、 查询Score表中成绩为85，86或88的记录。
6、 查询Student表中“95031”班或性别为“女”的同学记录。
7、 以Class降序查询Student表的所有记录。
8、 以Cno升序、Degree降序查询Score表的所有记录。
9、 查询“95031”班的学生人数。
10、查询Score表中的最高分的学生学号和课程号。
11、查询‘3-105’号课程的平均分。
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
13、查询最低分大于70，最高分小于90的Sno列。
14、查询所有学生的Sname、Cno和Degree列。
15、查询所有学生的Sno、Cname和Degree列。
16、查询所有学生的Sname、Cname和Degree列。
17、查询“95033”班所选课程的平均分。
18、假设使用如下命令建立了一个grade表：
create table grade(low int(3),upp int(3),rank char(1));
insert into grade values(90,100,‘A’);
insert into grade values(80,89,‘B’);
insert into grade values(70,79,‘C’);
insert into grade values(60,69,‘D’);
insert into grade values(0,59,‘E’);
commit;
现查询所有同学的Sno、Cno和rank列。
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
23、查询“张旭“教师任课的学生成绩。
24、查询选修某课程的同学人数多于5人的教师姓名。
25、查询95033班和95031班全体学生的记录。
26、查询存在有85分以上成绩的课程Cno.
27、查询出“计算机系“教师所教课程的成绩表。
28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
31、查询所有教师和同学的name、sex和birthday.
32、查询所有“女”教师和“女”同学的name、sex和birthday.
33、查询成绩比该课程平均成绩低的同学的成绩表。
34、查询所有任课教师的Tname和Depart.
35 查询所有未讲课的教师的Tname和Depart.
36、查询至少有2名男生的班号。
37、查询Student表中不姓“王”的同学记录。
38、查询Student表中每个学生的姓名和年龄。
39、查询Student表中最大和最小的Sbirthday日期值。
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
41、查询“男”教师及其所上的课程。
42、查询最高分同学的Sno、Cno和Degree列。
43、查询和“李军”同性别的所有同学的Sname.
44、查询和“李军”同性别并同班的同学Sname.
45、查询所有选修“计算机导论”课程的“男”同学的成绩表

面试题

有三张表,学生表S,课程C,学生课程表SC,学生可以选修多门课程,一门课程可以被多个学生选修,通过SC 表关联。【基础】
1）写出建表语句；
2）写出SQL 语句,查询选修了所有选修课程的学生；
3）写出SQL 语句,查询选修了至少5 门以上的课程的学生。

1 简介

JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序

Java 具有坚固、安全、易于使用、易于理解和可从网络上自动下载等特性，是编写数据库应用程序的杰出语言。所需要的只是 Java应用程序与各种不同数据库之间进行对话的方法。

JDBC可以在各种平台上使用Java，如Windows，Mac OS和各种版本的UNIX。

JDBC库包括通常与数据库使用相关的下面提到的每个任务的API。

• 连接数据库。
• 创建SQL或MySQL语句。
• 在数据库中执行SQL或MySQL查询。
• 查看和修改生成的记录。

2 JDBC体系结构

JDBC API支持用于数据库访问的两层和三层处理模型，但通常，JDBC体系结构由两层组成：

• **JDBC API：**这提供了应用程序到JDBC管理器连接。
• **JDBC驱动程序API：**这支持JDBC管理器到驱动程序连接。

JDBC API使用驱动程序管理器和特定于数据库的驱动程序来提供与异构数据库的透明连接。

3 JDBC核心组件

DriverManager： 此类管理数据库驱动程序列表。使用通信子协议将来自java应用程序的连接请求与适当的数据库驱动程序匹配。
Driver:此接口处理与数据库服务器的通信，我们很少会直接与Driver对象进行交互。而是使用DriverManager对象来管理这种类型的对象。

**Connection：**该界面具有用于联系数据库的所有方法。连接对象表示通信上下文，即，与数据库的所有通信仅通过连接对象。

Statement：使用从此接口创建的对象将SQL语句提交到数据库。除了执行存储过程之外，一些派生接口还接受参数。

**ResultSet：**在使用Statement对象执行SQL查询后，这些对象保存从数据库检索的数据。它作为一个迭代器，允许我们移动其数据。

**SQLException：**此类处理数据库应用程序中发生的任何错误

4 CRUD语法介绍

SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建项目，查询内容，更新内容，并删除条目等操作。

Create, Read, Update, and Delete 通常称为CRUD操作。

CREATE DATABASE语句用于创建新的数据库：

SQL> CREATE DATABASE DATABASE_NAME;

DROP DATABASE语句用于删除现有数据库：

SQL> DROP DATABASE DATABASE_NAME;
CREATE TABLE语句用于创建新表。语法是 -

SQL> CREATE TABLE Employees
(
id INT NOT NULL,
age INT NOT NULL,
first VARCHAR(255),
last VARCHAR(255),
PRIMARY KEY ( id )
);

DROP TABLE语句用于删除现有表。

SQL> DROP TABLE table_name;

INSERT的语法类似于以下内容，其中column1，column2等表示要显示在相应列中的新数据

SQL> INSERT INTO table_name VALUES (column1, column2, …);

SELECT语句用于从数据库中检索数据。SELECT的语法是 -

SQL> SELECT column_name, column_name, …
FROM table_name
WHERE conditions;

WHERE子句可以使用比较运算符，例如=，！=，<，>，<=和> =，以及BETWEEN和LIKE运算符。
UPDATE语句用于更新数据。

SQL> UPDATE table_name
SET column_name = value, column_name = value, …
WHERE conditions;

WHERE子句可以使用比较运算符，例如=，！=，<，>，<=和> =，以及BETWEEN和LIKE运算符。
DELETE语句用于从表中删除数据。

SQL> DELETE FROM table_name WHERE conditions;

WHERE子句可以使用比较运算符，例如=，！=，<，>，<=和> =，以及BETWEEN和LIKE运算符。

5 使用步骤

mysql-connector-java-5.1.3
8.jar
960.85KB

构建JDBC应用程序涉及以下六个步骤：

• **导入包：**需要包含包含数据库编程所需的JDBC类的包。大多数情况下，使用*import java.sql。**就足够了。
• **注册JDBC驱动程序：**要求您初始化驱动程序，以便您可以打开与数据库的通信通道。
• **打开连接：**需要使用*DriverManager.getConnection（）*方法创建一个Connection对象，该对象表示与数据库的物理连接。
• **执行查询：**需要使用类型为Statement的对象来构建和提交SQL语句到数据库。
• **从结果集中提取数据：**需要使用相应的*ResultSet.getXXX（）*方法从结果集中检索数据。
• **释放资源：**需要明确地关闭所有数据库资源，而不依赖于JVM的垃圾收集。

6 JDBC连接步骤

建立JDBC连接所涉及的编程相当简单。这是简单的四个步骤

• 导入JDBC包：将Java语言的import语句添加到Java代码中导入所需的类。
• **注册JDBC驱动程序：**此步骤将使JVM将所需的驱动程序实现加载到内存中，以便它可以满足您的JDBC请求。
• **数据库URL配置：**这是为了创建一个格式正确的地址，指向要连接到的数据库。
• **创建连接对象：**最后，调用DriverManager对象的*getConnection（）*方法来建立实际的数据库连接。

Class.forName();

注册驱动程序最常见的方法是使用Java的**Class.forName（）**方法，将驱动程序的类文件动态加载到内存中，并将其自动注册

try {
Class.forName(“oracle.jdbc.driver.OracleDriver”);
}catch(ClassNotFoundException ex) {
System.out.println(“Error: unable to load driver class!”);
System.exit(1);
}

DriverManager.registerDriver()

第二种方法是使用静态**DriverManager.registerDriver（）**方法。

try {
Driver myDriver = new com.mysql.jdbc.Driver();
DriverManager.registerDriver( myDriver );
}catch(ClassNotFoundException ex) {
System.out.println(“Error: unable to load driver class!”);
System.exit(1);
}

数据库URL配置

加载驱动程序后，可以使用**DriverManager.getConnection（）**方法建立连接。为了方便参考，让我列出三个重载的DriverManager.getConnection()方法 -

• getConnection（String url）
• getConnection（String url，Properties prop）
• getConnection（String url，String user，String password）

RDBMS	JDBC驱动程序名称	网址格式
MySQL	com.mysql.jdbc.Driver	**jdbc:mysql://hostname:3306/databaseName
ORACLE	oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:@ hostname：port Number：databaseName
DB2	COM.ibm.db2.jdbc.net.DB2Driver	**jdbc:db2：**hostname：port Number / databaseName
SYBASE	com.sybase.jdbc.SybDriver	jdbc:sybase:Tds: hostname：port Number / databaseName

创建数据库连接对象
String URL = “jdbc:mysql://localhost:3306/EMP”;
String USER = “username”;
String PASS = “password”
Connection conn = DriverManager.getConnection(URL, USER, PASS);
完整的连接地址:

java文件:
jdbc:mysql://localhost:3306/数据库名?useSSL=false&useUnicode=true&characterEncoding=UTF-8

配置文件:
jdbc:mysql://localhostt:3306/数据库名?useSSL=false&useUnicode=true&characterEncoding=UTF-8

使用数据库URL和属性对象

DriverManager.getConnection（）方法的第三种形式需要一个数据库URL和一个Properties对象 -
DriverManager.getConnection(String url, Properties info);

import java.util.*;
String URL = “jdbc:mysql://localhost:3306/EMP”;
Properties info = new Properties( );
info.put( “user”, “username” );
info.put( “password”, “password” );
Connection conn = DriverManager.getConnection(URL, info);

关闭数据库连接

为确保连接关闭，您可以在代码中提供一个“finally”块。一个finally块总是执行，不管是否发生异常。
要关闭上面打开的连接，你应该调用close（）方法如下 -

conn.close();

7 JDBC执行SQL语句

一旦获得了连接，我们可以与数据库进行交互。JDBC Statement和PreparedStatement接口定义了使您能够发送SQL命令并从数据库接收数据的方法和属性。

接口	推荐使用
声明	用于对数据库进行通用访问。在运行时使用静态SQL语句时很有用。Statement接口不能接受参数。
PreparedStatement的	当您计划多次使用SQL语句时使用。PreparedStatement接口在运行时接受输入参数。

8 Statement

创建语句对象

在使用Statement对象执行SQL语句之前，需要使用Connection对象的createStatement（）方法创建一个，如下例所示：
Statement stmt = null;
try {
stmt = conn.createStatement( );
. . .
}
catch (SQLException e) {
. . .
}
finally {
. . .
}

创建Statement对象后，您可以使用它来执行一个SQL语句，其中有三个执行方法之一。

• boolean execute（String SQL）：如果可以检索到ResultSet对象，则返回一个布尔值true; 否则返回false。使用此方法执行SQL DDL语句或需要使用真正的动态SQL时。
• int executeUpdate（String SQL）：返回受SQL语句执行影响的行数。使用此方法执行预期会影响多个行的SQL语句，例如INSERT，UPDATE或DELETE语句。
• ResultSet executeQuery（String SQL）：返回一个ResultSet对象。当您希望获得结果集时，请使用此方法，就像使用SELECT语句一样。

关闭Statement对象

就像我们关闭一个Connection对象以保存数据库资源一样，由于同样的原因，还应该关闭Statement对象。

一个简单的调用close（）方法将执行该作业。如果先关闭Connection对象，它也会关闭Statement对象。但是，应始终显式关闭Statement对象，以确保正确清理。

Statement stmt = null;
try {
stmt = conn.createStatement( );
. . .
}
catch (SQLException e) {
. . .
}
finally {
stmt.close();
}

8.1 SQL注入

就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。具体来说，它是利用现有应用程序，将（恶意的）SQL命令注入到后台数据库引擎执行的能力，它可以通过在Web表单中输入（恶意）SQL语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行SQL语句。比如先前的很多影视网站泄露VIP会员密码大多就是通过WEB表单递交查询字符暴出的，这类表单特别容易受到SQL注入式攻击。

String username =“admin”;
String password=" ‘abc’ or 1=1 “;
String sql=“select * from users where username= '”+username+”’ and password= "+password;

9 PreparedStatement(预状态通道)

该PreparedStatement的接口扩展了Statement接口，它为您提供了一个通用的Statement对象有两个优点附加功能。

此语句使您可以动态地提供参数。

PreparedStatement pstmt = null;
try {
String SQL = “Update Employees SET age = ? WHERE id = ?”;
pstmt = conn.prepareStatement(SQL);
. . .
}
catch (SQLException e) {
. . .
}
finally {
. . .
}

JDBC中的所有参数都由**？**符号，这被称为参数标记。在执行SQL语句之前，必须为每个参数提供值。

所述的setXXX（）方法将值绑定到所述参数，其中XXX代表要绑定到输入参数的值的Java数据类型。如果忘记提供值，将收到一个SQLException。

每个参数标记由其顺序位置引用。第一个标记表示位置1，下一个位置2等等。该方法与Java数组索引不同，从0开始。

关闭PreparedStatement对象

就像关闭Statement对象一样，由于同样的原因，还应该关闭PreparedStatement对象。

一个简单的调用close（）方法将执行该作业。如果先关闭Connection对象，它也会关闭PreparedStatement对象。但是，应始终显式关闭PreparedStatement对象，以确保正确清理。

PreparedStatement pstmt = null;
try {
String SQL = “Update Employees SET age = ? WHERE id = ?”;
pstmt = conn.preparedStatement(SQL);
. . .
}catch (SQLException e) {
. . .
}finally {
pstmt.close();
}

对比statement和PreparedStatement;
(1)statement属于状态通道，PreparedStatement属于预状态通道
(2)预状态通道会先编译sql语句,再去执行，比statement执行效率高
(3)预状态通道支持占位符?,给占位符赋值的时候，位置从1开始
(4)预状态通道可以防止sql注入，原因:预状态通道在处理值的时候以字符串的方式处理

10 ResultSet## 标题

SELECT语句是从数据库中选择行并在结果集中查看行的标准方法。该java.sql.ResultSet中的接口表示结果集数据库查询。

ResultSet对象维护指向结果集中当前行的游标。术语“结果集”是指包含在ResultSet对象中的行和列数据。

如果没有指定任何ResultSet类型，您将自动获得一个TYPE_FORWARD_ONLY。

类型	描述
ResultSet.TYPE_FORWARD_ONLY	光标只能在结果集中向前移动。
ResultSet.TYPE_SCROLL_INSENSITIVE	光标可以向前和向后滚动，结果集对创建结果集后发生的数据库的其他更改不敏感。
ResultSet.TYPE_SCROLL_SENSITIVE。	光标可以向前和向后滚动，结果集对创建结果集之后发生的其他数据库所做的更改敏感。

try {
Statement stmt = conn.createStatement(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
}catch(Exception ex) {
…
}finally {
…
}

数据的存储:

将数据库中的数据保存到对象中?
1.数据库中的一行数据对应java中的一个对象
2.产生数据库和类之间的对应关系:
创建实体类
类名=表名
属性名=列名
两表关系:
四种:双向一对一，一对多，多对一，多对多
多表关系处理数据
(1)数据库通过外键建立两表关系
(2)实体类通过属性的方式建立两表关系

作业题

1.登录
创建user表
id int 主键 自增
username varchar
password varchar
创建user_leaf 用户个人信息表
id int 主键 自增
name
age
sex
u_id 外键
控制台输入用户名 密码
连接数据库 判断和数据库里的是否一致
一致 显示 登录成功 不一致 显示登陆失败
扩展 如登录成功
显示user个人信息表信息
2.根据id查询指定的学生记录
创建学生信息表 创建方法 传入学生id 获取学生信息
student 表
sid(int 主键 自增)
sname(varchar 非空)
sage(int 默认值为10)
ssex(varchar)
birthday(date)
score(double 保留小数点后2位)
扩展 1.将 score单独存储到student_s表中
一对多关系
将数据存储到合适的集合中 遍历结合获取数据
2.将集合中的数据 写出到student.txt文件中 以逗号分隔

面试题## 标题

有如下两张表：【中等难度】
表city： 表state：
CityNo CityName StateNo
BJ 北京（Null）
SH 上海（Null）
GZ 广州GD
DL 大连LN
欲得
到如下结果： City
No City Name State No State Name BJ
北京（Null） （Null） DL
大连LN 辽宁GZ
广州GD 广东SH
上海（Null） （Null） 写相
应的SQL 语句。

1.自定义连接池

数据连接池原理
连接池基本的思想是在系统初始化的时候，将数据库连接作为对象存储在内存中，当用户需要访问数据库时，并非建立一个新的连接，而是从连接池中取出一个已建立的空闲连接对象。使用完毕后，用户也并非将连接关闭，而是将连接放回连接池中，以供下一个请求访问使用。而连接的建立、断开都由连接池自身来管理。同时，还可以通过设置连接池的参数来控制连接池中的初始连接数、连接的上下限数以及每个连接的最大使用次数、最大空闲时间等等，也可以通过其自身的管理机制来监视数据库连接的数量、使用情况等。

1.1 自定义连接池

我们可以通过自定义的方式实现连接池!分析连接池类应该包含特定的属性和方法!

属性: 集合 放置Connection
方法: 获取连接方法
回收连接方法
具体实现代码:

public class Pool{
static LinkedList list = new LinkedList();
static{
for (int i = 0; i < 10; i++) {
Connection connection = JDBCUtils.newInstance().getConnection();
list.add(connection);
}
}
/**
* 从连接池子中获取连接的方式
* @return
*/
public static Connection getConnection(){

    if (list.isEmpty()) {
        //JDBCUtils类是自定义类，封装了连接数据库的信息代码
        Connection connection = JDBCUtils.newInstance().getConnection();
        list.addLast(connection);
    }
    
    Connection conn = list.removeFirst();
    
    return conn;
}
/**
 * 返回到连接池子中
 */
public static void addBack(Connection conn){
    
    if (list.size() >= 10) {
        try {
            conn.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }else{
        list.addLast(conn);  //10
    }
    
}


/**
 * 获取连接池子中连接数量的方法
 */

public static int getSize(){
    return list.size();
}

}

1.2 java规范实现连接池

Java为连接池实现提供了一个规范(接口),规范的写法,我们需要实现DataSource接口!
但是实现DataSource接口有一个弊端,没有提供回收链接方法!这里我们将使用装饰者模式!
装饰Connection!具体实现代码如下:

1. 创建装饰Connection

public class MyConnection implements Connection{

//将被装饰者导入
private Connection conn;
private LinkedList<Connection> list;




public MyConnection(Connection conn, LinkedList<Connection> list) {
    super();
    this.conn = conn;
    this.list = list;
}


@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
    // TODO Auto-generated method stub
    return conn.unwrap(iface);
}


@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
    // TODO Auto-generated method stub
    return conn.isWrapperFor(iface);
}


@Override
public Statement createStatement() throws SQLException {
    // TODO Auto-generated method stub
    return conn.createStatement();
}


@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
    // TODO Auto-generated method stub
    return conn.prepareStatement(sql);
}


@Override
public CallableStatement prepareCall(String sql) throws SQLException {
    // TODO Auto-generated method stub
    return null;
}


@Override
public String nativeSQL(String sql) throws SQLException {
    // TODO Auto-generated method stub
    return null;
}


@Override
public void setAutoCommit(boolean autoCommit) throws SQLException {
    // TODO Auto-generated method stub
    
}


@Override
public boolean getAutoCommit() throws SQLException {
    // TODO Auto-generated method stub
    return false;
}


@Override
public void commit() throws SQLException {
    // TODO Auto-generated method stub
    conn.commit();
}


@Override
public void rollback() throws SQLException {
    // TODO Auto-generated method stub
    conn.rollback();
}


@Override
public void close() throws SQLException {
    // TODO Auto-generated method stub
    
    list.addLast(conn);
    
}
...

}

基于规范实现的连接池

/**

• 创建一个规范的连接池子
  */
  public class DataSourcePool implements DataSource{
  static LinkedList list = new LinkedList();
  static{

   for (int i = 0; i < 10; i++) {
       Connection connection = JDBCUtils.newInstance().getConnection();
       list.add(connection);
   }
  

  }

  public static int getSize(){
  return list.size();
  }

  @Override
  public Connection getConnection() throws SQLException {
  // TODO Auto-generated method stub
  Connection conn = list.removeFirst();
  MyConnection conn1 = new MyConnection(conn, list);
  return conn1;
  }

  @Override
  public PrintWriter getLogWriter() throws SQLException {
  // TODO Auto-generated method stub
  return null;
  }

  @Override
  public void setLogWriter(PrintWriter out) throws SQLException {
  // TODO Auto-generated method stub

  }

  @Override
  public void setLoginTimeout(int seconds) throws SQLException {
  // TODO Auto-generated method stub

  }

  @Override
  public int getLoginTimeout() throws SQLException {
  // TODO Auto-generated method stub
  return 0;
  }

  @Override
  public Logger getParentLogger() throws SQLFeatureNotSupportedException {
  // TODO Auto-generated method stub
  return null;
  }

  @Override
  public T unwrap(Class iface) throws SQLException {
  // TODO Auto-generated method stub
  return null;
  }

  @Override
  public boolean isWrapperFor(Class<?> iface) throws SQLException {
  // TODO Auto-generated method stub
  return false;
  }

  @Override
  public Connection getConnection(String username, String password) throws SQLException {
  // TODO Auto-generated method stub
  return null;
  }
  }

最小连接数:
是数据库一直保持的数据库连接数，所以如果应用程序对数据库连接的使用量不大，将有大量的数据库资源被浪费。
初始化连接数：
连接池启动时创建的初始化数据库连接数量。
最大连接数：
是连接池能申请的最大连接数，如果数据库连接请求超过此数，后面的数据库连接请求被加入到等待队列中。
最大等待时间：
当没有可用连接时，连接池等待连接被归还的最大时间，超过时间则抛出异常，可设置参数为0或者负数使得无限等待(根据不同连接池配置)。

注1:在DBCP连接池的配置中，还有一个maxIdle的属性，表示最大空闲连接数，超过的空闲连接将被释放，默认值为8。对应的该属性在Druid连接池已不再使用，配置了也没有效果，c3p0连接池则没有对应的属性。
注2：数据库连接池在初始化的时候会创建initialSize个连接，当有数据库操作时，会从池中取出一个连接。如果当前池中正在使用的连接数等于maxActive，则会等待一段时间，等待其他操作释放掉某一个连接，如果这个等待时间超过了maxWait，则会报错；如果当前正在使用的连接数没有达到maxActive，则判断当前是否空闲连接，如果有则直接使用空闲连接，如果没有则新建立一个连接。在连接使用完毕后，不是将其物理连接关闭，而是将其放入池中等待其他操作复用。
2.DBCP连接池

DBCP是一个依赖Jakarta commons-pool对象池机制的数据库连接池.DBCP可以直接的在应用程序中使用，Tomcat的数据源使用的就是DBCP。。

ja
r.zip
10.63MB

2.1 DBCP连接池的使用

2.1.1 导入相应jar包

mysql-jdbc.jar
commons-dbcp.jar
commons-pool.jar

2.1.2 硬编码使用DBCP

所谓的硬编码方式就是在代码中添加配置

@Test
public void testHard() throws SQLException{
// 硬编码 使用DBCP连接池子
BasicDataSource source = new BasicDataSource();
//设置连接的信息
source.setDriverClassName(“com.mysql.jdbc.Driver”);
source.setUrl(“jdbc:mysql://localhost:3306/day2”);
source.setUsername(“root”);
source.setPassword(“111”);

    Connection connection = source.getConnection();
    
    String sql = "select * from student";
    
    Statement createStatement = connection.createStatement();
    
    ResultSet executeQuery = createStatement.executeQuery(sql);
    
    while (executeQuery.next()) {
         
        System.out.println(executeQuery.getString(2));
    }
    connection.close(); //回收
}

2.1.4 软编码使用DBCP

所谓的软编码,就是在项目中添加配置文件,这样就不需要每次代码中添加配合!

1. 项目中添加配置

文件名称: info.properties

文件位置: src下

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day2
username=root
password=111
#
initialSize=10
#最大连接数量
maxActive=50
#
maxIdle=20
#
minIdle=5
#
maxWait=6000

1.DButils工具类 代码实现

//1.创建dbcp的工具类对象
static BasicDataSource datasource=new BasicDataSource();
//2.加载驱动
static {
try {
//加载属性文件
//1.使用工具类 ，参数是属性文件的文件名（不要加后缀）
ResourceBundle bundle = ResourceBundle.getBundle(“db”);
driverClass = bundle.getString(“driverclass”);
url = bundle.getString(“url”);
username = bundle.getString(“uname”);
password = bundle.getString(“upass”);
init=bundle.getString(“initsize”);

		//2.将驱动地址等信息传递给dbcp
		datasource.setDriverClassName(driverClass);
		datasource.setUrl(url);
		datasource.setUsername(username);
		datasource.setPassword(password);
		datasource.setInitialSize(Integer.parseInt(init));
		
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
**//3.获得连接**
public static  Connection  getConn() {
	try {
		con= datasource.getConnection();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return con;
}

3 C3P0连接池

c3p0是一个开放源代码的JDBC连接池，它在lib目录中与Hibernate一起发布,包括了实现jdbc3和jdbc2扩展规范说明的Connection 和Statement 池的DataSources 对象。

c3p0与dbcp区别

dbcp没有自动回收空闲连接的功能
c3p0有自动回收空闲连接功能
2.
dbcp需要手动设置配置文件
c3p0不需要手动设置

3.1 实现方式

1 .手动设置 ComboPooledDataSource
2 加载配置文件方式
src/c3p0-config.xml（文件名固定）
ComboPooledDataSource cpds = new ComboPooledDataSource();
加载 文件中 中的配置

ComboPooledDataSource cpds = new ComboPooledDataSource(“aaa”);
加载 中的配置

3.2 实现步骤

3.1.1 导入jar包

c3p0-0.9.1.2.jar
mysql-connector-java-5.0.8.jar

3.1.2.添加配置文件

c3p0是在外部添加配置文件,工具直接进行应用,因为直接引用,所以要求固定的命名和文件位置

文件位置: src

文件命名:c3p0-config.xml/c3p0-config.properties

<?xml version="1.0" encoding="utf-8"?> com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/day2 root 111 30000 30 10 30 100 10 200 com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/day2 root 111 5 20 10 40 20 5

注意: c3p0的配置文件内部可以包含命名配置文件和默认配置文件!默认是选择默认配置!如果需要切换命名配置可以在创建c3p0连接池的时候填入命名即可!

3.1.3.定义代码

Connection con=null;
ComboPooledDataSource db=new ComboPooledDataSource(“abc”);
public Connection getCon(){
try {
con=db.getConnection();
System.out.println(“初始化的链接数量:”+db.getInitialPoolSize());
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}

4 Druid(德鲁伊)连接池

阿里出品，淘宝和支付宝专用数据库连接池，但它不仅仅是一个数据库连接池，它还包含一个ProxyDriver（代理驱动），一系列内置的JDBC组件库，一个SQL Parser(sql解析器)。支持所有JDBC兼容的数据库，包括Oracle、MySql、Derby、Postgresql、SQL Server、H2等等。
Druid针对Oracle和MySql做了特别优化，比如Oracle的PS Cache内存占用优化，MySql的ping检测优化。
Druid提供了MySql、Oracle、Postgresql、SQL-92的SQL的完整支持，这是一个手写的高性能SQL Parser，支持Visitor模式，使得分析SQL的抽象语法树很方便。
简单SQL语句用时10微秒以内，复杂SQL用时30微秒。
通过Druid提供的SQL Parser可以在JDBC层拦截SQL做相应处理，比如说分库分表、审计等。Druid防御SQL注入攻击的WallFilter就是通过Druid的SQL Parser分析语义实现的。

Druid 是目前比较流行的高性能的，分布式列存储的OLAP框架(具体来说是MOLAP)。它有如下几个特点：
一. 亚秒级查询
druid提供了快速的聚合能力以及亚秒级的OLAP查询能力，多租户的设计，是面向用户分析应用的理想方式。
二.实时数据注入
druid支持流数据的注入，并提供了数据的事件驱动，保证在实时和离线环境下事件的实效性和统一性
三.可扩展的PB级存储
druid集群可以很方便的扩容到PB的数据量，每秒百 万级别的数据注入。即便在加大数据规模的情况下，也能保证时其效性
四.多环境部署
druid既可以运行在商业的硬件上，也可以运行在云上。它可以从多种数据系统中注入数据，包括hadoop，spark，kafka，storm和samza等
五.丰富的社区
druid拥有丰富的社区，供大家学习

4.1 使用步骤

4.1.1 导入jar包

druid-1.0.
9.jar
1.76MB

4.1.2 编写工具类

/**

• 阿里的数据库连接池
• 性能最好的
• Druid
• */
  public class DruidUtils {
  //声明连接池对象
  private static DruidDataSource ds;
  static{
  ///实例化数据库连接池对象
  ds=new DruidDataSource();
  //实例化配置对象
  Properties properties=new Properties();
  try {
  //加载配置文件内容
  properties.load(DruidUtils.class.getResourceAsStream(“dbcpconfig.properties”));
  //设置驱动类全称
  ds.setDriverClassName(properties.getProperty(“driverClassName”));
  //设置连接的数据库
  ds.setUrl(properties.getProperty(“url”));
  //设置用户名
  ds.setUsername(properties.getProperty(“username”));
  //设置密码
  ds.setPassword(properties.getProperty(“password”));
  //设置最大连接数量
  ds.setMaxActive(Integer.parseInt(properties.getProperty(“maxActive”)));
  } catch (IOException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
  }
  }
  //获取连接对象
  public static Connection getConnection() {
  try {
  return ds.getConnection();
  } catch (SQLException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
  }
  return null;
  }
  }

注:在Druid连接池的配置中，driverClassName可配可不配，如果不配置会根据url自动识别dbType(数据库类型)，然后选择相应的driverClassName。

5 DBUtils使用

Commons DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用它能够简化JDBC应用程序的开发，同时也不会影响程序的性能。

5.1 DBUtils简介

DBUtils是java编程中的数据库操作实用工具，小巧简单实用，

1.对于数据表的读操作，他可以把结果转换成List，Array，Set等java集合，便于程序员操作；

2.对于数据表的写操作，也变得很简单（只需写sql语句）

3.可以使用数据源，使用[JNDI]等技术来优化性能–重用已经构建好的数据库连接对象，而不像php，asp那样，费时费力的不断重复的构建和析构这样的对象。

DBUtils包括3个包

org.apache.commons.dbutils
org.apache.commons.dbutils.handlers
org.apache.commons.dbutils.wrappers

DBUtils封装了对JDBC的操作，简化了JDBC操作，可以少写代码。
org.apache.commons.dbutils
DbUtils 关闭链接等操作
QueryRunner 进行查询的操作
org.apache.commons.dbutils.handlers
ArrayHandler ：将ResultSet中第一行的数据转化成对象数组
ArrayListHandler将ResultSet中所有的数据转化成List，List中存放的是Object[]
BeanHandler ：将ResultSet中第一行的数据转化成类对象
BeanListHandler ：将ResultSet中所有的数据转化成List，List中存放的是类对象
ColumnListHandler ：将ResultSet中某一列的数据存成List，List中存放的是Object对象
KeyedHandler ：将ResultSet中存成映射，key为某一列对应为Map。Map中存放的是数据
MapHandler ：将ResultSet中第一行的数据存成Map映射
MapListHandler ：将ResultSet中所有的数据存成List。List中存放的是Map
ScalarHandler ：将ResultSet中一条记录的其中某一列的数据存成Object
org.apache.commons.dbutils.wrappers
SqlNullCheckedResultSet ：对ResultSet进行操作，改版里面的值
StringTrimmedResultSet ：去除ResultSet中中字段的左右空格。Trim()

主要方法

DbUtils类：启动类

ResultSetHandler接口：转换类型接口
MapListHandler类：实现类，把记录转化成List
BeanListHandler类：实现类，把记录转化成List，使记录为JavaBean类型的对象
Query Runner类：执行SQL语句的类

5.2 DBUtils工具类封装

5.2.1 项目准备

• 创建项目
• 导入jar包 工具类 配置文件
  dbutils.jar
  c3p0.jar
  jdbc.jar
  之前项目的info.properties
  c3p0工具类

5.2.2 实现代码

public class ResultHanlder {
//1.查询单行数据: ArrayHandler:适合取1条记录。把该条记录的每列值封装到一个数组中Object[]
public void testArrayHander() throws SQLException {
QueryRunner queryRunner=new QueryRunner(new ComboPooledDataSource());
Object[] query = runner.query(“select * from day13 where id = ?”,
new ArrayHandler(), 9);
for (Object object : query) {
System.out.println(object);
}
}

**// 2.查询多行数据: ArrayListHandler ,把该条记录的每列值封装到一个集合数组中**
public void testArrayListHander() throws SQLException {
          String sql="select * from student where studentno>?";
			List<Object[]> objs=q.query(sql,new ArrayListHandler(),3);
			for (Object[] obj : objs) {
				System.out.println(obj[0]+","+obj[1]+","+obj[2]);
			}
}


**//3.映射某列数据:ColumnListHandler:取某一列的数据。封装到List中。**
public void testColumnListHander() throws SQLException {
    QueryRunner runner = new QueryRunner(new ComboPooledDataSource());
    //ColumnListHandler(列位置)，位置从1开始
    List<Object> query = runner.query("select * from day13 ", 
        new ColumnListHandler(2));
    for (Object objects : query) {
        System.out.println(objects);
    }


}

//4.KeyedHandler:取多条记录，每一条记录封装到一个Map中，
//再把这个Map封装到另外一个Map中，key为指定的字段值
public void testKeyedHandler() throws SQLException {
QueryRunner runner = new QueryRunner(new ComboPooledDataSource());

    Map<Object, Map<String, Object>> query =

runner.query("select * from day13 ", new KeyedHandler(2));

    for (Object key : query.keySet()) {
        System.out.println(key);
        for (Entry<String, Object> entry : query.get(key).entrySet()) {
            System.out.println("key:" + entry.getKey() + "  values:" + entry.getValue());
        }
    }


}


**//5.单行数据:MapHandler。把当前记录的列名和列值放到一个Map中
//key 值是列名  value值是列值**
public void testMapHandler() throws SQLException {
    QueryRunner runner = new QueryRunner(new ComboPooledDataSource());


    Map<String, Object> query = 
            runner.query("select * from day13 ", new MapHandler());


    for (Entry<String, Object> entry : query.entrySet()) {
        System.out.println("key:" + entry.getKey() + "  values:" + entry.getValue());
    }


}

//6.多行数据:MapListHandler:适合取多条记录。把每条记录封装到一个Map中，再把Map封装到List中
public void testMapListHandler() throws SQLException {
QueryRunner runner = new QueryRunner(new ComboPooledDataSource());

    List<Map<String, Object>> query =

runner.query("select * from day13 ", new MapListHandler());

    for (Map<String, Object> entry : query) {


        for (Entry<String, Object> mapEntr : entry.entrySet()) {
            System.out.println("key:" + mapEntr.getKey() + "  values:" + mapEntr.getValue());
        }
    }
}

//7.取单行单列数据:ScalarHandler
public void testScalarHandler() throws SQLException {
QueryRunner runner = new QueryRunner(new ComboPooledDataSource());

    Object query = runner.query("select count(*) from day13 ", new ScalarHandler());
    System.out.println(query);
}


**//8.BeanHandler:适合取单行数据**
public void testBeanHandler() throws SQLException {
    QueryRunner runner = new QueryRunner(new ComboPooledDataSource());
    Day13 query = runner.query("select name,age from day13 ",
      new BeanHandler<Day13>(Day13.class));
    System.out.println(query.toString());
}
**//9.BeanListHandler:适合处理多行数据**
public void testbeanlist throws SQLException{
    QueryRunner queryRunner=new QueryRunner(new ComboPooledDataSource());
List<Student> query = queryRunner.query("select * from student ",
        new BeanListHandler<Student>(Student.class));
for (Student student : query) {
    System.out.println(student);
}
}

}

//10.增删改
int k=
q.update(“insert into student(studentno,stuname,stusex) values(?,?,?)”, 111,“abc”,“男”);
System.out.println(k);

作业题

1、使用数据库连接池和DbUtils改造之前的项目

面试题

1、描述数据库连接池的优缺点