script_70.txt

orale培训教材包括了所有的sql说明和实例

/*
 * 范例名称：INSERT  与	子查询基础
 * 文件名称：sm_emp_promote.txt
 */
create table sm_emp_promote
(PromEmpID char(10) primary key,
Name   varchar2(10),
salary number(8,2)
);

--从sm_emp向sm_emp_promote表插入数据

insert into sm_emp_promote (PromEmpID,Name,salary)
select EmpID,Name,salary from sm_emp where Name in ('张飞','关羽','刘备');

select * from sm_emp_promote;



---------- sm_emp_sale_data.txt ----------
/*
 * 范例名称：INSERT  与	子查询
 * 文件名称：sm_emp_sale_data.txt
 */

--insert data need in chap10 sm_saleorderlist,sm_emp

DROP TABLE sm_saleorderlist CASCADE CONSTRAINT;
CREATE TABLE sm_saleorderlist(
    TransactionID NUMBER(10) NOT NULL UNIQUE,
    TotalPrice NUMBER(7,2) NOT NULL,
    EmployID CHAR(10) NOT NULL,
    SaleTime DATE NOT NULL,
    PRIMARY KEY(TransactionID));

--if sm_emp exists,
DROP TABLE sm_emp;

CREATE table sm_emp
(EmpID CHAR(10) PRIMARY KEY,
Name   VARCHAR2(10),
salary NUMBER(8,2),
TelNo  CHAR(8));

INSERT INTO sm_emp VALUES('0000000001','张飞',100000,'62613546');
INSERT INTO sm_emp VALUES('0000000002','关羽',100000,'62613546');
INSERT INTO sm_emp VALUES('0000000003','刘备',100000,'62613546');
INSERT INTO sm_emp VALUES('0000000007','007',100000,'62634546');
COMMIT;

--data sm_emp
EMPID      NAME           SALARY TELNO
---------- ---------- ---------- --------
000000001  张飞            10000 62613546
000000002  关羽              100 62613547
0000000003 刘备            10000 62613547
0000000007 007                   62634567

--data sm_saleorderlist
INSERT INTO sm_saleorderlist VALUES(1,100.10,'0000000001','09_9月_02');
INSERT INTO sm_saleorderlist VALUES(2,222.20,'0000000001','09_9月_02');
INSERT INTO sm_saleorderlist VALUES(3,300.10,'0000000002','09_9月_02');
INSERT INTO sm_saleorderlist VALUES(4,100.10,'0000000003','09_9月_02');
COMMIT;

--sm_saleorderlog:用于保存售货信息.sm_saleorderlist + 对应销售人员的信息
DROP TABLE sm_saleorderlog ;
CREATE TABLE sm_saleorderlog (
    TransactionID NUMBER(10) NOT NULL UNIQUE,
    TotalPrice NUMBER(7,2) NOT NULL,
    EmployID CHAR(10) NOT NULL,
    EmpName   VARCHAR2(10),
    TelNo  CHAR(8),
    SaleTime DATE NOT NULL,
    PRIMARY KEY(TransactionID));



 
  
---------- sm_emp_promote_update.txt ----------
/*
 * 范例名称：UPDATE  与	子查询
 * 文件名称：sm_emp_promote_update.txt
 */


--首先插入提升人员
drop table sm_emp_promote;
create table sm_emp_promote
(PromEmpID char(10) primary key,
Name   varchar2(10),
salary number(8,2)
);


insert into sm_emp_promote (PromEmpID,Name,salary)
select EmpID,Name,salary from sm_emp where Name in ('张飞','关羽','刘备');

SELECT * FROM sm_emp;


--更改提升人员工资为所有员工平均工资的两倍
SELECT AVG(salary)*2 FROM sm_emp;

UPDATE sm_emp_promote SET salary
= (SELECT AVG(salary)*2 FROM sm_emp);

UPDATE sm_emp_promote SET salary
= SELECT AVG(salary)*2 FROM sm_emp;
ERROR 位于第 2 行:
ORA-00936: 缺少表达式.()问题！

SELECT * FROM sm_emp_promote;

