SQL Server数据使用 三（数据查询）

SQL Server数据使用 三

/*单表查询*/
select Sno,Sname
from student

select*
from student  /*详细数据*/

/*select <目标表达式>
from  ； */

select distinct Sno
from SC;  /*消除重复行*/

select Sname
from student
where sdept='CS'  

/*where子句 确定范围：(not)between and,确定集合：（not）in,字符匹配：（not）like,空值:is(not)null,逻辑运算：and or not*/

select*
from student
where sno like '2%21';
/*like :(not)like'<字符串>' 换码字符：escape'<换码字符>'  通配符 ‘%’:任意长度字符串‘_’：任意单个字符*/

select*
from course
where cname like'DB\_%i__'escape'\';  /*'\'转义’_‘为普通字符*/

select sno,grade
from sc
where cno='3'
order by grade desc; /*order by用于属性列的升序：asc降序：desc 默认为升序*/

select count(distinct sno)
from sc;

/*聚集函数  只能用于select  |group by 中having语句中：
	count(*)  元组个数
	count([distinct\all<列名>]) 列中值的个数
	sum([distinct\all<列名>]) 总和
	avg([distinct\all<列名>])  平均值
	max([distinct\all<列名>]) 最大值
	min([distinct\all<列名>])  最小值*/

select sum(Ccedit)
from sc,course
where sno like '2%21'and sc.cno=course.cno;

/*group by子句：将查询结果按某一列或多列值分组，值相等为一组*/
/*having短语作用于组  where子句作用于基本表或视图*/
select sno
from sc
group by sno
having count(cno)=3;

/*连接查询*/
/*[<表1>.]<列名1><比较运算符>[<表明2>.]<列名2>*/
/*[<表1>.]<列名1>between[<表明2>.]<列名2>and[<表明2>.]<列名3>*/

select student.*,sc.*
from student,sc
where student.sno=sc.sno; /*如果属性名唯一，则可省略前缀*/

select student.sno,sname
from student,sc
where student.sno=sc.sno and
		sc.cno='2'and sc.grade>85;

/*自身连接*/
select first.cno,second.cpno
from course first,course second
where first.cpno=second.cno;

/*外连接*/
select student.sno,sname,ssex,sage,sdept,cno,grade
from student left outer join sc on(student.sno=sc.sno);
/*左连接 left 右连接 right *//*using用来消除重复值  */
select student.sno,sname,ssex,sage,sdept,cno,grade
from student right outer join sc using(sno) on(student.sno=sc.sno);

/*多表连接*/
select student.sno,sname,cname,grade
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno; 

/*嵌套查询: 由select-from-where构成查询块，另加where,having条件的查询*/
select sname
from student
where sno in   /*外层查询 父查询*/ /*in 谓词*/
(select sno
from sc
where cno='2');  /*内查询 子查询：不能使用order by子句*/

select sno,cno
from sc x
where grade>=
(select avg(grade)
from sc y
where y.sno=x.sno);		/*带有比较运算符一般为相关子查询*/	

/*带有any：某一个值 all：全部值 的子查询*/
select sname,sage
from student
where sage<any
(select sage
from student
where sdept='cs')
and sdept<>'cs';  /*父查询'<>'  =   '!='*/

/*exists谓词;存在量词*/
select sname
from student
where exists
(select*
from sc
where sno=student.sno and cno='5');

/*集合查询：并操作：union，交操作：intersect,差操作：except*/
select*
from student
where sdept='cs'
union
select*
from student
where sage<=19;

/*基于派生表：子查询出现在from子句中，的查询*/
select sname
from student,(select sno from sc where cno='1')as sc1
where student.sno=sc1.sno;  /*as关键字可省略但派生表必须有别名*/

/*数据更新*/
/*插入*/
insert
into student(sno,sname,ssex,sdept,sage)
values('201215125','陈德','男','is','18');
/*更新*/
update sc
set grade=0
where sno in
(select sno
from student
where sdept='cs')

select*from student;
/*删除*/
delete
from student
where sno='201215125';

/*视图*/
/*create view<视图名>[(<列名>[,<列名>])]
as<子查询>
[with check option]  (update insert delete)*/

/*创建*/
create view is_student
as
select sno,sname,sage
from student


create view s_g(sno,gavg)
as
select sno,avg(grade)
from sc
group by sno;  /*聚集函数及group by子句定义视图为分组视图；*/

/*删除*/
drop view is_student;
/*drop view<视图名> cascade; 删除全部视图*/ 

/*查询*/
select sno,avg(grade)
from sc
group by sno
having avg(grade)=0;

/*更新视图*/
/*插入*/
insert 
into is_student
values('201215125','赵新','20');
/*修改*/
update is_student
set sname='刘陈'
where sno='201215122';
/*删除*/
delete
from is_student
where sno='201215125'