本文包括以下內容
簡單查詢 條件查詢:where 分組查詢:group by 排序查詢:order by 查詢結果去重:distinct
限制查詢量:limit
0 準備
先建立兩個表employee和department:
mysql> select * from employee; +----+----------+--------+--------+------+------+---------+ | id | name | gender | salary | age | gmr | dept_id | +----+----------+--------+--------+------+------+---------+ | 1 | lisan | f | 100.00 | 20 | 4 | 1001 | | 2 | Will | f | 130.00 | 28 | 3 | 1001 | | 3 | lucy | m | 500.70 | 18 | 2 | 1002 | | 4 | 李雷 | 男 | 670.00 | 20 | 4 | 1003 | | 5 | WangYong | m | 700.90 | 25 | 5 | NULL | | 6 | Sam | m | 600.90 | 25 | 5 | 1004 | +----+----------+--------+--------+------+------+---------+ mysql> select * from department; +------+-----------+--------------+ | id | name | description | +------+-----------+--------------+ | 1001 | 人事部 | 人事管理 | | 1002 | 行政部 | 行政管理 | | 1003 | 研發部 | 研發 | | 1004 | 質量部 | 質量保障 | +------+-----------+--------------+
1 簡單查詢
select * from employee; // select id,name,salary from employee;
2 條件查詢
where 條件表達式
//查詢條件有以下幾種
比較:=、<、>、>=,<=,!=,<> 指定范圍:between [] and [],NOT between [] and [] 指定集合:IN [],NOT IN [] 匹配字符:like,NOT LIKE 是否為空:IS NULL, IS NOT NULL 多個查詢條件: AND ,OR
//比較 select * from employ where age>20; select * from employee where age<>20; //指定范圍 select * from employee where age between 18 and 25; //指定集合 select * from employee where name in ("Will","lucy"); //匹配字符,其中為"%"為通配符,"_"匹配單一字符 select * from employee where name like "w%"; +----+----------+--------+--------+------+------+---------+ | id | name | gender | salary | age | gmr | dept_id | +----+----------+--------+--------+------+------+---------+ | 2 | Will | f | 130.00 | 28 | 3 | 1001 | | 5 | WangYong | m | 700.90 | 25 | 5 | NULL | +----+----------+--------+--------+------+------+---------+ select * from employee where name like "w_l";//查詢結果為空 變成 select * from employee where name like "w_ll";就可以查到這一條數據瞭。 //AND select * from employ where age>20 and salary<500; select * from employee where name like "w%" AND age>20 AND salary>500; //OR select * from employee where name like "w%" AND age>25 OR salary>500;
3 查詢結果去重
使用DISTINCT關鍵字來消除重復的記錄
select distinct [value Name] //eg: select distinct age from employee;
4 排序
排序分為降序DESC,升序ASC兩種,使用ORDER BY關鍵字。
ORDER BY [value] [ASC | DESC] 默認情況下安裝ASC方式排序
示例:
//升序 select * from employee order by age ASC; //降序 select * from employee order by age DESC; //多個排序條件:先按照age降序,相同age的字段按照id升序排序 select * from employee order by age DESC,id ASC;
5 分組查詢
GROUP BY [value] [HAVAING 條件表達式] [WITH ROLLUP]
其中,value是按照該字段值分組,HAVING用來限制分組後的顯示,WITH ROLLUP將會在坐在記錄的最後加一條記錄,該記錄是上面所有記錄的總和。
5.1 單獨使用group by查詢
//單獨使用group by分組,查詢結果隻顯示分組的一條記錄 select * from employee group by age;
5.2 與GRIOUP_CONCAT()函數一起使用
每個分組指定字段都顯示出來
//與"GROUP_CONCAT()"函數一起使用 select age,group_concat(name) from employee group by age; +------+--------------------+ | age | group_concat(name) | +------+--------------------+ | 18 | lucy | | 20 | lisan,李雷 | | 25 | WangYong,Sam | | 28 | Will | +------+--------------------+ 結果顯示分為4組,每組所有人的name 列出來瞭。
5.3 與集合函數一起使用
通過集合函數計算分組中的總記錄最大值、最小值。
//按照age分組查詢,age相同的為一組,然後對每組用集合函數count()計算出每一組的記錄數 select age,count(age) from employee group by age;
5.4與HAVING條件表達式一起使用
加上HAVCING…,可以限制輸出的結果,隻有滿足條件的才會顯示。
//與HAVING一起使用 //按照age字段查詢,然後顯示記錄大於等於2的分組。 select age,count(age) from employee group by age HAVING count(age)>=2; +------+------------+ | age | count(age) | +------+------------+ | 20 | 2 | | 25 | 2 | +------+------------+
5.5 多個字段分組
//先按照age分組,age中有相同的記錄用gender再分組 select * from employee group by age,gender; +----+----------+--------+--------+------+------+---------+ | id | name | gender | salary | age | gmr | dept_id | +----+----------+--------+--------+------+------+---------+ | 3 | lucy | m | 500.70 | 18 | 2 | 1002 | | 1 | lisan | f | 100.00 | 20 | 4 | 1001 | | 4 | 李雷 | 男 | 670.00 | 20 | 4 | 1003 | | 5 | WangYong | m | 700.90 | 25 | 5 | NULL | | 2 | Will | f | 130.00 | 28 | 3 | 1001 | +----+----------+--------+--------+------+------+---------+
5.6 與WITH ROLLUP一起使用
WITH ROLLUP將會在坐在記錄的最後加一條記錄,該記錄是上面所有記錄的總和。
//最後一條是總和 select age,count(*) from employee group by age WITH ROLLUP; +------+----------+ | age | count(*) | +------+----------+ | 18 | 1 | | 20 | 2 | | 25 | 2 | | 28 | 1 | | NULL | 6 | +------+----------+
6 限制查詢量
6.1 不指定初始位置
記錄的條數從第一條開始顯示。
limit [num]
//隻顯示前兩條 select * from employee limit 2;
6.2 指定初始位置
從指定位置開始顯示,第一條記錄為0.
limit [start],[limitNUm]
//指定初始,顯示第二到第四條 select * from employee limit 1,3;