SQL language Categories

SQL language Classification:

Data definition language (DDL data definition): Create creation, alter change, truncate truncation, drop delete

Data manipulation language (DML data manipulation Language): Insert Insert, select Select, delete delete, update updates

Transaction Control Language (TCL Transaction control Language): Commit commit, savepoint savepoint, rollback rollback

Data controlling language (DCL Data Control Language): Grant Grant, REVOKE recycle

Data Definition Language DDL:

1.create Table command:

CREATE table [schema.] table_name

(

COLUMN_NAME datatype,

column_name Datetype,

....

)

The schema represents the owner of the object, which is the name of the pattern. If the user creates a table in their own mode, you can specify no owner name.

Table represents the name of the tables.

column represents the name of the columns.

DataType represents the data type and width of the column.

Table name naming rules:

1) The first character of the table name should be a letter.

2) You cannot use an Oracle reserved word to name a table.

3) The maximum length of each table is 30 characters.

4) different tables in the same user mode cannot have the same name.

5) You can use underscores, numbers, and letters, but you cannot use spaces and single quotes.

Differences between the oracel and the SQL Server database object tables:

Number of columns: oracle,254; SQL server,1024

Row Size: Oracle has no limits; SQL server,8060 bytes, plus 16 bytes to point to each text or image column.

Maximum number of rows: Oracle has no limits; SQL Server has no restrictions

Table naming rules: Oracle,[schema.] table_name; SQL Server,[[[server.] Database.] Owner.] table_name

2.truncate Table command:

You can delete only records in a table without deleting the table structure, deleting all rows in the table without logging.

TRUNCATE TABLE <table_name>;

Data Manipulation Language DML: (insert,select,update,delete)

1. Select a row with no duplicates and include the DISTINCT clause in the SELECT command.

Select distinct stuname,stuage from Stuinfo;

2. With column aliases, column aliases do not affect the actual name of the column. The column alias is behind the column expression.

Select Stuname as "name", stuage as age, Stuno number from Stuinfo;

Column headings in column aliases that are specified with special characters (such as spaces) are enclosed in double quotation marks.

3. Create a new table with an existing table.

Syntax: CREATE TABLE <newTable_name>

As

Select {* | column (s)} from <oldTable_name> [where <condition>];

Copy table structure and records:

CREATE TABLE Newstuinfo

As

SELECT * from Stuinfo;

Copying tables Specifies columns and records:

CREATE TABLE Newstuinfo

As

Select Stuname,stuage,stuno from Stuinfo;

To replicate only the table structure:

CREATE TABLE Newstuinfo

As

SELECT * from Stuinfo where 1=2;

4. View the number of rows in a table:

Select COUNT (1) from Stuinfo; --1 is more efficient than *

5. Remove the Stuname,stuage column without duplicate data records:

Select Stuname,stuage from Stuinfo

GROUP BY Stuname,stuage

Having (count (stuname| | Stuage));//"| |" Here is the join operator, similar to "+", meaning that two parts of the content are connected to the

Together, because count () can have only one column in it, so it is connected together.

6. Delete stuname, stuage column duplicate rows (same data only one row)

Delete from Stuinfo where rowID isn't in (

Select Max (ROWID) from Stuinfo GROUP by Stuname,stuage

Having (count (stuname| | Stuage) >1)

Union

Select Max (ROWID) from Stuinfo GROUP by Stuname,stuage

Having (count (stuname| | Stuage) =1)

);

7. Information of the >100 table based on the current user's data volume

Select Tabke_name from User_all_tables a where a.num_rows>1000000;

--user_all_tables provides a system-provided view of the data that a user can query to get a description of the current users table.

Transaction Control Language TCL:

1) Commit: Commit the transaction and make a permanent save of the changes to the database in the transaction.

2) Rollback: Rollback The transaction, that is, to cancel any changes made to the database.

3) SavePoint <savepoint_name>: Creates a storage point in a transaction.

4) rollback to savepoint <savepoint_name>: Rolls back the transaction to the storage point. That is, the storage point created by savepoint

Open transaction: In Oracle, the data is automatically opened the first time it is modified, after the last transaction has ended.

End transaction: ① data is committed: A COMMIT command is issued, the current transaction automatically commits after executing a DDL or DCL statement, and is detached from Oracle.

② data is revoked: The rollback command is issued, the server process ends abnormally, and the DBA stops the session.

Example: INSERT INTO Dept values (' ACCOUNTING ', ' NEW YORK ');

Commit

SavePoint a;//a is just a name

INSERT INTO Dept values (+, ' SALES ', ' NEW YORK ');

Rollback to savepoint A;

rollback;--results only the first record is inserted successfully

Data Control Language DCL:

The Data Control language provides the user with permission control commands. The owner of a database object, such as a table, has control over those objects. The owner can follow his

Will determine how other users can access the object and grant other user rights (Insert,select,update,delete,.... ）

SQL language Categories