T-SQL查询语句大全

一、 1）建立数据库：
例：
create database student
on primary
(
name=student_data,
filename=”c:\student_data.mdf”,
size=2mb,
filegrowth=1mb,
maxsize=20mb
)
log on
(
name=student_log,
filename=”c:\student_log.ldf”,
size=1mb,
filegrowth=10%,
maxsize=15mb
)

注：create:创建 database:数据库 student:学生（自己起的数据库名称） primary:主要的 name:姓名 filename:文件路径 size:文件大小 filegrowth:文件增长 maxsize:文件最大存储容量 log：日志
2）修改已建好的数据库：
修改数据库建立时文件（主数据文件或日志文件）的大小，要更改文件最大值和文件增长速度，只需将size改为maxsize或filegrowth即可。
alter database student modify file
(
name='student_log',
size=4mb
)
注：alter：修改 modify：扩充
修改数据库，要增加数据库次要数据文件。
alter database student add file
(
name='student_data2',
filename="e:\student_data2.ndf",
size=1mb,
maxsize=10mb,
filegrowth=1mb
)
二、 配置数据库
（1） 为student数据库设置为“只读”属性
exec sp_dboption ‘student’,’read only’,’true’
（2） 为student数据库设置为“单用户”属性
exec sp_dboption ‘student’,’single user’,’true’
（3） 为student数据库设置为“自动收缩”属性
exec sp_dboption ‘student’,’autoshrink’,’true’

注：如对设置属性进行删除，则将’true’改为’false’
（4） 手动式压缩数据库
dbcc shrinkdatabase (student,10)
注：exec:执行配置 sp_dboption:存储
single user:单用户 read only:只读
autoshrink:自动压缩 dbcc:收缩数据库命令
shrinkdatabase:压缩数据库 student:数据库名
10:允许数据库有10%的未用空间
三、 删除数据库
drop database student
注：drop:删除
四、 创建用户定义数据类型
exec sp_addtype ci,’nvarchar(20)’,null
注：sp_addtype:新数据类型
ci:新数据类型名称
五、 创建数据库表
use student
create table student_inf
(
student_id int not null,
student_name nvarchar(15) not null,
student_sex nvarchar(1) not null,
student_age int not null
)
注：use:使用（如果没有选择在哪个数据库里建表，则必须使用此语句） student_inf:数据表名 student_id:学号（列名1） int:数据类型1 not null:非空 student_name:学员姓名（列名2） nvarchar(15):数据类型2 student_sex:学员性别（列名3） student_age:学员年龄（列名4）
六、 修改已建好的数据表
（1） 修改列的数据类型
alter table student_inf alter column student_name nvarchar(10) null
注：alter:修改 student_inf:表名 column:列 student_name:列名 nvarchar(10):数据类型 null:允许为空
（2） 删除一列内容：
alter table student_inf drop column student_sex
注：drop:删除 student_sex:所要删除的列名
（3） 增加一列内容：
alter table student_inf add student_sex nvarchar(1) null
注：student_sex:列名 null:增加列名允许为空
七、 为了保证数据的完整性，需要添加的约束
（1） 实体完整性：
1） 主键：primary key
在创建时加主键约束：
create table student_mark
(
student_id int not null primary key,
computer_mark nvarchar(15) not null,
math_mark nvarchar(1) not null,
Chinese_mark int not null
)
注：student_mark：表名 computer_mark：计算机成绩
math_mark：数学成绩 Chinese_mark：语文成绩
在修改表时添加主键约束：
alter table student_inf add constraint pk primary key (student_id)
注：add:加 constraint:约束
pk:自己起的约束名称，方便于对约束进行删除
在修改表时删除主键约束：
alter table student_inf drop constraint pk
2） 唯一约束：unique
在创建时加唯一约束：
create table student_mark
(
student_id int not null unique,
computer_mark nvarchar(15) not null,
math_mark nvarchar(1) not null,
Chinese_mark int not null
)

在修改表时添加唯一约束：
alter table student_inf add constraint un unique (student_id)

在修改表时删除唯一约束：
alter table student_inf drop constraint un

3） 标识列：identity(标识种子，标识增量)—从标识种子开始，每加一条记录就自增1，需在创建时加入，并可直接将此列定义为主键列。
create table student_mark
(
student_id int identity(1,1) primary key,
computer_mark nvarchar(15) not null,
math_mark nvarchar(1) not null,
Chinese_mark int not null
)
（2） 引用完整性
外键：foreign key
在创建时加入外键：
create table student_mark
(
student_id int not null foreign key references student_inf(student_id),
computer_mark nvarchar(15) not null,
math_mark nvarchar(1) not null,
Chinese_mark int not null
)
注：references：关系 student_inf：主键表 student_id：主键 列
在修改表时加入外键：
alter table student_mark add constraint fk foreign key (student_id) references student_inf(student_id)

在修改表时删除外键约束：
alter table student_mark drop constraint fk
（3） 域完整性
1） default约束：当列值为空时，用default约束后面的值来代替空值

在建表时同时创建：
create table student_mark
(
student_id int not null,
computer_mark nvarchar(15) null default ‘unknow’,
math_mark nvarchar(1) not null,
Chinese_mark int not null
)
注：unknow：不知道

在修改表时加入default约束：
alter table student_mark add constraint de default ‘unknow’ for computer_mark


在修改表时删除default约束：
alter table student_mark drop constraint de
2） check约束：用条件来约束本列数据
在建表时同时创建：
create table student_inf
(
student_id int not null,
student_age int not null check(student_age>15 and student_age <100 ) ,
student_name nvarchar(15) not null,
student_sex nvarchar(1) not null
)

