MySQL语句入门（三）

第十五章、联结表

联结：是一种机制，用来在一条SELECT语句中关联表
数据存在多个表中，用单条SELECT语句检索出数据。使用特殊的语法，可以 联结多个表返回一组输出

1. 创建联结

   规定要联结的所有表以及它们如何关联即可

   --供应商名，产品名，产品价格
   SELECT v_name,p_name,p_price
   FROM vendors,products
   WHERE vendors.v_id=products.v_id
   ORDER BY v_name,p_name;
   

• WHERE语句的重要性

  应该保证所有联结都有WHERE语句

  在一个SELECT语句中联结几个表时，相应的关系是在运行中构造的。数据库表的定义中并不能指示MySQL如何对表进行联结。在联结时，实际上是靠我们自己将第一个表中的每一行与第二个表中的每一行配对。

  而WHERE子句作为过滤条件，只留下匹配给定条件（联结条件）的行。若没有WHERE子句，在匹配时不会管它们是否可以配在一起

  笛卡尔积：由没有联结条件的表关系返回的结果为笛卡尔积。
  检索出行的数目 = 第一个表的行 * 第二个表中的行

• 内部联结

  目前为止的联结称为等值联结（内部联结），是基于两个表之间相等的测试

  可以用不同的语法来联结

  --供应商名，产品名，产品价格
  SELECT v_name,p_name,p_price
  FROM vendors INNER JOIN products
  ON vendors.v_id=products.v_id;
  -- INNER JOIN 与 ON 配套
  

• 联结多个表

  SELECT语句中可以联结的表的数目没有限制

  SELECT p_name,v_name,p_price,quantity
  FROM orderitems,products,vendors
  WHERE products.v_id = vendors.v_id
  AND orderitems.p_cd = products.p_id
  AND order_num =1001;
  

---

第十六章、创建高级联结

1. 联结中使用表别名

   FROM customers AS c,orders AS o,orderitems AS oi

   作用：缩短了SQL语句，允许在单条SELECT语句中多次使用相同的表

2. 使用不同类型的联结

   • 自联结

     通常作为外部语句来替代从相同表中检索数据时使用的子查询语句

     --已知一物品，找生产改物品的供应商生产的其他物品
     
     子查询：
     SELECT p_id,p_name
     FROM products
     WHERE v_id = (SELECT v_id
                   FROM products
                   WHERE p_name = 'nova 7');
     自联结：
     SELECT p1.p_id,p2.p_name
     FROM products AS p1,products AS p2
     WHERE p1.v_id = p2.v_id
     AND p2.p_name= 'nova 7';
     

• 自然联结

  排除列的多次出现，使每个列只返回一次

  一般是通过对表使用通配符（SELECT *）,对所有其他表的列使用明确的子集来完成

• 外部联结

  联结那些在相关表中没有关联行的行，称为外部联结，比如：列出所有产品以及订购规模，包括了没有人订购的产品。

  存在两种基本的外部联结形式：左外部联结和右外部联结。即关联表的顺序不同，可通过交换WHERE或FROM子句中两表的顺序转换

  LEFT OUTER JOIN:从FROM语句左边表向右选择

  RIGHT OUTER JOIN:从FROM语句右边表向左边选择

  --内部联结：检索所有客户及订单
  SELECT customers.c_id,orders.order_num
  FROM customers INNER JOIN orders
  ON customrs.c_id = orders.c_id;
  
  --外部联结：检索所有客户及订单，包括没有订单的客户
  SELECT customers.c_id,orders.order_num
  FROM customers LEFT OUTER JOIN orders
  ON customrs.c_id = orders.c_id;
  

• 带聚集函数的联结

  SELECT x.c_name,x.c_id,COUNT(xx.order_num) AS num
  FROM customers AS x,orders AS xx --相互联结
  WHERE x.c_id=xx.c_id  --联结条件
  GROUP BY x.c_id;   --分组
  

3. 联结的小结

   • 一般使用内部联结，但使用外部联结也是有效的

   • 必须提供联结条件，否则会得到笛卡尔积

   • 需使用正确的联结条件，否则将返回不正确的数据

   • 一个联结可以包含多个表，每个联结可以采用不同的联结类型（最好先测试每个联结，易排除故障

