MySQL查询数据

于 2020-04-08 10:54:06 发布
阅读量397 收藏
点赞数
分类专栏： 数据库知识点整理 文章标签： mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43422201/article/details/105327988
版权

介绍如何使用SELECT语句查询数据。
SELECT语句的基本格式

SELECT 
				{*|字段列表}
				[FROM <表1>,<表2>...
				WHERE <表达式>
				[GROUP BY <definition>]
				[HAVING <expression>[{<operation> <expression>}...]]
				[ORDER BY <definition>]
				[LIMIT [<offset>,] <row count>]
				]
SELECT [字段1，字段2，...,字段n]
FROM [表或视图]
WHERE [查询条件]；

其中各条子句的含义如下
{*|<字段列表>}包括星号通配符选字段列表，表示查询的字段
FROM <表1>，<表2>…，表1和表2表示查询数据的来源，可以是单个或多个。
WHERE 字句是可选项，表示限定查询行必须满足的查询条件。
GROUP BY <字段> 告诉MySQL如何显示查询出来的数据，并按照指定的字段分组。
ORDER BY<字段> 告诉MySQL按什么样的顺序显示查询出来的数据。
LIMIT [< offset >,] < row count>告诉MYSQL每次显示查询出来的数据条数。

下面将结合例子说明如和使用SELECT从单个表中获取数据。

创建样例数据表，插入数据表数据

//创建fruits
create table fruits(
    -> f_id char(10) not null,
    -> s_id int not null,
    -> f_name char(255) not null,
    -> f_price decimal(8,2) not null,
    -> primary key(f_id));
//创建customers
 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(10) null,
    -> c_contact char(50) null,
    -> c_email char(255) null,
    -> primary key (c_id));
//创建orderitems
 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));
//创建orders表
create table orders(o_num int not null auto_increment,
    -> o_date datetime not null,
    -> c_id int not null,
    -> primary key(o_num));
//插入数据
insert into fruits(f_id, s_id, f_name, f_price)values
    -> ('a1', 101,'apple', 5.2),
    -> ('b1', 101,'blackberry',10.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,'cherry',3.2),
    -> ('a2',103,'apricot',2.2),
    -> ('12',104,'lemon',6.4),
    -> ('b2',104,'berry',7.6),
    -> ('m1',106,'mango',15.7),
    -> ('m2',105,'xbaby',2.6),
    -> ('t4',107,'xbababa',3.6),
    -> ('m3',105,'xxtt',11.6),
    -> ('b5',107,'xxxx',3.6);
 insert into customers (c_id, c_name, c_address, c_city, c_zip, c_contact, c_email) values
    -> (10001, 'RedHook', '200 Street', 'Tianjin', '300000', 'LiMing', 'LiMing@163.com'),
    -> (10002, 'Stars', '333 Fromage Lane', 'Dalian', '116000', 'Zhaogbo', 'Jerry@hotmail.com'),
    -> (10003, 'Netbhood', '1 Sunny Place', 'Qingdao', '266000', 'LuoCong',null),
    -> (10004, 'JOTO', '829 Riverside Drive', 'Haiko', '570000', 'YangShan', 'sam@hotmail.com');
    insert into orderitems(o_num, o_item, f_id, quantity, item_price) values(30001,1,'a1',10,5.2),
    -> (30001, 2,'b2',3,7.6),
    ->  (30001, 3,'bs1',5,11.2),
    -> (30001, 4,'bs2',15,9.2),
    ->  (30002,1,'b3', 2,20.0),
    ->  (30003, 1,'c0',100,10),
    -> (30004, 1,'o2',50,2.50),
    -> (30005,1,'c0',5,10),
    -> (30005,2,'b1',10,8.99),
    -> (30005,3,'a2',10,2.2),
    -> (30005,4,'m1',5,14.99);
insert into orders values(30001,'2008-09-01',10001),
    -> (30002, '2008-09-12', 10003),
    -> (30003, '2008-09-30', 10004),
    -> (30004, '2008-10-03', 10005),
    -> (30005, '2008-10-08', 10001);

查询所有字段

//使用通配符*查询所有字符。SELECT * FROM 表名
SELECT * FROM fruits;
//指定所有字段 SELECT 字段1，字段2,...字段n FROM 表名
SELECT f_id, s_id, f_name, f_price FROM fruits;

查询指定记录

