MySQL的一次普通练习

-- 1、创建数据库

CREATE DATABASE AB

USE AB;

DROP DATABASE IF EXISTS AB;

-- 2、创建商品分类表

CREATE TABLE t_category

(

 f_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

 f_categoryName VARCHAR(100) NOT NULL,

 UNIQUE(f_categoryName)

)

INSERT INTO t_category(f_categoryName)

VALUES('笔记本电脑'),

('台式机电脑'),

('一体化电脑'),

('电脑周边'),

('数码产品');

SELECT*FROM t_category;

DROP TABLE t_category;

ALTER TABLE t_category CONVERT TO CHARACTER SET gbk COLLATE gbk_chinese_ci

-- 3、创建商品表

CREATE TABLE t_product

(

 f_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

 f_productName VARCHAR(100) NOT NULL,

 f_categoryID INT NOT NULL,

 f_model VARCHAR(100) NOT NULL,

 f_manufaturer VARCHAR(100) NULL,

 f_dateCreated DATETIME NOT NULL,

 f_remark VARCHAR(500) NULL,

 UNIQUE(f_productName,f_model),

 FOREIGN KEY(f_categoryID)REFERENCES t_category(f_id)

)

ALTER TABLE t_product CONVERT TO CHARACTER SET gbk COLLATE gbk_chinese_ci

INSERT INTO t_product(f_productName,f_categoryID,f_model,f_manufaturer,f_dateCreated,f_remark)

VALUES('C3011摄像头','4','个','联想','2006-09-09','经济适用'),

('家悦E1321','2','台','联想','2006-09-09','强烈推荐'),

('50M-TFO','1','台','NULL','2006-09-09','NULL'),

('家悦E3595速版','2','台','联想','2006-10-15','NULL'),

('家悦E2535','2','台','联想','2006-10-09','NULL');

SELECT*FROM t_product

DROP TABLE t_product;

-- 4、创建用户表

CREATE TABLE t_user

(

 f_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

 f_userName VARCHAR(100) NOT NULL,

 f_userPassword VARCHAR(100) NOT NULL DEFAULT'123'

)

ALTER TABLE t_user CONVERT TO CHARACTER SET gbk COLLATE gbk_chinese_ci

INSERT INTO t_user(f_userName,f_userPassword)

VALUES('张三','123'),

('李四','123'),

('王五','123');

SELECT*FROM t_user;

DROP TABLE t_user;

-- 5、创建商品近销明细表

CREATE TABLE t_productDetail

(

 f_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

 f_productID INT NOT NULL,

 f_codeType CHAR(1) NOT NULL,

 f_price DECIMAL(10,2) NOT NULL,

 f_quantity INT NOT NULL,

 f_dateOperation DATETIME NOT NULL,

 f_dealing VARCHAR(100) NOT NULL,

 f_userID INT NULL,

 f_remark VARCHAR(500) NULL,

 CHECK(f_codeType IN('1','2','3','4')),

 FOREIGN KEY(f_productID) REFERENCES t_product(f_id),

 FOREIGN KEY (f_userID) REFERENCES T_user(f_id) 

)

ALTER TABLE t_productDetail CONVERT TO CHARACTER SET gbk COLLATE gbk_chinese_ci

INSERT INTO t_productDetail(f_productID,f_codeType,f_price,f_quantity,f_dateOperation,f_dealing,f_userID,f_remark)

VALUES('2','1','2400.00','1','2006-09-25','新捷电脑','1','NULL'),

('2','3','3000.00','1','2006-09-29','唐玲','3','收据号2881239'),

('3','1','3200.00','2','2006-09-30','新捷电脑','1','NULL'),

('4','1','3900.00','2','2006-09-30','新捷电脑','1','NULL'),

('2','1','2500.00','3','2006-10-01','新捷电脑','1','NULL'),

('1','1','80.00','10','2006-10-02','新捷电脑','1','NULL'),

('4','3','4500.00','1','2006-10-03','玛丽','3','收据号2879064'),

('3','3','3800.00','1','2006-10-04','李霞','2','收据号1398766'),

('2','3','2900.00','1','2006-10-06','张强','2','收据号1398767'),

('1','3','100.00','1','2006-10-06','张强','2','收据号1398767'),

('4','2','3850.00','1','2006-10-10','新捷电脑','1','价格问题'),

('1','4','100.00','1','2006-10-20','张强','3','质量问题收据号'),

('1','2','80.00','1','2006-10-20','新捷电脑','1','质量问题');

SELECT*FROM t_productDetail;

DROP TABLE t_productDetail;

-- 10商品类别编号为2的台式机电脑的规格型号全部改为”台、黑色、标配“，生产商改为”联想中国“

UPDATE t_product SET f_model="台，黑色，标配",f_manufaturer="联想中国" WHERE f_categoryID=2;

SELECT*FROM t_product;

-- 11删除没有商品的商品类别

