单表查询 – FGP

select

select可以进行各种操作

mysql> select 'Hello World!';
+--------------+
| Hello World! |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.00 sec)

mysql> select 2*7;
+-----+
| 2*7 |
+-----+
|  14 |
+-----+
1 row in set (0.00 sec)

利用as取别名（as可省略）

mysql> select 'Hello World!' as 'hello';
+--------------+
| hello        |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.00 sec)

mysql> select 2*7 as 'result';
+--------+
| result |
+--------+
|     14 |
+--------+
1 row in set (0.00 sec)

from

from多张表，返回笛卡尔积

mysql> select * from student;

+-------+-------+

| stuId | name  |

+-------+-------+

| 10001 | Jerry |

| 10002 | Tom   |

| 10003 | Kaly  |

+-------+-------+

3 rows in set (0.00 sec)

mysql> select * from eatery;

+----+-------+---------+

| id | stuId | money   |

+----+-------+---------+

|  1 | 10001 | 20.9800 |

|  2 | 10003 | 17.2100 |

|  3 | 10001 | 14.5000 |

+----+-------+---------+

3 rows in set (0.00 sec)

mysql> select * from student,eatery;

+-------+-------+----+-------+---------+

| stuId | name  | id | stuId | money   |

+-------+-------+----+-------+---------+

| 10001 | Jerry |  1 | 10001 | 20.9800 |

| 10002 | Tom   |  1 | 10001 | 20.9800 |

| 10003 | Kaly  |  1 | 10001 | 20.9800 |

| 10001 | Jerry |  2 | 10003 | 17.2100 |

| 10002 | Tom   |  2 | 10003 | 17.2100 |

| 10003 | Kaly  |  2 | 10003 | 17.2100 |

| 10001 | Jerry |  3 | 10001 | 14.5000 |

| 10002 | Tom   |  3 | 10001 | 14.5000 |

| 10003 | Kaly  |  3 | 10001 | 14.5000 |

+-------+-------+----+-------+---------+

9 rows in set (0.00 sec)

dual

dual默认的伪表

mysql> select 2*7 as res from dual; # 计算器
+-----+
| res |
+-----+
|  14 |
+-----+
1 row in set (0.00 sec)

where

筛选

mysql> select * from teacher;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | Tom  | NULL   | 暂时未知 |
|  2 | Tom  | NULL   | 暂时未知 |
|  3 | Tom  | 123456 | ShangHai |
|  4 | Tom  | NULL   | NULL     |
+----+------+--------+----------+
4 rows in set (0.00 sec)

mysql> select * from teacher where id = 1;
+----+------+-------+----------+
| id | name | phone | address  |
+----+------+-------+----------+
|  1 | Tom  | NULL  | 暂时未知 |
+----+------+-------+----------+
1 row in set (0.00 sec)

设置`< > = != and or` 等条件

mysql> select * from teacher where id = 1 or phone = '123456';
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | Tom  | NULL   | 暂时未知 |
|  3 | Tom  | 123456 | ShangHai |
+----+------+--------+----------+
2 rows in set (0.00 sec)

in

用in代替等号的一种，或者用not in代替不等号


mysql> select * from student where stuId in (10001,10003);

+-------+-------+

| stuId | name  |

+-------+-------+

| 10001 | Jerry |

| 10003 | Kaly  |

+-------+-------+

2 rows in set (0.00 sec)

mysql> select * from student where stuId not in (10001,10003);

+-------+------+

| stuId | name |

+-------+------+

| 10002 | Tom  |

+-------+------+

1 row in set (0.00 sec)

between…and

与 > and < 不同，between and表示在什么之间，会取等于两个数字之间的值

mysql> select * from student where stuId>10001 and stuId<10003;

+-------+------+

| stuId | name |

+-------+------+

| 10002 | Tom  |

+-------+------+

1 row in set (0.00 sec)

mysql> select * from student where stuId between 10001 and 10003;

+-------+-------+

| stuId | name  |

+-------+-------+

| 10001 | Jerry |

| 10002 | Tom   |

| 10003 | Kaly  |

+-------+-------+

3 rows in set (0.00 sec)

mysql> select * from student where stuId not between 10001 and 10003;

Empty set (0.00 sec)

is null

查询数据为空的实体

mysql>  select * from teacher;

+----+------+--------+----------+

| id | name | phone  | address  |

+----+------+--------+----------+

|  1 | Tom  | NULL   | 暂时未知 |

|  2 | Tom  | NULL   | 暂时未知 |

|  3 | Tom  | 123456 | ShangHai |

|  4 | Tom  | NULL   | NULL     |