//SELECT 字段1，字段2，... FROM 表名 WHERE 查询条件
//查询价格为10.2元的水果名称
select f_name, f_price from fruits where f_price=10.2;
//查询apple的水果价格
select f_name, f_price from fruits where f_name='apple';
//使用IN关键字,NOT IN关键字
//查询s_id为101和102的记录
select s_id, f_name, f_price from fruits where s_id in (101,102) order by f_name;
//使用BETWEEN AND的范围查询
//查询价格在2.00和10.20之间的水果
select f_name, f_price from fruits where f_price between 2.00 and 10.20;
//带LIKE, %,_的字符匹配查询
//%匹配任意长度的字符，甚至包括零字符
//查找b开头的水果
select f_id, f_name from fruits where f_name like 'b%';
//_一次只能匹配一个字符
//查询以y结尾，五个字母的记录
select f_id, f_name from fruits where f_name like '____y';
//查询空值
//查询c_email为空的记录
select c_id ,c_name, c_email from customers where c_email is null;
//带AND，OR的多条件查询
//查询s_id=101, f_price大于等于5的水果价格和名称
select f_name, f_price from fruits where s_id=101 and f_price >=5;
//查询s_id=101或者s_id=102的水果供应商的f_price和f_name
select f_price, f_name from fruits where s_id=101 or s_id=102;
//使用DISTINCT关键字消除重复 select DISTINCT 字段名 from 表名;
//查询fruits表中s_id字段的值
select distinct s_id from fruits；
//使用order by排序，缺省升序asc,desc
//单列排序，依据f_name排序
select f_name from fruits order by f_name;
//多列排序，先按照f_name排序，再按照f_price排序
select f_name, f_price from fruits order by f_name, f_price;
//使用group by 分组查询 [GROUP BY 字段] [HAVING <条件表达式>]
//根据s_id对fruits中的数据分组
select s_id, count(*) as total from fruits group by s_id;
//使用GROUP_CONCAT() ，根据s_id对fruits中的数据进行分组，显示水果名
select s_id, group_concat(f_name) from fruits group by s_id;
//使用HAVING过滤分组, 根据s_id对fruits中的数据进行分组，显示水果种类大于1的水果名
select f_id, group_concat(f_name) from fruits group by s_id having count(f_name)>1;
//使用with rollup，列出查询数据的总和
select s_id, count(*) as total from fruits group by s_id with rollup;
//多字段分组，根据s_id和f_name分组
select *from fruits group by s_id, f_name;
//使用group by和order by一起使用
//查询订单价格大于100的订单号和总订单价格，并按照价格排序
select o_num, sum(quantity*item_price) as ordertotal from orderitems 
group by o_num 
having ordertotal>100 
order by ordertotal;
//使用limit限制查询结果的数量 LIMIT[位置偏移量,] 行数
//在fruits表中，使用limit子句，返回从第五个开始，行数长度为3的记录
select *from fruits limit 4,3;

连接查询

在关系数据库管理系统中，表建立时个数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当查询数据时，通过连接操作查询出存放在多个表中不同实体的信息。

内连接查询（inner join）

内连接使用比较运算符进行某些列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组成新记录。在内连接查询中，只有满足条件的记录才能出现在结果关系中。

//查询f_name, f_price, s_id, s_name
select suppliers.s_id, s_name, f_name, f_price from fruits, suppliers where fruits.s_id=suppliers.s_id;
//使用INNER JOIN语法进行内连接查询
select suppliers.s_id, s_name, f_name, f_price from fruits inner join suppliers on
 fruits.s_id=suppliers.s_id;
//查询供应f_id='a1'的水果供应商提供的水果种类(需要根据s_id找到水果种类）
select f1.f_id, f1.f_name from fruits as f1, fruits as f2 where f1.s_id=f2.s_id and f2.f_id='a1';

外连接查询
外连接查询将查询多个表中相关联的行，返回查询结果集合中的不仅包含符合连接条件的行，而且还包括左表，右表或两个边接表（全外连接）的所有数据行。
外连接分为左外连接（左连接）或右外连接（右连接）。
LEFT　JOIN（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。
RIGHT　JOIN（右连接）：返回包括右表中所有记录和左表中连接字段相等的记录。

