MySQL的深入浅出（二）

select查询

---

mysql中单引号代表具体的值,反引号代表字段或者表名称

    select 'c_id' as 序号;

输出:

+------+
| 序号 |
+------+
| c_id |
+------+

select '输出值' as 列名称;

select c_id as 序号;
select `c_id` as 序号,c_id as '序号' from commodity;
正常输出表中所有序号

总结:	
   as前的名称:
    	没有单引号或者用反引号括起来,输出相应的字段值               
    	有单引号,全部输出单引号中的值
		
   as后的名称: 仅仅作为输出列名,有没有单引号,反引号影响不大

---

查询方式一

select * from table_name;

查询表中所有数据

---

查询方式二

(字段之间有逗号,除了最后一个):

select field1,field2, ... ,fieldn from table_name;

查询指定字段的数据,field1,field2....的顺序无所谓,仅仅决定了输出顺序

---

查询方式三

(字段之间有逗号,除了最后一个):

->select field1 as name_one field2 as name_two 
->from table_name;

例子:

select c_id as 序号,c_name as 名称 from commodity;

---

查询方式四(四则运算)

例子:

->select c_id as 序号,c_name as 名称,
->c_inprice as 进价,
->c_outprice as 售价,
->c_inprice-c_outprice as 利润
->from commodity;

    注意:null值和其他值运算得到的数值是null

---

查询方式五(distinct)

SELECT DISTINCT field1,field2, ... FROM table_name;
#使用DISTINCT关键字去掉重复数据

例：select distinct c_madein from commodity;

---

查询方式六

(关系运算符和逻辑运算符,between)

select c_id,c_name 
from commodity where (c_id>10 and c_id<50) and c_type=3;
//查询id大于10,id小于50且类型为书籍的商品多个and条件建议使用括号分开
			
select c_id,c_name from commodity where (c_id between 10 and 50) and c_type=3;
#注意：between 关键字包括两个端点
			
select c_id,c_name from commodity where (c_id not between 10 and 50) and c_type=3;
#注意：not between不包括两个端点

关系运算符:>,<,>=,<=,!=
逻辑运算符:and,or,not,

---

查询方式七(is null和is not null非空判断)

select c_id,c_name,c_outprice  from commodity where c_outprice is null;

select c_id,c_name,c_outprice  from commodity where c_outprice is not null;

---

查询方式八(in和not in)

select c_name,c_inprice from commodity where c_inprice in (10,23,778,45,34);
#in:里面的数字之间是或关系
#进价在in范围中的数据
	
select c_name,c_inprice from commodity where c_inprice not in (10,23,778,45,34);
#not in:里面的数字之间是且关系

---

查询方式九(like)

LIKE要和通配符一起用，不然没有用通配符的LIKE效果等同于“=”，常用的通配符有“_”和“%”;

“_”:匹配单个字符
“%”:匹配任意个字符

select c_name from commodity where c_name like '%玩具%';
#商品名称中带有玩具二字的名称

注意:	
    1)like后的匹配字段属于值,用单引号括起来
    2)like关键字会让数据库主动放弃使用索引,降低效率;尽量不要使用like关键字

---

查询方式十(order by)

SELECT field1,field2,... FROM table_name [WHERE CONDITION] ORDER BY fieldn [ASC|DESC];

1),null数据认为是最小值
2),去除null,where相应字段中使用is not null
3)默认升序asc;默认对主键进行排序
4)字符串排序:	英文:按照字母顺序排序

---

查询方式十一(limit)

select * from table limit 2; 只输出前2行;
select * from table limit 0,5;从第0行开始，输出5行;

select c_name,c_inprice from commodity where c_type=1 order by c_inprice desc limit 5;

• limit关键字优化

翻页效果:

select * from table limit (page-1)*pageNumber,pageNumber;
#page当前页序号,pageNumber每一页数据量
#limit每次都从第一条开始查询,效率太低

解决方案:如同字典一样,安装目录(index,索引)

select * from table where id>=(page-1)*pageNumber and id<(page-1)*pageNumber+pageNumber;

---

聚合函数

很多情况下，都需要一些统计汇总工作，比如整个公司的人数， 某部门的人数等等，这时候就用到统计函数(聚合函数)了，它们分别为：

COUNT()函数：统计记录数；
AVG()函数：求平均值；
SUM()函数：求和；
MAX()函数：求最大数； 
MIN()函数：求最小数

其中，COUNT()函数可以通过以下两种方法来实现统计：

count(*)使用方式，实现对表中记录进行统计，不管是否包含 NULL还是NOT NULL count(field)使用方法对指定字段进行统计，将忽略NULL值！

还有，如果表中无数据，count()函数返回的是0，其它函数返回 null；

mysql> #登录验证
mysql> #cu_name cu_phone
mysql> select * from customer where cu_name='' and cu_phone='';
Empty set (0.00 sec)

mysql> #java拿到的是什么？ null -> 空指针异常 所以可以用couut(*)来避免空指针
mysql> select count(*) from customer where cu_name='' and cu_phone='';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> #if(count>0){//TUDO:登录成功}else{//TUDO:登录失败}

---

分组查询（group by）

group by 一般和聚合函数一起出现是兄弟俩

-> select sum(c_inprice),avg(c_inprice),max(c_outprice)
-> from commodity
-> group by c_type;

+----------------+----------------+-----------------+
| sum(c_inprice) | avg(c_inprice) | max(c_outprice) |
+----------------+----------------+-----------------+
|           2448 |       116.5714 |            3000 |
|            600 |        33.3333 |             460 |
|           1053 |        52.6500 |             400 |
+----------------+----------------+-----------------+
3 rows in set (0.02 sec)

---

having

对分组查询结果进行条件限制查询

mysql> select c_type
    -> from commodity
    -> group by c_type
    -> having avg(c_inprice)>100;
+--------+
| c_type |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> #这个having中不需要出现在结果表中 是从5.7版本才开始支持的

where和having的区别
1.类型：
    “Where”是一个约束声明，在查询数据库的结果返回之前对数据库中的查询条件进行约束，即在结果返回之前起作用，且where后面不能使用“聚合函数”；
    
    “Having”是一个过滤声明，所谓过滤是在查询数据库的结果返回之后进行过滤，即在结果返回之后起作用，并且having后面可以使用“聚合函数”。

2.使用的角度：
where后面之所以不能使用聚合函数是因为where的执行顺序在聚合函数之前，