note_SQL_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可以。

创建表

CREATETABLE audit_record_tb1

(

user_valuevarchar2(25),

date_recordedtimestamp(6)

);

复制表               

增加列

altertable "EMPLOYEES" add
("BIRTHDATE" DATE NULL)

增加限制

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

增加外键

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

禁用限制

ALTERTABLE depends DISABLE CONSTRAINT depends_fk;

删除表                                                       

DROPTABLE 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;

条件选择

SELECTemployee_id, last_name, department_id, manager_id, salary
FROM employees
WHERE department_id = 50;

使用变量进行条件选择

SELECTemployee_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 ;

 

NaturalJoin

selectdepartment_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

JOINTABLEs

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

JOINTABLEs Using the AND Clause

SELECTe.employee_id, e.last_name, e.department_id,

d.department_id,d.location_id

FROMemployees e JOIN departments d

ON(e.department_id = d.department_id)

ANDe.manager_id = 149

JOINTABLEs 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