Shulou(Shulou.com)06/01 Report--

one。 SQL classification

DDL: data definition language (Data Definition Language)

DML: data manipulation language (Data Manipulation Language)

TCL: transaction Control language (Transaction Control Language)

DQL: data query language (Data Query Language)

DCL: data Control language (Data Control Language)

1. DDL

Used to create, modify, and delete database objects

CREAE: create tables or other object structures

ALTER: modify the structure of a table or other object

DROP: delete the structure of a table or other object

TRUNCATE: delete table data and retain table structure

2. DML

INSERT: inserting data into a data table

UPDATE: update data that already exists in the data table

DELETE: delete data from database tables

3. TCL

COMMIT: submit and confirm data changes that have been made

ROLLBACK: rollback and cancel data changes that have been made

SAVEPOINT: SavePoint, so that the current transaction can be rolled back to the specified SavePoint, making it easy to cancel some changes

4. DQL

SELECT

5. DCL

GRANT: Grant, used to grant permissions to a user or role

REVOKE: used to reclaim existing permissions of a user or role

CREATE USER: creating user

two。 ORACLE basic types

1. NUMBER (numeric type)

NUMBER (PPMAG S): P represents the total number of digits; S represents the number of digits after the decimal point

2. CHAR (fixed length character type)

CHAR (N): n represents the number of bytes occupied, with a maximum length of 2000 bytes

No matter how long the given value is, it takes up N bytes.

3. VARCHAR2 (variable length character type)

VARCHAR (N): n represents the maximum number of bytes that can be occupied, with a maximum length of 4000 bytes

The given value takes up as many bytes as it is, but it is not good for characters that change frequently, because the characters that follow need to be recalculated, and the performance is worse than VCHAR.

There is also VARCHAR in ORACLE, as in Mysql, where VARCHAR is the same as VARCHAR2, but VARCHAR may change with the consent rules of the public, while VARCHAR2 is a longer character type.

4. DATE (data that defines date and time)

Length: 7 bytes

Default format: DD-MON-RR (eg:11-APR-71)

The year of RR is as follows:

Current time (column) / system time (row) 0-4950-990-49 next century 50-99 last century this century

three。 Basic grammar

1. Create table CREATE TABLE table_name (id NUMBER (4))

two。 View table structure DESC table_name

3. Assign the default value DEFAULT to the field when you create the table

4. Non-null (NOT NULL) conditional constraint to ensure that the field value is not empty

5. Modify table name

RENAME old_name TO new_name

6. Add column

ALTER TABLE table_name ADD (column datatype [DEFAULT EXPR] [column datatype...])

Columns can only be added at the end, not inserted into existing columns (Mysql can be inserted after the specified column)

Eg: add a column of hiredata to the table and set the default to the current date

ALTER TABLE myemp ADD (hiredate DATE DEFAULT sysdate)

7. Delete column

ALTER TABLE table_name DROP (column)

Deleting a field requires deleting the length and data occupied by the field from each row, and freeing up the space occupied in the data block. If the record is large, it may take a long time to delete the field.

8. Modify column

ALTER TABLE table_name MODIFY (column datatype [DEFAULT expr] [column datatype...])

After building a table, you can change the data type, length and default values of the columns in the table.

The modification is valid only for later inserted data

If the length is changed from large to small, it may not be successful.

Eg: MODIFY TABLE myemp MODIFY (job VARCHAR2 (40) DEFAULT 'CLERK')

9. Insert data

INSERT INTO table_name (column1, column2) VALUES (value1,value2)

After performing the DML operation, you need to execute the commit statement before the operation is really confirmed.

If the inserted column has a date field, you need to consider the format of the date. The default date format is' DD-MON-RR',. You can customize the date format and convert it to date-type data using the TO_DATE function.

Eg: INSERT INTO myemp (id, name, job, birth) VALUES (1002, 'doma',' MANAGER', TO_DATE ('2009-01-01,' YYYY-MM-DD''))

10. Update operation

UPDATE table_name SET column = value WHERE id = 2

11. Delete operation

DELETE FROM table_name = where id = 2

The TRUNCATE statement in the DDL statement also has the effect of deleting table data.

Different from the DELETE statement:

-DELETE can be deleted conditionally, and TRUNCATE deletes all table data.

-DELETE is a DML statement that can be returned, while TRUNCATE is a DDL statement that takes effect immediately and cannot be returned.

-if all table records are deleted and the amount of data is large, the efficiency of DELETE statements is lower than that of TRUNCATE statements

Transaction control:

COMMIT is used for transaction commit

ROLLBACK is used to roll back transactions. Then all additions, deletions and modifications in this transaction fail.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.