SQL 命令

基础命令

  • select 
    1
    select * from table_name;
  • select distinct
    去重 
    1
    select distinct column_name from table_name;
  • where
    过滤 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    select column_name from table_name where column_name>10;
    =
    <
    <> or !=
    >=
    <=
    between在某个范围内
    like搜索某种模式
    in
  • and or 
    1
    select * from table_name where column_name = 10 and/or column_name in ...
  • order by
    排序 默认为升序排序，若需要降序则使用关键字DESC 
    1
    select * from table_name order by column_name, column_name2 desc;
  • insert into
    插入 
    1
    2
    3
    insert into table_name (column_name, column_name) values (value1, value2)
    or 
    insert into table_name values (value1, value2)
  • update
    修改 
    1
    update table_name set column1=value1, column2=value2 where column=value;
  • delete
    删除 
    1
    delect from table_name where column=value;
    # 进阶命令
  • select top
    top, limit, rownum 
    1
    2
    3
    4
    5
    6
    7
    8
    select top 10 column_name from table_name;
    or
    select column_name from table_name limit 10;
    or 
    select column_name from table_name where rownum <= 10;
    or
    # 选择5-15行的数据 offset
    select column_name from table_name where limit 10 offset 5;
  • like
    模糊搜索 
    1
    select column_name from table_name where column_name like pattern;
通配符 说明
% 代表零个、一个或者多个任意的字符
_ 代表单个字符或者数字
[charlist] 字符列表中的任何单一字符。可以使用连字符（-）根据 ASCII 编码指定一个字符范围，例如：
[0-9] 表示从 0 到 9 的任一数字
[a-z] 表示小写英文字母
[a-zA-Z] 表示英文字母，不区分大小写
[a-zA-Z0-9] 表示英文字母和阿拉伯数字
[^charlist] 或 [!charlist] 不在字符列表中的任何单一字符
  • in
    配合where子句使用 
    1
    select column_name from table_name where column_name in (value1, value2, ...)
  • between
    选取介于两个值之间的数据范围内的值。 
    1
    2
    3
    select column_name from table_name where column_name between 0 and 10;
    # 文本值, 选取name以介于A-J 之间字母开始的所以。
    select * from table_name where name between 'A' and 'J';
  • 别名 as
    通常配和SQL函数使用。 
    1
    2
    3
    select column_name as name from table_name;
    or
    select column_name from table_name as table;
    ## join
join类型 sql
内连接 sql inner join
全连接 sql full join
左连接 sql left join
右连接 sql right join

padnas.merge 也是同样的操作
* inner join
inner join 与 join 相同 

1
select colnum_name from table1 inner join table2 on table1.col=table2.col;
* full join 
1
select * from table1 full outer join table2 on table1.col=table2.col;
* left join * right join * union
合并多个select语句的结果, union将选取不同的值。union all允许重复的值。 
1
2
3
select col from table1
union
select col from table2;
## create * create database 
1
2
create database dbname;

* create table 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table table_name
(
column_name type(site),
column_name type(site),
...
)
# eg.
create table user
(
uid int PRIMARY KEY,
name varchar(255) not null,
password varchar(255),
email varchar(32) UNIQUE,
group varchar(32) foreign key references Groups(gid)
)
* 约束

constraint details
not null 非空
unique 唯一
primary key 主键
foreign key 外键
check 存储条件
default 默认值
  • is null 
    1
    select * from table_name where col is null;
  • is not null 
    1
    select * from table_name where col is not null;
    ## sql函数
  • avg 
    1
    select avg(name) as name_avg from table_name;
  • count 
    1
    select count(distinct col) as count, avg(col) from table_name;
  • first
  • last
  • max
  • min
  • sum
  • ucase
    字段大小写转化
  • lcase
  • mid
  • len
  • round
  • now
  • format ### group by 
    1
    2
    3
    4
    select col1, max(col1) as max, count(distinct col2) as count
    from table_name
    where ...
    group by column_name
    ### having group by的where，用来选择分组 
    1
    2
    3
    4
    5
    select col1, max(col1) as max, count(distinct col2) as count
    from table_name
    where ...
    group by column_name
    having sum(col3)>10 ...