Use of DML (Database operating language) in SQL

1. INSERT statement: INSERT [INTO] table [(column1, column2, column3,...)] VALUES (value1, value2, value3,...); example: INSERT

1. INSERT statement: INSERT [INTO] table [(column1, column2, column3,...)] VALUES (value1, value2, value3,...); example: INSERT

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 the left table does not return any records. 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 ''.