目前最全的常用SQL语句示例(急用速查)!

目前最全的常用SQL语句示例(急用速查).
　　SQL语句大概有４,５年不太用了，最近换了个新公司，项目中又用到了SQL语句,赶忙突击温习了一下，汇编成册，以备以后临时查询。

Ⅰ示例使用库，表。
create table Student(sNo char(9) not null primary key, sName char(8), Sex char(2), Age tinyint, Department char(10),ClassName char(10),RuXueTime datetime);  --创建学生表
create table Course(sCourseNo char(4) not null primary key, sCourseName char(12), sFirstCourse char(4));  --创建课程表
create table Grade(sNo char(9),sCourseNo char(4) not null ,Score tinyint);  --创建成绩表
create table CourseSheet(TeacherName char(8),sCourseNo char(4) not null ,StudyHour tinyint,ClassName char(10) not null );  --创建授课表

--插入Student表记录
insert into student(sNo,sName,Sex,Age,Department,ClassName,RuXueTime) values('200009001','葛文卿','女',22,'国际贸易','国贸2班','08-29-2000');  --插入一条记录
insert into student(sNo,sName,Sex,Age,Department,ClassName,RuXueTime) values('200104019','郑秀莉','女',21,'会计学','会计1班','09-2-2001');  --插入一条记录
insert into student(sNo,sName,Sex,Age,Department,ClassName,RuXueTime) values('200203001','刘成锴','男',18,'计算机','软件2班','08-27-2002');  --插入一条记录
insert into student(sNo,sName,Sex,Age,Department,ClassName,RuXueTime) values('200206001','李涛','男',19,'电子学','电子1班','08-25-2002');  --插入一条记录
insert into student(sNo,sName,Sex,Age,Department,ClassName,RuXueTime) values('200203002','沈香娜','女',18,'计算机','软件2班','05-13-2001');  --插入一条记录
insert into student(sNo,sName,Sex,Age,Department,ClassName,RuXueTime) values('200206002','李涛','男',19,'电子学','电子1班','08-25-2002');  --插入一条记录
insert into student(sNo,sName,Sex,Age,Department,ClassName,RuXueTime) values('200203003','肖一竹','女',19,'计算机','软件2班','03-22-2000');  --插入一条记录

--插入course表记录
insert into course(sCourseNo,sCourseName,sFirstCourse) values('C801','高等数学',NULL);
insert into course(sCourseNo,sCourseName,sFirstCourse) values('C802','C++语言','C807');
insert into course(sCourseNo,sCourseName,sFirstCourse) values('C803','数据结构','C802');
insert into course(sCourseNo,sCourseName,sFirstCourse) values('C804','数据库原理','C803');
insert into course(sCourseNo,sCourseName,sFirstCourse) values('C805','操作系统','C807');
insert into course(sCourseNo,sCourseName,sFirstCourse) values('C806','编译原理','C803');
insert into course(sCourseNo,sCourseName,sFirstCourse) values('C807','离散数学',NULL);

--插入Grade表记录
insert into Grade(sNo,sCourseNo,Score) values('200203001','C801',98);
insert into Grade(sNo,sCourseNo,Score) values('200203002','C804',70);
insert into Grade(sNo,sCourseNo,Score) values('200206001','C801',85);
insert into Grade(sNo,sCourseNo,Score) values('200203001','C802',99);
insert into Grade(sNo,sCourseNo,Score) values('200206002','C803',82);

--插入CourseSheet表记录
insert into CourseSheet(TeacherName,sCourseNo,StudyHour,ClassName) values('苏亚步','C801',72,'软件2班');
insert into CourseSheet(TeacherName,sCourseNo,StudyHour,ClassName) values('王立山','C802',64,'软件2班');
insert into CourseSheet(TeacherName,sCourseNo,StudyHour,ClassName) values('何珊','C803',72,'软件2班');
insert into CourseSheet(TeacherName,sCourseNo,StudyHour,ClassName) values('王立山','C804',64,'软件2班');
insert into CourseSheet(TeacherName,sCourseNo,StudyHour,ClassName) values('苏亚步','C801',72,'电子1班');

