【数据库原理、编程与性能】Basic SQL Query Language

文章目录

• 1. History of SQL
• 2. Capabilities of SQL

• 2.1 综合统一
• 2.2 高度非过程化
• 2.3 面向集合的操作方式
• 2.4 SQL以同一种语法结构提供两种使用方式
• 2.5 语言简捷，易学易用

• 3. SQL语句

• 3.1 Create语句
• 3.2 Select语句
• 3.3 Subquery语句

• 3.3.1 定义
• 3.3.2 Conception（概念）
• 3.3.3 predicate（谓词）
• 3.3.4 set function

• 3.4 Insert语句
• 3.5 Update语句
• 3.6 Delete语句

1. History of SQL

SQL —— Structured Query Language

• 1986年，ANSI (American National Standard Institute) 的数据库委员会批准SQL作为”关系数据库语言的美国标准”；
• 1989年，ANSI与ISO(International Standard Origination)联合修改完善后，颁布了SQL_89国际标准；
• 1992 年，公布SQL_92 标准；
• 1999 年，公布SQL_99，增加对Object-Relation Model；

2. Capabilities of SQL

介于“关系代数、关系演算”之间的结构化查询语言

2.1 综合统一

• 集DDL 、DML 、DCL 于一体，可独立完成DB 生命周期中的全部活动；
• DB运行后，可根据需要随时修改模式，不影响DB的运行，系统具有良好的可扩展性；

2.2 高度非过程化

SQL操纵数据只需提出“做什么”，无须指明“怎样做”，数据存取路径、操作过程均由DBMS自动完成。

2.3 面向集合的操作方式

关系模型下，SQL数据操作one time a set；

2.4 SQL以同一种语法结构提供两种使用方式

• 是自含式语言（可独立联机交互使用），又是嵌入式语言（嵌入到其它高级语言中）；
• 两种使用方式下，SQL的语法结构基本一致；

2.5 语言简捷，易学易用

• 数据查询 select
• 数据操纵 insert, update, delete
• 数据控制 grant, revoke

3. SQL语句

3.1 Create语句

CREATE TABLE tablename (colname datatype [ not null ]{ , colname datatype [ not null ] … }[ , PRIMARY KEY ( colname { , colname … } ) ])

eg 1:

create table customers(cid char(40) not null, cname varchar(13), city varchar(20), discnt real, primary key(cid));

eg 2:

create table orders (ordno integer not null, month char(3),
cid char(4), aid char(3), pid char(3), qty integer, dollars double precision,
primary key (ordno),
foreign key(aid) reference agents(aid),
foreign key(cid) reference customers(cid),
foreign key(pid) reference products(pid) );

3.2 Select语句

Select [ all ∣ distinct ] < 目标列表达式 > [< 目标列表达式>]
From < 表名或视图名>[ ，< 表名或视图名>]
Where < 条件表达式>
Group by <列名1> [ Having< 条件表达式>]
Order by <列名2> [ ASC ∣DESC ] ；

eg:

select c.cname, c.cid, a.aname, a.aid, sum(o.dollars) as casales
from customers c, orders o, agents a
where c.cid = o.cid and o.aid = a.aid
group by c.cname, c.cid, a.aname, a.aid
having sum(o.dollars) >= 900.00
order by 5 desc;

3.3 Subquery语句

3.3.1 定义

A Select statement appearing within another Select statement.

eg: Find aids in Duluth or Dallas, and make a subquery to select aid from agents.

select distinct cid from orders
where aid in (select aid from agents
where city = ' Duluth ' or city = 'Dallas');

3.3.2 Conception（概念）

• Uncorrelated Subquery（不相关子查询）

• Can pre-calculate the inner SQL statement
  (the inner Subquery is completely independent of the outer one)

• Correlated Subquery（相关子查询）

• Can’t pre-calculate the inner loop, because the Subquery using data from an outer Select.

3.3.3 predicate（谓词）

• in

expr [NOT] in (Subquery) | expr [NOT] in (val1{ , val2…})

• Comparison Predicates (some、any、all)

=some <>some <=some >some <some >=some
=any <>any <=any >any <any >=any
=all <>all <=all >all <all >=all

eg: Find all customers who have the same discount as that of any of the customers in Dallas or Boston.

select cid, cname from customers c where discnt=some(select discnt from c where city in ('Dallas', 'Boston'));

• EXISTS Predicate(EXIST)

[NOT] EXIST (Subquery)
EXIST(subquery) is true <==> subquery is a non-empty set
NOT EXIST(subquery) is true <==> subquery is an empty set

• The UNION Operator

Subquery UNION [ALL] Subquery.（不含all则取并集，含all则不去除重复元组）

eg: We wish to list of cities where either a customer or an agent, or both, is based.

• SQL1: select city from customers union select city from agents; /* no duplicate rows */
• SQL2: select city from customers union all select city from agents; /* has duplicate rows */

• Division

双重否定表肯定。（具体请看实例）

eg 1 :Find cids of customers who place orders with ALL agents based in New York.

• SQL中无全称量词，需做等价转换，题目变为“找cids，他没有一张订单不是从New York 的agents处代理的”。

select cid from customers c where not exists (select * from agents a where city = 'New York' and not exists (select * from orders x where x.aid = a.aid and x.cid = c.cid) );

eg 2 :Get the aids of agents in New York or Duluth who place orders for all products costing more than a dollar.

• 找New York/Duluth 的aids，没有一件1 美元以上的产品不是他们代理的。

select aid from agent a where (a.city=„New York‟ or a.city=„Duluth‟) and not exists (select p.pid from product p where p.price>1.0 and not exists (select * from orders x where x.pid = p.pid and x.aid = a.aid ) );

• LIKE

WHERE column [NOT] LIKE pattern

LIKE通常与通配符%一起使用，%表示通配pattern中出现的内容，而不加通配符%的LIKE语法，表示精确匹配，其实际效果等同于 = 等于运算符

eg:

SELECT * FROM user WHERE username LIKE '小%'；--找到名字以小字开头的元组

3.3.4 set function

Five types : count(), max(), min(), sum(), avg();

3.4 Insert语句

INSERT INTO tablename [(column {, column…})]
{VALUES (expr | null { , expr | null…}) | subquery}

eg 1 :Add a row specified values to the orders table. (no qty or dollars, so on this new row they are null)

insert into orders (ordno, month, cid, aid, pid) values('1107', 'aug', 'c006', 'a04', 'p01');

eg 2 :Create a new table Swcusts of Southwestern customers, and insert into all customers from Dallas and Austin.

insert into swcusts select * from customers where city in ('Dallas', 'Austin');

3.5 Update语句

UPDATE tablename
SET colname = {expr | null | (subquery)}
{, {column = expr |null | (subquery)…}}
[where search_condition];

eg 1 :Give All agents in New York a 10% raise in the percent commission they earn on an order.

UPDATE agents SET percent = 1.1*percent WHERE city = 'New York';

3.6 Delete语句

DELETE FROM tablename [WHERE search_condition];

eg 1 :Delete all agents in New York.

delete from agents where city = 'New York';

eg 2 :Delete agents who have total orders of less than $600.

delete from agents where aid in ( select aid from orders group by aid having sum(dollars) < 600 );