一、简单查询：
基本语法：
| 代码如下 | 复制代码 |
| 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 | +-------------------+--------------------------------------+ | 旺旺腾讯微博 | http://www.111cn.net | | 旺旺新浪微博 | http://weibo.com/gz100ww | | 51CTO技术博客 | http://www.111cn.net/ | +-------------------+--------------------------------------+ 10 rows in set (0.00 sec) mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish';选择 |
|
对查询结果排序：
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; mysql> select ID,post_title,post_date from wp_posts where ID>1290 and post_status='publish' limit 2,3; ##红色部分（逗号前的数字）表示偏移量 mysql> select count(post_status) AS 各状态数量,post_status AS 状态名称 from wp_posts group by post_status;
|
|
二、多表查询
连接：
交叉连接：笛卡尔乘积
自然连接：将两张表某字段中相等连接起来，如下
| 代码如下 | 复制代码 |
| 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 | | 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) |
|