Ⅱ 操纵语言(INSERT、UPDATE、DELETE).sql
--3种不同的insert
insert into student(sNo,sName,Sex,Age,Department,ClassName,RuXueTime) values('200009001','葛文卿','女',22,'国际贸易','国贸2班','08-29-2000');  --插入Student表记录
insert into student values('200009001','葛文卿','女',22,'国际贸易','国贸2班','08-29-2000');  --插入Student表记录
insert into student(sNo,sName,RuXueTime) values('200104019','郑秀莉','09-2-2001');  --插入一条记录

update student set Sex='男',RuXueTime='08-13-2001' where sname='葛文卿'; --修改Student表中的一条记录
update Course set sFirstCourse=NULL where sCourseNo='C807';

--2种不同的delete
delete from student where sname='葛文卿';  --删除student中的一条记录
delete from student ;  --清空student表中的全部记录

Ⅲ 查询语言(SELECT).sql
--SQL语言中最灵活、最强大、最主要、最核心的部分就是它的查询功能（SELECT语句）

◆基于单表查询
--1.查询指定的字段
select sNo,sName,RuXueTime from student;

--2.通配符"*"的使用
select * from student;

--3.基于字段的表达式
select sno,sname,2002-age as  "出生年月" from student;
或者：
select sno,sname,2002-age "出生年月" from student;

--4.使用［DISTINCT］短语去掉重复的记录
select distinct sName from student;
select distinct sNo from Grade where Score>=80;

--5.用［WHERE］子句过滤记录
A.关系运算符----[=,<,<=,>,>=,!=或<>]
select * from student where RuXueTime<'2001-12-31';
select * from student where Department='计算机';
select * from student where ClassName<>'软件2班';

B.逻辑运算符----[OR,AND,NOT]
select * from student where ClassName='软件2班' and Sex='女';  --AND
select * from student where Age<19 OR Sex='女';   --OR

select * from student where NOT Age=19;  --NOT
或者:
select * from student where Age!=19;  --作用同NOT
select * from student where Age<>19;  --作用同NOT

C.特殊运算符----[%,_,BETWEEN,IS NULL,LIKE,IN,EXISTS]
select * from CourseSheet where TeacherName LIKE '苏%';  --LIKE %
select DISTINCT ClassName from student where ClassName NOT LIKE '软件2班';  --NOT LIKE
select DISTINCT TeacherName from CourseSheet where TeacherName LIKE '苏_步';  --LIKE _
select sCourseName from Course where sFirstCourse IS NULL;  --IS NULL
select * from student where sName LIKE '_成%';  --LIKE _ %

select * from student where Age BETWEEN 18 AND 20;   --BETWEEN
select * from student where Age NOT BETWEEN 18 AND 20;  --NOT BETWEEN
select * from student where RuXueTime BETWEEN '2000-1-1' AND '2002-12-31' order by RuXueTime;   --BETWEEN

select sFirstCourse from Course  where exists(SELECT NULL) ;  --exists

select * from student where department in ('计算机','国际贸易');  --IN
select * from student where Age in (18,22);  --IN

--6.使用［ORDER BY］子句对查询结果排序
select * from student order by Age; --按Age排序(升序)
或者:
select * from student order by Age asc; --按Age排序(升序)

select * from student order by Age desc; --按Age排序(降序)

select * from student order by Age asc,RuXueTime asc; --按Age排序(升序)
select * from student where RuXueTime<'2002-06-30' order by Age asc,RuXueTime asc; --按Age排序(升序)

--7.集合函数
A.[COUNT]函数的应用
select COUNT(distinct TeacherName) from CourseSheet;
select COUNT(distinct TeacherName) as '教师人数' from CourseSheet;
select COUNT(*) from Student where Sex='女' and Age=19;
select COUNT(distinct sNo)  from Grade;

B.[MAX]和[Min]函数的应用
select Max(Age) as '最大年龄',Min(Age) as '最小年龄' from student;
select Max(score) as '最高成绩' from Grade where sCourseNo='C801';
select Min(score) as '最低成绩' from Grade where sCourseNo='C801';

C.[AVG]函数的应用
select avg(Age) as '平均年龄' from student;

