Mysql---DML statement:

DML statement:

DML operations are operations that record tables in a database, primarily including insert (insert), UPDATE, delete (delete), and check (select) for table records, which are the most frequently used by developers. They are described in turn.

1. Inserting records

Once the table is created, you can insert a record into it, and the basic syntax for inserting the record is as follows:

INSERT into TableName (field1,field2,...... fieldn) VALUES (value1,value2,...... valuesn);

For example, insert the following record into the table emp: ename is zzx1,hiredate for 2000-01-01,sal and 2000,deptno to 1, the command executes as follows:

mysql> INSERT INTO EMP (ENAME,HIREDATE,SAL,DEPTNO) VALUES (' zzx1 ', ' 2000-01-01 ', ' 2000 ', 1);

You can also not specify a field name, but the order after values should be the same as the order in which the fields are arranged (deprecated):

mysql> INSERT INTO EMP values (' Lisa ', ' 2003-02-01 ', ' 3000 ', 2);

tip: For fields with nullable fields, non-empty but with default values, self-increment fields, you can use the field list after insert inside ,values are only written with value that corresponds to the field name, and the fields that are not written can be automatically set to NULL, default values, and the next number that is added, which in some cases can greatly reduce the complexity of the SQL statement.

For example, you explicitly insert values only for ename and Sal fields in a table:mysql> INSERT INTO EMP (ename,sal) VALUES (' Dony ', +);

In MySQL, the INSERT statement also has a good feature, you can insert multiple records at once, the syntax is as follows:

INSERT into TableName (field1, Field2,...... fieldn) VALUES (record1_value1, record1_value2,...... record1_valuesn), (record2_ Value1, record2_value2,...... record2_valuesn),...... (Recordn_value1, recordn_value2,...... recordn_valuesn);

As you can see, each record is separated by commas.

In the following example, the table dept is inserted two records at a time:mysql> INSERT INTO dept values (5, ' Dept5 '), (6, ' dept6 ');

This feature allows MySQL to save a lot of network overhead when inserting a large number of records, greatly improving the efficiency of insertion.

2. Update record

For the record value in the table, you can change it with the Update command, with the following syntax:

UPDATE tablename SET Field1=value1,field2.=value2,......fieldn=valuen [WHERE CONDITION]

For example, change the salary (SAL) in the table emp ename to "Lisa" from 3000 to 4000:mysql> update emp set sal=4000 where ename= ' Lisa ';

In MySQL, the update command can update data in multiple tables at the same time, with the following syntax:

UPDATE t1,t2...tn Set T1.FIELD1=EXPR1,TN.FIELDN=EXPRN [WHERE CONDITION]

In the following example, both field Sal in the table emp and the field deptname in table dept are updated:

mysql> update emp a,dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where A.deptno=b.deptno;

Since then, the data for the two tables has been updated at the same time.

Note: The syntax for multi-table updates is more used for dynamically updating fields of another table, depending on the field of one table

3. Deleting records

If the record is no longer needed, you can delete it with the Delete command, with the following syntax:delete from tablename [WHERE CONDITION]

For example, in the EMP, all records ename as ' Dony ' are deleted, the command is as follows:mysql> delete from emp where ename= ' dony ';

In MySQL, you can delete data from multiple tables at once, with the following syntax:delete t1,t2...tn from t1,t2...tn [WHERE CONDITION]

Note: If the name of the table after the from is aliased, then the delete is followed by the corresponding alias, otherwise a syntax error is prompted.

In the following example, the records DEPTNO 3 in the Table EMP and dept are both deleted:

Mysql> Delete A, a from EMP a,dept b where A.deptno=b.deptno and a.deptno=3;

Note: Regardless of whether it is single table or multiple tables, no where condition will delete all records of the table, so be careful when you operate.

4. Query record (FOCUS)

Once the data has been inserted into the database, a variety of queries can be made with the SELECT command, making the results of the output consistent with our requirements. Because the syntax of select is complex, all here only describes the most basic syntax:SELECT * FROM tablename [WHERE CONDITION]

The simplest way to query is to select all the records, and in the following example, all the records in the table emp are queried:mysql> select * from EMP;

