MySQL的连接及数据库表的增删改查等基本管理操作

数据库基本管理：

• 使用mysql命令连接数据库
• 练习查看/删除/创建库的相关操作
• 练习查看/删除/创建表的相关操作

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

连接MySQL服务器时，最基本的用法是通过 -u 选项指定用户名、-p指定密码。密码可以写在命令行（如果不写，则出现交互，要求用户输入）
[[email protected] ~]# mysql -uroot -p123456 //紧挨着选项，不要空格

[[email protected] ~]# mysql -uroot -p123456
mysql: [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 3
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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
Bye

默认情况下，msyql命令会连接本机的MySQL服务。但在需要的时候，可以通过 -h 选项指定远程主机；
[[email protected] ~]# mysql -h 127.0.0.1 -u root -p

[[email protected] ~]# mysql -h 127.0.0.1 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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
Bye

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

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

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

1）查看现有的库

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

2）切换/使用指定的库

mysql> use sys;                  //切换到sys库
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() |
+------------+
| sys        |
+------------+
1 row in set (0.00 sec)

)切换到mysql库：

mysql> use mysql;               //切换到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）新建名为newdb的库，确认结果：

mysql> create database newdb;   //新建名为newdb的库
Query OK, 1 row affected (0.00 sec)

mysql> show  databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |         
| newdb              |        //新建的newdb库
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

4）删除指定的库

mysql> drop database newdb;    //删除名为newdb的库
Query OK, 0 rows affected (0.00 sec)

mysql> show  databases;        //确认删除结果，已无newdb库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

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

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

)查看mysql库里有哪些表：

mysql> use mysql;
Database changed
mysql> show  tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| 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                      |    //存放数据库用户的表
+---------------------------+
31 rows in set (0.00 sec)

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

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

mysql> desc 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(32)
   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> desc columns_priv;       //查看表结构，以表格形式展现末尾需要有分号
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                                         | Null | Key | Default           | Extra                       |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host        | char(60)                                     | NO   | PRI |                   |                             |
| Db          | char(64)                                     | NO   | PRI |                   |                             |
| User        | char(32)                                     | 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列作为主键。两个字段值均不允许为空，其中密码列赋予默认空值。
）切换到mydb库：

mysql> use mydb;
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.38 sec)

）确认新创建的表：

mysql> show tables; 
+----------------+
| Tables_in_mydb |
+----------------+
| 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）在mydb库中创建一个学员表

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

mysql> CREATE TABLE mydb.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.31sec)

)查看student表的字段结构：

mysql> DESC mydb.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.00 sec)

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

mysql> SHOW CREATE TABLE mydb.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 配置，然后重启数据库服务。

[[email protected] ~]# vim /etc/my.cnf                          //修改运行服务配置
[mysqld]
.. ..
character_set_server=utf8

[[email protected] ~]# systemctl restart mysqld                  //重启服务
.. ..
[[email protected] ~]# 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)

附：
Add ： 添加字段
Modify ： 修改字段类型
Change ：修改字段名
Drop： 删除字段
Rename： 修改表名