MYSQL, notes (recording operations)

Record operation: Insert/update/delete/select
1. Insert record (total three kinds)
The first type: an expression

1.INSERT [into] tbl_name [(Col_name,...)] {values| VALUE} ({expr| DEFAULT},...), (...),... mysql> CREATE TABLE users (id SMALLINT UNSIGNED PRIMARY KEY auto_increment,-> username varchar () not null,-> password VARCHAR (+) not null,->-TINYINT UNSIGNED not NULL DEFAULT 10,-> sex boolean- >); Query OK, 0 rows affected (0.16 sec) mysql> INSERT into users VALUES (NULL, ' Tom ', ' 123 ', 25, 1); Query OK, 1 row affected (0.06 sec) mysql> SELECT * from users;+----+----------+----------+-----+------+| ID | Username | password | Age | Sex |+----+----------+----------+-----+------+| 1 | Tom | 123 | 25 | 1 |+----+----------+----------+-----+------+1 row in Set (0.00 sec) mysql> INSERT into users VALUES (NULL, ' July ', ' 123 ', 25,1); Query OK, 1 row affected (0.06 sec) mysql> SELECT * from users;+----+----------+----------+-----+------+| ID | Username | password | Age | Sex |+----+----------+----------+-----+------+| 1 | Tom | 123 | 25 | 1 | | 2 | July | 123 | 25 | 1 |+----+----------+----------+-----+------+2 rows in Set (0.00 sec) mysql> INSERT into users VALUES (DEFAULT, ' Tom ', ' 123 ', 25, 1); Query OK, 1 row affected (0.05 sec) mysql> SELECT * from users;+----+----------+----------+-----+------+| ID | Username | password | Age | Sex |+----+----------+----------+-----+------+| 1 | Tom | 123 | 25 | 1 | | 2 | July | 123 | 25 | 1 | | 3 | Tom | 123 | 25 | 1 |+----+----------+----------+-----+------+3 rows in Set (0.00 sec) mysql> INSERT into users VALUES (NULL, ' July ', ' 123 ' , 2*9*6-9,1); Query OK, 1 row affected (0.08 sec) mysql> SELECT * from users;+----+----------+----------+-----+------+| ID | Username | password | Age | Sex |+----+----------+----------+-----+------+| 1 | Tom | 123 | 25 | 1 | | 2 | July | 123 | 25 | 1 | | 3 | Tom | 123 | 25 | 1 | | 4 | July | 123 | 99 | 1 |+----+----------+----------+-----+------+4 rows in Set (0.00 sec) mysql> INSERT into users VALUES (DEFAULT, ' Tom ', ' 123 ', default,1); Query OK, 1 row affected (0.06 sec) mysql> SELECT * FROM users;+----+----------+----------+-----+------+| ID | Username | password | Age | Sex |+----+----------+----------+-----+------+| 1 | Tom | 123 | 25 | 1 | | 2 | July | 123 | 25 | 1 | | 3 | Tom | 123 | 25 | 1 | | 4 | July | 123 | 99 | 1 | | 5 | Tom | 123 | 10 | 1 |+----+----------+----------+-----+------+5 rows in Set (0.00 sec) mysql> INSERT into users VALUES (NULL, ' July ', ' 123 ' , 2*9*6-9,1), (DEFAULT, ' King ', ' 123 ', default,0); Query OK, 2 rows affected (0.05 sec) records:2 duplicates:0 warnings:0mysql> SELECT * from users;+----+----------+--- -------+-----+------+| ID | Username | password | Age | Sex |+----+----------+----------+-----+------+| 1 | Tom | 123 | 25 | 1 | | 2 | July | 123 | 25 | 1 | | 3 | Tom | 123 | 25 | 1 | | 4 | July | 123 | 99 | 1 | | 5 | Tom | 123 | 10 | 1 | | 6 | July | 123 | 99 | 1 | | 7 | King | 123 | 10 | 0 |+----+----------+----------+-----+------+7 rows in Set (0.00 sec) MD5 is a function of PHP to calculate the hash value of a string mysql> INSERT into Users VALUES (DEFAULT, ' Tom ', MD5 (' 123 '), default,1); Query OK, 1 row affEcted (0.06 sec) mysql> SELECT * from users;+----+----------+----------------------------------+-----+------+| ID | Username | password | Age | Sex |+----+----------+----------------------------------+-----+------+| 1 | Tom | 123 | 25 | 1 | | 2 | July | 123 | 25 | 1 | | 3 | Tom | 123 | 25 | 1 | | 4 | July | 123 | 99 | 1 | | 5 | Tom | 123 | 10 | 1 | | 6 | July | 123 | 99 | 1 | | 7 | King | 123 | 10 | 0 | | 8 | Tom | 202cb962ac59075b964b07152d234b70 | 10 | 1 |+----+----------+----------------------------------+-----+------+8 rows in Set (0.00 sec)

