创建用户
create user baoyw identified by oracle default tablespace DEFAULT_TBS;
授权用户
连接用户
创建表
省份 shengfen id,name,jiancheng
城市 chengshi id,xzq_id,name,cpdm,yzbm,quxian,sf_id
create table shengfen(id int,name varchar(100),jiancheng varchar(20));
create table chengshi(id int,name varchar(100),sf_id int);
插入数据
insert into shengfen values(1,'北京市','京','北京');
insert into shengfen values(2,'天津市','津','天津');
insert into shengfen values(3,'河北省','冀','石家庄');
insert into shengfen values(4,'山西省','晋','太原');
insert into shengfen values(5,'内蒙古自治区','内蒙古','呼和浩特');
insert into shengfen values(6,'辽宁省','辽','沈阳');
insert into shengfen values(7,'吉林省','吉','长春');
insert into shengfen values(8,'黑龙江省','黑','哈尔滨');
insert into shengfen values(9,'上海市','沪','上海');
insert into shengfen values(10,'江苏省','苏','南京');
insert into shengfen values(11,'浙江省','浙','杭州');
insert into shengfen values(12,'安徽省','皖','合肥');
insert into shengfen values(13,'福建省','闽','福州');
insert into shengfen values(14,'江西省','赣','南昌');
insert into shengfen values(15,'山东省','鲁','济南');
insert into shengfen values(16,'河南省','豫','郑州');
insert into shengfen values(17,'湖北省','鄂','武汉');
insert into shengfen values(18,'湖南省','湘','长沙');
insert into shengfen values(19,'广东省','粤','广州');
insert into shengfen values(20,'广西壮族自治区','桂','南宁');
insert into shengfen values(21,'海南省','琼','海口');
insert into shengfen values(22,'四川省','川或蜀','成都');
insert into shengfen values(23,'贵州省','贵或黔','贵阳');
insert into shengfen values(24,'云南省','云或滇','昆明');
insert into shengfen values(25,'重庆市','渝','庆');
insert into shengfen values(26,'西藏自治区','藏','拉萨');
insert into shengfen values(27,'陕西省','陕或秦','西安');
insert into shengfen values(28,'甘肃省','甘或陇','兰州');
insert into shengfen values(29,'青海省','青','西宁');
insert into shengfen values(30,'宁夏回族自治区','宁','银川');
insert into shengfen values(31,'新疆维吾尔自治区','新','乌鲁木齐');
insert into shengfen values(32,'香港特别行政区','港','香港');
insert into shengfen values(33,'澳门特别行政区','澳','澳门');
insert into shengfen values(34,'台湾省','台','台北');
commit;
insert into chengshi values(1,'兰州市',28);
insert into chengshi values(2,'嘉峪关市',28);
insert into chengshi values(3,'金昌市',28);
insert into chengshi values(4,'白银市',28);
insert into chengshi values(5,'天水市',28);
insert into chengshi values(6,'武威市',28);
insert into chengshi values(7,'张掖市',28);
insert into chengshi values(8,'平洲市',28);
insert into chengshi values(9,'酒泉市',28);
insert into chengshi values(10,'庆阳市',28);
insert into chengshi values(11,'定西市',28);
insert into chengshi values(12,'陇南市',28);
insert into chengshi values(13,'临夏回族自治州',28);
insert into chengshi values(14,'甘南藏族自治州',28);
commit;
insert into chengshi values(15,'银川市',30);
insert into chengshi values(16,'石嘴山市',30);
insert into chengshi values(17,'吴忠市',30);
insert into chengshi values(18,'固原市',30);
insert into chengshi values(19,'中卫市',30);
commmit;
update chengshi set id =15 where name = '银川市';
update chengshi set id =16 where name = '石嘴山市';
update chengshi set id =17 where name = '吴忠市';
update chengshi set id =18 where name = '固原市';
update chengshi set id =19 where name = '中卫市';
表新增列
alter table chengshi add (xzq_id varchar(20),cpdm varchar(10),yzbm number(10),quxian varchar(100));
表删除未使用的列
ALTER TABLE chengshi DROP(xzq_id,cpdm,yzbm,quxian);
修改数据
update chengshi set xzq_id = 1, cpdm = '宁A', yzbm = 750000, quxian = '兴庆区、金凤区、西夏区、灵武市、永宁县、贺兰县' where id = 15;
update chengshi set xzq_id = 2, cpdm = '宁B', yzbm = 753000, quxian = '大武口区、惠农区、平罗县' where id = 16;
update chengshi set xzq_id = 3, cpdm = '宁C', yzbm = 751100, quxian = '利通区、红寺堡区、青铜峡市、同心县、盐池县' where id = 17;
update chengshi set xzq_id = 4, cpdm = '宁D', yzbm = 756000, quxian = '原州区、西吉县、隆德县、泾源县、彭阳县' where id = 18;
update chengshi set xzq_id = 5, cpdm = '宁E', yzbm = 755000, quxian = '沙坡头区、中宁县、海原县' where id = 19;
update chengshi set xzq_id = 1, cpdm = '甘A', yzbm = 730000, quxian = '城关区、七里河区、西固区、安宁区、红古区、永登县、榆中县、皋兰县' where id = 1;
update chengshi set xzq_id = 2, cpdm = '甘B', yzbm = 735100, quxian = '8个街道，3个镇' where id = 2;
update chengshi set xzq_id = 3, cpdm = '甘C', yzbm = 737100, quxian = '金川区、永昌县' where id = 3;
update chengshi set xzq_id = 4, cpdm = '甘D', yzbm = 730900, quxian = '白银区、平川区、会宁县、靖远县、景泰县' where id = 4;
update chengshi set xzq_id = 5, cpdm = '甘E', yzbm = 741000, quxian = '秦州区、麦积区、甘谷县、武山县、秦安县、清水县、张家川回族自治县' where id = 5;
update chengshi set xzq_id = 6, cpdm = '甘H', yzbm = 733000, quxian = '凉州区、民勤县、古浪县和天祝藏族自治县' where id = 6;
update chengshi set xzq_id = 7, cpdm = '甘H', yzbm = 734000, quxian = '甘州区、山丹县、民乐县、临泽县、高台县、肃南裕固族自治县' where id = 7;
update chengshi set xzq_id = 8, cpdm = '甘L', yzbm = 744000, quxian = '崆峒区、华亭市、泾川县、灵台县、崇信县、庄浪县、静宁县' where id = 8;
update chengshi set xzq_id = 9, cpdm = '甘F', yzbm = 735000, quxian = '肃州区、玉门市、敦煌市、金塔县、瓜州县、肃北蒙古族自治县、阿克塞哈萨克族自治县' where id = 9;
update chengshi set xzq_id = 10, cpdm = '甘M', yzbm = 745000, quxian = '西峰区、庆城县、华池县、宁县、镇原县、合水县、正宁县、环县' where id = 10;
update chengshi set xzq_id = 11, cpdm = '甘J', yzbm = 743000, quxian = '安定区、通渭县、陇西县、渭源县、临洮县、漳县、岷县' where id = 11;
update chengshi set xzq_id = 12, cpdm = '甘K', yzbm = 742500, quxian = '武都区、成县、文县、宕昌县、康县、西和县、礼县、徽县、两当县' where id = 12;
update chengshi set xzq_id = 13, cpdm = '甘N', yzbm = 731100, quxian = '临夏市、临夏县、永靖县、广河县、和政县、康乐县、东乡族自治县、积石山保安族东乡族撒拉族自治县' where id = 13;
update chengshi set xzq_id = 14, cpdm = '甘P', yzbm = 747000, quxian = '合作市、夏河县、碌曲县、玛曲县、迭部县、舟曲县、临潭县、卓尼县' where id = 14;
commit;
update chengshi set xzq_id =1 where id = 1;
update chengshi set xzq_id =2 where id = 2;
update chengshi set xzq_id =3 where id = 3;
update chengshi set xzq_id =4 where id = 4;
update chengshi set xzq_id =5 where id = 5;
update chengshi set xzq_id =6 where id = 6;
update chengshi set xzq_id =7 where id = 7;
update chengshi set xzq_id =8 where id = 8;
update chengshi set xzq_id =9 where id = 9;
update chengshi set xzq_id =10 where id = 10;
update chengshi set xzq_id =11 where id = 11;
update chengshi set xzq_id =12 where id = 12;
update chengshi set xzq_id =13 where id = 13;
update chengshi set xzq_id =14 where id = 14;
update chengshi set xzq_id =1 where id = 15;
update chengshi set xzq_id =2 where id = 16;
update chengshi set xzq_id =3 where id = 17;
update chengshi set xzq_id =4 where id = 18;
update chengshi set xzq_id =5 where id = 19;
增加列，默认在最后一列
alter table shengfen add sf_sh varchar(20);
修改字段值
update shengfen set shenghui = '北京' where id = '1';
修改字段长度
alter table chengshi modify(quxian varchar(160));
alter table shengfen modify(sf_name varchar(50));
alter table chengshi modify(XZQ_ID varchar(10));
修改字段类型
##前提是要更改的列必须为空
update chengshi set xzq_id = null;
alter table chengshi modify (XZQ_ID number(10) null);
update chengshi set name = '平凉市' where id = '8';
修改字段名称
alter table shengfen rename column name to sf_name;
alter table shengfen rename column jiancheng to sf_jc;
alter table shengfen rename column SF_JHCS to sf_shcs;
修改表名
alter table shengfen rename to province;
表添加注释
comment on table shengfen is '省份及简称';
comment on table chengshi is '城市及行政区划';
表字段添加注释
comment on column chengshi.ID is '编号';
comment on column chengshi.name is '城市名称';
comment on column chengshi.SF_ID is '省份编号';
comment on column chengshi.XZQ_ID is '行政区划编号';
comment on column chengshi.CPDM is '车牌代码';
comment on column chengshi.YZBM is '邮政编码';
comment on column chengshi.QUXIAN is '行政区划';
comment on column shengfen.ID is '编号';
comment on column shengfen.SF_NAME is '省份名称';
comment on column shengfen.SF_JC is '省份简称';
comment on column shengfen.SF_SH is '省份省会';
comment on table chengshi is '城市及行政区划';
查询主键约束
select a.constraint_name, a.column_name
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'P'
and a.table_name = 'SHENGFEN';
添加主键约束
##添加主键约束
alter table shengfen add constraint PK_SF_ID primary key(ID);
alter table chengshi add constraint PK_CS_ID primary key(ID);
删除主键约束
ALTER TABLE shengfen DROP pk_id;
ALTER TABLE shengfen DROP CONSTRAINTS pk_id CASCADE;
ALTER TABLE shengfen DISABLE id ;
设置 被设置为主键的列为无效
添加外键约束
##外键是用来在两个表的数据之间建立连接，可以是一列或多列，一个表可以有一个或多个外键。
##外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键必须等于另一个表中主键的某个值。
alter table shengfen add cs_id number(10);
alter table shengfen add constraint fk_shengfen_cs_id foreign key(cs_id) references chengshi(id);
添加唯一约束
alter table shengfen add constraint uq_shengfen_SF_NAME unique(SF_NAME);
alter table shengfen add constraint uq_shengfen_SF_JC unique(SF_JC);
alter table shengfen add constraint uq_shengfen_SF_SH unique(SF_SH);
CHECK约束
##检查约束：某列取值范围限制，格式限制等。
alter table 表名 add constraint 约束名 check(约束条件);
alter table student add constraint ck_student check(gender in(‘男’,‘女’));
连接查询
select a.id,
a.sf_name,
a.sf_jc,
a.sf_sh,
b.cs_name,
b.cpdm,
b.yzbm,
b.quxian
from shengfen a, chengshi b
where a.id = b.sf_id
and b.cs_name = '银川市';
Oracle查看表的主键及外键拥有者
a.table_name 主键表,
c.column_name 主键列,
a.constraint_name 主键名称,
b.owner 外键拥有者,
b.table_name 外键表,
d.column_name 外键列,
b.constraint_name 外键名称
from user_constraints a,
user_constraints b,
user_cons_columns c,
user_cons_columns d
where a.constraint_type = 'P'
and b.constraint_type = 'R'
and a.constraint_name = c.constraint_name
and b.constraint_name = d.constraint_name
and a.owner = c.owner
and b.owner = d.owner
and a.table_name = c.table_name
and b.table_name = d.table_name;
拼接字段
select 'update chengshi set xzq_id =' || xzq_id || ' where id = ' || id || ';'
from chengshi;
select 'alter system kill session'||' '||''''||s.sid||','||s.serial#||''''||';',v$sql.sql_text
from v$process,v$session s,v$sql
where addr = paddr
and s.sql_id = v$sql.sql_id
and sql_hash_value = hash_value;