//内连接，左外连接，右外连接的区别
//内连接
select * from customers  join orders on customers.c_id=orders.c_id;
+-------+----------+---------------------+---------+--------+-----------+-----------------+-------+---------------------+-------+
| c_id  | c_name   | c_address           | c_city  | c_zip  | c_contact | c_email         | o_num | o_date              | c_id  |
+-------+----------+---------------------+---------+--------+-----------+-----------------+-------+---------------------+-------+
| 10001 | RedHook  | 200 Street          | Tianjin | 300000 | LiMing    | LiMing@163.com  | 30001 | 2008-09-01 00:00:00 | 10001 |
| 10003 | Netbhood | 1 Sunny Place       | Qingdao | 266000 | LuoCong   | NULL            | 30002 | 2008-09-12 00:00:00 | 10003 |
| 10004 | JOTO     | 829 Riverside Drive | Haiko   | 570000 | YangShan  | sam@hotmail.com | 30003 | 2008-09-30 00:00:00 | 10004 |
| 10001 | RedHook  | 200 Street          | Tianjin | 300000 | LiMing    | LiMing@163.com  | 30005 | 2008-10-08 00:00:00 | 10001 |
+-------+----------+---------------------+---------+--------+-----------+-----------------+-------+---------------------+-------+
//左外连接
select * from customers left outer join orders on customers.c_id=orders.c_id;
+-------+----------+---------------------+---------+--------+-----------+-------------------+-------+---------------------+-------+
| c_id  | c_name   | c_address           | c_city  | c_zip  | c_contact | c_email           | o_num | o_date              | c_id  |
+-------+----------+---------------------+---------+--------+-----------+-------------------+-------+---------------------+-------+
| 10001 | RedHook  | 200 Street          | Tianjin | 300000 | LiMing    | LiMing@163.com    | 30001 | 2008-09-01 00:00:00 | 10001 |
| 10003 | Netbhood | 1 Sunny Place       | Qingdao | 266000 | LuoCong   | NULL              | 30002 | 2008-09-12 00:00:00 | 10003 |
| 10004 | JOTO     | 829 Riverside Drive | Haiko   | 570000 | YangShan  | sam@hotmail.com   | 30003 | 2008-09-30 00:00:00 | 10004 |
| 10001 | RedHook  | 200 Street          | Tianjin | 300000 | LiMing    | LiMing@163.com    | 30005 | 2008-10-08 00:00:00 | 10001 |
| 10002 | Stars    | 333 Fromage Lane    | Dalian  | 116000 | Zhaogbo   | Jerry@hotmail.com |  NULL | NULL                |  NULL |
+-------+----------+---------------------+---------+--------+-----------+-------------------+-------+---------------------+-------+
//右外连接
select * from customers right outer join orders on customers.c_id=orders.c_id;
+-------+----------+---------------------+---------+--------+-----------+-----------------+-------+---------------------+-------+
| c_id  | c_name   | c_address           | c_city  | c_zip  | c_contact | c_email         | o_num | o_date              | c_id  |
+-------+----------+---------------------+---------+--------+-----------+-----------------+-------+---------------------+-------+
| 10001 | RedHook  | 200 Street          | Tianjin | 300000 | LiMing    | LiMing@163.com  | 30001 | 2008-09-01 00:00:00 | 10001 |
| 10001 | RedHook  | 200 Street          | Tianjin | 300000 | LiMing    | LiMing@163.com  | 30005 | 2008-10-08 00:00:00 | 10001 |
| 10003 | Netbhood | 1 Sunny Place       | Qingdao | 266000 | LuoCong   | NULL            | 30002 | 2008-09-12 00:00:00 | 10003 |
| 10004 | JOTO     | 829 Riverside Drive | Haiko   | 570000 | YangShan  | sam@hotmail.com | 30003 | 2008-09-30 00:00:00 | 10004 |
|  NULL | NULL     | NULL                | NULL    | NULL   | NULL      | NULL            | 30004 | 2008-10-03 00:00:00 | 10005 |
+-------+----------+---------------------+---------+--------+-----------+-----------------+-------+---------------------+-------+

//LEFT JOIN左连接，在customers表和orders表中，查询所有客户，包括没有订单的客户，SQL语言如下
select customers.c_id, orders.o_num from customers left outer join orders on customers.c_id=orders.c_id;
//RIGHT JOIN右连接，在customers表和orders表中，查询所有订单，包括没有客户的订单，SQL语言如下
select customers.c_id, orders.o_num from customers right outer join orders on customers.c_id=orders.c_id;
//复合条件连接查询
//在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询，并对查询结果排序，SQL语言如下
select suppliers.s_id, s_name, f_name, f_price from fruits inner join suppliers on fruits.s_id=suppliers.s_id order by fruits.s_id;

子查询
子查询是指一个查询语句嵌套在另一个查询语句内部的查询。在select子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个或者多个表。子查询中常用的操作符ANY（SOME），ALL，IN，EXISTS。

ANY，SOME，ALL的子查询

