基本sql语句

--删除用户
drop user test cascade;
--创建用户
即一个空间，可以设置密码
CREATE USER Test IDENTIFIED BY "egood" DEFAULT TABLESPACE bhis_egood_data;
加权限
可以查看其他空间
grant dba to Test;

--创建表
create table tb_User
(
codeid varchar2(100),
codename varchar2(500),
remark varchar2(200)
)

--查询
select * from tb_user;

--插入
1.普通插入，适用一条条插入
insert into tb_user(codeid,codename,remark)
values ('1001','系统菜单','备注')
2.将其他表的字段数据插入到当前表中
insert into tb_user(codeid,codename,remark)
select personid,name,now_address from bhis_yz_jh_430011029.ph_person_card where rownum<=20

--删除
1.最好先查出需要删除的某条数据
select * from tb_user where codeid='1001';
delete from tb_user where codeid='1001';
2.清空表
truncate table tb_user ;

--修改
1.数据库默认只读，输入修改命令点击解锁可直接修改表内容
select * from tb_user for update;

update tb_user a set a.remark='abcdefg'
where a.codeid='365445342';

--表关联
--内关联
两张表有相同的字段，可以分别取出需要的字段组成新的表
select * from jk_et_info a where a.personid='441418939';
备份
create table ph_person_card_bak_11 as
select * from ph_person_card b where b.personid='441418939';
delete from ph_person_card b where b.personid='441418939';

select a.mqxm,b.name from jk_et_info a inner join ph_person_card b on a.personid=b.personid
where a.personid='441418939';

--外关联（左关联，右关联，主表）
select a.mqxm,b.name from jk_et_info a left join ph_person_card b on a.personid=b.personid
where a.personid='441418939';

--备份数据
create table ph_person_card_bak_11 as
select * from ph_person_card b where b.personid='441418939';

insert into ph_person_card
select * from ph_person_card_bak_11