This is the first day of my participation in the August Challenge. For details, see:August is more challenging

Chapter 1 Overview of MySQL

Why should the front end learn the database

Recently, there has been a hot word “the new generation of migrant workers”, among which there is a special group of “front-end engineers”, which was also called “front-end cut graph zai” before it was named. Their status is low, but the education level is high, the occupation expectation is high, the work tolerance is high, the material and spiritual enjoyment is extremely low, the code farmer’s marginal day laborers.

Until one day, the earth spring flowers. The front end has revolutionized the status…

However, at this time, there is another new problem, “inner volume”. The huge front group is not willing to continue to be “oppressed”, they start to roll. Today, I will bring you an inner scroll artifact – “database”.

Front-end engineers can do business in the early stage, but after digging deep in the career is the front and back end, which is also the reason to learn the database.

If you don’t learn, someone else will.

“Do unto others as you would have them do unto you.”

The back end is learning vue, you are still pointing to their own acres of land ❓

What is a database

A database is a container used to store data. There are many databases on the market, including relational databases Oracle, mySQL, SQL Server, and documented database mongoDB, etc. We’re not going to talk about how a database works, we’re just going to talk about how it works.

What do you learn from this series of documents?

First of all, you can learn most of the uses of MySQL if you really follow through. You will also learn:

• Data types in SQL
• How to manipulate data with SQL statements
• How are views used
• How to do SQL programming
• The use of stored procedures
• A paradigm for table creation
• Create table indexes and constraints
• How to Use transactions
• MySQL in the lock
• The use of the orm
• Use mysql in Node
• .

Without further ado, let’s get started!

Chapter 2 MySQL data types

Database as an important means of data storage, it is very important to define the data type of the column of the data table. MySQL provides a number of data types to distinguish between different data types. In practice, defining appropriate data types can also improve database performance. MySQL data types are divided into the following types:

• Numeric types
• Date and time types
• String type

1. Numeric types

The integer

type	Byte size	Signed range	Unsigned range	said
tinyint	1 byte	(128127)	(0255).	A small integer value
smallint	2 bytes	(-32 768，32 767)	,65 (0 535).	Large integer value
mediumint	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	Large integer value
int	4 bytes	(-2 147 483 648，2 147 483 647)	(0,4 294, 967, 295)	Large integer value
bigint	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	Maximum integer value

So how do we represent a Boolean?

Usually we use Tinyint (1) for a Boolean value, where 0 is false and 1 is true.

Limit the use of negative numbers

Unsigned follows the field.

Create table Id unsigned
create table test4(id int(10) unsigned,num int(10));
-- Insert data
-- 1. Insert 10,10
insert into test4 values(10.10);

Query OK, 1 row affected (0.00 sec)
-- 2. Insert -10,10
insert into test4 values(- 10.10);

ERROR 1264 (22003) :Out of range value for column 'id' at row 1
-- 3. Insert 10,-10
insert into test4 values(10.- 10);

Query OK, 1 row affected (0.00 sec)
Copy the code

As we can see from the example, if unsigned is declared, it is not possible to insert negative numbers in both integral and floating point types.

What happens when the data is out of the maximum length range?

First of all, in Tinyint (100), 100 refers to the display width range, not only can store to 100. Here’s an example 🌰:

Create a table
create table test(id int, num tinyint(100));
Copy the code

You can see that we set the display width of the NUM field to 100.

Insert a piece of data
insert into test values(1.111);
-- Insert successful
Query OK, 1 row affected (0.00 sec)
Copy the code

As you can see, our data was inserted and again, tinyint’s unsigned maximum is 255, so we’re going to insert a data of 256.

Insert data 280
insert into test2 values(2.280);
- an error
ERROR 1264 (22003) :Out of range value for column 'age' at row 1
Copy the code

You can see that our database is reporting an error. The data is not inserted into the data table. So we are in the design of the table when the choice of numerical type to choose the appropriate appropriate, not large.

So what happens when our database increment id exceeds the maximum length?

First of all, if we set the primary key constraint, then we get the same error as above, the primary key conflict. If not set, the database will automatically generate a row_id and the new data will overwrite the old data. So when setting the primary key, try to use bigint.

detailed

floating-point

type	Byte size	Signed range	Unsigned range	said
float	4 bytes	(-3.402 823 466e +38, -1.175 494 351 e-38), 0, (1.175 494 351 e-38, 3.402 823 466 351 E+38)	0, (1.175 494 351e-38, 3.402 823 466e +38)	Single precision
double	8 bytes	(-1.797 693 134 862 315 7 E+308, -2.225 073 858 507 201 4 e-308), 0, (2.225 073 858 507 201 4 e-308), 1.797 693 134 862 315 7 E+308)	0, (2.225 073 858 507 201 4 e-308, 1.797 693 134 862 315 7 E+308)	double
decimal	For Decimal (M,D), M+2 if M>D otherwise D+2	Depends on the values of M and D	Depends on the values of M and D	Small numerical

Float, double, and Decimal can all be followed by lengths.

• float(M,D)
• double(M,D)
• decimal(M,D)

Here, M,D, where M is the display width and D is the number of decimal places.

How do we choose between float and double?

Float computes faster than double and takes up half the space of double. But float is less accurate than double. So how do we choose? Float can represent fewer decimal places, double can represent more places than float and be more precise. In practice, we will use decimal more often. Decimal can determine the number of decimal places, its storage space depends on M and D values, uses less space, and is more flexible.

How does decimal calculate storage space

Official forms:

Leftover Digits	Number of Bytes
0	0
1–2	1
3, 4	2
5–6	3
7–9	4

How does this work?

For example, we define:

• A decimal (15, 6)

If the number of integers is 15-6 = 9 and the number of decimal parts is 6, the storage space is 4 + 3 = 7.

• A decimal (20, 9)

If the number of integers is 20-9 = 11 and the number of decimal parts is 9, the storage space is 4 + 1 + 3. Here 1 is 11-9=2, 2 corresponds to the number of bytes 1. Every time 9 is exceeded, subtract 9 and start counting.

