萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> mysql數據庫入門教程之學習筆記

mysql數據庫入門教程之學習筆記

這是自己在學習mysql數據庫開發應用時的一些常用的語句總結,也算是自己的學習筆記吧,下面拿出來記錄一下,有需要學習的朋友可參考一下。


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.5,    select     查詢 

  

  (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個欄目下面價格在1000到3000之間,並且點擊量>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 having與group綜合運用查詢: 

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元以上的商品及該商品所省的錢(where和having分別實現) 

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-1 到2006-7-1之間舉行的所有比賽,並且用以下形式列出: 

拜仁  2:0 不來梅 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

copyright © 萬盛學電腦網 all rights reserved