日常SQL常见问题（二）

MySQL中的函数相关
MySQL考试试题及答案
MySQL中数据类型
在MySQL数据库中， 字段或列的注释是用属性comment来添加。

SQL优化：避免全表扫描；考虑在where和order by涉及的列建立索引；
避免在where子句中使用!=或者<>;或者使用or来连接条件；或者使用in和not in；或者like‘%abc%’；或者对字段进行表达式操作；或者对字段进行函数操作；有时候用exist代替in比较好；

char：定长，简单，浪费空间，存取速度快 0-255
varchar：变长，精准，节省空间，存取速度慢 0-65535
尽量使用varchar代替char

1.SQL 语句中 where 条件后 写上1=1 是什么意思？
我们知道1=1表示true，即永真，在SQL注入时配合or运算符会得到意向不到的结果；
有1=1就会有1<>1或1=2之类的永假的条件，这个在拷贝表的时候，加上where 1<>1，意思就是没有任何一条记录符合条件，这样我们就可以只拷贝表结构，不拷贝数据了；

超过经理收入的员工

select a.Name Employee from Employee a inner join Employee b on a.ManagerId = b.Id 
 where a.Salary>b.Salary;
或者
select c1.Name as Employee from Employee as c1,Employee as c2 where c1.ManagerId = c2.Id
and c1.Salary>c2.Salary;

case 搜索函数法：

case 
	when score<60 then '不及格'
	when score<70 then '一般'
	when score<85 then '良好'
	else '优秀'
end

1.mysql中count的用法

一、count情况

1、count(1)：可以统计表中所有数据，不统计所有的列，用1代表代码行，在统计结果中包含列字段为null的数据；
2、count(字段)：只包含列名的列，统计表中出现该字段的次数，并且不统计字段为null的情况；
3、count(*)：统计所有的列，相当于行数，统计结果中会包含字段值为null的列；

二、count执行效率

列名为主键，count(列名)比count(1)快；列名不为主键，count(1)会比count(列名)快；
如果表中多个列并且没有主键，则count(1)的执行效率优于count(*)；
如果有主键，则select count(主键)的执行效率是最优的；如果表中只有一个字段，则select count(xing)最优。

1.Mysql中where与on的区别及何时使用详析

首先有两张表：test_1234

test_1235;

说明：区分on和where首先我们将连接分为内部连接和非内部连接，内部连接时on和where的作用是一样的，通常我们分不清它们的区别说的是非内部连接(left join、right join、full join等)；
一般on用来连接两个表，指的是连接的条件，在内部连接时，可以省略on，此时它表示的是两个表的笛卡尔积；使用on连接后，mysql会生成一张临时表，而where就是在临时表的基础上，根据where子句来筛选出符合条件的记录，因此where是用来筛选的。
内部连接(inner join)
说明：join默认为inner join，当为内部连接时，on和where的作用你可以看做是一样的，一般分不清区别就是在使用非内部连接时。
1.inner join 连接两个表(无on和where)

select * from test_1234 join test_1235;
等价于select * from test_1234,test_1235;

结果集是两个表的笛卡尔积
2.inner join 连接两个表(有on)

select * from test_1234 t1 join test_1235 t2 on t1.trade_id = t2.trade_id;

结果集是两个表有相同trade_id的数据
3.inner join 连接两个表(有where)

select * from test_1234 t1 join test_1235 t2 where t1.trade_id = t2.trade_id;

结果集是两个表有相同trade_id的数据，从2和3的结果中我们可以看出，在使用inner join连接时，on和where的作用相等.
4.left join(下面以left join为例来连接两个表) 连接两个表

SELECT * FROM test_1234 t1 LEFT JOIN test_1235 t2 ON t1.trade_id=t2.trade_id;

结果集是以左面的表为基础，直接根据trade_id去右边查询相等的值然后连接，如果右表没有符合的数据，则都显示为null.

5.left join(下面以left join为例来连接两个表) 连接两个表，连接条件中有常量等式

SELECT * FROM test_1234 t1 LEFT JOIN test_1235 t2 ON t1.trade_id=t2.trade_id and t2.nick_name='wangwu';

