MySQL Lecture 3·CRUD operation of SQL boy

Hello, I am safe and sound.

Article directory

• Add, delete, check and modify: How to operate the data in the table?
• • adding data
  • • Insert data record
    • Insert query results
  • delete data
  • change the data
  • Query data
  • • select｜where｜group by｜having
    • from
    • order by
    • limit

Add, delete, check and modify: How to operate the data in the table?

CRUD operations on SQL are very important. Some companies will ask us to write some SQL during interviews. I encountered this (manual dog head) when I was interviewing Byte last time.

In our supermarket project, we have designed a database demo.goodsmaster for users, and defined the fields and various constraints in it, as follows:

mysql> desc demo.goodsmaster;
 + --------------- + --------------- + ------ + ----- + ----- ---- + -- +
| Field | Type | Null | Key | Default |Extra |
 + --------------- + ------------ + ------ + ----- + ------- -- + ------------ +
| itemnumber | int | NO | PRI | NULL |auto_increment |
| barcode | text | NO | | NULL | |
| goodsname | text | NO | | NULL | |
| specification | text | YES | | NULL | |
| unit | text | YES | | NULL | |
| price | decimal(10,2)| NO | | NULL | |
 + --------------- + ------------ + ------ + ----- + ------- -- + ---------------- +
6 rows in set (0.02 sec)

OK, let’s operate on this table.

Add data

First, the syntax structure of adding data is as follows:

insert into table name [(field name [,field name] ...)] values (list of values);

The ‘[ ]’ here means that the content inside is optional, that is, according to the syntax requirements of MySQL, you can write it or not.

Adding data is divided into two situations: inserting data records and inserting query results. The following are introduced separately:

Insert data record

The data insertion operation supported by MySQL is very flexible. You can either completely insert a data record by assigning values to all fields in the table, or you can only assign values to some fields when inserting records.

It is still the demo.goodsmaster table above, which includes 6 fields: itemnumber, barcode, goodsname, specification, unit and price.

I want to insert a data record that includes the values of all fields, so I can do this:

insert into demo.goodsmaster
(itemnumber, barcode, goodsname, specification, unit, price)
values (4, '0003', 'ruler', 'triangle', 'handle', 5);

Run this SQL and then query the data table:

mysql> select *
    -> from demo.goodsmaster;
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
| itemnumber | barcode | goodsname | specification | unit | price |
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
| 4 | 0003 | ruler | triangle | handle | 5.00 |
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
1 row in set (0.01 sec)

So if I want to insert a record, but only assign values to some fields, is it okay?

For example, a customer has a product that needs to be sold online immediately. Currently, only the barcode, name and price are known. The other information is not entered yet and will be filled in later. Is this possible?

Let’s try assigning values to only 3 fields and see if it works in practice?

insert into demo.goodsmaster
(
-- Only three fields are assigned values here, itemnumber, specification, and unit are not assigned values.
  barcode,
  goodsname,
  price
)
VALUES
(
  '0004',
  'test',
  10
);

Run this SQL statement and query the table data, and you will find that it is possible.

mysql> select *
    -> from demo.goodsmaster;
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
| itemnumber | barcode | goodsname | specification | unit | price |
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
| 4 | 0003 | ruler | triangle | handle | 5.00 |
| 5 | 0004 | Test | NULL | NULL | 10.00 |
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
2 rows in set (0.00 sec)

The reason why we can only assign values to some fields when inserting a data record lies in the way we define the fields.

How are the fields defined? Let’s take a look at the structure of the table and the definitions of each field:

mysql> desc demo.goodsmaster;
 + --------------- + --------------- + ------ + ----- + ---- ----- + ---------------- +
| Field | Type | Null | Key | Default | Extra |
 + --------------- + --------------- + ------ + ----- + ---- ----- + ---------------- +
| itemnumber | int | NO | PRI | NULL | auto_increment |
| barcode | text | NO | | NULL | |
| goodsname | text | NO | | NULL | |
| specification | text | YES | | NULL | |
| unit | text | YES | | NULL | |
| price | decimal(10,2) | NO | | NULL | |
 + --------------- + --------------- + ------ + ----- + ---- ----- + ---------------- +
6 rows in set (0.01 sec)

As you can see, the three fields that we did not explicitly assign when inserting data have their own characteristics.

Both specification and unit can be null, while itemnumber defines an auto-increment constraint.

When we insert a data record, we must consider three situations of field constraints.

• If a field is allowed to be null and we do not assign a value to it, MySQL will automatically assign a null value to it. In the code just now, both the specification and unit fields are allowed to be empty. Therefore, if the data insertion statement does not specify the values of these fields, MySQL will automatically insert null values;
• If the field is a primary key, it cannot be empty. At this time, MySQL will process it according to the constraints we added. For example, the field itemnumber is the primary key and cannot be empty, and we have defined an auto-increment constraint, so MySQL automatically adds 1 to the previous maximum value;
• If there is a field definition that cannot be empty and is not a primary key, when you insert a data record, you must assign a value to the record.

