目前最全的常用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,'国际贸易','国贸班','08-29-2000'); --插入一条记录
insert into student(sNo,sName,Sex,Age,Department,ClassName,RuXueTime) values('200104019','郑秀莉','女',21,'会计学','会计班','09-2-2001'); --插入一条记录
insert into student(sNo,sName,Sex,Age,Department,ClassName,RuXueTime) values('200203001','刘成锴','男',18,'计算机','软件班','08-27-2002'); --插入一条记录
insert into student(sNo,sName,Sex,Age,Department,ClassName,RuXueTime) values('200206001','李涛','男',19,'电子学','电子班','08-25-2002'); --插入一条记录
insert into student(sNo,sName,Sex,Age,Department,ClassName,RuXueTime) values('200203002','沈香娜','女',18,'计算机','软件班','05-13-2001'); --插入一条记录
insert into student(sNo,sName,Sex,Age,Department,ClassName,RuXueTime) values('200206002','李涛','男',19,'电子学','电子班','08-25-2002'); --插入一条记录
insert into student(sNo,sName,Sex,Age,Department,ClassName,RuXueTime) values('200203003','肖一竹','女',19,'计算机','软件班','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,'软件班');
insert into CourseSheet(TeacherName,sCourseNo,StudyHour,ClassName) values('王立山','C802',64,'软件班');
insert into CourseSheet(TeacherName,sCourseNo,StudyHour,ClassName) values('何珊','C803',72,'软件班');
insert into CourseSheet(TeacherName,sCourseNo,StudyHour,ClassName) values('王立山','C804',64,'软件班');
insert into CourseSheet(TeacherName,sCourseNo,StudyHour,ClassName) values('苏亚步','C801',72,'电子班');
操纵语言(INSERT、UPDATE、DELETE).sql
--3种不同的insert
insert into student(sNo,sName,Sex,Age,Department,ClassName,RuXueTime) values('200009001','葛文卿','女',22,'国际贸易','国贸班','08-29-2000'); --插入Student表记录
insert into student values('200009001','葛文卿','女',22,'国际贸易','国贸班','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<>'软件班';
B.逻辑运算符----[OR,AND,NOT]
select * from student where ClassName='软件班' 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 '软件班'; --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"课程并且成绩在分以上的所有学生清单。
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表
--插入外键
alter table Stu_PkFk_Sc add constraint Fk_s foreign key (sno) references Stu_PkFk_S(sno)
--在指定字段后插入字段
exec sp_configure 'allow updates',1 reconfigure with override
go
alter table 表add id int --添加字段
go
--处理字段位置
declare @colid int
select @colid=colid from syscolumns
where id=object_id('你要修改的表名') and name='字段名' --在该字段后插入
if @colid is null set @colid=1
update syscolumns set colid=colid+1
where id=object_id('你要修改的表名') and colid>@colid
update syscolumns set colid=@colid+1
where id=object_id('你要修改的表名') and colid=(
select max(colid) from syscolumns
where id=object_id('你要修改的表名'))
go
exec sp_configure 'allow updates',0 reconfigure with override
--批量替换列中特定的字符
Update tableName SET columeName = REPLACE(columeName, 'a', 'b')
--列转行
PIVOT(MAX(分数) FOR 课程IN ('语文','数学','外语')) A
--CONVERT() 函数
CONVERT(data_type(length),data_to_be_converted,style)
100 或者0 mon dd yyyy hh:miAM （或者PM）
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
109 或者9 mon dd yyyy hh:mi:ss:mmmAM（或者PM）
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
113 或者13 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
120 或者20 yyyy-mm-dd hh:mi:ss(24h)
121 或者21 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-ddThh:mm:ss.mmm（没有空格）
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM
--DATEPART
DATEPART(datepart,date)
年 yy, yyyy
季度 qq, q
月 mm, m
年中的日 dy, y
日 dd, d
周 wk, ww
星期 dw, w
小时 hh
分钟 mi, n
秒 ss, s
毫秒 ms
微妙 mcs
纳秒 ns