1,select查詢的基本結構
- select 字段
- from 表
- where 過濾條件
- group by 分組條件
- order by 排序條件
- having 過濾的第二條件
- limit 限定結果條件;
2,最簡單的查詢語句
- mysql> select * from user;
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 1 | kenan | kenan | 2012-10-31 15:32:26 |
- | 3 | kenan | lele | 2012-10-31 15:32:26 |
- +----+----------+----------+---------------------+
3,查詢一列或多列
- mysql> select username,createtime from user;
- +----------+---------------------+
- | username | createtime |
- +----------+---------------------+
- | kenan | 2012-10-31 15:32:26 |
- | kenan | 2012-10-31 15:32:26 |
- +----------+---------------------+
- 2 rows in set (0.00 sec)
4,多表查詢
- mysql> select * from user;
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 1 | kenan | kenan | 2012-10-31 15:32:26 |
- | 3 | kenan | lele | 2012-10-31 15:32:26 |
- +----+----------+----------+---------------------+
- 2 rows in set (0.00 sec)
- mysql> select * from teacher;
- +----+------------+---------------------+
- | id | username | createtime |
- +----+------------+---------------------+
- | 1 | Teacher Li | 2012-11-01 21:00:58 |
- | 2 | Teacher Le | 2012-11-01 21:02:07 |
- +----+------------+---------------------+
- 2 rows in set (0.00 sec)
- mysql> select * from user,teacher;
- +----+----------+----------+---------------------+----+------------+---------------------+
- | id | username | password | createtime | id | username | createtime |
- +----+----------+----------+---------------------+----+------------+---------------------+
- | 1 | kenan | kenan | 2012-10-31 15:32:26 | 1 | Teacher Li | 2012-11-01 21:00:58 |
- | 3 | kenan | lele | 2012-10-31 15:32:26 | 1 | Teacher Li | 2012-11-01 21:00:58 |
- | 1 | kenan | kenan | 2012-10-31 15:32:26 | 2 | Teacher Le | 2012-11-01 21:02:07 |
- | 3 | kenan | lele | 2012-10-31 15:32:26 | 2 | Teacher Le | 2012-11-01 21:02:07 |
- +----+----------+----------+---------------------+----+------------+---------------------+
- 4 rows in set (0.00 sec)
- 多表查詢中,如果沒有限制條件的話,兩個表的記錄會分别進行比對,産生的結果就是兩個表的笛卡爾積,叫做全連接配接
- 下面的這個帶有條件的叫做等同連接配接,相當于在上面的查詢結果中挑選出來滿足where後面條件的記錄,因為沒有所有是空的結果。
- mysql> select * from user,teacher where user.username = teacher.username;
- Empty set (0.00 sec)
5,帶有簡單的where字句條件查詢
- mysql> select * from user where id>2;
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 3 | kenan | lele | 2012-10-31 15:32:26 |
- +----+----------+----------+---------------------+
- 1 row in set (0.00 sec)
6,帶有in的查詢,in用來表示範圍
- mysql> select * from user where username in ('kenan','Micheal');
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 1 | kenan | kenan | 2012-10-31 15:32:26 |
- | 3 | kenan | lele | 2012-10-31 15:32:26 |
- +----+----------+----------+---------------------+
- 2 rows in set (0.00 sec)
這裡查詢出來username是kenan或者是Micheal的記錄
7,between and 用來表示一個字段的範圍的查詢
- mysql> select * from user where id between 2 and 3;
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 3 | kenan | lele | 2012-10-31 15:32:26 |
- +----+----------+----------+---------------------+
- 1 row in set (0.00 sec)
- mysql> select * from user where id between 3 and 4;
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 3 | kenan | lele | 2012-10-31 15:32:26 |
- +----+----------+----------+---------------------+
- 1 row in set (0.00 sec)
這裡分别查詢出來id在2和3之間的記錄,3到4之間的記錄,從結果可以看出 between and是包含邊界的,就是>=2而且<=3
8, is null 查詢空值
- mysql> select * from user ;
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 1 | kenan | kenan | 2012-10-31 15:32:26 |
- | 3 | kenan | lele | 2012-10-31 15:32:26 |
- | 4 | lele | lele | NULL |
- +----+----------+----------+---------------------+
- 3 rows in set (0.00 sec)
- mysql> select * from user where createtime is null;
- +----+----------+----------+------------+
- | id | username | password | createtime |
- +----+----------+----------+------------+
- | 4 | lele | lele | NULL |
- +----+----------+----------+------------+
- 1 row in set (0.00 sec)
9,用and連接配接多條件查詢 并關系
- mysql> select * from user where username = 'kenan' and password = 'kenan';
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 1 | kenan | kenan | 2012-10-31 15:32:26 |
- +----+----------+----------+---------------------+
- 1 row in set (0.00 sec)
10,用or 的多條件查詢 這個是或關系,滿足一個條件即可
- mysql> select * from user where username = 'kenan' or password = 'kenan';
- +----+----------+----------+---------------------+
- | id | username | password | createtime |
- +----+----------+----------+---------------------+
- | 1 | kenan | kenan | 2012-10-31 15:32:26 |
- | 3 | kenan | lele | 2012-10-31 15:32:26 |
- +----+----------+----------+---------------------+
- 2 rows in set (0.00 sec)
- mysql> select count(*) from user;
- +----------+
- | count(*) |
- +----------+
- | 3 |
- +----------+
- 1 row in set (0.00 sec)
- mysql> select id from user;
- +----+
- | id |
- +----+
- | 1 |
- | 3 |
- | 4 |
- +----+
- 3 rows in set (0.00 sec)
- mysql> select sum(id) from user;
- +---------+
- | sum(id) |
- +---------+
- | 8 |
- +---------+
- 1 row in set (0.00 sec)
- mysql> select avg(id) from user;
- +---------+
- | avg(id) |
- +---------+
- | 2.6667 |
- +---------+
- 1 row in set (0.04 sec)
- mysql> select id from user;
- +----+
- | id |
- +----+
- | 1 |
- | 3 |
- | 4 |
- +----+
- 3 rows in set (0.00 sec)
- mysql> select max(id) from user;
- +---------+
- | max(id) |
- +---------+
- | 4 |
- +---------+
- 1 row in set (0.00 sec)
- mysql> select min(id) from user;
- +---------+
- | min(id) |
- +---------+
- | 1 |
- +---------+
- 1 row in set (0.00 sec)