MySQL 学习笔记（1）— 创建/连接/选择/显示数据库(表) 查询单列(多列/所有列)/查询返回特定的行数 各种排序(单列/多列/降序/组合排序) 过滤数据

本文是对之前学习 MySQL 的一个总结，使用思维导图的方式将涉及到的知识点罗列出来，一方面作为自己对于知识点的归纳，另一方面也便于日后查询。

在该篇文章中将对 MySQL 的基本使用、检索基本数据、数据排序和过滤数据部分做一小结，具体见图。

• 单行注释
  – 注释
  MySQL 中也可以使用 # 单行注释
• 多行注释
  /* 多行注释 */

1. 连接、创建、选择、显示数据库

数据库（Database）由一组相关的对象组成，主要包括表、索引、视图、存储过程等。为了方便对象的管理和访问，数据库通常使用模式（Schema）来组织这些对象；模式是一个逻辑单元，或者一个存储对象的容器。

一个数据库由多个模式组成，一个模式由许多对象组成；在不同模式中可以创建同名的对象。

MySQL 中的模式和数据库是相同的概念，一个数据库对应一个同名的模式。

删除数据库使用 DROP DATABASE <数据库名称> ;

mysql -h 127.0.0.1 -uroot -P 3307 -p3307    # 连接数据库
mysqladmin -u用户名 -p旧密码 password 新密码            # 更改密码

1.1 管理模式

1.1.1 创建模式

创建了数据库之后，还需要创建模式才能够存储数据库对象。

创建模式 CREATE SCHEMA 命令用于创建一个新的模式：

-- SQL Server 以及 PostgreSQL 实现
CREATE SCHEMA hr
    [AUTHORIZATION user1];

以上语句创建一个名为 hr 的模式，可选的 AUTHORIZATION 表示为该模式指定一个拥有者 user1，拥有者是一个已经存在的数据库用户。

• SQL Server 创建数据库时会自动创建一个名为 dbo 的模式；
• PostgreSQL 创建数据库时会自动创建一个名为 public 的模式。
• MySQL 中的模式等价于数据库，因此 CREATE SCHEMA 等价于 CREATE DATABASE。
• Oracle 中的模式等价于用户，因此使用 CREATE USER 命令创建用户时就相当于创建一个同名的模式：

CREATE USER hr
  IDENTIFIED BY xxx;

Oracle 也提供了 CREATE SCHEMA 命令，但不是用于创建模式，而是用于在模式中创建表、视图以及执行授权操作。

1.1.1 删除模式

不需要的模式可以使用 DROP SCHEMA 命令删除：

-- SQL Server 以及 PostgreSQL 实现
DROP SCHEMA hr;

MySQL 中的模式等价于数据库，因此 DROP SCHEMA 等价于 DROP DATABASE。

Oracle 中的模式等价于用户，因此使用 DROP USER 命令创建用户时就相当于创建一个同名的模式：

-- Oracle 实现
DROP USER hr;

如果模式中存在对象，则无法删除该模式；可以先删除其中的对象，再删除模式。某些数据库支持级联删除：

-- PostgreSQL 实现
DROP SCHEMA hr CASCADE;

-- Oracle 实现
DROP USER hr CASCADE;

CASCADE 表示删除模式的同时，删除该模式中的所有对象。

2. 查询检索单列、多列、不重复数据及限制返回个数

SQL 使用 DISTINCT 关键字去除查询结果中的重复数据。注意两点：

1. DISTINCT 位于 SELECT 之后而不是像其他过滤条件一样位于 WHERE 之后；
2. 查询结果中重复的记录只会出现一次。

• 为列设置别名

SELECT product_id AS id, product_name AS name 
  FROM product;

• 为表设置别名

SELECT p.product_id AS id, p.product_name AS name 
  FROM product AS p;

3. 查询结果排序

1. ORDER BY 用于指定排序的字段；

• ASC 表示升序排序（Ascending），
• DESC 表示降序排序（Descending），默认值为升序排序。

多列排序是指基于多个字段或表达式的排序，使用逗号进行分隔。多列排序的语法如下：

SELECT col1, col2, ...
  FROM t
 ORDER BY col1 ASC, col2 DESC, ...;

首先基于第一个字段进行排序；对于第一个字段排序相同的数据，再基于第二个字段进行排序；依此类推。

2. 对于含有空值 NULL 的排序，不同数据库有区别：

• MySQL 和 SQL Server 认为空值最小，升序时空值排在最前，降序时空值排在最后；
• Oracle 和 PostgreSQL 认为空值最大，升序时空值排在最后，降序时空值排在最前；同时支持使用 NULLS FIRST 和 NULLS LAST 指定空值的顺序。

解决空值的排序问题还有一个更通用的方法，就是利用 COALESCE 函数将空值转换为一个指定的值。例如，将奖金为空的数据转换为 0，这样升序排序时一定在最前：

SELECT emp_name, COALESCE(bonus, 0) AS bonus
  FROM employee
 WHERE dept_id = 2
 ORDER BY COALESCE(bonus, 0);

4. 过滤数据（where 子句）

判断是否为 NULL 时，只能使用特定的谓词 IS NULL 或者 IS NOT NULL

• 选取 NULL 的记录使用 is NULL

SELECT product_name, purchase_price 
  FROM product 
 WHERE purchase_price is NULL;      

• 选取非 NULL 的记录使用 is not NULL

SELECT product_name, purchase_price 
  FROM product 
 WHERE purchase_price is not NULL;