单表查询:
SELECT 语句定义:
SELECT语句可以实现对表的选择、投影、及连接操作。通常生成一个临时表。
语法格式如下:
SELECT [ALL | DISTINCT] 输出列表表达式,···
[FROM 表名1 [,表名2]···] /*FROM子句*/
[WHERE 条件] /*WHERE子句*/
[GROUP BY {列名 | 表达式 | 列编号}
[ASC |DESC],··· /*GROUP BY子句*/
[HAVING 条件] /*HAVING子句*/
[ORDER BY {列名 | 表达式 | 列编号]
[ASC | DESC],···] /*GROUP BY子句*/
[LIMIT {[偏移量,]行数|行数OFFSET偏移量}] /*LIMIT子句*/
SELECT语句功能强大,有很多子句,所有被使用的字句必选语法说明中显示的顺序严格排序。
选择列:
选择指定的列:
SELECT 表达式;
例:查询bookstore数据库的members表中各会员的姓名、联系电话和注册时间。
USE bookstore
select 姓名,联系电话,注册时间
from members;
运行结果:
当使用SELECT 语句指定列的位置上使用“*”时,表示选择的所有列,拿members表为例,代码如下:
SELECT *FROM members;
运行结果如下:
定义列别名:
查询结果中的列使用自己选择的列标题时,可以在列名之后使用as子句来改查询结果的列名,代码如下:
SELECT 列名 [AS] 别名;
AS也可以选择不写,直接在中间打一个”空格“别名也可以
例:查询book表中图书类别为”计算机“的图书书名,作者和出版社,结果中各列的标题分别指定为name、auther和publisher。
select 书名 as name,作者 as auther,出版社 as publisher
from book
where 图书类别='计算机';
或
select 书名 name,作者 auther,出版社 publisher
from book
where 图书类别='计算机';
替换查询结果中的数据:
语法格式如下:
CASE
WHEN 条件1 THEN 表达式1
WHEN 条件2 THEN 表达式2
······
ELSH 表达式n
END
语法说明如下.
CASE 表达式以 CASE开始,以END结束,MySQL从条件1开始判断,条件1成立输出表达式1,结束;若条件1不成立,判断条件2,若条件2成立,输出表达式2后结束.....如果条件都不成立,输出表达式n。
例:查询book表中的图书编号、书名和数量,对其库存数量按以下规则进行替换:若数量为空值,则替换为“尚未进货”;若数量小于5本,替换为“需进货”;若数量在5~50本的范围内,替换为“库存正常”;若数量大于50本,替换为“库存积压”。列标题更改为“库存”。
select 图书编号,书名,
case
when 数量 is null then '尚未进货'
when 数量 <5 then '需进货'
when 数量 >=5 and 数量<=50 then '库存状态'
else '库存积压'
end as 库存
from book;
计算列值:
输出SELECT语句时,可以在结果中输出对列值进行计算后的值,即SELECT子句可使用表达式作为结果。
例:对sell表中已发货的记录计算订购金额(订购金额=订购册数*订购单价),并显示图书编号和订购金额。代码如下:
select 图书编号,round(订购册数*订购单价,2)as 订购金额
from sell
where 是否发货='已发货';
运行结果
其中,ROUND函数用于获得一个数四舍五入后的数值,逗号后面的数字用来指定小数的位数。
清楚结果集中的重复行:
使用DISTINCT关键字可以消除结果集中的重复行,保证行的唯一性,其格式如下:
SELECT DISTINCT 列名1[,列名2···];
例:对book表值选择图书类别和出版社两列,清楚结果集中的重复行。
select distinct 图书类别,出版社 from book;
运行结果如下图:
WHERE子句:
语句格式如下:
WHERE 判定结果;
语法说明
判定运算:结果为TRUE、FALSH或UNKNOWN,格式如下。
表达式 {= | < | <= | > | >= | <=> | <> | != } 表达式 /*比较运算*/
| 表达式 [ NOT ] LIKE 表达式 /*LIKE表达式*/
| 表达式 [ NOT ] BETWEEN 表达式1 AND 表达式2 /*指定范围*/
| 表达式 IS [ NOT ] NULL /*判断是否空值*/
| 表达式 [NOT] IN (子查询 | 表达式1 [,···表达式n] ) /*IN子句*/
比较运算:
MySQL支持的比较运算符有=(等于)、<(小于)、<=(小于等于)、>(大于)、>=(大于等于)、<=>(相等或都等于空)、<>(不等于)、!=(不等于)
例1:查询bookstore数据库book表中书名为’网页程序设计‘的记录
select *from book
where 书名='网页程序设计';
例2:查询book表中单价大于30元的图书情况。
select *from book
where 单价>30;
例3:查询sell表中还未收货的订单号、订单时间和是否收货。
select 订单号,订购时间,是否收货
from sell
where 是否收货<=>null;
逻辑运算:
运算符 | 运算规则 | 运算符 | 运算规则 |
NOT或! | 逻辑非 | OR或|| | 逻辑或 |
AND或&& | 逻辑与 | XOR | 逻辑异或 |
逻辑运算操作的结果是“1”或“0”,分别表示“TRUE”或“FALUE”。假设有关系表达式X和Y,其进行逻辑运算结果如下表:
X | Y | NOT X | X AND Y | X OR Y | X XOR Y |
0 | 0 | 1 | 0 | 0 | 0 |
0 | 1 | 1 | 0 | 1 | 1 |
1 | 0 | 0 | 0 | 1 | 1 |
1 | 1 | 0 | 1 | 1 | 0 |
说明 | 如果X是TRUE,那么示 0例结果是FALSH;如 果X是FALSE,那么示例 结果是TRUE | 如果X和Y都是TRUE,那么示例的结果TRUE,否则示例结果是FALSE | 如果X或Y任一是TRUE,那么示例的结果是TRUE,否则示例结果是FALSE | 如果X和Y不相同,那么示例的结果是TRUE,否则示例的结果是FALSE |
例1:查询sell表中已收货且已结清的订单情况。
select *from sell
where 是否收货='已收货' and 是否结清='已结清';
例2:查询book表中“清华大学出版社”“北京大学出版社”出版的价格大于等于35元的图书.
select *from book
where (出版社='清华大学出版社' or 出版社='北京大学出版社')
and 单价>=35;
或
select *from book
where (出版社='清华大学出版社' and 单价>=35)
or (出版社='北京大学出版社' and 单价>=35);
模糊匹配:
LIKE运算符用于指出一个字符串是否与指定的字符串相匹配,其运算对象可以是char、varchar、text、datetime等类型,返回逻辑值TRUE或FALSE.
表达式 [NOT ] LIKE 表达式
使用LIKE进行模式匹配时,常使用特殊符号“_”"%"进行模糊查询,“%”代表0个或多个字符,“_”代表单个字符。
例1:查询members表中姓“李”的用户的用户名、姓名及注册时间。
select 用户名,姓名,注册时间
from members
where 姓名 like '李%';
例2:查询book表中图书编号倒数第二位6的图书的图书编号和书名。
select 图书编号,书名 from book
where 图书编号 like '%6_';
范围比较:
用于范围比较的关键字有BETWEEN和IN两个。
当要查询的条件是某个值的范围时,可以使用BETWEEN关键字。BETWEEN关键字指出查询范围,格式如下。
表达式 [NOT] BETWEEN 表达式1 AND 表达式2
说明:若表达式expression的值在表达式1与表达式2之间(包括这两个值),返回TRUE,否则返回FALSE;使用NOT时,返回值刚好相反。表达式1的值不能大于表达式2的值。
例1:查询book表中2020你年出版的图书情况。
select *from book
where 出版时间 between '2020-01-01' and '2020-12-31';
上面的语句与下面的语句等价
select *from book
where 出版时间>='2020-1-1' and 出版时间 <='2020-12-31';
若要查询book表中不是在2020年出版的所有图书的情况,则要使用NOT
SELECT *from book
where 出版时间 not between '2020-1-1' and '2020-12-31';
上面的语句与下面的语句等价
select *from book
where 出版时间<='2020-1-1' or 出版时间>='2020-12-31';
使用IN关键字可以指定一个值表,值表中列出了所有可能的值,当字段的值与值表中的任意一个子匹配时,即返回TRUE,否则返回FALSE.格式如下
表达式 [NOT ] IN (子查询 | 表达式1 [,···表达式n]
例1:查询book表中“高等教育出版社”,''北京大学出版社",“人民邮电出版社”出版图书的情况
select *from book
where 出版社 IN('高等教育出版社','北京大学出版社','人民邮电出版社');
上面的语句与下面的语句等价
select *from book
where 出版社='高等教育出版社' or 出版社='北京大学出版社' or 出版社='人民邮电出版社';
空值比较:
当需要判定一个表达式的值是否为空值时,可使用IS NULL关键字,格式如下
表达式 IS [NOT] NULL
若表达式的值为空值,返回TRUE,否则返回FALSE;当使用NOT时,结果刚好相反。
例:查询sell表中尚未发货的订单记录。
select *from sell
where 是否发货 IS NULL;
多表查询:
FROM子句:
格式如下。
FROM 表名1 [ [AS] 别名1] [,表名2[[AS] 别名2]] ··· /*查询表*/
| JOIN子句 /*连接表*/
表名1 [ [AS] 别名1]:与列别名一样,可以使用AS子句为表指定别名。
例:从members表中检索出所有客户的信息,并使用表别名Users.
select *from members as users;
多表连接:
连接方式:
(1)全连接
全连接是指将每个表的每行都与其他表中的每行交叉,以产生所有可能的组合,列包含了所有表中出现的列,也就是笛卡尔积。
(2)内连接
全连接得到的表产生了数量非常多行,因为得到的行数为每个表中行数之积。但是,全连接产生的表在大多数情况下都没有意义。在这样的情形下,通常要设定条件来得到结果集减少且有意义的表,这样的连接即为内连接。如果设定的条件时等值条件,则也叫等值连接。
(3)外连接
外连接包括左外连接(LEDT OUTER JOIN)和右外连接(RIGHT OUTER JOIN)两种。
左外连接:结果表中除了匹配行外,还包括左表有但不匹配的行,对于这样的行,从右表中选择的列被设置为NULL。
右外连接:结果表中除了匹配行外,还包括右表有单左表中不匹配的行,对于这样的行,从左表中选择的列被设置为NULL.
例:查找bookstore数据库中客户订购的图书书名、订购册数和订购时间。
select book.书名,sell.订购册数,sell.订购时间
from book,sell
where book.图书编号=sell.图书编号;
JOIN连接:
使用JOIN关键字建立多表数据连接时,JOIN子句中定义了如何使用JOIN关键字连接表。
JOIN子句格式如下
表名1 INNER JOIN 表名2
| 表名1 {LEFT| RIGHT} [OUTER] JOIN 表名2
ON 连接条件 | USING (列名)
使用JOIN关键字的连接主要分为2种。
(1)内连接
指定了INNER的连接是内连接。内连接是在FROM子句产生的中间结果中应用ON条件后得到的结果。内连接是系统默认的,可以省略INNER关键字。
例:用JOIN关键字表达下列查询:查找购买了《MySQL数据库》且订购数量大于5本的图书信息。
select 书名,订购册数
from book join sell
on book.图书编号=sell.图书编号
where 书名='MySQL数据库' AND 订购册数>5;
内连接还可以用于多个表来连接
例:用JOIN关键字表达下列查询:查找购买了《MySQL数据库》且订购数量大于5本的图书信息及用户姓名和订购册数。
select book.图书编号,姓名,书名,订购册数
from sell join book on book.图书编号=sell.图书编号
join members on sell.用户号=members.用户号
where 书名='MYSQL数据库' AND 订购册数>5;
可以将一个表与它自身进行连接,称为自连接。若要在一个表中查找具有相同列值的行,则可以使用自连接。使用自连接时,需为表指定两个别名,且对所有列的引用均要用别名限定。
例:查找bookstore数据库中订单不同,同属编号相同的图书的订单号、图书编号和订购册数。
select distinct a.订单号,a.图书编号,a.订购册数
from sell as a join sell as b
on a.图书编号=b.图书编号 and a.订单号!=b.订单号;
ON条件也可以换成USING子句。USING子句用于为一系列的列进行命名。这些列必须同时在两个表中存在。
例:查找members表中所有订购过图书的用户的姓名。
select distinct 姓名 from members
join sell using (用户号);
(2)外连接
指定了OUTER关键字的连接为外连接。
例1:查找所有“计算机”类图书的图书编号、单价及订购了图书的客户的用户号,若用户从未订购过,也要包括其情况。
select book.图书编号,book.单价,用户号
from book left outer join sell
on book.图书编号=sell.图书编号 and 图书类别='计算机';
例2:查找了订购了图书的男性用户的订单号、图书编号、订购册数以及用户的姓名和联系电话。
select 订单号,图书编号,订购册数,members.姓名 ,members.联系电话
from sell right join members
on members.用户号=sell.用户号 and 性别='男';
子查询:
IN子查询用于进行一个给定值是否在子查询结果集中的判断。
语句格式如下。
表达式[NOT] IN(子查询)
语法说明如下。
●当表达式与子查询的结果表中的某个值相等时,IN谓词返回TRUE,否则返回FALSE;若使用了 NOT,则返回的值刚好相反。
IN(子查询):只能返回一列数据。对于较复杂的查询,可以使用嵌套的子查询。
例:查找Bookstore数据库中“张三”的订单信息。
因为含有订单信息的sell表中不包含用户的姓名,只有用户的用户号,而要查找“张三”的订单信息,先要知道“张三”的用户号,所以先要在members表中查找“张三”的用户号,再根据用户号查询订单信息。
select *from sell
where 用户号 in
(select 用户号 from members where 姓名='张三');
例:查找购买了除《MySQL数据库》以外图书的用户信息。要查找用户信息,先要知道用户的用户号,而要知道购买了除《MySQL数据库》以外图书的用户号,可以按图书编号在sell表中查找,但是《MySQL数据库》的图书编号要通过查找book表才可以获得。
select *from members where 用户号 in
(select 用户号 from sell where 图书编号 not in
(select 图书编号 from book where 书名='MySQL数据库'));
比较子查询:
这种子查询可以认为是IN子查询的扩展,它使表达式的值与子查询的结果进行比较运算。
语法格式如下。
表达式{<|<=|=|>|>=|!=|<>} { AIL| SOME | ANY)(子查询)
语法说明如下。
表达式:与子查询结果集进行比较的表达式。
ALL| SOME|ANY:说明对比较运算的限制。如果子查询的结果集只返回一行数据,可以通过比较运算符直接比较;如果子查询的结果集返回多行数据,则需要用{ALL|SOME|ANY}来限定。
ALL指定表达式要与子查询结果集中的每个值都进行比较,当表达式的每个值
都满足比较的关系时,才返回TRUE,否则返回FALSE。
SOME 和 ANY是同义词,表示表达式只要与子查询结果集中的某个值满足比
较的关系,就返回TRUE,否则返回 FALSE。
例:查找购买了图书编号为“TP.2525”的图书的用户信息。
select *from members where 用户号=ANY
(select 用户号 from sell
where 图书编号='TP.2525');
例:查找book表中所有比“网页设计”类图书价格都高的图书基本信息。
select 图书编号,图书类别,单价 from book
where 单价>all
(select 单价 from book where 图书类别='网页设计');
例:查找sell表中订购册数不少于图书编号为“ts.3035”的图书的任何一个订单的订购册数的订单信息
select 图书编号,订购册数 from sell where 订购册数>=some
(select 订购册数 from sell where 图书编号='ts.3035');
EXISTS子查询:
EXISTS位子用于测试子查询的结果是否为空表,若子查询的结果集不为空,EXISTS返回TRUE,否则返回FALSE。
EXISTS子查询的格式如下。
[NOT] EXISTS (子查询)
例:查找每次订购10本以上图书的用户的姓名。
select 姓名 from members where EXISTS
(select *from sell
where 用户号=members.用户号 and 订购册数>10);
联合查询:
语法格式如下。
SELECT 语句1 UNION [UNION选择] SELECT 语句2;
语法说明如下。
UNION选项:分为ALL和DISTINCT,联合查询时默认为DISTINCT,去掉结果集中的重复行;如果要保留结果集中的所有行,必须指定ALL。
例:将sell表中用户“C0138”的订单和图书编号为“TP.2525”的订单合并。
查询sell表中用户“C01380”的订单的SQL语句如下:
select 订单号,用户号,图书编号,订购册数 from sell where 用户号='C0138'
UNION ALL
SELECT 订单号,用户号,图书编号,订购册数 from sell where 图书编号='TP.2515';
聚合函数:
函数名 | 说明 |
COUNT | 求组中项数,返回int类型整数 |
MAX | 求最大值 |
MIN | 求最小值 |
SUM | 返回表达式中所有值的和 |
AVG | 求组中值的平均值 |
COUNT函数:
聚合函数中经常使用COUNT函数,用于统计表中满足条件的行数或总行数,返回SELECT语句检索到的行中非NULL值的数目,若找不到匹配的行,则返回0.
语法格式如下。
COUNT ({ALL|DISTINCT}]表达式|*)
ALL|DISTINCT:ALL表示对所有值进行运算,DISTINCT表示去除重复值,默认为ALL。
例:求会员总人数
select count(*) as '会员数' from members;
MAX函数和MIN函数:
语法格式如下。
MAX /MIN([ALL|DISTINCT] 表达式)
例:求图书编号为“TP.3035”的图书的最高订购册数和最低订购册数。
select MAX(订购册数),MIN(订购册数)
from sell
where 图书编号='Ts.3035';
SUM函数和AVG函数:
语法格式如下。
SUM/AVG([ALL|DISTINCT] 表达式)
例:求图书编号为“Ts.3035”的图书的订购总册数和平均册数。
select sum(订购册数) as 订购总册数,avg(订购册数) as 平均册数
from sell
where 图书编号='Ts.3035';
GROUP BY子句:
GROUP BY子句主要用于根据字段对行进行分组。
语法格式如下。
GROUP BY {列名|表达式} [ASC|DESC],···[WITN ROLLUP]
ASC表示升序
DESC表示降序
例:输出book表中的图书类别名。
select 图书类别
from book
group by 图书类别;
HAVING子句:
使用HAVING子句的目的与WHERE子句类似,不同的是WHERE子句用来在FROM 子句之后进行,而HAVING子句用来在GROUP BY子句之后选择行。
语法格式如下。
HAVING 条件
例:查找sell表中用户的订单数在2笔及以上且每笔订购册数都在5本以上的用户.
select 用户号
from sell
where 订购册数>5
group by 用户号
having count(*)>=2;
ORDER BY子句:
使用ORDER BY子句后可以保证结果中的行按一定顺序排列。
语法格式如下。
ORDER BY(列名|表达式 | 列编号} [ASC|DESC] ,···
例:将book表中的记录按出版时间的先后排序。
select *from book
order by 出版时间;
例:将sell表中的记录按订购册数从小到大排序
select *from sell
order by 订购册数 desc;
LIMIT子句:
LIMIT子句是SELECT语句的最后一个子句,主要用于限制返回的行数。
语法格式如下。
LIMIT {[偏移量,] 行数 OFFSET 偏移量}
语法说明如下、
偏移量:指定返回的第一行的偏移量。
行数:返回行数。
例:查找book表中从第4条记录开始的5条记录。
select *from book
order by 图书编号
limit 3,5;