where "*" means that you want to select all the records, or you can use commas to separate all the fields instead. The advantage of the "*" is that when you need to query all field information, the query statement is simple, but to query only part of the field, you have to list the fields one by one.

The syntax for querying all records is described in the example above, but in real-world applications, users also encounter a variety of queries

requirements, which are described separately below.

(1) query for records that are not duplicates.

Sometimes you need to remove the records from the table and display them, you can use the distinct keyword to achieve:

Mysql> select Ename,hiredate,sal,deptno from emp;

+--------+------------+---------+--------+| ename | HireDate | Sal | Deptno |+--------+------------+---------+--------+| ZZX | 2000-01-01 | 2000.00 | 1 | | Lisa | 2003-02-01 | 4000.00 | 2 | | Bjguan | 2004-04-02 | 5000.00 | 1 |+--------+------------+---------+--------+3 rows in Set (0.00 sec) mysql> SELECT distinct deptno from emp;+--------+ | Deptno |+--------+| 1 | | 2 |+--------+2 rows in Set (0.00 sec)

(2) Conditional query.

In many cases, the user does not need to query all the records, but only needs to query a subset of the data based on the qualification criteria, and the WHERE keyword can be used to implement such operations.

For example, you need to query all records with a deptno of 1:mysql> select * from emp where deptno=1;

The result set lists the records that match the criteria. In the above example, the condition behind the where is the ' = ' Comparison of a field, in addition to ' = ', you can also use the >, <, >=, <=,! = and other comparison operators, multiple conditions can also use the or, and and other logical operators for the multi-conditional union query, Operators are explained in more detail in later chapters.

The following is an example of a query using a multi-field condition:mysql> SELECT * from emp where deptno=1 and sal<3000;

(3) Sorting and restriction.

We often have this requirement to take out the result set of records sorted by a field, which uses the sort operation of the database, implemented by the keyword order BY, with the following syntax:SELECT * FROM TableName [WHERE CONDITION] [ ORDER by Field1 [desc| ASC], Field2

[desc| ASC],......FIELDN [desc| ASC]]

where DESC and ASC are sort order keywords, desc indicates descending order by field, ASC means ascending, and if not write this keyword is sorted in ascending order by default. the order by can be followed by several different sort fields, and each sort field can have a different sort order.

For example, the records in the EMP table are displayed according to the salary:mysql> SELECT * from emp order by Sal;

if the value of the sort field is the same, the field with the same value is sorted by the second sort field, and so on. If there is only one sort field, the records with the same fields will be unordered. for a sorted record, you can use the LIMIT keyword if you want to display only a subset, not all of them

, the syntax for LIMIT is as follows:SELECT ... [LIMIT Offset_start,row_count]

Where Offset_start represents the starting offset of the record, and Row_count represents the number of rows displayed.

By default, the starting offset is 0, just write the number of rows of records can be, this time, the actual display is the first n records, see the following example:

For example, the first 3 records in the EMP table that are sorted by Sal are displayed:mysql> SELECT * from emp order by Sal limit 3;

If you want to show that the EMP table is sorted by Sal and starts with the second record, 3 records are displayed:mysql> SELECT * from emp order by Sal limit 1,3;

Note: If the limit is followed by only one number, it refers to the number of rows displayed. limit is often used in conjunction with order by to make a paginated display of records.

Note: Limit is the syntax after MySQL extended SQL92 and is not common on other databases, similar to the top keyword in sql.

(4) aggregation.

In many cases, we need to do some summary operations, such as counting the number of people across the company or counting the number of people in each department, and this is the time to use SQL aggregation operations. The syntax for the aggregation operation is as follows:

SELECT [Field1,field2,...... fieldn] Fun_namefrom tablename[where where_contition][group by Field1,field2,...... Fieldn[WITH Rollup]][having Where_contition]

Make the following description of its parameters.

Fun_name represents the aggregation operation to be done , which is the aggregate function, which is commonly used with SUM, COUNT (*) (number of records), Max (maximum), min (minimum).

The group BY keyword indicates the fields to be aggregated by category, such as the number of employees to be classified by department, and the department should be written behind group by.

