构建Mysql服务器

《综合》构建Mysql服务器

1. 构建MySQL服务器
2. 数据库基本管理
3. MySQL 数据类型
4. 表结构的调整

1 构建MySQL服务器

1.1 问题

本案例要求熟悉MySQL官方安装包的使用，快速构建一台数据库服务器：

• 安装MySQL-server、MySQl-client软件包
• 修改数据库用户root的密码
• 确认MySQL服务程序运行、root可控

1.2 方案

本课程将使用64位的RHEL 7操作系统，MySQL数据库的版本是5.7.17。

访问http://dev.mysql.com/downloads/mysql/，找到MySQL Community Server下载页面，平台选择“Red Hat Enterprise Linux 7 / Oracle Linux”，然后选择64位的bundle整合包下载，如图-1所示。

 

图-1

注意：下载MySQL软件时需要以Oracle网站账户登录，如果没有请根据页面提示先注册一个（免费） 。

1.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：准备工作

1）卸载系统自带的mariadb-server、mariadb软件包（如果有的话）

1. [[email protected] ~]# yum -y remove mariadb-server mariadb
2. Setting up Remove Process
3. No Match for argument: mariadb-server
4. rhel7dvd                                                 | 3.9 kB     00:00 ...
5. Package(s) mariadb-server available, but not installed.
6. No Match for argument: mariadb
7. Package(s) mariadb available, but not installed.
8. No Packages marked for removal

2）清理/etc/my.cnf配置文件

此配置文件由RHEL自带的mariadb-libs库提供：

1. [[email protected] ~]# rpm -qf /etc/my.cnf
2. mariadb-libs-5.5.35-3.el7.x86_64

大量的系统软件包都需要用到mariadb-libs库，因此不建议直接卸载此软件包。最好是安装新的MySQL数据库软件时，采用 -U 升级的方式来进行替换。

配置文件/etc/my.cnf若不需要使用，可以直接删除。或者保险起见，也可以将其改名备份：

1. [[email protected] ~]# mv /etc/my.cnf /etc/my.cnf.old

步骤二：安装mysql-community-client、mysql-community-server软件包

1）释放bundle整合包

1. [[email protected] ~]# cd /var/ftp/pub/ 
2. [[email protected] pub]# tar xvf mysql-5.7.17-1.el7.x86_64.rpm-bundle.tar
3. mysql-community-client-5.7.17-1.el7.x86_64.rpm                
4. //MySQL 数据库客户端应用程序和工具
5. mysql-community-common-5.7.17-1.el7.x86_64.rpm                 
6. //MySQL 数据库和客户端库共享文件
7. mysql-community-devel-5.7.17-1.el7.x86_64.rpm                  
8. //MySQL 数据库客户端应用程序的库和头文件
9. mysql-community-embedded-5.7.17-1.el7.x86_64.rpm              
10. //MySQL嵌入式函数库
11. mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm
12. //MySQL嵌入式兼容函数库
13. mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm
14. //头文件和库文件作为Mysql的嵌入式库文件
15. mysql-community-libs-5.7.17-1.el7.x86_64.rpm
16. //MySQL 数据库客户端应用程序的共享库
17. mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm
18. //MySQL 5.6.31 数据库客户端应用程序的共享兼容库
19. mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm     
20. //mysql最小安装包的调试信息
21. mysql-community-server-5.7.17-1.el7.x86_64.rpm
22. //非常快速和可靠的 SQL 数据库服务器
23. mysql-community-server-minimal-5.7.17-1.el7.x86_64.rpm  
24. //非常快速和可靠的 SQL 数据库服务器(最小化安装)
25. mysql-community-test-5.7.17-1.el7.x86_64.rpm                   
26. //MySQL 数据库服务器的测试套件

2）安装MySQL数据库

在bundle的整合包中，并不是所有的rpm包都会用到，将一些重复的删除。

安装mysql时可能会缺少某些依赖包，需提前单独安装

1. [[email protected] pub]#yum -y  install perl-Data-Dumper  perl-JSON  perl-Time-HiRes
2. [[email protected] pub]# rpm -Uvh mysql-community-*.rpm
3. 准备中...                          ################################# [100%]
4. 正在升级/安装...
5. 1:mysql-community-common-5.7.17-1.e################################# [  9%]
6. 2:mysql-community-libs-5.7.17-1.el7################################# [ 18%]
7. 3:mysql-community-client-5.7.17-1.e################################# [ 27%]
8. 4:mysql-community-server-5.7.17-1.e################################# [ 36%]
9. 5:mysql-community-devel-5.7.17-1.el################################# [ 45%]
10. 6:mysql-community-embedded-5.7.17-1################################# [ 55%]
11. 7:mysql-community-embedded-devel-5.################################# [ 64%]
12. 8:mysql-community-test-5.7.17-1.el7################################# [ 73%]
13. 9:mysql-community-libs-compat-5.7.1################################# [ 82%]
14. 10:mysql-community-minimal-debuginfo################################# [ 91%]
15. 正在清理/删除...
16. 11:mariadb-libs-1:5.5.35-3.el7      ################################# [100%]
17. [[email protected] pub]#systemctl start mysqld.service

