數據庫day2_partone

DQL數據查詢語句的學習

數據庫執行DQL語言不會對數據庫中的數據發生任何改變，而是讓數據庫發送查詢結果到用戶端(查詢返回的結果其實是一張虛擬表)

語法:
SELECT 列名 FROM 表名【WHERE --> GROUP BY -->HAVING–> ORDER BY】

1.基礎查詢

a).查詢所有列

mysql> select * from student;
+------+----------+------+--------+
| id   | name     | age  | gender |
+------+----------+------+--------+
| 1    | aaaa     |   19 | female |
| 2    | bbbbbbbb |   20 | male   |
| 3    | cc       |   15 | male   |
| 4    | ddd      |   16 | female |
| 5    | eee      |   20 | female |
+------+----------+------+--------+
5 rows in set (0.00 sec)

b).查詢指定列
( *星號代表的是對應表的所有欄位,如果我們要查詢我們僅需要的欄位,那我們在select的後面加上對應欄位名就可以了,多個欄位逗號隔開.) 範例如下:

mysql> select id,name,gender from student;
+------+----------+--------+
| id   | name     | gender |
+------+----------+--------+
| 1    | aaaa     | female |
| 2    | bbbbbbbb | male   |
| 3    | cc       | male   |
| 4    | ddd      | female |
| 5    | eee      | female |
+------+----------+--------+
5 rows in set (0.00 sec)

2.條件查詢

主要結合where的使用
between…and: 介於…和…之間
and：邏輯與
or：邏輯或
in / not in：類似於Python中的成員運算子
is / is not: 類似於Python中的身份運算子 , 常用語判斷null值， 如：name is null

a).查詢性別爲女，並且年齡爲20的記錄

mysql> select * from student where gender='female' and age=20;
+------+------+------+--------+
| id   | name | age  | gender |
+------+------+------+--------+
| 5    | eee  |   20 | female |
+------+------+------+--------+
1 row in set (0.00 sec)

b).查詢編號爲1或者姓名爲ddd的記錄

mysql> select * from student where id='1' or name='ddd';
+------+------+------+--------+
| id   | name | age  | gender |
+------+------+------+--------+
| 1    | aaaa |   19 | female |
| 4    | ddd  |   16 | female |
+------+------+------+--------+
2 rows in set (0.00 sec)

c).查詢編號分別爲1,2,3的記錄

mysql> select * from student where id in('1','2','3');
+------+----------+------+--------+
| id   | name     | age  | gender |
+------+----------+------+--------+
| 1    | aaaa     |   19 | female |
| 2    | bbbbbbbb |   20 | male   |
| 3    | cc       |   15 | male   |
+------+----------+------+--------+
3 rows in set (0.00 sec)

d).查詢編號不爲1,2,3的記錄

mysql> select * from student where id not in('1','2','3');
+------+------+------+--------+
| id   | name | age  | gender |
+------+------+------+--------+
| 4    | ddd  |   16 | female |
| 5    | eee  |   20 | female |
+------+------+------+--------+
2 rows in set (0.00 sec)

e).查詢年齡在15~20之間的記錄

mysql> select * from student where age between 15 and 20;
+------+----------+------+--------+
| id   | name     | age  | gender |
+------+----------+------+--------+
| 1    | aaaa     |   19 | female |
| 2    | bbbbbbbb |   20 | male   |
| 3    | cc       |   15 | male   |
| 4    | ddd      |   16 | female |
| 5    | eee      |   20 | female |
+------+----------+------+--------+
5 rows in set (0.00 sec)

3.模糊查詢

where 子句中=表示精準查詢

like：一般情況下結合where子句使用

萬用字元：
​ _: 匹配任意一個字元,短橫線個數表示字元個數

​ %：匹配0~n個字元【n大於等於1】

演示：
a).查詢姓名由4個字元組成的記錄

mysql> select * from student where name like '____';
+------+------+------+--------+
| id   | name | age  | gender |
+------+------+------+--------+
| 1    | aaaa |   19 | female |
+------+------+------+--------+
1 row in set (0.00 sec)

b).查詢以a開頭的記錄

mysql> select * from student where name like 'a%';
+------+------+------+--------+
| id   | name | age  | gender |
+------+------+------+--------+
| 1    | aaaa |   19 | female |
+------+------+------+--------+
1 row in set (0.01 sec)

c).查詢姓名中包含b的記錄

mysql> select * from student where name like '%b%';
+------+----------+------+--------+
| id   | name     | age  | gender |
+------+----------+------+--------+
| 2    | bbbbbbbb |   20 | male   |
+------+----------+------+--------+
1 row in set (0.00 sec)

d).查詢姓名中第2個字母爲c的記錄

mysql> select * from student where name like '_c%';
+------+------+------+--------+
| id   | name | age  | gender |
+------+------+------+--------+
| 3    | cc   |   15 | male   |
+------+------+------+--------+
1 row in set (0.00 sec)