MySQL 学习

目录

• 基本知识
  • 打开并使用
  • 基本语法
    • USE 的用法
    • SHOW 的用法
    • SELECT 的用法
      • 检索列
      • 限制结果（LIMIT）
      • 排序检索数据（ORDER BY）
      • 过滤数据（WHERE）
      • 创建计算字段
      • 综合使用
      • SELECT 子句顺序
    • INSERT 的用法
      • 插入完整的行
      • 插入多行
      • 插入检索出的数据
  • 数据处理函数
    • 函数
      • 文本处理函数
      • 日期和时间处理函数
      • 数值处理函数
  • 汇总与分组数据（SELECT 语句下使用）
    • 汇总数据
      • SQL聚集函数
      • 组合聚集函数
    • 分组数据（GROUP BY）
      • 创建分组
      • GROUP BY 使用注意
  • 子查询
    • 利用子查询进行过滤
    • 作为计算字段使用子查询
  • 组合查询
    • 创建组合查询（ UNION ）
    • UNION 规则
    • 包含或取消重复的行
    • 对组合查询结果排序
  • sql 文件
    • 导入 sql 文件
• 常用命令
  • 查看 MySQL 端口

基本知识

打开并使用

开启MySQL服务
service mysqld start
登录MySQL
mysql -u root(用户名) -p

基本语法

USE 的用法

• USE DATABASE;：使用数据库 DATABASE

SHOW 的用法

• SHOW DATABASE;：返回可用的数据库
• SHOW TABLES;：返回当前数据库内的表的列表
• SHOW COLUMNS FROM TABLE_NAME（表名）;：返回当前数据库的表列

SELECT 的用法

检索列

• SELECT 列名 FROM 表名;：检索单个列
• SELECT 表名.列名 FROM 表名/数据库名.表名;：另一种写法
• SELECT 列名，列名 FROM 表名;：检索多个列
• SELECT * FROM 表名;：检索所有列
• SELECT DISTINCT 列名 FROM 表名;：检索不同的行（单列）
• SELECT DISTINCT 列名，列名 FROM 表名;：检索不同的行（多列），只有当多列均重复时，才算重复

限制结果（LIMIT）

• SELECT 列名 FROM 表名 LIMIT 行数;：显示该列不超过行数的行（有可能数据的行小于行数）
• SELECT 列名 FROM 表名 LIMIT 行数1 行数2;：从行数 1 开始显示该列的行，显示的行数的数量为行数2（行数从 0 开始计数，从行数 1 开始包括行数 1 ）
• SELECT 列名 FROM 表名 LIMIT 行数1 OFFSET 行数2;：另一种写法

排序检索数据（ORDER BY）

MySQL 默认是升序排序（ASC），且字母大小写具有相同的顺序。

• 按照单列数据进行排序：

  SELECT 列名1
  FROM 表名
  ORDER BY 列名2;
  

  按照列名 2 对输出结果进行排序，列名 1 和列名 2 可以不相同

• 按照多列数据进行排序：

  SELECT 列名1
  FROM 表名
  ORDER BY 列名2，列名3;
  

  按照列名 2，列名 3 对输出结果进行排序。当列名 2 不同时，按照列名 2 进行排序；当列名 2 相同时，按照列名 3 进行排序

• 指定排序方向

  • 指定单列排序方向：

    SELECT 列名1，列名2，列名3
    FROM 表名
    ORDER BY 列名4 DESC；
    

    按照列名 4 降序对输出结果进行排序
  • 指定多列排序方向：

    SELECT 列名1，列名2，列名3
    FROM 表名
    ORDER BY 列名4 DESC，列名5；
    

    按照列名 4 降序对输出结果进行排序；当列名 4 数据相同时，按照列名 5 升序（MySQL 默认是升序）对输出结果进行排序

    SELECT 列名1，列名2，列名3
    FROM 表名
    ORDER BY 列名4 DESC，列名5 DESC；
    

    按照列名 4 降序对输出结果进行排序；当列名 4 数据相同时，按照列名 5 降序对输出结果进行排序

过滤数据（WHERE）

• WHERE 字句操作符

  操作符	说明
  =	等于
  <>	不等于
  !=	不等于
  <	小于
  <=	小于等于
  >	大于
  >=	大于等于
  BETWEEN	在指定的两个值之间

• 指定搜索条件进行过滤

  SELECT 列名1，列名2
  FROM 表名
  WHERE 列名 = 条件;
  

