《MySQL必知必會》學習筆記

MySQL必知必會學習筆記

chapter 01

SQL（發音為字母S-Q-L或sequel）是結構化查詢語言 (SQL)（Structured Query Language）的縮寫。
SQL是一種專門用來與資料庫通訊的語言。

chapter 02
mysql -u USER -p[PASSWORD]  [-h HOST -P PORT]

chapter 03
show databases;
use DATABASE;
show tables;

desc TABLE = describe TABLE = show columns from TABLE;

show status;
show create database DATABASE;
show create table TABLE;
show grants;
show errors|warnings;
help show;

mysql> help show;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW AUTHORS
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CONTRIBUTORS
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where:
    LIKE 'pattern'
  | WHERE expr

If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL "%" and "_"
wildcard characters. The pattern is useful for restricting statement
output to matching values.

Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See

http://dev.mysql.com/doc/refman/5.5/en/extended-show.html.

URL: http://dev.mysql.com/doc/refman/5.5/en/show.html

MySQL5 新增庫information-schema

chapter 04 檢索資料
limit 3,4 = limit 4 offset 3

chapter 13 分組
select
from
where
group by
having
order by
limit

chapter 14 使用子查詢
子查詢作為計算欄位
select cust_name,
     cust_state,
     (select count(*) from orders where orders.cust_id=customer.cust_id) as orders
from customers
order by cust_name;

chapter 15 連接表
select a.x, b.y from a, b where a.xid = b.xid
=
select a.x b.y from a inner join b on a.xid=b.xid 推薦

chapter 16 建立進階連接
自然連接
select a1.id, a1.name
from  a as a1, a as a2
where a1.xid = a2.xid
and a1.name='xx';

外部連接 left|right outer join

chapter 17 組合查詢
union 組合，去除重複行
union all 組合全部行，不去除重複
union 排序只要寫到最後一個union後面就ok了。

chapter 18 全文檢索搜尋
條件：
引擎MyISAM;
接受FULLTEXT

CREATE TABLE productnotes
{
     note_id int NOT NULL AUTO_INCREMENT,
     prod_id char(10) NOT NULL,
     note_date datetime NOT NULL,
     note_text text NULL,
     PRIMARY KEY(note_id),
     FULLTEXT(note_text)
) ENGINE=MyISAM;
匯入表時先不要指定FULLTEXT，導完資料後再alter table;
執行全文檢索搜尋
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

chapter 19 插入資料
1.insert [LOW_PRIORITY] into TABLE values  (v1,v2....vn);
2.insert [LOW_PRIORITY] into TABLE (f1,f2...fn) values  (v1,v2....vn);
-- 適合插入大量資料
3.insert [LOW_PRIORITY] into TABLE (f1,f2...fn) values
(v1,v2....vn),
(v1,v2....vn),
....
(v1,v2....vn);
4.insert into TABLE (f1, f2...fn) select f1,f2..fn from TABLE1;