The second type: (If you do not understand this, first look down, read and come back to see it)

Description: The difference from the first approach is that this method can use a subquery (subquery). And only one piece of data can be inserted at a time. INSERT [into] tbl_name SET col_name={expr| DEFAULT},... mysql> CREATE TABLE Test (id TINYINT UNSIGNED PRIMARY KEY auto_increment,-> username VARCHAR (20)-& Gt ); Query OK, 0 rows affected (0.18 sec) mysql> SELECT * from test; Empty Set (0.00 sec) mysql> INSERT Test SELECT username from users WHERE age>=30; ERROR 1136 (21s01): Column count doesn ' t match value count at row 1mysql> INSERT Test (username) SELECT username from us ERs WHERE age>=30; Query OK, 2 rows affected (0.06 sec) records:2 duplicates:0 warnings:0mysql> SELECT * from test;+----+----------+| ID | Username |+----+----------+| 1 | July | | 2 | July |+----+----------+2 rows in Set (0.00 sec)

The third type:
Description: This method inserts the query results into the specified data table.
INSERT [into] tbl_name [(Col_name,...)] SELECT ...

2. Update the record (in two ways)
First type: single-table update

UPDATE [Low_priority][ignore] table_reference SET col_name={expr1| default}[,col_name2={expr2| DEFAULT}] ... [WHERE where_condition]mysql> UPDATE users SET age=age+5; Query OK, 9 rows affected (0.09 sec) rows Matched:9 changed:9 warnings:0mysql> UPDATE users SET age=age+10,sex=0 wher E id%2=0; Query OK, 4 rows affected (0.06 sec) rows Matched:4 changed:4 warnings:0

Second type: multi-table Update

3. Delete a record (in two ways)
First type: single-table deletion

DELETE from Tbl_name [where where_condition]delete from users where id = 6;

Second type: multiple table deletion

4. Find Records

SELECT select_expr[,select_expr ...] [From Table_reference[where Where_condition][group by {col_name|position}{asc| Desc}][having Where_condition][order by {col_name|expr|position}[asc| desc}]][limit {[Offset,]row_count|row_count offset offset}]]select 3+5;+-----+| 3+5 |+-----+| 8 |+-----+

query expressions
Each expression represents the desired column and must have at least one.
Multiple columns are separated by commas.
An asterisk (*) indicates all columns. Tbl_name.* can represent all the columns of a named table.
A query expression can use [as] alias_name to give it an alias.
Aliases can be used for a group by, an ORDER by, or a HAVING clause.
Query all

Mysql> SELECT * from users;+----+----------+----------------------------------+-----+------+| ID | Username | password | Age | Sex |+----+----------+----------------------------------+-----+------+| 1 | Tom | 123 | 29 | 0 | | 2 | July | 123 | 38 | 0 | | 3 | Tom | 123 | 27 | 0 | | 4 | July | 123 | 110 | 0 | | 5 | Tom | 123 | 10 | 0 | | 7 | King | 123 | 8 | 0 | | 8 | Tom | 202cb962ac59075b964b07152d234b70 | 17 | 0 | | 9 | Lucy | 123 | 6 | 0 | | 10 | QQQQQ | 123 | 10 | NULL |+----+----------+----------------------------------+-----+------+

Sort by Query Order

Mysql> SELECT Username,id from users;+----------+----+| Username | ID |+----------+----+| Tom | 1 | | July | 2 | | Tom | 3 | | July | 4 | | Tom | 5 | | King | 7 | | Tom | 8 | | Lucy | 9 | | QQQQQ | Ten |+----------+----+

