mysql学习总结

71 阅读 0 评论 47 点赞

我是靠谱客的博主明理太阳，最近开发中收集的这篇文章主要介绍mysql学习总结，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

检索数据
- 检索单个列数据
- 检索多个列数据
- 检索所有列数据
- 检索不同的值
  - 使用 DISTINCT关键字
  - 不能部分使用DISTINCT，关键字用于所有列，不仅仅是跟在其后的列。
- 限制结果
  - 输出指定的行数 SELECT name FROM user limit 5;
  - 输出指定的行数从哪一行开始 SELECT name FROM user limit 5 offset 5;
- 使用注释
  - 在一行的开始使用#，这一整行都将作为注释。
  - 多行注释,/**/
排序检索数据
- 为了明确地排序用SELECT语句检索出来的数据，可使用ORDER BY子句。
  - ORDER BY子句取一个或多个列的名字，据此对输出进行排序。
- ORDER BY子句的位置
  - 在指定一条ORDER BY子句的事后，应该确保它是SELECT语句的最后一条子句。如果它不是最后一条子句，将会出现错误信息。
- 按多个列排序
  - 要按多个列排序，简单指定列名，列名之间使用逗号分隔。
- 按照列位置进行排序
  - 除了能用列名指出排序顺序外，ORDER BY还支持按照相对位置进行排序。
  - 例子 SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2, 3;
  - 这一技术的主要好处是不用重新输入列名，但它也有缺点。不明确地给出列名可能会造成错用列名排序。
- 指定排序方向
  - 降序排序 DESC.
  - 升序 ASC
过滤数据
- 使用WHERE子句
  - 只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件。
  - 警告：在同时使用ORDER BY和WHERE子句，应该让ORDER BY位于WHERE之后，否则将会产生错误。
- WHERE子句操作符
  - 检查单个值
  - 不匹配检查
    - 例子: SELECT vend_id, prod_name FROM Products WHERE vend_id <> 'DLL01'和 SELECT vend_id, prod_name FROM Products WHERE vend_id != 'DLL01'。
  - 范围值检测
    - 要检查某个范围的值，可以使用BETWEEN操作符。
  - 空值检查
    - 确定值是否为NULL，不能简单地检查是否=NULL
    - 例子：SELECT prod_name FROM Products WHERE prod_price IS NULL
  - 警告：通过过滤选择不包含指定值的所有行，你可能希望返回含NULL值的行。但是这做不到，因为未知（unknown）有特殊的含义，数据库不知道他们是否匹配，所以在进行匹配过滤或非匹配过滤时，不会返回这些结果。
高级过滤数据
- 为了进行更强的过滤控制，SQL允许给出多个WHERE子句。这些子句有两种使用方式，即以AND和OR子句的方式使用。
  - 操作符（operator）用来联接或改变WHERE子句中的子句的关键字，也称为逻辑运算符（logical operation）
- AND操作符
  - 要通过不止一个列进行过滤，可以使用AND操作符给WHERE子句附加条件。
  - 例子：SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;
- OR操作符
  - 与AND操作符相反
- 求值顺序
  - 允许两者结合以进行复杂、高级的过滤。
  - SQL在处理OR操作符前，优先处理AND操作符。解决这个问题的方法是使用圆括号来进行明确分组。
  - 任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认求值顺序。
- IN操作符
  - IN操作符用来指定条件范围。
- NOT操作法
  - NOT操作符只有一格功能，否定其后所跟的任何条件。
用通配符进行过滤
- like操作符
  - %通配符
    - 在搜索中，百分号表示任意字符出现任意次数
    - 例子
      - 找出所有词Fish起头的产品：SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%'; # 执行这条语句将检索任意以Fish起头的词。
      - 多个匹配符：SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '%began bag%'; # 搜索模式’%began bag%'表示任何位置商包含文本began bag的值，不论它之前或之后出现什么字符。
      - 通配符出现在搜索模式的中间：SELECT prod_name FROM Products WHERE prod_name LIKE 'F%y'; # 有一种情况下把通配符放在中间是有用的，比如WHERE email LIKE b%forta.com
    - 注意NULL：子句SELECT prod_name LIKE %不会匹配产品名称为NULL的行
  - 下划线（_）通配符
    - 用途和%一样，但它只匹配单个字符，而不是多个字符。
    - 例子
      - SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear' # 注意后面的空格
    - 注意：_总是刚好匹配一格字符，不能多也不能少，与%匹配0个不一样。
  - 方括号（[]）通配符
    - 方括号（[]）通配符用来指定一格字符集，它必须匹配指定位置的一个字符。
- 使用通配符技巧
  - 不要过度使用通配符
  - 在确实使用通配符时，也尽量不要把他们用在搜索模式开始处。
  - 仔细注意通配符的位置
创建计算字段
- 计算字段
  - 字段（field）：基本上与列（column）意思相同，进程互相使用
- 拼接字段
  - 拼接（concatenate）:将值联接到一起（将一个值附加到另一个值）构成单个值
  - 例子：
    - SELECT vend_name + '(' + vend_country + ')' FROM Vendors ORDER BY vend_name
    - 相同效果：SELECT vend_name || '(' || vend_contry || ')' FROM Vendors ORDER BY vend_name;
- 使用别名
  - SQL支持列别名。别名（alias）是一个字段或值的替换名。别名用AS关键字赋予。
  - 例子
    - SELECT RTRIM(vend_name) + '(' + RTRIM(vend_contry) + ')' AS vend_title FROM Vendors ORDER BY vend_name;
- 执行算数计算
  - 例子：
    - SELECT prod_id, quantity, item_price, quantity * item_price AS expand_price FROM OrderItems WHERE order_num = 20008;
使用数据处理函数
- 函数
  - 可移植（portable）:所编写的代码可以在多个系统上运行
- 使用函数
  - 文本处理函数
    - 例子：SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM Vendors ORDER BY vend_name;
  - 日期和时间处理函数
    - 应用程序一般不使用日期和时间的存储格式。
    - 例子：
      - SELECT order_num FROM Orders WHERE YEAR(order_date) = 2012;
  - 数值处理函数
汇总数据
- 聚集函数
  - 聚集函数（aggregate function）对某些运行的函数，计算并返回一格值。
  - 例子：
    - AVG()：返回某列的平均值
    - COUNT(): 计算某列的行数
    - MAX() ：返回某列的最大值
    - MIN(): 返回某列的最小值
    - SUM(): 返回某列值值和
  - AVG()函数：
    - AVG（）通过对表中行数计算并计算其列值之和。
    - 例子：SELECT AVG(prod_price) AS avg_price FROM Products;
    - AVG（）函数忽略值为NULL的行
  - COUNT 函数
    - COUNT()函数进行计数。
    - COUNT()函数两种使用方式
      - 使用COUNT(*)对表中数目进行计算，不管表列中是否包含的是空值（NULL）还是非空值
      - 使用COUNT(column)对特定列中具有值的行进行计算，忽略NULL值
  - MAX函数
    - 返回指定列中的最大值
    - 例子：SELECT MAX(prod_price) AS max_price FROM Products;
    - 对非数值数据使用MAX()，用于文本数据时，MAX()返回按该列排序后的最后一行。
  - MIN函数
    - MIN函数与MAX函数功能相反
    - 例子：SELECT MIN(prod_price) AS min_price FROM Products
    - 虽然MIN()函数一般用来找出最小的数值或日期，但在用于文本数据的时候，MIN()会返回该列排序后最前面的行。
  - SUM函数
    - SUM（）函数用来返回指定列值的和（总计）。
    - 例子：SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE order_num = 2005;
    - NULL值，SUM函数会自动忽略NULL的值
- 聚集不同值
  - DISTINCT不能用于COUNT(*)
    - 如果指定列名，则DISTINCT只能用于COUNT。DISTINCT不能用于COUNT(*)。类似地，DISTINCT必须使用列名，不能用于计算或表达式。