Here’s an example 🌰:

We can use these three types as follows:

Create table test
create table test(id float(6.2),id2 double(10.4),id3 decimal(4.2));
- get
mysql> desc test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | float(6.2)   | YES  |     | NULL    |       |
| id2   | double(10.4) | YES  |     | NULL    |       |
| id3   | decimal(4.2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

-- Insert data
insert into test values(10.22.200.44444.88.88);

-- Get the result
mysql> select * from test;
+-------+----------+-------+
| id    | id2      | id3   |
+-------+----------+-------+
| 10.22 | 200.4444 | 88.88 |
+-------+----------+-------+
1 row in set (0.01 sec)

As you can see, we inserted 200.44444 into ID2, which exceeded the limit and only 200.4444 was displayed
Copy the code

Use the right combination of integers and floating-point numbers to better store data.

• You can follow an unsigned field to indicate that negative numbers cannot be used
• You can use tinyint(1) to represent the Boolean values 0, 1
• Floating-point numbers are generally defined using descmal

Date and time types

type	Byte size	Signed range	Unsigned range	said
date	3	The 1000-01-01/1000-01-01	YYYY-MM-DD	Date value
time	3	‘- 838:59:59’/’ 838:59:59 ‘	HH:MM:SS	Time value or duration
year	1	1901/2155	YYYY	Year value
datetime	8	The 1000-01-01 00:00:00/1000-01-01 23:59:59	YYYY-MM-DD HH:MM:SS	Mix date and time values
timestamp	4	1970-01-01 00:00:00/2038 The end time was 2147483647 SEC, 2038-1-19 11:14:07 Beijing time, 03:14:07 GMT on January 19, 2038	YYYYMMDD HHMMSS	Mix date and time values, time stamps

How do we choose the date type

In general, we consider several options to store time:

• int
• timestamp
• datetime

Select int

Only time stamps, which are milliseconds, can be stored. Int is a convenient choice, but we need to export the data, and when we do some data analysis, it’s hard to see the exact time.

Select timestamp type

This type essentially stores the number of milliseconds, and it does some time formatting, so it looks intuitive. But he does have scope. It ends in 2038

Select the dateTime type

Storage time format, 8 bytes, a large range, more applicable.

Here’s an example 🌰:

Create a table
create table test(id int(10),t_int int(10),t_time timestamp(6),t_date datetime(2));
--desc test;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int          | YES  |     | NULL    |       |
| t_int  | int          | YES  |     | NULL    |       |
| t_time | timestamp(6) | YES  |     | NULL    |       |
| t_date | datetime(2)  | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

Insert time data into table
insert into test5 values(1,unix_timestamp(now()),now(),now());

Results -
+------+------------+----------------------------+------------------------+
| id   | t_int      | t_time                     | t_date                 |
+------+------------+----------------------------+------------------------+
|    1 | 1627461516 | 202107 -- 28 16:38:36.000000 | 202107 -- 28 16:38:36.00 |
+------+------------+----------------------------+------------------------+

Copy the code

We can see that there are three ways to store time, timestamp and datetime look and feel very similar.

3. String type

type	Byte size	said
char	0-255 bytes	Fixed length string
varchar	0-65535 bytes	Variable-length string
tinyblob	0-255 bytes	A binary string of up to 255 characters
tinytext	0-255 bytes	Short text string
blob	0-65 535 bytes	Long text data in binary form
text	0-65 535 bytes	Long text data
mediumblob	0-16 777 215 bytes	Medium length text data in binary form
mediumtext	0-16 777 215 bytes	Medium length text data
longblob	0-4 294 967 295 bytes	Large text data in binary form
longtext	0-4 294 967 295 bytes	Maximum text data

The difference between char and varchar

1. char(n)andvarchar(n)N is the number of characters. If you exceed it, it will be truncated.
2. char(n)No matter how big the actual storage is, it takes up n characters.
3. varchar(n)It’s just going to take up the byte space that the actual character should take up plus 1, which is the length of the character.
4. char(n)It’s going to truncate the trailing space,varchar(n)Do not.

So generally char(n) is used to store the fields of a known comparison segment. Like a password or something.

Blob or text

1. blobIs used to store binary strings. There are three types of different lengths: blob, MediumBlob, longblob.
2. textUsed to store string text, there are three types of different lengths: text, MediumText, longText.

Here’s an example 🌰:

Create a table
create table test6(id int(10), name varchar(10),pwd char(10),color blob(6),txt text(100));
-- Insert data
insert into test6 values(1," Mengjia "," ZS123 "," FFFFFF "," I love my motherland!" );insert into test6 values(2"Meng Yang "," No 23",0xffffff"," I love my motherland! );Results -
+------+--------+---------+----------------+---------------------+
| id   | name   | pwd     | color          | txt                 |
+------+--------+---------+----------------+---------------------+
|    1 |Good dream| zs123   | 0x666666666666 |I love my motherland!       |
|    2 |Meng Yang|There is no23 | 0xFFFFFF       |I love my motherland!       |
+------+--------+---------+----------------+---------------------+
Copy the code

As you can see, the inserted color field, whether string or binary, is stored as binary data.

conclusion

When we get the demand, determine the technical proposal, we need to design the database, the definition of each table, the definition of data types is very important, we need to carefully and proficient in the use of SQL data types, not only applied to the table definition, in SQL programming is also essential. This chapter covers SQL data types, including:

• Numeric types: integer and floating point
• Date and time types
• String type

These types already cover most of our scenes. We need to pay attention to the scope and storage space of some data types, flexible use of type definition can better complete the task of requirements.

SQL data type table reference

Chapter 3 Constraints on creating a MySQL table

Primary key constraints

A primary key constraint uniquely identifies a single record in a table. By adding a constraint to a field, you can make it non-repetitive and non-empty. Use the keyword primary key for this primary key constraint.

Create a table of pets
create table pet(
    id int primary key,
    name varchar(20));-- query table PET
desc pet;
- get
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
Copy the code

Next, we insert a piece of data into the PET table:

Insert a piece of data
insert into pet values(1.'dog');
- the query
select * from pet;
- get
+----+--------+
| id | name   |
+----+--------+
|  1 |dog|
+----+--------+
Copy the code

At this point, can we continue to insert data with id=1?

Of course not.

insert into pet values(1.'dog');
- an error
ERROR 1062 (23000): Duplicate entry '1' for key 'pet.PRIMARY'
Copy the code

Similarly, the id cannot be passed null. In this way, we can identify a piece of data by its unique ID.

To uniquely identify a piece of data, you can also create a table in the form of a federated primary key.

Create table with primary key
create table pet1(
    id int,
    name varchar(10),
    pwd int(10),
    primary key(id,name)
);
-- desc
desc pet1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(10) | NO   | PRI | NULL    |       |
| pwd   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
Copy the code

Let’s see, insert two pieces of data.

insert into pet1 values(1"Dog ",123);
insert into pet1 values(2"Cat",345);
-- can get
+----+--------+------+
| id | name   | pwd  |
+----+--------+------+
|  1 |dog|  123 |
|  2 |The cat cat|  345 |
+----+--------+------+
Copy the code

If the inserted data ID and name are the same, it cannot be inserted.

2. Self-increasing constraints

In general, autoincrement constraints are used in conjunction with primary key constraints. Use the keyword “auto_increment” to indicate autoincrement constraints. Let’s create a table.

create table pet2(
    id int primary key auto_increment,
    name varchar(20));- get
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
Copy the code

Next, let’s insert two pieces of data:

insert into pet2 values(1, "dog");insert into pet2 (name) values(" cat ");- get
+----+--------+
| id | name   |
+----+--------+
|  1 |dog|
|  2 |The cat cat|
+----+--------+
Copy the code

Obviously, the data “cat” we insert has no ID, but mysql makes our ID grow automatically.

Note: If we forget to add the primary key constraint during table creation, we can continue to add, modify, and delete the primary key constraint.

- add
alter table pet3 add primary key(id);
- delete
alter table pet3 drop primary key;
- to modify
alter table pet3 modify id int primary key;
Copy the code

3. Unique constraints

The value of a field decorated by a constraint cannot be repeated. Use the keyword “unique” to add unique constraints.

create table pet5(
    id int,
    name varchar(20),
    unique(name)
);
- get
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
Copy the code

Note: We can also add the unique constraint directly after the keyword “unique”. Unique constraints can also be added in conjunction with primary key constraints. Two bonds together do not repeat.

And what are the unique constraints on mode operations?

After a 10000 table is created, a unique constraint can be added or deleted.

How to delete a unique constraint
alter table pet7 drop index name; 

Add, modify
alter table pet7 modify name varchar(20) unique;
Copy the code

The specific usage can be their own experiment. I won’t do it here.

Four, non-empty constraints

The decorated field cannot be empty. Use the keyword not NULL to create a non-null constraint.

create table pet8(
    id int,
    name varchar(20) not null
);
--desc
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

-- Insert data
insert into pet8 values(1, "dog");insert into pet8 values(2, "");- get
+------+--------+
| id   | name   |
+------+--------+
|    1 |dog|
|    2 |        |
+------+--------+
insert into pet8 values(2.null);
- get
ERROR 1048 (23000) :Column 'name' cannot be null
Copy the code

Obviously, with the NOT NULL constraint, the field will no longer be null.

Note: Null is not NULL.

Default constraints

When a field value is inserted and no value is passed, the default value is used. Use the keyword default to create a default constraint.

create table pet9(
    id int,
    name varchar(20),
    age int(10) default 10
);
- get
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | 10      |       |
+-------+-------------+------+-----+---------+-------+
-- Insert data
insert into pet9 (id,name) values(1.'the elephants');
- get
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 |The elephant|   10 |
+------+--------+------+
Copy the code

Obviously, values that are not passed in will automatically be filled in with default values if default constraints are set.

Foreign key constraints

The outer constraint requires the use of two tables, a master table and a side table.

First, let’s create two tables, a pet table pet10, and an animal classification table pet_type.

- create pet_type
create table pet_type(
    id int primary key,
    name varchar(20));- create pet10
create table pet10(
    id int primary key,
    name varchar(20),
    pet_type_id int.foreign key(pet_type_id) references pet_type(id)
);
Copy the code

Next, we insert data into the PET_TYPE table.

insert into pet_type values(1, "dog");insert into pet_type values(2"Cat");- get
+----+--------+
| id | name   |
+----+--------+
|  1 |dog|
|  2 |The cat|
+----+--------+
Copy the code

We then proceed to insert data into the PET10 table as follows.

insert into pet10 values(1, "dog",1);
insert into pet10 values(2"Cat",2);
- get
+----+--------+-------------+
| id | name   | pet_type_id |
+----+--------+-------------+
|  1 |dog|           1 |
|  2 |The cat cat|           2 |
+----+--------+-------------+
Copy the code

We have both tables created. Obviously, the PET_TYPE_ID field in the pet10 table is associated with the ID in the PET_TYPE table.

Next, let’s insert data from the secondary table pet10 that is not in the primary table pet_type.

insert into pet10 values(3, "a rat",3);
- an error
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`pet10`, CONSTRAINT `pet10_ibfk_1` FOREIGN KEY (`pet_type_id`) REFERENCES `pet_type` (`id`))
Copy the code

