SQL server语句大全

--创建数据库

@create database StudentDB

--创建表--

--ddl create -- create table Info (

--字段 --列，列和列之间用都好隔开

---snumb int primary key not null , ---学号 类型 （int）--主键约束 snumb int identity(1,1) primary key not null, ---唯一约束 ---varchar 代表可变长度字符 ---char 代表固定长度字符 sname varchar(8), --学生姓名

sex char(2) check (sex in ('男','女')), --性别 ----检查字段

birthday datetime check(birthday

address varchar(50) not null default'昆明市倘甸产业园区轿子雪山旅游开发区倘甸镇', --家庭住址

email varchar(20)check(email like '%@%.%'), -----检查字段 tel varchar(30),

ID int not null unique --联系电话 -----唯一值约束

---check (age>30 and birthday

---查看表 *代表所有列 ---- select * from Info

-----删除表

drop table Info

---创建books----- create table Books (

BNO varchar(16) primary key not null check(BNO like 'B%'), ---主键 书号 Bname varchar(100) not null, ----书名 Author varchar(50) not null, ----作者

Price money not null check(Price >0), ----单价

Quantity int not null check(Quantity>0) ----库存册数 )

------------------------------------------------- ----------------------------------引用完整性约束 -------------------------------------------------

---------主表 学生信息表 create table student (

SId int primary key, Sname varchar (8),

address varchar (20) default '昆明理工大学' )

----------从表（外键所在的表） 成绩表 create table score (

subject varchar (20),

sid int foreign key references student (SId), --与主表关联的字段必须数据类型一样，长度必须一致，名字可以不一样

score int check(score between 0 and 100) )

------------------------------------------------------------------------------

------------------------------------------------------------------------------

-------------------------------------------------------------------2013.10.16-

------------------------------------------------------------------------------

-------------------------通过代码选择数据库 use StudentDB go

create table Infom -----主表 (

id int not null,

name varchar (10) not null, age int not null

----------直接在表中添加约束(有名字) ----constraint pk_id primary key(id)

----------直接在表中添加约束（没有名字的约束，后续不好管理） ----primary key(id) )

------------------------------从表 create table scores (

subject varchar (20),-----科目

sid int , -----学号外键 score int , -----分数

scoreid varchar(10)--------查学分编号（唯一值约束） )

-------------------------修改表(结构)，如果表中的字段漏了，添加列 go

alter table Infom

add address varchar (10)---------------添加address字段

alter table Infom

add guidCol varchar(50) -------------添加字段

alter table infom

add score numeric(6,2) --------------精度6位,小数位数两位

-----------------------修改字段 column （字段） alter table Infom

alter column address varchar(50)

-----------------------删除列 alter table Infom drop column address

------------------------修改表（约束:constraint） alter table Infom

add constraint pk_id primary key(id) -----主键约束

------------------------联合主键约束 alter table info

add constraint pk_sno primary key(id,name)

alter table infom

add constraint ck_age check(age between 18 and 70) ----------check检查约束

alter table infom

add constraint df_address default '昆明理工大学' for address ---------默认值约束，df_address为约束名

alter table infom ------------------guid默认值约束

add constraint df_guidCol default (newid())for guidCol--------------全球唯一

标识

alter table scores

add constraint uq_scoreid unique (scoreid) ------唯一值约束 -----------------------------------------添加外键约束 alter table scores add constraint FK_scores_infom_sid foreign key (sid) references Infom(id ) -----外键约束

------------------另一种添加外键的方法，但是不利于后续的修改 --alter table scores

--add foreign key (sid) references Infom(id) -----外键约束

-----------------------------------------

-----------------------------------------删除约束 ----------------------------------------- alter table scores

drop constraint uq_scoreid --------约束名称

----------------------------------------删除表

---------------------------------------drop table 表名 drop table scores ------------------先删从表 drop table Infom ------------------再删主表 ---------------------------------------------- ---------------------------------添加数据 ---------------------------------添加所有列 ----------------方式一

insert into Infom(id,name,age,address)-----自动编号不用写,标识列为只读 values (1,'Mary',18,'england')-------------注意,日期要用单引号引起来, select * from Infom ----------------方式二

insert into Infom values (2,'Tim',19,'China',null) ----------------添加部分列

insert into Infom(id,name,address)------not null必须填 values (3,'陈成','中国')

-------------------------------插入多条数据到表中 insert into infom(id,name,age,address,guidCol) select 4,'Jack',20,'昆明理工大学 ',newid() union select 5,'Jeck',20,'昆明理工大学 ',newid() union select 6,'kack',22,'昆明理工大学 ',newid() select * from Infom

------------------------------特殊的数据添加操作-

------------------------------复制表查询(创建新表,把选中数据复制到新表中) select id,name,age,address into newtable from infom ---newtable新表的名称 select * from newtable

------------------------------复制表结构,不带数据 select ID,name,age,address into newtable2 from infom where 1=2---------------条件为假，选择0行数据

---------------------------复制所有满足where条件的数据到新的表中（age>20） select ID,name,age,address into newtable3 from infom where age>20

-------------------------------表已经存在,只想复制数据 insert into newtable2(id,name,age,address) select ID,name,age,address from infom

select * from newtable2

-----------------------------------

----------------------------------更新数据

----------------------------------把年龄>20的学生的名字改成“ttt” update infom

set name='ttt' ------------修改的内容写在set语句 where age>20 ------------符合条件的被修改

select * from infom

---------------------------------更新多个字段

---------------------------------把地址包含China的学生名字改为?xxx?，年龄改为?25?

update infom

set name='xxx' ,age=25 where address='%China%'

----------------------------------把所有学生的年龄都加5 ---------------------没有条件，代表所有行更新 update infom set age=age+5

----------------------------------把所有年满18岁，地址包含china的学生的名字该成“张三” update infom set name='张三'

where age >=18 and address ='%北京%'

------------删除所有学员 delete from infom

------------truncate 删除表中的数据 truncate table infom

---------------------------------查询

---------------------------------查询所有列 select * from Infom

----------------------------------查选择(部分)列显示 select age,name from Infom

----------------------------------where设置查询条件

----------------------------------查找年龄在19岁以上的学生 select name,age,address from Infom where age>=19 ---查询条件

----------------------------------查询地址第二个字是国字 select name,address from Infom

where address like '_国%' ------_代表占一个字符，%匹配任意字符

-------------------------------数据查询 基本查询

-------------------------------order by 排序 降序desc ，升序：asc 默认排序 select id, name,age,address,guidcol from infom

where address like '%昆明%'

order by age asc,score desc----多字段排序，先按第一个字段排序，如果字段相等再按第二个字段排

----------------------------成绩+年龄的平均值排序 select name,score,age from infom where age>20

order by (score+age)/2 desc

select name,score,age from infom where age>20

order by 3 desc -----------按第三个字段降序排序

-----------------------------------------------------------------修改表或者字段---

EXEC sp_rename 'student.年龄' , 'newname' 'column' ]

---------------------别名

---------------------1\\空格 别名

select name a姓名,address 地址 ---方法一 from infom

-------表 别名

select s.name ,s.address from infom s

--------------------2.as 别名

select name as 姓名,address as 地址 ---方法二 from infom

--------------------3.别名 = 列

select 姓名=name ,地址=address ---方法三 from infom

----------------------------------------------------------top关键字一般会跟排序混在一起用

--------------------top关键字 取结果集中的前几 --------------------top子句中必须是正数

select top 3 s.score,s.id, s.name ,s.address from infom s ------------成绩显示前三名

order by score desc

-------------------top 20 percent 表示取 20%，显示前百分比 select top 20 percent s.score,s.id, s.name ,s.address from infom s

order by score desc

----------------------------------------------------------distinct隐藏重复行,默认是all不隐藏重复行显示

select count(distinct name) from infom----------------------count 统计行数

----------------------------------------------------------where 条件的关键字

-- and \\or \\not \\like\\ botween and \\in(1,2,3)1或2或3，或者的关系 --<\\>\\<=\\>=\\!=、<>\\

----------------------------------------------------------条件通配符 ------%：任意字符，_ ：一个字符，

------[a-zA-Z] 字母, [0-9] 数字, [^1-3]不是123

------------------------------查询地址为null的行 select id,name,age,address from infom where address is null

------------------------------查询地址为 not null的行 select id,name,age,address from infom where address is not null

-----------------------------查询性别不是男 和 女 select id,name,sex,age,address from infom where sex not in('男','女')

-------------关键字like ，not like 模糊查询

---------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------

---------------------------分组查询和表连接

------------------------------------------------------------------ ---------------------------聚合函数(单行函

数)--------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------

select COUNT (ID)as 人数 from infom -----统计学生的总数

select COUNT (address) from infom ---------统计地址,统计具体字段时null 不会被统计

select COUNT (*) from infom ---------------count(*)会统计所有行,包括空值

--------注意!!---如果查询列表中既有聚合函数又有普通字段,那么普通字段必须是分组字段(在group by 字段 中)

select sum(score) as 总分, --------sum(字段) 求和 max(score) as 最高分, ---------max(字段) 最大值 min(score) as 最低分, ---------min(字段) 最小值 AVG(score) as 平均分 ---avg(字段) 平均值

--- AVG(score) as 平均分,name 错误的写法,name只能写在group by 语句中 from infom

---------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------

---------------------------group by

--------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------

---------------------按地址统计相同地址同学的总分

select SUM(score) as总分,AVG(score) as 平均分,address --------按地址分组,所以address能写在此行 from infom

group by address --------------分组,分组为了统计总分

having AVG(score)>80 --------------分组的筛选条件,没有出现group by 就不能出现having

----------------------所有70以上成绩,按地址分组,统计每组的总分\\平均分 select SUM (score) as 总分,AVG (score) as 平均分 ,address from infom

where score>70 ------结果集中数的筛选,在分组之前 group by address

select name,score from Info ----------------用子查询来实现最大值的输出 where score=(select MAX (score ) from Info)

--------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------

---------------------------简单方式实现交叉联接(两个表联接所有的数据都被现实)--------------------------

--------------------------------------------------------------------------------------------------------------- ----做笛卡尔积

select s.id,s.name,sc.id,sc.sname,sc.score from infom s ,score sc

------内连接 写法一

select s.id,s.name,sc.id,sc.sname,sc.score from infom s ,score sc inner join score sc on s.id= sc.id ------内连接的条件 ------on s.id <>sc.id --------是全集 - 交集 ------where sc.score>80

------内连接 方法二

select s.id,s.name,sc.id,sc.sname,sc.score from infom s ,score sc where s.id= sc.id

------

-------------------------------------------------------外连接 左连接 --------------左表数据完全显示，右表中相同的数据显示，不同数据null select Student.name,score.score

from Student left join score -----------------先写的为左表 on Student.id=score .id -----------------连接条件

-------------------------------------------------------外连接 右连接 --------------右表数据完全显示，左表中相同的数据显示，不同数据显示null select Student.name,score.score from Student right join score on Student.id=score .id

-------------------------------------------------------全连接 full join -------------------------------------------------------左、右表的数据完全显示，相同的数据显示一次

select Student.name,score.score from Student full join score on Student.id=score .id

-------------------------------------------------------交叉联接

------------------------------------------交叉联接得到的是两表联接所有的数据组合

------------------------------------------（A表的数据记录* B 表的数据记录） -------------------------------------------方式一 select Student.*,score.* from Student,score

-------------------------------------------方式二 select score .*,Student.* from Student cross join score

-----------------------------------------------------多表联接

--------------------------------------要求查出张三 C#的考试成绩，涉及student，score，subject三个表 ---------方式一:

select student.name,subject.sname ,score .score from Student inner join score

on student.id= score.id inner join subject on score.id=subject.id

where Student.name='张三' and subject.sname='C#'

---------方式二:等值联接

select student.name,subject.sname ,score .score

from Student,score ,subject

where StudentDB.id=score.id and score .id=subject.id and Student.name='张三' and subject.sname='C#'

------------------------------------------------------------------------------

-----------------------------------------------------------------子查询语句 -------------------------------------------------------------------------------

--------------------不知道李斯文的年龄,查询年龄大于李斯文年龄的人的信息 select stuname,stuno,stuage,stuseat,stuaddress from Student

where age>(select age from Student where stuname='李斯文')

---------------------在sql server 数据库中,在products 表中最贵的产品名称和价格

---------------------方法一

select ProductName,UnitPrice from Products

where UnitPrice=(select Max(UnitPrice) from Products) ---------------------方法二

select top 1 UnitPrice , ProductName from Products order by UnitPrice desc

---------------------在Products表中查询,查询所有价格高于平均价格的产品 ---------------------名称和价格,正确的SQL语句为(1条SQL) select ProductName,UnitPrice from Products

where UnitPrice>(select Avg(UnitPrice) from Products)

------------如果子查询充当查询条件\子查询返回的结果只能有一行 ------------如果有多行，可以使用in ，exists 来实现

------------------------------------------------------------------子查询充当表达式

select orderid,customerid,orderdate, (

select companyname from customers

where customers.CustomerID =orders.CustomerID )

from orders

--------------------案例:姓名,地址,_学生表的,,,成绩,科目_成绩表编号 select student.name ,student.address, from student

where Student.id in (

select id from score

)

----------------------子查询,不及格的学生姓名 ----------------------用in语句完成子查询 select student.name ,student.address from student

where Student.id in (

select id from score where score<60 )

------------------------没有参加考试的同学的名字,地址 select snumb, sname ,address from Info where Info.snumb not in (

select snumb from score )

-----------------------参加考试的同学的名字,地址,学号 select snumb, sname ,address from Info where Info.snumb in (

select snumb from score )

----------------------------------exists子查询

----------------------------------返回的是true 或者 false值

-----------------------查询info和score相等的数据(参加考试的学生) select sname from Info where not exists (

select * from score where Info.snumb=score.snumb )

---------------------------------查找系统中的数据库信息 use master go

----------mater库中系统视图 sysdatabases存储了服务器中所有的数据库的信息 select * from sysdatabases

---------------------------------如果数据库已经存在,那么就删了重新创建,如果不存在就新建

-----判断系统中是否有要创建的数据库 if exists (

select * from sysdatabases where name ='northwind1' )

------如果系统中存在该数据库则删除 drop database northwind1 -----创建数据库

create database northwind1

----------------------------------系统中判断库里是否已经存在表sysobjects use LibraryDB go

--------判断是否存在你要建的数据表 if exists (

select * from sysobjects where name ='student' )

------如果系统中存在该数据表则删除 drop table student -----创建数据表

create table student (

name varchar (20) primary key )

---------------------------------查询10-20之间的数据

select top 10 * from orders --------从11条开始的前10条数据 where orderid not in (

select top (10) orderid from orders ---------orderid 排除前10条数据 )

order by orderid asc

----------------------------查找20-30之间的数据

select top 10 * from orders --------要显示几行数据 where orderid not in (

select top (10*(3-1)) orderid from orders ---------排除前20行 )

order by orderid asc

-----------------------------10-15之间的数据

select top 5 * from orders -------要显示几行数据 where orderid not in (

select top (5*(3-1)) orderid from orders ---------排除前10行数据 )

order by orderid asc

--------------------------------取出25-30之间的数据

select top 5 orderid,customerid,orderdate,shipname from orders --------要显示几行数据

where orderid not in (

select top (5*(6-1)) orderid from orders ---------排除前行数据 not in )

order by orderid asc

-------------分页子查询语法：eg：查询10-15行的数据 --select top 每页行数 * from 表名 --where pk not in

--(select top (每页行数 * （页数-1）) pk from 表名) --order by pk asc ---排序

------------随机从表中抽取10条记录 select top 10 * from orders

order by NEWID() -----------------按guid

-----------T_sql编程 ---声明变量

declare @seatnum int----------声明变量

set @seatnum =8 -------------给变量赋值，方法一 select @seatnum =5------------给变量赋值，方法二

print '变量为：'+convert (varchar ,@seatnum )---------------输出变量,类型转换一

--print '变量为：'+cast (@seatnum as varchar)----------------输出变量，类型转换二

-----数据类型的转换

-- Cast （表达式 as 目标数据类型） -- Convert（目标数据类型 ， 表达式 ）

-----------给变量赋值，赋的是查出来的结果

---------语法：select @变量=值 from 表 where 条件 declare @num int

select @num = COUNT (snumb) from info

print '学生人数是：'+cast(@num as varchar)

--查询，已知学号3，获取学生姓名，赋给变量 declare @name varchar(8) ---声明变量

select @name =sname from INFO where snumb=3 ---给变量赋值

print '学生姓名是：'+@name -----输出

create table student (

stuname varchar(10), stuno varchar(10), stusex varchar(2), stuage int , stuseat int ,

stuaddress varchar(50) )

insert into student(stuname,stuno,stusex,stuage,stuseat,stuaddress) values ('张秋丽','s25301','男',18,1,'北京海淀')

insert into student(stuname,stuno,stusex,stuage,stuseat,stuaddress) values ('李文才','s25302','男',28,2,'地址不详')

insert into student(stuname,stuno,stusex,stuage,stuseat,stuaddress) values ('李斯文','s25303','女',22,3,'河南洛阳')

insert into student(stuname,stuno,stusex,stuage,stuseat,stuaddress) values ('欧阳俊雄','s25304','男',28,4,'新疆')

insert into student(stuname,stuno,stusex,stuage,stuseat,stuaddress) values ('梅超风','s25318','女',23,5,'地址不详')

-----查找李文才的左右同桌 ----先查找?李文才?的座位号 declare @seatnum int

select @seatnum =stuseat from student where stuname ='李文才' print '李文才de同桌'

select * from student where stuseat=@seatnum -1 or stuseat=@seatnum +1

------if else

-----案例：计算平均年龄，如果大于30，输出平均年龄偏大

---------输出年龄最大的两个学生，如果小于30，输出平均年龄正常 ---------输出平均年龄最小的两个同学 ---------1、声明变量

declare @avgage decimal(18,2) ---------2、赋值

select @avgage =AVG (stuage) from student ---------3、判断 if (@avgage >30) begin

print '平均年龄偏大' +cast(@avgage as varchar)

select top 2 * from student order by stuage desc-----年龄最大的两个人 end

else if (@avgage <30)

begin

print '平均年龄正常' +cast(@avgage as varchar )

select top 2 * from student order by stuage asc------年龄最小的两个人 end

------------统计平均分，>70,成绩优秀，显示前三名

-----------70分以下，本班成绩较差，显示后三名学生成绩 declare @avgscore decimal(10,2)

select @avgscore =AVG (score) from score if (@avgscore>70) begin

print '成绩优秀,平均成绩为：'+cast (@avgscore as varchar) select top 3 * from score order by score desc end

if (@avgscore<70) begin

print '本班成绩较差,平均成绩为：'+cast (@avgscore as varchar) select top 3 * from score order by score asc end

-----------while declare @i int =0 while (@i <5) begin

set @i=@i+1 --或者select----给变量赋值 print @i if (@i=3)

break ----退出循环

--continue -----结束本次循环继续下一次循环 end

---题目较难，提分确保都通过，提分原则，每次加两分，看是否全部通过，没有全部通过，再加两分，如此反复，知道所有人都通过 --1统计没有通过的人数 declare @num int

select @num =COUNT (snumb) from score where score <60 --2、有人没有通过，加2分 while (@num>0) begin

update score set score =score +2 ------加分 where score+2 <=100 ----限制分数在100内

select @num =COUNT (snumb) from score where score <60 end

print '加完分后的成绩：'

select * from score

----------------------------逻辑控制语句--case when then end select snumb ,成绩= case

when score between 60 and 70 then 'D' when score between 70 and 80 then 'C' when score between 80 and 90 then 'B' when score between 90 and 100 then 'A' ELSE 'E' END

from score

-----------------go 批处理语句

--------------全局变量

print 'SQL Server版本'+@@version print '服务器名称' +@@servername

--insert into student(stuname,stuno,stusex,stuage,stuseat,stuaddress) -- values ('tom','s25312','男',18,8,'地址不详')

-- print '当前错误号：'+cast (@@error as varchar(5)) ----如果大于0，表示上一条语句执行有错

insert into student(stuname,stuno,stusex,stuage,stuseat,stuaddress) values (25,25,1222,12,7,23)

print '当前错误号：'+cast (@@error as varchar(5)) go

--系统函数调用的练习

select newid() ----全球唯一标识 select GETDATE () -----系统时间 select ABS (-90) ----绝对值

select ROUND(3.1489,2)---四舍五入 select FLOOR (3.1415)----3 select FLOOR (3.9999)----3 select FLOOR (-3.9999)--- -4 select CEILING (3.1514)----4 select CEILING (3.9999)----4 select CEILING (-3.1514)---- -3

---字符串函数

select LEN('aaaaa') - --字符串长度 select LOWER ('AvdfASGFG')---小写 select upper ('AvdfASGFG')---大写

select RTRIM ( LTRIM (' aaaa ')) ---去左右空格 select SUBSTRING ('asfa123',2,3) ------取字符串

declare @index int

set @index=CHARINDEX('@','asdfgh@sinna.com') ---------获得某个字符的索引下标

select SUBSTRING ('asdfgh@sina.com',@index+1 ,10 ) -----取子字符串

-------------------日期函数 select GETDATE ()

select DATEPART(MONTH ,GETDATE ())---取当前日期中的月 select DATEPART(HOUR ,GETDATE ()) ---取当前日期中的小时 select DATEPART(YEAR ,GETDATE ()) ---取当前日期中的年 select DATEADD (day,100,getdate()) ----从当前日期到100天后 select DATEADD (day,-100,getdate()) ----从当前日期到100天之前

select DATEDIFF(day,getdate(),'2013-12-19') ----计算日期之间的差值 select DATEDIFF(dd ,'1990-12-17',getdate()) ---我的年龄

------将成绩进行反复加分 ------平均分超过85 分为止， ------90分以上：不加分 ------80-90加一分 ------70-79加2分 ------60-69加3分 ------69以下不加分 create database students use students

create table score (

examno varchar(10), stuno varchar(10), writtenexam int, labexam int )

insert into score(examno,stuno,writtenexam,labexam) values('s271811','s25303',96,58)

insert into score(examno,stuno,writtenexam,labexam)

values('s271813','s25302',66,90)

insert into score(examno,stuno,writtenexam,labexam) values('s271816','s25301',93,82)

insert into score(examno,stuno,writtenexam,labexam) values('s271818','s25328',61,65) select * from score

declare @labavg int while 1=1 begin

update score set labexam= case

when labexam<60 then labexam+5

when labexam>=60 and labexam<70 then labexam+3 when labexam>=70 and labexam<80 then labexam+2 when labexam>=80 and labexam<90 then labexam+1 else labexam end

select @labavg=AVG(labexam) from score if @labavg>=85 break end go

select * from score go

------------------------------------------------------------事物、视图、索引 ------------------------------------------银行转账业务 create table bank (

customerName char(10), currentMoney Money ) go

alter table bank

add constraint CK_currentMoney check (currentMoney>=1) go

--添加数据

insert into bank(customerName,currentMoney) values ('张三',1000)

insert into bank(customerName,currentMoney) values ('李四',1) select * from bank

update bank set currentMoney =currentMoney -1000 where customerName='张三'

update bank set currentMoney =currentMoney +1000 where customerName='李四' go

-----------------------------------------------显示事物模式，事物语句的写法

-----1、开始事物 begin transaction -----变量：记录错误 declare @errorsum int

set @errorsum =0 ----初始为0 -----两个update

update bank set currentMoney =currentMoney -1000 where customerName='张三'

set @errorsum =@errorsum +@@ERROR -----------记录错误号 update bank set currentMoney =currentMoney +1000 where customerName='李四'

set @errorsum =@errorsum +@@ERROR -----------记录错误号 -----判断变量 >0 有错 -----事物回滚 --- =0

---事物提交 ---查看数据 if(@errorsum >0) begin

print '转账失败，回滚事物'

rollback transaction------------回滚事物 end

else if(@errorsum =0) begin

print '转账成功，提交事物'

commit transaction -------------提交事物 end

select * from bank

----------------------------------------------------------事物模式的分类 set implicit_transactions on -----设置隐性事物模式

--事物自动开始，手动结束（rollback（回滚） 或者 commit（提交）） select * from bank

delete from bank where customerName='张三' select * from bank

rollback ----回滚（撤销） --commit ------提交

delete from bank where customerName='张三' commit

rollback --回滚是回滚到最后事物提交 select * from bank

set implicit_transactions off -----回到自动提交事物模式

----------------------------------------------------------- ----------同时操作时，上锁

delete from bank where customerName='张三' ----------用rollback 和commit 解锁 rollback

select * from bank

-------------------------------------------------------------索引 --------------------------------------------------------非聚集索引 -------------------------------------------------------最多可建249个 select * from student

create nonclustered index my_index

on student(stuname) -----------------要建在哪个表的哪个字段 ---------------------------------------------------删除索引 drop index student.my_index ------------------需要指定表名

-----------------------------------创建非聚集索引，有填充因子 create nonclustered index my_index on student(stuname) with fillfactor =40

-----------------------------------唯一值索引 create unique index my_index on student(stuno) with fillfactor =40

---------------------------------聚集索引 create clustered index my_index on student(stuno) with fillfactor =40

select * from sysindexes ------检索系统表中的索引

--------------------------------------------------------视图 view -------------------------------------------create view as select create view stu_sc_view

as select Info.snumb,Info.sname,Info.birthday,score.score from info,score

where info.snumb=score.snumb ---------使用视图查询

select * from stu_sc_view where score >=60

---------------------------------删除视图 drop view stu_sc_view

----------------------针对视图所做的修改不一定会成功，如果成功，修改的是基表中的数据

---------------------(能够追溯会原表中) ---------------------

update stu_sc_view set score =90 where sname='房祖名'

---------------------------------------------修改视图 alter view stu_sc_view as

select Info.snumb,Info.sname,score.score,Info.birthday,info.address,info.tel from info,score

where info.snumb=score.snumb

----------------------------------- 当视图中的字段不能追溯回原表的时候，就不能对视图进行增删改查，

------------------------------------如果要改数据，只能通过原表

insert into view_name--------------错误的，该视图不能进行增删改查操作 values ('ssss.aaaaa')

-----------------------------------------------------------------面试题 -- S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名

-- C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师

-- SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩

create database stu use stu go

create table S (

sno int ,

sname varchar(10)

)

create table C (

cno int ,

cname varchar(20), cteacher varchar(10) )

create table SC (

sno int , cno int, scgrade int )

insert into S(sno,sname) values (1,'张宇')

insert into S(sno,sname) values (2,'王王')

insert into S(sno,sname) values (3,'陈诚')

insert into S(sno,sname) values (4,'黄玉')

insert into C(cno,cname,cteacher) values (1,'C#','李明')

insert into C(cno,cname,cteacher) values (2,'C','李雨明')

insert into C(cno,cname,cteacher) values (3,'SQL','张三')

insert into C(cno,cname,cteacher) values (4,'C++','李无')

insert into SC(sno,cno,scgrade) values (1,1,45)

insert into SC(sno,cno,scgrade) values (2,2,80)

insert into SC(sno,cno,scgrade) values (3,1,56)

insert into SC(sno,cno,scgrade) values (4,3,56)

insert into SC(sno,cno,scgrade) values (1,4,55)

insert into SC(sno,cno,scgrade) values (2,3,55)

---找出没有选修过“李明”老师讲授课程的所有学生姓名

select sno, sname from S WHERE S.sno NOT IN (

select s.sno from S ,C ,SC

where S .sno=SC.sno and C.cno=SC.cno and C.cteacher ='李明' )

---2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩 select AVG(SC.scgrade) as 平均分,S.sname from S, sc, (

select sno from sc where sc.scgrade <60 group by SC .sno

having COUNT (SC.sno )>=2 ) as xx

where s.sno=SC .sno and xx.sno=SC .sno group by S .sname

---3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名 select sno ,sname from S,SC where SC .cno=1 and SC .cno=2

----用自连接来实现

---4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号 select sc1.*,sc2.* from sc sc1,sc sc2 where sc1 .cno=1 and sc2 .cno =2 and sc1 .sno =sc2 .sno

AND sc1.scgrade>sc2 .scgrade

------------------------------------------------------------------用户自定义函数

create function GetMessage(@name as varchar(20)) ---------创建函数 returns varchar(30) ------指定返回类型 as -------------------------------as 后面是函数体 begin --------------------------方法体 开始 declare @msg varchar(30)

set @msg= 'hell0,my name is '+@name return @msg end go

select dbo.GetMessage('陈瑞莲') ------------调用函数,要加上?dbo.函数名（参数）?

drop function dbo.GetMessage

sp_helptext 'GetMessage' ------------------ 查看数据库对象的原代码

------------------编写函数获取学生的平均分，并返回 create function GetAvg() returns int begin

declare @avger int

select @avger=avg(score) from score return @avger end go

DROP FUNCTION dbo.GetAvg ----------------删除自定义函数

--------------------------------------------------------------------存储过程

create proc getstudentInfo -----------存储过程的创建 as

select * from Info go

exec getstudentInfo ------------执行存储过程 方法一 getstudentInfo ------------执行存储过程 方法二

--------------------------------------------------------------------存储过程的参数

------------------------------------------------------------------1、in 类型参数（默认的参数类型）

create proc getstuBySex ----创建存储过程 @snumb int , ----参数一 @sex char(2) as

select sname,sex,birthday,address from INFO where snumb=@snumb and sex=@sex go

drop proc getstuBySex ----------------------删除执行过程

exec getstuBySex @snumb=2,@sex=0 ------------执行存储过程 方法一

exec getstuBySex @sex=0,@snumb=2 ------------执行存储过程 方法二 与顺序无关

exec getstuBySex 2,0 ------------执行存储过程 方法三 参数的顺序一定要对应,顺序不对就会报错

exec getstuBySex 0,2 ------错误的调用方法

------------------------------------------------------------带有默认值的参数

create proc getScore

@score int =60 ------------默认值 as

select * from score where score>@score go

------------------------------------------------------------执行带默认值参数的存储过程

exec getScore @score =80 ---------自己传入默认值 exec getScore ---------------------参数会使用默认值

----------------------------------------------------------练习存储过程 create proc getStusex

@sex char(2) , ----参数一

@snumb int=1 ---------带默认值的参数一般放在参数的最后 as

select sname,sex,birthday,address from INFO where snumb=@snumb and sex=@sex go

---------------------------------------------执行存储过程

--------------------------------------------建议按照参数名及顺序传参 exec getStusex 1 exec getStusex 1,2

exec getStusex 2,1 --错误的,顺序不对 exec getStusex @sex=0

exec getStusex @snumb=2,@sex=0

exec getStusex , @sex=0 ----错误的,中间有个逗号

-------------------------------------------------------插入海量数据的存储过程

create table dump (

id int identity(1,1), num int ,

name varchar(50) )

----------------------------------------循环插入数据的过程 create proc AddData @num int as

declare @i int --------声明局部变量 set @i =1

while(@i<=@num) begin

insert into dump(num,name ) values(@i ,NEWID ()) set @i =@i+1 end go

exec AddData @num=100000 select * from dump

-----------------------------SQL Server数据库 -存储过程返回值 ----------------------1、output类型参数可以返回值 create proc GetAvg

@Savg decimal(18,2) output --@Savg numeric(6,2) output as

select @Savg= AVG (score) from score go

-------------执行 输出参数，必须声明变量接收传出的参数值 declare @avg decimal(18,2) exec GetAvg @Savg=@avg output

---exec GetAvg @avg output ---简写方式 print @avg

--------------练习：加法，两个加数是in 类型参数，求和，把和返回， --------------和是output类型参数，调用过程输出和 create proc GetSum @numb1 int , @numb2 int , @sum int output as

select @sum= @numb1+@numb2 go

-----------执行存储过程 declare @sums int

exec GetSum @numb1=1,@numb2=1, @sum=@sums output --参数=值 print '和是:'+cast( @sums as varchar)

----------------------2、return语句返回值（SQL Server特殊特点） ---案列：求平均分，，并返回 create proc GetAvger as

declare @avg numeric(6,2)

select @avg =AVG (score ) from score return @avg -------可以通过rertun返回值 go

drop proc GetAvger -----执行存储过程

declare @avge numeric(6,2) exec @avge=GetAvger print @avge

----------return另外的作用,结束程序 create proc text as

print 'aaaaa' return

print 'bbbbb' go

exec text

------------------------------------------------查看系统存储过程 exec sp_help score -------查看对象信息 exec sp_helptext -----------查看原代码 exec sp_rename -----------改名

exec sp_stored_procedures -------查看存储过程

------------查年龄在40以上,返回名字,lastname.firstname||lastname.firstname create proc GetEmployee @age int as

select FirstName+'.'+LastName as 姓名 from Employees where DATEDIFF(YEAR ,BirthDate,getdate()) >@age go

drop proc GetEmployee

exec GetEmployee @age=40

-------------编写过程,计算班级的考试通过率(考试及格人数/ 总人数) create proc GetTexts as

declare @num int declare @count int

declare @ClassText numeric(6,2)

select @num =COUNT(snumb) from score where score>60-------考试通过人数 select @count=COUNT (snumb) from info ------------------总人数 set @ClassText =@num / @count

declare @p int --------------------声明该变量时为了解决00.00%的小数点后位数的问题

set @p =@ClassText*100

print '通过率为：'+cast(@p as varchar)+'%' go

----------执行存储过程 exec GetTexts

--------------------------------触发器的语法 create trigger trigger_name on table_name [with encryption]

for [delete,insert,update] as

T_SQL语句 go

create database bank

create table banks (

customername varchar(20),----账户名 cardid varchar(10),----账户

currentmoney money check(currentmoney>=1) )

create table transinfo (

transdate datetime,----交易时间 cardid varchar(10), ----交易账号 transtype varchar(10),----交易类型 transmonry money )

drop table transinfo

----账户表插入数据

insert into banks values('张三','1001',2000) insert into banks values('李二','1002',200) go

---------------------------------------------------------------------------------------insert触发器

-------------------------------------------------------------------------------------------触发器案例

---当向交易信息表（transinfo）中插入一条交易信息时，自动更新对应账户的余额 ---分析：插入触发器，transinfo表，触发器出发后要update账户表的余额 create trigger trigger_transinfo on transinfo ----触发器创建的表

for insert ---------插入触发器类型 as

------------------------------触发器的内容 ----------------------------1、提取有用数据 declare @cardid varchar(10) ----卡号

declare @transtype varchar(10) ---交易类型 declare @transmoney money ----交易金额

-----------------------------变量赋值---inserted表提供数据

select @cardid =cardid,@transtype=transtype,@transmoney=transmonry from inserted

----------------------------------2、修改余额 ----------------------------------判断交易类型 if(@transtype='存入')

update banks set currentmoney=currentmoney+@transmoney where cardid=@cardid

else if(@transtype='支取')

update banks set currentmoney=currentmoney-@transmoney where cardid=@cardid

print '交易后，账户余额为：'

select * from banks where cardid=@cardid go

------------------------------------测试出发器的功能

insert into transinfo values(GETDATE (),'1001','存入',10000) insert into transinfo values(GETDATE (),'1002','支取',100) insert into transinfo values(GETDATE (),'1002','存入',1000)

select * from transinfo select * from banks

------------------------------------------------------------------------------------------delete触发器

------------------------当删除交易信息表时,要求自动备份被删除的数据到表backup table

--------------------分析:交易信息表,出发类型为delete,触发的功能:把删除的数据添加到backup table中(已存在)

--------------------------表要先创建后添加数据

create trigger trig_del_transinfo on transinfo ----表

for delete ----删除触发器 as

----------判断backup table是否存在,不存在的就创建

if not exists(select * from sysobjects where name='backupTable') ----------创建表,并插入删除的数据 -----select into select * into backupTable from deleted else -----backupTable存在

insert into backupTable select * from deleted print '成功备份数据,备份数据为:' select * from backupTable go

select * from transinfo

delete from transinfo where cardid='1002'

----------------------------------------------------------------delete触发器

----------针对账户表禁止进行删除操作,用触发器实现此功能 create trigger trig_notdelete on banks for delete as

print '禁止删除数据'

rollback --------事物回滚,因为触发器是一个事物 go

-----------测试

delete from banks where cardid ='1001' select * from banks

-------------------------------------------------------------------------------update触发器

-----------跟踪用户的交易,交易金额超过20000元,则取消交易,并给出错误提示 --------分析:banks表上创建update触发器

-------------修改前的数据可以从deleted表中获取 -------------修改后的数据从inserted 表中获取 create trigger trig_Error on banks for update as

declare @beformoney money declare @aftermoney money

select @beformoney=currentmoney from deleted -----修改前的数据可以从deleted表中获取

select @aftermoney =currentmoney from inserted ---------修改后的数据从inserted 表中获取

if(ABS(@aftermoney-@beformoney) >20000) ----ABS()取绝对值 begin

print '交易金额'+cast(ABS(@aftermoney-@beformoney) as varchar) raiserror('每笔交易不能超过20000元',1,1)

----raiserror('显示错误信息',错误级别(0-18),错误状态)--------错误描述\\提示 rollback ----回滚,取消交易 end go

---------------------------删除触发器 drop trigger trig_Error

---------------------------------------------------触发器测试 select * from banks

update banks set currentmoney =currentmoney+40000 from banks where cardid ='1002'

update banks set currentmoney =currentmoney+200 from banks where cardid ='1002'

本文来源：https://www.bwwdw.com/article/3fj6.html