在修改表时加入check约束：
alter table student_inf add constraint ch check(student_age>15 and student_age <100 )
注：连接两个条用：and:并且 or：或

在修改表时删除default约束：
alter table student_mark drop constraint ch
八、 删除数据库表
drop table student_inf
九、 SQL里的条件表达式和逻辑运算符：
运算符 含义
> 大于
< 小于
>= 大于等于
<= 小于等于
= 等于
! 非
<> 不等于
not 逻辑非：否定条件

and 逻辑与：连接两个条件且仅当两个条件都为真时返回 TRUE
or 逻辑或：连接两个条件，但只要其中任一个为真就返回 TRUE
使用方法：一般放于where子句里进行条件限制的筛选
例：
a) select * from student_inf where student_id=1
b) select student_name,student_sex,student_age from student_inf where student_age>15 and student_age<20
注：select：查询显示
十、 通配符
图示：本博客不支持哦！
十一、向表中插入数据：
1）直接将值进行插入：
insert into student_mark (computer_mark,math_mark)
values (89,86)
2）用其他表中的数值直接插入到另一张表里：
insert into student_mark (computer_mark,math_mark)
select q,w from qwe
注：q:是表qwe的一列 w: 是表qwe的一列
qwe：是表名
十二、更新数据行：
update student_mark set computer_mark=computer_mark+2 where student_id=3
十三、联接：是为了联接显示最终结果，并非为两张表设置主外键
（1） 内联接：inner join … on …
筛选出两张表里的公共内容
select * from student_inf inner join student_mark on student_inf.student_id=student_mark.student_id
注：在查询结果窗口中显示两张表中的所有内容于一张联合表中。
（2） 外联接：
1） 左外联接：left outer join … on …
以左表为主，显示与其相连的右表内容，若右表中此记录不存在则用null来代替：
select * from student_inf left outer join student_mark on student_inf.student_id=student_mark.student_id
2） 右外联接：right outer join … on …
以右表为主，显示与其相连的左表内容，若左表中此记录不存在则用null来代替：
select * from student_inf right outer join student_mark on student_inf.student_id=student_mark.student_id
十四、删除数据
1） 删除某一行数据：
delete from student_mark where student_id=1
2） 删除表里的所有数据：
delete from student_mark
3） 删除表里的所有数据：
truncate table student_mark


附加：以下内容为附加内容，有精力的同学可以加以参考，不做要求
3） 完全外部联接：full outer join … on …
将两张表中所有行显示出来，如果没有匹配的内容则用null来补充。
select * from student_inf full outer join student_mark on student_inf.student_id=student_mark.student_id
（3） 自联接：本表与自身相连
假设要显示在一张表里，学员math_mark成绩相等的的两个学员的学号和语文成绩。那么就讲这张表与自身相连所联接的列是math_mark，为了保证显示数据的不冗余，因此加了个条件student_mark.student_id< student_mark1.student_id
代码：
select
student_mark.student_id, student_mark.chinese_mark, student_mark1.student_id, student_mark1.chinese_mark from
student_mark inner join student_mark as student_mark1 on student_mark.math_mark= student_mark1.math_mark and student_mark.student_id< student_mark1.student_id

（4） 交叉联接 ：CROSS JOIN
在这类联接的结果集内，两个表中每两个可能成对的行占一行。例如，在pubs数据库表中，通过作者 CROSS JOIN 出版商输出的结果集内，每个可能的作者/出版商组合占一行。所得到的 SQL 可能象下面这样：
SELECT *
FROM authors CROSS JOIN publishers

附加内容结束
十五、查询语句：select
1） 查询全部行和列：
select * from student_inf
2） 查询一张表里的某些列：
select student_id,student_name,student_age from student_inf
3） 查询一张表里的某一行：
select student_id,student_name from student_inf
where student_id=3
十六、排序子句：order by … desc 降序
order by … 升序
SELECT student_id,computer_mark, math_mark FROM student_mark ORDER BY math_mark, student_id DESC
十七、在查询中使用常量和运算符
SELECT student_name +':'+ student_name+ '->' + student_name
FROM student_inf
注：select后的列与加入符号的数据类型必须匹配。
十八、as子句:为查询显示列起个列名
SELECT student_name +':'+ student_name+ '->' + student_name as name123
FROM student_inf
十九、top子句：返回数据行: top
1）限制返回数据行
SELECT Top 3 student_id,student_name From student_inf
2）限制返回行百分比
SELECT Top 80 Percent student_id,student_name From student_inf
二十、聚合函数
1） SUM：求和函数
SELECT SUM(math_mark) As Total
From student_mark
2）AVG：平均值函数
SELECT AVG (math_mark) As avg
From student_mark
3）COUNT：统计个数
SELECT COUNT (math_mark) As avg
From student_mark
4）max：最大值
SELECT max (math_mark) As avg
From student_mark
5）min: 最小值
SELECT min (math_mark) As avg
From student_mark
二十一、分组子句：group by …
SELECT count(math_mark) as [123] FROM student_mark GROUP BY math_mark
二十二、having子句：用于设置group by子句的条件
SELECT AVG(math_mark) as [123] FROM student_mark where student_id>3 GROUP BY math_mark having AVG(math_mark)>70
二十三、其他模糊查询
1） Like运算符（之前已讲过）
2） In运算符：in()
只返回和in后（）内的值相匹配的项
SELECT student_name,student_age FROM student_inf
WHERE student_id IN ('2', '4')
3）BETWEEN运算符：between … and …
返回在between … and …之间的数值项
SELECT student_name,student_age FROM student_inf
WHERE student_id between 1 and 4
4）IS NULL运算符：IS NULL
返回为空值的内容项
SELECT student_name,student_age FROM student_inf
WHERE student_name is null