SQL语句（模式与表）

数据定义

模式的定义与删除

定义模式

CREATE SCHEMA <模式名> AUTHOTIZATION <用户名> [<表定义子句> |<视图定义子句> |<授权定义子句>];

为用户WANG定义一个学生-课程模式S-T:
CREATE SCHEMA "S-T" AUTHORIZATION WANG;

为用户ZHANG创建一个模式TEST，并且在其中定义一个表TAB1:

CREATE SCHEMA TEST AUTHORIZATION ZHANG
    CREATE TABLE TAB1(COL1 SMALLINT,
                      COL2 INT,
                      COL3 CHAR(20),
                      COL4 NUMBERIC(10,3),
                      COL5 DECIMAL(5,2)
                      );

删除模式

DROP SCHEMA <模式名><CASCADE|RESTRICT>;

CASCADE级联，表示在删除模式的同时把该模式下所有的数据库对象全部删除；
RESTRICT限制，表示如果该模式中已经定义了下属的数据库对象（如表、图等），则拒绝该删除语句的执行。

DROP SCHEMA ZHANG CASCADE; 该语句删除了模式ZHANG，同时，该模式中已经定义的TAB1也被删除。

表的定义修改与删除

定义基本表

CREATE TABLE <表名>(<列名><数据类型>[列级完整性约束条件]
....
[,<表级完整性约束条件>]);

创建一个“学生”表Student。

CREATE TABLE Student(
    Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(20) UNIQUE,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
    );

创建一个“课程“表Course。

CREATE TABLE Course(
    Cno CHAR(4) PRIMARY KEY,
    Cname CHAR(40) NOT NULL,
    Cpno CHAR(4),
    Ccredit SMALLINT,
    FOREIGN KEY(Cpno) REFERENCES Course(Cno) /* 表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno */
    );  
本例书名参照表和被参照表可以是同一个表。

建立学生选课表SC。

CREATE TABLE SC(
    Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT,
    PRIMARY KEY (Sno,Cno),     /* 主码由两个属性构成，必须作为表级完整性进行定义 */
    FOREIGN KEY (Sno) REFERENCES Student(Sno),  /* 表级完整性约束条件，Sno是外码，被参照表是Student */
    FOREIGN KEY (Cno) REFERENCES Course(Cno)    /* 表级完整性约束条件，Cno是外码，被参照表是Course */
    );

修改基本表

ALTER TABLE <表名>
[ADD [COLUMN] <新列名><数据类型>[完整性约束条件]]
[ADD <表级完整性约束>]
[DROP [COLUMN] <列名> [CASCADE|RESTRICT]]
[DROP CONSTRAINT <完整性约束条件> [CASCADE|RESTRICT]]
[ALTER COLUMN <列名><数据类型>];

向Student表增加“入学时间”列，其数据类型为日期型。
ALTER TABLE Student ADD S_entrance DATE;

将年龄的数据类型由字符型（假设原来为字符型）改为整数。
ALTER TABLE Student ALTER COLUMN Sage INT;

增加课程名称必须取唯一值的约束条件。
ALTER TABLE Course ADD UNIQUE(Cname);

删除基本表

DROP TABLE <表名> [RESTRICT|CASCADE];

若选择RESTRICT，则该表的删除是有限制条件的。欲删除的表不能被其他表的约束所引用，不能有视图，不能有触发器，不能有存储过程或函数等。
若选择CASCADE，则该表的删除没有限制条件。在删除表的同时，相关的依赖对象，即将被一起删除。

删除Student表
DROP TABLE Student CASCADE;

索引的建立与删除

建立索引

CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名> [<次序>] [,<列名>[<次序>]]...);

UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。
CLUSTER表示要建立的索引是聚族索引。

为学生-课程数据库中的Student、Course和SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。

 CREATE UNIQUE INDEX Stusno ON Student(Sno);
 CREATE UNIQUE INDEX Coucno ON Course(Cno);
 CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);

修改索引

ALTER INDEX <旧索引名> RENAME TO <新索引名>;

将SC表的SCno索引名改为SCSno。
ALTER INDEX SCno RENAME TO SCSno;

删除索引

DROP INDEX <表名> <索引名>;

数据查询

数据查询的一般格式为：

SELECT [ALL | DISTINCT] <目标列表达式>[,<目标列表达式>]...
FROM <表名或视图名>[,<表名或视图名>...] | (< SELECT语句 >) [AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列名 1> [HAVING <条件表达式>]]
[ORDER BY <列名 2> [ASC|DESC]];

单表查询

选择表中若干列

查询全体学生的学号和姓名。

SELECT Sno,Sname
FROM Student;

查询全体学生的姓名、学号、所在系。

SELECT Sname,Sno,Sdept
FROM Student;

查询全体学生的详细记录。

SELECT *
FROM Student;

查询全体学生的姓名及出生年份。

SELECT Sname,2014-Sage
FROM Student;

查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名。并给出别名NAME,BIRTH,BIRTHDAY,DEPARTMENT表示

