Shulou(Shulou.com)06/01 Report--

The following mainly brings you how MySQL should operate the records in the data table. I hope these contents can bring you practical use. This is also the main purpose of this article that I edit MySQL how to manipulate the records in the data table. All right, don't talk too much nonsense, let's just read the following.

1. Insert record INSERT

INSERT [INTO] tbl_name [(col_name,...)] {VAULES | VALUE} ({expr | DEFAULT},...), (...),...

For example, insert a single record:

Mysql > USE testDatabase changedmysql > CREATE TABLE users (- > id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,-> username VARCHAR (20) NOT NULL,-> password VARCHAR (32) NOT NULL,-> age TINYINT UNSIGNED NOT NULL DEFAULT 10,-> sex BOOLEAN->); mysql > INSERT users VALUES (NULL,'Tom','asd123',24,1); mysql > SELECT * FROM users +-+ | id | username | password | age | sex | +-+ | 1 | Tom | asd123 | 24 | 1 | +-+- +-+

Insert multiple records:

Mysql > INSERT users VALUES (DEFAULT,'John','asd123',24,1),-> (NULL,'Huang','daddd',25,1); mysql > SELECT * FROM users +-+ | id | username | password | age | sex | +-+ | 1 | Tom | asd123 | 24 | 1 | 2 | John | asd123 | 24 | 1 | | 3 | Huang | daddd | 25 | 1 | +-+

Insert a mathematical expression:

Mysql > INSERT users VALUES (NULL,'John','asd123',3*7-5); mysql > SELECT * FROM users +-+ | id | username | password | age | sex | +-- + | 1 | Tom | asd123 | 24 | 1 | | 2 | John | asd123 | 24 | 1 | | 3 | | Huang | daddd | 25 | 1 | | 4 | John | asd123 | 16 | 1 | +-- + |

Insert the hash password of md5:

Mysql > INSERT users VALUES (NULL,'John',md5 ('123'), DEFAULT,0) Mysql > SELECT * FROM users +-- + | id | username | password | age | sex | +-+-- -+ | 1 | Tom | asd123 | 24 | 1 | | 2 | John | asd123 | 24 | 1 | 3 | Huang | daddd | 25 | 1 | | 4 | John | asd123 | | 16 | 1 | | 5 | John | 202cb962ac59075b964b07152d234b70 | 10 | 0 | +-+-+ |

two。 Insert record INSERT SET-SELECT

(the difference from the first method is that subqueries can be used and multiple records cannot be inserted at the same time.)

INSERT [INTO] tbl_name SET col_name= {expr | DEFAULT},...

Example:

Mysql > INSERT users SET username='Ben',password=md5 ('345'); mysql > SELECT * FROM users +-+ | id | username | password | age | sex | + -+-+ | 1 | Tom | asd123 | 24 | 1 | | 2 | John | asd123 | 24 | 1 | 3 | Huang | daddd | 25 | 1 | 4 | John | asd123 | 16 | 1 | 5 | John | 202cb962ac59075b964b07152d234b70 | 10 | 0 | 6 | Ben | d81f9c1be2e08964bf9f24b15f0e4900 | 10 | NULL | + -+

3. Update record UPDATE

Single table update

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1= {expr1 | DEFAULT} [, col_name2= {expr2 | DEFAULT}]. [WHERE where_condition]

For example, add the ages of all records by 5:

Mysql > UPDATE users SET age = age+5;mysql > SELECT * FROM users +-+ | id | username | password | age | sex | | +-+ | 1 | Tom | asd123 | 29 | 1 | | 2 | John | | | asd123 | 29 | 1 | 3 | Huang | daddd | 30 | 1 | | 4 | John | asd123 | 21 | 1 | | 5 | John | 202cb962ac59075b964b07152d234b70 | 15 | 0 | | 6 | Ben | d81f9c1be2e08964bf9f24b15f0e4900 | 15 | NULL | | | +-+

Add the age of Tom to 10 years:

Mysql > UPDATE users SET age = age+10 WHERE id=1;mysql > SELECT * FROM users WHERE id=1 +-+ | id | username | password | age | sex | +-+ | 1 | Tom | asd123 | 39 | 1 | +-+- +-+

Add 10 years to the even age of id:

