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
- 下划线(_)通配符:用途与%一样,但只匹配单个字符
使用通配符的技巧:
- 不要过度使用,会减慢搜索速度
- 在确实需要使用通配符时,如非必要,不要将之用在开头,这时搜索速度最慢
- 注意通配符的位置,如放错可能得不到想要的结果
正则表达式
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支持