0%

mysql复习

mysql复习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
mysql复习
一:复习前的准备
1:确认你已安装wamp
2:确认你已安装ecshop,并且ecshop的数据库名为shop

二 基础知识:
1.数据库的连接
mysql -u -p -h
-u 用户名
-p 密码
-h host主机
2:库级知识
2.1 显示数据库: show databases;
2.2 选择数据库: use dbname;
2.3 创建数据库: create database dbname charset utf8;
2.3 删除数据库: drop database dbname;

3: 表级操作:
3.1 显示库下面的表
show tables;

3.2 查看表的结构:
desc tableName;

3.3 查看表的创建过程:
show create table tableName;

3.4 创建表:
create table tbName (
列名称1 列类型 [列参数] [not null default ],
....列2...
....
列名称N 列类型 [列参数] [not null default ]
)engine myisam/innodb charset utf8/gbk

3.4的例子:
create table user (
id int auto_increment,
name varchar(20) not null default "",
age tinyint unsigned not null default 0,
index id (id)
)engine=innodb charset=utf8;
注:innodb是表引擎,也可以是myisam或其他,但最常用的是myisam和innodb,
charset 常用的有utf8,gbk;

3.5 修改表
3.5.1 修改表之增加列:
alter table tbName
add 列名称1 列类型 [列参数] [not null default ] #(add之后的旧列名之后的语法和创建表时的列声明一样)

3.5.2 修改表之修改列
alter table tbName
change 旧列名 新列名 列类型 [列参数] [not null default ]
(注:旧列名之后的语法和创建表时的列声明一样)

3.5.3 修改表之减少列:
alter table tbName
drop 列名称;

3.5.4 修改表之增加主键
alter table tbName add primary key(主键所在列名);
例:alter table goods add primary key(id)
该例是把主键建立在id列上

3.5.5 修改表之删除主键
alter table tbName drop primary key;

3.5.6 修改表之增加索引
alter table tbName add [unique|fulltext] index 索引名(列名);

3.5.7 修改表之删除索引
alter table tbName drop index 索引名;

3.5.8 清空表的数据
truncate tableName;

4:列类型讲解
列类型:
整型:tinyint (0~255/-128~127) smallint (0~65535/-32768~32767) mediumint int bigint (参考手册11.2)
参数解释:
unsigned 无符号(不能为负) zerofill 0填充 M 填充后的宽度
举例:tinyint unsigned;
tinyint(6) zerofill;
数值型
浮点型:float double
格式:float(M,D) unsignedzerofill;
字符型
char(m) 定长
varchar(m)变长
text
列 实存字符i 实占空间 利用率

char(M) 0<=i<=M M i/m<=100%
varchar(M) 0<=i<=M i+1,2 i/i+1/2<100%
year YYYY 范围:1901~2155. 可输入值2位和4位(如98,2012)
日期时间类型 date YYYY-MM-DD 如:2010-03-14
time HH:MM:SS 如:19:26:32
datetime YYYY-MM-DD HH:MM:SS 如:2010-03-14 19:26:32
timestamp YYYY-MM-DD HH:MM:SS 特性:不用赋值,该列会为自己赋当前的具体时间

5:增删改查基本操作

5.1 插入数据
insert into 表名(col1,col2,……) values(val1,val2……); -- 插入指定列
insert into 表名 values (,,,,); -- 插入所有列
insert into 表名 values -- 一次插入多行
(val1,val2……),
(val1,val2……),
(val1,val2……);

5.3修改数据
update tablename
set
col1=newval1,
col2=newval2,
...
...
colN=newvalN
where 条件;

5.4,删除数据 delete from tablenaeme where 条件;

5.5select 查询

1) 条件查询 where a. 条件表达式的意义,表达式为真,则该行取出
b. 比较运算符 =!=< > <= >=
c. like , not like ("%"匹配任意多个字符,"_"匹配任意单个字符)
in , not in , between and
d. is null , is not null
2) 分组 group by
一般要配合5个聚合函数使用:max,min,sum,avg,count
3) 筛选 having
4) 排序 order by
5) 限制 limit

