基础查询

1、格式
SELECT column_name,column_name FROM table_name
[WHERE Clause]---分组前的条件
[GROUP By coulemn_name]-----按哪一列进行分组
[HAVING Clause]------分组后的条件
[ORDER BY column_name]------按哪组进行排序
[LIMIT N][ OFFSET M]-------分页显示
查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以使用 LIMIT 属性来设定返回的记录数：
你可以通过OFFSET指定SELECT语句开始查询的起始位置，从0开始，M表示每页显示的数量。
2、SELECT的口罩DSTINCT，不重复选取。对显示的结果集进行除重
2.1 格式如下：
SELECT DSTINCT 列名1，列名2 FROM 表名（查询出列名1和列名2都重复时才除重）
2.2 举例

mysql> select age from employee;
+------+
| age  |
+------+
|   26 |
|   24 |
|   22 |
|   35 |
|   21 |
|   26 |
|   27 |
|   24 |
|   31 |
|   23 |
| NULL |
| NULL |
+------+
12 rows in set (0.00 sec)

mysql> select distinct age from employee;------对age除重查询
+------+
| age  |
+------+
|   26 |
|   24 |
|   22 |
|   35 |
|   21 |
|   27 |
|   31 |
|   23 |
| NULL |
+------+
9 rows in set (0.00 sec)

mysql> select age,in_dpt from employee;
+------+--------+
| age  | in_dpt |
+------+--------+
|   26 | dpt4   |
|   24 | dpt2   |
|   22 | dpt3   |
|   35 | dpt1   |
|   21 | dpt2   |
|   26 | dpt1   |
|   27 | dpt1   |
|   24 | dpt3   |
|   31 | dpt2   |
|   23 | dpt4   |
| NULL | dpt2   |
| NULL | dpt3   |
+------+--------+
12 rows in set (0.00 sec)

mysql> select distinct age,in_dpt from employee;----对age,in_dpt除重查询结果不变
+------+--------+
| age  | in_dpt |
+------+--------+
|   26 | dpt4   |
|   24 | dpt2   |
|   22 | dpt3   |
|   35 | dpt1   |
|   21 | dpt2   |
|   26 | dpt1   |
|   27 | dpt1   |
|   24 | dpt3   |
|   31 | dpt2   |
|   23 | dpt4   |
| NULL | dpt2   |
| NULL | dpt3   |
+------+--------+
12 rows in set (0.0

3、WHERE
3.1 格式：
SELECT 列名 FROM 表名 WHERE 列 运算符 值
(值是字符串时用单双引号扩起来都可以）
3.2 举例

mysql> select * from employee;
+----+------+------+--------+--------+--------+
| id | name | age  | salary | phone  | in_dpt |
+----+------+------+--------+--------+--------+
|  1 | Tom  |   26 |   2500 | 119119 | dpt4   |
|  2 | Jack |   24 |   2500 | 120120 | dpt2   |
|  3 | Rose |   22 |   2800 | 114114 | dpt3   |
|  4 | Jim  |   35 |   3000 | 100861 | dpt1   |
|  5 | Mary |   21 |   3000 | 100101 | dpt2   |
|  6 | Alex |   26 |   3000 | 123456 | dpt1   |
|  7 | Ken  |   27 |   3500 | 654321 | dpt1   |
|  8 | Rick |   24 |   3500 | 987654 | dpt3   |
|  9 | Joe  |   31 |   3600 | 110129 | dpt2   |
| 10 | Mike |   23 |   3400 | 110110 | dpt4   |
| 11 | Jobs | NULL |   3600 |  19283 | dpt2   |
| 12 | Tony | NULL |   3400 | 102938 | dpt3   |
+----+------+------+--------+--------+--------+
12 rows in set (0.00 sec)

mysql> select * from employee where age >30;----年龄大于30的员工信息
+----+------+------+--------+--------+--------+
| id | name | age  | salary | phone  | in_dpt |
+----+------+------+--------+--------+--------+
|  4 | Jim  |   35 |   3000 | 100861 | dpt1   |
|  9 | Joe  |   31 |   3600 | 110129 | dpt2   |
+----+------+------+--------+--------+--------+
2 rows in set (0.00 sec)

mysql> 

4.AND表示逻辑且，OR表示逻辑或
格式：
SELECT 列名 FROM 表名 WHERE 列 运算符 值 AND 列 运算符 值
SELECT 列名 FROM 表名 WHERE 列 运算符 值 OR 列 运算符 值
4.1 举例

mysql> select * from employee;
+----+------+------+--------+--------+--------+
| id | name | age  | salary | phone  | in_dpt |
+----+------+------+--------+--------+--------+
|  1 | Tom  |   26 |   2500 | 119119 | dpt4   |
|  2 | Jack |   24 |   2500 | 120120 | dpt2   |
|  3 | Rose |   22 |   2800 | 114114 | dpt3   |
|  4 | Jim  |   35 |   3000 | 100861 | dpt1   |
|  5 | Mary |   21 |   3000 | 100101 | dpt2   |
|  6 | Alex |   26 |   3000 | 123456 | dpt1   |
|  7 | Ken  |   27 |   3500 | 654321 | dpt1   |
|  8 | Rick |   24 |   3500 | 987654 | dpt3   |
|  9 | Joe  |   31 |   3600 | 110129 | dpt2   |
| 10 | Mike |   23 |   3400 | 110110 | dpt4   |
| 11 | Jobs | NULL |   3600 |  19283 | dpt2   |
| 12 | Tony | NULL |   3400 | 102938 | dpt3   |
+----+------+------+--------+--------+--------+
12 rows in set (0.00 sec)

mysql> select * from employee where (in_dpt='dpt4' or in_dpt='dpt1') and age <30;
--查询在dpt4或者dpt1部门，并且年龄小于30的员工信息
+----+------+------+--------+--------+--------+
| id | name | age  | salary | phone  | in_dpt |
+----+------+------+--------+--------+--------+
|  1 | Tom  |   26 |   2500 | 119119 | dpt4   |
|  6 | Alex |   26 |   3000 | 123456 | dpt1   |
|  7 | Ken  |   27 |   3500 | 654321 | dpt1   |
| 10 | Mike |   23 |   3400 | 110110 | dpt4   |
+----+------+------+--------+--------+--------+
4 rows in set (0.00 sec)

mysql> select * from employee where in_dpt in('dpt1','dpt4') and age <30;
--可以用IN简化OR
+----+------+------+--------+--------+--------+
| id | name | age  | salary | phone  | in_dpt |
+----+------+------+--------+--------+--------+
|  1 | Tom  |   26 |   2500 | 119119 | dpt4   |
|  6 | Alex |   26 |   3000 | 123456 | dpt1   |
|  7 | Ken  |   27 |   3500 | 654321 | dpt1   |
| 10 | Mike |   23 |   3400 | 110110 | dpt4   |
+----+------+------+--------+--------+--------+
4 rows in set (0.00 sec)

mysql> 

5 计算列
5.1 一般可以使用四则运算符对一些数值型的列进行运算，当列中某个值为null时，运算后的结果也为null。null参与的运算结果都为null。
5.2 ifnull(列名，w）-----将列中为null的替换为w,w一般设置为0
5.3 取别名：可以用AS也可以省略
5.4 例子

mysql> select * from employee;
+----+------+------+--------+--------+--------+
| id | name | age  | salary | phone  | in_dpt |
+----+------+------+--------+--------+--------+
|  1 | Tom  |   26 |   2500 | 119119 | dpt4   |
|  2 | Jack |   24 |   2500 | 120120 | dpt2   |
|  3 | Rose |   22 |   2800 | 114114 | dpt3   |
|  4 | Jim  |   35 |   3000 | 100861 | dpt1   |
|  5 | Mary |   21 |   3000 | 100101 | dpt2   |
|  6 | Alex |   26 |   3000 | 123456 | dpt1   |
|  7 | Ken  |   27 |   3500 | 654321 | dpt1   |
|  8 | Rick |   24 |   3500 | 987654 | dpt3   |
|  9 | Joe  |   31 |   3600 | 110129 | dpt2   |
| 10 | Mike |   23 |   3400 | 110110 | dpt4   |
| 11 | Jobs | NULL |   3600 |  19283 | dpt2   |
| 12 | Tony | NULL |   3400 | 102938 | dpt3   |
+----+------+------+--------+--------+--------+
12 rows in set (0.00 sec)

mysql> select age,salary,age+salary from employee;
---Null参与的运算结果都为Null
+------+--------+------------+
| age  | salary | age+salary |
+------+--------+------------+
|   26 |   2500 |       2526 |
|   24 |   2500 |       2524 |
|   22 |   2800 |       2822 |
|   35 |   3000 |       3035 |
|   21 |   3000 |       3021 |
|   26 |   3000 |       3026 |
|   27 |   3500 |       3527 |
|   24 |   3500 |       3524 |
|   31 |   3600 |       3631 |
|   23 |   3400 |       3423 |
| NULL |   3600 |       NULL |
| NULL |   3400 |       NULL |
+------+--------+------------+
12 rows in set (0.00 sec)

mysql> select age,salary,ifnull(age,0)+salary as ZongHe from employee;
---IFNULL(age,0)用0替换Null进行运算
+------+--------+--------+
| age  | salary | ZongHe |
+------+--------+--------+
|   26 |   2500 |   2526 |
|   24 |   2500 |   2524 |
|   22 |   2800 |   2822 |
|   35 |   3000 |   3035 |
|   21 |   3000 |   3021 |
|   26 |   3000 |   3026 |
|   27 |   3500 |   3527 |
|   24 |   3500 |   3524 |
|   31 |   3600 |   3631 |
|   23 |   3400 |   3423 |
| NULL |   3600 |   3600 |
| NULL |   3400 |   3400 |
+------+--------+--------+
12 rows in set (0.00 sec)

mysql> select age,salary,ifnull(age,0)+salary ZongHe from employee;
--取别名可以用AS 也可以直接空格
+------+--------+--------+
| age  | salary | ZongHe |
+------+--------+--------+
|   26 |   2500 |   2526 |
|   24 |   2500 |   2524 |
|   22 |   2800 |   2822 |
|   35 |   3000 |   3035 |
|   21 |   3000 |   3021 |
|   26 |   3000 |   3026 |
|   27 |   3500 |   3527 |
|   24 |   3500 |   3524 |
|   31 |   3600 |   3631 |
|   23 |   3400 |   3423 |
| NULL |   3600 |   3600 |
| NULL |   3400 |   3400 |
+------+--------+--------+
12 rows in set (0.00 sec)