DELETE FROM t_category WHERE f_id NOT IN(SELECT f_categoryid FROM t_product);

SELECT*FROM t_category;

-- 12、查询包含“家”字符的商品，按照创建日期最近到远排序

SELECT *FROM T_Product WHERE F_ProductName LIKE '%家%'

ORDER BY F_DateCreated DESC

-- 13、

SELECT*FROM t_productDetail WHERE f_codeType='3'

AND(

f_dateOperation >='2006-10-1' AND  f_dateOperation<='2006-10-7'

OR

f_dateOperation >='2006-5-1' AND f_dateOperation<='2006-5-3'

)

-- 14、

SELECT * FROM t_product WHERE f_manufaturer IS NULL OR f_remark IS NULL;

-- 15

SELECT*,f_price*f_qunanity AS f_amount FROM t_productdetail WHERE f_codeType IN ('3','4')AND f_dateOperation

BETWEEN '2006-09-25' AND '2006-10-20' ORDER BY  f_amount DESC;

SELECT*,f_price*f_qunanity AS F_Amount FROM t_productDetail WHERE f_codeType IN ('3','4')AND

f_dateOperation>='2006-10-1'AND f_dateOperation<='2006-10-31'

ORDER BY F_Amount DESC

SELECT*,f_price*f_qunanity AS F_Amount FROM t_productDetail WHERE f_codeType IN ('3','4')AND

f_dateOperation BETWEEN '2006-10-1' AND '2006-10-31'

ORDER BY F_Amount DESC

-- 16

SELECT*,f_price*f_qunanity AS F_Amount FROM t_productDetail WHERE f_codeType='3'

ORDER BY F_Amount DESC LIMIT 1

SELECT*,f_price*f_qunanity AS F_Amount FROM t_productDetail WHERE f_codeType='3'

ORDER BY F_Amount DESC LIMIT 0,1

-- 17、（只要是count .. null .. 就会非零计数）（count除了空不计数其他都计数。）

SELECT

COUNT(f_id)s,

COUNT(CASE WHEN f_categoryid=1 THEN f_id ELSE NULL END)c1,

COUNT(CASE WHEN f_categoryid=2 THEN f_id ELSE NULL END)c2,

COUNT(CASE WHEN f_categoryid=3 THEN f_id ELSE NULL END)c3,

COUNT(CASE WHEN f_categoryid=4 THEN f_id ELSE NULL END)c4,

COUNT(CASE WHEN f_categoryid=5 THEN f_id ELSE NULL END)c5,

CASE WHEN COUNT(f_id)=0 THEN 0 ELSE ROUND(100*COUNT(CASE WHEN f_categoryid=1 THEN f_id ELSE NULL END)/COUNT(f_id),2)END b1,

CASE WHEN COUNT(f_id)=0 THEN 0 ELSE ROUND(100*COUNT(CASE WHEN f_categoryid=2 THEN f_id ELSE NULL END)/COUNT(f_id),2)END b2,

CASE WHEN COUNT(f_id)=0 THEN 0 ELSE ROUND(100*COUNT(CASE WHEN f_categoryid=3 THEN f_id ELSE NULL END)/COUNT(f_id),2)END b3,

CASE WHEN COUNT(f_id)=0 THEN 0 ELSE ROUND(100*COUNT(CASE WHEN f_categoryid=4 THEN f_id ELSE NULL END)/COUNT(f_id),2)END b4,

CASE WHEN COUNT(f_id)=0 THEN 0 ELSE ROUND(100*COUNT(CASE WHEN f_categoryid=5 THEN f_id ELSE NULL END)/COUNT(f_id),2)END b5

FROM t_product

--

SELECT f_categoryID,COUNT(f_id) FROM t_product GROUP BY f_categoryID

-- 18、

SELECT f_codetype,SUM(f_price*f_qunanity) FROM t_productdetail ORDER BY f_codetype;

SELECT

SUM(CASE WHEN f_codeType=1 THEN f_price*f_qunanity ELSE 0 END)c1,

SUM(CASE WHEN f_codeType=2 THEN f_price*f_qunanity ELSE 0 END)c2,

SUM(CASE WHEN f_codeType=3 THEN f_price*f_qunanity ELSE 0 END)c3,

SUM(CASE WHEN f_codeType=4 THEN f_price*f_qunanity ELSE 0 END)c4

FROM t_productDetail

-- 19、在用户表添加身份证号的字段

ALTER TABLE t_user ADD idcard CHAR(18);

-- 20

 UPDATE t_user SET idcard='510105200103150016' WHERE f_id=1;

 UPDATE t_user SET idcard='510105200209180026' WHERE f_id=2;

 UPDATE t_user SET idcard='510124200007280078' WHERE f_id=3;

 UPDATE t_user SET idcard='510155200312010087' WHERE f_id=4;

