SQL 基本语句

定义基本表

Eg.建立一个”学生”表Student,它由学号Sno,姓名Sname,性别Ssex,年龄Sage,所在系Sdept五个属性组成.学号不能为空,且值唯一.姓名取值也唯一.

CREATE TABLE Student

(Sno CHAR(5) NOT NULL UNIQUE,

Sname CHAR(20) UNIQUE,

Ssex CHAR(1),

Sage INT,

Sdept CHAR(15));

建立一个课程表SC,学号Sno,课程名Scourse, 分数 Grade学号不能为空,且值唯一.

CREATE TABLE SC

(Sno CHAR(5) NOT NULL UNIQUE,

Scourde CHAR(15),

Grade INT);

这里为了以后的查询,先引入插入单个元组

INSERT INTO Student

VALUES('001','KATE','女',20,'CS');

INSERT INTO Student

VALUES('002','JIM','男',21,'MATH');

INSERT INTO Student

VALUES('003','JACK','男',21,'MATH');

INSERT INTO Student

VALUES('004','LUCY','女',21,'ART');

INSERT INTO Student

VALUES('005','LILY','女',21,'ART');

INSERT INTO Student

VALUES('006','JHON','男',21,'IS');

INSERT INTO SC

VALUES('001','JAVA',90);

INSERT INTO SC

VALUES('002','C',85);

INSERT INTO SC

VALUES('003','PHP',70);

INSERT INTO SC

VALUES('006','JAVA',60);

Ø 修改基本表

Eg.向Student表添加”入学时间”列,其数据类型为日期型.

ALTER TABLE Student ADD ScomeDATE;

Ø 删除基本表

Eg.DROP TABLE Student;

Ø 查询

?nbsp; 简单查询

Eg.查询全体学生的详细记录.

SELECT*

FROM Student;

Eg.查询全体学生的姓名,学号,系.

SELECT Sname,Sno,Sdept

FROM Student;

Eg.查询全体学生姓名和出生年份,年龄

SELECT Sname NAME,'Year ofBirth:'BIRTH,2007-Sage BIRTHDAY,Sage age

FROM Student;

Eg.在查询结果中去除重复行.

SELECT DISTINCT Sdept

FROM Student;

?nbsp; 查询满足条件的元组

Eg.查询艺术系的学生姓名.

SELECT Sname

FROM Student

WHERE Sdept='ART';

Eg.查询18岁以下的学生姓名.

SELECT Sname

FROM Student

WHERE Sage<18;

?nbsp; 范围查询

Eg.查询20到23岁间的学生姓名.

SELECT Sname

FROM Sudent

WHERE Sage BETWEEN 20AND 23;

?nbsp; 确定集合

Eg.IN谓词作用是多个OR运算符

Eg.查询MATH,ART系的学生姓名.

SELECT Sname

FROM Student

WHERE Sdept IN('MATH','ART');

Eg.查询不是MATH,ART系的学生姓名.

SELECT Sname

FROM Student

WHERE Sdept NOT IN('MATH','ART');

?nbsp; 字符匹配查询

% 代表长的字符串

a%b 是以a开头,以b结尾的任意长的字符串

a% 是以a开头的任意长的字符串

%b 是以b结尾的任意长的字符串.

_ 代表单个字符.

a_b 是以a开头,以b结尾的长度为3的字符串

a_ 是以a开头的长度为2的字符串

_b 是以b结尾的长度为2的字符串.

一个汉字占两个字符.

Eg.查询所有姓刘的学生的姓名.

SELECT Sname

FROM Student

Where Sname LIKE ‘刘%’;

Eg.查询姓欧阳且名为三个字的学生姓名.

SELECT Sname

FROM Student

WHERE Sname LIKE ‘欧阳_ _’;

Eg.查询第二个字为阳的学生姓名.

SELECT Sname

FROM Student

WHERE Sname LIKE ‘_ _阳%’;

Eg.查询有DB_Design课程的学生名.

SELECT Sname

FROM Student

WHERE Scourse LIKE‘DB\_Design’ ESCAPE ‘\’;

?nbsp; 空查询

Eg.查询所有没有成绩的课程和姓名.

SELECT Scourse,Sname

FROM SC

WHERE Grade IS NULL;

SC是关于学生课程的表

Eg.查询所有有成绩的课程和姓名.

SELECT Scourse,Sname

FROM Student

