Utility SQL statement collection this article is enough

preface

Article along the design of a hypothetical application awesome_app as the main line, from zero create modify database, table, field attributes, index, character set, default value, increment, increment, delete, query, multi-table query, built-in functions and other practical SQL statements. Bookmark this article as a farewell to the scattered and inefficient search for frequently used SQL statements. All SQL is verified in MySQL, you can save it for future reference, or you can also do it together. If you do not install MySQL, you can refer to “Installing MySQL on macOS” (Windows installation is similar).

1. Create

1.1 Creating a Database

Syntax: create database db_name

Example: Create the application database awesome_app

create database `awesome_app`
Copy the code

1.2 Creating a Table

Create table table_name (… columns )

Example: Create the user table Users

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

1.3 Creating An Index

Create index index_name on table_name (column_name)

Example: Create index IDx_id for user ID

create index `idx_id` on `users` (`id`)
/* Create unique index */
create unique index `idx_id` on `users` (`id`)
Copy the code

1.4 Create primary keys for existing columns

A more common way to create a table sentence is to add a primary key (column_name) line after all column definitions.

Alter table table_name add primary key (column_name)

Example: Set the user ID as the primary key

alter table users add primary key (`id`)
Copy the code

1.5 Create an increment constraint for an existing column

Int not NULL AUTO_increment (int not NULL auto_increment);

alter table `users` modify `id` int not null auto_increment
Copy the code

2. Insert the

Grammar:

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

Example: Add a registered user

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

3. Modify

3.1 Modifying Data Records

Grammar:

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

Example:

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

3.2 Changing the Database character Set toutf8

alter database `awesome_app` default character set utf8
Copy the code

3.3 Changing the table character Set toutf8

alter table `users` convert to character set utf8
Copy the code

3.4 Changing the table field character set toutf8

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

3.5 Changing the Field Type

alter table `users` modify `regtime` datetime not null
Copy the code

3.5 Changing The Default Field Values

alter table `users` alter `regtime` set default '2019-10-12 00:00:00'
/* Set the default to the current time current_TIMESTAMP, you need to redefine the entire column */
alter table `users` modify `regtime` datetime not null default current_timestamp
Copy the code

3.6 Modifying Field Comments

alter table `users` modify `id` int not null auto_increment comment 'user ID';
alter table `users` modify `name` char(10) comment 'Username';
alter table `users` modify `avatar` varchar(300) comment 'User profile picture';
alter table `users` modify `regtime` datetime not null default current_timestamp comment 'Registration Time';
Copy the code

After modification, view the changed columns:

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, the references user name | | | avatar | Varchar (300) | utf8_general_ci | YES | | NULL | | select, insert, update, and the references | avatars | | regtime | datetime | NULL | NO | | CURRENT_TIMESTAMP | | select, insert, update, and the references | | registration time +---------+--------------+-----------------+------+-----+-------------------+----------------+-------------------------- -------+--------------+Copy the code

4. Remove

4.1 Deleting Data Records

Delete from table_name where condition

Example: Delete a user whose user name is not specified

Add a user with an empty username
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 line with user name empty
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 |
+----+--------+----------------------------------------------------+------------+
Copy the code

4.2 Deleting a Database

drop database if exists `awesome_app`
Copy the code

4.3 delete table

drop table if exists `users`
Copy the code

4.4 Clearing all data in a table

This operation is equivalent to dropping a table and then creating a table, so you need to have the DROP permission.

truncate table `users`
Copy the code

4.5 Deleting An Index

drop index `idx_id` on `users`
Copy the code

5. The query

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]]
Copy the code

5.2 Querying a Single table

5.2.1 Preparing Data:

insert into users (`name`.`avatar`) values
('Joe'.'http://cdn.awesome_app.com/path/to/xxx/3.jpg'),
('bill'.'http://cdn.awesome_app.com/path/to/xxx/4.jpg'),
('Cathy'.'http://cdn.awesome_app.com/path/to/xxx/5.jpg'),
('the horse 6'.'http://cdn.awesome_app.com/path/to/xxx/6.jpg'),
('xiao seven'.'http://cdn.awesome_app.com/path/to/xxx/7.jpg'),
('liu eight'.'http://cdn.awesome_app.com/path/to/xxx/8.jpg'),
('nine Yang'.'http://cdn.awesome_app.com/path/to/xxx/9.jpg'),
('zheng ten'.'http://cdn.awesome_app.com/path/to/xxx/10.jpg');

/* Adds a duplicate line */
insert into users (`name`.`avatar`) values
('Joe'.'http://cdn.awesome_app.com/path/to/xxx/3.jpg'),
('bill'.'http://cdn.awesome_app.com/path/to/xxx/4.jpg'),
('Cathy'.'http://cdn.awesome_app.com/path/to/xxx/5.jpg');
Copy the code

5.2.2 Querying 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, dick, and harry http://cdn.awesome_app.com/path/to/xxx/4.jpg | 2019-10-13 10:58:37 | | | 5 fifty | http://cdn.awesome_app.com/path/to/xxx/5.jpg | 2019-10-13 10:58:37 | | 6. | | horse 7 http://cdn.awesome_app.com/path/to/xxx/6.jpg 10:58:37 | 2019-10-13 | | | | seven shaw http://cdn.awesome_app.com/path/to/xxx/7.jpg | 2019-10-13 10:58:37 | | | 8 liu eight | http://cdn.awesome_app.com/path/to/xxx/8.jpg | 2019-10-13 10:58:37 | | 9 9 | | Yang http://cdn.awesome_app.com/path/to/xxx/9.jpg | 2019-10-13 10:58:37 | | | 10 zheng ten | http://cdn.awesome_app.com/path/to/xxx/10.jpg | 2019-10-13 10:58:37 | | | | zhang SAN http://cdn.awesome_app.com/path/to/xxx/3.jpg | 2019-10-13 11:20:17 | | | | 12, dick, and harry 13 11:20:17 http://cdn.awesome_app.com/path/to/xxx/4.jpg | 2019-10-13 | | | fifty | http://cdn.awesome_app.com/path/to/xxx/5.jpg | 2019-10-13 11:20:17 | +----+--------+----------------------------------------------------+---------------------+Copy the code

5.2.3 Querying the specified column

mysql> select id,name from users; + - + -- -- -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- -- -- + | 1 | Steven | | 2 | bill | | 3 | threes | | | 4 li si | | | 5 fifty and | | | 6 horses. | | 7 | shaw. | | | 8 liu. | | | 9 Yang. | | | 10 zheng ten | | | 11 threes | | | 12 li si | | | 13 fifty | + - + -- -- -- -- -- -- -- -- +Copy the code

5.2.4 Querying Non-Duplicate Records

mysql> select distinct name,avatar from users; +--------+----------------------------------------------------+ | name | avatar | +--------+----------------------------------------------------+ | steven | Bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg http://cdn.awesome_app.com/path/to/xxx/steven.jpg | | | | | zhang SAN http://cdn.awesome_app.com/path/to/xxx/3.jpg | | |, dick, and harry http://cdn.awesome_app.com/path/to/xxx/4.jpg | | fifty | Six | | http://cdn.awesome_app.com/path/to/xxx/6.jpg http://cdn.awesome_app.com/path/to/xxx/5.jpg | | horse seven | | shaw http://cdn.awesome_app.com/path/to/xxx/7.jpg | |. | | http://cdn.awesome_app.com/path/to/xxx/8.jpg liu 9 | | Yang http://cdn.awesome_app.com/path/to/xxx/9.jpg | | zheng ten | | http://cdn.awesome_app.com/path/to/xxx/10.jpg +--------+----------------------------------------------------+Copy the code

5.2.5 Limiting the number of Query rows

Query the first few lines

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

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

mysql> select id,name from users limit2, 3; + - + -- -- -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- -- -- + | 3 | threes | | | 4 li si | | | 5 fifty | + - + -- -- -- -- -- -- -- -- +Copy the code

5.2.6 sorting

