零基礎學習MySQL資料庫—3

LiuWang_1122發表於2020-04-05
MySql資料庫

一、select 查詢

1.1 select 的簡單使用

語法：

select [distinct] *| {column1,column2,...} from tbl_name;

建立一張學生表來測試：

create table student (
id int not null default 1,
name varchar(20) not null default '',
chinese float not null default 0.0 comment '語文成績',
english float not null default 0.0 comment '英語成績',
math float not null default 0.0 comment '數學成績'
);

插入資料：

insert into student values(1, '李濤', 89,78, 90);
insert into student values(2, '唐僧', 67,98, 56);
insert into student values(3, '孫悟空', 87,78, 77);
insert into student values(4, '老妖婆', 88,98, 90);
insert into student values(5, '紅孩兒', 82,84, 67);
insert into student values(6, '如來佛祖', 55,85, 45);
insert into student values(7, '菩薩', 75,65, 30);

可以指定查詢哪些列，比如：查詢id，姓名，數學成績

mysql> select id, name,math from student;
+----+----------+------+
| id | name     | math |
+----+----------+------+
|  1 | 李濤     |   90 |
|  2 | 唐僧     |   56 |
|  3 | 孫悟空   |   77 |
|  4 | 老妖婆   |   90 |
|  5 | 紅孩兒   |   67 |
|  6 | 如來佛祖 |   45 |
|  7 | 菩薩     |   30 |
+----+----------+------+
7 rows in set (0.00 sec)
  • 號表示查詢所有列（星號效率很低，用哪些欄位就取哪些欄位）：
mysql> select * from student;
+----+----------+---------+---------+------+
| id | name     | chinese | english | math |
+----+----------+---------+---------+------+
|  1 | 李濤     |      89 |      78 |   90 |
|  2 | 唐僧     |      67 |      98 |   56 |
|  3 | 孫悟空   |      87 |      78 |   77 |
|  4 | 老妖婆   |      88 |      98 |   90 |
|  5 | 紅孩兒   |      82 |      84 |   67 |
|  6 | 如來佛祖 |      55 |      85 |   45 |
|  7 | 菩薩     |      75 |      65 |   30 |
+----+----------+---------+---------+------+
7 rows in set (0.00 sec)

distinct 如果結果中有完全相同的行，就去除重複行

mysql> select distinct math from student;
+------+
| math |
+------+
|   90 |
|   56 |
|   77 |
|   67 |
|   45 |
|   30 |
+------+
6 rows in set (0.00 sec)
  • 在select語句中可以使用表示式對查詢的列進行運算
  • select語句中可以使用as起別名

語法：

select column as 別名from 表；

練習：
1.在所有學生分數上加上10分（查詢所有學生的總分再加10分）：

mysql> select name, chinese+english+math+10 as total from student;
+----------+-------+
| name     | total |
+----------+-------+
| 李濤     |   267 |
| 唐僧     |   231 |
| 孫悟空   |   252 |
| 老妖婆   |   286 |
| 紅孩兒   |   243 |
| 如來佛祖 |   195 |
| 菩薩     |   180 |
+----------+-------+
7 rows in set (0.00 sec)

2.將所有姓唐的學生成績增加60%（查詢總成績增加60%）：

mysql> select name, (chinese+english+math)*1.6 as total from student where name like '唐%';
+------+-------+
| name | total |
+------+-------+
| 唐僧 | 353.6 |
+------+-------+
1 row in set (0.00 sec)

1.2 select 的 where 子句

使用 where 子句，進行查詢過過濾。
在 where 子句中經常使用的運算子：

比較運算子：

>、<、<=、>=、=、<>、!= 大於、小於、小於等於、大於等於、等於、不等於、不等於
between … and … 顯示在某一區間的值
in() 顯示在 in 列表中的值，如：in(100， 200)
like ” 模糊查詢
not like ’ ‘ 模糊查詢
is null 判斷是否為空

邏輯運算子：

and 多個條件同時成立
or 多個條件任一成立
not 不成立，如：where not(math > 90)

示例：查詢姓李的學生的成績

