爱站程序员基地数据库基本管理：
• 使用mysql命令连接数据库
• 练习查看/删除/创建库的相关操作
• 练习查看/删除/创建表的相关操作
步骤一：使用mysql命令连接数据库
连接MySQL服务器时，最基本的用法是通过 -u 选项指定用户名、-p指定密码。密码可以写在命令行（如果不写，则出现交互，要求用户输入）
[root@zhangyx ~]# mysql -uroot -p123456 //紧挨着选项，不要空格
[root@zhangyx ~]# mysql -uroot -p123456mysql: [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 3Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.mysql> exitBye
默认情况下，msyql命令会连接本机的MySQL服务。但在需要的时候，可以通过 -h 选项指定远程主机；
[root@zhangyx ~]# mysql -h 127.0.0.1 -u root -p
[root@zhangyx ~]# mysql -h 127.0.0.1 -u root -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \\g.Your MySQL connection id is 4Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.mysql> exitBye
步骤二：练习查看/删除/创建库的相关操作
以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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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 changedmysql> 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: HostType: char(60)Null: NOKey: PRIDefault:Extra:*************************** 2. row ***************************Field: DbType: char(64)Null: NOKey: PRIDefault:Extra:*************************** 3. row ***************************Field: UserType: char(32)Null: NOKey: PRIDefault:Extra:*************************** 4. row ***************************Field: Table_nameType: char(64)Null: NOKey: PRIDefault:Extra:*************************** 5. row ***************************Field: Column_nameType: char(64)Null: NOKey: PRIDefault:Extra:*************************** 6. row ***************************Field: TimestampType: timestampNull: NOKey:Default: CURRENT_TIMESTAMPExtra: on update CURRENT_TIMESTAMP*************************** 7. row ***************************Field: Column_privType: set(\'Select\',\'Insert\',\'Update\',\'References\')Null: NOKey: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; //手工指定字符集，采用utf8Query 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 配置，然后重启数据库服务。
[root@dbsvr1 ~]# vim /etc/my.cnf //修改运行服务配置[mysqld].. ..character_set_server=utf8[root@dbsvr1 ~]# systemctl restart mysqld //重启服务.. ..[root@dbsvr1 ~]# mysql –u root -pEnter 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： 修改表名
