Programmer’s most practical SQL statement collection, read this is enough

Time:2021-7-23

preface

Along the design of a hypothetical application awesomes_ App as the main line, create and modify database, table, field attribute, index, character set, default value, add, delete, modify query, multi table query, built-in function and other practical SQL statements from zero. Collect this article, bid farewell to scattered and inefficient search frequently used SQL statements. All SQL is verified under mysql, which can be reserved for future review and reference, or you can do it together with your hands. If MySQL is not installed, you can refer to it《Installing MySQL on Mac OS》 (Windows installation is much the same)。

1. Create

1.1 create database

Syntax: create databasedb_name

Example: create application database awesome_ app


create database `awesome_app`

1.2 form creation

Syntax: create tabletable_name ( … columns )

Example: create user table users


create table `users`
(
 `id` int,
 `name` char(10),
 `avatar` varchar(300),
 `regtime` date
)

1.3 index creation

Syntax: create indexindex_name on table_name (column_name)

Example: creating index idx for user ID_ id

create index `idx_id` on `users` (`id`)
/*Create a unique index*/
create unique index `idx_id` on `users` (`id`)

1.4 create primary keys for existing columns

A more common way is to add a row of primary key (column) after all column definitions in the CREATE TABLE statement_ name)。

Syntax: alter tabletable_name add primary key (column_name)

Example: set user ID as primary key


alter table users add primary key (`id`)

1.5 create incremental constraints for existing columns

A more common way is to add the autoincrement column id int not null Auto in the CREATE TABLE statement_ increment。


alter table `users` modify `id` int not null auto_increment

2. Insert

Syntax:

  • insert into table_name values (value1, value2, …)
  • insert into table_name (column1, column2, …) values (value1, value2, …)

Example: new registered users

insert into `users` values (1, 'ken', 'http://cdn.awesome_app.com/path/to/xxx/avatar1.jpg', curdate())
/*Specify column insert*/
insert into `users` (`name`, `avatar`) values ('bill', 'http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg')

3. Modification

3.1 modifying data records

Syntax:

  • update table_name set column=new_value where condition
  • update table_name set column1=new_value1,column2=new_value2,… wherecondition

Example:

update `users` set `regtime`=curdate() where `regtime` is null
/*Modify multiple columns at once*/
update `users` set `name`='steven',`avatar`='http://cdn.awesome_app.com/path/to/xxx/steven.jpg' where `id`=1

3.2 modify the database character set to utf8


alter database `awesome_app` default character set utf8

3.3 modify the table character set to utf8


alter table `users` convert to character set utf8

3.4 modify the table field character set to utf8


alter table `users` modify `name` char(10) character set utf8

3.5 modify field type


alter table `users` modify `regtime` datetime not null

3.5 modify the default value of the field

alter table `users` alter `regtime` set default '2019-10-12 00:00:00'
/*The default setting is current time_ Timestamp, you need to redefine the entire column*/
alter table `users` modify `regtime` datetime not null default current_timestamp

3.6 modify field notes

alter table `users` modify `id` int not null auto_ Increment comment 'user ID';
Alter table 'users' modify' name 'char (10) comment' user name ';
Alter table 'users' modify' Avatar 'varchar (300) comment' user profile ';
alter table `users` modify `regtime` datetime not null default current_ Timestamp comment 'registration time';

After modification, view the modified column:

mysql> show full columns from users;
+---------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+--------------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+---------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+--------------+
| id | int(11) | NULL | NO | PRI | NULL | auto_ Increment | select, insert, update, references | user ID|
| name | char(10) | utf8_ general_ Ci | yes | null | select, insert, update, references | user name|
| avatar | varchar(300) | utf8_ general_ Ci | yes | null | select, insert, update, references | user profile|
| regtime | datetime | NULL | NO | | CURRENT_ Timestamp | select, insert, update, references | registration time|
+---------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+--------------+

4. Deletion

4.1 deleting data records

Syntax: delete fromtable_name where condition

Example: delete a user whose user name is not filled in

#First add a user whose user name is empty
mysql> insert into `users` (`regtime`) values (curdate());
mysql> select * from users;
+----+--------+----------------------------------------------------+------------+
| id | name | avatar | regtime |
+----+--------+----------------------------------------------------+------------+
| 1 | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg | 2019-10-12 |
| 2 | bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | 2019-10-12 |
| 3 | NULL | NULL | 2019-10-12 |
+----+--------+----------------------------------------------------+------------+
#Delete row with empty user name
mysql> delete from `users` where `name` is null;
mysql> select * from users;
+----+--------+----------------------------------------------------+------------+
| id | name | avatar | regtime |
+----+--------+----------------------------------------------------+------------+
| 1 | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg | 2019-10-12 |
| 2 | bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | 2019-10-12 |
+----+--------+----------------------------------------------------+------------+

