行转列
建表
Create table StudentSubject(SId int identity(1,1)not null PRIMARY KEY,
SName varchar(50) null,
Subject varchar(50) null,
Score numeric(18,2)null)
插入数据
INSERT INTO StudentSubject
select '张三','语文',90
union all select '李四','语文',87
union all select '王五','语文',78
union all select '张三','数学',88
union all select '李四','数学',77
union all select '王五','数学',86
union all select '张三','英语',88
union all select '李四','英语',87
union all select '王五','英语',98
表效果
select * from StudentSubject order by SName
行转列
方法一：
with ss as (
select SName,(Case When Subject='语文' then Score else 0 end) as '语文',
case when Subject='数学' then Score else 0 end as '数学',
case when Subject='英语' then Score else 0 end as '英语'
from StudentSubject
)
SELECT SName ,MAX(语文)as 语文,max (数学)as 数学,max(英语)as 英语from ss group by SName
方法二:
select SName,max(Case When Subject='语文' then Score else 0 end) as '语文',
max(case when Subject='数学' then Score else 0 end) as '数学',
max(case when Subject='英语' then Score else 0 end) as '英语'
from StudentSubject group by SName
效果图:
横向汇总和竖向汇总:
Sql: select SName,max(Case When Subject='语文' then Score else 0 end) as '语文',
max(case when Subject='数学' then Score else 0 end) as '数学',
max(case when Subject='英语' then Score else 0 end) as '英语',
sum(Score)as '总分',
cast(avg(Score)as numeric(18,2))as '平均分'
from StudentSubject group by SName
union all select '总分',sum(case when subject='语文' then score else 0 end),
sum(case when subject='数学' then score else 0 end ),
sum(case when subject='英语'then score else 0 end ),
sum(score),avg(score)from StudentSubject
----------------------------------------------以下是常用sql-------------------------------------------------------------
一、 新建数据库
语法如下：
CREATE DATABASE [DATATEST] ON PRIMARY(NAME=N'DBTEST',FILENAME=N'E:\DBTEST.MDF',SIZE=3072KB,MAXSIZE=UNLIMITED,FILEGROWTH=1024KB) LOG ON (NAME=N'DBTEST_LOG',FILENAME=N'E:\DBTEST_LOG.LDF',MAXSIZE=1024GB,FILEGROWTH=10%)
COLLATE Chinese_PRC_CI_AS
其中COLLATE Chinese_PRC_CI_AS 是用来设置数据库排序用，具体详见文档理解collate_Chinese_PRC_CI_AS.docx
select * from ::fn_helpcollations();可以查出所有排序规则
二、数据表的相关修改
1、新建表
方法一：create table [表名]
(
[自动编号字段] int IDENTITY (1,1) PRIMARY KEY ,
[字段1] nVarChar(50) default ‘默认值’ null ,
[字段2] ntext null ,
[字段3] datetime,
[字段4] money null ,
[字段5] int default 0,
[字段6] Decimal (12,4) default 0,
[字段7] image null ,
)
方法二：
CREATE TABLE [dbo].[Students] (
[Guid]uniqueidentifier not null,
[Code] varchar(50)COLLATE CHINESE_PRC_CI_AS null,
[ID] INT NULL,
[Adress]varchar(50)COLLATE CHINESE_PRC_CI_AS NULL,
[Age] INT NULL,
[Tel] VARCHAR(50) COLLATE CHINESe_PRC_CI_AS NULL,
[Chinese]numeric (18,2)default 0 NULL,
[Mathematical]numeric(18,2) default 0 null,
[English]numeric(18,2) default 0 null
CONSTRAINT [PK_Students] primary key nonclustered
(Guid asc)
) ON [PRIMARY]
删除表：
Drop table [表名]
删除表的一列: ALTER TABLE [表名] DROP COLUMN [字段名]
添加表的一列:ALTER TABLE [表名] ADD [字段名] NVARCHAR (50) NULL
修改表的一列: ALTER TABLE [表名] ALTER COLUMN [字段名] NVARCHAR (50) NULL
为表创建约束: ALTER TABLE [表名] ADD CONSTRAINT 约束名 CHECK ([约束字段] <= ‘2000-1-1′)
删除表约束:ALTER TABLE [表名] DROP CONSTRAINT 约束名
新建默认值
ALTER TABLE [表名] ADD CONSTRAINT 默认值名 DEFAULT ‘51WINDOWS.NET’ FOR [字段名]
删除默认值
ALTER TABLE [表名] DROP CONSTRAINT 默认值名
插入数据：
INSERT INTO [表名] (字段1,字段2) VALUES (100,’51WINDOWS.NET’)
INSERT INTO [表名]（字段1，字段2，……）SELECT newid()，字段二,……FROM 表二
删除数据：
DELETE FROM [表名] WHERE [字段名]>100
Truncate table [表名]删除表中所以数据同时不删除表结构，比delete from [表名]效率要高的多
更新数据
UPDATE [表名] SET [字段1] = 200,[字段2] = ‘51WINDOWS.NET’ WHERE [字段三] = ‘HAIWA’
连接查询
SELECT A.* ,B.Type,C.Count FROM 表A inner join 表B on a.字段1=B.字段一 left outer join 表C on B.字段二=C.字段二
嵌套查询
order by
group by
case when then else end
left outer join; right outer join ; inner join ,full outer join
excit
游标
存储过程
数据库写事务
分页查询如何做
创建数据库函数
熟记数据库内置函数
数据库里写递归cte
数据库作业
视图
Create View as (select * from 表一 inner join 表二on 表一.id=表二.Fid)
行变列，列变行，横向汇总，竖向汇总
优化，并发如何处理
浙公网安备 33010602011771号