该系列博文根据韩顺平老师视频整理,若有错误之处,请下面留言。
附:视频下载链接!
0、以system/sys登录,创建用户u6,密码为u6,为u6授予connect, resource权限
代码如下所示:
CONN system/manager@ideapad; CREATE USER u6 IDENTIFIED BY u6; GRANT CONNECT TO u6; GRANT RESOURCE TO u6; CONN u6/u6@ideapad;
运行结果如下所示:
SQL> CONN system/manager@ideapad; 已连接。 SQL> CREATE USER u6 IDENTIFIED BY u6; 用户已创建。 SQL> GRANT CONNECT TO u6; 授权成功。 SQL> GRANT RESOURCE TO u6; 授权成功。 SQL> CONN u6/u6@ideapad; 已连接。
1、创建表t1,字段如下:
empno number(4) not null primary key, ename varchar(20) not null, sex varchar(1) not null, birthday date, salary number(7,2) default(0)
代码如下所示:
CREATE TABLE t1( empno NUMBER(4) NOT NULL PRIMARY KEY, ename VARCHAR2(20) NOT NULL, sex VARCHAR2(1) NOT NULL, birthday DATE, salary NUMBER(7,2) DEFAULT(0));
运行结果如下所示:
SQL> CREATE TABLE t1( 2 empno NUMBER(4) NOT NULL PRIMARY KEY, 3 ename VARCHAR2(20) NOT NULL, 4 sex VARCHAR2(1) NOT NULL, 5 birthday DATE, 6 salary NUMBER(7,2) DEFAULT(0)); 表已创建。
2、在表t1中插入数据,数据如下:(sex:0—男 1—女)
empno | ename | sex | birthday | salary |
1 | 丁一 | 0 | ||
2 | 丁二 | 1 |
代码如下所示:
INSERT INTO t1(empno,ename,sex) VALUES(1,'丁一','0'); INSERT INTO t1(empno,ename,sex) VALUES(2,'丁二','1'); SELECT empno,ename,sex,birthday,salary FROM t1;
运行结果如下所示:
SQL> INSERT INTO t1(empno,ename,sex) VALUES(1,'丁一','0'); 已创建 1 行。 SQL> INSERT INTO t1(empno,ename,sex) VALUES(2,'丁二','1'); 已创建 1 行。 SQL> SELECT empno,ename,sex,birthday,salary FROM t1; EMPNO ENAME S BIRTHDAY SALARY ---------- -------------------- - -------------- -------------------------------------------------------- 1 丁一 0 0 2 丁二 1 0
3、利用子查询创建表t2,子查询的条件是:性别为男
代码如下所示:
CREATE TABLE t2 AS SELECT * FROM t1 WHERE sex='0'; SELECT empno,ename,sex,birthday,salary FROM t2;
运行结果如下所示:
SQL> CREATE TABLE t2 2 AS SELECT * 3 FROM t1 4 WHERE sex='0'; 表已创建。 SQL> SELECT empno,ename,sex,birthday,salary FROM t2; EMPNO ENAME S BIRTHDAY SALARY ---------- -------------------- - -------------- ---------- 1 丁一 0 0
4、利用子查询创建表t3,要求是:只获取t1的表结构
代码如下所示:
CREATE TABLE t3 AS SELECT * FROM t1 WHERE 0=1; SELECT empno,ename,sex,birthday,salary FROM t3;
运行结果如下所示:
SQL> CREATE TABLE t3 2 AS SELECT * 3 FROM t1 4 WHERE 0=1; 表已创建。 SQL> SELECT empno,ename,sex,birthday,salary FROM t3; 未选定行
5、完整性约束
查看表t2的约束,请解释与t1相比,缺少了什么?为什么?为表t2增加缺少的约束。
代码如下所示:
SET LINESIZE 800; SELECT table_name,constraint_type,constraint_name,search_condition FROM user_constraints WHERE table_name='T2' ORDER BY table_name,constraint_type; SELECT table_name,constraint_type,constraint_name,search_condition FROM user_constraints WHERE table_name='T1' ORDER BY table_name,constraint_type; 解释:与t1相比缺少了“T1 P SYS_C005867”,即缺少了对主键的约束,是因为t2表是通过子查询得到的,会缺少一定的约束。 ALTER TABLE t2 MODIFY ( empno NUMBER(4) PRIMARY KEY); SELECT table_name,constraint_type,constraint_name,search_condition FROM user_constraints WHERE table_name='T2' ORDER BY table_name,constraint_type;
运行结果如下所示:
SQL> SET LINESIZE 800; SQL> SELECT table_name,constraint_type,constraint_name,search_condition 2 FROM user_constraints 3 WHERE table_name='T2' 4 ORDER BY table_name,constraint_type; TABLE_NAME C CONSTRAINT_NAME SEARCH_CONDITION ------------------------------ - ------------------------------ ------------------------------------ T2 C SYS_C005868 "EMPNO" IS NOT NULL T2 C SYS_C005870 "SEX" IS NOT NULL T2 C SYS_C005869 "ENAME" IS NOT NULL SQL> SELECT table_name,constraint_type,constraint_name,search_condition 2 FROM user_constraints 3 WHERE table_name='T1' 4 ORDER BY table_name,constraint_type; TABLE_NAME C CONSTRAINT_NAME SEARCH_CONDITION ------------------------------ - ------------------------------ ------------------------------------ T1 C SYS_C005864 "EMPNO" IS NOT NULL T1 C SYS_C005865 "ENAME" IS NOT NULL T1 C SYS_C005866 "SEX" IS NOT NULL T1 P SYS_C005867 SQL> ALTER TABLE t2 MODIFY ( 2 empno NUMBER(4) PRIMARY KEY); 表已更改。 SQL> SELECT table_name,constraint_type,constraint_name,search_condition 2 FROM user_constraints 3 WHERE table_name='T2' 4 ORDER BY table_name,constraint_type; TABLE_NAME C CONSTRAINT_NAME SEARCH_CONDITION ------------------------------ - ------------------------------ ------------------------------------ T2 C SYS_C005868 "EMPNO" IS NOT NULL T2 C SYS_C005870 "SEX" IS NOT NULL T2 C SYS_C005869 "ENAME" IS NOT NULL T2 P SYS_C005874
6、簇表
创建聚簇c1,字段:id int
创建聚簇表tc1,字段:id int primary key, name1 varchar2(20)
创建聚簇表tc2,字段:id int primary key, name2 varchar2(20)
创建聚簇索引ic
查看聚簇表所属的聚簇
代码如下所示:
CREATE CLUSTER c1( id NUMBER); CREATE TABLE tc1( id NUMBER PRIMARY KEY, name1 VARCHAR2(20)); CREATE TABLE tc2( id NUMBER PRIMARY KEY, name2 VARCHAR2(20)); CREATE INDEX ic ON CLUSTER c1; SELECT cluster_name,table_name FROM user_tables; SELECT cluster_name,table_name FROM user_tables WHERE cluster_name IS NOT NULL; INSERT INTO tc1(id,name1) VALUES(1,'AA1'); INSERT INTO tc1(id,name1) VALUES(2,'BB1'); INSERT INTO tc2(id,name2) VALUES(1,'AA2'); INSERT INTO tc2(id,name2) VALUES(2,'BB2'); SELECT dbms_rowid.rowid_block_number(tc1.rowid) AS tc1_blk, dbms_rowid.rowid_block_number(tc2.rowid) AS tc2_blk, tc1.id FROM tc1,tc2 WHERE tc1.id = tc2.id; 结论:基于聚簇c1创建的聚簇表tc1和tc2的数据依据公共的id列,两个表的id列的值存储在一起(在c1中)
运行结果如下所示:
SQL> CREATE CLUSTER c1( 2 id NUMBER); 簇已创建。 SQL> CREATE TABLE tc1( 2 id NUMBER PRIMARY KEY, 3 name1 VARCHAR2(20)) 4 CLUSTER c1(id); 表已创建。 SQL> CREATE TABLE tc2( 2 id NUMBER PRIMARY KEY, 3 name2 VARCHAR2(20)) 4 CLUSTER c1(id); 表已创建。 SQL> CREATE INDEX ic ON CLUSTER c1; 索引已创建。 SQL> SELECT cluster_name,table_name 2 FROM user_tables; CLUSTER_NAME TABLE_NAME ------------------------------ ------------------------------ T1 T2 T3 C1 TC2 C1 TC1 SQL> SELECT cluster_name,table_name 2 FROM user_tables 3 WHERE cluster_name IS NOT NULL; CLUSTER_NAME TABLE_NAME ------------------------------ ------------------------------ C1 TC2 C1 TC1 SQL> INSERT INTO tc1(id,name1) VALUES(1,'AA1'); 已创建 1 行。 SQL> INSERT INTO tc1(id,name1) VALUES(2,'BB1'); 已创建 1 行。 SQL> INSERT INTO tc2(id,name2) VALUES(1,'AA2'); 已创建 1 行。 SQL> INSERT INTO tc2(id,name2) VALUES(2,'BB2'); 已创建 1 行。 SQL> SELECT dbms_rowid.rowid_block_number(tc1.rowid) 2 AS tc1_blk, dbms_rowid.rowid_block_number(tc2.rowid) 3 AS tc2_blk, tc1.id 4 FROM tc1,tc2 5 WHERE tc1.id = tc2.id; TC1_BLK TC2_BLK ID ---------- ---------- ---------- 644 644 1 648 648 2
未完待续。。。。