mysql 之 知识点与细节

这些天又把mysql系统的看了一遍，温故而知新……

1. char/varchar 类型区别

• char定长字符串，长度固定为创建表时声明的长度（0-255），长度不足时在它们的右边填充空格以达到声明长度。当检索到CHAR值时，尾部的空格被删除掉
• varchar变长字符串(0-65535),VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节),varchar值保存时不进行填充。当值保存和检索时尾部的空格仍保留。
• 它们检索的方式不同,char速度相对快些
  

注意它们的长度表示为字符数，无论中文还是英文均是那么长

text搜索速度稍慢，因此如果不是特别大的内容，用char/varchar,另外text不能加默认值

2. windows中mysql自带的客户端中查询的内容乱码，那是因为系统的编码为gbk,使用前发送一条"set names gbk"语句即可

3. 小数型：float(M,D)，double(M,D),decimal(M,D)  M代表总位数，不包括小数点，D代表小数位，如(5,2) -999.99——999.99

    M是小数总位数，D是小数点后面的位数。如果M和D被省略，根据硬件允许的限制来保存值。单精度浮点数精确到大约7位小数位。

4. group by与聚合函数结合使用才有意义（sum,avg,max,min,count），group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面，如果没有出现，只取每类中第一行的结果

如果想查询每个栏目下面最贵的商品：id,价格，商品名,种类。用select goods_id,cat_id,goods_name,max(shop_price) from goods group by cat_id;查询的结果商品名，id是和最贵商品不匹配的，如果再加上order by哪？也是错误的，因为:Select语句执行有顺序（语法前后也有）： 

•  where子句基于指定的条件对记录行进行筛选； 
• group by子句将数据划分为多个分组； 
• 使用聚集函数进行计算；
• 使用having子句筛选分组； 
• 计算所有的表达式； 
• 使用order by对结果集进行排序。

可以用下面查询语句： select * from (select goods_id,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc) as tmp group by cat_id;

5. having  having子句在查询过程中慢于聚合语句(sum,min,max,avg,count).而where子句在查询过程中则快于聚合语句(sum,min,max,avg,count)。 
简单说来： 
where子句： 
select sum(num) as rmb from order where id>10 
//只有先查询出id大于10的记录才能进行聚合语句 

having子句: 
select reportsto as manager, count(*) as reports from employees 
group by reportsto having count(*) > 4

以下这条语句是错误的:
 select goods_id,cat_id,market_price-shop_price as sheng where cat_id=3 where sheng>200; 应改为:
 select goods_id,cat_id,market_price-shop_price as sheng where cat_id=3 having sheng>200;  where针对表中的列发挥作用,查询数据，having针对查询结果中的列发挥作用,筛选数据

看下面的一道面试题：

有下面一张表

查询：有两门及两门以上不及格成绩同学的平均分

起初用的以下语句： select name,count(score<60) as s,avg(score) from stu group by name having s>1;这条语句是不行的

首先弄清以下2点：

a，count(exp) 参数无论是什么，查询的都是行数，不受参数结果影响如

b，

可以用如下语句,将count换成sum:

或者：select name,avg(score) from stu group by name having sum(score<60)>1;写法

6. 子查询

   a. where 子查询：把内层查询的结果作为外层查询的比较条件。eg：查询最新的商品

select max(goods_id),goods_name from goods;报错：Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

可以用这样的查询：select goods_id,goods_name from goods where goods_id=(select max(goods_id) from goods);

查询每个栏目下的最新商品: select goods_id,cat_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);

   b.from 型子查询：把内层查询结果当成临时表，供外层sql再次查询（临时表必须加一个别名）
查询每个栏目下最新商品  select * from (select goods_id,cat_id,goods_name from goods order by cat_id asc,goods_id desc) as t group by cat_id;
5中 查询挂科两门及以上同学的平均分   select sname from (select name as sname from stu) as tmp;

    c. exists子查询：把外层查询的结果变量，拿到内层，看内层的查询是否成立

查询有商品的栏目：select cat_id,cat_name from category where exists (select * from goods where goods.cat_id=category.cat_id);

由于没有条件，将会查出所有栏目: select cat_id,cat_name from category where exists (select * from goods); 用in也可实现

7. in(v1,v2-----)    between v1 and v2(包括v1,v2)   like(%,_)       order by column1(asc/desc),column2(asc/desc)先按第一个排序，然后在此基础上按第二个排序

8. union 把两次或多次查询结果合并起来

• 两次查询的列数一致 ，对应列的类型一致
• 列名不一致时，取第一个sql的列名
• 如果不同的语句中取出的行的值相同，那么相同的行将会合并（去重复），如果不去重用union all来指定 
• 如果子句中有order by,limit 子句必须加（），