//定义两个表tb11和tb12：
create table tb11(num1 int not null);
create table tb12(num2 int not null);
//分别向两个表中插入数据
insert into tb11 values(1), (5), (13), (27);
insert into tb12 values(6), (14), (11), (20);
//返回tb12表的所有num2列，然后将tb11中的num1的值与之进行比较，只要大于num2的任何一个值，即为符合查询条件的结果。
select num1 from tb11 where num1>any(select num2 from tb12);
//返回tb11表中比tb12表num2列所有值都大的值
select num1 from tb11 where num1>all(select num2 from tb12);

带EXISTS关键字的子查询
EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断他是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。

//查询suppliers表中是否存在s_id=107的供应商，如果存在，则查询fruits表中的记录
select * from fruits where exists (select s_name from suppliers where s_id=107);
//内层查询结果表明suppliers表中存在s_id=107的记录，因此EXISTS表达式返回true,外层查询语句接受true之后对表fruits进行查询，返回所有的记录。
//查询supplieres表中是否存在s_id=107的供应商，如果存在，则查询fruits表中是否有f_price>10.20的记录。
select * from fruits where f_price>10.20 and exists(select s_name from suppliers where s_id=107);

NOT EXISTS与EXISTS相反，子查询如果至少返回一行，那么NOT EXISTS的结果就是false，此时外层查询将不进行查询，如果子查询没有返回任何行，那么NOT EXISTS返回的结果是true，此时外层结果将进行查询。

//查询suppliers表中是否存在s_id=107的供应商，如果不存在，则查询fruits表中的记录
select * from fruits where not exists (select s_name from suppliers where s_id=107);

IN关键字查询

IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列中值将提供给外层查询语句进行比较操作。NOT IN 作用与EXISTS相反，使用方法类似。

//在orderitems表中查询f_id为c0的订单号，并根据订单号查询具有订单号的用户c_id
select c_id from orderitems where c_num in (select c_num from orderitems where f_id='c0');

比较运算符的子查询

//在suppliers表中拆线呢s_city等于‘Tianjin'的供应商s_id， 然后再fruits中查询所有该供应商提供的水果的种类
select s_id, f_name from fruits where s_id = (select s1.s_id from suppliers as s1 where s1.s_city ='Tianjin');

合并查询结果
使用UNION关键字，可以给出多条select语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。UNION不使用关键字ALL，执行的时候删除重复的记录，所有返回的行都是唯一的；使用关键字ALL，不删除重复行也不对结果进行自动排序

select column,.. from table1
union [all]
select column,.. from table2;

//查询所有价格小于9的水果的信息，查询s_id等于101和103的水果的信息。
select * from fruits where f_price<9
union all
select * from fruits where s_id in (101,103);

为表名和列名取别名

表名 [as] 表别名
列名 [as] 列别名
查询suppliers表中字段s_name和s_city，只用CONCAT函数连接这两个值，并取别名suppliers_title
select concat(trim(s_name,'(', trim(s_city),')' from suppliers order by s_name;
+--------------------------------------------+
| concat(trim(s_name),'(', trim(s_city),')') |
+--------------------------------------------+
| ACME(ShangHai)                             |
| DK Inc.(Zhengzhou)                         |
| FastFruit Inc(Tianjin)                     |
| FNK Inc.(Zhongshan)                        |
| Good Set(Taiyuang)                         |
| Just Eat Ours(Beijing)                     |
| LT Suppliers(Chongqing)                    |
+--------------------------------------------+

使用正则表达式REGEXP查询

正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。

选项说明例子匹配值示例
^匹配文本的开始字符'^b’匹配以字母b开头的字符串book，big
$匹配文本的结束字符'st$'匹配以st结尾的字符串test，resist
.匹配任何单字符‘b.t’匹配b，t之前任何字符的字符串bit，bat
*匹配零个或多个在它前面的字符'f*n’匹配字符n前面有任意次数ffn，fan，faan
+匹配前面的字符一次或多次'ba+'匹配以b开头后面紧跟至少有一个aba，bay，bare
<字符串>匹配包含指定的字符串的文本‘fa’fan,faa,far
[字符集合]匹配字符集合中的任何一个字符'[xz]'匹配x或zdizzy，zebra
[^]匹配不在括号中的任何字符'[^abc]'匹配任意不含a,b,c字符串desk, fox
字符串{n,}匹配前面的字符串至少n次b{2}匹配2个或更多个bbbb，bb,bbbbb
字符串{n,m}匹配前面的字符串至少n次，至多m次b{2,4}匹配最少2个，最多4个bbb,bbb,bbbb
会唱歌的猪233
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值