Oracle SQL Syntax Series (i)

first, Structured Query language ( SQL )

1 , Data manipulation language ( DML )

INSERT

UPDATE

DELETE

2 , data definition language

CREATE

Alter

DROP

RENAME

TRUNCATE

3 , transaction control ( TC )

COMMIT

ROLLBACK

SavePoint

4 , Data Control Language ( DCL )

GRANT

REVOKE

Appendix: Execute SQL script under Linux

@/tmp/create_table.sql

Second, create a database

Create DATABASE Xiaoxu

User Sys identified by 123456

User system identified by 123456

LogFile

Group 1 ('/u01/logdata/redo01_xiaoxu.log ') size 20M,

Group 2 ('/u01/logdata/redo02_xiaoxu.log ') size 20M,

Group 3 ('/u01/logdata/redo03_xiaoxu.log ') size 20M,

Maxlogfiles 6

Maxlogmembers 5

Maxloghistory 8

Maxdatafiles 300

Maxinstances 1

Archivelog

Force logging

DataFile '/u02/dbdata/system_xiaoxu_01.dbf ' size 300M,

Undo Tablespace Undotbs

DataFile '/u03/dbdata/undo_xiaoxu_01.dbf ' size 80M,

Default Temporary Tablespace Temp

Tempfile '/u04/dbdata/temp_xiaoxu_01.dbf ' size 60M,

Extent management Local uniform size 1M,

Character Set Al32utf8

Time_zone = ' asia/beijing ';

Iii. retrieving information from a database table

1. column Operations

Select 10 * (10/2-2)

from dual;

Description

(1) Line identifier (shoddy): rowID, RowNum

(2) using a space in an alias and preserving the case of the aliased text, you must enclose it in double quotation marks.

(3) using the Join operator | | To merge the output of the column

Select First_Name | | "| | Last_Name as "Customer name"

From customers;

(4) Understanding null values

①select * FROM Customers

where DOB is null;

②select CUSTOMER_ID,FIRST_NAME,LAST_NAME,NVL (phone, ' not phone number ') as Phone_number

From customers;

(5) Prevent duplicate rows from appearing

SELECT DISTINCT customer_id

From purchases;

(6) Comparison operators

Any: Arbitrary value

All: All values

=: equals

<> or! =: Not equal to

: Greater Than

>=: greater than or equal to

<: Less than

<=: Less than or equal to

(7) SQL operator

Like

Inch

Between

Is null

Is Nan

You can also use not to make an operator mean the opposite.

(8) using the LIKE operator

Underline _: Matches one character

Percent percent: matches any character

(9) Logical operators

X and Y

X OR y

Not X

Note: The comparison operator takes precedence over and, and the priority of and is higher than or.

(10) Sort

ORDER by

The ORDER by clause must be located after the from or WHERE clause.

2. Add, modify, delete rows

(1) Adding rows to the table

INSERT INTO customers

VALUES (6, ' Xiaoxu ', ' whit ', ' August-September-66 ', ' 900-100-1111 ');

(2) Modifying rows in a table

Update Customers

Set last_name = ' Orange '

where customer_id = 6;

(3) Deleting rows

Delete from Customers

where customer_id = 6;

Iv. Connection

1 , equivalent connection ( = )

(1) SELECT statement to connect two tables

To concatenate two tables in a query, you need to specify two tables in the FROM clause of the query, specifying the related columns in the two tables in the WHERE clause.

Select products.name,product_types.product_type_id

From Products,product_types

where products.product_type_id = product_types.product_type_id

and products.product_id = 3;

(2) SELECT statement to connect four tables

Select C.first_name,c.last_name,p.name as product,pt.name as type

From Customers c,purchases pr,products P,product_types PT

where c.customer_id = pr.customer_id

and p.product_id = pr.product_id

and p.product_type_id = pt.product_type_id;

2 , three types of connections

INNER JOIN: in a join condition, if the column of a row is a null value, the row will not return

outer joins: in the join condition, even if the column of a row is a null value, the row is returned; left outer join, right outer join