- 组合聚集函数
  - 例子
    - SELECT COUNT(*) AS num_items,
      MIN(prod_price) AS price_min,
      MAX(prod_price) AS price_max,
      MAX(prod_price) AS price_avg
      FROM Products
分组数据
- 数据分组
  - 例子：SELECT COUNT(*) AS num_prods FROM Products WHERE vend_id = 'DLL01'
- 创建分组
  - 分组是使用SELECT和GROUP BY子句。
  - 例子：SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id
- 过滤分组
  - HAVING非常类似于WHERE。事实上，目前所有类型的WHERE都可以用HAVING来代替。唯一的差别，WHERE过滤行，而HAVING过滤分组。
  - 过滤分组例子：
    - SELECT cust_id, COUNT(*) AS orders
      FROM Orders
      GROUP BY cust_id
      HAVING COUNT(*) >= 2;
- 分组和排序
  - GROUP BY和ORDER BY完成相同工作。
  - ORDER BY对产生的输出排序，任意列都可以使用，但不一定需要。
  - 一般在使用GROUP BY子句时，应该也使用ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要依赖GROUP BY排序数据。
  - 例子：
    - SELECT order_num, COUNT(*) AS items
      FROM OrderItems
      GROUP BY order_num
      HAVING COUNT(*) >= 3
- SELECT子句顺序
使用子查询
- 子查询
  - 查询（query）：任何SQL语句都是查询。
- 使用子查询过滤
  - 例子
    - SELECT cust)id
      FROM Orders
      WHERE order_num IN (SELECT order_num
      FROM OrderItems
      WHERE prod_id = 'RGAN01');
    - SELECT cust_name, cust_contact
      FROM Customers
      WHERE cust_id IN (SELECT cust_id
      FROM Order
      WHERE order_num IN (SELECT order_num
      FROM OrderItems
      WHERE prod_id = 'RANGE01'));
- 作为计算字段使用子查询
  - 使用子查询的另一个方法是创建计算字段。
  - 例子
    - SELECT cust_name,
      cust_state,
      (SELECT Orders
      FROM Orders
      WHERE Orders.cust_id = Customers.cust_id) AS orders
      FROM Customers
      ORDER BY cust_name;
创建和操作表
- 创建表
  - 表创建基础
    - 使用CREATE TABLE
      - 新表的名字
      - 表列的名字和定义，逗号分隔
      - 有的DBMS还要求表的位置
    - 例子
      - 创建Products
        CREATE TABLE Products
        (
        prod_id CHAR(10) NOT NULL,
        prod_vend_id CHAR(10) NOT NULL,
        prod_name CHAR(254) NOT NULL,
        prod_price DECIMAL(8, 2) NOT NULL,
        prod_desc VARCHAR(1000) NULL
        )
    - 注意：指定的表名必须不存在，否则将会报错。
  - 使用NULL值
    - 允许NULL值的列也允许在插入行时不给该列的值
    - 不允许NULL值的列不接受没有列值的行
    - 每个表列要么是NULL列，要么是NOT NULL值。
    - 例子
      - CREATE TABLE Vendors
        (
        vend_id CHAR(10) NOT NULL,
        vend_name CHAR(50) NOT NULL,
        vend_city CHAR(50) ,
        vend_zip CHAR(10)
        );
  - 指定默认值
    - SQL允许指定默认值，在插入行时如果不给出值，将会采用默认值。默认值在CREATE TABLE语句的列定义中用关键字DEFAULT指定。
    - 例子
      - CREATE TABLE OrderItems
        (
        order_num INTEGER NOT NULL,
        order_item INTEGER NOT NULL,
        prod_id CHAR(10) NOT NULL,
        quantity INTEGER NOT NULL DEFAULT 1,
        item_price DECIMAL(8,2) NOT NULL
        );
- 更新表
  - 更新表定义，可以使用ALTER TABLE语句。
  - 例子
    - ALTER TABLE Vendors
      ADD vend_phone CHAR(20);
    - ALTER TABLE Vendors
      DROP COLUMN vend_phone
  - 小心使用ALTER TABLE
    - 使用ALTER TABLE要极为小心，应该在进行该动前做完整的备份。（模式和数据的备份）。数据表的更改不能撤销，如果增加了不需要的列，业务无法删除他们。
