sql语句

use stu;
#修改基本表
#1.增新属性列
#alter table sc add Smo varchar(10) not null;
#2.增列级约束
#alter table sc add unique(Smo);
#3.增列级约束表级
#alter table sc add foreign key(Sno) references is_stu(Sno);
#4.删列
#alter table sc drop Smo cascade;
#alter table sc drop Smo restrict;
#5.删约束
#alter table sc drop constraint unique cascade;
#6改列
#alter table sc alter column Smo INT;

 

#建立索引
#CREATE INDEX myIndex ON website(name);
#create unique index idx_stusname on sc(Sno asc,Cno desc);
#聚簇索引cluster index
#重命名索引
#alter index idx_stusname rename to idx_stusname_;
#删索引
#drop index idx_stusname_

#查询经过计算的值
select Sname, (extract(year from current_date)-extract(year from Sbirthdate)) "年龄" from student;
select distinct Sno from SC where Grade<60;
#谓语in 属性属于指定集合
select Sname ,Ssex from Student where Smajor in ("信息安全","计算机科学与技术");
#字符正则
select Sname,Ssex from Student where Sname not like '刘%';
#空值
select Sno from SC where Grade is not null;
#order by
select * from SC order by Cno,Grade desc;
#having作用于组，不是基本表
select Sno from SC where Semester='20192' group by Sno;
having count(*)>10;
select Sno,avg(Grade) from sc group by Sno ;
having avg(Grade)>=80;

#嵌套查询----条件用select语句表示
select Sname from Student where Sno in (select Sno from sc where Cno='81003');
#相关子查询
select Sno,Cno from sc x where Grade>=(select avg(Grade) from sc y where x.Sno=y.Sno);
#连接查询
#1.自然连接，查询的列若两表都有，注意 表.列
select sc.Sno,Sname from Student,sc where Student.Sno=sc.Cno and Grade>80;
#2.左外连
select * from Student Left outer join sc on(Student.Sno=sc.Sno);
#any，all
select Sname,Sbirthdate,Smajor from Student where Sbirthdate> any(select Sbirthdate from Student where Smajor="计算机科学与技术") and Smajor!="计算机科学与技术";
#exists的子查询核心看内层是否有返回值
select Sno,Sname,Smajor from Student s1 where exists(select * from Student s2 where s2.Smajor=s1.Smajor and s2.Sname='刘晨');
#插入子查询
#create table Smajorage ( Smajor varchar(20),avg_age smallint);
insert into Smajorage(Smajor,avg_age) select Smajor,avg(extract(year from current_date)-extract(year from Sbirthdate)) from Student group by Smajor;
#