MySQL随笔

      一、MySQL 导入数据库

• 创建数据库 -

  mysql> CREATE DATABASE IF NOT EXISTS jialedb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
  
  mysql> use jialedb;
  

•  导入数据库
• 导入数据库

  测试导入结果

• 测试

  mysql> select city,phone,country from `offices`;
  +---------------+------------------+-----------+
  | city          | phone            | country   |
  +---------------+------------------+-----------+
  | San Francisco | +1 650 219 4782  | USA       |
  | Boston        | +1 215 837 0825  | USA       |
  | NYC           | +1 212 555 3000  | USA       |
  | Paris         | +33 14 723 4404  | France    |
  | Beijing       | +86 33 224 5000  | China     |
  | Sydney        | +61 2 9264 2451  | Australia |
  | London        | +44 20 7877 2041 | UK        |
  +---------------+------------------+-----------+

   二、查询数据

• select 语句简介
• 使用SELECT语句从表或视图获取数据。表由行和列组成，如电子表格。 通常，我们只希望看到子集行，列的子集或两者的组合。SELECT语句的结果称为结果集，它是行列表，每行由相同数量的列组成。
• 我们来看一下select语句的语法：

• SELECT 
      column_1, column_2, ...
  FROM
      table_1
  [INNER | LEFT |RIGHT] JOIN table_2 ON conditions
  WHERE
      conditions
  GROUP BY column_1
  HAVING group_conditions
  ORDER BY column_1
  LIMIT offset, length;

  SELECT语句由以下列表中所述的几个子句组成：

  • select之后是逗号分隔列或星号(*)的列表，表示要返回所有列。
  • from指定要查询数据的表或视图。
  • join根据某些连接条件从其他表中获取数据。
  • where过滤结果集中的行。
  • group by将一组行组合成小分组，并对每个小分组应用聚合函数。
  • having过滤器基于GROUP BY子句定义的小分组。
  • order by指定用于排序的列的列表。
  • limit限制返回行的数量。

                select 语句示例：             

SELECT 
    lastname, firstname, jobtitle
FROM
    employees;

• MySQL distinct 子句简介
• 从表中查询数据时，可能会收到重复的行记录。为了删除这些重复行，可以在SELECT语句中使用DISTINCT子句。
• distinct 子句语法如下：

• select distinct columns from table_name where where_conditions;

  示例：

• select distinct state,city from customers where state is not null
  order by state,city;

  以上示例，表示从customers 表中获取城市（city）和州（state）的唯一组合；

• distinct 和聚合函数示例：

SELECT 
    COUNT(DISTINCT state)
FROM
    customers
WHERE
    country = 'USA';

• distinct 与  limit 子句示例：
• 以下查询customers表中的前3个非空(NOT NULL)唯一state列的值

SELECT DISTINCT state FROM customers WHERE state IS NOT NULL LIMIT 3;

• 过滤数据
• where 语句
• 示例：
• 假设只想从employees表中获取销售代表员工，可使用以下查询：

• SELECT 
      lastname, firstname, jobtitle
  FROM
      employees
  WHERE
      jobtitle = 'Sales Rep';

  要在办公室代码(officeCode)等于1中查找所有销售代表，请使用以下查询：

• SELECT 
      lastname, firstname, jobtitle
  FROM
      employees
  WHERE
      jobtitle = 'Sales Rep' AND officeCode = 1;

  下表列出了可用于在WHERE子句中形成过滤表达式的比较运算符。

  操作符	描述
  =	等于，几乎任何数据类型都可以使用它。
  <> 或 !=	不等于
  <	小于，通常使用数字和日期/时间数据类型。
  >	大于，
  <=	小于或等于
  >=	大于或等于

• 以下查询使用不等于(!=)运算符来获取不是销售代表的其它所有员工：

• SELECT 
      lastname, firstname, jobtitle
  FROM
      employees
  WHERE
      jobtitle <> 'Sales Rep';

   and 运算符 例如，以下查询返回位于美国加州的客户，并且信用额度大于100K。

• SELECT customername, country, state, creditlimit FROM customers
  WHERE country = 'USA' AND state = 'CA' AND creditlimit > 100000;

  OR运算符

• 以下声明返回位于美国(USA)或者法国(France)，并且信用额度大于10000的客户。

• SELECT 
      customername, country, creditLimit
  FROM
      customers
  WHERE
      (country = 'USA' OR country = 'France')
      AND creditlimit > 100000;

  IN运算符

• SELECT 
      column1,column2,...
  FROM
      table_name
  WHERE 
   (expr|column_1) IN ('value1','value2',...);

   in 示例：

• 如果您想查找位于美国和法国的办事处，可以使用IN运算符作为以下查询：

• SELECT officeCode, city, phone, country FROM offices WHERE country IN ('USA' , 'France');

• 要获得不在美国和法国的办事处，请在WHERE子句中使用NOT IN如下：

• SELECT officeCode, city, phone FROM offices WHERE country NOT IN( 'USA', 'France');

• in 子查询

  IN运算符通常用于子查询。子查询不提供常量值列表，而是提供值列表。

  我们来看看两张表：orders和orderDetails表的结构以及它们之间的关系：

  
  

  例如，如果要查找总金额大于60000的订单，则使用IN运算符查询如下所示：