self-connect: returns rows that are connected to the same table

( 1 ) Outer connection

Use the plus sign (+) on the other side of a column that is not a null value table

Select P.name,pt.name

From Products P,product_types PT

where p.product_type_id = pt.product_type_id (+)

Order BY P.name;

In the left outer join, the outer join operator (+) is on the right side of the equals operator:

Select P.name,pt.name

From Products P,product_types PT

where p.product_type_id = pt.product_type_id (+)

Order BY P.name;

In the right outer join, the outer join operator (+) is on the left side of the equals operator:

Select P.name,pt.name

From Products P,product_types PT

where p.product_type_id (+) = pt.product_type_id

Order BY P.name;

( 2 ) self-connect

Since a connection is a connection to the same table, to perform a self-join, you must use a different table alias to identify each reference to the table in the query.

Select W.first_name | | "| | W.last_name | | ' Works for ' | | M.first_name | | "| | M.last_name

From Employees w,employees m

where w.manager_id = m.employee_id

Order BY W.first_name;

Use the standard SQL syntax execution connection (recommended)

( 3 ) Inner Connection ( inner join ... on ... .. )

① internal connections for two tables

Select P.name,pt.name

From Products P INNER join Product_types PT

On p.product_type_id = pt.product_type_id

Order BY P.name;

② internal connections with more than two tables

Customers, purchases, products, product_types are connected to the 4 tables.

SELECT  as  as type  from INNER JOIN purchases prusing (customer_id) INNER JOIN Products pusing (product_id) INNER JOIN product_types ptusing (product_type_id) ORDER  by P.name;

Connection Logic diagram

Use the Using keyword Description:

1) The query must be a wait connection

2) The column in the join must have the same name

3) Do not use table names or aliases when referencing columns in the Using clause, otherwise an error occurs

(4) Full outer connection ( Full outer JOIN )

Select P.name,pt.name

From Products p FULL outer join Product_types PT

using (product_type_id)

Order BY P.name;

( 5 ) Cross-connect

Use the ON or using clause to avoid producing a Cartesian product.

3 , sub-query

The types of subqueries are:

Single-line subquery

Multi-row subqueries

Dolez Query

Correlated subqueries

Nested subqueries

(1) Single-line subquery

① using subqueries in the WHERE clause

Select Employee_id,last_name

From Employees

Where salary < any

(Select Low_salary

from Salary_grades);

② using subqueries in a HAVING clause

③ using subqueries in the FROM clause (inline view)

Select product_id

From

(select product_id from Products where product_id <3);

(2) Multi-row sub-query

You can use the any or all operators in multiline subqueries.

You must use a =, <>, <, >, >=, <= operator before the any operator in the query

Select Employee_id,last_name

From Employees

Where salary < any

(Select Low_salary

from Salary_grades);

(3) Correlated sub-query

The associated subquery references one or more columns in an external SQL statement

Select Product_id,product_type_id,name,price

From Products outer

where Price >

(select AVG (price) from products inner

where inner.product_type_id = outer.product_type_id);

(4) Update and DELETE statements that contain sub-queries

For example, a payroll with an employee ID of 4 is set to the average of a high salary scale returned by a subquery:

Update Employees

Set salary =

(select AVG (high_salary) from Salary_grades)

where employee_id = 4;

V. Advanced Enquiry

1. Set operator

Operator	Description
Union All	Return rows that include duplicates
Union	Return rows that do not include duplicates
Intersect	Returns a total of two queries retrieved from a row
Minus

Description

When you use the collection operator, the number of columns returned by all queries and the type of the columns must match, but the column names can be different.

Select Product_id,product_type_id,name  from  Products Union Select Prd_id,prd_type_id,name  from More_products;

2. Using Report functions

Check the total monthly sales (Total_month_amount) for the three months preceding 2003 years and the sum of all product type Sales (Total_product_type_amount):

