结构化查询语言:SQL

1. 查询:select column from table where condition group by column having condition order by column limit start,end

a) 去掉重复:select disdinct column from table

b) 条件:select column from table where condition(非空column is not null)

c) 多个条件:select column from table where condition1 and condition2

d) 离散值集:select column from table where column in (value1,value2)

e) 连续值域:select column from table where column between from and to

f) 相似值:select column from table where column like ‘%key%’

g) 排序:select column from table order by column desc

h) 函数:select sum|count|avg|max|min(column) from table

i) 分组:select column from table group by column

j) 函数结果限定:select sum(col) from table group by col having sum(col) > 1

k) 别名:select sum(column) as alias from table

l) 连接:select a1.col1,a2.col2 from table1 as a1,table2 as a2 where al.col = a2.col

左(右)连接:select column from table1 left|right join table2 on table1.col = table2.col

m) 外连接:不要求两个表都有同一笔数据,where a1.col = a2.col(+)

全连接:select column from table1 full join table2

n) 子查询:select col from table where name in (select name from table where condition)

o) 合并:select col from table1 union select col from table2,disdict唯一,union all全部

p) 交集:select-statement intersect select-statement

q) 差集:select-statement minus select-statement

r) 拼接字段:select concat(column1,column2) from table

s) 裁剪字段:select substr(column,pos[,len]) from table,pos从1开始,len太长会出错

t) 去掉空白:select [l|r]trim(column) from table,直接用select trim(“ haha ”)

 

2. 创建:

a) 表:

create table table_name

(First_Name char(50),

Last_Name varchar(50),

Birth_Date date)

b) 视图:虚拟表格,在实际表格基础之上,创建视图后查看用select * from view_name

create view view_name as

select-statement

c) 索引:命名可考虑idx、table、column相关,创建时添加key idx_name (column)

create index index_name on table(column)

 

3. 重构:

a) 添加列:alter table add column type

b) 改列名:alter table change old_column new_column type

c) 改类型:alter table modify column type

d) 删除列:alter table drop column

e) 主键:alter table add primary key (column),创建时添加primary key (column)

f) 外键:alter table add foreign key (column) references table(column),创建时添加foreign key (column) references table(column)

g) 删除表:drop table table_name

h) 清空表(保留表结构):truncate table table_name

 

4. 更新:

a) 添加行:insert into table(column) values(value)

b) 添加查询结果:insert into table(column) select-statement

c) 更改行:update table set column = value where condition

d) 删除行:delete from table where condition

posted @ 2011-06-17 14:56 xlongwei 阅读(...) 评论(...) 编辑 收藏
xlongwei