# positive sequence
mysql> select distinct name from users order by name asc limit3; + -- -- -- -- -- -- -- -- + | name | + -- -- -- -- -- -- -- -- + | bill | | Steven | | liu eight | + -- -- -- -- -- -- -- -- +# reverse
mysql> select id,name from users order by id desc limit3; + - + -- -- -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- -- -- + | | 13 fifty and | | | | 12, dick, and harry | | | 11 zhang SAN + - + -- -- -- -- -- -- -- -- +Copy the code

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;
Copy the code

Statistics the number of users by city group

mysql> select city, count(name) as num_of_user from users group by city; + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | city | num_of_user | + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | Shanghai | 3 | | Beijing | 3 | | guangzhou | 2 | | | in San Francisco Shenzhen | 3 | 1 | | | | | + 1 in Seattle -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + mysql > select city, count(name) as num_of_user from users group by city having num_of_user=1; + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | city | num_of_user | + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 | San Francisco | | 1 | in Seattle +-----------+-------------+ 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 | | 3 | | shenzhen +--------+-------------+Copy the code

5.3 Associated Query of Multiple tables

5.3.1 Preparing Data

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 group' after `city`;

insert into `groups` (`title`) values ('bosses'), (' 'new'), ('cooking chicken');
insert into `orders` (`title`.`user_id`) values ('How are Big Men Made? '.3), ('MySQL from new to delete'.6), ('Vegetable Chicken Stepping on a 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);
Copy the code

5.3.2 the join

join

Use to query for 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 "bosses have?" Six | | | horse "MySQL from sprout new to delete library run" | | | 9 Yang the chicken dish on pit "| + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

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 "bosses have?" Six | | | horse "MySQL from sprout new to delete library run" | | | 9 Yang the chicken dish on pit "| + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

left join

Port connection. Returns all rows in the left table, even if there are no matching rows in the right table.

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 "bosses have?" Six | | | horse "MySQL from sprout new to delete library run" | | | 9 Yang the chicken dish on pit "| | Steven | NULL | | bill | NULL | | li si | NULL | | fifty | NULL | | | NULL seven shaw | | | NULL eight liu | | | 10 zheng NULL | | zhang SAN | NULL | | li si | NULL | | fifty | NULL | +-----------+--------------------------------------+Copy the code

right join

The right connection. In contrast to left JOIN, all rows in the right table are returned, even if there are no matching rows in the left table. Those that do not match are filled with NULL.

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 | + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | bosses | Steven | | bosses bill | | | 'new | Li si | | of the new six | | horse new | | sprouting liu eight | | 'new | zheng ten | |' new | li si | | | | food chicken zhang SAN chicken | | food fifty and | | food chicken | fifty and | | NULL seven | | shaw | NULL nine | | Yang | NULL | zhang SAN | + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - +Copy the code

5.3.3 the union

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

mysql> (select `id`, `title` from `groups`) union (select `id`, `title` from `orders`); + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id title | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | bosses | | 2 | sprouting new | | 3 | food chicken | | 1 | "bosses have?" | | 2 | the MySQL from sprout new to delete library run "| | 3 | the | chicken dishes on pit ji + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

Function of 6.

6.1 grammar

select function(column) from table_name

6.2 Aggregate Functions

Aggregate functions operate on a series of values and return a single value. Usually used with group by statements.

function	describe
avg(column)	Returns the average value of a column
count(column)	Returns the number of rows in a column (excluding NULL values)
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	describe
ucase(c)	Convert to uppercase
lcase(c)	Convert to lowercase
mid(c, start[, end])	Extract characters from text
len(c)	Return text length
instr(c, char)	Returns the numeric position of the specified character in the text
left(c, number_of_char)	Returns the left side of the text
right(c, number_of_char)	Returns the right hand portion of the text
round(c, decimals)	The log value specifies the number of decimal places to be rounded
mod(x, y)	Mod (mod)
now()	Returns the current system date
format(c, format)	Formatted display
datediff(d, date1, date2)	date

---

If MySQL is not installed, see Installing MySQL on macOS (Windows installation is similar).

This article was first published with a public number