6: 连接查询

6.1, 左连接
.. left join .. on
table A left join table B on tableA.col1 = tableB.col2 ;
例句:
select 列名 from table A left join table B on tableA.col1 = tableB.col2
2. 右链接: right join
3. 内连接: inner join

左右连接都是以在左边的表的数据为准,沿着左表查右表.
内连接是以两张表都有的共同部分数据为准,也就是左右连接的数据之交集.

7 子查询
where 型子查询:内层sql的返回值在where后作为条件表达式的一部分
例句: select * from tableA where colA = (select colB from tableB where ...);

from 型子查询:内层sql查询结果,作为一张表,供外层的sql语句再次查询
例句:select * from (select * from ...) as tableName where ....

8: 字符集
客服端sql编码 character_set_client
服务器转化后的sql编码 character_set_connection
服务器返回给客户端的结果集编码 character_set_results
快速把以上3个变量设为相同值: set names 字符集

存储引擎 engine=12
1 Myisam 速度快 不支持事务 回滚
2 Innodb 速度慢 支持事务,回滚

①开启事务 start transaction
②运行sql;
③提交,同时生效回滚 commit
ollback

触发器 trigger
监视地点:表
监视行为:增 删 改
触发时间:afterefore
触发事件:增 删 改

创建触发器语法
create trigger tgName
after/before insert/delete/update
on tableName
for each row
sql; -- 触发语句

删除触发器:drop trigger tgName;

索引
提高查询速度,但是降低了增删改的速度,所以使用索引时,要综合考虑.
索引不是越多越好,一般我们在常出现于条件表达式中的列加索引.
值越分散的列,索引的效果越好

索引类型
primary key主键索引
index 普通索引
unique index 唯一性索引
fulltext index 全文索引

综合练习:
连接上数据库服务器
创建一个gbk编码的数据库
建立商品表和栏目表,字段如下:

商品表:goods
goods_id --主键,
goods_name -- 商品名称
cat_id -- 栏目id
brand_id -- 品牌id
goods_sn -- 货号
goods_number -- 库存量
shop_price -- 价格
goods_desc --商品详细描述

栏目表:category
cat_id --主键
cat_name -- 栏目名称
parent_id -- 栏目的父id

建表完成后,作以下操作:
删除goods表的goods_desc 字段,及货号字段
并增加字段:click_count -- 点击量

在goods_name列上加唯一性索引
在shop_price列上加普通索引
在clcik_count列上加普通索引
删除click_count列上的索引

