共计 10248 个字符，预计需要花费 26 分钟才能阅读完成。
这篇文章主要介绍“SQL Server 数据库的基本操作语句总结”，在日常操作中，相信很多人在 SQL Server 数据库的基本操作语句总结问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”SQL Server 数据库的基本操作语句总结”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！
复制代码 代码如下:
–sql 基本操作
– 创建数据库
create database Studets
– 创建表
create table student (sno char(5), sname char(20), ssex char(2), sage smallint, sdept char(15) )
create table course (cno char(3), cname char(30), cpno char(3), ccredit smallint )
create table sc (sno char(5), cno char(3), grade int )
– 查看表信息
select * from student select sno as 学号 from student select * from course select * from sc
– 修改表
– 插入列
alter table student add scome datetime
– 修改列的字段类型 alter table student alter column scome char(50)
– 删除 – 删除列
alter table student drop column scome
– 删除表 drop table student drop table course drop table sc
– 完整性约束实现
–sno 非空唯一，ssex 检查约束，sage 默认大小
create table student (sno char(5) not null unique, sname char(20), sex char(2), sage smallint default 20, sdept char(15), constraint sex check(sex in( 男 , 女)), )
– 删除表的约束 alter table student drop constraint ssex
– 添加字段约束 alter table student add constraint ssex check(sex in( 男 , 女))
– 添加主键约束 alter table student add constraint PK_SNO primary key(sno) create table course (cno char(3) not null unique, cname char(30), cpno char(3), ccredit smallint )
– 关联表主键已经存在，可以如下操作添加主键和外键约束
alter table course add constraint PK_CNO primary key(cno), constraint FK_CPNO foreign key(cpno) REFERENCES sc(cno)
create table sc
(
sno char(5) foreign key references student(sno),
cno char(3) foreign key references course(cno),
grade int,
constraint PK_SC primary key(sno,cno)
)
ALTER TABLE [dbo].[sc] DROP CONSTRAINT [FK__sc__sno__0F975522]
ALTER TABLE [dbo].[sc] DROP CONSTRAINT [PK_SC]
ALTER TABLE [dbo].[sc] DROP CONSTRAINT [PK_SC]
– 创建 sc 后，通过如下修改主外键
alter table sc add constraint PK_SC primary key(sno,cno),
constraint FK_SNO foreign key(sno) references student(sno),
constraint FK_CNO foreign key(cno) references course(cno)
– 创建索引。
分为聚簇索引（clustered 物理顺序）和非聚簇索引（nonclustered 逻辑顺序，可多个）
复制代码 代码如下:
–not null 约束字段时候。会创建一个系统内置的约束键值，并且这种非空判断，通过索引查询实现 – 的，索引默认创建一个系统索引
create unique index STUsno
on student(sno)
create unique index COUcno
on course(cno)
create unique index SCno
on sc(sno asc,cno desc)
drop index SCno on sc
– 显示表的数据和索引的碎块信息 DBCC SHOWCONTIG
– 插入数据 select * from student
alter table student alter column sno char(10)
insert into student values(10021 , 张三 , 男 ,20, 计科系)
insert into student values(10022 , 王朝 , 女 ,18, 软件)
insert into student values(10023 , 朱元璋 , 男 ,20, 管理)
insert into student values(10024 , 刘彻 , 男 ,18, 军事)
insert into student values(10025 , 刘表 , 男 ,20, 商学系)
insert into student values(10026 , 白居易 , 男 ,19, 文法)
insert into student values(10027 , 李清照 , 女 ,24, 文法)
select * from course insert into course values(001 , 数据库 , 005 ,4)
insert into course values(002 , 高等数学 , ,2)
insert into course values(003 , 信息系统 , 001 ,4)
insert into course values(004 , 操作系统 , 006 ,2)
insert into course values(005 , 数据结构 , 007 ,3)
insert into course values(006 , 数据处理 , ,2)
insert into course values(007 , C 语言 , 006 ,5)
select * from sc insert into sc values(10021 , 002 ,100)
insert into sc values(10021 , 001 ,88)
insert into sc values(10021 , 006 ,100)
insert into sc values(10021 , 007 ,68)
insert into sc values(10022 , 002 ,100)
insert into sc values(10023 , 005 ,30)
insert into sc values(10024 , 002 ,100)
insert into sc values(10024 , 006 ,56)
select * from student – 查询操作
– 查询 select * from student select * from course select * from sc
– 去掉重复行 select distinct sno from sc
– 格式化查询
select sname as 姓名 ,2013-sage as 出生日期 from student
select sname, 出生日期 ,2013-sage from student
select 姓名 =sname, 出生日期 =2013-sage from student
– 条件查询
select * from course where ccredit 3
select * from course where ccredit between 2 and 5
select * from course where ccredit 2 and ccredit 5
select * from course where ccredit in(2)
select * from course where ccredit not in(2)
– 匹配查询
select * from student where sname like 刘__
select * from student where sname like _表__
select * from student where sname like % 表 %
– 算术元算查询
select grade*(1+0.2) as 总成绩,grade/(10) as 绩点 from sc
– 分组函数查询
select COUNT(*) as 总人数 from student
select COUNT(distinct sno) as 选修的总人数 from sc
select AVG(grade) as 平均成绩 from sc where sno= 10021
select MAX(grade) as MAX 成绩 from sc where sno= 10021
select MIN(grade) as MIN 成绩 from sc where sno= 10021
select SUM(grade) as 总成绩 from sc where sno= 10021
select SUM(grade)/COUNT(grade) as 平均成绩 from sc where sno= 10021
select SUM(grade) as 总成绩 from sc group by sno having sum(grade) 100 –
- 连接查询、
– 等值连接
select distinct student.*,sc.* from student,sc where student.sno=sc.sno
– 自身连接
select distinct A.*,B.* from student A,sc B where A.sno=B.sno
select B.sname as 同一个系 from student A,student B where A.sname= 白居易 and A.sdept=B.sdept
– 外连接
select A.*,B.* from student A left join sc B on A.sno=B.sno
select A.*,B.* from student A right join sc B on A.sno=B.sno
select A.*,B.* from student A FULL join sc B on A.sno=B.sno
– 复合条件连接
select * from sc select * from course
select distinct A.*,B.* from student A,sc B where A.sno=B.sno and B.grade 99 and B.cno= 002
select distinct A.*,B.*,C.* from student A,sc B,course C where A.sno=B.sno and B.cno=C.cno and B.grade 99 and B.cno= 002
– 字符串连接查询
select sname+sno from student
select distinct sname from student ,sc where student.sno=sc.sno
select sname from student ,sc where student.sno=sc.sno and student.sno not in (select sno from sc where grade 60) group by sname
– 子查询
select * from student where sage (select AVG(sage) from student)
– 是否存在的查询
select * from student where exists(select * from sc where sno=student.sno)
select * from student where not exists(select * from sc where sno=student.sno)
–sql 创建用户 sys.sp_addlogin bnc,bnc,Studets sp_adduser bnc,bnc
– 权限分配和收回
grant select on student to bnc
select * from student
revoke select on student from bnc
– 视图的创建
create view VIEW_STUGrade(学号, 姓名, 课程, 成绩)
as
select student.sno,student.sname,course.cname,sc.grade from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and student.sdept= 软件
– 查看视图
select * from VIEW_STUGrade
– 视图修改
alter view VIEW_STUGrade(学号, 姓名, 课程, 成绩)
as
select student.sno,student.sname,course.cname,sc.grade from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and student.sdept= 软件
with check option
– 更新失败后不影响视图查看
– 视图更新
update VIEW_STUGrade set 姓名 = 王超 where 学号 = 10022 select * from student where sno= 10022
/* 1, 可更新视图：a, 单个基本表导出的 2，不可更新视图 a 两个以上基本表导出的 b 视图字段来自表达式或者函数 c 嵌套查询的表 d 分组子句使用 distinct */
– 删除视图 drop view VIEW_STUGrade
– 高级 sql 编程
– 数据类型 1,int 2,smallint 3,tinyint (0–255) 4,bigint 5char 固定长度 800. 如：学号，姓名 6，varchar 可变长度小于 800 7,text 2GB 8，nvarchar1–4000 */
– 运算符和通配符
select GETDATE()-1 昨天,GETDATE() 今天,GETDATE()+1 明天
select 59 12
select 59|12
select 59^12
– 模糊查询
select * from student where sname like % 刘 %
select * from student where sno like 1002[5-9]
– 控制流程语句
declare @name char(10) set @name= 司马相如
print @name
– 输出一个表达式，不能进行查询 select @name
– 输出多个表达式
declare @a nvarchar(50),@b nvarchar(50)
set @a=33 set @b=34 — 简写 select@a=33，@b=34
if @a @b
print 最小值是：+@a
else
print 最大值是：+@b
–waitfor 间隔一段时间执行
waitfor delay 00:00:04 print 推迟 4 秒执行
waitfor time 17:45:50 print 等待这一时刻执行
– 创建函数
CREATE FUNCTION GetTime (@date1 datetime, @date2 datetime)
RETURNS TABLE
AS RETURN (
select datediff(dd,@date1,@date2) 日差,datediff(mm,@date1,@date2) 月差, datediff(yy,@date1,@date2) 年差
)
– 创建存储过程，
– 查看
GO create proc [dbo].[sel] (
@sno char(10)
)
as
select * from student where sno=@sno
exec sel @sno= 10021
– 查看
GO create proc sel2
as
select * from student
exec sel2
– 修改
GO create proc updat @sno char(10), @sex char(2)
as
update student set sex=@sex where sno=@sno
select * from student exec updat @sno= 10021 , @sex= 女
– 删除
GO create proc dele @sno char(10)
as
delete student where sno=@sno
select * from student
exec dele @sno= 10029
– 插入
GO create proc inser @sno char(10), @sname char(20), @sex char(2), @sage smallint, @sdept char(15)
as
insert into student values(@sno,@sname,@sex,@sage,@sdept)
exec inser @sno= 10029 , @sname= tom , @sex= 男 , @sage=100, @sdept= sc select * from student
– 查询操作
– 查询
select * from student select * from course select * from sc
– 去掉重复行 select distinct sno from sc
– 格式化查询
select sname as 姓名 ,2013-sage as 出生日期 from student
select sname, 出生日期 ,2013-sage from student
select 姓名 =sname, 出生日期 =2013-sage from student
– 条件查询
select * from course where ccredit 3
select * from course where ccredit between 2 and 5
select * from course where ccredit 2 and ccredit 5
select * from course where ccredit in(2)
select * from course where ccredit not in(2)
– 匹配查询
select * from student where sname like 刘__
select * from student where sname like _表__
select * from student where sname like % 表 %
– 算术元算查询
select grade*(1+0.2) as 总成绩,grade/(10) as 绩点 from sc
– 分组函数查询
select COUNT(*) as 总人数 from student
select COUNT(distinct sno) as 选修的总人数 from sc select AVG(grade) as 平均成绩 from sc where sno= 10021
select MAX(grade) as MAX 成绩 from sc where sno= 10021
select MIN(grade) as MIN 成绩 from sc where sno= 10021
select SUM(grade) as 总成绩 from sc where sno= 10021
select SUM(grade)/COUNT(grade) as 平均成绩 from sc where sno= 10021
select SUM(grade) as 总成绩 from sc group by sno having sum(grade) 100
– 连接查询、– 等值连接
select distinct student.*,sc.* from student,sc where student.sno=sc.sno
– 自身连接
select distinct A.*,B.* from student A,sc B where A.sno=B.sno select B.sname as 同一个系 from student A,student B where A.sname= 白居易 and A.sdept=B.sdept
– 外连接
select A.*,B.* from student A left join sc B on A.sno=B.sno select A.*,B.* from student A right join sc B on A.sno=B.sno
select A.*,B.* from student A FULL join sc B on A.sno=B.sno
- 复合条件连接
select distinct A.*,B.* from student A,sc B where A.sno=B.sno and B.grade 99 and B.cno= 002
select distinct A.*,B.*,C.* from student A,sc B,course C where A.sno=B.sno and B.cno=C.cno and B.grade 99 and B.cno= 002
– 字符串连接查询
select sname+sno from student
select distinct sname from student ,sc where student.sno=sc.sno
select sname from student ,sc where student.sno=sc.sno and student.sno not in (select sno from sc where grade 60) group by sname
– 子查询
select * from student where sage (select AVG(sage) from student)
– 是否存在的查询
select * from student where exists(select * from sc where sno=student.sno)
select * from student where not exists(select * from sc where sno=student.sno)
–sql 创建用户
sys.sp_addlogin bnc,bnc,Studets sp_adduser bnc,bnc
– 权限分配和收回
grant select on student to bnc
select * from student
revoke select on student from bnc
– 视图的创建
create view VIEW_STUGrade(学号, 姓名, 课程, 成绩)
as
select student.sno,student.sname,course.cname,sc.grade from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and student.sdept= 软件
– 查看视图
select * from VIEW_STUGrade
– 视图修改
alter view VIEW_STUGrade(学号, 姓名, 课程, 成绩) as select student.sno,student.sname,course.cname,sc.grade from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and student.sdept= 软件
with check option
– 更新失败后不影响视图查看 – 视图更新
update VIEW_STUGrade set 姓名 = 王超 where 学号 = 10022 select * from student where sno= 10022
/* 1, 可更新视图：a, 单个基本表导出的 2，不可更新视图 a 两个以上基本表导出的 b 视图字段来自表达式或者函数 c 嵌套查询的表 d 分组子句使用 distinct */
– 删除视图 drop view VIEW_STUGrade
– 触发器
use Studets
GO create trigger insert_Tri
ON student after
insert as print 有新数据插入！
GO create trigger update_Tri
on student after
update as print 有数据更新！
GO create trigger delete_Tri
on student after
delete as print 有数据删除！
– 修改触发器
GO alter trigger delete_Tri
on student after delete
as
if 王帅 in (select sname from deleted)
print 该信息不许删除！
rollback transaction
– 执行存储过程查看触发器使用情况
exec sel @sno= 10021
exec inser @sno= 10029 , @sname= 王帅 , @sex= 男 , @sage=25, @sdept= 国贸
exec updat @sno= 10029 , @sex= 女
exec dele @sno= 10029
– 查看, 修改, 删除触发器
/* sp_*+ 触发器名称
sp_helptext：触发器正文信息 sp_help: 查看一般信息，触发器名称，属性，创建时间，类型 sp_depends：引用或指定表的所有触发器 sp_helptrigger：指定信息 */ sp_help delete_Tri
sp_helptext delete_Tri
sp_depends delete_Tri
sp_helptrigger student
– 删除触发器
drop trigger delete_Tri
到此，关于“SQL Server 数据库的基本操作语句总结”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！
