Secular wanderer: a programmer who focuses on technical research

Say what I said before

Table management is introduced in front, so this section we based on the table talked about in the last section to do data operations, the same, is no more than C(create)R(read)U(modify)D(delete) four operations, but in R can always play a variety of tricks

Before we start, let’s talk about their keywords:

INSERT
SELECT
UPDATE
DELETE

You can use the help command to view the syntax

Formal presentation

Let’s take a look at the previous table structure

create table if not exists tb_user(
    id bigint primary key auto_increment comment 'primary key',
    login_name varchar(48) comment 'Login Account',
    login_pwd char(36) comment 'Login password'.account decimal(20.8) comment 'Account balance',
    login_ip int comment 'the login IP'
) charset=utf8mb4 engine=InnoDB comment 'User table';
Copy the code

Insert data

Before we insert it, how do we use it in general

insert into table_name[(column_name[,column_name] ...)] value|values (value_list) [, (value_list)]
Copy the code

To throw one’s weight around to throw one’s weight around to throw one’s weight around

Insert single data for all fields

insert into tb_user value(1.'admiun'.'abc123456'.2000.inet_aton('127.0.0.1'));
Copy the code

This inserts a piece of data:

auto_increment: auto-increment key. You can insert data without specifying data for the current column, and by default we recommend auto-increment for primary keys
inet_aton: IP translation function, and the correspondinginet_ntoa()

It is also important to note that if the same primary key exists, there will be an error when inserting it

The primary key has been duplicated
Duplicate entry '4' for key 'tb_user.PRIMARY'
Copy the code

Insert more data into a specified field

insert into tb_user(login_name, login_pwd) values('admin1'.'abc123456'), ('admin2'.'abc123456')
Copy the code

You can see that the data has been inserted, and columns that are not filled with data have been NULL filled. In this regard, we can specify the DEFAULT value with DEFAULT when creating the table, which is used at this point

alter table tb_user add column email varchar(50) default '[email protected]' comment 'email'
Copy the code

What’s more convincing than getting your hands dirty

ON DUPLICATE KEY UPDATE

Here is another point that is not used very much, but is quite useful: ON DUPLICATE KEY UPDATE

If there are duplicate primary keys in the table, then update the table.

insert into tb_user(id, login_name, email) value(4.'test'.'[email protected]') on duplicate key update login_name = values(login_name), email = values(email);
Copy the code

Comparing the data above, it’s easy to see that the numbers are different

Values (column name): Fetches the data of the previously inserted field

insert into tb_user(id, login_name, email) values(4.'test'.'[email protected]'), (5.'test5'.'test5@sinacom') on duplicate key update login_name = values(login_name), email = values(email);
Copy the code

Insert a number of data is also the same, not texture, we do it yourself try

Modify the data

Inserting data is relatively simple, so let’s look at modifying data

First, the update syntax is simpler:

update table_name set column_name=value_list (,column_name=value_list) where condition
Copy the code

Here’s an example:

update tb_user set login_name = '[email protected]' where id = 1
Copy the code

This modifies the data for loign_name numbered 1 under tb_user

Where conditions can also be divided according to

Of course, if the query criteria are not set, the default is to modify the entire table data

update tb_user set login_name = '[email protected]'.account = 2000
Copy the code

Okay, so that’s the end of modifying the data, very simple

Delete the data

Deleted data is divided into

Delete specified data
Clear the entire table

If you just want to delete some data, you can delete it by using delete.

delete from tb_user where login_ip is null;
Copy the code

This removes the data for the specified condition

So what if we execute the delete condition, but don’t set the condition? Let’s take a look

Start with an INSERT operation to insert a few pieces of data

delete from tb_user ;
Copy the code

As you can see, all data is deleted

However, in fact, there is another way to clear the entire table, that is, the truncate method, which is more efficient

truncate tb_user;
Copy the code

Finally, no stickers, no problem

Query data

Query data can be divided into a variety of situations, combined use can exist in N, so this is the most complex way, we will introduce one by one

In fact, if you look at the syntax: query syntax key points will only contain the following:

SELECT 
	[DISTINCT] select_expr [, select_expr] 