• SELECT 
      orderNumber, customerNumber, status, shippedDate
  FROM
      orders
  WHERE
      orderNumber IN (SELECT 
              orderNumber
          FROM
              orderDetails
          GROUP BY orderNumber
          HAVING SUM(quantityOrdered * priceEach) > 60000);

  between 运算符

• BETWEEN运算符允许指定要测试的值范围。 我们经常在SELECT，INSERT，UPDATE和DELETE语句的WHERE子句中使用BETWEEN运算符。
• 示例：
• 假设您想要查找价格在90和100(含90和100)元范围内的商品，可以使用BETWEEN运算符作为以下查询：

• SELECT 
      productCode, productName, buyPrice
  FROM
      products
  WHERE
      buyPrice BETWEEN 90 AND 100;

  要查找购买价格不在20到100(含20到100)之间的产品，可将BETWEEN运算符与NOT运算符组合使用，如下：

• SELECT 
      productCode, productName, buyPrice
  FROM
      products
  WHERE
      buyPrice NOT BETWEEN 20 AND 100;

  between 与 日期类型数据示例：

• 例如，要查询获取所需日期(requiredDate)从2013-01-01到2013-01-31的所有订单，请使用以下查询：

• SELECT orderNumber,
           requiredDate,
           status
  FROM orders
  WHERE requireddate
      BETWEEN CAST('2013-01-01' AS DATE)
          AND CAST('2013-01-31' AS DATE);

   like 运算符

• MySQL提供两个通配符，用于与LIKE运算符一起使用，它们分别是：百分比符号 - %和下划线 - _。

  • 百分比(%)通配符允许匹配任何字符串的零个或多个字符。
  • 下划线(_)通配符允许匹配任何单个字符。
• 示例：

• SELECT 
      employeeNumber, lastName, firstName
  FROM
      employees
  WHERE
      lastname like "%on%";

  查找名字以T开头的员工，以m结尾，并且包含例如Tom，Tim之间的任何单个字符，可以使用下划线通配符来构建模式，如下所示：

• SELECT 
      employeeNumber, lastName, firstName
  FROM
      employees
  WHERE
      firstname like "T_m";

  如下语句，将查询productCode字段中包含_20字符串的值。

• SELECT 
      productCode, productName
  FROM
      products
  WHERE
      productCode like "%\_20%" ;

  有时想要匹配的模式包含通配符，例如10%，_20等这样的字符串时。在这种情况下，您可以使用ESCAPE子句指定转义字符，以便MySQL将通配符解释为文字字符。如果未明确指定转义字符，则反斜杠字符是默认转义字符。

• limit 子句
• 在SELECT语句中使用LIMIT子句来约束结果集中的行数。LIMIT子句接受一个或两个参数。两个参数的值必须为零或正整数

• SELECT 
      column1,column2,...
  FROM
      table
  LIMIT offset , count;

  我们来查看LIMIT子句参数：

  • offset参数指定要返回的第一行的偏移量。第一行的偏移量为0，而不是1。
  • count指定要返回的最大行数。
  • 示例：
  • 我们的任务找出结果集中价格第二高的产品。可以使用LIMIT子句来选择第二行，如以下查询(注意：偏移量从0开始，所以要指定从1开始，然后取一行记录)：

  • select  productCode, productName, buyprice FROM  products
    ORDER BY buyprice DESC
    LIMIT 1, 1;

    四、排序数据

当使用SELECT语句查询表中的数据时，结果集不按任何顺序进行排序。要对结果集进行排序，请使用ORDER BY子句。 ORDER BY子句允许：

• 对单个列或多个列排序结果集。
• 按升序或降序对不同列的结果集进行排序。

下面说明了ORDER BY子句的语法：

SELECT column1, column2,...
FROM tbl
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],...

ASC表示升序，DESC表示降序。默认情况下，如果不明确指定ASC或DESC，ORDER BY子句会按照升序对结果集进行排序。

SELECT
 contactLastname,
 contactFirstname
FROM
 customers
ORDER BY
 contactLastname DESC,
 contactFirstname ASC;

以下查询从orderdetails表中选择订单行记录项目。它计算每个订单项的小计，并根据订单编号，订单行号(orderLineNumber)和小计(quantityOrdered * priceEach)对结果集进行排序。

SELECT
 ordernumber,
 orderlinenumber,
 quantityOrdered * priceEach
FROM
 orderdetails
ORDER BY
 ordernumber,
 orderLineNumber,
 quantityOrdered * priceEach;

ORDER BY子句允许使用FIELD()函数为列中的值定义自己的自定义排序顺序。

看看下面 orders表的结构如下所示 -

SELECT 
    orderNumber, status
FROM
    orders
order by field(status,
        "In Process",
        "On Hold",
        "Cancelled",
        "Resolved",
        "Disputed",
        "Shipped");

order by 子句 与自然语言排序

示例：

SELECT 
    item_no
FROM
    items
order by cast(item_no as unsigned),item_on;

SELECT 
    item_no
FROM
    items
order by length(item_no),item_no;