您的位置: 首页  >  IT文章  >  MySQL基本查询实战

MySQL基本查询实战

分类: IT文章 • 2023-11-07 16:26:54

目录

1）创建测试所用表

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) );
#创建测试所用表一
insert into fruits 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,'coconut','9.2'),
('c0',101,'cherry','3.2'),
('a2',103,'apricot','2.2'),
('l2',104,'lemon','6.4'),
('b2',104,'berry','7.6'),
('m1',106,'mango','15.7'),
('m2',105,'xbabay','2.6'),
('t4',107,'xbababa','2.6'),
('m3',105,'xxtt','11.6'),
('b5',107,'xxxx','3.6');
#向表一批量插入数据

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(50) null,
primary key(c_id)
);
#创建测试所用表二
insert into customers values
(10001,'RedHook','200 Street','Tianjin','300000','LiMing','LMing@163.com'),
(1002,'Stars','333 Fromage Lane','Dalian','116000','Zhangbo','Jerry@hotnail.com'),
(10003,'Netbhood','1 Sunny Place','Qingdao','266000','LuoCong',NULL),
(1004,'JOTO','829 Riverside Drive','Haikou','570000','YangShan','sam@hotmail.com');
#向表二批量插入数据

2）查看两个表中存储的数据

select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| l2   |  104 | lemon      |    6.40 |
| m1   |  106 | mango      |   15.70 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    2.60 |
+------+------+------------+---------+
select * from customers;
+-------+----------+---------------------+---------+--------+-----------+-------------------+
| c_id  | c_name   | c_address           | c_city  | c_zip  | c_contact | c_email           |
+-------+----------+---------------------+---------+--------+-----------+-------------------+
|  1002 | Stars    | 333 Fromage Lane    | Dalian  | 116000 | Zhangbo   | Jerry@hotnail.com |
|  1004 | JOTO     | 829 Riverside Drive | Haikou  | 570000 | YangShan  | sam@hotmail.com   |
| 10001 | RedHook  | 200 Street          | Tianjin | 300000 | LiMing    | LMing@163.com     |
| 10003 | Netbhood | 1 Sunny Place       | Qingdao | 266000 | LuoCong   | NULL              |
+-------+----------+---------------------+---------+--------+-----------+-------------------+

3）只查询fruits表中f_name列的数据

select f_name from fruits;
+------------+
| f_name     |
+------------+
| apple      |
| apricot    |
| blackberry |
| berry      |
| xxxx       |
| orange     |
| melon      |
| cherry     |
| lemon      |
| mango      |
| xbabay     |
| xxtt       |
| coconut    |
| banana     |
| grape      |
| xbababa    |
+------------+

4）只查询fruits表中f_name列和f_price列的数据

select f_name,f_price from fruits;
+------------+---------+
| f_name     | f_price |
+------------+---------+
| apple      |    5.20 |
| apricot    |    2.20 |
| blackberry |   10.20 |
| berry      |    7.60 |
| xxxx       |    3.60 |
| orange     |   11.20 |
| melon      |    8.20 |
| cherry     |    3.20 |
| lemon      |    6.40 |
| mango      |   15.70 |
| xbabay     |    2.60 |
| xxtt       |   11.60 |
| coconut    |    9.20 |
| banana     |   10.30 |
| grape      |    5.30 |
| xbababa    |    2.60 |
+------------+---------+

5）查询fruits表中f_name和f_price的列，并且f_price的数值等于5.2

select f_name,f_price from fruits where f_price=5.2;
+--------+---------+
| f_name | f_price |
+--------+---------+
| apple  |    5.20 |
+--------+---------+

6）查询fruits表中f_name和f_price的列，并且f_price的数值大于或等于10

select f_name,f_price from fruits where f_price>=10;
+------------+---------+
| f_name     | f_price |
+------------+---------+
| blackberry |   10.20 |
| orange     |   11.20 |
| mango      |   15.70 |
| xxtt       |   11.60 |
| banana     |   10.30 |
+------------+---------+

7）查询fruits表中f_name和f_price的列，并且f_price的数值在2到8之间

select f_name,f_price from fruits where f_price between 2 and 8;
+---------+---------+
| f_name  | f_price |
+---------+---------+
| apple   |    5.20 |
| apricot |    2.20 |
| berry   |    7.60 |
| xxxx    |    3.60 |
| cherry  |    3.20 |
| lemon   |    6.40 |
| xbabay  |    2.60 |
| grape   |    5.30 |
| xbababa |    2.60 |
+---------+---------+

8）查询fruits表中的f_name和s_id列，并且s_id的值为101或者103

方法一：

select f_name,s_id from fruits where s_id = 101 or s_id = 103;
+------------+------+
| f_name     | s_id |
+------------+------+
| apple      |  101 |
| apricot    |  103 |
| blackberry |  101 |
| cherry     |  101 |
| coconut    |  103 |
+------------+------+

方法二：

select f_name,s_id from fruits where s_id in (101,103);
+------------+------+
| f_name     | s_id |
+------------+------+
| apple      |  101 |
| apricot    |  103 |
| blackberry |  101 |
| cherry     |  101 |
| coconut    |  103 |
+------------+------+

9）查询fruits表中的f_name和s_id列，并且s_id的值不为101和103

方法一：

select f_name,s_id from fruits where s_id != 101 and s_id != 103;
+---------+------+
| f_name  | s_id |
+---------+------+
| berry   |  104 |
| xxxx    |  107 |
| orange  |  102 |
| melon   |  105 |
| lemon   |  104 |
| mango   |  106 |
| xbabay  |  105 |
| xxtt    |  105 |
| banana  |  102 |
| grape   |  102 |
| xbababa |  107 |
+---------+------+

方法二：

select f_name,s_id from fruits where s_id not in (101,103);
+---------+------+
| f_name  | s_id |
+---------+------+
| berry   |  104 |
| xxxx    |  107 |
| orange  |  102 |
| melon   |  105 |
| lemon   |  104 |
| mango   |  106 |
| xbabay  |  105 |
| xxtt    |  105 |
| banana  |  102 |
| grape   |  102 |
| xbababa |  107 |
+---------+------+

10）模糊查询“%”和“_”的使用

① 查询fruits表中的f_name列，并且值以“b”开头
select f_name from fruits where f_name like 'b%';
+------------+
| f_name     |
+------------+
| blackberry |
| berry      |
| banana     |
+------------+
② 查询fruits表中的f_name列，并且值以“b”开头，以“y”结尾
select f_name from fruits where f_name like 'b%y';
+------------+
| f_name     |
+------------+
| blackberry |
| berry      |
+------------+
③ 查询fruits表中的f_name列，值以“b”开头，以“y”结尾，并且b和y之间有三个字符
select f_name from fruits where f_name like 'b___y';
+--------+
| f_name |
+--------+
| berry  |
+--------+

11）查询fruits表中s_id的值为101并且f_price的值大于2.0的行

 select * from fruits where s_id = 101 and f_price > 2.0;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| b1   |  101 | blackberry |   10.20 |
| c0   |  101 | cherry     |    3.20 |
+------+------+------------+---------+

12）查询fruits表中s_id的值为101或103并且f_price列的值大于5

 select * from fruits where s_id in (101,103) and f_price > 5;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| b1   |  101 | blackberry |   10.20 |
| o2   |  103 | coconut    |    9.20 |
+------+------+------------+---------+

13）查询fruits表中的s_id列，并去除重复值

select distinct s_id from fruits;
+------+
| s_id |
+------+
|  101 |
|  103 |
|  104 |
|  107 |
|  102 |
|  105 |
|  106 |
+------+

14）查询fruits表中的s_id和f_name列，并以s_id对结果进行排序

select s_id,f_name from fruits order by s_id;
+------+------------+
| s_id | f_name     |
+------+------------+
|  101 | apple      |
|  101 | blackberry |
|  101 | cherry     |
|  102 | orange     |
|  102 | banana     |
|  102 | grape      |
|  103 | apricot    |
|  103 | coconut    |
|  104 | berry      |
|  104 | lemon      |
|  105 | melon      |
|  105 | xbabay     |
|  105 | xxtt       |
|  106 | mango      |
|  107 | xxxx       |
|  107 | xbababa    |
+------+------------+

15）查询fruits表中的f_name及f_price列，并以f_name和f_price列进行排序

select distinct f_name,f_price from fruits order by f_name,f_price;
+------------+---------+
| f_name     | f_price |
+------------+---------+
| apple      |    5.20 |
| apricot    |    2.20 |
| banana     |   10.30 |
| berry      |    7.60 |
| blackberry |   10.20 |
| cherry     |    3.20 |
| coconut    |    9.20 |
| grape      |    5.30 |
| lemon      |    6.40 |
| mango      |   15.70 |
| melon      |    8.20 |
| orange     |   11.20 |
| xbababa    |    2.60 |
| xbabay     |    2.60 |
| xxtt       |   11.60 |
| xxxx       |    3.60 |
+------------+---------+

注：多字段排序，如果第一个排序的字段一致，会依靠第二个字段排序，依次类推，如果第一个字段不一样，则直接以第一段来进行排序！

16）查询fruits表中的f_price列，并对结果以降序进行排序

默认是asc升序排序，可以通过关键字DESC更改为降序！

select f_price from fruits order by f_price desc;
+---------+
| f_price |
+---------+
|   15.70 |
|   11.60 |
|   11.20 |
|   10.30 |
|   10.20 |
|    9.20 |
|    8.20 |
|    7.60 |
|    6.40 |
|    5.30 |
|    5.20 |
|    3.60 |
|    3.20 |
|    2.60 |
|    2.60 |
|    2.20 |
+---------+

17）查询fruits中s_id列不同值出现的次数，并对其进行分组显示

调用count(*)函数统计次数，并通过as来对其设置别名，group by来进行分组！

 select s_id,count(*) as total from fruits group by s_id;
+------+-------+
| s_id | total |
+------+-------+
|  101 |     3 |
|  102 |     3 |
|  103 |     2 |
|  104 |     2 |
|  105 |     3 |
|  106 |     1 |
|  107 |     2 |
+------+-------+

18）查询fruits表中每个相同的s_id对应的f_name列的所有值，f_name的值以一行显示，并且其值在1个以上

select s_id,group_concat(f_name) as name from fruits group by s_id having count(f_name) > 1;
+------+-------------------------+
| s_id | name                    |
+------+-------------------------+
|  101 | apple,blackberry,cherry |
|  102 | orange,banana,grape     |
|  103 | apricot,coconut         |
|  104 | berry,lemon             |
|  105 | melon,xbabay,xxtt       |
|  107 | xxxx,xbababa            |
+------+-------------------------+

19）查询customers表中c_email列为空值的行

select * from customers where c_email is null;
+-------+----------+---------------+---------+--------+-----------+---------+
| c_id  | c_name   | c_address     | c_city  | c_zip  | c_contact | c_email |
+-------+----------+---------------+---------+--------+-----------+---------+
| 10003 | Netbhood | 1 Sunny Place | Qingdao | 266000 | LuoCong   | NULL    |
+-------+----------+---------------+---------+--------+-----------+---------+

20）查询fruits表中每个s_id对应的所有f_name值

select s_id,group_concat(f_name) as name from fruits group by s_id having count(f_name) > 1;
+------+-------------------------+
| s_id | name                    |
+------+-------------------------+
|  101 | apple,blackberry,cherry |
|  102 | orange,banana,grape     |
|  103 | apricot,coconut         |
|  104 | berry,lemon             |
|  105 | melon,xbabay,xxtt       |
|  107 | xxxx,xbababa            |
+------+-------------------------+

21）统计fruits表相同s_id值的行有多少？

select s_id,count(*) as total from fruits group by s_id with rollup;
+------+-------+
| s_id | total |
+------+-------+
|  101 |     3 |
|  102 |     3 |
|  103 |     2 |
|  104 |     2 |
|  105 |     3 |
|  106 |     1 |
|  107 |     2 |
| NULL |    16 |
+------+-------+

22）创建所需表并插入数据

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
(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');
#插入数据
select * from orderitems;
+-------+--------+------+----------+------------+
| o_num | o_item | f_id | quantity | item_price |
+-------+--------+------+----------+------------+
| 30001 |      1 | a1   |       10 |       5.20 |
| 30001 |      2 | b2   |        3 |       7.60 |
| 30001 |      3 | bs1  |        5 |      11.20 |
| 30001 |      4 | bs2  |       15 |       9.20 |
| 30002 |      1 | b3   |        2 |      20.00 |
| 30003 |      1 | c0   |      100 |      10.00 |
| 30004 |      1 | o2   |       50 |       2.50 |
| 30005 |      1 | c0   |        5 |      10.00 |
| 30005 |      2 | b1   |       10 |       8.99 |
| 30005 |      3 | a2   |       10 |       2.20 |
| 30005 |      4 | m1   |        5 |      14.99 |
+-------+--------+------+----------+------------+
#查询数据

23） 查询同一个o_num列的quantity（数量）和item_price（价格）相乘结果大于100的行

select o_num,SUM(quantity*item_price) as total from orderitems
group by o_num having total > 100 order by total;
+-------+---------+
| o_num | total   |
+-------+---------+
| 30004 |  125.00 |
| 30005 |  236.85 |
| 30001 |  268.80 |
| 30003 | 1000.00 |
+-------+---------+

24）limit——限制返回的行数

① 仅显示前四行
select * from fruits limit 4;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
+------+------+------------+---------+
② 从第四行开始，显示后三行
select * from fruits limit 4,3;
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b5   |  107 | xxxx   |    3.60 |
| bs1  |  102 | orange |   11.20 |
| bs2  |  105 | melon  |    8.20 |
+------+------+--------+---------+

25）查询每个o_num对应的f_id有几个

select o_num,count(f_id) as items_total from orderitems group by o_num;
+-------+-------------+
| o_num | items_total |
+-------+-------------+
| 30001 |           4 |
| 30002 |           1 |
| 30003 |           1 |
| 30004 |           1 |
| 30005 |           4 |
+-------+-------------+

26）查询o_num为30005的quantity（数量）有多少

select sum(quantity) as items_total from orderitems where o_num = 30005;
+-------------+
| items_total |
+-------------+
|          30 |
+-------------+

27）查询s_id为103的f_price的平均数是多少（s_id的平均价格是多少）

select avg(f_price) as avg_price from fruits where s_id = 103;
+-----------+
| avg_price |
+-----------+
|  5.700000 |
+-----------+

28）查询每个s_id对应的平均价格（f_price）是多少?

select s_id,avg(f_price) as avg_price from fruits group by s_id;
+------+-----------+
| s_id | avg_price |
+------+-----------+
|  101 |  6.200000 |
|  102 |  8.933333 |
|  103 |  5.700000 |
|  104 |  7.000000 |
|  105 |  7.466667 |
|  106 | 15.700000 |
|  107 |  3.100000 |
+------+-----------+

29）查询每个s_id中f_price值最大的行是哪个？

select s_id, max(f_price) as max_price from fruits group by s_id;
+------+-----------+
| s_id | max_price |
+------+-----------+
|  101 |     10.20 |
|  102 |     11.20 |
|  103 |      9.20 |
|  104 |      7.60 |
|  105 |     11.60 |
|  106 |     15.70 |
|  107 |      3.60 |
+------+-----------+
#若要查看最小的行，只需要将max换为min即可。

30）查询每个f_price值最大的值及其所对应的s_id、f_name

select s_id,f_price,f_name from fruits
where f_price in(select max(f_price) from fruits group by s_id);
+------+---------+------------+
| s_id | f_price | f_name     |
+------+---------+------------+
|  101 |   10.20 | blackberry |
|  104 |    7.60 | berry      |
|  107 |    3.60 | xxxx       |
|  102 |   11.20 | orange     |
|  106 |   15.70 | mango      |
|  105 |   11.60 | xxtt       |
|  103 |    9.20 | coconut    |
+------+---------+------------+

31）创建所需表并插入数据

create table suppliers (
s_id int not null auto_increment,
s_name char(50) not null,
s_city char(50) null,
s_zip char(10) null,
s_call char(50) not null,
primary key(s_id) );
#创建测试表一
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 suppliers values
(101,'FastFruit Inc.','tianjin','300000','48075'),
(102,'LT Supplies','chongqing','400000','44333'),
(103,'acme','shanghai','200000','90046'),
(104,'fnk inc.','zhongshan','528437','11111'),
(105,'good set','taivuang','030000','22222'),
(106,'just eat ours','beijing','010','45678'),
(107,'dk inc.','zhengzhou','450000','33332');
#向表一插入数据
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);
#向表二插入数据

