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

时间:2023-03-09 19:45:00
自己整理的常用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)--年龄限制,进行检查约束