MySQL基本SELECT语句

select语句

检索数据

• 检索单个列

SELECT columnName FROM tableName; //单个语句可以不加分号，本语句将返回一组未过滤（过滤将得出结果集的一个子集，消除冗余），未排序的数据

• 检索多个列

SELECT columnsName1,columnsName2,... FROM tableName;//列名中用逗号隔开

• 检索所有列

SELECT * FROM tableName;//使用通配符 *，返回列的顺序一般是在表中定义出现的顺序

• 检索不同的行

SELECT DISTINCT columnsName FROM tableName;//使用关键字DISTINCT，告诉MySQL只返回不同的值

DISTINCT关键字作用于全部列，不能部分使用

SELECT DISTINCT columnsName1,columnsName2 FROM tableName;

除非制定的两个列都不同，否则所有行都会被检索出来

• 限制结果

SELECT columnsName FROM tableName LIMIT 5;//只显示前5行数
SELECT columnsName FROM tableName LIMIT 5,5;//返回从第5行开始（不包括第5行），往后数5个数据

1.第一行是第零行
2.指定返回行数为最大行数，表中数据不够时到最后一行停止
3.MySQL 5版本开始支持：LIMIT 5,5 等价于 LIMIT 5 OFFSET 5

• 使用完全限定的表名或者列名

SELECT tableName.columnsName FROM databaseName.tableName;

排序数据

使用ORDER BY子句

• 按一个列排序

SELECT columnsName FROM tableName ORDER BY columnsName;//按照列的某种顺序排序
SELECT columnsName1 FROM tableName ORDER BY columnsName2;//按照列2的某种顺序来排列列1

• 按多个列排序

SELECT c1,c2,c3 FROM t ORDER BY c1,c2;//先按照c1排序，c1相同的按c2排序

• 制定排序方向（默认升序ASC）

SELECT c1,c2,c3 FROM t ORDER BY c1 DESC;//按c1降序排列
SELECT c1,c2,c3 FROM t ORDER BY c1 DESC, c2;//按c1降序排列，相同的按c1升序排列

• ORDER BY与LIMIT

SELECT c1 FROM t ORDER BY c1 DESC LIMIT 1;//LIMIT位于ORDER BY后

过滤数据

使用WHERE子句，放在ORDER BY子句之前

SELECT c1 FROM t WHERE 条件；

WHERE子句操作符

• AND操作符

SELECT c1,c2,c3 FROM t WHERE 条件1 AND 条件2;//可以有多个AND，同时符合所有条件

• OR操作符

SELECT c1,c2 FROM t WHERE 条件1 OR 条件2;//可以有多个OR，满足任意条件即可

当同时使用AND或者OR时，默认顺序是先满足AND，但是建议尽量使用括号（）括起来，便于阅读。

• IN操作符

SELECT c1,c2 FROM t WHERE 某个属性 IN (条件1，条件2，……)；//功能类似于OR，但速度更快，且支持嵌套SELECT查询子句

• NOT操作符
  用于否定它后面跟的条件，MySQL支持对IN、BETWEEN、EXISTS子句取反

SELECT name,price FROM products WHERE id NOT IN (1002,1003) ORDER BY name;

使用通配符

• LIKE操作符
  为使用通配符，必须使用LIKE操作符，它指示MySQL，后面跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。（此时从技术讲，应该称呼LIKE为谓词）
• 百分号（%）通配符：表示任何字符出现任何次数

SELECT id,name FROM pproducts WHERE name LIKE 'jet%';//查询所有以‘jet’开头的产品的id，name，可以使用多次，出现在任意地方，也可以代表0次出现

不能匹配NULL

WHERE name LIKE '%';即使这样也不会匹配到NULL

• 下划线（_）通配符：用途与%一样，但只匹配单个字符

使用通配符的技巧：

1. 不要过度使用，会减慢搜索速度
2. 在确实需要使用通配符时，如非必要，不要将之用在开头，这时搜索速度最慢
3. 注意通配符的位置，如放错可能得不到想要的结果
   正则表达式

SELECT name FROM products WHERE name REGEXP '1000' ORDER BY name;

关键字LIKE被REGEXP替换
LIKE匹配整个列，如果被匹配文本在列中出现，则不会被找到

LIKE '1000';//如果列中有1000，结果不会返回数据
REGEXP '1000';//返回1000
REGEXP '.000'; //返回四位数后三位为000的值
//匹配不分大小写，若想区分则使用关键字 BINARY
WHERE name REGEXP BINARY 'JetPack .000';
REGEXP '1000|2000';//or操作，可使用多个
REGEXP [123] Ton';//匹配1 Ton，2 Ton，3 Ton，类似于or功能
REGEXP [^123];//匹配除1，2，3字符以外的任何东西
REGEXP [0-9];//匹配0到9

\作为前导，相当于转义

REGEXP '\\.';//匹配.开头的所有字符