安装过程中会尝试做一些检测，然后完成基本的初始化任务，期间会给出相关的提示。比如由于MySQL 5.7对TIMESTAMP时间戳的处理不同于之前的版本，会给出警告和提示出解决办法（使用--explicit_defaults_for_timestamp选项）：

1. 2017-04-04T15:59:07.324470Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

MySQL 5.7默认采用的存储引擎不再是MyISAM，而是InnoDB。初始化时若相关的文件不存在，会自动创建并设置相关参数：

1. 2017-04-04T15:59:09.075698Z 0 [Warning] InnoDB: New log files created, LSN=45790
2. 2017-04-04T15:59:09.381634Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
3. 2017-04-04T15:59:09.579733Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a3973917-194f-11e7-a75b-52540018542e.
4. 2017-04-04T15:59:09.703759Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed‘ cannot be opened.
5. 2017-04-04T15:59:09.711439Z 1 [Note] A temporary password is generated for [email protected]: ;%7SDdD)quOI
6. 2017-04-04T15:59:29.758102Z 1 [ERROR] Failed to open the bootstrap file /tmp/install-validate-password-plugin.xqy7Ay.sql
7. 2017-04-04T15:59:29.758122Z 1 [ERROR] 1105  Bootstrap file error, return code (0). Nearest query: ‘LSE SET @sys.tmp.table_exists.SQL = CONCAT(‘SELECT COUNT(*) FROM `‘, in_db, ‘`.`‘, in_table, ‘`‘); PREPARE stmt_select FROM @sys.tmp.table_exists.SQL; IF (NOT v_error) THEN DEALLOCATE PREPARE stmt_select; SET out_exists = ‘TEMPORARY‘; END IF; END IF; END;
8. ‘
9. 2017-04-04T15:59:29.758336Z 0 [ERROR] Aborting
11. 2017-04-04T15:59:33.078575Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
12. 2017-04-04T15:59:33.092082Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.17) starting as process 3326 ...
13. 2017-04-04T15:59:33.095074Z 0 [Note] InnoDB: PUNCH HOLE support available
14. 2017-04-04T15:59:33.095104Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
15. 2017-04-04T15:59:33.095109Z 0 [Note] InnoDB: Uses event mutexes
16. 2017-04-04T15:59:33.095112Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
17. 2017-04-04T15:59:33.095115Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
18. 2017-04-04T15:59:33.095120Z 0 [Note] InnoDB: Using Linux native AIO
19. 2017-04-04T15:59:33.095340Z 0 [Note] InnoDB: Number of pools: 1
20. 2017-04-04T15:59:33.095428Z 0 [Note] InnoDB: Not using CPU crc32 instructions
21. 2017-04-04T15:59:33.096904Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
22. 2017-04-04T15:59:33.106888Z 0 [Note] InnoDB: Completed initialization of buffer pool
23. 2017-04-04T15:59:33.108711Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
24. 2017-04-04T15:59:33.120189Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
25. 2017-04-04T15:59:33.454908Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
26. 2017-04-04T15:59:33.455034Z 0 [Note] InnoDB: Setting file ‘./ibtmp1‘ size to 12 MB. Physically writing the file full; Please wait ...
27. 2017-04-04T15:59:34.057704Z 0 [Note] InnoDB: File ‘./ibtmp1‘ size is now 12 MB.
28. 2017-04-04T15:59:34.058603Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
29. 2017-04-04T15:59:34.058615Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
30. 2017-04-04T15:59:34.063078Z 0 [Note] InnoDB: Waiting for purge to start
31. 2017-04-04T15:59:34.113304Z 0 [Note] InnoDB: 5.7.17 started; log sequence number 2536157
32. 2017-04-04T15:59:34.113841Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
33. 2017-04-04T15:59:34.114310Z 0 [Note] Plugin ‘FEDERATED‘ is disabled.
34. 2017-04-04T15:59:34.118690Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
35. 2017-04-04T15:59:34.118921Z 0 [Warning] CA certificate ca.pem is self signed.
36. 2017-04-04T15:59:34.119582Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170404 23:59:34
37. 2017-04-04T15:59:34.237643Z 0 [Note] Server hostname (bind-address): ‘*‘; port: 3306
38. 2017-04-04T15:59:34.241687Z 0 [Note] IPv6 is available.
39. 2017-04-04T15:59:34.241727Z 0 [Note]   - ‘::‘ resolves to ‘::‘;
40. 2017-04-04T15:59:34.241753Z 0 [Note] Server socket created on IP: ‘::‘.
41. 2017-04-04T15:59:34.313591Z 0 [Note] Event Scheduler: Loaded 0 events
42. 2017-04-04T15:59:34.313686Z 0 [Note] Executing ‘SELECT * FROM INFORMATION_SCHEMA.TABLES;‘ to get a list of tables using the deprecated partition engine. You may use the startup option ‘--disable-partition-engine-check‘ to skip this check.
43. 2017-04-04T15:59:34.313693Z 0 [Note] Beginning of list of non-natively partitioned tables
44. 2017-04-04T15:59:34.322126Z 0 [Note] End of list of non-natively partitioned tables
45. 2017-04-04T15:59:34.322261Z 0 [Note] /usr/sbin/mysqld: ready for connections.
46. Version: ‘5.7.17‘  socket: ‘/var/lib/mysql/mysql.sock‘  port: 3306  MySQL Community Server (GPL)