mysql> select * from student where name like '李%';
+----+------+---------+---------+------+
| id | name | chinese | english | math |
+----+------+---------+---------+------+
|  1 | 李濤 |      89 |      78 |   90 |
+----+------+---------+---------+------+
1 row in set (0.00 sec)

查詢英語成績大於90分的同學：

mysql> select * from student where english > 90;
+----+--------+---------+---------+------+
| id | name   | chinese | english | math |
+----+--------+---------+---------+------+
|  2 | 唐僧   |      67 |      98 |   56 |
|  4 | 老妖婆 |      88 |      98 |   90 |
+----+--------+---------+---------+------+
2 rows in set (0.00 sec)

查詢總分大於200分的同學：

mysql> select id, name, math+english+chinese as 'total' 
                from student where math+english+chinese>200;
+----+--------+-------+
| id | name   | total |
+----+--------+-------+
|  1 | 李濤   |   257 |
|  2 | 唐僧   |   221 |
|  3 | 孫悟空 |   242 |
|  4 | 老妖婆 |   276 |
|  5 | 紅孩兒 |   233 |
+----+--------+-------+
5 rows in set (0.00 sec)

查詢姓李並且 id 大於 10 的學生：

mysql> select * from student where name like '李%' and id > 10;
Empty set (0.00 sec)

查詢英語成績大於語文成績的學生：

mysql> select * from student where english > chinese;
+----+----------+---------+---------+------+
| id | name     | chinese | english | math |
+----+----------+---------+---------+------+
|  2 | 唐僧     |      67 |      98 |   56 |
|  4 | 老妖婆   |      88 |      98 |   90 |
|  5 | 紅孩兒   |      82 |      84 |   67 |
|  6 | 如來佛祖 |      55 |      85 |   45 |
+----+----------+---------+---------+------+
4 rows in set (0.00 sec)

查詢總分大於200分並且數學成績小於語文成績的姓唐的學生：

mysql> select * from student where (math+english+chinese) > 200 
                            and math < chinese and name like '唐%';
+----+------+---------+---------+------+
| id | name | chinese | english | math |
+----+------+---------+---------+------+
|  2 | 唐僧 |      67 |      98 |   56 |
+----+------+---------+---------+------+
1 row in set (0.00 sec)

查詢英語成績在 80 ~ 90 之間的學生：

以下兩種方法等價：
mysql> select * from student where english between 80 and 90;   -- between...and...方法是閉區間

mysql> select * from student where english>=80 and english<=90;

+----+----------+---------+---------+------+
| id | name     | chinese | english | math |
+----+----------+---------+---------+------+
|  5 | 紅孩兒   |      82 |      84 |   67 |
|  6 | 如來佛祖 |      55 |      85 |   45 |
+----+----------+---------+---------+------+
2 rows in set (0.00 sec)

查詢數學成績為 89，90，91 的學生：

兩種方法：
mysql> select * from student where math=89 or math=90 or math=91; 

mysql> select * from student where math in(89,90,91); 

+----+--------+---------+---------+------+
| id | name   | chinese | english | math |
+----+--------+---------+---------+------+
|  1 | 李濤   |      89 |      78 |   90 |
|  4 | 老妖婆 |      88 |      98 |   90 |
+----+--------+---------+---------+------+
2 rows in set (0.00 sec)

1.3 select 的 order by 子句

order by 子句排序查詢結果，語法：

select column1,column2,... from table order by column asc|desc,...;
  • order by 指定排序的列，排序的列可以使表中的列名，也可以是select語句後指定的別名
  • asc升序(預設)，desc降序
  • order by 子句應該位於select語句的結尾

示例：
對數學成績進行排序：

mysql> select * from student order by math;
+----+----------+---------+---------+------+
| id | name     | chinese | english | math |
+----+----------+---------+---------+------+
|  7 | 菩薩     |      75 |      65 |   30 |
|  6 | 如來佛祖 |      55 |      85 |   45 |
|  2 | 唐僧     |      67 |      98 |   56 |
|  5 | 紅孩兒   |      82 |      84 |   67 |
|  3 | 孫悟空   |      87 |      78 |   77 |
|  1 | 李濤     |      89 |      78 |   90 |
|  4 | 老妖婆   |      88 |      98 |   90 |
+----+----------+---------+---------+------+
7 rows in set (0.00 sec)