Then, we delete the field in the main table with id=2.

delete from pet_type where id=2;
- an error
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`pet10`, CONSTRAINT `pet10_ibfk_1` FOREIGN KEY (`pet_type_id`) REFERENCES `pet_type` (`id`))
Copy the code

Obviously, it can’t be deleted.

Note: Data that is not in the main table cannot be used in the side table. Data in the main table is referenced by data in the side table and cannot be deleted from the main table.

Build table paradigm

Briefly learned the constraints for creating tables. Then we can think about designing the business table. We have three major paradigms when designing a table.

The first paradigm

All fields in the data table are indivisible atomic values; This means that each field is made as small as possible until it is indivisible. For example, “Hangzhou City, Zhejiang Province, China” can be broken down to “China”, “Zhejiang Province”, and “Hangzhou City”.

create table student(
    id int primary key,
    name varchar(10),
    country varchar(20),
    privence varchar(20),
    city varchar(20));Copy the code

The more detailed the design is not the better, according to the actual demand design.

The second paradigm

In the first normal form. The second normal form requires that every column except the primary key must be completely dependent on the primary key. Incomplete dependencies, if they occur, can only occur if the primary key is joined.

create table order_list(
    product_id int,
    customer_id int,
    product_name varchar(20),
    customer_name varchar(20),
    primary key(product_id,customer_id)
);
Copy the code

