A, SQL statements classification
According to the type of the instructions given by the RDBMS, SQL statements can be divided into the following three categories.
DDL
DDL (Data Definition Language, Data Definition Language (DDL) is used to create or delete the Data is stored in the database and the tables in the database object, DDL contains the following instructions,
CREATE: CREATE a database and table objects such as
DROP: delete the database and table objects such as
ALTER, modify the structure of the database and table object
DML
DML (Data Manipulation Language, Data Manipulation Language) is used to query or change the record in the table, DML contains the following instructions,
SELECT: lookup table data in a
To INSERT new data in the table INSERT:
UPDATE: UPDATE the data in the table
DELETE: DELETE the data in the table
DCL
DCL (Data Control Language, Data Control Language) is used to confirm or cancel to change Data in the database, in addition to this, you can also to RDBMS if users have permission to operate the object in the database (database tables, etc.) are configured, DCL contains the following instructions,
COMMIT: confirm the data in the database change
ROLLBACK: cancel the changes to the data in the database for
GRANT: give user operation permission
REVOKE: cancel the user's operation permission
90% of the actual use of the SQL statement belongs to the DML
Second, basic statement
1. DATABASE created (the CREATE DATABASE statement)
The CREATE DATABASE & lt; The database name & gt; ;
Exp: the CREATE DATABASE shop;
2. CREATE TABLE (the CREATE TABLE statement)
The CREATE TABLE & lt; The table name & gt;
(& lt; The column 1 & gt;
.
.
Product_id CHAR (4) NOT NULL,
Product_name VARCHAR (100) NOT NULL,
Product_type VARCHAR (32) NOT NULL,
Sale_price INTEGER,
Purchase_price INTEGER,
Regist_date DATE,
PRIMARY KEY (product_id)
);
Note:
2.1. The naming rules
2.1.1 half horn can only use English letters, Numbers, underscores (_) as a database, table and column names
2.1.2 name must begin with a half Angle English letter
2.2. The data type specified
Database tables created, all the columns must specify the data type, each column can store in conformity with the column data type of the data,
Four kinds of most basic data type
2.2.1 INTEGER type
Used to specify the columns of the stored integer data type (digital), cannot store the decimal,
2.2.2 CHAR
Used to store fixed-length string, when stored in a column of the length of the string can not reach the maximum length of time, use the half Angle space length, because will waste storage space, so you don't normally use,
2.2.3 VARCHAR type
Used to store a variable length string, fixed-length string in the number of characters have not reached the maximum length will be half Angle space up, but a variable length string is different, even if the number of characters have not reached the maximum length, also won't use half Angle space make up,
2.2.4 the DATE type
Used to store date (date) specified column data type (date)
2.3. The constraint set
Constraints are, besides data type of the column to restrict or the data stored in the function of the additional conditions,
2.3.1 the NOT NULL constraint is empty, that is, the column must be input data,
2.3.2 PRIMARY KEY is a PRIMARY KEY constraint, on behalf of the column is the only value, can through the column in a particular row,
3. The table delete
DROP TABLE & lt; The table name & gt; ;
Exp: DROP TABLE product;
4. Add a column of the ALTER TABLE statement
The ALTER TABLE & lt; The table name & gt; ADD the COLUMN & lt; The definition of column & gt;;
Exp: the ALTER TABLE product ADD COLUMN product_name_pinyin VARCHAR (100);
5. Delete the column of the ALTER TABLE statement
The ALTER TABLE & lt; The table name & gt; DROP the COLUMN & lt; The column name & gt;;
Exp: the ALTER TABLE product DROP COLUMN product_name_pinyin;
The ALTER TABLE statement, like DROP TABLE statement cannot recover after execution, mistakenly add columns can pass the ALTER TABLE statement deletion, or will create TABLE after delete all,
6. Empty the table content
TRUNCATE TABLE TABLE_NAME;
Advantages: compared with the drop ` `/` ` delete, truncate to remove the data, the fastest,
7. The data update
Basic syntax:
The UPDATE & lt; The table name & gt;
The SET & lt; The column name & gt;=& lt; Expression & gt; [, & lt; column names & gt; 2=& lt; expression & gt; 2. ;
WHERE & lt; Conditions & gt;; - optional, is very important,
The ORDER BY clause. - optional
LIMIT clause; - optional
Pay attention to when using the update to add the where condition, otherwise will amend the all line according to the statement
Modify all the registration time
UPDATE the product
The SET regist_date='2009-10-10';
- only modify the unit price of certain goods
UPDATE the product
The SET sale_price=sale_price * 10
WHERE product_type='kitchen utensils;
Use the UPDATE columns can be updated to NULL (the UPDATE commonly known as NULL empty), now just need to assignment expression the value to write directly on the right is NULL,
- making the goods number 0008 (ballpoint pen) the date of registration of data update is NULL
UPDATE the product
The SET regist_date=NULL
WHERE product_id='0008'.
As well as the INSERT statement, UPDATE statement can also be NULL as a value to use,
* * but only NOT set the NOT NULL constraint and the columns of the primary key constraint can be cleared to NULL, * * if you set the column to update is NULL, the constraint can go wrong, this is the same as the INSERT statement,
Multiple columns update
UPDATE statement of support SET clause will be more than one column at the same time as the UPDATE object,
- based method and an UPDATE statement to UPDATE only a list
UPDATE the product
The SET sale_price=sale_price * 10
WHERE product_type='kitchen utensils;
UPDATE the product
The SET purchase_price=purchase_price/2
WHERE product_type='kitchen utensils;
This method can obtain correct results, but the code is relatively complicated, can be used in a combined method to simplify the code,
- the combined writing
UPDATE the product
The SET sale_price=sale_price * 10,
Purchase_price=purchase_price/2
WHERE product_type='kitchen utensils;
To be clear, the SET clause of the column can be not only two columns, also can be a three columns or more,
2.9 to insert data in the product table
In order to study the INSERT statement usage, we first create a table, called productins built table statements are as follows:
The CREATE TABLE productins
(product_id CHAR (4) NOT NULL,
Product_name VARCHAR (100) NOT NULL,
Product_type VARCHAR (32) NOT NULL,
Sale_price INTEGER DEFAULT 0,
Purchase_price INTEGER,
Regist_date DATE,
PRIMARY KEY (product_id));
Basic syntax:
INSERT INTO & lt; The table name & gt; (column 1, 2, 3,... ) VALUES (VALUES 1, 2, the value 3,... );
To full list of INSERT table, can be omitted after the table name list, then the value of the VALUES clause will default in the order from left to right is assigned to each column,
- contains a list
INSERT INTO productins (product_id, product_name, product_type,
Sale_price purchase_price, regist_date) VALUES (' 0005 ', 'the pressure cooker, kitchen utensils, 6800, 5000,' 2009-01-15 ');
- to omit a list
INSERT INTO productins
VALUES (' 0005 ', 'the pressure cooker, kitchen utensils, 6800, 5000,' 2009-01-15 ');
In principle, the implementation of an INSERT statement inserts a row, INSERT multiple rows, usually need to cycle through the corresponding number of the INSERT statement, a lot of RDBMS is supported by an INSERT more rows of data
- usually INSERT
INSERT INTO productins VALUES (' 0002 ', 'punch'
'office supplies, 500, 320,' 2009-09-11 ');
INSERT INTO productins VALUES (' 0003 ', 'sports T-shirt,
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull