数据库（class0506)

CASE函数用法1

单值判断，相当于switch case
CASE expression 
WHEN value1 THEN returnvalue1 
WHEN value2 THEN returnvalue2 
WHEN value3 THEN returnvalue3 
ELSE defaultreturnvalue 
END 
例子：论坛中用户的等级
case level
        when 1 then '骨灰'
        when 2 then '大虾'
        when 3 then '菜鸟'
    end

create table [user]
(
    uId int identity(1,1) primary key,
    name varchar(50),
    level int  --1骨灰 2大虾 3菜鸟
)
insert into [user] (name,level) values('犀利哥',1)
insert into [user] (name,level) values('小月月',2)
insert into [user] (name,level) values('芙蓉姐姐',3)

select name,等级=(
    case level
        when 1 then '骨灰'
        when 2 then '大虾'
        when 3 then '菜鸟'
    end
)    
from [user]

CASE函数用法2

ASE 
WHEN condition1 THEN returnvalue1 
WHEN condition 2 THEN returnvalue2 
WHEN condition 3 THEN returnvalue3 
ELSE defaultreturnvalue 
END 
相当于if…else if…else….
例子：百分制转换为素质教育
注意：then后面返回的数据类型要一致

select studentId,成绩=(    
    case
        when english between 90 and 100 then 'A'
        when english between 80 and 89 then 'B'
        when english between 70 and 79 then 'C'
        when english between 60 and 69 then 'D'
        when english < 60 then 'E'
        else '缺考'
    end
)
from score

练习1

表中有A B C三列,用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 t

练习2

单号   金额
Rk1     10
Rk2     20
Rk3     -30
Rk4     -10
将上面的表输出为如下的格式：
单号   收入   支出
Rk1     10       0
Rk2     20       0
Rk3      0        30
Rk4      0        10

create table test
(
    number varchar(10),
    amount int
)
insert into test(number,amount) values('RK1',10)
insert into test(number,amount) values('RK2',20)
insert into test(number,amount) values('RK3',-30)
insert into test(number,amount) values('RK4',-10)


select number,
(
    case 
        when amount > 0 then amount
        else 0
    end
) 收入,
(
    case 
        when amount < 0 then abs(amount)
        else 0
    end 
) 支出
from test

*练习3

有一张表student0，记录学生成绩
name       subject    result
    张三        语文        80
张三        数学        90
张三        物理        85
李四        语文        85
李四        数学        92
李四        物理        NULL
要求输出下面的格式：
name       语文         数学         物理
---------- ----------- ----------- -----------
李四        85          92          0
张三        80          90          85
数据和参考答案见备注

CREATE TABLE student0 (name nvarchar(10),subject nvarchar(10),result int)
INSERT INTO student0 VALUES ('张三','语文',80)
INSERT INTO student0 VALUES ('张三','数学',90)
INSERT INTO student0 VALUES ('张三','物理',85)
INSERT INTO student0 VALUES ('李四','语文',85)
INSERT INTO student0 VALUES ('李四','数学',92)
INSERT INTO student0 VALUES ('李四','物理',null)

SELECT * FROM student0
 
select name,sum(
    case subject
        when '语文' then result
    end
) 语文,sum(
    case subject
        when '数学' then result
    end
) 数学,isnull(sum(
    case subject
        when '物理' then result
    end
),0) 物理
from student0 group by name

索引Index

全表扫描：对数据进行检索（select）效率最差的是全表扫描，就是一条条的找。

如果没有目录，查汉语字典就要一页页的翻，而有了目录只要查询目录即可。为了提高检索的速度，可以为经常进行检索的列添加索引，相当于创建目录。

创建索引的方式，在表设计器中点击右键，选择“索引/键”→添加→在列中选择索引包含的列。

使用索引能提高查询效率，但是索引也是占据空间的，而且添加、更新、删除数据的时候也需要同步更新索引，因此会降低Insert、Update、Delete的速度。只在经常检索的字段上(Where)创建索引。

（*）即使创建了索引，仍然有可能全表扫描，比如like、函数、类型转换等。

子查询

将一个查询语句做为一个结果集供其他SQL语句使用，就像使用普通的表一样，被当作结果集的查询语句被称为子查询。所有可以使用表的地方几乎都可以使用子查询来代替。 SELECT * FROM (SELECT * FROM student where sAge<30) as t

重做Union all的一道题。要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩.

select (select max(english) from score),(select min(english) from score),(select avg(english) from score)