The design of the table above is a bit bloated. At this time we need to dismantle the table

- orders table
create table order_list(
    order_id int primary key, 
    product_id int,
    customer_id int
);
- the product table
create table product(
    id int primary key, 
    name archer(20));- the user table
create table customer(
    id int primary key, 
    name archer(20));Copy the code

In this way, the second normal form is satisfied, and splitting the table can better design the database.

The third paradigm

You have to satisfy the second normal form first. There cannot be passed dependencies between columns other than the primary key column.

create table order_list(
    order_id int primary key, 
    product_id int,
    customer_id int,
    customer_phone int
);
Copy the code

Obviously, with this design, customer_Phone can be found based on customer_ID. We can put customer_Phone in the table customer.

create table customer(
    id int primary key, 
    name archer(20),
    phone int(10));Copy the code

Above, we will briefly introduce the design of the table in MySQL, how to design the table, need their own practice and demand specific requirements to think about.

Chapter 4 SQL language

Introduction to SQL

Structured Query Languag. SQL is a Structured Query language suitable for managing relational database management systems. It can let us operate the database conveniently, as well as the data “add, delete, change and check” and other operations. Most databases support THE SQL language, and each has its own extension, but there is no difference. The next demo will be based on MySQL.

Second, SQL language classification

1. Data Query Language (DQL) Data Query Language, such as SELECT and FROM
2. DMLDML Data Manipulate Language, such as INSERT, UPDATE, and DELETE
3. Data Define Languge (DDL) A Data definition language, such as CREATE and FROP, that is used to create objects in the database —– Tables and views
4. Data Control Language (DCL) Data Control Language, such as Grant and COMMIT

These four languages together make up the SQL language.

Manage databases and tables

First, we need to start the MySQL database server

mysql -u root -p123456
-- 123456 is your database password
Copy the code

Let’s start with a few database commands:

---

1. Database management

We need to know how to look at the database and how to use the database and look at the tables in the database. How to View the Database

Show all databases.
Show databases;
- get
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
Copy the code

Using a database

Use the library.Copy the code

View all tables for the current library

show tables;
show tables fromThe library;Copy the code

2. Library creation and deletion

Manage database:

-- 1. Create library test
create database test;
-- 2. Delete library tset
drop database test;
Copy the code

Management Data sheet:

Let’s start with how do we create a table

Create a user table with name and age
create table user(
    id int,
    name varchar(20),
    age int
);
--2. View the description of the user table
desc user;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
Copy the code

Do not create a table with the same name. If not exists, no error is reported when a table with the same name is created, but the table is not created successfully.

create table if not exists user(     
    id int,     
    name varchar(20),
    age int(200));The table is still created.
desc user; 
Copy the code

Next, let’s learn how to modify the User table.

---

(1) how to modify the table name ✅

-- 1. How do I change the table name
alter table user rename students;
Results -
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| students       |
+----------------+
-- The following statement is also acceptable:
alter table user rename to students;
Copy the code

(2) How to add the field ✅

-- 2. Add fields
alter table students add phone int(20); 
-- Can be followed by first to indicate insertion in the first column, or after to indicate insertion after a column
alter table students add pwd int(20) first;
alter table students add city varchar(10) after age;
-- can get
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pwd   | int         | YES  |     | NULL    |       |
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| city  | varchar(10) | YES  |     | NULL    |       |
| phone | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

We can also declare that the field is not null by following the statement with not NULL.
-- Column can be omitted to indicate that the column is being modified.
alter table students add column phone int(20); 
-- Column omission will get a warning, but that's ok.
-- add Supports batch adding
alter table students add (phone int(20), pwd int(20)); 
Copy the code

(3) Delete the field ✅

-- 3. Delete fields
alter table students drop pwd;
alter table students drop column phone;

Copy the code

(4) Change the field type to ✅

-- 4. Change the field type
alter table students modify column city varchar(20);
alter table students modify column age varchar(10);
alter table students modify id int primary key;
You can see that the data type of our city and age fields has been changed and id has been set as the primary key
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | varchar(10) | YES  |     | NULL    |       |
| city  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
Copy the code

(5) Change the column name ✅

-- 5. Change the column name
alter table students change column age sex varchar(10);
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
| city  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
Copy the code

3. Add, delete and modify the data

How to add or delete data

---

The SQL heavy DML language is used here.

(1) Insert data ✅

Insert by insert into method.

-- 1. Insert data
insert into students values(1," Super brother "," male "," Hangzhou ");You can see that we have inserted a piece of data into the students table
+----+--------+------+--------+
| id | name   | sex  | city   |
+----+--------+------+--------+
|  1 |Super brother| 男   |hangzhou|
+----+--------+------+--------+

-- Can batch insert
insert into students values(2"Meng Yang "," male ","null"),(3," Mengjia "," female "," Mountain Gully ");+----+--------+------+-----------+
| id | name   | sex  | city      |
+----+--------+------+-----------+
|  1 |Super brother| 男   |hangzhou|
|  2 |Meng Yang| 男   | null      |
|  3 |Good dream| 女   |Gully channel|

-- You can insert only some fields, and the rest of the fields will take their default values
insert into students (id,name) values(4, "Ed");+----+--------+------+-----------+
| id | name   | sex  | city      |
+----+--------+------+-----------+
|  1 |Super brother| 男   |hangzhou|
|  2 |Meng Yang| 男   | null      |
|  3 |Good dream| 女   |Gully channel|
|  4 |Ed| NULL | NULL      |
+----+--------+------+-----------+
Copy the code

