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

This article is about what basic commands are commonly used in MySQL databases. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Use help information

If you log in to the database, there will be no introduction.

For example, if you want to do the operation of the created database, if you don't know the command, you can check the help information.

Mysql > help create

Many help items for your request exist.

To make a more specific request, please type 'help'

Where is one of the following

Topics:

CREATE DATABASE # the simplest create command

CREATE EVENT

CREATE FUNCTION

CREATE FUNCTION UDF

CREATE INDEX

CREATE LOGFILE GROUP

CREATE PROCEDURE

CREATE SERVER

CREATE TABLE

CREATE TABLESPACE

CREATE TRIGGER

CREATE USER

CREATE VIEW

SHOW

SHOW CREATE DATABASE

SHOW CREATE EVENT

SHOW CREATE FUNCTION

SHOW CREATE PROCEDURE

SHOW CREATE TABLE

SPATIAL

2. Create, delete and view the database

Mysql > create database test_data

# create a database of the default character set (Latin character set by default)

Query OK, 1 row affected (0.02 sec)

Mysql > show databases like "test%"

+-+

| | Database (test%) |

+-+

| | test_data |

+-+

1 rows in set (0.00 sec)

Create a database of the gbk character set

Mysql > create database test_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci

Query OK, 1 row affected (0.04 sec)

Mysql > show create database test_gbk

# View the statement for creating a database

+-+ +

| | Database | Create Database |

+-+ +

| | test_gbk | CREATE DATABASE `test_ gbk` / *! 40100 DEFAULT CHARACTER SET gbk * / | |

+-+ +

1 row in set (0.00 sec)

Delete database

Mysql > drop database test_data

Query OK, 0 rows affected (0.07 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | test_gbk |

+-+

3. Connect to the database

Mysql > use test_gbk

# equivalent to cd command, switch to database for operation

Database changed

Mysql > select database ()

# View the currently connected database, which is equivalent to pwd

+-+

| | database () |

+-+

| | test_gbk |

+-+

1 row in set (0.00 sec)

Mysql > select user ()

# View the users who are currently connected to the database, which is equivalent to whoami

+-+

| | user () |

+-+

| | root@localhost |

+-+

1 row in set (0.00 sec)

4. Create users, authorize, and reclaim permissions

When the database is created, users need to be created for people who need to connect to the database to use and operate the database. It is impossible for everyone to log in using root, so the permission setting is also very important.

Mysql > grant all on test_gbk.* to 'testuser'@'localhost' identified by' 123456'

# create a user and all permissions to all tables in the test_ gbk library with a password of '123456'

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

# refresh permissions to make them effective

Query OK, 0 rows affected (0.00 sec)

Mysql > show grants for 'testuser'@'localhost'

# check what permissions the user has

+-+

| | Grants for testuser@localhost |

+-+

| | GRANT USAGE ON *. * TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

| | GRANT ALL PRIVILEGES ON `test_ gbk`. * TO 'testuser'@'localhost' |

+-+

2 rows in set (0.00 sec)

Take back the authority

Mysql > revoke insert,update,select,delete on test_gbk.* from 'testuser'@'localhost'

# withdraw the above permissions

Query OK, 0 rows affected (0.00 sec)

Mysql > show grants for 'testuser'@'localhost'

+-+

| | Grants for testuser@localhost |

+-+

| | GRANT USAGE ON *. * TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

| | GRANT CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test_ gbk`. * TO 'testuser'@'localhost' |

+-+

2 rows in set (0.00 sec)

Note: we do not know what the all permissions are, but after using this method, it should be clear.

SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER

5. Create and delete tables

Mysql > create table test (id int (4) not null,name char (20) not null)

# create a table and create two fields

Query OK, 0 rows affected (0.06 sec)

Mysql > show tables;# View Table

+-+

| | Tables_in_test_gbk |

+-+

| | test |

+-+

1 row in set (0.00 sec)

Mysql > desc test; # View table structure

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (4) | NO | | NULL |

| | name | char (20) | NO | | NULL |

+-+ +

2 rows in set (0.01sec)

Mysql > create table test1 (id int (4) not null,name char (20) not null)

Query OK, 0 rows affected (0.01 sec)

Mysql > show tables

+-- +

| | Tables_in_test_gbk |

+-- +

| | test |

| | test1 |

+-+

2 rows in set (0.00 sec)

Delete tabl

Mysql > drop tables test

Query OK, 0 rows affected (0.00 sec)

Mysql > show tables

+-+

| | Tables_in_test_gbk |

+-+

| | test1 |

+-+

1 row in set (0.00 sec)

View table creation

Mysql > show create table test1\ G

* * 1. Row *

Table: test1

Create Table: CREATE TABLE `test1` (

`id`int (4) NOT NULL

`name` char (20) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

Thank you for reading! This is the end of this article on "what are the basic commands commonly used in MySQL database". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.