关于MySQL数据库的管理员账号root，其密码也不再是空，而是安装时随机生成一个，这种处理方式一定程度上增强了MySQl服务器的安全性。随机生成的密码字串可以从保存到mysql日志文件中找到：

1. [[email protected] pub]#grep ‘temporary password‘ /var/log/mysqld.log
2. 2017-04-04T15:59:09.711439Z 1 [Note] A temporary password is generated for [email protected]: ;%7SDdD)quOI

3）确认安装后的服务单元文件、服务状态

查看服务单元文件

1. [[email protected] pub]# ls -lh /usr/lib/systemd/system/mysqld.service
2. -rw-r--r--. 1 root root 1.6K 11月 29 04:30 /usr/lib/systemd/system/mysqld.service

mysql服务的自启状态为enabled：

1. [[email protected] ~]# # systemctl  is-enabled  mysqld.service
2. enabled

步骤三：查看Mysql服务的运行状态

服务器进程为mysqld，监听的默认端口为TCP 3306：

1. [[email protected] pub]# netstat -antpu | grep mysql
2. tcp6       0      0 :::3306                 :::*                    LISTEN      3913/mysqld         

查看Mysql服务的状态

1. [[email protected] pub]#systemctl  is-active  mysqld.service
2. active
3. [[email protected] pub]#systemctl  status mysqld.service
4. mysqld.service - MySQL Server
5. Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled)
6. Active: active (running) since 日 2017-04-23 08:56:24 CST; 1s ago
7. Docs: man:mysqld(8)
8. http://dev.mysql.com/doc/refman/en/using-systemd.html
9. Process: 13753 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
10. Process: 13732 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
11. Main PID: 13757 (mysqld)
12. CGroup: /system.slice/mysqld.service
13. └─13757 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

数据库的默认存放位置为 /var/lib/mysql：

1. [[email protected] pub]# ls  /var/lib/mysql
2. auto.cnf    client-cert.pem  ibdata1      ibtmp1      mysql.sock.lock     public_key.pem   sys
3. ca-key.pem  client-key.pem   ib_logfile0  mysql       performance_schema  server-cert.pem
4. ca.pem      ib_buffer_pool   ib_logfile1  mysql.sock  private_key.pem     server-key.pem

步骤四：连接MySQL服务器，修改密码

查看随机生成的root管理密码

1. [[email protected] pub]#grep ‘temporary password‘ /var/log/mysqld.log
2. 2017-04-01T18:10:42.948679Z 1 [Note] A temporary password is generated for [email protected]: GWphBZ_g 1&          //密码为GWphBZ_g 1&

2）使用客户端命令mysql连接到MySQL服务器

提示验证时，填入前一步获得的随机密码，验证成功后即可进入“mysql> ”环境：

1. [[email protected] pub]# mysql -u root -p
2. Welcome to the MySQL monitor.  Commands end with ; or g.
3. Your MySQL connection id is 14
4. Server version: 5.7.17
6. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
8. Oracle is a registered trademark of Oracle Corporation and/or its
9. affiliates. Other names may be trademarks of their respective
10. owners.
12. Type ‘help;‘ or ‘h‘ for help. Type ‘c‘ to clear the current input statement.
14. mysql>                                     //登录成功后，进入SQL操作环境

用该密码登录到服务端后，必须马上修改密码，不然会报如下错误：

1. mysql> show databases;
2. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

3）执行SET PASSWORD命令修改密码

这个其实与validate_password_policy的值有关，默认为1，所以刚开始设置的密码必须符合长度，且必须含有数字，小写或大写字母，特殊字符。如果我们不希望密码设置的那么复杂，需要修改两个全局参数：validate_password_policy与validate_password_length。validate_password_length默认值为8,最小值为4，如果你显性指定validate_password_length的值小于4，尽管不会报错，但validate_password_length的值将设为4。

可参考下列指令：

1. mysql>set global validate_password_policy=0;
2. mysql>set global validate_password_length=4；
3. mysql> SET PASSWORD FOR ‘root‘@‘localhost‘=PASSWORD(‘1234567‘);
4. Query OK, 0 rows affected, 1 warning (0.00 sec)

上述操作的结果是——更改数据库用户root从本机访问时的密码，设为1234567。

退出“mysql> ”环境，重新登录验证，必须采用新的密码才能登入：

