mysql多表查询sql语句怎么写_MySQL基本SQL语句之单表查
询、多表查询和⼦查询
⼀、简单查询：
基本语法：
SELECT * FROM tb_name；查询全部
SELECT field1,field2 FROM tb_name; 投影
SELECT [DISTINCT] * FROM tb_name WHERE qualification; 选择
说明：
FROM⼦句： 要查询的关系 表、多个表、其它SELECT语句
WHERE⼦句：布尔关系表达式，主要包含如下这⼏类表达式：
⽐较：=、>、>=、<=、<
逻辑关系：
AND
OR
NOT
BETWEEN ... AND ... ：在两个值之间
LIKE ‘’
%: 任意长度任意字符
_：任意单个字符
REGEXP, RLIKE ：正则表达式，此时索引⽆效
IN
IS NULL
IS NOT NULL
如下查询本博客的wp-links和wp_posts表：
mysql> select * from wp_links; 查询全部 mysql> select link_name,link_url from wp_links; 投影
+-------------------+--------------------------------------+
| link_name | link_url |
+-------------------+--------------------------------------+
+-------------------+--------------------------------------+
10 rows in set (0.00 sec)
mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish'；选择
+------+----------------------------------------------------------------+----------------------+
| ID | post_title | post_date |
+------+----------------------------------------------------------------+----------------------+
| 1291 | 【转】HP 3PAR存储概念之三 | 2013-08-29 17:21:27 |
| 1298 | 【转】HP 3PAR存储概念之四 | 2013-08-29 17:22:33 |
| 1351 | 【转】XenDesktop 5.5+vSphere 5创建虚拟机报错 | 2013-09-04 17:41:26 |
| 1357 | linux下强⼤的⽹络⼯具Netcat | 2013-09-09 22:26:45 |
| 1360 | MySQL常⽤命令、技巧和注意事项 | 2013-09-20 11:04:15 |
| 1369 | 【转】数据库设计原理知识--B树、B-树、B+树、B*树都是什么 | 2013-09-21 12:30:18 |
| 1379 | MySQL基本SQL语句之常⽤管理SQL | 2013-09-21 12:39:23 |
+------+----------------------------------------------------------------+---------------------+
mysql语句多表查询7 rows in set (0.01 sec)
对查询结果排序：
ORDER BY field_name {ASC|DESC}
如下：
mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' ORDER by ID; ##升序,ID是排序的字段
mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' ORDER by ID DESC;##降序
字段别名：AS
select col_name AS COL_Aliases … ：对字段使⽤别名
select col_name,… from tb_name AS tb_Aliases … ：对表使⽤别名
如下：
mysql> select post_title AS ⽂章标题 from wp_posts where ID>1290 and post_status='publish';
+----------------------------------------------------------------+
单片机程序破解| ⽂章标题 |
+----------------------------------------------------------------+
| 【转】HP 3PAR存储概念之三 |
| 【转】HP 3PAR存储概念之四 |
| 【转】XenDesktop 5.5+vSphere 5创建虚拟机报错 |
| linux下强⼤的⽹络⼯具Netcat |
| MySQL常⽤命令、技巧和注意事项 |
| 【转】数据库设计原理知识--B树、B-树、B+树、B*树都是什么 |
| MySQL基本SQL语句之常⽤管理SQL |
+----------------------------------------------------------------+
7 rows in set (0.02 sec)
##还可以这样:
mysql> select 3+2 AS SUM;
+-----+
| SUM |
+-----+
| 5 |
+-----+
1 row in set (0.00 sec)
LIMIT⼦句：LIMIT [offset,]Count
如下：
mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' limit 5;
+------+--------------------------------------------------+---------------------+
| ID | post_title | post_date |
+------+--------------------------------------------------+---------------------+
| 1291 | 【转】HP 3PAR存储概念之三 | 2013-08-29 17:21:27 |
| 1298 | 【转】HP 3PAR存储概念之四 | 2013-08-29 17:22:33 |
| 1351 | 【转】XenDesktop 5.5+vSphere 5创建虚拟机报错 | 2013-09-04 17:41:26 |
| 1357 | linux下强⼤的⽹络⼯具Netcat | 2013-09-09 22:26:45 |
| 1360 | MySQL常⽤命令、技巧和注意事项 | 2013-09-20 11:04:15 |
+------+--------------------------------------------------+---------------------+
5 rows in set (0.01 sec)
mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' limit2,3; ##红⾊部分(逗号前的数字)表⽰偏移量
+------+--------------------------------------------------+---------------------+
| ID | post_title | post_date |
+------+--------------------------------------------------+---------------------+
| 1351 | 【转】XenDesktop 5.5+vSphere 5创建虚拟机报错 | 2013-09-04 17:41:26 |
| 1357 | linux下强⼤的⽹络⼯具Netcat | 2013-09-09 22:26:45 |
instr什么意思| 1360 | MySQL常⽤命令、技巧和注意事项 | 2013-09-20 11:04:15 |
+------+--------------------------------------------------+---------------------+
3 rows in set (0.00 sec)
聚合：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; 平均值
网页paddingmysql> select count(ID) from wp_posts；计数
分组：GROUP BY，⼀般配合聚合运算使⽤
如下：
mysql> select count(post_status) AS 各状态数量,post_status AS 状态名称 from wp_posts group bypost_status;
+-----------------+--------------+
| 各状态数量 | 状态名称 |
+-----------------+--------------+
| 1 | auto-draft |
| 9 | draft |
| 251 | inherit |
| 238 | publish |
| 2 | trash |
+-----------------+--------------+
5 rows in set (0.01 sec)
注意：可以使⽤HAVING qualification将GROUP BY的结果再次过滤，⽤法同where
⼆、多表查询
连接：
交叉连接：笛卡尔乘积
⾃然连接：将两张表某字段中相等连接起来，如下
单位门户网站模板mysql> SELECT students.Name,students.Age,courses.Cname,students.Gender FROM students,courses WHERE students.CID1 = courses.CID;
+--------------+------+------------------+--------+
| Name | Age | Cname | Gender |
+--------------+------+------------------+--------+
| GuoJing | 19 | TaiJiquan | M |
| YangGuo | 17 | TaiJiquan | M |
| DingDian | 25 | Qishangquan | M |
| HuFei | 31 | Wanliduxing | M |
| HuangRong | 16 | Qianzhuwandushou | F |
| YueLingshang | 18 | Wanliduxing | F |
| ZhangWuji | 20 | Hamagong | M |
| Xuzhu | 26 | TaiJiquan | M |
+--------------+------+------------------+--------+
8 rows in set (0.00 sec)
外连接：
左外连接：left_tb LEFT JOIN right_tb ON ... ：以左表为标准
mysql> SELECT s.Name,c.Cname FROM students AS s LEFT JOIN courses AS c ON s.CID1=c.CID; +--------------+--------------------+
| Name | Cname |
+--------------+--------------------+
| GuoJing | TaiJiquan |
| YangGuo | TaiJiquan |
| DingDian | Qishangquan |
| HuFei | Wanliduxing |
| HuangRong | Qianzhuwandushou |
| YueLingshang | Wanliduxing |
| ZhangWuji | Hamagong |
| Xuzhu | TaiJiquan |
| LingHuchong | NULL |
| YiLin | NULL |
+--------------+--------------------+
10 rows in set (0.00 sec)
右外连接 : left_tb RIGHT JOIN right_tb ON ... ：以右表为标准
mysql> SELECT s.Name,c.Cname FROM students AS s RIGHT JOIN courses AS c ON s.CID1=c.CID; +--------------+--------------------+
| Name | Cname |
+--------------+--------------------+
| GuoJing | TaiJiquan |
| YangGuo | TaiJiquan |
| DingDian | Qishangquan |
| HuFei | Wanliduxing |java中trim函数用法
| HuangRong | Qianzhuwandushou |
| YueLingshang | Wanliduxing |
| ZhangWuji | Hamagong |
| Xuzhu | TaiJiquan |
| NULL | Yiyangzhi |
| NULL | Jinshejianfa |
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论