SQL语句
SQL的定义功能
1.表结构的定义
CREAT TABLE 表名
(
属性名 数据类型 PRIMARY KEY,
属性名 数据类型,
属性名 数据类型,
PRIMARY KEY( 属性名,属性名)/*关键字由多个属性构成时使用该方法*/
);
NOT NULL UNIQUE/*等同于主码*/
/*外码的定义*/
CREAT TABLE 表名
(
属性名 数据类型 REFERENCES 表名 (属性名),
属性名 数据类型,
属性名 数据类型,
FOREIGN KEY (属性名) REFERENCES 表名 (属性名)
);
/*默认值的定义*/
CREAT TABLE 表名
(
属性名 数据类型 DEFAULT 默认值;
属性名 数据类型 DEFAULT 默认值;
属性名 数据类型 DEFAULT 默认值;
);
2.基本表的修改和删除
1.修改基本表
/*给表增加属性*/、
ALTER TABLE 学生 ADD COLUMN 年龄 SMALLINT; --给学生表增加年龄属性
/*删除表中的属性*/
ALTER TABLE 学生 DROP COLUMN 年龄; --在学生表中删除年龄属性
/*修改列定义,包括修改列名和数据类型*/
ALTER TABLE 课程 ALTER COLUMN 课程名称 CHAR(20); --修改课程表中的课程名称属性的长度为20位
2.删除基本表
DROP TABLE 表名 RESTRICT || CASCADE;
/*RESTRICT 表示如果有视图或者约束条件涉及要删除的表时,就禁止DBMS执行该命令,CASCADE表示将该表和其涉及的对象一起删除*/
3.索引的建立与删除
CREATE UNIQUE || CLUSTER INDEX 索引名 ON 表名 (属性名);
/*1. 索引可以建立在该表的一列或者多列上,各列名之间用逗号分隔,这种由两列或者多列属性组成的索引称为复合索引。
2. UNIQUE 表明此索引的每个索引值只对应唯一的数据记录
3. CLUSTER 表明建立聚簇索引,聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织*/
DROP INDEX 索引名; --删除索引
数据查询
1.单表查询
1. SQL中的投影
SELECT 属性名
FROM 表名;
/*通配符 * 代表所有的属性*/
/*SELECT 后面不仅可以是表中已经有的列名,也可以是对列进行运算后得到的表中不存在的信息,通过运算得到的列,系统你都会自动地赋给它一个列名,但是这样的列名晦涩难懂,可以通过使用 AS 语句来给列起别名 */
SELECT 学号 AS abc
2.SQL中的选择运算
WHERE 条件;
WHERE 出生年份 BETWEEN 1996 AND 2000;
WHERE 学院 IN ('信电','计科'); ------查找属性值在给定集合的元组 (NOT IN 与之相反)
/*字符匹配可用LIKE*/
WHERE 姓名 LIKE '王%',/*查姓王的*/
WHERE 姓名 LIKE '_小',/*查第二个字是小的人*/-----通配符"%"代表任意长度的字符串包括0;通配符 "_"用来代表任何单个字符。
/*如果对于通配符只需要它表示本来的字符意思,不需要执行通配符的功能,可用ESCAPE'\' 对通配符进行转义,紧跟在'\'后面的通配符变为普通的字符*/
WHERE 课程名 LIKE 'DB\_Design' ESCAPE '\ ';
/*空值*/
WHERE 成绩 IS NULL; ----没成绩的,IS NOT NULL相反;
3.对查询结果进行分组
GROUP BY 子句可以将查询结果的各行,按照一列或者多列取值相等的原则进行分组,对查询结果分组的目的是为了细化集函数的作用对象。
SELECT 课程号,COUNT(学号) AS 选课人数
FROM 学习
GROUP BY 课程号;
HAVING 条件 /*查询结果为满足条件的组*/
/*WHERE 语句作用对象是基本表或者视图,从中选出满足条件的元组
HAVING 语句作用对象是组,从中选出满足条件的组*/
4.
COUNT (*): 统计元组个数
COUNT (列名): 统计该列中值的个数
SUM (列名): 计算一列值的总和(必须是数值型)
AVG (列名):计算一列值的平均值
MAX (列名): 求一列中的最大值
MIN (列名):求一列中的最小值
/*如果指定DISTINCT 短语,则表示在计算时要取消指定列的重复值,如果没指定或者指定ALL,则不去掉重复值*/
5. 使用 ORDER BY短语对查询结果排序
ORDER BY ASC 升序 (从上往下看,数值越来越大)
ORDER BY DESC 降序 (从上往下看,数值越来越小)
2.连接查询
1.等值连接非等值连接
表名.列名 比较运算符 表名. 列名
/*
1.连接谓词中的列名称为连接字段,连接条件中的各连接字段类型必须是可比的但不必相同。
2.DBMS执行连接操作的过程,首先在表1中找到第一个远足,然后从头开始顺序扫描或者索引扫描表2,查找满足连接条件的元组,每找到一个满足条件的元组,就将表1中的第一个元组与该元组连接起来,形成结果表中的一个元组.............
*/
2.自身连接查询
3.外链接查询
/*左外连接*/
---theta 方式
SELECT 姓名,课程号,成绩
FROM 学生,学习
WHERE 学习.学号(+)=学生.学号 ------等号那边是开口方向
---ANSI方式
SELECT 姓名,课程号,成绩
FROM 学生 (开口所向的那个表)
LEFT OUTER JOIN 学习 ON 学生.学号=学习.学号;
/*右外连接*/
---theta方式
SELECT 姓名,课程号,成绩
FROM 学生,学习
WHERE 学生.学号=(+)学习.学号
---ANSI方式
SELECT 姓名,课程号,成绩
FROM 学生
RIGHT OUTER JOIN 学习 ON 学习.学号= 学生.学号
4.复合条件连接
5.集合运算连接查询
并:UNION
交:INTERSECT
差:EXCEPT
( SELECT 学号
FROM 学习
WHERE 课程号= '180101')
UNION
( SELECT 学号
FROM 学习
WHERE 课程号 ='080102'
)
UNION ,INTERSECT,EXCEPT 都自动去除重复;如果想保留全部,UNION ALL.
3.嵌套查询
分类: 1. 不相关子查询:子查询独立于上层的父查询,每个子查询在其上一级查询处理之前可以完成求解,子查询的结果用于建立其父查询的查找条件
2. 相关子查询:子查询依赖于父查询,最内层子查询的执行需要用到上层父查询中的某些属性值,因此最内层的子查询不能独立于父查询先完成。
1. 带有IN 谓词的子查询
父查询和子查询之间用IN 进行连接,判断某个属性列值是否在子查询的结果中
2. 带有比较运算符的子查询 (子查询返回单值时可用)
3. 带有ANY 或ALL谓词的子查询 ANY 代表一些,某个;ALL 代表全部。
4. 带有EXISTS谓词的子查询
EXISTS 代表存在量词,带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值true,或逻辑假值false
SELECT 学号,姓名
FROM 学生
WHERE EXISTS
( SELECT *
FROM 学习
WHERE 学生.学号 = 学习.学号
AND 学习.课程号 = '180101'
);
/*执行顺序:从学生表中依次取出每个元组的学号,用此值去检测学习表,如果学习表中存在这样的元组,其学号与此学生.学号相同,并且该学生学习的课程的课程号是1080101 ,则取此学生的学号和姓名送入结果表中*/
与EXISTS 相反的是NOT EXISTS ,当内层查询的结果为空,则NOT EXISTS 返回结果为真,外层WHERE 字句返回真值,否自返回假值。
!!! 除法问题
除法问题必定涉及一个集合包含另一个集合的概念,首先,把这两个集合找出来。集合 A 包含集合 B 则说明,B-A为空。
例题:查询至少选修了091501号学生选修的全部课程的学生学号。
集合A 某个学生选修的课程号;集合B 学号为091501 的学生选修的课程的课程号
A:
SELECT 课程号
FROM 学习
WHERE 学习.学号 = 'xxxx' (某个学生的学号)
B:
SELECT 课程号
FROM 学习
WHERE 学习.学号 = '091501';
然后:B-A 为空
NOT EXISTS
( (SELECT 课程号
FROM 学习 AS First
WHERE First.学号 = '091501'
)
EXCEPT
( SELECT 课程号
FROM 学习 AS Second
WHERE Second.学号 = 'xxxx');
)
最后:
SELECT 学号
FROM 学生
WHERE NOT EXISTS
( (SELECT 课程号
FROM 学习 AS First
WHERE First.学号 = '091501'
)
EXCEPT
( SELECT 课程号
FROM 学习 AS Second
WHERE Second.学号 = 学生.学号);
)
/*从学生表中依次取出每个学生的学号,送入内层查询中进行检验*/
另一种:
SELECT 学号
FROM 学生
WHERE NOT EXISTS
(SELECT *
FROM 学习 AS First
WHERE First.学号 = '091501' AND NOT EXISTS
( SELECT *
FROM 学习 AS Second
WHERE Second.学号 = 学生.学号
AND First.课程号 = Second.课程号
));
数据查询
1.插入数据
1.插入单个元组
INSERT
INTO 表名 (属性列,属性列,属性列)
VALUES (常量,常量,常量);
例如: INSERT
INTO 学生
VALUES ('091530','夏雨','男','海南','1999','计算机')
2.插入子查询结果
INSERT
INTO 表名 (属性列,属性列)
子查询;
/*子查询的SELECT 的属性列应该与 INTO 的属性列对应*/
3.删除数据
DELETE
FROM 表名
WHERE 条件;
/*省略WHERE语句时,删除所有的元组值*/
4.修改数据
UPDATE 表名
SET 列名 = 表达式
WHERE 条件;
修改指定表中满足WHERE条件的元组的某些属性的值。
如: UPDATE 学生
SET 籍贯 = '江苏'
WHERE 学号 = '091611';
视图
1.建立视图
CREATE VIEW 视图名(列名)
AS 子查询
/*注意,视图的列名或者全部省略,或者全部给出,没有其他选择。下列三种情况必须指定组成视图的所有列名
1.某个目标列是奇函数或列表达式
2.多表连接时选出了几个同名的列作为视图的字段
3.需要在视图中为某个列启用新的更合适的名字
*/
例如:
CREATE VIEW CS_VIEW
AS SELECT *
FROM 学生
WHERE 学院 = '计算机';
/*
1.视图可以建立在多个基本表上
2.视图可以建立在视图上
*/
/*
定义视图时只是把视图的定义放入数据字典,并不执行子查询语句。只有在运行视图时,才会得到数据;不运行时视图只是一个表的模式的定义
*/
2.删除视图
DROP VIEW 视图名
/*一个视图被删除后,由它导出的其他视图也将失效,用户应该使用DROP VIEW 语句将他们一一删除*/
3.查询视图
DBMS在查询视图时,首先先对其有效性进行检查,看其涉及的视图或者基本表是否在数据库中存在,如果存在,则从数据字典中取出查询涉及的视图的定义,把定义中的子查询和用户对视图的查询结合起来,转化成对基本表的查询,然后执行这个修正过的查询。
4.视图更新
插入(INSERT),删除(DELETE),修改(UPDATE).
一个视图是从单表导出的,并且只是去掉了某些行和列(不包括关键字),称这类视图为行列子集视图,是可更新的视图。
数据控制
1.授权
GRANT 权限 ,权限
ON 对象类型 对象名
TO 用户,用户
/*语义为:将对指定操作对象的指定操作权限授予指定的用户*/
例如:
GRANT SELECT ON TABLE 学生 TO User1; ---- 把对学生表的查询权限授予用户1;
GRANT ALL PRIVILEGES ON TABLE 学生,课程 TO User2,User3; ---把对学生,课程表的所有权限授予用户23;
GRANT SELECT ON TABLE 学习 TO PUBLIC;-----把对学生表的查询权限授予所有人;
2.收回权限
REVOKE 权限
ON 对象类型 对象名
FROM 用户
例如:
REVOKE UPDATE(成绩) ON TABLE 学习 FROM user4; ----把用户4修改成绩的权限收回