17 子查询的种类
-- 17 子查询的种类 包含: 标量,行,表子查询 列子查询在19中 -- exists查询 -- 题目: 查询deck表中的全部数据 条件是如果deck和deck2两表的id相等 -- 1 方法1 使用where子查询 SELECT * FROM deck a WHERE a.id IN (SELECT b.id FROM deck2 b); -- 2 方法2 使用exists查询 SELECT * FROM deck a WHERE EXISTS (SELECT 1 FROM deck2 b WHERE b.id = b.id); -- where子查询 -- 3 找出deck表atk最大值, 且要是id最大的 结果为id=5 卡通混沌战士 atk=3000 SELECT * FROM deck WHERE atk=(SELECT MAX(atk) FROM deck) ORDER BY id DESC LIMIT 0,1; -- 4 找出atk为2500中 def最小的值 结果id为7 卡通恶魔 def 1200 -- 标记: min函数 查询def的最小值 作为条件 给外层 SELECT * FROM deck WHERE def = (SELECT MIN(def) FROM deck WHERE atk=2500 ); -- from子查询 -- 5 将子查询作为表去查询 标记: 外层的排序会覆盖内层的排序,内层的顺序会失效 SELECT * FROM (SELECT * FROM deck ORDER BY def DESC) AS a ORDER BY a.atk ASC; -- 6 四表连接 要求: 内连接 四表连接 检索列分别为a.b.c的c_name SELECT a.c_name,b.c_name,c.c_name,d.c_name FROM( (deck a INNER JOIN deck2 b ON a.id=b.id) INNER JOIN deck3 c ON a.id=c.id ) INNER JOIN deck4 d ON a.id=d.id; -- 7 要求: 外层查atk列 用in 子查询: 检索列def 条件id=5 标记: 5为混沌战士 SELECT * FROM deck WHERE atk IN ( SELECT def FROM deck WHERE id=5 ); -- 8 要求: 外层检列随意 不去重 子查询: deck中地属性条数, 作新列给外层 SELECT c_name,race,atk, (SELECT COUNT(attribute) FROM deck WHERE attribute=‘地‘) AS subColumn FROM deck;
18 列子查询
-- 子查询的关键字 all 和 any 标记: 也就是列子查询 -- 1 3中的记录行,大于2中最大值 要求: 使用>all 标记: 3200,2900 由于2800相等不算 SELECT * FROM deck3 WHERE atk >ALL (SELECT atk FROM deck2) -- 2 1表最低是1300 2表中小于这个值有3条 2表中的自然南瓜1400 正好要比1300大 SELECT * FROM deck2 WHERE atk<ALL (SELECT atk FROM deck) -- 3 要求: 使用any 2中大于1表的任一数值 结果7条 都比1300大 标记: 别名some SELECT * FROM deck2 WHERE atk >ANY (SELECT atk FROM deck) -- 4 对比 =any和in 效果一样 SELECT * FROM deck2 WHERE atk =ANY (SELECT atk FROM deck) -- 使用in SELECT * FROM deck2 WHERE atk IN (SELECT atk FROM deck) -- 5 对比 <>all和not in SELECT * FROM deck2 WHERE atk <>ALL (SELECT atk FROM deck) -- 使用notin SELECT * FROM deck2 WHERE atk NOT IN (SELECT atk FROM deck)
19 视图的单表查询
-- ⑥ 视图 -- 如果视图存在就删除 DROP VIEW IF EXISTS v1 -- 1 要求: deck表 atk表 查前三 视图中列要设属于视图的列名v_原列名 CREATE VIEW v1 (v_name,v_atk,vdef) AS SELECT c_name,atk,def FROM deck ORDER BY atk DESC LIMIT 0,3 -- 2 计算字段 要求: atk和def相加 指定属于视图的列的名称 v_原列名 CREATE VIEW v2 (v_name,v_atkdefSum) AS SELECT c_name,atk+def FROM deck -- 3 格式化处理数 要求: 使用concat和trim 原表检索字段设别名concat_title CREATE VIEW v3 AS SELECT CONCAT(TRIM(id) , ‘(‘ , TRIM(c_name) , ‘)‘ ) AS concat_title FROM deck -- 4 要求: 日期加时间 日期 时间 标记: now curdate curtime CREATE VIEW v4 (v_currentTime,v_curdate,v_curtime) AS SELECT NOW(),CURDATE(),CURTIME() AS current; -- 5 正则表达式 and 模糊查询 要求:名称中卡通开头,后4个字符 atk值是3000-4000 CREATE VIEW v5 AS SELECT * FROM deck WHERE c_name LIKE ‘卡通____‘ AND atk REGEXP ‘3[0-9]00‘ -- 6 and和or优先级 将必要条件 和 任一 合并 要求: 只用and和or 结果id为1,2,3,4 -- 必要条件地属性 排除6个 必要条件atk>2700 四个中再排除两个 机械族再排除一个 -- 有特点的也可以选出来 名称中有自然字样的 种族是魔法师族的 CREATE VIEW v6 AS SELECT * FROM deck WHERE (attribute=‘地‘ AND atk>2700 AND race=‘机械族‘) OR c_name LIKE ‘%自然%‘ OR race=‘魔法师族‘ -- 7 原表分组汇总 要求: 分组 降序 汇总 检索列atk,count,sum 不指定视图列名 CREATE VIEW v7 AS SELECT atk,COUNT(atk) AS atkCount,SUM(atk) AS atkSum FROM deck GROUP BY atk DESC WITH ROLLUP -- 8 使用视图保护原表数据 要求: 将c_name列名改成v_name 标记: 提高安全性 CREATE VIEW v8 (id,v_name,attribute,race,atk,def) AS SELECT * FROM deck -- 9 视图的视图 在v1的基础上再新建一个视图 标记: 视图嵌套 CREATE VIEW v9 (v1_name,v1_atk,v1_def)AS SELECT * FROM v1 LIMIT 0,2 -- 10 排序 查询视图时,如果指定顺序,那么这个顺序会覆盖"原视图中的排序"顺序 CREATE VIEW v10 AS SELECT * FROM deck ORDER BY atk DESC SELECT * FROM v10 ORDER BY atk ASC
20 操作视图
-- 19 操作视图 -- 11 查询视图数据 要求: 查v11前6的名称和攻击力 结果为3800到2900 SELECT c_name,atk FROM v11 ORDER BY atk DESC LIMIT 0,6 -- 要求: v8 列v_name属于视图, c_name是原表 标记: 检索的列名是视图的 SELECT v_name FROM v8 -- 标记: 列名为原表中的列名 "查看数据"只显示列名,不显示列类型 SELECT * FROM v8 -- 12 修改视图数据 标记: 对视图操作,会影响到原表(基本表) -- 创建视图v12 内容是deck2表 CREATE VIEW v12 AS SELECT * FROM deck2 -- 修改视图本身数据 要求: v12 id为2的列 atk值改成2700 UPDATE v12 SET atk=2700 WHERE id=2 -- 就是修改原表中的数据, 视图会自动更新 SELECT * FROM v12 SELECT * FROM deck2 -- 删除行 DELETE FROM v12 WHERE id=2 -- 添加行 INSERT INTO v12 (id,c_name,attribute,race,atk,def) VALUES (2, ‘自然兽‘, ‘地‘, ‘兽族‘, 2200, 1700); -- 13 修改视图本身 要求: v12 deck2改成deck3 不是修改视图内容 ALTER VIEW v12 AS SELECT * FROM deck3 -- 删除视图 DROP VIEW v1; DROP TABLE v1; -- 14 查询信息: 视图列表, 建视图语句, 结构 -- 方法1 查看数据表列表 标记: 视图也在普通表的列表中 SHOW TABLES; -- 方法2 查看视图列表 视图名模糊查询 SHOW TABLES LIKE ‘%v%‘; -- 方法3 不切换数据库 列表在information_schema SELECT * FROM information_schema.VIEWS -- 查看建视图的语句 标记: 可看到编码格式 SHOW CREATE VIEW v1 SHOW CREATE TABLE v12 -- 查看视图的列结构 要求: 视图v1 标记: "查看结构"是不显示数据的 DESC v12
21 视图的多表查询
-- 19 视图的多表查询 -- 15 4表合并 要求: 合并 不去重 CREATE VIEW v15 AS SELECT * FROM deck UNION ALL SELECT * FROM deck2 UNION ALL SELECT * FROM deck3 UNION ALL SELECT * FROM deck4 -- 16 要求: where连接两表 检索id和名称 视图列不指定名称,检索列重复时要指定 表别名 CREATE VIEW v16 AS SELECT a.id,a.c_name,b.attribute FROM deck a, deck b WHERE a.id=b.id -- 17 要求: 两表内连接 属性关联 检索id,名称,右表任意列 重名时给视图列别名 CREATE VIEW v17 AS SELECT a.id,a.c_name,a.attribute,b.deckRace,b.effect FROM deck a INNER JOIN deck5 b ON a.attribute=b.attribute -- 18 属于mysql的外连接方式 左连接+union+右连接 CREATE VIEW v18 (id,c_name,attribute,race,atk,def,r_id,r_attribute,r_deckRace,r_effect) AS SELECT * FROM deck a LEFT JOIN deck5 b ON a.attribute=b.attribute UNION SELECT * FROM deck a RIGHT JOIN deck5 b ON a.attribute=b.attribute -- 去除不想要查询的列 CREATE VIEW v18_2 (id,c_name,attribute,race,atk,def,r_deckRace,r_effect) AS SELECT a.id,a.c_name,a.attribute,a.race,a.atk,a.def,b.deckRace,b.effect FROM deck a LEFT JOIN deck5 b ON a.attribute=b.attribute UNION SELECT a.id,a.c_name,a.attribute,a.race,a.atk,a.def,b.deckRace,b.effect FROM deck a RIGHT JOIN deck5 b ON a.attribute=b.attribute -- 19 并集去交集 CREATE VIEW v19 (id,c_name,attribute,race,atk,def,r_deckRace,r_effect) AS SELECT a.id,a.c_name,a.attribute,a.race,a.atk,a.def,b.deckRace,b.effect FROM deck a LEFT JOIN deck5 b ON a.attribute=b.attribute WHERE b.attribute IS NULL UNION SELECT a.id,a.c_name,a.attribute,a.race,a.atk,a.def,b.deckRace,b.effect FROM deck a RIGHT JOIN deck5 b ON a.attribute=b.attribute WHERE a.attribute IS NULL