【MySQL学习笔记】MySQL库与表

在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.users\G
*************************** 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'