+----+------+--------+----------+

4 rows in set (0.00 sec)

mysql> select * from teacher where address is null;

+----+------+-------+---------+

| id | name | phone | address |

+----+------+-------+---------+

|  4 | Tom  | NULL  | NULL    |

+----+------+-------+---------+

1 row in set (0.00 sec)

mysql> select * from teacher where address is not null;

+----+------+--------+----------+

| id | name | phone  | address  |

+----+------+--------+----------+

|  1 | Tom  | NULL   | 暂时未知 |

|  2 | Tom  | NULL   | 暂时未知 |

|  3 | Tom  | 123456 | ShangHai |

+----+------+--------+----------+

3 rows in set (0.00 sec)

聚合函数

sum、avg、count等函数的使用



mysql> select * from score;

+----+---------+---------+-------+

| id | chinese | english | math  |

+----+---------+---------+-------+

|  1 |   87.50 |   88.00 | 90.90 |

|  2 |   89.70 |   78.90 | 80.90 |

|  3 |   77.00 |   79.80 | 97.00 |

+----+---------+---------+-------+

3 rows in set (0.00 sec)

mysql> select sum(chinese) from score;

+--------------+

| sum(chinese) |

+--------------+

|       254.20 |

+--------------+

1 row in set (0.00 sec)

mysql> select avg(chinese) from score;

+--------------+

| avg(chinese) |

+--------------+

|    84.733333 |

+--------------+

1 row in set (0.00 sec)

mysql>  select count(chinese) from score;

+----------------+

| count(chinese) |

+----------------+

|              3 |

+----------------+

1 row in set (0.00 sec)

模糊查询

利用like使用百分号(%)和问号(?)通配符查询数据

%匹配一个或者多个字符
_只能匹配一个字符

mysql> select * from info;

+----+-------+------+--------+---------+

| id | name  | age  | gender | address |

+----+-------+------+--------+---------+

|  1 | Tom   |   21 | 男     | 上海    |

|  2 | Jerry |   19 | 男     | 上海    |

|  3 | Blue  |   23 | 男     | 北京    |

|  4 | KaLy  |   20 | 女     | 北京    |

|  5 | Amy   |   18 | 女     | 上海    |

|  6 | Tonny |   20 | 男     | 北京    |

|  7 | Sally |   21 | 女     | 北京    |

+----+-------+------+--------+---------+

7 rows in set (0.00 sec)

mysql> select * from info where name like '%l%';

+----+-------+------+--------+---------+

| id | name  | age  | gender | address |

+----+-------+------+--------+---------+

|  3 | Blue  |   23 | 男     | 北京    |

|  4 | KaLy  |   20 | 女     | 北京    |

|  7 | Sally |   21 | 女     | 北京    |

+----+-------+------+--------+---------+

3 rows in set (0.00 sec)

#匹配了所有含l的对象

mysql> select * from info where name like 'To_';

+----+------+------+--------+---------+

| id | name | age  | gender | address |

+----+------+------+--------+---------+

|  1 | Tom  |   21 | 男     | 上海    |

+----+------+------+--------+---------+

1 row in set (0.00 sec)
#只有Tom，未匹配Tonny

group by分组查询

配合聚合函数一起使用

mysql> select avg(age) as '平均年龄',gender as '性别' from info group by gender;

+----------+------+

| 平均年龄 | 性别 |

+----------+------+

| 19.6667  | 女   |

| 20.7500  | 男   |

+----------+------+

2 rows in set (0.07 sec)

mysql> select avg(age) as '平均年龄',address as '地区' from info group by address;

+----------+------+

| 平均年龄 | 地区 |

+----------+------+

| 19.3333  | 上海 |

| 21.0000  | 北京 |

+----------+------+

2 rows in set (0.10 sec)

mysql> select avg(age) as '平均年龄',gender as '性别', address as '地区' from info group by gender,address;

+----------+------+------+

| 平均年龄 | 性别 | 地区 |

+----------+------+------+

| 18.0000  | 女   | 上海 |

| 20.5000  | 女   | 北京 |

| 20.0000  | 男   | 上海 |

| 21.5000  | 男   | 北京 |

+----------+------+------+

4 rows in set (0.09 sec)

group_concat

例：按性别聚合名字



mysql> select group_concat(name) as 'name',gender as '性别' from info group by gender;

+----------------------+------+

| name                 | 性别 |

+----------------------+------+

| KaLy,Amy,Sally       | 女   |

| Tom,Jerry,Blue,Tonny | 男   |

+----------------------+------+

2 rows in set (0.00 sec)