A Basic MySQL Tutorial
Tags: mysql, guide
Published: 2019-07-25 02:12:12

> MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于Oracle旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的RDBMS(Relational Database Management System，关系数据库管理系统) 应用软件。

## 1. 如何安装MySQL

Ubuntu:

```bash
sudo apt install mysql-server
```

Centos:

```bash
wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
sudo rpm -ivh mysql57-community-release-el7-9.noarch.rpm
sudo yum install mysql-server
```

## 2. 如何连接到Mysql

成功安装MySQL后使用以下命令连接数据库的命令行：

```bash
mysql -u root -p
```

输入密码后，就可以开始操作数据库了。
有两点需要注意：

1. 所有MySQL的命令都以分号结尾，不见分号，命令行不执行输入指令。
2. 一般MySQL命令的关键字大写，以更好区分和读懂SQL命令，但是命令行并不区分大小写。

## 2. 数据库的创建与删除

列出用户的所有数据库:

```sql
SHOW DATABASES;
```

命令行会打印类似如下内容:

```sql
 mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)
```

创建数据库:

```sql
 CREATE DATABASE database name;
```

之后可见创建的名为`events`的数据库:

```sql
 mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| events             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
```

删除数据库：

```sql
 DROP DATABASE database name;
```

## 3. 表的创建与操作

首先需要切换到一个被指定的数据库中，比如`events`:

```sql
 USE events;
```

像列出所有可用的数据库一样，使用`SHOW`可以列出所在库中的所有表名：

```sql
SHOW tables;
```

对表的操作和其他关系型数据库的SQL大同小异，建一个名为`potluck`的表：

```sql
CREATE TABLE potluck (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
name VARCHAR(20),
food VARCHAR(30),
confirmed CHAR(1),
signup_date DATE);
```

此时再次执行`SHOW tables`命令查看输出结果：

```sql
 mysql> SHOW TABLES;
+------------------+
| Tables_in_events |
+------------------+
| potluck          |
+------------------+
1 row in set (0.01 sec)
```

使用`DESCRIBE`命令可以查看表的结构：

```sql
 mysql>DESCRIBE potluck;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| name        | varchar(20) | YES  |     | NULL    |                |
| food        | varchar(30) | YES  |     | NULL    |                |
| confirmed   | char(1)     | YES  |     | NULL    |                |
| signup_date | date        | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
```

## 4. 数据的增删改查

数据的插入示例：

```sql
INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "John", "Casserole","Y", '2012-04-11');
```

执行成功后会有反馈

```sql
 Query OK, 1 row affected (0.00 sec)
```

再次插入更多的数据：

```sql
INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Sandy", "Key Lime Tarts","N", '2012-04-14');
INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Tom", "BBQ","Y", '2012-04-18');
INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Tina", "Salad","Y", '2012-04-10'); 
```

`SELECT` 用来查询数据：

```sql
mysql> SELECT * FROM potluck;
+----+-------+----------------+-----------+-------------+
| id | name  | food           | confirmed | signup_date |
+----+-------+----------------+-----------+-------------+
|  1 | John  | Casserole      | Y         | 2012-04-11  |
|  2 | Sandy | Key Lime Tarts | N         | 2012-04-14  |
|  3 | Tom   | BBQ            | Y         | 2012-04-18  |
|  4 | Tina  | Salad          | Y         | 2012-04-10  |
+----+-------+----------------+-----------+-------------+
4 rows in set (0.00 sec)
```

`UPDATE` 用来查询数据：

```sql
UPDATE `potluck`
SET
`confirmed` = 'Y'
WHERE `potluck`.`name` ='Sandy';
```

使用如下命令来删除一行记录：

```sql
DELETE from [table name] where [column name]=[field text];
```

```sql
mysql> DELETE from potluck  where name='Sandy';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM potluck;
+----+------+-----------+-----------+-------------+
| id | name | food      | confirmed | signup_date |
+----+------+-----------+-----------+-------------+
|  1 | John | Casserole | Y         | 2012-04-11  |
|  3 | Tom  | BBQ       | Y         | 2012-04-18  |
|  4 | Tina | Salad     | Y         | 2012-04-10  |
+----+------+-----------+-----------+-------------+
3 rows in set (0.00 sec)
```