1 SQL statement multi-table query

For example, query employees (employee table) and departments (department table) information according to department_id.  

Mode 1 (Universal): SELECT... FROM... WHERE

SELECT e.last_name,e.department_id,d.department_name
 FROM employees e,departments d 
where e.department_id = d.department_id 

Mode 2: SELECT... FROM... NATURAL JOIN...

Limitations: The same columns in two tables can be automatically joined (there may be multiple: department_id and manager_id)

SELECT last_name,department_id,department_name 
FROM employees 
NATURAL JOIN departments 

Mode 3: SELECT... JOIN... USING...

Limitations: better than mode 2, but this method is not appropriate if the join columns of multiple tables are different.

SELECT last_name,department_id,department_name 
FROM employees 
JOIN departments 
USING(department_id) 

Mode 4: SELECT... FROM... JOIN... ON....

Common mode, more easily to achieve Outlink (left, right, full)

SELECT last_name,e.department_id,department_name 
FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id

- internal connection

1) 

- Equivalent connection

- unequal connection

2) 

- Non-self-connection

Self connection

 

External connection

- Left outer connection, right outer connection, full outer connection

2 Create and manage tables (DDL)

CRAETE TABLE/TRUNCATE TABLE/ALTER TABLE/REANME... TO/DROP TABLE... After operation, commit automatically; therefore, rollback has no effect on its operation.

1. Create tables

1) Direct creation

create table emp1( 
name varchar2(20), 
salary number(8,2)default 1000, 
id number(4), 
hire_date date 
); 

2) Create by subquery

create table emp2 
as 
select last_name name,employee_id id,hire_date  
from employees; 

Or

create table emp2 
as 
select last_name name,employee_id id,hire_date
from employees 
where department_id = 80;/where 1=2; 

2. Modification of the table

1) Add new columns

alter table emp1 
add(birthday date) 

2) Modify existing columns

alter table emp1 
modify(name varchar2(25) default 'abc') 

3) Rename existing columns

alter table emp1 
rename column salary to sal;

 

4) Delete existing columns

alter table emp1
drop column birthday; 

3. Empty the data in the table (distinguished from delete from table_name)

truncate table emp2; 

4. Rename table

rename emp2 to emp3; 

5. Delete tables

      drop table emp3; 

3 Data Processing DML

1) increase

1.1 Add a record

insert into    [Table name](,,,,,) 
values(,,,,,) 

1.2 Copy data from other tables

insert into [Table name]
select .... from [Another table] 
where .... 

2) change

update [Table name] 
set ..... 
where .... 

3) delete

delete from [Table name] 
where ....

 

4) Check (most commonly used database operations)

select .... 

from ... 

where .... 

group by ... 

having ... 

order by ....

4 constraint

Restrictions on column attributes and fields of created tables. For example: not null/unique/primary key/foreign key/check

1. How to define constraints - adding constraints for corresponding attributes while creating tables

 

1.1 Table Level Constraints - Column Level Constraints

create table emp1( 
employee_id number(8), 
salary number(8), 
hire_date date not null,     --Column level constraint  
dept_id number(8), 
email varchar2(8) constraint emp1_email_uk unique, 
name varchar2(8) constaint emp1_name_uu not null, 
first_name varchar2(8), 
--Table level constraint 
constraint emp1_emp_id_pk primarykey(employee_id), 
constraint emp1_fir_name_uk unique(first_name), 
constraint emp1_dept_id_fk foreignkey(dept_id) references departments(department_id) ON DELETE CASCADE 
) 

1.2 Only not null can use column level constraints. Other constraints are available in both ways.

2. Constraints on adding and deleting tables - After creating tables, only add and delete tables, not modify them.

2.1 add

alter table emp1 
add constaint emp1_sal_ck check(salary > 0) 

2.1.1 For not null, instead of add ing, you need to use modify:

alter table emp1 
modify (salary not null) 

2.2 delete

alter table emp1 
drop constaint emp1_sal_ck 

2.3 Invalidate a constraint: This constraint still exists in the table, but does not work.

alter table emp1 
disable constraint emp1_email_uk; 

2.4 Activate a constraint: After activation, the constraint is binding.

alter table emp1 
enable constraint emp1_email_uk;