---

第十七章、组合查询

1. 前言

   并（复合查询）：执行多个查询，并将结果作为单个结果集返回。

   使用情况：

   • 在单个查询中从不同的表返回类似的数据

   • 对单个表执行多个查询，按单个查询返回数据

   任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询，根据情况选择

2. 组合查询UNION

   • 规则

     必须由两条或以上SELECT语句组成，语句间用关键字UNION分隔

     每个列必须包含相同的列、表达式或聚集函数

     列的数据类型必须兼容

   • UNION ALL

     UNION会从查询结果中自动忽略重复的行（与多个WHERE子句的行为一样）

     UNION ALL则能够返回所有匹配行（WHERE子句无法完成的工作）

   • 排序

     使用UNION组合查询时，只能使用一条ORDER BY子句，必须出现在最后一条SELECT语句之后

   SELECT  v_id,p_name,p_price 
   FROM products 
   WHERE p_price<4000
   UNION[ALL] --[]表示能够使用
   SELECT  v_id,p_name,p_price 
   FROM products 
   WHERE v_id IN(1001,1002);
   ORDER BY v_id,p_name;
   
   --显示的结果类似于用OR连接多个WHERE语句
   

---

第十八章、全文本搜索

相比于通识符和正则表达式，全文本搜索性能更好，更能明确控制，更加智能化。

全文本搜索时，不需要分别查看每个行、分析和处理每个词，它创建指定列中各词的一个索引，搜索则针对这些词进行

1. 使用

• 启用FULLTEXT

  一般在创建表时启用全文本搜索，不要再导入数据时使用（关于引擎见21章）

• 使用

  Match()函数:指定被搜索的列

  Against()函数：指定要使用的搜索表达式

SELECT note
FROM pronotes
WHERE Match(note) Against('Hello');

• 排序（等级值）

  SELECT note
         Match(note) Against('Hello') AS xx
  FROM productnotes;
  

  等级由MySQL根据行中词的数目，唯一词的数目，整个索引中词的总数，包含改词的行的数目计算出来。

  不包含词xxx的行等级为0，包含词xxx则有等级值，词靠前的行的等级值比词靠后的等级值高

2. 其他用法

• 查询拓展

  • 作用：设法放宽所返回的全文本搜索结果的范围，返回与搜索有关的其他行

  • 过程：MySQL先进行基本的全文本搜索，找出匹配的行；其次，检查这些匹配行并选择所有有用的词（MySQL自行判定）；最后，使用原来的条件和所有有用的词在进行一次全文本搜索。

    -- 添加WITH QUERY EXPANSION关键字
    SELECT note
    FROM pronotes
    WHERE Match(note) Against('Hello' WITH QUERY EXPANSION);
    

  • 表中的行越多，说明使用查询拓展的结果越好

• 布尔文本搜索

  • 关键字：IN BOOLEAN MODE

  • 操作符

    布尔操作符	说明
    +	包含，词必须存在
    -	排除，词必须不出现
    >	包含，增加等级值
    <	包含，减少等级值
    ()	把词组成表达式
    ~	取消一个词的排序词
    *	词尾的通配符
    “ ”	定义一个短语

    Against('" HELLO WORLD"',IN BOOLEAN MODE)
        --匹配的是一个短语，而不是两个词
    
    Against('>HELLO  <WORLD',IN BOOLEAN MODE) 
        -- 增加前者等级，降低后者等级
    

---

第十九章、插入数据

1. 插入完整的行

   格式：
   INSERT INTO 表名(列名1，列名2...) VALUES (列名1数据，列名2数据...);
   例：
   INSERT INTO customers(c_name,c_city,c_email)
   VALUES('xxx','BeiJing',NULL);
   
   --有提供列名，必须对每个列出的列提供一个值；未提供列名，必须给每个表列提供一个值
   --VALUES 必须以指定的次序匹配指定的列名
   

   使用这种语法，可以省略列。前提是：该列定义为允许NULL值；在表的定义中有给出默认值。

   INSERT LOW_PRIORITY INTO能指示MySQL降低INSERT语句的优先级