字符类

元字符


‘ ^ ’在集合或 [ ] 中表示否定，否则表示在文本开始

• 拼接字段：Concat(str1,str2,...)

SELECT CONCAT(adminName,'(',age,')') FROM admininfo ORDER BY age DESC;

可以通过Rtrim(str) [Ltrim(str)]来去掉右侧[左侧]多余空格，Trim(str)去掉两端空格

SELECT CONCAT(RTRIM(adminName),'   (',age,')') FROM admininfo ORDER BY age DESC;

• 使用别名：AS,同样可以为表起别名

SELECT CONCAT(RTRIM(adminName),'   (',age,')') AS title FROM admininfo ORDER BY age DESC;

• 执行算数计算

SELECT ticketName,price,residue,price*residue AS sumprice FROM ticketinfo ORDER BY sumprice DESC;

使用数据处理函数

• 文本处理函数
  RTrim(),LTrim(),Trim()用来处理空格
  Upper()用来将字母转为大写
  以下为一些常用的文本处理函数
  
  
  其中SOUNDEX()函数是一个将任何文本串转换为其语音表示的字母数字模式的算法。可以进行发音比较搜索，将与目标参数发音相似的结果搜索出来
• 日期和时间处理函数
  常用日期和时间函数
  
  日期格式应该尽量为yyyy-mm-dd，避免出现歧义
• 数值处理函数
  常用的数值处理函数
  
• 聚集函数
  常见的聚集函数
  ，
• AVG()：对特定列满足某些特定条件的行求平均值，只用于单个列，且自动忽略`NULL

SELECT AVG(price) AS avg_price FROM ticketinfo;

• COUNT():对行进行计数，确定总数或满足特定条件的行数；COUNT(*)表示对行总数计数，包括NULL，COUNT(column)对特定列中具有值的行进行计数，忽略NULL

SELECT COUNT(price) AS '价格表' FROM ticketinfo;

• MAX():返回最大值，必须指定列名，若对非数值列使用，返回最后一行，自动忽略`NULL

SELECT MAX(price) AS '最高价' FROM ticketinfo;

• MIN():返回最小值，对非数值使用返回首行，自动忽略NULL
• SUM（）：求和

SELECT SUM(residue*price) AS '剩余总票数' FROM ticketinfo;

使用DISTINCT聚集不同值，不能用于COUNT(*)

• 数据分组：GROUP BY

SELECT roleId,COUNT(*) AS num FROM admininfo GROUP BY roleId ;
SELECT roleId,COUNT(*) AS num FROM admininfo GROUP BY roleId WITH ROLLUP;//得到每个分组以及每个分组汇总级别的值

• 过滤分组：HAVING

SELECT roleId,COUNT(*) AS num FROM admininfo GROUP BY roleId WITH ROLLUP HAVING num>2;

• 排序：ORDER BY

SELECT roleId,COUNT(*) AS num FROM admininfo GROUP BY roleId WITH ROLLUP HAVING num>2 ORDER BY roleId DESC;

• 联结：联结多个表，使用WHERE描述联结条件

SELECT v_name,p_name,p_price FROM v,p WHERE v.id = p.id ORDER BY v_name,p_name;

• 笛卡尔积：由没有联结条件的表关系返回的结果，检索出的行数为第一个表的行数乘以第二个的行数

SELECT v_name,p_name,p_price FROM v,p ORDER BY v_name,p_name;

• 内部联结（等值联结）：JOIN IN...ON

SELECT v_name,p_name,p_price FROM v INNER JOIN p ON v.id = p.id ORDER BY v_name,p_name;
等价于
SELECT v_name,p_name,p_price FROM v,p WHERE v.id = p.id ORDER BY v_name,p_name;

• 自联结

SELECT p1.prod_id,p1.prod_name FROM products AS p1,products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';//这里p1，p2表示同一个表

• 自然联结，由使用者自己在SELECT中设置那些列需要出现
• 外部联结

SELECT customs.cust_id, orders.order_num FROM customs LEFT OUTER JOIN orders ON customs.cust_id = orders.cust_id;

使用RIGHT(LEFT) OUTER JOIN ...ON表示右侧（左侧）表包括其所有行

• 组合查询UNION

SELECT v_id,p_id,p_price FROM p WHERE p_price<=5 UNION SELECT v_id,p_id,p_price FROM p WHERE v_id IN (1001,1002);

等价于

SELECT v_id,p_id,p_price FROM p WHERE p_price<=5 OR v_id IN (1001,1002);

使用时必须满足：
1.必须两条或以上SELECT语句，两两之间用UNION
2.每个查询必须包含相同的列，表达式或聚集函数，出现次序可以不同
3.列数据必须兼容
UNION默认消除重复行，如果不想这样使用UNION ALL
若要排序，在最后加入ORDER BY即可

• InnoDB不支持全文本搜索，MyISAM支持