4.2 delete database


drop database if exists `awesome_app`

4.3 delete table


drop table if exists `users`

4.4 clear all data in the table

This operation is equivalent to drop table first and then create table, so you need to have drop permission.


truncate table `users`

4.5 delete index


drop index `idx_id` on `users`

5. Inquiry

5.1 grammar


SELECT
 [ALL | DISTINCT | DISTINCTROW ]
 [HIGH_PRIORITY]
 [STRAIGHT_JOIN]
 [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
 [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
 select_expr [, select_expr ...]
 [FROM table_references
 [PARTITION partition_list]
 [WHERE where_condition]
 [GROUP BY {col_name | expr | position}
 [ASC | DESC], ... [WITH ROLLUP]]
 [HAVING where_condition]
 [ORDER BY {col_name | expr | position}
 [ASC | DESC], ...]
 [LIMIT {[offset,] row_count | row_count OFFSET offset}]
 [PROCEDURE procedure_name(argument_list)]
 [INTO OUTFILE 'file_name'
 [CHARACTER SET charset_name]
 export_options
 | INTO DUMPFILE 'file_name'
 | INTO var_name [, var_name]]
 [FOR UPDATE | LOCK IN SHARE MODE]]

5.2 single table query

5.2.1 data preparation:

insert into users (`name`, `avatar`) values
('zhang San ',' http://cdn.awesome_ app.com/path/to/xxx/3.jpg'),
('li Si ',' http://cdn.awesome_ app.com/path/to/xxx/4.jpg'),
('wang Wu ',' http://cdn.awesome_ app.com/path/to/xxx/5.jpg'),
('ma Liu ',' http://cdn.awesome_ app.com/path/to/xxx/6.jpg'),
('xiao Qi ',' http://cdn.awesome_ app.com/path/to/xxx/7.jpg'),
('liu Ba ',' http://cdn.awesome_ app.com/path/to/xxx/8.jpg'),
('yang Jiu ',' http://cdn.awesome_ app.com/path/to/xxx/9.jpg'),
('zheng Shi ',' http://cdn.awesome_ app.com/path/to/xxx/10.jpg');
/*Add duplicate lines*/
insert into users (`name`, `avatar`) values
('zhang San ',' http://cdn.awesome_ app.com/path/to/xxx/3.jpg'),
('li Si ',' http://cdn.awesome_ app.com/path/to/xxx/4.jpg'),
('wang Wu ',' http://cdn.awesome_ app.com/path/to/xxx/5.jpg');

5.2.2 query all columns

mysql> select * from users;
+----+--------+----------------------------------------------------+---------------------+
| id | name | avatar | regtime |
+----+--------+----------------------------------------------------+---------------------+
| 1 | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg | 2019-10-12 00:00:00 |
| 2 | bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | 2019-10-12 00:00:00 |
|3. Zhang San| http://cdn.awesome_ app.com/path/to/xxx/3.jpg | 2019-10-13 10:58:37 |
|4 | Li Si| http://cdn.awesome_ app.com/path/to/xxx/4.jpg | 2019-10-13 10:58:37 |
|5. Wang Wu| http://cdn.awesome_ app.com/path/to/xxx/5.jpg | 2019-10-13 10:58:37 |
|6. Ma Liu| http://cdn.awesome_ app.com/path/to/xxx/6.jpg | 2019-10-13 10:58:37 |
|7. Xiao Qi| http://cdn.awesome_ app.com/path/to/xxx/7.jpg | 2019-10-13 10:58:37 |
|8. Liu Ba| http://cdn.awesome_ app.com/path/to/xxx/8.jpg | 2019-10-13 10:58:37 |
|9. Yang Jiu| http://cdn.awesome_ app.com/path/to/xxx/9.jpg | 2019-10-13 10:58:37 |
|10. Zheng Shi| http://cdn.awesome_ app.com/path/to/xxx/10.jpg | 2019-10-13 10:58:37 |
|11. Zhang San| http://cdn.awesome_ app.com/path/to/xxx/3.jpg | 2019-10-13 11:20:17 |
|12. Li Si| http://cdn.awesome_ app.com/path/to/xxx/4.jpg | 2019-10-13 11:20:17 |
|13. Wang Wu| http://cdn.awesome_ app.com/path/to/xxx/5.jpg | 2019-10-13 11:20:17 |
+----+--------+----------------------------------------------------+---------------------+

5.2.3 query the specified column

mysql> select id,name from users;
+----+--------+
| id | name |
+----+--------+
| 1 | steven |
| 2 | bill |
|3. Zhang San|
|4 | Li Si|
|5. Wang Wu|
|6 | Ma Liu|
|7. Xiao Qi|
|8 | Liu Ba|
|9. Yang Jiu|
|10. Zheng Shi|
|11. Zhang San|
|12. Li Si|
|13 | Wang Wu|
+----+--------+

5.2.4 query non duplicate records

mysql> select distinct name,avatar from users;
+--------+----------------------------------------------------+
| name | avatar |
+--------+----------------------------------------------------+
| steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg |
| bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg |
|Zhang San| http://cdn.awesome_ app.com/path/to/xxx/3.jpg |
|Li Si| http://cdn.awesome_ app.com/path/to/xxx/4.jpg |
|Wang Wu| http://cdn.awesome_ app.com/path/to/xxx/5.jpg |
|Ma Liu| http://cdn.awesome_ app.com/path/to/xxx/6.jpg |
|Xiao Qi| http://cdn.awesome_ app.com/path/to/xxx/7.jpg |
|Liu Ba| http://cdn.awesome_ app.com/path/to/xxx/8.jpg |
|Yang Jiu| http://cdn.awesome_ app.com/path/to/xxx/9.jpg |
|Zheng Shi| http://cdn.awesome_ app.com/path/to/xxx/10.jpg |
+--------+----------------------------------------------------+

5.2.5 limit the number of query lines

Query the first few lines


mysql> select id,name from users limit 2;
+----+--------+
| id | name |
+----+--------+
| 1 | steven |
| 2 | bill |
+----+--------+

Query the rows starting from the specified offset (the first row offset is 0)

mysql> select id,name from users limit 2,3;
+----+--------+
| id | name |
+----+--------+
|3. Zhang San|
|4 | Li Si|
|5. Wang Wu|
+----+--------+

5.2.6 sorting

#Positive order
mysql> select distinct name from users order by name asc limit 3;
+--------+
| name |
+--------+
| bill |
| steven |
|Liu Ba|
+--------+
#Reverse order
mysql> select id,name from users order by id desc limit 3;
+----+--------+
| id | name |
+----+--------+
|13 | Wang Wu|
|12. Li Si|
|11. Zhang San|
+----+--------+

5.2.7 grouping

Add city field

Alter table ` users ` add ` city ` varchar (10) comment 'user's city' after ` name ';
Update 'users' set' city '='san Francisco' where 'ID' = 1;
Update 'users' set' city '='seattle' where 'ID' = 2;
Update ` users ` set ` city ` = 'Beijing' where ` ID ` in (3,5,7);
Update 'users' set' city '='shanghai' where 'ID' in (4,6,8);
Update ` users ` set ` city ` = 'Guangzhou' where ` ID ` between 9 and 10;
Update 'users' set' city '='shenzhen' where 'ID' between 11 and 13;

Number of users grouped by city

mysql> select city, count(name) as num_of_user from users group by city;
+-----------+-------------+
| city | num_of_user |
+-----------+-------------+
|Shanghai | 3|
|Beijing | 3|
|Guangzhou | 2|
|San Francisco 1|
|Shenzhen | 3|
|Seattle | 1|
+-----------+-------------+
mysql> select city, count(name) as num_of_user from users group by city having num_of_user=1;
+-----------+-------------+
| city | num_of_user |
+-----------+-------------+
|San Francisco 1|
|Seattle | 1|
+-----------+-------------+
mysql> select city, count(name) as num_of_user from users group by city having num_of_user>2;
+--------+-------------+
| city | num_of_user |
+--------+-------------+
|Shanghai | 3|
|Beijing | 3|
|Shenzhen | 3|
+--------+-------------+

5.3 multi table Association query

5.3.1 data preparation

create table if not exists `orders`
(
 `id` int not null primary key auto_ Increment comment 'order ID',
 `Title ` varchar (50) not null comment 'order title',
 `user_ ID ` int not null comment 'user ID',
 `cretime` timestamp not null default current_ Timestamp comment 'creation time'
);
create table if not exists `groups`
(
 `id` int not null primary key auto_ Increment comment 'user group ID',
 `Title ` varchar (50) not null comment 'user group title',
 `cretime` timestamp not null default current_ Timestamp comment 'creation time'
);
alter table `users` add `group_ ID ` int comment 'user grouping' after 'city';
Insert into 'groups' ('title') values', ('mengxin '), ('caiji');
insert into `orders` (`title`, `user_ ID `) values ('How did the big guy make it? ', 3) , ('mysql running from Mengxin to delete database ', 6), ('caiji stepping on the pit', 9));
update `users` set `group_id`=1 where `id` between 1 and 2;
update `users` set `group_id`=2 where `id` in (4, 6, 8, 10, 12);
update `users` set `group_id`=3 where `id` in (3, 5, 13);

5.3.2 join

join

Used to query matching data in multiple tables.

mysql> select `users`.`name` as `user_name`, `orders`.`title` as `order_title` from `users`, `orders` where `orders`.`user_id`=`users`.`id`;
+-----------+--------------------------------------+
| user_name | order_title |
+-----------+--------------------------------------+
|Zhang San | "how is the big man made?"|
|Ma Liu | "MySQL from cute new to deleted"|
|The story of the vegetable chicken stepping on the pit|
+-----------+--------------------------------------+

inner join

Internal connection. The effect is the same as join, but the usage is different. Join uses where, inner join uses on.

mysql> select `users`.`name` as `user_name`, `orders`.`title` as `order_title` from `users` inner join `orders` on `orders`.`user_id`=`users`.`id`;
+-----------+--------------------------------------+
| user_name | order_title |
+-----------+--------------------------------------+
|Zhang San | "how is the big man made?"|
|Ma Liu | "MySQL from cute new to deleted"|
|The story of the vegetable chicken stepping on the pit|
+-----------+--------------------------------------+

left join

Left connection. Returns all the rows in the left table. Even if there are no matching rows in the right table, the unmatched rows are filled with null.

```
mysql> select `users`.`name` as `user_name`, `orders`.`title` as `order_title` from `users` left join `orders` on `orders`.`user_id`=`users`.`id`;
+-----------+--------------------------------------+
| user_name | order_title |
+-----------+--------------------------------------+
|Zhang San | "how is the big man made?"|
|Ma Liu | "MySQL from cute new to deleted"|
|The story of the vegetable chicken stepping on the pit|
| steven | NULL |
| bill | NULL |
|Li Si | null|
|Wang Wu|
|Xiao Qi|
|Liu Ba|
|Zheng Shiyu|
|Zhang San|
|Li Si | null|
|Wang Wu|
+-----------+--------------------------------------+

```

**right join**

Right link. Contrary to left join, all rows in the * * right table * * will be returned. Even if there are no matching rows in the * * left table * *, the mismatched rows will be filled with null.
```sql
mysql> select `groups`.`title` as `group_title`, `users`.`name` as `user_name` from `groups` right join `users` on `users`.`group_id`=`groups`.`id`;
+-------------+-----------+
| group_title | user_name |
+-------------+-----------+
|Big brother|
|Big man | bill|
|Meng Xin Li Si|
|Meng Xin Ma Liu|
|Meng Xin Liu Ba|
|Meng Xin Zheng Shi|
|Meng Xin Li Si|
|Vegetable chicken Zhang San|
|Chicken with vegetables|
|Chicken with vegetables|
|Null | Xiao Qi|
|Null | Yang Jiu|
|Zhang San|
+-------------+-----------+

```

**5.3.3 union**

Union is used to merge two or more query results. The merged query results must have the same number of columns, have similar data types, and have the same order of columns.


```sql
mysql> (select `id`, `title` from `groups`) union (select `id`, `title` from `orders`);
+----+--------------------------------------+
| id | title |
+----+--------------------------------------+
|Big brother|
|2. Mengxin|
|3 | vegetable chicken|
|1 | "how is the big man made?"|
|2 | "MySQL from cute new to deleted"|
|3 | "the story of the vegetable chicken stepping on the pit"|
+----+--------------------------------------+
```

6. Function

6.1 syntax

**select function**(*column*) **from** *table_name*

6.2 aggregate functions

The aggregate function operates on a series of values and returns a single value. Usually used with the group by statement.

Function description AVG (column) returns the average value of a column count (column) returns the number of rows (excluding null value) count (*) returns the number of rows selected first (column) returns the value of the first record in the specified field last (column) returns the value of the last record in the specified field max (column) returns the highest value of a column min (column) returns the lowest value of a column sum (column) Returns the sum of a column. 6.3 scalar functions

Function description ucase (c) to uppercase lcase (c) to lowercase mid (C, start [, end]) extract the character from the text len (c) return the text length instr (C, char) return the numerical position left (C, number) of the specified character in the text_ of_ Char) returns the left part of the text right (C, number)_ of_ Char) returns the right part of the text. Round (C, decimals) specifies the number of decimal places. Mod (x, y) is rounded. Now() returns the current system date. Format (C, format) displays the date diff (D, date1, date2) date calculation

The above is the programmer’s most practical SQL statement collection. After reading this article, it’s enough. For more information about programmer’s SQL statements, please pay attention to other related articles of developer!