主页 > 知识库 > 数据库 > 数据库综合 >

如何使用SQL语句将行和列进行转换?

来源: 作者: 发表于:2012-06-11 08:23  点击:
本来只是想解决怎么把数据的行和列进行转换的,但最近觉得一些数据库SQL语句的操作,很久没用了,有点陌生。所以也就随笔记录一些简单但很基本的操作。 我的数据库是MSSQL2005. 第一部分主要的操作包含:数据库的创建、删除,表的增、删、改,表中数据的增、

   本来只是想解决怎么把数据的行和列进行转换的,但最近觉得一些数据库SQL语句的操作,很久没用了,有点陌生。所以也就随笔记录一些简单但很基本的操作。

  我的数据库是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:DBSKeyTest.mdf',
     size = 3,
     filegrowth = 2
)
log on
(
     name = 'TestLog',
     filename = 'G:DBSKeyTest.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

  2,得到姓名,通过group by

select Student as '姓名'
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

  查看其它资料时,看到另外一种方法,用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

    有帮助
    (0)
    0%
    没帮助
    (0)
    0%