一、MySQL基本管理

1.使用mysql命令连接数据库。


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


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


实验步骤:

1.使用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 (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 选项指定:


    [root@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)


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


    [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;                //删除数据库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)调整字符集设置:


    [root@dbsvr1 ~]# vim /etc/my.cnf     //编辑数据库住配置文件

    [mysqld]

    .. ..

    character_set_server=utf8          //设置默认字符集为utf8


2)重启服务程序:


    [root@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换行