练习题下

31.select sname,ssex,sbirthday from student
union
select tname,tsex,tbirthday from teacher

32.select sname,ssex,sbirthday from student where ssex='女'
union
select tname,tsex,tbirthday from teacher where tsex='女'

33. select * from score a where degree<(select avg(degree) from score b where b.cno=a.cno)

34.select tname,depart from teacher where tno in(select tno from course where cno in (select distinct cno from score))

35.select tname,depart from teacher where tno not in(select tno from course where cno in (select distinct cno from score))

36.select class from student where ssex='男' group by class having count(*) >1

37.select * from student where sname not like '王%'

38.select sname,YEAR(getdate())-YEAR(sbirthday) from student

39.select max(sbirthday),min(sbirthday) from student

40.select * from student order by class desc,sbirthday asc

41.select cname from course where tno in(select tno from teacher where tsex='男')

select tname,cname from course,teacher where course.tno = teacher.tno and teacher.tsex='男'

42. select * from score where degree = (select max(degree) from score)

select top 1 * from score order by degree desc

43.select sname from student where ssex=(select ssex from student where sname='李军')

44.select sname from student where ssex=(select ssex from student where sname='李军') and class = (select class from student where sname='李军')

45.select * from score where sno in(select sno from student where ssex='男') and cno in(select cno from course where cname='计算机导论')

 

1.数据库操作
create database 数据库名称
drop database 数据库名称
use 数据库名称
go 两条SQL语句之间分隔

2.表操作
create table 表名( 列名 类型 其它,列名 id类型 其它 )
primary key 主键
identity 自增长列
not null 非空
unique 唯一
references 外键 references 主表名(主表主键列)

drop table 表名

3.数据操作

增加：
insert into 表名 values(每一列的值)
insert into 表名(列名) values(值)

修改：
update 表名 set 列名=值,列名=值 where 筛选条件

删除：
delete from 表名 where 筛选条件

查询：

1.简单查询
select * from 表名
select 列名 from 表名
select 列名 as 别名 from 表名

2.条件查询 where or and
select * from 表名 where 条件1
select * from 表名 where 条件1 or 条件2
select * from 表名 where 条件1 and 条件2

3.范围查询 between and
select * from 表名 where 列名 between 值1 and 值2

4.离散查询 in not in
select * from 表名 where 列名 in(数据列表)

5.模糊查询 like %任意多个字符 _任意一个字符
select * from 表名 where 列名 like ‘%_’

6.排序查询 order by desc降序 asc升序
select * from 表名 order by 列名

7.分组查询 group by having
select * from 表名 group by 列名 having 条件

8.分页查询 top n 取前n个值
select top n * from 表名

9.去重查询 distinct
select distinct 列名 from 表名

10.聚合函数（统计函数）
select count(*) from 表名
select sum(列名) from 表名
select avg(列名) from 表名
select max(列名) from 表名
select min(列名) from 表名

高级查询：

1.连接查询
列的扩展 join on

2.联合查询
行的扩展 union

3.子查询
无关子查询
子查询和父查询没有一定的关系，子查询可以单独执行
相关子查询
子查询执行的时候必须使用父查询的内容作为条件