-- 創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE IF NOT EXISTS `JD` 
CHARACTER SET `utf8` 
COLLATE `utf8_persian_ci`;
-- 使用
USE JD;
-- 創(chuàng)建表
CREATE TABLE IF NOT EXISTS `JD`.`goods` (
  id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL ,
  cate_name VARCHAR(40) NOT NULL,
  brand_name VARCHAR(40) not null,
  price decimal(10,3) NOT NULL DEFAULT 0,
  is_show bit NOT NULL DEFAULT 1,
  is_saleoff bit NOT NULL DEFAULT 0
) ENGINE = InnoDB CHARSET = utf8 COLLATE utf8_persian_ci;
-- 插入數(shù)據(jù)
INSERT INTO
  `JD`.`goods` 
VALUES
  (0,"r510vc 15.6英寸筆記本","筆記本","華碩","3399",DEFAULT,DEFAULT);
  INSERT INTO
  `JD`.`goods` 
VALUES
  (0,"y400n 14英寸筆記本","筆記本","聯(lián)想","4999",DEFAULT,DEFAULT);
  INSERT INTO
  `JD`.`goods` 
VALUES
  (0,"g150th 15.6英寸筆記本","游戲本","雷神","8499",DEFAULT,DEFAULT);
  INSERT INTO
  `JD`.`goods` 
VALUES
  (0,"x550cc 15.6英寸筆記本","筆記本","華碩","2799",DEFAULT,DEFAULT);
  INSERT INTO
  `JD`.`goods` 
VALUES
  (0,"svp13226 超極本","超極本","索尼","7999",DEFAULT,DEFAULT);
  INSERT INTO
  `JD`.`goods` 
VALUES
  (0,"ipad mini 7.9英寸平板電腦","平板電腦","蘋果","1999",DEFAULT,DEFAULT);
  INSERT INTO
  `JD`.`goods` 
VALUES
  (0,"ipad air 9.7存平板電腦","平板電腦","蘋果","3388",DEFAULT,DEFAULT);
 
--  查詢類型為 cate_name 為超極本的商品名稱 的name,price
select name,price from goods where cate_name="超極本";
-- 顯示商品種類
-- 1>分組
select cate_name from goods group by cate_name;
-- 2>DISTINCT
select distinct cate_name from goods;

-- 求所有電腦產(chǎn)品的平均價格 ，并保留兩位小數(shù)
select round(avg(price),2) from goods where cate_name in ("筆記本","游戲本","超極本","平板電腦");
-- 顯示每種類型cate_name的的平均價格
select cate_name,avg(price) from goods GROUP BY cate_name;
-- 查詢每種商品的最貴max,最便宜min,平均價avg，數(shù)量count
select max(price) from goods;
-- 查詢所有大于平均價格的商品，并按照價格降序排序
select * from goods where price > (select avg(price) from goods) ORDER BY price DESC;

-- 查詢每種類型中最貴的電腦信息
-- 1.查出每個分類的最貴的
select cate_name,max(price) as max_price  from goods GROUP BY cate_name
-- 2.內(nèi)連接取交集
select * from 
goods
inner join 
(select cate_name,max(price) as max_price  from goods GROUP BY cate_name) as r
on 
goods.cate_name=r.cate_name and goods.price=r.max_price;