Oracle 10g XE _ Self Study Step 1

按着入门教程 一步一步走下去

http://st-curriculum.oracle.com/tutorial/DBXETutorial/index.htm

 

发现在upload load sample.sql时, 可以load 文件但无法在editor中现实内容，也无法run。

不知道是怎么回事，最后还是启动了sqlplus。

 

SQL> conn sheila

SQL> @e:/loadsample.sql

 

最后以sheila进入web 界面时，发现表格已经创建。

可是。。。还是不知道为啥web界面中upload sql script失败，不管它了。

后来发现看不到script的原因是因为firefox无法显示code界面，IE可以。

 

创建表

CREATE TABLE audit_record_tb1

(

user_value varchar2(25),

date_recorded timestamp(6)

);

 

复制表

CREATE TABLE locations_copy AS SELECT * from locations

 

增加列

alter table "EMPLOYEES" add
("BIRTHDATE" DATE NULL)

 

增加限制

alter table "DEPENDS" add constraint
"DEPENDS_CON" check ( "GENDER" IN ('M','F'))

 

增加外键

alter table "DEPENDS" add constraint
"DEPENDS_FK" foreign key ("RELATIVEID") references "EMPLOYEES" ("EMPLOYEE_ID")

 

禁用限制

ALTER TABLE depends DISABLE CONSTRAINT depends_fk;

 

删除表

DROP TABLE locations_copy

 

Syntax

SELECT [DISTINCT] * | column [alias], ... FROM table;

SELECT	Lists one or more columns
DISTINCT	Eliminates the display of dupicate rows
*	Selects all columns
COLUMN	Selects the named column
ALIAS	Gives selected columns a customized heading
FROM	Identifies the table or tables containing the columns

 

排序

select * from employees
order by last_name;

 

条件选择

SELECT employee_id, last_name, department_id, manager_id, salary
FROM employees
WHERE department_id = 50;

 

使用变量进行条件选择

SELECT employee_id, last_name, department_id, manager_id, salary
FROM employees
WHERE department_id = :deptno;

 

 

Syntax

SELECT [DISTINCT] * | column [alias], ...
FROM table NATURAL JOIN ;

 

Syntax

SELECT [DISTINCT] * | column [alias], ...
FROM table1 JOIN table2
USING common_col_name ;

 

Natural Join

select department_id, department_name, city
from departments
natural join locations;

 

Syntax

SELECT [DISTINCT] * | column [alias], ...
FROM table1 JOIN table2
ON col_name_1 = col_name_2

 

JOIN TABLEs

select e.employee_id, e.last_name, e.hire_date
from employees e
join job_history j
ON e.employee_id = j.employee_id;

 

JOIN TABLEs Using the AND Clause

SELECT e.employee_id, e.last_name, e.department_id,

d.department_id, d.location_id

FROM employees e JOIN departments d

ON (e.department_id = d.department_id)

AND e.manager_id = 149

 

JOIN TABLEs Using the WHERE clause
SELECT e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
WHERE e.manager_id = 149

JOIN 3 TABLES In the FROM clause,
you identify the tables you want to join.
FROM table1
JOIN table2
ON conditon_x JOIN table3
ON condition_y

select e.first_name, e.last_name,
d.id, d.firstname, d.lastname, d. relation,
w.department_name
from employees e
join dependents d
on d.relativeid = e.employee_id
join departments w
on w.department_id = e.department_id
order by e.first_name