SQL基础入门学习(2)

注意:
SQL语句中含有字符串的时候，需要像'abc'这样,使用单引号(')将字符串括起来，用来标识这是一个字符串。
SQL语句中含有日期的时候，同样需要使用单引号(')将其括起来，日期的格式有很多种('26 Jan 2017'或者'07/01/2017'或者'2017-07-01')

MySQL
-- DML:插入数据
START TRANSACTION;
INSERT INTO product VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
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;
Oracle DB2
DML:插入数据
INSERT INTO product VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
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;

SQL Server PostgreSQL
DML:插入数据
BEGIN TRANSACTION;
INSERT INTO product VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
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;

SELECT * FROM product;
SELECT product_id,product_name,product_type,sale_price,purchase_price,regist_date FROM product;
SELECT product_name,product_type,sale_price FROM product;
SELECT product_name AS `name`,product_name AS type,sale_price AS price FROM product;
SELECT product_name AS '商品名称',product_type AS '商品类型',sale_price AS '商品售价' FROM product;

SELECT DISTINCT product_type FROM product;
SELECT DISTINCT purchase_price FROM product;
SELECT DISTINCT product_type,regist_date FROM product;
SELECT * FROM product WHERE product_type = '衣服';
SELECT product_name,product_type FROM product WHERE product_type = '衣服';
SELECT product_name FROM product WHERE product_type = '衣服';

SELECT product_name,product_type,sale_price*2,purchase_price FROM product;
SELECT product_name,product_type,sale_price FROM product WHERE sale_price = 500;
SELECT product_name,product_type,sale_price FROM product WHERE sale_price > 500;
SELECT product_name,product_type,sale_price FROM product WHERE sale_price <> 500;
SELECT * FROM product WHERE regist_date < '2009-09-01'
SELECT product_name,product_type,sale_price,purchase_price FROM product WHERE sale_price - purchase_price > 500

CREATE TABLE chars(
chr CHAR(3) NOT NULL,
PRIMARY KEY(chr)
);
INSERT INTO chars VALUES ('1');
INSERT INTO chars VALUES ('2');
INSERT INTO chars VALUES ('3');
INSERT INTO chars VALUES ('10');
INSERT INTO chars VALUES ('11');
INSERT INTO chars VALUES ('222');
SELECT * FROM chars;
SELECT * FROM chars WHERE chr > '2';

SELECT * FROM product WHERE purchase_price = 2800;
SELECT * FROM product WHERE purchase_price <> 2800;
SELECT * FROM product WHERE purchase_price = NULL;
SELECT * FROM product WHERE purchase_price IS NULL;
SELECT * FROM product WHERE purchase_price IS NOT NULL;
SELECT product_name,product_type,purchase_price FROM product WHERE product_type = '厨房用具' AND purchase_price > 1000;
SELECT product_name,product_type,purchase_price FROM product WHERE product_type = '厨房用具' OR purchase_price > 3000;
SELECT * FROM product WHERE product_type = '办公用品' AND (regist_date = '2009-09-11' OR regist_date = '2009-09-20');
SELECT product_name,product_type,regist_date FROM product WHERE regist_date > '2009-04-28';

SELECT * FROM product WHERE product_name = NULL;
SELECT * FROM product WHERE product_name <> NULL;
SELECT * FROM product WHERE regist_date > NULL;

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

/*
注意:COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包括NULL的数据行数，
而COUNT(<列名>)会得到NULL之外的数据行数。
*/
SELECT COUNT(*) FROM product;
SELECT COUNT(*),COUNT(purchase_price) FROM product;
/*
聚合函数(COUNT、SUM、AVG、MIN、MAX)会将NULL排除在外，但COUNT(*)列外，并不会排除NULL。
*/
SELECT SUM(sale_price),SUM(purchase_price) FROM product;
SELECT AVG(sale_price),AVG(purchase_price) FROM product;
/*
MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。
*/
SELECT MIN(regist_date),MAX(regist_date) FROM product;
SELECT MIN(sale_price),MAX(purchase_price) FROM product;

SELECT COUNT(DISTINCT product_type) FROM product;
/*
在聚合函数的参数中使用DISTINCT，可以删除重复数据。
SUM(DISTINCT sale_price)是先去除掉销售价格的重复数据，再计算总和。
*/
SELECT SUM(sale_price),SUM(DISTINCT sale_price) FROM product;