MySQL-高级查询2

DROP TABLE stu;
DROP TABLE class;
DROP DATABASE zhazhen;
CREATE DATABASE zhazhen;
USE zhazhen;
CREATE TABLE type_table(
tid INT PRIMARY KEY,
tname VARCHAR(10)
);
CREATE TABLE foods(
fid INT PRIMARY KEY,
fname VARCHAR(10) NOT NULL,
fbuild DATE,
fsale DOUBLE,
ftype INT,
CONSTRAINT ft FOREIGN KEY (ftype)
REFERENCES type_table(tid)
);
INSERT INTO type_table VALUES (1,"hot"),(2,"cold"),(3,"normal"),(4,"drink");
INSERT INTO foods VALUES
(1001,"红牛","2021-12-15",6,3),
(1002,"伴面","2020-1-30",5,4),
(1003,"亲嘴烧","2021-11-15",0.5,4);

INSERT INTO foods VALUES
(1004,"火鸡面","2021-1-15",6,3),
(1005,"酸辣粉","2021-4-15",6,2),
(1006,"鸡腿","2021-10-15",6,4),
(1007,"六个核桃","2021-7-15",6,3);

SELECT * FROM type_table;
SELECT * FROM foods;

/*
条件查询：
	where子句：进入内存之前筛选
	having子句：进入内存之后的筛选,在gronp by之后
	能用where解决就不用HAVING
*/
SELECT * FROM foods WHERE fsale<5;
SELECT COUNT(*) AS num,tname,GROUP_CONCAT(fname)
FROM foods,type_table
WHERE foods.`ftype`=type_table.`tid`
GROUP BY ftype
HAVING num>1;
DESC foods;
SELECT * FROM foods;
-- 按类型分组统计价格在4~100之间的foods
SELECT COUNT(*),tname,GROUP_CONCAT(fname)
 FROM foods,type_table
 WHERE fsale BETWEEN 4 AND 100 AND  foods.`ftype`=type_table.`tid`
 GROUP BY ftype;
 -- 排序查询:order by
 /*
 默认：升序ASC
 desc: 降序
 */
 SELECT * FROM foods ORDER BY fsale ASC;
 SELECT * FROM foods ORDER BY fsale,fid DESC;
 -- 分页查询：limit
 SELECT * FROM foods LIMIT 1;-- 查多少条数据
 SELECT * FROM foods LIMIT 2,2; -- 偏移位置，长度
 
 TRUNCATE foods;-- 清空数据 
 SELECT * FROM foods;