【面试笔试复习笔记】——数据库

*注：如无特殊说明则例子中的SQL语句均操作于下表

student(sno, sname, ssex, sbirthday, class)

teacher(tno, tname, tsex, tbirthday, prof, depart)

course(cno, cname, tno)

score(sno, cno, degree)

▪order by  

(asc升序，desc降序，默认升序，asc可省略)

[例]以cno升序，degree降序查询score表的所有记录

select *
from score
order by cno, degree desc; 

▪limit x,y

取第x到y的记录，可用于实现分页（适用于mysql)

[例]取成绩最高的sno、cno

select sno, cno
from score
order by degree desc
limit 0,1

▪SQL Server分页

用Top关键字实现

select top @pageSize
from student
where sno not in
( select top @pageSize*(@pageIndex-1) id
 from student);

top n 代表前n个数据

▪like 字符串匹配

 % 任意子串

 _  任意一个字符

▪聚集函数

以值的一个集合为输入，返回单个值的函数，如avg、min、max、sum、count。

▪分组聚集 group by

group by 子句中的所有属性上取值相同的元组将被分在一个组中。

出现在select语句但没有被聚集的属性只能是出现在group by子句中的那些属性。

▪having子句 

对分组限定条件（相当于为每个分组使用where限定范围）。

任何出现在having子句中，但没有被聚集的属性必须出现在group by子句中。

▪[例]查找score表中至少有5名学生选修并以3开头的课程的平均数

select cno, avg(degree)
from score
where cno like ‘3%’
group by cno
having count(*) >= 5;

顺序上可理解为group by 对 from、where得出的结果进行分组，having对分组后的各组分别进行操作，最后select投影出需要的属性。

记住from、where操作不会删减属性列，只有select语句能对查询结果属性列进行裁剪。

▪自然连接

自然连接只考虑在两个表中都出现的属性上取值相同的元组对。

（当执行 A natural join B natural join C操作时，是以A、B自然连接的结果与C进行自然连接，所以务必认真比对A、B自然连接的结果与C中都出现的属性是否都是希望进行匹配的属性，如不是则该自然连接得出的结果将不是希望得出的结果）

▪create table

[例]创建表course

create table course
(cno varchar(8) primary key,
cname varchar(8),
tno varchar(8),
foreign key (tno) references teacher);

如果要指定多个属性为主码，需写成

primary key (a, b……)

在mysql中外码需指定参照表的参照属性，如

foreign key (tno) references teacher(tno);

▪insert into

[例]向score表插入一条数据

insert into score values(‘0001’, ‘c001’, 98);

或

insert into score(sno, cno, degree) values(‘0001’, ‘c001’, 98);

Insert into语句也支持以一张表作为输入插入到表中

insert into score
  select *
  from tmpscore;

▪delete from

[例]从score表中删除sno为’0001’的数据

delete from score
where sno=’0001’;

▪update

[例]使score表中sno为’0001’的成绩都加10分

update score
set score=score+10
where sno=’0001’;

▪case

一般格式为

case 
    when pred1 then result1
    when pred2 then result2
    ……
    when predn then resultn
    else result0
end

case语句可以用在任何应该出现值的地方。

[例]（经典考题）

表中有ABC三列， 用SQL语句实现：

当A大于B时选择A否则选择B，当B大于C时选择B否则选择C。

select case when A>B then A
          else B
      end,
      case when B>C then B
          else C
      end
from table_name;

假设表中数据如下

SQL语句执行结果为

▪内连接（只列出匹配的数据行）

分为：等值连接、不等连接、自然连接

▪外连接（不匹配的数据行也会列出）

分为：

左外连接（保留左表元组）

右外连接（保留右表元组）

全外连接（保留两表的元组）

 

[例]找出所有一门课程都没有选修的学生

select ID
from student natural left outer join takes
when course_id is null;

▪视图的作用

1、简化用户的操作

经常使用的查询可以定义为视图，使用户不必为每次操作指定全部条件。

2、限制用户在数据的不同子集上

通过视图用户只能查询和修改他们所能见到的数据。

3、视图对数据库重构提供了一定程度的逻辑独立性

视图可帮助用户屏蔽真实表结构变化带来的影响。

▪范式

1NF 属性的域都是原子不可分的

2NF 每个实例必须可以唯一地区分

3NF 一个表中不包含已在其它表中已包含的非主关键字信息

BCNF 未有好的总结，请查阅详细资料

▪JDBC

try {
	// 0)加载JDBC驱动器（如复制驱动jar包到WEB-INF/lib目录下）
	// 1)加载JDBC驱动
	Class.forName("com.mysql.jdbc.Driver");
	// 2)建立数据库连接，取得Connection对象
	con = DriverManager.getConnection(url, user, psw);
	// 3)建立Statement或PreparedStatement对象
	stmt = con.createStatement();
	// 4)执行SQL语句
	stmt.execute("insert into A values(……)");
	rs = stmt.executeQuery("select * from A");
	// 5)访问结果集ResultSet对象
	while (rs.next()) {
		System.out.println(rs.getInt(1) + " " + rs.getString(2));
	}
} catch (SQLException e) {
	e.printStackTrace();
} finally {
	// 6)依次将ResultSet、Statement、PreparedStatement、Connection对象关闭
	try {
		if (rs != null)
			rs.close();
		if (stmt != null)
			stmt.close();
		if (con != null)
			con.close();
	} catch (SQLException e1) {
		System.out.println(e1.getMessage());
	}
}

▪[题]

设表Test结构如下

用SQL语句查询平均分>75分的科目及平均分

select 科目,avg(分数)
from test
group by(科目)
having avg(分数)>75;

▪JDBC中的PreparedStatement相比Statement的好处

1、相对比较安全，可防止SQL注入

2、有预编译功能，相同操作批量数据效率较高