1 -- 1 存储过程   语句: 1表存在则删除, 2创建表, 3批量添加数据
 2 DELIMITER //
 3 CREATE PROCEDURE deckPro()
 4   BEGIN
 5   
 6   DROP TABLE IF EXISTS deck;
 7   
 8   CREATE TABLE deck (
 9   id INT(50) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT ‘主键,自增‘,
10   c_name VARCHAR(50) COMMENT ‘名称‘,
11   attribute CHAR(50) COMMENT ‘属性‘,
12   race CHAR(50) COMMENT ‘种族‘,
13   atk INT(50) DEFAULT 0 COMMENT ‘攻击力‘,
14   def INT(50) DEFAULT 0 COMMENT ‘防御力‘
15   )ENGINE=INNODB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
16   
17   INSERT INTO deck (id,c_name,attribute,race,atk,def) VALUES
18   (1,‘自然木龙兽‘,‘地‘,‘兽族‘,2800,2400),
19   (2,‘自然木鳞龙‘,‘地‘,‘龙族‘,2500,1800),
20   (3,‘自然兽‘,‘地‘,‘兽族‘,2200,1700),
21   (4,‘自然竹笋‘,‘地‘,‘植物族‘,2000,2000),
22   (5,‘自然蜻蜓‘,‘地‘,‘昆虫族‘,1200,400);
23  
24 END //
25 DELIMITER;
26   
27 -- 个人习惯  存储过程后缀是Pro,前缀是表名
28 CALL deckPro;
29 
30 
31 -- 存储过程2
32 DELIMITER //
33 CREATE PROCEDURE magicPro()
34   BEGIN
35    
36   DROP TABLE IF EXISTS magic;
37   
38   CREATE TABLE magic (
39   id INT(50) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT ‘主键,自增‘,
40   c_name VARCHAR(50) COMMENT ‘名称‘,
41   attribute CHAR(50) COMMENT ‘属性‘,
42   race CHAR(50) COMMENT ‘种族‘,
43   atk INT(50) DEFAULT 0 COMMENT ‘攻击力‘,
44   def INT(50) DEFAULT 0 COMMENT ‘防御力‘
45   )ENGINE=INNODB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
46 
47 INSERT INTO magic (id,c_name,attribute,race,atk,def) VALUES
48   (11,‘黑魔术师‘,‘暗‘,‘魔法师族‘,2500,2100),
49   (12,‘黑魔导骑士‘,‘暗‘,‘魔法师族‘,2500,2100),
50   (13,‘黑魔导少女‘,‘暗‘,‘魔法师族‘,2000,1700),
51   (14,‘黑魔导师 库兰‘,‘暗‘,‘魔法师族‘,1200,0),
52   (15,‘黑魔法神官‘,‘暗‘,‘魔法师族‘,3200,2800);
53   
54 END //
55 DELIMITER;
56 
57 CALL magicPro;
58 
59 -- 存储过程3
60 DELIMITER //
61 CREATE PROCEDURE magic2Pro()
62   BEGIN
63    
64   DROP TABLE IF EXISTS magic2;
65   
66   CREATE TABLE magic2 (
67   id INT(50) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT ‘主键,自增‘,
68   c_name VARCHAR(50) COMMENT ‘名称‘,
69   attribute CHAR(50) COMMENT ‘属性‘,
70   race CHAR(50) COMMENT ‘种族‘,
71   atk INT(50) DEFAULT 0 COMMENT ‘攻击力‘,
72   def INT(50) DEFAULT 0 COMMENT ‘防御力‘
73   )ENGINE=INNODB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
74 
75 INSERT INTO magic2 (id,c_name,attribute,race,atk,def) VALUES
76   (21,‘神圣魔导王‘,‘暗‘,‘魔法师族‘,2700,1700),
77   (22,‘魔力引导者‘,‘暗‘,‘魔法师族‘,1700,1400),
78   (23,‘魔导骑士防御者‘,‘暗‘,‘魔法师族‘,1600,2000),
79   (24,‘见习魔术师‘,‘暗‘,‘魔法师族‘,400,800),
80   (25,‘王立魔法图书馆‘,‘暗‘,‘魔法师族‘,0,2000);
81   
82 END //
83 DELIMITER;
84 
85 CALL magic2Pro;  

 1 -- 查询全部
 2 SELECT * FROM deck
 3 -- 只查询某列
 4 SELECT c_name FROM deck
 5 
 6 -- 多条件查询
 7 SELECT * FROM deck WHERE attribute="地" AND race=‘兽族‘
 8 -- 任一条件查询  攻击力大于2000, 或者防御力小于1000的 均满足
 9 SELECT * FROM deck WHERE atk>2000 OR def < 1000
