10人小班的基本資料庫操作

第 11 屆 iThome 鐵人賽

DAY 4

自我挑戰組

MySQL 學習筆記系列第 4 篇

11th鐵人賽 mysql

stca

2019-09-20 02:36:09

1583 瀏覽

前言

前面幾天的內容，是對資料庫基本的認識。接下來藉由操作記錄10人資訊的資料庫，來讓我們更瞭解資料庫能做什麼。

練習

由class資料庫備份一份新資料庫為bk1

MariaDB [class]> create table bk1 select * from students;
MariaDB [class]> show tables;
+-----------------+
| Tables_in_class |
+-----------------+
| bk1             |
| students        |
+-----------------+

查特定欄位，輸入欄位名稱後就只會秀出這幾個欄位資料

MariaDB [class]> select cName,cPHONE from bk1;
+--------+------------+
| cName  | cPHONE     |
+--------+------------+
| 簡奉君 | 0922988876 |
| 黃靖輪 | 0918181111 |
| 潘四敬 | 0914530768 |
| 賴勝恩 | 0946820035 |
| 黎楚寧 | 0920981230 |
| 蔡中穎 | 0951983366 |
| 徐佳螢 | 0918123456 |
| 林雨媗 | 0907408965 |
| 林心儀 | 0916456723 |
| 王燕博 | 0918976588 |
+--------+------------+

在指令中，把欄位名稱後加入 as 可以在查詢結果上變更成你要的欄位名稱

ex:原本的cName改成了sname

MariaDB [class]> select cid,cName as sname ,cPHONE from bk1;
+-----+--------+------------+
| cid | sname  | cPHONE     |
+-----+--------+------------+
|  01 | 簡奉君 | 0922988876 |
|  02 | 黃靖輪 | 0918181111 |
|  03 | 潘四敬 | 0914530768 |
|  04 | 賴勝恩 | 0946820035 |
|  05 | 黎楚寧 | 0920981230 |
|  06 | 蔡中穎 | 0951983366 |
|  07 | 徐佳螢 | 0918123456 |
|  08 | 林雨媗 | 0907408965 |
|  09 | 林心儀 | 0916456723 |
|  10 | 王燕博 | 0918976588 |
+-----+--------+------------+

不用加入 as 也能置換但要在原本的欄位名稱後面輸入你想要顯示的欄位

ex:cName後面加入myname即可置換無須再加入as

MariaDB [class]> select cid,cName myname ,cPHONE from bk1;
+-----+--------+------------+
| cid | myname | cPHONE     |
+-----+--------+------------+
|  01 | 簡奉君 | 0922988876 |
|  02 | 黃靖輪 | 0918181111 |
|  03 | 潘四敬 | 0914530768 |
|  04 | 賴勝恩 | 0946820035 |
|  05 | 黎楚寧 | 0920981230 |
|  06 | 蔡中穎 | 0951983366 |
|  07 | 徐佳螢 | 0918123456 |
|  08 | 林雨媗 | 0907408965 |
|  09 | 林心儀 | 0916456723 |
|  10 | 王燕博 | 0918976588 |
+-----+--------+------------+

排序【order by 應用】

MariaDB [class]> select cid,cName myname ,cPHONE from bk1 order by cid;
+-----+--------+------------+
| cid | myname | cPHONE     |
+-----+--------+------------+
|  01 | 簡奉君 | 0922988876 |
|  02 | 黃靖輪 | 0918181111 |
|  03 | 潘四敬 | 0914530768 |
|  04 | 賴勝恩 | 0946820035 |
|  05 | 黎楚寧 | 0920981230 |
|  06 | 蔡中穎 | 0951983366 |
|  07 | 徐佳螢 | 0918123456 |
|  08 | 林雨媗 | 0907408965 |
|  09 | 林心儀 | 0916456723 |
|  10 | 王燕博 | 0918976588 |
+-----+--------+------------+

反向排序【order by.....desc.】

MariaDB [class]> select cid,cName myname ,cPHONE from bk1 order by  cid desc;
+-----+--------+------------+
| cid | myname | cPHONE     |
+-----+--------+------------+
|  10 | 王燕博 | 0918976588 |
|  09 | 林心儀 | 0916456723 |
|  08 | 林雨媗 | 0907408965 |
|  07 | 徐佳螢 | 0918123456 |
|  06 | 蔡中穎 | 0951983366 |
|  05 | 黎楚寧 | 0920981230 |
|  04 | 賴勝恩 | 0946820035 |
|  03 | 潘四敬 | 0914530768 |
|  02 | 黃靖輪 | 0918181111 |
|  01 | 簡奉君 | 0922988876 |
+-----+--------+------------+

