MySQL基本SQL查询语句:多表查询和子查询示例

小浪SEO博客 专注SEO优化思维、SEO技术的实战分享类博客~

mysql中表单查询用到select命令了，如果是多表查询我们用很多方法，如select union与left join之类的联合查询了，下面我给各位mysql初学者来介绍sql查询示例。

一、简单查询.

基本语法:SELECT * FROM tb_name;

查询全部:SELECT field1,field2 FROM tb_name;

投影代码如下:

SELECT [DISTINCT] * FROM tb_name WHERE qualification;

选择说明:FROM子句:要查询的关系 表、多个表、其它SELECT语句

WHERE子句:布尔关系表达式，主要包含如下这几类表达式:

  1. 比较：=、>、>=、<=、<
  2. 逻辑关系：
  3. AND
  4. OR
  5. NOT
  7. BETWEEN...AND...：在两个值之间
  8. LIKE‘’
  9. %:任意长度任意字符
  10. _：任意单个字符
  11. REGEXP,RLIKE:正则表达式，此时索引无效
  12. IN
  13. ISNULL
  14. ISNOTNULL

如下查询本博客的wp-links和wp_posts表,代码如下:

  1. mysql>select*fromwp_links;查询全部mysql>selectlink_name,link_urlfromwp_links;投影
  2. +-------------------+--------------------------------------+
  3. |link_name|link_url|
  4. +-------------------+--------------------------------------+
  5. |旺旺腾讯微博|http://www.phpfensi.com |
  6. |旺旺新浪微博|http://weibo.com/gz100ww|
  7. |51CTO技术博客|http://www.phpfensi.com/|
  8. +-------------------+--------------------------------------+
  9. 10rowsinset(0.00sec)
  11. mysql>selectID,post_title,post_datefromwp_postswhereID>1290andpost_status='publish';选择
  12. +------+----------------------------------------------------------------+----------------------+
  13. |ID|post_title|post_date|
  14. +------+----------------------------------------------------------------+----------------------+
  15. |1291|【转】HP3PAR存储概念之三|2013-08-2917:21:27|
  16. |1298|【转】HP3PAR存储概念之四|2013-08-2917:22:33|
  17. |1351|【转】XenDesktop5.5+vSphere5创建虚拟机报错|2013-09-0417:41:26|
  18. |1357|linux下强大的网络工具Netcat|2013-09-0922:26:45|
  19. |1360|MySQL常用命令、技巧和注意事项|2013-09-2011:04:15|
  20. |1369|【转】数据库设计原理知识--B树、B-树、B+树、B*树都是什么|2013-09-2112:30:18|
  21. |1379|MySQL基本SQL语句之常用管理SQL|2013-09-2112:39:23|
  22. +------+----------------------------------------------------------------+---------------------+
  23. 7rowsinset(0.01sec)

对查询结果排序:ORDER BY field_name {ASC|DESC},代码如下:

  1. mysql>selectID,post_title,post_datefromwp_postswhereID>1290andpost_status='publish'ORDERbyID;
  3. ##升序,ID是排序的字段
  5. mysql>selectID,post_title,post_datefromwp_postswhereID>1290andpost_status='publish'ORDERbyIDDESC;
  7. ##降序
  8. 字段别名：AS
  9. selectcol_nameASCOL_Aliases…：对字段使用别名

select col_name,… from tb_name AS tb_Aliases …:对表使用别名,如下:

  1. mysql>selectpost_titleAS文章标题fromwp_postswhereID>1290andpost_status='publish';
  2. +----------------------------------------------------------------+
  3. |文章标题|
  4. +----------------------------------------------------------------+
  5. |【转】HP3PAR存储概念之三|
  6. |【转】HP3PAR存储概念之四|
  7. |【转】XenDesktop5.5+vSphere5创建虚拟机报错|
  8. |linux下强大的网络工具Netcat|
  9. |MySQL常用命令、技巧和注意事项|
  10. |【转】数据库设计原理知识--B树、B-树、B+树、B*树都是什么|
  11. |MySQL基本SQL语句之常用管理SQL|
  12. +----------------------------------------------------------------+
  13. 7rowsinset(0.02sec)
  15. ##还可以这样:
  17. 代码如下复制代码
  18. mysql>select3+2ASSUM;
  19. +-----+
  20. |SUM|
  21. +-----+
  22. |5|
  23. +-----+
  24. 1rowinset(0.00sec)

