基礎(chǔ)知識(shí):
1.數(shù)據(jù)庫(kù)的連接
mysql -u -p -h
-u 用戶名
-p 密碼
-h host主機(jī)
2:庫(kù)級(jí)知識(shí)
2.1 顯示數(shù)據(jù)庫(kù): show databases;
2.2 選擇數(shù)據(jù)庫(kù): use dbname;
2.3 創(chuàng)建數(shù)據(jù)庫(kù): create database dbname charset utf8;
2.3 刪除數(shù)據(jù)庫(kù): drop database dbname;
3: 表級(jí)操作:
3.1 顯示庫(kù)下面的表
show tables;
3.2 查看表的結(jié)構(gòu):
desc tableName;
3.3 查看表的創(chuàng)建過(guò)程:
show create table tableName;
3.4 創(chuàng)建表:
create table tbName (
列名稱(chēng)1 列類(lèi)型 [列參數(shù)] [not null default ],
....列2...
....
列名稱(chēng)N 列類(lèi)型 [列參數(shù)] [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 列名稱(chēng)１ 列類(lèi)型 [列參數(shù)] [not null default ] #(add之后的舊列名之后的語(yǔ)法和創(chuàng)建表時(shí)的列聲明一樣)
3.5.2 修改表之修改列
alter table tbName
change 舊列名 新列名 列類(lèi)型 [列參數(shù)] [not null default ]
(注:舊列名之后的語(yǔ)法和創(chuàng)建表時(shí)的列聲明一樣)
3.5.3 修改表之減少列:
alter table tbName
drop 列名稱(chēng);
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 清空表的數(shù)據(jù)
truncate tableName;
4:列類(lèi)型講解
列類(lèi)型:
整型:tinyint (0255/-128127) smallint (065535/-3276832767) mediumint int bigint (參考手冊(cè)11.2)
參數(shù)解釋:
unsigned 無(wú)符號(hào)(不能為負(fù)) zerofill 0填充 M 填充后的寬度
舉例:tinyint unsigned;
tinyint(6) zerofill;
數(shù)值型
浮點(diǎn)型:float double
格式:float(M,D) unsigned\zerofill;
字符型
char(m) 定長(zhǎng)
varchar(m)變長(zhǎng)
text
列 實(shí)存字符i 實(shí)占空間 利用率
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)
日期時(shí)間類(lèi)型 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 特性:不用賦值,該列會(huì)為自己賦當(dāng)前的具體時(shí)間
5:增刪改查基本操作
5.1 插入數(shù)據(jù)
insert into 表名(col1,col2,……) values(val1,val2……); -- 插入指定列
insert into 表名 values (,,,,); -- 插入所有列
insert into 表名 values -- 一次插入多行
(val1,val2……),
(val1,val2……),
(val1,val2……);
5.3修改數(shù)據(jù)
update tablename
set
col1=newval1,
col2=newval2,
...
...
colN=newvalN
where 條件;
5.4茂卦，刪除數(shù)據(jù) delete from tablenaeme where 條件;
5.5蹄梢， select 查詢(xún)
（1） 條件查詢(xún) where a. 條件表達(dá)式的意義禁炒，表達(dá)式為真，則該行取出
b. 比較運(yùn)算符 = 暴备，!=们豌，< > <= >=
c. like , not like ('%'匹配任意多個(gè)字符,'_'匹配任意單個(gè)字符)
in , not in , between and
d. is null , is not null
（2） 分組 group by
一般要配合5個(gè)聚合函數(shù)使用:max,min,sum,avg,count
（3） 篩選 having
（4） 排序 order by
（5） 限制 limit
6: 連接查詢(xún)
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
- 右鏈接: right join
- 內(nèi)連接: inner join
左右連接都是以在左邊的表的數(shù)據(jù)為準(zhǔn),沿著左表查右表.
內(nèi)連接是以?xún)蓮埍矶加械墓餐糠謹(jǐn)?shù)據(jù)為準(zhǔn),也就是左右連接的數(shù)據(jù)之交集.
7 子查詢(xún)
where 型子查詢(xún):內(nèi)層sql的返回值在where后作為條件表達(dá)式的一部分
例句: select * from tableA where colA = (select colB from tableB where ...);
from 型子查詢(xún):內(nèi)層sql查詢(xún)結(jié)果,作為一張表,供外層的sql語(yǔ)句再次查詢(xún)
例句:select * from (select * from ...) as tableName where ....
8: 字符集
客服端sql編碼 character_set_client
服務(wù)器轉(zhuǎn)化后的sql編碼 character_set_connection
服務(wù)器返回給客戶端的結(jié)果集編碼 character_set_results
快速把以上3個(gè)變量設(shè)為相同值: set names 字符集
存儲(chǔ)引擎 engine=1\2
1 Myisam 速度快 不支持事務(wù) 回滾
2 Innodb 速度慢 支持事務(wù),回滾
①開(kāi)啟事務(wù) start transaction
②運(yùn)行sql;
③提交,同時(shí)生效\回滾 commit\rollback
觸發(fā)器 trigger
監(jiān)視地點(diǎn):表
監(jiān)視行為:增 刪 改
觸發(fā)時(shí)間:after\before
觸發(fā)事件:增 刪 改
創(chuàng)建觸發(fā)器語(yǔ)法
create trigger tgName
after/before insert/delete/update
on tableName
for each row
sql; -- 觸發(fā)語(yǔ)句
刪除觸發(fā)器:drop trigger tgName;
索引
提高查詢(xún)速度,但是降低了增刪改的速度,所以使用索引時(shí),要綜合考慮.
索引不是越多越好,一般我們?cè)诔３霈F(xiàn)于條件表達(dá)式中的列加索引.
值越分散的列涛浙，索引的效果越好
索引類(lèi)型
primary key主鍵索引
index 普通索引
unique index 唯一性索引
fulltext index 全文索引
綜合練習(xí):
連接上數(shù)據(jù)庫(kù)服務(wù)器
創(chuàng)建一個(gè)gbk編碼的數(shù)據(jù)庫(kù)
建立商品表和欄目表,字段如下:
商品表:goods
goods_id --主鍵,
goods_name -- 商品名稱(chēng)
cat_id -- 欄目id
brand_id -- 品牌id
goods_sn -- 貨號(hào)
goods_number -- 庫(kù)存量
shop_price -- 價(jià)格
goods_desc --商品詳細(xì)描述
欄目表:category
cat_id --主鍵
cat_name -- 欄目名稱(chēng)
parent_id -- 欄目的父id
建表完成后,作以下操作:
刪除goods表的goods_desc 字段,及貨號(hào)字段
并增加字段:click_count -- 點(diǎn)擊量
在goods_name列上加唯一性索引
在shop_price列上加普通索引
在clcik_count列上加普通索引
刪除click_count列上的索引
對(duì)goods表插入以下數(shù)據(jù):
+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+
| 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耳機(jī) | 8 | 1 | ECS000002 | 24 | 68.00 | 3 |
| 5 | 索愛(ài)原裝M2卡讀卡器 | 11 | 7 | ECS000005 | 8 | 20.00 | 3 |
| 6 | 勝創(chuàng)KINGMAX內(nèi)存卡 | 11 | 0 | ECS000006 | 15 | 42.00 | 0 |
| 7 | 諾基亞N85原裝立體聲耳機(jī)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 | 索愛(ài)C702c | 3 | 7 | ECS000010 | 7 | 1328.00 | 11 |
| 11 | 索愛(ài)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 | 恒基偉業(yè)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 | 多普達(dá)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 | 聯(lián)通100元充值卡 | 15 | 0 | ECS000027 | 2 | 95.00 | 0 |
| 28 | 聯(lián)通50元充值卡 | 15 | 0 | ECS000028 | 0 | 45.00 | 0 |
| 29 | 移動(dòng)100元充值卡 | 14 | 0 | ECS000029 | 0 | 90.00 | 0 |
| 30 | 移動(dòng)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 |
+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+
三 查詢(xún)知識(shí)
注:以下查詢(xún)基于ecshop網(wǎng)站的商品表(ecs_goods)
在練習(xí)時(shí)可以只取部分列,方便查看.
1: 基礎(chǔ)查詢(xún) where的練習(xí):
查出滿足以下條件的商品
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:本店價(jià)格高于3000元的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods
where shop_price >3000;
1.4:本店價(jià)格低于或等于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<=價(jià)格<=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分別實(shí)現(xiàn))
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:取出價(jià)格大于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個(gè)欄目下面價(jià)格<1000或>3000,并且點(diǎn)擊量>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個(gè)欄目下面的商品(注意:1欄目下面沒(méi)商品,但其子欄目下有)
select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods
where cat_id in (2,3,4,5);
1.11:取出名字以"諾基亞"開(kāi)頭的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goods where goods_name like '諾基亞%';
1.12:取出名字為"諾基亞Nxx"的手機(jī)
select goods_id,cat_id,goods_name,shop_price from ecs_goods
where goods_name like '諾基亞N__';
1.13:取出名字不以"諾基亞"開(kāi)頭的商品
select goods_id,cat_id,goods_name,shop_price from ecs_goos
where goods_name not like '諾基亞%';
1.14:取出第3個(gè)欄目下面價(jià)格在1000到3000之間,并且點(diǎn)擊量>5 "諾基亞"開(kāi)頭的系列商品
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 '諾基亞%';
1.15 一道面試題
有如下表和數(shù)據(jù),查出num>=20 and num<=39的數(shù)字,
并且,把num值處于[20,29]之間,顯示為20
num值處于[30,39]之間的,顯示30
mian表
+------+
| num |
+------+
| 3 |
| 12 |
| 15 |
| 25 |
| 23 |
| 29 |
| 34 |
| 37 |
| 32 |
| 45 |
| 48 |
| 52 |
+------+
1.16 練習(xí)題:
把good表中商品名為'諾基亞xxxx'的商品,改為'HTCxxxx',
提示:大膽的把列看成變量,參與運(yùn)算,甚至調(diào)用函數(shù)來(lái)處理 .
substring(),concat()
2 分組查詢(xún)group:
2.1:查出最貴的商品的價(jià)格
select max(shop_price) from ecs_goods;
2.2:查出最大(最新)的商品編號(hào)
select max(goods_id) from ecs_goods;
2.3:查出最便宜的商品的價(jià)格
select min(shop_price) from ecs_goods;
2.4:查出最舊(最小)的商品編號(hào)
select min(goods_id) from ecs_goods;
2.5:查詢(xún)?cè)摰晁猩唐返膸?kù)存總量
select sum(goods_number) from ecs_goods;
2.6:查詢(xún)所有商品的平均價(jià)
select avg(shop_price) from ecs_goods;
2.7:查詢(xún)?cè)摰暌还灿卸嗌俜N商品
select count(*) from ecs_goods;
2.8:查詢(xún)每個(gè)欄目下面
最貴商品價(jià)格
最低商品價(jià)格
商品平均價(jià)格
商品庫(kù)存量
商品種類(lèi)
提示:(5個(gè)聚合函數(shù),sum,avg,max,min,count與group綜合運(yùn)用)
select cat_id,max(shop_price) from ecs_goods group by cat_id;
3 having與group綜合運(yùn)用查詢(xún):
3.1:查詢(xún)?cè)摰甑纳唐繁仁袌?chǎng)價(jià)所節(jié)省的價(jià)格
select goods_id,goods_name,market_price-shop_price as j
from ecs_goods ;
3.2:查詢(xún)每個(gè)商品所積壓的貨款(提示:庫(kù)存單價(jià))
select goods_id,goods_name,goods_numbershop_price from ecs_goods
3.3:查詢(xún)?cè)摰攴e壓的總貨款
select sum(goods_number*shop_price) from ecs_goods;
3.4:查詢(xún)?cè)摰昝總€(gè)欄目下面積壓的貨款.
select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id;
3.5:查詢(xún)比市場(chǎng)價(jià)省錢(qián)200元以上的商品及該商品所省的錢(qián)(where和having分別實(shí)現(xiàn))
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:查詢(xún)積壓貨款超過(guò)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綜合練習(xí)題
有如下表及數(shù)據(jù)
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 張三 | 數(shù)學(xué) | 90 |
| 張三 | 語(yǔ)文 | 50 |
| 張三 | 地理 | 40 |
| 李四 | 語(yǔ)文 | 55 |
| 李四 | 政治 | 45 |
| 王五 | 政治 | 30 |
+------+---------+-------+
要求:查詢(xún)出2門(mén)及2門(mén)以上不及格者的平均成績(jī)
一種錯(cuò)誤做法
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)
加上趙六后錯(cuò)誤暴露
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
錯(cuò)誤顯現(xiàn)
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)
正確思路,先查看每個(gè)人的平均成績(jī)
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> # 看每個(gè)人掛科情況
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> #計(jì)算每個(gè)人的掛科科目
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)
同時(shí)計(jì)算每人的平均分
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門(mén)以上的.
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查詢(xún)
4.1:按價(jià)格由高到低排序
select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc;
4.2:按發(fā)布時(shí)間由早到晚排序
select goods_id,goods_name,add_time from ecs_goods order by add_time;
4.3:接欄目由低到高排序,欄目?jī)?nèi)部按價(jià)格由高到低排序
select goods_id,cat_id,goods_name,shop_price from ecs_goods
order by cat_id ,shop_price desc;
4.4:取出價(jià)格最高的前三名商品
select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc limit 3;
4.5:取出點(diǎn)擊量前三名到前5名的商品
select goods_id,goods_name,click_count from ecs_goods order by click_count desc limit 2,3;
5 連接查詢(xún)
5.1:取出所有商品的商品名,欄目名,價(jià)格
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個(gè)欄目下的商品的商品名,欄目名,價(jià)格
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個(gè)欄目下的商品的商品名,欄目名,與品牌名
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: 用友面試題
根據(jù)給出的表結(jié)構(gòu)按要求寫(xiě)出SQL語(yǔ)句但骨。
Match 賽程表
字段名稱(chēng)
字段類(lèi)型
描述
matchID
int
主鍵
hostTeamID
int
主隊(duì)的ID
guestTeamID
int
客隊(duì)的ID
matchResult
varchar(20)
比賽結(jié)果奔缠，如（2:0）
matchTime
date
比賽開(kāi)始時(shí)間
Team 參賽隊(duì)伍表
字段名稱(chēng)
字段類(lèi)型
描述
teamID
int
主鍵
teamName
varchar(20)
隊(duì)伍名稱(chēng)
Match的hostTeamID與guestTeamID都與Team中的teamID關(guān)聯(lián)
查出 2006-6-1 到2006-7-1之間舉行的所有比賽添坊，并且用以下形式列出：
拜仁 2：0 不來(lái)梅 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 | 國(guó)安 |
| 2 | 申花 |
| 3 | 公益聯(lián)隊(duì) |
+------+----------+
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 | 國(guó)安 | 2:0 | 2 | 申花 | 2006-05-21 |
| 2 | 申花 | 1:2 | 3 | 公益聯(lián)隊(duì) | 2006-06-21 |
| 3 | 公益聯(lián)隊(duì) | 2:5 | 1 | 國(guó)安 | 2006-06-25 |
| 2 | 申花 | 3:2 | 1 | 國(guó)安 | 2006-07-21 |
+------+----------+------+------+----------+------------+
4 rows in set (0.00 sec)
6 union查詢(xún)
6.1:把ecs_comment,ecs_feedback兩個(gè)表中的數(shù)據(jù),各取出4列,并把結(jié)果集union成一個(gè)結(jié)果集.
6.2:3期學(xué)員碰到的一道面試題
A表:
+------+------+
| id | num |
+------+------+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
+------+------+
B表:
+------+------+
| id | num |
+------+------+
| b | 5 |
| c | 15 |
| d | 20 |
| e | 99 |
+------+------+
要求查詢(xún)出以下效果:
+------+----------+
| id | num |
+------+----------+
| a | 5 |
| b | 15 |
| c | 30 |
| d | 30 |
| e | 99 |
+------+----------+
create table a (
id char(1),
num int
)engine myisam charset utf8;
insert into a values ('a',5),('b',10),('c',15),('d',10);
create table b (
id char(1),
num int
)engine myisam charset utf8;
insert into b values ('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 |
+------+------+
參考答案:
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 | 30 |
| d | 30 |
| e | 99 |
+------+----------+
5 rows in set (0.00 sec)
7: 子查詢(xún):
7.1:查詢(xún)出最新一行商品(以商品編號(hào)最大為最新,用子查詢(xún)實(shí)現(xiàn))
select goods_id,goods_name from
ecs_goods where goods_id =(select max(goods_id) from ecs_goods);
7.2:查詢(xún)出編號(hào)為19的商品的欄目名稱(chēng)(用左連接查詢(xún)和子查詢(xún)分別)
7.3:用where型子查詢(xún)把ecs_goods表中的每個(gè)欄目下面最新的商品取出來(lái)
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型子查詢(xún)把ecs_goods表中的每個(gè)欄目下面最新的商品取出來(lái)
select * from (select goods_id,cat_id,goods_name from ecs_goods order by goods_id desc) as t group by cat_id;
7.5 用exists型子查詢(xún),查出所有有商品的欄目
select * from category
where exists (select * from goods where goods.cat_id=category.cat_id);
創(chuàng)建觸發(fā)器:
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