32）表联接类型

在进行接下来的查询，有必要说一下多表查询的相关概念！

① 内联接

内联接（inner join）是最常见的一种联接方式，只返回两个数据集合之间匹配关系的行，将位于两个互相交叉的数据集合中重叠部分以内的数据行联接起来。

内联接使用比较运算符进行表间某些列数据的比较操作，并列出这些表中与联接相匹配的数据行。

② 外联接

外联接（outer join）是对内联接的扩充，除了将两个数据集合中重复部分以内的数据行联接起来之外，还可以根据要求返回左侧或右侧表中非匹配的数据或全部的数据。

外联接还可以分为以下几种：

  • 左外联接（left join或left outer join）的结果包括左表的所有行，如果左表的某一行在右表中没有匹配行，则右表返回空值，否则返回相应值；

  • 右外联接（right join或right outer join）是左外联接的反向联接，将返回右表的所有行，如果右表的某一行在左表中没有匹配行，则左表返回空值，否则返回相应值；

  • 全联接（full join 或full outer join）将返回左表和右表中的所有行，当某一行在另一个表中没有匹配行时，另一个表返回空值，否则返回相应值；

33）内联接查询，将两个表的指定列生成一个新表

select suppliers.s_id,s_name,f_name,f_price from fruits inner join suppliers on fruits.s_id = suppliers.s_id;
+------+----------------+------------+---------+
| s_id | s_name         | f_name     | f_price |
+------+----------------+------------+---------+
|  101 | FastFruit Inc. | apple      |    5.20 |
|  103 | acme           | apricot    |    2.20 |
|  101 | FastFruit Inc. | blackberry |   10.20 |
|  104 | fnk inc.       | berry      |    7.60 |
|  107 | dk inc.        | xxxx       |    3.60 |
|  102 | LT Supplies    | orange     |   11.20 |
|  105 | good set       | melon      |    8.20 |
|  101 | FastFruit Inc. | cherry     |    3.20 |
|  104 | fnk inc.       | lemon      |    6.40 |
|  106 | just eat ours  | mango      |   15.70 |
|  105 | good set       | xbabay     |    2.60 |
|  105 | good set       | xxtt       |   11.60 |
|  103 | acme           | coconut    |    9.20 |
|  102 | LT Supplies    | banana     |   10.30 |
|  102 | LT Supplies    | grape      |    5.30 |
|  107 | dk inc.        | xbababa    |    2.60 |
+------+----------------+------------+---------+

34）左外联接查询示例

select customers.c_id,orders.o_num from customers
left outer join orders on customers.c_id = orders.c_id;
+-------+-------+
| c_id  | o_num |
+-------+-------+
| 10001 | 30001 |
| 10003 | 30002 |
| 10001 | 30005 |
|  1002 |  NULL |
|  1004 |  NULL |
+-------+-------+

35）内联接查询时指定其他条件

select customers.c_id,orders.o_num from customers inner join orders on customers.c_id = orders.c_id;
+-------+-------+
| c_id  | o_num |
+-------+-------+
| 10001 | 30001 |
| 10003 | 30002 |
| 10001 | 30005 |
+-------+-------+

相关推荐