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 defau,--删除表的约束 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,--关联表主键已经存在，可以如下操作办法添加主键和外键约束,alter table course add constraint PK_CNO primary key(cno), constraint FK_CPNO foreign key(cpno) REFE,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.gra,--字符串连接查询,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 wh,--子查询,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 2,--运算符和通配符,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 ,--外连接,select A.*,B.* from student A left join sc B on A.sno=B.sno select A.*,B.* from student A right join,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.gra,--字符串连接查询,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 wh,--子查询,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,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：指定信息 */ s,sp_helptext delete_Tri,sp_depends delete_Tri,sp_helptrigger student,--删除触发器,drop trigger delete_Tri,
SQL中Case的使用具体相关方法
Case具有两种格式。简单Case函数和Case搜索函数。
复制具体相关代码 具体相关代码如下:
--简单Case函数
CASE sex
WhEN '1' ThEN '男'
WhEN '2' ThEN '女'
ELSE '其他' END
--Case搜索函数
CASE WhEN sex = '1' ThEN '男'
WhEN sex = '2' ThEN '女'
ELSE '其他' END
这两种相关方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。
还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。
--比如说，下面这段SQL，您永远无法得到“第二类”这个结果
复制具体相关代码 具体相关代码如下:
CASE WhEN col_1 IN ( 'a', 'b') ThEN '第一类'
WhEN col_1 IN ('a') ThEN '第二类'
ELSE'其他' END
下面咱们来看一下，使用Case函数都能做些什么事情。
一，已知数据按照另外一种相关方式进行分组，分析。
有如下数据:(为了看得更清楚，我并没有使用国家具体相关代码，而是直接用国家名作为Primary Key)
国家（country）人口（population）
中国600
美国100
加拿大100
英国200
法国300
日本250
德国200
墨西哥50
印度250
根据这个国家人口数据，统计亚洲和北美洲的人口数量。应该得到下面这个结果。
洲人口
亚洲1100
北美洲250
其他700
想要解决这个问题，您会怎么做？生成一个带有洲Code的View，是一个解决具体相关方法，但是这样很难动态的改变统计的相关方式。
假如如果使用Case函数，SQL具体相关代码如下:
复制具体相关代码 具体相关代码如下:
SELECT SUM(population),
CASE country
WhEN '中国' ThEN '亚洲'
WhEN '印度' ThEN '亚洲'
WhEN '日本' ThEN '亚洲'
WhEN '美国' ThEN '北美洲'
WhEN '加拿大' ThEN '北美洲'
WhEN '墨西哥' ThEN '北美洲'
ELSE '其他' END
FROM Table_A
GROUP BY CASE country
WhEN '中国' ThEN '亚洲'
WhEN '印度' ThEN '亚洲'
WhEN '日本' ThEN '亚洲'
WhEN '美国' ThEN '北美洲'
WhEN '加拿大' ThEN '北美洲'
WhEN '墨西哥' ThEN '北美洲'
ELSE '其他' END;
同样的，咱们也可以用这个具体相关方法来判断工资的等级，并统计每一等级的人数。SQL具体相关代码如下；
复制具体相关代码 具体相关代码如下:
SELECT
CASE WhEN salary <= 500 ThEN '1'
WhEN salary > 500 AND salary <= 600 ThEN '2'
WhEN salary > 600 AND salary <= 800 ThEN '3'
WhEN salary > 800 AND salary <= 1000 ThEN '4'
ELSE NULL END salary_class,
COUNT(*)
FROM Table_A
GROUP BY
CASE WhEN salary <= 500 ThEN '1'
WhEN salary > 500 AND salary <= 600 ThEN '2'
WhEN salary > 600 AND salary <= 800 ThEN '3'
WhEN salary > 800 AND salary <= 1000 ThEN '4'
ELSE NULL END;
二，用一个SQL数据库语句完成不同条件的分组。
有如下数据
国家（country）性别（sex）人口（population）
中国1 340
中国2 260
美国1 45
美国2 55
加拿大1 51
加拿大2 49
英国1 40
英国2 60
按照国家和性别进行分组，得出结果如下
国家男女
中国340 260
美国45 55
加拿大51 49
英国40 60
普通情况下，用UNION也可以实现用一条数据库语句进行查询。但是那样增加消耗(两个Select部分)，而且SQL数据库语句会比较长。
下面是一个是用Case函数来完成这个功能的例子
复制具体相关代码 具体相关代码如下:
SELECT country,
SUM( CASE WhEN sex = '1' ThEN
population ELSE 0 END), --男性人口
SUM( CASE WhEN sex = '2' ThEN
population ELSE 0 END) --女性人口
FROM Table_A
GROUP BY country;
这样咱们使用Select，完成对二维表的输出形式，充分显示了Case函数的强大。
三，在Check中使用Case函数。
在Check中使用Case函数在很多情况下都是非常不错的解决具体相关方法。可能有很多人根本就不用Check，那么我建议您在看过下面的例子之后也尝试一下在SQL中使用Check。
下面咱们来举个例子
公司A，这个公司有个规定，女职员的工资必须高于块。假如如果用Check和Case来表现的话，如下所示
复制具体相关代码 具体相关代码如下:
CONSTRAINT check_salary ChECK
( CASE WhEN sex = '2'
ThEN CASE WhEN salary > 1000
ThEN 1 ELSE 0 END
ELSE 1 END = 1 )
假如如果单纯使用Check，如下所示
复制具体相关代码 具体相关代码如下:
