MySQL Four: Data manipulation

First, Introduction

MySQL Data manipulation: DML Database Manipulation language

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

Inserting data by using insert

Update to implement data updates

Using Delete to implement data deletion

Use Select to query data and

The main contents include:

Inserting data

Update data

Delete data

Querying data

Inserting data Insert

1. Insert full data (sequential insertion)

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 fields to insert data

Grammar:

INSERT into table name (Field 1, Field 2, Field 3 ...) Values (value 1, value 2, value 3 ...);

3. Inserting more than one record

Grammar:

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 Results

Grammar:

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 ...;

Third, update data updates

Grammar:

UPDATE Table name SET

field 1= value 1,

field 2= value 2,

WHERE CONDITION;

Example:

UPDATE mysql.user SET Password=password (' 123 ') where user= ' root ' and host= ' localhost ';

Iv. Deleting data Delete

Grammar:

DELETE from table name WHERE conition;

Example:

DELETE from Mysql.user WHERE password= ';

Practice:

Update MySQL root user password to mysql123

UPDATE mysql.user SET password=password (' mysql123 ') where user= ' root ';

Remove all users except the root user logged on locally

Drop Mysql.user [email protected] ' localhost '

DELETE from Mysql.user WHERE user= ' root ' and host= ' localhost ';

Five, query data select

Single-Table query:

Multi-Table query:

VI. Rights Management

1. Authorization Form

User #该表放行的权限 for: All data, all tables under all libraries, and all fields under the table

DB #该表放行的权限 for: a database, all tables under that database, and all fields under the table

Tables_priv #该表放行的权限. For: A table, and all fields under the table

Columns_priv #该表放行的权限, for: a field

2, according to the figure explanation:

User: Release db1,db2 and all of its included

DB: Release DB1, and its db1 contains all

Tables_priv: Release Db1.table1, and all of the tables contained in the

Columns_prive: Release db1.table1.column1, release this field only

3. Permission-related actions

Create user

Create user ' test ' @ ' 1.1.1.1 ' identified by ' test ';

Create user ' test ' @ ' 192.168.1.% ' identified by ' test ';

Create user ' test ' @ '% ' identified by ' test ';

Authorization: Permissions on a folder, on a file, on a field of a file

View Help: Helping Grant

Common permissions are: Select,update,alter,delete

All can represent all permissions except Grant

Authorization for All libraries: *. *

Grant SELECT On *. test1 ' @ ' localhost ' identified by ' test2 '; #只在user表中可以查到test用户的select权限被设置为Y

For a database: db1.*

Grant SELECT on db1.* to ' test2 ' @ '% ' of ' identified by ' test2 '; #只在db表中可以查到test2用户的select权限被设置为Y

For a table: Db1.t1

Grant SELECT on Db1.t1 to ' test3 ' @ '% ' of ' identified by ' test3 '; #只在tables_priv表中可以查到test3用户的select权限

For a field:

Grant Select (id,name), update (age) on Db1.t3 to ' test4 ' @ ' localhost ' identified by ' test4 '; #可以在tables_priv和columns_priv中看到相应的权限

SELECT * from Tables_priv where user= ' test4 ' \g

SELECT * from Columns_priv where user= ' test4 ' \g

Delete permissions

Revoke select on db1.* from ' Test ' @ '% ';

MySQL Four: Data manipulation