2. 插入多个行

   格式一：
   INSERT INTO 表名(列名1，列名2...) VALUES (列名1数据，列名2数据...);
   INSERT INTO 表名(列名1，列名2...) VALUES (列名1数据，列名2数据...);
   
   格式二（每条INSERT语句中的列名都相同）：
   INSERT INTO 表名(列名1，列名2...) 
   VALUES (列名1数据，列名2数据...),VALUES (列名1数据，列名2数据...)...;
   

3. 插入检索中的数据

   INSERT INTO 表名(列名1，列名2...)
   SELECT 列名1，列名2...
   FROM custnew;
   
   --将custnew的所有数据导入customers
   --主键的值不能重复！！！
   --INSERT和SELECT的列名可以不用相同，使用的是列所在的位置
   

---

第二十章、更新与删除数据

1. 更新数据UPADATE

   更新一个列：UPDATE 表名 SET 列名 = 新值 WHERE 指定行
   更新多个列：UPDATE 表名 SET 列名 = 新值,列名 = 新值... WHERE 指定行
   更新所有行：UPDATE 表名 SET 列名 = 新值;  --没有WHERE语句
   

   UPDATE语句中可以使用子查询，即有SELECT语句检索出的数据更新列数据

   当更新出现差错时，用UPDATE IGNORE 表名能够继续更新

   删除某个列的值：可设置它为NULL（前提是表定义允许NULL值）

2. 删除数据DELETE

   删除一行：DETELE FROM 表名 WHERE 指定行;
   删除所有行：DETELE FROM 表名;   --没有WHERE语句
   

   DETELE语句删除的是表的内容，而不是表的本身

   TRUNCATE TABLE:能够更快的删除所有行（实际为删除一个表并重新建立一个表）

3. 指导原则

   • 一定要注意WHERE语句的使用

   • 使用WHERE语句之前，最好先用SELECT语句测试，防止出错

---

第二十一章、创建和操纵表

1. 创建表

   • CREATE TABLE 关键字

   • 关于NULL值

     NULL值就是没有值或缺值

     NULL:允许NULL值，允许插入行时不给出该列的值（默认）

     NOT NULL :不允许NULL值，即该列插入时必须要有值

   • PRIMARY KEY:主键，必须唯一，不可以为空，可组合PRIMARY KEY(p_id,p_x)

   • AUTO_INCREMENT:每次执行一个INSERT操作时，MySQL将会自动对该列增量,在每个表中是唯一的

   • DEFAULT:可指定使用默认值。不允许使用函数为默认值，只能是常量。不是NULL

   • 引擎类型

     ENGINE = 引擎类型，可省略，默认类型为MyISAM

     MySQL中具有多种引擎:

     • InnoDB：一个可靠的事务处理引擎，不支持全文本搜索

     • MyISAM：支持全文本搜索，但不支持事务处理

     引擎类型可以混用。但是外键不能跨引擎

   • CREATE TABLE products
     (
         p_id int PRIMARY KEY AUTO_INCREMENT,
         p_name char(20) NOT NULL,
         p_price decimal(4,2) NOT NULL，
         quantity int NOT NULL DEFAULT 1,
         p_city char(20) NULL
     )ENGINE = InnoDB;
     

2. 更新表

   添加一个列：ALTER TABLE 表名 ADD 新增列名 数据类型;
          例：ALTER TABLE vendors ADD v_phone CHAR(10);
   
   
   删除一个列：ALTER TABLE 表名 DROP COLUMN 列名;
          例：ALTER TABLE 表名 DROP COLUMN v_phone;
   
   修改列名：ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型
        例：ALTER TABLE vendors CHANGE v_phone phone CHAR(10);
   
   添加外键约束:ALTER TABLE 从表 ADD CONSTRAINT 外键
   (定义外键)   FOREIGN KEY 从表(外键字段) REFERENCES 主表(主键字段);
            例：ALTER TABLE products ADD CONSTRAINT fk_p_o
                FOREIGN KEY products(v_id) REFERENCES vendors(主键字段);
   

3. 删除表

   DROP TABLE 表名;删除整个表而不是其内容

4. 重命名表

   RENAME TABLE 新名 TO 表名;