MySQL查询数据

单表查询

1.带IN关键字的查询

满足指定范围内条件的记录
SELECT s_id,f_name,f_price FROM fruits WHERE s_id IN(101,102) ORDER BY f_name;
不在条件范围内的记录
SELECT s_id,f_name,f_price FROM fruits WHERE s_id NOT IN(101,102) ORDER BY f_name;

2.带BETWEEN AND的范围查询

SELECT f_name,f_price FROM fruits WHERE f_price BETWEEN 2.00 AND 10.20;
SELECT f_name,f_price FROM fruits WHERE f_price NOT BETWEEN 2.00 AND 10.20;

3.带LIKE字符匹配查询

SELECT f_id,f_name FROM fruits WHERE f_name LIKE 'b%';（查找所有以b开头的水果）
SELECT f_id,f_name FROM fruits WHERE f_name LIKE '%g%';（查询f_name中包含g的记录）
SELECT f_name FROM fruits WHERE f_name LIKE 'b%y';（查询以b开头，以y结尾的水果名）
SELECT f_id,f_name FROM fruits WHERE f_name LIKE '_____y';（以y结尾，y前面有5个字母的记录）
 %用于匹配在指定位置的任意数目的字符
 _下划线通配符一次只能匹配任意一个字符

4.在SELECT语句中使用IS NULL子句，查询空值

SELECT c_id,c_name,c_email FROM customers WHERE c_email IS NULL;
SELECT c_id,c_name,c_email FROM customers WHERE c_email IS NOT NULL;

5.带AND的多条件查询

SELECT f_id,f_name,f_price FROM fruits WHERE s_id='101' AND f_price>=5;
SELECT f_id,f_name,f_price FROM fruits WHERE s_id IN('101','102') AND f_price>=5 AND f_name='apple';

6.带OR的多条件查询

查询s_id=101或s_id=102的水果
SELECT s_id,f_name,f_price FROM fruits WHERE s_id=101 OR s_id=102;
SELECT s_id,f_name,f_price FROM fruits WHERE s_id IN(101,102);
 OR与IN的结果相同，但IN的检索语句简洁明了，IN的执行速度快于OR

7.查询结果不重复（DISTINCT）

SELECT DISTINCT s_id FROM fruits;

8.对查询结果排序

SELECT f_name FROM fruits ORDER BY f_name;
SELECT f_name,f_price FROM fruits ORDER BY f_name,f_price;
SELECT f_name,f_price FROM fruits ORDER BY f_price DESC;（f_price降序排列）
SELECT f_name,f_price FROM fruits ORDER BY f_price DESC,f_name ;

9.分组查询

 GROUP BY关键字通常和集合函数一起使用，例如：MAX(),MIN(),COUNT(),SUM(),AVG()
SELECT s_id,COUNT(*) AS Total FROM fruits GROUP BY s_id;
 MySQL中可以在GROUP BY字节中使用GROUP_CONCAT()函数，将每个分组中各个字段的值显示出来
SELECT s_id,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id;
SELECT s_id,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id HAVING COUNT(f_name)>1;（使用HAVING过滤分组）
SELECT s_id,COUNT(*) AS Total FROM fruits GROUP BY s_id WITH ROLLUP;
 在GROUP BY子句中使用WITH ROLLUP，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和
SELECT o_num,SUM(quantity*item_price) AS orderTotal FROM orderitems GROUP BY o_num HAVING SUM(quantity*item_price)>=100;
SELECT o_num,SUM(quantity*item_price) AS orderTotal FROM orderitems GROUP BY o_num HAVING SUM(quantity*item_price)>=100 ORDER BY orderTotal;

10.使用LIMIT限制查询结果的数量

SELECT * FROM fruits LIMIT 4;（显示前四行）
SELECT * FROM fruits LIMIT 4,3;（从第5个记录开始，行数为3的记录）
 LIMIT[位置偏移量，] 行数 位置偏移量指示MySQL从哪一行开始显示，第一条的位置偏移量是0

使用集合函数查询

MySQL聚合函数
函数 作用
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值的和

1. COUNT()函数

 COUNT(*) 计算表中总的行数，不管某列有数值或者为空值
 COUNT(字段名) 计算指定列下总的行数，计算时将忽略空值的行
SELECT COUNT(*) AS num FROM fruits;
SELECT s_id,COUNT(f_id) FROM fruits GROUP BY s_id;

2.SUM()函数

SELECT SUM(f_price) AS total1 FROM fruits;
SELECT s_id,SUM(f_price) AS total2 FROM fruits GROUP BY s_id;

3. AVG()函数

SELECT AVG(f_price) AS avg_price FROM fruits WHERE s_id=102;
SELECT s_id,AVG(f_price) AS avg_price FROM fruits GROUP BY s_id;（计算每个分组的平均值）

4.MAX()函数

SELECT MAX(f_price) AS max_price FROM fruits;
SELECT s_id,MAX(f_price) AS max_price FROM fruits GROUP BY s_id;（求每个分组的最大值）
SELECT MAX(f_name) FROM fruits；
 MAX()不仅适用于查找数值类型，也可应用于字符类型

5. MIN()函数

SELECT MIN(f_price) AS min_price FROM fruits;
SELECT s_id,MIN(f_price) AS min_price FROM fruits GROUP BY s_id;

使用正则表达式查询

 MySQL使用REGEXP指定正则表达式的字符匹配模式
字符“^”匹配以特定字符或者字符串开头的文本
SELECT * FROM fruits WHERE f_name REGEXP '^b';（查询f_name字段以字母b开头的记录）
SELECT * FROM fruits WHERE f_name REGEXP '^or';（查询f_name字段以be开头的记录）
字符”$” 以特定字符或者字符串结尾的文本
SELECT * FROM fruits WHERE f_name REGEXP 'y$';
SELECT * FROM fruits WHERE f_name REGEXP 'rry$';
字符”.”匹配任意一个字符
SELECT * FROM fruits WHERE f_name REGEXP 'a.g';（查询f_name字段值包含字母a与g且两个字母之间只有一个字母的记录）
星号”*”匹配前面的字符任意多次，包括0次，加号”+”匹配前面的字符至少一次
SELECT * FROM fruits WHERE f_name REGEXP '^ba*';
SELECT * FROM fruits WHERE f_name REGEXP '^ba+';
匹配指定字符
SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';（查询f_name字段值包含字符串”on”,”ap”的记录）
SELECT * FROM fruits WHERE f_name REGEXP 'on';（查询f_name字段值包含字符串”on”的记录）
方括号”[]”指定一个字符集合，只匹配其中任何一个字符
[a-z] 表示集合区间为从a~z的字母
[0-9]表示集合区间为所有数字
SELECT * FROM fruits WHERE f_name REGEXP '[ot]';
SELECT * FROM fruits WHERE s_id REGEXP '[456]';（查询s_id字段中数值包含4,5或6的记录）

连接查询

SELECT suppliers.s_id,s_name,f_name,f_price FROM fruits,suppliers WHERE fruits.s_id=suppliers.s_id;
SELECT suppliers.s_id,s_name,f_name,f_price FROM fruits INNER JOIN suppliers ON fruits.s_id=suppliers.s_id;
 两个表之间的关系通过INNER JOIN指定。连接的条件使用ON子句给出而不是WHERE