mysql學習一之select陳述句
- 完整語法
- 創建User 表
- 單表查詢
- 查詢空值
- AND多條件查詢
- OR 多條件查詢
- DISTINCT
- 查詢結果排序
- 分組查詢
完整語法
先給一下完整的語法，后面將逐一來講解，
-
基礎語法：select 欄位串列 from 資料源;
-
完整語法：
select 去重選項 欄位串列 [as 欄位別名]
from 資料源
[where子句]
[group by 子句]
[having子句]
[order by 子句]
[limit子句];
創建User 表
- 創建User表
use test;
CREATE TABLE `test`.`user` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`pwd` VARCHAR(32) NOT NULL,
`age` INT NOT NULL,
`sex` VARCHAR(8) NOT NULL,
PRIMARY KEY (`id`));
- 插入資料
use test;
insert into user(name,pwd,age,sex)
values ('小灰灰','xiaohuihui',6,'男'),('黑太郎','heitailang',20,'男');
- 查詢資料
./mysql -h 127.0.0.1 -u root -P 3306 -p
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select id,name from user;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 喜羊羊 |
| 2 | 懶羊羊 |
| 3 | 慢羊羊 |
| 4 | 慢羊羊 |
| 7 | 沸羊羊 |
| 8 | 花羊羊 |
| 9 | 奔羊羊 |
| 10 | 瘦羊羊 |
| 11 | 紅太狼 |
| 12 | 蕉太狼 |
| 13 | 小灰灰 |
| 14 | 黑太郎 |
+----+-----------+
12 rows in set (0.00 sec)
單表查詢
- 在SELECT 陳述句中使用 “*” 通配符查詢所有欄位
mysql> SELECT * FROM user;
+----+-----------+-------------+-----+-----+
| id | name | pwd | age | sex |
+----+-----------+-------------+-----+-----+
| 1 | 喜羊羊 | xiyangyang | 12 | 男 |
| 2 | 懶羊羊 | lanyangyang | 10 | 男 |
| 3 | 慢羊羊 | meiyangyang | 10 | 女 |
| 4 | 慢羊羊 | manyangyang | 14 | 男 |
| 7 | 沸羊羊 | feiyangyang | 16 | 女 |
| 8 | 花羊羊 | huayangyang | 18 | 女 |
| 9 | 奔羊羊 | benyangyang | 20 | 男 |
| 10 | 瘦羊羊 | souyangyang | 24 | 女 |
| 11 | 紅太狼 | hongtailang | 20 | 女 |
| 12 | 蕉太狼 | jiaotailang | 24 | 男 |
| 13 | 小灰灰 | xiaohuihui | 6 | 男 |
| 14 | 黑太郎 | heitailang | 20 | 男 |
+----+-----------+-------------+-----+-----+
12 rows in set (0.00 sec)
- 在SELECT 陳述句中指定所有欄位
mysql> SELECT id,name,pwd,age,sex FROM user;
+----+-----------+-------------+-----+-----+
| id | name | pwd | age | sex |
+----+-----------+-------------+-----+-----+
| 1 | 喜羊羊 | xiyangyang | 12 | 男 |
| 2 | 懶羊羊 | lanyangyang | 10 | 男 |
| 3 | 慢羊羊 | meiyangyang | 10 | 女 |
| 4 | 慢羊羊 | manyangyang | 14 | 男 |
| 7 | 沸羊羊 | feiyangyang | 16 | 女 |
| 8 | 花羊羊 | huayangyang | 18 | 女 |
| 9 | 奔羊羊 | benyangyang | 20 | 男 |
| 10 | 瘦羊羊 | souyangyang | 24 | 女 |
| 11 | 紅太狼 | hongtailang | 20 | 女 |
| 12 | 蕉太狼 | jiaotailang | 24 | 男 |
| 13 | 小灰灰 | xiaohuihui | 6 | 男 |
| 14 | 黑太郎 | heitailang | 20 | 男 |
+----+-----------+-------------+-----+-----+
12 rows in set (0.00 sec)
- 查詢指定欄位：SELECT 列名 FROM 表名;
mysql> SELECT name from user;
+-----------+
| name |
+-----------+
| 喜羊羊 |
| 懶羊羊 |
| 慢羊羊 |
| 慢羊羊 |
| 沸羊羊 |
| 花羊羊 |
| 奔羊羊 |
| 瘦羊羊 |
| 紅太狼 |
| 蕉太狼 |
| 小灰灰 |
| 黑太郎 |
+-----------+
12 rows in set (0.00 sec)
- 查詢多個欄位：SELECT 欄位名1,欄位名2, … 欄位名n FROM 表名;
mysql> SELECT name pwd,sex from user;
+-----------+-----+
| pwd | sex |
+-----------+-----+
| 喜羊羊 | 男 |
| 懶羊羊 | 男 |
| 慢羊羊 | 女 |
| 慢羊羊 | 男 |
| 沸羊羊 | 女 |
| 花羊羊 | 女 |
| 奔羊羊 | 男 |
| 瘦羊羊 | 女 |
| 紅太狼 | 女 |
| 蕉太狼 | 男 |
| 小灰灰 | 男 |
| 黑太郎 | 男 |
+-----------+-----+
12 rows in set (0.00 sec)
- 查詢指定記錄 SELECT 欄位名1,欄位名2, … 欄位名n FROM 表名 WHERE 查詢條件
mysql> SELECT name,pwd,sex from user where sex='女';
+-----------+-------------+-----+
| name | pwd | sex |
+-----------+-------------+-----+
| 慢羊羊 | meiyangyang | 女 |
| 沸羊羊 | feiyangyang | 女 |
| 花羊羊 | huayangyang | 女 |
| 瘦羊羊 | souyangyang | 女 |
| 紅太狼 | hongtailang | 女 |
+-----------+-------------+-----+
5 rows in set (0.01 sec)
- 查詢年齡小于20的記錄
mysql> SELECT name,age FROM user where age <20;
+-----------+-----+
| name | age |
+-----------+-----+
| 喜羊羊 | 12 |
| 懶羊羊 | 10 |
| 慢羊羊 | 10 |
| 慢羊羊 | 14 |
| 沸羊羊 | 16 |
| 花羊羊 | 18 |
| 小灰灰 | 6 |
+-----------+-----+
7 rows in set (0.01 sec)
- IN 關鍵字查詢
mysql> SELECT id,name,sex FROM user where id in (1,2,3,4);
+----+-----------+-----+
| id | name | sex |
+----+-----------+-----+
| 1 | 喜羊羊 | 男 |
| 2 | 懶羊羊 | 男 |
| 3 | 慢羊羊 | 女 |
| 4 | 慢羊羊 | 男 |
+----+-----------+-----+
4 rows in set (0.00 sec)
- NOT IN 關鍵字查詢
mysql> SELECT id,name,sex FROM user where id not in (1,2,3,4);
+----+-----------+-----+
| id | name | sex |
+----+-----------+-----+
| 7 | 沸羊羊 | 女 |
| 8 | 花羊羊 | 女 |
| 9 | 奔羊羊 | 男 |
| 10 | 瘦羊羊 | 女 |
| 11 | 紅太狼 | 女 |
| 12 | 蕉太狼 | 男 |
| 13 | 小灰灰 | 男 |
| 14 | 黑太郎 | 男 |
+----+-----------+-----+
8 rows in set (0.01 sec)
- BETWEEN AND 關鍵字查詢
mysql> SELECT id,name,sex,age FROM user where age between 20 and 24;
+----+-----------+-----+-----+
| id | name | sex | age |
+----+-----------+-----+-----+
| 9 | 奔羊羊 | 男 | 20 |
| 10 | 瘦羊羊 | 女 | 24 |
| 11 | 紅太狼 | 女 | 20 |
| 12 | 蕉太狼 | 男 | 24 |
| 14 | 黑太郎 | 男 | 20 |
+----+-----------+-----+-----+
5 rows in set (0.00 sec)
- NOT BETWEEN AND 關鍵字查詢
mysql> SELECT id,name,sex,age FROM user where age not between 20 and 24;
+----+-----------+-----+-----+
| id | name | sex | age |
+----+-----------+-----+-----+
| 1 | 喜羊羊 | 男 | 12 |
| 2 | 懶羊羊 | 男 | 10 |
| 3 | 慢羊羊 | 女 | 10 |
| 4 | 慢羊羊 | 男 | 14 |
| 7 | 沸羊羊 | 女 | 16 |
| 8 | 花羊羊 | 女 | 18 |
| 13 | 小灰灰 | 男 | 6 |
+----+-----------+-----+-----+
7 rows in set (0.00 sec)
- 查詢以“小”開頭的資料
mysql> SELECT id,name,age,sex FROM user where name like '小%';
+----+-----------+-----+-----+
| id | name | age | sex |
+----+-----------+-----+-----+
| 13 | 小灰灰 | 6 | 男 |
+----+-----------+-----+-----+
1 row in set (0.00 sec)
- 查詢以“喜”開頭 和 “ 羊”結尾的資料
mysql> SELECT id,name,age,sex FROM user where name like '喜%羊';
+----+-----------+-----+-----+
| id | name | age | sex |
+----+-----------+-----+-----+
| 1 | 喜羊羊 | 12 | 男 |
+----+-----------+-----+-----+
1 row in set (0.00 sec)
- 下劃線通配符查詢
mysql> SELECT id,name,age,sex FROM user where name like '__灰';
+----+-----------+-----+-----+
| id | name | age | sex |
+----+-----------+-----+-----+
| 13 | 小灰灰 | 6 | 男 |
+----+-----------+-----+-----+
1 row in set (0.00 sec)
查詢空值
- 添加欄位email
ALTER TABLE `test`.`user`
ADD COLUMN `email` VARCHAR(64) NULL AFTER `sex`;
UPDATE `test`.`user` SET `email` = 'xiyangyang@163.com' WHERE (`id` = '1');
UPDATE `test`.`user` SET `email` = 'lanyangyang@163.com' WHERE (`id` = '2');
UPDATE `test`.`user` SET `email` = 'meiyangyang@163.com' WHERE (`id` = '3');
UPDATE `test`.`user` SET `email` = 'manyangyang@163.com' WHERE (`id` = '4');
UPDATE `test`.`user` SET `email` = 'feiyangyang@163.com' WHERE (`id` = '7');
- IS NULL 查詢
mysql> SELECT id,name,age,sex,email FROM user WHERE email IS NULL;
+----+-----------+-----+-----+-------+
| id | name | age | sex | email |
+----+-----------+-----+-----+-------+
| 8 | 花羊羊 | 18 | 女 | NULL |
| 9 | 奔羊羊 | 20 | 男 | NULL |
| 10 | 瘦羊羊 | 24 | 女 | NULL |
| 11 | 紅太狼 | 20 | 女 | NULL |
| 12 | 蕉太狼 | 24 | 男 | NULL |
| 13 | 小灰灰 | 6 | 男 | NULL |
| 14 | 黑太郎 | 20 | 男 | NULL |
+----+-----------+-----+-----+-------+
7 rows in set (0.00 sec)
- IS NOT NULL 查詢
mysql> SELECT id,name,age,sex,email FROM user WHERE email IS NOT NULL;
+----+-----------+-----+-----+---------------------+
| id | name | age | sex | email |
+----+-----------+-----+-----+---------------------+
| 1 | 喜羊羊 | 12 | 男 | xiyangyang@163.com |
| 2 | 懶羊羊 | 10 | 男 | lanyangyang@163.com |
| 3 | 慢羊羊 | 10 | 女 | meiyangyang@163.com |
| 4 | 慢羊羊 | 14 | 男 | manyangyang@163.com |
| 7 | 沸羊羊 | 16 | 女 | feiyangyang@163.com |
+----+-----------+-----+-----+---------------------+
5 rows in set (0.00 sec)
AND多條件查詢
mysql> SELECT id,name,age,sex,email FROM user where id IN (4,7) AND age = 16 and name=' 沸羊羊';
+----+-----------+-----+-----+---------------------+
| id | name | age | sex | email |
+----+-----------+-----+-----+---------------------+
| 7 | 沸羊羊 | 16 | 女 | feiyangyang@163.com |
+----+-----------+-----+-----+---------------------+
1 row in set (0.00 sec)
OR 多條件查詢
mysql> SELECT id,name,age,sex,email FROM user where id=4 OR id=7;
+----+-----------+-----+-----+---------------------+
| id | name | age | sex | email |
+----+-----------+-----+-----+---------------------+
| 4 | 慢羊羊 | 14 | 男 | manyangyang@163.com |
| 7 | 沸羊羊 | 16 | 女 | feiyangyang@163.com |
+----+-----------+-----+-----+---------------------+
2 rows in set (0.00 sec)
DISTINCT
- 插入一條資料
INSERT INTO `test`.`user` (`name`, `pwd`, `age`, `sex`, `email`)
VALUES ('喜羊羊', 'xiyangyang', '12', '男', 'xiyangyang@163.com');
- 不使用 distinct 關鍵字
mysql> SELECT name FROM user;
+-----------+
| name |
+-----------+
| 喜羊羊 |
| 懶羊羊 |
| 慢羊羊 |
| 慢羊羊 |
| 沸羊羊 |
| 花羊羊 |
| 奔羊羊 |
| 瘦羊羊 |
| 紅太狼 |
| 蕉太狼 |
| 小灰灰 |
| 黑太郎 |
| 喜羊羊 |
+-----------+
13 rows in set (0.00 sec)
- 使用 distinct 關鍵字
mysql> SELECT distinct name FROM user;
+-----------+
| name |
+-----------+
| 喜羊羊 |
| 懶羊羊 |
| 慢羊羊 |
| 沸羊羊 |
| 花羊羊 |
| 奔羊羊 |
| 瘦羊羊 |
| 紅太狼 |
| 蕉太狼 |
| 小灰灰 |
| 黑太郎 |
+-----------+
11 rows in set (0.00 sec)
查詢結果排序
- 不使用 ORDER BY : 根據資料插入到資料表的順序來顯示
mysql> SELECT name FROM user;
+-----------+
| name |
+-----------+
| 喜羊羊 |
| 懶羊羊 |
| 慢羊羊 |
| 慢羊羊 |
| 沸羊羊 |
| 花羊羊 |
| 奔羊羊 |
| 瘦羊羊 |
| 紅太狼 |
| 蕉太狼 |
| 小灰灰 |
| 黑太郎 |
| 喜羊羊 |
+-----------+
13 rows in set (0.00 sec)
- 使用 ORDER BY
mysql> SELECT name FROM user ORDER BY name;
+-----------+
| name |
+-----------+
| 喜羊羊 |
| 喜羊羊 |
| 奔羊羊 |
| 小灰灰 |
| 慢羊羊 |
| 慢羊羊 |
| 懶羊羊 |
| 沸羊羊 |
| 瘦羊羊 |
| 紅太狼 |
| 花羊羊 |
| 蕉太狼 |
| 黑太郎 |
+-----------+
13 rows in set (0.00 sec)
- 使用DESC 關鍵字
mysql> SELECT name FROM user ORDER BY name DESC;
+-----------+
| name |
+-----------+
| 黑太郎 |
| 蕉太狼 |
| 花羊羊 |
| 紅太狼 |
| 瘦羊羊 |
| 沸羊羊 |
| 懶羊羊 |
| 慢羊羊 |
| 慢羊羊 |
| 小灰灰 |
| 奔羊羊 |
| 喜羊羊 |
| 喜羊羊 |
+-----------+
13 rows in set (0.00 sec)
分組查詢
- 使用 GROUP BY 關鍵
mysql> SELECT sex,COUNT(*) FROM user GROUP BY sex;
+-----+----------+
| sex | COUNT(*) |
+-----+----------+
| 男 | 8 |
| 女 | 5 |
+-----+----------+
2 rows in set (0.01 sec)
- 使用GROUP_CONCAT函式
mysql> SELECT sex,GROUP_CONCAT(name) FROM user GROUP BY sex;
+-----+---------------------------------------------------------------------------------+
| sex | GROUP_CONCAT(name) |
+-----+---------------------------------------------------------------------------------+
| 女 | 慢羊羊,沸羊羊,花羊羊,瘦羊羊,紅太狼 |
| 男 | 喜羊羊,懶羊羊,慢羊羊,奔羊羊,蕉太狼,小灰灰,黑太郎,喜羊羊 |
+-----+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
- 使用HAVING
mysql> SELECT sex,GROUP_CONCAT(name) as names FROM user GROUP BY sex HAVING COUNT(name) > 5;
+-----+---------------------------------------------------------------------------------+
| sex | names |
+-----+---------------------------------------------------------------------------------+
| 男 | 喜羊羊,懶羊羊,慢羊羊,奔羊羊,蕉太狼,小灰灰,黑太郎,喜羊羊 |
+-----+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- 使用WITH ROLLUP
mysql> SELECT sex,GROUP_CONCAT(name) FROM user GROUP BY sex WITH ROLLUP;
+------+-----------------------------------------------------------------------------------------------------------------------------------+
| sex | GROUP_CONCAT(name) |
+------+-----------------------------------------------------------------------------------------------------------------------------------+
| 女 | 慢羊羊,沸羊羊,花羊羊,瘦羊羊,紅太狼 |
| 男 | 喜羊羊,懶羊羊,慢羊羊,奔羊羊,蕉太狼,小灰灰,黑太郎,喜羊羊 |
| NULL | 慢羊羊,沸羊羊,花羊羊,瘦羊羊,紅太狼,喜羊羊,懶羊羊,慢羊羊,奔羊羊,蕉太狼,小灰灰,黑太郎,喜羊羊 |
+------+-----------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)
mysql> SELECT sex,COUNT(name) FROM user GROUP BY sex WITH ROLLUP;
+------+-------------+
| sex | COUNT(name) |
+------+-------------+
| 女 | 5 |
| 男 | 8 |
| NULL | 13 |
+------+-------------+
3 rows in set (0.00 sec)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/259539.html
標籤：其他
上一篇：Redis-第八章節-應用場景
下一篇：資料庫連接池怎么用？