一、简单查询：
1.基本语法：
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
2.对查询结果排序：
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;//降序
3.字段别名：AS
select col_name AS COL_Aliases … //对字段使用别名
select col_name,… from tb_name AS tb_Aliases … //对表使用别名
4.LIMIT子句：
mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' limit 5;//显示匹配到的5条数据
mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' limit2,3; //(逗号前的数字)表示偏移量
5.聚合：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;//计数
6.分组：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 将GROUP BY的结果再次过滤，用法同where
二、多表查询
连接：
交叉连接：笛卡尔乘积
自然连接：将两张表某字段中相等连接起来，如下
mysql> SELECT students.Name,students.Age,ame,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_tbLEFT JOINright_tb ON ... ：以左表为标准
mysql> SELECT s.Name,ame 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,ame FROM students AS s RIGHT 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 |
| NULL | Yiyangzhi |
| NULL | Jinshejianfa |
| NULL | Qiankundanuoyi |
| NULL | Pixiejianfa |
| NULL | Jiuyinbaiguzhua |
+--------------+--------------------+
13 rows in set (0.01 sec)
自连接：本表中不同字段间进行连接
mysql> SELECT c.Name AS student,s.Name AS teacher FROM students AS c,students AS s WHERE c.TID=s.SID;
+-----------+-------------+
| student | teacher |
+-----------+-------------+
| GuoJing | DingDian |
| YangGuo | GuoJing |
| DingDian | ZhangWuji |
| HuFei | HuangRong |
| HuangRong | LingHuchong |
+-----------+-------------+
5 rows in set (0.02 sec)
注意：使用了别名
三、子查询：一个查询中嵌套另外一个查询
如下：在students表中查询年龄大于平均年龄的学生
mysql> SELECT Name,Age FROM students WHERE Age > (SELECT AVG(Age) FROM students);
+-------------+------+
| Name | Age |
+-------------+------+
| DingDian | 25 |
| HuFei | 31 |
| Xuzhu | 26 |
| LingHuchong | 22 |
+-------------+------+
4 rows in set (0.08 sec)
子查询注意事项：
比较操作中使用子查询：子查询只能返回单个值；
IN(): 使用子查询；
在FROM中使用子查询；
联合查询：UNION,将两个查询的结果合并
mysql> (SELECT Name,Age FROM students) UNION (SELECT Tname,Age FROM tutors);
+--------------+------+
| Name | Age |
+--------------+------+
| GuoJing | 19 |
| YangGuo | 17 |
| DingDian | 25 |
| HuFei | 31 |
| HuangRong | 16 |
| YueLingshang | 18 |
| ZhangWuji | 20 |
| HuYidao | 42 |
| NingZhongze | 49 |
+--------------+------+
19 rows in set (0.00 sec)
如果觉得《mysql查询子表的语句_MySQL基本SQL语句之单表查询 多表查询和子查询》对你有帮助，请点赞、收藏，并留下你的观点哦！
