数据库基本管理

数据库基本管理
问题
本案例要求熟悉MySQL的连接及数据库表的增删改查等基本管理操作，主要完成以下几个方便的操作：
使用mysql命令连接数据库
练习查看/删除/创建库的相关操作
练习查看/删除/创建表的相关操作，表数据参考如表-1所示内容
表－1 测试用表数据
学号 姓名 性别 手机号 通信地址
NSD131201 张三 男 13012345678 朝阳区劲松南路… …
NSD131202 韩梅梅 女 13722223333 海淀区北三环西路…
NSD131203 王五 男 18023445678 丰台区兴隆中街… …
步骤
实现此案例需要按照如下步骤进行。
步骤一：使用mysql命令连接数据库
连接MySQL服务器时，最基本的用法是通过 -u 选项指定用户名、-p指定密码。密码可以写在命令行（如果不写，则出现交互，要求用户输入），当然基于安全考虑一般不推荐这么做：
[root@dbsvr1 ~]# mysql -uroot -p1234567 //紧挨着选项，不要空格
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright © 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> exit //退出已登录的mysql> 环境
Bye
默认情况下，msyql命令会连接本机的MySQL服务。但在需要的时候，可以通过 -h 选项指定远程主机；如果端口不是3306，还可以通过大写的 -P 选项指定：
[root@dbsvr1 ~]# mysql -u root -p -h 127.0.0.1 -P 3306
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright © 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> exit //退出已登录的mysql> 环境
Bye
连接其他主机的MySQL服务，有一个前提条件——对方已经添加了此用户从此客户机访问的数据库授权，授权操作方法会在后续课程学习。
步骤二：练习查看/删除/创建库的相关操作
以root用户登入“mysql> ”环境后，可以执行各种MySQL指令、SQL指令。基本的用法事项如下：
操作指令不区分大小写（库名/表名、密码、变量值等除外）。
每条SQL指令以 ; 结束或分隔。
不支持 Tab 键自动补齐。
\c 可废弃当前编写错的操作指令。
1）查看现有的库
mysql> SHOW DATABASES;
±-------------------+
| Database |
±-------------------+
| information_schema | //信息概要库
| mysql | //授权库
| performance_schema | //性能结构库
| test | //测试库（空库）
±-------------------+
4 rows in set (0.15 sec)
2）切换/使用指定的库
切换到test库：
mysql> USE test;
Database changed
mysql> SELECT DATABASE(); //确认当前所在的库
±-----------+
| DATABASE() |
±-----------+
| test |
±-----------+
1 row in set (0.00 sec)
切换到mysql库：
mysql> USE mysql;
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> SELECT DATABASE(); //确认当前所在的库
±-----------+
| DATABASE() |
±-----------+
| mysql |
±-----------+
1 row in set (0.00 sec)
3）创建新的库
新建名为mydb的库，确认结果：
mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.03 sec)

mysql> SHOW DATABASES;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mydb | //新建的mydb库
| mysql |
| performance_schema |
| test |
±-------------------+
5 rows in set (0.00 sec)
新建名为newdb的库，确认结果：
mysql> CREATE DATABASE newdb;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mydb | //新建的mydb库
| mysql |
| newdb | //新建的newdb库
| performance_schema |
| test |
±-------------------+
6 rows in set (0.00 sec)
新建数据库以后，会为每个数据库建立同名文件夹，可从命令行确认：
[root@dbsvr1 ~]# ls -l /var/lib/mysql/{my,new}db/
/var/lib/mysql/mydb/:
总用量 4
-rw-rw----. 1 mysql mysql 65 1月 7 17:00 db.opt

