自己整理的常用SQL Server 2005 语句、

--创建数据库

create database 数据库

go

--打开数据库

use 数据库

--删除数据库

drop database 数据库

Go

--创建数据表

create table 数据表

(

列名1  数据类型1  限定条件(是否是主外键、是否为空),

列名2  数据类型2  限定条件(是否是主外键、是否为空)

)go

--删除数据表

drop table 数据表

 

--插入数据

--插入单行数据

insert  into 表名 [(列名1,列名2 )]  values  (‘数据1’,’数据2’)

--一次插入多行数据  两个表都存在 

insert  into 表2  [(列名)]  select  列名  from 表1

/*注：1、这样就可以将表1中的数据一次取出存储到表2了

2、语法中的表2必须事先存在，如果不存在，执行时会出现错误

3、查询出的字段数目、数据类型，字段顺序，与插入列保持一致*/

--一次插入多行数据  新表不存在，原始表存在

select  列名  into 新表  from  原始表

--一次插入多行数据  新表不存在，原始表存在 并且自动生成标识列

Select  identity (数据类型，标识种子，标识增量） as 列名  into  新表  from  原始表

--使用union关键字插入多行数据

Insert  [into] 表名  [列名]  

select  数据  union

select  数据  union

select  数据

/*注：1、列名可以省略，如果省略了列名，那么数据中的个数，顺序，数据类型必须与表中字段顺序，数据类型保持一致

 2、如果列名部分有默认约束列，不能使用default关键字设定默认值

--修改数据

update 表名 set 列名=值 [where 更新条件]

--表中该列的所有数据都更新

update 表名 set 列名=’要修改成的值’

--例，将表中id为2的人的年龄改为24

update 表名 set age=24 where id=2

--例 把图书表中“计算机”或者“经济管理”类的图书价格降低2元

update books set price=price-2 where CatagoryID=1 or Catagory=2

/*注：1、可以更新主键列数据，但应保证更新后的主键列数据不能出现重复信息，否则更新失败

 2、可以更新外键列数据，但应保证新数据在主键表的中应事先存在*/

--删除数据

delete  from  表名  [where  更新条件]

/*注：1、使用delete删除数据时，不能删除主键值被引用的数据行

     2、使用delete语句时，一般都需要使用where子句限定条件，如果忘记写where条件，那么表中所有数据都将被删除，无法恢复。*/

truncate  table 数据表

/*1、truncate  table 语句相当于一个没有where子句的delete语句，但是truncate执行速度更快

  2、truncate  table 只删除表中的数据行，不会删除表结构及各种约束

  3、truncate  table 不能删除具有引用关系的数据表

--查询数据

Select  [列名]  from  表名  

[where  查询条件表达式]  

[order  by  排序的列名  [ asc (升序) 或 desc (降序)]]

/*  1、[]代表可选的，<>代表必需的

        2、如果有多个列名用逗号隔开

3、order by 是用于排序的，可以将查询出的数据按照order by 所指定的字段进行排序，排序时通过asc 或desc 指定升序或降序排列*/

--1、将表中的所有列查询出来，在列名处使用“*”，表示所有列

Select  *  from  表名

--2、使用where 过滤部分行数据，例如，查询city=北京的作者信息

Select  name, email, age  from authors  where  city=’北京’

--3、使用as子句将列转别名  除了使用as子句转别名，还可以使用“=”  例：

Select  name as ‘姓名’ ,  email as ‘电子邮件’ ,  age as ‘年龄’  from  authors 

Select   ‘姓名’ =name ,  电子邮件 =email , 年龄 =age  from  authors 

/*1、使用as转别名时，如果是中文别名时，可以不写引号

  2、别名列应使用英文半角引号，否则将出现错误*/

4、使用“+”连接多个字段，合并成一列    例：

Select  authorname + ‘&&&’+ city as 城市  from  表名

5、使用top关键字查询表中限定行数

Select top 5 * from 表名

Select top 30 percent * from 表名

6、在查询结构中使用常量列    有时需要在查询结果集中增加默认值列。

例如，查询作者姓名和满意度分值，要求满意度分值都是80分

Select authorname as 作者姓名 , 80 as 满意度  from 表名

7、使用distinct关键字屏蔽重复数据

Select  distinct 列名 from 表名

8、使用is null 查询空数据    查询还没有填写过‘出生日期’的信息的作者

Select  name, birthday, city  from  authors  where  birthday  is  null

9、将多个表的数据一并查出，并且以一个结果集的形式显示可以使用union联合查询,union写在每条查询语句的后面

--对查询的结果集进行排序

1、查询所有信息并按某一列排序  默认asc(升序)

Select  *  from  表名  order by 列名

2、查询单价在50元以上的图书信息，要求价格以8折显示，并按价格降序排列

Select  bookname as 图书名，description as 描述， Unitprice * 0.8 as 单价  from  books

Where  Unitprice >50  order by Unitprice  desc

3、查询结果可以按多列进行排序，并且每列都可以指定不同的排序方式。例：查询图书信息先按图示类别升序排，再按价格降序排列

Select  bookname as 图书名, categoryID as 类别编号, Unitprice as 单价  from  books 

order by categoryID, Unitprice  desc

--模糊查询

--使用like进行模糊查询

Select  [列名]  from  表名  where 列名  [not]  like 字符串表达式

查询中间含有关键字的: 

select * from 表 where 字段名 like '%关键字%' 

第1个字符与关键字符合的: 

select * from 表 where 字段名 like '关键字%' 

第2个字符含有关键字的: 

select * from 表 where 字段名 like '_关键字%' 

第3个字符含有关键字的: 

select * from 表 where 字段名 like '__关键字%'

--使用in在限定值内进行模糊查询

Select  [列名]  from  表名  where 列名  [not]  in  (常量列表)    --常量列表中各常量值用逗号隔开

例如：查询居住地（不）在，北京，上海，西安，成都的作者

Select  authorname,city  from  authors  where  city  (not)  in (‘北京’,‘上海’.‘西安’.‘成都’)

--使用between...and...语句在指定的范围内查询

Select  [列名]  from  表名  where 列名  [not]  between  起始表达式  and  结束表达式

--常用的SQL内置函数

1，返回字符表达式中最左侧字符的ASCII代码值  select Ascii('a') --a:97,A:65

2，将整数ASCII代码转换为字符 select Char(97)--97:a,65:A

3，返回具有给定的整数代码的UNICODE字符 select Nchar(65)--A

4，返回表达第一个字符的UNICODE整数值 select Unicode('a')--97

5, 返回将大写字符转换为小字符的字符表达式 select Lower('ABCDEFG')--abcdefg

6, 返回将小写字符转换为大写字符的字符表达式 select Upper('a')--'A'

7，返回删除了前导空格之后字符表达式 select Ltrim('　　 abcdefg')--abcdefg

8, 返回截断了所有尾随空格之后的字符表达式 select Rtrim('abcd　　　 ')--abcd

    

9，返回字符表达式最左侧指定数目的字符 select Left('abcdefg',3)--abc

10, 返回字符表达式右侧指定数目的字符 select Right('abcd',3)--bcd

11, 以整数返回两个字符表达式的SOUNDEX值之差 select Difference('bet','bit')--3  www.2cto.com  

12, 返回由四个字符表达的SOUNDEX代码 select Soundex('abcd')--A120

13, 返回给定字符串表达的字符数 select Len('abcdefg')--7

14，返回表达式中指定字符的开始位置  select Charindex('b','abcdefg',5)

15, 返回指定表达式中模式第一次出现的开始位置 select Patindex('%_cd%','abcdefg')--2

16, 按指定次数重复表达式 select Replicate('abc|',4)--abc|abc|abc|abc|

17, 返回由重复空格组成的字符串 select Space(10)--[　　　　　　　　　 ]

18, 返回从默认表达转换而来的字符串 select Str(100)--[　　　　　　 100]

19, 返回字符表达式的逆向表达式 select Reverse('abc')--cba

20, 删除指定长度的字符,并在指定的起点处插入另一组字符 select Stuff('abcdefg',2,4,'xxx')--axxxfg

21, 用第三个表达式替换第一个表达式中出现的第二个表达式 select Replace('abcdefg','cd','xxx')--abxxxefg

22, 返回为成为有效的SQL SERVER分隔标识符而添加了分隔符的UNICODE字符串 select Quotename('create table')

23, 返回字符表达式,二进制,文本表达式或图像表达的一部分 select Substring('abcdefg',2,3)--bcd

24，返回由数字数据转换而成的字符数据 select Str(14.4444,5,4)  (输入float类型数据也可以是int  加小数点一共几位，取出的小数中保留几个小数位)

--几个聚合函数

--可以多个聚合函数一起使用，进行查询

--SUM,用于对表达式中的所有数值进行汇总求和

Select  SUM(字段名)  as 总分  from 表名

--Avg函数返回数据列的平均值，数据列中如果有null值，null值不包括在计算中

Select  AVG(字段名)  as 平均分  from 表名

--MAX函数返回一列中的最大值，null值不包括在计算中

Select  MAX(字段名)  as 最高积分  from 表名

--MIN函数返回一列中的最大值，null值不包括在计算中

Select  MIN(字段名)  as 最低积分  from 表名

/*注：max和min除了计算数值列，还可以用于计算字符型以及日期时间类型数据列*/ 例：

Select  max(birthday)  as  最大日期  from 表名

--Count 返回结果集的非空行数  Count(*)返回表中所有数据行的记录数  Count(列名)返回指定列非空值个数

Select count(*)  as  个数  from 表名 where  限定条件

--分组统计

Select  字段列表  [聚合函数]  from  表名  [where  条件]  group by 字段列表

Select  字段1，字段2， [聚合函数]  from  表名  group by 字段1，字段2  --按多列分组

--使用having子句对分组再次过滤

Select  字段列表  [聚合函数]  from  表名  [where  条件]  [ group by 字段列表]  

[having 条件][order by 字段列表 asc/desc]

--约束

在SQL Server 2005中有6种约束：

 主键约束（primary key constraint）、

 惟一性约束（unique constraint）、

 检查约束（check constraint）、

 默认约束（default constraint）、

 外部键约束（foreign key constraint）

 空值（NULL）约束。

--单独添加约束

-----------alter table stuInfo  --指定添加约束的表明

----------add constraint pk_stuNo primary key (stuNo)  --添加主键约束

alter table stuInfo --指定表名

drop constraint pk_stuNo --指定删除的约束名 constraint 只是显示和隐式的区别

alter table stuInfo

---添加默认约束,如果地址不填写，则为地址不详--

add constraint DF_stuAddress default ('地址不详') for stuAddress

alter table stuInfo

---添加唯一约束,号码唯一---

add constraint uq_stuID unique (stuID)

alter table stuInfo

---添加检查约束，年龄在15到40之间---

add constraint ck_stuAge check (stuAge between 15 and 40)

alter table stuMarks

---建立外键约束,主表stuInfo和从表stuMarks建立关系，关联字段为stuNo---

add constraint fk_stuNo

foreign key(stuNo) references stuInfo(stuNo)

alter table stuMarks --指定表名

drop constraint fk_stuNo --指定删除的约束名 constraint 只是显示和隐式的区别,删除外键约束

--表中添加约束

id int primary key identity(11001,1),--编号，主键，自动增长

taccount varchar(50) not null unique,--教师工号 唯一约束

tDepartment int foreign key references t_department(id)--所属系别，外键，主表t_department(id)

Truenumber Int default(0) not null--实际选课人数 默认0

Age int check (Age between 15 and 40)--年龄限制，进行检查约束