mysql的命令入门

mysql入门实践

学习教程

  • 教程链接地址

  • 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,每个数据库都有一个或多个不同的API用于创建,访问,管理,搜索和复制所保存的数据。

  • 文件中读写数据较慢,所以使用关系型数据库管理系统(RDBMS)来存储和管理的大数据量。

    • 所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
    • RDBMS即关系数据库管理系统(Relational Database Management System)的特点:
      1. 数据以表格的形式出现
      2. 每行为各种记录名称
      3. 每列为记录名称所对应的数据域
      4. 许多的行和列组成一张表单
      5. 若干的表单组成database
  • 简单入门

  • mysql学习的好书

  • 知乎推荐的学习书籍

  • mysql -u root -p 进行登录

  • show databases; 查看有哪些数据库。

  • use mysql;进入mysql的数据库。

  • 插入新的用户,INSERT INTO user
    (host, user, password,
    select_priv, insert_priv, update_priv)
    VALUES ('localhost', 'jianglong05',
    PASSWORD('JJjl930112'), 'Y', 'Y', 'Y');

  • 在同一个数据库中不能创建两个相同名称的表,在同
    一个表中也不能创建两个名称相同的列。

        CREATE TABLE Product
        (product_id CHAR(4) NOT NULL,
        product_name VARCHAR(100) NOT NULL,
        product_type VARCHAR(32) NOT NULL,
        sale_price INTEGER ,
        purchase_price INTEGER ,
        regist_date DATE ,
        PRIMARY KEY (product_id));
    
  • 向表中增加一列: ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);;

  • 向表中删除一列: ALTER TABLE Product DROP COLUMN product_name_pinyin;

  • 插入数据:

