一、数据过滤子句
必须子句+过滤子句
1. WHERE(对行进行过滤)
位于 ORDER前,FROM后
#按照sale_price过滤,但只显示prod_name
SELECT m.* FROM milk_tea AS m WHERE m.prod_id=2;
SELECT m.* FROM milk_tea AS m WHERE m.prod_id=2;
SELECT m.* FROM milk_tea AS m WHERE m.sale_price < 5;
SELECT m.* FROM milk_tea AS m WHERE m.sale_price = 15;
SELECT m.prod_name FROM milk_tea AS m WHERE m.sale_price = 15;
(2).计算再筛选
#不展示计算列也可以
SELECT m.*,m.sale_price*0.9 AS new_sale FROM milk_tea AS m WHERE m.sale_price *0.9 < 10;
SELECT m.* FROM milk_tea AS m WHERE m.sale_price *0.9 < 10;
2.BETWEEN... AND...
SELECT m.* FROM milk_tea AS m WHERE m.in_price BETWEEN 5 AND 10 ;
3. IS NULL or IS NOT NULL
SELECT M.* FROM milk_tea AS m WHERE m.sale_price IS NULL;
SELECT M.* FROM milk_tea AS m WHERE m.sale_price IS NOT NULL;
4.LIKE 模糊查询
通配符:_ 或 %
WHERE [ ] LIKE ' ... _...' ; 知道字符数目或者字符位置
SELECT * FROM milk_tea AS m WHERE m.prod_name LIKE '奶_';
SELECT * FROM milk_tea AS m WHERE m.prod_name LIKE '方_面';
SELECT * FROM milk_tea AS m WHERE m.prod_name LIKE '方__';
WHERE [ ] LIKE ' ... %...' ; 不知道字符数目(最少占位0个)、知道字符大概位置
SELECT * FROM milk_tea AS m WHERE m.prod_name LIKE '%糖';
SELECT * FROM milk_tea AS m WHERE m.prod_name LIKE '方便%面';
5.AND
WHERE [ ] AND [ ] AND...;
6.OR
WHERE [ ] OR [ ] OR...;
7.AND OR
SELECT m.* FROM milk_tea AS m WHERE m.sale_price <= 5 AND m.sale_price <= 15;
SELECT m.* FROM milk_tea AS m WHERE m.sale_price <= 5 AND m.sale_price <= 15 OR m.sale_price IS NULL;
SELECT m.* FROM milk_tea AS m WHERE m.sale_price <= 5 OR m.sale_price <= 15;
8.否定NOT
加在条件之前
不能单独使用,只否定紧跟着的一个条件
NOT [ 1] NOT [2 ]
SELECT m.* FROM milk_tea AS m WHERE NOT m.prod_name IN ('奶茶','薯片','棒棒糖');
9. IN 离散而确定的对象
两句输出结果相同:
SELECT m.* FROM milk_tea AS m WHERE m.prod_name='奶茶' OR m.prod_name='薯片' OR m.prod_name='棒棒糖';
SELECT m.* FROM milk_tea AS m WHERE m.prod_name IN ('奶茶','薯片','棒棒糖');
操作:
处理空值:不处理会漏掉空值
IFNULL(对象列 ,把空值替换成某个数值)
不等于: <> 或 !=
通配符放开头会影响查询效率
AND 和 OR 优先度相同,默认从左到右执行,可以加括号表示先执行。
(四)
一 、数据类型
INTEGER 整数型
DECIMAL 定点型(储存固定位数小数)
FLOAT/DOUBLE/REAL 浮点型(储存不固定位数小数)
二、文本型
CHAR 定长字符串(不足位补空位)
VARCHAR 可变长字符串
--在utf-8中,一个中文占三个字节 ; gbk 一个中文占两个字节。
三、日期型
DATE
TIME
DATETIME
四、函数使用
函数名(参数)=函数表达式
(一)数值处理函数:ABS(绝对值函数)ABS(-3)=3
SQRT(平方根函数)
EXP(指数函数)
ROUND(四舍五入函数) ROUND(1.234,2)=1.23
PI(圆周率函数)
(二)文本处理函数
CHAR_LENGTH(字符长度):CHAR_LENGTH(‘数据’)=2
LENGTH(字节数):LENGTH(‘数据’)=6
RTRIM(去除右边空值):RTRIM(‘home ’)='home'
LTRIM :LTRIM(' home')='home'
UPPER(大写字母转换):UPPER('home')='HOME'
LOWER :LOWER('HOME')=''home'
(三)日期处理函数
YEAR/M/D : YEAR('2019-1-2')='2019'
HOUR/M/S :HOUR('14:01:24') ='14'
MONTHNAME : MANTHNAME('2019-1-2') = 'JAN'
NOW : NOW()=systime
CURDATE : CURDATE=' '
CURTIME:
DATE_ADD: DATE_ADD(' ',INTERVAL 1 MONTH)
DATE_SUB
二、聚合函数
(一)计数函数
COUNT(1)
*括号里可以是列数,也可以是列名*
SELECT COUNT(1) AS stuNum
FROM students
WHERE students.class='95031';
COUNT(col) :计算此行有多少列(不能作用于多行)
COUNT(DISTINCT col):去重计数
(二)求和、均值函数
SUM(col)可以同时操作多列
AVG(col)分母不对NULL列计
(三)最值
MAX(col)
MIN(col)
三、分组数据 GROUP BY
SELECT m.net_w, SUM(m.sale_price) FROM milk_tea AS m GROUP BY m.net_w;
SELECT m.net_w, SUM(m.sale_price) FROM milk_tea AS m WHERE m.net_w IN('100g','150g') GROUP BY m.net_w;
GROUP BY...HAVING...
HAVING:对分组结果进行筛选
SELECT m.net_w, SUM(m.sale_price) FROM milk_tea AS m GROUP BY m.net_w HAVING SUM(m.sale_price) > 20;
SELECT m.net_w, SUM(m.sale_price) FROM milk_tea AS m GROUP BY m.net_w HAVING m.net_w IN('100g','150g');
---不推荐,因为是聚合完了再筛选,计算量更大
语句结构
SELECT...FROM... WHERE... GROUP BY... HAVING ...ORDRE BY... ;
必须子句 过滤行 分组 筛选组 排序(最后一句)
执行顺序
SELECT WHERE GROUP BY HAVING SELECT ORDRE BY... ;
#GROUP BY 尽量不使用别名,因为SELECT执行在其之后