8 查询数据
8.1 基本查询语句
select语句的基本格式是:
select
{* | 字段1[,字段2,...]}
[
from 表1,表2...
[where 表达式]
[group by <分组条件>]
[having 条件表达式 [{操作 表达式}...]]
[order by [排序条件]]
[limit 行数 [offset 偏移量]]
]
select [字段1,字段2,...,字段n]
from [表或视图]
where [查询条件];
先建表并插入数据:
create table fruits(
f_id char(10) not null,
s_id int,
f_name char(255) not null,
f_price decimal(8,2) not null,
primary key(f_id)
);
insert into fruits
values('a1',101,'苹果',5.2),('b1',101,'黑莓',10.2),
('bs1',102,'橙子',11.2),('bs2',105,'甜瓜',8.2),
('t1',102,'香蕉',10.3),('t2',102,'葡萄',5.3),
('o2',103,'椰子',9.2),('c0',101,'樱桃',3.2),
('a2',103,'杏子',2.2),('l2',104,'柠檬',6.4),
('b2',104,'番茄',8.6),('m1',106,'芒果',15.6),
('m2',105,'xbaby',3.6),('t4',107,'xbababa',3.6),
('m3',105,'xxtt',11.6),('b5',107,'xxxx',3.6);
+------+------+---------+---------+
| f_id | s_id | f_name | f_price |
+------+------+---------+---------+
| a1 | 101 | 苹果 | 5.20 |
| a2 | 103 | 杏子 | 2.20 |
| b1 | 101 | 黑莓 | 10.20 |
| b2 | 104 | 番茄 | 8.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | 橙子 | 11.20 |
| bs2 | 105 | 甜瓜 | 8.20 |
| c0 | 101 | 樱桃 | 3.20 |
| l2 | 104 | 柠檬 | 6.40 |
| m1 | 106 | 芒果 | 15.60 |
| m2 | 105 | xbaby | 3.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | 椰子 | 9.20 |
| t1 | 102 | 香蕉 | 10.30 |
| t2 | 102 | 葡萄 | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+---------+---------+
8.2 单表查询
8.2.1 查询所有字段
select * from fruits;select f_id,s_id,f_name,f_price from fruits;
8.2.2 查询指定字段
select 字段1[,字段2,...] from 表名;
select f_name from fruits;select f_name,f_price from fruits;
8.2.3 查询指定记录
select 字段1[,字段2,...] from 表名 where 查询条件;
select f_name,f_price from fruits where f_price > 10;
where字节判断符:
- = <> != < <= > >=
between and
in \ not in
like : 模糊查找(通配符'%')
is null : 空值
and
or
8.2.7 空值查询
创建数据表的时候,设计者可以指定某列是否可以包含空值null.
空值不同于0,也不同于空字符串.空值一般表示数据未知
不适用或将在以后添加数据.在select语句中可以使用 is null
子句查询某字段内容为空记录
8.2.10 查询结果不重复
select distinct 字段名 from 表名;
select distinct s_id from fruits;
8.2.11 对查询结果排序
order by 字段1[,字段2,...] [ASC | DESC]
select f_name,f_price from fruits
order by f_name,f_price desc;
8.2.12 分组查询
group by 字段1[,字段2,...] [having 条件表达式]
group by 通常和集合函数一起使用,例如
max() min() count() sum() avg()
- having与where都是用来过滤数据,区别:
having用在数据分组之后进行过滤,
where在分组之前用来选择记录,
另外where排除的记录不再包括在分组中
select s_id,count(*) as total from fruits
group by s_id;
select s_id,count(f_name) from fruits
group by s_id having count(f_name) > 1;
select s_id,count(f_name) from fruits
group by s_id having count(f_name) > 1
order by count(f_name);
- cookie:
在MySQL中可以写成select s_id,count(f_name) as c from fruits
group by s_id having c > 1;PostgreSQL会报错字段'c'不存在
8.2.13 用limit现在查询结果的数量
limit 行数 [offset 偏移量]
select * from fruits
order by f_price ASC
limit 3 offset 2;
8.3 使用集合函数查询
count() 计数
select count(*) as c from fruits;sum() 求和
select sum(f_price) as s from fruits
where s_id = 101;
- avg() 平均数
select avg(f_price) as s from fruits
where s_id = 101;
- max() 最大
select max(f_price) as s from fruits;
- min() 最小
select max(f_price) as s from fruits;
8.4 连接查询
8.4.1 内连接查询
inner join
先创建表suppliers并插入数据
create table suppliers(
s_id int primary key,
s_name varchar(50) not null,
s_city varchar(50) not null
);
insert into suppliers
values(101,'周通','天津'),(102,'张良宇','上海'),
(103,'蔡宇航','北京'),(104,'彭田杰','郑州'),
(105,'金鑫','*'),(106,'雷统江','九江'),
(107,'邵鹏飞','武汉');
select suppliers.s_id,s_name,f_name,f_price from fruits,suppliers
where fruits.s_id = suppliers.s_id;
select suppliers.s_id,s_name,f_name,f_price from fruits
inner join suppliers on fruits.s_id=suppliers.s_id;
8.4.2 外连接查询
- left join(左连接)
返回包括左表中所有记录和右表中连接字段相等的记录 - right join(右连接)
返回包括右表中所有记录和左表中连接字段相等的记录
select suppliers.s_id,s_name,f_name,f_price from fruits
left join suppliers on fruits.s_id = suppliers.s_id;
select suppliers.s_id,s_name,f_name,f_price from suppliers
right join fruits on fruits.s_id = suppliers.s_id;
- 注:
当两表中有相同的字段,就需要用完全限定表名,格式为"表名.列名"
8.4.3 复合条件连接查询
select suppliers.s_id,s_name,f_name,f_price from fruits
inner join suppliers on fruits.s_id = suppliers.s_id
order by f_price;
8.5 子查询
8.5.1 带any,some关键字的子查询
any和some关键字是同义词,表示满足其中任一条件
先建表tb11 tb12并插入数据
create table tb11(num1 int not null);
create table tb12(num2 int not null);
insert into tb11 values(1),(3),(5),(99);
insert into tb12 values(2),(4),(8),(16);
select num1 from tb11 where num1 > any (
select num2 from tb12
);
+------+
| num1 |
+------+
| 3 |
| 5 |
| 99 |
+------+
8.5.2 带all关键字的子查询
使用all关键字时需要满足所有内层查询条件
select num1 from tb11 where num1 > all (
select num2 from tb12
);
+------+
| num1 |
+------+
| 99 |
+------+
8.5.3 带exists关键字的子查询
exists关键字后面的参数是一个任意的子查询,系统对子查询进行计算以
判断它是否返回行.如果至少返回一行,那么exists的结果为true,此
时外层查询语句进行查询;如果子查询没有返回任何行,那么exists
返回结果为false,此时外层语句将不进行查询
select * from fruits where f_price >10.2 and exists
(select s_name from suppliers where s_id=107);
8.5.4 带in关键字的子查询
利用in关键字进行子查询时,内层查询语句仅仅返回一个数据列,这个数据
列里的值将提供给外层查询语句进行比较操作
先创建表customers并插入数据
create table customers(
c_id char(10) primary key,
c_name varchar(255) not null,
c_emil varchar(50) null
);
insert into customers
values('10001','RedHook','LMing@163.com'),
('10002','Stars','Jerry@outlook.com'),
('10003','RedHook',null),
('10004','JOTO','sam$hotmail.com');
再创建表orders并插入数据
create table orders(
o_num int null,
o_date date not null,
c_id varchar(50) not null
);
insert into orders
values(30001,'2018-09-01 00:00:00','10001'),
(30002,'2018-09-12 00:00:00','10003'),
(30003,'2018-09-30 00:00:00','10004'),
(null,'2018-10-03 00:00:00','10002'),
(30004,'2018-10-03 00:00:00','null'),
(30005,'2018-10-08 00:00:00','10001');
select o_num from orders where c_id in (
select c_id from customers where c_name='RedHook'
);
8.5.5 带比较运算符的子查询
< = >= <= != <>
select s_id f_name from fruits where s_id <> (
select s1.s_id from suppliers as s1
where s1.s_city = '天津'
);
8.6 合并查询
利用union关键字,可以给出多条select语句,并将它们的结果组合成
单个结果集.合并时,两个表对应的列数和数据类型必须相同.各
个select语句之间使用 union或 union all关键字分隔.union
不使用all,执行的时候删除重复的记录,返回的行都是唯一的;使
用关键字all的作用是不删除重复行也不对结果进行自动排序
select 字段1[,字段2,...] from 表1
union [all] select 字段1[,字段2,...] from 表2;
select s_id,f_name,f_price from fruits where f_price < 9
union select s_id,f_name,f_price from fruits
where s_id in (101,103);
8.7 为表和字段取别名
8.7.1 为表取别名
表名 [as] 表别名select * from orders as o where o.o_num=30001;
8.7.2 为字段取别名
列名 [as] 列别名
select f1.f_name as fn, f1.f_price as fp
from fruits as f1 where f1.f_price <8;
8.8 使用正则表达式查询
PostgreSQL中正则表达式的操作符使用方法如下:
- ~ 匹配正则表达式,区分大小写
- ~* 匹配正则表达式,不区分大小写
- !~ 不匹配正则表达式,区分大小写
- !~ 不匹配正则表达式,不0区分大小写
8.8.1 查询以特定字符或字符串开头的记录 ^
select * from fruits where f_name ~ '^x';
- cookie:
MySQL中正则语法与PostgreSQL略有不同,关键字是regexpselect * from fruits where f_name regexp '^x';
8.8.3 用'.'符号代替字符串中的任意一个字符
select * from fruits where f_name ~ '.子';
8.8.4 使用'*'和'+'匹配多个字符
'*'匹配前面的字符任意次,'+'匹配前面字符至少一次select * from fruits where f_name ~ 'ba*';
8.8.5 匹配指定字符串
匹配多个字符串时,多个字符串之间使用分隔符'|'隔开select * from fruits where f_name ~ '子';
8.8.6 匹配指定字符中的任意一个 []
select * from fruits where f_name ~ '[果子]';
8.8.7 匹配指定字符以外的字符 !~
select * from fruits where f_name !~ '[果子]';
8.8.8 使用{M}或者{M,N}指定字符串连续出现的次数
'字符串{n,m}',表示匹配前面的字符不少于n次,不多于m次select * from fruits where f_name ~ 'x{2,}';