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

Let's talk about the SQL instruction set of MySQL database management tools. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on the SQL instruction set of MySQL database management tools.

1. View database list information

Show databases

2. Enter the database

Use mysql (database name mysql)

3. View the data sheet

Show table info (table name info)

4. Display table structure information (fields)

Describe user (table user); (1) where PRI is the primary key (cannot be null) definition-- determines the identification characteristics of unique entity objects in the table-- uniqueness, non-emptiness (2) where Extra is a constraint.

5. Create a database

Create database named (library name named) SQL statement SQL language ● is the abbreviation of Structured Query Language, that is, structured query language ● is the standard language for relational databases ● is used to maintain and manage databases, such as data query, data update, access control, object management and other functions SQL classification ● DDL: data definition language: DDL statements are used to create database objects Such as libraries, tables, indexes and other ● 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, those smaller than him on the left, larger than him on the right. DDL operation command

1. Create new libraries and tables with DDL statements

Create database auth:mysql > create database auth;Query OK, 1 row affected (0.00 sec) create data table: 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,-> grade decimal default 0,-> primary key (ID); Query OK, 0 rows affected (0 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 | | Grade | decimal (10Force 0) | YES | 0 | | +-+-| -+ 4 rows in set (0.00 sec)

2. Delete libraries and tables with DDL statement

Delete the specified data table: drop database table [database name.] Table name delete the specified database: drop database (database name database) mysql > drop database auth;Query OK, 0 rows affected (0.05 sec) 2, DML operation command DML statement to manage the data in the table includes the following operations. ● inster: insert 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 (value of field 1, value of field,.); mysql > insert into info values (1 row affected 'Zhou Mei'er', 'Nanjing', 80); Query OK, 1 row affected (0. 00 sec) mysql > insert into info values (2 sec '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)

(2) 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) Delete the specified data record in the data table

Delete from table name where conditional expression; statement without where condition indicates deletion of all records in the table (caution); # Delete the specified data record in the table mysql > delete from info where ID=2; 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) 3, DQL operation command DQL is a data query statement, there is only one: SELECT is used to find qualified data records from the data table

1. No conditions can be specified when querying

Select 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) 4, 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)

2. View the permissions of the user

SHOW GRANTS FOR username @ 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)

Is there anything you don't understand about the SQL instruction set of the above MySQL database management tools? Or if you want to know more about it, you can continue to follow our industry information section.

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.