05 data manipulation language

1 data manipulation language data manipulation language for retrieving, inserting and modifying data manipulation language is the most common SQL command data manipulation language commands include:

SELECT query

INSERT add

UPDATE modification

DELETE deletion

2 SELECT command (query) 2.1 query the records in the table

SELECT * FROM EMP;-query all employee information

2.2 create tables using existing tables

Syntax: CREATE TABLE new_table_name AS

SELECT column_names FROM old_table_name

2.3 Select rows with no duplicates (DISTINCT keyword)

In the SELECT clause, use the DISTINCT keyword

SELECT DISTINCT emp_name FROM emp

2.4 use column aliases

Provide different names for column expressions

The alias specifies the column header

Select emp_id,emp_name,emp_sex,emp_birthday,emp_salary*2 from emp

Select emp_id,emp_name,emp_sex,emp_birthday,emp_salary*2 as "double salary" from emp;3 INSERT command (add) 3.1 add record

Insert into emp (emp_id,emp_name,emp_sex,emp_salary)

Values (2)'Li Si', 'female', 3000)

The default format of the date data type is "DD-MON-RR". Use the default format of the date to convert using the TO_DATE function

Insert into emp (emp_id,emp_name,emp_sex,emp_birthday,emp_salary)

Values (5Jing'Li Si','Nu', 'Dec-Dec-2017')

Insert into emp (emp_id,emp_name,emp_sex,emp_birthday,emp_salary)

Values (6'Li Si','Nu', to_date ('2017-12-12' recorder YYYYMMI DD'), 3000)

3.3 insert records from other tables

INSERT INTO table_name SELECT column_names FROM other_table_name

Insert into emp2 select * from emp

Insert into emp2 (emp_id,emp_name) select emp_id,emp_name from emp

4 UPDATE command (modify)

Update emp set emp_name = 'Zhang San', emp_sex=' male 'where emp_id='3'

5 DELETE command (modify)

Delete from emp-Delete all records

Delete from emp where emp_id=1-Delete a record

6 the difference between DROP, TRUNCATE and DELETE

(1) logging

The DELETE statement performs deletions by deleting one row at a time from the table and saving the deletion of that row in the log as a transaction record for rollback operations. On the other hand, TRUNCATE TABLE deletes all the data from the table at once and does not log the individual delete operation records, and the delete row cannot be recovered. And table-related delete triggers are not activated during deletion. The execution speed is fast

(2) Space occupied by tables and indexes

When the table is TRUNCATE, the space occupied by the table and index is restored to the original size. The DELETE operation does not reduce the space occupied by the table or index. The DROP statement frees up all the space occupied by the table.

(3) efficiency

Drop > truncate > delete