What happens if we violate the constraints of a field?

For example, we try to change the field specification of the table demo.goodsmaster to not be empty:

alter table demo.goodsmaster
modify specification text not null;

When running this SQL statement, the system will prompt an error. The reason is that we have just partially inserted a data record without assigning a value to the field “specification”. This conflicts with our operation of adding a non-null constraint to the field “specification”.

Therefore, we need to delete the data records with empty spcification value in the table, and then modify the field constraints:

delete from demo.goodsmaster
where itemnumber=5;

After deleting the data record, run the above statement and add a non-null constraint to the field specification, and it succeeds.

Now let’s verify the non-null constraint. We try to partially insert a data record without assigning a value to the field specification:

insert into demo.goodsmaster
(barcode, goodsname, price)
values ('0004', 'test', 10);

When you run this SQL statement, MySQL will report an error, indicating that the field specification has no default value. In other words, this field cannot be empty. If you do not assign a value to it when inserting data, you must give it a default value.

Now we understand that it is possible to partially insert a data record, but the premise is that MySQL must know how to handle the fields without assigned values. For example, it can be empty, have a default value, or be an auto-increment constraint field, etc. Otherwise, MySQL will prompt an error.

In actual work, inserting only one piece of data at a time is sometimes not enough.

So next we will explain another data insertion operation in MySQL: inserting query results into the data table.

Insert query results

MySQL supports inserting query results into the data table. We can specify fields, or even values, to insert into the data table. The syntax structure is as follows:

insert into table name (field name)
select field name or value
from table name
where condition

Okay, that’s it for adding data. Now we know that when we insert a data record into a data table, we can assign values to all fields or to some fields, depending on the definition of the fields.

If the field cannot be empty and has no default value, it must be assigned a value. In addition, we can also improve the efficiency of adding data by inserting a query result into the data table.

Delete data

The syntax for data deletion is very simple, as follows:

delete from table name
where condition

If we now want to clean up the contents of the table demo.goodsmaster we just used and delete all the data, we can do this through the following SQL statement:

delete from demo.goodsmaster;

Here is a suggestion: when we delete data, we should get used to adding conditional statements where to prevent misoperations.

Suppose our data table demo.goodsmaster has the following data records:

mysql> select *
    -> from demo.goodsmaster;
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
| itemnumber | barcode | goodsname | specification | unit | price |
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
| 4 | 0003 | ruler | triangle | handle | 5.00 |
| 5 | 0004 | Test | NULL | NULL | 10.00 |
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
2 rows in set (0.00 sec)

If we want to delete all the data in the table, we can write like this:

delete from demo.goodsmaster where itemnumber > 1;

Modify data

MySQL syntax for modifying data:

update table name
set field name=value
where condition

Note: Do not modify the value of the primary key field, because the primary key is the unique identifier of the data record. If the value of the primary key is modified, the integrity of the data may be destroyed.

Query data

select｜where｜group by｜having

Let’s take a look at the syntax structure of the query statement:

select *|field list
from data source
where condition
group by field
having condition
order by field
limit starting point, number of lines

Among these fields, select, where, group by and having are easier to understand. We only need to know their meaning:

• select: query keyword, indicating that we want to make a query. “*” is a wildcard character, indicating that we want to query all fields in the table. Of course, we can also list the fields to be queried in the table. In this case, the query results can only display the contents of the fields you want to query.
• where: Indicates query conditions. We can put the conditions met by the data to be queried after the where keyword.
• group by: The function is to tell MySQL how to group the query results. It is often used together with MySQL’s aggregate function.
• having: used to filter query results, similar to where.

From, order by and limit are relatively complicated, and there are many things that need to be paid attention to. Please read on:

from

The from keyword identifies the data source of the query. We have only studied a single database now. When we study related tables later, the from keyword will be followed by more complex data table connections.

One thing to note is that the data source does not have to be a table, it can also be the result of a query. for example:

The part in the red box is called a derived table, or subquery, which means that we treat a query result data set as a virtual data table.

MySQL stipulates that the AS keyword must be used to give an alias to this derived table. That is a’ in the picture above.

order by

The function of ORDER BY is to tell MySQL how to sort the query results. ASC means ascending order, DESC means descending order.

Let’s take a simple example to show you how ORDER BY is used (here we still assume that the fields “specification” and “unit” are allowed to be empty). We insert 2 pieces of data into the table demo.goodsmaster:

insert into demo.goodsmaster
(
  barcode,
  goodsname,
  price
)
values
(
  '0003',
  'Ruler 1',
  15
);
insert into demo.goodsmaster
(
  barcode,
  goodsname,
  price
)
values
(
  '0004',
  'Test 1',
  20
);

If we do not control the order of query results, we will get results like this:

mysql> select *
    -> from demo.goodsmaster;
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
| itemnumber | barcode | goodsname | specification | unit | price |
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
| 4 | 0003 | ruler | triangle | handle | 5.00 |
| 5 | 0004 | Test | NULL | NULL | 10.00 |
| 6 | 0003 | Ruler 1 | NULL | NULL | 15.00 |
| 7 | 0004 | Test 1 | NULL | NULL | 20.00 |
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
4 rows in set (0.00 sec)

