数据库单表查询

--创建student表格
create table student
(
	sno int primary key,
	sname char(10) unique,
	ssex char(5),
	sage int,
	sdept char(10)
);

--创建course表
create table course
(
	cno int primary key,
	cname char(10) unique,
	cpno int,
	ccredit int,
	foreign key(cpno) references course(cno)
);

--创建SC表
create table sc
(
	sno int,
	cno int,
	grade int,
	primary key(sno,cno),
	foreign key(sno) references student(sno),
	foreign key(cno) references course(cno)
);

--创建任意一个表
create table anyone
(
	a int primary key,
	b int
);

--向student表中增加入学时间列
alter table student add senterence date;

--将年龄的数据类型由整数型改为短整型
alter table student alter column sage smallint;

--增加课程名称取唯一值的约束条件
alter table course add unique(cname);

--删除基本表有两种：restrict和cascade
--restrict删除表是有限制的，即想要删除的基本表不能被其他表的约束所引用；不能存在依赖该表的对象
--cascade删除表则是没有限制的，在删除表的同时相关的依赖对象也会被删除
drop table anyone;

--建立索引是为了加快查询速度，DBA和表的属主（即建立表的人）都可以根据需要建立索引，有些DBMS自动建立索引。DBMS可以自动完成维护索引和自动选择是否使用索引和使用哪些索引
--聚簇索引：按表中记录的物理顺序排序
--student表按照学号升序建立唯一索引
create unique index stusno on student(sno);

--按照课程号升序建立唯一索引在course表中
create unique index coucno on course(cno);
--按照学号升序和课程号降序建立唯一索引
create unique index scno on sc(sno asc,cno desc);
--【注】对于已含重复值的属性列不能建立unique索引，也就是唯一索引
--对某个列建立unique索引后，插入新记录时DBMS会自动检查新纪录在该列上是否取了重复值。这就相当于是增加了一个unique约束

--建立聚簇索引cluster后，基表中数据也需要按照指定的聚簇属性值的升序或降序存放。也就是说聚簇索引的索引项顺序与表中的物理顺序一致
create clustered index stusname on student(sname);
--在SQL server中建立聚簇索引需要使用clustered，否则会发生编译错误，且需要注意的是在创建完基本表后，系统会自动创建一个聚簇索引，所以如果想为这个表在创建一个聚簇索引时应该先删除以前的索引在创建，因为一个基本表只允许拥有一个聚簇索引


--修改索引的名字
alter index scno rename to scsno on sc;

--删除索引：删除索引时，系统会从数据字典中删除有关该索引的描述
drop index student.stuname;--必须加上表名




--查询：查询分为单表查询和多表查询
--    单表查询
--查询全体学生的学号和姓名
select sno,sname  --必选语句，指定要显示的属性列
from student      --必选语句，指定从那个表或者视图内查询
where sage=18;    --查询条件

--选出所有属性列：*
select *          --用*表示所有的列
from student;

--select后面可以跟表达式
select sno,2021-sage  --两列，列名分别为sno，和2021-sage
from student;

--给表达式起别名
select 2021-sage borned
from student;
--结果中只有一列，且列名为borned，属性值为2021-sage

--表达式可以是字符串常量，即可以在查询列后面加上你想添加的列
select sname,'2019' school
from student;
--最终结果为两列，第一列是sname，第二列是school，school列的值均为2019



--目标表达式还可以是函数
select COUNT(Sname)
from student;

--指定distinct关键词可以消除重复的行，否则将输出所有的行
--查询选修了课程的学生学号
select distinct sno
from sc;
 --查询选修了计算机科学系全体学生的名单
 select sname
 from student
 where sdept='CS';
 
 --查询所有年龄在20岁以下的学生姓名及其年龄
 select sname,sage
 from student 
 where sage<20;
 
 --查询考试成绩有不及格的学生的学号
 select distinct sno
 from sc
 where grade<60;

--查询性别为女的学生的学号和姓名
select sno,sname
from student
where ssex='女';

--查询学分为4学分的课程
select cname
from course
where ccredit>=4;

--查询成绩在85分以上的学生学号
select distinct sno
from sc
where grade>85;