Note the following when inserting data:

• The field type is consistent with the value and corresponds to one to one.
• The default value is NULL and no insertion is allowed.
• The number of fields is the same as the number of inserted values.
• Omit the fields. The values after values correspond to the fields in the table in the correct order.

(2) Modify the data ✅

Modify table data using the update method.

-- 2. Modify data
update students set city='hangzhou' where id=3;
+----+--------+------+--------+
| id | name   | sex  | city   |
+----+--------+------+--------+
|  1 |Super brother| 男   |hangzhou|
|  2 |Meng Yang| 男   | null   |
|  3 |Good dream| 女   |hangzhou|
|  4 |Ed| NULL | NULL   |
+----+--------+------+--------+
-- Can support multiple field modification and multiple conditions.
You can also modify data with an order by and limit.
update students set city='Mountain Gully' where id=3 order by id asc limit 1;
--order by id asc = id; Limit 1 Modifies only one row.
- desc reverse.
Copy the code

(3) Delete data ✅

Deletes data using DELETE and TRUNCate.

-- 3. Delete data
delete from students where id=4;
Results -
+----+--------+------+-----------+
| id | name   | sex  | city      |
+----+--------+------+-----------+
|  1 |Super brother| 男   |hangzhou|
|  2 |Meng Yang| 男   | null      |
|  3 |Good dream| 女   |Gully channel|
+----+--------+------+-----------+
You can also specify order by and limit the number of rows to be deleted
delete from students where id=4 order by id desc limit 1;
If you have the same ID, you can select a different delete condition

Truncate is another deletion method
truncate table students;
Delete all records from table
delete from students;
-- delete deletes all records in a table.
Copy the code

Note when using delete and TRUNCate:

• Use the delete operation and its delete condition properly
• Truncate delete cannot have A WHERE condition
• Truncate is faster than the delete
• Delete can also delete all records in a table

4, a variety of query methods

We use DQL language to query data. Query is an important part of database operation. A good query method can improve the efficiency of obtaining data. There are many ways to query:

• Common query
• Conditions of the query
• Fuzzy query
• Sorting query
• Grouping query
• Multi-table linked query
• The subquery
• Paging query
• The joint query
• Function queries
• .

Let’s take a closer look at how each query works.

---

(1) General query: ✅

-- 1. General query
select id, name from students;
Results -
+----+--------+
| id | name   |
+----+--------+
|  1 |Super brother|
|  2 |Meng Yang|
|  3 |Good dream|
-- Query all records
select * from students;
Note: mysql does not support select into
create temporary table students2(select * from students);
Copy the code

Select * from table; select * from table;

(2) Search for ✅

Use the where keyword and some operators and logical operators condition operator “> < =! = < > > = < =” logical operators “&& | |!” As with “and or not”, the symbol is the same as the keyword

-- 2. Conditional query
select * from students where id=2;
- get
+----+--------+------+------+
| id | name   | sex  | city |
+----+--------+------+------+
|  2 |Meng Yang| 男   | null |
+----+--------+------+------+

select * from students where id=2 or name="Good dream";- get
+----+--------+------+-----------+
| id | name   | sex  | city      |
+----+--------+------+-----------+
|  2 |Meng Yang| 男   | null      |
|  3 |Good dream| 女   |Gully channel|
+----+--------+------+-----------+
Copy the code

(3) Fuzzy query ✅

The keywords of fuzzy query include “like, BETWEEN and, in, is NULL, is not NULL” and some SQL functions.

-- 3. Fuzzy query
-- Fuzzy query keywords include like, BETWEEN and, in, is NULL, is not NULL, and some SQL functions
-- (1)
-- like is usually used with wildcards.
-- % indicates any character, and _ indicates a single character
-- '%a' data ending in a
-- 'a%' starts with a
-- '%a%' contains data for a
-- '_a_' with a middle letter a
-- two digits' _A 'ending in a
-- two digits' _A 'ending in a
-- 'a_' with two digits and beginning with an a

select * from students where name like '% % better';
- get
+----+--------+------+-----------+
| id | name   | sex  | city      |
+----+--------+------+-----------+
|  3 |Good dream| 女   |Gully channel|
+----+--------+------+-----------+
select * from students where name like '_ the yankees';
- get
+----+--------+------+------+
| id | name   | sex  | city |
+----+--------+------+------+
|  2 |Meng Yang| 男   | null |
+----+--------+------+------+

-- (2) between and
select * from students where id between 2 and 5;
- get
+----+--------+------+-----------+
| id | name   | sex  | city      |
+----+--------+------+-----------+
|  2 |Meng Yang| 男   | null      |
|  3 |Good dream| 女   |Gully channel|
+----+--------+------+-----------+

-- (3)
select * from students where id in (1.2.5.8.9);
- get
+----+--------+------+--------+
| id | name   | sex  | city   |
+----+--------+------+--------+
|  1 |Super brother| 男   |hangzhou|
|  2 |Meng Yang| 男   | null   |
+----+--------+------+--------+

Use the rlike keyword to use regular expressions
select * from students where name rlike '^ dream';
-- Get one that starts with a dream
+----+--------+------+-----------+
| id | name   | sex  | city      |
+----+--------+------+-----------+
|  3 |Good dream| 女   |Gully channel|
+----+--------+------+-----------+

-- (5), use some functions instr(STR,substr), locate(substr, STR), position(substr in STR), etc.;
select * from students whereInstr (name, "good")>0;
select * from students whereLocate (" good ", name)>0;
select * from students where position(" good"in name)>0;
-- You get the same result
+----+--------+------+-----------+
| id | name   | sex  | city      |
+----+--------+------+-----------+
|  3 |Good dream| 女   |Gully channel|
+----+--------+------+-----------+

Is not NULL is not NULL is not NULL is not null is not null is not null
Copy the code

(4) Sort query ✅

Use “Order by” to sort the data.

