选择操作

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