10 -- having可单独使用  和where效果类似
11 SELECT * FROM deck HAVING atk>2000
12 -- and也可连接
13 SELECT * FROM deck HAVING atk>2000 AND def>2000
14 
15 
16 -- 模糊查询 名称中带有‘兽‘字的卡片
17 SELECT * FROM deck WHERE c_name LIKE ‘%兽%‘
18 -- 原本2条, 但因为自然兽atk不满足另一条件(and) 所以也不符合条件
19 SELECT * FROM deck WHERE c_name LIKE ‘%兽%‘ AND atk>2500
20 
21 -- 降序  竹笋的防御力较高
22 SELECT * FROM deck ORDER BY def DESC
23 -- 升序 也可省略asc
24 SELECT * FROM deck ORDER BY def ASC

 1 -- 表中记录条数
 2 SELECT COUNT(*) FROM magic
 3 
 4 -- 攻击力重复的条数, 由于没有重复所以目前效果不明显
 5 SELECT atk,COUNT(atk) FROM magic GROUP BY atk WITH ROLLUP
 6 
 7 -- 将攻击力汇总,计算出总值
 8 SELECT atk,SUM(atk) FROM magic GROUP BY atk WITH ROLLUP
 9 
10 -- 将攻击力原值,条数,合计值  查询
11 SELECT atk,COUNT(atk),SUM(atk) FROM magic GROUP BY atk WITH ROLLUP

 1 -- 两表查询
 2 SELECT * FROM deck 
 3 UNION ALL
 4 SELECT * FROM magic
 5 ORDER BY atk DESC LIMIT 0,5
 6 
 7 -- 三表查询 降序 前5
 8 SELECT * FROM deck 
 9 UNION ALL
10 SELECT * FROM magic
11 UNION ALL
12 SELECT * FROM magic2
13 ORDER BY atk DESC LIMIT 0,5
14 
15 -- 三表查询, 按攻击降序, 前5, 且防御力不少于2000
16 -- 由于不符合条件的记录,被除去, 原本第6,7位的排名提高. 没有时,则有几条显示几条
17 SELECT * FROM deck WHERE def > 2000
18 UNION ALL
19 SELECT * FROM magic WHERE def > 2000
20 UNION ALL
21 SELECT * FROM magic2 WHERE def > 2000
22 ORDER BY atk DESC LIMIT 0,5

 1 -- 左连接  deck和deck_attribute表中 attribute字段值相等时才连接
 2 SELECT 
 3 a.c_name,a.attribute,a.atk,
 4 b.deckType,b.effect
 5 FROM 
 6 deck a LEFT JOIN deck_attribute b ON
 7 a.attribute = b.attribute
 8 
 9 -- 左连接2  
10 SELECT 
11 a.c_name,a.attribute,a.atk,
12 b.deckType,b.effect
13 FROM 
14 magic a INNER JOIN deck_attribute b ON
15 a.attribute = b.attribute
16 
17 
18 -- where查询多表  区别: 不能查询相等的列,因不明确,需指明
19 SELECT 
20 c_name, atk,deckType,effect
21 FROM deck,deck_attribute
22 WHERE deck.attribute = deck_attribute.attribute
23 
24 -- 解决方法  指出不明确的列,表名.目标列
25 SELECT 
26 c_name, deck.attribute,atk,deckType,effect
27 FROM deck,deck_attribute
28 WHERE deck.attribute = deck_attribute.attribute