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

Below to bring you about the most commonly used MySQL database to the basic operation command content, I believe you must have seen similar articles. What's the difference between what we bring to everyone? Let's take a look at the body. I believe you will gain something after reading the basic operation commands that are most commonly used in the MySQL database.

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) access to 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-- determine the identification characteristics of the unique entity object in the table-- uniqueness, non-emptiness (2) where Extra is the constraint mysql > describe user +-- +-- +-+ | Field | Type | | Null | Key | Default | Extra | + -+-+ | Host | char (60) | NO | PRI | User | char (30) | NO | PRI | Select_priv | enum ('N') | '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 database Such as data query, data update, access control, object management and other functions 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, smaller than him on the left, bigger than him on the right. DDL operation commands 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 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.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 | | Grade | decimal (10Force 0) | 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 DML statement to manage the data in the table includes the following operations. ● insert: 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)

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; 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 sec) DQL operation command DQL is a data query statement with only one entry: SELECT is 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 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)

Do you think you want the basic operation commands that are most commonly used in MySQL databases above? 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.