对goods表插入以下数据:
+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+
| goods_id | goods_name | cat_id | brand_id | goods_sn | goods_number | shop_price | click_count |
+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+
| 1 | KD876 | 4 | 8 | ECS000000 | 10 | 1388.00 | 7 |
| 4 | 诺基亚N85原装充电器 | 8 | 1 | ECS000004 | 17 | 58.00 | 0 |
| 3 | 诺基亚原装5800耳机 | 8 | 1 | ECS000002 | 24 | 68.00 | 3 |
| 5 | 索爱原装M2卡读卡器 | 11 | 7 | ECS000005 | 8 | 20.00 | 3 |
| 6 | 胜创KINGMAX内存卡 | 11 | 0 | ECS000006 | 15 | 42.00 | 0 |
| 7 | 诺基亚N85原装立体声耳机HS-82 | 8 | 1 | ECS000007 | 20 | 100.00 | 0 |
| 8 | 飞利浦9@9v | 3 | 4 | ECS000008 | 17 | 399.00 | 9 |
| 9 | 诺基亚E66 | 3 | 1 | ECS000009 | 13 | 2298.00 | 20 |
| 10 | 索爱C702c | 3 | 7 | ECS000010 | 7 | 1328.00 | 11 |
| 11 | 索爱C702c | 3 | 7 | ECS000011 | 1 | 1300.00 | 0 |
| 12 | 摩托罗拉A810 | 3 | 2 | ECS000012 | 8 | 983.00 | 14 |
| 13 | 诺基亚5320 XpressMusic | 3 | 1 | ECS000013 | 8 | 1311.00 | 13 |
| 14 | 诺基亚5800XM | 4 | 1 | ECS000014 | 4 | 2625.00 | 6 |
| 15 | 摩托罗拉A810 | 3 | 2 | ECS000015 | 3 | 788.00 | 8 |
| 16 | 恒基伟业G101 | 2 | 11 | ECS000016 | 0 | 823.33 | 3 |
| 17 | 夏新N7 | 3 | 5 | ECS000017 | 1 | 2300.00 | 2 |
| 18 | 夏新T5 | 4 | 5 | ECS000018 | 1 | 2878.00 | 0 |
| 19 | 三星SGH-F258 | 3 | 6 | ECS000019 | 0 | 858.00 | 7 |
| 20 | 三星BC01 | 3 | 6 | ECS000020 | 13 | 280.00 | 14 |
| 21 | 金立 A30 | 3 | 10 | ECS000021 | 40 | 2000.00 | 4 |
| 22 | 多普达Touch HD | 3 | 3 | ECS000022 | 0 | 5999.00 | 15 |
| 23 | 诺基亚N96 | 5 | 1 | ECS000023 | 8 | 3700.00 | 17 |
| 24 | P806 | 3 | 9 | ECS000024 | 148 | 2000.00 | 36 |
| 25 | 小灵通/固话50元充值卡 | 13 | 0 | ECS000025 | 2 | 48.00 | 0 |
| 26 | 小灵通/固话20元充值卡 | 13 | 0 | ECS000026 | 2 | 19.00 | 0 |
| 27 | 联通100元充值卡 | 15 | 0 | ECS000027 | 2 | 95.00 | 0 |
| 28 | 联通50元充值卡 | 15 | 0 | ECS000028 | 0 | 45.00 | 0 |
| 29 | 移动100元充值卡 | 14 | 0 | ECS000029 | 0 | 90.00 | 0 |
| 30 | 移动20元充值卡 | 14 | 0 | ECS000030 | 9 | 18.00 | 1 |
| 31 | 摩托罗拉E8 | 3 | 2 | ECS000031 | 1 | 1337.00 | 5 |
| 32 | 诺基亚N85 | 3 | 1 | ECS000032 | 1 | 3010.00 | 9 |
+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+

三 查询知识
注:以下查询基于ecshop网站的商品表(ecs_goods)
在练习时可以只取部分列,方便查看.

1: 基础查询 where的练习:

查出满足以下条件的商品
1.1:主键为32的商品
select goods_id,goods_name,shop_price
from ecs_goods
where goods_id=32;
1.2:不属第3栏目的所有商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods
where cat_id!=3;

1.3:本店价格高于3000元的商品

select goods_id,cat_id,goods_name,shop_price from ecs_goods
where shop_price >3000;

1.4:本店价格低于或等于100元的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price <=100;

1.5:取出第4栏目或第11栏目的商品(不许用or)
select goods_id,cat_id,goods_name,shop_price from ecs_goods
where cat_id in (4,11);

1.6:取出100<=价格<=500的商品(不许用and)
select goods_id,cat_id,goods_name,shop_price from ecs_goods
where shop_price between 100 and 500;

1.7:取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)
select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id!=3 and cat_id!=11;

select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id not in (3,11);

1.8:取出价格大于100且小于300,或者大于4000且小于5000的商品()
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;

1.9:取出第3个栏目下面价格<1000>3000,并且点击量>5的系列商品
select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods where
cat_id=3 and (shop_price <1000 or shop_price>3000) and click_count>5;

1.10:取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)
select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods
where cat_id in (2,3,4,5);

1.11:取出名字以"诺基亚"开头的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where goods_name like "诺基亚%";

1.12:取出名字为"诺基亚Nxx"的手机
select goods_id,cat_id,goods_name,shop_price from ecs_goods
where goods_name like "诺基亚N__";

1.13:取出名字不以"诺基亚"开头的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goos
where goods_name not like "诺基亚%";