1. mysql> exit                                                 //退出 mysql> 环境
2. Bye
3. [[email protected] ~]# mysql -u root –p            //重新登录
4. Enter password:                                         //输入新设置的密码
5. Welcome to the MySQL monitor.  Commands end with ; or g.
6. Your MySQL connection id is 15
7. Server version: 5.7.17 MySQL Community Server (GPL)
9. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
11. Oracle is a registered trademark of Oracle Corporation and/or its
12. affiliates. Other names may be trademarks of their respective
13. owners.
15. Type ‘help;‘ or ‘h‘ for help. Type ‘c‘ to clear the current input statement.
17. mysql> mysql> show databases;
18. --------------------
19. | Database           |
20. --------------------
21. | information_schema |
22. | mysql              |
23. | performance_schema |
24. | sys                |
25. --------------------
26. 4 rows in set (0.07 sec)

 

2 数据库基本管理

2.1 问题

本案例要求熟悉MySQL的连接及数据库表的增删改查等基本管理操作，主要完成以下几个方便的操作：

• 使用mysql命令连接数据库
• 练习查看/删除/创建库的相关操作
• 练习查看/删除/创建表的相关操作，表数据参考如表-1所示内容

表－1 测试用表数据

 

2.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：使用mysql命令连接数据库

连接MySQL服务器时，最基本的用法是通过 -u 选项指定用户名、-p指定密码。密码可以写在命令行（如果不写，则出现交互，要求用户输入），当然基于安全考虑一般不推荐这么做：

1. [[email protected] ~]# mysql -uroot -p123456          //紧挨着选项，不要空格
2. mysql: [Warning] Using a password on the command line interface can be insecure.
3. Welcome to the MySQL monitor.  Commands end with ; or g.
4. Your MySQL connection id is 16
5. Server version: 5.7.17 MySQL Community Server (GPL)
7. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
9. Oracle is a registered trademark of Oracle Corporation and/or its
10. affiliates. Other names may be trademarks of their respective
11. owners.
13. Type ‘help;‘ or ‘h‘ for help. Type ‘c‘ to clear the current input statement.
15. mysql> exit                                  //退出已登录的mysql> 环境
16. Bye

默认情况下，msyql命令会连接本机的MySQL服务。但在需要的时候，可以通过 -h 选项指定远程主机；如果端口不是3306，还可以通过大写的 -P 选项指定：

1. [[email protected] ~]# mysql -u root -p -h 127.0.0.1 -P 3306
2. Enter password: 
3. Welcome to the MySQL monitor.  Commands end with ; or g.
4. Your MySQL connection id is 17
5. Server version: 5.7.17 MySQL Community Server (GPL)
7. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
9. Oracle is a registered trademark of Oracle Corporation and/or its
10. affiliates. Other names may be trademarks of their respective
11. owners.
13. Type ‘help;‘ or ‘h‘ for help. Type ‘c‘ to clear the current input statement.
15. mysql> exit                                  //退出已登录的mysql> 环境
16. Bye

连接其他主机的MySQL服务，有一个前提条件——对方已经添加了此用户从此客户机访问的数据库授权，授权操作方法会在后续课程学习。

步骤二：练习查看/删除/创建库的相关操作

以root用户登入“mysql> ”环境后，可以执行各种MySQL指令、SQL指令。基本的用法事项如下：

• 操作指令不区分大小写（库名/表名、密码、变量值等除外）。
• 每条SQL指令以 ; 结束或分隔。
• 不支持 Tab 键自动补齐。
• c 可废弃当前编写错的操作指令。

1）查看现有的库

1. mysql> SHOW DATABASES;
2. --------------------
3. | Database            |
4. --------------------
5. | information_schema |                              //信息概要库
6. | mysql               |                              //授权库
7. | performance_schema |                              //性能结构库
8. | sys                  |                              //系统元数据库
9. --------------------
10. 4 rows in set (0.15 sec)

2）切换/使用指定的库

切换到sys库：

1. mysql> USE sys;
2. Database changed
3. mysql> SELECT DATABASE();                          //确认当前所在的库
4. ------------
5. | DATABASE() |
6. ------------
7. | sys         |
8. ------------
9. 1 row in set (0.00 sec)

切换到mysql库：

1. mysql> USE mysql;
2. Reading table information for completion of table and column names
3. You can turn off this feature to get a quicker startup with -A
5. Database changed
6. mysql> SELECT DATABASE();                          //确认当前所在的库
7. ------------
8. | DATABASE() |
9. ------------
10. | mysql      |
11. ------------
12. 1 row in set (0.00 sec)

3）创建新的库

新建名为mydb的库，确认结果：

1. mysql> CREATE DATABASE mydb;
2. Query OK, 1 row affected (0.00 sec)
4. mysql> SHOW DATABASES;
5. --------------------
6. | Database           |
7. --------------------
8. | information_schema |
9. | mydb               |                          //新建的mydb库
10. | mysql              |
11. | performance_schema |
12. | sys               |
13. --------------------
14. 5 rows in set (0.00 sec)

新建名为newdb的库，确认结果：

