SQL Server数据库的基本操作语句总结

这篇文章主要介绍“SQL Server数据库的基本操作语句总结”，在日常操作中，相信很多人在SQL Server数据库的基本操作语句总结问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”SQL Server数据库的基本操作语句总结”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！复制代码 代码如下:
–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 STUsnoon student(sno)create unique index COUcnoon course(cno)create unique index SCnoon sc(sno asc,cno desc)drop index SCno on sc–显示表的数据和索引的碎块信息 DBCC SHOWCONTIG–插入数据 select * from studentalter 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 studentselect sname,’出生日期’,2013-sage from studentselect 姓名=sname,出生日期=2013-sage from student–条件查询select * from course where ccredit>3select * from course where ccredit between 2 and 5select * from course where ccredit> 2 and ccredit
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 studentselect COUNT(distinct sno) as ‘选修的总人数’ from scselect 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.snoselect 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.snoselect A.*,B.* from student A right join sc B on A.sno=B.snoselect A.*,B.* from student A FULL join sc B on A.sno=B.sno–复合条件连接select * from sc select * from courseselect 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 studentselect distinct sname from student ,sc where student.sno=sc.snoselect sname from student ,sc where student.sno=sc.sno and student.sno not in (select sno from sc where grade
–子查询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 bncselect * from studentrevoke select on student from bnc–视图的创建create view VIEW_STUGrade(学号,姓名,课程,成绩)asselect student.sno,student.sname,course.cname,sc.grade from student,course,scwhere student.sno=sc.sno and course.cno=sc.cno and student.sdept=’软件’–查看视图select * from VIEW_STUGrade–视图修改alter view VIEW_STUGrade(学号,姓名,课程,成绩)asselect student.sno,student.sname,course.cname,sc.grade from student,course,scwhere 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固定长度
–运算符和通配符select GETDATE()-1 昨天,GETDATE() 今天,GETDATE()+1 明天select 59&12select 59|12select 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=34if @a>@bprint ‘最小值是：’+@aelseprint ‘最大值是：’+@b–waitfor间隔一段时间执行waitfor delay ’00:00:04’ print ‘推迟4秒执行’waitfor time ’17:45:50’ print ‘等待这一时刻执行’–创建函数CREATE FUNCTION GetTime ( @date1 datetime, @date2 datetime )RETURNS TABLEAS RETURN (select datediff(dd,@date1,@date2) 日差,datediff(mm,@date1,@date2) 月差, datediff(yy,@date1,@date2) 年差)–创建存储过程，–查看GO create proc [dbo].[sel] (@sno char(10))asselect * from student where sno=@snoexec sel @sno=’10021′–查看GO create proc sel2asselect * from studentexec sel2–修改GO create proc updat @sno char(10), @sex char(2)asupdate student set sex=@sex where sno=@snoselect * from student exec updat @sno=’10021′, @sex=’女’–删除GO create proc dele @sno char(10)asdelete student where sno=@snoselect * from studentexec dele @sno=’10029′–插入GO create proc inser @sno char(10), @sname char(20), @sex char(2), @sage smallint, @sdept char(15)asinsert 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 studentselect sname,’出生日期’,2013-sage from studentselect 姓名=sname,出生日期=2013-sage from student–条件查询select * from course where ccredit>3select * from course where ccredit between 2 and 5select * from course where ccredit> 2 and ccredit
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 studentselect 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.snoselect 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 studentselect distinct sname from student ,sc where student.sno=sc.snoselect sname from student ,sc where student.sno=sc.sno and student.sno not in (select sno from sc where grade
–子查询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 bncselect * from studentrevoke select on student from bnc–视图的创建create view VIEW_STUGrade(学号,姓名,课程,成绩)asselect student.sno,student.sname,course.cname,sc.grade from student,course,scwhere 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,scwhere 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 StudetsGO create trigger insert_TriON student afterinsert as print ‘有新数据插入！’GO create trigger update_Trion student afterupdate as print ‘有数据更新！’GO create trigger delete_Trion student afterdelete as print ‘有数据删除！’–修改触发器GO alter trigger delete_Trion student after deleteasif ‘王帅’ 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_Trisp_helptext delete_Trisp_depends delete_Trisp_helptrigger student–删除触发器drop trigger delete_Tri到此，关于“SQL Server数据库的基本操作语句总结”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注云技术网站，小编会继续努力为大家带来更多实用的文章！

相关推荐: MySQL中mysqldump的使用方法

本篇内容介绍了“MySQL中mysqldump的使用方法”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ 逻辑备份一般用于数据迁移或者数据量很小时，采用数据导出的…