mysql 聚合函数字符串_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; -- 设置种子，生成随机数