START TRANSACTION;
INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11');
COMMIT;
  • 表的修改: RENAME TABLE Product to Poduct;

  • 查看表的内容: desc product

  • 将一个key放到另外一个key之后: alter table Addressbook modify mail_address varchar(20) after tel_no;

  • 删出一个表: DROP TABLE Product;

  • 恢复一个表: ``

  • 从表中选出固定的列: SELECT product_id, product_name, purchase_price FROM Product;

  • 查询全部的列: SELECT * FROM <表名>;

  • 为列设置列名: SELECT product_id AS id, product_name AS name, purchase_price AS price FROM Product;

    • 设定汉语别名时需要使用双引号(")括起来。
  • distinct 删除重复的行: SELECT DISTINCT product_type FROM Product;

  • 根据WHERE语句来选择记录: SELECT <列名>, …… FROM <表名> WHERE <条件表达式>

    • SELECT product_name, product_type FROM Product WHERE product_type = '衣服';
  • mysql 用 -- 进行单行注释,用/**/进行多行注释。

  • 比较运算符: SELECT product_name, product_type FROM Product WHERE sale_price = 500;

  • where可以做减: SELECT product_name, sale_price, purchase_price FROM Product WHERE sale_price - purchase_price >= 500;

  • 选取不为NULL的记录: SELECT product_name, purchase_price FROM Product WHERE purchase_price IS NOT NULL;

  • 选取为NULL的记录:SELECT product_name, purchase_price FROM Product WHERE purchase_price IS NULL;

  • NOT运算符: 条件运算取非,SELECT product_name, product_type, sale_price FROM Product WHERE NOT sale_price >= 1000;

    • NOT运算符用来否定某一条件,但是不能滥用。
  • AND运算符和OR运算符: 可以对多个查询条件进行组合。

    • SELECT product_name, purchase_price FROM Product WHERE product_type = '厨房用具' AND sale_price >= 3000;
    • SELECT product_name, purchase_price, product_type FROM Product WHERE product_type = '厨房用具' or sale_price >= 3000;
    • 多个查询条件进行组合时,需要使用AND运算符或者OR运算符。
    • SELECT product_name, product_type, regist_date FROM Product WHERE product_type = '办公用品' AND ( regist_date = '2009-09-11' OR regist_date = '2009-09-20');

聚合与排序

  • 聚合函数:
    • COUNT: 计算表中的记录数(行数)
    • SUM: 计算表中数值列中数据的合计值
    • AVG: 计算表中数值列中数据的平均值
    • MAX: 求出表中任意列中数据的最大值
    • MIN: 求出表中任意列中数据的最小值
  • 计算除 NULL 以外的行:SELECT COUNT(purchase_price) FROM Product;
    • COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数,而COUNT(<列名>)会得到NULL之外的数据行数。
    • SELECT MAX(sale_price), MIN(purchase_price) FROM Product;
    • SELECT COUNT(DISTINCT product_type) FROM Product;
    • SELECT SUM(sale_price), SUM(DISTINCT sale_price) FROM Product;
  • 对表进行分组: GROUP BY子句进行汇总
    • SELECT <列名1>, <列名2>, <列名3>, …… FROM <表名> GROUP BY <列名1>, <列名2>, <列名3>, ……;
    • SELECT product_type, COUNT(*) FROM Product GROUP BY product_type;
    • group by中的聚合键包含NULL。
  • 使用WHERE子句和GROUP BY子句进行汇总处理:
    • SELECT <列名1>, <列名2>, <列名3>, …… FROM <表名> WHERE GROUP BY <列名1>, <列名2>, <列名3>, ……;
    • SELECT purchase_price, COUNT(*) FROM Product WHERE product_type = '衣服' GROUP BY purchase_price;
    • 使用GROUP BY子句时,SELECT子句中不能出现聚合键之外的列名。
    • SELECT product_type AS pt, COUNT(*) FROM Product GROUP BY pt;
    • 只有SELECT子句和HAVING子句(以及ORDER BY子句)中能够使用聚合函数。
  • 为聚合结果指定条件: having子句
    • WHERE子句用来指定数据行的条件,HAVING子句用来指定分组的条件。
    • HAVING子句要写在GROUP BY子句之后。
    • SELECT <列名1>, <列名2>, <列名3>, …… FROM <表名> GROUP BY <列名1>, <列名2>, <列名3>, …… HAVING <分组结果对应的条件>
    • SELECT product_type, COUNT(*) FROM Product GROUP BY product_type HAVING COUNT(*) = 2;
    • 聚合键所对应的条件不应该书写在HAVING子句当中,而应该书写在WHERE子句当中。
    • ,将条件写在WHERE 子句中要比写在HAVING 子句中的处理速度更快,返回结果所需的时间更短
  • 对查询结果进行排序: order by
    • SELECT <列名1>, <列名2>, <列名3>, …… FROM <表名> ORDER BY <排序基准列1>, <排序基准列2>, ……
    • SELECT product_id, product_name, sale_price, purchase_price FROM Product ORDER BY sale_price;
    • 不论何种情况,ORDER BY 子句都需要写在SELECT 语句的末尾。这是因为对数据行进行排序的操作必须在结果即将返回时执行。
      1. SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 → 5. HAVING 子句 → 6. ORDER BY 子句
    • SELECT product_id, product_name, sale_price, purchase_price FROM Product ORDER BY sale_price DESC;
      • ASC 和DESC 是ascendent(上升的)和 descendent(下降的)这两个单词的缩写。
    • order by 可以指定多个排序键: SELECT product_id, product_name, sale_price, purchase_price FROM Product ORDER BY sale_price, product_id;
    • 对 NULL 不会进行排序,要么在最开始,要么在最末尾。
    • 排序键中包含NULL时,会在开头或末尾进行汇总。
    • 对排序键用别名: SELECT product_id AS id, product_name, sale_price AS sp, purchase_price FROM Product ORDER BY sp, id;
    • 在ORDER BY子句中可以使用SELECT子句中定义的别名。
    • 在ORDER BY子句中不要使用列编号。

数据更新

  • 数据插入:INSERT语句的使用方法
    • CREATE TABLE 语句只负责创建表,但创建出的表中并没有数据
      CREATE TABLE ProductIns
      (product_id CHAR(4) NOT NULL,
      product_name VARCHAR(100) NOT NULL,
      product_type VARCHAR(32) NOT NULL,
      sale_price INTEGER DEFAULT 0,
      purchase_price INTEGER ,
      regist_date DATE ,
      PRIMARY KEY (product_id));
    
    • 通过INSERT 语句插入数据
      • INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);
      • INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
      • INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0001', 'T恤衫', '衣服', 1000, 500); 少一行会报错
      • -- 多行INSERT (Oracle以外):
      • INSERT INTO ProductIns VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'), ('0003', '运动T恤', '衣服', 4000, 2800, NULL), ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
      • 对表进行全列INSERT 时,可以省略表名后的列清单.
      • INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
      • INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0007', '擦菜板', '厨房用具', DEFAULT, 790, '2009-04-28'); 插入默认值, 默认值为0.
      • INSERT INTO ProductIns (product_id, product_name, product_type, purchase_price, regist_date) VALUES ('0007', '擦菜板', '厨房用具', 790, '2009-04-28'); --- 隐式方法插入默认值,就是缺省对应的列。
      • 省略INSERT语句中的列名,就会自动设定为该列的默认值(没有默认值时会设定为NULL)。
      • INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, regist_date) VALUES ('0008', '圆珠笔', '办公用品', 100, '2009-11-11');
      • INSERT INTO ProductIns (product_id, product_type, sale_price, purchase_price, regist_date) VALUES ('0009', '办公用品', 1000, 500, '2009-12-12');
    • 从其他表中赋值数据:
      CREATE TABLE ProductCopy
      (product_id CHAR(4) NOT NULL,
      product_name VARCHAR(100) NOT NULL,
      product_type VARCHAR(32) NOT NULL,
      sale_price INTEGER ,
      purchase_price INTEGER ,
      regist_date DATE ,
      PRIMARY KEY (product_id));
      
      • INSERT ... SELECT语句:
        • INSERT INTO ProductCopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date) SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date FROM Product; --- 从 product 表中赋值数据到 productcopy中
        • INSERT INTO ProductType (product_type, sum_sale_price, sum_purchase_price) SELECT product_type, SUM(sale_price), SUM(purchase_price) FROM Product GROUP BY product_type;
    • 向表中插入数据
  • 数据删除:DELETE语句的使用方法
    • DROP TABLE语句和DELETE语句:
      • DROP TABLE 语句可以将表完全删除
        • DROP TABLE 语句会完全删除整张表,因此删除之后再想插入数据,就必须使用CREATE TABLE 语句重新创建一张表.
      • DELETE 语句会留下表(容器),而删除表中的全部数据
        • DELETE 语句在删除数据(行)的同时会保留数据表,因此可以通过INSERT 语句再次向表中插入数据。
        • DELETE FROM <表名>;
        • DELETE语句的删除对象并不是表或者列,而是记录(行)。
      • 删除数据时都要慎重,一旦误删,想要恢复数据就会变得十分困难。
      • DELETE FROM ProductCopy WHERE sale_price >= 4000;
      • 与SELECT 语句不同的是,DELETE 语句中不能使用GROUP BY、HAVING 和ORDER BY 三类子句,而只能使用WHERE 子句。
  • 数据的更新:UPDATE语句的使用方法
    • 改变表中数据的UPDATE语句: UPDATE <表名> SET <列名> = <表达式>;
    • UPDATE ProductCopy SET regist_date = '2009-10-10';
    • 指定条件的UPDATE语句(搜索型UPDATE): UPDATE <表名> SET <列名> = <表达式> WHERE <条件>;
    • UPDATE Productcopy SET sale_price = sale_price * 10 WHERE product_type = '厨房用具';
    • UPDATE Productcopy SET regist_date = NULL WHERE product_id = '0008';
    • 使用UPDATE语句可以将值清空为NULL(但只限于未设置NOT NULL约束的列)。
    • 多列更新:
      • UPDATE Product SET sale_price = sale_price * 10, purchase_price = purchase_price / 2 WHERE product_type = '厨房用具';
      • UPDATE Product SET (sale_price, purchase_price) = (sale_price * 10, purchase_price / 2) WHERE product_type = '厨房用具';
  • 事务: 什么是事务
    • 事务是需要在同一个处理单元中执行的一系列更新处理的集合.
    • 通过使用事务,可以对数据库中的数据更新处理的提交和取消进行管理。
    • 事务处理的终止指令包括COMMIT( 提交处理)和ROLLBACK(取消处理)两种。
    • DBMS的事务具有原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)四种特性。
    • 事务就是 需要在同一个处理单元中执行的一系列更新处理的集合 。
    • UPDATE Product SET sale_price = sale_price - 1000 WHERE product_name = '运动T恤'; UPDATE Product
      SET sale_price = sale_price + 1000 WHERE product_name = 'T恤衫'; 降低价格和上浮价格一起操作就叫一个事物。
  • 事务是需要在同一个处理单元中执行的一系列更新处理的集合。
    • START TRANSACTION (BEGIN TRANSACTION)开始一个事物
      START TRANSACTION;
      -- 将运动T恤的销售单价降低1000日元
      UPDATE Product
      SET sale_price = sale_price - 1000
      WHERE product_name = '运动T恤';
      -- 将T恤衫的销售单价上浮1000日元
      UPDATE Product
      SET sale_price = sale_price + 1000
      WHERE product_name = 'T恤衫';
      COMMIT;
    
    • COMMIT 是提交事务包含的全部更新处理的结束指令(图4-3),相当于文件处理中的覆盖保存。一旦提交,就无法恢复到事务开始前的状态了。
  • ROLLBACK 是取消事务包含的全部更新处理的结束指令, 相当于文件处理中的放弃保存。一旦回滚,数据库就会恢复到事务开始之
    前的状态。
  • 保证持久性的方法根据实现的不同而不同,其中最常见的就是将事务的执行记录保存到硬盘等存储介质中(该执行记录称为日志)。