SELECT Sname NAME,'Year of Birth' BIRTH,
2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT
FROM Student;

选择表中的若干元组

查询选修了课程的学生学号，并去除重复的行。

SELECT DISTINCT Sno
FROM SC;

查询计算机科学系全体学生的名单。

SELECT Sname
FROM Student
WHERE Sdept='CS';

查询年龄(不)在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage (NOT) BETWEEN 20 AND 23;

查询(全不是)计算机科学系CS，数学系MA，信息系IS学生的姓名和性别。

SELECT Sname,Ssex
FROM Student
WHERE Sdept (NOT) IN('CS','MA','IS');

字符匹配

[NOT] LIKE '<匹配串>' [ESCAPE '<换码字符>']

其含义是查找指定的属性列值与<匹配串>相匹配的元组。
<匹配串>可以是一个完整的字符串，也可以是含有通配符%和_

  • %代表任意长度的字符串（可为0）
  • _代表任意单个字符

查询所有姓刘的学生的姓名、学号和性别。

SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname LIKE '刘%';

查询姓“欧阳”且全名为三个汉字的学生的姓名。

SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳_';

查询名字中第二个字为“阳”的学生的姓名和学号。

SELECT Sname,Sno
FROM Student
WHERE Sname LIKE '_阳%';

查询DB_Design课程的课程号和学分。

SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE'\';

ESCAPE''表示“\“为换码字符。

查询以”DB_“开头，且倒数第三个字符为i的课程的详细情况。

SELECT *
FROM Course
WHERE Cname LIKE 'DB\_%i__' ESCAPE'\';

多重条件查询

查询计算机科学系年龄在20岁以下的学生姓名。

SELECT Sname
FROM Student
WHERE Sdept='CS' AND Sage<20;

ORDER BY子句

查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。

SELECT Sno,Grade
FROM SC
WHERE Cno='3'
ORDER BY Grade DESC;

聚集函数

查询学生总人数。

SELECT COUNT(*)
FROM Student;

查询选修了课程的学生人数。

SELECT COUNT(DISTINCT Sno)
FROM SC;

查询选修1号课程的学生的平均成绩。

SELECT AVG(Grade)
FROM SC
WHERE Cno='1';

查询选修1号课程的学生的最高分。

SELECT MAX(Grade)
FROM SC
WHERE Cno='1';

查询学生201215012选修课程的总学分数。

SELECT SUM(Ccredit)
FROM SC,Course
WHERE Sno='201215012' 
      AND SC.Cno=Course.Cno;

聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句。

GROUP BY子句

GROUP BY子句将查询结果按某一列或多列的值分组，值相等的为一组。
分组的目的是为了细化聚集函数的作用对象。分组后，聚集函数将作用于每组，即每一组都有一个函数值。

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

SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;

该语句对查询结果按Cno的值分组，所有具有相同Cno值的元组为一组，然后对每组作用的聚集函数COUNT进行计算，以求得该组的学生人数。

查询选修了三门以上课程的学生学号。

SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3;

COUNT(*)统计元组个数。
COUNT(列名 1)统计1列中值的个数。

查询平均成绩大于等于90分的学生学号和平均成绩。

SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grad)>=90;

连接查询

等值与非等值连接查询

连接查询的WHERE子句用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：

[<表名 1>.]<列名 1><比较运算符>[<表名 2.>]<列名 2>

其中比较运算符主要有=、>、<、>=、<=、!=(或<>)等。当连接运算符为=时，称为等值连接。使用其他运算符称为非等值连接。

查询每个学生及其选修课程的情况。

SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno;

一条SQL语句可以同时完成选择和连接查询，这时WHERE子句是由连接谓词和选择谓词组成的复合条件。（连接谓词：连接不同条件的；选择谓词：条件中的判断）

查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。

SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND  /*连接谓词*/
      SC.Cno='2' AND SC.Grade>90;  /*其他限定条件*/

自身连接

外连接

多表连接

查询每个学生的学号、姓名、选修的课程名及成绩。

SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

关系数据库管理系统在执行多表连接时，通常是先进行两个表的连接操作，再将其连接结果与第三个表进行连接。

嵌套查询

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。
SQL语言支持多层嵌套查询。但是子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。

带IN的子查询

查询与”刘晨“在同一个系学习的学生。

①SELECT Sdept
FROM Student
WHERE Sname='刘晨';
结果为CS
②SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept='CS';
将第一步插入第二步的查询条件中，
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
      (SELECT Sdept
       FROM Student
       WHERE Sname='刘晨'
);

本例中，子查询的查询条件不依赖于父查询，称为不相关子查询。

带有比较运算符的子查询

找出每个学生超过他自己选修课程平均成绩的课程号。

SELECT Sno,Cno
FROM SC x
WHERE Grade>=(SELECT AVG(Grade)
              FROM SC y
              WHERE y.Sno=x.Sno);