With ROLLUP is an optional syntax that indicates whether the aggregated results of the classification are re-aggregated.

The Having keyword indicates that the result of the classification is then filtered by the condition.

Note: The difference between having and where is that having is filtering the condition of the aggregated result, where the record is filtered before the aggregation, and if the logic allows, we use where possible to filter the records as much as we can, so that the result set decreases and the aggregation efficiency is greatly improved. Finally, according to the logic to see if have to re-filter.

For example, to the total number of statistics companies in the EMP table:mysql> Select COUNT (1) from EMP;

+----------+| Count (1) |+----------+| 4 |+----------+1 row in Set (0.00 sec)

On this basis, to count the number of departments:mysql> Select Deptno,count (1) from the EMP group by DEPTNO;

+--------+----------+| Deptno | Count (1) |+--------+----------+| 1 | 2 | | 2 | 1 | | 4 | 1 |+--------+----------+3 rows in Set (0.00 sec)

To be more detailed, it is necessary to count the number of departments and the total number of people:mysql> Select Deptno,count (1) from the EMP group by DEPTNO with rollup;

+--------+----------+| Deptno | Count (1) |+--------+----------+| 1 | 2 | | 2 | 1 | | 4 | 1 | | NULL | 4 |+--------+----------+4 rows in Set (0.00 sec)

Department with statistics greater than 1 persons:mysql> Select Deptno,count (1) from EMP Group by DEPTNO have count (1) >1;

+--------+----------+| Deptno | Count (1) |+--------+----------+| 1 | 2 |+--------+----------+1 row in Set (0.00 sec)

Finally, the total salary, maximum and minimum salary of all employees of the company are counted:mysql> select * from EMP;

+--------+------------+---------+--------+| ename | HireDate | Sal | Deptno |+--------+------------+---------+--------+| ZZX | 2000-01-01 | 100.00 | 1 | | Lisa | 2003-02-01 | 400.00 | 2 | | Bjguan | 2004-04-02 | 100.00 | 1 | | Dony | 2005-02-05 | 2000.00 | 4 |+--------+------------+---------+--------+4 rows in Set (0.00 sec) mysql> Select sum (SAL), Max (Sal), Min (sal) from emp;+----------+----------+----------+| Sum (SAL) | Max (SAL) | Min (sal) |+----------+----------+----------+| 2600.00 | 2000.00 | 100.00 |+----------+----------+----------+1 row in Set (0.00 sec)

(5) Table connection.

When you need to display fields from multiple tables at the same time, you can use table joins to implement such a feature. From the big class above, the table joins into the inner and outer joins, the most important difference between them is that the internal connection only selects the records that match each other in two tables, and the outer joins will select other mismatched records. What we use most is the inner connection.

For example, the name of all employees and the department name are queried, because employee names and departments are stored in the table EMP and dept, so you need to use a table connection to query:mysql> select Ename,deptname from Emp,dept where Emp.deptno=dept.deptno;

+--------+----------+| ename | Deptname |+--------+----------+| ZZX | Tech | | Lisa | Sale | | Bjguan | Tech | | Bzshen | HR |+--------+----------+4 rows in Set (0.00 sec)

The outer joins are divided into left and right connections, as defined below.

Left join: Contains all the records in the left table or even the records that do not match it in the right table

Right connection: Contains all records in the right-hand table, even those that do not match it in the left table

For example, query all user names and department names in the EMP:

Mysql> Select Ename,deptname from the EMP left JOIN dept on Emp.deptno=dept.deptno;

Comparing this query to the query in the previous example is the query user name and department name, the difference is that all the user names are listed in this example, even if there is a user name (Dony) does not exist the Legal department name (department number is 4, in dept does not have this department) In the example above, only the user name and department name that exist in the legal department are listed. The right connection and the left join are similar, and the two can be converted to each other, for example, the above example can be rewritten as the right connection as follows:

Mysql> Select Ename,deptname from dept right join EMP on DEPT.DEPTNO=EMP.DEPTNO;

