sql sever代码

数据库

创建销售数据库sales。文件的存储路径为D:\mydb。
主数据文件的逻辑名为saledat1，物理名为saledat1.mdf，最大容量20MB，增量5MB，次数据文件和事务日志文件的参数如图所示。
create database sales
on
(name=saledat1,filename=‘D:\sql sever\saledat1.mdf’,maxsize=20,filegrowth=5), NAME=逻辑名称，FILENAME={‘存储路径’}SIZE=初始大小 MAXSIZE={最大容量|UNLIMITED FILEGROWTH=增量【容量|%
(name=saledat2,filename=‘D:\sql sever\saledat2.ndf’)
log on
(name=salelog,filename=‘D:\sql sever\salelog.ldf’,size=10,filegrowth=10%)

创建销售数据库payroll
文件的存储路径为D:\mydb。
create database payroll
on
primary
(name=paydat1,filename=‘D:\sql sever\saledat3.mdf’),
filegroup paygp
(name=paydat2,filename=‘D:\sql sever\saledat4.ndf’)

（1）调用系统存储过程sp_helpdb查看数据库信息。
sp_helpdb payroll123;

（2）使用ALTER DATABASE命令修改数据库。向payrol数据库的paygp文件组添加次数据文件paydat4
alter database payroll123
add file
(name=paydat4,
filename=‘D:\sql sever\saledat5.ndf’,size=5
)
to filegroup paygp

删除sales数据库的次数据文件saledat2。
alter database sales remove file saledat2;

将sales数据库的主数据文件最大容量修改为100MB。
alter database sales
modify file
(name=paydat2,maxsize=100)

将payroll123数据库更名为pay123。
alter database payroll123 modify name=pay123

删除book2数据库。
drop database book2;

数据表

在book1数据库中创建student2表。
use book1
go
create table student2
(sno char(7) not null,
sname char(10),
sex char(2)
)

删除数据表
drop table book1

数据表中字段的操作

CREATE TABLE #savg
注意：如果在表名前添加“#”，如#savg，该表为临时表。
·“#”表示本地临时表
·“##”表示全局临时表，可以由所有用户使用。临时表在断开与数据库连接时，会被服务器删除。

在例的student表中增加tel字段。
alter table student add tel char(15)

删除studen表中birthday字段。
alter table student drop column birthday

将birthday列的数据类型改为smalldatetime。
alter table student
alter column birthday smalldatetime
go

将student表中sname列的长度为改为；
alter table student
alter column sname char(16)
go
注意：alter column是修改列的意思，使用ALTER COLUMN子句时，一次只能修改一个列的属性。
add不用column，加进来后都要开始加column了

约束

约束可分为种类型：
实体完整性
·主键约束（PRIMARY KEY）
·唯一约束（UNIQUE）
域完整性或
·检查约束（CHECK）
·默认约束（DEFAULT）
·非空约束（NOT NULL）
参照完整性
·外键约束（FOREIGN KEY）

实体完整性的实现：
·主键约束
·唯一约束
又称为行完整性，就是要求每个数据表都必须有主键，其值不能为空，且能唯一地标识对应的记录。

主键约束 1个 不能为空 聚集索引
唯一约束 多个 可以为空 非聚集索引

创建stinfo1，对sname字段定义唯一约束。
create table student4(sno char(10) not null,sname varchar(20) unique not null,sex char(2))

设置主键
alter table student4 add constraint add_str primary key(sno,sname)

设置外键：例创建教师表tinfo，要求tinfo表中所有的院系编号都出现在dinfo表中，假设已经在dinfo中创建dno为主键。
create table student1(
tid char(10),
tname varchar(20),
tsex char(2),
dno char(10)
foreign key(dno)references student(sex)

删除student4表中的唯一约束add_str;
alter table student4 drop constraint add_str;

列完整性，是对数据表中字段属性的约束，检验给定列输入的有效性，是用户自定义完整性的主要内容。
create table book(sno char(10)not null,sname char(20),sex char(2) default ‘男’)
域完整性的实现：
·数据类型
·非空约束
·默认约束
·检查约束

例创建stinfo2表，只考虑学号、性别、出生日期三列，性别只能包含“男”或“女”，出生日期必须大于年月日，并命名约束为ck_s_birthday。
create table student(
sno char(10) not null,
sex char(2) check(sex in (‘男’,‘女’)),
birthday date constraint h_s_birthday check(birthday>‘1998-01-01’))
check约束：check+（约束） //可加名字，也可以不加名字。加名字时是“约束 名字 check（）”让人知道这个名字是约束的就可以了

插入数据

插入记录
use book1
go
insert into student2 values(‘1’,‘李一’,‘男’)
或者
insert student2(sno,sname,sex)values(‘2’,‘赵二’,‘男’)

修改数据

修改记录：讲students表中学号为的名字改为上官二
update student2
set sname=‘上官二’
where sno=2

删数据

（3）删除记录可以使用DELETE语句或TRUNCATE TABLE语句来实现。
TRUNCATE TABLE table_name和delete table_name删除表中所有记录
删除students表中学号为的那一行记录
delete from student2
where sno=2

delete student2

truncate table student2

查询数据

查询student2表中所有字段信息。
SELECT * FROM student2
或者写成：
SELECT id,name,age FROM student2

查询student2表中id,name,age字段，分别以学号、姓名和年龄作为查询结果的列名。
select id as 学号,name as 姓名,age as 年龄 from student2

查询student2中的字段，且不重复
select distinct id from student2

查询结果集的前行
select top 5 id,name,age from student2

返回例查询结果前%行
select top 10 percent id,name,age from student2

使用表达式计算列值：按分计算成绩并显示SCInfo表中前行学生的成绩情况。
select top 5 id,name 成绩,age120=name*1.2 from student2;

条件查询

字符匹配运算LIKE
运算符
% address LIKE**’%公司%’**
address字段的字符串任意位置包含“公司”

_ employee_name LIKE**’_卫平’**
将查找以“卫平”结尾的共个字符的名字

[] employee name LIKE**’[张李王]卫平’**
将查找张卫平、李卫平、王卫平的名字

[^] employee_name LIKE**‘[^张李]卫平’**
将查找不姓张、李的名为卫平的名字

查询student表中姓张的学生信息。
select * from student2
where id like '张%'

在Stinfo表中查询学号倒数第个数字为，倒数第个数字在~4
select id from student2
where id like '%1_[1234]'

范围比较运算BETWEEN……AND
查询id从到的记录
select * from student2
where id between ‘20’ and '25’

空值比较运算
空值表示值未知。空值不同于空白或零值。
select * from student2
where id is null

并集，差集，交集

UNION并集运算
列出student2 表和student2_1表中id为‘5’的全部信息。
select * from student2 where id =‘5’
union
select * from student2_1 where id=‘5’

EXCEPT差集运算
select * from student2
except
select * from student2_1

INTERSECT交集运算
select * from student2
intersect
select * from student2_1

注意：使用UNION、EXCEPT或INTERSECT运算的基本规则：
·所有查询中的列数和列的顺序必须相同
·对应列的数据类型必须相同

连接查询

最简单的语句，从两个表中查询信息，条件是两表的id相同
select student2.id,student2_1.name
from student2,student2_1
where student2.id=student2_1.id
或者用
select x.id,y.name
from student2 x,student2_1 y //不一样的地方就是这里起了个别名，一样的
where x.id=y.id

内连接INNER
外连接OUTER
交叉连接CROSS

内连接
内连接的两个表的等值连接：两个表合并起来，筛选条件是两个表的id相同
select student2.age,student2_1.id
from student2 join student2_1
on student2.id=student2_1.id

三个表的等值连接，student2 和student2_1 两个表连接，且两个id要相同的显示。student2_1和student3 相连，而且两表的id要相同
select student2.age,student2_1.id
from student2 inner join student2_1
on student2.id=student2_1.id
join student3 on student2_1.id=student3.id

自然连接
是一种特殊的等值连接，要求连接字段必须是相同的属性，并且在结果中去掉重复的字段。
select student2.age,student2_1.id
from student2 join student2_1
on student2.id=student2_1.id

自连接
是指一个表自己与自己建立连接，是连接的特例
select a.age,b.id
from student2 a join student2 b //自己这个表分成了两个表，一个表叫a，另一个叫b
on a.id=b.id

外连接

（OUTER JOIN）

左外连接（LEFT OUTER JOIN）
右外连接（RIGHT OUTER JOIN）
全外连接（FULL OUTER JOIN）

左外连接（以左表为基准）
使用LEFT OUTER JOIN关键字进行连接。左外连接保留了第一个表（左表）的所有行，但只包含第二个表（右表）与第一个表匹配的行。第二个表相应的空行的字段置NULL值。
select a.age,b.id
from student2 a left outer join student2_1 b
on a.id=b.id

右外连接（以右表为基准）
select a.age,b.id
from student2 a right outer join student2_1 b
on a.id=b.id

全外连接（以两个表都是基准）
select a.age,b.id
from student2 a full outer join student2_1 b
on a.id=b.id

交叉连接
它返回连接表中所有数据行的笛卡尔积。
select a.age,b.id
from student2 a cross join student2_1 b

索引的创建

在TInfo表上为姓名建立唯一索引ixname
CREATE UNIQUE INDEX ixname
ON TInfo（TName）

例5删除stinfo表的ixstsex索引
DROP INDEX StInfo.ixstsex

自动应用索引查询已在stname列上建立索引ixstname
SELECT stname FROM StInfo
在T-SQL中指定索引查询例

在StSex列上建立了ixsex索引（降序），使用以下方式指定按某个索引查询。
SELECT stname，StSex，CIName FROM StInfo WITH（INDEX（ixsex））

聚合函数

AVG（<字段表达式>）|求一列数据的平均值
SUM（<字段表达式>）求一列数据的和
MIN（<字段表达式>）求列中的最小值
MAX（<字段表达式>）|求列中的最大值
COUNT（*|<字段名>）|统计查询的行数

查询所有学生所有课程的平均成绩。
select AVG(id) from student2

查询StInfo表的学生总数和所有学生的平均年龄。
分析：
年龄=今年的年份-出生年份
GETDATE（）函数：获取当前系统的日期时间
YEAR（日期）函数：计算日期数据的年份
计算平均年龄：AVG（YEAR（GETDATE（）-YEAR（BirthDate））

select COUNT(*)as 总人数,
AVG(YEAR(getdate())-YEAR(id))as 平均年龄
from student2

比较运算
查询Stlnfo表中年及以后出生的学生情况，要求列出学号、姓名、出生年份和所在班级。
select id,name,age from student2
where id>20

逻辑运算
查询SCInfo表中选课成绩分数大于等于分，且小于分的学生信息。
select * from student2
where id>20 and id<30

查询结果处理

排序输出
按班级升序列出student2表的学生信息，id>2再按id由小到大排序。
select * from student2
where id>2
order by id desc

重定向输出
查询student2 表中id>2的所有的学生信息，并将结果存入newstudent表中。
select * into student2_1 from student2
where id>2

分组与筛选
分别统计student2表中每门课程的课程编号和姓名，以id作为分组，如果是以性别作为分组，那男为一组，女的为一组。
select id,name
from student2
group by id

HAVING筛选条件
查询id>2的数据
select id,name
from student2
group by id
having id>2
点击查看更详细

嵌套查询

单值嵌套查询：子查询的返回结果是一个值的嵌套查询称为单值嵌套查询。
多值嵌套查询：子查询的返回结果是多个值的嵌套查询称为多值嵌套查询。

单值嵌套查询
查询选修“数据库技术与应用”课程的学生的学号和成绩。（因为student2表中没有课程名提供查询，只能通过其他表来间接查询）
select age,id
from student2
where id=(select id from student2_1 where name=‘数据库’)

多值嵌套
使用ANY运算符：子查询结果集中任意一个值满足比较条件就返回TURE，否则返回FALSE。

查询选修“9710031”课程的学生的成绩比选修“9710041”的学生的最低成绩高的学生的学号和成绩。（student2 中的成绩有比student2_1中任意一个大的都可以返回student2的值）
select age,id
from student2
where cno=‘9710031’ and score> any (select score from student2_1 where cno=‘9710041’)

使用ALL运算
子查询结果集中每个值都满足比较条件时返回TURE，否则返回FALSE。
在sems数据库中，查询选修“9710031”课程的学生的成绩比选修“9710041”的学生的最高成绩还要高的学生的学号和成绩。（要大于子表中全部的才能显示出来，那就是大于子表的最大值）
select age,id
from student2
where cno=‘9710031’ and score> all (select score from student2_1 where cno=‘9710041’)

使用IN运算符
IN是属于的意思，等价于“=ANY”，等于子查询中任何一个值即为TRUE。（符合条件的就可以显示）
查询选修“数据库技术与应用”或选修“C++程序设计基础”的学生学号和成绩。
select age,id
from student2
where cno in(select cno from student2_1 where cname=‘数据库技术与应用’ or cname=‘C++程序设计基础’)

使用EXISTS运算
如果子查询包含至少一行数据，那么值为TRUE（存在就可以显示）
列出所有成绩在90分以上的学生的姓名和班级名称。
SELECT StName, CIName
FROM StInfo
WHERE EXISTS(SELECT* FROM SCInfo WHERE Score >90 AND Stinfo. StID=SCInfo. StID)

视图

视图视图是从一个或者几个数据表中选取出来的数据组成的逻辑窗口，是一个虚拟表，并不包含任何的物理数据，视图中的数据存放在基本表中，SELECT 语句的结果集构成了视图的内容
注意：对视图数据的操作最终都会转化为对基本表的操作

例3：在Sems数据库中创建v视图，该视图选择学生信息表student2 中id='5’的信息。（查询一个表信息的视图）
create view v
as select * from student2 where id=‘5’

例4：创建vStu1视图，包括“法学1601"班学生的学号、姓名以及他们选修的课程号及成绩。（查询两个表信息的视图）
create view v1
as
select a.id,b.id
from student2 a join student2_1 b
on a.id=b.id
where a.id=‘5’

创建视图vstuavg，显示每个学生的平均成绩。
create view v1
as
select id as 学号,AVG(age) as 年龄
from student2
group by id

视图的查询

视图创建后，就可以像查询基本表那样对视图进行查询
select id,age
from vst //vst是已经创建好的视图
where id>10

视图的修改
例3：修改vstuo视图。将视图中选择学生信息表Stlnfo中的所有女学生修改为选择所有男学生，以后通过该视图的操作都是对男生的操作。
alter view v
as
select *
from student2
where stsex=‘男’
with check option //只能对性别为男的数据进行插入操作

视图的删除
删除视图后，基本表中的数据并不受影响。
drop view v

局部变量

DECLARE @sportavg int
DECLARE @name char(20),@birthday datetime

变量的赋值语句
DECLARE @sportavg int
DECLARE@name char（20），@birthday datetime
赋一个值：SET @Name=张强’
赋多个值：SELECT @sportavg =82，@birthday=‘1999-5-8’

创建一个名为@sportavg的局部变量，用于保存所查询出来的体育课的平均分。
DECLARE @sportavg int
SELECT @sportavg=avg（score） //自己定义的变量最大，其他是赋值给自己
FROM scinfo sc JOIN cinfo c ON sc.cno=c.cno
WHERE cname='体育’

变量的输出语句
SELECT @sportavg
PRINT @sportavg

全局变量

全局变量不需要用户声明，是服务器级定义的，作用范围是任何程序。
全局变量以@@开头，分为两大类，记录了系统的活动状态或系统内部信息。

控制语句

条件语句
–定义变量
DECLARE @x int，@y int
SET@x=1
SET@y=4
IF@x>@y
PRINT’x>y’
ELSE
PRINT’x<y’

条件分支语句
DECLARE @sportavg int
SELECT @sportavg=avg（score）
FROM scinfo sc JOIN cinfo c
ON sc.cno=C.cno
WHERE cname='体育’
IF @sportavg>=80
PRINT’成绩不错”
ELSE
PRINT’需要继续努力’

循环语句
DECLARE @num int
SET @num=80
WHILE @num<100
BEGIN
SELECT 平均分大于’+str（@num）+'学生：’
SELECT stid，AVG（score）FROM SCInfo
GROUP BY StID
HAVING AVG（score）>@num
SET@num=@num+10
END

分支语句
SELECT stid，cno，score= CASE score/10
WHEN 6 then’及格’
WHEN 7 then’中’
WHEN 8 then’良’
WHEN 9 then‘优’
WHEN 10 then‘优’
ELSE’不及格’
END
FROM scinfo

批处理

USE sems
GO
SELECT * FROM stinfo
SELECT * FROM scinfo
GO

RETURN语句
语法：RETURN【整型表达式】
无条件地从存储过程、批处理或语句块中退出，在RETURN之后的其他语句不会被执行。
存储过程返回0值表示成功，除非特别指明，返回非零值表示失败。

不带参数的存储过程的创建
use book1
go
CREATE PROCEDURE pStu
AS
SELECT Stname，StSex，Telephone
FROM StInfo
WHERE CIName=法学1603’

运行存储过程
exec pStu

带输入参数的存储过程的创建
CREATE PROCEDURE pStu @class varchar(30)
AS
SELECT Stname，StSex，Telephone
FROM StInfo
WHERE CIName=@class

exec pStu ‘材料科学1702’

带两个输入参数的存储过程的创建
CREATE PROCEDURE pStu @class varchar(30),@class1 varchar(30)
AS
SELECT Stname，StSex，Telephone
FROM StInfo
WHERE CIName=@class and classname=@class1

exec pStu ‘材料科学1702’,‘一班’
或者：exec pstu @class=‘材料科学1702’，@class1=‘一班’ //这个不用按顺序写

带输入输出参数的存储过程的创建
CREATE PROCEDURE pStu @stid varchar(30),@classname varchar(30) output //@stid是输入，@classname是输出
AS
SELECT @classname=clname
FROM StInfo
WHERE stinfo.stid=@stid
执行语句
declare @getclname char(20) //重新声明一个变量去调用
exec stclass ‘0603170109’,@getclname output
select @getclname

查看存储过程
查看存储过程的文本信息：sp_helptext 存储过程名
查看存储过程的相关性：sp_depends 存储过程名
查看存储过程的一般信息：sp_help 存储过程名

存储过程的修改
alter procedure student2_pro
as
select *
from student2

存储过程的重命名
pstu改为studentproc
sp_rename ‘pstu’,‘studentproc’

删除已创建的存储过程pstu
drop proc pstu

use book1
go
if exists(select name from sysobjects where name=‘pstu’)
drop procedure pstu
else
print ‘pstu存储过程不存在’
//sysobjects保存当前数据库的对象，如约束，默认值，日志，规则，存储过程等

触发器

开始事务：BEGIN TRANSACTION
提交事务：COMMIT TRANSACTION
回滚（撤销）事务：ROLLBACK TRANSACTION

触发器的类型
AFTER触发器（后触发器）:在引发触发器的语句成功完成之后，执行触发器。
如果操作语句因错误（如违反约束或语法错误）而失败，触发器将不会执行。

INSTEAD OF触发器（替代触发器）
执行触发器，而不是执行引发触发器的SQL语句，从而替代引发触发器的语句的操作。

按触发事件不同分为：
·DDL（数据定义语言）触发器是指当服务器或数据库中发生DDL事件时将启用，是对数据库对象进行操作的DDL语句（如 CREATE、ALTER或DROP）所激发·
DML（数据操纵语言）触发器是指在数据表中发生DML事件时将启用，即指在数据表或视图中修改数据的INSERT、UPDATE、DELETE语句激发

AFTER触发器的创建 //这里就是很有节奏的，mysql是翻译英语一样的
CREATE TRIGGER trstu
ON student2
AFTER INSERT,UPDATE
AS //这里用到as，mysql没有用到as
SELECT *FROM student2 //当插入或者更新表格的时候触发触发器，查询表格的内容

实现修改，触发触发器
UPDATE student2
SET stname='张三’
WHERE stname='李四‘

INSTEAD OF触发器的创建
CREATE TRIGGER tria
ON Stinfo
INSTEAD OF DELETE
AS
PRINT ‘hello,world’ //当删除表格的信息的时候，打印一行话出来hello,world

与触发器相关的临时表
例4在sems数据库中，为课程表CInfo建立一个名为DelCourse的触发器，其作用是当删除课程表中的记录时，同时删除SCInfo中与该课程编号相关的记录。
USE sems
GO
CREATE TRIGGER DelCourse
ON CInfo
FOR DELETE
AS
DELETE FROM SCInfo
WHERE CNo IN(SELECT CNo FROM deleted)

触发器的查看
查看数据表下各触发器的属性：sp_helptrigger 表名

触发器的修改
ALTER TRIGGER tria
ON StInfo
AFTER DELETE
AS
PRINT ‘hello,world’

触发器的删除
DROP TRIGGER tria,trib

create index…on
create view…as
create procedure as
create trigger as