-- 4, sort the query
-- We also touched on sorting order by earlier
For convenience, insert the field age in the previous table, students
alter table students add age int(20); 
update students set age=20 where id=1;
update students set age=10 where id=2;
update students set age=18 where id=3;

-- Then we query the data
select * from students  order by age asc;
- get
+----+--------+------+-----------+------+
| id | name   | sex  | city      | age  |
+----+--------+------+-----------+------+
|  2 |Meng Yang| 男   | null      |   10 |
|  3 |Good dream| 女   |Gully channel|   18 |
|  1 |Super brother| 男   |hangzhou|   20 |
+----+--------+------+-----------+------+

select * from students where age > 0 order by age desc, id asc;
- get
+----+--------+------+-----------+------+
| id | name   | sex  | city      | age  |
+----+--------+------+-----------+------+
|  1 |Super brother| 男   |hangzhou|   20 |
|  3 |Good dream| 女   |Gully channel|   18 |
|  2 |Meng Yang| 男   | null      |   10 |
+----+--------+------+-----------+------+

select * from students where age > 0 order by age desc, id asc limit 1;
- get
+----+--------+------+--------+------+
| id | name   | sex  | city   | age  |
+----+--------+------+--------+------+
|  1 |Super brother| 男   |hangzhou|   20 |
+----+--------+------+--------+------+

We can get as much data as we need by sorting.
We can also use the limit keyword to limit the length of the query.
Copy the code

(5) Group query ✅

Use ‘group by’ to group data.

-- 5. Group query
-- (1) group by
select sex from students group by sex;
- get
+------+
| sex  |
+------+
| 男   |
| 女   |
+------+
-- When we want to query multiple fields in a group, for example:
select name,sex from students group by sex;
The database will report an error
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column.
-- When we group sex, there are only "male" and "female", so it's hard for the database to know which name is
"Only_full_group_by" is automatically enabled after mysql5.7.
-- I won't go into the relevant method here. There's a lot on the Internet.
-- Generally we query by what group which column is ok

How do we query the data after we divide it into groups?
select sex, group_concat(name) from students group by sex;
- get
+------+--------------------+
| sex  | group_concat(name) |
+------+--------------------+
| 女   |Good dream|
| 男   |Chao ge, Meng Yang|
+------+--------------------+
-- As we can see, there is only one "mengjia" for "female" and two for "male". The query is correct.

-- (2) Group by uses aggregate functions
-- Here's a brief introduction to count(), which will be covered in more detail in the Functions section.
select sex, group_concat(name),count(name) from students group by sex;
- get
+------+--------------------+----------+
| sex  | group_concat(name) | count(*) |
+------+--------------------+----------+
| 女   |Good dream|        1 |
| 男   |Chao ge, Meng Yang|        2 |
+------+--------------------+----------+
The count(name) function counts the number of men and women.

-- (3)
Let's add one more piece of data to the example above
insert into students values(4"Ed "," male "," Zhengzhou ",18);
Having has a similar effect to where, but having can only be used for group by
Select * from age > 10 where age > 10
select age,group_concat(name) as n_name,group_concat(age) as n_age,count(age) as age_num from students group by age having age > 10;
- get
+------+---------------+-------+---------+
| age  | n_name        | n_age | age_num |
+------+---------------+-------+---------+
|   18 |Mengjia, Ed| 18.18 |       2 |
|   20 |Super brother| 20    |       1 |
+------+---------------+-------+---------+
-- Divided by age, two 18-year-olds and one 20-year-old
- You can filter the statistical fields after grouping. For example, the number of statistical fields in each age group is greater than 1
select age,group_concat(name) as n_name,group_concat(age) as n_age,count(age) as age_num from students group by age having  age_num > 1;
- get
+------+---------------+-------+---------+
| age  | n_name        | n_age | age_num |
+------+---------------+-------+---------+
|   18 |Mengjia, Ed| 18.18 |       2 |
+------+---------------+-------+---------+

Copy the code

What’s the difference between “where” and “having”?

Where, having, having, having, having, having, having, having, having, having

(6) Multi-table link query ✅

Before we learn how to query multiple tables, let’s go ahead and create two tables, grades and Jobs. Remember how we created the table and inserted the data?

Create the Grades table
create table grades(id int, grade varchar(10));
Insert data into grades
insert into grades values(1First grade2"Grade two"3"Third grade ");- get
+------+-----------+
| id   | grade     |
+------+-----------+
|    1 |In grade one|
|    2 |Second grade|
|    3 |The third grade|
+------+-----------+

Create table jobs
create table jobs(id int, job varchar(10));
Insert data into grades
insert into jobs values(1, "monitor"),2Commissary in charge of Studies3Sports commissar4"Mathematics class representative ");- get
+------+-----------------+
| id   | job             |
+------+-----------------+
|    1 |Monitor of the class|
|    2 |Learning committee member|
|    3 |Athletics member of committee|
|    4 |Mathematics representative|
+------+-----------------+
Let's move on to how to do multiple table queries.
Copy the code

Cross join (Cartesian product)

What is a Cartesian product?

We learned in high school math that it’s just a permutation or combination of two sets.

Assuming that set A = {A, b}, set b = {0, 1}, the two sets of cartesian product for {(A, 0), (A, 1), (b, 0), (b, 1)}.

In SQL, cartesian products are often used to link multiple tables.

-- Cross connection
select * from students, grades;
select * from students cross join grades;
-- These two queries give the same result.
+----+--------+------+-----------+------+------+-----------+
| id | name   | sex  | city      | age  | id   | grade     |
+----+--------+------+-----------+------+------+-----------+
|  1 |Super brother| 男   |hangzhou|   20 |    3 |The third grade|
|  1 |Super brother| 男   |hangzhou|   20 |    2 |Second grade|
|  1 |Super brother| 男   |hangzhou|   20 |    1 |In grade one|
|  2 |Meng Yang| 男   | null      |   10 |    3 |The third grade|
|  2 |Meng Yang| 男   | null      |   10 |    2 |Second grade|
|  2 |Meng Yang| 男   | null      |   10 |    1 |In grade one|
|  3 |Good dream| 女   |Gully channel|   18 |    3 |The third grade|
|  3 |Good dream| 女   |Gully channel|   18 |    2 |Second grade|
|  3 |Good dream| 女   |Gully channel|   18 |    1 |In grade one|
|  4 |Ed| 男   |zhengzhou|   18 |    3 |The third grade|
|  4 |Ed| 男   |zhengzhou|   18 |    2 |Second grade|
|  4 |Ed| 男   |zhengzhou|   18 |    1 |In grade one|
+----+--------+------+-----------+------+------+-----------+
select * from grades cross join students;
-- This gives the same result, just in a different order.
Copy the code

