MySQL學習筆記

標籤：

MySQL技術內幕
一、MySQL基礎知識
1.1、顯示表中的列
SHOW COLUMNS FROM order_info like ‘order%‘
1.2、顯示表
SHOW TABLES LIKE ‘order%‘;
1.3、限制返回行數
例如從第10行開始返回5個記錄
SELECT last_name FROM student ORDER BY birth DESC LIMIT 10,5;
1.4、時間相關
SELECT
CURDATE() AS 當前日期,
YEAR(NOW()) AS 年,
MONTH(NOW()) AS 月,
DAYOFMONTH(NOW()) AS 日,
DATE_ADD(NOW(),INTERVAL 10 DAY) 日期添加,
DATE_ADD(NOW(),INTERVAL 10 MONTH) 月份添加,
DATE_ADD(NOW(),INTERVAL -2 YEAR) 年份添加,
DATE_SUB(NOW(),INTERVAL 10 YEAR) 年份添加2,
TO_DAYS(NOW()) - TO_DAYS(‘2015-08-01‘) AS 日期相減,
TIMESTAMPDIFF(DAY,NOW(),‘2015-08-01‘)AS 日期相減2,
UNIX_TIMESTAMP(NOW()) AS 時間 戳 ,
FROM_UNIXTIME(1439869720) 時間戳記轉,
STR_TO_DATE(‘2008-4-2 15:3:28‘,‘%Y-%m-%d %H:%i:%s‘) AS 時間
二、使用SQL管理資料
2.1、大小寫：
資料庫，資料表和視圖的名字由伺服器的作業系統規定（Windows不區分，Unix區分）
關鍵字和函數名不區分大小寫，
儲存函數、預存程序和事件的名字不分大小寫，
資料列和索引的名字不區分大小寫，
觸發器的名字要區分大小寫，
資料表的別名最好儲存同樣的的字母大小寫組合
2.2、快速建表
CREATE TABLE kehu_20150819 LIKE kehu -- 只複製資料結構
CREATE TABLE kehu_20150818 AS SELECT * FROM kehu -- 複製表結構和資料
2.3、擷取資料庫中繼資料
列出資料庫
SHOW DTABASES;
列出預設資料庫或者給定資料庫的表
SHOW TABLES;
SHOW TABLES FROM db_name;
查看資料表的create table語句
SHOW CREATE TABLE tbl_name;
查看資料表的資料列或者索引資訊
SHOW COLUMNS FROM tbl_name;
SHOW INDEX FROM tbl_name;
最好的方法查詢information_schema庫
2.4、涉及多個資料包的刪除和更新
2.4.1 一次刪除多個資料表的資料行
DELETE t1,t2 FROM t1 INNER JOIN t2 ON t1.id = t2.id;
或者
DELETE FROM t1,t2 USING t1 INNER JOIN t2 ON t1.id = t2.id;
2.4.2 從資料表找出兵刪除資料行
DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id =t2.id WHERE t2.id IS NULL;
或者
DELETE FROM t1 USING t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL;
2.4.3 更新單表
UPDATE score ,grade_event SET score.score =score.score +1
WHERE score.event_id = grade_event.event_id
AND grade_event.date = ‘2015-08-01‘ ;
或者
UPDATE score SET score =score +1
WHERE event_id = (SELECT event_id FROM grade_event WHERE date = ‘2015-08-01‘ );
必須用到2.4.1寫法的情況：
UPDATE t1,t2 SET t2.a = t1.a WHERE t2.id = t1.id;
三、資料類型
座標值 POINT(10 20)
空值 NULL "\N"解釋為NULL
SELECT
INET_ATON(‘192.168.1.1‘) AS IP轉整數,
INET_NTOA(3232235777) AS 整數轉IP;
四、查詢最佳化
索引類型：
hash index 散列索引在使用"="或者"<=>"操作符進行的精確匹配比較操作速度快，但是在尋找一個範圍的
比較動作表現不佳，例如 id< 50 weight BETWEEN 100 AND 150
bit tree index 在使用<,<=,=,>=,>,<>,!=和BETWEEN 進行的精確操作比較或者範圍比較操作裡很有效率。

 

MySQL學習筆記