SELECT *,IF(MOD(CAST(SUBSTR(idcard,17,1) AS UNSIGNED),2)=1,'男','女') AS sex FROM t_user

SELECT *,CASE WHEN MOD(CAST(SUBSTR(idcard,17,1) AS UNSIGNED),2)=1 THEN '男' ELSE '女' END AS sex

FROM t_user

-- 1、商品类别为笔记本电脑的销售清单，显示T_ProductDetail的所有字段

           

SELECT *

FROM t_productDetail

WHERE f_id IN(SELECT f_id FROM t_category WHERE f_categoryName='笔记本电脑');

-- 2、各商品类别的销售金额，显示所有商品类别名称、销售金额

SELECT f_CategoryName,SUM(f_Price)

FROM t_category,t_productDetail

WHERE t_productDetail.f_ProductID=t_category.f_id GROUP BY f_categoryName

-- 3、用户为张三的销售清单，显示T_ProductDetail的所有字段

SELECT *

FROM t_productDetail

WHERE f_userid IN(SELECT f_id FROM t_user WHERE f_userName='张三')

-- 4、各用户的销售金额，显示所有用户名称、销售金额

SELECT f_id,f_userName,

SELECT IFNULL(SUM(f_qunanity,f_price),0)FROM t_productDetail AS d

WHERE f_codeType='3'AND d.f_userID=u.f_id)AS amount FROM t_user AS u

-- 用连接查询完成

-- 1、商品的销售清单，显示商品类别名称、商品名称、商品规格、销售单价、数量、金额、销售日期、销售者用户名

SELECT*FROM t_category c JOIN t_product p ON c.f_id=p.f_productID

JOIN t_productDetail d ON p.f_id=d.f_productID

JOIN t_user u ON u.f_id=d.f_userID

-- 2、商品类别为笔记本电脑的销售清单，显示商品类别名称、商品名称、商品规格、销售单价、数量、金额、销售日期、销售者用户名

SELECT f_categoryname,f_productname,f_model,f_price,f_quantity,f_price*f_quantity amount,f_dateoperation,f_username

FROM t_category c JOIN t_product p ON c.f_id=p.f_categoryid AND f_categoryname='笔记本电脑'

JOIN t_productDetail d ON p.f_id=d.f_productid AND f_codetype='3'

JOIN t_user u ON u.f_id=d.f_userid

-- 3、用户为张三的销售清单，显示商品类别名称、商品名称、商品规格、销售单价、数量、金额、销售日期

SELECT f_categoryname,f_productname,f_model,f_price,f_quantity,f_price*f_quantity amount,f_dateoperation,f_username

FROM t_category c JOIN t_product p ON c.f_id=p.f_categoryid

JOIN t_productDetail d ON p.f_id=d.f_productid AND f_codetype='3'

JOIN t_user u ON u.f_id=d.f_userid AND f_username='李四'

-- 1、各商品的销售总数量、销售总金额，显示所有商品名称、销售总数量、总金额，包括没有销售过的商品

SELECT `f_productName`,

IFNULL(SUM(f_quantity),0) q,

IFNULL(SUM(`f_price`*f_quantity),0) amount

FROM t_product p LEFT JOIN t_productDetail d ON p.f_id=d.f_productid

AND f_codetype='3'

GROUP BY `f_productName`

-- 2、各商品类别的销售金额，显示所有商品类别名称、销售金额，包括没有销售的商品

SELECT f_categoryname,IFNULL(SUM(`f_quantity`*f_price),0) amount

FROM t_category c LEFT JOIN t_product p ON c.f_id=p.f_categoryid

LEFT JOIN `t_productdetail` d ON p.f_id=d.`f_productID`

AND f_codetype='3'

GROUP BY f_categoryname

-- 3、各用户的销售金额，显示所有用户名称、销售金额 

-- 计算字段例如（YEAR()A和MONTH()）在group by作为分组条件时不能别名

SELECT f_username,IFNULL(YEAR(`f_dateOperation`),0) years,IFNULL(MONTH(`f_dateOperation`),0) months,IFNULL(SUM(`f_quantity`*`f_price`),0) amount

FROM `t_user` u LEFT JOIN `t_productdetail` d ON u.`f_id`=d.`f_userID`

AND `f_codeType`='3'

GROUP BY u.`f_id`,`f_userName`,

YEAR(`f_dateOperation`),MONTH(`f_dateOperation`)

-- 进销统计

SELECT

CASE f_codeType WHEN '1' THEN '1' THEN'采购进货' WHEN '2' THEN '采购退货' WHEN '3' THEN '销售出货' WHEN '4' THEN '销售退货' END f_codeType,

YEAR(`f_dateOperation`) years,MONTH(`f_dateOperation`) months,

IFNULL(SUM(`f_quantity`*`f_price`),0) amount

FROM `t_productdetail` d

GROUP BY `f_codeType`,YEAR(`f_dateOperation`),MONTH(`f_dateOperation`)