目前最全的常用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表
