数据库部分 全书总结：

MYSQL:

dbs 数据库系统

bdms 数据库管理系统

bda 数据库管理员

db 数据库

dba通过dbms来操作db！

关系型数据库（MySQL MySQLServer Oracle）和非关系型数据库

登录mysql

mysql -h主机地址 -u用户名 -p密码

查询所有的数据库

show databases;

创建数据库

create database [if not exists ] 数据库名称；

删除数据库

drop database [if exists ] 数据库名称；

结构语言分类

DDL(数据定义语言) create drop alter 创建删除以及修改数据库，表，存储过程，触发器，索引....

DML(数据操作语言) insert delete update 用来操作数据库中的数据

DQL（数据查询语言） select 用来查询数据库中的数据

DCL（数据控制语言） grant（授权） revoke（撤销）

TCL(事务控制语言) begin savepoint (设置回滚点) rollback commit

grant 查询 to 用户名；

revoke 查询 from 用户名；

crud（增删改查）

 

创建用户

create user 用户名@‘地址’ identified by ‘密码’;

给用户授权

01. grant all on *.* to 用户名；

02. grant all on *.* to 用户名@‘地址’；

命令:GRANT privileges ON databasename.tablename TO 'username'@'host'

说明: privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等(详细列表见该文最后面).如果要授予所的权限则使用ALL.;databasename - 数据库名,tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示, 如*.*.

例子: GRANT SELECT, INSERT ON test.user TO 'pig'@'%';

GRANT ALL ON *.* TO 'pig'@'%';

注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

刷新系统权限

flush privileges；

删除用户

所有的用户都存储在mysql数据库中的user表中

01. delete from mysql.user where user='t13';

02. delete from mysql.user where user='t13' and host='localhost';

逻辑运算符

and && 与

or || 或

not ！ 非

USE 切换到指定的数据库

如果没有切换数据库，需要在表名前面加上数据库名称！

USE mysql;

SELECT `host`,`user` FROM `user`;

数据类型

int double

在mysql中使用的是decimal（a,b）

a指定指定小数点左边和右边可以存储的十进制数字的最大个数，最大精度38。

b指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 a之间的值。默认小数位数是 0

在oracle中经常 使用 **** number ****

 

 

char 固定长度 例子： 性别 是 char类型 长度是10 但是我们的实际输入的长度是2， 那么存储的时候也是10个空间！ 资源浪费

varchar 可变长度 例子： 性别 是 char类型 长度是10 但是我们的实际输入的长度是2， 那么存储的时候也是2个空间！

日期格式

date yyyy-MM-dd

datetime yy-MM-dd hh:mm:ss

time hh:mm:ss

timestamp 1970到现在 yyyyMMddhhmmss

year yyyy 1901

 

 

约束类型

主键约束 primary key (pk) 用于设置表的主键，用来确保该行的唯一性

外键 foreign key （fk） 用于建立表与表之间的关系

非空约束 not null 字段不允许为空

默认约束 default 字段的默认值

唯一约束 unique key(uk) 字段的值是唯一的可以是null,但只能有一个

