MySQL DML operation--------Curd best combat

1. Background

* curd operations are typically done using the Structured Query Language (structured Query language,sql) in a relational database system

* Curd defines the basic atomic operations used to process the data

* Curd represents creation (create), update, read (Retrieve), and delete operations.

2. CREATE TABLE Actions

* CREATE DATABASE (db) MyTest

CHARACTER Set: setting character sets

mysql> CREATE DATABASE mytest CHARACTER SET utf8mb4; Query OK, 1 row Affected (0.00 sec)

* Create tables in database (table)

　　 Engine=innodb specifying the INNODB storage engine

charset=utf8mb4 Setting the table character set

Mysql> CREATE TABLE users, id BIGINT PRIMARY KEY not NULL auto_increment, name VARCHAR (+) NOT NULL, -A sex ENUM (' M ', ' F ') not NULL,--------------------"Engine=innodb charset=utf8mb4 Query OK, 0 rows affected (0.03 sec)

3. Inserting Data operations

* Select Inserts a single data

Mysql> INSERT into users SELECT NULL, ' Tom ', ' M ', 29; Query OK, 1 row affected (0.01 sec) records:1 duplicates:0 warnings:0mysql> SELECT * FROM users;+----+------+-----+ -----+| ID | name | sex |  Age |+----+------+-----+-----+| 1 | Tom |  M | |+----+------+-----+-----+1 row in Set (0.00 sec)

* values Insert a single piece of data

Mysql> INSERT into Users VALUES (NULL, ' jak ', ' F ', 33); Query OK, 1 row affected (0.01 sec) mysql> Select * FROM users;+----+------+-----+-----+| ID | name | sex |  Age |+----+------+-----+-----+| 1 | Tom |  M |  29 | | 2 | Jak |  F | |+----+------+-----+-----+2 rows in Set (0.00 sec)

* Select Specify column insert [ID column will increment]

Mysql> INSERT into the users (name, sex, age) SELECT ' sea ', ' M ', ' 26 '; Query OK, 1 row affected (0.01 sec) records:1 duplicates:0 warnings:0mysql> SELECT * FROM users;+----+------+-----+ -----+| ID | name | sex |  Age |+----+------+-----+-----+| 1 | Tom |  M |  29 | | 2 | Jak |  F |  33 | | 3 | Sea |  M | |+----+------+-----+-----+3 rows in Set (0.01 sec)

* values Specify column insertion

Mysql> INSERT into the users (name, sex, age) VALUES (' Hai ', ' F ', ' 18 '); Query OK, 1 row affected (0.02 sec) mysql> SELECT * FROM users;+----+------+-----+-----+| ID | name | sex |  Age |+----+------+-----+-----+| 1 | Tom |  M |  29 | | 2 | Jak |  F |  33 | | 3 | Sea |  M |  26 | | 4 | Hai |  F | |+----+------+-----+-----+4 rows in Set (0.00 sec)

   * values insert multiple data

mysql> insert into users values  (null,  ' test1 ',  ' F ',  23),  ( null,  ' test2 ',  ' M ',  34); query ok, 2 rows affected  (0.01 sec) records: 2  duplicates:  0  warnings: 0mysql> select * from users;+----+-------+-----+-----+|  id | name  | sex | age |+----+-------+-----+-----+|  1  | tom   | m   |  29 | |   2 | jak   | f   |  33 | |   3 | sea   | m   |  26 | |   4 | hai   | f   |  18 | |   5 | test1 | f   |  23 | |   6 | test2 | m   |  34 |+----+-------+-----+-----+6 rows in set  (0.00 sec) 

* values Specify columns to insert multiple data

Mysql> insert into users (name, sex, age)  VALUES  (' user1 ',  ' F ',  23),  (' User2 ',  ' M ',  34); query ok, 2 rows affected  (0.01 sec) records: 2  duplicates:  0  warnings: 0mysql> select * from users;+----+-------+-----+-----+|  id | name  | sex | age |+----+-------+-----+-----+|  1  | tom   | m   |  29 | |   2 | jak   | f   |  33 | |   3 | sea   | m   |  26 | |   4 | hai   | f   |  18 | |   5 | test1 | f   |  23 | |   6 | test2 | m   |  34 | |   7 | user1 | f   |  23 | |   8 | user2 | m   |  34 |+----+-------+-----+----- +8 rows in set  (0.00 SEC)