用性別排序

MariaDB [class]> select cid,cName myname ,cPHONE,csex from bk1 order by csex;
+-----+--------+------------+------+
| cid | myname | cPHONE     | csex |
+-----+--------+------------+------+
|  01 | 簡奉君 | 0922988876 | F    |
|  09 | 林心儀 | 0916456723 | F    |
|  08 | 林雨媗 | 0907408965 | F    |
|  07 | 徐佳螢 | 0918123456 | F    |
|  05 | 黎楚寧 | 0920981230 | F    |
|  06 | 蔡中穎 | 0951983366 | M    |
|  04 | 賴勝恩 | 0946820035 | M    |
|  03 | 潘四敬 | 0914530768 | M    |
|  02 | 黃靖輪 | 0918181111 | M    |
|  10 | 王燕博 | 0918976588 | M    |
+-----+--------+------------+------+

有條件【where】的排序(只選擇女生)

MariaDB [class]> select cid,cName myname ,cPHONE,csex from bk1
    -> where csex ='f';
+-----+--------+------------+------+
| cid | myname | cPHONE     | csex |
+-----+--------+------------+------+
|  01 | 簡奉君 | 0922988876 | F    |
|  05 | 黎楚寧 | 0920981230 | F    |
|  07 | 徐佳螢 | 0918123456 | F    |
|  08 | 林雨媗 | 0907408965 | F    |
|  09 | 林心儀 | 0916456723 | F    |
+-----+--------+------------+------+

以亂數隨機出現分數【rand()】

MariaDB [class]> update bk1 set score=rand()*101;
Query OK, 10 rows affected (0.051 sec)
Rows matched: 10  Changed: 10  Warnings: 0

MariaDB [class]> select cid ,score from bk1;
+-----+-------+
| cid | score |
+-----+-------+
|  01 |     4 |
|  02 |    77 |
|  03 |    70 |
|  04 |    19 |
|  05 |    85 |
|  06 |    67 |
|  07 |    79 |
|  08 |    92 |
|  09 |    22 |
|  10 |    36 |
+-----+-------+

【floor()應用】往左邊取數值

MariaDB [class]> select floor(100.5);
+--------------+
| floor(100.5) |
+--------------+
|          100 |
+--------------+

MariaDB [class]> select floor(-100.5);
+---------------+
| floor(-100.5) |
+---------------+
|          -101 |
+---------------+
1 row in set (0.001 sec)

【ceil() 應用】往右邊取數值

MariaDB [class]> select ceil(-100.5);
+--------------+
| ceil(-100.5) |
+--------------+
|         -100 |
+--------------+

MariaDB [class]> select ceil(100.5);
+-------------+
| ceil(100.5) |
+-------------+
|         101 |
+-------------+

增加一欄國語成績沒有正負號【unsigned】預設值分數為【default 0】

