Oracle数据库Sql语句详解大全

nullOracleSQLOracleSQLnull第一章SELECT查询本章目标本章目标写一条SELECT查询语句 在查询中使用 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 达式、运算符 对空值的处理 对查询字段起别名 查询字段的连接SELECT查询基本语法SELECT查询基本语法SELECT <列名> FROM <表名> SQL> SELECT * FROM s_emp请查询出s_emp表中所有的员工信息:查询指定列查询指定列SQL> SELECT dept_id , salary FROM s_emp请查询出s_emp表中所有的员工的部门ID，工资:运用算数表达式运用算数表达式SQL> SELECT salary*12 FROM s_emp请查询出s_emp表中所有的员工的年薪:运用算数表达式运用算数表达式括号可以改变运算符运算的优先顺序:SQL> SELECT last_name, salary, 12 * salary + 100 2 FROM s_emp; ... Velasquez 2500 30100SQL> SELECT last_name, salary, 12 * (salary + 100) 2 FROM s_emp; ... Velasquez 2500 31200列别名列别名请查询出s_emp表中所有的员工的姓名:SQL> SELECT firname_name || last_name FROM s_empSQL> SELECT firname_name || last_name “姓名” FROM s_emp请查询出s_emp表中所有的员工的姓名:空值的处理空值的处理请查询出s_emp表中所有的员工的工资:SQL> SELECT last_name, salary*commission_pct/100 “工资” 2 FROM s_emp;SQL> SELECT last_name , salary+salary*NVL(commission_pct,0)/100 2 FROM s_emp;去掉重复行去掉重复行请查询出s_dept表的部门名称:SQL> SELECT name 2 FROM s_dept;SQL> SELECT DISTINCT name 2 FROM s_dept;去掉多列重复行去掉多列重复行SQL> SELECT DISTINCT dept_id, title 2 FROM s_emp;请查询出s_emp表中所有的员工的部门ID及职称:小结小结查询表的全部 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 查询指定的列 给列起别名 NVL函数及字符串连接符 重复行的处理DistinctSELECT [DISTINCT] {*,column[alias],...} FROM table;引言引言null第二章 条件查询本章目标本章目标WHERE条件查询 在查询中使用表达式、运算符 使用LIKE、BETWEEN、IN进行模糊查询第一章内容回顾第一章内容回顾对员工表中信息进行查询，具体要求如下： 1. 查询s_emp表要求输出员工姓名(firs_name、last_name)和实际工资(基本工资+提成)：查询基本语法查询基本语法SELECT <列名> FROM <表名> [WHERE <查询条件表达式>]请查询出s_emp表中dept_id为41的员工信息:WHERE条件查询WHERE条件查询请查询出s_emp表中last_name为Smith的员工的信息:SELECT * FROM s_emp WHERE last_name = 'Smith'请查询出s_emp表中部门ID为50并且工资大于1500的员工的信息:SELECT * FROM s_emp WHERE salary>1500 and dept_id=50WHERE条件查询-BETWEEN&INWHERE条件查询-BETWEEN&IN请查询出s_emp表中工资在1500到2000之间的员工信息：SELECT * FROM s_emp WHERE salary between 1500 and 2000请查询出s_dept表中region_id为1，3的部门信息：SELECT * FROM s_dept WHERE region_id in (1,3)WHERE条件查询-likeWHERE条件查询-like请查询出s_emp表中姓中含有字母a的员工信息：SELECT * FROM s_emp WHERE last_name like '%a%'请查询出当前用户下所有以‘s_’开头的表： SELECT table_name FROM user_tables WHERE table_name like 'S\_%' escape '\'请查询出s_emp表姓中第二个字母为a的员工信息：SELECT * FROM s_emp WHERE last_name like ‘_a%'空值的查询空值的查询查询出s_emp表中非销售职位的员工信息： SELECT * FROM s_emp WHERE commission_pct is null 课堂练习课堂练习已建立好的S_emp表，对此表中的数据进行模糊查询，具体要求如下： 查询一名last_name以“M”开头的员工，他的dept_id好像是’3X’ 查询工资在1200至1500之间的员工 查询来自部门ID为(41，42，43)的员工 查询结果排序查询结果排序查询出s_emp表将部门ID为41的员工的工资按从高到低排列显示出来： SELECT * FROM s_emp WHERE dept_id=41 ORDER BY salary DESC SQL> SELECT last_name, dept_id, salary 2 FROM s_emp 3 ORDER BY dept_id, salary DESC;小结小结Where条件查询 Between…and & In & Like 模糊查询 对查询结果排序 SELECT [DISTINCT] {*, column [alias], ...} FROM table [WHERE condition(s)] [ORDER BY {column, expr, alias} [ASC|DESC]];null第三章 单行函数本章目标本章目标熟悉各种类型单行函数的使用 掌握转换函数的使用两种SQL函数两种SQL函数单行函数 Character Number Date ConversionSINGLE-ROW FUNCTION多行函数 GroupMULTI-ROW FUNCTION字符函数字符函数LOWER 将字符串转换成小写 UPPER 将字符串变为大写 INITCAP 将字符串的第一个字母变为大写 CONCAT 拼接两个字符串，与 || 相同 SUBSTR 取字符串的子串 LENGTH 以字符给出字符串的长度 NVL 以一个值来替换空值 字符函数举例字符函数举例LOWER('SQL Course') sql course UPPER('SQL Course') SQL COURSE INITCAP('SQL Course') Sql Course SELECT * FROM s_emp WHERE last_name=‘PATEL’ SELECT * FROM s_emp WHERE UPPER( last_name)=‘PATEL’字符操作函数字符操作函数CONCAT('Good', 'String') GoodString SUBSTR('String',1,3) Str LENGTH('String') 6数字函数数字函数ROUND(value,precision) 按precision 精度4舍5入 TRUNC(value,precision) 按precision 截取valueSQL> SELECT round(55.5),round(-55.5),trunc(55.5),trunc(-55.5) FROM dual; round(55.5) round(-55.5) trunc(55.5) trunc(-55.5) ----------- ------------ ----------- ------------- 56 -56 55 -55SQL> SELECT TRUNC (124.16666, -2) trunc1, trunc(124.16666,2) FROM dual; TRUNC1 TRUNC(124.16666,2) ---------- ------------------ 100 124.16Round&Trunc函数Round&Trunc函数ROUND (45.923, 2) 45.92 ROUND (45.923, 0) 46 ROUND (45.923, -1) 50 TRUNC (45.923, 2) 45.92 TRUNC (45.923) 45 TRUNC (45.923, -1) 40日期函数日期函数MONTHS_BETWEEN(date2,date1) 给出 Date2 - date1的月数 ADD_MONTHS 增加或减去月份 NEXT_DAY ( date,’day’) 给出日期date之后下一天的日期 LAST_DAY(date) 返回日期所在月的最后一天 日期函数日期函数MONTHS_BETWEEN(‘01-SEP-95’,‘11-JAN-94’) 19.774194 ADD_MONTHS('11-JAN-94',6) '11-JUL-94‘ NEXT_DAY('01-SEP-95','FRIDAY') '08-SEP-95‘ LAST_DAY('01-SEP-95') '30-SEP-95'日期函数日期函数ROUND('25-MAY-95','MONTH') 01-JUN-95 ROUND('25-MAY-95 ','YEAR') 01-JAN-95 TRUNC('25-MAY-95 ','MONTH') 01-MAY-95 TRUNC('25-MAY-95 ','YEAR') 01-JAN-95转换函数转换函数TO_CHAR(date, 'fmt') 转换日期格式到字符串 用下列格式显示字符为数字： 9 代表一个数字. 0 显示前缀零. $ 根据本地语言环境显示货币. L 采用当地货币符号 . 打印一个小数点. , 千位计算法显示.日期格式日期格式To-char举例To-char举例SQL> SELECT last_name, TO_CHAR(start_date, 2 'fmDdspth "of " Month YYYY fmHH:MI:SS AM') HIREDATE 3 FROM s_emp 4 WHERE start_date LIKE '%91';SQL> SELECT 'Order ‘ || TO_CHAR(id) || 2 ' was filled for a total of ‘ || TO_CHAR(total,'fm$9,999,999') 3 FROM s_ord 4 WHERE ship_date = '21-SEP-92';To-char举例To-char举例查询员工表中入职日期在7月份的员工信息：SELECT * FROM s_emp WHERE to_char(start_date,'mm')=07RR 日期格式RR 日期格式Current Year 1995 1995 2001 2001Specified Date 27-OCT-95 27-OCT-17 27-OCT-17 27-OCT-95RR Format 1995 2017 2017 1995YY Format 1995 1917 2017 2095If the specified two-digit year isIf two digits of the current year are0-490-4950-9950-99The return date is in the current century.The return date is in the century after the current one.The return date is in the century before the current one.The return date is in the current century.转换函数转换函数TO_NUMBER(‘String’) 转换字符串到数字 TO_DATE(‘String’) 转换字符串到日期格式 SELECT to_date(‘2009-09-22’,’yyyy-mm-dd’) FROM dual 转换函数的嵌套转换函数的嵌套F3(F2(F1(col,arg1),arg2),arg3)Step 1 = Result 1Step 2 = Result 2Step 3 = Result 3转换函数嵌套举例转换函数嵌套举例SQL> SELECT last_name, 2 NVL(TO_CHAR(manager_id),'No Manager') 3 FROM s_emp 4 WHERE manager_id IS NULL;查询员工表中manager_id为空的员工查询出来，并将空列的值置为“No Manager”：小结小结字符函数 日期函数 数值函数 转换函数null第四章 关联查询本章目标本章目标在一张或多张表中使用等值或非等值连接 使用外连接查询 自连接查询等值连接的种类等值连接的种类等值连接 非等值连接 外连接 自连接表间的关系S_EMP Table ID LAST_NAME DEPT_ID -- --------------- ------- 1 Velasquez 50 2 Ngao 41 3 Nagayama 31 4 Quick-To-See 10 5 Ropeburn 50 6 Urguhart 41 7 Menchu 42 8 Biri 43 9 Catchpole 44 10 Havel 45 11 Magee 31 12 Giljum 32 13 Sedeghi 33 14 Nguyen 34 15 Dumas 35 16 Maduro 41表间的关系S_DEPT Table ID NAME REGION_ID -- --------------- --------- 30 Finance 1 31 Sales 1 32 Sales 2 43 Operations 3 50 Administration 1S_REGION Table ID NAME -- --------------------- 1 North America 2 South America 3 Africa / Middle East 4 Asia 5 Europe简单关联查询的语法简单关联查询的语法查询员工表中last_name为’Biri’的员工的last_name与部门名称查询出来：SELECT table.column, table.column FROM table1, table2 WHERE table1.column1 = table2.column2SQL> SELECT e.last_name , d.name 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id and e.last_name = ‘Biri’非等值连接非等值连接SQL> SELECT e.ename, e.job, e.sal, s.grade 2 FROM emp e, salgrade s 3 WHERE e.sal BETWEEN s.losal AND s.hisal;自连接自连接S_EMP (WORKER)S_EMP (MANAGER)LAST_NAME MANAGER_ID ID LAST_NAME --------- ---------- -- ---------- Ngao 1 1 Velasquez Nagayama 1 1 Velasquez Ropeburn 1 1 Velasquez Urguhart 2 2 Ngao Menchu 2 2 Ngao Biri 2 2 Ngao Magee 3 3 Nagayma Giljum 3 3 Nagayma ... ...自连接自连接查询员工表中last_name为’Biri’的员工的last_name及其部门经理名称查询出来：SQL> SELECT worker.last_name||' works for '||manager.last_name 2 FROM s_emp worker, s_emp manager 3 WHERE worker.manager_id = manager.id;外连接外连接SQL> SELECT worker.last_name||' works for '||manager.last_name 2 FROM s_emp worker, s_emp manager 3 WHERE worker.manager_id = manager.id （+）;SQL> SELECT worker.last_name||' works for '||manager.last_name 2 FROM s_emp worker, s_emp manager 3 WHERE worker.manager_id（+） = manager.id;SQL> SELECT worker.last_name||' works for '||manager.last_name 2 FROM s_emp worker left outer join s_emp manager 3 on worker.manager_id= manager.id;内连接内连接SQL> SELECT e.last_name , d.name 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id and e.last_name = ‘Biri’SQL> SELECT e.last_name , d.name 2 FROM s_emp e inner join s_dept d on e.dept_id = d.id 3 WHERE e.last_name = ‘Biri’小结小结等值连接 非等值连接 外连接 自连接null第五章 组函数本章目标本章目标定义及有效的使用组函数 使用Group By对查询数据分组 使用HAVING子句对分组后的数据进行过滤 使用Group By的查询语法使用Group By的查询语法SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];常用组函数常用组函数AVG (DISTINCT|ALL|n) COUNT (DISTINCT|ALL|expr|*) MAX (DISTINCT|ALL|expr) MIN (DISTINCT|ALL|expr) SUM (DISTINCT|ALL|n)应用举例应用举例查询s_emp表中所有员工的平均工资：SQL> SELECT avg（salary） 2 FROM s_emp查询s_emp表中各个部门员工的平均工资及部门名称：SQL> SELECT e.dept_id, max(d.name),avg(e.salary) 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id 4 GROUP BY dept_id；应用举例应用举例查询s_emp表中31部门一共有多少员工：SQL> SELECT COUNT(*) 2 FROM s_emp 3 WHERE dept_id = 31;查询s_emp表中销售人员的数量(提成率不为空的记录个数)：SQL> SELECT COUNT(commission_pct) 2 FROM s_emp应用举例应用举例SQL> SELECT e.dept_id, max(d.name),avg(e.salary),sum(salary) 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id 4 GROUP BY dept_id 5 ORDER BY sum(salary);查询s_emp表中各个部门员工的平均工资,工资总和及部门名称并按照工资总和排序：应用举例应用举例SQL> SELECT e.dept_id, max(d.name),avg(e.salary),sum(salary) 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id and e.dept_id !=41 4 GROUP BY dept_id 5 ORDER BY sum(salary);查询s_emp表中除41部门以外的部门员工的平均工资,工资总和及部门名称并按照工资总和排序：SQL> SELECT e.dept_id, max(d.name),avg(e.salary),sum(salary) 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id 4 GROUP BY dept_id 5 HAVING e.dept_id !=41 6 ORDER BY sum(salary);应用举例应用举例求平均工资高于1500的部门的工资总和，最高工资，最低工资：SQL> SELECT e.dept_id, max(d.name),avg(e.salary),sum(e.salary) 2 FROM s_emp e , s_dept d 3 WHERE e.dept_id = d.id 4 GROUP BY dept_id 5 HAVING avg(e.salary)>1500 6 ORDER BY avg (e.salary);课堂练习课堂练习求不以“VP”开头职位的，各个职位中工资总和大于5000的职位及工资总和，并按工资总和排序：SQL> SELECT title, SUM(salary) PAYROLL 2 FROM s_emp 3 WHERE title NOT LIKE 'VP%' 4 GROUP BY title 5 HAVING SUM(salary) > 5000 6 ORDER BY SUM(salary);小结小结SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];null第六章 子查询子查询子查询查询s_emp表中工资最低的员工的姓名：SQL> SELECT min（salary） 2 FROM s_empSQL> SELECT last_name 2 FROM s_emp 3 WHERE salary = 最小工资（上一条的运行结果）SQL> SELECT last_name 2 FROM s_emp 3 WHERE salary = (SELECT min(salary) FROM s_emp)子查询子查询查询s_emp表中平均工资低于32部门的部门ID：SQL> SELECT avg（salary） 2 FROM s_emp 3 WHERE dept_id = 32;SQL> SELECT dept_id,avg(salary) 2 FROM s_emp 3 GROUP BY dept_id 4 HAVING avg(salary)<32部门的平均工资SQL> SELECT dept_id,avg(salary) 2 FROM s_emp 3 GROUP BY dept_id 4 HAVING avg(salary)<(SELECT avg(salary) 5 FROM s_emp 6 WHERE dept_id = 32) 7 ORDER BY DEPT_ID;子查询子查询查询s_emp表中平均工资低于32部门的部门ID及名称：SQL> SELECT dept_id,avg(salary),name 2 FROM s_emp,s_dept 3 WHERE s_emp.dept_id = s_dept.id 4 GROUP BY dept_id,name 5 HAVING avg(salary)<(SELECT avg(salary) 6 FROM s_emp 7 WHERE dept_id = 32) 8 ORDER BY DEPT_ID;子查询举例子查询举例SQL> SELECT last_name, first_name, title 2 FROM s_emp 3 WHERE dept_in = 4 (SELECT ID 5 FROM s_dept 6 WHERE name = 'Finance' 7 OR region_id = 2); ORA-01427:single-row subquery returns more than one row小结小结SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);null第七章 数据建模及数据库设计本章目标本章目标了解系统开发的 步骤 新产品开发流程的步骤课题研究的五个步骤成本核算步骤微型课题研究步骤数控铣床操作步骤 数据关系的定义 理解实体关系映射图（E-R图） 系统开发步骤系统开发步骤数据模型数据模型Model of system in client's mindEntity model of client's modelTable model of entity modelTables on diskEntity Relationship Modeling ConceptsEntity Relationship Modeling ConceptsEntity A thing of significance about which information needs to be known Examples: customers, sales representatives, orders Attribute Something that describes or qualifies an entity Examples: name, phone, identification number Relationship An association between two entities Examples: orders and items, customers and sales representativesEntity Relationship ModelEntity Relationship ModelCreate an entity relationship diagram from business specifications or narratives. Scenario ". . . Assign one or more customers to a sales representative . . ." ". . . Some sales representatives do not yet have assigned customers . . ."Relationship TypesRelationship TypesOne-to-one Have a degree of one and only one in both directions. Are rare. Example: Husband and wife. Many-to-one Have a degree of one or more in one direction and a degree of one and only one in the other direction. Are very common. Example: passengers and plane. Many-to-many Have a degree of one or more in both directions. Are resolved with an intersection entity. Example: Employees and skills.数据库3范式数据库3范式数据库中的每一列都是不可再分的基本数据项，同一列中不能有多个值 数据库表中不存在非关键字段对任何候选关键字段的部分依赖 数据库表中不存在非关键字段对任何候选关键字段的传递引用 约束类型约束类型PK ——Primary Key 唯一且非空 FK ——Foreign Key 外键约束，值引用另一张表 已经存在的数据 UK——Unique Key 唯一且可为空 NOT NULL 非空约束举例约束举例 ID LAST_NAME FIRST_NAME ... DEPT_ID ... 1 Velasquez Carmen 50 2 Ngao LaDoris 41 3 Nagayama Midori 31 4 Quick-To-See Mark 10 5 Ropeburn Audry 50 ID NAME REGION_ID 10 Finance 1 31 Sales 1 41 Operations 1 50 Administration 1Primary KeyForeign KeyS_EMP TableS_DEPT TablePrimary Keynull第八章 创建表本章目标本章目标掌握创建表的语法 Oracle的数据类型 使用约束 数据结构数据结构一个Oracle数据库包含下列几种数据结构： ——Table 存储数据 ——View 从一个表或多个表的数句中得到的子集 ——Sequence 生成主键值 ——Index 提高查询性能建表语法建表语法CREATE TABLE [schema.]table (column datatype [DEFAULT expr] [column_constraint], ... [table_constraint]);Oracle的数据类型Oracle的数据类型Char(size) 定长字符型，字符长度不够自动在右边加空格符号 Varchar2(size) 可变长字符型，大小必须指定 Number(m,n) 数字型，可存放实数和整数 Date 日期类型 Blob 2进制大对象其最大大小为4 GB 此数据类型映射到 Byte 类型的 Array。 Clob 2进制大对象其最大大小为4 GB 此数据类型映射到 String 命名 规范 编程规范下载gsp规范下载钢格栅规范下载警徽规范下载建设厅规范下载 命名规范必须以字母开头 1-30个字符长度 只允许包含A–Z, a–z, 0–9, _, $, and # 在一个数据库保证命名的唯一 不能使用Oracle内部的关键字建表举例建表举例CREATE TABLE Mytest( id number， name varchar2(32) );CREATE TABLE Mytest( id number deault 11， name varchar2(32) );建表使用约束举例建表使用约束举例CREATE TABLE Mytest( id number check(id>10), name varchar2(32) );CREATE TABLE Mytest( id number check(id>10), name varchar2(32) not null );CREATE TABLE Mytest( id number UNIQUE, name varchar2(32) not null );建表主键约束举例建表主键约束举例CREATE TABLE Mytest( id number primary key, name varchar2(32) not null );CREATE TABLE Mytest( id number, name varchar2(32) not null, primary key (id) );CREATE TABLE Mytest( m number, n number, primary key (m,n) );建表外键约束举例建表外键约束举例CREATE TABLE parent( id number primary key, name varchar2(32) );CREATE TABLE child( id number primary key, p_id number references parent(id) );CREATE TABLE child( id number primary key, p_id number , foreign key(p_id) references parent(id) );约束命名约束命名CREATE TABLE child( id number constraint mytest_pk primary key, p_id number , foreign key(p_id) references parent(id) );查看表的约束查看表的约束SELECT * FROM user_constraints WHERE table_name ='CHILD' CONSTRAINT_TYPE C   --check P   --primary key R   --forgien key U   --unique级联删除级联删除CREATE TABLE child( id number primary key, p_id number references parent(id) on delete cascade );CREATE TABLE child( id number primary key, p_id number references parent(id) on delete set null );子查询创建表子查询创建表CREATE TABLE emp_41 as ( select * from s_emp where dept_id =41 );null第九章 对数据的操作本章目标本章目标在已创建表中插入新的数据 修改已经存在的数据 删除表中的数据 理解事物控制及其重要性 DML命令DML命令Description Adds a new row to the table. Modifies existing rows in the table. Removes existing rows from the table. Makes all pending changes permanent. Allows a rollback to that savepoint marker. Discards all pending data changes.Command INSERT UPDATE DELETE COMMIT SAVEPOINT ROLLBACKInsert插入语法Insert插入语法INSERT INTO table [(column [, column...])] VALUES (value [, value...]);插入举例插入举例CREATE TABLE Mytest( id number primary key, name varchar2(32), birth Date );INSERT INTO mytest (id,name,brith) VALUES (1,’BluesWang’,’?’);更新语法更新语法UPDATE table SET column = value [, column = value] [WHERE condition];删除语法删除语法DELETE [FROM] table [WHERE condition];Database TransactionsDatabase TransactionsContain one of the following statements: DML commands that make up one consistent change to the data One DDL command One DCL command Begin when the first executable SQL command is executed. End with one of the following events: COMMIT or ROLLBACK DDL or DCL command executes (automatic commit) Errors, exit, or system crash还原点还原点SQL> UPDATE... SQL> SAVEPOINT update_done; Savepoint created. SQL> INSERT... SQL> ROLLBACK TO update_done; Rollback complete.Controlling TransactionsControlling TransactionsCOMMITROLLBACKINSERTUPDATEINSERTDELETESavepoint Marker ASavepoint Marker BROLLBACKROLLBACK to AROLLBACK to Bnull第十章 修改表结构及添加约束本章目标本章目标增加或者修改列 添加删除约束 删除表 删除表所有的数据 添加列添加列ALTER TABLE table ADD (column datatype [DEFAULT expr][NOT NULL] [, column datatype]...);添加列举例添加列举例向下表mytest添加名为age，类型为number的一列：ALTER TABLE mytest ADD (age number);再向下表mytest添加名为salary,类型为number(9,1)缺省值为8888的一列：ALTER TABLE mytest ADD (salary number(9,1) default 8888);删除列删除列ALTER TABLE table DROP column [, column] ...;删除列举例删除列举例将表mytest的salary列删除：ALTER TABLE mytest drop column salary ;修改列修改列ALTER TABLE table MODIFY (column datatype [DEFAULT expr][NOT NULL] [, column datatype]...);修改列举例修改列举例将表mytest的age列由原来的number类型更改为number（4，2）类型：ALTER TABLE mytest modify (age number(4,2)) ;添加约束添加约束SQL> ALTER TABLE table 2 ADD [CONSTRAINT constraint] type (column);添加约束举例添加约束举例将表mytest的id列添加主键约束：ALTER TABLE mytest ADD constraints mytest_pk primary key(id);将表child的p_id列添加外键约束：ALTER TABLE child ADD constraints c_fk foreign key(p_id) references parent(id);删除约束举例删除约束举例SQL> ALTER TABLE child 2 DROP CONSTRAINT c_fk;删除表及表的重命名删除表及表的重命名DROP TABLE table [CASCADE CONSTRAINTS];SQL> RENAME 表名 TO 新名;删除表数据删除表数据SQL> TRUNCATE TABLE 表名;小结小结Command CREATE TABLE ALTER TABLE DROP TABLE RENAME TRUNCATE Description Creates a table and indicated constraints. Modifies table structures and constraints. Removes the