复杂查询

  • 从SQL的角度来看,视图和表是相同的,两者的区别在于表中保存的是实际的数据,而视图中保存的是SELECT语句(视图本身并不存储数据)。

    • 视图和表到底有什么不同呢?区别只有一个,那就是“是否保存了实际的数据”。
    • 使用视图时并不会将数据保存到存储设备之中,而且也不会将数据保存到其他任何地方。
    • 表中存储的是实际数据,而视图中保存的是从表中取出数据所使用的SELECT语句。
    • 可以将频繁使用的SELECT 语句保存成视图,这样就不用每次都重新书写了。
    • 应该将经常使用的SELECT语句做成视图。
  • 使用视图,可以轻松完成跨多表查询数据等复杂操作。

  • 可以将常用的SELECT语句做成视图来使用。

  • 创建视图需要使用CREATE VIEW语句。

  • 视图包含“不能使用ORDER BY”和“可对其进行有限制的更新”两项限制。

  • 删除视图需要使用DROP VIEW语句。

  • 创建视图: CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……) AS <SELECT语句>

    • CREATE VIEW ProductSum (product_type, cnt_product) AS SELECT product_type, COUNT(*) FROM Product GROUP BY product_type;
    • 应该避免在视图的基础上创建视图。因为多重视图可能会降低数据库的性能。
    • 定义视图时不能使用ORDER BY子句。视图和表一样,数据行都是没有顺序的。
  • 视图和表需要同时进行更新,因此通过汇总得到的视图无法进行更新。

  • CREATE VIEW ProductJim (product_id, product_name, product_type, sale_price, purchase_price, regist_date) AS SELECT * FROM Product WHERE product_type = '办公用品';

    • INSERT INTO ProductJim VALUES ('0009', '印章', '办公用品', 95, 10, '2009-11-30');
  • 删除视图: DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ……);

    • DROP VIEW ProductSum;
  • 子查询: SELECT product_type, cnt_product FROM (SELECT Product_type, COUNT(*) AS cnt_product FROM Product GROUP BY product_type) AS ProductSum;

    • 子查询作为内层查询会首先执行。
  • 嵌套的子查询:

      SELECT product_type, cnt_product
      FROM (SELECT *
      FROM (SELECT product_type, COUNT(*) AS cnt_product
      FROM Product
      GROUP BY product_type) AS ProductSum
      WHERE cnt_product = 4) AS ProductSum2;
    
  • 标量子查询就是返回单一值的子查询。

    • SELECT product_id, product_name, sale_price FROM Product WHERE sale_price > (SELECT AVG(sale_price) FROM Product);
    • SELECT product_id, product_name, sale_price, (SELECT AVG(sale_price) FROM Product) AS avg_price FROM Product;
    • SELECT product_type, AVG(sale_price) FROM Product GROUP BY product_type HAVING AVG(sale_price) > (SELECT AVG(sale_price) FROM Product);
    • 标量子句只返回一个结果,该子查询绝对不能返回多行结果.
  • 关联子查询会在细分的组内进行比较时使用。

  • 关联子查询和GROUP BY子句一样,也可以对表中的数据进行切分。

  • 关联子查询的结合条件如果未出现在子查询之中就会发生错误。