x是SC的别名，又称为元组变量，可以用来表示SC的一个元组。内层查询是求一个学生选修课程平均成绩的，至于是哪个学生的平均成绩要看参数x.Sno的值，而该值是与父查询相关的，因此此类查询称为相关子查询。

带有ANY(SOME)或ALL谓词的子查询

image

查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。

SELECT Sname,Sage
FROM Student
WHERE Sage<ANY(SELECT Sage
               FROM Student
               WHERE Sdept='CS')
AND Sdept<>'CS';   /*注意这是父查询块中的条件*/

带有EXISTS谓词的子查询

EXISTS代表存在量词带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真或者假。
查询没有选修1号课程的学生姓名。

SELECT Sname
FROM Student
WHERE NOT EXISTS
      (SELECT *
       FROM SC
       WHERE Sno=Student.Sno AND Cno='1');

由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值，给出列名没有实际意义。

集合查询

SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。集合操作主要包括并操作UNION，交操作INTERSECT和差操作EXCEPT。参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。

查询计算机科学系的学生及年龄不大于19岁的学生。

SELECT *
FROM Student
WHERE Sdept='CS'
UNION
SELECT *
FROM Student
WHERE Sage<='19';

数据更新

插入数据

插入元组

INSERT
INTO <表名> [(<属性列 1>[,<属性列 2>]...)]
VALUES(<常量 1>[,<常量 2>]....);

将一个新学生元组（学号：201215128，姓名：陈东，性别：男，所在系：CS，年龄：18岁）插入到Student表中。

INSERT
INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES('201215128','陈东','男','CS',18);

字符串常数要用单引号括起来。

插入子查询结果

INSERT
INTO <表名> [(<属性列 1>[,<属性列 2>...])]
子查询;

对每一个系，求学生的平均年龄，并把结果存入数据库。

CREATE TABLE Dept_age
       (Sdept CHAR(15),
        Avg_age SMALLINT);

INSERT
INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage)
FROM Student
GROUP BY Sdept;

修改数据

UPDATE <表名>
SET<列名>=<表达式>[,<列名>=<表达式>]...
[WHERE <条件>];

用来修改指定表中满足WHERE子句条件的元组。如果省略WHERE子句，则表示要修改表中的所有元组。

修改某一个元组的值

将学生201215121的年龄改为22岁。

UPDATE Student
SET Sage=22
WHERE Sno='201215121';

修改多个元组的值

将所有学生的年龄增加1岁。

UPDATE Student
SET Sage=Sage+1;

带子查询的修改语句

将计算机科学系全体学生的成绩置零。

UPDATE SC
SET Grade=0
WHERE Sno IN
    (SELETE Sno
     FROM Student
     WHERE Sdept='CS'
    );

删除数据

DELETE
FROM <表名>
[WHERE <条件>];

删除一个元组的值

删除学号为201215128的学生记录。

DELETE 
FROM Student
WHERE Sno='201215128';

删除多个元组的值

删除所有学生的选课记录。

DELETE 
FROM SC;

带子查询的删除语句

删除计算机科学系所有学生的选课记录。

DELETE
FROM SC
WHERE Sno IN
      (SELECT Sno
       FROM Student
       WHERE Sdept='CS'
       );

视图

定义视图

建立视图

CREATE VIEW <视图名>[(<列名>[,<列名>]...)]
AS <子查询>
[WITH CHECK OPTION];

建立信息系学生的视图。

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS';

建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;

在定义视图时，加上了WITH CHECK OPTION子句，以后对该视图进行插入、删除、修改操作时，关系数据库管理系统会自动加上Sdept_'IS'的条件。（即子查询中的条件表达式）

建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）。

CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept='IS' AND
      Student.Sno=SC.Sno AND
      SC.Cno='1';

用带有聚集函数和GROUP BY子句的查询来定义视图，这种视图叫分组视图。

将学生的学号及平均成绩定义成一个视图。

CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;

删除视图

DROP VIEW <视图名> [CASCADE];

查询视图

在信息系学生的视图中找出年龄小于20岁的学生。

SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;

更新视图

由于视图是不实际存在的虚表，因此对视图的更新最终要转换为对基本表的操作。

将信息系学生视图IS_Student中学号为“201215122”的学生姓名改为“刘晨”。

UPDATE IS_Student
SET Sname='刘晨'
WHERE Sno='201215122';
转换后的更新语句为
UPDATE Student
SET Sname='刘晨'
WHERE Sno='201215122' AND Sdept='IS';

视图的作用

视图能够简化用户的操作。
视图使用户能以多种角度看待同一数据。
视图对重构数据库提供了一定程度的逻辑独立性。
视图能对机密数据提供安全保护。
适当利用视图可以更清晰地表达查询。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 202,529评论 5赞 475
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,015评论 2赞 379
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 149,409评论 0赞 335
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,385评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,387评论 5赞 364
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,466评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,880评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,528评论 0赞 256
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,727评论 1赞 295
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,528评论 2赞 319
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,602评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,302评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,873评论 3赞 306
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,890评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,132评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,777评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,310评论 2赞 342