mysql数据库基本操作练习

23rd

一、MySQL基本管理
1．使用mysql命令连接数据库。

2．练习查看/删除/创建库的相关操作。

3．练习查看/删除/创建表的相关操作。

实验步骤：
1．使用mysql命令连接数据库

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

    [iyunv@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 (c) 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 -uroot -p  回车在交互操作中输入密码，安全性比较高。

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

    [iyunv@dbsvr1 ~]# mysql -u root -p -h 127.0.0.1 -P 3306    //登录本地数据库，-u是定用户，-p交互输入密码，-h主机  -P指定端口
    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 (c) 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服务，有一个前提条件——对方已经添加了此用户从此客户机访问的数据库授权。

2．练习查看/删除/创建库的相关操作

以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;                                       //切换到数据库test
    Database changed
    mysql> SELECT DATABASE();                         //确认当前所在的库
    +------------+
    | DATABASE() |
    +------------+
    | test |
    +------------+
    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 |                                                        //mysql
    +------------+
    1 row in set (0.00 sec)

3）创建新的库

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

    mysql> CREATE DATABASE mydb;       //创建数据库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;       //新建数据库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)

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

    [iyunv@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;                //删除数据库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)

3．练习查看/删除/创建表的相关操作

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

查看test库里有哪些表：

    mysql> USE test;     //切换到数据库test
    Database changed
    mysql> SHOW TABLES;       //查看当前数据库中的表信息
    Empty 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> 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(     //新建表pwlist
    -> name CHAR(16) NOT NULL,      //设置name字段属性，字符型，长度16，内容不能为空
    -> password CHAR(48) DEFAULT '',  //设置密码字段属性，字符行，长度48，默认为空
    -> PRIMARY KEY(name)            //设置主键为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;        //查询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;        //删除表pwlist
    Query OK, 0 rows affected (0.01 sec)

确认删除结果：

    mysql> SHOW TABLES;
    Empty set (0.00 sec)

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



在MySQL表内存储中文数据时，需要更改字符集（默认为latin1不支持中文）。若要修改MySQL服务的默认字符集，可参考本节扩展部分的方法，以使MySQL支持存储中文数据记录；或者，也可以在创建库或表的时候，手动添加“DEFAULT CHARSET=utf8”来更改。

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

    mysql> CREATE TABLE test.student(     //在数据库test中创建表student
    -> 学号 char(9) NOT NULL,                  //学号字段为字符型，长度为9，内容不能空
    -> 姓名 varchar(4) NOT NULL,               //姓名字段为不定长字符型，长度4，不能为空
    -> 性别 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;       //查看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)

**** 扩展实验****

1．修改MySQL服务器使用的默认字符集

1）调整字符集设置：

    [iyunv@dbsvr1 ~]# vim /etc/my.cnf     //编辑数据库住配置文件
    [mysqld]
    .. ..
    character_set_server=utf8          //设置默认字符集为utf8

2）重启服务程序：

    [iyunv@dbsvr1 ~]# service mysql restart      //重启mysql服务
    Shutting down MySQL.. [确定]
    Starting MySQL. [确定]

3）确认设置结果：

    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)

2.MySQL数据类型

1．在home库里创建famliy表，表结构自定义。

2．定义表时的字段约束、默认值、主键设置。

3．练习各种时间函数的使用。
实验步骤：
1．创建home库、family表

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

    mysql> CREATE DATABASE home;     /新建home库
    Query OK, 1 row affected (0.00 sec)
    mysql> USE home;                   //切换到home库
    Database changed

2）新建family表

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

    mysql> CREATE TABLE family(       //新建home表
    -> name varchar(16) NOT NULL,
    -> gender enum('male','femal') DEFAULT 'male',
    -> birth date NOT NULL,
    -> job varchar(16) DEFAULT '',
    -> relation varchar(24) NOT NULL,
    -> PRIMARY KEY(name)
    -> );
    Query OK, 0 rows affected (0.06 sec)

查看family表的字段结构：

    mysql> DESC family;      //查看family表的结构
    +----------+----------------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +----------+----------------------+------+-----+---------+-------+
    | name | varchar(16) | NO | PRI | NULL | |
    | gender | enum('male','femal') | YES | | male | |
    | birth | date | NO | | NULL | |
    | job | varchar(16) | YES | | | |
    | relation | varchar(24) | NO | | NULL | |
    +----------+----------------------+------+-----+---------+-------+
    5 rows in set (0.00 sec)

2．练习各种时间函数的使用

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

    mysql> SELECT now();     //查看当前时间和日期
    +---------------------+
    | now() |
    +---------------------+
    | 2014-01-07 18:33:31 |
    +---------------------+
    1 row in set (0.05 sec)

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

    mysql> SELECT sysdate();     //查看当前系统日期和时间
    +---------------------+
    | sysdate() |
    +---------------------+
    | 2014-01-07 18:34:12 |
    +---------------------+
    1 row in set (0.00 sec)

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

    mysql> SELECT curdate();                //查看当前日期
    +------------+
    | curdate() |
    +------------+
    | 2014-01-07 |
    +------------+
    1 row in set (0.00 sec)

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

    mysql> SELECT curtime();    //查看当前时间
    +-----------+
    | curtime() |
    +-----------+
    | 18:35:26 |
    +-----------+
    1 row in set (0.00 sec)

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

    mysql> SELECT year(now()),month(now()),day(now());     //查看当前时间：年-月-日
    +-------------+--------------+------------+
    | year(now()) | month(now()) | day(now()) |
    +-------------+--------------+------------+
    | 2014 | 1 | 7 |
    +-------------+--------------+------------+
    1 row in set (0.00 sec)

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

    mysql> SELECT month(sysdate()),day(sysdate());        //查看系统时间中的月-日信息
    +------------------+----------------+
    | month(sysdate()) | day(sysdate()) |
    +------------------+----------------+
    | 1 | 7 |
    +------------------+----------------+
    1 row in set (0.00 sec)

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

    mysql> SELECT time(sysdate());              //获取当前系统时间
    +-----------------+
    | time(sysdate()) |
    +-----------------+
    | 18:44:01 |
    +-----------------+
    1 row in set (0.00 sec)
注：在mysql语句中  关键字和命令字可以不区分大小写，但是数据库名和表名区分大小写，各字段之间需用“逗号”分隔，以“分号”结尾。；分割sql语句    \c终止sql语句   \G换行