MySQL Database Basics: A Summary of Basic Commands

mysql> help create;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> 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

mysql> create database test_data;
#Create a database with the default character set (the default is Latin character set)
Query OK, 1 row affected (0.02 sec)
mysql> show databases like "test%";
+------------------+
| Database (test%) |
+------------------+
| test_data |
+------------------+
1 row in set (0.00 sec)

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 to create a database+----------+----------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| test_gbk | CREATE DATABASE `test_gbk` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> drop database test_data;
Query OK, 0 rows affected (0.07 sec)
mysql> show databases;
+----------------------------+
| Database |
+----------------------------+
| information_schema |
| test_gbk |
+----------------------------+

mysql> use test_gbk;
#Equivalent to the cd command, switch to the database for operation Database changed
mysql> select database();
#View the currently connected database, equivalent to pwd
+------------+
| database() |
+------------+
| test_gbk |
+------------+
1 row in set (0.00 sec)
mysql> select user();

#View the user currently connected to the database, equivalent to whoami
+--------------------+
| user() |
+-------------------+
| root@localhost |
+--------------------+
1 row in set (0.00 sec)

mysql> grant all on test_gbk.* to 'testuser'@'localhost' identified by '123456';
#Create a user and give 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 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)

mysql> revoke insert,update,select,delete on test_gbk.* from 'testuser'@'localhost';
# Revoke 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)

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 the table+--------------------+
| Tables_in_test_gbk |
+--------------------+
| test |
+--------------------+
1 row in set (0.00 sec)
mysql> desc test; #View the 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)

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)

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)