4. Modifying Data Operations

Update <table_name>

Set column = Val, ....

WHERE Condition statement ( no write condition statement modifies all data in the table )

* One-time modification of single-column data operations

Mysql> SELECT * from users where id = 1;+----+------+-----+-----+| ID | name | sex |  Age |+----+------+-----+-----+| 1 | Tom |  M | |+----+------+-----+-----+1 row in Set (0.00 sec) mysql> UPDATE users set name= ' Lisea ' where id = 1; Query OK, 1 row affected (0.02 sec) Rows matched:1 changed:1 warnings:0mysql> select * from users where id = 1;+--- -+-------+-----+-----+| ID | name | sex |  Age |+----+-------+-----+-----+| 1 | Lisea |  M | |+----+-------+-----+-----+1 row in Set (0.00 sec)

   *

Mysql> SELECT * from users where id = 1;+----+-------+-----+-----+| ID | name | sex |  Age |+----+-------+-----+-----+| 1 | Lisea |  M | |+----+-------+-----+-----+1 row in Set (0.01 sec) mysql> UPDATE users set sex= ' F ', age=33 where id = 1; Query OK, 1 row affected (0.01 sec) Rows matched:1 changed:1 warnings:0mysql> select * from users where id = 1;+--- -+-------+-----+-----+| ID | name | sex |  Age |+----+-------+-----+-----+| 1 | Lisea |  F | |+----+-------+-----+-----+1 row in Set (0.00 sec)

5. Delete Data operations

Delete from <table_name>

　 Where Condition statement [ No write condition statement modifies all data in table ]

mysql> select * from users;+----+-------+-----+-----+| id | name   | sex | age |+----+-------+-----+-----+|  1 | lisea |  f   |  33 | |   2 | jak   | f   |  33 | |   3 | sea   | m   |  26 | |   4 | hai   | f   |  18 | |   5 | test1 | f   |  23 | |   6 | test2 | m   |  34 | |   7 | user1 | f   |  23 | |   8 | user2 | m   |  34 |+----+-------+-----+----- +8 rows in set  (0.00 sec) MYSQL> DELETE FROM&NBsp;users where id = 1; query ok, 1 row affected  (0.02 sec) mysql> select * from  users;+----+-------+-----+-----+| id | name  | sex | age |+----+- ------+-----+-----+|  2 | jak   | f   |  33  | |   3 | sea   | m   |  26 | |   4 | hai   | f   |  18 | |   5 | test1 | f   |  23 | |   6 | test2 | m   |  34 | |   7 | user1 | f   |  23 | |   8 | user2 | m   |  34 |+----+-------+-----+----- +7 rows in set  (0.00 sec) Mysql> delete from users where id in  (2, 4); query ok, 2 rows affected  (0.01 sec) mysql> select * from  users;+----+-------+-----+-----+| id | name  | sex | age |+----+- ------+-----+-----+|  3 | sea   | m   |  26  | |   5 | test1 | f   |  23 | |   6 | test2 | m   |  34 | |   7 | user1 | f   |  23 | |   8 | user2 | m   |  34 |+----+-------+-----+----- +5 rows in set  (0.00 sec) mysql> delete from users where  id >= 7; query ok, 2 rows affected  (0.01 sec) mysql> select  * from users;+----+-------+-----+-----+| id | name  | sex |  age |+----+-------+-----+-----+|  3 | sea   | m    |  26 | |   5 | test1 | f   |  23 | |   6 | test2 | m   |  34 |+----+-------+-----+----- +3 rows in set  (0.00 SEC)

6. Summary

To demand-driven technology, the technology itself does not have a better point, only the division of business.

This article is from the "Sea" blog, be sure to keep this source http://lisea.blog.51cto.com/5491873/1943701

MySQL DML operation--------Curd best combat