数据库操作集合
数据库的创建一定要在master数据库当中
use master
go
--判断在数据库中是否含有该数据库
--如果有，则删除
if exists(select * from sysdatabases where name=‘stuDB‘)
drop database stuDB
go
--创建数据库
create database stuDB
on
(
name=‘stuDB_mdb‘,--逻辑名称
filename=‘e:\DB\stuDB_mdb.mdf‘,--文件物理路径
--size=5,--初始大小，可以不写，默认为1MB
maxsize=10,--增长最大限制，不写为无限
filegrowth=1--增长率，可以是数据，可以是百分比
),
(
name=‘stuDB_ndb‘,
filename=‘e:\DB\stuDB_ndb.ndf‘
)
log on
(
name=‘stuDB_ldb‘,
filename=‘e:\DB\stuDB_ldb.ldf‘,
size=5,
maxsize=10,
filegrowth=1
)
go
/*
--添加数据文件
--alter database stuDB add file
--(
-- name=‘stuDB_ndb2‘,
-- filename=‘e:\db\stuDB_ndb2.ndf‘
--)
go
--修改数据库名称
--alter database stuDB_T modify name=stuDB
go
--删除数据库文件
--alter database stuDB remove file stuDB_ndb2
--go
----修改数据文件的参数
--alter database stuDB modify file
--(
-- name=‘stuDB_ndb‘,
-- --size=5,--初始大小，可以不写，默认为3MB
-- maxsize=10,--增长最大限制，不写为无限
-- filegrowth=50%--增长率，可以是数据，可以是百分比
--)*/
go
/*
--exec sp_detach_db ‘stuDB‘--分离数据库
go
--附加数据库
--exec sp_attach_db ‘stuDB‘,‘e:\DB2\stuDB_mdb.mdf‘
-- ,‘e:\DB2\stuDB_ndb.ndf‘
-- ,‘e:\DB2\stuDB_ldb.ldf‘
*/
go
--对数据库里面的数据进行操作
use stuDB
go
if exists(select * from sysobjects where name=‘stuInfo‘)
drop table stuInfo
go
create table stuInfo
(
stuNo int identity(1,1) primary key,--学号
stuName varchar(10) not null,--姓名
stuAge int not null,--年龄
stuSex varchar(2) not null,--性别
stuId char(18) not null,--身份证号码
stuTel char(11),--电话号码
stuAddress varchar(50) default(‘地址不详‘),--家庭地址
stuGroup int --学习小组组长id
)
go
--成绩表
if exists(select * from sysobjects where name=‘stuMarks‘)
drop table stuMarks
go
create table stuMarks
(
Mid int identity(1,1) primary key,
stuNo int foreign key references stuInfo(stuNo),
LabExam int,
WritterExam int
)
go
--添加约束
--主键约束
--alter table stuInfo
-- add constraint PK_stuNo primary key(stuNo)
--唯一约束
--alter table stuInfo
-- add constraint UQ_stuId unique(stuId)
--检查约束
--alter table stuInfo
-- add constraint CK_stuAge check(stuAge>=15 and stuAge<=30)
--默认约束
alter table stuInfo
add constraint DF_stuSex default(‘男‘)for stuSex
--外键约束
--alter table stuMarks
-- add constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)
go
--删除约束
--alter table stuInfo
-- drop constraint DF__stuInfo__stuAddr__0519C6AF
go
--select * from stuInfo
insert into stuInfo (stuName,stuAge,stuSex,stuId,stuTel,stuAddress,stuGroup)
values(‘罗*‘,18,‘男‘,‘430981111111111111‘,‘15999999999‘,‘广东湛江‘,1)
insert into stuInfo values(‘赖*‘,19,‘男‘,‘430981111111111112‘,‘15999999998‘,‘江西赣州‘,1)
--批量插入，可以查询表格中的数据
insert into stuInfo
select ‘向*‘,17,‘男‘,‘430981111111111113‘,‘15999999997‘,‘四川成都‘,1
insert into stuInfo
select ‘陈*‘,21,‘女‘,‘430981111111111114‘,‘15999999995‘,‘湖南常德‘,4
insert into stuInfo
select ‘虞*‘,18,‘男‘,‘430981111111111115‘,‘15999999996‘,‘江西上饶‘,4
insert into stuInfo
select ‘蓝*‘,19,‘男‘,‘430981111111111116‘,‘15999999998‘,‘广东阳江‘,4
insert into stuInfo
select ‘向*‘,17,‘男‘,‘430981111111111117‘,‘15999999997‘,‘四川成都‘,1
go
insert into stuMarks values(1,80,68)
insert into stuMarks values(2,50,90)
insert into stuMarks values(3,70,40)
insert into stuMarks values(4,80,80)
insert into stuMarks values(5,70,80)
go
--查询并将数据插入一个新表，该表结构与原表一样，
--请注意，是重新创建一个新表
select * into stuInfoBack from stuInfo
go
select * from stuinfoBack
go
insert into stuinfoBack
select stuName,stuAge,stuSex,stuId,stuTel,stuAddress,stuGroup from stuInfo
go
--修改数据
--upate
update stuInfo set stuSex=‘男‘,stuAge=15 where stuNo=1
go
--查询
select * from stuInfo
--查询前3条记录
select top 5 * from stuInfo
--查询百分比的记录
select top 5 percent * from stuInfo
--
select stuName+‘_‘+stuSex+‘_‘+stuAddress as new from stuInfo
--
select* from stuInfo order by stuAge,stuId desc
--分组统计
select stuGroup, COUNT(1) num
from stuInfo group by stuGroup
having COUNT(1)>=3--having用作分组统计后的数据筛选
--
go
--联合查询
select * from stuInfo
select * from stuMarks
go
--最基本的联合查询
select I.stuName,stuSex,LabExam,WritterExam
from stuInfo I,stuMarks M
where I.stuNo=M.stuNo
and M.LabExam>=60 and M.WritterExam>=60
go
--查询所有参加了考试的同学的信息
select I.stuName,stuSex,LabExam,WritterExam
from stuInfo I inner join stuMarks M
on I.stuNo=M.stuNo
where M.LabExam>=60 and M.WritterExam>=60
go
--查询所有同学的信息，并且加上该同学的考试成绩
select I.stuName,stuSex,LabExam,WritterExam
from stuMarks M right join stuInfo I
on I.stuNo=M.stuNo
--全外连接
select I.stuName,stuSex,LabExam,WritterExam
from stuMarks M full outer join stuInfo I
on I.stuNo=M.stuNo
--交叉连接
--数据行为两 表行数的乘积
select I.stuName,stuSex,LabExam,WritterExam
from stuMarks M cross join stuInfo I
--自连接
--查询学生的信息，以及该学生所在学习小组的组长名字
select I1.stuName,I1.stuSex,I2.stuName,I2.stuSex
from stuInfo I1,stuInfo I2
where I1.stuGroup=I2.stuNo
go
--联合查询，两表的字段类型必须一致
select * from stuInfo
union all
select * from stuInfoBack
go
select *
--,(select LabExam from stuMarks M where M.stuNo=I.stuNo) lab
from stuInfo I where stuNo not in (
select stuNo from stuMarks
where LabExam>=60 and WritterExam>=60
)
go
select *
--,(select LabExam from stuMarks M where M.stuNo=I.stuNo) lab
from stuInfo I where exists (
select * from stuMarks M where M.stuNo=I.stuNo
)
go
--any,some,all
--select * from stuInfo where stuAge>any(15,18,19)
go
--第五章 T-SQL编程
--声明局部变量
declare @name varchar(10)
declare @age int
--set赋值用于普通的变量赋值
set @name=‘华*‘
set @age=20*2
--select用于查询表中的数据
select @name=‘麦*‘
select @name=stuName from stuInfo --where stuNo=1
print @name
go
--查找相关岁数的同学的信息
declare @name varchar(10)
declare @age int
set @name=‘虞*‘
select @age=stuAge from stuInfo
where stuName=@name
print @age
select * from stuInfo where
stuAge=@age+1 or stuAge=@age-1
go
--全局变量（系统变量@@）
--@@ERROR
select @@ERROR
--错误产生后的值
print ‘aa‘+@age
--@@identity
select @@identity
go
--IF...ElSE...
declare @age int
select @age=stuAge from stuInfo
where stuName=‘虞*‘
if (@age>20)
--begin
print ‘你成熟了‘
--end
else
begin
print ‘屁大点‘
end
go
--if..else..的用法
declare @avg int
select @avg=avg(WritterExam) from stuMarks
if(@avg>70)
begin
print ‘全校成绩优秀‘
select top 3* from stuMarks order by WritterExam desc
end
else
begin
print ‘全校成绩较差‘
select top 3* from stuMarks order by WritterExam asc
end
go
--while
declare @len int
set @len=1
while(@len<=10)
begin
print @len
set @len=@len+1
if(@len=5)
break
end
go
--while实例
declare @count int
while (1=1)
begin
select @count=COUNT(1) from stuMarks where WritterExam<60
if(@count>0)
update stuMarks set WritterExam=WritterExam+2-- where WritterExam<60
else
break
end
select * from stuMarks
/*
select * from stuInfo I
left join stuMarks M on I.stuNo=M.stuNo*/
go
/*case...when...then...else*/
--第一种用法，用于判断范围内的不确定值
select stuNo,LabExam,
case
when LabExam<60 then ‘不及格‘
when LabExam<70 then ‘及格‘
when LabExam<80 then ‘一般‘
when LabExam<90 then ‘良好‘
else ‘优秀‘
end Level
from stuMarks
--用于判断固定值
select stuNo,LabExam,
case LabExam
when 50 then ‘不及格‘
when 70 then ‘及格‘
when 80 then ‘一般‘
when 90 then ‘良好‘
else ‘优秀‘
end Level
from stuMarks
--
update stuMarks set WritterExam=WritterExam+
case when WritterExam<100 then 2
else 0
end
go
--waitfor delay time
waitfor time ‘10:17:20‘
select * from stuInfo
go
--raiserror 错误显示
raiserror(‘错了%d‘,18,1,50) with log
--添加错误信息到系统表中
sp_addmessage 50001,18,‘错了‘,‘us_english‘
--如何调用已有的错误信息
raiserror(50001,19,1) with log
go
select ABS(20),--绝对值
ascii(‘A‘),--求ASCII码
RAND(),--0-1的随机数,一旦放入整型参数，则随机值只初始化一次，所有的值相等
PI(),
ROUND(50.688,1)
--字符函数
select
stuName,LEN(stuName),--求得字符串的长度，以字符数计量
DATALENGTH(stuName),--求得字符串字节的长度，以字节为计量
LEFT(stuName,2),--取得左边开始的2个字符
RIGHT(stuName,3),--取得右边开始的3个字符
SUBSTRING(stuName,1,2),--截取字符串，从第1个字符开始，取2个字符，
--如果第一个参数为0，代表第1个字符行为表现前数一位
REPLICATE(stuName,2),--复制字符串，第二个参数为复制的个数
REPLACE(stuName,‘向‘,‘刘‘),--替换字符串，第二个参数为被替换的字符，第三个是替换以后的字符
CHAR(65),CHARINDEX(‘罗‘,stuName,1),--第一个参数是要找的字，第二个被查找的字符串值
PATINDEX(‘罗%‘,stuName)--可以用通配符
from stuInfo
go
--日期函数
select GETDATE(),DATEPART(WEEKDAY,GETDATE()),
DATEADD(DAY,-1,GETDATE())--日期的运算
go
--格式转换
select CONVERT(varchar(20), GETDATE(),101)
go
--系统函数
select USER,USER_ID(‘dbo‘),DB_ID(‘stuDB‘),OBJECT_ID(‘stuInfo‘)
--if OBJECT_ID(‘stuInfo‘)<>null
--drop table stuInfo
go
use master
go
select * from sysdatabases
go
--自定义函数
--标量
alter function fn_Test
(
@one int,@two int
)
returns int
as
begin
return @one+@two
end
go
alter function fn_Level(
@LabExam int,--参数1，@名字,数据类型
@WritterExam int
)
returns varchar(10)--返回值的数据类型
as
begin
declare @avg int
declare @level varchar(10)
set @avg=(@LabExam+@WritterExam)/2
select @level=
case
when @avg<60 then ‘不及格‘
when @avg<70 then ‘及格‘
when @avg<80 then ‘一般‘
when @avg<90 then ‘良好‘
else ‘优秀‘ end
return @level --最后必须要返回一个值
end
go
select * from stuMarks
--函数的使用可以结合数据的查询，并能将表格中的字段当成参数传递
select dbo.fn_Test(LabExam,WritterExam),
dbo.fn_Level(LabExam,WritterExam) level
from stuMarks m
go
--表值函数
create function fn_Table
(@age int)
returns table
as
return
select * from stuInfo where stuAge>@age
go
--表值函数的使用，作为一个数据源
select * from dbo.fn_Table(18)
go
--多语句表值函数的定义
create function fn_Table_2
(
@age int,@sex varchar(2)
)
--首先字义一个变量，数据类型为table
--在table中字义该临时表的数据字段
returns @table table(stuNo int,stuName varchar(10),stuAddress varchar(50))
as
begin
--将数据从表中取出插入到临时表中,数据字段的数据类型必须与临时表一致
insert into