复习笔记：基本SQL

<一>基本数据类型

1.char(n):定长字符串，n为指定长度
2.varchar(n):变长字符串，n为指定最大长度
3.int
4.smallint
5.numeric(p,d):定点数，总共p位数字，其中d位数字在小数点右边
6.real,double precisiion
7.float(n):n为精度

相关术语：
table_name(attribute_name)

attribute_1(type)	…	attribute_n(type)
attribute_1_value	…	attribute_n_value

<二>sql基本模式定义

create table table_name(
attribute_1 type not null default default_value,
attribute_2 type,
...,
primary key (attribute_1,...,)
)

1.insert

//插入行
insert into table_name
values (attribute_1_value,...,attribute_n_value)

//插入数据
insert into table_name (attribute_1,..,attribute_i)
values (attribute_1_value,...,attribute_i_value)

2.delete

//删除行
delete from table_name
where attribute_i = attribute_i_value

//删除所有行
delete from table_name
delete * from table_name

3.drop

//删除表
drop table table_name
//删除数据库
drop database database_name

4.alter

//添加列
alter table table_name
add attribute_1 type
//删除列
alter table table_name
drop column attribute_1

<三>sql查询基本结构

1.3个基本子句:select,from,where

select table_1.attribute_1,...,table_i.attribute_i
from table_1,...,table_i
where condition

2.select : 关系代数的投影运算

//选择某一列
select attribute_name from table_name
// + - * /
select attribute_name * 2 from table_name
// 选择所有列
select * from table_name
// 删除重复
select distinct attribute_name from table_name

3.where : 关系代数的选择谓词

select attribute_name
from table_name
where attribute_name operator value and|or|not ...
// operator: = , <> , > , < , >= , <= , between and , not between , like

4.from : 关系代数的笛卡尔积

from table_1,…,table_i

select attribute_name
from (
      select attribute_1,function(attribute_2)
      from table_name group by attribute_1)
      ) as temp_name(temp_attribute_1,temp_attribute_2)
where condition

5.as : 更名运算

//old as new
select attribute_name as fuck_attribute
from table_name

select table_1.attribute_1, fuck_table.attribute_1
from table_1, table_1 as fuck_table

6.like : 字符串匹配

select attribute_name
from table_name
where attribute_name like patten

// patten: % _

//’abc%’ : 以abc开头的字符串

//’%abc%’ : 包含abc的字符串

//’_’ : 只含3个字符的字符串

//’_%’: 以3个字符开头的字符串，也就是至少含3个字符的字符串

// escape : 用于定义转义字符

select attribute_name
from table_name
where attribute_name like 'ab\%cd%' escape '\' // 匹配以ab%cd开头的字符串

7.order by : 排列(默认升序)

// desc : 降序，asc : 升序

select attribute_1,attribute_2
from table_name
where condition
order by attribute_1 desc, attribute_2 asc

<四>集合运算

1.union : 两个select的结果的并集，自动去重

// 在table_name_1里或在table_name_2里或都在的
select attribute_name from table_name_1
union
select attribute_name from table_name_2

// union all : 允许重复
select attribute_name from table_name_1
union all
select attribute_name from table_name_2

2.intersect : 两个select的结果的交集，自动去重

// 既在table_name_1里又在table_name_2里
select attribute_name from table_name_1
intersect
select attribute_name from table_name_2

// intersect all : 允许重复

3.except : 两个select的结果的差集，自动去重

// 在table_name_1里但不在table_name_2里
select attribute_name from table_name_1
except
select attribute_name from table_name_2

// except all : 允许重复

<五>聚集函数

//avg(),min(),max(),sum(),count()

//avg,sum的输入必须为数字集

1.group by : 对一个或多个列进行分组（相同值的为一组）

// account(customer_name,branch_name,balance)

account

customer_name	branch_name	balance
customer_1	branch_1	100
customer_2	branch_1	200
customer_3	branch_2	300
customer_4	branch_2	400
customer_5	branch_3	500

select branch_name,avg(balance)
from account
group by branch_name

// 结果

result

branch_name	avg(balance_
branch_1	150
branch_2	350
branch_3	500

2.having : 与group by搭配使用，作用相当与where

select attribute_name,function(attribute_name)
from table_name
where attribute_name operator value
group by attribute_name
having fuction(attribute_name) operator value

<六>嵌套子查询

1.in/not in : 判断属性是否在集合中

select attribute_name
from table_name
where attribute_name in (select attribute_name
                         from table_name)
// 多属性判断
where (attribute_name_1,attribute_name_2) in ()

// 枚举集合
where attribute_name in (attribute_value_1,attribute_value_2)

2.some : 某一个

// >some : 至少大于某一个

//

select attribute_name
from table_name
where attribute >some (select attribute_name from table_name)

3.exists : 判断select结果集是否为空，为空返回false,否则返回true

select attribute_name
from table_name
where exists (select attribute_name from table_name where condition)

// not exists

4.unique : 查重，有重复则返回false,否则返回true

select attribute_name
from table_name
where unique (select attribute_name from table_name where condition)

//not unique

5.with : 临时视图

with temp_view_name(temp_attribute_name) as
    select attribute_name
    from table_1
select attribute_name
from table_1, temp_view_name
where condition

6.create view : 创建视图

create view view_name as <查询表达式>

7.drop view : 删除视图

drop view view_name

<七>修改数据库

1.delete

delete from table_name where condition

2.insert

insert into table_name values (attribute_value_1,attribute_value_2,...)
insert into table_name(attribute_1,attribute_3) values (attribute_value_1,attribute_value_3)

3.update

update table_name
set attribute_name = attribute_value
where condition

4.case end

update table_name
set attribute_name = case
                      when attribute_name operator attribute_value
                      then operation_1
                      else operation_2
                    end

case
    when condition_1 then operation_1
    when condition_2 then operation_2
    ...
    when condition_n then operation_n
    else operation_0
end
// 当condition_i第一个被满足，则执行opertion_i

5.可更新的视图

// 满足一下所有条件的视图才能更新

1.from 子句中只有一个表

2.select 子句中只含属性名，不包含表达式，聚集函数，distinct

3.没有在select子句中属性可空

4.不含group by和having子句

<八>连接

1.join : 从多个表中获取结果

// inner join, left join, right join, full join

2.inner join : 从多个表中获取共有结果

select attribute_name
from table_1
inner join talbe_2
on table_1.attribute_name = table_2.attribute_name

3.left join : 以左表为准

select attribute_name
from table_1
left join table_2
on table_1.attribute_name = talbe_2.attribute_name

4.right join : 以右表为准

5.full join : 以左右两个表为准

---

table_1 table_2

attribute_1	attribute_2		attribute_1	attribute_3
001	apple		001	doge
002	banana		001	edge
003	canada		004	fucker

===>inner join

select attribute_1,table_2.attribute_3
from table_1
inner join table_2
on table_1.attribute_1 = table_2.attribute_1

result

attribute_1	attribute_3
001	doge
001	edge

===>left join

select attribute_1,table_2.attribute_3
from table_1
left join table_2
on table_1.attribute_1 = table_2.attribute_1

result

attribute_1	attribute_3
001	doge
001	edge
002	null
003	null

===>right join

select attribute_1,table_2.attribute_3
from table_1
right join table_2
on table_1.attribute_1 = table_2.attribute_1

result

attribute_1	attribute_3
001	doge
001	edge
004	fucker

===>full join

select attribute_1,table_2.attribute_3
from table_1
full join table_2
on table_1.attribute_1 = table_2.attribute_1

result

attribute_1	attribute_3
001	doge
001	edge
002	null
003	null
004	fucker