This is our cross-join query, isn’t it simple? Of course, queries can also be brought in conditions, groups, and so on. That’s consistent with what we said before.

In the connection

This is the most common and commonly used table join, but only matching rows are queried.

Let’s first look at displaying internal links, using the join on keyword.

Here, we need to add a field grade_ID to the previous students table to indicate the grade ID.

-- Add a column grade_id
alter table students add grade_id int;
-- Insert data
-- Grade_id is the same as grade table ID
update students set grade_id=2 where id=1;
update students set grade_id=1 where id=2;
update students set grade_id=3 where id=3;
update students set grade_id=2 where id=4;
- get
+----+--------+------+-----------+------+----------+
| id | name   | sex  | city      | age  | grade_id |
+----+--------+------+-----------+------+----------+
|  1 |Super brother| 男   |hangzhou|   20 |        2 |
|  2 |Meng Yang| 男   | null      |   10 |        1 |
|  3 |Good dream| 女   |Gully channel|   18 |        3 |
|  4 |Ed| 男   |zhengzhou|   18 |        2 |
+----+--------+------+-----------+------+----------+
Copy the code

We correspond each person’s grade by associating students’ grade_ID with the ID field in the Grades table. It is also common to associate primary keys with foreign keys. We’ll talk about that later.

-- Query the grade of each person
select * from students join grades on students.grade_id = grades.id;
- get
+----+--------+------+-----------+------+----------+------+-----------+
| id | name   | sex  | city      | age  | grade_id | id   | grade     |
+----+--------+------+-----------+------+----------+------+-----------+
|  1 |Super brother| 男   |hangzhou|   20 |        2 |    2 |Second grade|
|  2 |Meng Yang| 男   | null      |   10 |        1 |    1 |In grade one|
|  3 |Good dream| 女   |Gully channel|   18 |        3 |    3 |The third grade|
|  4 |Ed| 男   |zhengzhou|   18 |        2 |    2 |Second grade|
+----+--------+------+-----------+------+----------+------+-----------+
Copy the code

Within the inner link, we can also use implicit linking. Use the WHERE field.

-- Use where implicit joins
select * from students, grades where students.grade_id = grades.id;
-- The result is the same as above
+----+--------+------+-----------+------+----------+------+-----------+
| id | name   | sex  | city      | age  | grade_id | id   | grade     |
+----+--------+------+-----------+------+----------+------+-----------+
|  1 |Super brother| 男   |hangzhou|   20 |        2 |    2 |Second grade|
|  2 |Meng Yang| 男   | null      |   10 |        1 |    1 |In grade one|
|  3 |Good dream| 女   |Gully channel|   18 |        3 |    3 |The third grade|
|  4 |Ed| 男   |zhengzhou|   18 |        2 |    2 |Second grade|
+----+--------+------+-----------+------+----------+------+-----------+
Copy the code

Outer join

The outer join can actually be regarded as an extension of the inner join, including left and right join, all outer join. In mysql, the method of full join is not full join.

• The left outer join

That is, all records in the left table are displayed first. The second table contains only rows that match the first table and is null. Let’s insert a row into the students table first.

Insert data first
insert into students values(5"The Sun god "," the woman ", "The Arahan ",88.4); 
-- Use the left outer join query
select * from students left join grades on students.grade_id = grades.id;
- get
+----+--------+------+-----------+------+----------+------+-----------+
| id | name   | sex  | city      | age  | grade_id | id   | grade     |
+----+--------+------+-----------+------+----------+------+-----------+
|  1 |Super brother| 男   |hangzhou|   20 |        2 |    2 |Second grade|
|  2 |Meng Yang| 男   | null      |   10 |        1 |    1 |In grade one|
|  3 |Good dream| 女   |Gully channel|   18 |        3 |    3 |The third grade|
|  4 |Ed| 男   |zhengzhou|   18 |        2 |    2 |Second grade|
|  5 |Xu god| 女   |We rarely|   88 |        4 | NULL | NULL      |
+----+--------+------+-----------+------+----------+------+-----------+
Copy the code

Select * from grade where id = 4; select * from grade where id = 4; If you use an inner join query, the row in the students table with id=5 is lost.

• Right connection

That is, all records in the right table are preferentially displayed. The second table contains only rows that match the first table, null.

-- Use the outer right join query
select * from students right join grades on students.grade_id = grades.id;
- get
+------+--------+------+-----------+------+----------+------+-----------+
| id   | name   | sex  | city      | age  | grade_id | id   | grade     |
+------+--------+------+-----------+------+----------+------+-----------+
|    2 |Meng Yang| 男   | null      |   10 |        1 |    1 |In grade one|
|    4 |Ed| 男   |zhengzhou|   18 |        2 |    2 |Second grade|
|    1 |Super brother| 男   |hangzhou|   20 |        2 |    2 |Second grade|
|    3 |Good dream| 女   |Gully channel|   18 |        3 |    3 |The third grade|
+------+--------+------+-----------+------+----------+------+-----------+
Copy the code

It can be clearly seen that the data record with id=5 in students table is not queried.

• Full outer join

You can completely display all records for both tables. In mysql, you can only use union to join the left outer join and the right outer join to achieve full outer join.

