mysql笔记整理1——常用知识及问题bug

一.常用知识

1. 查看表定义：DESC tablename；show create table emp
2. ADD/CNAHGE/MODIFY中，都有一个可选项first|after column_name，用来修改字段在表中的位置，ADD 增加的新字段默认是加在表的最后位置，而CHANGE/MODIFY 默认都不会改变字段的位置。

alter table [表名] add [字段] [类型] after [已存在字段];
alter table [表名] modify [字段] [类型] first;

1. 显示部分数据：SELECT ……[LIMIT offset_start,row_count]
   offset_start 表示记录的起始偏移量，row_count 表示显示的行数。
   在默认情况下，起始偏移量为0，只写记录行数，实际就是前n行。

select * from [表名] limit n；
select * from [表名] order by [字段] limit 1,3;

排序后从第二条开始，显示3 条记录：
limit 经常和order by 一起配合使用来进行记录的分页显示。
4. CURDATE()函数：返回当前日期，只包含年月日。
CURTIME()函数：返回当前时间，只包含时分秒。
NOW()函数：返回当前的日期和时间，年月日时分秒全都包含。
DATEDIFF（date1，date2）函数：用来计算两个日期之间相差的天数。

mysql> select DATEDIFF('2008-08-08',now());

IF(value,t,f) 如果value 是真，返回t；否则返回f

mysql> select if(salary>6000,'high','low') from salary;

1. 看你的mysql当前默认的存储引擎:

mysql> show variables like '%storage_engine%';

查询当前数据库支持的存储引擎:

mysql> show engines;

1. MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表。InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
2. 随机取样时，ORDER BY RAND()能够把数据随机排序, 再抽取前n 条记录。

mysql> select * from XXX order by rand() limit 5;

使用GROUP BY的WITH ROLLUP字句可以检索出更多的分组聚合信息，可
以满足用户想要得到的任何一个分组以及分组组合的聚合信息值。

mysql> select year, country, product, sum(profit) from sales group by year, country, product with rollup;

前3 行表示2004 年在中国各个产品（tnt1、tnt2、tnt3）的利润，
第4 行表示2004 年在中国所有产品的利润，
第5 行表示2004 年全世界所有产品的利润
8. 在MySQL 中在硬盘上保存、使用表名和数据库名由lower_case_tables_name 系统变量决定，在启动mysqld 时设置这个变量。
值 含义

0：  使用CREATE TABLE 或CREATE DATABASE 语句指定的大写和小写在硬盘上保存表名和数据库名。
名称对大小写敏感。在UNIX 系统中的默认设置就是这个值
1：  表名在硬盘上以小写保存，名称对大小写敏感。MySQL 将所有表名转换为小写以便存储和查找。
该值为Windows 和Mac OS X 系统中的默认值
2：  表名和数据库名在硬盘上使用CREATE TABLE 或CREATE DATABASE 语句指定的大小写进行保存，但MySQL 将它们转换为小写以便查找。此值只在对大小写不敏感的文件系统上适用

在UNIX 中将lower_case_tables_name 设置为1 并且重启mysqld 之前，必须先将旧的数据库名和表名转换为小写！！！
9. 备份所有数据库：

mysqldump -uroot -p --all-database > all.sql

备份数据库test：

mysqldump -uroot -p test > test.sql

备份数据库test 下的表emp 和dept：

mysqldump -uroot -p test emp dept > emp_dept.sql

对于事务存储引擎（InnoDB 和BDB）来说，采用更好的选项–single-transaction，此选项将使得InnoDB 存储引擎得到一个快照（Snapshot），使得备份的数据能够保证一致性。
导出数据：

    mysqldump –u root –p –d [数据库名] [表名] > 表名.sql

二.问题解决

–1.问题描述
use scm之后，切换到scm数据库；
show tables之后，显示有A表；
select * from A之后报表不存在；
在此数据库下我新建表B，select * from B成功；
–2.处理方式
已处理大小写敏感问题，mysql也重启了，表也进行了repair修复
–3.结果
没一点作用，还是报表A不存在；
–4.解决方案
原来是lower_case_table_names这个参数在作怪，创建表之前这个参数是默认的0，因此创建的表都是大小写敏感的，结果后面改了一下参数为1了，大小写不敏感了，查询的时候不管是select大写还是小写，统统都转换成小写了，而表名其实是大写的，所以才报错找不到表的。

三.数据库bug

delete from A a where a.id in (select b.ids from B b);

测试过程中发现有一张表每次都被清空，但是业务设置上是只有id在子查询中时才删除，后来检查发现是mysql-5.6.19有个bug，当括号中子查询异常时(本次为子查询中select后面的字段写错了，单独执行子查询报错不存在此字段)，直接执行了where前面的主查询的delete语句，所以delete的时候很恐怖的，所有mysql中尽量别使用嵌套子查询，能用join的就用join吧。