只有返回且仅返回一行、一列数据的子查询才能当成单值子查询。下面的是错误的：SELECT 1 AS f1,2,(SELECT english FROM score)

查询高二二班的所有学生

select * from student where sClassId = (select cId from class where cName='高二二班')

子查询返回的值不止一个。当子查询跟随在=、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

 

如果子查询是多行单列的子查询，这样的子查询的结果集其实是一个集合。可以使用in关键字代替=号

查询高一一班和高二一班的所有学生

select * from student where sClassId in (select cId from class where cName='高一一班' or cName='高二一班')

查询刘关张的成绩

select studentId,english from score where studentId in

(select sId from student where sName='刘备' or sName = '关羽' or sName='张飞')

快速实现删除多个学生

delete from student where sId in

(select sId from student where sName='刘备' or sName = '关羽' or sName='张飞')

数据库中分页的实现
取最近插入的5个学生
取最近插入的第6-10个学生
select top 5 * from student where sId not in 
(select top 5 sId from student order by sId desc)
order by sId desc
上面是sql 2000以前的实现方式。SQLServer2005后增加了Row_Number函数简化实现。

限制结果集。返回第3行到第5行的数据（ ROW_NUMBER 不能用在where子句中，所以将带行号的执行结果作为子查询，就可以将结果当成表一样用了）： 
select * from 
(select row_number() over (order by sId asc) as num,* from student) as s
where s.num between 6 and 10  order by sId asc

select top 5 * from student where sId not in 
(select top (5*(2-1)) sId from student order by sId desc)
order by sId desc

select * from 
(select row_number() over (order by sId desc) as num,* from student) as s
where s.num between 5*(3-1)+1 and 5*3 
sql中的over函数和row_numbert()函数配合使用，可生成行号。
over里的order只能查查询里的原始数据进行操作，不会对计算出的新值或新字段起作用。 

表连接Join

案例1：查询所有学生的姓名、年龄及所在班级

案例2：查询年龄超过20岁的学生的姓名、年龄及所在班级

案例3：查询学生姓名、年龄、班级及成绩

案例4：查询所有学生(参加及未参加考试的都算)及成绩

Inner Join、Left Join、Right Join

(*)Cross Join 交叉连接 笛卡尔积 其它连接的基础

--查询所有女学生的姓名、年龄及所在班级

select sName,sAge,class.cName from student 
inner join class on student.sClassId = class.cId
where sSex = '女'

--查询学生姓名、年龄及成绩

select sName,sAge,english from student 
inner join score on student.sId = score.studentId

--查询学生姓名、年龄、班级及成绩 

select sName,sAge,cName,english from student s
left join class c on s.sClassId = c.cId left join score 
on s.sId = score.studentId

--查询学生姓名、年龄及成绩,分数为null的显示缺考

select sName,sAge,
    case  
        when english is null then '缺考'
        else convert(varchar(10),english)
    end
 from student 
inner join score on student.sId = score.studentId

练习1：查询所有及格的学生姓名、年龄及成绩

练习2：查询所有参加考试的(分数不为null)学生姓名、年龄及成绩

练习3：查询所有学生(参加和未参加考试)的学生姓名、年龄、班级及成绩，如果没有参加考试显示缺考

视图概述

回顾数据怎么存储的

视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上

视图在操作上和数据表没有什么区别，但两者的差异是其本质是不同:数据表是实际存储记录的地方，然而视图并不保存任何记录，它存储的实际上是查询语句

相同的数据表，根据不同用户的不同需求，可以创建不同的视图（不同的查询语句）

视图的目的是方便查询，所以一般情况下不能对视图进行增删改

优点：

筛选表中的行

防止未经许可的用户访问敏感数据

降低数据库的复杂程度

数据库设计

数据库设计过程

数据库设计过程一般包括：

(1)需求分析 分析客户的业务需求

(2)概要设计 根据需求分析阶段的数据画E-R图，通过E- R图和用户沟通

(3)详细设计 将E-R图转换为表，并使用三大范式审核

(4)物理设计 选择合适的数据库， 进行物理实现：建库 、建表、加约束等

(5)实施与维护

实体关系图(E-R图) entity relationship

符合	含义
正方形	实体，一般是名词
椭圆形	属性，一般是名词
菱形	关系，一般是动词

 

实体之间的关系 映射级数1：1