由以上結果可知，沒有指定升序還是降序，預設是升序。

對總分進行排序後，按從高到低排序輸出：

mysql> select id, name, math+english+chinese as total from student order by total desc;
+----+----------+-------+
| id | name     | total |
+----+----------+-------+
|  4 | 老妖婆   |   276 |
|  1 | 李濤     |   257 |
|  3 | 孫悟空   |   242 |
|  5 | 紅孩兒   |   233 |
|  2 | 唐僧     |   221 |
|  6 | 如來佛祖 |   185 |
|  7 | 菩薩     |   170 |
+----+----------+-------+
7 rows in set (0.00 sec)

對行李的學生按成績進行從低到高排序，（因為表中只有一個姓李的學生，所以我插入一條記錄）：

mysql> insert into student values(8,'李雅',79,31,56);

mysql> select id, name, math+english+chinese as total from student where name like '李%' order by total;
+----+------+-------+
| id | name | total |
+----+------+-------+
|  8 | 李雅 |   166 |
|  1 | 李濤 |   257 |
+----+------+-------+
2 rows in set (0.00 sec)

1.4 count

count(列名) 返回某一列，行的總數，語法：

select count(*)|count(列名) from tbl_name where condition;

示例：
統計一個班有多少名學生：

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

統計數學成績大於等於90的學生有多少人：

mysql> select count(*) from student where math>=90;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

統計總分大於250的人數有多少：

mysql> select count(*) from student where math+english+chinese>250;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

說明：count(*)會統計一共的記錄數，count(列名)會排除為null的情況

1.5 sum

sum 函式返回滿足 where 條件的行的和。
語法：

