SQL操作语句

SQL

create table

a table needs:

The table name
The name and type of each attribute name (A name may contain ‘_’ but not ‘-’).
Integrity constraints: E.g., primary key, foreign key, NOT NULL, unique key, CHECK

basic types: char, int, numeric(p,d), real(floating-point number)

query

create table invoice
(inv_id int,
timestamp int
cust_id int
amout numeric(10,2)
primary key(inv_id))

other SQL statements

basic SQL

select

1.简单查询 SELECT语句可以用来查询

一个或者几个列查询（显示顺序是根据自己所列的顺序出现的）

SELECT column（列名）, another_column, …FROM mytable（表名）;

查询所有数据

SELECT * FROM mytable;

---

2.条件查询 使用where

SELECT column, another_column, …
FROM mytable
WHERE condition
    AND/OR another_condition
    AND/OR …;

如果不加; 就代表这句话还没有结束，为了美观可以直接换行

下面是一些数字比较的

for example

SELECT * FROM movies where year not between 2000 and 2009

然后是字符串比较的，字符串需要带引号，否则会被认为是属性

好像like的话字符串内不区分大小写

通配符like运算比其他运算更慢所以尽量少用或者筛选后再用

---

3. 查询结果filtering和sort

DISTINCT只返回唯一一列

SELECT DISTINCT column, another_column, … FROM mytable WHERE *condition(s)*;

ORDER BY col_name可以来排序，asc是升序，desc是降序

SELECT column, another_column, … FROM mytable WHERE condition(s) ORDER BY column ASC/DESC;

如：

SELECT distinct director FROM movies order by director ASC

LIMIT来限制返回条目个数

OFFSET来限制从哪一行开始，比如选择2，那么显示的第一个是第3行

SELECT column, another_column, … 
FROM mytable WHERE condition(s) 
ORDER BY column ASC/DESC 
LIMIT num_limit OFFSET num_offset;

可以说LIMIT和OFFSET一般在SQL的其他部分都执行完之后，再执行

4. GROUP BY

是分类，把某列有相同属性相同值分在一起

SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression
GROUP BY column, another_col;

和select一样，后面跟个column，能继续分组

5. HAVING

分组之后再来一次筛选，组内筛选

SELECT group_by_column, AGG_FUNC(*column_expression*) AS aggregate_result_alias, … 
FROM mytable 
WHERE condition
GROUP BY column 
HAVING group_condition;

---

JOIN

1. inner join

在有着相同属性的情况下做交集

SELECT column, another_table_column, … 
FROM mytable （主表） 
INNER JOIN another_table （要连接的表）    
ON mytable.id = another_table.id (想象一下刚才讲的主键连接，两个相同的连成1条) 
WHERE condition(s) ORDER BY column, … ASC/DESC 
LIMIT num_limit OFFSET num_offset;

2. outer join

外部连接是指会包含没有关联的那些行

左连接：保留outer join左边的所有行，如果右边有多个就为左边补上重复，如果右边有不匹配的就删去，左边有多的右边就加null

左连接，右连接两者可以通过调换from join中间的位置互换使用

full则是全部都保留

SELECT column, another_column, … FROM mytable 
LEFT/RIGHT/FULL JOIN another_table    
ON mytable.id = another_table.matching_id 
WHERE condition(s) ORDER BY column, … ASC/DESC 
LIMIT num_limit OFFSET num_offset;

3. 通过where筛选

from可以多选几个表，然后使用where筛选条件，筛选方式和join一样（如果没有where作筛选则会出现笛卡尔积）

4. 自连接

单条SELECT语句中能多次引用相同的表

比如某个生产商生产的一个名为“DTNTR”的物品出现了问题，我们想知道生产该物品的生产商们，他们的其他产品会不会也有问题，那么就可以用下面的语句来筛选

SELECT prod_id,prod_name
FROM products
WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR')

也可以用子查询

---

一些计算

1. 简单计算

±*/和之前的都差不多，但是判断中=相当于==

null不参与计算

在select中可以对列进行计算后使用 AS 来给新变量命名，后面可以直接使用，列也会用这个名字

2.一些统计函数

e.g.

SELECT MAX(prod_price) AS max_price;
SELECT SUM(quantity) AS items_ordered

配合group by能进行组内计算

3. 执行顺序

1. FROM JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT/OFFSET

4. like

是对字符串的操作，包含某字符串段则为真。

select * from table
where product like "%o"

对table进行更改

set

set operations: union, intersect, except

these operations remove duplicates. To allow duplicates, use the keyword all.

(select name from a) union (select name from b) 
(select name from a) union all (select name from b) 

insertion

insert tuples into a relation, or insert tuples obtained by select statement

insert into product 
values(5,'name','brand',1.1)

insert into product select column from table where conditions

deletion

delete tuple(s) from a relation

delete form product where prod_id=3

update

update tuple(s) based on a condition

update product
set price=price*2
where brand='AB'

subqueries

use ( )

select * from product
where price<(select avg(price) from product)

in是包含为真

some是不少于1个满足为真，all是所有满足为真

select * from product 
where price < some(selct price from product where brand='CO')

select * from product 
where price < all(selct price from product where brand='CO')

correlated subquery: a subquery that refers to the outer statement

Let ST be the result of a subquery:

• exist()

exist(ST) return true if ST is not empty.

• unique()

unique(ST) return true if ST has no duplicate. MySQL没有unique

view

A virtual table defined by a SQL statement. Its content not physically stored in DBMS

create view contact as 
select email, address
from customer

SQL authorization

privilege list contains: select, insert, update, delete, all.

grant

confer authorization

grant <privilege list> on <relation or view> to <user list>

revoke

revoke <privilege list> on <relaiton or view> from <user list>

role