前言
很久没有用MySQL,很多东西都是有个大致印象,具体操作起来手就生了,想要系统的复习一下,发现一本不错的书,《MySQL必知必会》,理论不多,几乎全部是实践,同时还附赠了数据源,正好解决了自己练习MySQL操作时没有合适数据源,新建数据又太繁琐的问题,话不多说,先把环境搭起来。文中用到的数据库脚本下载地址:下载地址
安装步骤
sudo apt-get install mysql-server
安装过程中会提示设置数据库root用户密码,按提示设置sudo apt-get install mysql-client
sudo apt-get install libmysqlclient-dev
安装完毕后登录测试mysql -u root -p
输入密码后终端输出如下表示安装成功
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.12-0ubuntu1.1 (Ubuntu)
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>
先退出mysql,接下来安装图像化管理工具:
sudo apt-get install mysql-workbench
安装完毕后终端输入mysql-workbench
启动管理工具:
点击左上角输入密码进入管理界面,另外可以将workbench的图标锁定到启动器,方便下次启动
点击箭头所指图标创建新数据库,设置如下图,create schema 与 create database 是一样的,前者是后者的别名
创建完数据库后点击查询窗口内的文件夹图标打开create.sql
脚本,在脚本前面加上一行use my_database;
来指定使用我们刚刚创建的数据库,点击上方的闪电按钮执行脚本创建相应的数据表,如下图:
同理打开populate.sql
脚本创建数据。
打开一个新的查询窗口,输入select * from customers;
执行后下方会输出结果,说明安装导入正常,可以进行自己的练习了。也可以在终端命令行下进入练习,下面是安装完后命令行下测试结果:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| my_database |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.07 sec)
mysql> use my_database;
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_my_database |
+-----------------------+
| customers |
| orderitems |
| orders |
| productnotes |
| products |
| vendors |
+-----------------------+
6 rows in set (0.00 sec)
mysql> select * from custorms;
ERROR 1146 (42S02): Table 'my_database.custorms' doesn't exist
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| my_database |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use my_database;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_my_database |
+-----------------------+
| customers |
| orderitems |
| orders |
| productnotes |
| products |
| vendors |
+-----------------------+
6 rows in set (0.00 sec)
mysql> select * from customers;
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com |
| 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL |
| 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com |
| 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com |
| 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
5 rows in set (0.00 sec)
另外使用apt安装后的一些目录如下:
- 数据库存放目录: /var/lib/mysql/
- 相关配置文件存放目录: /usr/share/mysql
- 相关命令存放目录: /usr/bin
- 启动脚本存放目录: /etc/rc.d/init.d/