- 删除表
  - DROP TABLE CustCopy;
- 重新命名表
  - 不存在严格标准，MySQL使用RENAME语句。
插入数据
- 数据插入
  - 插入完整行
    - 例子
      - INSERT INTO TABLE Customer
        VALUES ('1004',
        'Toy Land'
        );
  - 插入部分行
  - 插入检索出数据
    - INSERT一般用来给表插入具有指定列的值的行。INSERT还存在另外一种形式，可以利于它将SELECT语句的结果插入表中。这就是所谓的INSERT SELECT.
    - 例子
      - INSER INTO Customers（
        cust_id,
        cust_contact)
        SELECT cust_id,
        cust_contact
        FROM Custnew;
- 从一个表复制到另一个表
  - 有一种数据插入不使用INSERT语句。要将表的内容复制到另一个全新的表，可以使用SELECT INTO.
  - 例子：
    - SELECT *
      INTO CustCopy
      FROM Customer;
更新和删除数据
- 更新数据使用UPDATE
  - 例子
    - UPDATE Customer
      SET cust_email = 'abc123@163.com'
      WHERE cust_id = '11'
- 删除数据使用DELETE
  - 例子
    - DELETE FROM Customer
      WHERE cust_id = '100';
  - 如果要删除所有的行，请使用TRUNCATE TABLE;语句
- 跟新和删除的指导原则
  - 下面是很多SQL程序员使用UPDATE和DELETE时所遵循的原则
    - 除非确实打算更新和删除每一行，否则绝不要使用不带WHERE子句的UPDATE和DELETE
    - 保证每个表都有主键，尽可能像WHERE那样使用它。
    - 在UPDATE或DELETE语句使用WHERE子句之前，应该先使用SELECT进行测试，确保它过滤的是正确的记录，以防止编写的WHERE子句不正确。
视图
- 视图是虚拟的表。与包含数据的表不一样，视图只包含使用动态检索数据的查询。
  - 例子
    - SELECT cust_name, cust_contact
      FROM Customers, Orders, OrderItems
      WHERE Customers.cust_id = Orders.cust_id
      AND OrderItems.order_num = Orders.order_num
      AND prod_id = 'RGAN01';
- 创建视图
  - 视图用CREATE VIEW来创建视图。只能用于创建不存在的视图。删除视图使用DROP VIEW,覆盖更新视图需要先删除存在的视图。
  - 例子
    - CREATE VIEW ProductCustomer AS
      SELECT custom_id, custom_contact, prod_id
      FROM Customer, Orders, OrderItems
      WHERE Customer.cust_id = Orders.cust_id
      AND OrderItems.order_num = Orders.order_num;
      
      SELECT custom, cust_content
      FROM ProductCustomer
      WHERE prod_id = 'RGAN01';
- 利用视图简化复杂的联接
  - 一个最常见的视图作用是隐藏复杂的SQL，这通常涉及到联结。
  - 视图大大简化了复杂的SQL语句的使用。利用视图，可一次性编写基础的SQL，然后根据多次使用。
    - 创建不绑定特定数据的视图是一个好方法。扩展视图的范围不仅使得它能被重用，而且可能很有用。这样做不需要创建和维护多个类似视图。
- 用视图重新格式化检索出的数据
联结表
- 联结
  - SQL最强大的功能之一就是在数据查询的执行中联结（join）表。联结就是利用SQL 的SELECT能执行的最重要动作。
- 创建联结
- 联结表是SQL一个最重要、最强大的特性，有效地使用联结需要对关系数据库设计的基本了解。
创建高级联结
- 使用表别名
  - 例子
    - SELECT RTRIM(vend_name) + '(' + RTRIM(vend_price) + ')'
      AS vend_title
      FROM Vendors
      ORDER BY vend_name;
- 使用不同类型的联结
- 使用带聚集函数的联结
- 使用联结和联结条件