SQL常用语句

建表语句

    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','1446397444@qq.com',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 userinfo_new select * from usersinfo

更新表数据语法：

 update tablename set column1=value1,...[where conditions]

注：当where条件子句不存在时表示更新表中所有行指定字段数据

update usersinfo set REGDATE=sysdate      --不指定条件，更新所有数据
update usersinfo set EMAIL='2653491062@qq.com' 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

//查询约束的状态信息【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     第一类 */