Oracle:SQL（DDL、DML）_ddl job id 658-优快云博客

本文详细介绍了SQL中的数据类型，如NUMBER、CHAR、VARCHAR1、DATE等，并阐述了如何创建、修改和操作数据库表。通过实例演示了如何使用CREATE、DESC、ALTER TABLE等SQL语句来创建表、修改表结构、增加或删除列，以及如何使用INSERT、UPDATE、DELETE语句进行数据操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL（DDL、DML）

1.1. Oracle数据类型

1.1.1. NUMBER

NUMBER表示数字类型，经常被定义成NUMBER（P，S）形式，其中：

· P表示数字的总位数

· S表示小数点后面的位数

例如在表Emp中的Sal列的定义如下：

1. Sal NUMBER(6,1)

表示Sal列中的数据，整数位最大为4位，小数位最大位数是1位，也就是最大取值：9999.99。

1.1.1. CHAR

CHAR表示固定长度的字符类型，经常被定义成CHAR（N）形式, N表示占用的字节数，N的最大取值是1000。

例如在表Emp中的Ename列的定义如下：

1. Ename CHAR(10)；

表示Ename列中最多可存储10个字节的字符串，并且占用的空间是固定的10个字节。

1.1.3. VARCHAR1

VARCHAR1表示变长的字符类型，定义格式是VARCHAR1（N）, N表示最多可占用的字节数，最大长度是4000字节。

例如在表Emp中的JOB列的定义如下：

1. JOB VARCHAR(100)；

表示JOB列中最多可存储长度为100个字节的字符串。根据其中保存的数据长度，占用的空间是变化的，最大占用空间为100个字节。

1.1.4. DATE

DATE用于定义日期时间的数据，长度是7个字节，默认格式是：DD-MON-RR, 例如：“11-APR-71”。如果是中文环境，是“11-4月-71”这种形式。

例如在表Emp中的Hiredate列的定义如下：

1. Hiredate DATE；

表示Hiredate列中存放的是日期数据。

1.2. 创建表

1.2.1. CREATE语句

创建表的语法是：

1. CREATE TABLE [schema.]table_name(

2. column_name datatype[DEFAULT expr][,…]

3. );

创建职员表的例子：

1. --创建表emp

2. CREATE TABLE emp(

3. id NUMBER(10),

4. name VARCHAR1(10),

5. gender CHAR(1),

6. birth DATE,

7. salary NUMBER(6,1),

8. job VARCHAR1(30),

9. deptid NUMBER(1)

10. );

1.2.2. DESC语句

DESC是查看表的数据结构，语法是：

1. DESC table_name;

1.2.3. DEFAULT

可以通过DEFAULT子句给列指定默认值，例如，在emp表中给gender列赋默认值‘M’,如果插入数据时没有指定性别的员工，则默认是男性。

创建表emp1：

1. CREATE TABLE emp1(

2. id NUMBER(10),

3. name VARCHAR1(10),

4. gender CHAR(1) DEFAULT ‘M’,

5. birth DATE,

6. salary NUMBER(6,1),

7. job VARCHAR1(30),

8. deptid NUMBER(1)

9. );

1.2.4. NOT NULL

默认情况下，任何列都允许有空值。非空(Not Null)是一种约束条件，用于确保字段值不为空，当某个字段被设置了非空约束条件，这个字段中必须存在有效值，也就是说，当执行插入数据的操作时，必须提供这个列的数据，当执行更新操作时，不能给这个列的值设置为NULL。

创建表emp1，设置name字段为非空，也即是表emp1中的每条记录，name列都必须有值。

1. CREATE TABLE emp1(

2. id NUMBER(10),

3. name VARCHAR1(10) NOT NULL,

4. gender CHAR(1),

5. birth DATE,

6. salary NUMBER(6,1),

7. job VARCHAR1(30),

8. deptid NUMBER(1)

9. );

1.3. 修改表

1.3.1. 修改表名

在建表后如果希望修改表名，可以使用RENAME语句实现，语法如下，将改变表名old_name为new_name：

1. RENAME old_name TO new_name;

举例说明，如果要将表名emp修改为employee，使用如下语法：

1. RENAME emp TO employee;

1.3.1. 增加列

在建表之后，要给表增加列可以使用ALTER TABLE的ADD子句实现。语法如下：

1. ALTER TABLE table_name ADD

2. (column datatype [DEFAULT expr] [, column datatype…])

注意一点，列只能增加在最后，不能插入到现有的列中。下例给表employee增加一列hiredate，并设置默认值为当前日期。

1. ALTER TABLE employee ADD (hiredate DATE DEFAULT sysdate);

1.3.3. 删除列

在建表之后，使用ALTER TABLE的DROP子句删除不需要的列。语法如下：

1. ALTER TABLE table_name DROP (column);

删除字段需要从每行中删掉该字段占据的长度和数据，并释放在数据块中占据的空间，如果表记录比较大，删除字段可能需要比较长的时间。

下例删除表employee的列hiredate：

1. ALTER TABLE employee DROP (hiredate);

1.3.4. 修改列