自动增长 auto_increment 设置列的自动增长(标识列），通常用于设置主键

 

 

 

创建表

如果是关键字 那么使用反引号 `` esc下面的键

create table [if not exists] 表名（

字段1 数据类型 [约束，索引，注释]，

字段2 数据类型 [约束，索引，注释]，

字段3 数据类型 [约束，索引，注释]

）[表类型][表字符集][注释]

有符号类型 和 无符号类型

有符号类型 ：可以取负值

无符号类型：默认是0！ 0--类型的长度

ZEROFILL属性：如果位数不够，前面用零补齐！

若某数值字段指定了ZEROFILL属性，

将自动添加UNSIGNED属性！

创建学生表

CREATE TABLE IF NOT EXISTS student(

studentNo INT(4) NOT NULL PRIMARY KEY COMMENT '学号',

loginPwd VARCHAR(20) NOT NULL COMMENT '密码',

studentName VARCHAR(50) NOT NULL COMMENT '姓名',

sex CHAR(2) NOT NULL DEFAULT'男' COMMENT '性别',

gradeID INT(4) UNSIGNED COMMENT '年级编号',

phone VARCHAR(50) COMMENT '电话',

address VARCHAR(255) DEFAULT'地址不详' COMMENT '地址',

bornDate DATETIME COMMENT '出生日期',

email VARCHAR(50) COMMENT '邮箱账号',

identityCard VARCHAR(18) UNIQUE KEY COMMENT '身份证号'

)COMMENT='学生表';

修改表中的数据

-- 修改表名 把 student表名 改成 stu

ALTER TABLE student RENAME AS stu;

-- 给表中增加 微信 字段 不为空 唯一

ALTER TABLE student ADD wechat VARCHAR(20) UNIQUE KEY;

-- 修改表中 微信 字段的长度为50

ALTER TABLE student MODIFY wechat VARCHAR(50);

-- 删除表中 微信 字段

ALTER TABLE student DROP wechat;

-- 修改studentName字段的名称为 stuName

ALTER TABLE student CHANGE `name` stuName VARCHAR(20);

创建subject（科目表）

CREATE TABLE IF NOT EXISTS `subject`(

subjectNo INT(4) AUTO_INCREMENT PRIMARY KEY COMMENT '课程编号',

subjectName VARCHAR(50) COMMENT '课程名称',

classHour INT(4) COMMENT '学时',

gradeID INT(4) COMMENT '年级编号'

)COMMENT='科目表' CHARSET='utf8';

遇到的问题

在使用AUTO_INCREMENT 必须和PRIMARY KEY 联合使用！

标识列 是 自增列！

主键是 唯一！

创建年级表

CREATE TABLE IF NOT EXISTS grade(

gradeID INT(4) COMMENT '年级编号',

gradeName VARCHAR(10) COMMENT '年级名称'

)COMMENT='年级表';

添加主键的语法

ALTER TABLE 表名 ADD CONSTRAINT 主键名

PRIMARY KEY 表名（主键字段）;

-- 给年级表中id设置成主键

ALTER TABLE grade ADD CONSTRAINT pk_grade_gradeID PRIMARY KEY(gradeID);

创建成绩表

CREATE TABLE IF NOT EXISTS result(

studentNo INT(4) NOT NULL PRIMARY KEY COMMENT '学号',

subjectNo INT(4) NOT NULL COMMENT '课程编号',

examDate DATETIME NOT NULL COMMENT '考试日期 ',

studentResult INT(4) NOT NULL COMMENT '考试成绩'

)COMMENT='成绩表' charset='utf8' engine=InnoDB;

 

 

外键的语法：

ALTER TABLE 表名 ADD CONSTRAINT 外键名

FOREIGN KEY（外键字段）

REFERENCES 关联表名（关联字段）;

 

创建student表和grade表的关系

学生应该属于某个年级

外键必须建立在 从表上！

ALTER TABLE student ADD CONSTRAINT fk_student_grade

FOREIGN KEY(gradeID)

REFERENCES grade(gradeID);

问题： 前提已经建立了 主外键关系

如果说有三个年级，编号分别是 1 2 3

那么也有三个学生 对应的关系是

第1个学生 1年级

第2个学生 2年级

第3个学生 2年级

01.有没有这种情况 有个学生的年级编号是4 ？？

主表中没有数据！ 从表无法创建！

02.可不可以删除年级表2年级或者1年级？？？

不能删除！因为从表中还有关联数据！

03.如果我们非得删除年级 怎么办？？

先把从表中的关联数据删除 ，之后再删除主表中的数据！

联合主键

将多列设置成主键！

-- 给成绩表设置联合主键

ALTER TABLE result ADD PRIMARY KEY pk_result (studentno,subjectno,examdate);

 

mysql数据库中常用的两种 存储 引擎

MyISAM InnoDB

事务处理 不支持 支持

外键约束 不支持 支持

全文索引 支持 不支持

使用的场景：

MyISAM：不能使用事务，空间小，适合查询！

InnoDB：多适用于增删改，安全性高！事务的并发处理！

-- 查询当前默认的存储引擎

SHOW VARIABLES LIKE 'storage_engine%';

-- 修改存储引擎

-- 找到安装路径下面的my.ini文件

-- 加入 default-storage-engine=InnoDB

数据库表的存储位置

InnoDB类型的文件

*.frm ：表结构定义文件（存放的是表的元数据，与存储引擎无关）

*.ibd : 数据文件（存放数据，字段的值）

MyISAM类型的文件

*.frm ：表结构定义文件

*.MYD ：数据文件

*.MYI ：索引文件

 

 

DML和DQL 增删改查

 

SELECT * FROM grade

新增 insert

-- 向年级表中新增3条数据

INSERT INTO grade(gradeID,gradeName) VALUES(4,'4年级');

INSERT INTO grade(gradeID,gradeName) VALUES(5,'5年级');

INSERT INTO grade(gradeID,gradeName) VALUES(6,'6年级');

-- 删除 456

DELETE FROM grade WHERE gradeID>3;

DELETE FROM grade WHERE gradeID=4 OR gradeID=5 OR gradeID=6;

DELETE FROM grade WHERE gradeID IN (4,5,6);

-- 同时插入多条数据 oracle数据库不支持

INSERT INTO grade(gradeID,gradeName)

VALUES(4,'4年级'),(5,'5年级'),(6,'6年级');

-- 修改gradeID=1的年级名称为 one

UPDATE grade SET gradeName='one'

WHERE gradeID=1

-- delete 删除表中所有的数据

DELETE FROM grade;

 

delete 和 truncate的区别

01.delete

begin （开启事务）

select * from grade;(查询年级表中所有的数据)

delete from grade; (删除年级表中所有的数据)

select * from grade;(查询年级表中所有的数据，没有数据)

rollback; （事务回滚）

select * from grade;(查询年级表中所有的数据,删除的数据恢复)

commit （提交事务)

02.truncate

begin （开启事务）

select * from grade;(查询年级表中所有的数据)

truncate table grade; (删除年级表中所有的数据)

select * from grade;(查询年级表中所有的数据，没有数据)

rollback; （事务回滚）

select * from grade;(查询年级表中所有的数据，没有数据)

commit （提交事务)

区别：

01.delete后面可以拼接where条件，删除指定的行！

truncate只能删除表中所有的数据！不能有where！

02.delete可以回滚，数据库可以恢复！

truncate 不能事务混滚，数据不可以恢复！

03.truncate执行效率高！

 

事务的特性 ACID

事务：一条或者多条sql语句的集合！

原子性 (Atomicity)：在事务中的操作，要么都执行，要么都不执行！

一致性（Consistency）：事务必须保证数据库从一个一致性的状态变成另一个一致性的状态！

隔离性（Isolation）:每个事务之间互不干扰！哪怕是并发执行也不干扰！

持久性（Durability）:事务一旦被改变，那么对数据库中数据的影响是永久性的！

 

 

查询

将查询结果保存到 新表中！

create table newStudent

(select stuName,address from student)

view （视图） :不占物理空间！

 

使用 具体的列 代替 *

select * from student;

select stuName,age ,address from student;

使用别名

SELECT gradeID AS 年级编号,gradeName '年级 名称' FROM grade;

格式

01. 列名 AS 别名

02. 列名 别名

03. 如果别名中有特殊符号，必须把 别名用 单引号 引起来！

查询年级表中 id不等于1的数据 <> !=

SELECT gradeID,gradeName

FROM grade

WHERE gradeid <> 1

 

+ 必须是相同的数据类型，能转换成2进制的数据！

如果有一个列是null 整体返回null！

我们通常使用 concat来做合并

SELECT CONCAT(loginPwd,',',studentNAME) AS 合并列 FROM STUDENT

 

使用is null 的时候 要确保 查询的列 可以为空！

null:

01.标识 空值

02.不是0，也不是空串""

03.只能出现在定义 允许为null的字段

04.只能使用is null 或者is not null 进行比较！

SELECT * FROM student

WHERE loginPwd IS NULL

聚合函数

count() 查询某个字段在表中占的行数

max()查询某个字段的最大值

min()查询某个字段的最小值

sum()查询某个字段的和

avg()查询某个字段的平均值

-- 查询成绩表的总成绩

SELECT SUM(studentResult) FROM result;

-- 查询成绩的平均值

SELECT AVG(studentResult) FROM result;

-- 查询成绩的最高分

SELECT MAX(studentResult) FROM result;

-- 查询成绩的最低分

SELECT MIN(studentResult) FROM result;

-- 查询有成绩的总数

SELECT COUNT(studentResult) FROM result;