1. mysql> CREATE DATABASE newdb;
2. Query OK, 1 row affected (0.00 sec)
4. mysql> SHOW DATABASES;
5. --------------------
6. | Database            |
7. --------------------
8. | information_schema |
9. | mydb                |                          //新建的mydb库
10. | mysql               |
11. | newdb               |                          //新建的newdb库
12. | performance_schema |
13. | sys                 |
14. --------------------
15. 6 rows in set (0.00 sec)

新建数据库以后，会为每个数据库建立同名文件夹，可从命令行确认：

1. [[email protected] ~]# ls -l /var/lib/mysql/{my,new}db/
2. /var/lib/mysql/mydb/:
3. 总用量 4
4. -rw-r-----. 1 mysql mysql 65 4月   2 03:14 db.opt
6. /var/lib/mysql/newdb/:
7. 总用量 4
8. -rw-r-----. 1 mysql mysql 65 4月   2 03:15 db.opt

4）删除指定的库

删除名为newdb的库：

1. mysql> DROP DATABASE newdb;
2. Query OK, 0 rows affected (0.04 sec)
4. mysql> SHOW DATABASES;                          //确认删除结果，已无newdb表
5. --------------------
6. | Database           |
7. --------------------
8. | information_schema |
9. | mydb               |
10. | mysql              |
11. | performance_schema |
12. | sys                 |
13. --------------------
14. 5 rows in set (0.00 sec)

步骤三：练习查看/删除/创建表的相关操作

1）查看指定的库里有哪些表

查看mysql库里有哪些表：

1. mysql> USE mysql;
2. Reading table information for completion of table and column names
3. You can turn off this feature to get a quicker startup with -A
5. Database changed
6. mysql> SHOW TABLES;
7. ---------------------------
8. | Tables_in_mysql         |
9. ---------------------------
10. | columns_priv              |
11. | db                               |
12. | engine_cost               |
13. | event                          |
14. | func                            |
15. | general_log                |
16. | gtid_executed            |
17. | help_category            |
18. | help_keyword            |
19. | help_relation              |
20. | help_topic                  |
21. | innodb_index_stats   |
22. | innodb_table_stats    |
23. | ndb_binlog_index      |
24. | plugin                         |
25. | proc                           |
26. | procs_priv                  |
27. | proxies_priv               |
28. | server_cost                |
29. | servers                       |
30. | slave_master_info      |
31. | slave_relay_log_info  |
32. | slave_worker_info      |
33. | slow_log                     |
34. | tables_priv                 |
35. | time_zone                  |
36. | time_zone_leap_second     |
37. | time_zone_name            |
38. | time_zone_transition      |
39. | time_zone_transition_type |
40. | user                      |                     //存放数据库用户的表
41. ---------------------------
42. 31 rows in set (0.00 sec)

2）查看指定表的字段结构

当前库为mysql，查看columns_priv表的结构，以列表形式展现：

1. mysql> DESCRIBE columns_privG              //末尾不用分号
2. *************************** 1. row ***************************
3. Field: Host
4. Type: char(60)
5. Null: NO
6. Key: PRI
7. Default: 
8. Extra: 
9. *************************** 2. row ***************************
10. Field: Db
11. Type: char(64)
12. Null: NO
13. Key: PRI
14. Default: 
15. Extra: 
16. *************************** 3. row ***************************
17. Field: User
18. Type: char(32)
19. Null: NO
20. Key: PRI
21. Default: 
22. Extra: 
23. *************************** 4. row ***************************
24. Field: Table_name
25. Type: char(64)
26. Null: NO
27. Key: PRI
28. Default: 
29. Extra: 
30. *************************** 5. row ***************************
31. Field: Column_name
32. Type: char(64)
33. Null: NO
34. Key: PRI
35. Default: 
36. Extra: 
37. *************************** 6. row ***************************
38. Field: Timestamp
39. Type: timestamp
40. Null: NO
41. Key: 
42. Default: CURRENT_TIMESTAMP
43. Extra: on update CURRENT_TIMESTAMP
44. *************************** 7. row ***************************
45. Field: Column_priv
46. Type: set(‘Select‘,‘Insert‘,‘Update‘,‘References‘)
47. Null: NO
48. Key: 
49. Default: 
50. Extra: 
51. 7 rows in set (0.01 sec)

查看columns_priv表的结构，以表格形式展现：

1. mysql> DESCRIBE columns_priv;                  //末尾需要有分号
2. ------------- ---------------------------------------------- ------ ----- ------------------- -----------------------------
3. | Field       | Type                                         | Null | Key | Default           | Extra                       |
4. ------------- ---------------------------------------------- ------ ----- ------------------- -----------------------------
5. | Host        | char(60)                                              | NO   | PRI |                   |                             |
6. | Db          | char(64)                                               | NO   | PRI |                   |                             |
7. | User        | char(32)                                              | NO   | PRI |                   |                             |
8. | Table_name  | char(64)                                        | NO   | PRI |                   |                             |
9. | Column_name | char(64)                                     | NO   | PRI |                   |                             |
10. | Timestamp   | timestamp                                      | NO   |        | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
11. | Column_priv | set(‘Select‘,‘Insert‘,‘Update‘,‘References‘) | NO   |     |                   |                             |
12. ------------- ---------------------------------------------- ------ ----- ------------------- -----------------------------
13. 7 rows in set (0.00 sec)

