Use of DML (database operation language) in SQL, sqldml

Use of DML (database operation language) in SQL, sqldml
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 method: for 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 example: SELECT user. username, userinfo. age, userinfo. sex, userinfo. phone FROM user, userinfo WHERE user. uid = userinfo. uid; SELECT user associated with left. uid, uid. name, orders. orderid FROM user left join orders ON user. uid = orders. uid; only records meeting the conditions are returned if no association is used. If left join is used, all records are returned even if the right table does not match, the value on the right of the query will be replaced by NULL (in other words, the left join operation will return all records meeting the conditions, and the records not returned in the left table will also be returned, the required right table data is like orders. orderid is filled with NULL) ③ use subquery basic subquery examples: 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); // an internal query reference A subquery of an external query data row. Example: SELECT c1, c2, c3 FROM t1 WHERE (c1, c2, c3) IN (SELECT c1, c2, 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 function and group aggregate function: avg () count () min () max () std () stddev () sum ()... grouping is usually used in combination with the aggregate function to GROUP the result set. Example: SELECT uid, avg (amount) FROM orders group by uid; HAVING is similar to WHERE and is only used for aggregation and grouping, the reason HAVING is added in SQL is that aggregate functions cannot be used in WHERE, for example: SELECT uid, avg (amount) FROM orders GROUP BY uid HAVING avg (amount)> 100; ⑤ Use Cases of sorting and restriction: 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 of the user table, for example, SELECT username, password FROM user LIMIT 3, 4; // query the four records of the user table starting 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: UPDATE user SET password = '000000' WHERE uid = 4; // SET the password of the record whose uid is 4 in the user table to 111111: UPDATE user SET password = '000000' order by uid desc limit 5; // set the password of the first five records in the uid descending ORDER of the user table to 111111. order by here is usually used in combination with LIMIT. it is meaningless to use order by separately.
4. language of deletion: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table [WHERE condition] [order by order_cols] [LIMIT number]; example: delete from user; // DELETE all user data examples: delete from user WHERE uid = 4; // DELETE uid = 4 Record example: delete from user order by uid desc limit 5; // Delete the first five data entries in the uid descending order in the user table
Tip: [] indicates that the field name can be enclosed with backticks ''to avoid conflict between the field name and the database keyword. The string value must be enclosed by quotation marks'', and the value is not required.
Which of the following statements does not belong to DML in SQL?

DML (data manipulation language): They are SELECT, UPDATE, INSERT, and DELETE, just like their names. These four commands are used to operate the data in the database.

DDL (data definition language): DDL is more than DML. The main Commands include CREATE, ALTER, and DROP. DDL is mainly used to define or change the TABLE structure, data Types, links and constraints between tables, etc. They are mostly used when creating tables.

The definition of database integrity is generally implemented by the SQL () Statement DDL or DML.

The answer to this question is DDL.
Data Definition Language (DDL) is a Language used to describe the real-world entities to be stored in a database. A database mode contains the description definitions of all entities in the database. These definitions include schema definitions and Operation Method definitions.

DML = Data Manipulation Language, Data Manipulation Language, and commands enable users to query databases and computer languages that operate on Data in existing databases. Specifically, UPDATE, INSERT, and DELETE.

DML (Data Manipulation Language) is a Data Manipulation Language, one of the types of SQL, in addition to the Data Definition Language (DDL) Data Definition Language and Data Control Language (DCL) Data Control Language. DML includes INSERT, UPDATE, and DELETE. Note that the select statement belongs to DQL (Data Query Language ). 1