常用的字符函数

-- concat(str1,str2...strN) 连接字符串

SELECT CONCAT('h','el','lo') FROM DUAL;

-- insert(str,begin,length,newStr) 替换字符串

-- str初始的字符串 begin 开始的位置 从1开始

-- length 替换长度 newStr替换的字符串

SELECT INSERT('hello',2,3,'55555') FROM DUAL;

-- subString(str,begin,length) 截取字符串

SELECT SUBSTRING('hello',2,3) FROM DUAL;

-- lower(str)转换成小写

-- upper(str)转换成大写

 

 

 

dual 我们称之为 伪表！

在mysql中是一个摆设

select 9*9;

select 9*9 from dual;

select * from dual; 报错

oracle中 必须使用 from dual;

select 9*9 from dual; 正确的

select 9*9 ; 错误

dual是一个只有一行一列的表！

只能查询！ 不能对 dual进行增删改！

 

和并列

DROP TABLE IF EXISTS `testa`;

CREATE TABLE `testa` (

`name` varchar(20) DEFAULT NULL,

`subject` varchar(20) DEFAULT NULL,

`score` double DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

insert into `testa`(`name`,`subject`,`score`) values ('张三','语文',80),('李四','语文',90),('王五','语文',70),('张三','数学',60),('李四','数学',98),('王五','数学',100);

-- 需要成绩和科目 在一列 显示 根据name分组

SELECT

`name` AS 姓名,

GROUP_CONCAT(`subject`,':',score) AS 成绩

FROM testa

GROUP BY `name`;

-- 常用的日期和时间函数

-- 获取 年月日

SELECT CURDATE() FROM DUAL;

-- 获取 时分秒

SELECT CURTIME() FROM DUAL;

-- 获取 年月日 时分秒

SELECT NOW() FROM DUAL;

-- 获取年份

SELECT YEAR(NOW()) FROM DUAL;

SELECT YEAR(CURDATE()) FROM DUAL;

-- 获取小时

SELECT HOUR(NOW()) FROM DUAL;

SELECT HOUR(CURTIME()) FROM DUAL;

-- 获取分钟

SELECT MINUTE(NOW()) FROM DUAL;

SELECT MINUTE(CURTIME()) FROM DUAL;

-- 获取当前日期是本年的第几周

SELECT WEEK(NOW()) FROM DUAL;

-- 获取两个日期之间的天数

SELECT DATEDIFF(NOW(),'2015-06-01') FROM DUAL;

-- 获取给定日期之后的日期

SELECT ADDDATE(NOW(),30) FROM DUAL;

-- 天花板函数

SELECT CEIL(3.0) FROM DUAL;

SELECT CEIL(3.1) FROM DUAL;

SELECT FLOOR(3.9) FROM DUAL;

-- 返回0-1之间的随机数

SELECT RAND() FROM DUAL;

-- 四舍五入

SELECT ROUND(2.4) FROM DUAL;

-- 查询所有年级编号为1的学员信息，按学号升序排序

SELECT * FROM student

WHERE GradeID=1

ORDER BY Studentno ASC;

-- 显示前4条记录

SELECT * FROM student

WHERE GradeID=1

ORDER BY Studentno ASC

LIMIT 0,4;

-- 每页4条，显示第2页，即从第5条记录开始显示4条数据

SELECT * FROM student

WHERE GradeID=1

ORDER BY Studentno ASC

LIMIT 4,4

-- 子查询 【from后面跟子查询 必须用别名】

-- 把一个查询的结果 当成另一个查询的 字段，条件或者表！

SELECT studentName FROM student

-- 只能通过student 表 查询出 学生对应的 年级名称

-- 01. 先查询出 学生 武松 对应的 年级编号

SELECT GradeID FROM student WHERE studentName='武松'

-- 02.根据年级编号 取 年级名称

SELECT gradeName FROM grade WHERE GradeID=???

SELECT gradeName FROM grade WHERE GradeID

=(SELECT GradeID FROM student WHERE studentName='武松')

-- 查询年级编号是1或者2 的 所有学生列表

SELECT * FROM student WHERE gradeId IN(1,2)

-- 查询 年级名称是 大一或者大二的所有学生信息

-- 学生表 中没有 年级名称 但是有年级编号

-- 01.根据 年级名称 查询出 编号

SELECT gradeID FROM grade WHERE gradeName IN('大一','大二');

-- 02.再根据id查询学生信息

SELECT * FROM student WHERE

gradeID

IN (SELECT gradeID FROM grade WHERE gradeName IN('大一','大二'))

-- 查询参加 最近一次 高等数学-1 考试成绩的学生的最高分和最低分

-- 01. 发现成绩表中 没有 科目名称 只有编号！根据名称取编号

SELECT SubjectNo FROM `subject` WHERE

subjectName='高等数学-1'

-- 02.查询最近一次 高等数学-1 考试的时间

SELECT MAX(ExamDate) FROM result

WHERE

SubjectNo=(SELECT SubjectNo FROM `subject` WHERE

subjectName='高等数学-1')

-- 所有最近考试的成绩

SELECT * FROM result

WHERE ExamDate='2013-11-11 16:00:00'

-- 03.开始获取最高分和 最低分

SELECT MAX(studentResult) AS 最高分,

MIN(studentResult) AS 最低分

FROM result

WHERE SubjectNo=(SELECT SubjectNo FROM `subject` WHERE

subjectName='高等数学-1')

AND ExamDate=

(SELECT MAX(ExamDate) FROM result

WHERE

SubjectNo=(SELECT SubjectNo FROM `subject` WHERE

subjectName='高等数学-1'))

 

-- 查询 高等数学-1 考试成绩是 60 分的 学生信息

-- 01.根据 科目名称 获取 科目编号

SELECT SubjectNo FROM `subject`

WHERE SubjectName='高等数学-1'

-- 02.根据编号 查询 所有的学生编号

SELECT studentNo FROM result

WHERE SubjectNo=(SELECT SubjectNo FROM `subject`

WHERE SubjectName='高等数学-1')

AND StudentResult=60; -- 成绩=60

-- 03.查询学生信息

SELECT * FROM student

WHERE studentNo IN

(SELECT studentNo FROM result

WHERE SubjectNo=(SELECT SubjectNo FROM `subject`

WHERE SubjectName='高等数学-1')

AND StudentResult=60)

-- 使用in替换 等于（=）的子查询语句！

-- in后面的子查询可以返回多条记录！

-- not in :不在某个范围之内

-- 查询未参加 “高等数学-1” 课程最近一次考试的在读学生名单

-- 01.根据 科目名称 获取 科目编号

SELECT SubjectNo FROM `subject`

WHERE SubjectName='高等数学-1'

-- 02.获取最近一次考试时间

SELECT MAX(ExamDate) FROM result

WHERE SubjectNo=

(SELECT SubjectNo FROM `subject`

WHERE SubjectName='高等数学-1')

-- 03.查询没参加的学生编号

SELECT studentNo,StudentName FROM student

WHERE studentNo NOT IN

(

SELECT StudentNo FROM result

WHERE SubjectNo=

(SELECT SubjectNo FROM `subject`

WHERE SubjectName='高等数学-1')

AND ExamDate=

(SELECT MAX(ExamDate) FROM result

WHERE SubjectNo=

(SELECT SubjectNo FROM `subject`

WHERE SubjectName='高等数学-1'))

)

高级查询

SELECT * FROM `grade`INNER JOIN `student`

笛卡尔积 ：两个表数据的乘积！

两个表的内连接

SELECT * FROM `grade`INNER JOIN `student`

ON grade.`GradeID`=student.`GradeId`

on 两个表通过那一列建立关联关系

-- exists的使用

-- 01. 用于检测表，数据库等等 是否存在

-- 02. 检查子查询中是否会返回一行数据！其实子查询并不返回任何数据！

值返回 true或者false！

SELECT * FROM Student WHERE EXISTS(SELECT NULL)

SELECT * FROM Student WHERE EXISTS(SELECT 9*9)

SELECT * FROM Student WHERE EXISTS(SELECT StudentName FROM student)

SELECT * FROM Student

WHERE EXISTS(SELECT studentName FROM Student WHERE studentName='张三')

SELECT * FROM Student WHERE studentName IN(SELECT studentName FROM Student)

-- in 效果等同于 =any

SELECT * FROM Student WHERE

studentName =ANY(SELECT studentName FROM Student)

-- all 大于子查询语句中的 最大值 >(1,2,3) >3

SELECT * FROM student

WHERE studentNo>ALL

(SELECT studentNo FROM student WHERE studentNo IN(1003,1004,1005))

-- any 大于子查询语句中的 最小值 >(1,2,3) >1

SELECT * FROM student

WHERE studentNo>ANY

(SELECT studentNo FROM student WHERE studentNo IN(1003,1004,1005))

-- some 和any功能一样

SELECT * FROM student

WHERE studentNo>SOME

(SELECT studentNo FROM student WHERE studentNo IN(1003,1004,1005))

-- 检查“高等数学-1” 课程最近一次考试成绩

-- 如果有 80分以上的成绩，显示分数排在前5名的学员学号和分数

-- 不使用exists

-- 01.查询“高等数学-1” 课程 对应的编号

SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1'

-- 02.查询最近的考试成绩

SELECT MAX(ExamDate) FROM result

WHERE SubjectNo=(SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1')

-- 03. 在02的基础上 加条件 成绩大于80

SELECT * FROM result

WHERE ExamDate=

(SELECT MAX(ExamDate) FROM result

WHERE SubjectNo=(SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1'))

AND StudentResult>80

-- 04.优化

SELECT studentNo,StudentResult FROM result

WHERE ExamDate=

(SELECT MAX(ExamDate) FROM result

WHERE SubjectNo=(SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1'))

AND StudentResult>80

ORDER BY StudentResult DESC

LIMIT 0,5

 

-- 使用exists

-- 检查“高等数学-1” 课程最近一次考试成绩

-- 如果有 80分以上的成绩，显示分数排在前5名的学员学号和分数

-- 01.查询“高等数学-1” 课程 对应的编号

SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1'

-- 02.查询最近的考试成绩

SELECT MAX(ExamDate) FROM result

WHERE SubjectNo=(SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1')

-- 03.查询学号和成绩

SELECT StudentNo,StudentResult FROM result

WHERE EXISTS

(

SELECT * FROM result

WHERE subjectNo=(

SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1'

)

AND ExamDate=(

SELECT MAX(ExamDate) FROM result

WHERE SubjectNo=(SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1')

)

AND StudentResult>80

)

AND subjectNo=(

SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1'

)

AND ExamDate=(

SELECT MAX(ExamDate) FROM result

WHERE SubjectNo=(SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1')

)

ORDER BY StudentResult DESC

LIMIT 0,5

 

-- not exists

-- 检查“高等数学-1”课程最近一次考试成绩

-- 如果全部未通过考试（60分及格），认为本次考试偏难，计算的该次考试平均分加5分

-- 01.查询“高等数学-1” 课程 对应的编号

SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1'

-- 02.查询最近的考试成绩

SELECT MAX(ExamDate) FROM result

WHERE SubjectNo=(SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1')

 

-- 03.查询成绩大于60的 反着来

SELECT StudentResult FROM result

WHERE StudentResult>60

AND SubjectNo=(

SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1'

)

AND ExamDate=(

SELECT MAX(ExamDate) FROM result

WHERE SubjectNo=(SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1')

)

 

-- 04. 如果全部未通过考试，考试平均分加5分

SELECT AVG(StudentResult)+5 FROM result

WHERE NOT EXISTS

(

SELECT StudentResult FROM result

WHERE StudentResult>60

AND SubjectNo=(

SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1'

)

AND ExamDate=(

SELECT MAX(ExamDate) FROM result

WHERE SubjectNo=(SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1')

)

)

AND SubjectNo=(

SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1'

)

AND ExamDate=(

SELECT MAX(ExamDate) FROM result

WHERE SubjectNo=(SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1')

)

 

-- 如果有 年级名称是大二 的学生，就 查询出 年级名称是大一的 所有学生信息

-- 01.先查询出 对应的年级编号

SELECT GradeId FROM grade WHERE GradeName='大一'

SELECT GradeId FROM grade WHERE GradeName='大二'

-- 02.在学生表中是否存在 年级名称是大二 的学生

SELECT * FROM student WHERE gradeID=(

SELECT GradeId FROM grade WHERE GradeName='大二'

)

-- 03.如果有查询出 年级名称是大一的 所有学生信息

SELECT * FROM student

WHERE EXISTS

(

SELECT * FROM student WHERE gradeID=(

SELECT GradeId FROM grade WHERE GradeName='大二'

)

)

AND GradeId=(

SELECT GradeId FROM grade WHERE GradeName='大一'

)

 

-- 使用子查询的注意事项

-- 01.任何允许使用表达式的地方都可以使用子查询

-- 02.只出现在子查询中但是没有在父查询中出现的列，结果集中的列不能包含！

sql优化

使用exists 代替 in

使用not exists 代替not in

exists 只返回true或者false.不返回结果集

in 返回结果集

-- 查询姓李的学生信息 % 代表0或者多个字符 _代表一个字符

SELECT * FROM student WHERE StudentName LIKE '李%'

SELECT * FROM student WHERE StudentName LIKE '李_'

-- 使用in完成上述代码

SELECT * FROM student WHERE StudentName IN(

SELECT studentName FROM student WHERE StudentName LIKE '李%')

-- in(多条数据--》返回结果集)

-- 使用exists替换

SELECT * FROM student WHERE EXISTS(

SELECT studentName FROM student)

AND StudentName LIKE '李%'

-- exists（有没有数据）

 

-- 统计每门课程平均分各是多少 GROUP BY 列名 分组

SELECT subjectno,AVG(studentresult) FROM result

GROUP BY subjectno

-- 查询出课程平均分大于60的课程编号 和 平均分

SELECT subjectno,AVG(studentresult) FROM result

GROUP BY subjectno

HAVING AVG(studentresult)>60 -- 分组之后的条件

-- 统计每门课程平均分各是多少 降序排列

SELECT subjectno,AVG(studentresult) FROM result

GROUP BY subjectno

ORDER BY AVG(studentresult) DESC

-- 如果成绩相同 再按照 课程编号 升序排序

SELECT subjectno,AVG(studentresult) FROM result

GROUP BY subjectno

ORDER BY AVG(studentresult) DESC,subjectno

-- 分组统计每个年级的 男女人数

SELECT gradeid 年级编号,sex 性别,COUNT(sex) 人数

FROM student

GROUP BY gradeid,sex

-- 创建表

CREATE TABLE IF NOT EXISTS examTest(

id INT(2) NOT NULL,

sex VARCHAR(20)

)

-- 同时新增多条数据

INSERT INTO examTest VALUES(1,'男'),(2,'男'),(3,'女'),(4,NULL);

SELECT sex AS '性别',COUNT(sex) AS '人数' FROM examTest

WHERE sex IS NOT NULL

GROUP BY sex

ORDER BY COUNT(sex) DESC

SELECT sex AS '性别',COUNT(sex) AS '人数' FROM examTest

GROUP BY sex

HAVING sex IS NOT NULL

ORDER BY COUNT(sex) DESC

SELECT sex AS '性别',COUNT(sex) AS '人数' FROM examTest

WHERE sex IN('男','女')

GROUP BY sex

ORDER BY COUNT(sex) DESC

 

-- 创建表

CREATE TABLE IF NOT EXISTS mytable(

`name` VARCHAR(10) NOT NULL,

class INT(4) NOT NULL,

sorce DOUBLE NOT NULL

)

-- 插入数据

INSERT INTO mytable

VALUES

('小黑1',1,88),('小黑2',1,80),

('小黑3',1,68),('小黑4',1,70),

('小黑5',1,98),('小黑6',1,90),

('小白1',2,88),('小白2',2,80),

('小白3',2,68),('小白4',2,70),

('小白5',2,98),('小白6',2,90)

-- 找出表中分数的前三名

SELECT * FROM mytable

ORDER BY sorce DESC

LIMIT 0,3

-- 找出每个班级的前三名

SELECT * FROM mytable t1

WHERE

(

SELECT COUNT(1) FROM mytable t2

WHERE t1.`sorce`<t2.`sorce`

AND t1.class=t2.`class`

)<3

ORDER BY class,sorce DESC

 

内连接 ：通过匹配两个表中公共列，找到 公共的行！

左外连接： 以左表为准，右表中没有数据返回null

右外连接： 以右表为准，左表中没有数据返回null

-- 输出学生姓名以及对应的年级名称 内连接

SELECT StudentName,GradeName FROM student INNER JOIN grade

ON student.`GradeId`=grade.`GradeID`

-- 隐式内连接

SELECT StudentName,GradeName FROM student,grade

WHERE student.`GradeId`=grade.`GradeID`

-- 查询 考试 课程编号是1的 学生姓名 以及年级名称 和科目名称以及成绩

01.

SELECT s.StudentName,GradeName,SubjectName ,studentResult FROM student s

INNER JOIN grade g ON (s.gradeID=g.gradeID)

INNER JOIN `subject` b ON(g.gradeID=b.gradeID)

INNER JOIN result r ON (b.subjectNo=r.subjectNo)

AND s.studentNo=r.studentNo

AND b.subjectNo=1

02.

SELECT StudentName,GradeName,SubjectName ,studentResult FROM

student s,grade g,`subject` b,result r

WHERE s.gradeID=g.gradeID

AND g.gradeID=b.gradeID

AND s.studentNo=r.studentNo

AND b.subjectNo=r.subjectNo

AND b.subjectNo=1

-- 查询的列 不在同一个表中！ 必须使用连接查询！建立关联关系！

-- 临时表只有当前连接可见 随连接的关闭 自动删除

-- 临时表的增删改 不会影响到 真表

CREATE TEMPORARY TABLE myStudent

(SELECT * FROM student)

SELECT * FROM myStudent

DELETE FROM mystudent -- 临时表的数据删除

SELECT * FROM student -- 不会影响到真表

自连接

-- 自连接 把一个表当成多个表来使用 关键是 使用别名

SELECT * FROM teacher

-- 查询 老师3 的姓名和 对应的 导师的姓名

-- t1 老师 t2 导师 老师的导师编号=== 导师的编号

SELECT t1.`name` AS a,t2.`name` AS 导师姓名 FROM teacher t1,teacher t2

WHERE t1.`name`='老师3'

AND t2.id=t1.tid

事务

事务的特性 ACID

事务：一条或者多条sql语句的集合！

原子性 (Atomicity)：在事务中的操作，要么都执行，要么都不执行！

一致性（Consistency）：事务必须保证数据库从一个一致性的状态变成另一个一致性的状态！

隔离性（Isolation）:每个事务之间互不干扰！哪怕是并发执行也不干扰！

持久性（Durability）:事务一旦被改变，那么对数据库中数据的影响是永久性的！

模拟 银行转账！

-- 创建数据库

CREATE DATABASE myBank;

-- 切换指定的数据库

USE myBank;

-- 创建表

CREATE TABLE IF NOT EXISTS bank(

customerName VARCHAR(10) NOT NULL COMMENT '用户名',

currentMoney DECIMAL(10,2) NOT NULL COMMENT '账户余额'

);

-- 插入数据

INSERT INTO bank VALUES('小黑',50000),('小白',500000);

-- 小黑 给小白 转账10000

-- 修改两条数据

-- 01.小黑-10000

-- 02.小白+10000

UPDATE bank SET currentMoney=currentMoney-10000

WHERE customerName='小黑';

-- 故意写错字段名称 让02 报错

UPDATE bank SET currentMoneysss=currentMoney+10000

WHERE customerName='小白';

-- 开启事务 START TRANSACTION 或者 BEGIN

-- 01.02 为一个事务

BEGIN

UPDATE bank SET currentMoney=currentMoney-10000

WHERE customerName='小黑';

UPDATE bank SET currentMoneysss=currentMoney+10000

WHERE customerName='小白';

-- 事务回滚 ROLLBACK

-- 提交事务 COMMIT

UPDATE bank SET currentMoney=500000

WHERE customerName='小黑';

-- 证明mysql是默认提交事务的！

SET autocommit=0(关闭事务自动提交) | 1(开启事务自动提交)

-- 关闭事务自动提交

SET autocommit=0;

BEGIN; -- 开启事务

UPDATE bank SET currentMoney=currentMoney-10000

WHERE customerName='小黑';

UPDATE bank SET currentMoney=currentMoney+10000

WHERE customerName='小白';

COMMIT; -- 手动提交事务

UPDATE bank SET currentMoney=10000;

ROLLBACK; -- 事务回滚

SET autocommit=1; -- 恢复自动提交

 

事务的隔离级别

为什么 引入了 事务隔离级别？？

在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别。

 

更新丢失

两个事务都同时更新一行数据，一个事务对数据的更新把另一个事务对数据的更新覆盖了。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。

脏读

一个事务读取到了另一个事务未提交的数据操作结果。这是相当危险的，因为很可能所有的操作都被回滚。

不可重复读

不可重复读（Non-repeatable Reads）：一个事务对同一行数据重复读取两次，但是却得到了不同的结果。

包括以下情况：

（1）虚读：事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。

（2） 幻读（Phantom Reads）：事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据或者缺少了第一次查询中出现的数据（这里并不要求两次查询的SQL语句相同）。这是因为在两次查询过程中有另外一个事务插入数据造成的。

 

 

解决方案： 使用事务隔离级别

案例一： 允许脏读取

-- 查询mysql默认的事务隔离级别 (可重复读取 repeatable-read )

SELECT @@tx_isolation;

-- 修改事务 隔离级别

SET tx_isolation='read-uncommitted'; 允许脏读取，不允许更新丢失

案例二： 禁止更新丢失

在doc窗口中

bengin

update 一张表的数据

不回滚 也不 提交

在 sqlyong中

操作同一张表，发现不允许，在等待！

 

未授权读取

也称为读未提交（Read Uncommitted）：允许脏读取，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。

授权读取

也称为读提交（Read Committed）：允许不可重复读取，但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

可重复读取（Repeatable Read）

可重复读取（Repeatable Read）：禁止不可重复读取和脏读取，但是有时可能出现幻读数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。

序列化（Serializable）

序列化（Serializable）：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

博文连接 ：http://lvwenwen.iteye.com/blog/2045951

视图：

-- 视图 是一张虚拟的表

01.表示一张表的部分数据或者是多张表的综合数据！

02.结构和数据都是建立在对真表的查询基础之上的！

03.视图中存放的数据其实就是对真实表的引用！

对视图中的数据进行添加，更新删除都会影响到真实的表！

04.一个真实的表可以创建N个视图！

05.如果视图关联了多表，不允许增 删！单表可以增删改

06.视图一般都是使用查询！

-- 创建一个视图 只获取 学生姓名 编号 以及考试成绩

CREATE VIEW view_student_result

AS

SELECT s.studentNo 学生编号,studentName 学生姓名,

studentResult 考试成绩

FROM student s,result r

WHERE s.`studentNo`=r.`studentNo`

-- 查询视图中的内容

SELECT * FROM view_student_result

-- 查询mysql数据库中所有的视图

SELECT * FROM information_schema.views;

-- 删除视图

DROP VIEW view_student_result;

-- 创建一个表的视图 学生姓名，地址，手机号

CREATE VIEW view_student

AS

SELECT studentName 学生姓名,address 地址,phone 手机号

FROM student

-- 查询视图（只是为了查询(看) 不是为了操作）

虚拟的表 方便 简洁

存储的数据是真表的引用 改变试图数据 真表数据也会改变

单表可以增删改 多表可以改 不可以增加 删除

SELECT * FROM view_student

 

-- 索引：是一种有效组合数据的方式！目的就是快速或者某个记录！

提高了数据库的检索速度！

索引是一个文件

mysql索引按存储类型分2种:

01.B-树索引（BTREE）:INNODB,MyISAM 支持

02.哈希索引 memory

01.普通索引：允许在定义为索引的列中 出现 重复值和空值！

02.唯一索引：索引列不允许出现重复值，但是可以出现一个空值！

03.主键索引：创建主键的时候，系统会默认创建一个主键索引！唯一！不能为空！

04.复合索引：(name+id)只有复合索引左边的被使用，索引才有效 将我们的多个列组合起来！（name,sid）姓名和身份证号！

05.全文索引：允许值的全文查询！允许空值和重复值！适合在一个内容比较多的列！text！

06.空间索引：对空间数据类型的列建立的索引！记录位置的一列设置为空间索引。

-- 查询指定表的索引

SHOW INDEX FROM student;

TABLE:索引所在的表

Non_unique:索引是否唯一 0:唯一 1：不唯一

key_name：索引名称

seq_in_index:该列在索引中的位置

column_name:定义所用的列名称

null：该列是否为空

index_type:索引类型

-- 给姓名增加 普通索引

CREATE INDEX index_studentName

ON student(studentName);

-- 给学生姓名和身份证号 增加 组合索引

CREATE INDEX index_name_sid

ON student(studentName,identityCard)

-- 删除索引

DROP INDEX index_name_sid ON student;

DROP INDEX index_studentName ON student;

-- 创建索引的原则

01.经常被查询的列

02.经常用作选择的列

03.经常排序，分组的列

04.经常用作连接的列（主键/外键）

使用索引时的注意事项：

01.查询时减少使用*返回全部的列，不要返回不需要的列！

02.索引尽量要少，在字节数小的列上建立索引！

03.where字句中有多个条件表达式的时候，包含索引的列要放在其他表达式之前！

04.在order by的字句中避免使用表达式！

sql 语句的执行 顺序

5.查询 select

1.找表 from

2.条件 where

3.分组 group by

4.条件 having

6.排序 order by

7.分页 limit

数据库的恢复和备份

为保证账户密码安全，命令中可不写密码，但参数“-p”必须有，回车后根据提示写密码！

数据库的备份

cmd进入命令行

mysqldump -u用户名 -p 数据库名称 >指定的文件

数据库的恢复

前提是必须要有对应的数据库

mysql -u用户名 -p <指定的文件

1. mysql为DOS命令

2.在执行该语句之前，必须在MySQL服务器中创建新数据库，如果不存在恢复数据库过程将会出错

 

软件项目开发周期中数据库设计

01.需求分析阶段：分析客户的业务和数据处理需求

02.概要设计阶段：设计数据库的E-R模型图，确认需求信息的正确和完整

03.详细设计阶段：应用三大范式审核数据库结构

04.代码编写阶段：物理实现数据库，编码实现应用

05.软件测试阶段：……

06.安装部署：……

数据库设计步骤：

01.收集信息

02.标识实体

03.标识每个实体的属性

04.标识实体之间的关系

E-R图：

01.矩形 实体

02.椭圆形 属性

03.菱形 实体与实体的关系

1 ： 一对一

1：N： 一对多

M：N： 多对多

必须满足 三大范式

第一范式确保每列的原子性

第二范式要求每个表只描述一件事情

第三范式要求表中各列必须和主键直接相关，不能间接相关

JDBC

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public class DBHelper {

public static final String url = "jdbc:mysql://127.0.0.1/student";

public static final String name = "com.mysql.jdbc.Driver";

public static final String user = "root";

public static final String password = "root";

public Connection conn = null;

public PreparedStatement pst = null;

public DBHelper(String sql) {

try {

Class.forName(name);//指定连接类型

conn = DriverManager.getConnection(url, user, password);//获取连接

pst = conn.prepareStatement(sql);//准备执行语句

} catch (Exception e) {

e.printStackTrace();

}

}

public void close() {

try {

this.conn.close();

this.pst.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

import java.sql.ResultSet;

import java.sql.SQLException;

public class Demo {

static String sql = null;

static DBHelper db1 = null;

static ResultSet ret = null;

public static void main(String[] args) {

sql = "select *from stuinfo";//SQL语句

db1 = new DBHelper(sql);//创建DBHelper对象

try {

ret = db1.pst.executeQuery();//执行语句，得到结果集

while (ret.next()) {

String uid = ret.getString(1);

String ufname = ret.getString(2);

String ulname = ret.getString(3);

String udate = ret.getString(4);

System.out.println(uid + "\t" + ufname + "\t" + ulname + "\t" + udate );

}//显示数据

ret.close();

db1.close();//关闭连接

} catch (SQLException e) {

e.printStackTrace();

}

}

}

static void Main(string[] args)

{

string url = "server=127.0.0.1;user=root;database=student;port=3306;password=root;";//指明要连接的数据库地址，用户名，数据库名，端口，密码

MySqlConnection conn = new MySqlConnection(url);//实例化连接

conn.Open();//打开连接

string sta = "select * from stuinfo";//执行一个简单的语句

MySqlCommand comm = new MySqlCommand(sta, conn);

MySqlDataReader reader = comm.ExecuteReader();//用MySqlDataReader接收执行结果

while (reader.Read())

{

Console.WriteLine(reader.GetString(0) + " " + reader.GetString(1) + " " + reader.GetString(2) +" " + reader.GetString(3));//读出查询的结果

}

Console.ReadKey();

reader.Close();

conn.Close();//关闭连接

}

Java之dao模式详解及代码示例

这篇文章主要介绍了Java之dao模式详解及代码示例，具有一定参考价值，需要的朋友可以了解下。

什么是dao模式？

DAO（Data Access Object）顾名思义是一个为数据库或其他持久化机制提供了抽象接口的对象，在不暴露底层持久化方案实现细节的前提下提供了各种数据访问操作。在实际的开发中，应该将所有对数据源的访问操作进行抽象化后封装在一个公共API中。用程序设计语言来说，就是建立一个接口，接口中定义了此应用程序中将会用到的所有事务方法。在这个应用程序中，当需要和数据源进行交互的时候则使用这个接口，并且编写一个单独的类来实现这个接口，在逻辑上该类对应一个特定的数据存储。DAO模式实际上包含了两个模式，一是Data Accessor（数据访问器），二是Data Object（数据对象），前者要解决如何访问数据的问题，而后者要解决的是如何用对象封装数据。

一、信息系统的开发架构

客户层-------显示层-------业务层---------数据层---------数据库

1.客户层：客户层就是客户端，简单的来说就是浏览器。

2.显示层：JSP/Servlet，用于给浏览器显示。

3.业务层：对于数据层的原子操作进行整合。

4.数据层：对于数据库进行的原子操作，增加、删除等；

二、DAO(Data Access Object)介绍

DAO应用在数据层那块(对于数据库进行的原子操作，增加、删除等；),用于访问数据库，对数据库进行操作的类。

三、DAO设计模式的结构

DAO设计模式一般分为几个类：

1.VO(Value Object)：一个用于存放网页的一行数据即一条记录的类，比如网页要显示一个用户的信息，则这个类就是用户的类。

2.DatabaseConnection：用于打开和关闭数据库。

3.DAO接口：用于声明对于数据库的操作。

4.DAOImpl：必须实现DAO接口，真实实现DAO接口的函数，但是不包括数据库的打开和关闭。

5.DAOProxy：也是实现DAO接口，但是只需要借助DAOImpl即可，但是包括数据库的打开和关闭。

6.DAOFactory：工厂类，含有getInstance()创建一个Proxy类。

四、DAO的好处

DAO的好处就是提供给用户的接口只有DAO的接口，所以如果用户想添加数据，只需要调用create函数即可，不需要数据库的操作。

五、DAO包命名

对于DAO，包的命名和类的命名一定要有层次。

1.Emp.Java

package org.vo;

import java.util.*;

public class Emp{

private int empno;

private String ename;

private String job;

private Date hireDate;

private float sal;

public Emp(){

}

public int getEmpno(){

return empno;

}

public void setEmpno(int empno){

this.empno = empno;

}

public String getEname(){

return ename;

}

public void setEname(String ename){

this.ename = ename;

}

public Date getHireDate(){

return hireDate;

}

public void setHireDate(Date hireDate){

this.hireDate = hireDate;

}

public float getSal(){

return sal;

}

public void setSal(float sal){

this.sal = sal;

}

public String getJob(){

return job;

}

public void setJob(String job){

this.job = job;

}

}

2.DatabaseConnection.java

package org.dbc;

import java.sql.*;

public class DatabaseConnection{

private Connection con = null;

private static final String DRIVER = "com.mysql.jdbc.Driver";

private static final String USER = "root";

private static final String URL = "jdbc:mysql://localhost:3306/mldn";

private static final String PASS = "12345";

public DatabaseConnection()throws Exception{

Class.forName(DRIVER);

con = DriverManager.getConnection(URL,USER,PASS);

}

public Connection getConnection()throws Exception{

return con;

}

public void close()throws Exception{

if(con!=null){

con.close();

}

}

}

3.IEmpDAO.java

package org.dao;

import java.util.List;

import org.vo.*;

public interface IEmpDAO{

public boolean doCreate(Emp emp)throws Exception;

public List<Emp> findAll()throws Exception;

public Emp findById(int empno)throws Exception;

}

4.EmpDAOImpl.java

package org.dao.impl;

import org.dao.*;

import java.sql.*;

import org.vo.*;

import java.util.*;

public class EmpDAOImpl implements IEmpDAO{

private Connection con;

private PreparedStatement stat = null;

public EmpDAOImpl(Connection con){

this.con = con;

}

public boolean doCreate(Emp emp)throws Exception{

String sql = "INSERT INTO emp(empno,ename,job,hiredate,sal) VALUES(?,?,?,?,?)";

stat = con.prepareStatement(sql);

stat.setInt(1,emp.getEmpno());

stat.setString(2,emp.getEname());

stat.setString(3,emp.getJob());

stat.setDate(4,new java.sql.Date(emp.getHireDate().getTime()));

stat.setFloat(5,emp.getSal());

int update = stat.executeUpdate();

if(update>0){

return true;

}

else{

return false;

}

}

public List<Emp> findAll()throws Exception{

String sql = "SELECT empno,ename,job,hiredate,sal FROM emp";

stat = con.prepareStatement(sql);

ResultSet rs = stat.executeQuery();

Emp emp = null;

List<Emp> list = new ArrayList<Emp>();

while(rs.next()){

int empno = rs.getInt(1);

String ename = rs.getString(2);

String job = rs.getString(3);

float sal = rs.getFloat(5);

emp = new Emp();

emp.setEmpno(empno);

emp.setEname(ename);

emp.setJob(job);

emp.setHireDate(rs.getDate(4));

emp.setSal(sal);

list.add(emp);

}

return list;

}

public Emp findById(int empno)throws Exception{

String sql = "SELECT empno,ename,job,hiredate,sal FROM emp WHERE empno=?";

stat = con.prepareStatement(sql);

stat.setInt(1,empno);

ResultSet rs = stat.executeQuery();

Emp emp = null;

if(rs.next()){

String ename = rs.getString(2);

String job = rs.getString(3);

float sal = rs.getFloat(5);

emp = new Emp();

emp.setEmpno(empno);

emp.setEname(ename);

emp.setJob(job);

emp.setHireDate(rs.getDate(4));

emp.setSal(sal);

}

return emp;

}

}

5.EmpDAOProxy.java

package org.dao.impl;

import org.dao.*;

import java.sql.*;

import org.vo.*;

import java.util.*;

import org.dbc.*;

public class EmpDAOProxy implements IEmpDAO{

private DatabaseConnection dbc;

private IEmpDAO dao = null;

public EmpDAOProxy()throws Exception{

dbc = new DatabaseConnection();

dao = new EmpDAOImpl(dbc.getConnection());

}

public boolean doCreate(Emp emp)throws Exception{

boolean flag = false;

if(dao.findById(emp.getEmpno())==null){

flag = dao.doCreate(emp);

}

dbc.close();

return flag;

}

public List<Emp> findAll()throws Exception{

List<Emp>list = dao.findAll();

dbc.close();

return list;

}

public Emp findById(int empno)throws Exception{

Emp emp = dao.findById(empno);

dbc.close();

return emp;

}

}

6.DAOFactory.java

package org.dao.factory;

import org.dao.*;

import java.sql.*;

import org.vo.*;

import java.util.*;

import org.dbc.*;

import org.dao.impl.*;

public class DAOFactory{

public static IEmpDAO getInstance(){

IEmpDAO dao = null;

try{

dao = new EmpDAOProxy();

}

catch(Exception e){

e.printStackTrace();

}

return dao;

}

}

7.TestDAO.java

package org.dao.test;

import org.dao.factory.*;

import org.vo.*;

import org.dao.*;

public class TestDAO{

public static void main(String args[])throws Exception{

Emp emp = null;

for(int i=0;i<5;i++){

emp = new Emp();

emp.setEmpno(i);

emp.setEname("xiazdong-"+i);

emp.setJob("stu-"+i);

emp.setHireDate(new java.util.Date());

emp.setSal(500*i);

DAOFactory.getInstance().doCreate(emp);

}

}

}

通过DAO设计模式，可以在JSP中屏蔽了数据库连接的操作，达到JSP只负责显示的效果。

 

String sql = "INSERT INTO emp_table(ename,job,sal,hiredate) VALUES(?,?,?,?)";

stat = (PreparedStatement) con.prepareStatement(sql);

stat.setString(1,emp.getEname());

stat.setString(2,emp.getJob());

stat.setFloat(3,emp.getSal());