mysql筆記整理1——常用知識及問題bug

thinkpadshi發表於2016-11-26
MySql筆記

一.常用知識

  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吧。

相關文章