重庆巴南网站建设,电商网站建设技术交流问题,网站建设详细教程视频教程,创新logo设计数据库系统概论#xff08;个人笔记#xff09; 文章目录 数据库系统概论#xff08;个人笔记#xff09;3、SQL介绍3.1 SQL查询语言概述3.2 SQL数据定义3.3 SQL查询的基本查询结构3.4 其他基本操作3.5 设置操作3.6 空值3.7 聚合函数3.8 嵌套子查询3.9 数据库的修改 3、SQL…数据库系统概论个人笔记 文章目录 数据库系统概论个人笔记3、SQL介绍3.1 SQL查询语言概述3.2 SQL数据定义3.3 SQL查询的基本查询结构3.4 其他基本操作3.5 设置操作3.6 空值3.7 聚合函数3.8 嵌套子查询3.9 数据库的修改 3、SQL介绍 3.1 SQL查询语言概述 Qveriew of The SQL Query Language History IBM Sequel 语言是在 IBM 圣何塞研究实验室开发的 System R 项目的一部分。
重命名为结构化查询语言SQL
ANSI 和 ISO 标准 SQL:
SQL数据库SQL-89SQL-92SQL:1999语言名称符合2000年要求!SQL数据库:2003年
商业系统提供了大部分如果不是全部的话SQL-92特性以及来自后来标准的各种特性集和特殊的专有特性。
并非这里的所有示例都适用于您的特定系统。 SQL Parts DDL 数据库定义语言——提供用于定义关系模式、删除关系和修改关系式的命令。
DML数据操作语言——提供从数据库查询信息、向数据库中插入元组、从数据库中删除元组以及修改元组的能力。
Integrity 完整性——DDL包括用于指定完整性约束的命令。
View definition 视图定义——DDL包括用于定义视图的命令。
Transaction control 事务控制——包括指定事务的开始和结束的命令。
Embedded SQL and dynamic SQL 嵌入式SQL和动态SQL——定义如何将SQL语句嵌入到通用编程语言中。
Authorization 授权——包括用于指定对关系与视图得访问权限得命令。 3.2 SQL数据定义 SQL Data Definition Data Definition Language SQL 数据定义语言DDL允许指定有关关系的信息包括:
每个关系的模式。与每个属性相关联的值的类型。完整性约束要为每个关系维护的索引集。每个关系的安全性和授权信息。每个关系在磁盘上的物理存储结构。 Domain Types in SQL SQL中的域类型char(n).固定长度的字符串用户指定长度为nvarchar(n).可变长度字符串具有用户指定的最大长度nint.整数与机器相关的整数的有限子集smallint.小整数依赖于计算机的整数域类型的子集numeric(p,d).定点数用户指定精度为p位数小数点右侧为d位数。例如数字(3,1)允许44.5精确地存储但不允许444.5或0.32real, double precision.浮点数和双精度浮点数具有与机器相关的精度float(n).浮点数用户指定的精度至少为n位更多内容将在第四章介绍 Create Table Construct SQL关系是使用 create table 命令定义的 Integrity Constraints in Create Table 完整性约束的类型 SQL阻止任何违反完整性约束的数据库更新。 And a Few More Relation Definitions And more still Updates to tables Insert
insert into instructor values(10211, Smith, Biology, 66000);Delete
移除 student 关系里的所有元组
delete from student Drop Table
drop table rAlter alter table r add A D其中A是要添加到关系r中的属性的名称D是A的域。关系中所有现有的元组都被指定为null作为新属性的值。 alter table r drop A其中A是关系r的属性名删除许多数据库不支持的属性。 3.3 SQL查询的基本查询结构 Basic Query Struction of SQL Queries Basic Query Structure 一个典型的SQL查询有这样的表单 等价于关系代数表达式 ∏ A 1 , A 2 , … , A n ( σ P ( r 1 × r 2 × … × r m ) ) ∏_{A_1,A_2,…, A_n}(σ_P(r_1× r_2×…×r_m)) A1,A2,…,An∏(σP(r1×r2×…×rm)) SQL查询的结果是一个关系。 The select Clause select 子句列出查询结果中所需的属性
对应于关系代数的投影运算
Example查找所有 instructors 的名字
select name from instructorNOTESQL名称不区分大小写即您可以使用大写字母或小写字母
例如Name ≡ NAME ≡ name有些人在我们用粗体的地方用大写上面的紫色字体。
SQL 允许关系和查询结果中的重复。
若要强制删除重复项请在 select 后插入关键字 distinct。
查找所有 instructors 的 department names并删除重复的名称:
select distinct dept_name from instructor关键字 all 指定不应删除重复项:
select all dept_name from instructorselect 子句中的星号 asterisk 表示“所有属性”:
select * from instructor属性可以是不带 from 子句的文字:
select 437Results是一个包含一列和一行值为“437”的表。 可以给列一个名称使用: select 437 as FOO属性可以是**带有 from **子句的文字:
select A from instructor结果是一个包含一列和N行instructors 表中的元组数量的表每行的值为“A”。
select 子句可以包含算术表达式包括操作、-、*和/以及对元组的常量或属性进行操作
select ID,name,salary/12 from instructor查询将返回一个与教员关系相同的关系只是属性salary的值除以12。 可以使用as子句重命名“salary/12”: select ID,name,salary/12 as monthly_salaryThe where Clause where 子句指定结果必须满足的条件
对应于关系代数的选择谓词。
为了寻找 Comp.Sci.dept 里所有的 instructors
select name from instructor where dept_name Comp.Sci.SQL 允许使用逻辑连接词and、or 和 not
逻辑连接符的操作数可以是包含比较操作符、、、、和的表达式
比较可以应用于算术表达式的结果
为了寻找 Comp.Sci.dept 里且 salary 70000 所有的 instructors
select name from instructor where dept_name Comp.Sci. and salary 70000The from Clause from 子句列出查询中涉及的关系
对应于关系代数中的笛卡尔积运算。
寻找 instructor × teaches 的所有的笛卡尔积
select * from instructor, teaches生成所有可能的教师-教学对并包含双方关系的所有属性。对于公共属性例如ID结果表中的属性使用关系名称例如instructor.ID进行重命名。
笛卡尔积不是很直接有用但与where子句条件关系代数中的选择操作结合使用很有用。 Examples 查找教过某门课程的所有教员的姓名和course_id select name, course_id from instructor, teaches where instructor.ID teaches.ID查找艺术系所有教过某门课程的教师的姓名和course_id select name, course_id
from instructor, teaches
where instructor.ID teaches.ID and instructor.dept_name Art3.4 其他基本操作 Additional Basic Operations The Rename Operation SQL允许使用 as 子句重命名关系和属性:
old-name as new-name在“Comp. Sci”中找到所有薪水比某个教员高的教员的名字: select distinct T.name
from instructor as T, instructor as S
where T.salary S.salary and S.dept_name Comp.Sci.关键字 as 是可选的可以省略:
instructor as T ≡ instructor TString Operations SQL包含一个字符串匹配运算符用于对字符串进行比较。操作符 like 使用使用两个特殊字符描述的模式:
百分比% %字符匹配任何子字符串。下划线 _ _ 字符匹配任何字符。
查找名称中包含子字符串“dar”的所有教员的名称 select name from instructor where name like dar匹配字符串 100%
like 100\% escape \在上面的代码中我们使用反斜杠\作为转义字符。
模式区分大小写。
模式匹配示例:
Intro%匹配任何以’Intro 开头的字符串。%Comp%匹配任何包含 Comp 作为子字符串的字符串。‘_ _ _’ 匹配任何恰好三个字符的字符串。_ _ _ %匹配任何至少三个字符的字符串。
SQL支持各种字符串操作例如
串联使用 || 从大写到小写的转换反之亦然查找字符串长度提取子字符串等 Ordering the Display of Tuples 请按字母顺序列出所有教员的姓名
select distinct name from instructor order by name我们可以为每个属性指定 desc 表示降序asc 表示升序升序是默认的: Example order by name desc可以对多个属性进行排序: Example order by dept_name, nameWhere Clause Predicates SQL包含一个 between 比较操作符
Example查找工资在$90,000到$100,000即≥$90,000 和 ≤$100,000之间的所有教师的姓名 select name from instructor where salary between 90000 and 100000元组比较 select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) (teaches.ID, Biology)3.5 设置操作 Set Operations 查找2017年秋季或2018年春季的课程:
(select course_id from section where sem Fall and year 2017)
union
(select course_id from section where sem Spring and year 2018)查找2017年秋季和2018年春季的课程:
(select course_id from section where sem Fall and year 2017)
intersect
(select course_id from section where sem Spring and year 2018)查找2017年秋季而不是2018年春季的课程:
(select course_id from section where sem Fall and year 2017)
except
(select course_id from section where sem Spring and year 2018)设置操作 union, intersect 和 except
上述每个操作都会自动消除重复项
要保留所有副本请使用:
union allintersect allexcept all
假设一个元组在 r 中出现 m 次在 s 中出现 n 次那么它出现:
m n times in r union all smin(m, n) times in r intersect all smax(0, m – n) times in r except all s 3.6 空值 Null Values 元组的某些属性可能有一个空值用 null 表示
null 表示未知值或值不存在
任何涉及 null 的算术表达式的结果都是 null Example: 5 null returns null谓词 is null 可用于检查空值 Example: select name from instructor where salary is null如果应用谓词的值 is not null 则谓词不为空成功。
SQL将任何涉及空值的比较的结果视为 unknown谓词为空和非空除外。 Example: 5 null or null null or null nullwhere 子句中的谓词可以涉及布尔操作and, or, not因此布尔运算的定义需要扩展以处理 unknown 。 and (true and unknown) unknown,
(false and unknown) false,
(unknown and unknown) unknownor (unknown or true) true,
(unknown or false) unknown
(unknown or unknown) unknown如果 where 子句谓词的计算结果为 unknown则将其结果视为 false 3.7 聚合函数 Aggregate Functions 这些函数对关系列的多值集进行操作并返回一个值
avg平均值min最小值max最大值sum值求和count值数量 Aggregate Functions Examples 找出计算机科学系讲师的平均工资 select avg (salary) from instructor where dept_name Comp. Sci.查找在2018年春季学期教授某门课程的教师总数: select count (distinct ID) from teaches where semester Spring and year 2018求课程关系中元组的个数: select count (*) from course;Aggregate Functions – Group By 求出各部门教员的平均工资: select dept_name, avg (salary) as avg_salary from instructor group by dept_name聚合函数之外的 select 子句中的属性必须出现在 group by 列表中: /* erroneous query */
select dept_name, ID, avg (salary) from instructor group by dept_nameAggregate Functions – Having Clause 查找平均工资大于42000的所有部门的名称和平均工资:
select dept_name, avg (salary) as avg_salary
from instructor group by dept_name having avg (salary) 42000Notehaving 子句中的谓词在组形成之后应用而 where 子句中的谓词在组形成之前应用 3.8 嵌套子查询 Nested Subqueries Nested 嵌套
SQL 为子查询的嵌套提供了一种机制。子查询 A subquery 是嵌套在另一个查询中的 “select-from-where” 表达式。
嵌套可以在以下 SQL 查询中完成
select A1, A2, ..., An from r1, r2, ..., rm where Pas follows: From clauseri 可以被任何有效的子查询替换。 Where clauseP 可以用如下形式的表达式代替: B (subquery) B是稍后定义的属性和。 Select clause: Ai 可以替换为生成单个值的子查询。 Set Membership 查找2017年秋季和2018年春季提供的课程:
select distinct course_id
from section
where semester Fall and year 2017 andcourse_id in (select course_idfrom sectionwhere semester Spring and year 2018)
查找2017年秋季但不是2018年春季提供的课程:
select distinct course_id
from section
where semester Fall and year 2017 andcourse_id not in (select course_idfrom sectionwhere semester Spring and year 2018)说出所有既不是 “Mozart” 也不是 “Einstein” 的老师的名字:
select distinct name
from instructor
where name not in (Mozart, Einstein)查找由 ID 为10101的讲师讲授的课程部分的不同的学生总数:
select count (distinct ID)
from takes
where (course_id, sec_id, semester, year) in (select course_id, sec_id, semester, yearfrom teacheswhere teaches.ID 10101);Note上面的查询可以用更简单的方式编写。上面的公式只是为了说明 SQL 的特性 Set Comparison Set Comparison – “some” Clause 找出工资高于生物系某些至少一个教师的姓名:
select distinct T.name
from instructor as T, instructor as S
where T.salary S.salary and S.dept name Biology相同的查询使用 some子句:
select name
from instructor
where salary some (select salaryfrom instructorwhere dept name Biology)Definition of “some” Clause Set Comparison – “all” Clause 查找工资高于生物系所有教师工资的所有教师的姓名:
select name
from instructor
where salary all (select salaryfrom instructorwhere dept name Biology)Definition of “all” Clause Test for Empty Relations 如果参数子查询非空exists 构造返回值 true。 Use of “exists” Clause 这是指定查询“查找2017年秋季学期和2018年春季学期教授的所有课程”的另一种方式:
select course_id
from section as S
where semester Fall and year 2017 andexists (select * from section as T where semester Spring and year 2018 and S.course_id T.course_id)关联名称 Correlation name ——外部查询中的变量 S
相关子查询 Correlated subquery ——内部查询
select distinct course_id
from section
where semester Fall and year 2017 and course_id in (select course_idfrom sectionwhere semester Spring and year 2018)Use of “not exists” Clause 找到所有修过生物系所有课程的学生:
select distinct S.ID, S.name
from student as S
where not exists ( (select course_idfrom coursewhere dept_name Biology)except(select T.course_idfrom takes as Twhere S.ID T.ID))第一个嵌套查询列出了生物学提供的所有课程第二个嵌套查询列出了特定学生所修的所有课程 Test for Absence of Duplicate Tuples unique 构造测试子查询的结果中是否有任何重复元组。
如果给定的子查询不包含重复项则 unique 构造的计算结果为“true”。
查找2017年最多开设一次的所有课程:
select T.course_id
from course as T
where unique (select R.course_idfrom section as R where T.course_id R.course_id and R.year 2017) Subqueries in the From Clause SQL 允许在 from 子句中使用子查询表达式
找出那些平均工资超过4.2万美元的院系教师的平均工资:
select dept_name, avg_salary
from ( select dept_name, avg (salary) as avg_salary from instructor group by dept_name)
where avg_salary 42000注意我们不需要使用 having 从句
上述查询的另一种写法:
select dept_name, avg_salary
from (select dept_name, avg (salary) from instructor group by dept_name)as dept_avg (dept_name, avg_salary)
where avg_salary 42000With Clause with 子句提供了一种定义临时关系的方法该临时关系的定义仅对出现 with 子句的查询可用。
找到所有有最大预算的部门:
with max_budget (value) as (select max(budget) from department)
select department.name
from department, max_budget
where department.budget max_budget.value;Complex Queries using With Clause 找出总工资大于所有部门总工资平均值的所有部门:
with dept _total (dept_name, value) as(select dept_name, sum(salary)from instructorgroup by dept_name),
dept_total_avg(value) as(select avg(value)from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value dept_total_avg.valueScalar Subquery 标量 Scalar 子查询是在需要单个值的地方使用的查询
列出所有部门以及每个部门的教师人数:
select dept_name, (select count(*) from instructor where department.dept_name instructor.dept_name) as num_instructors
from department如果子查询返回多个结果元组则运行时错误 3.9 数据库的修改 Modification of the Database 从给定关系中删除元组在给定的关系中插入新的元组更新给定关系中某些元组中的值 Deletion 删除所有教员
delete from instructor删除财务部门的所有教员
delete from instructor where dept_name Finance删除教员关系中与Watson大楼中某个部门相关联的教员的所有元组
delete from instructor
where dept_name in (select dept_namefrom departmentwhere building Watson)删除所有工资低于教员平均工资的教员
delete from instructor
where salary (select avg (salary) from instructor)Problem当我们从教员中删除元组时平均工资发生了变化SQL中使用的解决方案 首先计算 avg salary并找到所有要删除的元组接下来删除上面找到的所有元组不重新计算 avg 或重新测试元组 Insertion 向course添加一个新的元组
insert into course values (CS-437, Database Systems, Comp. Sci., 4)或者同样的
insert into course (course_id, title, dept_name, credits)
values (CS-437, Database Systems, Comp. Sci., 4)添加一个新的元组到student, tot_creds设置为null
insert into student values (3003, Green, Finance, null)让每个获得超过144个学分的音乐系学生成为音乐系讲师年薪18000美元
insert into instructor select ID, name, dept_name, 18000 from student where dept_name Music and total_cred 144在将 select from where 语句的任何结果插入到关系中之前将对其进行完全求值。
否则如下的查询将导致问题
insert into table1 select * from table1Updates 给所有教员加薪5%
update instructor set salary salary * 1.05对收入低于7万的指导员加薪5%
update instructor set salary salary * 1.05 where salary 70000对工资低于平均水平的指导员给予5%的加薪
update instructor
set salary salary * 1.05
where salary (select avg (salary)from instructor)对年薪超过10万美元的教员加薪3%其他教员加薪5% 写两个 update 语句: update instructor set salary salary * 1.03 where salary 100000
update instructor set salary salary * 1.05 where salary 100000顺序很重要 用 case 语句可以做得更好如下 Case Statement for Conditional Updates 与之前相同的查询但使用case语句
update instructorset salary casewhen salary 100000 then salary * 1.05else salary * 1.03endUpdates with Scalar Subqueries 重新计算并更新所有学生的tot_creds值
update student S
set tot_cred (select sum(credits)from takes, coursewhere takes.course_id course.course_id andS.ID takes.ID.andtakes.grade F andtakes.grade is not null)对于没有上过任何课程的学生将tot_creds设置为null
代替 sumcredits使用:
casewhen sum(credits) is not null then sum(credits)else 0
end
