mysql 必知必會總結

標籤：

以前 mysql 用的不是很多， 2 天看了一遍 mysql 必知必會又複習了一下基礎。  200 頁的書，很快就能看完，

大部分知識比較基礎， 但還是瞭解了一些以前不知道的知識點。自己做一個備份，隨時查看。

命令：
sql 不區分大小寫，語句大寫，列、表名小寫是一種習慣
串連命令：mysql -u user_name –h example.mysql.alibabalabs.com –P3306 –pxxxx
quit
help show; // 查看所有 show 命令
show databases;
use dbname;
show tables;
show columns from tablename; (簡寫 desc tablename;)
show status;
show grants;
show create database dbname; // 查看建立某一個資料庫的語句
show create table tablename; // 查看建立某一個表的語句

查詢：
// 檢索過多不需要的列會降低效能，盡量用什麼查什麼
select * from users;
// distinct
select distinct title from users;
// limit
select * from users limit 5;
// 檢索從第 5 行開始的 5 行
select * from users limit 5,5;
// 檢索第二行，行號從 0 開始
select * from users limit 1,1;

排序：
// ** mysql 按字母順序排序，預設 A 和 a 是一樣的， 但可配置
select * from news order by title;
// between and
select * from news where id between 3 and 6;
// NULL 空值檢查
select * from news where created is null;
// 括弧改變計算順序
select prod_name,prod_price from products where (id=1002 or id=1003) and prod_price >= 10;
// IN
select * from news where id in(6,8,9,222);
萬用字元避免過度使用

Regex
select title from news where title regexp ‘.11‘;
select title from news where title regexp ‘1000|2000|3000‘; // [1,2,3] [1|2|3] [^123]
// 匹配特殊字元需要 \\ 轉義
select * from news where content regexp ‘\\.‘
// 預定義字元集
[:alnum:] 任意字母和數字
[:alpha:] 任一字元 同 [a-zA-Z]
[:digit:] 任一數字 同 [0-9]
‘\\([0-9] sticks?\\)‘
定位器
^ $
[[:<:]] 匹配詞的開始
[[:>:]] 匹配詞的結束

資料處理
// concat 函數 連接字串
select concat(title,‘ (‘,content,‘)‘) as aaa from news;
// ltrim rtim trim 函數去除空格
select trim(title) from news where title regexp ‘bbb‘;

select 語句也可以用來測試語句
select 3*2;
select now();
select ‘hello world‘ regexp ‘world‘; // 返回 1

函數 標準 SQL 可移植性強， 函數移植性不是很強， 各個 DBMS 實現很可能不同
// 字串處理
upper() lower() length() sustring()
// 日期和時間處理函數
AddDate() AddTime() CurDate() CurTime() Date() DateDiff()
Date_Add() Date_Format() Day() DayOfWeek() Hour() Minute()
Month() Now() Second() Time() Year()
// 查詢 2014-12-15 的資料
select * from news where Date(created) = ‘2014-12-15‘;
// 查詢 2014年11月的資料
select * from news where Year(created)=2014 and Month(created)=11;

// 數值處理函數
Abs() Sqrt() Sin() Cos() Mod() Pi() Rand()
// 彙總函式, 能用 SQL 直接計算的盡量不要用程式處理
AVG() COUNT() MAX() MIN() SUM()
// count 作用於列時 會忽略 NULL
select count(created) from news;

// HAVING 用於過濾分組
select title ,count(*) as num from news group by title having count(*)>=2;
// 分組時不能保證排序， 一般還需要自己顯示排序
select title ,count(*) as num from news group by title having count(*)>=2 order by num;
// 相互關聯的子查詢
// 不只一種解決方案, 不一定是解決這種查詢最有效方法，也可連接表，多做實驗，驗證效能
select cust_name,(select count(*) from orders where orders.cust_id=customers.cust_id) from customers order by cust_name;
// 子查詢嵌套過多時一層一層調試，先從最裡層寫入程式碼

表連接
select vender.id as vender_id,vender_name,products.id as prod_id,prod_name,prod_price from vender, products where vender.id=products.vender_id;
// 內部連接結果同上,推薦這種明確連接的文法
select vender_name,prod_name,prod_price from vender inner join products on vender.id=products.vender_id;
// 自連接，連接自身進行查詢 （相比子查詢究竟哪個效率高，應該多做實驗）
select p1.prod_name from products as p1, products as p2 where p1.vender_id=p2.vender_id and p2.prod_name=‘product2‘;

// 外部連接 連接中包含了那些在相關表中沒有關聯的行
// 沒有訂單的客戶也會被連接進來
// left right 指定左邊或右邊的表必須包含所有的行(無論是否匹配)
SELECT `cust_name`, `order_num` from `customer` left outer join `order` on `customer`.`cust_id`=`order`.`cust_id`;

UNION
// 組合多條 select 語句
// UNION ALL （union 預設不包含重複行， UNION ALL 包含重複行）

全文本搜尋 InnoDB 不支援 MyISAM 支援
全文搜素會對結果根據優先順序進行排序

