SQL编程,创建表,数据查询
总图
modify与change的区别:
modify:更多的是修改字段的数据类型、约束
change:更多的是修改字段名称
1.关系数据库标准语言SQL( structured Query Language)
SQL是独立的语言
能够独立地用于联机交互的使用方式
SQL又是嵌入式语言
SQL能够嵌入到高级语言(例如C,C++,Java)程序中,供程序员设计程序时使用
基本表
本身独立存在的表
SQL中一个关系就对应一个基本表
一个(或多个)基本表对应一个存储文件
一个表可以带若干索引
存储文件
逻辑结构组成了关系数据库的内模式
物理结构是任意的,对用户透明
视图
从一个或几个基本表导出的表
数据库中只存放视图的定义而不存放视图对应的数据
视图是一个虚表
用户可以在视图上再定义视图
2. 数据定义(CREATE)
定义模式
定义模式实际上定义了一个命名空间
在这个空间中可以定义该模式包含的数据库对象,例如基本表、视图、索引等。
定义表
表中属性的数据类型
模式与表
修改基本表
[例8]向Student表增加“入学时间”列,其数据类型为日期型。
ALTER TABLE Student ADD S_entrance DATE;
不论基本表中原来是否已有数据,新增加的列一律为空值。
[例9]将年龄的数据类型由字符型(假设原来的数据类型是字符型)改为整数。
ALTER TABLE Student ALTER COLUMN Sage INT;
[例10]增加课程名称必须取唯一值的约束条件。
ALTER TABLE Course ADD UNIQUE(Cname);
删除基本表
DROP TABLE <表名>[RESTRICT| CASCADE];
RESTRICT:删除表是有限制的。
欲删除的基本表不能被其他表的约束所引用
如果存在依赖该表的对象,则此表不能被删除
CASCADE:删除该表没有限制。
在删除基本表的同时,相关的依赖对象一起删除
[例11] 删除Student表
DROP TABLE Student CASCADE ;
基本表定义被删除,数据被删除
表上建立的索引、视图、触发器等一般也将被删除
[例12]若表上建有视图,选择RESTRICT时表不能删除
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept=‘IS’;
DROP TABLE Student RESTRICT;
–ERROR: cannot drop table Student because other
objects depend on it
[例12]如果选择CASCADE时可以删除表,视图也自动被删除
DROP TABLE Student CASCADE;
–NOTICE: drop cascades to view IS_Student
SELECT * FROM IS_Student;
–ERROR: relation " IS_Student " does not exist
mysql表中的主键
数据库的单个数据表中只能有一个主键。
数据库主键,指的是一个列或多列的组合,其值能唯一地标识表中的每一行,通过它可强制表的实体完整性。主键主要是用与其他表的外键关联,以及本记录的修改与删除。
1、数据库的每张表只能有一个主键,不可能有多个主键。
2、所谓的一张表多个主键,我们称之为联合主键。
注:联合主键:就是用多个字段一起作为一张表的主键。
3、主键的主键的作用是保证数据的唯一性和完整性,同时通过主键检索表能够增加检索速度。
3. 数据查询(SELECT)
3.1 单表查询
选择表中的若干列
[例1] 查询全体学生的学号与姓名。
SELECT Sno,Sname
FROM Student;
[例3] 查询全体学生的详细记录。
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;
或
SELECT *
FROM Student;
[例5] 查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名
SELECT Sname,‘Year of Birth: ',2004-Sage,
ISLOWER(Sdept)
FROM Student;
使用列别名改变查询结果的列标题:
SELECT Sname NAME,'Year of Birth: ’ BIRTH,
2000-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT
FROM Student;
选择表中的若干元组
- 消除取值重复的行
如果没有指定DISTINCT关键词,则缺省为ALL
[例6] 查询选修了课程的学生学号。
SELECT Sno FROM SC;
等价于:
SELECT ALL Sno FROM SC;
执行上面的SELECT语句后,结果为:
指定DISTINCT关键词,去掉表中重复的行
SELECT DISTINCT Sno
FROM SC;
[例7] 查询计算机科学系全体学生的名单。
SELECT Sname
FROM Student
==WHERE Sdept=‘CS’; ==
[例8] 查询所有年龄在20岁以下的学生姓名及其年龄。
SELECT Sname,Sage
FROM Student
WHERE Sage < 20;
[例9] 查询考试成绩有不及格的学生的学号。
SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;
谓词: BETWEEN … AND …
NOT BETWEEN … AND …
[例10] 查询年龄在20~23岁(包括20岁和23岁)之间的学生的
姓名、系别和年龄
SELECT Sname,Sdept,Sage
FROM Student
**WHERE Sage BETWEEN 20 AND 23; **
[例11] 查询年龄不在20~23岁之间的学生姓名、系别和年龄
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;
谓词:IN <值表>, NOT IN <值表>
[例12]查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
SELECT Sname,Ssex
FROM Student
** WHERE Sdept IN ( ‘IS’,‘MA’,‘CS’ );**
[例13]查询既不是信息系、数学系,也不是计算机科学系的学生的姓名和性别。
SELECT Sname,Ssex
FROM Student
WHERE Sdept NOT IN ( ‘IS’,‘MA’,‘CS’ );
** 谓词: [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’] **
匹配串为固定字符串
[例14] 查询学号为200215121的学生的详细情况。
SELECT *
FROM Student
WHERE Sno LIKE ‘200215121’;
等价于:
SELECT *
FROM Student
WHERE Sno = ’ 200215121 ';
2) 匹配串为含通配符的字符串
[例15] 查询所有姓刘学生的姓名、学号和性别。
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname LIKE ‘刘%’;
[例16] 查询姓"欧阳"且全名为三个汉字的学生的姓名。
SELECT Sname
FROM Student
WHERE Sname LIKE ‘欧阳__’;
[例17] 查询名字中第2个字为"阳"字的学生的姓名和学号。
SELECT Sname,Sno
FROM Student
WHERE Sname LIKE ‘__阳%’;
[例18] 查询所有不姓刘的学生姓名。
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname NOT LIKE ‘刘%’;
多重条件查询
逻辑运算符:AND和 OR来联结多个查询条件
AND的优先级高于OR
可以用括号改变优先级
SELECT Sname
FROM Student
WHERE Sdept= ‘CS’ AND Sage<20;
[例12] 查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
SELECT Sname,Ssex
FROM Student
WHERE Sdept IN ( ‘IS’,‘MA’,‘CS’ )
可改写为:
SELECT Sname,Ssex
FROM Student
WHERE Sdept= ’ IS ’ OR Sdept= ’ MA’ OR Sdept= ’ CS ';
ORDER BY字句
ORDER BY子句
可以按一个或多个属性列排序
升序:ASC;降序:DESC;缺省值为升序
当排序列含空值时
ASC:排序列为空值的元组最后显示
DESC:排序列为空值的元组最先显示
[例24] 查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。
SELECT Sno,Grade
FROM SC
WHERE Cno= ’ 3 ’
ORDER BY Grade DESC;
[例25] 查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
SELECT *
FROM Student
ORDER BY Sdept,Sage DESC;
聚集函数(COUNT,AVG,MAX,MIN)
聚集函数:
计数
COUNT(*)
COUNT([DISTINCT|ALL] <列名>)
计算总和
SUM([DISTINCT|ALL] <列名>)
计算平均值
AVG([DISTINCT|ALL] <列名>)
最大最小值
MAX([DISTINCT|ALL] <列名>)
MIN([DISTINCT|ALL] <列名>)
[例26] 查询学生总人数。
SELECT COUNT(*)
FROM Student;
[例27] 查询选修了课程的学生人数。
SELECT COUNT(DISTINCT Sno)
FROM SC;
[例28] 计算1号课程的学生平均成绩。
SELECT AVG(Grade)
FROM SC
WHERE Cno= ' 1 ';
[例29] 查询选修1号课程的学生最高分数。
SELECT MAX(Grade)
FROM SC
WHER Cno= ‘ 1 ’;
[例30]查询学生200215012选修课程的总学分数。
SELECT SUM(Ccredit)
FROM SC, Course
WHER Sno=‘200215012’ AND SC.Cno=Course.Cno;
GROUP BY字句
GROUP BY子句分组:
细化聚集函数的作用对象
未对查询结果分组,聚集函数将作用于整个查询结果
对查询结果分组后,聚集函数将分别作用于每个组
作用对象是查询的中间结果表
按指定的一列或多列值分组,值相等的为一组
[例32] 查询选修了3门以上课程的学生学号。
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) >3;
HAVING短语与WHERE子句的区别:
HAVING短语与WHERE子句的区别:
作用对象不同
WHERE子句作用于基表或视图,从中选择满足条件的元组
HAVING短语作用于组,从中选择满足条件的组。 组是经过GROUP BY之后的语句
3.2连接查询
等值连接:连接运算符为=
[例33] 查询每个学生及其选修课程的情况
SELECT Student.,SC.
FROM Student,SC
WHERE Student.Sno = SC.Sno;
[例34] 对[例33]用自然连接完成。
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno = SC.Sno;
自身连接
自身连接:一个表与其自己进行连接
需要给表起别名以示区别
由于所有属性名都是同名属性,因此必须使用别名前缀
示例表
[例35]查询每一门课的间接先修课(即先修课的先修课)
SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno = SECOND.Cno;
查询结果
外连接
外连接与普通连接的区别
普通连接操作只输出满足连接条件的元组
外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出
[例 36] 改写[例33]
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno);
这里左外连接和右外连接不懂
左外连接
列出左边关系(如本例Student)中所有的元组
右外连接
列出右边关系中所有的元组
复合条件连接
复合条件连接:WHERE子句中含多个连接条件
[例37]查询选修2号课程且成绩在90分以上的所有学生
SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno AND
/* 连接谓词*/
SC.Cno= ‘2’ AND SC.Grade > 90;
/* 其他限定条件 */
[例38]查询每个学生的学号、姓名、选修的课程名及成绩
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course /多表连接/
WHERE Student.Sno = SC.Sno
and SC.Cno = Course.Cno;
3.3 嵌套查询
嵌套查询概述
一个SELECT-FROM-WHERE语句称为一个查询块
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
子查询的限制
不能使用ORDER BY子句
层层嵌套方式反映了 SQL语言的结构化
有些嵌套查询可以用连接运算替代
不相关子查询:
子查询的查询条件不依赖于父查询
由里向外逐层处理。即每个子查询在上一级查询处理之前求解,子查询的结果用于建立其父查询的查找条件。
带IN谓语的子查询
[例39] 查询与“刘晨”在同一个系学习的学生。
将第一步查询嵌入到第二步查询的条件中
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname= ‘ 刘晨 ’);
此查询为不相关子查询。
带有比较运算符的子查询
当能确切知道内层查询返回单值时,可用比较运算符(>,<,=,>=,<=,!=或< >)
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept = //可以使用等于代替IN,子查询一定要跟在比较符之后
(SELECT Sdept
FROM Student
WHERE Sname= ‘刘晨’);
带有ANY(SOME)或ALL谓词的子查询
谓词语义
ANY:任意一个值
ALL:所有值
< ANY 小于子查询结果中的某个值
< ALL 小于子查询结果中的所有值
[例42] 查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄
SELECT Sname,Sage
FROM Student
WHERE Sage < ANY (SELECT Sage
FROM Student
WHERE Sdept= ’ CS ')
AND Sdept <> ‘CS ’ ; /*父查询块中的条件 */
用聚集函数实现[例42]
SELECT Sname,Sage
FROM Student
WHERE Sage <
(SELECT MAX(Sage)
FROM Student
WHERE Sdept= ‘CS ')
AND Sdept <> ' CS ’;
带有EXISTS谓词的子查询
- EXISTS谓词
存在量词$
带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”。
若内层查询结果非空,则外层的WHERE子句返回真值
若内层查询结果为空,则外层的WHERE子句返回假值
由EXISTS引出的子查询,其目标列表达式通常都用* ,因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义 - NOT EXISTS谓词
若内层查询结果非空,则外层的WHERE子句返回假值
若内层查询结果为空,则外层的WHERE子句返回真值\
[例44]查询所有选修了1号课程的学生姓名。
思路分析:
本查询涉及Student和SC关系
在Student中依次取每个元组的Sno值,用此值去检查SC关系
若SC中存在这样的元组,其Sno值等于此Student.Sno值,并且其Cno= ‘1’,则取此Student.Sname送入结果关系
用嵌套查询
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno= ’ 1 ');
用连接运算
SELECT Sname
FROM Student, SC
WHERE Student.Sno=SC.Sno AND SC.Cno= ‘1’;
3.4集合查询(UNION)
集合操作的种类
并操作UNION
交操作INTERSECT
差操作EXCEPT
参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同
[例48] 查询计算机科学系的学生及年龄不大于19岁的学生。
方法一:
SELECT *
FROM Student
WHERE Sdept= ‘CS’
UNION
SELECT *
FROM Student
WHERE Sage<=19;
方法二:
SELECT DISTINCT *
FROM Student
WHERE Sdept= ‘CS’ OR Sage<=19;
UNION:将多个查询结果合并起来时,系统自动去掉重复元组。
UNION ALL:将多个查询结果合并起来时,保留重复元组
3.5select语句的一般形式
having是在分组后对数据进行过滤. where是在分组前对数据进行过滤. having后面可以使用聚合函数. where后面不可以使用聚合.
4. 数据更新(INSERT, UPDATE,DELETE)
4.1插入数据([INSERT INFO …VALUES…])
两种插入数据方式
- 插入元组
- 插入子查询结果
可以一次插入多个元组
语句格式
INSERT
INTO <表名> [(<属性列1>[,<属性列2 >…)]
VALUES (<常量1> [,<常量2>] … )
功能
将新元组插入指定表中
[例1] 将一个新学生元组(学号:200215128;姓名:陈冬;性别:男;所在系:IS;年龄:18岁)插入到Student表中。
INSERT
INTO Student (Sno,Sname,Ssex,Sdept,Sage)
VALUES ('200215128','陈冬','男','IS',18);
[例2] 将学生张成民的信息插入到Student表中。
INSERT
INTO Student
VALUES (‘200215126’, ‘张成民’, ‘男’,18,'CS');
[例3] 插入一条选课记录( ‘200215128’,'1 ')。
INSERT
INTO SC(Sno,Cno)
VALUES (‘ 200215128 ’,‘ 1 ’);
RDBMS将在新插入记录的Grade列上自动地赋空值。
或者:
INSERT
INTO SC
VALUES (' 200215128 ',' 1 ',NULL);
插入子查询结果
[例4] 对每一个系,求学生的平均年龄,并把结果存入数据库。
第一步:建表
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;
4.2 修改数据([UPDATE 表名 SET…])
语句格式
UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>]…
[WHERE <条件>];
功能
修改指定表中满足WHERE子句条件的元组
三种修改方式
- 修改某一个元组的值
- 修改多个元组的值
- 带子查询的修改语句
[例5] 将学生200215121的年龄改为22岁
UPDATE Student
SET Sage=22
WHERE Sno=' 200215121 ';
[例6] 将所有学生的年龄增加1岁
UPDATE Student
SET Sage= Sage+1;
[例7] 将计算机科学系全体学生的成绩置零。
UPDATE SC
SET Grade=0
WHERE 'CS'=
(SELETE Sdept
FROM Student
WHERE Student.Sno = SC.Sno);
4.3 删除数据(DELETE)
语句格式
DELETE
FROM <表名>
[WHERE <条件>];
功能
删除指定表中满足WHERE子句条件的元组
WHERE子句
指定要删除的元组
缺省表示要删除表中的全部元组,表的定义仍在字典中
三种删除方式
- 删除某一个元组的值
- 删除多个元组的值
- 带子查询的删除语句
[例8] 删除学号为200215128的学生记录。
DELETE
FROM Student
WHERE Sno= 200215128 ';
[例10] 删除计算机科学系所有学生的选课记录。
DELETE
FROM SC
WHERE 'CS'=
(SELETE Sdept
FROM Student
WHERE Student.Sno=SC.Sno);
5 视图(VIEW)
视图的特点
- 虚表,是从一个或几个基本表(或视图)导出的表
- 只存放视图的定义,不存放视图对应的数据
- 基表中的数据发生变化,从视图中查询出的数据也随之改变
基于视图的操作
- 查询
- 删除
- 受限更新
- 定义基于该视图的新视图
5.1定义视图(CREATE VIEW)
[例1] 建立信息系学生的视图。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS';
[例2]建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生 。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS'
WITH CHECK OPTION;
对IS_Student视图的更新操作:
修改操作:自动加上Sdept= ‘IS’的条件
删除操作:自动加上Sdept= ‘IS’的条件
插入操作:自动检查Sdept属性值是否为’IS’
如果不是,则拒绝该插入操作
如果没有提供Sdept属性值,则自动定义Sdept为’IS’
[例3] 删除视图BT_S: DROP VIEW BT_S;
带表达式的视图
[例5] 定义一个反映学生出生年份的视图。
CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2000-Sage
FROM Student;
5.2查询视图
[例9] 在信息系学生的视图中找出年龄小于20岁的学生。
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;
IS_Student视图的定义 (参见视图定义例1)
5.3更新视图
[例12] 将信息系学生视图IS_Student中学号200215122的学生姓名改为“刘辰”。
UPDATE IS_Student
SET Sname= '刘辰'
WHERE Sno= ' 200215122 ';
转换后的语句:
UPDATE Student
SET Sname= '刘辰'
WHERE Sno= ' 200215122 ' AND Sdept= 'IS';
5.4视图的作用
- 视图能够简化用户的操作
- 视图使用户能以多种角度看待同一数据
- 视图对重构数据库提供了一定程度的逻辑独立性
- 视图能够对机密数据提供安全保护
- 适当的利用视图可以更清晰的表达查询