16 KiB

Raw Permalink Blame History

查询数据

一、单表查询

1.查询所有字段

（1）在 select 语句中使用 * 查询所有字段

语法：select * from 表名;

（2）在 select 语句中指定所有字段（字段名可以变换顺序）

语法：select f_id,s_id,f_name,f_price from fruits;

2.查询指定字段

（1）查询单个字段：

语法：select f_name from fruits;

（2）查询多个字段：

语法：select f_id,s_id,f_name,f_price from fruits;

3.查询指定记录（where）

可以跟 =,>,<,<>,!=,<=,>=,between,and

查询 f_name 是 xxxx 的字段

语法：select f_name,f_price from fruits where f_name='xxxx';

查询 f_price 不等于 10.2 的字段

语法：select f_name,f_price from fruits where f_price<>10.2;

查询 f_price 在5和10之间的字段

语法：select f_name,f_price from fruits where f_price between 5 and 10;（包含端点值）

查询 f_price 不在5和10之间的字段

语法：select f_name,f_price from fruits where f_price not between 5 and 10;（不包含端点值）

4.使用in关键字查询

使用 in 关键字将检索条件括起来，检索条件之间用逗号隔开，只需要满足条件范围内一个值即为匹配项。

语法：select s_id,f_name,f_price from fruits where s_id in(101,102);

可以使用关键字 not in 检索不在条件范围内的记录

语法：select s_id,f_name,f_price from fruits where s_id not in(101,103);

5.带 like 的字符匹配查询（ % _ ）

（1）%匹配任意长度的字符，包括0个字符

以b开头字符查询

语法：select f_name,f_price from fruits where f_name like 'b%';

查询水果名中包含p字符的记录

语法：select f_name,f_price from fruits where f_name like '%p%';

查询以b开头以y结尾

语法：select f_name,f_price from fruits where f_name like 'b%y';

查询以y结尾且y的前面有且仅有4个字母的记录

语法：select f_name,f_price from fruits where f_name like '____y';

6.查询空值（is 【not】null）

查询 f_name 为空

语法：select f_name,f_price from fruits where f_name is null;

查询 f_name 不为空

语法：select f_name,f_price from fruits where f_name is not null;

7.带 and 的多条件查询（相当于‘与’，同时满足多个条件）

查询id为101同时价格大于等于5的字段

语法：select f_name,f_price from fruits where s_id=101 and f_price>=5;

语法：select s_id,f_name,f_price from fruits where s_id=101 and f_price>=5 and f_name like 'a%';

8.带 or 的多条件查询（相当于‘或’，满足一个条件即可）

查询i为101或者103的字段

语法：select s_id,f_name,f_price from fruits where s_id=101 or s_id=103;

语法：select s_id,f_name,f_price from fruits where s_id in(101,103);

这里可以用in操作符实现相同功能

and 的优先级高于 or

在实际开发中使用 in 操作符开发语句，更加简单明了，并且 in 执行速度要快于 or ，更重要的是使用 in 操作符，可以执行更加复杂的嵌套查询

9.查询结果不重复（distinct）

查询 id 不重复

语法：select distinct s_id from fruits ;

10.对查询结果排序(默认升序【asc】，降序【desc】)

（1）单列排序

升序

语法： select distinct s_id from fruits order by s_id;

降序

语法：select distinct s_id from fruits order by s_id desc;

语法：select s_id,f_name from fruits order by f_name;

（2）多列排序

升序

语法：select s_id,f_name from fruits order by s_id,f_name;

先按照 s_id 排序， s_id 相同时再按照 s_name 排序

降序

语法：select s_id,f_name from fruits order by s_id desc,f_name desc;

11.分组查询（group by，通常和集合函数配合使用）

（1）创建分组

语法：select s_id,count(*) from fruits group by s_id;

语法：select s_id,count(*) as total from fruits group by s_id;

as 后面是别名

若查看每个供应商提供的水果种类名称，可以在group by中使用group_concat()函数，将每个分组中各个字段的值列举出来，例如：语法：select s_id,group_concat(f_name) from fruits group by s_id;

