Oracle DDL语句及示例代码

墨蓝 2022-06-09 09:50 239阅读 0赞

**创建Oracle 数据表的 基本格式：**  
CREATE TABLE 表名称(  
列名称 类型 \[DEFAULT 默认值\],  
列名称 类型 \[DEFAULT 默认值\],  
列名称 类型 \[DEFAULT 默认值\],  
列名称 类型 \[DEFAULT 默认值\],  
列名称 类型 \[DEFAULT 默认值\]  
);  
注意 最后一个列不能有 , 号  
创建表  
CREATE TABLE student (  
stuno NUMBER(5),  
sname VARCHAR2(18),  
age NUMBER(3) DEFAULT 0,  
birthday DATE DEFAULT sysdate,  
test CLOB  
);  
  
**INSERT 增加数据**  
语法：insert into 表名(列名1，列名2....) values(值1,值2....);  
INSERT INTO student (stuno, sname, age, birthday) VALUES (1001, '李四', 18, '08-9月 -17');  
INSERT INTO student (stuno, sname, age, birthday) VALUES (1002, '张三', 19, to\_date('1995-12-14', 'yyyy-mm-dd'));  
简写 可以省略表名后面的列，但是值必须按照顺序完全添加  
INSERT INTO student VALUES (1003, '王五', 20, '08-9月 -17', 1111);  
  
提交（执行语句后不提交数据只是在缓存中并不会提交到数据库中）  
COMMIT;  
  
**UPDATE 修改数据**  
语法：UPDATE 表名称 SET 列名=值,列名=值... WHERE 条件  
UPDATE student SET sname = '哈哈' WHERE stuno = '1002';  
将年龄小于平均年龄的人的年龄+10  
UPDATE student SET age = age + 10 WHERE age < (SELECT avg(age) FROM student);  
将赵六的年龄修改为李四的年龄  
UPDATE student SET age = (SELECT age FROM student WHERE sname = '李四') WHERE sname = '赵六';  
  
在更新数据的时候，内部也是一个查询的操作，非常消耗资源  
记住，以后再更新数据的时候，一定不能不写WHERE  
  
**delete 删除数据**  
语法： DELETE FROM student WHERE 条件  
DELETE FROM student WHERE stuno = 1001;  
删除高于平均年龄的学生信息  
DELETE FROM student WHERE age > (SELECT avg(age) FROM student);  
删除生日小于2000之前的学生  
DELETE FROM student WHERE birthday < to\_date('2000', 'yyyy');  
删除整张表的数据  
DELETE FROM student;  
删除表  
语法：drop table 表名;  
DROP TABLE student;  
  
**修改表的结构 ALTER**  
数据表的修改  
CREATE TABLE test (  
tid NUMBER(3)  
);  
添加数据列  
ALTER TABLE test  
ADD (  
email VARCHAR2(30)  
);  
添加新列  
ALTER TABLE test  
ADD (  
sex VARCHAR2(5) DEFAULT '男'  
);  
修改数据列  
ALTER TABLE test  
MODIFY (  
email VARCHAR2(32)  
);  
  
将数据列 email 的名称修改为 yx  
ALTER TABLE student RENAME COLUMN email TO yx;  
  
删除数据列  
\-删除表中数据列的语法：  
ALTER TABLE 表名称 DROP COLUMN 列名称;  
\-删除 性别 sex 列。  
ALTER TABLE student DROP COLUMN sex;  
  
**数据表的重命名**  
语法：  
RENAME 旧表名 TO 新表名;  
  
**数据表的截断**  
可以把数据表所占用的全部资源都清空释放掉  
TRUNCATE TABLE 表名称;  
  
**数据表的复制（Oracle 特有语法）**  
复制表的基本语法：  
CREATE TABLE 表名称 AS 子查询;  
CREATE TABLE myemp AS (SELECT empno,ename FROM emp);

注意：以上复制表基本都有数据产生。如果只是想复制一张数据表的结构，而不需要复制数据的话，可以在子查询中加上条件 WHERE 1=2; 条件不成立 则不会返回任何数据行

**创建数据表案例：（运动员表，比赛项目表，成绩表）**

CREATE TABLE sporter (
      sporterid  NUMBER(4),
      name       VARCHAR2(18) NOT NULL,
      sex        VARCHAR2(3)  NOT NULL,
      department VARCHAR2(20) NOT NULL,
      CONSTRAINT pk_sporter_sporterid PRIMARY KEY (sporterid)
    );
    
    CREATE TABLE item (
      itemid   VARCHAR2(6),
      itemname VARCHAR2(20) NOT NULL,
      location VARCHAR2(30) NOT NULL,
      CONSTRAINT pk_item_itemid PRIMARY KEY (itemid)
    
    );
    
    CREATE TABLE grade (
      sporterid NUMBER(4),
      itemid    VARCHAR2(6),
      mark      NUMBER(1) DEFAULT 0,
      CONSTRAINT fk_grade_sporterid FOREIGN KEY (sporterid) REFERENCES sporter (sporterid) ON DELETE CASCADE,
      CONSTRAINT fk_grade_itemid FOREIGN KEY (itemid) REFERENCES item (itemid) ON DELETE SET NULL,
      CONSTRAINT ch_grade_mark CHECK (mark IN (6, 4, 2, 0))
    );