首页 > 代码库 > 菜鸟的sql server

菜鸟的sql server

bit 布尔类型
int nvarchar datetime 常用类型
nvarchar(max) 存文章(不超过5000)
字符串 用 ‘‘
1. char/nchar,varchar/nvarchar
 
char(10) 只能放五个中文,定长,如果只放一个字节,后面就是九个空格(一个中文两个字节)
nchar(10) 放十个中文,定长
 
varchar(10)放五个中文,变长,如果只放一个字节,就只放一个字节
nvarchar(10)放十个中文,变长。。。。。。。
 
2.创建一个数据库,在数据库里面创建表,以及添加表里面的项
create database TestDB
 
create table UserInfor
(
UserId int primary key not null,
UserName nvarchar(50),
Pwd nvarchar(50),
RealName nvarchar(50),
QQ nvarchar(50),
Sex nvarchar(50),
Age int,
ClassName nvarchar(20),
Major nvarchar(20),
Birthday datetime
)
 
3.select语句用法
select * from UserInfor
select UserId,UserName from UserInfor
select * from UserInfor where (Major=计算机 or 电子商务) and Sex =  order by Age
select top 2 * from UserInfor where (Major=计算机 or 电子商务) and Sex= order by Age desc
select top 2 * from UserInfor where Major in(计算机 , 电子商务) and Sex= order by Age desc
 
select Max(Age) from UserInfor
select Min(Age) from UserInfor
select Avg(Age) from UserInfor
select * from UserInfor where Age > 
(
     select Avg(Age) from UserInfor
)
select SUM(Age) from UserInfor

select count(*) as UserCount from UserInfor where Major=计算机
--分组
select count(*) as UserCount,Marjor from UserInfor group by Major having count(*)>3

--模糊查询
select * from UserInfor where RealName like %刘% 

--去除重复数据
select distinct RealName from UserInfor 

分页:
查询的区间要连贯 select row_number() over(order by UserId)
select top 5 *
from
(
--查询出所有行号并取别名
select row_number() over(order by UserId) as rownumber,* from UserInfor
) A
where rownumber >0 
--rownumber >0 1-5 第一页
--rownumber >5 6-10 第二页
--rownumber >10 11-15 第三页
--rownumber >0 16-20 第四页
。。。。

select UserId,Age
case
when Age>=20 and Age<=30 then 大一
when Age>=24 and Age<=26 then 大二 
else 大三
end as UserGrade from UserInfor

select * from UserInfor where Age>=20 and Age<=24
select * from UserInfor where Age between 20 and 24

select * UserId isnull(ClassName,一班) from UserInfor 

--获取系统默认时间
datetime--->getdate()
select year(getdate()) --获取当前年
select year(getdate()) --获取当前年
select year(getdate()) --获取当前年

其他的:
select * from employ where month(birthday)=8   // 打印出8月份过生日的员工的所有信息
select * from employ where year(getdate())-year(birthday)>25// year(getdate())为当前年份,打印出年龄大于25岁的员工的所有信息
select * from employ where year(birthday)=2008 and month(birthday)=8 and day(birthday)=12 //打印出生日为2008-8-12日的所有员工信息
 
select dateadd(yy,100,getdate())//当天加上100年的时间,getdate()也可以换成具体的某一天比如写成:‘2108/12/31‘
select dateadd(mm,1,getdate())//当天加上1个月的时间
select dateadd(dd,100,getdate())//当天加上100天的时间
 
select datediff(yy,getdate(),2108/12/31)//当天距离2108/12/31还有多少年
select datediff(mm,getdate(),2108/12/31)
select datediff(dd,getdate(),2108/12/31)

create table StuScore
(
ScoreId int primary key not null,
UserId int,
MathScore float,
ChineseScore float,
PCScore float
)
--联合查询
select UI.UserId,UI.UserName,UI.QQ,UI.RealName,SS.ScoreId,SS.ChineseScore form UserInfor UI.StuScore left join StuScore SS
on UI.UserId=SS.UserId      --以左边的表为准

select UI.UserId,UI.UserName,UI.QQ,UI.RealName,SS.ScoreId,SS.ChineseScore form UserInfor UI.StuScore right join StuScore SS
on UI.UserId=SS.UserId      --以右边的表为准

select UI.UserId,UI.UserName,UI.QQ,UI.RealName,SS.ScoreId,SS.ChineseScore form UserInfor UI.StuScore inner join StuScore SS
on UI.UserId=SS.UserId --两边都有

create table Parent
(
ParentId int primary key not null,
UserId int,
MMName nvarchar(50),
FatherName nvarchar(50) 
)

---三张表的联合查询
select UIS.*,SP.MMName,SP.FatherName from
(
select UI.UserId,UI.UserName,UI.QQ,UI.RealName,SS.ScoreId,SS.ChineseScore form UserInfor UI.StuScore inner join StuScore SS
on UI.UserId=SS.UserId
) UIS inner join Parent SP
on UIS.UserId=SP.UserId

insert into Parent values(2,大西街,回合肥) --主键自增不用添加
insert into Parent (UserId,MMName,FatherName) values(2,大西街,回合肥 )

update Parent set MMName=sss,ParentName=fff where ParentId=6
delete Parent where ParentId=6 --删之前一定要备份

create table A
(
AId int primary key not null,
UserId int,
AMMName nvarchar(50),
AFName nvarchar(50)
)

--复制数据
insert into A(UserId,AMMName,AFName) select UserId,MMName,FartherName from Parent

--约束:
右键-check-表达式-保存