• 空值检查

  SELECT 列名
  FROM 表名
  WHERE 列名 IS NULL;
  

  当进行过滤选出不具有特定值的值行时，可能希望过滤出具有 NULL 值得行，但是不行。因为未知具有特殊含义，数据库不知道是否匹配

• 范围值检查（BETWEEN）

  SELECT 列名
  FROM 表名
  WHERE 列名 BETWEEN value1 AND value2;
  

注意：value 1 应小于 value 2

• AND OR
  AND 操作符的优先级高于 OR 操作符
• IN 和 NOT

  SELECT 列名1，列名2
  FROM 表名
  WHER 列名 NOT IN (值，值)
  ODER BY 列名3;
  

• LIKE 操作符
  • 百分号（%）通配符

    SELECT 列名1，列名2
    FROM 表名
    WHERE 列名 LIKE ‘字符串%’
    

    在搜索串中，%表示任何字符出现任何次数。
  • 下划线（_）通配符

    SELECT 列名1，列名2
    FROM 表名
    WHERE 列名 LIKE ‘字符串_’
    

    下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。
  • 使用通配符的技巧
  1. 不要过度使用通配符。
  2. 在确实需要使用通配符时，尽量不要用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索速度会很慢。
  3. 注意通配符的位置。放置错误会导致返回错误数据。
• 使用正则表达式
  正则表达式还不太会用，就先不写例子了。

创建计算字段

计算字段主要是对原数据在进行再处理。计算字段并不实际存在于数据库表中。计算字段是运行时在 SELECT 语句中创建的。

• 拼接
  将值连接在一起构成单个值。在 MySQL 的 SELECT 语句中，可使用 Concat() 函数来拼接两个列。在多数 DBMS 中使用 + 或 // 来实现拼接，MySQL则使用 Concat() 来实现。当 SQL 语句转化成 MySQL 语句时一定要注意这个区别。

  SELECT Concat (vend_name, '(', vend_country, ')')
  FROM vendors
  ORDER BY vend_name;
  

  Concat() 拼接串，即把多个串拼接起来形成一个较长的串。Concat() 需要一个或多个指定的串，各个串之间用逗号分隔。
• 执行算数计算
  对检索出的数据进行算数计算。

  SELECT prod_id,
                 quantity,
                 item_price,
                 quantity*item_price AS expanded_price
  FROM orderitems
  WHERE order_num = 2005;
  

  MySQL 算术操作符

  操作符	说明
  +	加
  -	减
  *	乘
  /	除

综合使用

• LIMIT && ORDER BY

   SELECT prod_price
   FROM products
   ORDER BY prod_price DESC
   LIMIT 1；
  

  输出价格最贵的 product 的价格 prod_price。在语句中，ORDER BY 必须在 SELECT 之后，LIMIT 必须在 ORDER BY 之后
• WHERE && ORDER BY
  在同时使用 WHERE 和 ORDER BY 时，应该让 WHERE 在 ODER BY 之后，否则会产生错误。
• GROUP BY && HAVING && ORDER BY

   SELECT order_num, SUM(quantity*item_price) AS ordertotal
   FROM orderitems
   GROUP BY order_num
   HAVING SUM(quantity*item_price) >= 50
   ORDER BY ordertotal
  

SELECT 子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表中选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

INSERT 的用法

INSERT 是用来插入（或添加）行到数据表的，插入可以下面四种方式使用：

• 插入完整的行
• 插入行的一部分
• 插入多行
• 插入某些查询的结果

插入完整的行

INSERT INTO 表名
VALUES(列值1, 列值2,...,列值n)

这种语法虽然简单，但是不安全，应该避免。因为这种语法高度依赖表中各列的定义顺序。如果表中各列的顺序发生变化，这种语法并不能因此做出改变。
所以在插入数据时应该指明列名：

INSERT INTO 表名(列名1, 列名2,...,列名n)
VALUES(列值1, 列值2,...,列值n)

插入多行

使用多条 INSERT 语句，每条语句使用分号结束

INSERT INTO 表名(列名1, 列名2,...,列名n)
VALUES(列值1, 列值2,...,列值n);
INSERT INTO 表名(列名1, 列名2,...,列名n)
VALUES(列值1, 列值2,...,列值n);

插入检索出的数据

INSERT INTO 表名1(列名1, 列名2,...,列名n)
VALUES(列值1, 列值2,...,列值n)
SELECT 列名1, 列名2,...,列名n
FROM 表名2;