MariaDB [class]> alter table bk1 add ch int unsigned default 0;
Query OK, 0 rows affected (0.015 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [class]> desc bk1;
+-----------+------------------------------+------+-----+---------+-------+
| Field     | Type                         | Null | Key | Default | Extra |
+-----------+------------------------------+------+-----+---------+-------+
| cID       | tinyint(2) unsigned zerofill | NO   |     | 00      |       |
| cName     | varchar(20)                  | NO   |     | NULL    |       |
| cSex      | enum('F','M')                | NO   |     | F       |       |
| cBirthday | date                         | NO   |     | NULL    |       |
| cEmail    | varchar(100)                 | YES  |     | NULL    |       |
| cPhone    | varchar(50)                  | YES  |     | NULL    |       |
| cAddr     | varchar(255)                 | YES  |     | NULL    |       |
| score     | int(11)                      | YES  |     | 0       |       |
| ch        | int(10) unsigned             | YES  |     | 0       |       |
+-----------+------------------------------+------+-----+---------+-------+

把科目加入分數

MariaDB [class]> update bk1 set ch=floor(rand()*101),
    -> eng=floor(rand()*101),
    -> math=floor(rand()*101);
Query OK, 10 rows affected (0.013 sec)
 
MariaDB [class]> select  cid,ch,eng,math from bk1;
+-----+------+-----+------+
| cid | ch   | eng | math |
+-----+------+-----+------+
|  01 |   13 |  59 |   55 |
|  02 |  100 |  32 |   60 |
|  03 |    5 |  48 |   21 |
|  04 |   63 |  52 |   73 |
|  05 |    4 |   6 |   17 |
|  06 |   70 |  95 |   62 |
|  07 |   28 |  56 |   93 |
|  08 |   96 |   0 |   16 |
|  09 |   79 |  45 |   91 |
|  10 |   15 |   4 |   79 |
+-----+------+-----+------+

將三個欄位加總顯示總分數

MariaDB [class]> select cid,ch,eng,math,ch+eng+math score from bk1;
+-----+------+-----+------+-------+
| cid | ch   | eng | math | score |
+-----+------+-----+------+-------+
|  01 |   13 |  59 |   55 |   127 |
|  02 |  100 |  32 |   60 |   192 |
|  03 |    5 |  48 |   21 |    74 |
|  04 |   63 |  52 |   73 |   188 |
|  05 |    4 |   6 |   17 |    27 |
|  06 |   70 |  95 |   62 |   227 |
|  07 |   28 |  56 |   93 |   177 |
|  08 |   96 |   0 |   16 |   112 |
|  09 |   79 |  45 |   91 |   215 |
|  10 |   15 |   4 |   79 |    98 |
+-----+------+-----+------+-------+

分數平均

MariaDB [class]> select cid,ch,eng,math,ch+eng+math score,(ch+eng+math)/3 avg from bk1;
+-----+------+-----+------+-------+---------+
| cid | ch   | eng | math | score | avg     |
+-----+------+-----+------+-------+---------+
|  01 |   13 |  59 |   55 |   127 | 42.3333 |
|  02 |  100 |  32 |   60 |   192 | 64.0000 |
|  03 |    5 |  48 |   21 |    74 | 24.6667 |
|  04 |   63 |  52 |   73 |   188 | 62.6667 |
|  05 |    4 |   6 |   17 |    27 |  9.0000 |
|  06 |   70 |  95 |   62 |   227 | 75.6667 |
|  07 |   28 |  56 |   93 |   177 | 59.0000 |
|  08 |   96 |   0 |   16 |   112 | 37.3333 |
|  09 |   79 |  45 |   91 |   215 | 71.6667 |
|  10 |   15 |   4 |   79 |    98 | 32.6667 |
+-----+------+-----+------+-------+---------+

全班國語平均

MariaDB [class]> select avg(ch) from bk1;
+---------+
| avg(ch) |
+---------+
| 47.3000 |
+---------+

查詢全班國語成績最高分【max() 應用】

MariaDB [class]> select max(ch) from bk1;
+---------+
| max(ch) |
+---------+
|      99 |
+---------+

查詢國語全班成績最低分【min() 應用】

MariaDB [class]> select min(ch) from bk1;
+---------+
| min(ch) |
+---------+
|      20 |
+---------+

選出男生英文分數【用where條件式】

MariaDB [class]> select  cid,csex,eng from bk1 where csex='m';
+-----+------+-----+
| cid | csex | eng |
+-----+------+-----+
|  02 | M    |  12 |
|  03 | M    |  87 |
|  04 | M    |  95 |
|  06 | M    |  88 |
|  10 | M    |  98 |
+-----+------+-----+

男生英文分數平均

MariaDB [class]> select avg(eng) from bk1 where csex='m';
+----------+
| avg(ebg) |
+----------+
|  46.2000 |
+----------+

數學運算條件練習

//先創建一個表格
MariaDB [class]> desc test1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| f1    | int(11)    | YES  |     | NULL    |       |
| f2    | int(11)    | YES  |     | NULL    |       |
| f3    | varchar(4) | YES  |     | NULL    |       |
| f4    | varchar(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
 
MariaDB [class]> insert into test1 values (12,34,'4ok','ok4');
 
MariaDB [class]> select f1+f2 f12 from test1;
+------+
| f12  |
+------+
|   46 |
+------+

MariaDB [class]> select f1,f2,f1+f2 f12 from test1;
+------+------+------+
| f1   | f2   | f12  |
+------+------+------+
|   12 |   34 |   46 |
+------+------+------+

MariaDB [class]> select f1,f3,f1+f3 f13 from test1;
//f13數值是16
+------+------+------+
| f1   | f3   | f13  |
+------+------+------+
|   12 | 4ok  |   16 |
+------+------+------+

MariaDB [class]> select f1,f4,f1+f4 f14 from test1;
//f14數值是12(由左至右沒找到數字所以為12)
+------+------+------+
| f1   | f4   | f14  |
+------+------+------+
|   12 | ok4  |   12 |
+------+------+------+

字串相加【concat 應用】

MariaDB [class]> select concat(f3,f4)from test1;
+---------------+
| concat(f3,f4) |
+---------------+
| 4okok4        |
+---------------+
1 row in set (0.001 sec)

MariaDB [class]> select concat(f1,f2)from test1;
+---------------+
| concat(f1,f2) |
+---------------+
| 1234          |
+---------------+

千分位符號

MariaDB [class]> select format(123456789.123,3);
+-------------------------+
| format(123456789.123,3) |
+-------------------------+
| 123,456,789.123         |
+-------------------------+

建立群組【group by 應用】

(會找出第一筆找到的男生女生名稱)
MariaDB [class]> select cid, cname,csex from bk1 group by csex;
+-----+--------+------+
| cid | cname  | csex |
+-----+--------+------+
|  01 | 簡奉君 | F    |
|  02 | 黃靖輪 | M    |
+-----+--------+------+

男女生分別有幾個人

MariaDB [class]> select cid, cname,csex,count(*) from bk1 group by csex;
+-----+--------+------+----------+
| cid | cname  | csex | count(*) |
+-----+--------+------+----------+
|  01 | 簡奉君 | F    |        5 |
|  02 | 黃靖輪 | M    |        5 |
+-----+--------+------+----------+

男女生分別國語平均分數

MariaDB [class]> select cid, cname,csex,count(*),avg(ch) from bk1 group by csex;
+-----+--------+------+----------+---------+
| cid | cname  | csex | count(*) | avg(ch) |
+-----+--------+------+----------+---------+
|  01 | 簡奉君 | F    |        5 | 79.0000 |
|  02 | 黃靖輪 | M    |        5 | 33.8000 |
+-----+--------+------+----------+---------+

男女生國語平均跟總分

MariaDB [class]> select cid, cname,csex,count(*),avg(ch),sum(ch) from bk1 group by csex;
+-----+--------+------+----------+---------+---------+
| cid | cname  | csex | count(*) | avg(ch) | sum(ch) |
+-----+--------+------+----------+---------+---------+
|  01 | 簡奉君 | F    |        5 | 79.0000 |     395 |
|  02 | 黃靖輪 | M    |        5 | 33.8000 |     169 |
+-----+--------+------+----------+---------+---------+

選擇生日的欄位

MariaDB [class]> select cbirthday from bk1;
+------------+
| cbirthday  |
+------------+
| 1987-04-04 |
| 1987-07-01 |
| 1987-08-11 |
| 1984-06-20 |
| 1988-02-15 |
| 1987-05-05 |
| 1985-08-30 |
| 1986-12-10 |
| 1988-12-01 |
| 1993-08-10 |
+------------+

找年份//會顯示1999

MariaDB [class]> select year('1999-01-02');
+--------------------+
| year('1999-01-02') |
+--------------------+
|               1999 |
+--------------------+

找月份//會顯示1(月)

MariaDB [class]> select month ('1999-01-02');
+----------------------+
| month ('1999-01-02') |
+----------------------+
|                    1 |
+----------------------+

將生日欄位排序

MariaDB [class]> select cbirthday from bk1 order by cbirthday;
+------------+
| cbirthday  |
+------------+
| 1984-06-20 |
| 1985-08-30 |
| 1986-12-10 |
| 1987-04-04 |
| 1987-05-05 |
| 1987-07-01 |
| 1987-08-11 |
| 1988-02-15 |
| 1988-12-01 |
| 1993-08-10 |
+------------+

將生日年份相同的群組分類

MariaDB [class]> select year(cbirthday),count(*) from bk1 group by year(cbirthday);
//會顯示1984有幾人，1987有幾人這樣
+-----------------+----------+
| year(cbirthday) | count(*) |
+-----------------+----------+
|            1984 |        1 |
|            1985 |        1 |
|            1986 |        1 |
|            1987 |        4 |
|            1988 |        2 |
|            1993 |        1 |
+-----------------+----------+

群組分類完後裡面學生個別的分數平均跟總和

MariaDB [class]> select year(cbirthday),count(*),sum(ch),avg(ch) from bk1 group by year(cbirthday);
+-----------------+----------+---------+---------+
| year(cbirthday) | count(*) | sum(ch) | avg(ch) |
+-----------------+----------+---------+---------+
|            1984 |        1 |      57 | 57.0000 |
|            1985 |        1 |      43 | 43.0000 |
|            1986 |        1 |      99 | 99.0000 |
|            1987 |        4 |     193 | 48.2500 |
|            1988 |        2 |     157 | 78.5000 |
|            1993 |        1 |      15 | 15.0000 |
+-----------------+----------+---------+---------+

在群組內我要篩選女生

MariaDB [class]> select year(cbirthday),count(*),sum(ch),avg(ch), csex from bk1 group by year(cbirthday) having csex='f';
+-----------------+----------+---------+---------+------+
| year(cbirthday) | count(*) | sum(ch) | avg(ch) | csex |
+-----------------+----------+---------+---------+------+
|            1985 |        1 |      43 | 43.0000 | F    |
|            1986 |        1 |      99 | 99.0000 | F    |
|            1987 |        4 |     193 | 48.2500 | F    |
|            1988 |        2 |     157 | 78.5000 | F    |
+-----------------+----------+---------+---------+------+

依照年份排列，並列出國文平均及總分，並顯示出性別

MariaDB [class]> select year(cbirthday),count(*),sum(ch),avg(ch), csex from bk1 group by year(cbirthday);
+-----------------+----------+---------+---------+------+
| year(cbirthday) | count(*) | sum(ch) | avg(ch) | csex |
+-----------------+----------+---------+---------+------+
|            1984 |        1 |      57 | 57.0000 | M    |
|            1985 |        1 |      43 | 43.0000 | F    |
|            1986 |        1 |      99 | 99.0000 | F    |
|            1987 |        4 |     193 | 48.2500 | F    |
|            1988 |        2 |     157 | 78.5000 | F    |
|            1993 |        1 |      15 | 15.0000 | M    |
+-----------------+----------+---------+---------+------+

找出群組裡面找出成績大於60的條件

MariaDB [class]> select year(cbirthday),count(*),sum(ch),avg(ch), csex from bk1 group by year(cbirthday) having avg(ch)>=60;
+-----------------+----------+---------+---------+------+
| year(cbirthday) | count(*) | sum(ch) | avg(ch) | csex |
+-----------------+----------+---------+---------+------+
|            1986 |        1 |      99 | 99.0000 | F    |
|            1988 |        2 |     157 | 78.5000 | F    |
+-----------------+----------+---------+---------+------+

群組裡面篩選為男生的條件

MariaDB [class]> select year(cbirthday),count(*),sum(ch),avg(ch), csex from bk1 where csex='m' group by year(cbirthday);
+-----------------+----------+---------+---------+------+
| year(cbirthday) | count(*) | sum(ch) | avg(ch) | csex |
+-----------------+----------+---------+---------+------+
|            1984 |        1 |      57 | 57.0000 | M    |
|            1987 |        3 |      97 | 32.3333 | M    |
|            1993 |        1 |      15 | 15.0000 | M    |
+-----------------+----------+---------+---------+------+

分頁資料【limit 應用】

MariaDB [class]> select cname from bk1;
+--------+
| cname  |
+--------+
| 簡奉君 |
| 黃靖輪 |
| 潘四敬 |
| 賴勝恩 |
| 黎楚寧 |
| 蔡中穎 |
| 徐佳螢 |
| 林雨媗 |
| 林心儀 |
| 王燕博 |
+--------+



MariaDB [class]> select cname from bk1 limit 3;

//取三筆資料
+--------+
| cname  |
+--------+
| 簡奉君 |
| 黃靖輪 |
| 潘四敬 |
+--------+

MariaDB [class]> select cname from bk1 limit 0,3;
//取前三筆資料
+--------+
| cname  |
+--------+
| 簡奉君 |
| 黃靖輪 |
| 潘四敬 |
+--------+

MariaDB [class]> select cname from bk1 limit 3,3;
//取第三筆之後三筆資料
+--------+
| cname  |
+--------+
| 賴勝恩 |
| 黎楚寧 |
| 蔡中穎 |
+--------+

MariaDB [class]> select cname from bk1 limit 6,3;
//取第六筆之後三筆資料
+--------+
| cname  |
+--------+
| 徐佳螢 |
| 林雨媗 |
| 林心儀 |
+--------+

MariaDB [class]> select cname from bk1 limit 9,3;
//取第九筆之後三筆資料
+--------+
| cname  |
+--------+
| 王燕博 |
+--------+

列出名字,且國語分數要在六十分以上的學生

MariaDB [class]> select cname , ch from bk1 where ch >= 60;
+--------+------+
| cname  | ch   |
+--------+------+
| 簡奉君 |   96 |
| 黃靖輪 |   63 |
| 黎楚寧 |   95 |
| 林雨媗 |   99 |
| 林心儀 |   62 |
+--------+------+

列出國語分數大於等於60分不等於62分

MariaDB [class]> select cname , ch from bk1 where ch >= 60 and ch<>62;
+--------+------+
| cname  | ch   |
+--------+------+
| 簡奉君 |   96 |
| 黃靖輪 |   63 |
| 黎楚寧 |   95 |
| 林雨媗 |   99 |
+--------+------+

列出分數介於60~100分中間的【between 應用】

MariaDB [class]> select cname , ch from bk1 where ch between 60 and 100;
+--------+------+
| cname  | ch   |
+--------+------+
| 簡奉君 |   96 |
| 黃靖輪 |   63 |
| 黎楚寧 |   95 |
| 林雨媗 |   99 |
| 林心儀 |   62 |
+--------+------+

列出分數不是在60~100分之間的學生

MariaDB [class]> select cname , ch from bk1 where ch not between 60 and 100;
+--------+------+
| cname  | ch   |
+--------+------+
| 潘四敬 |   29 |
| 賴勝恩 |   57 |
| 蔡中穎 |    5 |
| 徐佳螢 |   43 |
| 王燕博 |   15 |
+--------+------+

列出分數為63或43或58的學生【or 應用】

MariaDB [class]> select cname , ch from bk1  where ch=63 or ch=43 or ch=58;
+--------+------+
| cname  | ch   |
+--------+------+
| 黃靖輪 |   63 |
| 徐佳螢 |   43 |
+--------+------+

MariaDB [class]> select cname , ch from bk1  where ch in (63,43,58);
+--------+------+
| cname  | ch   |
+--------+------+
| 黃靖輪 |   63 |
| 徐佳螢 |   43 |
+--------+------+

利用地址欄位練習

MariaDB [class]> select caddr from bk1;
+------------------------+
| caddr                  |
+------------------------+
| 台北市濟洲北路12號     |
| 台北市敦化南路93號5樓  |
| 台北市中央路201號7樓   |
| 台北市建國路177號6樓   |
| 台北市忠孝東路520號6樓 |
| 台北市三民路1巷10號    |
| 台北市仁愛路100號      |
| 台北市民族路204號      |
| 台北市建國北路10號     |
| 台北市北環路2巷80號    |
+------------------------+

找出資料內含有"北路"的【like 應用】

MariaDB [class]> select caddr from bk1 where caddr like'%北路%';
+--------------------+
| caddr              |
+--------------------+
| 台北市濟洲北路12號 |
| 台北市建國北路10號 |
+--------------------+

MariaDB [class]> select caddr from bk1 where caddr like'%建國路%';
+----------------------+
| caddr                |
+----------------------+
| 台北市建國路177號6樓 |
+----------------------+

MariaDB [class]> select caddr from bk1 where caddr like'%建國%路%';
+----------------------+
| caddr                |
+----------------------+
| 台北市建國路177號6樓 |
| 台北市建國北路10號   |
+----------------------+

MariaDB [class]> select caddr from bk1 where caddr like'%6樓';;;';
+------------------------+
| caddr                  |
+------------------------+
| 台北市建國路177號6樓   |
| 台北市忠孝東路520號6樓 |
+------------------------+

MySQL 基本指令運用

資料庫延伸操作

系列文

MySQL 學習筆記共 8 篇

RSS系列文訂閱系列文

4 人訂閱

完整目錄

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

尚未有邦友留言

立即登入留言

參賽組數

1123 組

團體組數

52 組

累計文章數

23096 篇

完賽人數

656 人

15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css react 程式設計 vue.js

IT邦幫忙

MySQL 學習筆記系列 第 4 篇

10人小班的基本資料庫操作

前言

練習

由class資料庫備份一份新資料庫為bk1

查特定欄位，輸入欄位名稱後 就只會秀出這幾個欄位資料

在指令中，把欄位名稱後加入 as 可以在查詢結果上變更成你要的欄位名稱

不用加入 as 也能置換 但要在原本的欄位名稱後面輸入你想要顯示的欄位

排序 【order by 應用】

反向排序 【order by.....desc.】