常用数据库语句

--建表语句
create table userInfo(
    id number(6,0),
    username varchar2(20),
    userpwd varchar2(20),
    email varchar2(30),
    regdate date 
)

//向表里添加字段
--alter table userInfo add remard varchar2(500)
//删除字段(注意关键字drop column)
--alter table userInfo drop column sex
//修改表中字段的数据类型
--alter table userInfo modify userpwd number(6,0)
//修改表字段名
--alter table userInfo rename column remard to remarks
//修改表名
--rename userInfo to usersinfo
//查询表信息
--select * from usersInfo
//删除表中的全部数据（速度较drop来说快）
--truncate table usersInfo
//删除整个表
--drop table usersInfo

/*******************************添加、复制、修改、删除表数据*********************************************/
//添加表中数据(所以字段)
--insert into usersInfo values(1,'Mery','123456','mail@mail.mail',sysdate)
//给表中指定字段添加值
--insert into USERSINFO(id,USERNAME,USERPWD) values(2,'panada','a')
//为表中字段添加默认值
/*  create table userInfo(
    id number(6,0),
    regdate date default sysdate --将系统日期作为默认日期
） 
insert into userinfo(id)values(1)   --必须指定列添加
select * from USERINFO
*/
//修改表中字段为其添加默认值
--alter table usersinfo modify userpwd default '000000'
--insert into usersinfo(id)values(3)   --必须指定列添加

/*在建表时复制数据
语法：create table table_newname
      as
      select column1,column2,...|* from table_oldname
*/
--create table userinfo_new as (select * from usersinfo) 
--create table userinfo_new1 as (select id,USERNAME from usersinfo)
--select * from userinfo_new

/*在添加时复制旧表数据（注：insert into table_newname【必须是存在的】）
    insert into table_newname [(column1,column2,column3,...)] 
        select column1,column2,...|* from table_oldname
    注：添加字段与查询字段必须一一对应
*/
--insert into userinfo_new select * from usersinfo

/*更新表数据语法：
    update tablename set column1=value1,...[where conditions]
    注：当where条件子句不存在时表示更新表中所有行指定字段数据
*/
--update usersinfo set REGDATE=sysdate      --不指定条件，更新所有数据
--update usersinfo set EMAIL='mail@mail.mail' where id=3  --有条件更新
--select * from usersinfo

/* 删除表数据语法： 
    delete from table_name [where conditions]
    注：当无where条件删除时等同于truncate语句，只是效率相较低，因为truncate是数据库定义级操作
*/
--delete from usersinfo where id=1

/**********************************Oracle约束*********************************************/
//非空约束
/* 1、在创建表时设置非空约束
create table tablename(
    column_name datatype not null,...
)
   2、在修改表时创建非空约束
   alter table tablename modify column_name datatype not null
   注：执行此语句前必须确保表中该字段无空值
   3、在修改表时去除非空约束
   alter table tablename modify column_name datatype null
*/
--alter table usersinfo modify userpwd varchar2(20) not null

//主键约束
/*作用：确保表中每一行数据的唯一性，一张表只能有一个主键约束，主键约束可以由多个字段构成（复合主键或联合主键）
    1、创建表时设置主键约束
   第一种方法： create table tablename(
        column_name datatype primary key,...
    )
   第二种方法：constraint constrain_name primary key(column1,column2,...) --联合主键约束设置
    2、在修改表时添加主键约束
   语法：alter table tablename add constraint constrain_name primary key(column_name1,...);
*/
/* create table userinfo_p1(
    id number(6,0) primary key,
    username varchar2(20)
) */
/* create table userinfo_p2(        --联合主键约束的创建
    id number(6,0),
    username varchar2(20),
    userpwd varchar2(20),
    constraint pk_id_username primary key(id,username)
) */
--alter table userinfo add constraint pk_id primary key(id)     --修改时创建主键约束

//更改约束的名称
--alter table userinfo rename constraint pk_id to pk_sid

//删除主键(外键、唯一，检查)约束
/* 不永久删除仅禁用
    alter table tablename disable|enable constraint constraint_name
 */
--alter table userinfo disable constraint pk_sid      --disable禁用 ，enable启用
//查询约束的状态信息【user_constraints是约束的数据字典】
--select constraint_name,status from user_constraints 
/* 永久性删除主键（外键，唯一，检查适用第一种方法）约束 
    第一种方法：alter table tablename drop constraint constraint_name
    第二种方法：alter table tablename drop primary key[cascade] --cascade用于级联删除
*/

