01.
02.mysql复习
03.一:复习前的准备
04.1:确认你已安装wamp
05.2:确认你已安装ecshop,并且ecshop的数据库名为shop
06.
07.二 基础知识:
08.1.数据库的连接
09.mysql -u -p -h
10.-u 用户名
11.-p 密码
12.-h host主机
13.
14.2:库级知识
15.2.1 显示数据库: show databases;
16.2.2 选择数据库: use dbname;
17.2.3 创建数据库: create database dbname charset utf8;
18.2.3 删除数据库: drop database dbname;
19.
20.3: 表级操作:
21.3.1 显示库下面的表
22.show tables;
23.
24.3.2 查看表的结构:
25.desc tableName;
26.
27.3.3 查看表的创建过程:
28.show create table tableName;
29.
30.3.4 创建表:
31. create table tbName (
32.列名称1 列类型 [列参数] [not null default ],
33.....列2...
34.....
35.列名称N 列类型 [列参数] [not null default ]
36.)engine myisam/innodb charset utf8/gbk
37.
38.
39.3.4的例子:
40.create table user (
41. id int auto_increment,
42. name varchar(20) not null default '',
43. age tinyint unsigned not null default 0,
44. index id (id)
45. )engine=innodb charset=utf8;
46.注:innodb是表引擎,也可以是myisam或其他,但最常用的是myisam和innodb,
47.charset 常用的有utf8,gbk;
48.
49.
50.3.5 修改表
51.3.5.1 修改表之增加列:
52.alter table tbName
53.add 列名称1 列类型 [列参数] [not null default ] #(add之后的旧列名之后的语法和创建表时的列声明一样)
54.
55.3.5.2 修改表之修改列
56.alter table tbName
57.change 旧列名 新列名 列类型 [列参数] [not null default ]
58.(注:旧列名之后的语法和创建表时的列声明一样)
59.
60.3.5.3 修改表之减少列:
61.alter table tbName
62.drop 列名称;
63.
64.
65.3.5.4 修改表之增加主键
66.alter table tbName add primary key(主键所在列名);
67.例:alter table goods add primary key(id)
68.该例是把主键建立在id列上
69.
70.3.5.5 修改表之删除主键
71.alter table tbName drop primary key;
72.
73.3.5.6 修改表之增加索引
74.alter table tbName add [unique|fulltext] index 索引名(列名);
75.
76.3.5.7 修改表之删除索引
77.alter table tbName drop index 索引名;
78.
79.3.5.8 清空表的数据
80.truncate tableName;
81.
82.4:列类型讲解
83.列类型:
84. 整型:tinyint (0~255/-128~127) smallint (0~65535/-32768~32767) mediumint int bigint (参考手册11.2)
85. 参数解释:
86. unsigned 无符号(不能为负) zerofill 0填充 M 填充后的宽度
87. 举例:tinyint unsigned;
88. tinyint(6) zerofill;
89.数值型
90. 浮点型:float double
91. 格式:float(M,D) unsigned\zerofill;
92.
93.
94.字符型
95. char(m) 定长
96. varchar(m)变长
97. text
98.
99.列 实存字符i 实占空间 利用率
100.
101.char(M) 0<=i<=M M i/m<=100%
102.
103.varchar(M) 0<=i<=M i+1,2 i/i+1/2<100%
104.
105.
106. year YYYY 范围:1901~2155. 可输入值2位和4位(如98,2012)
107.日期时间类型 date YYYY-MM-DD 如:2010-03-14
108. time HH:MM:SS 如:19:26:32
109. datetime YYYY-MM-DD HH:MM:SS 如:2010-03-14 19:26:32
110. timestamp YYYY-MM-DD HH:MM:SS 特性:不用赋值,该列会为自己赋当前的具体时间
111.
112.
113.
114.5:增删改查基本操作
115.
116.5.1 插入数据
117. insert into 表名(col1,col2,……) values(val1,val2……); -- 插入指定列
118. insert into 表名 values (,,,,); -- 插入所有列
119. insert into 表名 values -- 一次插入多行
120. (val1,val2……),
121. (val1,val2……),
122. (val1,val2……);
123.
124.
125.5.3修改数据
126. update tablename
127. set
128. col1=newval1,
129. col2=newval2,
130. ...
131. ...
132. colN=newvalN
133. where 条件;
134.
135.5.4,删除数据 delete from tablenaeme where 条件;
136.
137.5.5, select 查询
138.
139. (1) 条件查询 where a. 条件表达式的意义,表达式为真,则该行取出
140. b. 比较运算符 = ,!=,< > <= >=
141. c. like , not like ('%'匹配任意多个字符,'_'匹配任意单个字符)
142. in , not in , between and
143. d. is null , is not null
144. (2) 分组 group by
145. 一般要配合5个聚合函数使用:max,min,sum,avg,count
146. (3) 筛选 having
147. (4) 排序 order by
148. (5) 限制 limit
149.
150.
151.
152.6: 连接查询
153.
154.6.1, 左连接
155. .. left join .. on
156. table A left join table B on tableA.col1 = tableB.col2 ;
157. 例句:
158. select 列名 from table A left join table B on tableA.col1 = tableB.col2
159.2. 右链接: right join
160.3. 内连接: inner join
161.
162.左右连接都是以在左边的表的数据为准,沿着左表查右表.
163.内连接是以两张表都有的共同部分数据为准,也就是左右连接的数据之交集.
164.
165.7 子查询
166. where 型子查询:内层sql的返回值在where后作为条件表达式的一部分
167. 例句: select * from tableA where colA = (select colB from tableB where ...);
168.
169. from 型子查询:内层sql查询结果,作为一张表,供外层的sql语句再次查询
170. 例句:select * from (select * from ...) as tableName where ....
171.
172.
173.8: 字符集
174. 客服端sql编码 character_set_client
175. 服务器转化后的sql编码 character_set_connection
176. 服务器返回给客户端的结果集编码 character_set_results
177. 快速把以上3个变量设为相同值: set names 字符集
178.
179. 存储引擎 engine=1\2
180. 1 Myisam 速度快 不支持事务 回滚
181. 2 Innodb 速度慢 支持事务,回滚
182.
183. ①开启事务 start transaction
184. ②运行sql;
185. ③提交,同时生效\回滚 commit\rollback
186.
187. 触发器 trigger
188. 监视地点:表
189. 监视行为:增 删 改
190. 触发时间:after\before
191. 触发事件:增 删 改
192.
193.
194. 创建触发器语法
195. create trigger tgName
196. after/before insert/delete/update
197. on tableName
198. for each row
199. sql; -- 触发语句
200.
201. 删除触发器:drop trigger tgName;
202.
203.
204. 索引
205. 提高查询速度,但是降低了增删改的速度,所以使用索引时,要综合考虑.
206. 索引不是越多越好,一般我们在常出现于条件表达式中的列加索引.
207. 值越分散的列,索引的效果越好
208.
209. 索引类型
210. primary key主键索引
211. index 普通索引
212. unique index 唯一性索引
213. fulltext index 全文索引
214.
215.
216.综合练习:
217.连接上数据库服务器
218.创建一个gbk编码的数据库
219.建立商品表和栏目表,字段如下:
220.
221.商品表:goods
222.goods_id --主键,
223.goods_name -- 商品名称
224.cat_id -- 栏目id
225.brand_id -- 品牌id
226.goods_sn -- 货号
227.goods_number -- 库存量
228.shop_price -- 价格
229.goods_desc --商品详细描述
230.
231.栏目表:category
232.cat_id --主键
233.cat_name -- 栏目名称
234.parent_id -- 栏目的父id
235.
236.
237.
238.建表完成后,作以下操作:
239.删除goods表的goods_desc 字段,及货号字段
240.并增加字段:click_count -- 点击量
241.
242.在goods_name列上加唯一性索引
243.在shop_price列上加普通索引
244.在clcik_count列上加普通索引
245.删除click_count列上的索引
246.
247.
248.对goods表插入以下数据:
249.+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+
250.| goods_id | goods_name | cat_id | brand_id | goods_sn | goods_number | shop_price | click_count |
251.+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+
252.| 1 | KD876 | 4 | 8 | ECS000000 | 10 | 1388.00 | 7 |
253.| 4 | 诺基亚N85原装充电器 | 8 | 1 | ECS000004 | 17 | 58.00 | 0 |
254.| 3 | 诺基亚原装5800耳机 | 8 | 1 | ECS000002 | 24 | 68.00 | 3 |
255.| 5 | 索爱原装M2卡读卡器 | 11 | 7 | ECS000005 | 8 | 20.00 | 3 |
256.| 6 | 胜创KINGMAX内存卡 | 11 | 0 | ECS000006 | 15 | 42.00 | 0 |
257.| 7 | 诺基亚N85原装立体声耳机HS-82 | 8 | 1 | ECS000007 | 20 | 100.00 | 0 |
258.| 8 | 飞利浦9@9v | 3 | 4 | ECS000008 | 17 | 399.00 | 9 |
259.| 9 | 诺基亚E66 | 3 | 1 | ECS000009 | 13 | 2298.00 | 20 |
260.| 10 | 索爱C702c | 3 | 7 | ECS000010 | 7 | 1328.00 | 11 |
261.| 11 | 索爱C702c | 3 | 7 | ECS000011 | 1 | 1300.00 | 0 |
262.| 12 | 摩托罗拉A810 | 3 | 2 | ECS000012 | 8 | 983.00 | 14 |
263.| 13 | 诺基亚5320 XpressMusic | 3 | 1 | ECS000013 | 8 | 1311.00 | 13 |
264.| 14 | 诺基亚5800XM | 4 | 1 | ECS000014 | 4 | 2625.00 | 6 |
265.| 15 | 摩托罗拉A810 | 3 | 2 | ECS000015 | 3 | 788.00 | 8 |
266.| 16 | 恒基伟业G101 | 2 | 11 | ECS000016 | 0 | 823.33 | 3 |
267.| 17 | 夏新N7 | 3 | 5 | ECS000017 | 1 | 2300.00 | 2 |
268.| 18 | 夏新T5 | 4 | 5 | ECS000018 | 1 | 2878.00 | 0 |
269.| 19 | 三星SGH-F258 | 3 | 6 | ECS000019 | 0 | 858.00 | 7 |
270.| 20 | 三星BC01 | 3 | 6 | ECS000020 | 13 | 280.00 | 14 |
271.| 21 | 金立 A30 | 3 | 10 | ECS000021 | 40 | 2000.00 | 4 |
272.| 22 | 多普达Touch HD | 3 | 3 | ECS000022 | 0 | 5999.00 | 15 |
273.| 23 | 诺基亚N96 | 5 | 1 | ECS000023 | 8 | 3700.00 | 17 |
274.| 24 | P806 | 3 | 9 | ECS000024 | 148 | 2000.00 | 36 |
275.| 25 | 小灵通/固话50元充值卡 | 13 | 0 | ECS000025 | 2 | 48.00 | 0 |
276.| 26 | 小灵通/固话20元充值卡 | 13 | 0 | ECS000026 | 2 | 19.00 | 0 |
277.| 27 | 联通100元充值卡 | 15 | 0 | ECS000027 | 2 | 95.00 | 0 |
278.| 28 | 联通50元充值卡 | 15 | 0 | ECS000028 | 0 | 45.00 | 0 |
279.| 29 | 移动100元充值卡 | 14 | 0 | ECS000029 | 0 | 90.00 | 0 |
280.| 30 | 移动20元充值卡 | 14 | 0 | ECS000030 | 9 | 18.00 | 1 |
281.| 31 | 摩托罗拉E8 | 3 | 2 | ECS000031 | 1 | 1337.00 | 5 |
282.| 32 | 诺基亚N85 | 3 | 1 | ECS000032 | 1 | 3010.00 | 9 |
283.+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+
284.
285.
286.
287.三 查询知识
288.注:以下查询基于ecshop网站的商品表(ecs_goods)
289.在练习时可以只取部分列,方便查看.
290.
291.1: 基础查询 where的练习:
292.
293.查出满足以下条件的商品
294.1.1:主键为32的商品
295.select goods_id,goods_name,shop_price
296. from ecs_goods
297. where goods_id=32;
298.1.2:不属第3栏目的所有商品
299.select goods_id,cat_id,goods_name,shop_price from ecs_goods
300. where cat_id!=3;
301.
302.1.3:本店价格高于3000元的商品
303.
304. select goods_id,cat_id,goods_name,shop_price from ecs_goods
305. where shop_price >3000;
306.
307.1.4:本店价格低于或等于100元的商品
308.select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price <=100;
309.
310.1.5:取出第4栏目或第11栏目的商品(不许用or)
311.select goods_id,cat_id,goods_name,shop_price from ecs_goods
312. where cat_id in (4,11);
313.
314.
315.1.6:取出100<=价格<=500的商品(不许用and)
316.select goods_id,cat_id,goods_name,shop_price from ecs_goods
317. where shop_price between 100 and 500;
318.
319.
320.1.7:取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)
321.select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id!=3 and cat_id!=11;
322.
323.select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id not in (3,11);
324.
325.
326.
327.1.8:取出价格大于100且小于300,或者大于4000且小于5000的商品()
328.select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price>100 and shop_price <300 or="" shop_price="">4000 and shop_price <5000;
329.
330.
331.
332.1.9:取出第3个栏目下面价格<1000>3000,并且点击量>5的系列商品
333.select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods where
334.cat_id=3 and (shop_price <1000 or="" shop_price="">3000) and click_count>5;
335.
336.1.10:取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)
337.select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods
338. where cat_id in (2,3,4,5);
339.
340.1.11:取出名字以"诺基亚"开头的商品
341.select goods_id,cat_id,goods_name,shop_price from ecs_goods where goods_name like '诺基亚%';
342.
343.
344.1.12:取出名字为"诺基亚Nxx"的手机
345.select goods_id,cat_id,goods_name,shop_price from ecs_goods
346. where goods_name like '诺基亚N__';
347.
348.
349.1.13:取出名字不以"诺基亚"开头的商品
350.select goods_id,cat_id,goods_name,shop_price from ecs_goos
351. where goods_name not like '诺基亚%';
352.
353.1.14:取出第3个栏目下面价格在1000到3000之间,并且点击量>5 "诺基亚"开头的系列商品
354.select goods_id,cat_id,goods_name,shop_price from ecs_goods where
355.cat_id=3 and shop_price>1000 and shop_price <3000 and="" click_count="">5 and goods_name like '诺基亚%';
356.
357.
358.select goods_id,cat_id,goods_name,shop_price from ecs_goods where
359.shop_price between 1000 and 3000 and cat_id=3 and click_count>5 and goods_name like '诺基亚%';
360.
361.
362.一道面试题
363.有如下表和数组
364.把num值处于[20,29]之间,改为20
365.num值处于[30,39]之间的,改为30
366.
367.mian表
368.+------+
369.| num |
370.+------+
371.| 3 |
372.| 12 |
373.| 15 |
374.| 25 |
375.| 23 |
376.| 29 |
377.| 34 |
378.| 37 |
379.| 32 |
380.| 45 |
381.| 48 |
382.| 52 |
383.+------+
384.
385.练习题:
386.把good表中商品名为'诺基亚xxxx'的商品,改为'HTCxxxx',
387.提示:大胆的把列看成变量,参与运算,甚至调用函数来处理 .
388.substring(),concat()
389.
390.
391.2 分组查询group:
392.2.1:查出最贵的商品的价格
393.select max(shop_price) from ecs_goods;
394.
395.2.2:查出最大(最新)的商品编号
396.select max(goods_id) from ecs_goods;
397.
398.2.3:查出最便宜的商品的价格
399.select min(shop_price) from ecs_goods;
400.
401.2.4:查出最旧(最小)的商品编号
402.select min(goods_id) from ecs_goods;
403.
404.2.5:查询该店所有商品的库存总量
405.select sum(goods_number) from ecs_goods;
406.
407.2.6:查询所有商品的平均价
408. select avg(shop_price) from ecs_goods;
409.
410.2.7:查询该店一共有多少种商品
411. select count(*) from ecs_goods;
412.
413.
414.2.8:查询每个栏目下面
415.最贵商品价格
416.最低商品价格
417.商品平均价格
418.商品库存量
419.商品种类
420.提示:(5个聚合函数,sum,avg,max,min,count与group综合运用)
421.select cat_id,max(shop_price) from ecs_goods group by cat_id;
422.
423.
424.3 having与group综合运用查询:
425.3.1:查询该店的商品比市场价所节省的价格
426.select goods_id,goods_name,market_price-shop_price as j
427. from ecs_goods ;
428.
429.
430.3.2:查询每个商品所积压的货款(提示:库存*单价)
431.select goods_id,goods_name,goods_number*shop_price from ecs_goods
432.
433.3.3:查询该店积压的总货款
434.select sum(goods_number*shop_price) from ecs_goods;
435.
436.3.4:查询该店每个栏目下面积压的货款.
437.select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id;
438.
439.3.5:查询比市场价省钱200元以上的商品及该商品所省的钱(where和having分别实现)
440.select goods_id,goods_name,market_price-shop_price as k from ecs_goods
441.where market_price-shop_price >200;
442.
443.select goods_id,goods_name,market_price-shop_price as k from ecs_goods
444.having k >200;
445.
446.3.6:查询积压货款超过2W元的栏目,以及该栏目积压的货款
447.select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id
448.having k>20000
449.
450.3.7:where-having-group综合练习题
451.有如下表及数据
452.+------+---------+-------+
453.| name | subject | score |
454.+------+---------+-------+
455.| 张三 | 数学 | 90 |
456.| 张三 | 语文 | 50 |
457.| 张三 | 地理 | 40 |
458.| 李四 | 语文 | 55 |
459.| 李四 | 政治 | 45 |
460.| 王五 | 政治 | 30 |
461.+------+---------+-------+
462.
463.要求:查询出2门及2门以上不及格者的平均成绩
464.
465.## 一种错误做法
466.mysql> select name,count(score<60 as="" k="" avg="" score="" from="" stu="" group="" by="" name="" having="" k="">=2;
467.+------+---+------------+
468.| name | k | avg(score) |
469.+------+---+------------+
470.| 张三 | 3 | 60.0000 |
471.| 李四 | 2 | 50.0000 |
472.+------+---+------------+
473.2 rows in set (0.00 sec)
474.
475.mysql> select name,count(score<60 as="" k="" avg="" score="" from="" stu="" group="" by="" name="" 476="" ------="" ---="" ------------="" 477="" name="" k="" avg="" score="" 478="" ------="" ---="" ------------="" 479="" 3="" 60="" 0000="" 480="" 2="" 50="" 0000="" 481="" 1="" 30="" 0000="" 482="" ------="" ---="" ------------="" 483="" 3="" rows="" in="" set="" 0="" 00="" sec="" 484="" 485="" mysql=""> select name,count(score<60 as="" k="" avg="" score="" from="" stu="" group="" by="" name="" having="" k="">=2;
486.+------+---+------------+
487.| name | k | avg(score) |
488.+------+---+------------+
489.| 张三 | 3 | 60.0000 |
490.| 李四 | 2 | 50.0000 |
491.+------+---+------------+
492.2 rows in set (0.00 sec)
493.
494.#加上赵六后错误暴露
495.mysql> insert into stu
496. -> values
497. -> ('赵六','A',100),
498. -> ('赵六','B',99),
499. -> ('赵六','C',98);
500.Query OK, 3 rows affected (0.05 sec)
501.Records: 3 Duplicates: 0 Warnings: 0
502.
503.#错误显现
504.mysql> select name,count(score<60 as="" k="" avg="" score="" from="" stu="" group="" by="" name="" having="" k="">=2;
505.+------+---+------------+
506.| name | k | avg(score) |
507.+------+---+------------+
508.| 张三 | 3 | 60.0000 |
509.| 李四 | 2 | 50.0000 |
510.| 赵六 | 3 | 99.0000 |
511.+------+---+------------+
512.3 rows in set (0.00 sec)
513.
514.#正确思路,先查看每个人的平均成绩
515.mysql> select name,avg(score) from stu group by name;
516.+------+------------+
517.| name | avg(score) |
518.+------+------------+
519.| 张三 | 60.0000 |
520.| 李四 | 50.0000 |
521.| 王五 | 30.0000 |
522.| 赵六 | 99.0000 |
523.+------+------------+
524.4 rows in set (0.00 sec)
525.
526.mysql> # 看每个人挂科情况
527.mysql> select name,score < 60 from stu;
528.+------+------------+
529.| name | score < 60 |
530.+------+------------+
531.| 张三 | 0 |
532.| 张三 | 1 |
533.| 张三 | 1 |
534.| 李四 | 1 |
535.| 李四 | 1 |
536.| 王五 | 1 |
537.| 赵六 | 0 |
538.| 赵六 | 0 |
539.| 赵六 | 0 |
540.+------+------------+
541.9 rows in set (0.00 sec)
542.
543.mysql> #计算每个人的挂科科目
544.mysql> select name,sum(score < 60) from stu group by name;
545.+------+-----------------+
546.| name | sum(score < 60) |
547.+------+-----------------+
548.| 张三 | 2 |
549.| 李四 | 2 |
550.| 王五 | 1 |
551.| 赵六 | 0 |
552.+------+-----------------+
553.4 rows in set (0.00 sec)
554.
555.#同时计算每人的平均分
556.mysql> select name,sum(score < 60),avg(score) as pj from stu group by name;
557.+------+-----------------+---------+
558.| name | sum(score < 60) | pj |
559.+------+-----------------+---------+
560.| 张三 | 2 | 60.0000 |
561.| 李四 | 2 | 50.0000 |
562.| 王五 | 1 | 30.0000 |
563.| 赵六 | 0 | 99.0000 |
564.+------+-----------------+---------+
565.4 rows in set (0.00 sec)
566.
567.#利用having筛选挂科2门以上的.
568.mysql> select name,sum(score < 60) as gk ,avg(score) as pj from stu group by name having gk >=2;
569.+------+------+---------+
570.| name | gk | pj |
571.+------+------+---------+
572.| 张三 | 2 | 60.0000 |
573.| 李四 | 2 | 50.0000 |
574.+------+------+---------+
575.2 rows in set (0.00 sec)
576.
577.
578.
579.4: order by 与 limit查询
580.4.1:按价格由高到低排序
581.select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc;
582.
583.4.2:按发布时间由早到晚排序
584.select goods_id,goods_name,add_time from ecs_goods order by add_time;
585.
586.4.3:接栏目由低到高排序,栏目内部按价格由高到低排序
587.select goods_id,cat_id,goods_name,shop_price from ecs_goods
588. order by cat_id ,shop_price desc;
589.
590.
591.4.4:取出价格最高的前三名商品
592.select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc limit 3;
593.
594.
595.
596.4.5:取出点击量前三名到前5名的商品
597.select goods_id,goods_name,click_count from ecs_goods order by click_count desc limit 2,3;
598.
599.5 连接查询
600.5.1:取出所有商品的商品名,栏目名,价格
601.select goods_name,cat_name,shop_price from
602.ecs_goods left join ecs_category
603.on ecs_goods.cat_id=ecs_category.cat_id;
604.
605.5.2:取出第4个栏目下的商品的商品名,栏目名,价格
606.select goods_name,cat_name,shop_price from
607.ecs_goods left join ecs_category
608.on ecs_goods.cat_id=ecs_category.cat_id
609.where ecs_goods.cat_id = 4;
610.
611.
612.
613.5.3:取出第4个栏目下的商品的商品名,栏目名,与品牌名
614.select goods_name,cat_name,brand_name from
615.ecs_goods left join ecs_category
616.on ecs_goods.cat_id=ecs_category.cat_id
617.left join ecs_brand
618.on ecs_goods.brand_id=ecs_brand.brand_id
619.where ecs_goods.cat_id = 4;
620.
621.5.4: 用友面试题
622.
623.根据给出的表结构按要求写出SQL语句。
624.Match 赛程表
625.
626.
627.
628.
629.
630.
631.
632.
633.
634.
635.
636.
637.
638.
639.
640.
641.
642.
字段名称 | 字段类型 | 描述 |
matchID | int | 主键 |
hostTeamID | int | 主队的ID |
guestTeamID | int | 客队的ID |
matchResult | varchar(20) | 比赛结果,如(2:0) |
matchTime | date | 比赛开始时间 |
643.
644.Team 参赛队伍表
645.
646.
647.
648.
649.
650.
651.
652.
653.
654.
字段名称 | 字段类型 | 描述 |
teamID | int | 主键 |
teamName | varchar(20) | 队伍名称 |
655.
656.Match的hostTeamID与guestTeamID都与Team中的teamID关联
657.查出 2006-6-1 到2006-7-1之间举行的所有比赛,并且用以下形式列出:
658.拜仁 2:0 不来梅 2006-6-21
659.
660.mysql> select * from m;
661.+-----+------+------+------+------------+
662.| mid | hid | gid | mres | matime |
663.+-----+------+------+------+------------+
664.| 1 | 1 | 2 | 2:0 | 2006-05-21 |
665.| 2 | 2 | 3 | 1:2 | 2006-06-21 |
666.| 3 | 3 | 1 | 2:5 | 2006-06-25 |
667.| 4 | 2 | 1 | 3:2 | 2006-07-21 |
668.+-----+------+------+------+------------+
669.4 rows in set (0.00 sec)
670.
671.mysql> select * from t;
672.+------+----------+
673.| tid | tname |
674.+------+----------+
675.| 1 | 国安 |
676.| 2 | 申花 |
677.| 3 | 传智联队 |
678.+------+----------+
679.3 rows in set (0.00 sec)
680.
681.mysql> select hid,t1.tname as hname ,mres,gid,t2.tname as gname,matime
682. -> from
683. -> m left join t as t1
684. -> on m.hid = t1.tid
685. -> left join t as t2
686. -> on m.gid = t2.tid;
687.+------+----------+------+------+----------+------------+
688.| hid | hname | mres | gid | gname | matime |
689.+------+----------+------+------+----------+------------+
690.| 1 | 国安 | 2:0 | 2 | 申花 | 2006-05-21 |
691.| 2 | 申花 | 1:2 | 3 | 传智联队 | 2006-06-21 |
692.| 3 | 传智联队 | 2:5 | 1 | 国安 | 2006-06-25 |
693.| 2 | 申花 | 3:2 | 1 | 国安 | 2006-07-21 |
694.+------+----------+------+------+----------+------------+
695.4 rows in set (0.00 sec)
696.
697.6 union查询
698.6.1:把ecs_comment,ecs_feedback两个表中的数据,各取出4列,并把结果集union成一个结果集.
699.
700.6.2:3期学员碰到的一道面试题
701.A表:
702.+------+------+
703.| id | num |
704.+------+------+
705.| a | 5 |
706.| b | 10 |
707.| c | 15 |
708.| d | 10 |
709.+------+------+
710.
711.B表:
712.+------+------+
713.| id | num |
714.+------+------+
715.| b | 5 |
716.| c | 15 |
717.| d | 20 |
718.| e | 99 |
719.+------+------+
720.
721.
722.mysql> # 合并 ,注意all的作用
723.mysql> select * from ta
724. -> union all
725. -> select * from tb;
726.+------+------+
727.| id | num |
728.+------+------+
729.| a | 5 |
730.| b | 10 |
731.| c | 15 |
732.| d | 10 |
733.| b | 5 |
734.| c | 15 |
735.| d | 20 |
736.| e | 99 |
737.+------+------+
738.
739.要求查询出以下效果:
740.+------+----------+
741.| id | sum(num) |
742.+------+----------+
743.| a | 5 |
744.| b | 15 |
745.| c | 30 |
746.| d | 30 |
747.| e | 99 |
748.+------+----------+
749.
750.参考答案:
751.mysql> # sum,group求和
752.mysql> select id,sum(num) from (select * from ta union all select * from tb) as tmp group by id;
753.+------+----------+
754.| id | sum(num) |
755.+------+----------+
756.| a | 5 |
757.| b | 15 |
758.| c | 25 |
759.| d | 30 |
760.| e | 99 |
761.+------+----------+
762.5 rows in set (0.00 sec)
763.
764.
765.7: 子查询:
766.7.1:查询出最新一行商品(以商品编号最大为最新,用子查询实现)
767.select goods_id,goods_name from
768. ecs_goods where goods_id =(select max(goods_id) from ecs_goods);
769.
770.
771.7.2:查询出编号为19的商品的栏目名称(用左连接查询和子查询分别)
772.7.3:用where型子查询把ecs_goods表中的每个栏目下面最新的商品取出来
773.select goods_id,goods_name,cat_id from ecs_goods where goods_id in (select max(goods_id) from ecs_goods group by cat_id);
774.7.4:用from型子查询把ecs_goods表中的每个栏目下面最新的商品取出来
775.select * from (select goods_id,cat_id,goods_name from ecs_goods order by goods_id desc) as t group by cat_id;
776.
777.
778.
779.
780.创建触发器:
781.
782. CREATE trigger tg2
783.after insert on ord
784.for each row
785.update goods set goods_number=goods_number-new.num where id=new.gid
786.
787.CREATE trigger tg3
788.after delete on ord
789.for each row
790.update goods set goods_number=good_number+old.num where id=old.gid
791.
792.
793.CREATE trigger tg4
794.after update on ord
795.for each row
796.update goods set goods_number=goods_number+old.num-new.num where id=old.gid
797.