Mysql查询数据（四）

一、基本查询语句

mysql> insert into fruits(f_id,s_id,f_name,f_price) values(‘a1’,101,’apple’,5.2),
-> (‘b1’,101,’blackberry’,10.2);
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0

mysql> select *from fruits;
+——+——+————+———+
| f_id | s_id | f_name | f_price |
+——+——+————+———+
| a1 | 101 | apple | 5.2 |
| b1 | 101 | blackberry | 10.2 |
+——+——+————+———+
2 rows in set

二、单表查询（查询单个或多个字段）

mysql> select f_id,f_name from fruits;
+——+————+
| f_id | f_name |
+——+————+
| a1 | apple |
| b1 | blackberry |
+——+————+
2 rows in set

//查询指定记录

mysql> insert into fruits values(‘b4’,106,’Xxx’,1.4);
Query OK, 1 row affected

mysql> select f_name,f_price from fruits where f_price=1.4;
+——–+———+
| f_name | f_price |
+——–+———+
| melon | 1.4 |
| Xxx | 1.4 |
+——–+———+

//查询价格小于5的
mysql> select f_name,f_price from fruits where f_price<5;
+——–+———+
| f_name | f_price |
+——–+———+
| banana | 4.1 |
| melon | 1.4 |
| Xxx | 1.4 |
+——–+———+
3 rows in set

//带between and的范围查询where f_price between 2 and 10;
//带like的字符串匹配where f_name like ‘b%’;
//查询属性为空的字段where c_email is null;
//带add的多条件查询where s_id =’101’ and s_price>10;
//带or的多条件查询where s_id=101 or f_price>10;
//select distinct s_id from fruits;查询结果不重复
//对查询结果排序

mysql> select f_name from fruits order by f_name;
+————+
| f_name |
+————+
| apple |
| banana |
| blackberry |
| melon |
| Xxx |
+————+
5 rows in set

//desc关键字实现降序操作，先实现价格降序，在实现名字升序
mysql> select f_name,f_price from fruits order by f_price desc,f_name;
+————+———+
| f_name | f_price |
+————+———+
| blackberry | 10.2 |
| apple | 5.2 |
| banana | 4.1 |
| melon | 1.4 |
| Xxx | 1.4 |
+————+———+
5 rows in set

分组查询

group by 关键字与集合函数一起使用例如max(),min(),count(),sum(),avg();

创建分组
mysql> select s_id,count(*) as total from fruits group by s_id;
+——+——-+
| s_id | total |
+——+——-+
| 101 | 2 |
| 103 | 1 |
| 104 | 1 |
| 106 | 1 |
+——+——-+
4 rows in set

//根据s_id对fruits表中的数据进行分组(使用group_concat()函数)，将每个供应商的水果名称显示出来

mysql> select s_id,group_concat(f_name) as names from fruits group by s_id;
+——+——————+
| s_id | names |
+——+——————+
| 101 | apple,blackberry |
| 103 | banana |
| 104 | melon |
| 106 | Xxx |
+——+——————+
4 rows in set

使用HAVING过滤分组

//group by与having一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示，水果种类大于1的分组信息
mysql> select s_id,group_concat(f_name) as names from fruits group by s_id having count(f_name)=1;
+——+——–+
| s_id | names |
+——+——–+
| 103 | banana |
| 104 | melon |
| 106 | Xxx |
+——+——–+
3 rows in set

在group by 子句中使用with rollup增加一个查询语句，该记录计算查询所有记录的总和 select s_id,count(*) as total from fruits group by s_id with rollup;

多字段分组 :select *from fruits group by s_id,f_name;
group by 与order by一起使用(实现总订单价格排序显示)：select o_num,SUM(quantity*item_price) as
orderTotal from orderitems by o_num having SUM(quantity*item_price)>=100
order by orderTotal;

三、使用聚合函数查询

1）count()函数计算表中总行数包括空值，count(字段名)计算指定列下总的行数忽略空值