Querying fields for a specified table

Mysql> SELECT users.id,users.username from users;+----+----------+| ID | Username |+----+----------+| 1 | Tom | | 2 | July | | 3 | Tom | | 4 | July | | 5 | Tom | | 7 | King | | 8 | Tom | | 9 | Lucy | | 10 | QQQQQ |+----+----------+

Use of ' as '

Mysql> SELECT ID username from users; not standard +----------+| Username |+----------+| 1 | | 2 | | 3 | | 4 | | 5 | | 7 | | 8 | | 9 | | |+----------+9 rows in Set (0.00 sec) mysql> SELECT ID as username from users; standard +----------+| Username |+----------+| 1 | | 2 | | 3 | | 4 | | 5 | | 7 | | 8 | | 9 | | Ten |+----------+

4.1. Conditional expressions

Filters the records and displays all records if no WHERE clause is specified.
In the where expression, you can use the functions or operators supported by MySQL.

4.2. Grouping of query results
[GROUP by {col_name|position} [asc| DESC],...]
Group BY sex Field

mysql> SELECT sex from the Users GROUP by sex;+------+| Sex |+------+| NULL | | 0 |+------+2 rows in Set (0.00 sec)

Group based on the first field of a query

mysql> SELECT sex from the Users GROUP by 1;+------+| Sex |+------+| NULL | | 0 |+------+2 rows in Set (0.00 sec)

Grouping based on the second field of a query

Mysql> SELECT Sex,username from the users GROUP by 2;+------+----------+| sex | Username |+------+----------+| 0 | July | | 0 | King | | 0 | Lucy | | NULL | QQQQQ | | 0 | Tom |+------+----------+5 rows in Set (0.00 sec)

4.3 Grouping conditions

[The field used after having Where_condition]hava is either an aggregate function or an error occurs in the preceding field.] mysql> SELECT sex from the Users GROUP by 1 have age >5; ERROR 1054 (42S22): Unknown column ' age ' ' have clause ' mysql> SELECT sex,age from the users GROUP by ; 5;+------+-----+| sex | Age |+------+-----+| NULL | 10 | | 0 | 6 | | 0 | 8 | | 0 | 10 | | 0 | 17 | | 0 | 27 | | 0 | 29 | | 0 | 38 | | 0 |  |+------+-----+9 rows in Set (0.00 sec) mysql> SELECT sex,age from the users GROUP by 1 have age >5;+------+-----+| sex | Age |+------+-----+| NULL | 10 | | 0 | |+------+-----+2 rows in Set (0.00 sec) mysql> SELECT sex,age from the users GROUP by 1 have count (ID) >2;+------+-- ---+| sex | Age |+------+-----+| 0 | #四舍五入后的平均值 +------+-----+

4.4 Sorting the results of a query
[ORDER by {col_name|expr|position}[asc| DESC],...]
Default by Insert Order

Mysql> SELECT * from users;+----+----------+----------------------------------+-----+------+| ID | Username | password | Age | Sex |+----+----------+----------------------------------+-----+------+| 1 | Tom | 123 | 29 | 0 | | 2 | July | 123 | 38 | 0 | | 3 | Tom | 123 | 27 | 0 | | 4 | July | 123 | 110 | 0 | | 5 | Tom | 123 | 10 | 0 | | 7 | King | 123 | 8 | 0 | | 8 | Tom | 202cb962ac59075b964b07152d234b70 | 17 | 0 | | 9 | Lucy | 123 | 6 | 0 | | 10 | QQQQQ | 123 | 10 | NULL |+----+----------+----------------------------------+-----+------+9 rows in Set (0.00 sec)

ORDER By default is ascending

Mysql> SELECT * from the users ORDER by age;+----+----------+----------------------------------+-----+------+| ID | Username | password | Age | Sex |+----+----------+----------------------------------+-----+------+| 9 | Lucy | 123 | 6 | 0 | | 7 | King | 123 | 8 | 0 | | 5 | Tom | 123 | 10 | 0 | | 10 | QQQQQ | 123 | 10 | NULL | | 8 | Tom | 202cb962ac59075b964b07152d234b70 | 17 | 0 | | 3 | Tom | 123 | 27 | 0 | | 1 | Tom | 123 | 29 | 0 | | 2 | July | 123 | 38 | 0 | | 4 | July | 123 | 110 | 0 |+----+----------+----------------------------------+-----+------+9 rows in Set (0.00 sec)

