一.JDBC连oracle/MySQL,并实现查询
public class Test {
private String driName = "com.mysql.jdbc.Driver";
private String url = "jdbc:mysql://localhost:3306/myschool";
private String dbName = "root";
private String dbPwd = "root";
/**
* 获得连接
*/
public Connection getConn() {
Connection conn = null;
try {
Class.forName(driName);
conn = DriverManager.getConnection(url, dbName, dbPwd);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 关闭数据库连接
*/
public void closeAll(ResultSet rs,PreparedStatement stat, Connection conn) {
try {
if (rs != null)
rs.close();
if (stat != null)
stat.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}}}
二.数据库部分：
现有商品表（product）如下：
|
ID |
NAME |
PRICE |
TYPE |
|
1 |
冰箱 |
1500.0 |
家电 |
|
2 |
苹果 |
3.0 |
水果 |
|
3 |
李子 |
7.0 |
水果 |
|
4 |
电视 |
6000.0 |
家电 |
|
5 |
台灯 |
20.0 |
家电 |
顾客表（customer）如下：
|
ACID |
ACNAME |
ACADRESS |
|
1 |
张三 |
北京 |
|
2 |
李四 |
上海 |
|
3 |
王五 |
天津 |
商品交易表（order）如下：
|
ACID |
ID |
AMOUNT |
|
1 |
1 |
12 |
|
3 |
1 |
1 |
|
2 |
2 |
3 |
|
1 |
3 |
1 |
|
1 |
2 |
4 |
|
3 |
5 |
4 |
请回答以下问题并编写数据库语句：
1.按照逻辑关系，这三张表的主外键关系以及约束你认为应该如何设置？并阐明你的理由。（1）商品表（product）的主键是 ID，对应的外键是商品交易表（order）的ID;
（2）顾客表（customer）的主键是ACID,对应的外键是商品交易表的（order）的ACID;
2.求取买过李子的顾客名称及地址的SQL语句
select ACNAME as 顾客名称,ACADRESS as 地址 from product as p
Inner join order as o on o.ID=p.ID
Inner join customer as c on c.ACID=o.ACID
Where p.NAME=’李子’;
- 求取既买过李子又买过苹果的顾客名称
select ACNAME as 顾客名称 from product as p
Inner join order as o on o.ID=p.ID
Inner join customer as c on c.ACID=o.ACID
Where p.NAME=’李子’and p.NAME=’苹果’;
4.求取销售数量前两名的顾客名称
select NAME as 商品名称 from product as p
Inner join order as o on o.ID=p.ID
Order by amount desc
Limit 0,2;
5.请写出包含三张表的数据，包含主外键
1)CREATE TABLE `product` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`PRICE` double NOT NULL,
`TYPE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB comment=’现有商品表’
2)CREATE TABLE `customer` (
`ACID` int(11) NOT NULL AUTO_INCREMENT,
`ACNAME` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`ACADRESS` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`ACID`)
) ENGINE=InnoDB comment=’顾客表’;
3)CREATE TABLE `order` (
`ACID` int(11) NOT NULL,
`ID` int(11) NOT NULL,
`AMOUNT` int(11) NOT NULL,
PRIMARY KEY (`ACID`),
KEY `order_id` (`ID`),
CONSTRAINT `order_acid` FOREIGN KEY (`ACID`) REFERENCES `customer` (`ACID`),
CONSTRAINT `order_id` FOREIGN KEY (`ID`) REFERENCES `product` (`ID`)
) ENGINE=InnoDB comment=’商品交易表’;
6、请描述当该场景数据量增长到10万行以后，为了保证查询效率，应如何设置索引，并写出创建索引的语句。
等待上网查
三.简答题
1.Oracle是怎样分页的？
使用rownum进行分页
2.简述Oracle中dml、ddl、dcl的使用
DML是 数据操纵语言update、delete、insert
DDL是数据定义语言 create 、drop 、alter
DCL是 数据控制语言 commit、rollback、grant、invoke
3.Oracle数据库union和union all的区别？
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；union和union all的区别是,union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。Union All 速度比Union快
4.rowid和rownum的定义
Rowid： 表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的物理地址。使用 ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的标识表中的一行。由于 ROWID 返回的是该行的物理地址，因此使用 ROWID 以显示行是如何存储的。
也可以通过指定 ROWID 来查询记录
Rownum：
在查询的结果集中，ROWNUM 为结果集中每一行标识一个行号，第一行返回 1，第二行返回 2，以此类推。通过 ROWNUM 伪列可以限制查询结果集中返回的行数。
5.请说明数据库主键、外键的作用
主键：唯一标识，不能为空，加快查询速度，自动创建索引
外键：约束内表的数据的更新，从定义外键时可以发现 外键是和主键表联系，数据类型要统一，长度(存储大小)要统一。这样在更新数据的时候会保持一致性
6.说说Oracle中经常使用的函数
ceil(n) 大于或等于数值n的最小整数
floor(n) 小于等于数值n的最大整数
power(m,n) m的n次方
round(n,m) 将n四舍五入,保留小数点后m位
sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1
sqrt(n) n的平方根
initcap(char) 把每个字符串的第一个字符换成大写
lower(char) 整个字符串换成小写
replace(char,str1,str2) 字符串中所有str1换成str2
substr(char,m,n) 取出从m字符开始的n个字符的子串
7.怎样创建索引、索引使用的原则，有什么优点和缺点？
对于查询频率高的字段创建索引
对排序、分组、联合查询频率高的字段创建索引
索引的数目不宜太多
优点：（1）帮助用户提高查询速度
（2）利用索引的唯一性来控制记录的唯一性
（3）可以加速表与表之间的连接
（4）降低查询中分组和排序的时间
创建索引的缺点：
（1）存储索引占用磁盘空间
（2）执行数据修改操作(INSERT、UPDATE、DELETE)产生索引维护
四、如执行 select * from score 查询语句返回结果集：
|
name |
subject |
score |
|
张三 |
语文 |
80 |
|
张三 |
数学 |
80 |
|
张三 |
英语 |
59 |
|
李四 |
语文 |
56 |
|
李四 |
数学 |
47 |
|
李四 |
英语 |
62 |
|
王五 |
语文 |
77 |
|
王五 |
数学 |
65 |
|
王五 |
英语 |
88 |
1)写一段 Sql 语句，查询至少两门科目不及格（小于60分）的人的姓名;
Select name as '姓名'
From Score
WHERE score<60
GROUP BY name
HAVING COUNT(`subject`)>=2
2）写一段 sql 语句，查询所有科目都不及格的人的姓名（科目数未知）
Select name as '姓名'
From Score
WHERE score<60
GROUP BY name
HAVING COUNT(`subject`)>=3
五、数据库有学生表： student(sid,sName,age,address,dNo);
成绩表： score(sid,cid,score);
课程表： class(cld,cName,tName);
Sid--学号，sName--学生名称，dNo--学生所在系的编号，cid--课程号，cName--课程名，tName--教师名，数据库含有数据，写出下列查询的SQL语句：
A、查询姓名为“丁一”的学生的所选课程、任课教师及课程成绩：
Select
class.`cName` AS ‘所选课程’,class.`tName` AS ‘任课教师’,score.`score` AS ‘课程成绩’
From student
Inner join score on student.sid = score.sid
Inner join class on class.cid = score.cid
Where student.`sName` = ‘丁一’;
B、查询“计算机”课程的成绩等于或大于90分学生的姓名及学号：
Select student.`sName` AS ‘学生姓名’,student.`sid` As ‘学号’
From student
Inner join score on student.sid = score.sid
Inner join class on class.cid = score.cid
Where class.`cName` = ‘计算机’
And score.`score`>=90;
C、查询学生的总数和平均年龄：
Select count(*) AS ‘学生总数’, avg(age) AS ‘平均年龄’
From student
D、查询每个系的选修各门课程的学生数量及平均成绩：
Select
student.`dNo` AS ‘院系’, class.`cName` AS ‘课程名’, count(score.`sid`) AS ‘学生数量’, avg(score.`score`) AS ‘平均成绩’
From student
Inner join score on student.sid = score.sid
Inner join class on class.cid = score.cid
Group by student.`dNo`
Select
student.`dNo` AS '院系', count(score.`sid`) AS '大于70分的学生数量', avg(score.`score`) AS '英语平均成绩'
From score
Inner join student on student.sid = score.sid
Inner join class on class.cid = score.cid
Where class.cName = '英语'
Group by student.`dNo`
HAVING avg(score.`score`)>70