建表之后，可以改变表中列的数据类型、长度和默认值，注意这种修改仅对以后插入的数据有效，另外如果表中已经有数据的情况下，把长度由大改小，有可能不成功，比如原来的类型是VARCHAR1(100),其中已经存放了100个字节长度的数据，如果要改为VARCHAR1（80），则不会修改成功。

语法如下：

1. ALTER TABLE table_name MODIFY

2. (column datatype [DEFAULT expr] [, column datatype…])

下例修改表employee的列job，并增加默认值的设置

1. ALTER TABLE employee

2. MODIFY(job VARCHAR1(40) DEFAULT 'CLERK' );

1.4. DML语句

在做后续练习之前，先熟悉本部分使用的样例表emp和dept的结构和数据：

1. DROP TABLE dept;

2. CREATE TABLE dept

3. (deptno NUMBER(1) CONSTRAINT PK_DEPT PRIMARY KEY,

4. dname VARCHAR1(14) ,

5. loc VARCHAR1(13) ) ;

7. DROP TABLE emp;

8. CREATE TABLE emp

9. (empno NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,

10. ename VARCHAR1(10),

11. job VARCHAR1(15),

12. mgr NUMBER(4),

13. hiredate DATE,

14. sal NUMBER(7,1),

15. comm NUMBER(7,1),

16. deptno NUMBER(1) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

17.

18. INSERT INTO dept VALUES

19. (10,'ACCOUNTING','NEW YORK');

20. INSERT INTO dept VALUES

21. (10,'RESEARCH','DALLAS');

22. INSERT INTO dept VALUES

23. (30,'SALES','CHICAGO');

24. INSERT INTO dept VALUES

25. (40,'OPERATIONS','BOSTON');

26.

27. SELECT * FROM dept;

28.

29.

30. INSERT INTO emp VALUES(7369,'SMITH','CLERK',7901,to_date('17-11-1980','dd-mm-yyyy'),800,NULL,10);

31. INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,to_date('10-1-1981','dd-mm-yyyy'),1600,300,30);

32. INSERT INTO emp VALUES(7511,'WARD','SALESMAN',7698,to_date('11-1-1981','dd-mm-yyyy'),1150,500,30);

33. INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,to_date('1-4-1981','dd-mm-yyyy'),1975,NULL,10);

34. INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,to_date('18-9-1981','dd-mm-yyyy'),1150,1400,30);

35. INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),1850,NULL,30);

36. INSERT INTO emp VALUES(7781,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),1450,NULL,10);

37. INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,to_date('19-4-87','dd-mm-yyyy'),3000,NULL,10);

38. INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);

39. INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);

40. INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,to_date('13-5-87','dd-mm-yyyy'),1100,NULL,10);

41. INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,to_date('3-11-1981','dd-mm-yyyy'),950,NULL,30);

42. INSERT INTO emp VALUES(7901,'FORD','ANALYST',7566,to_date('3-11-1981','dd-mm-yyyy'),3000,NULL,10);

43. INSERT INTO emp VALUES(7934,'MILLER','CLERK',7781,to_date('13-1-1981','dd-mm-yyyy'),1300,NULL,10);

1.4.1. INSERT语句

INSERT语句用来给数据表增加记录，每次增加一条记录。语法如下：

1. INSERT INTO table_name[(column[, column…])]

2. VALUES(value[, value…]);

需要注意的一点，所有的DML操作，都需要再执行事务提交语句commit，才算真正确认了此操作。

向employee表插入一条记录的例子如下：

1. INSERT INTO employee(id, name, job, salary)

2. VALUES(1001, 'rose', 'PROGRAMMER', 5500);

Oracle中的日期数据比较特殊，如果插入的列有日期字段，需要考虑日期的格式。Oracle的默认的日期格式‘DD-MON-RR，你或者按照默认格式插入数据，或者自定义日期格式，用TO_DATE函数转换为日期类型的数据。

1. --使用默认日期格式插入记录

2. INSERT INTO employee(id, name, job,birth)

3. VALUES(1001, 'martha', 'ANALYST', '01-9月-89');

5. --使用自定义日期格式插入记录

6. INSERT INTO employee(id, name, job, birth)

7. VALUES(1003, 'donna', 'MANAGER',

8. TO_DATE('1978-09-01', 'YYYY-MM-DD'));

1.4.1. UPDATE语句

UPDATE语句用来更新表中的记录，语法如下：

1. UPDATE table_name

2. SET column = value [, column = value]…

3. [WHERE condition];

其中WHERE子句是可选的，但是如果没有WHERE子句，则全表的数据都会被更新，务必小心。

下例更改职员ROSE的薪水为8500

1. UPDATE employee SET salary = 8500 WHERE name = 'ROSE';

1.4.3. DELETE语句

DELETE语句用来删除表中的记录，语法如下：

1. DELETE [FROM] table_name [WHERE condition];

和UPDATE语句一样，WHERE子句是可选的，但是如果没有WHERE子句，则全表的数据都会被删除，务必确认后再予以删除。

下例将删除职位是空的员工记录

1. DELETE FROM employee WHERE job is null;