上述操作中，DESCRIBE可缩写为DESC；另外，当引用非当前库中的表时，可以用“库名.表名”的形式。比如，切换为mysql库再执行“DESCRIBE columns_priv;”，与以下操作的效果是相同的：

1. mysql> DESC mysql.columns_priv;
2. ------------- ---------------------------------------------- ------ ----- ------------------- -----------------------------
3. | Field       | Type                                         | Null | Key | Default           | Extra                       |
4. ------------- ---------------------------------------------- ------ ----- ------------------- -----------------------------
5. | Host        | char(60)                                              | NO   | PRI |                   |                             |
6. | Db          | char(64)                                               | NO   | PRI |                   |                             |
7. | User        | char(16)                                              | NO   | PRI |                   |                             |
8. | Table_name  | char(64)                                        | NO   | PRI |                   |                             |
9. | Column_name | char(64)                                     | NO   | PRI |                   |                             |
10. | Timestamp   | timestamp                                      | NO   |        | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
11. | Column_priv | set(‘Select‘,‘Insert‘,‘Update‘,‘References‘) | NO   |     |                   |                             |
12. ------------- ---------------------------------------------- ------ ----- ------------------- -----------------------------
13. 7 rows in set (0.00 sec)

3）在test库中创建一个名为pwlist的表

包括name、password两列，其中name列作为主键。两个字段值均不允许为空，其中密码列赋予默认空值，相关操作如下所述。

切换到mydb库：

1. mysql> USE mydb;
2. Database changed

新建pwlist表：

1. mysql> CREATE TABLE pwlist(
2. -> name CHAR(16) NOT NULL,
3. -> password CHAR(48) DEFAULT ‘‘,
4. -> PRIMARY KEY(name)
5. -> );
6. Query OK, 0 rows affected (0.38 sec)

确认新创建的表：

1. mysql> SHOW TABLES;
2. ----------------
3. | Tables_in_mydb |
4. ----------------
5. | pwlist         |                                  //新建的pwlist表
6. ----------------
7. 1 rows in set (0.01 sec)

查看pwlist表的字段结构：

1. mysql> DESC pwlist;
2. ---------- ---------- ------ ----- --------- -------
3. | Field       | Type     | Null | Key | Default | Extra |
4. ---------- ---------- ------ ----- --------- -------
5. | name        | char(16) | NO   | PRI | NULL    |       |
6. | password | char(48) | YES  |     |         |       |
7. ---------- ---------- ------ ----- --------- -------
8. 2 rows in set (0.01 sec)

4）删除指定的表

删除当前库中的pwlist表：

1. mysql> DROP TABLE pwlist;
2. Query OK, 0 rows affected (0.01 sec)

确认删除结果：

1. mysql> SHOW TABLES;
2. Empty set (0.00 sec)

5）在mydb库中创建一个学员表

表格结构及数据内容如表-1所示。

在MySQL表内存储中文数据时，需要更改字符集（默认为latin1不支持中文），以便MySQL支持存储中文数据记录；比如，可以在创建库或表的时候，手动添加“DEFAULT CHARSET=utf8”来更改字符集。

根据上述表格结构，创建支持中文的student表：

1. mysql> CREATE TABLE mydb.student(
2. -> 学号 char(9) NOT NULL,
3. -> 姓名 varchar(4) NOT NULL,
4. -> 性别 enum(‘男‘,‘女‘) NOT NULL,
5. -> 手机号 char(11) DEFAULT ‘‘,
6. -> 通信地址 varchar(64),
7. -> PRIMARY KEY(学号)
8. -> ) DEFAULT CHARSET=utf8;                  //手工指定字符集，采用utf8
9. Query OK, 0 rows affected (0.31sec)

查看student表的字段结构：

1. mysql> DESC mydb.student;
2. -------------- ------------------- ------ ----- --------- -------
3. | Field        | Type              | Null | Key | Default | Extra |
4. -------------- ------------------- ------ ----- --------- -------
5. | 学号         | char(9)               | NO   | PRI | NULL    |       |
6. | 姓名         | varchar(4)          | NO   |        | NULL    |       |
7. | 性别         | enum(‘男‘,‘女‘)   | NO   |        | NULL    |       |
8. | 手机号     | char(11)             | YES  |        |              |       |
9. | 通信地址 | varchar(64)        | YES  |        | NULL    |       |
10. -------------- ------------------- ------ ----- --------- -------
11. 5 rows in set (0.04 sec)

查看student表的实际创建指令：

