MySQL管理库与表

1.Mysql中的表是基于库维护的，也就是说它属于某个库，不管对象是有谁创建的，只要库在表就在。

2.当配置好一套Mysql数据库服务后，系统就默认创建4个库：

                information_schema:记录用户，表，视图等元数据信息；这个库是虚拟出来的库，是由Mysql实例构建和维护的，其对象都保存在内存中的，磁盘上找不到对应的物理存在，用户也无法在该库下创建对象，

                mysql：记录用户的权限，日志，帮助等信息；

                performance_schema:MySQL服务性能指标库。

3.查看当前存在的数据库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.09 sec)

4.创建数据库

mysql> create database gis;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| gis                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

查看数据库对应的数据目录：

[root@localhost ~]# cat /etc/my.cnf
[root@localhost mysql]# ls
auto.cnf    client-cert.pem  ib_buffer_pool  ib_logfile1  mysql.sock          private_key.pem  server-key.pem
ca-key.pem  client-key.pem   ibdata1         ibtmp1       mysql.sock.lock     public_key.pem   sys
ca.pem      gis              ib_logfile0     mysql        performance_schema  server-cert.pem

[root@localhost mysql]# cd gis/
[root@localhost gis]# ls
db.opt
[root@localhost gis]# cat db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci

在数据目录下创建一个目录测试：Mysql将其视为一个数据库，和语句不同的地方就是，语句创建的文件夹下面有个db.opt文件

[root@localhost mysql]# mkdir sanwei
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| gis                |
| mysql              |
| performance_schema |
| sanwei             |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

 5.查看数据库设置的字符集

mysql> show create database sanwei;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| sanwei   | CREATE DATABASE `sanwei` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

还有一种方法

mysql> 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          | gis                | latin1                     | latin1_swedish_ci      | NULL     |
| def          | mysql              | latin1                     | latin1_swedish_ci      | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | sanwei             | latin1                     | latin1_swedish_ci      | NULL     |
| def          | sys                | utf8                       | utf8_general_ci        | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
6 rows in set (0.00 sec)

 6.选定数据库，进入数据库

mysql> use gis;
Database changed

查看当前用户及当前数据库

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)

mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

查看当前数据库下的表

mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |

7.创建表

mysql> create table gis.gisuser(username varchar(10),sex tinyint,birth date,address varchar(50),phone varchar(15));
Query OK, 0 rows affected (0.04 sec)

查看表：

mysql> use gis;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_gis |
+---------------+
| gisuser       |
+---------------+
1 row in set (0.00 sec)

查看表结构：

mysql> desc gisuser;
+----------+-------------+------+-----+---------+-------+
| 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    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

查看表创建语句：

mysql> show create table gisuser;
+---------+------------------------------------------------------------------------------------------------------------------------------
| Table   | Create Table                                                                                                                                                                                                                                  
+---------+------------------------------------------------------------------------------------------------------------------------------
| gisuser | CREATE TABLE `gisuser` (
  `username` varchar(10) DEFAULT NULL,
  `sex` tinyint(4) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `phone` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+-------------------------------------------------------------------------------------------------------------------------------

1 row in set (0.00 sec)

8修改表

给表增加列

mysql> alter table gisuser add (email varchar(20),salary smallint);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc gisuser;
+----------+-------------+------+-----+---------+-------+
| 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(20) | YES  |     | NULL    |       |
| salary   | smallint(6) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

 删除列：

mysql> alter table gisuser drop salary;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改表的字符类型

mysql> alter table gisuser change username username char(10);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc gisuser;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | char(10)    | YES  |     | NULL    |       |
| sex      | tinyint(4)  | YES  |     | NULL    |       |
| birth    | date        | YES  |     | NULL    |       |
| address  | varchar(50) | YES  |     | NULL    |       |
| phone    | varchar(15) | YES  |     | NULL    |       |
| email    | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

 也可以

mysql> alter table gisuser modify username varchar(20);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc gisuser;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES  |     | NULL    |       |
| sex      | tinyint(4)  | YES  |     | NULL    |       |
| birth    | date        | YES  |     | NULL    |       |
| address  | varchar(50) | YES  |     | NULL    |       |
| phone    | varchar(15) | YES  |     | NULL    |       |
| email    | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

重命名表

mysql> rename table gisuser to gisusers;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+---------------+
| Tables_in_gis |
+---------------+
| gisusers      |
+---------------+
1 row in set (0.01 sec)