select sum(列名）{，sum(列名)...} from tbl_name [where condition]

示例：統計一個班級數學總成績：

mysql> select sum(math) from student;
+-----------+
| sum(math) |
+-----------+
|       511 |
+-----------+
1 row in set (0.01 sec)

統計一個班級語文，英語，數學各科的總成績：

mysql> select sum(chinese),sum(math),sum(english) from student;
+--------------+-----------+--------------+
| sum(chinese) | sum(math) | sum(english) |
+--------------+-----------+--------------+
|          622 |       511 |          617 |
+--------------+-----------+--------------+
1 row in set (0.00 sec)

統計一個班總成績之和：

mysql> select sum(chinese+english+math) from student;
+---------------------------+
| sum(chinese+english+math) |
+---------------------------+
|                      1750 |
+---------------------------+
1 row in set (0.00 sec)

統計一個班語文成績平均分：

mysql> select sum(chinese)/count(chinese) from student;
+-----------------------------+
| sum(chinese)/count(chinese) |
+-----------------------------+
|                       77.75 |
+-----------------------------+
1 row in set (0.00 sec)

注意：sum僅對數值起作用，否則結果無意義。

1.6 avg

avg函式返回滿足 where 條件的一列的平均值：

select avg(列名) [,avg(列名),...] from tbl_name [where condition];

示例：求一個班級的數學平均分

mysql> select avg(math) from student;
+-----------+
| avg(math) |
+-----------+
|    63.875 |
+-----------+
1 row in set (0.00 sec)

求一個班級總分的平均分：

mysql> select avg(chinese+english+math) from student;
+---------------------------+
| avg(chinese+english+math) |
+---------------------------+
|                    218.75 |
+---------------------------+
1 row in set (0.00 sec)

1.7 max/min

max/min函式返回滿足where條件的一列的最大/最小值。

select max(列名) from tbl_name [where condition]

示例：求班級中總分的最高分與最低分

mysql> select max(chinese+math+english),min(chinese+math+english) from student;
+---------------------------+---------------------------+
| max(chinese+math+english) | min(chinese+math+english) |
+---------------------------+---------------------------+
|                       276 |                       166 |
+---------------------------+---------------------------+
1 row in set (0.00 sec)

1.8 group by 子句的使用

group by 子句對列進行分組。

select column1, column2, .. from table group by column;

為了講清楚分組，建立一個僱員資訊表 密碼：73k8（來自oracle 9i的經典測試表）
1. EMP員工表
2. DEPT部門表
3. SALGRADE工資等級表

示例：顯示每個部門的平均工資和最高工資

mysql> select deptno,avg(sal),max(sal) from EMP group by deptno;
+--------+-------------+----------+
| deptno | avg(sal)    | max(sal) |
+--------+-------------+----------+
|     10 | 2916.666667 |  5000.00 |
|     20 | 2175.000000 |  3000.00 |
|     30 | 1566.666667 |  2850.00 |
+--------+-------------+----------+
3 rows in set (0.01 sec)

顯示每個部門的每種崗位的平均工資和最低工資：

mysql> select avg(sal),min(sal),job,deptno from EMP group by deptno,job;
+-------------+----------+-----------+--------+
| avg(sal)    | min(sal) | job       | deptno |
+-------------+----------+-----------+--------+
| 1300.000000 |  1300.00 | CLERK     |     10 |
| 2450.000000 |  2450.00 | MANAGER   |     10 |
| 5000.000000 |  5000.00 | PRESIDENT |     10 |
| 3000.000000 |  3000.00 | ANALYST   |     20 |
|  950.000000 |   800.00 | CLERK     |     20 |
| 2975.000000 |  2975.00 | MANAGER   |     20 |
|  950.000000 |   950.00 | CLERK     |     30 |
| 2850.000000 |  2850.00 | MANAGER   |     30 |
| 1400.000000 |  1250.00 | SALESMAN  |     30 |
+-------------+----------+-----------+--------+
9 rows in set (0.00 sec)

說明：首先按照deptno分組，然後各組再按照job進行分組。

二、函式

2.1 常用日期函式

current_date() 當前日期
current_time() 當前時間
current_timestamp() 當前時間戳
date(datetime) 返回 datetime 的日期部分
date_add(date2, interval d_value d_type) 在 date2 中加上日期或時間
date_sub(date2, interval d_value d_type) 在 date2 上減去一個時間
datediff(date1, date2) 兩個日期差（結果是相差的天數）
now() 當前時間

獲取當前的年月日：

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2018-08-10     |
+----------------+
1 row in set (0.00 sec)

獲取當前時間（時分秒）：

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 16:33:51       |
+----------------+
1 row in set (0.00 sec)

獲取當前時間戳：

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2018-08-10 16:34:25 |
+---------------------+
1 row in set (0.00 sec)

在日期的基礎上加日期：

mysql> select date_add('2018-08-10', interval 36 day);
+-----------------------------------------+
| date_add('2018-08-10', interval 36 day) |
+-----------------------------------------+
| 2018-09-15                              |
+-----------------------------------------+
1 row in set (0.00 sec)

在日期的基礎上減去時間：

mysql> select date_sub('2018-08-10', interval 36 day);
+-----------------------------------------+
| date_sub('2018-08-10', interval 36 day) |
+-----------------------------------------+
| 2018-07-05                              |
+-----------------------------------------+
1 row in set (0.00 sec)

計算兩個日期之間相差多少天：

mysql> select datediff(now(), '2018-09-01');
+-------------------------------+
| datediff(now(), '2018-09-01') |
+-------------------------------+
|                           -22 |
+-------------------------------+
1 row in set (0.00 sec)

示例1：建立一張表，記錄生日：

mysql> create table t_birthday (
    -> id int primary key auto_increment,
    -> birthday date
    -> );

新增當前日期：

mysql> insert into t_birthday(birthday) values(current_date());
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_birthday;
+----+------------+
| id | birthday   |
+----+------------+
|  1 | 2018-08-10 |
+----+------------+
1 row in set (0.00 sec)

示例2：建立一個留言表

mysql> create table msg (
    -> id int primary key auto_increment,
    -> content varchar(30) not null,
    -> sendtime datetime
    -> );
Query OK, 0 rows affected (0.06 sec)

插入資料：

mysql> insert into msg(content,sendtime) values('hello1', now()); 
Query OK, 1 row affected (0.01 sec)

mysql> insert into msg(content,sendtime) values('hello2', now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from msg;
+----+---------+---------------------+
| id | content | sendtime            |
+----+---------+---------------------+
|  1 | hello1  | 2018-08-10 16:43:55 |
|  2 | hello2  | 2018-08-10 16:44:09 |
+----+---------+---------------------+
2 rows in set (0.00 sec)

顯示所有留言資訊，釋出日期只顯示日期，不用顯示時間：

mysql> select content,date(sendtime) from msg;
+---------+----------------+
| content | date(sendtime) |
+---------+----------------+
| hello1  | 2018-08-10     |
| hello2  | 2018-08-10     |
+---------+----------------+
2 rows in set (0.00 sec)

查詢在十分鐘內釋出的留言：

mysql> select * from msg where date_add(sendtime, interval 10 minute) > now();
+----+---------+---------------------+
| id | content | sendtime            |
+----+---------+---------------------+
|  1 | hello1  | 2018-08-10 16:43:55 |
|  2 | hello2  | 2018-08-10 16:44:09 |
+----+---------+---------------------+
2 rows in set (0.00 sec)

日期函式使用細節：
date_ add()和date_ sub()中的interval後面可以使year minute second day。
datediff(date1, date2)得到的是天數，二期是date1-date2的天數，因此可以使負數。

2.2 字串函式

charset(str) 返回字串字符集
concat(string [, …]) 連線字串
instr(string, substring) 返回 substring 在 string 中出現的位置，沒有就返回0
ucase(string) 轉換成大寫
lcase(string) 轉換成小寫
left(string, length) 從 string 的左邊起取 length 個字元
length(string) 返回 string 的長度(位元組數)
replace(str, search_str, replace_str) 在 str 中用 replace_str 替換 search_str
strcmp(string1, string2) 逐字元比較兩字元的大小
substring(str, position [, length]) 從 str 的下標為 position 開始，取 length 個字元， 如果沒有 length 就取到 str 末尾
ltrim(string) rtrim(string) trim(string) 去除前空格或者後空格

示例：
獲取 emp 表中的 ename 列的字符集：

mysql> select charset(ename) from EMP;
+----------------+
| charset(ename) |
+----------------+
| utf8           |
+----------------+

顯示 student 表中的資訊，顯示格式：“XXX的語文成績是XXX分，數學XXX分，英語XXX分”：

mysql> select concat(name,'的語文是',chinese,'分, 數學是', math, '分, 英語是', english, '分')
    ->  as '分數' from student;
+----------------------------------------------+
| 分數                                         |
+----------------------------------------------+
| 李濤的語文是89分, 數學是90分, 英語是78分        |
| 唐僧的語文是67分, 數學是56分, 英語是98分        |
| 孫悟空的語文是87分, 數學是77分, 英語是78分      |
| 老妖婆的語文是88分, 數學是90分, 英語是98分      |
| 紅孩兒的語文是82分, 數學是67分, 英語是84分      |
| 如來佛祖的語文是55分, 數學是45分, 英語是85分    |
| 菩薩的語文是75分, 數學是30分, 英語是65分        |
| 李雅的語文是79分, 數學是56分, 英語是31分        |
+----------------------------------------------+
8 rows in set (0.00 sec)

求學生表中學生姓名佔用的位元組數：

mysql> select length(name), name from student;
+--------------+----------+
| length(name) | name     |
+--------------+----------+
|            6 | 李濤     |
|            6 | 唐僧     |
|            9 | 孫悟空   |
|            9 | 老妖婆   |
|            9 | 紅孩兒   |
|           12 | 如來佛祖 |
|            6 | 菩薩     |
|            6 | 李雅     |
+--------------+----------+
8 rows in set (0.00 sec)

將 EMP 表中所有的名字中有 S 的替換成 ‘上海’：

mysql> select replace(ename, 'S', '上海') , ename from EMP;
+-----------------------------+--------+
| replace(ename, 'S', '上海') | ename  |
+-----------------------------+--------+
| 上海MITH                    | SMITH  |
| ALLEN                       | ALLEN  |
| WARD                        | WARD   |
| JONE上海                    | JONES  |
| MARTIN                      | MARTIN |
| BLAKE                       | BLAKE  |
| CLARK                       | CLARK  |
| 上海COTT                    | SCOTT  |
| KING                        | KING   |
| TURNER                      | TURNER |
| ADAM上海                    | ADAMS  |
| JAME上海                    | JAMES  |
| FORD                        | FORD   |
| MILLER                      | MILLER |
+-----------------------------+--------+
14 rows in set (0.00 sec)

擷取EMP表中ename欄位的第二個到第三個字元：

mysql> select substring(ename, 2, 2), ename from EMP;
+------------------------+--------+
| substring(ename, 2, 2) | ename  |
+------------------------+--------+
| MI                     | SMITH  |
| LL                     | ALLEN  |
| AR                     | WARD   |
| ON                     | JONES  |
| AR                     | MARTIN |
| LA                     | BLAKE  |
| LA                     | CLARK  |
| CO                     | SCOTT  |
| IN                     | KING   |
| UR                     | TURNER |
| DA                     | ADAMS  |
| AM                     | JAMES  |
| OR                     | FORD   |
| IL                     | MILLER |
+------------------------+--------+
14 rows in set (0.00 sec)

以首字母小寫的方式顯示所有員工的姓名：

mysql> select concat(lcase(substring(ename, 1, 1)), substring(ename, 2)) from EMP;
+------------------------------------------------------------+
| concat(lcase(substring(ename, 1, 1)), substring(ename, 2)) |
+------------------------------------------------------------+
| sMITH                                                      |
| aLLEN                                                      |
| wARD                                                       |
| jONES                                                      |
| mARTIN                                                     |
| bLAKE                                                      |
| cLARK                                                      |
| sCOTT                                                      |
| kING                                                       |
| tURNER                                                     |
| aDAMS                                                      |
| jAMES                                                      |
| fORD                                                       |
| mILLER                                                     |
+------------------------------------------------------------+
14 rows in set (0.00 sec)

2.3 數學函式

abs(number) 絕對值
bin(decimal_num) 十進位制轉二進位制
ceiling(number) 向上取整
conv(number, from_base, to_base) 進位制轉換
floor(number) 向下取整
format(number, decimal_places) 保留小數位數
hex(decimalNumber) 轉換成 16 進位制數
rand() 返回隨機數浮點值，範圍 0 到 1， [0, 1]
mod(numerator, denominator) 餘數

絕對值：

mysql> select abs(-100.23);
+--------------+
| abs(-100.23) |
+--------------+
|       100.23 |
+--------------+
1 row in set (0.01 sec)

向上取整：

mysql> select ceiling(23.03);
+----------------+
| ceiling(23.03) |
+----------------+
|             24 |
+----------------+
1 row in set (0.00 sec)

向下取整：

mysql> select floor(23.99);
+--------------+
| floor(23.99) |
+--------------+
|           23 |
+--------------+
1 row in set (0.00 sec)

保留2位小數位數（小數四捨五入)：

mysql> select format(12.3456, 2);
+--------------------+
| format(12.3456, 2) |
+--------------------+
| 12.35              |
+--------------------+
1 row in set (0.00 sec)

產生隨機數[0,1]

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.6257614471258236 |
+--------------------+
1 row in set (0.00 sec)

2.4 其他常用函式

  • user() ：查詢當前使用者
  • md5 (str) ：可以對一個字串進行 md5 加密，加密後得到一個 32 1為字串

建立一張表，存使用者名稱和密碼：

mysql> create table user(name varchar(30), passwd char(32));

插入資料，密碼用md5加密：

mysql> select * from user;
+----------+----------------------------------+
| name     | passwd                           |
+----------+----------------------------------+
| zhangsan | e10adc3949ba59abbe56e057f20f883e |
+----------+----------------------------------+
1 row in set (0.00 sec)

database() 顯示當前正在使用的資料庫：

mysql> select database();
+------------+
| database() |
+------------+
| mytest     |
+------------+

password()函式，對mysql使用者加密
ifnull（val1，val2）如果val1位null，返回val2，否則返回val1的值

相關文章