SQL常用语句-TMaize Blog

选择操作

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;
```
设置外键