|
|
4.4 数据更新
创建表的目的是为了存储和管理、查询数据，实现数据存储的前提是向表格中添加数据；实现表格的管理经常要修改、删除表格中的数据。SQL提供了数据更新功能，包括INSERT、DELETE、UPDATE分别完成对表的插入、删除和更新操作。
4.4.1插入语句INSERT
在SQL中，最常用的向表格中添加数据的方法是使用INSERT语句。但是，用INSERT语句每次只能插入一个元组。用带子查询插入语句，一个次可以插入一个或多个元组。
● 插入一个元组的语句格式：
INSERT INTO 表名[（列名[，列名]…）]
VALUES(值[，值]…..)
插入单个元组，按顺序在表名后给出表中每个列名，在VALUES后给出对应的每个列值。插入一个完整的新元组时，可省略表的列名。插入部分列值，必须在表名后给出要输入值的列名。 例1，插入单个元组，按顺序给出表中每个列值。
INSERT INTO DEPT VALUES( "CS","计算机","888", "10区" )
例2，插入一个学生的部分列值，必须在表名后给出要输入值的列名。
INSERT INTO STUDENT (sno ,sname)
VALUES( "J20045", "刘琉")
在使用INSERT语句的时候，VALUES列表中的值或表达式必须匹配表中的列数，并和相应各列数据类型兼容。如果表格中存在某些列定义为NOT NULL，那么在插入时该列必须要出现在VALUES的列表中，否则插入会失败。
● 用子查询插入多个元组值的语句格式：
INSERT INTO 表名[（列名[，列名]…）]
子查询
注意：在使用子查询的结果插入元组时，子查询的结果必须匹配待插入表中的列数，并和相应各列数据类型兼容。如果表格中存在某些列定义为NOT NULL，那么子查询的结果在该列上必须有值，否则插入会失败。 例3，建立一个新表，存放每个学生的学号、姓名和平均成绩。把子查询结果插入新表中。
CREATE TABLE savg( sno CHAR(8),sname CHAR(20), avage REAL )
INSERT INTO savg (sno,sname,avage)
( SELECT sno,sname，AVG(grade)
FROM SC，STUDENT
WHERE SC.sno = STUDENT.sno
GROUP BY sno )
例4，在表Studio中插入属性name，这些name是在表Movie中提到，但没有在表Studio中出现的studioName。
INSERT INTO Studio(name)
SELECT DISTINCT studioName
FROM Movie
WHERE studioName NOT IN
(SELECT name FROM Studio);
4.4.2更新语句UPDATE
从某种意义上来说，更新语句UPDATE比插入语句INSERT，删除语句DELETE更接近于"修改"的具体意义。当需要修改数据库表中某一列的数据的时候，使用UPDATE语句可以指定要修改的列和想要赋予的新值。通过给出查询匹配数据行的WHERE子句，还可以指定要更新的列必须符合的条件。
单个更新操作可修改指定表中满足条件的一行或多行元组中的一个或多个列值。
语句格式： UPDATE 表名
SET 列名={表达式|(子查询)} [, 列名={表达式|(子查询)}]….
[WHERE 条件表达式]
WHERE子句中的条件表达式给出被修改元组应满足的条件；
SET子句指定要修改的列和修改后的值。
例1，把 DEPT 中的计算机系的电话号码改为"9888"。
UPDATE DEPT SET deptphno = "9888" WHERE deptno = 'cs'
例2，有课程的学分都加1
UPDATE COUSE
SET credit = credit+1
例3，数据库课程的成绩提高10%。
UPDATE SC
SET grade = grade*110%
WHERE cno = "g008"
例4，将JS2001斑学生的成绩提高10%。
UPDATE SC
SET grade = grade*110%
WHERE "JS2001"=
SELECT sclass
FROM STUDENT
WHERE STUDENT.sno=SC.sno)
4.4.3删除语句DELETE
DELETE 语句从表中删除一个或多个满足条件的元组。语句格式：
DELETE FROM 表名 ［WHERE 条件表达式］
WHERE子句中的条件表达式给出被删除元组应满足的条件；若不写WHERE子句，表示删除表中的所有元组，但表的定义仍存在。
例1，只涉及单个元组，从数据库中删除某个学生李楷:
DELETE FROM STUDENT
WHERE sname = "李楷"
例2，删除操作涉及多个元组，从数据库中删除某门课程的所有元组:
DELETE FROM SC
WHERE cno = "E001"
4.5 数据查询语句SELECT
SQL语言的核心是表达查询的SELECT 语句。 SELECT语句的基本结构是由SELECT -FROM-WHERE组成的查询块，实现对数据的查询操作。
4.5.1 简单查询
简单查询是指实现对单表的选择或投影操作，查询满足条件的全部列或部分列。语句格式:
SELECT <目标列名字表>
FROM <关系表名>
WHERE <查询条件表达式>
参数说明：
SELECT子句中的目标列名字表：指出查询结果中的列名；
FROM子句中的关系表：指定查询有关的表名；
WHERE子句中的条件表达式：给出选择元组应满足的条件。
1. 单表查询操作
(1) 查询指定的列和全部的列
例1，查询指定的列，列出所有学生的名字和学号:
SELECT sname, sno FROM STUDENT
例2，查询全部的列，列出计算机系所有学生的信息:
SELECT *
FROM STUDENT
WHERE sclass = "JS%"
2. SELECT语句的基本格式：
SELECT [UNIQUE/DISTINCT]<目标列名字>
FROM 表名[，表名]….
[ WHERE 条件表达式] [ GROUP BY 分组的列名 ]
[ HAVING 对组再选择的条件表达式] [ORDER BY 排序列名[ASC/DESC]]
参数说明：
GROUP BY子句：指出用于把查询结果分组的列名，该列属性值相同的元组被划分为一组。
HAVING子句：与GROUP BY子句配合使用, HAVING子句给出对分组进行再选择的条件表达式。
ORDER BY字句：指定对查询结果进行排序(升序/降序)的列名。该列必须出现在结果集中。ASC为升序，DESC为降序，缺省为升序。
UNIQUE/DISTINCT：指名查询结果中去掉重复行。
(2) 没有重复值的查询
例如：从表COUSE中查询当前任课教师的编号（名单），去掉重复值。
select distinct ctno from couse
输出结果：
ctno
T001
T020
T012
(3) 查询结果排序
例如：从表COUSE中查询当前认课教师的编号（名单），去掉重复值，结果排序。
select distinct ctno from couse order by ctno
输出结果：
ctno
T001
T012
T020
(4) 指定别名查询
例如：假如希望得到查询的结果具有与原来的属性不同的列标题名，可在SELECT语句中指定别名来实现。
select distinct ctno 教师编号 from couse order by ctno
输出结果：
ctno
T001
T020
T012
3. 查询中使用表达式运算符
(1) SQL中使用的表达式运算符包括：
◆ 算术运算符：+,-,*,/
◆ 比较运算符：=，<>，>=，>，<，<=
◆ 逻辑运算符：NOT，AND，OR
◆ 测试空值：IS NULL，IS NOT NULL
◆ 集合运算符：IN，NOT IN，ANY，ALL
◆ 模糊查询： LIKE(_ 或 %), NOT LIKE
◆ 判断列值是否在指定的区间内：
BETWEEN … AND …
NOT BETWEEN … AND …
(2) 算术运算符和比较运算符的例子
例1：搜索pubs数据库中的title表，返回书的价格打了8折后仍然大于12美元的书名、书的类型和价格。
USE pubs
Go
SELECT title_id , type , price
FROM titles
WHERE price * 0.8 >12
Go
(3) 使用集合运算符的例子灵活的使用IN,NOT IN,ANY,ALL这些关键字，可以用简洁的语言实现较为复杂的查询，同时整个程序的可读性也会变得更好。
例2：
Use pubs
Go
SELECT au_id, au_lname, au_fname
FROM authors
WHERE state IN ('CA','KS','MI','IN')
Go
例3，查询籍贯是北京、天津和上海来的学生：
SELECT *FROM student
WHERE 籍贯 IN ('北京'，'天津'，'上海')ORDER BY 籍贯
例4，查询年龄大于35岁的学生：
SELECT *FROM student
WHERE 年龄 >= 35
(4) 测试空值的查询的例子使用IS操作符测试空值的例子，例如，从STUDENT表中查找学生电话是空值的学生名字和班级号码： SELECT sname, sclass
FROM STUDENT
WHERE sphno IS NULL;
输出结果如下：
sname sclass
王 者 JS2001
赵 良 DZ2001
(5) 使用逻辑运算符的例子查询所有在美国加利福尼亚州的出版社
Use pubs
Go
SELECT pub_id, pub_name, city, state, country
FROM publishers
WHERE country='USA' and state='CA'
Go
(6) 进行模糊查询的例子
在实际的应用中，如果无法给出精确的例子，只能根据较为模糊的情况来查询数据，比如：只知道数据中含有某几个特定的字符，在这种情况下，SQL提供了LIKE子句和通配符进行模糊查询。
通配符的使用：
%：表示从0到n个任意字符。
_：表示单个的任意字符。
[ ]：表示方括号里面列出的任意一个字符。
[^]：表示任意一个没有在方括号里面列出的字符
例1，查询所有以D开头的作家的名字
use pubs
go select au_lname+','+au_fname from authors
where au_fname like'D%'
go
+','+ --------表示在lname和fname之间加一个逗号。
输出结果：
Straight, Dean
Stringer,Dirk
例2，查询电话号码本中含有5737的电话号码：
SELECT *FROM calltable
WHERE callno LIKE '%5735%' ;
例3，查询电话号码本中最后一位数是8的电话号码：
SELECT *FROM calltable
WHERE callno LIKE '%8' ;
例4，查询所有满足au_id的前两个字母为'72'，第四个字母为'－'的作家的姓名和电话号码：
Use pubs
Go
SELECT au_lname ,au_fname, phone, au_id
FROM authors
WHERE au_id LIKE'72_-%'
Go
如果在LIKE表达式中包含字符％或_，可以采用加上换码字符的方法解决。
例5，下面的例子将匹配所有以'％'开始并以'％'结束的任意字符串：
s LIKE 'x%%x%' ESCAPE 'x'ESCAPE 'x' 声明：x作为'x%%x%'的换码字符，x%指的是单独的字符％，x只起标记作用，不再表示字符。
例6，在SQL中用两个连续的单引号"'"表示一个单独的单引号，下面的例子表示电影名中含有's的电影。
Title LIKE '%''s%'(7) 指定范围查询的例子例1，查询年龄在20至30之间的学生：
SELECT *FROM student
WHERE 年龄 BETWEEN 20 AND 30 ;
例2，查询年龄不在20至30之间的学生：
SELECT *FROM student
WHERE 年龄 NOT BETWEEN 20 AND 30 ;
4. SQL中表达式使用的聚合函数
(1) SQL允许对列值进行统计，也可以按给定的条件对关系元组进行分组，再对每个组进行统计操作。
◆ COUNT([DISTINCT∣ALL ]<列名>)：统计某个列中值的个数（计数）
◆ COUNT(([DISTINCT∣ALL]*)：统计满足条件的元组个数。
◆ SUM([DISTINCT∣ALL]<列名>)：对某个列的值求和
◆ MIN ([DISTINCT∣ALL]<列名>)：求某个列的最小值
◆ MAX ([DISTINCT∣ALL]<列名>)：求某个列的最大值
◆ AVG ([DISTINCT∣ALL]<列名>)：求某个列的平均值
其中：
DISTINCT：表示计算时要取消指定列中的重复值。
ALL： 指所有的值。缺省为ALL
.
(2) 使用聚合函数查询，统计某个列中值的个数
例如1，查询学生的总人数，
SELECT COUNT(*)
FROM student；
例如2，查询选修了课程的学生总人数，
SELECT COUNT(DISTINCT sno)
FROM sc；
例2：求每个班级男生的人数，必须按班号分组，而后按班统计学生人数
select 班号 count(*) from student where 性别='男'group by 班号
输出结果：
班号， count(*)
js9901 21
js9902 22
js9903 23
只有出现在GROUP By子句中的属性才能与聚合操作并列出现在SELECT子句中。
(3) 使用聚合函数查询，求列的最大最小值
例1 :列出所有学生的最小和最大年龄
select min(sage),max(sage)from student
输出结果：
min(sage) 20 max(sage) 25
(4) 使用聚合函数查询，查询结果分组
例2:统计各科成绩的最低、最高分和平均成绩 。
select cno,min(grade),max(grade),avg(grade) from sc group by cno
输出结果：
cno min(grade) max(grade) avg(grade)
G006 60 92 75
G007 65 90 78
G006 60 94 72
(5) 聚合查询示例，对列的值进行求和
例如，查询学分不到20分的学生的学号和学分总数。
SELECT 学号，SUM(credit)
FROM SC，COURSE
WHERE grade >= 60 AND grade NOT NULL
AND SC.cno = COURSE.cno
GROUP BY 学号
HAVING SUM(credit) < 20 |
|
|手机版|小黑屋|网站帮助|职业IT人-IT人生活圈
( 粤ICP备12053935号-1 )|网站地图
IP卡
狗仔卡
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
抢沙发
千斤顶
显身卡
楼主
