数据库基本语言（2）

@feifeifan //数据库基本语言(2)

建表，以该table为基础，使用数据库基本sql。

create table test01(
  staff_id             VARCHAR2(10) not null,
  staff_code           VARCHAR2(11) not null,
  staff_no             VARCHAR2(10) not null,
  staff_name           VARCHAR2(20) not null,
  staff_post           VARCHAR2(1000),
  mnemonic_code        VARCHAR2(100),
  staff_pwd            VARCHAR2(32),
  staff_number         VARCHAR2(10),
  is_activation        VARCHAR2(1) default 'Y' not null,
  family_address       VARCHAR2(80),
  created_time         DATE,
  updated_time         DATE,
  deleted_time         DATE,
  remarks              VARCHAR2(100)
);

-- Add comments to the table 表注释
comment on table test01 is '职员基础资料';
-- Add comments to the columns 字段注释
comment on column test01.staff_id  is '职员ID';
comment on column test01.staff_code is '职员内码';
comment on column test01.staff_no is '职员编号';
comment on column test01.staff_name is '职员名称';
comment on column test01.staff_post is '职员岗位';
comment on column test01.mnemonic_code is '助记码';
comment on column test01.staff_pwd is '口令';
comment on column test01.staff_number is '工号';
comment on column test01.is_activation is '是否活动';
comment on column test01.family_address is '家庭住址';
comment on column test01.created_time  is '生成时间';
comment on column test01.updated_time  is '更新时间';
comment on column test01.deleted_time  is '删除时间';
comment on column test01.remarks is '备注';
alter table test01 add constraint PK_test01 primary key (STAFF_ID);--主键

select基本语言：

select * from test01;--查询test01所有列
select staff_id from test01;--查询test01指定列
select staff_id as '职员id' from test01;--赋别名 as可省略
select distinct staff_name from test01;--去重（职员名称）
select  staff_id,staff_name,floor(months_between(sysdate.created_time))'间隔' from test01;--计算列
select * from (select a.*,rownum rn from (select * from test01) a where rownum<='8') where rn>='5';分页查询5-8数据① ①的效率高于②
select * from (select a.*,rownum from (select* from test01) a) where rownum between '5' and '8';分页查询5-8数据②
select * from test01 where staff_id>'1001';--查询职员id大于1001的所有数据
select * from test01 where staff_id between '1001' and '1010';--查询职员id在1001到1010之间的所有数据
select * from test01 where staff_name='刘x' and is_activation='Y';查询姓名为刘x且活动的数据
select * from test01 where staff_name like '刘%';--查询姓名是刘开头的所有数据
select * from test01 where staff_name in ('刘想','刘我');--查询姓名为刘想，刘我的所有数据
select * from test01 where remarks is null/not null;查询备注为空/不为空的所有数据;
select * from test01 order by created_time asc/desc;--按生成时间进行升序/降序排列
select staff_post,count(staff_post) from test01 group by staff_post;按职员岗位分组，查出数目
select staff_post,count(staff_post) from test01 group by staff_post having by count(staff_post) >5;--数目大于5的职员岗位;
select * from test01 where staff_id =(select staff_id from test02 where staff_name='刘想');--查询test02里刘想在test01的信息
select * from test01 where staff_id>any(select staff_id from tesst01 where staff_post='01');--查询岗位为01的且大于子查询staff_id最小的所有职员信息
 select * from test01 where staff_id>all(select staff_id from tesst01 where staff_post='01');--查询岗位为01的且大于子查询staff_id最大的所有职员信息
 select * from test01 where exists (select staff_id from test02 where test02.staff_id=test01.staff_id and test02.staff_name='刘想');查询test02里刘想里test01的所有信息

update基本语言：

 update test01 set  staff_post='02' where staff_id='1001';

delete 基本语言：

 delete test01 set  staff_post='02' where staff_id='1001';