WHERE Grade IS NOT NULL;

?nbsp; 多重条件查询

Eg.查询艺术系21岁以下的男生的姓名..

SELECT Sname

FROM Student

WHERE Sage<=21 AND

Ssex='男' AND

Sdept='ART';

?nbsp; 对查询结果排序

Eg.查询选了JAVA课程的学生姓名和分数,查询结果按课程分数降序排列.

ASC 升序 DESC降序默认的为升序

SELECT Sno,Grade

FROM SC

WHERE Scourse='JAVA’

ORDER BY Grade DESC;

Eg.查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄按降序排列.

SELECT *

FROM Student

ORDER BY Sdept,Sage DESC;

?nbsp; 使用函数查询

Eg. 查询学生人数

SELECT COUNT (*)

FROM Student;

Eg.计算JAVA课程的学生平均成绩.

SELECT AVG(Grade)

FROM SC

WHERE Scourse='JAVA';

Eg.查询JAVA课程的最高分

SELECT MAX(Grade)

FROM SC

WHERE Scourse='JAVA';

?nbsp; 查询结果进行分组

GROUP BY 将查询结果分组,值相等的为一组.

Eg.求各个课程号及相应的选课人数.

SELECT Scourse, COUNT(Sno)

FROM SC

GROUP BY Scourse;

Eg.查询选修了3门以上课程的学生学号.

SELECT Sno

FROM SC

GROUP BY Sno

HAVING COUNT(*)>3;

?nbsp; 连接查询

以前的查询都是单表查询,下面是关于多表查询的,这种查询叫作连接查询.

等值与非等值连接查询

Eg.查询每个学生及选修课程的情况.

学生情况放在Student表中,学生选课情况放在Sc表中.这个查询涉及两个表,两个表通过Sno联系的.

SELECT Student.*,SC.*

FROM Student,Sc

WHERE Student.Sno=SC.Sno;

注意查询结果中将有两列Sno!

在等值连接种把目标中重复的属性列去掉成为自然连接.

Eg.将上例改用自然连接完成.

SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Scourse,Grade

FROM Student,SC

WHERE Student.Sno=SC.sno;

无重复的可不加前缀,Sno是重复的,所以加前缀.

?nbsp; 自身连接

一个表与自身的连接.

Eg.查询每一门课程的间接先修课.

在 SC表中,只有这门课的直接先修课.,FIRST,SECOND就这个表的两个别名.

SELECTFIRST.Scourse,SECOND.Cpo

FROM SC FIRST,SCSECOND

WHEREFIRST.Cpno=SECOND.Scourse;

?nbsp; 外连接

在通常的连接中,只有符合条件的元组才会输出,但有时,想把Student表中所有的学生的信息都显示出来,要是他没选课,只会显示他的其他信息,选课信息为空.这时就用到了外连接.

Eg.显示Student表中所有学生的信息(包括选课信息)

SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Scourse,Grade

FROM Student,SC

WHERE Student.Sno=SC.Sno(*);

?nbsp; 复合条件连接

Eg.查询选了JAVA且得分90以上的所有男生.

SELECT Student.Sno,Sname

FROM Student,SC

WHERE Student.Sno=SC.Sno AND

SC.Scourse=’JAVA’ AND

SC.Grade>=90 AND

Student.Ssex=’男’;

?nbsp; 嵌套查询

将一个查询块嵌套在另一个查询块的WHERE或HAVING条件中,称为嵌套查询.

Eg.查询选了JAVA课程的学生

SELECT Sname

FROM Student

WHERE Sno IN

(SELECT Sno

FROM SC

WHERE Scourse='JAVA');

?nbsp; IN动词的查询

查询和codear在同一系的学生

SELECT Sname

FROM Student

WHERE Sdept IN

(SELECT Sdept

FROM Student

WHERE Sname=’codear’);

因为一个学生只能在一个系学习,所以IN也可用=取代.

用自身连接实现上述例子

SELECTS1.Sno,S1.Sname,S1.Sdept

FROM Student S1,Student S2

WHERE S1.Sdept=S2.Sdept AND

S2.Sname=’codear’;

也可用表的别名来和嵌套查询实现

SELECT Sno,Sname,Sdept

FROM Stuent S1

WHERE S1.Sdept IN

(SELECT Sdept

FROM Student S2

WHERE S2.Sname=’codear’);

