标准SQL语句综合

-- 创建表
CREATE TABLE brand (id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键 自增
					factory VARCHAR(255) NOT NULL,  -- 非空
					-- factory VARCHAR(255) REFERENCES factory(name),  
                    name VARCHAR(255) UNIQUE,   -- 唯一约束
                    price INT CHECK(price>=0),  -- 检查约束
                    sum INT NOT NULL DEFAULT 0, -- 非空，默认为0
                    sell INT NOT NULL,  -- 非空
                    last INT，
                    constraint factory_factoryname foreign key(factory) references factory(name) on delete cascade on update cascade  -- 外键约束
);

-- 删除brand表
DROP TABLE brand;


-- 插入数据
INSERT INTO brand(factory,name,price,sum, sell, last) VALUES('一汽大众', '奥迪A6', 36, 50, 8, 42);
INSERT INTO brand(factory,name,price,sum, sell, last) VALUES('一汽大众', '捷达',   10, 80, 4, 76);
INSERT INTO brand(factory,name,price,sum, sell, last) VALUES('一汽大众', '奔驰',   83, 40, 6, 34);
INSERT INTO brand(factory,name,price,sum, sell, last) VALUES('二汽神龙', '毕加索', 39, 50, 6, 44);
INSERT INTO brand(factory,name,price,sum, sell, last) VALUES('二汽神龙', '富康',   28, 60, 5, 55);
INSERT INTO brand(factory,name,price,sum, sell, last) VALUES('二汽神龙', '标致307',27, 70, 4, 66);
INSERT INTO brand(factory,name,price,sum, sell, last) VALUES('上海大众', '桑塔纳', 25, 75, 2, 73);
INSERT INTO brand(factory,name,price,sum, sell, last) VALUES('上海大众', '帕萨特', 27, 65, 10, 55);


-- 从其他表插入表
INSERT INTO brand_copy(factory,name,price,sum, sell, last) SELECT factory,name,price,sum, sell, last FROM brand;

-- 更新数据
UPDATE brand SET factory = '广州本田' WHERE id = 3;  -- 没有 WHERE factory 列全改

-- 删除数据
DELETE FROM brand WHERE id = 3; -- 没有 WHERE 全删

-- 更新表
ALTER TABLE brand ADD cost INT; -- 增加列
  -- 其他操作参考各DB手册

-- 删除表
DROP TABLE brand;



-- 基本查询语句
SELECT factory FROM brand;  -- 选择一列
SELECT factory ,name FROM brand;  -- 选择多列
SELECT * FROM brand;  -- 选择所有列

-- 查询排序
SELECT name FROM brand ORDER BY price;  -- 选择一列并安照某一列排序
SELECT * FROM brand ORDER BY factory, sell;  -- 选择某列并按照多列排序，按一列排好的基础上按另一列再排，然后再按另一列再排...
SELECT * FROM brand ORDER BY factory DESC, sell;  -- 加上DESC表示倒序，DESC直接作用于前面（仅）一个列名

-- 过滤数据
SELECT * FROM brand WHERE price < 50 AND (name <> '奔驰' OR sell BETWEEN 10 AND 50) AND last is NOT NULL;  
															-- 具体数据库支持逻辑运算符可能不同,括号优先运算
SELECT * FROM brand WHERE factory IN ('一汽大众', '上海大众') ORDER BY price;  -- ORDER BY 须放在后面

-- 通配符过滤数据
SELECT * FROM brand WHERE factory LIKE '一汽%';  -- %号匹配多字符， _号匹配单个字符
SELECT * FROM brand WHERE name LIKE '%[^Aq]';  -- []匹配括号中的一个字符，^即匹配不是括号中任一字符 (中文貌似无效)

-- 创建计算字段
SELECT RTRIM(factory) + '(' + TRIM(name) + ')' AS car_name FROM brand ORDER BY price;  -- 拼接字符串(MySQL不可用) (R/L)TRIM()函数去掉空格
SELECT CONCAT(factory , '(' , name , ')') AS car_name FROM brand ORDER BY price;  -- MySQl拼接字符串, 注意AS的位置!
SELECT price, last, price * last on_sale_value FROM brand WHERE sell > 10 ORDER BY price;  -- 算术运算
		-- price * last on_sale_value 即 price * last AS on_sale_value；（oracle不支持AS， 不过其他DB支持不用AS）

-- 使用函数
-- 文本处理函数 UPPER(), LOWER(), LENGTH(), (L/R)TRIM(), LEFT(), RIGHT()等
-- 日期处理函数 不同DB不同函数，使用百度
-- 数值处理函数 ABS(), SIN(), PI(), SQRT(), EXP()等

-- 汇总数据
-- 聚集函数 : AVG(), COUNT(), MIN(), MAX(), SUM()； 均为高效设计函数，不会影响程序效率
SELECT AVG(DISTINCT price) AS avg_price, AVG(sell) AS sell_price FROM brand;
			-- AVG()求平均值 ,只能求单列，忽略NULL，DISTINCT除去重复数据
SELECT COUNT(DISTINCT factory) AS fac, COUNT(name) AS nam, COUNT(*) AS cot FROM brand;
			-- COUNT()求行数，*计所有行包括NULL，指定列名忽略NULL，加DISTINCT去掉重复行， DISTINCT不能修饰*