SELECT product_type, product_name, sale_price
FROM Product AS P1 WHERE sale_price > (SELECT AVG(sale_price)
FROM Product AS P2 WHERE P1.product_type = P2.product_type
GROUP BY product_type);

SELECT product_type, product_name, sale_price
FROM Product AS P1
WHERE P1.product_type = P2.product_type
AND sale_price > (SELECT AVG(sale_price)
FROM Product AS P2
GROUP BY product_type);  --- 作用域不对

SELECT product_type, product_name, sale_price
FROM Product AS P1
WHERE sale_price>(SELECT AVG(sale_price)
FROM Product AS P2
WHERE P1.product_type=P2.product_type
GROUP BY product_type);

CREATE TABLE SampleStr
(str1 VARCHAR(40),
str2 VARCHAR(40),
str3 VARCHAR(40));
START TRANSACTION;
INSERT INTO SampleStr (str1, str2, str3) VALUES ('opx' , 'rt',NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc' , 'def' ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('山田' , '太郎' ,'是我');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aaa' , NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES (NULL , 'xyz',NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('@!#$%' , NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('ABC' , NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aBC' , NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc太郎' , 'abc' ,'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abcdefabc' , 'abc' ,'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('micmic' , 'i' ,'I');
COMMIT;

函数,谓词,case表达式

  • 函数的种类:

    • 算术函数(用来进行数值计算的函数)
      • ABS——绝对值
        • SELECT m, ABS(m) AS abs_col FROM SampleMath;
      • MOD——求余
        • SELECT n, p, MOD(n, p) AS mod_col FROM SampleMath;
      • ROUND——四舍五入
        • SELECT m, n, ROUND(m, n) AS round_col FROM SampleMath;
    • 字符串函数(用来进行字符串操作的函数)
      • ||——字符串拼接.
        • SELECT str1, str2, str1 || str2 AS str_concat FROM SampleStr;
      • MySQL使用CONCAT函数来完成字符串的拼接
        • SELECT str1, str2, str3, CONCAT(str1, str2, str3) AS str_concat FROM SampleStr;
      • LENGTH——字符串长度
        • SELECT str1, LENGTH(str1) AS len_str FROM SampleStr;
      • LOWER——小写转换
        • SELECT str1, LOWER(str1) AS low_str FROM SampleStr WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');
      • REPLACE——字符串的替换
        • SELECT str1, str2, str3, REPLACE(str1, str2, str3) AS rep_str FROM SampleStr;
        • REPLACE(对象字符串,替换前的字符串,替换后的字符串).
      • SUBSTRING——字符串的截取
        • SUBSTRING(对象字符串 FROM 截取的起始位置 FOR 截取的字符数)
        • SELECT str1, SUBSTRING(str1 FROM 3 FOR 2) AS sub_str FROM SampleStr;
    • 日期函数(用来进行日期操作的函数)
      • CURRENT_DATE——当前日期
        • select CURRENT_DATE;
      • CURRENT_TIME——当前时间
        • select CURRENT_TIME;
      • CURRENT_TIMESTAMP——当前日期和时间
        • select CURRENT_TIMESTAMP;
      • EXTRACT——截取日期元素
        • SELECT CURRENT_TIMESTAMP, EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year, EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month, EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day, EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour, EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute, EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
    • 转换函数(用来转换数据类型和值的函数)
      • CAST——类型转换
        • CAST(转换前的值 AS 想要转换的数据类型)
      • COALESCE——将NULL转换为其他值
        • COALESCE(数据1,数据2,数据3……)
        • SELECT COALESCE(NULL, 1) AS col_1, COALESCE(NULL, 'test', NULL) AS col_2, COALESCE(NULL, NULL, '2009-11-01') AS col_3;
    • 聚合函数(用来进行数据聚合的函数)
      • 聚合函数基本上只包含COUNT、SUM、AVG、MAX、MIN
  • 谓词:

    • LIKE
      • 字符串的部分一致查询
      • SELECT * FROM SampleLike WHERE strcol LIKE 'ddd%';
      • SELECT * FROM SampleLike WHERE strcol LIKE '%ddd%';
      • SELECT * FROM SampleLike WHERE strcol LIKE '%ddd';
      • 使用LIKE和_(下划线)进行后方一致查询
        • SELECT * FROM SampleLike WHERE strcol LIKE 'abc__';
    • BETWEEN
      • BETWEEN谓词——范围查询
      • SELECT product_name, sale_price FROM Product WHERE sale_price BETWEEN 100 AND 1000;
    • IS NULL、IS NOT NULL
      • IS NULL、IS NOT NULL——判断是否为NULL
      • SELECT product_name, purchase_price FROM Product WHERE purchase_price IS NULL;
      • SELECT product_name, purchase_price FROM Product WHERE purchase_price IS NOT NULL;
    • IN
      • IN谓词——OR的简便用法
      • SELECT product_name, purchase_price FROM Product WHERE purchase_price IN (320, 500, 5000);
      • SELECT product_name, sale_price FROM Product WHERE product_id NOT IN (SELECT product_id FROM ShopProduct WHERE shop_id = '000A');
    • EXISTS
      • 谓词的作用就是“判断是否存在满足某种条件的记录”。
      • SELECT product_name, sale_price FROM Product AS P WHERE EXISTS (SELECT * FROM ShopProduct AS SP WHERE SP.shop_id = '000C' AND SP.product_id = P.product_id);
      • 通常指定关联子查询作为EXIST的参数。
  • CASE表达式:

    • CASE 表达式也是函数的一种
    • CASE 表达式是在区分情况时使用的,这种情况的区分在编程中通常称为(条件)分支
    • CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种.
      CASE WHEN <求值表达式> THEN <表达式>
      WHEN <求值表达式> THEN <表达式>
      WHEN <求值表达式> THEN <表达式>
      .. .
      ELSE <表达式>
      END
    
    • SELECT product_name, CASE WHEN product_type = '衣服' THEN CONCAT('A :', product_type) WHEN product_type = '办公用品' THEN CONCAT('B :', product_type) WHEN product_type = '厨房用具' THEN CONCAT('C :', product_type) ELSE NULL END AS abc_product_type FROM Product;
    • SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END) AS sum_price_clothes, SUM(CASE WHEN product_type = '厨房用具' THEN sale_price ELSE 0 END) AS sum_price_kitchen, SUM(CASE WHEN product_type = '办公用品' THEN sale_price ELSE 0 END) AS sum_price_office FROM Product;
  • 简单CASE表达式

      CASE <表达式>
      WHEN <表达式> THEN <表达式>
      WHEN <表达式> THEN <表达式>
      WHEN <表达式> THEN <表达式>
      .. .
      ELSE <表达式>
      END
    

SQL 高级处理

  • 窗口函数
    • 窗口函数也称为OLAP 函数.
    • <窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>)
    • RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数
    • 窗口函数兼具分组和排序两种功能。
    • SELECT product_name, product_type, sale_price, RANK () OVER (PARTITION BY product_type ORDER BY sale_price) AS ranking FROM Product;
    • 通过PARTITION BY分组后的记录集合称为“窗口”
    • SELECT product_name, product_type, sale_price, RANK () OVER (ORDER BY sale_price) AS ranking FROM Product; --- 全部商品进行排序。
    • RANK函数 --- 计算排序时,如果存在相同位次的记录,则会跳过之后的位次。
    • DENSE_RANK函数 --- 同样是计算排序,即使存在相同位次的记录,也不会跳过之后的位次。
    • ROW_NUMBER函数 --- 赋予唯一的连续位次。
    • SELECT product_id, product_name, sale_price, AVG (sale_price) OVER (ORDER BY product_id ROWS 2 PRECEDING) AS moving_avg FROM Product; --- 计算移动平均数。
  • GROUPING运算符 --- 得到合计行
    • SELECT '合计' AS product_type, SUM(sale_price) FROM Product UNION ALL SELECT product_type, SUM(sale_price) FROM Product GROUP BY product_type; --- 得到合计行
    • ROLLUP——同时得出合计和小计:
    • GROUPING 运算符包含以下3 种:
      • ROLLUP
        • SELECT product_type, SUM(sale_price) AS sum_price FROM Product GROUP
          BY product_type WITH ROLLUP;
      • CUBE
      • GROUPING SETS
  • GROUPING函数——让NULL更加容易分辨
    • CUBE——用数据来搭积木
    • GROUPING SETS——取得期望的积木
posted @ 2019-11-24 12:18  coding-for-self  阅读(150)  评论(0编辑  收藏  举报