Shulou(Shulou.com)05/31 Report--

This article is to share with you what are the basic operation commands of MySQL database. 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

Login to the database will not be introduced, for example, if you want to do the operation of the created database, if you do not 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 creation 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 default character sets (default is Latin character set) 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 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; # is 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, 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' 123456permissions; # create a user and all permissions to all tables in the test_ gbk database, password '123456' Query OK, 0 rows affected (0.00 sec) mysql > flush privileges; # refresh permissions, make permissions 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 from 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.01 sec) 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 operation commands of 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.