MySQL（5）：聚合函数、日期函数、字符串函数、数学函数

常见SQL语句练习。

DROP DATABASE IF EXISTS db_ex1;

CREATE DATABASE db_ex1;
USE db_ex1;
CREATE TABLE orders_table (
id INT,
product_name INT,
price FLOAT,
number FLOAT,
-- totlePrice float,
deptNo INT,
an_extra_col INT
);

DESC orders_table; -- description

ALTER TABLE orders_table ADD totlePrice FLOAT; -- 添加列
ALTER TABLE orders_table DROP an_extra_col; -- 删除列
ALTER TABLE orders_table MODIFY product_name VARCHAR(64); -- 改列结构
RENAME TABLE orders_table TO orders; -- 改表名

ALTER TABLE orders CHANGE COLUMN product_name product VARCHAR(64); -- 改列名

SELECT * FROM orders;

INSERT INTO orders (id,product,price,number,deptNo) VALUES (1,'洗衣机',800,2,1);
INSERT INTO orders (id,product,price,number,deptNo) VALUES (2,'电视',1000,1,2);
INSERT INTO orders (id,product,price,number,deptNo) VALUES (3,'洗衣机',700,3,1);
INSERT INTO orders (id,product,price,number,deptNo) VALUES (4,'冰箱',800,1,3);
INSERT INTO orders (id,product,price,number,deptNo) VALUES (5,'洗衣机',700,3,2);
INSERT INTO orders (id,product,price,number,deptNo) VALUES (6,'空调',2000,1,3);
INSERT INTO orders (id,product,price,number,deptNo) VALUES (7,'空调',2000,1,1);

-- 计算总价
UPDATE orders SET totlePrice = number * price;
SELECT * FROM orders;

-- 一共有几种商品
SELECT product FROM orders GROUP BY product;

-- 一共有几种商品，每一种的价格
SELECT product, SUM(totlePrice) FROM orders GROUP BY product;
-- SELECT product, avg(totlePrice) FROM orders GROUP BY product;

-- 1号部门买了多少
SELECT deptNo,product, totlePrice FROM orders WHERE deptNo = 1;

-- 1号部门买的每一类商品的名字和对应的总花费
SELECT deptNo, product, SUM(totlePrice) FROM orders WHERE deptNo = 1 GROUP BY product;

SELECT product, COUNT(*) 购买数量 FROM orders GROUP BY product;

-- having 怎么筛选?
-- where 是聚合（sum、count、avg等）之前筛选
-- having 是聚合后筛选 ...

-- 哪些部门的订单数>1 ? 
SELECT deptNo, COUNT(id) 订单数 FROM orders GROUP BY deptNo HAVING COUNT(id) > 1;
SELECT * FROM orders;

-- group by 的用法
-- 对订单表中商品归类后，显示每一类商品的总价
SELECT product, SUM(price) FROM orders GROUP BY product;
-- 注意，是对产品分类后的，进行sum。sum参与的是分组后的sum

-- having 过滤
-- 查询购买了总价大于100的商品的类别

-- 任务拆解。

-- 1. select * from orders group by product; -- 这是查询购买的所有商品类别，按类别分组显示了

-- 2. 要查询每一类商品的总价，要用sum集合函数

-- 3. 还要求总价大于100，要对sum聚合后的结果再次筛选，要用having！
SELECT product, SUM(price) FROM orders GROUP BY product HAVING (SUM(price)>100);

-- Having和where均可实现过滤，但在having可以使用合计函数,having通常跟在group by后，它作用于组。 
-- 顺序：group by … having … order by …

-- 一共买了哪些类型的产品
SELECT product FROM orders GROUP BY product; 

-- having后面跟的条件判断的字段必须是聚合函数返回的结果

查询出重复的数据

select id,count(*) from A group by A.id havinig count(*)>1;

分页查询
SELECT * FROM orders LIMIT 0, 3; 

函数。

-- 日期和时间函数

SELECT NOW() FROM DUAL;
SELECT CURRENT_TIME() FROM DUAL;

SELECT CURRENT_DATE() FROM DUAL;
SELECT YEAR(NOW()), MONTH(CURRENT_DATE()), SECOND(CURRENT_TIME()) FROM DUAL;
SELECT DATE(NOW()) FROM DUAL;

-- 有一个留言表
CREATE TABLE message(id INT , title VARCHAR(64), publishdate DATETIME);
INSERT INTO message VALUES (1,'title1','2009-12-13 00:12:34');
INSERT INTO message VALUES (2,'title2',NOW());
SELECT * FROM message;

-- 距离现在2天内的结果
SELECT * FROM message WHERE DATE_ADD(publishdate, INTERVAL 2 DAY) >= NOW();

-- 请查询出，两个小时内，发布的消息:
SELECT * FROM message WHERE  DATE_ADD(publishdate, INTERVAL 2 HOUR) >= NOW();
SELECT DATE_ADD(NOW(),INTERVAL +30 DAY) FROM DUAL;
SELECT DATE_ADD(NOW(),INTERVAL -30 DAY) FROM DUAL;

-- 入职100天的人有哪些?
-- select * from xxx where (datediff(now(),hiredate) > 100 );

-- 从现在到2017-2-2，有多少天?（2月2日是博客头像的那位女孩的生日）
SELECT DATEDIFF('2017-2-2', NOW());
SELECT TIMEDIFF('2017-2-2 19:12:00',NOW());

字符串函数

-- 学生
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'张小明',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'李进',67,98,56);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'王五',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'李一',88,98,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'李来财',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'张进宝',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);

SELECT CHARSET(math) FROM student;

SELECT CONCAT(id,'abc') FROM student; -- 字符串连接

-- 把数字123变成字符串

select concat(1,'') from dual;

-- 把smith 第一个字母大写，其它全部小写，怎么办?
SELECT CONCAT(SUBSTRING(UCASE(LCASE('smith')),1,1),SUBSTRING(LCASE('smith'),2 )) FROM DUAL;


-- 数学函数

SELECT ABS(-3.5) FROM DUAL;
SELECT BIN(10) FROM DUAL;
-- select xxx+0 from xxx; -- 把bit的0和1，从控制台显示出来
SELECT CEILING(3.4) FROM DUAL;
SELECT FLOOR(-3.5) FROM DUAL;
SELECT CONV(1111,2,16) FROM DUAL; -- 进制转换 -- 注意啊，这个conv不是卷积convolution…
SELECT RAND() FROM DUAL;
SELECT RAND(1) FROM DUAL; -- 设置种子，生成随机数