SQL查询和排序代码

use  shop;

CREATE TABLE product
(product_id CHAR(4) NOT NULL,
 product_name VARCHAR(100) NOT NULL,
 product_type VARCHAR(32) NOT NULL,
 sale_price INTEGER ,
 purchase_price INTEGER ,
 regist_date DATE ,
 PRIMARY KEY (product_id));
 
ALTER TABLE product ADD COLUMN product_name_pinyin VARCHAR(100);
 
ALTER TABLE product DROP COLUMN product_name_pinyin;


INSERT INTO Product VALUES ('0001','T恤衫','衣服',1000,500,'2009-09-20');           #verchar和char需要打引号，其他都不需要引号。
INSERT INTO Product VALUES ('0002', '打孔器','办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤','衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子','厨房用具', 500, NULL, '2009-09-20');
INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
COMMIT; #DML需要提交即COMMIT，(insert、update、delete);DDL会自动提交(create、alter ...)

ALTER TABLE product
ALTER sale_price SET DEFAULT 0;

SELECT product_name,product_type 
from product
WHERE product_type='衣服';

-- 想要查询出全部列时，可以使用代表所有列的星号（*）。
SELECT * FROM product;

-- SQL语句可以使用AS关键字为列设定别名（用中文时需要双引号（“”））。
SELECT product_id As id,
product_name As name,
purchase_price AS "进货单价"
FROM product;

-- 选取出sale_price列为500的记录
SELECT product_name,product_type
FROM product
WHERE sale_price = 500;

-- SQL语句中也可以使用运算表达式
SELECT product_name,sale_price,sale_price * 2 AS "sale_price x2"
FROM product;

-- WHERE子句的条件表达式中也可以使用计算表达式
SELECT product_name,sale_price,purchase_price
FROM product
WHERE sale_price-purchase_price >= 500; 

/* 对字符串使用不等号
首先创建chars并插入数据
1选取出大于‘2’的SELECT语句*/

-- DDL：创建表
CREATE TABLE chars
(chr CHAR(3)NOT NULL,
PRIMARY KEY(chr));

-- 选取出大于'2'的数据的SELECT语句('2'为字符串)
SELECT chr
FROM chars
WHERE chr > '2';

-- 选取NULL的记录
SELECT product_name,purchase_price
FROM product
WHERE purchase_price IS NULL;

-- 选取不为NULL的记录
SELECT product_name,purchase_price
FROM product
WHERE purchase_price IS NOT NULL;

-- 选取出销售单价大于等于1000日元的记录
SELECT product_name,product_type,sale_price
FROM product
WHERE sale_price >= 1000;

-- 向代码清单2-30的查询条件中添加NOT运算符
SELECT product_name,product_type,sale_price
FROM product
WHERE NOT sale_price >= 1000;

-- “商品种类为办公⽤品”并且“登记⽇期是 2009 年 9 ⽉ 11 ⽇或者 2009 年 9 ⽉ 20 ⽇”
-- 理想结果为“打孔器”，但当你输⼊以下信息时，会得到错误结果
-- 通过使用括号让OR运算符先于AND运算符执行
SELECT product_name, product_type, regist_date
FROM product
WHERE product_type = '办公用品'
AND ( regist_date = '2009-09-11'
OR regist_date = '2009-09-20');

-- 编写1条SQL语句，从product（商品）表中选取出“登记日期（regist在2009年4⽉28⽇之后”的商品，查询结果要包含product name和regist_date两列。
SELECT product_name,regist_date
FROM product 
WHERE regist_date > '2009-04-28';

SELECT * FROM product
WHERE purchase_price = NULL;

SELECT * FROM product
WHERE purchase_price <> NULL;

SELECT *
FROM product
WHERE product_name > NULL;

SELECT *
FROM product
WHERE NOT sale_price-purchase_price < 500;

SELECT product_name,product_type, sale_price * 0.9 - purchase_price AS profit
FROM product
WHERE sale_price * 0.9 - purchase_price > 100 AND (product_type="办公用品"OR "厨房用具") ;

-- 计算全部数据的行数（包含NULL）
SELECT COUNT(*)
FROM product;
-- 计算NULL以外数据的行数
SELECT COUNT(purchase_price)
FROM product;
-- 计算销售单价和进货单价的合计值
SELECT SUM(sale_price), SUM(purchase_price)
FROM product;
-- 计算销售单价和进货单价的平均值
SELECT AVG(sale_price), AVG(purchase_price)
FROM product;
-- MAX和MIN也可用于非数值型数据
SELECT MAX(regist_date), MIN(regist_date)
FROM product;

-- 计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)
FROM product;

-- 是否使用DISTINCT时的动作差异（SUM函数）
SELECT SUM(sale_price), SUM(DISTINCT sale_price)
FROM product; 

-- 按照商品种类统计数据行数
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type;

-- 不含GROUP BY
SELECT product_type, COUNT(*)
FROM product;

SELECT purchase_price, COUNT(*)
FROM product
GROUP BY purchase_price;

SELECT purchase_price, COUNT(*)
FROM product
WHERE product_type = '衣服'
GROUP BY purchase_price;

-- 数字
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
HAVING COUNT(*) = 2;

-- 错误形式（因为product_name不包含在GROUP BY聚合键中）
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
HAVING product_name = '圆珠笔';

-- 降序排列
SELECT product_id, product_name, sale_price, purchase_price
FROM product
ORDER BY sale_price DESC;
-- 多个排序键
SELECT product_id, product_name, sale_price, purchase_price
FROM product
ORDER BY sale_price, product_id;
-- 当用于排序的列名中含有NULL时，NULL会在开头或末尾进行汇总。
SELECT product_id, product_name, sale_price, purchase_price
FROM product
ORDER BY purchase_price;

SELECT product_type, SUM(purchase_price) , SUM(sale_price)
FROM product
GROUP BY product_type
HAVING 1.5*sum(purchase_price) < sum(sale_price);

SELECT *
FROM product
ORDER BY regist_date DESC,sale_price ;