Mysql data operation insert query modify delete command usage

Insert to add data

We learned about databases and database tables. Next we will learn about the data in the operation table.

Let's start to use the insert statement to add records. This is an SQL statement. You need to specify the table for which you want to insert data rows or the table that puts values by row. If you have set a primary key and auto-increment, you need to set the corresponding primary key value to null or the number "0" when adding it ".

Basic syntax:

Insert [into] tbl_name [(col_name,...)] VALUES (pression ,...)
Insert [into] tbl_name SET col_name = expression

The word "into" is optional since MySQL 3.22.5. (This is also true for other forms of insert statements .) The values table must contain the values of each column in the table and are given in the order of column storage in the table. (Generally, this is the sequence in which columns are defined when a table is created. If not, use describe tbl_name to view the order .). Take a look at the instance:

You can specify the values of all columns:

Insert test01_01 values (null, 'www .111cn.net ', '23', 'beij ');

Multiple value tables can provide multiple rows of data at a time:

Insert test01_01 values ('www .111cn.net ', '23', 'beij'), ('gray ', '34', 'AH ');

You can give the column to be assigned a value and then list the values. This is useful for creating records that require initial settings for only a few columns:

Insert test01_01 (name) values ('gery ');
Insert test01_01 (name) values ('Mack'), ('Rose ');
Insert test01_01 set name = 'Sara'; // using this form of insert statements cannot insert multiple rows

Insert... select Add data

When we learn how to create a table in the previous section, we know that you can use select to directly create a table from other tables, or even copy data records at the same time. If you already have a table, you can also benefit from the cooperation of select statements.

Basic syntax:

Insert into tb_name select * from tb_name1; // The table structure is completely consistent.
Insert into tb_name (col1) select col1 from tb_name1; // add a value
The insert into... select statement meets the following conditions:

1: The query cannot contain an order by clause.

2: The target table of the insert statement cannot appear in the from clause in the select query section, because this is forbidden in ansi SQL to allow select from the table you are inserting. the problem is that the select statement may be sent to records previously inserted during the same running period. When a sub-selection clause is used, the situation can be easily confused.

See the following example:

Insert into test01_03 select * from test01_01;
Insert into test01_03 (name) select name from test01_01;

Select query

Unless they are finally retrieved and used to do something, it is no good to put records into the database. This is the purpose of the select statement, that is, to help retrieve data. Select is probably the most commonly used statement in SQL, and it is also the most important to use it; using it to select records may be quite complex and may involve comparison between columns in many tables. This section describes the basic functions of a select statement. The select statement syntax is as follows:

Select selection_list // which columns to select
From table_list // Where to select a row
Where primary_constraint // what conditions must a row meet?
Group by grouping_columns // how to group results
Having secondary_constraint // The second condition that the row must meet
Order by sorting_columns // how to sort the results
Limit count // result limit

Note: All keywords used must be given exactly in the above order. For example, a having clause must be followed by the group by clause and before the order by clause.

In the preceding syntax, everything except the select keyword is optional. Some databases need to use the from keyword, but MySQL allows evaluate expressions without referencing any tables.

Common Query

The simplest form of select is to retrieve everything from a table.

Basic syntax:

Select columns from tb_name;

Columns is the column name. It can be written as a * sign to query all columns. You can also specify a column. Tb_name is your target database table.

Let's look at two examples:

Select * from test01_01; // query the data of all columns in the current table
Select name, age from test01_01; // query all data in the name column and age column of the current table
Select (2 + 3*4.5)/2.5; // evaluate the expression without referencing any table

Conditional query

You do not have to return all row records for each query. You can select only specific rows from your table. You can use where or having. Here we only talk about where statements, because where can implement most of having functions.

Basic syntax:

Select columns from tb_name where expression ..;
There are many expressions, such as name = 'www .111cn.net ', age> = 20, name = 'www .111cn.net' or name = 'www .111cn.net ', etc, can be written after the where expression.

Let's look at an example:

Select * from test01_01 where name = "www.111cn.net"; // filter out all data whose name value is www.111cn.net in the current table.

Query sorting

Use the order by clause to sort the query results by one or more columns. The syntax format of the order by clause is:

Order by column_name [asc | desc] [,…]

Asc indicates ascending order, which is the default value and desc indicates descending order. Order by cannot be sorted by text, text, or image data type. In addition, you can sort data by expressions.

See the following example:

Select * from test01_01 order by age asc; // output in ascending order of age
Note that the desc/asc keyword is only applicable to column names that follow it

Group and row count

The group by clause returns the query results of a group based on the given column name. It can be used to query columns with the same value.

Basic syntax:

Group by col_name ,....

It makes no sense to use group by independently. It works with a variety of combined functions for row counting.

See the following example:

Select name, count (*) from test01_01 group by name; // group by name and count

If you return the value of a column in addition to the count, you must use the group by statement. Otherwise, records cannot be calculated and errors are generated.

Multi-table query

Query multiple tables. The from clause lists the table names and is separated by commas (,), because the query needs to pull information from the two tables.

Basic syntax:

Select t1.name, t2.name from tb_name1 as t1, tb_name2 as t2 where t1.name = t2.name;

The code above indicates that the values with the same name are retrieved from t1 and t2. As indicates the alias for this table.

Example:

Select t1.name as t1name, t1.age as t1age, t2.name as t2name, t2.age as t2age
From test01_01 as t1, test01_03 as t2 where t1.name = t2.name;

Update data

We use update to update data. This is the most common data update statement. Let's look at the syntax.

Update tbl_name SET column to be changed

Where record to be updated

The where clause is optional. Therefore, if not specified, each record in the table is updated.

Example:

Update test01_03 set name = 'www .111cn.net 'where age = '20 ';

Delete data

Delete a statement. We usually use the delete keyword. Syntax definition:

Delete from tbl_name where record to be deleted

The where clause specifies which records should be deleted. It is optional, but if it is not selected, all records will be deleted. This means that the simplest delete statement is also the most dangerous.

Example:

Delete from test01_01 where age = 10; // delete data of the age of 10