一、基本查询语句
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’;