SQL operation summary and SQL Summary

SQL operation summary and SQL Summary

Multi-Table query of SQL statements
For example, query employees and departments by department_id)
.
Method 1(General): 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
Method 2:SELECT... FROM... natural join...
Limitations: the two tables are automatically connected to the same columns (there may be multiple: department_id and
Manager_id)
SELECT last_name, department_id, department_name
FROM employees
Natural join orders ments
Method 3:SELECT... JOIN... USING...
Restrictions: This method is better than method 2. However, this method is not suitable if the names of connected columns in multiple tables are different.
SELECT last_name, department_id, department_name
FROM employees
JOIN orders ments
USING (department_id)
Method 4:SELECT... FROM... JOIN... ON...
Commonly used method. Compared with method 1, it is easier to implement Outer Join (left, right, full)
SELECT last_name, e. department_id, department_name
FROM employees e
JOIN orders ments d
ON e. department_id = d. department_id
--Internal Connection
1)
-- Equijoin
-- Unequal connection
2)
-- Non-self-connection
-- Self-connection
--External Connection
-- Left Outer Join, right Outer Join, full outer join

Create and manage tables (DDL)
Craete table/truncate table/alter table/REANME... TO/drop table... Operation
Automatic commit after completion; therefore, rollback has no effect on its operations.
1.Create a table
1) Directly create
Create table emp1 (
Name varchar2 (20 ),
Salary number (1000) default,
Id number (4 ),
Hire_date date
);
2) Created 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.Modify 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 an existing column
Alter table emp1
Rename column salary to sal;
4)Delete existing columns
Alter table emp1
Drop column birthday;
3.Clear table data(AndDelete from table_nameSeparated)
Truncate table emp2;
4.Rename a table
Rename emp2 to emp3;
5.Delete table
Drop table emp3;

Data processing DML
1) Increase
1.1Add a record
Insert into [Table name] (,)
Values (,,,,,)
1.2Copy 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) Query(Most common database operations)
Select ....
From...
Where ....
Group...
Having...
Order ....
About bundle
Restrict the column attributes and fields of the created table. Such as: not null/unique/primary
Key/foreign key/check
1.How to define constraints---When creating a table, add the corresponding attribute Constraints
1.1Table-level constraints&Column-level constraints
Create table emp1 (
Employee_id number (8 ),
Salary number (8 ),
-- Column-level constraints
Hire_date date not null,
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 constraints
Constraint emp1_emp_id_pk primary key (employee_id ),
Constraint emp1_fir_name_uk unique (first_name ),
Constraint emp1_dept_id_fk foreign key (dept_id) references
Departments (department_id) ON DELETE CASCADE
)
1.2OnlyNot nullOnly column-level constraints can be used. You can use either of the other constraints.
2.Constraints for adding and deleting tables--After creating a table, you can only add or delete it, but cannot modify it.
2.1Add
Alter table emp1
Add constaint emp1_sal_ck check (salary> 0)
2.1.1ForNot nullNot RequiredAdd, You need to useModify:
Alter table emp1
Modify (salary not null)
2.2Delete
Alter table emp1
Drop constaint emp1_sal_ck
2.3Invalidates a constraint.:This constraint still exists in the table, but does not work.
Alter table emp1
Disable constraint emp1_email_uk;
2.4Activate a constraint: the constraint is binding upon activation.
Alter table emp1
Enable constraint emp1_email_uk;