?nbsp; 带有ANY或ALL谓词的子查询

Eg.查询其他系中比信息系中某一学生年龄小的学生姓名和年龄.

SELECT Sname,Sage

FROM Student

WHERE Sage<ANY(SELECT Sage

FROM Student

WHERE Sdept='IS')

AND Sdept<>'IS';

相当于

SELECT Sname,Sage

FROM Student

WHERE Sage< (SELECTMAX(Sage)

FROM Student

WHERE Sdept=’IS’)

AND Sdept<>’IS’

Eg.查询其他系中比信息系中所有学生年龄都小的学生姓名和年龄.

SELECT Sname,Sage

FROM Student

WHERE Sage<ALL(SELECT Sage

FROM Student

WHERE Sdept=’IS’)

AND Sdept<>’IS’

相当于

SELECT Sname,Sage

FROM Student

WHERE Sage< (SELECTMIN(Sage)

FROM Student

WHERE Sdept=’IS’)

AND Sdept<>’IS’

?nbsp; 带有EXISTS谓词的子查询.

带有EXISTS谓词的子查询不返回人和数据,只返回TURE,FALSE.

Eg.查询选了JAVA课程的学生姓名.

SELECT Sname

FROM Student

WHERE EXISTS

(SELECT*

FROM SC

WHERE Sno=Student.Sno ANDScourse='JAVA');

与EXISTS相对应的是NOTEXISTS

Eg.查询没选JAVA课程的学生姓名.

SELECT Sname

FROM Student

WHERE NOT EXISTS

(SELECT*

FROM SC

WHERE Sno=Studet.Sno ANDScourse=’JAVA’);

IN谓词,比较运算符,ANY和ALL谓词都可用EXISTS 或NOT EXISTS 谓词来代替,然而反之不成立.

Eg.例如查询和codear在同一系的学生也可用EXISTS完成.

SELECT Sname

FROM Student S1

WHERE EXISTS

(SELECT *

FROM Student S2

WHERE S2.Sdept=S1.Sdept AND

S2.Sname=’codear’);

?nbsp; 集合查询

集合操作主要包括: 并UNION, 交 INTERSECT, 差MINUS.

Eg. 查询计算机系的18岁下的男生.

SELECT *

FROM Student

WHERE Sdept=’CS’

INTERSECT

SELECT *

FROM Student

WHERE Sage<18;

集合查询都可用其他查询方法实现

Eg.查询计算机学生与年龄18下的学生的差集.

SELECT*

FROM Student

WHERE Sdept=’CS’ AND Sage<18;

Ø 数据更新

?nbsp; 插入数据

插入单个元组

Eg.插入学生记录(学号:001,姓名:KATE,性别:女,系:CS,年龄:20)

INSERT

INTO Student

VALUES('001','KATE','女',20,'CS');

Eg.插入查询结果

子查询结果也可嵌套在INSERT INTO语句中.

CREATE TABLE Deptage

(Sdept CHAR(15),

Avgage SMALLINT);

INSERT

INTO Deptage(Sdept,Avgage)

(SELECT Sdept,AVG(Sage)

FROM Student

GROUP BY Sdept);

?nbsp; 修改数据

修改某一元组的值

Eg.将001的年龄改为19岁.

UPDATE Student

SET Sage=’19’

WHERE Sno=’001’;

Eg.修改所有元组的值

将所有学生年龄减1岁.

UPDATE Student

SET Sage=Sage-1;

带有自查询的修改语句.

自查询也可作为修改条件嵌套在UPDATE语句中.

Eg.将计算机系所有学生成绩置零.

UPDATE SC

SET Grade=0

WHERE ‘CS’=

(SELECT Sdept

FROMStudent

WHERE Student.Sno=SC.Sno);

?nbsp; 删除数据

Eg. 删除某一个元组

删除学号为001的学生记录

DELETE

FORM Student

WHERE Sno=’001’;

Eg.删除所有元组

删除所有学生选课记录

DELETE

FORM Stuent

带子查询的删除语句

同样自查询也可嵌套到DELETE语句中.

Eg.删除所有计算机学生选课记录

DELETE

FROM SC

WHERE ‘CS’=

(SELECT Sdept

FROMStudent

WHERE Student.Sno=SC.Sno);

删除表中某一列

Eg.删除Student表中列Sdept

ALTERTABLE Student DROP COLUMN Sdept;