语法：select s_id,group_concat(f_name) as names from fruits group by s_id;

（2）使用having过滤分组

语法：select s_id,group_concat(f_name) as names from fruits group by s_id having count(f_name)>1;、

分组后过滤用having，分组前用where进行分组

select s_id,group_concat(f_name) as names from fruits where s_id>=104 group by s_id having count(f_name)>1;

（3）在group by子句中使用 with rollup

在查出所☐一条记录，该记录出的所有记录的总和

语法： select s_id,count(*) from fruits group by s_id with rollup;

注意： with rollup和order by不能同时使用互斥

（4）多字段分组

语法：select s_id,f_name,count(*) from fruits group by s_id,f_name;

（5）group by 和 order by一起使用

语法：select s_id,count(*) from fruits group by s_id order by s_id;

先分组再排序

12.使用limit限制查询结果数量

语法：select * from fruits limit 4,3;

语法：select * from fruits limit 3 offset 4;

4代表是起始位置，3是指三条记录

语法： select * from fruits limit 3;

第一个参数（位置偏移量）可以省略不写，表示返回前三条记录

二、使用集合函数查询

1.count()函数

（1）select count (*) as name from 表名; 计算表中总行数不管是不是空值

（2）count(字段名) 计算指定列下的总行数，忽略空值

（3）和group by关键字一起使用语法：select o_num,count(*) from orderitems group by o_num;

select sum(quantity) from orderitems where o_num=3005;

2.sum()函数（求和）

语法：select o_num,sum(quantity) from orderitems group by o_num;

3.avg()函数（平均值）

语法：select o_num,avg(item_price) from orderitems group by o_num;

4.max()函数（最大值）

select o_num,max(item_price) from orderitems group by o_num;

5.min()函数（最小值）

select o_num,min(item_price) from orderitems group by o_num;

max和min不仅可以对数值类型，也可对字符类型求最大最小

三、连接查询

语法：

select 查询的列 from 表名连接类型表名2 on 连接条件

【where 筛选条件】【group by 分组字段（having分组后筛选条件）】【order by 排序字段】

1.内连接查询（inner join=join）

不满足 on 后连接条件的会直接过滤掉

select student.name,class.name from student join class on student.class_id=class.id where student.name like '%小%';

也可以自连接

2.外连接查询

分为一个主表和一个从表

外连接的查询结果为主表中的所有记录

如果从表中有和它匹配的，则显示匹配结果

如果从表中没有和它匹配的，则显示null

（1）左外连接（left outer join=left join）

select *from student left join class on student.class_id=class.id;//以左表为主若没有与它匹配的是空

（2）右外连接（right outer join=right join）

select *from student right join class on student.class_id=class.id;//以右表为主若没有与它匹配的是空

3.符合条件连接查询

select student.name,class.name from student join class on student.class_id=class_id where student.name like "%小%

等价于

select student.name,class.name from student, class where student.class_id=class_id and student.name like %小%

等价于

select student.name,class.name from student join class on student.class_id-class_id and student.name like %小%

seletc * from student as s1 join student as s2 on s1.id=s2.id；//自连接

四、子查询

五、合并查询结果

六、为表和字段取别名

七、使用正则表达式查询

八、综合案例讲解

上课代码

show database;

use text2;

create table fruits(
f_id char(10) not null primary key,
s_id int not null,
f_name char(25) not null,
f_price decimal(8,2) not null
);

desc fruits;

insert into fruits
values('a1',101,'apple',10.2),
('b1',101,'blackberry,',5.2),
('bs1',102,'orange',11.2),
('bs2',105,'melon',8.2),
('t1',102,'banana',10.3),
('t2',102,'grape',5.3),
('o2',103,'cocount',9.2),
('c0',101,'chaerry',3.2),
('a2',103,'apricot',2.2),
('i2',104,'lemon',6.2),
('d2',104,'berry',7.6),
('m1',106,'mango',15.6),
('m2',105,'xbabay',2.6),
('t4',107,'xbababa',3.6),
('m3',105,'xxtt',11.6),
('b5',107,'xxx',3.6);