-- Implement full external connection
select * from students left join grades on students.grade_id = grades.id union select * from students right join grades on students.grade_id = grades.id;
- get
+------+--------+------+-----------+------+----------+------+-----------+
| id   | name   | sex  | city      | age  | grade_id | id   | grade     |
+------+--------+------+-----------+------+----------+------+-----------+
|    1 |Super brother| 男   |hangzhou|   20 |        2 |    2 |Second grade|
|    2 |Meng Yang| 男   | null      |   10 |        1 |    1 |In grade one|
|    3 |Good dream| 女   |Gully channel|   18 |        3 |    3 |The third grade|
|    4 |Ed| 男   |zhengzhou|   18 |        2 |    2 |Second grade|
|    5 |Xu god| 女   |We rarely|   88 |        4 | NULL | NULL      |
+------+--------+------+-----------+------+----------+------+-----------+
Copy the code

Note that the union all keyword displays all records; the union deletes the same records.

(7), subquery ✅

A subquery is a query that uses the result of one query statement as a condition for another.

When the child temporary table has only one field
select * from students where grade_id = (select id from grades where id=1);
- get
+----+--------+------+------+------+----------+
| id | name   | sex  | city | age  | grade_id |
+----+--------+------+------+------+----------+
|  2 |Meng Yang| 男   | null |   10 |        1 |
+----+--------+------+------+------+----------+

-- When there are multiple fields in a child temporary table
select * from students where grade_id in (select id from grades where id>1);
- get
+----+--------+------+-----------+------+----------+
| id | name   | sex  | city      | age  | grade_id |
+----+--------+------+-----------+------+----------+
|  1 |Super brother| 男   |hangzhou|   20 |        2 |
|  3 |Good dream| 女   |Gully channel|   18 |        3 |
|  4 |Ed| 男   |zhengzhou|   18 |        2 |
+----+--------+------+-----------+------+----------+
Copy the code

As you can see, the subquery actually requires two selectors, so it’s inefficient. Normally we use connection queries. We can use keywords such as IN, Not in, ANY, ALL, EXISTS, and Not EXISTS to determine conditions.

The select statement for a subquery must be in parentheses. Using exits, the inner query does not return the query record. It returns true and false instead. True: The query can continue. The same applies to not EXISTS.

(8) Query ✅

Paging is a concept that those of you who have done web pages are certainly familiar with, so when we have a huge number of pages, we have to think about paging the data. The use in the database is as simple as using the LIMIT field for paging. The limit is followed by two arguments, the first is the number of lines to start with and the second is the number of lines to display per page.

-- Page 1, two on each page;
select * from students limit 0.2;
- get
+----+--------+------+--------+------+----------+
| id | name   | sex  | city   | age  | grade_id |
+----+--------+------+--------+------+----------+
|  1 |Super brother| 男   |hangzhou|   20 |        2 |
|  2 |Meng Yang| 男   | null   |   10 |        1 |
+----+--------+------+--------+------+----------+
-- Page 2, two on each page;
select * from students limit 2.2;
- get
+----+--------+------+-----------+------+----------+
| id | name   | sex  | city      | age  | grade_id |
+----+--------+------+-----------+------+----------+
|  3 |Good dream| 女   |Gully channel|   18 |        3 |
|  4 |Ed| 男   |zhengzhou|   18 |        2 |
+----+--------+------+-----------+------+----------+

Copy the code

We can find a pattern, when we know page A, each page b, we can return the corresponding data.

select * from students limit (a- 1)*b, b;
Copy the code

It’s very simple to implement paging. At the same time, we can also use where, order by and other keywords in the query for combination query.

Note: In large data volumes, we need to use primary key constraints or unique indexes for paging. At the same time, for example, when we do the ranking, we need to rank the data according to certain rules.

(9) Joint query ✅

A federated query returns the same data from two tables superimposed. Let’s look at the following example:

select * from students union select * from grades;
- an error
ERROR 1222 (21000): The used SELECT statements have a different number of columns
Copy the code

We need two or more fields to be the same to use a federated query. First we create another PET table;

- create a pet
create table pet (id int, name varchar(10),age int(10),owner varchar(20));
insert into pet values(1, "dog",2", "super brother ");insert into pet values(2"Cat",3, "mengjia ");- get
+------+--------+------+--------+
| id   | name   | age  | owner  |
+------+--------+------+--------+
|    1 |dog|    2 |Super brother|
|    2 |The cat cat|    3 |Good dream|
+------+--------+------+--------+
Copy the code

Next we query the students table and pet table jointly.

-- Query the age of people and pets
select name,age from students union select name,age from pet;
- get
+--------+------+
| name   | age  |
+--------+------+
|Super brother|   20 |
|Meng Yang|   10 |
|Good dream|   18 |
|Ed|   18 |
|Xu god|   88 |
|dog|    2 |
|The cat cat|    3 |
+--------+------+
Will the following queries be successful?
select name,age from students union select name from pet;
- an error
select name from students union select name from pet;
Success -
select name from students union select age from pet;
Success -
+--------+
| name   |
+--------+
|Super brother|
|Meng Yang|
|Good dream|
|Ed|
|Xu god|
| 2      |
| 3      |
+--------+
Copy the code

Note: All single query results should have the same number of columns. The column names of the first query are predominant. The union query uses union. The default value is UNION DISTINCT. Use union ALL to query all records, which can be repeated.

(10), function query ✅7

Use aggregate functions for data query.

- use the count ()
select count(*) from students;
- get
+----------+
| count(*) |
+----------+
|        5 |
+----------+
- the sum ()
select sum(age) as age_sum from students;
- get
+---------+
| age_sum |
+---------+
|     154 |
+---------+
Copy the code

Instead of listing the specific functions, we can use SQL functions to calculate and query the data we need.

At this point, our query methods are basically finished, and many kinds of query methods can be combined with each other.

Specific needs specific analysis, to be very skilled use of SQL queries only through a lot of practice and actual combat.

---

It’s too long. I’ll see you in the next one.

“New Generation of migrant workers” before the end of the engineer must be the database -MySQL(2)

This article is shallow, please don’t hesitate your comments and likes ~ note: this article is the author’s painstaking work, reprint must declare