MySQL Four: Data manipulation

Read Catalogue

• An introduction
• Two Insert data inserts
• Three Update data updates
• Four delete data deletes
• Five query data Select
• Six Rights Management

An introduction

MySQL Data manipulation: DML

========================================================

In MySQL management software, you can use the DML language in SQL statements to implement data manipulation, including

1. Inserting data by using insert
2. Update to implement data updates
3. Using Delete to implement data deletion
4. Use Select to query data and.

========================================================

This section includes the following:

Inserting data
Update data
Delete data
Querying data

Two Insert data inserts

1. Insert full data (sequential insert)    syntax One:    insert into table name (Field 1, Field 2, Field 3 ...) field N) VALUES (value 1, value 2, value 3 ...) Value n);    Syntax two:    INSERT into table name values (value 1, value 2, value 3 ...) Value n); 2. Specify field Insert data    syntax:    insert INTO table name (Field 1, Field 2, Field 3 ...) Values (value 1, value 2, value 3 ...); 3. Insert multiple record    syntax:    insert INTO table name values        (value 1, value 2, value 3 ...) Value n),        (value 1, value 2, value 3 ...) Value n),        (value 1, value 2, value 3 ...) Value n);         4. Insert Query result    syntax:    insert INTO table name (Field 1, Field 2, Field 3 ...) Field N)                     SELECT (Field 1, Field 2, Field 3 ...) field N) from table 2                    WHERE ...;

Three Update data updates

Syntax:    Update table name set        field 1= value 1,        field 2= value 2,        where CONDITION; Example:    UPDATE mysql.user Set Password =password (' 123')         where userand host= ' localhost ';

Four delete data deletes

Syntax:    Delete from table name         where conition; Example:    DELETE from Mysql.user         where password="; Practice:    Update the MySQL root user password for mysql123    remove all users except the root user logged on locally

Five query data Select

Single-table query: http://www.cnblogs.com/llhtjwq/p/8306743.html

Multi-table query: http://www.cnblogs.com/llhtjwq/p/8306766.html

Six Rights Management

# Authorization Form # permission for this table to be released for: All data, all tables under all libraries, and all fields under the table # permission for this table to be released for: a database, all tables under that database, and all fields under the table # the permissions that the table releases. For: A table, and all fields under that table # the permission for this table to be released, for: a field # explained by the figure:User: Release DB1, DB2 and all of the db contained in it: Release DB1, and all Tables_priv included with DB1: Release Db1.table1, and all columns_prive included in the table: Release Db1.table1.column1, release only this field

#Create userCreate user'Egon'@'1.1.1.1'Identified by'123'; Create user'Egon'@'192.168.1.%'Identified by'123'; Create user'Egon'@'%'Identified by'123';#Authorization: Permissions on a folder, on a file, on a field of a fileView Help: Select,update,alter,deleteall can represent all permissions except grant:#Authorization for All libraries: *. *Grant SELECT On *. * To'Egon1'@'localhost'Identified by'123';#only the user table can find that the SELECT permission for Egon1 users is set to Y#for a database: db1.*Grant SELECT on db1.* to'Egon2'@'%'Identified by'123';#only the DB table can be traced to the Egon2 user's SELECT permission is set to Y#for a table: Db1.t1Grant SELECT on Db1.t1 to'Egon3'@'%'Identified by'123';#only SELECT permissions for Egon3 users can be found in the Tables_priv table#for a field:Mysql> SELECT * fromT3;+------+-------+------+| ID | name |    Age |+------+-------+------+| 1 |   Egon1 |    18 | | 2 |   Egon2 |    19 | | 3 |   Egon3 | |+------+-------+------+Grant Select (id,name), update (age) on DB1.T3 to'Egon4'@'localhost'Identified by'123'; #you can see the appropriate permissions in Tables_priv and Columns_privMysql> SELECT * fromTables_priv where user='Egon4'\g1. Row ***************************host:localhost db:db1 user:egon4 table_name:t3 grantor: [email protected] Timestamp: 0000-00-00 00:00:00Table_priv:column_priv:select,updaterowinchSet (0.00sec) MySQL> select * fromColumns_priv where user='Egon4'\g1. Row ***************************host:localhost db:db1 user:egon4 Table_name:t3Column_name:id Timestamp:0000-00-00 00:00:00Column_priv:select2. Row ***************************host:localhost db:db1 user:egon4 Table_name:t3Column_name:name Timestamp:0000-00-00 00:00:00Column_priv:select3. Row ***************************host:localhost db:db1 user:egon4 Table_name:t3Column_name:age Timestamp:0000-00-00 00:00:00column_priv:updaterowsinchSet (0.00sec)#Delete PermissionsRevoke select on db1.* to'Alex'@'%';

permission-related actions

MySQL Four: Data manipulation