select * from fruits;

select f_id,s_id,f_name,f_price from fruits;

select f_name,f_price from fruits where f_price=10.2;
select f_name,f_price from fruits where f_price>10.2;
select f_name,f_price from fruits where f_price<>10.2;

select f_name,f_price from fruits where f_name='xxtt';

select f_name,f_price from fruits where f_price between 5 and 10;

select s_id,f_name,f_price from fruits where s_id in(101,102);

select s_id,f_name,f_price from fruits where s_id not in(101,103);

select f_name,f_price from fruits where f_price not between 5 and 10;

 select f_name,f_price from fruits where f_name like 'b%';

select f_name,f_price from fruits where f_name like 'b%y';

select f_name,f_price from fruits where f_name like '____y';

select f_name,f_price from fruits where f_name is not null;

select f_name,f_price from fruits where s_id=101 and f_price>=5;

select s_id,f_name,f_price from fruits where s_id=101 and f_price>=5 and f_name like 'a%';

select s_id,f_name,f_price from fruits where s_id=101 or s_id=103;

select distinct s_id from fruits ;

select distinct s_id from fruits order by s_id asc;
select distinct s_id from fruits order by s_id desc;

select s_id,f_name from fruits order by f_name;

select s_id,f_name from fruits order by s_id,f_name;

select s_id,count(*) from fruits group by s_id;

select s_id,count(*) as total from fruits group by s_id;

select s_id,group_concat(f_name) from fruits group by s_id;

select s_id,group_concat(f_name) as names from fruits group by s_id;

select s_id,group_concat(f_name) as names from fruits group by s_id having count(f_name)>1;

select s_id,group_concat(f_name) as names from fruits where s_id>=104 group by s_id having count(f_name)>1;

 select s_id,count(*) from fruits group by s_id with rollup;

select s_id,f_name,count(*) from fruits group by s_id,f_name;

select s_id,count(*) from fruits group by s_id order by s_id;

select * from fruits limit 4,3;

create table orderitems(
o_num int not null,
o_item int not null,
f_id char(10) not null,
quantity int not null,
item_price decimal(8,2) not null,
primary key(o_num,o_item)
);

insert into orderitems
values(3001,1,'a1',10,5.20),
(3001,2,'b2',3,7.60),
(3001,3,'bs1',5,11.20),
(3001,4,'bs2',15,9.20),
(3002,1,'b3',2,20.00),
(3003,1,'c0',100,10.00),
(3004,1,'o2',50,2.50),
(3005,1,'c0',5,10.00),
(3005,2,'b1',10,8.99),
(3005,3,'a2',10,2.20),
(3005,4,'m1',5,14.99);

create table customers(
c_id int not null auto_increment,
c_name char(50) not null,
c_address char(50) null,
c_city char(50) null,
c_zip char(50) null,
c_contact char(50) null,
c_email char(255) null,
primary key(c_id)
);

insert customers values
(1001,'Redhook','200 Street','Tianjin','300000','Liming','Lming@163.com'),
(1002,'Stars','333 Fromage lane','Dalian','116000','Zhangbo','Jerry@hotmail.com'),
(1003,'Netbhood','1 Sunny place','Qingdao','266000','Luocong',''),
(1004,'J0t0','829 Riverside drive','Haikou','570000','Yangshan','sam@hotmail.com')
;

select o_num,count(*) from orderitems group by o_num;

select sum(quantity) from orderitems where o_num=3005;

select o_num,sum(quantity) from orderitems group by o_num;

select o_num,avg(item_price) from orderitems group by o_num;

select o_num,max(item_price) from orderitems group by o_num;

select o_num,min(item_price) from orderitems group by o_num;

select min(f_name) from fruits;

在fruits中查询不同供应商供应的价格最低的水果
select s_id,min(f_price) from fruits group by s_id;

select s_id,min(f_price) from fruits group by s_id order by s_id desc;

16 KiB Raw Permalink Blame History Unescape Escape