数据库操作集合
时间：2021-07-01 10:21:17
帮助过：3人阅读
数据库的创建一定要在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
