欢迎来到258分享网,纯净的网络源码分享基地!

258资源分享网

全部作品
全部作品
网站源码
微信源码
素材特效
源码插件
视频教程
建站学院
热门搜索: 织梦  农业种植  农业  安全设置  官方
258资源分享 > 建站学院 > MSSQL教程 > SQL Server 数据库基本操作语句总结

推荐下载

HTML5响应式自适应网咯设计

2020-05-12   浏览:732

高端HTML5响应式企业通用网

2020-05-06   浏览:513

html5响应式外贸网站英文版

2020-05-08   浏览:503

HTML5自适应律师工作室类网

2020-04-04   浏览:494

HTML5影视传媒文化公司类网

2020-05-12   浏览:489

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

发布时间:2020-06-05  

复制代码 代码如下:


--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'

本文标签

:备案管家服务帮您把复杂流程变简单,解决您在进行网站备案过程中遇到的效率低下和成功率不高的问题。个人300/2个域名,企业500/5个域名,qq1650004.