8.30

USE newdb;

-- 查询所有的字段

SELECT * FROM userinfos;

DESC tp_goods;

-- 查询商品id以及对应的超市价格

SELECT goods_id,market_price FROM tp_goods;

SELECT goods_id AS 编号,market_price AS 价格 FROM tp_goods;

SELECT goods_id 编号,market_price 价格 FROM tp_goods;

-- 查询当前商品的品牌id

SELECT brand_id FROM tp_goods;

SELECT DISTINCT brand_id FROM tp_goods;

-- 找出brand_id是15的商品

SELECT * FROM tp_goods WHERE brand_id=15;

-- 找出brand_id是15或goods_id<40的商品

SELECT * FROM tp_goods WHERE brand_id=15 OR goods_id<40;

-- 找出goods_id<200且brand_id是17的商品

SELECT * FROM tp_goods WHERE brand_id=17 AND goods_id<200;

-- 找出商品库中小米的产品

SELECT * FROM tp_goods WHERE goods_name LIKE '小米%';

-- 找出当前数据表中姓张的

SELECT * FROM boys WHERE boyName LIKE '张%';

-- 找出goods_id 是1，3,5,7,11的商品

SELECT * FROM tp_goods WHERE goods_id IN (1,3,5,7,11);

-- 找出goods_id在40-60之间的

SELECT * FROM tp_goods WHERE goods_id>40 AND goods_id<60;  -- 不包括40和60

SELECT * FROM tp_goods WHERE goods_id BETWEEN 40 AND 60;   -- 包括40和60

-- 找出用户表中，年龄为空值的数据

SELECT * FROM userinfos WHERE age IS NULL;

-- 找出id在60-80之间的商品，按照超市价格进行排序

SELECT * FROM tp_goods WHERE goods_id BETWEEN 60 AND 80;

SELECT goods_id,goods_name,market_price FROM tp_goods WHERE goods_id BETWEEN 60 AND 80 ORDER BY market_price;  -- 不写默认ASC升序

SELECT goods_id,goods_name,market_price FROM tp_goods WHERE goods_id BETWEEN 60 AND 80 ORDER BY market_price DESC;  -- 降序

-- 找出id在60-80之间的商品，按照超市价格进行排序，如果价格一致，按照id升序排列

SELECT goods_id,goods_name,market_price FROM tp_goods WHERE goods_id BETWEEN 60 AND 80 ORDER BY market_price DESC，goods_id;

-- 找出超市价格在2000以内的数据，按照id进行降序排序

SELECT * FROM tp_goods WHERE market_price<2000 ORDER BY goods_id DESC;

-- 统计出价格在2000以内的商品总数

SELECT * FROM tp_goods WHERE market_price<2000;

SELECT COUNT(*) FROM tp_goods WHERE market_price<2000;

-- 分别找出商品的最高价格和最低价格

SELECT * FROM tp_goods ORDER BY market_price LIMIT 0,1;

SELECT * FROM tp_goods ORDER BY market_price DESC LIMIT 0,1;

SELECT MAX(market_price) FROM tp_goods; -- 10099

SELECT MIN(market_price) FROM tp_goods; -- 115.9

SELECT * FROM tp_goods WHERE market_price=10099;

SELECT * FROM tp_goods WHERE market_price=115.9;

/* 找出当前所有产品的均价

均价=总价/总数

*/

SELECT SUM(market_price) FROM tp_goods;

SELECT COUNT(*) FROM tp_goods;

SELECT SUM(market_price)/COUNT(*) FROM tp_goods;

SELECT AVG(market_price) FROM tp_goods;