Categories
Uncategorized

Mysql Professional Series – Day 5: DML operation summary, make sure you will?

This is the first series Mysql 5.

Presentation mysql5.7.25, cmd command: the environment.

DML (Data Manipulation Language) data manipulation language, INSERT, UPDATE, DELETE three kinds of instruction as the core, representing the insert, update and delete commands that must be mastered, select SQL DML and cooked in said CRUD (CRUD ).

Syntax relates to a text [] contains the content is optional, but described in detail below.

Insert

Inserting a single row 2 ways

Mode 1

insert into 表名[(字段,字段)] values (值,值);

Description:

Values ​​and needs one to one field

If the type of character or date, values ​​need to be caused by single quotes; if numeric type, do not need to use single quotes

And the number of fields must match values, corresponding to the position

If the field is not null, then the value must be inserted

Can not be inserted into the value field is empty, but note: the fields and values ​​are not written; or a write field, substituting null value

Following the table name field can be omitted, then all fields showing consistent, and the sequence order of the fields in the table.

Mode 2

insert into 表名 set 字段 = 值,字段 = 值;

Mode 2 is not common, it is recommended to use Mode 1

Bulk Insert 2 ways

Mode 1

insert into 表名 [(字段,字段)] values (值,值),(值,值),(值,值);

Mode 2

insert into 表 [(字段,字段)]
数据来源select语句;

Description:

Source select statement can have a variety of writing, note: the number of fields of the result and select returns insert data, order, type needs to be consistent.

About select wording will be described in detail later in the article.

Such as:

-- 删除test1
drop table if exists test1;
-- 创建test1
create table test1(a int,b int);
-- 删除test2
drop table if exists test2;
-- 创建test2
create table test2(c1 int,c2 int,c3 int);
-- 向test2中插入数据
insert into test2 values (100,101,102),(200,201,202),(300,301,302),(400,401,402);
-- 向test1中插入数据
insert into test1 (a,b) select 1,1 union all select 2,2 union all select 2,2;
-- 向test1插入数据,数据来源于test2表
insert into test1 (a,b) select c2,c3 from test2 where c1>=200;

select * from test1;

mysql> select * from test1;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    2 |    2 |
|  201 |  202 |
|  301 |  302 |
|  401 |  402 |

mysql> select * from test2;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|  100 |  101 |  102 |
|  200 |  201 |  202 |
|  300 |  301 |  302 |
|  400 |  401 |  402 |
+------+------+------+
4 rows in set (0.00 sec)

Data Update

Single-table updates

grammar:

update 表名 [[as] 别名] set [别名.]字段 = 值,[别名.]字段 = 值 [where条件];

Some names may be the name of a long table, in order to facilitate the operation, to the table name from the alias for a simple, more convenient operation of some.

If no alias when the table name is an alias.

Example:

mysql> update test1 t set t.a = 2;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 6  Changed: 4  Warnings: 0

mysql> update test1 as t set t.a = 3;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> update test1 set a = 1,b=2;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

Multi-table updates

Data can be updated at the same time a plurality of tables

grammar:

update 表1 [[as] 别名1],表名2 [[as] 别名2]
set [别名.]字段 = 值,[别名.]字段 = 值
[where条件]

Example:

-- 无别名方式
update test1,test2 set test1.a = 2 ,test1.b = 2, test2.c1 = 10;
-- 无别名方式
update test1,test2 set test1.a = 2 ,test1.b = 2, test2.c1 = 10 where test1.a = test2.c1;
-- 别名方式更新
update test1 t1,test2 t2 set t1.a = 2 ,t1.b = 2, t2.c1 = 10 where t1.a = t2.c1;
-- 别名的方式更新多个表的多个字段
update test1 as t1,test2 t2 set t1.a = 2 ,t1.b = 2, t2.c1 = 10 where t1.a = t2.c1;

Recommendations

Recommended way to update a single table, and easy maintenance.

Delete data manipulation

Use delete to delete

delete delete single table

delete [别名] from 表名 [[as] 别名] [where条件];

note:

If no alias when the table name is an alias

If there is an alias, must be written later delete the alias

If no alias, delete the alias back can be omitted.

Examples

-- 删除test1表所有记录
delete from test1;
-- 删除test1表所有记录
delete test1 from test1;
-- 有别名的方式,删除test1表所有记录
delete t1 from test1 t1;
-- 有别名的方式删除满足条件的记录
delete t1 from test1 t1 where t1.a>100;

The above four kinds of writing, we can seriously look at.

Multi-table delete

You can also delete records from multiple tables, the syntax is as follows:

delete [别名1,别名2] from 表1 [[as] 别名1],表2 [[as] 别名2] [where条件];

Description:

Aliases can be omitted, but need to keep up the table name in the back delete, separated by commas between multiple table names.

Example 1

delete t1 from test1 t1,test2 t2 where t1.a=t2.c2;

Test1 delete records in the table, such that a field of the record present in the recording test.c2

Look at the operating results:

-- 删除test1
drop table if exists test1;
-- 创建test1
create table test1(a int,b int);
-- 删除test2
drop table if exists test2;
-- 创建test2
create table test2(c1 int,c2 int,c3 int);
-- 向test2中插入数据
insert into test2 values (100,101,102),(200,201,202),(300,301,302),(400,401,402);
-- 向test1中插入数据
insert into test1 (a,b) select 1,1 union all select 2,2 union all select 2,2;
-- 向test1插入数据,数据来源于test2表
insert into test1 (a,b) select c2,c3 from test2 where c1>=200;

mysql> select * from test1;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    2 |    2 |
|  201 |  202 |
|  301 |  302 |
|  401 |  402 |

mysql> select * from test2;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|  100 |  101 |  102 |
|  200 |  201 |  202 |
|  300 |  301 |  302 |
|  400 |  401 |  402 |
+------+------+------+
4 rows in set (0.00 sec)

mysql> delete t1 from test1 t1,test2 t2 where t1.a=t2.c2;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from test1;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    2 |    2 |
+------+------+
3 rows in set (0.00 sec)

From the above output can be seen in Table 3 test1 record is deleted.

Example 2

delete t2,t1 from test1 t1,test2 t2 where t1.a=t2.c2;

2 while deleting the table, with the proviso that recording test.a = test.c2

Look at the operating results:

-- 删除test1
drop table if exists test1;
-- 创建test1
create table test1(a int,b int);
-- 删除test2
drop table if exists test2;
-- 创建test2
create table test2(c1 int,c2 int,c3 int);
-- 向test2中插入数据
insert into test2 values (100,101,102),(200,201,202),(300,301,302),(400,401,402);
-- 向test1中插入数据
insert into test1 (a,b) select 1,1 union all select 2,2 union all select 2,2;
-- 向test1插入数据,数据来源于test2表
insert into test1 (a,b) select c2,c3 from test2 where c1>=200;

mysql> select * from test1;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    2 |    2 |
|  201 |  202 |
|  301 |  302 |
|  401 |  402 |
+------+------+
6 rows in set (0.00 sec)

mysql> select * from test2;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|  100 |  101 |  102 |
|  200 |  201 |  202 |
|  300 |  301 |  302 |
|  400 |  401 |  402 |
+------+------+------+
4 rows in set (0.00 sec)

mysql> delete t2,t1 from test1 t1,test2 t2 where t1.a=t2.c2;
Query OK, 6 rows affected (0.00 sec)

mysql> select * from test1;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    2 |    2 |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from test2;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|  100 |  101 |  102 |
+------+------+------+
1 row in set (0.00 sec)

As can be seen from the output test1 and test2 Total 6 record has been deleted.

Usually we use more way is to delete from this table name syntax, and then delete the Above us back to keep up with the table name usage, we can recall, enhance memory.

Use truncate delete

grammar

truncate 表名;

drop, truncate, delete the difference

  • drop (delete the table): Delete the content and definition, free space, in short, it is to remove the entire table, after new data to be impossible, unless a new table.

    drop table delete statement structure is dependent constraints (constrain), the trigger (Trigger) index (index), is dependent on the table stored procedure / function will be retained, but its status changes: invalid.

    If you want to remove table definition and its data, use the drop table statement.

  • truncate (empty data table): delete the content, free up space without deleting the definition (reserved table data structure), and the drop is different, just clear the table data only.

    Note: truncate can not delete specific rows of data, it is imperative to delete the entire table cleared.

  • delete (delete the data in the table): delete statement to remove rows in a table. Process delete delete statement is deleted from the table each row, and at the same row as the delete transaction records stored in a log, for rollback operation.

    truncate and delete without a where: the only delete data, without deleting the table structure (as defined)

    Truncate table to delete all the rows in the table, but the table structure and its columns, constraints, indexes and so on remain unchanged.

    For tables referenced by the foreign key constraints, you can not use truncate table, and use the delete statement without a where clause. Since truncate table recorded in the log, it can not activate the trigger.

    delete statement is a database manipulation language (dml), ​​this operation will put rollback segement, the only take effect after the transaction commits; if there is a corresponding trigger, when the execution will be triggered.

    truncate, drop a database definition language (ddl), operating with immediate effect, the original data is not put rollback segment can not be rolled back, the operator does not trigger the trigger.

    If you have an auto increment, truncate delete mode after auto-increment value will be initialized, delete way points to the case (if the database is restarted, an auto-increment value will be initialized, the database is not restarted, unchanged)

  • If you want to remove table definition and its data, use the drop table statement

    Safety: Be careful using the drop and truncate, in particular when there is no backup, or too late to cry

  • Delete speed, in general: drop> truncate> delete

Deleting

not support

not support

stand by

Delete table structure

stand by

not support

not support

To delete a transactional way

not support

not support

stand by

The trigger is activated

no

no

Yes

drop truncate delete

Mysql series directory

    Day 1: mysql Basics

    Day 2: Detailed mysql data type (Key)

    Day 3: Administrator essential skills (must master)

    Day 4: DDL common operations

mysql series of about 20 articles, please look like!

Leave a Reply