--多字段ok
UPDATE sm_emp_promote SET (salary,name)
=( SELECT AVG(salary)*2,max(name) FROM sm_emp);

---------- sm_emp_update.txt ----------
/*
 * 范例名称：UPDATE  与	子查询练习
 * 文件名称：sm_emp_update.txt
 */

以0000000007员工总销售额的1/8作为
0000000007员工的工资。
说明：2个表。
sm_saleorderlist：销售信息
Sm_emp




---------- sm_createtab.txt ----------
/*
 * 范例名称：CREATE 与子查询
 * 文件名称：sm_createtab.txt
 */

CREATE TABLE sm_saleorderlogwk AS
SELECT * FROM sm_saleorderlog;

SELECT * FROM sm_saleorderlogwk;

DROP TABLE sm_saleorderlogwk;



---------- sm_rename.txt ----------
/*
 * 范例名称：重新命名表
 * 文件名称：sm_rename.txt
 */

RENAME sm_saleorderlogWK TO sm_saleorderlogtemp;


  
---------- sm_emp_alter.txt ----------
/*
 * 范例名称：增加列
 * 文件名称：sm_emp_alter.txt
 */

--增加雇员住址字段
ALTER TABLE sm_emp ADD address  VARCHAR2(50) NOT NULL;

--如果表有纪录，出
--ERROR 位于第 1 行:
--ORA-01758: 要添加法定 (NOT NULL) 列，则表必须为空

ALTER TABLE sm_emp ADD address  VARCHAR2(50) ;



---------- sm_emp_altertab_modify.txt ----------
/*
 * 范例名称：更改列的数据类型
 * 文件名称：sm_emp_altertab_modify.txt
 */

ALTER TABLE sm_emp MODIFY address VARCHAR2(20);
DESC sm_emp;

--insert data
INSERT INTO sm_emp VALUES('0000000008','008',100,'65341234','12345678901234567890');

SELECT * FROM sm_emp;

--减少长度, column必须为empty
ALTER TABLE sm_emp MODIFY address VARCHAR2(10);
-- update sm_emp set address='123' where address='12345678901234567890';
--是不是纪录过长，改为小，试试
update sm_emp set address=null where address='123';
-- update sm_emp set address='' where address='123';

--ALTER TABLE sm_emp MODIFY address VARCHAR2(10);


--将address 改为null;
update sm_emp set address= null where address='123';
ALTER TABLE sm_emp MODIFY address VARCHAR2(10);
--ok.null是empty
--改回20为下一步测试
alter table sm_emp modify address varchar2(20);

--将null改为'';
update sm_emp set address='' where address is null;
ALTER TABLE sm_emp MODIFY address VARCHAR2(10);
--ok!
--小结:
--null ,''都是empty

ALTER TABLE sm_emp MODIFY address DATE;
--ORA-01439: 要更改数据类型，则要修改的列必须为空 (empty)
--什么是empty？
update sm_emp set address=null where empid='0000000008';
update sm_emp set address='' where empid='0000000008'


--改回为VARCHAR2(20);为改变字段的null属性做准备
ALTER TABLE sm_emp MODIFY address VARCHAR2(20);
----------

--null
update sm_emp set address= null;


--update sm_emp set address='';在此时null 与''认为相同。

ALTER  TABLE  sm_emp  
MODIFY address  NOT    NULL;
--ORA-02296: 无法启用  - 找到空值

UPDATE sm_emp SET address='北京';
ALTER  TABLE  sm_emp  
MODIFY address  NOT    NULL;

DESC sm_emp;

 

---------- sm_rename_column.txt ----------
/*
 * 范例名称：更改字段名
 * 文件名称：sm_rename_column.txt
 */

--modify 不能用于更改字段名
--alter table sm_emp modify address sm_address；err!
drop table sm_temp;

CREATE TABLE  sm_temp AS
SELECT empid,name,salary,telno,address sm_address
FROM sm_emp;


CREATE TABLE  sm_temp1(id,name,sal,telno) AS
SELECT empid,name,salary,telno 
FROM sm_emp;

desc sm_temp;

DROP TABLE sm_emp;

RENAME sm_temp TO sm_emp;

DESC sm_emp;