--查询年龄在20-23之间的学生姓名、性别和年龄
select sname,ssex,sage
from student
where sage>=20 and
		sage<=23;
		
select sname
from student
where sage between 20 and 23;
		
--查询年龄不在20~23岁之间的学生姓名、系别和年龄
select sname,sdept,sage
from student
where sage<20 and
		sage>23;
		
select sname
from student
where sage not between 20 and 23;

--查询CS、MA、IS学生的姓名和性别
select sname,ssex
from student
where sdept in('CS','MA','IS');

--查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别。
select sname,ssex
from student
where sdept not in('IS','MA');

--%代表任意长度的字符串（长度可以是0）：a%b表示以a开头以b结尾的任意长度的字符串
-- _下划线表示任意单个字符：a_b表示以a开头以b结尾的长度为3的字符串
--查询学号为201215121的学生的详细情况。
select *
from student
where sno like '21';

select *
from student
where sno='21';

--查询所有姓刘学生的姓名、学 号和性别。
select sname,sno,ssex
from student
where sname like'刘%';

--查询姓"欧阳"且全名为三个汉字的 学生的姓名。
select sname
from student
where sname like '欧阳_';

--查询名字中第2个字为"阳"字 的学生的姓名和学号
select sname
from student
where sname like '_阳%';

--查询所有不姓刘的学生姓名、学号 和性别。
select sname
from student
where sname not like'刘%';


--将通配字符转换成普通字符，用escape声明转义字符是谁
--查询DB_Design课程的课程号和学分
select cno,ccredit
from course
where cname like'DB\_Design' escape'\';

--查询以“DB_”开头且倒数第三个字符为i的课程的详细情况
select *
from course
where cname like'DB\_%i_ _' escape'\';

--is null中的is不能用“=”来代替
--某些学生选修课程后没有参加考试，所以有选课记录，但没 有考试成绩。 查询缺少成绩的学生的学号和相应的课程号
select sno,cno
from sc
where grade is null;

--查所有有成绩的学生学号和课程号。
select sno,cno
from sc
where grade is not null;

--and 和 or可以用来连接多个查询条件，其中and的优先级要高于or
--查询计算机系年龄在20岁以下的学生姓名
select sname
from student
where sdept='CS' and
		sage<20;

--查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别
select sname,ssex
from student
where sdept='CS' or
		sdept='IS' or
		sdept='MA';
		
select sname,ssex
from student
where sdept in('CS','MA','IS');

--升序：ASC  降序：DESC
--对于空值的排序显示的排序次序有具体的DBMS来决定
--查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
select sno,grade
from sc
where cno=3
order by grade ASC;

select sno,grade
from sc
where cno=3
order by grade;

--查询全体学生情况，查询结果按所在系 的系号升序排列，同一系中的学生按年龄降序排 列。
select *
from student
order by sdept,sage DESC;

--聚集函数：

--1.查询学生总数
select COUNT(*)
from student;

--2.查询选修了课程的学生人数
select COUNT(distinct sno)
from sc;

--3.计算1号课程的平均成绩
select AVG(grade)
from sc
where cno=1;

--4.查询选修1号课程的学生最高分数。
select MAX(grade)
from sc
where cno=1;

--5.查询学生201215012选修课程的总学分数。
select SUM(ccredit)
from sc,course
where sc.cno=course.cno and
		sno='21';
		
		
--细化聚集函数的作用对象：
--如果未对查询结果分组，聚集函数将作用于整个查询结果
--对查询结果分组后，聚集函数将分别作用于每个组
--按指定的一列或多列值分组，值相等的为一组

--求各个课程号及相应的选课人数
select cno,COUNT(sno)
from sc
group by cno;

--查询选修了3门以上课程的学生学号。
select sno
from sc
group by sno
having COUNT(*)>3;

--HAVING短语与WHERE子句的区别：
--作用对象不同
--WHERE子句作用于基表或视图，从中选择满足条件的元组
--HAVING短语作用于组，从中选择满足条件的组。


--查询平均成绩大于等于90分的学生学号和平均成绩
select sno,AVG(grade)
from sc
group by sno
having AVG(grade)>=90;