sql语句快查.docxVIP

目前最全的常用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种不同的insertinsert 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种不同的deletedelete 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 RuXueTime2001-12-31;select * from student where Department=计算机;select * from student where ClassName软件2班;B.逻辑运算符-OR,AND,NOTselect * from student where ClassName=软件2班 and Sex=女; -ANDselect * from student where Age19 OR Sex=女; -ORselect * from student where NOT Age=19; -NOT或者:select * from student where Age!=19; -作用同NOTselect * from student where Age19; -作用同NOTC.特殊运算符-%,_,BETWEEN,IS NULL,LIKE,IN,EXISTSselect * from CourseSheet where TeacherName LIKE 苏%; -LIKE %select DISTINCT ClassName from student where ClassName NOT LIKE 软件2班; -NOT LIKEselect DISTINCT TeacherName from CourseSheet where TeacherName LIKE 苏_步; -LIKE _select sCourseName from Course where sFirstCourse IS NULL; -IS NULLselect * from student where sName LIKE _成%; -LIKE _ %select * from student where Age BETWEEN 18 AND 20; -BETWEENselect * from student where Age NOT BETWEEN 18 AND 20; -NOT BETWEENselect * from student where RuXueTime BETWEEN 2000-1-1 AND 2002-12-31 order by RuXueTime; -BETWEENselect sFirstCourse from Course where exists(SELECT NULL) ; -existsselect * from student where department in (计算机,国际贸易); -INselect * 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 RuXueTime1;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 score90;基于嵌套的查询-查询沈香娜所在班级所有学生的名单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 =高等数学;-列出学习