複製匯入表資料 insert select
insert into customers(id,name) select cust_id,cust_name from newcust;

刪除表中所有的行 truncate tablename 比 delete from tablename 更快
因為 truncate 是先刪除原來的表再建立一個新表

// autoincrement 也可以手動插入一個值，只要唯一就行
select last_insert_id()； 返回自增長列當前最大值,與表無關

資料庫引擎
InnoDB 支援可靠的事物處理
MEMORY 功能等同與 MyISAM ，資料存放區在記憶體中 （試用與暫存資料表）
MyISAM 高效能引擎，支援全文檢索搜尋，不支援事物處理
外鍵不能跨引擎

// 更改表
alter table news add name varchar(50);
alter table news drop column name;
alter table products add constraint fk_products_venders foreign key (vender_id) references venders(vender_id);
rename table news to news1;

// 視圖就是一個 SQL 陳述式
// 視圖最常用的是查詢，但也能進行更新操作
create view viewname;
show create view viewname;
drop view viewname;

預存程序
// 調用預存程序
call procedurename(@param1,@param2,@param3);
// 建立預存程序
creage procedure getTitle()
begin
select title from news;
end;
// 命令列臨時改變分隔字元
delimiter //
delimiter ;
// 刪除預存程序
drop procedure procedurename;
drop procedure procedurename if exists;
// 傳參數
delimiter //
CREATE procedure getCount(out p int)
begin
select count(*)
into p
from news;
end //
delimiter ;
// 調用
call getCount(@ret);
// 查詢
select @ret;

// 查看預存程序建立語句
show create procedure procedurename;
// 擷取預存程序列表
show procedure status;

// ***********************
// 預存程序一般用於處理比較複雜的商務邏輯
// 一般根據不同條件執行不同的操作
-- Name: ordertotal
-- Parameters: onumber = order number
-- taxable = 0 if not taxable. 1 if taxable
-- ototal = order total variable
create procedure ordertotal(
in onumber int,
in taxable boolean,
out ototal decimal(8,2)
)
begin
declare total decimal(8,2);
declare taxrate int default 6;
select sum(item_price * quantity)
from orderitems
where order_num = onumber
into total;

if taxable then
select total + (total/100*taxrate) into total;
end if;

select total into ototal;
end;
// ************************

// 遊標（作用於預存程序和函數）
// 使用遊標遍曆資料

觸發器(定義在表上), 觸發器中不能返回資料
create trigger inserttrigger after insert on news for each row insert into vender(vender_name) values(‘new vender‘);
// 刪除觸發器
drop trigger inserttrigger;
// insert 觸發器可以引用一個名為 new 的虛擬表訪問到被插入的資料
// delete 觸發器可以引用一個名為 old 的虛擬表訪問到被刪除的資料

交易處理
start transaction
rollback
commit
// rollback 能回退 insert update delete 操作，不能回退 create drop 操作
// 保留點 savepoint
// 複雜的事務可以設定多個保留點，便於回退到某個保留點而不是復原整個事務
savepoint delete1
rollback to delete1
// 禁用自動認可
set autocommit = 0

// 顯示所有字元集
show character set;
// 顯示所有校對 (字元比較規則)
show collation;
// 顯示當前使用的字元集
show variable like ‘character%‘;
// 顯示當前使用的校對
show variable like ‘collation‘;
// 給表指定字元集和校對
// 列也可以指定字元集和校對
create table mytable(
column1 int,
column2 varchar(10) character set latin1 collate latin1_general_ci
) default character set utf8
collate utf8_general_ci;
// select 時臨時改變預設校對
select * from customers order by lastname collate latin1_geeral_cs

安全管理
現實中一定不要使用 root
所有使用者儲存在 mysql 資料庫的 user 表中
// password 採用 MD5加密,簡單的密碼一搜就能查出來
select user,password from user;
建立使用者
CREATE USER ‘aaa‘@‘localhost‘ IDENTIFIED BY ‘***‘;
// 重新命名
rename user aaa to bbb;
// 刪除使用者賬戶及相關的許可權
drop user aaa;
// 查看存取權限,許可權由使用者名稱和 host 結合定義
show grants for aaa; (=show grants for [email protected]%;)
show grants for [email protected];
// 授予使用者權限
// 使用者 aaa 可以訪問 mydb 資料庫的所有表
grant select on mydb.* to aaa;
mysql> show grants for aaa;
+---------------------------------------+
| Grants for [email protected]% |
+---------------------------------------+
| GRANT USAGE ON *.* TO ‘aaa‘@‘%‘ |
| GRANT SELECT ON `mydb`.* TO ‘aaa‘@‘%‘ |
+---------------------------------------+
// 撤銷許可權
revoke select on mydb.* from aaa;
// 更新口令
set password for aaa = Password(‘123456‘);
// 更新目前使用者的口令
set password = Password(‘‘);

效能最佳化
硬體設定
不只一種方法編寫同一條 select (串連 並 子查詢)
預存程序
不用檢索不需要的資料
匯入資料時關閉自動認可，刪除索引，匯入完後再重建
索引
。。。。

 

mysql 必知必會總結