/var/lib/mysql/newdb/:
总用量 4
-rw-rw----. 1 mysql mysql 65 1月 7 17:00 db.opt
4）删除指定的库
删除名为newdb的库：
mysql> DROP DATABASE newdb;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW DATABASES; //确认删除结果，已无newdb表
±-------------------+
| Database |
±-------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
±-------------------+
5 rows in set (0.00 sec)
步骤三：练习查看/删除/创建表的相关操作
1）查看指定的库里有哪些表
查看test库里有哪些表：
mysql> USE test;
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)
查看mysql库里有哪些表：
mysql> USE mysql;
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_mysql |
±--------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user | //存放数据库用户的表
±--------------------------+
28 rows in set (0.00 sec)
2）查看指定表的字段结构
当前库为mysql，查看columns_priv表的结构，以列表形式展现：
mysql> DESCRIBE columns_priv\G //末尾不用分号
*************************** 1. row ***************************
Field: Host
Type: char(60)
Null: NO
Key: PRI
Default:
Extra:
*************************** 2. row ***************************
Field: Db
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 3. row ***************************
Field: User
Type: char(16)
Null: NO
Key: PRI
Default:
Extra:
*************************** 4. row ***************************
Field: Table_name
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 5. row ***************************
Field: Column_name
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 6. row ***************************
Field: Timestamp
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: on update CURRENT_TIMESTAMP
*************************** 7. row ***************************
Field: Column_priv
Type: set(‘Select’,‘Insert’,‘Update’,‘References’)
Null: NO
Key:
Default:
Extra:
7 rows in set (0.00 sec)
查看columns_priv表的结构，以表格形式展现：
mysql> DESCRIBE columns_priv; //末尾需要有分号
±------------±---------------------------------------------±-----±----±------------------±----------------------------+
| Field | Type | Null | Key | Default | Extra |
±------------±---------------------------------------------±-----±----±------------------±----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set(‘Select’,‘Insert’,‘Update’,‘References’) | NO | | | |
±------------±---------------------------------------------±-----±----±------------------±----------------------------+
7 rows in set (0.00 sec)
上述操作中，DESCRIBE可缩写为DESC；另外，当引用非当前库中的表时，可以用“库名.表名”的形式。比如，切换为mysql库再执行“DESCRIBE columns_priv;”，与以下操作的效果是相同的：
mysql> DESC mysql.columns_priv;
±------------±---------------------------------------------±-----±----±------------------±----------------------------+
| Field | Type | Null | Key | Default | Extra |
±------------±---------------------------------------------±-----±----±------------------±----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set(‘Select’,‘Insert’,‘Update’,‘References’) | NO | | | |
±------------±---------------------------------------------±-----±----±------------------±----------------------------+
7 rows in set (0.00 sec)
3）在test库中创建一个名为pwlist的表
包括name、password两列，其中name列作为主键。两个字段值均不允许为空，其中密码列赋予默认空值，相关操作如下所述。
切换到test库：
mysql> USE test;
Database changed
新建pwlist表：
mysql> CREATE TABLE pwlist(
-> name CHAR(16) NOT NULL,
-> password CHAR(48) DEFAULT ‘’,
-> PRIMARY KEY(name)
-> );
Query OK, 0 rows affected (0.26 sec)
确认新创建的表：
mysql> SHOW TABLES;
±---------------+
| Tables_in_test |
±---------------+
| pwlist | //新建的pwlist表
±---------------+
1 rows in set (0.01 sec)
查看pwlist表的字段结构：
mysql> DESC pwlist;
±---------±---------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±---------±---------±-----±----±--------±------+
| name | char(16) | NO | PRI | NULL | |
| password | char(48) | YES | | | |
±---------±---------±-----±----±--------±------+
2 rows in set (0.01 sec)

4）删除指定的表
删除当前库中的pwlist表：
mysql> DROP TABLE pwlist;
Query OK, 0 rows affected (0.01 sec)
确认删除结果：
mysql> SHOW TABLES;
Empty set (0.00 sec)
5）在test库中创建一个学员表
表格结构及数据内容如表-1所示。
在MySQL表内存储中文数据时，需要更改字符集（默认为latin1不支持中文），以便MySQL支持存储中文数据记录；比如，可以在创建库或表的时候，手动添加“DEFAULT CHARSET=utf8”来更改字符集。
根据上述表格结构，创建支持中文的student表：
mysql> CREATE TABLE test.student(
-> 学号 char(9) NOT NULL,
-> 姓名 varchar(4) NOT NULL,
-> 性别 enum(‘男’,‘女’) NOT NULL,
-> 手机号 char(11) DEFAULT ‘’,
-> 通信地址 varchar(64),
-> PRIMARY KEY(学号)
-> ) DEFAULT CHARSET=utf8; //手工指定字符集，采用utf8
Query OK, 0 rows affected (0.07 sec)
查看student表的字段结构：
mysql> DESC test.student;
±-------------±------------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------------±------------------±-----±----±--------±------+
| 学号 | char(9) | NO | PRI | NULL | |
| 姓名 | varchar(4) | NO | | NULL | |
| 性别 | enum(‘男’,‘女’) | NO | | NULL | |
| 手机号 | char(11) | YES | | | |
| 通信地址 | varchar(64) | YES | | NULL | |
±-------------±------------------±-----±----±--------±------+
5 rows in set (0.04 sec)
查看student表的实际创建指令：
mysql> SHOW CREATE TABLE test.student;
±--------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±--------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE student (
学号 char(9) NOT NULL,
姓名 varchar(4) NOT NULL,
性别 enum(‘男’,‘女’) NOT NULL,
手机号 char(11) DEFAULT ‘’,
通信地址 varchar(64) DEFAULT NULL,
PRIMARY KEY (学号)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±--------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
注意：若要修改MySQL服务的默认字符集，可以更改服务器的my.cnf配置文件，添加character_set_server=utf8 配置，然后重启数据库服务。
[root@dbsvr1 ~]# vim /etc/my.cnf //修改运行服务配置
[mysqld]
… …
character_set_server=utf8

[root@dbsvr1 ~]# service mysql restart //重启服务
… …
[root@dbsvr1 ~]# mysql –u root -p
Enter password:
… …
mysql> SHOW VARIABLES LIKE ‘character%’; //确认更改结果
±-------------------------±---------------------------+
| Variable_name | Value |
±-------------------------±---------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
±-------------------------±---------------------------+
8 rows in set (0.03 sec)