Three types of SQL statements--the record is an article on my previous Weibo blog

One, DML statements
Second, DDL statements
Third, transaction control statements
One, DML statement-data mulipulation Language
DML statement data operations generate a transaction after Daisy executes, the transaction needs to be committed to be permanent, and can be rolled back before committing the undo operation!
1. Insert INTO insertion statement
(1), insert date
INSERT into EMP values (' Tengjiang ', to_date (' 1990-7-19 ', ' yyyy-mm-dd '));
You must use the To_date statement when inserting date data, which converts the character to a date format;
(2), insert has three kinds of formats
A, insert into EMP (VALUES1,VALUES2,VALUES3);
B, insert into EMP (empno,ename,job) values (VALUES1,VALUES2,VALUES3);
C, insert into bonus select (); Put the query results in another table, the equivalent of copying data;
INSERT INTO bonus select Ename,job,comm from EMP, put ename,job,comm three fields in the EMP table into the bonus table;
2. Update Modify update EMP set modify content
Update bonus set (SAL,COMM) = (select Sal,comm form bonus where ename= ' Tengjiang ')
where ename= ' Zhangsan ';
3. Delete Deletes
(1), delete from bonus where ename= ' Tengjiang '; Delete the data named Tengjiang;
(2), delete bonus; Delete all data directly, keep table structure;
DML statements in the operation, the need for a commit to take effect, such as not commit, the next time you reconnect the database, the data will revert to the original state;
Second, the DDL statement data definition statement, the Operation table structure, no need to submit can be effective;
1, truncate delete the statement, quickly delete the table content, nominally belong to the DML statement, actually belongs to the DDL statement, the direct effect does not need to manually commit, can not be rolled back;
TRUNCATE TABLE bonus;
2. CREATE table Test (field data type, field 2 data type)
(1), CREATE TABLE test (name varchar), nianling number (3), Sal number (10));
Create a test table with three fields
INSERT into test values (' Tengjiang ', 20,3000);
Number (M,n): Defines that the maximum number of digits is m, the numeric type that is accurate to n digits after the decimal point, and the maximum number of digits cannot exceed 38 bits
CHAR (n): Defines a fixed-length character type with a length of n, the maximum value of this n is 2000 bytes
----If the stored character length is less than n, a space is used to store the N bytes
VARCHAR2 (N): Defines a variable-length character type that has a maximum length of n, and the maximum value of n is 4,000 characters
----stored according to the actual character length entered
CHAR (): Wasted space, high query efficiency
VARCHAR2 (): space saving, low query efficiency
Date: Type, January 1, 4712 BC to December 31, 4712
Number (M,n): Defines that the maximum number of digits is m, the numeric type that is accurate to n digits after the decimal point, and the maximum number of digits cannot exceed 38 bits
CHAR (n): Defines a fixed-length character type with a length of n, the maximum value of this n is 2000 bytes
----If the stored character length is less than n, a space is used to store the N bytes
VARCHAR2 (N): Defines a variable-length character type that has a maximum length of n, and the maximum value of n is 4,000 characters
----stored according to the actual character length entered
CHAR (): Wasted space, high query efficiency
VARCHAR2 (): space saving, low query efficiency
Date: Type, January 1, 4712 BC to December 31, 4712

CREATE TABLE Test3
(
TestID Number (4),
TestName varchar2 (20),
Testgender char (1) Default ' M ',
Testage Number (3) default 18,
Testcore number (4,1),
TestDate Date
);
(2), create Tabel table name as Select to create a new table for the results of the query
CREATE TABLE test2 SA select * from test;
3, alert modify table structure
(1), ALTER TABLE name add column name data type adds a column
ALTER TABLE TEST2 add TestID number (4);
(2), ALTER TABLE name drop colume column name; Delete a column
ALTER TABLE test2 drop column TestID;
(3), ALTER TABLE name rename column name to the new column name;
ALTER TABLE test2 Rename column nianling to age; Modify field names
(4), ALTER TABLE name modify column name data type;
ALTER TABLE TEST2 Modify age number (4);
(5), add, modify, delete default values
ALTER TABLE name modify column name default value;
ALTER TABLE name modify column name default null;
4. Drop Delete Table
drop table name;
drop table test;
-----Delete: is a DML statement that requires a commit to take effect and can roll back the undo operation to delete all the data in the table
-----can also delete only the data in the condition range, preserving the table structure
-----Truncate: Is a DDL statement that directly takes effect and does not need to be committed or rolled back. Quickly delete all data in a table
----cannot specify a range deletion, preserving the table structure
-----Drop: Is a DDL statement that directly takes effect and does not need to be submitted or Guild Hall. Delete the structure of data and tables
5. Rename Rename table
Rename original table name to new table name

Three types of SQL statements--the record is an article on my previous Weibo blog