FROM table_name 
WHERE where_condition
GROUP BY col_name
HAVING where_condition
ORDER BY col_name ASC | DESC
LIMIT offset[, row_count]
Copy the code

With these key points in mind, the query is fairly simple, so let’s look at a simple operation first

A simple query

select * from tb_user;

Sort by the specified field asC: positive desc: reverse order
select * from tb_user order by id desc;
Copy the code

A total of 44 pieces of data were inserted, not all screenshots

Select * from table_name where table (select * from table_name); select * from table_name where table (select * from table_name);

select id, login_name, login_pwd from tb_user;
Copy the code

It’s that simple

Of course, remember this keyword: DISTINCT, let’s try this out:

select distinct login_name from tb_user;
Copy the code

The meaning is obvious, yes, to redo.

However, the distinct keyword will only work if it is repeated in a combination of multiple fields. For example:

select distinct id,login_name from tb_user;
Copy the code

This parameter takes effect only when id + login_name is the same

Aggregation function

An aggregation function built into MySQL that evaluates a set of data and returns a single value

We can add the WHERE condition

-- Queries the number of entries in the current table
select count(*) from tb_user;

Query the largest column in the current table
select max(id) from tb_user;
Query the smallest column in the current table
select min(id) from tb_user;

-- Queries the average value of the specified column in the current table
select avg(account) from tb_user;

Query the total of the specified columns in the current table
select sum(account) from tb_user;
Copy the code

In addition to the aggregate function, also contains a lot of ordinary functions, here is not a list, give the official documentation, when using the specific check

Conditions of the query

If you see the first example, it’s not that hard to query. In each of the above examples, we need to add some criteria to filter the data, using our WHERE statement, remember one thing:

There can be more than one conditional filter

Equivalent query

We can make a conditional judgment in the following way

select * from tb_user where login_name = 'admin1' and login_pwd = 'abc123456';
Copy the code

In most cases, column_name = column_value is the more common query method, which is called equivalent query,

In addition, I noticed that BEFORE the condition, I used and to make the association. Those who are familiar with Java must remember that &&, both mean and

If either of these two things satisfy one of them, then the opposite must be or

select * from tb_user where login_name = 'admin1' or login_pwd = 'abc123456';
Copy the code

There are more ways than just = matching, <, <=, >, >=

Contrary to what we know:<>Not equal to

But they’re all used the same way

Batch query

In certain cases, if you want to query a batch of data, you can use in to query

select * from tb_user where id in(1.2.3.4.5.6);
Copy the code

In is equivalent to passing in a collection and then querying the data for the specified collection. In many cases, this SQL statement could be written as follows

select * from tb_user where id in (
	select id from tb_user where login_name = 'admin1'
);
Copy the code

Instead of in, there is not in: the query does not contain the specified data

Fuzzy query

Having looked at the equivalent query, let’s look at a fuzzy query:

As long as the field data contains the query data, the data can be matched

select * from tb_user where login_name like '%admin%';
select * from tb_user where login_name like '%admin';
select * from tb_user where login_name like 'admin%';
Copy the code

Like is the key member of our fuzzy query, and the following query keywords can be divided into three situations:

%admin% : % with the query keyword indicates that as long as the data containsadminYou can match it
%admin: Any keyword that starts with the end of admin will be matched
Admin % : must start with admin, anything else is optional, so that the data will match

This approach is more recommended because if the query column is indexed, the other approach will invalidate the index

Judge not empty

Query the current table and find that some columns in the data are NULL. If we want to filter out these columns during the query, we can do this:

select * from tb_user where account is not null;
select * from tb_user where account is null;
Copy the code

Is not null is the key point, as opposed to is null, which means the opposite

Time to determine

In many cases, if we want to match queries by time range, we can do this:

Table tb_user does not have a time field; instead, add a field create_time

select * from tb_user where create_time between '2021-04-01 00:00:00' and now(a);Copy the code

The **now()** function represents the current time

Between indicates the start time and and indicates the end time

Transfer line column

Let me talk about this query from an interview question:

When I was interviewed for the first time in 14 years, I met such interview questions, in fact, MY heart is broken

The scenario is the same, but the SQL is different.