1.14:取出第3个栏目下面价格在10003000之间,并且点击量>5 "诺基亚"开头的系列商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where
cat_id=3 and shop_price>1000 and shop_price <3000 and click_count>5 and goods_name like "诺基亚%";

select goods_id,cat_id,goods_name,shop_price from ecs_goods where
shop_price between 1000 and 3000 and cat_id=3 and click_count>5 and goods_name like "诺基亚%";

一道面试题
有如下表和数组
把num值处于[20,29]之间,改为20
num值处于[30,39]之间的,改为30

mian表
+------+
| num |
+------+
| 3 |
| 12 |
| 15 |
| 25 |
| 23 |
| 29 |
| 34 |
| 37 |
| 32 |
| 45 |
| 48 |
| 52 |
+------+

练习题:
把good表中商品名为"诺基亚xxxx"的商品,改为"HTCxxxx",
提示:大胆的把列看成变量,参与运算,甚至调用函数来处理 .
substring(),concat()

2 分组查询group:
2.1:查出最贵的商品的价格
select max(shop_price) from ecs_goods;

2.2:查出最大(最新)的商品编号
select max(goods_id) from ecs_goods;

2.3:查出最便宜的商品的价格
select min(shop_price) from ecs_goods;

2.4:查出最旧(最小)的商品编号
select min(goods_id) from ecs_goods;

2.5:查询该店所有商品的库存总量
select sum(goods_number) from ecs_goods;

2.6:查询所有商品的平均价
select avg(shop_price) from ecs_goods;

2.7:查询该店一共有多少种商品
select count(*) from ecs_goods;

2.8:查询每个栏目下面
最贵商品价格
最低商品价格
商品平均价格
商品库存量
商品种类
提示:(5个聚合函数,sum,avg,max,min,count与group综合运用)
select cat_id,max(shop_price) from ecs_goods group by cat_id;

3 havinggroup综合运用查询:
3.1:查询该店的商品比市场价所节省的价格
select goods_id,goods_name,market_price-shop_price as j
from ecs_goods ;

3.2:查询每个商品所积压的货款(提示:库存*单价)
select goods_id,goods_name,goods_number*shop_price from ecs_goods

3.3:查询该店积压的总货款
select sum(goods_number*shop_price) from ecs_goods;

3.4:查询该店每个栏目下面积压的货款.
select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id;

3.5:查询比市场价省钱200元以上的商品及该商品所省的钱(wherehaving分别实现)
select goods_id,goods_name,market_price-shop_price as k from ecs_goods
where market_price-shop_price >200;

select goods_id,goods_name,market_price-shop_price as k from ecs_goods
having k >200;

3.6:查询积压货款超过2W元的栏目,以及该栏目积压的货款
select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id
having k>20000

3.7:where-having-group综合练习题
有如下表及数据
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 张三 | 数学 | 90 |
| 张三 | 语文 | 50 |
| 张三 | 地理 | 40 |
| 李四 | 语文 | 55 |
| 李四 | 政治 | 45 |
| 王五 | 政治 | 30 |
+------+---------+-------+

要求:查询出2门及2门以上不及格者的平均成绩

## 一种错误做法
mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;
+------+---+------------+
| name | k | avg(score) |
+------+---+------------+
| 张三 | 3 | 60.0000 |
| 李四 | 2 | 50.0000 |
+------+---+------------+
2 rows in set (0.00 sec)

mysql> select name,count(score<60) as k,avg(score) from stu group by name;
+------+---+------------+
| name | k | avg(score) |
+------+---+------------+
| 张三 | 3 | 60.0000 |
| 李四 | 2 | 50.0000 |
| 王五 | 1 | 30.0000 |
+------+---+------------+
3 rows in set (0.00 sec)

mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;
+------+---+------------+
| name | k | avg(score) |
+------+---+------------+
| 张三 | 3 | 60.0000 |
| 李四 | 2 | 50.0000 |
+------+---+------------+
2 rows in set (0.00 sec)

#加上赵六后错误暴露
mysql> insert into stu
-> values
-> ("赵六","A",100),
-> ("赵六","B",99),
-> ("赵六","C",98);
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