D.[SUM]函数的应用
select SUM(Age) as '年龄总和' from student;
select SUM(Age+1) as '年龄总和' from student;
select SUM(Age) as '女生年龄总和' from student where Sex='女';

--8.使用[GROUP BY]对查询结果进行分组
select RuXueTime as '入学年份',COUNT(*) FROM student group by RuXueTime;
select Sex,Count(Age),avg(Age) from student group by sex; --统计男女生各自的人数和平均年龄
select sCourseNo as '课程号',Count(sCourseNo) as '人数' from Grade group by sCourseNo;  --列出各个课程号以及相应的选修人数

--9.利用[HAVING]筛选结果表
select sNo,COUNT(*) from Grade group by sno having count(*)>1;
select sNo as '学号',COUNT(*) as '选修门数' from Grade group by sno having count(*)>1;

◆基于多表查询
A.自然连接
select student.*,Grade.* from student,Grade where student.sno=Grade.sno;
select student.sno,sname,sex,age,department,className,sCourseNo,Score from student,Grade where student.sno=Grade.sno;

B.自身连接
--列出每一课程的间接先修课(即先修课的先修课)
select F.sCourseNo as '课程号',S.sFirstCourse as '间接先修课' from course F,course S where F.sFirstCourse=S.sCourseNo;

C.复合条件连接
--列出所有学生的学习成绩(要求:输出姓名,课程名称,课程号,成绩,授课教师和该课程的学时数)
select A.sName, D.sCourseName, B.sCourseNo, B.score, C.TeacherName, C.StudyHour  from student A, Grade B, CourseSheet C, Course D where (A.sNo=B.sNo) and (A.ClassName=C.ClassName) and (B.sCourseNo=C.sCourseNo) and (C.sCourseNo=D.sCourseNo);

--列出选修了"C801"课程并且成绩在90分以上的所有学生清单。
select student.sNo,sName,Sex,Age,Department,ClassName from student,Grade where student.sNo=Grade.sNo and sCourseNo='C801' and score>90;

◆基于嵌套的查询
--查询"沈香娜"所在班级所有学生的名单
select sNo,sName,Sex,Age,Department,ClassName from student where ClassName =(select ClassName from student where sName='沈香娜');

A.带有[IN]的子查询
--查询'刘成锴'所在系的所有女生名单
select sNo,sName,Sex,Age,Department,ClassName from student where Department in (select department from student where sName='刘成锴') and Sex='女';

--列出选修了'高等数学'的学生学号、姓名和所在院系。(从课程表中取得'高等数学'的课程号,然后根所查到的高等数学课程号,从成绩表中查找满足条件的学号列表(2个),然后根据查到学号列表(2个)在学生表中找到满足条件的学生信息(2个).)
select sNo,sName,Department from student where sNo in (select sNo from Grade where sCourseNo in (select sCourseNo from Course where sCourseName ='高等数学'));
或者：
select student.sNo,sName,Department from student,Grade,Course where student.sNo=Grade.sNo and Grade.sCourseNo=Course.sCourseNo and Course.sCourseName ='高等数学';

--列出学习课程号为"C801"或"C804"的学生学号、姓名、所在院系、课程名称和成绩。
--本例主要学习有关特殊运算符[IN]和自然连接的表达式书写格式。
select student.sNO,sName,Department,sCourseName,score from student,grade,Course where student.sNo=Grade.sNo and grade.sCourseNo=Course.sCourseNo and Course.sCourseNo in ('C801','C804');

Ⅳ 定义语言(CREATE、ALTER、DROP).sql
--对DataBase库操作
drop database XueJiDataBase;  --删除学籍数据库(XueJiDataBase)
create database XueJiDataBase;  --创建学籍数据库(XueJiDataBase)
alter database XueJiDataBase modify file(Name='XueJiDataBase',maxsize=unlimited );

--对Table表操作
create table Student(sNo char(9) not null primary key, sName char(8), Sex char(2), Age tinyint, Department char(10),ClassName char(10),RuXueTime datetime);  --创建学生表
alter table Student alter column sName char(10);  --修改Student表sName列
drop table Student;  --从XueJiDataBase数据库中删除Student表