+--------+----------+| ename | Deptname |+--------+----------+| ZZX | Tech | | Lisa | Sale | | Bjguan | Tech | | Bzshen | HR | | Dony | |+--------+----------+5 rows in Set (0.00 sec)

(6) Sub-query.

In some cases, when we query, the condition is the result of another SELECT statement, this time, we need to use the subquery. The keywords used for subqueries mainly include in, not in, =,! =, exists, not exists, and so on.

For example, all records from the EMP table are queried for all departments in the Dept table:

Mysql> SELECT * from emp where deptno in (select Deptno from dept);

If the subquery record number is unique, you can also use = instead of in:mysql> select * from emp where deptno = (select Deptno from dept);

ERROR 1242 (21000): subquery returns more than 1 row

Mysql> SELECT * from emp where deptno = (select Deptno from dept limit 1);

+--------+------------+---------+--------+| ename | HireDate | Sal | Deptno |+--------+------------+---------+--------+| ZZX | 2000-01-01 | 2000.00 | 1 | | Bjguan | 2004-04-02 | 5000.00 | 1 |+--------+------------+---------+--------+2 rows in Set (0.00 sec)

In some cases, subqueries can be converted to table joins, for example:mysql> select * from emp where deptno in (select Deptno from dept);

+--------+------------+---------+--------+| ename | HireDate | Sal | Deptno |+--------+------------+---------+--------+| ZZX | 2000-01-01 | 2000.00 | 1 | | Lisa | 2003-02-01 | 4000.00 | 2 | | Bjguan | 2004-04-02 | 5000.00 | 1 | | Bzshen | 2005-04-01 | 4000.00 | 3 |+--------+------------+---------+--------+4 rows in Set (0.00 sec)

After conversion to table connection:mysql> select emp.* from EMP, dept where Emp.deptno=dept.deptno;

+--------+------------+---------+--------+| ename | HireDate | Sal | Deptno |+--------+------------+---------+--------+| ZZX | 2000-01-01 | 2000.00 | 1 | | Lisa | 2003-02-01 | 4000.00 | 2 | | Bjguan | 2004-04-02 | 5000.00 | 1 | | Bzshen | 2005-04-01 | 4000.00 | 3 |+--------+------------+---------+--------+4 rows in Set (0.00 sec)

Note: The conversion between subqueries and table joins is primarily applied in two ways: MySQL 4.1 does not support subqueries, and table joins are used to implement the function table connection of subqueries in many cases to optimize subqueries

(7) record the union.

We often encounter such an application, the two table data according to a certain query criteria, the results are merged together to display, this time, it is necessary to use the Union and the Union ALL keyword to implement such a function, the specific syntax is as follows:

SELECT * from t1union| UNION allselect * from T2 ... union| UNION Allselect * from TN;

The main difference between union and union all is that union all merges the result set directly, and the Union is DISTINCT the result of the Union all once, removing the result of the duplicate record. Consider the following example, which displays the set of department numbers in the EMP and dept tables:

Mysql> SELECT * from emp;+--------+------------+---------+--------+| ename | HireDate | Sal | Deptno |+--------+------------+---------+--------+| ZZX | 2000-01-01 | 100.00 | 1 | | Lisa | 2003-02-01 | 400.00 | 2 | | Bjguan | 2004-04-02 | 100.00 | 1 | | Dony | 2005-02-05 | 2000.00 | 4 |+--------+------------+---------+--------+4 rows in Set (0.00 sec) mysql> Select * from dept;+--------+----------+| Deptno | Deptname |+--------+----------+| 1 | Tech | | 2 | Sale | | 5 | Fin |+-------+----------+3 rows in Set (0.00 sec) mysql> Select Deptno from emp-> Union all-> select Deptno from D ept;+--------+| Deptno |+--------+| 1 | | 2 | | 1 | | 4 | | 1 | | 2 | | 5 |+--------+7 rows in Set (0.00 sec) Displays:mysql> Select Deptno from emp-> union-> Select Deptno if you want to remove the results of duplicate records from dept;+--------+| Deptno |+--------+| 1 | | 2 | | 4 | | 5 |+--------+4 rows in Set (0.00 sec)

Mysql---DML statement: