博客

本来只是想解决怎么把数据的行和列进行转换的，但最近觉得一些数据库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:\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

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