MySQL查询的强化使用，系统函数和联合查询学习笔记（难点）

操作符

一、比较操作符

相等 =

不相等 ！= or <>

小于和大于 < or >

比较操作符的组合 <= or >=

二、逻辑操作符

IS NULL

用于NULL值进行比较

例：SELECT * FROM students WHERE height is null;

BETWEEN

定位最大值与最小值之间

语法： 列 BETWEEN 最小值 and 最大值

例：SELECT * FROM students WHERE age BETWEEN 18 AND 28;(包含18和28)

IN

用于把一个值与一个指定列表进行比较，当被比较的值至少与列表中的一个值相匹配时返回true

例：SELECT * FROM students WHERE s_id IN ('J180232','J180708');（搜索ID为J180232和J180708的数据）

LIKE （模糊匹配）

用于通配符把一个值与类似的值进行比较， 百分号：（%）没有字数限制 下划线 (_) 一个下划线表示一个字符

例：SELECT * FROM students WHERE s_name like '%我%';（输出s_name含有‘我’的结果）

模糊匹配：‘%’代表0到任意字符（不管长度）；'_'占位符表示任意一个字符

EXISTS

用于搜索指定表里是否存在满足特定条件的记录。 (子查询）

例：SELECT * FROM students WHERE EXISTS (SELECT NULL);

ALL、SOME、和ANY操作

三、链接操作符

AND 左右的条件都为真是返回真(与)

例：SELECT * FROM students WHERE s_name LIKE '%叶铭锋%' and age>20;

OR 一个为真就返回真(或)

例：SELECT * FROM students WHERE age>20 or age<15;

四、求反操作符

不相等

NOT

BETWEEN

NOT IN

NOT LIKE IS

NOT NULL

NOT EXISTS

五、算术操作符

a+b a-b a*b a/b a%b

加法、减法、乘法、除法、算术操作操作符的组合

例： SELECT 10/3;（结果为3.3333）SELECT 10%3;（结果为1）

 

关键字

FROM

FROM子句：

指定查询数据的表

WHERE

WHERE（条件查询）：

用于给查询添加条件，从而去除用户不需要的数据。

WHERE后边跟的条件值只有true 或false，从而限制我们查询中获取的数据。

ORDER BY

ORDER BY 排序

默认为升序（ASC） 降序(DESC)

语法：

SELECT * FROM 表名 [WHERE 条件] ORDER BY [ASC | DESC] （默认升序）

ASC:升序（从小到大）；

DESC:降序（从大到小）；

例：

select * from students where s_sex='男' ORDER BY s_age;

select * from students ORDER BY s_age;

select * from students where s_age>18 ORDER BY s_age DESC,s_height ASC;

注意：SQL排序是基于字符的ASCII排序，数字0-9会按其字符值进行排序， 并且位于A-Z之前。

LIMIT

limit 关键字

限制结果条数

在哪个位置开始取多少个结果

语法：

LIMIT[OFFSET,]NUM 

OFFSET 代表位移

NUM 代表可以取多少条

mysql的分页公式：

LIMIT (页码-1)*每页条数，每页条数

例：

select * from 表名 [where 条件] LIMIT 起始值,要取的条数

select * from students LIMIT 1,2; 从第二条开始取两条数据。

 

GROUP BY

分组

与SELECT语句配合使用，把相同的数据划分为组。在select语句里，group by子句在where条件之后，在order by 子句之前。

SELECT的返回列中只能出现GRROP BY 的字段，如果要出现其他值必须是结合聚合函数使用的

常用的统计函数：count：求总行数（统计数量） avg：求平均值  min:求最小值  max:求最大值  sum 求总和

语法：

SELECT 列名1,列名2 FROM 表名

WHERE 条件

GROUP BY 列名1,列名2

ORDER BY 列名1,列名2 ;

例：

统计每个性别的人数：

SELECT sex,count(1) FROM students GROUP BY sex;

统计每个性别的最大年龄：

SELECT sex,max(age) FROM students GROUP BY sex;

统计每个性别中身高最高的值：

SELECT sex,max(height) FROM students GROUP BY sex;

统计每个性别的平均身高：

SELECT sex,avg(height) FROM students GROUP BY sex;

注意：被选中的字段（查询中select之后的字段列表）必须在group by子句里引用过(mysql不受此约束)，avg，max，min，sum和count 函数除 外。

HAVING

having 筛选

该子句在select 语句里与group by 子句联合使用时，用于告诉group by 子句在输出里包含那几个分组。

Having 对于group by 的作用相当于 where对于select的作用。

WHERE跟在表名之后，HAVING是用在GROUP BY之后

HAVING的表达式跟WHERE是一样的

语法：

SELECT 列名1,列名2 FROM 表名

WHERE 条件

GROUP BY 列名1,列名2

HAVING 条件

ORDER BY 列名1,列名2

HAVING 子句必须跟在GROUP BY子句之后，在ORDER BY 子句之前。

 

系统函数

一、概念

函数是SQL里的关键字，用于对字段里的数据进行操作。函数是一个命令，通常与字段名 称或者是表达式联合使用，处理输入的数据并产生结果

二、常用函数

常用函数

控制函数	字符串函数	数学函数	日期时间函数	汇总函数
CASE	SUBSTRING	ABS	DATEDIFF	AVG
IF	LEFT	FLOOR	DATE_ADD	COUNT
NULLIF	LENGTH	RAND	STR_TO_DATE	MAX
IFNULL	LOWER		DATE_FORMAT	MIN
	REPLACE			SUM
	CONCAT

 

控制函数

CASE WHEN

语法：

CASE 值 WHEN 比较的值 THEN 返回的值 WHEN ... THEN... END

CASE WHEN 表达式 THEN ture时候的返回值 ELSE false时候的返回值 END

例：

SELECT s_name, CASE s_sex WHEN 0 THEN '女性' WHEN 1 THEN '男性' END 性别 FROM students;

SELECT s_name, CASE WHEN s_age>23 THEN '大叔' ELSE '小鲜肉' END FROM students;

IF

语法：

IF (表达式，true时候的返回值，false时候的返回值)

例：

SELECT s_name,IF(s_age>23,'大叔','小鲜肉）FROM students;

IFNULL

语法：

IFNULL(字段值,为空时返回的值)

例：

SELECT IFNULL(s_height,'未知') FROM students;

（如果s_height为NULL则返回‘未知’）

NULLIF

语法：

NULLIF(第一个值，第二个值)

如果第一个值与第二个值相等，则返回NULL，否则的话返回第一个值

例：

SELECT s_name,NULLIF(s_age,0) FROM students;

（如果s_age为0，则返回NULL）

 

字符串函数

SUBSTRING

从起始位置开始截取相应长度的字符

语法：

SUBSTRING(str FROM pos FOR len)

SUBSTRING(str FROM pos)

SUBSTRING(str,pos)

SUBSTRING(str,pos,len)

str：值 pos：起始下表 len：截取长度 

下标从1开始

例：

SELECT SUBSTRING(s_name,1,5) s_name FROM students;

LEFT/RIGHT

从左/右边开始截取相应数量的字符

语法：

LEFT(值，截取长度)

RIGHT(值，截取长度)

例：

SELECT LEFT(s_name,3) FROM students;

SELECT RIGHT(s_name,3) FROM students;

LENGTH

计算值的长度

语法：

LENTH(值)

例：

SELECT LENGTH(s_name),s_name FROM students;(utf-8中文占3个长度)

LOWER/UPPER

将值全部转化为大写/小写

语法：

LOWER(值)

UPPER(值)

例：

SELECT LOWER(s_name) FROM students;

SELECT UPPER(s_name) FROM students;

REPLACE

替换指定字符

语法：

REPLACE(值,被替换的字符,替换后的字符)

例：

SELECT REPLACE(s_name,'SB','**') FROM students;

CONCAT

字符串拼接

语法：

CONCAT(值,字符串,值,值....)

例：

SELECT CONCAT(';',s_id,s_name,s_age) FROM students;

SELECT CONCAT_WS(';',s_id,s_name,s_age) FROM students;（ws=with separator 以;分隔字符）

数学函数

ABS(数字)：取绝对值

FLOOR(数字)：去尾法取值

CEIL(数字)：进一法取值

ROUND(数字)：四舍五入取值

RAND(数字)：随机生成一个0到1范围的数字

日期函数

日期说明符：

%Y：年  

%m：月 

%d：日

%H：时

%i：分 

%s：秒

DATEDIFF

比较两个时间点之间的差值

语法：

DATEDIFF(时间点1,时间点2)

例：

SELECT DATEDIFF(now(),STR_TO_DATE('2016-09-08','%Y-%m-%d)) FROM students;

（比较现在时间和2016-09-08之间的差值）

DATE_ADD

在一个时间点上增加一个时间段

语法：

DATE_ADD(时间点，时间段)

例：

SELECT DATE_ADD(now(),INTERVAL '2:4' DAY HOUR);

STR_TO_DATE

将字符串转化为时间

例：

STR_TO_DATE('2016-09-08','%Y-%m-%d)

DATE_FORMAT

将日期格式化（转化为字符串）

例：

SELECT DATE_FORMAT(NOW(),'%Y,%m,%d:%H,%i,%s');

 

多表联合查询

笛卡尔积：（了解）

外连接

LEFT JOIN / RIGHT JOIN

左连接/右连接：

左连接：以左边的表尾标准，去右表查询数据，如果没有匹配到数据，则以null补充

右连接：以右边的表尾标准，去左表查询数据，如果没有匹配到数据，则以null补充

外连接查询时，主表的数据为准，次表只是作为补充。主表的数据不存在的话，次表多余的数据不会展示出来

语法：

LEFT JOIN 右表 ON 条件

RIGHT JOIN 左表 ON 条件

例：

SELECT s.s_name,c.c+name FROM students s LEFT JOIN class c ON s.class_id =c.class_id;

SELECT s.s_name,c.c+name FROM students s RIGHT JOIN class c ON s.class_id =c.class_id;

内连接

INNER JOIN

把两个表相关联的数据展示出来，相当于求两个集合的交集。

语法：

SELECT A.* ,B.* FROM A INNER JOIN B ON A.colName = B.colName

例：

SELECT s.name,c.c_name FROM students s INNER JOIN class c ON s.class_id = c.class_id;

SELECT s.name,c.c_name FROM students s,class c WHERE s.class_id = c.class_id;

联合查询

UNION

联合查询 UNION[ALL] 不加ALL的情况下，会自动去重，加上ALL，不去除重复记录

拼接两个或以上SELECT结果集

例：

SELECT s_name FROM students UNION SELECT c_name FROM class;