Select Month, prd_type_id,sum(sum(amount) Over(Partition by Month)) asTotal_month_amount,sum(sum(amount)) Over(Partition byPRD_TYPE_ID) asTotal_product_type_amount fromAll_saleswhere  Year = 2003  and Month <= 3;

3, modify the contents of the table

Copy table structure to new table

① data is copied together:

CREATE table new table as SELECT * from old table

② Copy only the table structure without copying the data:

CREATE TABLE t2 as SELECT * from T1 where id<1000;

Note: The ID minimum value in the Components table is 1000, so this is the structure of the table if you choose a row that is smaller than the minimum value.

Vi. completeness of the database

1. PRIMARY KEY constraint

2, FOREIGN KEY constraints

3. Database transactions

The two actions of a transaction, namely commit and rollback. At the same time, you can set the save point (savepoint) anywhere in the transaction.

ACID Properties of transactions

Atomicity: A transaction is atomic, and all the SQL statements that a transaction contains are indivisible units of work.

Consistency: Transactions must ensure that the state of the database remains consistent.

Isolation: Multiple transactions can be run independently, with no impact on each other

Persistence: Once a transaction is committed, the changes to the database are permanently preserved.

4. Concurrent transactions

5. Transaction Lock

6. Transaction ISOLATION LEVEL

Phantom read

Non-REPEATABLE READ

Dirty Read

7, Inquiry flash back

Vii. users, privileges, and roles

1. Create users and their permissions

Create user Xiaoxu identified by 123456;

Authorized

Grant create Session,connect to Xiaoxu;

Change Password

Alter user Xiaoxu identified by 1234567;

Delete User

Drop user Xiaoxu;

The two useful roles granted to users are: CONNECT, RESOURCE.

Granting system privileges to users

Grant create table,create user to Xiaoxu;

Query grants the user system privileges to log on to the database

Select * from User_sys_privs

Revoke User System privileges

Revoke CREATE table from Xiaoxu;

2. Object privileges

Object privileges allow a user to perform specific operations on database objects.

① grant the Select object privilege of the Employees table to student users

Grant Select on employees to student;

② grants student the Update object privilege for the last_name and salary columns of the employee table, and grants this permission to other users

Grant Update (Last_name,salary) on employees to student with GRANT option;

Description

If you want users to be able to grant object privileges to other users, use the GRANT option

If you want users to be able to grant system privileges to other users, use the admin option

③ querying granted Object privileges

Select * from User_tab_privs_made

Where table_name = ' employees ';

④ revoking the user's object privileges

Revoke student user's insert privileges on the Products table

Revoke Insert on the products to student;

3. Role

(1) Create a role

Create role Hr_manager;

(2) authorizing the role

Grant create user to Hr_manager;

(3) grant the role to the user

Grant Hr_manager to student;

(4) Query the role granted to the user

Select * from User_role_privs;

(5) Querying system privileges granted to a role

Select * from Role_sys_privs;

(6) Querying the object privileges granted to a role

Select * from Role_tab_privs

Where role = ' Hr_manager ';

(7) Revoke a role

Revoke Hr_manager from student;

(8) Revoke privileges from a role

Revoke all on product_types from Hr_manager;

(9) Deleting a role

Drop role Hr_manager;

Viii. creating tables, sequences, indexes, and views

1. Table

(1) Create a table

CREATE TABLE Student (

ID int constraint STUDENT_ID_PK primary key,

Status Varchar2 (40),

Last_modified Date Default Sysdate

);

(2) Modify the table

Adding columns

ALTER TABLE student

add modified_by int;

Modifying the data type of a column

Alter Table Student

Modify status char (15);

Delete Column

Alter Table Student

Drop column status;

2. Constraints

(1) Adding a CHECK constraint

(2) Add NOT NULL constraint

(3) Add foreign KEY constraint

(4) Adding a unique constraint

(5) Delete constraint

Alter Table Student

Drop constraint student_id_pk;

(6) Query constraint information

Select * from User_constraints;

(7) Query the constraint information about the column

Select * from User_cons_columns

Where table_name = ' student ';

Oracle SQL Syntax Series (i)