Shulou(Shulou.com)06/02 Report--

Basic operation command

1. View database list information

Show databases

Mysql > show databases

+-+ | Database |

+-+ | information_schema | | mysql | | performance_schema | | sys |

+-+ 4 rows in set (0.11 sec)

2. View the data table information in the database

(1) enter the database use mysql

Mysql > use mysql

Database changed

(2) View the data sheet

Show tables

Mysql > show tables

+-+

| | Tables_in_mysql |

+-+

| | columns_priv |

| | db |

| | engine_cost |

| | event |

.

3. Display table structure information (fields)

Describe user

(1) where PRI is the primary key (cannot be empty)

Definition-determines the identity of the unique entity object in the table

Characteristics-uniqueness and non-emptiness

(2) where Extra is a constraint

Mysql > describe user

+-+-+ | Field | Type | Null | Key | Default | Extra |

+-+

| | Host | char (60) | NO | PRI | | User | char (30) | NO | PRI |

| | Select_priv | enum ('Nickel Magazine Y') | NO | | N | |

.

4. Create a database

Create database named

Mysql > create database auth

Query OK, 1 row affected (0.00 sec)

SQL statement

QL language

● is the abbreviation of Structured Query Language, that is, structured query language.

● is the standard language of relational database.

● is used to maintain and manage databases, such as data query, data update, access control, object management, etc.

SQL classification

● DDL: data definition language ● DML: data manipulation language ● DQL: data query language ● DCL: data control language

Typical database index algorithm-binary search

Definition: take a data as a reference, put the smaller ones on the left and the larger ones on the right.

DDL operation command

DDL statements are used to create database objects, such as libraries, tables, indexes, etc.

1. Use DDL statements to create new libraries and tables

Create database: creste databae database name

Mysql > create database auth

Query OK, 1 row affected (0.00 sec)

Create a datasheet: create table table name (field definition …)

Mysql > create table info (

-> ID int (4) not null

-> name varchar (8) not null

-> address varchar (10) not null

-> score decimal default 0

-> primary key (ID))

Query OK, 0 rows affected (0.01 sec)

Mysql > desc info

+-+ | Field | Type | Null | Key | Default | Extra |

+-+ +

| | ID | int (4) | NO | PRI | NULL | | name | varchar (8) | NO | | NULL |

| | address | varchar (10) | NO | | NULL | | result | decimal (1010) | YES | | 0 |

+-+ +

4 rows in set (0.00 sec)

two。 Delete libraries and tables using DDL statements

Delete the specified data table: drop table [database name.] Table name

Delete the specified database: drop database database name

Mysql > drop database auth

Query OK, 0 rows affected (0.05 sec)

DML operation command

The DML statement is used to manage the data in the table

This includes the following operations.

● insert: inserting new data

● update: update existing data

● delete: delete unwanted data

1. Insert a new data record into the data table

Insert into table name (field 1, field 2,.) Values (the value of field 1, the value of field,.)

Mysql > insert into info values (1) Zhou Mei'er, Nanjing, 80)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into info values (2) 'Zhang Qiandi', 'Nanjing', 66)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into info values (3)'Li Xiangyang', 'Shanghai', default)

Query OK, 1 row affected (0.02 sec)

two。 Modify and update the data records of data table P F

Update table name set field name 1 = value 1 [, field name 2 = value 2] where conditional expression

Mysql > update info set address = 'Nanjing' where ID=3

Query OK, 1 row affected (0.06 sec)

Rows matched: 1 Changed: 1 Warnings: 0

3. Deletes the specified data record in the data table

Delete from table name where conditional expression

Statements without where conditions indicate that all records in the table are deleted (proceed with caution)

# Delete the specified data record mysql > delete from info where ID=2 in the table

Query OK, 1 row affected (0.02 sec)

# Delete table mysql > drop table info

Query OK, 0 rows affected (0.00 sec)

# Delete library mysql > drop database test

Query OK, 0 rows affected (0.00 sec)

DQL operation command

DQL is a data query statement with only one item: SELECT

Used to find qualified data records from the data table

1. No conditions can be specified when querying

Selext field name 1, field name 2. From table name

Mysql > select * from info

+-+ | ID | name | address | score |

+-- +

| | 1 | Zhou Mei'er | Nanjing | 80 | | 3 | Li Xiangyang | Nanjing | 0 |

+-- +

2 rows in set (0.00 sec)

two。 Specify conditions when querying

Select field name 1, field name 2. From table name where conditional expression

Mysql > select address from info where address = 'Nanjing'

+-+

| | address |

+-+

| | Nanjing |

| | Nanjing |

+-+ 2 rows in set (0.04 sec)

DCL operation command

1. Set user permissions (create a new user when the user does not exist)

GRANT permission list ON database name. Table name TO username @ source address [IDENTIFIED BY 'password']

Mysql > grant all privileges on. To 'root'@'%' identified by' abc123'

Query OK, 0 rows affected, 1 warning (0.08 sec)

two。 View the user's permissions

SHOW GRANTS FOR user name @ Source address

Mysql > show grants for 'root'@'%'

+-- +

| | Grants for root@% |

+-- +

| GRANT ALL PRIVILEGES ON. TO 'root'@'%' WITH GRANT OPTION |

+-+ 1 row in set (0.00 sec)

3. Revoke the permissions of a user

REVOKE permission list ON database name. Table name FROM user name @ Source address

Mysql > revoke all on. From 'root'@'%'

Query OK, 0 rows affected (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.