选择操作
- 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;
- 设置外键