选择操作

• SELECT .. FROM ..
  最基本的操作，指定选取的表和选取的列
  *号表示选取所有列，或者table.*表示选择这个table的所有列
  注意如果不加where限定的话得到的是多张表的笛卡尔积

  SELECT
        *
    FROM
        myorder,
        order_detail
    WHERE
        myorder.order_id = order_detail.order_id
  
    --
  
    SELECT
        order_detail.*, myorder.order_time
    FROM
        myorder, order_detail
    WHERE
        myorder.order_id = order_detail.order_id
  

• SELECT DISTINCT
  用于排除重复的行

  SELECT `user`.user_name,`user`.user_status FROM `user`
  
    SELECT DISTINCT `user`.user_name,`user`.user_status FROM `user`
  

• ORDER BY
  用于对结果集进行排序

  SELECT
        `user`.*, myorder.order_id,
        myorder.order_time
    FROM
        myorder,
        `user`
    WHERE
        myorder.user_id = `user`.user_id
    ORDER BY
        `user`.user_id ASC,
        myorder.order_time DESC
  

• JOIN
  用于多表的连接,可以使用(select .. where t1,t2,t3 where t1.xx=t2.xx and t2.xx=t3.xx)替代，不过在某些情况下效率有差异
  有INNER JOIN,LEFT JOIN,RIGHT JOIN,FULL OUTER JOIN几种操作
  INNER JOIN就是取交集，LEFT JOIN就是取左边的，如果连接的表在ON条件上有就显示，没有就显示为NULL

  SELECT
        *
    FROM
        order_detail
    INNER JOIN myorder ON order_detail.order_id = myorder.order_id
    INNER JOIN product ON product.product_id = order_detail.product_id
  

  SELECT
        product.product_name,
        order_detail.order_id
    FROM
        product
    LEFT JOIN order_detail ON order_detail.product_id = product.product_id
  
    product_name	order_id
    洗衣机	            1
    洗衣机	            4
    iPhone 8	        4
    iPhone 8	        6
    投影仪	            null
  
    比如用来选取没有被买过的商品
  
    SELECT
        product.product_name
    FROM
        product
    LEFT JOIN order_detail ON order_detail.product_id = product.product_id
    WHERE ISNULL(order_detail.order_id)
  

• UNION
  用于合并两个或多个 SELECT 语句的结果集
  一般用于同一张表的合并，不过不同表也不会出错，不过返回的两个操作返回的列数要一样，其次在上下合并是并不会对数据类型进行检查，按照位置进行合并
  默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL
  UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名

  SELECT * FROM order_detail WHERE product_id = 2
    UNION
    SELECT * FROM order_detail WHERE product_id = 3
  

• 别名
  以为表名称或列名称指定别名，在进行多张表关联时不至于太混乱

  SELECT column_name AS alias_name
    FROM table_name;
  
    SELECT w.name, w.url, a.count, a.date 
    FROM Websites AS w, access_log AS a 
    WHERE a.site_id=w.id and w.name="菜鸟教程";
  

• 子查询
  引用其他查询的结果

  select ename,deptno,sal
    from emp
    where deptno=(select deptno from dept where loc='NEW YORK')；
  
    SELECT ename,job,sal
    FROM EMP
    WHERE deptno in ( SELECT deptno FROM dept WHERE dname LIKE 'A%')；
  

  把结果作为一个表，一定要为结果设置别名

  SELECT * FROM(SELECT * FROM `user` WHERE `user`.user_id) AS u
  

• 多表连接子查询做计算

  select spacesize-used from (select * from xc_tym_user u
    join xc_tym_sizetype s
    on u.sizetype_id=s.id
    where u.id=4) a,(select count(*) used from xc_tym_sizetype) b
  

• GROUP BY .. HAVING
  结果分组，之后所做的统计都是组内的统计
  having 相当于组组内的where

  SELECT
        A.order_id,
        SUM(
            A.product_count * B.product_price
        ) AS money
    FROM
        order_detail A
    INNER JOIN product B ON A.product_id = B.product_id
    WHERE
        A.order_id > 4
    GROUP BY
        A.order_id
    HAVING
        SUM(
            A.product_count * B.product_price
        ) > 500
  

修改操作

update，insert，delete都被归纳为修改操作

• insert
  VALUES里面值额顺序要和table里面的顺序一致

  INSERT INTO table_name
    VALUES (value1,value2,value3,...);
  

  或者直接指定为插入时为哪几个属性赋值

  INSERT INTO table_name (column1,column2,column3,...)
    VALUES (value1,value2,value3,...);
  

• update
  注意如果没有用WHERE来限定条件的话table中指定列的值都会被update

  UPDATE table_name
    SET column1=value1,column2=value2,...
    WHERE some_column=some_value;
  

• delete
  注意如果没有用WHERE来限定条件的话，所有的记录都将被删除

  DELETE FROM table_name
    WHERE some_column=some_value;
  

• 设置外键