数据处理函数

函数

SQL 支持利用函数处理数据，给数据的转化和处理提供了方便。但是函数的可移植性不强，不同 DBMS 之间存在差异。如果决定使用函数，应该做好代码注释，以便以后能确切知道所编写 SQL 代码的含义。
大多数 SQL 实现支持以下类型的函数：

• 用于处理文本串的文本函数；
• 用于在数值数据上进行算数操作的数值函数；
• 用于处理特定日期和时间值并从这些值中提取特定成分的日期和时间函数；
• 返回 DBMS 正使用的特殊信息的系统函数。

文本处理函数

常用的文本处理函数

函数	说明
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	返回串的子串
Lower()	将串转换为小写
LTrim()	去掉串左边的空格
Right()	返回串右边的字符
RTrim()	去掉串右边的空格
Soundex()	返回串的 SOUNDEX 值
SubString()	返回串的字符
Upper()	将串转换为大写

日期和时间处理函数

常用日期和时间处理函数

函数	说明
AddDate()	添加一个日期（天、周等）
AddTime()	添加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前的日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

数值处理函数

常用的数值处理函数

函数	说明
Abs()	返回一个数的绝对值
Cos()	添加一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

汇总与分组数据（SELECT 语句下使用）

汇总数据

汇总数据的应用场景：

• 确定表中行数
• 获得表中行组的和
• 找出表列的最大值和最小值

SQL聚集函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

组合聚集函数

SELECT COUNT(*) AS num_item,
               MIN(prod_price) AS price_min,
               MAX(prod_price) AS price_max,
               AVG(prod_price) AS price_avg
FROM products;

分组数据（GROUP BY）

创建分组

单个字段

SELECT vend_id，COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

多个字段
GROUP BY 多个字段

ROLL UP
GROUP BY ROLL UP

GROUP BY 使用注意

• GROUP BY 子句包含任意数目的列；
• 如果 GROUP BY 子句中嵌套了分组，数据将在最后规定的分组进行汇总；
• GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果 SELECT 中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式。不能使用别名；
• 除聚集计算的语句外，SELECT 语句中的每个列都必须在 GROUP BY 子句中给出；
• 如果分组列中具有 NULL 值，则 NULL 值作为一个分组返回。如果列中有多行 NULL 值，它们将分为一个组；
• GROUP BY 子句必须在 WHERE 子句之后，ORDER BY 子句之前。

子查询

子查询：嵌套在其他查询中的查询，嵌套的数目没有限制。子查询最常见的两个应用场景是在 WHERE 子句的 IN 操作符中以及用来填充计算列

利用子查询进行过滤

需求：列出订购物品 TNT2 的所有客户
步骤：
（ 1 ）检索包含物品 TNT2 的所有订单的编号
（ 2 ）检索具有前一步骤列出的订单编号的所有客户的 ID
（ 3 ）检索前一步返回的所有客户 ID 的客户信息

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
                    FROM orderitems
                    WHERE prod_id = 'TNT2');

作为计算字段使用子查询

需求：显示 customers 表中每个客户的订单总数
步骤：
（ 1 ） 从 customers 表中检索客户列表
（ 2 ）对于检索出的每个客户，统计其在 orders 表中的订单数目

SELECT cust_name,
       cust_state,
               (SELECT COUNT( * )
                FROM orders
                WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

组合查询

MySQL 中允许执行多个查询（多条 SELECT 语句）并将结果作为单个查询结果返回。这些组合查询通常称为并（ union ）或复合查询（ computed query ）。两种基本情况需要使用组合查询：

• 在单个查询中从不同的表返回类似结构的数据
• 对单个表执行多个查询，按单个查询返回数据

创建组合查询（ UNION ）

A 语句
UNION
B 语句

UNION 规则

• UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 隔开
• UNION 的每个查询必须包含相同的列、表达式或聚集函数
• 列数据必须兼容。类型可以不同，但 DBMS 必须可以隐含的转换

包含或取消重复的行

UNION 从查询结果中自动去除了重复的行。如果想返回所有匹配行，可使用 UNION ALL

对组合查询结果排序

在使用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须出现在最后一行 SELECT 语句之后。对于结果集，不存在用一种方式排序一部分，而用另一种方式排序另一部分的情况，因此不允许使用多条 ORDER BY

sql 文件

导入 sql 文件

source 路径/***.sql

常用命令

查看 MySQL 端口

mysql> show global variables like 'port';