-- MIN(), MAX(), SUM() 均忽略NULL;  DISTINCT 对应 ALL，ALL是默认参数， DISTINCT后必须且仅能跟列名！

-- 分组数据		-- 注意顺序：SELECT->FROM->WHERE->GROUP BY->HAVING->ORDER BY
-- HAVING 可以替代 WHERE，但是 WHERE 不能替代 HAVING
SELECT factory, COUNT(*), name FROM brand WHERE price>10 GROUP BY factory HAVING COUNT(*)>0 ORDER BY COUNT(*);
				-- SELECT语句中出现的每一列(除聚集函数外)必须在GROUP语句中出现；
                -- SELECT语句中使用表达式，必须在GROUP语句中使用相同的表达式


-- ----------------- 多表查询 ---------- （示例使用 原表 或 派生表 做多表演示）
-- 子查询
SELECT name, price FROM brand WHERE price<50 AND id IN (SELECT id FROM brand WHERE price>20);

-- 作为计算字段的子查询
SELECT factory, (SELECT COUNT(*) FROM brand WHERE price<50) FROM brand WHERE price>10;
				-- 子查询作为一个字段时，子查询SELECT只能查询一列，且只能返回一行（即不能用GROUP）

-- 联结表
-- ANSI SQL规范首选语法:INNER JOIN
SELECT t1.factory, COUNT(*) FROM 
		brand t1 INNER JOIN (SELECT factory FROM brand WHERE price>80) t2 ON t1.factory = t2.factory  -- (内联结)
        WHERE t1.price<20 GROUP BY t1.factory;  -- 在售价有超过80的品牌中，找出售价低于20的车的个数
    -- brand t1 即 brand AS t1；给表 brand 取别名 t1 
	-- (SELECT factory FROM brand WHERE price>80) t2 即 (SELECT factory FROM brand WHERE price>80) AS t2；给派生表 (...) 取别名 t2
    
-- 也可以这么写：
SELECT t1.factory, COUNT(*) FROM
		brand t1, (SELECT factory FROM brand WHERE price>80) t2 WHERE t1.factory = t2.factory -- (等值联结)
        AND t1.price<20 GROUP BY t1.factory;  -- 在售价有超过80的品牌中，找出售价低于20的车的个数
	-- 内联结 和 等值联结是同一回事， ANSI SQL规范首选 内联结

-- 也可以这么写：
SELECT t1.factory, COUNT(*) FROM
		brand t1, brand t2 WHERE t1.factory = t2.factory -- (自联结)
        AND t2.price>80 AND t1.price<20 GROUP BY t1.factory;
 
 
-- 外部联结，使查找结果包含没有关联的行；LEFT为OUTER JOIN左边表中的行，会从左表返回所有的行，即使右边表没有匹配的行；RIGHT为右边；FULL为全部(FULL MySQL等不支持)
SELECT t1.factory FROM 
		brand t1 LEFT OUTER JOIN (SELECT factory FROM brand WHERE price>80) t2 ON t1.factory = t2.factory  -- (外部联结)
        GROUP BY t1.factory;  -- LEFT 与 RIGHT 的结果将不一样
        
-- 也可以这么写：
SELECT t1.factory FROM
		brand t1, (SELECT factory FROM brand WHERE price>80) t2 WHERE t1.factory *= t2.factory -- (等值联结)
        GROUP BY t1.factory; 
        -- *=为LEFT， =*为RIGHT； MySQL实测这种语法不可用
        

-- 组合查询
SELECT factory, name FROM brand WHERE price<30
UNION
SELECT factory, name FROM brand WHERE price>50
UNION
SELECT factory, name FROM brand WHERE sell>50
ORDER BY factory;  -- UNION 使用 ORDER BY，必须在最后 而且 ORDER BY 后列名必须在 SELECT 子句中出现
		-- 每条SELECT语句中的列名必须一致； UNION 会除去重复结果，使用 ALL UNION 则会保留重复结果
        
        
-- 使用视图  CREATE VIEW view_name AS SELECT... , AS不能省略
CREATE VIEW brand_view AS SELECT t1.factory FROM 
		brand t1 INNER JOIN (SELECT factory FROM brand WHERE price>80) t2 ON t1.factory = t2.factory;

CREATE VIEW brand_view AS SELECT price, last, price * last on_sale_value FROM brand WHERE sell > 10 ORDER BY price;

DROP VIEW brand_view;

SELECT * FROM brand_view;


-- 储存过程  这个一般用不着

-- 事务处理  参考各DB文档

-- 游标  

-- 约束  创建表时添加，或如下：
ALTER TABLE brand ADD CONSTRAINT PRIMARY KEY (id);  -- 增加主键约束
ALTER TABLE brand ADD CONSTRAINT CHECK (sell>= 0);  -- 增加检查约束
ALTER TABLE brand ADD CONSTRAINT FOREIGN KEY (factory) REFERENCES factory (name);  -- 增加外键约束

-- 索引
CREATE INDEX id_index ON brand (id);  -- 在 id 列上创建索引，也可以在多列上创建，如：
									  -- CREATE INDEX id_index ON brand (id, name);
-- 触发器  参考各DB文档