SQL语句小结

------------------表,视图,索引的操作----------------------

1.创建视图:其中有表uproduct和表uType 视图是从一个表或多个基本表中导出视图,是一个虚表
Create VIEW vProduct as
select p.pid as 产品编号,p.pname as 产品名称,t.tid as 产品类型编号,t.tname as 产品类型名
from uproduct as p inner join uType as t on p.pid=t.tid;

可以通过视图对所有产品列表进行查询.
select * from vProduct //vProduct 为视图名
2.创建表:
create table Student(
sno char(10) primary key,
sname varchar(10),
sage smallint,
sex char(2)
)
3.更改表
alter table student add sdept varchar;//增加列
alter table Student alter colnmu Sage int//更改Sage这一字段的数据类型
alter table Student add unique(Sname)
4.删除表
drop table Student distinct/cascade,
若表上有视图则需要使用cascade关键字才能将表和视图(触发器,存储过程等)一同删除,
若使用distinct,则删除不了表 即 alter table Student cascade
5.创建索引:为了提高查找速度,类似于书本的目录.
①创建一个聚簇索引
create cluster index Stusname on Student(sname);将会在表Student的sname列上创建一个聚簇索引Stusname,而且索引的顺序同Sname的顺序一样排序.
create unique index Coucno on Course(cno);默认排序方式为升序排序
create unique index SCno on SC(Sno desc ,Cno asc);
6.删除一个索引
drop index <索引名字> ; drop index Stusname;

------------------数据查询----------------------
1.select sno,sname from Student //查询全体学生的学号和姓名
2.select Sname NAME,Year of Birth BIRTH ,2004-age BIRTHDAY,Sdept LOWER(Sdept) from Student
select distinct sno form Student;//消除元组中重复的学号.
查询满足条件的元组,使用where子句:
3.select Sname form Student where id ='CS';
4.select Sname,Sno,Sex from Student where Sdept not in('Cs','Ma','Is')
select Sage,Sno form Student where Sname like '黎%'
select * from Course where Cname like 'DB\_%i__';查询以DB开头,倒数第三个字符为i的课程的详细情况.
select * from Sc where Grade is null; 查询成绩为空的所有学生信息
select * from Sc where Grade is not null;查询有成绩的学生的所有信息
select Sname from Student where Sage >20 and sdept ='CS';
select Sno Grade From Sc where cno='3' order by Grade desc
5.聚集函数
select sum(ceredit) form Sc,Course where sno='201371010215' and sc.cno=course.cno 查询201371010215学生选修课程的总学分.
select sno from sc group by sno having count(*)>3;
6.连接查询(多表查询)
select sno.*,sc.* from Student,sc where Student.sno=sc.sno


7.嵌套查询
select Sname from Student where sno in(select sno from sc where cno='2')//不相关子查询
select Student.sno,sname from Student where sdept in(select sdept from Student where sname='liuchen');
上一句查询语句等同于
select S1.sno,S1.sname,S1.sdept from Student S1,Student S2 where S1.sdept=S2.sdept and S2.Sname='liuchen'; //自身连接

select Student.Sno,Sname From Student ,Sc,Course where Student.sno=Sc.sno and Course.cno=Sc.cno and Course.cname='信息系统'//不相关子查询

------------------数据更新----------------------
1.插入数据
insert into Student values('tony','cs','18')
insert into dept_avg(Sdept,Avg_age) select Sdept,Avg_age from Student group by Sdept;
2.修改数据
update Student set Sage=22 where sno='201371010215'
update Student set Sage=Sage+1;
update Student set grade=0 where 'cs'=(select sdept from Student where Student.sno=Sc.sno);
3.删除数据
delete from Student where Sno='201371010215';
delete from Sc
delete from Sc where 'CS'=(select sdept from Student where Student.sno=Sc.sno);