Descending

Mysql> SELECT * from the Users ORDER by age desc;+----+----------+----------------------------------+-----+------+| ID | Username | password | Age | Sex |+----+----------+----------------------------------+-----+------+| 4 | July | 123 | 110 | 0 | | 2 | July | 123 | 38 | 0 | | 1 | Tom | 123 | 29 | 0 | | 3 | Tom | 123 | 27 | 0 | | 8 | Tom | 202cb962ac59075b964b07152d234b70 | 17 | 0 | | 5 | Tom | 123 | 10 | 0 | | 10 | QQQQQ | 123 | 10 | NULL | | 7 | King | 123 | 8 | 0 | | 9 | Lucy | 123 | 6 | 0 |+----+----------+----------------------------------+-----+------+9 rows in Set (0.00 sec)

4.5 Limit the number of query results returned

[LIMIT {[offset,] row_count | row_count offset offset}]mysql> SELECT * from users;+----+----------+------------------ ----------------+-----+------+| ID | Username | password | Age | Sex |+----+----------+----------------------------------+-----+------+| 1 | Tom | 123 | 29 | 0 | | 2 | July | 123 | 38 | 0 | | 3 | Tom | 123 | 27 | 0 | | 4 | July | 123 | 110 | 0 | | 5 | Tom | 123 | 10 | 0 | | 7 | King | 123 | 8 | 0 | | 8 | Tom | 202cb962ac59075b964b07152d234b70 | 17 | 0 | | 9 | Lucy | 123 | 6 | 0 | | 10 | QQQQQ | 123 | 10 | NULL |+----+----------+----------------------------------+-----+------+9 rows in Set (0.00 sec)

Query two article

Mysql> SELECT * from users LIMIT 2;+----+----------+----------+-----+------+| ID | Username | password | Age | Sex |+----+----------+----------+-----+------+| 1 | Tom | 123 | 29 | 0 | | 2 | July | 123 | 38 | 0 |+----+----------+----------+-----+------+2 rows in Set (0.00 sec)

The query starts with subscript 3 and checks two. (MySQL subscript starting from 0)

Mysql> SELECT * from users LIMIT 3,2;+----+----------+----------+-----+------+| ID | Username | password | Age | Sex |+----+----------+----------+-----+------+| 4 | July | 123 | 110 | 0 | | 5 | Tom | 123 | 10 | 0 |+----+----------+----------+-----+------+2 rows in Set (0.00 sec)

The query starts with subscript 2 and checks two. (MySQL subscript starting from 0)

Mysql> SELECT * from users LIMIT 2,2;+----+----------+----------+-----+------+| ID | Username | password | Age | Sex |+----+----------+----------+-----+------+| 3 | Tom | 123 | 27 | 0 | | 4 | July | 123 | 110 | 0 |+----+----------+----------+-----+------+2 rows in Set (0.00 sec)

After the query descending from subscript 2, check two.

Mysql> SELECT * from the Users ORDER by age desc;+----+----------+----------------------------------+-----+------+| ID | Username | password | Age | Sex |+----+----------+----------------------------------+-----+------+| 4 | July | 123 | 110 | 0 | | 2 | July | 123 | 38 | 0 | | 1 | Tom | 123 | 29 | 0 | | 3 | Tom | 123 | 27 | 0 | | 8 | Tom | 202cb962ac59075b964b07152d234b70 | 17 | 0 | | 5 | Tom | 123 | 10 | 0 | | 10 | QQQQQ | 123 | 10 | NULL | | 7 | King | 123 | 8 | 0 | | 9 | Lucy | 123 | 6 | 0 |+----+----------+----------------------------------+-----+------+mysql> SELECT * from the Users ORDER by age DESC LIMIT 2,2;+----+----------+----------+-----+------+| ID | Username | password | Age | Sex |+----+----------+----------+-----+------+| 1 | Tom | 123 | 29 | 0 | | 3 | Tom | 123 | 27 | 0 |+----+----------+----------+-----+------+

　　

MYSQL, notes (recording operations)