select count(*) as fruitnum from fruits;
select count(s_name) as fruitname from fruits;

2）sum函数返回指定列值的总和

select sum(quantity) as itemsTotal from orderitem where o_num=3005;

3)avg函数计算返回的行数和每一行的数据和，求得指定列数据的平均值

select avg(f_price) as avgPrice from fruits where s_id=101;
select s_id avg(f_price) as avgPrice from fruits group bys_id;

4)Max函数 返回指定列的最大值

select max(f_price) as MaxPrice from fruits ;
select s_id,Max(f_price) as maxprice from fruits group by s_id;

四、连接查询

1）内连接查询(使用比较运算符进行表件某些数据的比较操作)

//两个表中有共同的s_id属性从fruits里查找f_name与f_price，从suppliers里查询s_id，s_name

mysql> select suppliers.s_id,s_name,f_name,f_price
-> from fruits,suppliers where fruits.s_id=suppliers.s_id;
+——+—————+————+———+
| s_id | s_name | f_name | f_price |
+——+—————+————+———+
| 101 | FastFruit Inc | apple | 5.2 |
| 103 | Good Set | banana | 4.1 |
| 101 | FastFruit Inc | blackberry | 10.2 |
| 104 | ACME | melon | 1.4 |
| 106 | FuXx | Xxx | 1.4 |
+——+—————+————+———+
5 rows in set

//使用inner join语法 on与where后面条件一样
mysql> 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.2 |
| 103 | Good Set | banana | 4.1 |
| 101 | FastFruit Inc | blackberry | 10.2 |
| 104 | ACME | melon | 1.4 |
| 106 | FuXx | Xxx | 1.4 |
+——+—————+————+———+
5 rows in set

2)外连接查询

查询所有订单包括没有客户的订单
//left join（左连接）
如果左表的某行右表中没有匹配行，则右表显示为空值
mysql> select customers.c_id,orders.o_num from customers left outer join orders on customers.c_id=orders.c_id;
+——+——-+
| c_id | o_num |
+——+——-+
| 1001 | 30001 |
| 1003 | 30002 |
| 1004 | 30003 |
| 1001 | 30005 |
| 1002 | NULL |
+——+——-+
5 rows in set

//right join （右连接）
mysql> select customers.c_id,orders.o_num from customers right outer join orders on customers.c_id=orders.c_id;
+——+——-+
| c_id | o_num |
+——+——-+
| 1001 | 30001 |
| 1003 | 30002 |
| 1004 | 30003 |
| NULL | 30004 |
| 1001 | 30005 |
+——+——-+
5 rows in set

五、子查询

1）带any 、some关键字子查询：
select num1 from table1 where num1>any (select num2 from table2);
2)带all关键字的子查询：
select num1 from table1 where num1>all (select num2 from table2);
3）带exists关键字的子查询：
select *from fruits where exists and f_price>5.20 (select s_name from suppliers where s_id=107);
4)带in关键字的子查询（内层查询语句只返回一个数据列）：
select c_id from orders where o_num in (select o_num from orderitems where f_id=’c0’);
5)带比较运算符的子查询：
select s_id,f_name from fruits where s_id<>(select s1.s_id from suppliers as s1 where s1.s_city=’Tianjing’);

六、合并查询结果

利用UNION关键字，可以给出多条select语句，将他们的结果组合成单个的结果集

select s_id，f_name ,f_price from fruits where f_price<9.0 union all select s_id,f_name,f_price from fruits where s_id in(101,103);

七、为表和字段取别名

mysql> select *from orders as o where o.o_num=30001;
+——-+———————+——+
| o_num | o_date | c_id |
+——-+———————+——+
| 30001 | 2017-08-10 23:42:24 | 1001 |
+——-+———————+——+
1 row in set

八、使用正表达式查询

正则表达式常用字符 ^ $ . * + <> [] [^] {n} {m,n},关键字regexp

select *from fruits where f_name regexp ‘a.g’;