SQL常用语句

选择操作

  • 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;
  • 设置外键
暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