If we use ORDER BY to control the query results, the results will be different:

mysql> select *
    -> from demo.goodsmaster
    -> order by barcode asc, price desc;
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
| itemnumber | barcode | goodsname | specification | unit | price |
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
| 6 | 0003 | Ruler 1 | NULL | NULL | 15.00 |
| 4 | 0003 | ruler | triangle | handle | 5.00 |
| 7 | 0004 | Test 1 | NULL | NULL | 20.00 |
| 5 | 0004 | Test | NULL | NULL | 10.00 |
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
4 rows in set (0.00 sec)

As you can see, the query results will first be sorted in ascending order of field barcode, and fields with the same barcode will be sorted in descending order of price.

limit

The function of LIMIT is to tell MySQL to only display the results of part of the query. For example, now there are 4 pieces of data in our data table demo.goodsmaster. If we only want to display the 2nd and 3rd pieces of data, we can use the LIMIT keyword to achieve this:

mysql> select *
    -> from demo.goodsmaster
    -> limit 1,2;
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
| itemnumber | barcode | goodsname | specification | unit | price |
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
| 5 | 0004 | Test | NULL | NULL | 10.00 |
| 6 | 0003 | Ruler 1 | NULL | NULL | 15.00 |
 + ------------ + --------- + ------------ + ------------- - + ------ + ------- +
2 rows in set (0.00 sec)

In “LIMIT 1,2” here, “1” represents the starting position. In MySQL, the starting point of the starting position is 0, 1 represents starting from the 2nd record; “2” represents 2 pieces of data. Therefore, “LIMIT 1,2” means that starting from the 2nd piece of data, 2 pieces of data will be displayed, that is, the 2nd and 3rd pieces of data will be displayed.

Finally, I would like to add that if the primary key constraint or unique constraint is destroyed when we insert the query results into the table, we can use the “on duplicate” keyword to process it. The function of this keyword is to tell MySQL how to handle duplicate data if it encounters it.

For example: Suppose the user has 2 independent stores, each with its own system. Now it is necessary to introduce a chain operation model and manage the two stores uniformly with one system. Then the first problem encountered is the need for data integration. Let’s take the product information table as an example to illustrate how to integrate the product information data of two stores by using the “on duplicate” keyword.

Assume that the product information table of store A is “demo.goodsmaster”, and the code is as follows:

mysql> select *
    -> from demo.goodsmaster;
 + ------------ + --------- + ------------ + ------------- - + ------ + ---------- +
| itemnumber | barcode | goodsname | specification | unit | salesprice |
 + ------------ + --------- + ------------ + ------------- - + ------ + ---------- +
| 1 | 0001 | Book | 16K | Edition | 89.00 |
| 2 | 0002 | Pen | Pack of 10 | Bag | 5.00 |
| 3 | 0003 | Eraser | NULL | pieces | 3.00 |
 + ------------ + --------- + ------------ + ------------- - + ------ + ---------- +
3 rows in set (0.00 sec)

The product information table of store B is “demo.goodsmaster1”:

mysql> select *
    -> from demo.goodsmaster1;
 + ------------ + --------- + ------------ + ------------- - + ------ + ---------- +
| itemnumber | barcode | goodsname | specification | unit | salesprice |
 + ------------ + --------- + ------------ + ------------- - + ------ + ---------- +
| 1 | 0001 | Textbook | NULL | NULL | 89.00 |
| 4 | 0004 | Steamed Buns | | | 1.50 |
 + ------------ + --------- + ------------ + ------------- - + ------ + ---------- +
2 rows in set (0.00 sec)

Suppose we want to insert the product data of store B into the product table of store A. If there are duplicate product numbers, replace the barcode of store A with the barcode of store B, and replace the barcode of store A with the product name of store B. product name; if there are no duplicate numbers, directly insert the product data of store B into the product table of store A. This operation can be achieved using the following SQL statement:

insert into demo.goodsmaster
select * from demo.goodsmaster as a
on duplicate key update barcode = a.barcode, goodsname = a.goodsname;

The running results are as follows:

--The running results are as follows
mysql> select *
    -> from demo.goodsmaster;
 + ------------ + --------- + ------------ + ------------- - + ------ + ---------- +
| itemnumber | barcode | goodsname | specification | unit | salesprice |
 + ------------ + --------- + ------------ + ------------- - + ------ + ---------- +
| 1 | 0001 | Textbook | 16K | This book | 89.00 |
| 2 | 0002 | Pen | Pack of 10 | Bag | 5.00 |
| 3 | 0003 | Eraser | NULL | pieces | 3.00 |
| 4 | 0004 | Steamed Buns | | | 1.50 |
 + ------------ + --------- + ------------ + ------------- - + ------ + ---------- +
4 rows in set (0.00 sec)