create database 学习文档;

/*create代表创建文档，database代表创建的是数据库table代表创建的是表，后面跟的是名称*/

use 学习文档; /*将当前操作的文档变为该文档*/

create table Student

(

Sno char(9) primary key, /*其中char代表是数据类型（）里面的数代表所占的字节长度，primary key是设置主码*/

Sname char(20) unique, /*unique代表这一列的数据取唯一值*/

Ssex char(2),

Sage smallint, /*smallint代表着这一列数据为短整型数据*/

Sdept char(20)

);

create table Course

(

Cno char(4) primary key,

Cname char(40),

Cpno char(4),

Ccredit smallint, /*smallint代表着这一列数据为短整型数据*/

foreign key (Cpno) references Course(Cno) /*Cpno是外码，被参照表示Course，被参照列是Cno*/

);

create table SC

(

Sno char(9),

Cno char(4),

Grade smallint, /*smallint代表着这一列数据为短整型数据*/

primary key(Sno,Cno),

foreign key (Sno) references Student(Sno),

foreign key (Cno) references Course(Cno) /*Cpno是外码，被参照表示Course，被参照列是Cno*/

);

/*distinct除去重复值，all 缺省值表示全部输出可以不写*/

/*between...and....和not between...and...可以查找属性值在不在指定的范围内，

其中between后是范围的最小值（即低值），and后的是范围的最大值（即高值）*/

/*in 表示查找该元组内的元素，not in查找不是该元组内的元素*/

/*escape'\','DB\%Design\_A'这里表示'\'为换码字符，

这样匹配串中紧跟在后面的字符'_'不在具有通配符的作用而是转义为普通的‘_'字符

，不然的话就认为是通配符*/

/*涉及空值的查询必须使用 IS 不能使用“=”来代替*/

/*order by子句

用户可以用order by子句对查询结果按照一个或多个属性列的升序（ASC）或

降序（desc）排列，缺省值为升序*/

聚集函数

count([distinct|all] *) 统计元组的个数

count([distinct|all] <列名>) 统计一列中值的个数

sum([distinct|all] <列名>) 计算一列值得总和（此列必须是数值型）

AVG([distinct|all] <列名>) 计算一列值得平均值（此列必须是数值型）

Max([distinct|all] <列名>) 求一列值得最大值

min([distinct|all] <列名>) 求一列值得最小值

group by子句

将查询结果按某一列或多列的值分组，值相等的为一组

对查询的结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组，

聚集函数将作用于整个查询结果

如果分组后还要求按一定的条件对这些组进行筛选，最终输出满足指定条件的组，则

可以使用having 短语指定筛选条件

/*完成如下查询：1.查询全体学生的姓名、学号、所在系。*/

select sname 姓名,sno 学号,sdept 院系

from student;

/*2.查询全体学生的详细记录。*/

select *

from student;

/*3.查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。

并且查询结果分别使用“姓名”、“出生年份”和“系别”表示。*/

select sname 姓名,2015-Sage 出生年份

from student;

/*4.查询所有选了课程的学生的学号。*/

select distinct sno 学号

from SC;

select sno 学号

from SC;

/*5.查询计算机科学系年龄小于20岁的全体学生的名单。*/

select sage 年龄,sdept 系别

from Student

where sage<20;

/*6.查询考试成绩有不及格的学生的学号。*/

select sno 学号,grade 成绩

from SC

where grade<90;

/*7.查询年龄在19~21岁（包括19岁和21岁）之间的学生的姓名、系别和年龄。*/

select sname 姓名,sdept 系别,sage 年龄

from student

where sage>=19 and sage<=21;

select sname 姓名,sdept 系别,sage 年龄

from student

where sage between 19 and 21;

/*8.查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。*/

select sname 姓名,sdept 系别,Ssex 性别

from student

where sdept in ('is','ma');

/*9.查询姓“李”且全名为三个汉字的学生的姓名。*/

select sname 姓名

from student

where sname like '李____';/*存在问题不能删去其他不是对应数量的汉字的名字*/

/*10.查询名字中倒数2个字为"阳"字的学生的姓名和学号。*/

select sname 姓名

from student

where sname like '%阳__';

/*11.查询DB%Design_A课程的课程号和学分。*/

select Cno 课程号,Ccredit 学分,cname 课程

from course

where Cname like 'DB\%Design\_A'escape'\';

select Cno 课程号,Ccredit 学分,cname 课程

from course

where Cname like '数据结构';

/*12.查询以"DB_"开头，且倒数第2个字符为 i的课程的详细情况。*/

select Cno 课程号,Ccredit 学分,cname 课程

from course

where Cname like 'DB\_%i_'escape'\';

/*13.查所有有成绩的学生学号和课程号。*/

select sno 学号,Cno 课程号

from SC

where grade is null;

/*14.查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。*/

select sno 学号,grade 成绩

from SC

where cno='3'

order by Grade desc;

/*15.查询全体学生情况，查询结果按所在系的系号降序排列，同一系中的学生按年龄降序排列。*/

select *

from Student

order by Sdept desc,sage desc;

/*16.查询2号课程的学生最高成绩。*/

select max(grade)

from SC

where cno='2';

/*17.求各个课程号及相应的选课人数。*/

select count(distinct Sno)人数

from SC;

/*18.求各个学号及相应的选课门数。*/

select cno,count(Sno)门数

from SC

group by cno;

/*19.查询各个系及其人数。*/

select sdept,count(Sno)人数

from Student

group by sdept;

/*20.统计男女同学的人数。*/

select ssex,count(Sno)人数

from Student

group by ssex;

/*21.求各门课程的课程号及平均成绩。*/

select Cno,AVG(grade)平均成绩

from SC

group by Cno;

/*22.求平均成绩大于90分的学生的学号输出。*/

select Sno,avg(grade)

from sc

group by Sno

having avg(grade) >60;

/*23.把student和SC做自然连接。*/

select student.*,sc.*

from student,sc

where student.Sno=sc.sno;

/*24.把course和SC做自然连接。*/

select course.*,sc.*

from course,sc

where course.Cno=sc.Cno;

/*25.把student、SC和course做自然连接。*/

select student.*,course.*,sc.*

from student,sc,course

where student.Sno=sc.sno and course.Cno=sc.Cno;

/*26.查询与“刘晨”在同一个系学习的学生的学号、姓名。（用表与表的自身连接实现）*/

select sno 学号,sname 姓名

from student

where sdept in

(

select sdept

from Student

where sname='刘晨'

);