select * from ta union all select * from tb;
   取第四栏目商品，价格降序排列，还想取第五栏目商品，价格也按降序排列
   (select goods_id,cat_id,goods_name,shop_price from  goods where cat_id=4 order by shop_price desc) union (select goods_id,cat_id,goods_name,shop_price from goods          where cat_id=5 order by shop_price desc);
   推荐放到所有子句之后，即：对最终合并的结果来排序
        ( select goods_id,cat_id,goods_name,shop_price from  goods where cat_id=4 order by shop_price desc) union (select goods_id,cat_id,goods_name,shop_price from                   goods where cat_id=5 order by shop_price desc);
9. 连接查询
 左连接：
 select column1,column2,columnN from ta left join tb on ta列=tb列[此处表连接成一张大表，完全当成普通的表看]
 where group,having....照常写
 右连接：
 select column1,column2,columnN from ta right join tb on ta列=tb列[此处表连接成一张大表，完全当成普通的表看]
 where group,having....照常写
 内连接：
 select column1,column2,columnN from ta inner join tb on ta列=tb列[此处表连接成一张大表，完全当成普通的表看]
 where group,having....照常写

 左连接以左表为准，去右表找匹配数据，没有匹配的列用null补齐，有多个的均列出

如有下两表：

 select boy.*,girl.* from boy left join girl on boy.flower=girl.flower;
结果：

 左右连接可以相互转化，推荐用左连接，数据库移植方便
内连接：查询左右表都有的数据（左右连接的交集） 选取都有配对的组合 


左或右连接查询实际上是指定以哪个表的数据为准，而默认（不指定左右连接）是以两个表中都存在的列数据为准，也就是inner join 

mysql不支持外连接 outer join 即左右连接的并集

当多个表中都有的字段要指明哪个表中的字段

三个表连接查询 brand,goods,category
select g.goods_id,cat_name,g.brand_id,brand_name,goods_name from goods g left join brand b on b.brand_id=g.brand_id left join category c on g.cat_id=c.cat_id;

10. 事务transaction：引擎innodb acid

          start transaction;

  sql语句

 commit(提交)/roolback（回滚）
     注意有一些语句会造成事务的隐式提交，比如start transaction
11.数据库备份与恢复  mysql 自带的工具：mysqldump
导出t库下表:
mysqldump -u 用户名 -p 密码 库名 表1 表2 表n > 地址
eg: mysqldump -u root -p 123456 test boy > d:\boy.sql
导出所有表：
mysqldump -u 用户名 -p 密码 库名 > 地址
以库为单位导出：
mysqldump -u 用户名 -p 密码 -B 库1 库2 库n > 地址
导出所有库
mysqldump -u 用户名 -p 密码 -A > 地址

恢复数据库 source 地址
12：存储过程：把一段代码封装起来，当要执行这一段代码的时候，可以通过调用该存储过程来实现
在封装的语句体里面可以用if/else,case,while等控制结构。
可以进行sql编程
 显示所有存储过程：show procedure status;
 drop procedure 名字           delimiter $

 create procedure p1()
 begin
 select * from boy;
 end$
调用： call 名字()
 create procedure p2(num int)
 begin
 select * from boy where id>num
 end$

 create procedure p2(num int,level char(1))
 begin
 if j='h' then
 select * from boy where id>num
 else
 select * from g where id<num;
 end if;
 end$

13.视图view 由查询结果形成的一张虚拟表 

如果某个查询结果出现的非常频繁，也就是拿这个结果当做进行子查询出现频繁，可以把这个结果做成一个视图
create view 视图名 as select语句         
14. 触发器：trigger,监视某种情况并触发某种操作
四要素：监视地点（table）,监视事件（insert/update/delete）,触发时间after/before,触发事件（insert/update/delete）
语法：create trigger triggerName
      after/before insert/update/delete on 表名
      for each row
      begin
      sql语句 #一句或多句（insert/update/delete），以;结束
      end;
如何在触发器引用行的值
对于insert:新增的行用new来表示，行中的每一列的值用new.列名来表示
对于delete:删除的行用old来表示，行中的每一列的值用old.列名来表示
对于update：修改的行 修改前行的值用old来表示，修改后的用new
订单表与商品表
 #买三只羊
 #监视地点：o表
 #监视操作：insert
 #触发振作：update
 #触发时间：after
delimiter $
create trigger tg1
after insert on order
for each row
begin
update goods set num=num-new.much where id=new.gid;   #much/gid 是order中的列
end$

before执行某些操作之前验证
create trigger tg1
before insert on order
for each row
begin 
if new.much>100 then
set new.much=5;
end if;
update goods set num=num-new.much where id=new.gid;   #much/gid 是order中的列
end$

where多个条件先后执行顺序

在mysql中从后向前解析sql语句，应该按照过滤数据多少来安排条件的顺序，所以把能过滤最多结果的条件放在最后这样效果好些。如 select * from t where kid=1 and name=’ljfbest’，如果 kid 过滤60%的记录，name 过滤 30%，那么应该把 kid 和 name 的的条件换下顺序