MySQL數(shù)據(jù)庫基礎篇之入門基礎命令小結(jié)

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 #最簡單的創(chuàng)建命令
 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;
#創(chuàng)建默認字符集的數(shù)據(jù)庫（默認是拉丁字符集）
Query OK, 1 row affected (0.02 sec)
mysql> show databases like "test%";
+------------------+
| Database (test%) |
+------------------+
| test_data  |
+------------------+
1 rows 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; 
#查看創(chuàng)建數(shù)據(jù)庫的語句
+----------+----------------------------------------------+
| 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;
#相當于cd命令，切換到數(shù)據(jù)庫進行操作
Database changed
mysql> select database();
#查看當前連接的數(shù)據(jù)庫，相當于pwd
+------------+
| database() |
+------------+
| test_gbk |
+------------+
1 row in set (0.00 sec)
mysql> select user();

#查看當前連接數(shù)據(jù)庫的用戶,相當于whoami
+--------------------+
| user()    |
+-------------------+
| root@localhost |
+--------------------+
1 row in set (0.00 sec)

mysql> grant all on test_gbk.* to 'testuser'@'localhost' identified by '123456';
#創(chuàng)建用戶并all權(quán)限給在test_gbk庫所有表，密碼‘123456'
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
#刷新權(quán)限，使權(quán)限生效
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'testuser'@'localhost';
#查看用戶有哪些權(quán)限
+-----------------------------------------------------------------------------------------------------------------+
| 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';
#將以上權(quán)限收回
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);
#建表，并且建立兩個字段
Query OK, 0 rows affected (0.06 sec)
mysql> show tables;#查看表
+--------------------+
| Tables_in_test_gbk |
+--------------------+
| test      |
+--------------------+
1 row in set (0.00 sec)
mysql> desc test; #查看表結(jié)構(gòu)
+-------+----------+------+-----+---------+-------+
| 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)