在linux环境中的MySQL服务，是区分大小写的，如果创建库时是test，那么就只能用test，而不能写出 Test。
一般情况下，mysql的语句（不仅是sql，还有mysql内部的命令）都要以 分号 或者 G 来结尾，只有 use 语句。
(system@localhost) [(none)]> show warnings;
+-------+------+----------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------+
| Note | 1008 | Can't drop database 'test'; database doesn't exist |
+-------+------+----------------------------------------------------+
1 row in set (0.01 sec)
查看帮助文档：
(system@localhost) [mysql]> ? create table
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
另一种查看帮助的方法：
(system@localhost)[test]> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
一、数据库
1、显示当前连接用户,有权限访问的数据库
(system@localhost) [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.17 sec)
2、删除数据库
(system@localhost) [(none)]> drop database test;
Query OK, 0 rows affected (0.09 sec)
(system@localhost) [(none)]> drop database test ;
ERROR 1008 (HY000): Can't drop database 'test'; database doesn't exist
(system@localhost) [(none)]> drop database if exists test;
Query OK, 0 rows affected, 1 warning (0.00 sec)
(system@localhost) [(none)]> show warnings;
+-------+------+----------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------+
| Note | 1008 | Can't drop database 'test'; database doesn't exist |
+-------+------+----------------------------------------------------+
1 row in set (0.01 sec)
3、创建数据库
(system@localhost) [(none)]> create database test;
Query OK, 1 row affected (0.06 sec)
每个数据库就是OS层的一个目录，也就是data目录下：mysql、performance_schma、test 3个数据库，
而information_schema是由mysql实例在启动之后，内存中创建的虚拟数据库，所以这里没有显示。
[root@wc1 Desktop]# ll /data/mysqldata/3306/data
total 2852884
-rw-rw----. 1 mysql mysql 56 Aug 26 14:11 auto.cnf
-rw-rw----. 1 mysql mysql 2116026368 Aug 29 13:44 ibdata1
-rw-rw----. 1 mysql mysql 268435456 Aug 29 13:44 ib_logfile0
-rw-rw----. 1 mysql mysql 268435456 Aug 26 14:05 ib_logfile1
-rw-rw----. 1 mysql mysql 268435456 Aug 26 14:05 ib_logfile2
drwx------. 2 mysql mysql 4096 Aug 26 14:05 mysql
drwx------. 2 mysql mysql 4096 Aug 26 14:05 performance_schema
drwx------. 2 mysql mysql 4096 Aug 31 18:09 test
在OS中创建1个目录ggg，那么在mysql中就能看到这个数据库。
[root@wc1 Desktop]# mkdir /data/mysqldata/3306/data/ggg
(system@localhost) [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ggg |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.03 sec)
发现之前创建的test数据库，在OS层的目录里有一个文件：db.opt,
内容是：字符集、排序规则，都继承于：@@character_set_database,@@collation_database。
但是对于ggg数据库也会继承这些熟悉，只是没有这个db.opt文件了，当然，也可以把test数据库的db.opt文件复制到ggg库。
[root@wc1 Desktop]# ls -al /data/mysqldata/3306/data/test
total 12
drwx------. 2 mysql mysql 4096 Aug 31 18:09 .
drwxrwxr-x. 6 mysql mysql 4096 Aug 31 18:14 ..
-rw-rw----. 1 mysql mysql 61 Aug 31 18:09 db.opt
[root@wc1 Desktop]# more /data/mysqldata/3306/data/test/db.opt
default-character-set=utf8
default-collation=utf8_general_ci
(system@localhost) [(none)]> select @@character_set_database,@@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8 | utf8_general_ci |
+--------------------------+----------------------+
1 row in set (0.01 sec)
查看数据库字符集
方法1：
(system@localhost) [(none)]> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.02 sec)
(system@localhost) [(none)]> show create database ggg;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| ggg | CREATE DATABASE `ggg` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
方法2：
(system@localhost) [(none)]> select * from information_schema.schemata;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def | information_schema | utf8 | utf8_general_ci | NULL |
| def | ggg | utf8 | utf8_general_ci | NULL |
| def | mysql | utf8 | utf8_general_ci | NULL |
| def | performance_schema | utf8 | utf8_general_ci | NULL |
| def | test | utf8 | utf8_general_ci | NULL |
+--------------+--------------------+----------------------------+------------------------+----------+
5 rows in set (0.02 sec)
二、表
1、切换数据库
(system@localhost) [(none)]> use mysql
Database changed
2、显示表
(system@localhost) [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
3、创建表
(system@localhost)[(none)]> use test
Database changed
(system@localhost)[test]> create table users(
-> username varchar(10),
-> sex tinyint,
-> birth date,
-> address varchar(50),
-> phoneno varchar(15));
Query OK, 0 rows affected (0.08 sec)
(system@localhost)[test]> create table ggg.users(
-> username varchar(10),
-> sex tinyint,
-> birth date,
-> address varchar(50),
-> phoneno varchar(15));
Query OK, 0 rows affected (0.05 sec)
(system@localhost)[test]> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
(system@localhost)[test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| users |
+----------------+
1 row in set (0.02 sec)
查看表结构的2种方法：
(system@localhost)[test]> desc users;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | YES | | NULL | |
| sex | tinyint(4) | YES | | NULL | |
| birth | date | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| phoneno | varchar(15) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
(system@localhost)[test]> show columns from ggg.users;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | YES | | NULL | |
| sex | tinyint(4) | YES | | NULL | |
| birth | date | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| phoneno | varchar(15) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
查看索引情况：
(system@localhost)[test]> show index from mysql.db;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| db | 0 | PRIMARY | 1 | Host | A | NULL | NULL | NULL | | BTREE | | |
| db | 0 | PRIMARY | 2 | Db | A | NULL | NULL | NULL | | BTREE | | |
| db | 0 | PRIMARY | 3 | User | A | 0 | NULL | NULL | | BTREE | | |
| db | 1 | User | 1 | User | A | NULL | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.01 sec)
查看建表语句：
(system@localhost)[test]> show create table test.usersG
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`username` varchar(10) DEFAULT NULL,
`sex` tinyint(4) DEFAULT NULL,
`birth` date DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
`phoneno` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
4、修改表：
Innodb 数据库引擎，是通过create+drop来变更表结构，
所以开销会很大。
因此，通过把多个修改需求合并成单个语句，来减少操作，提高效率。
增加列
(system@localhost)[test]> alter table users add(email varchar(50),salary smallint);
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除列
(system@localhost)[test]> alter table users drop salary;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
可以同时修改 列名、列定义
(system@localhost)[test]> alter table users change phoneno phone varchar(20);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
只能修改列定义
(system@localhost)[test]> alter table users modify phone varchar(15);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
(system@localhost)[test]> desc users;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | YES | | NULL | |
| sex | tinyint(4) | YES | | NULL | |
| birth | date | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| phone | varchar(15) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
5、删除表
(system@localhost)[test]> drop table users;
Query OK, 0 rows affected (0.05 sec)
重命名表，同时把表从ggg库 移到 test库。
(system@localhost)[test]> rename table ggg.users to test.users;
Query OK, 0 rows affected (0.08 sec)
没有权限，报错了。。。
(system@localhost)[test]> rename table information_schema.tables to tables;
ERROR 1044 (42000): Access denied for user 'system'@'localhost' to database 'information_schema'