LIMIT子句:LIMIT [offset,]Count,代码如下:

  1. mysql>selectID,post_title,post_datefromwp_postswhereID>1290andpost_status='publish'limit5;
  2. +------+--------------------------------------------------+---------------------+
  3. |ID|post_title|post_date|
  4. +------+--------------------------------------------------+---------------------+
  5. |1291|【转】HP3PAR存储概念之三|2013-08-2917:21:27|
  6. |1298|【转】HP3PAR存储概念之四|2013-08-2917:22:33|
  7. |1351|【转】XenDesktop5.5+vSphere5创建虚拟机报错|2013-09-0417:41:26|
  8. |1357|linux下强大的网络工具Netcat|2013-09-0922:26:45|
  9. |1360|MySQL常用命令、技巧和注意事项|2013-09-2011:04:15|
  10. +------+--------------------------------------------------+---------------------+
  11. 5rowsinset(0.01sec)
  13. mysql>selectID,post_title,post_datefromwp_postswhereID>1290andpost_status='publish'limit2,3;##红色部分（逗号前的数字）表示偏移量
  14. +------+--------------------------------------------------+---------------------+
  15. |ID|post_title|post_date|
  16. +------+--------------------------------------------------+---------------------+
  17. |1351|【转】XenDesktop5.5+vSphere5创建虚拟机报错|2013-09-0417:41:26|
  18. |1357|linux下强大的网络工具Netcat|2013-09-0922:26:45|
  19. |1360|MySQL常用命令、技巧和注意事项|2013-09-2011:04:15|
  20. +------+--------------------------------------------------+---------------------+
  21. 3rowsinset(0.00sec)

聚合:SUM(), MIN(), MAX(), AVG(), COUNT()，括号中为字段名

mysql> select sum(ID) from wp_posts;计算和

mysql> select min(ID) from wp_posts;查早最小的

mysql> select max(ID) from wp_posts; 查找最大的

mysql> select avg(ID) from wp_posts; 平均值

mysql> select count(ID) from wp_posts;计数

分组:GROUP BY,一般配合聚合运算使用,如下:

  1. mysql>selectcount(post_status)AS各状态数量,post_statusAS状态名称fromwp_postsgroupbypost_status;
  2. +-----------------+--------------+
  3. |各状态数量|状态名称|
  4. +-----------------+--------------+
  5. |1|auto-draft|
  6. |9|draft|
  7. |251|inherit|
  8. |238|publish|
  9. |2|trash|
  10. +-----------------+--------------+
  11. 5rowsinset(0.01sec)

注意:可以使用HAVING qualification将GROUP BY的结果再次过滤,用法同where.

二、多表查询

连接:

交叉连接:笛卡尔乘积

自然连接:将两张表某字段中相等连接起来,代码如下:

  1. mysql>SELECTstudents.Name,students.Age,courses.Cname,students.GenderFROMstudents,coursesWHEREstudents.CID1=courses.CID;
  2. +--------------+------+------------------+--------+
  3. |Name|Age|Cname|Gender|
  4. +--------------+------+------------------+--------+
  5. |GuoJing|19|TaiJiquan|M|
  6. |YangGuo|17|TaiJiquan|M|
  7. |DingDian|25|Qishangquan|M|
  8. |HuFei|31|Wanliduxing|M|
  9. |HuangRong|16|Qianzhuwandushou|F|
  10. |YueLingshang|18|Wanliduxing|F|
  11. |ZhangWuji|20|Hamagong|M|
  12. |Xuzhu|26|TaiJiquan|M|
  13. +--------------+------+------------------+--------+
  14. 8rowsinset(0.00sec)

外连接:

左外连接:left_tb LEFT JOIN right_tb ON ...:以左表为标准,代码如下:

  1. mysql>SELECTs.Name,c.CnameFROMstudentsASsLEFTJOINcoursesAScONs.CID1=c.CID;
  2. +--------------+--------------------+
  3. |Name|Cname|
  4. +--------------+--------------------+
  5. |GuoJing|TaiJiquan|
  6. |YangGuo|TaiJiquan|
  7. |DingDian|Qishangquan|
  8. |HuFei|Wanliduxing|
  9. |HuangRong|Qianzhuwandushou|
  10. |YueLingshang|Wanliduxing|
  11. |ZhangWuji|Hamagong|
  12. |Xuzhu|TaiJiquan|
  13. |LingHuchong|NULL|
  14. |YiLin|NULL|
  15. +--------------+--------------------+
  16. 10rowsinset(0.00sec)

右外连接:left_tb RIGHT JOIN right_tb ON ...:以右表为标准,代码如下:

  1. mysql>SELECTs.Name,c.CnameFROMstudentsASsRIGHTJOINcoursesAScONs.CID1=c.CID;
  2. +--------------+--------------------+
  3. |Name|Cname|
  4. +--------------+--------------------+
  5. |GuoJing|TaiJiquan|
  6. |YangGuo|TaiJiquan|
  7. |DingDian|Qishangquan|
  8. |HuFei|Wanliduxing|
  9. |HuangRong|Qianzhuwandushou|
  10. |YueLingshang|Wanliduxing|
  11. |ZhangWuji|Hamagong|
  12. |Xuzhu|TaiJiquan|
  13. |NULL|Yiyangzhi|
  14. |NULL|Jinshejianfa|
  15. |NULL|Qiankundanuoyi|
  16. |NULL|Pixiejianfa|
  17. |NULL|Jiuyinbaiguzhua|
  18. +--------------+--------------------+
  19. 13rowsinset(0.01sec)

自连接:本表中不同字段间进行连接,代码如下:

  1. mysql>SELECTc.NameASstudent,s.NameASteacherFROMstudentsASc,studentsASsWHEREc.TID=s.SID;
  2. +-----------+-------------+
  3. |student|teacher|
  4. +-----------+-------------+
  5. |GuoJing|DingDian|
  6. |YangGuo|GuoJing|
  7. |DingDian|ZhangWuji|
  8. |HuFei|HuangRong|
  9. |HuangRong|LingHuchong|
  10. +-----------+-------------+
  11. 5rowsinset(0.02sec)

注意:使用了别名

三、子查询:一个查询中嵌套另外一个查询

如下,在students表中查询年龄大于平均年龄的学生,代码如下:

  1. mysql>SELECTName,AgeFROMstudentsWHEREAge>(SELECTAVG(Age)FROMstudents);
  2. +-------------+------+
  3. |Name|Age|
  4. +-------------+------+
  5. |DingDian|25|
  6. |HuFei|31|
  7. |Xuzhu|26|
  8. |LingHuchong|22|
  9. +-------------+------+
  10. 4rowsinset(0.08sec)

子查询注意事项:

■比较操作中使用子查询:子查询只能返回单个值；

■IN():使用子查询；

■在FROM中使用子查询；

联合查询:UNION,将两个查询的结果合并,代码如下:

  1. mysql>(SELECTName,AgeFROMstudents)UNION(SELECTTname,AgeFROMtutors);
  2. +--------------+------+
  3. |Name|Age|
  4. +--------------+------+
  5. |GuoJing|19|
  6. |YangGuo|17|
  7. |DingDian|25|
  8. |HuFei|31|
  9. |HuangRong|16|
  10. |YueLingshang|18|
  11. |ZhangWuji|20|
  12. |HuYidao|42|
  13. |NingZhongze|49|
  14. +--------------+------+
  15. 19rowsinset(0.00sec)
  16. //开源代码phpfensi.com

相关广告
  • MySQL基本SQL查询语句:多表查询和子查询示例 MySQL基本SQL查询语句:多表查询和子查询示例 MySQL基本SQL查询语句:多表查询和子查询示例
相关阅读

MySQL基本SQL查询语句:多表查询和子查询示例

2019/10/10 17:37:04 | 谷歌SEO算法 | SEO教程