# mysql

**Repository Path**: jinzhengdong/mysql

## Basic Information

- **Project Name**: mysql
- **Description**: 数据库教程，介绍关系数据库MySQL
- **Primary Language**: SQL
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: https://gitee.com/jinzhengdong/mysql/blob/master/README.md
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 2
- **Created**: 2021-06-01
- **Last Updated**: 2024-03-25

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

## 开始

### 什么是数据库

* 什么是数据库？
  数据库是一个存储数据的集合，在这个集合中，数据以一定格式被存储以便数据能够被容易的访问。
* 什么是数据库管理系统（DBMS）?
  管理数据库的系统叫做数据库管理系统。

数据库分为：

* 关系型数据库（Relational）：以关系表的形式存储数据，并且以结构化查询语言来操纵数据（SQL, Structured Query Language），关系数据库管理系统（RDBMS）包括：MySQL，SQL Server，Oracle等。
* 非关系型数据库（NoSQL）与关系型数据库非常的不同，需要单独的课程讲解，我们需要明白的仅是：NoSQL不理解SQL，他们有自己的查询语言。

### 安装MySQL

* [下载MySQL](https://dev.mysql.com/downloads/mysql/)
* [安装MySQL](https://www.php.cn/mysql-tutorials-454993.html)
* [演示数据库脚本](https://gitee.com/jinzhengdong/mysql/blob/master/resources/db.sql)

### 创建数据库

* ```Windows```下，完成```MySQL```安装后我们可以看到```MySQL Workbench```开发工具，启动并进入我们可以看到下面界面：

<img src="./images/img001.png" width="600"/>

* 上图，点击红框指示的按钮打开之前下载的数据库脚本，脚本打开后点击右侧面板的蓝框指示的执行按钮运行脚本完成数据库的创建
* 完成数据库创建后，左侧面包将显示以下数据库设备：
  * sql_hr
  * sql_inventory
  * sql_invoicing
  * sql_store
* 尝试使用并熟练该工具

### 学习内容介绍

我们首先要学习结构化查询语言最基本的四个子句，内容包括：

* 查询数据（select）
* 插入数据（insert）
* 更新数据（update）
* 删除数据（delete）

之后我们还要学习：

* 汇总数据（summarizing data）
* 编写复杂的查询
* 学习内置函数（聚合函数）
* 视图（Views）
* 存储过程（Store Procedures）
* 触发器（Triggers）
* 事件（Events）
* 事务（Transactions）
* 并发（Concurrency）
* 数据库设计
* 索引及数据库性能
* 数据库安全

## 单表查询

这一节讲述如何从单表查询数据，从```MySQL Workbench```来探查数据库，每个数据库都是一个容器，其中有很多数据对象：

* 表（Tables）
  * 列（Columns），组成表的字段
  * 索引（Indexes），反映表中一列或多列的排序情况。
  * 外键（Foreign Keys），反映表间某种约定的关系。
  * 触发器（Triggers），在表中进行insert，update，delete前后可以调用相应的触发器进行数据处理。
* 视图（Views），本质上就是查询
* 存储过程（Stored Procedures）
* 函数（Functions）

### Select语句

```sql
--设置缺省数据库
use sql_store;

--查询语句形式一
select * from customers;

--查询语句形式二
select
  *
from
  customers
where
  customer_id = 1
order by
  first_name;

--select 常数
select 1, 2
```

### select 子句

```sql
--查询指定表重的所有字段
select
  *
from
  customers;

--查询指定表重的指定字段
--查询的数学表达式支持及命名
select
  first_name,
  last_name,
  points,
  points + 10 as discount
from
  customers

-- distinct
select
  distinct state
from
  customers;
```

练习：

```sql
--返回products的所有行，其中字段列表包括：
--  name
--  unit_price
--  new_price => (unit_price * 1.1)
```

### where 子句

```sql
select
  *
from
  customers
where
  points > 3000;
```

where子句支持以下比较操作符号：

* >，大于
* >=，大于等于
* <，小于
* <=，小于等于
* =，等于
* !=，不等于
* <>，不等于

再参看下面例子：

```sql
select
  *
from
  customers
where
  state = 'va';

select
  *
from
  customers
where
  birthdate > '1990-01-01';
```

练习：

```sql
--从orders表中返回2019年的数据
```

### and or not 操作符

看下面例子：

```sql
select
  *
from
  customers
where
  birth_date > '1990-01-01' and points > 1000;

select
  *
from
  customers
where
  birth_date > '1990-01-01' or points > 1000;

select
  *
from
  customers
where
  birth_date > '1990-01-01' or 
  (points > 1000 and state = 'va');

select
  *
from
  customers
where not
  (birth_date <=> '1990-01-01' and points <=> 1000);
```

练习：

* 从order_items获取订单号为6的，并且总价大于30的订单条目

### in 操作符

```sql
select
  *
from
  customer
where state in ('va', 'fl', 'ga');

select
  *
from
  customer
where state not in ('va', 'fl', 'ga');
````

练习：

* 从products中查询库存为49, 38, 72

### between 操作符

```sql
select
  *
from
  customers
where
  pointss between 1000 and 3000;
```

练习：

* 从customers中获取生日在1990-01-01和2000-01-01之间的顾客

### like 操作符

查询中的通配符包括：

* % 表示任何数字或字符
* _ 表示单个字符

```sql
select
  *
from
  customers
where
  last_name like 'b%';

select
  *
from
  customers
where
  last_name like 'brush%';

select
  *
from
  customers
where
  last_name like '%b%';

select
  *
from
  customers
where
  last_name like '_____y';

select
  *
from
  customers
where
  last_name like 'b____y';
```

练习：

* 从customers表中获取地址包含 trail 或 avenue；电话号码以9结束的顾客。

### regexp 正则表达式操作符

```sql
--查询结果包含'field'
select
  *
from
  customers
where
  last_name regexp 'field';

--查询结果开始于'field'
select
  *
from
  customers
where
  last_name regexp '^field';

--查询结果以'field'结尾
select
  *
from
  customers
where
  last_name regexp 'field$';

--查询结果包含'field'或'mac'...
select
  *
from
  customers
where
  last_name regexp 'field$|mac|^rose';

--查询结果包含‘ge’,'ie', 'me'
select
  *
from
  customers
where
  last_name regexp '[gim]e';

select
  *
from
  customers
where
  last_name regexp '[a-h]e'
```

在正则表达式中：

* ```^```表示开始
* ```$```表示结束
* ```|```表示逻辑或
* ```[abcd]```表示包含列表中的各字母
* ```[a-f]```表示包含按字母序a到f的各字母

练习：

* 从```customers```表中查询
  * ```first_name```包含```elka```或```ambur```
  * ```last_name```以```ey```或```on```结尾
  * ```last_name```以```my```开始或包含```se```
  * ```last_name```包含```b```之后跟随字母```r```或```u```

### IsNull 操作符

本节演示如何查询字段值是```null```的情况，看下面例子：

```sql
--查询电话号码为空值的记录
select
  *
from
  customers
where
  phone is null;

--查询电话号码不为空值的记录
select
  *
from
  customers
where
  phone is not null;
```

练习：

* 从```orders```表中获取那些没有被递送的记录

### Order By 子句

本节演示```order by```排序子句，请看下面例子：

```sql
--first_name 正序排
select
  *
from
  customers
order by first_name;

--first_name正序排
select
  *
from
  customers
order by first_name asc;

--first_name倒序排
select
  *
from
  customers
order by first_name desc;

select
  *
from
  customers
order by state desc, first_name desc;

--1 => firstname, 2 => last_name
select
  first_name,
  last_name
from
  customers
order by 1, 2
```

练习：

* 挑选一张表练习相关的排序操作

### Limit 子句

查看下面的例子：

```sql
--查询头部的3条数据
select
  *
from
  customers
limit 3;

--查询结果按3条分一页进行分页，并显示第六页的数据
select
  *
from
  customers
limit 6, 3
```

练习：

* 获取头三名忠诚的客户

## 多表查询

### Inner Join

参看下面例子：

```sql
use sql_store;

select
	  o.order_id,
    o.customer_id,
    c.first_name,
    c.last_name
from
    orders o
    left outer join customers c on o.customer_id = c.customer_id
order by
    o.order_id;
```

### 跨数据库的Join

设置了缺省库时跨库的Join连接可用下面方式：

```sql
use sql_store;

select
    *
from
    order_items oi
    left outer join sql_inventory.products p on oi.product_id = p.product_id;
```

或者使用下面的查询语句则不需要指定缺省的数据库设备：

```sql
select
    *
from
    sql_store.order_items oi
    left outer join sql_inventory.products p on oi.product_id = p.product_id;
```

### 自引用 Self Join

查看employees表结构，我们发现```reports_to```字段指向表中的```employee_id```，那位```3720```雇员就是组织机构的管理员：

```sql
use sql_hr;
select
    e.employee_id,
    e.first_name,
    m.first_name as manager
from
    employees e
left outer join employees m on e.reports_to = m.employee_id
```

### 多表Join

看下面的例子：

```sql
use sql_store;

select
    o.order_id,
    o.order_date,
    c.first_name,
    c.last_name,
    os.name as status
from
    orders o
left outer join customers c using(customer_id)
left outer join order_statuses os on o.status = os.order_status_id
```

再看另一例子：

```sql
use sql_invoicing;

select
    p.date,
    p.invoice_id,
    p.amount,
    c.name as client,
    pm.name as payment_method
from
    payments p
left outer join clients c using(client_id)
left outer join payment_methods pm on p.payment_method = pm.payment_method_id
```

### 组合Join

```sql
use sql_store;
select
    *
from
    order_items oi
join order_item_notes oin 
    on oi.order_Id = oin.order_Id and oi.product_id = oin.product_id;
```

### 非直言join

```sql
use sql_store;
select
    *
from
    orders o, customers c
where
    o.customer_id = c.customer_id
```

### Outer Join

缺省情况，所有的join都是inner join，我没看下面的例子:

Inner Join:

```sql
use sql_store;
select
    c.customer_id,
    c.first_name,
    o.order_id
from
    customers c
join
    orders o using(customer_id)
order by
    c.customer_id;
```

Outer Join:

```sql
use sql_store;
select
    c.customer_id,
    c.first_name,
    o.order_id
from
    customers c
left outer join
    orders o using(customer_id)
order by
    c.customer_id
```

* 以上两个Join查询，left join将显示所有来自customers表中的记录
* 如果使用right outer join则所有来自orders表中记录将被显示

### 多表间的 outer join

下面的查询显示inner join的查结果：

```sql
select
    c.customer_id,
    c.first_name,
    o.order_id
from
    customers c
join orders o using(customer_id)
order by
    c.customer_id
```

下面则是left outer join的查询结果：

```sql
select
    c.customer_id,
    c.first_name,
    o.order_id
from
    customers c
left outer join orders o using(customer_id)
order by
    c.customer_id
```

下面的例子shippers是inner join:

```sql
select
    c.customer_id,
    c.first_name,
    o.order_id
from
    customers c
left outer join orders o using(customer_id)
join shippers sh using(shipper_id)
order by
    c.customer_id
```

下面例子，如果全采用left outer join则如下：

```sql
select
    c.customer_id,
    c.first_name,
    o.order_id,
    sh.name as shipper
from
    customers c
left outer join orders o using(customer_id)
left outer join shippers sh using(shipper_id)
order by
    c.customer_id
```

练习：

* 看下面的inner join例子：

```sql
select
    o.order_id,
    o.order_date,
    c.first_name as customer,
    sh.name as shipper
from
    orders o
join customers c using(customer_id)
join shippers sh using(shipper_id)
```

看下面outer join 的例子：

```sql
select
    o.order_id,
    o.order_date,
    c.first_name as customer,
    sh.name as shipper,
    os.name as status
from
    orders o
join customers c using(customer_id)
left join shippers sh using(shipper_id)
join order_statuses os on o.status = os.order_status_id
```

### Self Outer Joins

看下面例子使用inner join：

```sql
use sql_hr;

select
    e.employee_id,
    e.first_name,
    m.first_name as manager
from
    employees e
join
    employees m on e.reports_to = m.employee_id
```

下面的例子使用left outer join

```sql
use sql_hr;

select
    e.employee_id,
    e.first_name,
    m.first_name as manager
from
    employees e
left outer join
    employees m on e.reports_to = m.employee_id
```

上面例子拿掉outer后仍然是left outer join:

```sql
use sql_hr;

select
    e.employee_id,
    e.first_name,
    m.first_name as manager
from
    employees e
left join
    employees m on e.reports_to = m.employee_id
```

注意：

* MySQL查询中join通常指left inner join；而left join通常指left outer join

### Using子句

* 请考察下面的例子, join连接时两张表的关联字段完全一样：

```sql
use sql_store;

select
    o.order_id,
    c.first_name
from
    orders o
join customers c
    on o.customer_id = c.customer_id
```

* 如果关联表的关联字段完全一样则可以使用using子句来描述关联字段，如下：

```sql
use sql_store;

select
    o.order_id,
    c.first_name
from
    orders o
join customers c
    using (customer_id)
```

* 同样的方式我们可以关联其他表如下：

```sql
use sql_store;

select
    o.order_id,
    c.first_name,
    sh.name as shipper
from
    orders o
join customers c
    using (customer_id)
join shippers sh
    using (shipper_id)
```

* 如果我们使用left outer join连接shippers如下：

```sql
use sql_store;

select
    o.order_id,
    c.first_name,
    sh.name as shipper
from
    orders o
join customers c
    using (customer_id)
left join shippers sh
    using (shipper_id)
```

* 最后，请考察下面复合关联的例子：

```sql
use sql_store;

select
    *
from
    order_items oi
join
    order_item_notes oin
        on oi.order_id = oin.order_id and
            oi.product_id = oin.product_id
```

* 对于上面的例子，我们可以按下面方式使用using进行简化：

```sql
use sql_store;

select
    *
from
    order_items oi
join
    order_item_notes oin
        using (order_id, product_id)
```

练习下面查询：

```sql
use sql_invoicing;

select
    p.date,
    c.name as client,
    p.amount,
    pm.name as payment_method
from
    payments p
join clients c using (client_id)
join payment_methods pm
    on p.payment_method = pm.payment_method_id
```

### natural join

参看下面的例子，我们使用natural join后并未指定连接的关联字段：

```sql
use sql_store;

select
    o.order_id,
    c.first_name
from
    orders o
natural join customers c
```

注意：

在使用natural join后查询会使用两表中共同的字段customer_id进行关联，这种方式并不常用。

### 交叉关联 Cross Joins

查看下面例子，执行交叉连接是，第一张表的记录会逐条与第二张表的记录进行迭代，因此，不需要指定关联条件：

```sql
use sql_store;

select
    c.first_name as customer,
    p.name as product
from
    customers c
cross join products p
order by c.first_name
```

交叉关联也可用下面的语句表示：

```sql
use sql_store;

select
    c.first_name as customer,
    p.name as product
from
    customers c,
    products p
order by c.first_name
```

### 联合 Unions

查看下面联合查询的例子：

```sql
use sql_store;

select
    order_id,
    order_date,
    'Active' as status
from
    orders
where
    order_date >= '2019-01-01'
    
union

select
    order_id,
    order_date,
    'Archived' as status
from
    orders
where
    order_date < '2019-01-01'
```

## 插入、更新及删除数据

### 列属性（字段属性）

我们考察一张表，一张表由多个字段组成，而每个字段又包含以下属性：

* 字段名（column name），例如：customer_id, first_name
* 字段的数据类型（Datatype），例如：int, varchar
* 约束条件及索引：
  * PK, Primary Key
  * NN, Not Null
  * UQ, Unique
  * BIN, Binary
  * UN, Unsigned Data Type
  * ZF, for numeric column, it will be filled with zero
  * AI, Auto Incremental
  * G, Generated Column
  * Default/Expression, default value or expression

### 插入行

如果没有指定插入的字段列表，则请按表的字段顺序填写相关的插入值，如下：

```sql
insert into customers
values (
	  200,
    'Jerry',
    'Smith',
    '1968-08-20',
    '13888000000',
    '470# Bailong RD, Kunming, Yunnan, China',
    'KM',
    'YN',
    1000
);
```

我们也可以提供插入的字段列表如下：

```sql
insert into customers (
	  customer_id,
    first_name,
    last_name,
    birth_date,
    phone,
    address,
    city,
    state,
    points
)
values (
	  200,
    'Jerry',
    'Smith',
    '1968-08-20',
    '13888000000',
    '470# Bailong RD, Kunming, Yunnan, China',
    'KM',
    'YN',
    1000
);
```