一、 索引
(1) 概述
表的存储由两部分组成,一部分用来存放数据页面,另一部分存放索引页面。
索引的功能类似百科全书中的目录,使用索引是快速查找数据库中实际信息的一种方法。
索引分为:
聚集索引--------每一个表只能有一个聚集索引,它对应的表按照其索引进行物理排序,对于百科全书,聚集索引就类似于书的页码,按页码顺序保存着百科全书的信息。
非聚集索引-------每一个表可以有多个非聚集索引,对于百科群书,非聚集索引指的是百科全书后面的关键字目录。
(2)两者选择
情况描述                                   使用聚簇索引            使用非聚簇索引 

用于返回某范围内数据的列                     应                      不应 

经常被用作分组排序的列                        应                       应 

小数目不同值的列                                 应                      不应 

连接操作使用的列                              应                       应 

频繁更新、修改的列                            不应                       应 

一个或极少不同值的列                         不应                      不应 

大数目不同值的列                               不应                       应
(3)创建索引时注意事项
1) 默认情况下,SQL Server会默认主键为聚集索引,这样会造成资源的浪费。
 
假设病人费用表名为“brfy”,其中住院号字段名为“zyh”,日期字段名为“riqi”,要求是从表brfy中检索zyh为“028246”的病人2005年3月1日到20日的费用,对应的SQL语句如下: 
Select * from brfy where zyh=’028246’ and riqi>=’20050301’ and riqi<=’20050320’; 
第一种情况,用ID列建立聚簇索引,不为zyh和riqi建立索引,查询时间为87秒。 
第二种情况,用ID列建立聚簇索引,为zyh和riqi两列建立非聚簇索引(zyh在前),查询时间为33秒。 
第三种情况,用zyh和riqi两列建立聚簇索引(zyh在前),查询时间为2秒。 
由以上分析可以看出聚簇索引是非常宝贵的,应该为经常用于检索某个范围内数据的列或group by、order by等子句的列建立聚簇索引,这样能够极大的提高系统性能。

2)顺序问题
在第二、第三种情况下,如果把riqi放在zyh前面,执行上述SQL语句就不会用到这两个索引,检索的时间也会变得很长。应该按照顺序来。
 
3) 索引的维护 
数据库系统运行一段时间后,随着数据行的插入、删除和数据页的分裂,索引对系统的优化性能就会大大降低。这时候,我们需要对索引进行分析和重建。 
SQL Server使用DBCC SHOWCONTIG确定是否需要重建表的索引。在 SQL Server的查询分析器中输入命令: 
Use database_name 
Declare @table_id int 
Set @table_id=object_id (Employee) 
Dbcc showcontig (@table_id) 
在命令返回的参数中Scan Density 是索引性能的关键指示器,这个值越接近100%越好,一般在低于90%的情况下,就需要重建索引。重建索引可以使用DBCC DBREINDEX,使用方式如下: 
dbcc dbreindex(表名, 索引名, 填充因子)       /*填充因子一般为90或100*/ 
如果重建后,Scan Density还没有达到100%,可以重建该表的所有索引: 
dbcc dbreindex(表名, ‘‘, 填充因子)
 
右键-索引-重新组织-重新生成
右键-索引-删除

(4)如何建索引
1 在“索引/键”对话框中单击“添加”。
2 从“选定的主/唯一键或索引”列表中选择新索引。
3 在网格中选择“创建为聚集的”,然后从该属性右侧的下拉列表中选择“是”。保存表时将在数据库中创建索引。
 
二、 视图
视图是一种虚拟表,它的使用基本和表的使用类似,主要目的是控制用户所要显示的数据。
创建一个视图:
Eg:
create view CurrentEmployees_vw
as
select categoryID,[name],age from Category
where state=1
 
在Management stodio中编辑视图(方法略)
 
三.存储过程
1)优点:在创建时进行编译,以后每次执行存储过程不需要再编译,而一般的SQL语句要每执行一次编译一次(所以一些复杂逻辑的SQL建议写在存储过程里面);存储过程过重复使用,可减少数据库开发人员的工作量(所以一些经常被调用到的SQL建议写在存储过程里面);安全性高,可设定只有某些用户才具有指定存储过程的使用权。
 
1)基本语法
Eg:
create proc procUserInfor
(
 @UserName varchar(50),
 @Pwd nvarchar(50)
)
as
select * from UserInfor where UserName=@UserName and Pwd=@Pwd ---登录时用到的语句可以用存储过程
2) 查看结果exec procUserInfor 萨顶顶,45612
3) 更改/删除存储过程
ALTER proc
Drop proc
 
4)流控制语句
Eg:
create proc procNewsInsert
(
  @creatTime datetime
)
as
if datediff(dd,@creatTime,getdate())>7  /*creatTime距离当天大于天*/
begin;
select * from News
end;
else
begin;
select top 3* from News
end;
 
执行:exec procNewsInsert 2009-05-10 11:36:43.810
 
结果:
 
 
 
四 触发器
触发器是一种特殊的存储过程,它是在数据在进行增、删、改的时候同时进行的操作,相当于一个事件。
新建一个触发器:
 
写的脚本:
Eg1:
create trigger trigCategoryDelete
  ON  Category
  after delete /* 有三种INSERT,DELETE,UPDATE*/
AS
begin
    select * from deleted /*在删除后同时查出删除后的内容*/
end
GO
 
执行:
 
 
Eg2:
create trigger trigCategoryDelete
  ON  Category
  instead of delete /* instead of,表示当执行删除的时候这个删除语句被begin下面的语句所代替*/
AS
begin
    declare @id int
select @id=id from deleted
delete news where caID=@id
delete category where id=@id
end
GO
 
执行:
delete from category where id=5 /*这样就可以删除category里面id=5的东东的同时删除news里面id=5的东东了 */
 
backup database TestDB to disk=h://123.bak

 

菜鸟的sql server