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