结果集是以左面的表为基础，如果on连接条件最后没有找到匹配的记录，则都显示null
6.left join(下面以left join为例来连接两个表) 连接两个表，将常量表达式放入where子句中

select * from test_1234 t1 left join test_1235 t2 on t1.trade_id = t2.trade_id where t2.nick_name = 'wangwu';

结果只会显示符合where子句的数据，只要没有符合的都不会显示，因为它是筛选连接后的临时表中的数据，而on只是连接，如果右边没有符合的数据，就显示null，而左边的数据都会显示，不会被过滤，这就是where和on最大的区别。

力扣自连接写法（与以上文章不相关）：

-- a表中的经理Id=b表中的员工Id，即b表中 的人员都是a表中的经理
select a.Name Employee from Employee a inner join Employee b on a.ManagerId = b.Id 
where a.Salary>b.Salary;

from here

2.正确理解MySQL中的where和having的区别

前言：where: 后面不能接聚合函数 ，可以接单行函数。
单行函数:就是使用函数查询返回一条结果如（等于，时间转换，转换函数等）
聚合函数:就是使用函数查询返回多调数据的如（大于，小于，不等于）
having: 在group by 之后执行 可以接聚合函数
示例：

1） 只可以用having，不可以用where情况

查询每种goods_category_id商品的价格平均值，获取平均价格大于1000元的商品信息

    select goods_category_id , avg(goods_price) as ag from sw_goods group by goods_category having ag > 1000

    select goods_category_id , avg(goods_price) as ag from sw_goods where ag>1000 group by goods_category //报错！！因为from sw_goods 这张数据表里面没有ag这个字段

注意:where 后面要跟的是数据表里的字段，如果我把ag换成avg(goods_price)也是错误的！因为表里没有该字段。而having只是根据前面查询出来的是什么就可以后面接什么。

2）只可以用where，不可以用having的情况

    select goods_name,goods_number from sw_goods where goods_price > 100

    select goods_name,goods_number from sw_goods having goods_price > 100 //报错！！！因为前面并没有筛选出goods_price 字段

3）where和having都可以使用的场景

    select goods_price,goods_name from sw_goods where goods_price > 100

        select goods_price,goods_name from sw_goods having goods_price > 100

解释：上面的having可以用的前提是我已经筛选出了goods_price字段，在这种情况下和where的效果是等效的，但是如果我没有select goods_price 就会报错！！因为having是从前筛选的字段再筛选，而where是从数据表中的字段直接进行的筛选的。

**举例：**商品交易(网易校招笔试真题)
描述
如下有一张商品表（goods），字段依次为：商品id、商品名、商品质量
±-----±-----±-------+
| id | name | weight |
±-----±-----±-------+
| 1 | A1 | 100 |
| 2 | A2 | 20 |
| 3 | B3 | 29 |
| 4 | T1 | 60 |
| 5 | G2 | 33 |
| 6 | C0 | 55 |
±-----±-----±-------+
还有一张交易表（trans），字段依次为：交易id、商品id、这个商品购买个数
±-----±---------±------+
| id | goods_id | count |
±-----±---------±------+
| 1 | 3 | 10 |
| 2 | 1 | 44 |
| 3 | 6 | 9 |
| 4 | 1 | 2 |
| 5 | 2 | 65 |
| 6 | 5 | 23 |
| 7 | 3 | 20 |
| 8 | 2 | 16 |
| 9 | 4 | 5 |
| 10 | 1 | 3 |
±-----±---------±------+

查找购买个数超过20,质量小于50的商品，按照商品id升序排序,如:
±-----±-----±-------±------+
| id | name | weight | total |
±-----±-----±-------±------+
| 2 | A2 | 20 | 81 |
| 3 | B3 | 29 | 30 |
| 5 | G2 | 33 | 23 |
±-----±-----±-------±------+

select g.id,g.name,g.weight,sum(t.count) from goods g inner join trans t on g.id = t.goods_id
where  g.weight<50  --不能同时用sum(t.count),因为表里没有该字段
group by g.id
having sum(t.count)>20
order by g.id;

from here