//外键约束
/*
    1、在创建表时设置外键约束(table1表示从表，table2表示主表)
    第一种方法： create table table1(
        column_name datatype references table2(column_name),...
    );
    注：设置外键约束时，主表中的字段必须是主键，主从表中相应的字段必须是相同的数据类型
    从表中外键字段的值必须来自主表中相应字段的值，或者为null值
    第二种方法：constraint constraint_name foreign key(column_name
                references table_name(column_name)[on delete cascade]
    2、在修改表时添加外键语法：（tablename从表名,tablename_column从表中的外键字段,tablePKname主表名）
    alter table tablename 
    add constraint fk_name foreign key(tablename_column) references tablePKname(tablePKname_column)
*/
/* create table typeinfo(
    typeid varchar2(10) primary key,
    typename varchar2(20)
)
create table userinfo_f(
    id varchar2(10)primary key,
    username varchar2(20),
    typeid varchar2(10) references typeinfo(typeid)  ----在创建表时设置外键约束第1种方法
) */
--insert into typeinfo values(1,'it')
--select * from typeinfo
--insert into userinfo_f values(1,'a',1)
--insert into userinfo_f values(2,'a',null)
--select * from userinfo_f
/* create table userinfo_f1(   --在创建表时设置外键约束第二种方法
    id varchar2(10)primary key,
    username varchar2(20),
    typeid varchar2(10)
     constraint fk_typeid foreign key(typeid)
     references typeinfo(typeid)
)   */
//在修改表时添加外键语法
/* alter table userinfo_f2 
    add constraint fk_name foreign key(typeid) references typeinfo(typeid) */

//唯一性约束
/*唯一约束和主键约束的区别：
    主键字段值必须是非空的，唯一约束允许有一个空值
    主键约束在一张表中只能有一个，唯一约束可不止一个
    1、在创建表时设置唯一约束语法：
   第一种方法（列级设置）： create table tablename( 
        column_name datatype unique,...
     )
   第二种方法（表级设置）：create table tablename( 
        column_name datatype,
        constraint un_username unique(username)
      )
    2、在修改时添加唯一约束
    alter table tablename add constraint unique_name unique(column_name)
    3、删除唯一约束（同上删除主键，外键约束）
*/
/* create table userinfo_u(     --列级设置唯一约束
    id varchar2(10)primary key,
    username varchar2(20) unique,
    userpwd varchar2(20)
) */
/* create table userinfo_u1(    --表级设置唯一约束
    id varchar2(10)primary key,
    username varchar2(20),
    constraint un_name unique(username)
) */
//检查约束(使数据库数据具有实际意义)
/*
    1、在创建表的时候设置检查约束（可有多个）
    第一种方法（列级）：create table tablename(
        column_name datatype check(expressions),...
    )
    第二种方法（表级）：create table tablename(
        column_name datatype,...
        constraint constraint_name check(expressions)
    2、在修改表时添加检查约束
       alter table tablename add constraint constraint_name check(expressions);
    3、删除检查约束（同上）
*/
/* create table userinfo_c(     --列级设置检查约束
    id varchar2(10)primary key,
    username varchar2(20) unique,
    userpwd varchar2(20)，
    salary number(5,0) check(salary>0)
) */
/* create table userinfo_c1(     --表级设置检查约束
    id varchar2(10)primary key,
    username varchar2(20) unique,
    salary number(5,0),
    constraint ck_salary check(salary>0)
) */
--insert into userinfo_c1 values(1,'a',-10)   --ERROR:ORA-02290: 违反检查约束条件 (SCOTT.SYS_C0011068)


/**********************************ORACLE基本查询语句**************************************************/
//基本查询语句【distinct表示去掉重复行】
--select [distinct]column1,column2,...from table_name [where conditions]
//逻辑运算符的优先级：按not  、and   、or的顺序依次递减
//查询工资用户名为a,工资在2000到5000之间
--select * from userinfo_c where username='a' or salary>2000 and salary<=5000  
//模糊查询【like 通配符%，_表示任意一个字符】
--select * from usersinfo where username like '%a%'
//范围查询
/* 
    between ...and... 
    in / not in (,,,...)相当于or
*/

//对查询结果进行排序语法:select ...from ...where ... order by column1 deac/asc(降序/升序)

/* 
case ...when 语句的使用 ：第一种方法：case column_name when value1 then result1,...[else result] end
            第二种方法：case when column_name=value1 then result1,...[else result] end 
*/
--select * from usersinfo
--update usersinfo set  username='aaa' where id=3
/* select username,case username when 'panada' then '计算机部门' 
when 'aaa' then '销售部门' else '其他部门' end as 部门 from usersinfo */
/* select username,case when  username='panada' then '计算机部门' 
when  username='aaa' then '销售部门' else '其他部门' end as 部门 from usersinfo */

//decode函数的作用和case ...when ... 相同
--select id,username ,decode(username,'aaa','销售部','panada','计算机部门')as 部门 from usersinfo
//*注：Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。 
select id,username ,case when id in(2,3) then '第一类'
when id in(2) then '第二类' end as 类别 from usersinfo  2 
/*结果显示： panada 第一类
             aaa     第一类 */