Mysql > UPDATE users SET age = age+10 WHERE id% 2 = 0bot MySQL > select * from users +-- + | id | username | password | age | sex | +-- -- + | 1 | Tom | asd123 | 39 | 1 | | 2 | John | asd123 | | 39 | 1 | 3 | Huang | daddd | 30 | 1 | | 4 | John | asd123 | 31 | 1 | | 5 | John | 202cb962ac59075b964b07152d234b70 | 15 | 0 | + | -+

Modify the sex of all records to 0:

Mysql > UPDATE users SET sex = 0 Mysql > select * from users +-+ | id | username | password | age | sex | + -+-+ | 1 | Tom | asd123 | 39 | 0 | | 2 | John | asd123 | | 39 | 0 | 3 | Huang | daddd | 30 | 0 | | 4 | John | asd123 | 31 | 0 | 5 | John | 202cb962ac59075b964b07152d234b70 | 15 | 0 | 7 | Ben | d81f9c1be2e08964bf9f24b15f0e4900 | 15 | 0 | + | -+

4. Delete record DELETE

Delete single table (delete [all records] if you don't add WHERE)

DELETE FROM tbl_name [WHERE where_condition]

For example, delete the record of id=7:

Mysql > DELETE FROM users WHERE id = 7 * MySQL > select * from users +-- + | id | username | password | age | sex | +-+-- -- + | 1 | Tom | asd123 | 39 | 0 | | 2 | John | asd123 | 39 | 0 | 3 | Huang | daddd | 30 | 0 | | 4 | John | asd123 | | 31 | 0 | | 5 | John | 202cb962ac59075b964b07152d234b70 | 15 | 0 | +-+-+ |

5. Find record SELECT

SELECT select_expr [, select_expr...] [FROM tbl_references [WHERE where_condition] [GROUP BY {col_name | position} [ASC | DESC],...] [HAVING where_condition] [ORDER BY {col_name | expo | position} [ASC | DESC],...] [LIMIT {[offset,] row_count | row_count OFFSET offset}]]

Query expression (select_expr)

1. Each expression represents the desired column, and there must be at least one

two。 Multiple columns are separated by commas

3. An asterisk (*) indicates all columns; tbl_name.* can represent all columns of a named table

4. Query expressions can be aliased using [AS] alias_name

5. Aliases can be used in GROUP BY,ORDER BY or HAVING clauses

The order in which SELECT query expression fields appear will affect the order in which result set fields appear.

The alias of the field will also affect the later result set and play an important role in the future PHP.

For example, only id and username columns in the users table are queried:

Mysql > SELECT id,username FROM users;+----+-+ | id | username | 1 | Tom | | 2 | John | 3 | Huang | 4 | John | | 5 | John | +-+-+

Query the id and username columns in the users table, using userid,uname as aliases:

Mysql > SELECT id AS userid, username AS uname FROM users; +-+-+ | userid | uname | +-+-+ | 1 | Tom | 2 | John | | 3 | Huang | | 4 | John | | 5 | John | +-+-+

6.where statement for conditional query

Conditional expression (WHERE)

Filter records, and if no WHERE clause is specified, all records are displayed

In WHERE expressions, you can use functions or operators supported by MySQL

The 7.group by statement groups the query results

Group by

Grouping query results

[GROUP BY {col_name | position} [ASC | DESC],...]

You can specify the column name and column location of the column.

Asc birth order, desc descending order, and multiple groups are distinguished by commas.

For example, query the users table and group by gender:

Mysql > SELECT sex FROM users GROUP BY sex;+-+ | sex | +-+ | 0 | +-+

8.having statement sets grouping conditions

Grouping condition (HAVING)

[HAVING where_condition]

Where where_condition is either an aggregate function (max,min,avg,count,sum), or the field in it must be a query field in SELECT, otherwise an error will be reported]

For example, query the sex grouping in the users table if age > 20 (this method requires the query field to be added as well as the age field):

Mysql > SELECT sex,age FROM users GROUP BY sex having age > 20; +-+-+ | sex | age | +-- + | 0 | 39 | +-+-+

Query the sex grouping in the users table if id > = 2:

Mysql > SELECT sex FROM users GROUP BY sex HAVING count (id) > = 2; +-+ | sex | +-+ | 0 | +-+

The 9.order by statement sorts the query results

Sort the query results (ORDER BY)

[ORDER BY {col_name | expr | position} [ASC | DESC],...]

You can sort by multiple columns

For example, query the users table, sorted by age:

Mysql > SELECT * FROM users ORDER BY age ASC +-- + | id | username | password | age | sex | +-+-- -+ | 8 | Hui | 9e1e06ec8e02f0a0074f2fcc6b26303b | 8 | 1 | | 5 | John | 202cb962ac59075b964b07152d234b70 | 15 | 0 | 10 | Luyuan | 9e1e06ec8e02f0a0074f2fcc6b26303b | 21 | 1 | 9 | Dui | 9e1e06ec8e02f0a0074f2fcc6b26303b | 27 | 1 | 3 | Huang | daddd | 30 | 0 | 12 | Murouan | 9e1e06ec8e02f0a0074f2fcc6b26303b | 31 | 1 | | 11 | | | Oduyuan | 9e1e06ec8e02f0a0074f2fcc6b26303b | 31 | 1 | | 13 | Spqoan | 9e1e06ec8e02f0a0074f2fcc6b26303b | 31 | 1 | | 4 | John | asd123 | 31 | 0 | 2 | John | asd123 | 39 | 0 | 1 | Tom | asd123 | 39 | 0 | + | -- +

Query the users table, in the first order from age to age, and in the second order from id to id:

Mysql > SELECT * FROM users ORDER BY age,id ASC +-- + | id | username | password | age | sex | +-+-- -+ | 8 | Hui | 9e1e06ec8e02f0a0074f2fcc6b26303b | 8 | 1 | | 5 | John | 202cb962ac59075b964b07152d234b70 | 15 | 0 | 10 | Luyuan | 9e1e06ec8e02f0a0074f2fcc6b26303b | 21 | 1 | 9 | Dui | 9e1e06ec8e02f0a0074f2fcc6b26303b | 27 | 1 | 3 | Huang | daddd | 30 | 0 | 4 | John | asd123 | | 31 | 0 | | 11 | Oduyuan | 9e1e06ec8e02f0a0074f2fcc6b26303b | 31 | 1 | | 12 | Murouan | 9e1e06ec8e02f0a0074f2fcc6b26303b | 31 | 1 | 13 | Spqoan | 9e1e06ec8e02f0a0074f2fcc6b26303b | 31 | 1 | | 1 | Tom | asd123 | 39 | 0 | 2 | John | asd123 | 39 | 0 | +-+-| -- +

10.LIMIT statement limits the number of queries

LIMIT limits the number of query results returned

[LIMIT {[offset,] row_count | row_count OFFSET offset}] SELECT * from users # returns all results SELECT * from users limit 2 # returns the first two mysql > SELECT * FROM users LIMIT 2 in all result sets +-+ | id | username | password | age | sex | +-- + | 1 | Tom | asd123 | 39 | 0 | | 2 | John | asd123 | 39 | 0 | +- -+ SELECT * FROM users LIMIT 2pm 2 # to query articles 3 and 4 records in the result set. The records in mysql start at 0, with a total of several entries. Mysql > SELECT * FROM users LIMIT 2 +-+ | id | username | password | age | sex | +-- + | 3 | Huang | daddd | 30 | 0 | | 4 | John | asd123 | 31 | 0 | +- -- +

Summary

Record operations: INSERT, UPDATE, DELETE, SELECT

Three kinds of 1.insert

Insert [into] Table name [(column name, column name)] {values | value} ({(expression | default}, ()) insert [into] Table name set column name = {(expression | default},. Insert [into] Table name [(column name, column name)] SELECT..

2.UPDATE

(1) single table update

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1= {expr1 | DEFAULT}, [col_name2= {expr2 | DEFAULT}]... [WHERE where_condition]

Multi-table update

3.DELETE: deleting data

(1) single table deletion

DELETE FROM tbl_name [WHERE where_condition}

4.SELECT: / / query

SELECT select_expr [, select expr2...] # finds only one function or expression [FROM table_references # query table name [WHERE where_conditon] # query condition [GROUP BY {col_name | position} [ASC | DESC],...] # grouped by a field, and the same only shows the first [HAVING where_conditon] # grouping Give the display condition [ORDER BY {col_name | expr | position} [ASC | DESC],...] # sort [LIMIT {[offset,] row_count | row_count OFFSET offset}] # limit the number of returns]

SELECT

References to the FROM

WHERE condition

GROUP BY groups the recording results

The setting of grouping condition by HAVING

Combined with the content, the results of the search are stored in the specified data table:

Mysql > CREATE TABLE test (- > id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,-> username VARCHAR (20) NOT NULL->); mysql > INSERT test (username) SELECT username FROM users WHERE age > = 25th MySQL > SELECT * FROM test +

For the above about how MySQL should manipulate the records in the data table, you do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.