#错误显现
mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;
+------+---+------------+
| name | k | avg(score) |
+------+---+------------+
| 张三 | 3 | 60.0000 |
| 李四 | 2 | 50.0000 |
| 赵六 | 3 | 99.0000 |
+------+---+------------+
3 rows in set (0.00 sec)

#正确思路,先查看每个人的平均成绩
mysql> select name,avg(score) from stu group by name;
+------+------------+
| name | avg(score) |
+------+------------+
| 张三 | 60.0000 |
| 李四 | 50.0000 |
| 王五 | 30.0000 |
| 赵六 | 99.0000 |
+------+------------+
4 rows in set (0.00 sec)

mysql> # 看每个人挂科情况
mysql> select name,score < 60 from stu;
+------+------------+
| name | score < 60 |
+------+------------+
| 张三 | 0 |
| 张三 | 1 |
| 张三 | 1 |
| 李四 | 1 |
| 李四 | 1 |
| 王五 | 1 |
| 赵六 | 0 |
| 赵六 | 0 |
| 赵六 | 0 |
+------+------------+
9 rows in set (0.00 sec)

mysql> #计算每个人的挂科科目
mysql> select name,sum(score < 60) from stu group by name;
+------+-----------------+
| name | sum(score < 60) |
+------+-----------------+
| 张三 | 2 |
| 李四 | 2 |
| 王五 | 1 |
| 赵六 | 0 |
+------+-----------------+
4 rows in set (0.00 sec)

#同时计算每人的平均分
mysql> select name,sum(score < 60),avg(score) as pj from stu group by name;
+------+-----------------+---------+
| name | sum(score < 60) | pj |
+------+-----------------+---------+
| 张三 | 2 | 60.0000 |
| 李四 | 2 | 50.0000 |
| 王五 | 1 | 30.0000 |
| 赵六 | 0 | 99.0000 |
+------+-----------------+---------+
4 rows in set (0.00 sec)

#利用having筛选挂科2门以上的.
mysql> select name,sum(score < 60) as gk ,avg(score) as pj from stu group by name having gk >=2;
+------+------+---------+
| name | gk | pj |
+------+------+---------+
| 张三 | 2 | 60.0000 |
| 李四 | 2 | 50.0000 |
+------+------+---------+
2 rows in set (0.00 sec)

4: order by 与 limit查询
4.1:按价格由高到低排序
select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc;

4.2:按发布时间由早到晚排序
select goods_id,goods_name,add_time from ecs_goods order by add_time;

4.3:接栏目由低到高排序,栏目内部按价格由高到低排序
select goods_id,cat_id,goods_name,shop_price from ecs_goods
order by cat_id ,shop_price desc;

4.4:取出价格最高的前三名商品
select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc limit 3;

4.5:取出点击量前三名到前5名的商品
select goods_id,goods_name,click_count from ecs_goods order by click_count desc limit 2,3;

5 连接查询
5.1:取出所有商品的商品名,栏目名,价格
select goods_name,cat_name,shop_price from
ecs_goods left join ecs_category
on ecs_goods.cat_id=ecs_category.cat_id;

5.2:取出第4个栏目下的商品的商品名,栏目名,价格
select goods_name,cat_name,shop_price from
ecs_goods left join ecs_category
on ecs_goods.cat_id=ecs_category.cat_id
where ecs_goods.cat_id = 4;

5.3:取出第4个栏目下的商品的商品名,栏目名,与品牌名
select goods_name,cat_name,brand_name from
ecs_goods left join ecs_category
on ecs_goods.cat_id=ecs_category.cat_id
left join ecs_brand
on ecs_goods.brand_id=ecs_brand.brand_id
where ecs_goods.cat_id = 4;

5.4: 用友面试题

