Use of DML (Database operating language) in SQL

Use of DML (Database operating language) in SQL

1. Insert statement:
INSERT [INTO] table [(column1, column2, column3,...)] VALUES (value1, value2, value3 ,...);
Example: insert into user VALUES (NULL, 'test', '20140901 ');
Example: insert into user (username, password) VALUES ('test', '123 ');
There is actually another way:
Example: insert into user SET username = 'test', password = '000000 ';
 

2. query statement:
 
SELECT [option] item [INTO file_details] FROM tables [WHERE condition] [group by group_type] [HAVING where_definition]
 
[Order by order_type] [LIMIT limit_criteria] [PROCEDURE proc_name (arguments)] [lock_option];
 
① Simple query
 
Example: SELECT * FROM user WHERE uid = 4;
 
② Multi-Table query
 
Basic multi-table queries
 
Example: SELECT user. username, userinfo. age, userinfo. sex, userinfo. phone FROM user, userinfo WHERE user. uid = userinfo. uid;
 
Left join
 
SELECT user. uid, uid. name, orders. orderid FROM user left join orders ON user. uid = orders. uid;
 
If no association is used, only records meeting the conditions are returned. If left join is used, all left tables are returned. Even if the right table does not match, the value on the right is replaced by NULL.
 
(In other words, the left join operation returns all records that meet the conditions, and records that are not returned in the left table. The required right table data, such as orders. orderid, is filled with NULL)
 
③ Use subquery
 
Basic subqueries
 
Example: SELECT uid, amount FROM orders WHERE amount = (SELECT max (amount) FROM orders );
 
Associate subquery
 
Example: SELECT isbn, title FROM books WHERE exists (SELECT * FROM orders WHERE orders. isbn = books. isbn); // reference external query data for internal Query
 
Row subquery
 
Example: SELECT c1, c2, c3 FROM t1 WHERE (c1, c2, c3) IN (SELECT c1, c2, c3 FROM t2 );
 
Use a subquery as a temporary table
 
Example: SELECT * FROM (SELECT uid, username FROM user WHERE city = 'beijing') AS user_beijing;
 
④ Aggregate functions and Groups
 
Aggregate functions:
 
Avg ()
 
Count ()
 
Min ()
 
Max ()
 
Std ()
 
Stddev ()
 
Sum ()
 
...
 
Grouping is usually used in combination with Aggregate functions to group result sets.
 
Example: SELECT uid, avg (amount) FROM orders group by uid;
 
HAVING is similar to WHERE and only used for aggregation and grouping. The reason why HAVING is added in SQL is that the aggregate function cannot be used in WHERE.
 
Example: SELECT uid, avg (amount) FROM orders group by uid HAVING avg (amount)> 100;
 
⑤ Use of sorting and restrictions
 
Example: SELECT username, password FROM user order by username ASC; // query the user table and sort BY username field in ascending ORDER
 
Example: SELECT username, password FROM user order by uid DESC; // query the user table and sort BY uid field in descending ORDER
 
Example: SELECT username, password FROM user LIMIT 4; // query the first four records in the user table
 
Example: SELECT username, password FROM user LIMIT 3, 4; // query four records after the user table starts FROM the third
 

 

3. Update statement:
 
UPDATE [LOW_PRIORITY] [IGNORE] table SET column = expression1, column2 = expression2 ,...
 
[WHERE condition] [order by order_criteria] [LIMIT number];
 
For example, UPDATE user SET password = '000000'; // SET the password of all users in the user table to 111111
 
For example, UPDATE user SET password = '000000' WHERE uid = 4; // SET the password of the record with uid 4 in the user table to 111111
 
For example, UPDATE user SET password = '000000' order by uid desc limit 5; // SET the password of the first five records in the user table in reverse ORDER of uid to 111111
 
Order by is usually used in combination with LIMIT. it is meaningless to use order by separately.
 

 

4. Deletion language:
 
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table [WHERE condition] [order by order_cols] [LIMIT number];
 
Example: delete from user; // DELETE all user data
 
Example: delete from user WHERE uid = 4; // DELETE records with uid = 4
 
Example: delete from user order by uid desc limit 5; // DELETE the first five data entries in the user table in inverted uid ORDER
 

Tip: [] indicates optional
 
Adding back quotation marks ''on the field name can avoid conflicts between the field name and the database keyword.
 
The string value must be enclosed in quotation marks ''.

This article permanently updates the link address: