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)