目录
7、更新：使得不改变整个元组的情况下改变其部分属性的值 update
12、空值：SQL将涉及空值的任何比较运算的结果视为unknown
14、自然连接：只考虑那些在两个关系模式中都出现的属性上取值相同的元组对 natural join、join...using
15、聚集函数：以值的一个集合（集或多重集）为输入、返回单个值的函数
16、分组聚集 group by：该子句中所有属性上取值相同的元组将被分在一个组中
17、having子句：用group by形成分组后，对分组进行限定
24、with子句：定义临时关系，这个定义只对包含with子句的查询有效
1、查询所有的数据库
show databases;2、指明使用哪个数据库
不指名的话，可能无法进行下面的种种操作
use database_name; -- database_name为数据库名3、新建表（ create table）
create table 表名1(
属性A1 类型D1，
属性A2 类型D2,
…………
属性An 类型Dn，
主键(Ax1,Ax2,……,Axn)
外键(Ak1,Ak2,……,Akn) references 表名2(Ak1,Ak2,……,Akn));有外键就写，没有就可以省略。其中使用外键时要注意
- 参照表的字段是否存在
- 外键字段在其他表上是主键
- 字段类型一定要一样
create table department
(dept_name varchar(20),
building varchar(15),
budget numeric(12,2),
primary key(dept_name)); create table course
(course_id varchar(7),
title varchar(50),
dept_name varchar(20),
credits numeric(2,0),
primary key(course_id),
foreign key(dept_name) references department(dept_name));4、插入数值 insert
插入数值时，如果插入的表有外键的话，要保证插入的外键值在以该外键为主键的表中存在，否则会出现以下类似错误，无法正确的插入
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t`, CONSTRAINT `t_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`))
insert into table_name values(数据1,数据2,……,数据n); -- 对单个元组的插入请求
insert into table_name(A1,A2,...,An) values(数据1,数据2,...,数据n); -- 指定属性进行插入
insert into table_name select A1,... from tb1 where P; -- 在查询结果的基础上插入元组没被插入的值用null表示，赋予空值
insert into department values('Comp.Sci.','Taylor',65000);
-- -------------------------------------------------------
insert into course(course_id,title,det_name,credits)
values('CS-437','Database Systems','Comp.Sci.',4);
-- -------------------------------------------------------
insert into instructor
select ID,name,dept_name,18000
from student
where dept_name = 'Music' and tot_cred > 144;5、删除表
- 用delete时，保留表中的关系，删除表中的所有元组
delete from table_name; -- table_name为表名 删除表中所有元组
delete from table_name where P; -- 找出使P为真的元组- 用drop时，不仅删除表中所有元组，还删除表中的关系。一旦使用了该语句，除非用create table重新建该表，否则没有元组可以插入到该表中。
drop table table_name; -- table_name为表名6、修改属性 alter
（1）为已有关系的表增加属性
alter table tb1 add A D; -- tb1为表名 A为添加的属性 D为添加属性的类型（2）为已有关系的表去掉属性
alter table tb1 drop A; -- tb1为表名 A为要删除的属性7、更新：使得不改变整个元组的情况下改变其部分属性的值 update
update tb1 set P;
update tb1 set P where P1;update instructor set salary = salary * 1.05 where salary < 7000;case语句：可以利用它在一条update语句中执行多种更新，并避免更新次序引发的问题
case
when pred1 then result1
when pred2 then result2
......
when predn then resultn
else result0
endupdate instructor
set salaty = case
when salary <= 10000 then salary * 1.05
else salary * 1.03
end8、更名运算----重命名 as
old-name as new-name（1）把一个长的关系名换成短的，在查询其他地方使用起来更加方便
（2）适用于需要比较同一个关系中的元组的情况
select T.name, S.course_id
from instructor as T, teaches as S
where T.ID = S.ID;
-- 由于执行顺序为from---where---select，因此可以在更名后使用T和S
-- ----------------------------------------------------------
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';9、字符串运算
- SQL使用一对单引号来标示字符串，若单引号是字符串的组成部分，则用两个单引号字符来标示 eg:'it''s right'
- 字符串上的相等运算是大小写敏感的 eg:'Comp.Sci.'不等于'comp.sci.'
模式匹配
（1）like
- 百分号（%）：匹配任意子串
- 下划线（_）：匹配任意一个字符
（2）escape——使得模式中能够包含特殊模式（%和_）的字符，使用反斜线（\）作为转义字符
（3）使用not like搜寻不匹配项
select dept_name from department where building like '%Watson%';
-- 匹配包含子串'Watson'的所有系名
-- -------------------------------------------------------------
like 'ab\%cd%' escape '\'; -- 匹配所有以“ab%cd”开头的字符串
-- -------------------------------------------------------------
like 'ab\\cd%' escape '\'; -- 匹配所有以“ab\cd”开头的字符串10、排列元组的显示次序
（1）升序（默认）：order by 、asc
（2）降序：desc
select name from instructor where dept_name = 'Physics' order by name;
select * from instructor order by salary desc, name asc;11、集合运算
union（或）intersect（并）except（除去、差）使用时均自动去除重复项
若想保留重复项，则用union all、intersect all、except all
(select course_id
from section
where semester = 'Fall' and year = 2009)
union
(select course_id
from section
where semester = 'Spring' and year = 2010);把上面代码的union（并运算）换成intersect或except，就可变为交运算或差运算
12、空值：SQL将涉及空值的任何比较运算的结果视为unknown
unknown是独立于true和false的第三种表达结果方式
where子句的谓语中可以对比较结果使用and、or和not的布尔运算
- and：true and unknown的结果是unknown，false and unknown结果是false，unknown and unknown的结果是unknown
- or：true or unknown的结果是true，false or unknown结果是unknown，unknown or unknown结果是unknown
- not：not unknown的结果是unknown
如果where子句谓语对一个元组计算出false或unknown，那么该元组不能被加入到结果集中
- 如果元组在所有属性上的取值相等，那么它们就被当作相同元组，即使某些值为空
- SQL允许使用子句is unknown和is not unknown来测试一个表达式的结果是否为unknown
- 当比较两个元组对应的属性值时，如果这两个值都是非空且值相等，或者都是空，那么它们是相同的。如：｛('A', null), ('A', null)｝这样的两个元组拷贝被认为是相同的，即使在某些属性上存在空值。注意：上述对待空值的方式与谓词中对待空值的方式是不同的，在谓词中“null = null”会返回unknown，而不是true。
13、查询
执行顺序：from --- where --- group by ---having --- select
select A from tb1 where r;-- A为所要查询属性 tb1为表明 r为所要满足关系
select * from tb1; -- 全表的所有元组都会列出
select distinct A from tb1; -- distinct可强行删除重复
select all A from tb1; -- all可显示指明不去除重复，这是默认的，可不写- distinct可强行删除重复；all可显示指明不去除重复，这是默认的，可不写
- where子句允许只选出那些在from子句的结果关系中满足特定谓语的元组，有需要才写。
- 可以在select子句中用 * 表示“所有的属性”
14、自然连接：只考虑那些在两个关系模式中都出现的属性上取值相同的元组对 natural join、join...using
select A1,A2,...,An
from r1 natural join r2 natural join ... natural join rn
where P;select name1, course_id
from instructor, teaches
where instructor.ID = teaches.ID;等价于
select name1, course_id
from instructor natural join teaches；### 但以下的一个例子却不等价 ###
前提：已知instructor和teaches自然连接包括属性（ID, name, dept_name, salary, course_id, sec_id）
而course关系包含的属性是（course_id, title, dept_name, credits）
select name,title
from instructor natural join teaches, course
where teaches.course_id = course.course_id;
-- 列出教师的名字以及他们所讲授课程的名称不等价于
select name1,title
from instructor natural join teaches natural join course;原因：instructor、teaches和course三者自然连接包括属性（course_id, dept_name）
而instructor和teaches自然连接后，再让teaches与course根据属性course_id相同选出元组的结果，会忽略掉所有（course_id, dept_name）这样的对，因此两种查询结果不同
因此，为了发扬自然连接的优点，同时避免不必要的相等的属性带来的危险，引入了下面的构造形式
select A1,A2
from r1 join r2 using (A1,A3); -- r1、r2为表名join...using与自然连接相似，但可以指定用哪些属性进行匹配连接，在t1.A1 = t2.A1并且t1.A2 = t2.A2成立的前提下（t1、t2为元组），来自r1的元组t1和来自r2的元组t2就能匹配，即使r1和r2都具有名为A3的属性，也不需要t1.A3 = t2.A3成立
15、聚集函数：以值的一个集合（集或多重集）为输入、返回单个值的函数
- 平均值：avg
- 最小值：min
- 最大值：max
- 总和：sum
- 计数：count
sum和avg的输入必须是数字集，其他运算符还可以作用在非数字集数据类型的集合上，如字符串
select count(distinct ID)
from teaches
where semester = 'Spring' and year = 2010;
-- 找出在2010年春季学期讲授一门课程的教师总数
-- ---------------------------------------
select count(*)
from course; -- 计算一个关系中元组的个数SQL不允许在用count(*)时使用distinct，但在用max和min时使用distinct是合法的，可用all保留重复元组，这是默认的
16、分组聚集 group by：该子句中所有属性上取值相同的元组将被分在一个组中
※出现在select语句中但没有被聚集的属性只能是出现在group by子句中的那些属性，即任何没有出现在group by子句中的属性如果出现在select子句中的话，它只能出现在聚集函数内部，否则查询错误。
省略掉group by意味着整个关系被当作是一个分组
正确例子：
select dept_name, count(ID) as instr_count
from instructor natural join teaches
where semester = 'Spring' and year = 2010;
group by dept_name;
-- 找出每个系在2010年春季学期讲授一门课程的教师人数错误例子：
select dept_name, ID, avg(salary)
from instructor
group by dept_name;错误原因：ID没有出现在group by子句中，但它出现在了select子句中。（每个老师都有一个ID，既然每个分组只输出一个元组，那就无法确定选哪个ID值作为输出）
17、having子句：用group by形成分组后，对分组进行限定
※任何出现在havaing子句中，但没有被聚集的属性必须出现在group by子句中，否则查询错误
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;
-- 找出教师平均工资超过42000元的系18、对空值和布尔值的聚集
※除了count(*)外所有的聚集函数都忽略输入集合中的空值。由于空值被忽略，有可能造成参加函数运算的输入值集合为空集，规定空集的count运算值为0，其他所有聚集运算在输入为空集的情况下返回一个空值。
select sum(salary) from instructor;由于一些元组在salary上取空值，上述查询待求和的值中就包含了空值，SQL标准并不认为总和本身为null，而是认为sum运算符应忽略输入中的null值。
19、集合成员资格
- in：测试元组是否是集合中的成员
- not in：测试元组是否不是集合中的成员
select distinct course_id
from section
where semester = 'Fall' and year = 2009 and
course_id in(select course_id
from section
where semester = 'Spring' and year = 2010);
-- 找出在2009年秋季学期和2010年春季学期同时开课的所有课程in和not in操作符也能用于枚举集合
select distinct name
from instructor
where name not in('Mozart', 'Einstein');
-- 查询既不叫“Mozart”，也不叫“Einstein”的教师姓名20、集合的比较
- > some：“至少比某一个要大”
- 还可使用 < some、<= some、>=some、= some和<> some，其中 = some等价于in，但<> some不等价于not in
- > all：“比所有的都大”
- 还可使用 < all、<= all、>=all、= all和<> all，其中 = all等价于not in，但<> all不等价于in
select name
from instructor
where salary > some(select salary
from instructor
where dept_name = 'Biology');
-- 找出工资至少比Biology系某一个教师的工资要高的所有教师姓名select dept_name
from instructor
group by dept_name
having avg(salary) >= all(select avg(salary)
from instructor
group by dept_name);
-- 找出平均工资最高的系21、空关系测试
- exists：测试一个子查询的结果中是否存在元祖
- not exists：测试一个子查询的结果中是否不存在元祖
select course_id
from section as S
where semester = 'Fall' and year = 2009 and
exists(select *
from section as T
where semester = 'Spring' and year = 2010 and
S.course_id = T.course_id);
-- 找出在2009年秋季学期和2010年春季学期同时开课的所有课程select S.ID, S.name
from student as S
where not exits((select course_id
from course
where dept_name = 'Biology')
except
(select T.course_id
from takes as T
where S.ID = T.ID));
-- 找出选修了Biology系开设的所有课程的学生22、重复元组存在性测试
- unique：测试子查询的结果中是否存在重复元组，没有重复返回true，假的定义：当且仅当关系中存在两个元组t1和t2，且t1 = t2。由于在t1或t2的某个域为空时，判断t1 = t2为假。
- not unique：测试子查询的结果中是否存在重复元组
select T.course_id
from course as T
where unique(select R.course_id
from section as R
where T.course_id = R.course_id and R.year = 2009);
-- 找出所有在2009年最多开设一次的课程23、from子句中的子查询
- 某些SQL实现要求对每个子查询结果关系都给一个名字，即使该名字从不被引用
- Oracle允许对子查询结果关系命名（省略关键字as），但是不允许对关系中的属性重命名
select max(tot_salary)
from(select dept_name, sum(salary)
from instructor
group by dept_name) as dept_total(dept_name, tot_salary);
-- 找出所有系中工资总额最大的系- 在from子句中的子查询用关键字lateral作为前缀，以便访问from子句中在它前面的表或子查询中的属性。
select name, salary,avg_salary
from instructor I1, lateral(select avg(salary) as avg_salary
from instructor I2
where I2.dept_name = I1.dept_name);
-- 打印每位教师的姓名，以及他们的工资和所在系的平均工资24、with子句：定义临时关系，这个定义只对包含with子句的查询有效
with子句定义了临时关系，此关系在随后的查询中马上被使用了
with max_budget(value) as
(select max(budget)
from department)
select budget
from department, max_budget
where department.budget = max_budget.value;
-- 找出具有最大预算值的系25、标量子查询：子查询只返回包含单个属性的单个元组
- 从技术上讲标量子查询的结果类型仍然是关系，尽管其中只包含单个元组
- 标量子查询出现的位置是单个值出现的地方，SQL就从该关系中包含单属性的单元组中取出相应的值
select dept_name,
(select count(*)
from instructor
where department.dept_name = instructor.dept_name)
as num_instructors
from department;
-- 列出所有的系以及它们拥有的教师数
511
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