1. mysql> SHOW CREATE TABLE mydb.student;
2. --------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3. | Table   | Create Table                                                                                                                                                                                                                                                             |
4. --------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5. | student | CREATE TABLE `student` (
6. `学号` char(9) NOT NULL,
7. `姓名` varchar(4) NOT NULL,
8. `性别` enum(‘男‘,‘女‘) NOT NULL,
9. `手机号` char(11) DEFAULT ‘‘,
10. `通信地址` varchar(64) DEFAULT NULL,
11. PRIMARY KEY (`学号`)
12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8                  |
13. --------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
14. 1 row in set (0.00 sec)

注意：若要修改MySQL服务的默认字符集，可以更改服务器的my.cnf配置文件，添加character_set_server=utf8 配置，然后重启数据库服务。

1. [[email protected] ~]# vim /etc/my.cnf                          //修改运行服务配置
2. [mysqld]
3. .. ..
4. character_set_server=utf8
6. [[email protected] ~]# systemctl restart mysqld                  //重启服务
7. .. ..
8. [[email protected] ~]# mysql –u root -p  
9. Enter password:
10. .. ..
11. mysql> SHOW VARIABLES LIKE ‘character%‘;                  //确认更改结果
12. -------------------------- ----------------------------
13. | Variable_name            | Value                      |
14. -------------------------- ----------------------------
15. | character_set_client     | utf8                       |
16. | character_set_connection | utf8                       |
17. | character_set_database   | utf8                       |
18. | character_set_filesystem | binary                     |
19. | character_set_results    | utf8                       |
20. | character_set_server     | utf8                       |
21. | character_set_system     | utf8                       |
22. | character_sets_dir       | /usr/share/mysql/charsets/ |
23. -------------------------- ----------------------------
24. 8 rows in set (0.03 sec)

 

3 MySQL 数据类型

3.1 问题

本案例要求熟悉MySQL的字段数据类型、时间函数的使用，完成以下任务操作：

• 在home库里创建famliy表，表结构、字段类型自定义
• 练习各种时间函数的使用

3.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：创建home库、family表

1）新建home库，并切换到home库

1. mysql> CREATE DATABASE home;
2. Query OK, 1 row affected (0.00 sec)
3. mysql> USE home;
4. Database changed

2）新建family表

假定family表用来记录每个家庭成员的姓名（name）、性别（gender）、出生日期（birth）、职业（job）、与户主关系（relation）。

1. mysql> CREATE TABLE family(
2. -> name varchar(16) NOT NULL,
3. -> gender enum(‘male‘,‘femal‘) DEFAULT ‘male‘,
4. -> birth date NOT NULL,
5. -> job varchar(16) DEFAULT ‘‘,
6. -> relation varchar(24) NOT NULL,
7. -> PRIMARY KEY(name)
8. -> );
9. Query OK, 0 rows affected (0.61sec)

查看family表的字段结构：

1. mysql> DESC family;
2. ---------- ---------------------- ------ ----- --------- -------
3. | Field    | Type                 | Null | Key | Default | Extra |
4. ---------- ---------------------- ------ ----- --------- -------
5. | name     | varchar(16)          | NO   | PRI | NULL    |       |
6. | gender   | enum(‘male‘,‘femal‘) | YES  |     | male    |       |
7. | birth    | date                 | NO   |     | NULL    |       |
8. | job      | varchar(16)          | YES  |     |         |       |
9. | relation | varchar(24)          | NO   |     | NULL    |       |
10. ---------- ---------------------- ------ ----- --------- -------
11. 5 rows in set (0.00 sec)

步骤二：练习各种时间函数的使用

1）使用now()查看当前的日期和时间

1. mysql> SELECT now();
2. ---------------------
3. | now()               |
4. ---------------------
5. | 2017-04-02 04:02:42 |
6. ---------------------
7. 1 row in set (0.00 sec)

2）使用sysdate()查看系统日期和时间

1. mysql> SELECT sysdate();
2. ---------------------
3. | sysdate()           |
4. ---------------------
5. | 2017-04-02 04:03:21 |
6. ---------------------
7. 1 row in set (0.00 sec)

3）使用curdate()获得当前的日期，不含时间

1. mysql> SELECT curdate();
2. ------------
3. | curdate()  |
4. ------------
5. | 2017-04-02 |
6. ------------
7. 1 row in set (0.00 sec)

4）使用curtime()获得当前的时间，不含日期

1. mysql> SELECT curtime();
2. -----------
3. | curtime() |
4. -----------
5. | 04:04:55  |
6. -----------
7. 1 row in set (0.00 sec)

5）分别获取当前日期时间中的年份、月份、日

1. mysql> SELECT year(now()),month(now()),day(now());
2. ------------- -------------- ------------
3. | year(now()) | month(now()) | day(now()) |
4. ------------- -------------- ------------
5. |        2017 |            4 |          2 |
6. ------------- -------------- ------------
7. 1 row in set (0.00 sec)

6）获取系统日期时间中的月份、日

1. mysql> SELECT month(sysdate()),day(sysdate());
2. ------------------ ----------------
3. | month(sysdate()) | day(sysdate()) |
4. ------------------ ----------------
5. |                4 |              2 |
6. ------------------ ----------------
7. 1 row in set (0.00 sec)

7）获取系统日期时间中的时刻

1. mysql> SELECT time(sysdate());
2. -----------------
3. | time(sysdate()) |
4. -----------------
5. | 04:06:08        |
6. -----------------
7. 1 row in set (0.00 sec)

4 表结构的调整

4.1 问题

本案例要求熟悉MySQL库中表的字段修改，主要练习以下操作：

• 添加字段
• 修改字段名
• 修改字段类型
• 删除字段

4.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：添加字段

在home中创建tea6表

1. mysql> CREATE TABLE home.tea6(id int(4) PRIMARY KEY,
2. -> name varchar(4) NOT NULL,
3. -> age int(2) NOT NULL
4. -> );
5. Query OK, 0 rows affected (0.34 sec)

为tea6表添加一个address字段

添加前：

1. mysql> DESC tea6;
2. ------- ------------ ------ ----- --------- -------
3. | Field | Type       | Null | Key | Default | Extra |
4. ------- ------------ ------ ----- --------- -------
5. | id    | int(4)     | NO   | PRI | NULL    |       |
6. | name  | varchar(4) | NO   |     | NULL    |       |
7. | age   | int(2)     | NO   |     | NULL    |       |
8. ------- ------------ ------ ----- --------- -------
9. 3 rows in set (0.00 sec)

添加address字段：

1. mysql> ALTER TABLE tea6 ADD address varchar(48);
2. Query OK, 0 rows affected (0.84 sec)
3. Records: 0  Duplicates: 0  Warnings: 0

添加后（默认作为最后一个字段）：

1. mysql> DESC tea6;
2. --------- ------------- ------ ----- --------- -------
3. | Field   | Type        | Null | Key | Default | Extra |
4. --------- ------------- ------ ----- --------- -------
5. | id      | int(4)      | NO   | PRI | NULL    |       |
6. | name    | varchar(4)  | NO   |     | NULL    |       |
7. | age     | int(2)      | NO   |     | NULL    |       |
8. | address | varchar(48) | YES  |     | NULL    |       |
9. --------- ------------- ------ ----- --------- -------
10. 4 rows in set (0.00 sec)

3）在tea6表的age列之后添加一个gender字段

添加操作：

1. mysql> ALTER TABLE tea6 ADD gender enum(‘boy‘,‘girl‘) AFTER age;
2. Query OK, 0 rows affected (0.59 sec)
3. Records: 0  Duplicates: 0  Warnings: 0

确认添加结果：

1. mysql> DESC tea6;
2. --------- -------------------- ------ ----- --------- -------
3. | Field   | Type               | Null | Key | Default | Extra |
4. --------- -------------------- ------ ----- --------- -------
5. | id      | int(4)             | NO   | PRI | NULL    |       |
6. | name    | varchar(4)         | NO   |     | NULL    |       |
7. | age     | int(2)             | NO   |     | NULL    |       |
8. | gender  | enum(‘boy‘,‘girl‘) | YES  |     | NULL    |       |
9. | address | varchar(48)        | YES  |     | NULL    |       |
10. --------- -------------------- ------ ----- --------- -------
11. 5 rows in set (0.00 sec)

步骤二：修改字段名和字段类型

将tea6表的gender字段改名为sex，并添加非空约束

修改操作：

1. mysql> ALTER TABLE tea6 CHANGE gender
2. -> sex enum(‘boy‘,‘girl‘) NOT NULL;
3. Query OK, 0 rows affected (0.08 sec)
4. Records: 0  Duplicates: 0  Warnings: 0

确认修改结果：

1. mysql> DESC tea6;
2. --------- -------------------- ------ ----- --------- -------
3. | Field   | Type               | Null | Key | Default | Extra |
4. --------- -------------------- ------ ----- --------- -------
5. | id      | int(4)             | NO   | PRI | NULL    |       |
6. | name    | varchar(4)         | NO   |     | NULL    |       |
7. | age     | int(2)             | NO   |     | NULL    |       |
8. | sex     | enum(‘boy‘,‘girl‘) | NO   |     | NULL    |       |
9. | address | varchar(48)        | YES  |     | NULL    |       |
10. --------- -------------------- ------ ----- --------- -------
11. 5 rows in set (0.00 sec)

步骤三：删除字段

删除tea6表中名为sex的字段：

1. mysql> ALTER TABLE tea6 DROP sex;                             //删除操作
2. Query OK, 0 rows affected (0.52 sec)
3. Records: 0  Duplicates: 0  Warnings: 0
5. mysql> DESC tea6;                                                            //确认删除结果
6. --------- ------------- ------ ----- --------- -------
7. | Field   | Type        | Null | Key | Default | Extra |
8. --------- ------------- ------ ----- --------- -------
9. | id      | int(4)      | NO   | PRI | NULL    |       |
10. | name    | varchar(4)  | NO   |     | NULL    |       |
11. | age     | int(2)      | NO   |     | NULL    |       |
12. | address | varchar(48) | YES  |     | NULL    |       |
13. --------- ------------- ------ ----- --------- -------
14. 4 rows in set (0.00 sec)