create table test(
   id int(10) primary key.type int(10) ,
   t_id int(10),
   value varchar(5));insert into test values(100.1.1.'Joe');
insert into test values(200.2.1.'male');
insert into test values(300.3.1.'50');

insert into test values(101.1.2.'liu 2');
insert into test values(201.2.2.'male');
insert into test values(301.3.2.'30');

insert into test values(102.1.3.'liu three');
insert into test values(202.2.3.'woman');
insert into test values(302.3.3.'10');
Copy the code

Write an SQL statement showing the following results:

Name Gender Age--------- -------- ----Zhang Three male 50 Liu two male 30 Liu three female 10Copy the code

Compared with regular query, we can say that we need to redefine the new attribute column to display, so we need to complete the transformation of the attribute column by judgment

case

First step by step, since you need to judge, then through the case.. when .. then .. else .. End to

SELECT
	CASE type WHEN 1 THEN value END 'name'.CASE type WHEN 2 THEN value END 'gender'.CASE type WHEN 3 THEN value END 'age'
FROM
	test
Copy the code

Look at that. That’s how it ended up

In the next step, we need to aggregate all the data. Based on the aggregation function we learned earlier, we can choose to use Max ().

SELECT
	max(CASE type WHEN 1 THEN value END) 'name'.max(CASE type WHEN 2 THEN value END) 'gender'.max(CASE type WHEN 3 THEN value END) 'age'
FROM
	test
GROUP BY
	t_id;

-- Second grammar
SELECT
	max(CASE WHEN type = 1 THEN value END) 'name'.max(CASE WHEN type = 2 THEN value END) 'gender'.max(CASE WHEN type = 3 THEN value END) 'age'
FROM
	test
GROUP BY
	t_id;
Copy the code

This completes the row to column transition, and we can use the same method later if we need to do so:

The main thing is to find patterns in the data

If you just aggregate it, you end up with only one piece of data, so we need to group it here

It doesn’t matter if you don’t know. We’ll talk more about that later

if()

There are other ways to do it besides using case so let’s look at that

SELECT
	max(if(type = 1.value.' ')) 'name'.max(if(type = 2.value.' ')) 'gender'.max(if(type = 3.value.0)) 'age'
FROM
	test
GROUP BY
	t_id
Copy the code

If () means that the first value is returned if the condition is met, and the second value is returned otherwise

In addition, if we want to query NULL data for default values, we can use ifnull() to do so

-- If 'account' is' null ', then 0 is displayed
select ifnull(account.0) from tb_user;
Copy the code

Paging sorting

Conventional paging

Now all of the above queries match all of the data that matches the criteria, which would probably drag down the server if there were a lot of data in real development, so here we’re going to display the data page by page

In MySQL, paging is done using the limit keyword

select * from tb_user limit 0.2
Copy the code

The former parameter indicates the starting position, and the latter parameter indicates the number of items to display

Paging optimization

MySQL > select * from ‘MySQL’; MySQL > select * from ‘MySQL’; MySQL > select * from ‘MySQL’

select * from tb_user limit 10000000.10
Copy the code

So let’s talk a little bit about how limit does paging, okay

Limit will find the starting point of the page, discard the data, and continue to read the number of columns from there
So the larger the starting position, the more data you need to read and the longer the query takes

Here is an optimization: given the query range of data, preferably indexed columns (indexed columns can speed up the query efficiency)

select * from tb_user where id > 10000000 limit 10;
select * from tb_user where id > 10000000 limit 0 10;
Copy the code

If the limit is followed by only one parameter, then this parameter only indicates the number of entries to display

Associated query

At present, our queries are single-table queries, and our query SQL in our work basically involves operations between multiple tables, so we need to carry out multi-table associated queries

Now let’s simply create a table and see how we can perform a multi-table associative query

create table tb_order(
	id bigint primary key auto_increment,
    user_id bigint comment 'Owning User',
    order_title varchar(50) comment 'Order Name'
) comment 'Order sheet';

insert into tb_order(user_id, order_title) values(1.'orders - 1'), (1.'orders - 2'), (1.'orders - 3'), (2.'orders - 4'), (5.'orders - 5'), (7.'orders - 71');
Copy the code