转化为关系模型： 经理(姓名,民族,住址,出生年月,电话) 公司(名称,注册地,类型,电话,姓名

(张小辉,汉,北京前门大街156号,48,68705633,京广实业公司) (京广实业公司,北京复兴门外大街278号,有限责任,65783265,张小辉)

实体之间的关系 1：n

关系模型： 学生(学号,姓名,民族,出生年月,班号) 班级(班号,名称,年级,系,专业) 1对多时候，需要在N方加入外键。在学生表中增加“班级”中的关键字“班号”作为外部关键字

实体之间的关系 m：n

学生(学号，姓名，民族，出生年月) 课程(课程号，课程名，学时数) 学习(学号，课程号，成绩)

当多对多关系时，需要把关系也创建成一个表

数据库的范式

表设计后，很可能结构不合理，出现数据重复保存，简称数据的冗余，这对数据的增删改查带来很多后患，所以我们需要审核是否合理，就想施工图设计后，还需要其他机构进行审核图纸是否设计合理一样。

如何审核呢？需要一些有关数据库设计的理论指导规则，这些规则业界简称数据库的范式。

对数据库审核  三大范式

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

如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）

例如，如果关于员工的关系中有一个工资属性，而工资又由更基本的两个数据项基本工资和岗位工资组成，则这个员工的关系模式就不满足1NF。

员工表(员工号,姓名,工资)进行分解，使其满足1NF条件。

员工表(员工号,姓名,基本工资,岗位工资)

对数据库审核  三大范式

如果一个关系满足1NF，并且除了主键以外的其他列，都依赖与该主键，则满足第二范式（2NF）

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

部门编号	部门名称	员工编号	员工姓名	性别	住址
D001	总经理办	E001	钱达理	男	东风路78号
D001	总经理办	E002	东方牧	男	五一北路25号
D002	市场部	E003	郭文斌	男	公司集体宿舍
D003	销售部	E004	肖海燕	女	公司集体宿舍
D004	仓储部	E005	张明华	男	韶山北路55号

如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF） 第三范式要求其它列必须直接依赖于主键

使用三大范式减少了数据冗余，但是牺牲了查询性能

所以有时为了性能，需要做适当折中，适当牺牲规范化的要求，来提高数据库的性能。

SQL面试题

1列出EMPLOYEES表中各部门的部门号，最高工资，最低工资

2列出EMPLOYEES表中各部门EMPLOYEE_JOB为'CLERK'的员工的最低工资，最高工资

3对于EMPLOYEES中最低工资小于1000的部门，列出EMPLOYEE_JOB为'CLERK'的员工的部门号，最低工资，最高工资

4根据部门号由高而低，工资有低而高列出每个员工的姓名，部门号，工资

5列出'张三'所在部门中每个员工的姓名与部门号

6列出每个员工的姓名，工作，部门号，部门名

7列出EMPLOYEES中工作为'CLERK'的员工的姓名，工作，部门号，部门名

8对于EMPLOYEES中有管理者的员工，列出姓名，管理者姓名（管理者外键为MANAGER）

9对于DEPARTMENTS表中，列出所有部门名，部门号，同时列出各部门工作为'CLERK'的员工名与工作

10对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序

11对于EMPLOYEES，列出各个部门中平均工资高于本部门平均水平的员工数和部门号，按部门号排序

12对于EMPLOYEES中工资高于本部门平均水平，人数多与1人的，列出部门号，人数，按部门号排序

13对于EMPLOYEES中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数

表结构
DEPARTMENTS:
DEPARTMENT_ID(primary key),
DEPARTMENT_NAME,
LOCATION
 
EMPLOYEES:
EMPLOYEE_ID(primary key),
EMPLOYEE_NAME,
EMPLOYEE_JOB,
MANAGER,
SALARY,
DEPARTMENT_ID

列出EMPLOYEES表中各部门的部门号，最高工资，最低工资
1select max(SALARY) as 最高工资,min(SALARY) as 最低工资,DEPARTMENT_ID from EMPLOYEES group by DEPARTMENT_ID;
列出EMPLOYEES表中各部门EMPLOYEE_JOB为'CLERK'的员工的最低工资，最高工资
1select max(SALARY) as 最高工资,min(SALARY) as 最低工资,DEPARTMENT_ID as 部门号 from EMPLOYEES where EMPLOYEE_JOB = 'CLERK' group by DEPARTMENT_ID;
对于EMPLOYEES中最低工资小于1000的部门，列出EMPLOYEE_JOB为'CLERK'的员工的部门号，最低工资，最高工资
1select max(SALARY) as 最高工资,min(SALARY) as 最低工资,DEPARTMENT_ID as 部门号 from EMPLOYEESas b
2where EMPLOYEE_JOB ='CLERK' and 1000>(select min(SALARY) from EMPLOYEES as a where a.DEPARTMENT_ID = b.DEPARTMENT_ID) group by b.DEPARTMENT_ID
根据部门号由高而低，工资有低而高列出每个员工的姓名，部门号，工资
1select DEPARTMENT_ID as 部门号,EMPLOYEE_NAME as 姓名,SALARY as 工资 from EMPLOYEES order by DEPARTMENT_ID desc,SALARY asc
列出'张三'所在部门中每个员工的姓名与部门号
1select EMPLOYEE_NAME,DEPARTMENT_ID from EMPLOYEES where DEPARTMENT_ID = (select DEPARTMENT_ID from EMPLOYEES where EMPLOYEE_NAME = '张三')
列出每个员工的姓名，工作，部门号，部门名
1select EMPLOYEE_NAME,EMPLOYEE_JOB,EMPLOYEES.DEPARTMENT_ID,DEPARTMENTS.DEPARTMENT_NAME from EMPLOYEES,DEPARTMENTS where EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
列出EMPLOYEES中工作为'CLERK'的员工的姓名，工作，部门号，部门名
1select EMPLOYEE_NAME,EMPLOYEE_JOB,DEPARTMENTS.DEPARTMENT_ID,DEPARTMENT_NAME from EMPLOYEES,DEPARTMENTS where DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID andDEPARTMENT_JOB = 'CLERK'
对于EMPLOYEES中有管理者的员工，列出姓名，管理者姓名（管理者外键为MANAGER）
1select a.EMPLOYEE_NAME as 姓名,b.EMPLOYEE_NAME as 管理者 from EMPLOYEES as a,EMPLOYEES as b where a.MANAGER is not null and a.MANAGER = b.EMPLOYEE_ID
对于DEPARTMENTS表中，列出所有部门名，部门号，同时列出各部门工作为'CLERK'的员工名与工作
1select DEPARTMENT_NAME as 部门名,DEPARTMENTS.DEPARTMENT_ID as 部门号,EMPLOYEE_NAME as 员工名,EMPLOYEE_JOB as 工作 from DEPARTMENTS,EMPLOYEES
2where DEPARTMENTS.DEPARTMENT_ID *= EMPLOYEES.DEPARTMENT_ID and EMPLOYEE_JOB = 'CLERK'
对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序
1select a.DEPARTMENT_ID as 部门号,a.EMPLOYEE_NAME as 姓名,a.SALARY as 工资 from EMPLOYEES as a
2where a.SALARY>(select avg(SALARY) from EMPLOYEES as b where a.DEPARTMENT_ID = b.DEPARTMENT_ID) order by a.DEPARTMENT_ID
对于EMPLOYEES，列出各个部门中平均工资高于本部门平均水平的员工数和部门号，按部门号排序
1select count(a.SALARY) as 员工数,a.DEPARTMENT_ID as 部门号 from EMPLOYEES as a
2where a.SALARY>(select avg(SALARY) from EMPLOYEES as b where a.DEPARTMENT_ID = b.DEPARTMENT_ID) group by a.DEPARTMENT_ID order by a.DEPARTMENT_ID
对于EMPLOYEES中工资高于本部门平均水平，人数多与1人的，列出部门号，人数，按部门号排序
1select count(a.EMPLOYEE_ID) as 员工数,a.DEPARTMENT_ID as 部门号,avg(SALARY) as 平均工资 from EMPLOYEES as a
2where (select count(c.EMPLOYEE_ID) from EMPLOYEES as c where c.DEPARTMENT_ID = a.DEPARTMENT_ID and c.SALARY>(select avg(SALARY) from EMPLOYEES as b where c.DEPARTMENT_ID = b.DEPARTMENT_ID))>1
3group by a.DEPARTMENT_ID order by a.DEPARTMENT_ID
对于EMPLOYEES中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数
1select a.DEPARTMENT_ID,a.EMPLOYEE_NAME,a.SALARY,(select count(b.EMPLOYEE_NAME) from EMPLOYEES as b where b.SALARY < a.SALARY) as 人数 from EMPLOYEES as a
2where (select count(b.EMPLOYEE_NAME) from EMPLOYEES as b where b.SALARY<a.SALARY)>5