8.1 开端
细说查询语句
8.2 select
可以查询不存在的表，也可以做计算，别名操作使用as 或者省略
mysql> select 2*7 as res; +-----+ | res | +-----+ | 14 | +-----+ 1 row in set (0.00 sec)
8.3 from
来自哪张表，返回笛卡尔积
mysql> select * from stu; +-------+-------+ | stuId | name | +-------+-------+ | 4 | frank | +-------+-------+ 1 row in set (0.00 sec) mysql> select * from eatery; +----+----------+-------+ | id | money | stuId | +----+----------+-------+ | 1 | 20.5000 | NULL | | 2 | 78.6000 | 4 | | 3 | 99.9000 | NULL | | 4 | 748.4000 | 4 | | 5 | 748.4000 | NULL | +----+----------+-------+ 5 rows in set (0.00 sec) mysql> select * from stu,eatery; +-------+-------+----+----------+-------+ | stuId | name | id | money | stuId | +-------+-------+----+----------+-------+ | 4 | frank | 1 | 20.5000 | NULL | | 4 | frank | 2 | 78.6000 | 4 | | 4 | frank | 3 | 99.9000 | NULL | | 4 | frank | 4 | 748.4000 | 4 | | 4 | frank | 5 | 748.4000 | NULL | +-------+-------+----+----------+-------+ 5 rows in set (0.00 sec)
8.4 dual
dual表丝滑默认的伪表，你可以在没有表的情况下指定一个虚拟的表名
mysql> select 2*7 as res from dual; /*计算器*/ +-----+ | res | +-----+ | 14 | +-----+ 1 row in set (0.00 sec)
8.5 where
1.用来筛选
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)
2.设置< > = != 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)
8.6 in
用in代替等号的一种，或者用not in代替不等号
mysql> select * from teacher where address in ('ShangHai'); +----+------+--------+----------+ | id | name | phone | address | +----+------+--------+----------+ | 3 | Tom | 123456 | ShangHai | +----+------+--------+----------+ 1 row in set (0.00 sec) mysql> select * from teacher where address not in ('ShangHai'); +----+------+-------+----------+ | id | name | phone | address | +----+------+-------+----------+ | 1 | Tom | NULL | 暂时未知 | | 2 | Tom | NULL | 暂时未知 | +----+------+-------+----------+ 2 rows in set (0.00 sec)
8.7 between…and
与 > and < 不同，between and表示在什么之间，会取等于两个数字之间的值
mysql> select * from teacher where id >1 and id < 4; +----+------+--------+----------+ | id | name | phone | address | +----+------+--------+----------+ | 2 | Tom | NULL | 暂时未知 | | 3 | Tom | 123456 | ShangHai | +----+------+--------+----------+ 2 rows in set (0.00 sec) mysql> select * from teacher where id between 1 and 4; +----+------+--------+----------+ | 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 not between 1 and 4; Empty set (0.00 sec)
8.8 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)
8.9 聚合函数
sum、avg、count等函数的使用
mysql> select * from score; +------+---------+---------+------+ | id | chinese | english | math | +------+---------+---------+------+ | 1 | 99 | 99 | 99 | | 2 | 77 | 94 | 96 | +------+---------+---------+------+ 2 rows in set (0.00 sec) mysql> select sum(chinese) from score; +--------------+ | sum(chinese) | +--------------+ | 176 | +--------------+ 1 row in set (0.00 sec) mysql> select avg(chinese) from score; +--------------+ | avg(chinese) | +--------------+ | 88.0000 | +--------------+ 1 row in set (0.00 sec) mysql> select count(chinese) from score; +----------------+ | count(chinese) | +----------------+ | 2 | +----------------+ 1 row in set (0.00 sec)
统计所有数据的总数
count(1)计算一共有多少符合条件的行，1并不是表示第一个字段，而是表示一个固定值
count(*)（是针对全表）将返回表格中所有存在的行的总数包括值为null的行
count(列名)（是针对某一列）将返回表格中某一列除去null以外的所有行的总数
mysql> select count(*) from score; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) mysql> select count(1) from score; +----------+ | count(1) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec)
8.10 第三方客户端的使用
学习使用Navicat Premium 15
8.11 like模糊查询
% 表示任意0个或多个字符
_ 表示任意单个字符
mysql> select * from teacher where name like 'T%'; +----+------+--------+----------+ | 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 name like 'T_'; Empty set (0.00 sec) mysql> select * from teacher where name like 'To_'; +----+------+--------+----------+ | 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)
8.12 order by 排序查询
desc表示降序，asc表示升序
mysql> select * from score; +------+---------+---------+------+ | id | chinese | english | math | +------+---------+---------+------+ | 1 | 99 | 99 | 99 | | 2 | 77 | 94 | 96 | +------+---------+---------+------+ 2 rows in set (0.00 sec) mysql> select * from score order by chinese desc; +------+---------+---------+------+ | id | chinese | english | math | +------+---------+---------+------+ | 1 | 99 | 99 | 99 | | 2 | 77 | 94 | 96 | +------+---------+---------+------+ 2 rows in set (0.00 sec) mysql> select * from score order by chinese asc; +------+---------+---------+------+ | id | chinese | english | math | +------+---------+---------+------+ | 2 | 77 | 94 | 96 | | 1 | 99 | 99 | 99 | +------+---------+---------+------+ 2 rows in set (0.00 sec)
8.13 group by 分组查询
mysql> select 聚合函数 as 'xx' ,分组字段 as 'xx' ' from info group by xx;
mysql> select avg(age) as '年龄' ,gender as '性别' ' from info group by gender;
查询的字段必须是分组字段和聚合函数
8.14 group_concat
concat()函数将多个字符串连接成一个字符串
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
mysql> select * from eatery; +----+----------+-------+ | id | money | stuId | +----+----------+-------+ | 1 | 20.5000 | NULL | | 2 | 78.6000 | 4 | | 3 | 99.9000 | NULL | | 4 | 748.4000 | 4 | | 5 | 748.4000 | NULL | +----+----------+-------+ 5 rows in set (0.00 sec) mysql> select stuId ,group_concat(money) from eatery group by stuId; +-------+--------------------------+ | stuId | group_concat(money) | +-------+--------------------------+ | NULL | 20.5000,99.9000,748.4000 | | 4 | 78.6000,748.4000 | +-------+--------------------------+ 2 rows in set (0.00 sec)
通过使用distinct可以排除重复值
mysql> select stuId ,group_concat(distinct money) from eatery group by stuId; +-------+------------------------------+ | stuId | group_concat(distinct money) | +-------+------------------------------+ | NULL | 20.5000,99.9000,748.4000 | | 4 | 78.6000,748.4000 | +-------+------------------------------+ 2 rows in set (0.00 sec)
使用order by子句对结果中的值进行排序
mysql> select stuId ,group_concat(distinct money order by money desc) from eatery group by stuId; +-------+--------------------------------------------------+ | stuId | group_concat(distinct money order by money desc) | +-------+--------------------------------------------------+ | NULL | 748.4000,99.9000,20.5000 | | 4 | 748.4000,78.6000 | +-------+--------------------------------------------------+ 2 rows in set (0.00 sec)
当然可以非常长，separator是一个字符串值，缺省为一个逗号
mysql> select stuId ,group_concat(distinct money order by money desc separator '||') from eatery group by stuId; +-------+-----------------------------------------------------------------+ | stuId | group_concat(distinct money order by money desc separator '||') | +-------+-----------------------------------------------------------------+ | NULL | 748.4000||99.9000||20.5000 | | 4 | 748.4000||78.6000 | +-------+-----------------------------------------------------------------+ 2 rows in set (0.00 sec)
8.15 having
在 SQL 中增加having子句原因是where关键字无法与合计函数一起使用
having子句可以让我们筛选分组后的各组数据。
mysql> select avg(money) as '$' ,stuId as 'stuId' from eatery group by stuId having $>400; +--------------+-------+ | $ | stuId | +--------------+-------+ | 413.50000000 | 4 | +--------------+-------+ 1 row in set (0.00 sec)
8.16 limit
limit子句被用于强制select语句返回指定的记录数，初始记录行的偏移量是0而不是1
mysql> select * from eatery limit 0,2; +----+---------+-------+ | id | money | stuId | +----+---------+-------+ | 1 | 20.5000 | NULL | | 2 | 78.6000 | 4 | +----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from eatery limit 3; +----+---------+-------+ | id | money | stuId | +----+---------+-------+ | 1 | 20.5000 | NULL | | 2 | 78.6000 | 4 | | 3 | 99.9000 | NULL | +----+---------+-------+ 3 rows in set (0.00 sec)
8.17 distinct all
去重
mysql> select * from eatery; +----+----------+-------+ | id | money | stuId | +----+----------+-------+ | 1 | 20.5000 | NULL | | 2 | 78.6000 | 4 | | 3 | 99.9000 | NULL | | 4 | 748.4000 | 4 | | 5 | 748.4000 | NULL | +----+----------+-------+ 5 rows in set (0.00 sec) mysql> select distinct money from eatery; +----------+ | money | +----------+ | 20.5000 | | 78.6000 | | 99.9000 | | 748.4000 | +----------+ 4 rows in set (0.00 sec)
