sql基本用法

sql基本用法

---

oracle的常用数据类型

• varchar2 0~4000 可变长 ASCII编码
• nvarchar2 0~1000 可变长 Unicode编码
• number(p,s) p最大进度38 s保留的小数位数
• date 日期，时间
• timestamp 日期时间精确到毫秒
• clob 4G 存储字符串
• blob 4G 二进制

---

建表语句

    create table table_name(
        column_name datatype,
        ...
        column_name datatype
    )

修改表列属性

    alter table table_name 
        add column_name datatype     //添加列
        modify column_name datatype  //修改列类型
        modify column_name  null || not null;  //修改列属性能不能为空
        drop column column_name      //删除列

添加表字段约束

    alter table table_name 
        add constraint constraint_name primary key(column_name);    //增添主键约束    
        add constraint constraint_name check (condition)           // check约束比如：name !='a'
        add constraint constraint_name unique (column_name)        //添加唯一约束在column_name列

删除表字段约束

    alter table table_name 
     drop constraint constraint_name                              //删除constraint_name的约束

插入数据

    insert into table_name                                        //一条一条插入
        (column_name1,column_name2,column_name3,...,column_nameN)
    values
        (value1,value2,value3,...,valueN)

    insert into table_name                                       //从其他表导入
        (column_name1,column_name2,column_name3,...,column_nameN)
        select 
            column_name1,column_name2,column_name3,...,column_nameN from 
        from table_name2

修改数据

   update table_name 
    set 
        column_name1 = value1,
        column_name2 = value2,
        column_name3 = value3,
        ...
        column_nameN = valueN

删除数据

   delete from table_name 
        where column_name=value

查询数据

    select 
        [distinct| all]
    from 
        table_name
    [where]
        > >=  > >= != <>
        is null
        like 
        between and
        in 
        and 
        or 
        not
    [group by]
    [having]
    [order by]

表连接

    select * from table_name1,table_name2                           //等值连接
        where table_name1.a=table_name2.b               

    select * from table_name1                                       //左外连接
        left join table_name2
        on table_name1.a=table_name2.b

    select * from table_name1                                       //右外连接
        right join table_name2 
        on table_name1.a=table_name2.b

    select * from table_name1 as table1, table_name1 as table2       //自连接
      where table1.a=table2.b

    select * from table_name1 
        full join table_name2 
        on table_name1.a = table_name2.b         //全外连接 返回左外连接和右外连接的并集

添加索引

    create [unique] index index_name
        on table_name(column_name1,column_name2,column_name3,...,column_nameN)

修改索引

    alter index index_name
       rename to last_index_name

删除索引

    drop index index_name