Equivalent query

If you want to do a relational query, this is how SQL works

select * from tb_user, tb_order where tb_user.id = tb_order.user_id;
Copy the code

Equivalent query means that two tables containing the same column name match the same column name in the query

Comparing equivalent queries, there are also non-equivalent queries: two tables do not have the same column name, but one column is in the range of another table’s columns

**between.. and .. * * to query

The subquery

The so-called sub-query can be understood as:

Complete SQL statements nested within other SQL statements

So again, let’s do it the other way

select * from tb_order where user_id = (select id from tb_user where id = 1);
select * from tb_order where user_id in ( select id from tb_user);
Copy the code

Subqueries can also be classified into different types according to the results returned by subqueries

SQL1 returns only one piece of data, and if the query is judged by equivalence, it can be called a single-row subquery
SQL2, of course, is a multi-row subquery

In addition to the WHERE condition, subqueries can also be used in display columns

select od.*, (select login_name from tb_user where id = od.user_id ) from tb_order od;
Copy the code

Left associated

Left associative query has left JOIN as the main key point. The key fields in the two tables are associated with each other through ON. The data queried in this way is dominated by the left table

select 
	user.*, od.user_id, od.order_title 
from tb_user user 
left join tb_order od on user.id = od.user_id;
Copy the code

The right link

Right join is the key point of the right association. The associated table on the right of the data is the main one. Other operations are the same as the left association

select 
	user.*, od.user_id, od.order_title 
from tb_user user 
right join tb_order od on user.id = od.user_id;
Copy the code

And it can be seen that in the display of data, if the right table does not have corresponding data in the left table, the data in the left table will not be displayed

If the actual work of the query is so simple, it is not too comfortable

Aggregation query

Earlier, we talked about aggregate functions, which evaluate a set of data and return a single value.

In many cases, if we want to group data in a table using aggregate functions, we need to use group by queries

With the data in the table so far, we can make a scenario:

Figure out how many records there are for each login account in the table

select count(*), login_name from tb_user group by login_name
Copy the code

Each query syntax is actually quite simple to use

If you want to filter aggregated query data, you can not use WHERE to filter, you need to filter by HAVING

select count(*), login_name from tb_user group by login_name having login_name = 'admin1';
Copy the code

Also note:

The current column did not passgroup byGroup, then can’t passhavingTo query

Grammar problem

If we encounter such a problem while operating, this is because the display column contains columns that are not grouped, as determined by sql_mode mode. Let’s take a look at the default Settings

The main thing is that the syntax is not standard

-- ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
select @@sql_mode;
Copy the code

set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Copy the code

Just follow the instructions

The last word

Or I said in front, in the database level, add, delete and change is not difficult, more important is to check, how to query these data quickly and efficiently is our most work, we still need more practice is

A lot of details are not discussed, then you can see the official website documentation

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

MySQL delves into table data manipulation

Say what I said before

Formal presentation

Insert data

Insert single data for all fields

Insert more data into a specified field

ON DUPLICATE KEY UPDATE

Modify the data

Delete the data

Query data

A simple query

Aggregation function

Conditions of the query

Equivalent query

Batch query

Fuzzy query

Judge not empty

Time to determine

Transfer line column

case

if()

Paging sorting

Conventional paging

Paging optimization

Associated query

Equivalent query

The subquery

Left associated

The right link

Aggregation query

The last word

MySQL delves into table data manipulation

Say what I said before

Formal presentation

Insert data

Insert single data for all fields

Insert more data into a specified field

ON DUPLICATE KEY UPDATE

Modify the data

Delete the data

Query data

A simple query

Aggregation function

Conditions of the query

Equivalent query

Batch query

Fuzzy query

Judge not empty

Time to determine

Transfer line column

case

if()

Paging sorting

Conventional paging

Paging optimization

Associated query

Equivalent query

The subquery

Left associated

The right link

Aggregation query

The last word

Related Posts

Noodles don’t panic when | hi, talk about the understanding of Synchronized? (5)

MySQL Page Complete Guide – The principle of shallow in deep out page

Pycharm tutorial (19) — Code folding of the Pycharm editor function