数据库是MSSQL2005.
第一部分主要的操作包含:数据库的创建、删除,表的增、删、改,表中数据的增、删、改、查,视图的操作。
--
查询数据库是否存在
if exists ( select * from sysdatabases where [ name ] = ' TestDB ' )
print ' Yes, the DB exists '
else
print ' No, need a new one? '
-- 新建一个数据库
create database TestDB on
(
name = ' TestData ' ,
filename = ' G:\DBS\KeyTest.mdf ' ,
size = 3 ,
filegrowth = 2
)
log on
(
name = ' TestLog ' ,
filename = ' G:\DBS\KeyTest.ldf ' ,
size = 3 ,
filegrowth = 10
)
-- drop database TestDB
use TestDB
go
-- 新建一个表
create table [ Scores ]
(
[ ID ] int identity ( 1 , 1 ) primary key ,
[ Student ] varchar ( 20 ) ,
[ Subject ] varchar ( 30 ),
[ Score ] float
)
-- drop table [Scores]
-- 修改表中的一列
alter table Scores alter column [ Student ] varchar ( 20 ) not null
-- 新增一列
alter table Scores add Birthday datetime
-- 删除一列
alter table Scores drop column Birthday
-- 往表中插入单条数据,方法1:带列名
insert into Scores(Student,Subject,Score)
values ( ' 张三 ' , ' 语文 ' , ' 90 ' )
-- 往表中插入单条数据,方法2:不带列名,但要求值的类型要和列字段类型对应
insert into Scores
values ( ' 张三 ' , ' 英语 ' , ' 95 ' )
-- 插入多条数据:用union或者union all
insert into Scores(Student,Subject,Score)
select ' 李四 ' , ' 语文 ' , ' 89 '
union all
select ' 李四 ' , ' 英语 ' , ' 78 '
-- 删除表中数据,没有条件时,删除所有
delete from Scores where ID in ( 7 , 8 )
-- 修改表中数据
update Scores
set Student = ' 王五 ' ,Score = ' 94 '
where ID = 10
-- 查看数据
select * from Scores
-- 查看表中最大的identity值
select @@identity
-- 或者利用dbcc命令查看表中最大的identity值
dbcc checkident( ' Scores ' ,noreseed)
-- 创建视图,全部省略视图的属性列名,由子查询目标列的字段组成
create view StudentView
as
select Student,Subject,Score
from Scores
-- 加上with check option,以后对视图的操作(增,改,删,查)都会自动加上where ID>3
/*
create view StudentView
as
select Student,Subject,Score
from Scores
where ID>3
with check option
*/
-- 创建视图,全部定义属性列名,需要定义列名的情况:
-- --某个目标列(子查询)不是单纯的属性列,而是聚集函数或列表达式
-- --多表连接时选出了几个同名列
-- --需要在视图中为某个列启用新的更合适的名字
create view IS_Student(Student,Subject,MaxScore)
as
select Student,Subject,Score
from Scores
where Score = ( select max (Score) from Scores)
-- 查询视图,和基本表完全样,只不过如果视图中有with check option,会自动加上那个条件
select *
from StudentView
-- 查询自定义列名的视图
select *
from IS_Student
-- 对视图的insert/delete/update,和对基本表的操作一样,并且最终都是用RDBMS自动转换为对基本表的更新
-- 并不是所有的视图都是可更新的,因为有些视图的更新不能有意义的转换成对相应基本表的更新
-- 删除视图
drop view StudentView
if exists ( select * from sysdatabases where [ name ] = ' TestDB ' )
print ' Yes, the DB exists '
else
print ' No, need a new one? '
-- 新建一个数据库
create database TestDB on
(
name = ' TestData ' ,
filename = ' G:\DBS\KeyTest.mdf ' ,
size = 3 ,
filegrowth = 2
)
log on
(
name = ' TestLog ' ,
filename = ' G:\DBS\KeyTest.ldf ' ,
size = 3 ,
filegrowth = 10
)
-- drop database TestDB
use TestDB
go
-- 新建一个表
create table [ Scores ]
(
[ ID ] int identity ( 1 , 1 ) primary key ,
[ Student ] varchar ( 20 ) ,
[ Subject ] varchar ( 30 ),
[ Score ] float
)
-- drop table [Scores]
-- 修改表中的一列
alter table Scores alter column [ Student ] varchar ( 20 ) not null
-- 新增一列
alter table Scores add Birthday datetime
-- 删除一列
alter table Scores drop column Birthday
-- 往表中插入单条数据,方法1:带列名
insert into Scores(Student,Subject,Score)
values ( ' 张三 ' , ' 语文 ' , ' 90 ' )
-- 往表中插入单条数据,方法2:不带列名,但要求值的类型要和列字段类型对应
insert into Scores
values ( ' 张三 ' , ' 英语 ' , ' 95 ' )
-- 插入多条数据:用union或者union all
insert into Scores(Student,Subject,Score)
select ' 李四 ' , ' 语文 ' , ' 89 '
union all
select ' 李四 ' , ' 英语 ' , ' 78 '
-- 删除表中数据,没有条件时,删除所有
delete from Scores where ID in ( 7 , 8 )
-- 修改表中数据
update Scores
set Student = ' 王五 ' ,Score = ' 94 '
where ID = 10
-- 查看数据
select * from Scores
-- 查看表中最大的identity值
select @@identity
-- 或者利用dbcc命令查看表中最大的identity值
dbcc checkident( ' Scores ' ,noreseed)
-- 创建视图,全部省略视图的属性列名,由子查询目标列的字段组成
create view StudentView
as
select Student,Subject,Score
from Scores
-- 加上with check option,以后对视图的操作(增,改,删,查)都会自动加上where ID>3
/*
create view StudentView
as
select Student,Subject,Score
from Scores
where ID>3
with check option
*/
-- 创建视图,全部定义属性列名,需要定义列名的情况:
-- --某个目标列(子查询)不是单纯的属性列,而是聚集函数或列表达式
-- --多表连接时选出了几个同名列
-- --需要在视图中为某个列启用新的更合适的名字
create view IS_Student(Student,Subject,MaxScore)
as
select Student,Subject,Score
from Scores
where Score = ( select max (Score) from Scores)
-- 查询视图,和基本表完全样,只不过如果视图中有with check option,会自动加上那个条件
select *
from StudentView
-- 查询自定义列名的视图
select *
from IS_Student
-- 对视图的insert/delete/update,和对基本表的操作一样,并且最终都是用RDBMS自动转换为对基本表的更新
-- 并不是所有的视图都是可更新的,因为有些视图的更新不能有意义的转换成对相应基本表的更新
-- 删除视图
drop view StudentView
第二部分,这次练习的主题。
【一】行转列
1,查询原始的数据
/*
**这次练习的主题,行转列,列转行**
*/
select * from Scores
select * from Scores
2,得到姓名,通过group by
select
Student
as
'
姓名
'
from Scores
group by Student
order by Student
from Scores
group by Student
order by Student
3,再加上max, case……when
select
Student
as
'
姓名
'
,
max ( case Subject when ' 语文 ' then Score else 0 end ) as ' 语文 ' , -- 如果这个行是“语文”,就选此行作为列
max ( case Subject when ' 英语 ' then Score else 0 end ) as ' 英语 '
from Scores
group by Student
order by Student
max ( case Subject when ' 语文 ' then Score else 0 end ) as ' 语文 ' , -- 如果这个行是“语文”,就选此行作为列
max ( case Subject when ' 英语 ' then Score else 0 end ) as ' 英语 '
from Scores
group by Student
order by Student
查看其它资料时,看到另外一种方法,用pivot
-- group by, avg/max, pivot。这里用max和avg,结果都一样,有什么区别吗?有点不明白
-- 参考网上的资料,用法如下
/*
pivot(
聚合函数(要转成列值的列名)
for 要转换的列
in(目标列名)
)
*/
select Student as ' 姓名 ' ,
avg (语文) as ' 语文 ' , avg (英语) as ' 英语 ' from Scorespivot( avg (Score) for Subject in (语文,英语) ) as NewScoresgroup by Studentorder by Student asc