Tsql语句大全.docx

1、Tsql语句大全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:学生（

2、自己起的数据库名称） primary:主要的 name:姓名 filename:文件路径 size:文件大小 filegrowth:文件增长 maxsize:文件最大存储容量 log：日志 2）修改已建好的数据库： 修改数据库建立时文件（主数据文件或日志文件）的大小，要更改文件最大值和文件增长速度，只需将size改为maxsize或filegrowth即可。 alter database student modify file ( name=student_log, size=4mb ) 注：alter：修改 modify：扩充 修改数据库，要增加数据库次要数据文件。 alter databa

3、se 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 s

4、tudent,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_addt

5、ype 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

6、:数据类型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 tabl

7、e 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, compu

8、ter_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:自己起的约束名称，方便于对约束进行删除 在修改表时删除主键约束：

9、 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

10、) 在修改表时删除唯一约束： 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） 引用完

11、整性 外键：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 stud

12、ent_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 unkn

13、ow, 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_

14、id int not null, student_age int not null check(student_age15 and student_age 15 and student_age 大于 = 大于等于 = 小于等于 = 等于 ! 非 不等于 not 逻辑非：否定条件 and 逻辑与：连接两个条件且仅当两个条件都为真时返回 TRUE or 逻辑或：连接两个条件，但只要其中任一个为真就返回 TRUE 使用方法：一般放于where子句里进行条件限制的筛选 例： a) select * from student_inf where student_id=1 b) select studen

15、t_name,student_sex,student_age from student_inf where student_age15 and student_age20 注：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 qw

16、e 注：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 注：在查询结果窗口中显示两张表中的

17、所有内容于一张联合表中。 （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 jo

18、in 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来

19、补充。 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, s

20、tudent_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_name FROM student_inf 注：select后的列与加入符号的数据类型必须匹配。 十八、as子句:为查询显示列起个列名 SELEC

21、T 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_

22、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 s

23、tudent_mark GROUP BY math_mark 二十二、having子句：用于设置group by子句的条件 SELECT AVG(math_mark) as 123 FROM student_mark where student_id3 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