【数据库】《SQL必知必会 4th》部分笔记

9.汇总数据

count(*) 包括空

count(name) 不包括空

 

10.分组数据

group by 分组

having 过滤分组

where 过滤行

11.子查询

select .. from .. where in (select ...)

由内向外处理

A.子查询过滤

作为子查询的语句只能查询单个列。

B.作为计算字段使用子查询

select

cust_name,

cust_state,

(select count(*) from orders where orders.cust_id=customer.cust_id))as orders

from customers

order by cust_name;

12.联结

A.cross join(叉连接)

没有where的话，会返回笛卡尔积。

B.inner join(内连接)首选。

连接处理耗费资源，连接的表越多，性能下降越厉害。

但是，处理连接比处理子查询快得多。

C.self join(自连接)

D.natural join(自然连接)

E.out join（外连接）

　　left join

　　right join

　　full outer join

注意：1).一般使用内连接

　　　2).总是提供连接条件，否则笛卡尔积。

　　　3).采用不同的连接(inner left right)条件测试，排查故障方便。

14.组合查询（UNION）

15.插入数据

A.插入检索出的数据(到已有的表)

　　INSERT INTO

B.从一个表复制到另一个表(新表)

　　SELECT INTO　　

　　select * into custCopy from customers

16.更新与删除数据

A.使用update、delete一定不要忘了where！！！

B.有的update、delete需要权限。

C.一定要使用update、delete之前，要先select看看！！！

17.创建和操纵表

create table student

(

id int not null,　

name varchar(20)

);

ALTER TABLE student add age int;

ALTER TABLE student drop column age;

DROP TABLE student;

18.视图。

A.什么使用视图？

　　1).重用sql

　　2).简化sql操作

　　3).使用表的一部分而不是整个表

　　4).保护数据

　　5).更改数据格式和表示。

　　视图可以返回与底层表的表示和格式不同的数据。

B.视图规则与限制

C.创建视图

CREATE VIEW studentView as (select ...)

　　1)简化复杂连接

　　2)格式化检索出的数据

　　3)过滤不想要的数据

19.存储过程。

A.简介

　　一些复杂的操作需要多条语句才能完成。

　　可将其视为批文件，虽然他们的作用不仅限于批处理。

B.为什么？

　　1)通过把处理封装在一个易用的单元中，简化复杂的操作。

　　2)由于不要求反复建立一系列处理步骤，保证了数据的一致性。防止错误。步骤越多，出错的可能性越大。

　　　　如果所有开发人员都是用同一存储过程，则所使用的代码都是相同的。

　　3)简化对变动的管理。安全性。

　　4)存储过程通常以编译过的形式存储，提高性能。

　　5)编写更灵活的代码

C.好处

　　简单、安全、高性能

D.缺陷

　　1)不易移植

　　2)编写存储过程比编写基本sql复杂，需要更高的技能，更丰富的经验。

 

E.执行存储过程

　　EXECUTE

F.创建存储过程

　　create procedure mailingListCount(ListCount out Integer)

　　is v_rows Integer;

　　begin 

　　　　select count(*) into v_rows

　　　　from customers

　　　　where not cust_email is null;

　　　　ListCount :=vrows;

　　end;

var ReturnValue Number

exec mailingListCount(:ReturnValue);

select ReturnValue; 

20.管理事务处理

事务处理是一种机制，用来管理必须成批执行的sql操作，保证数据库不包含不完整的操作结果。

A.事务处理

事务(transaction)

回退(rollback)

提交(commit)

保留点(savepoint)

B.控制事务处理

 

21.使用游标

22.高级sql特性

（22.1）约束

　　A.主键约束

　　B.外键约束

　　　　1)外键是表中的一列，其值必须列在灵异表的主键中。

　　　　2)外键是保证引用完整性的及其重要部分。

　　　　3)定义

　　　　　　alter table orders 

　　　　　　add constraint foreign key(cust_id)

　　　　　　references customers(cust_id)

　　　　4)外键有助防止意外删除

　　　　5)级联删除

　　C.唯一约束

　　D.检查约束

（22.2）索引

索引用来排序数据以加快搜索和排序操作的速度。想象一本书后的索引。

可以在一个或多个列上定义索引，使DBMS保存其内容的一个“排过序”的列表。

A.索引改善查询select操作性能，降低insert、update、delete操作性能，在执行这些操作时，DBMS必须动态更新索引。

B.索引可能占用大量存储空间。

C.并非所有数据都适合做索引。取值不多的数据不需要索引。

D.索引用于数据过滤和数据排序。经常排序数据适合索引。

E.索引可以定义多个列。

F.用法：

create index product_name_index

on products(product_name);

G.索引必须唯一命名

H.索引效率岁数据增加而变化，过去创建的某个理想的索引经过几个月的数据处理后可能变得不再理想。

　　最好定期检查索引，并根据需要调整更新索引。

（22.3）触发器

触发器是特殊的存储过程，他在特定的数据库活动发生时自动执行。触发器可以与特定表上的insert、update、delete操作相关联。

A.触发器可以在特定操作执行之前或之后执行。

B.用途：

　　1)保证数据一致

　　2)基于某个表的变动在其他表上执行活动。

　　　　比如，更新或删除一行时将跟踪记录写入某个日记表。

　　3)进行额外的验证并根据需要回退数据。

　　4)计算计算列的值或更新时间戳。

C.写法

　　create trigger customer_state

　　on customers for insert,update

　　as update customers set....

（22.4）数据库安全