根据给出的表结构按要求写出SQL语句。
Match 赛程表
字段名称 字段类型 描述
matchID int 主键
hostTeamID int 主队的ID
guestTeamID int 客队的ID
matchResult varchar(20) 比赛结果,如(2:0
matchTime date 比赛开始时间

Team 参赛队伍表
字段名称 字段类型 描述
teamID int 主键
teamName varchar(20) 队伍名称

Match的hostTeamID与guestTeamID都与Team中的teamID关联
查出 2006-6-12006-7-1之间举行的所有比赛,并且用以下形式列出:
拜仁 20 不来梅 2006-6-21

mysql> select * from m;
+-----+------+------+------+------------+
| mid | hid | gid | mres | matime |
+-----+------+------+------+------------+
| 1 | 1 | 2 | 2:0 | 2006-05-21 |
| 2 | 2 | 3 | 1:2 | 2006-06-21 |
| 3 | 3 | 1 | 2:5 | 2006-06-25 |
| 4 | 2 | 1 | 3:2 | 2006-07-21 |
+-----+------+------+------+------------+
4 rows in set (0.00 sec)

mysql> select * from t;
+------+----------+
| tid | tname |
+------+----------+
| 1 | 国安 |
| 2 | 申花 |
| 3 | 传智联队 |
+------+----------+
3 rows in set (0.00 sec)

mysql> select hid,t1.tname as hname ,mres,gid,t2.tname as gname,matime
-> from
-> m left join t as t1
-> on m.hid = t1.tid
-> left join t as t2
-> on m.gid = t2.tid;
+------+----------+------+------+----------+------------+
| hid | hname | mres | gid | gname | matime |
+------+----------+------+------+----------+------------+
| 1 | 国安 | 2:0 | 2 | 申花 | 2006-05-21 |
| 2 | 申花 | 1:2 | 3 | 传智联队 | 2006-06-21 |
| 3 | 传智联队 | 2:5 | 1 | 国安 | 2006-06-25 |
| 2 | 申花 | 3:2 | 1 | 国安 | 2006-07-21 |
+------+----------+------+------+----------+------------+
4 rows in set (0.00 sec)

6 union查询
6.1:把ecs_comment,ecs_feedback两个表中的数据,各取出4列,并把结果集union成一个结果集.

6.2:3期学员碰到的一道面试题
A表:
+------+------+
| id | num |
+------+------+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
+------+------+

B表:
+------+------+
| id | num |
+------+------+
| b | 5 |
| c | 15 |
| d | 20 |
| e | 99 |
+------+------+

mysql> # 合并 ,注意all的作用
mysql> select * from ta
-> union all
-> select * from tb;
+------+------+
| id | num |
+------+------+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
| b | 5 |
| c | 15 |
| d | 20 |
| e | 99 |
+------+------+

要求查询出以下效果:
+------+----------+
| id | sum(num) |
+------+----------+
| a | 5 |
| b | 15 |
| c | 30 |
| d | 30 |
| e | 99 |
+------+----------+

参考答案:
mysql> # sum,group求和
mysql> select id,sum(num) from (select * from ta union all select * from tb) as tmp group by id;
+------+----------+
| id | sum(num) |
+------+----------+
| a | 5 |
| b | 15 |
| c | 25 |
| d | 30 |
| e | 99 |
+------+----------+
5 rows in set (0.00 sec)

7: 子查询:
7.1:查询出最新一行商品(以商品编号最大为最新,用子查询实现)
select goods_id,goods_name from
ecs_goods where goods_id =(select max(goods_id) from ecs_goods);

7.2:查询出编号为19的商品的栏目名称(用左连接查询和子查询分别)
7.3:用where型子查询把ecs_goods表中的每个栏目下面最新的商品取出来
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);
7.4:用from型子查询把ecs_goods表中的每个栏目下面最新的商品取出来
select * from (select goods_id,cat_id,goods_name from ecs_goods order by goods_id desc) as t group by cat_id;

创建触发器:

CREATE trigger tg2
after insert on ord
for each row
update goods set goods_number=goods_number-new.num where id=new.gid

CREATE trigger tg3
after delete on ord
for each row
update goods set goods_number=good_number+old.num where id=old.gid

CREATE trigger tg4
after update on ord
for each row
update goods set goods_number=goods_number+old.num-new.num where id=old.gid
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!