1. INSERT statement: Insert [into] table [(Column1, Column2, Column3, ...)] VALUES (value1, value2, Value3, ...);
Example: INSERT into user VALUES (NULL, ' Test ', ' 123456 ');
Example: INSERT into user (username, password) VALUES (' Test ', ' 123456 ');
There is actually another way: INSERT into user SET username= ' test ', password= ' 123456 ';
2. Query statement: SELECT [option] item [into file_details] from tables [WHERE condition] [GROUP by Group_type] [have Where_definiti On][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 Query example: SELECT user.username, Userinfo.age, Userinfo.sex, userinfo.phone from user, UserInfo WHERE User.uid=userinfo.uid; Left Associate Select User.uid, Uid.name, Orders.OrderID from user, and JOIN orders on User.uid=orders.uid; Only records that satisfy the condition are returned if they are not used, and if the left table is all returned, even if the right table does not match, the value on the right will be replaced with null (in other words: The left association will return all records that satisfy the condition, and the left table will return no records returned. The required right table data, such as Orders.OrderID, is filled with null) ③ uses the subquery basic subquery example: Select UID, amount from Orders WHERE amount= (select Max (amount) from Orders ); Correlated subquery Example: Select ISBN, title from books where exists (select * FROM Orders whereorders. isbn=Books. ISBN); Internal query referencing external query data row Subquery Example: Select C1, C2, C3 from T1 WHERE (C1, C2, C3) in (select C1, C2, c3 from T2); Use subqueries as temporary tables: SELECT * FROM (SELECT uid, username from user WHERE city= ' Beijing ') As User_beijing;④ aggregate function and grouping aggregate function: AVG () Count () min () max () STD () StdDev () sum () ... Groupings are usually used in conjunction with aggregate functions, and the result set is grouped as an example: SELECT uid, AVG (amount) from the Orders group by UID; Having something similar to where, only for totals and groupings, the reason for the addition of having in SQL is that you cannot use the aggregate function example in where: the SELECT uid, avg (amount) from the orders GROUP by UID has avg ( Amount) >100;⑤ Use example of sorting and restriction: SELECT username, password from user ORDER by username ASC; Query the user table and sort by the username word orderby order Example: SELECT username, password from the user ORDER by UID DESC; Query the user table and sort by the UID field in descending order Example: SELECT username, password from user LIMIT 4; Query user table top four records example: SELECT username, password from user LIMIT 3, 4; Query the user table from the beginning of the third article 4 records
3. Updated statement: Update [low_priority] [IGNORE] table SET column=expression1, Column2=expression2, ... [WHERE condition] [ORDER by Order_criteria] [LIMIT number]; Example: UPDATE user SET password= ' 111111 '; Set the owner password in the user table to 111111 cases: UPDATE user set password= ' 111111 ' WHERE uid=4; Set the password for the record with the UID equal to 4 in the user table to 111111 cases: UPDATE user set password= ' 111111 ' ORDER by uid DESC LIMIT 5; Set the password for the first 5 records in the user table in reverse of the UID to 111111 here the order by is usually used in conjunction with limit, and using order by alone is meaningless
4. Delete language: delete [low_priority] [QUICK] [IGNORE] FROM table [WHERE condition] [ORDER by order_cols] [LIMIT number]; Example: Delete F ROM user; Delete User All data example: Delete from user WHERE uid=4; Delete uid=4 Record example: Delete from the user ORDER by UID DESC LIMIT 5; Remove the first 5 data in the user table that are reversed by the UID
Hint: [] means optional Add anti-quotes to field names to avoid conflicts between field names and database keywords String values need to be enclosed in quotation marks, and values do not need to be
