数据库最全的sql语句总结大全.docVIP

CREATE DATABASE edu ON( NAME=edu_data, FILENAME=c:/edu_data.mdf, SIZE=10M, MAXSIZE=1024M, FILEGROWTH=1M) LOG ON( NAME=edu_log, FILENAME=c:/edu_log.ldf, SIZE=5M, MAXSIZE=100M, FILEGROWTH=10%) create database st use st drop database st create schema st drop schema cascade 1 create table st.Student(....); 2 create schema st create table Student(...); 3 set search_path to st; create table Student(..); create table Student (Sno char(9) primary key, Sname char(20) unique, Ssex char(20) check(sex=or sex=) ) create table sc (Sno char(9) Cno char(4) Grade smallint primary key (Sno,Cno) foreign key(Sno) references Student(Sno), foreign key(Cno) references Course(Cno)); alter table Student add Scom datetime; add unique(Cname); alter column Sage smallint; create unique(每一个索引值对应唯一数据) index student on Student(Sno); create cluster index Stusname on Student(Sname); drop index st.student; select 2004-Sage age,distinct Sno from Student where Sdept in(is,cs); select * from Student where Sno like212; where rtrim(Sname) like _liu%\ escape\; select Sno from SC where Grade ie null order by Grade desc(降序); select count(distict *) sum avg max min from Student; 分组 grop by 分组后聚集函数作用于每一个组 select Cno,count(Sno)集合查询 使用了聚集函数或在group by 后 from SC group by Cno; select sdept,count(*) from Student where ssex= group by sdept order by sdept desc; having 对集合特性的限制 select Sno,count(Cno) from SC where Grade=85 group by Sno having count(*)3 order by count(*)desc; select Student.Sno,Sname,Cno,grade from Student,SC whereStudent.Sno=SC.Sno;(两表连接条件) select Studednt.Sno,Sname,Cnno,grade from Student inner join SC on Student.Sno=SC.Sno where sdept=CS; select S1.Sno,S1.Sname,S1.Sdept from Student S1,Student S2 where S1.Sdept=S2.Sdept and S2.Sname=; (选修了课程的学生信息) select s1.Sno,Sname,Cno,grade from Student S1 left outer join SC s2 on s1.Sno=s2.Sno order by s1,Sno; 课程被选修的情况 right outer join 所有情况 full outer join select s=Student.sno,Sname,avg(grade) 平均成绩 from Studen