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 ');
Actually another way: example: 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;② Multiple table queries main multiple 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 without using the association, assuming that the left associative left table will return all returns even if the right table does not match, the value on the right is replaced with null (in other words: The left association will return all records that meet the criteria. Also returns records that do not return the left table, and the right table data, such as Orders.OrderID, is filled with null ③ use the subquery main subquery example: Select UID, amount from Orders WHERE amount= (select Max ( Amount) from orders); Associative subquery Example: Select ISBN, title from books where exists (SELECT * from orders whereorders. isbn=Books. ISBN); An internal query references an external query data 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 example: 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 typically used in conjunction with aggregate functions. Example of grouping result set: SELECT UID, AVG (amount) from Orders group by UID; Having something similar to where, just for totals and groupings, the reason that SQL adds having has is. An example of an aggregate function cannot be used in the Where: Select UID, AVG (amount) from Order GROUP by UID have avg (amount) >100;⑤ ordering and Restriction use example: SELECT Username, PA ssWOrd from the 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; Querying the User table 4 records from the beginning of the third entry
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 all the people in the user table password 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 a backslash to the field name "can avoid the field name and Database keyword conflict chord values need to use quotes ' quotes, number values do not need
Copyright notice: This article Bo Master original articles, blogs, without consent may not be reproduced.
SQL is used in DML (Database manipulation language)
