1. 基本概念 · MySQL Small Cookbook

本文出處：http://andrewliu.in/2015/05/24/MySQL-Small-Cookbook 作者：Andrew Liu > MySQL是一種關系型數據庫(`RDBMS`), 數據庫可以理解為相關文件的集合. 數據庫和控制器數據庫的軟件稱為數據庫管理系統(`DBMS`) > > 數據庫提供處理數據的方法:?`SQL` # 基本概念 * 每個表由多個`行`和`列`組成 * 每行包含一個單獨實體的數據, 稱為`記錄` * 每一列包含與該記錄相關的`一項數據`, 稱為`屬性` ## 安裝 > 本博文中所有的SQL語句遵循`小寫書寫風格`, 不喜勿噴 ~~~ $ brew install mysql $ mysql -u root mysql #設置開機啟動 $ ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents #加載mysql $ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist #啟動mysql服務器 $ mysql.server start #關閉mysql服務器 $ mysql.server stop #使用根用戶 $ mysql -u root #創建用戶密碼 mysql> set password=password('123456'); #創建數據庫 mysql> create database firstdb; #添加用戶和密碼, 并賦予firstdb的完全訪問權限, 賬戶名為amdin, 密碼為123456 mysql> grant all on firstdb.* to admin@localhost identified by '123456'; #退出 mysql> exit ~~~ ## 初試數據庫 ~~~ #使用非根用戶登陸數據庫, 并使用firstdb mysql> mysql -u admin -p123456 firstdb ~~~ ### 創建表 ~~~ #創建 mysql> create table sales_rep( -> employee_number int, -> surname varchar(40), -> first_name varchar(30), -> commission tinyint -> ); #顯示已有表 mysql> show tables; #describe來檢查表結構 mysql> describe sales_rep; ~~~ sales_rep為表名, employee_number, surname, first_name, commission為屬性, int表示整型, varchar表示變長字符, tinyint表示小整數 ### 刪除表和數據庫 ~~~ #創建一個表 mysql> create table com(id int); #刪除表使用drop關鍵字 mysql> drop table com; #切換root用戶, 創建數據庫com mysql> create database com; #刪除數據庫 mysql> drop database com; ~~~ ### 插入/刪除/修改記錄 ~~~ #插入數據 insert into 表名(要插入的屬性名) values(屬性值), 字符串使用單引號 mysql> insert into sales_rep values(1, 'Rive', 'Sol', 10); mysql> insert into sales_rep values(2, 'Gordimer', 'Charlens', 15); mysql> insert into sales_rep values(3, 'Serote', 'Mike', 10); #一行添加數據 mysql> insert into sales_rep values >(1, 'Rive', 'Sol', 10), >(2, 'Gordimer', 'Charlens', 15), >(3, 'Serote', 'Mike', 10); #從文件中加載數據, 格式load data local infile "文件名" into table 表名; mysql> load data local infile "sales_rep.sql" into table sales_rep; ~~~ 刪除記錄 ~~~ # 刪除employee_number為5的記錄 mysql> delete from sales_rep where employee_number = 5; ~~~ 修改記錄 ~~~ #修改employee_number的記錄的commission為12 mysql> update sales_rep set commission = 12 where employee_number = 1; ~~~ ### 數據檢索 ~~~ #檢索所有插入的數據 mysql> select * from sales_rep; #檢索surname為'Gordimer'的記錄 mysql> select * from sales_rep where surname='Gordimer'; ~~~ ### 模式匹配 `like和%`進行模糊查找 ~~~ # 輸出已surname已R開頭的數據 mysql> select * from sales_rep where surname like 'R%'; ~~~ ### 排序 order by ~~~ #數據按照surname排序輸出, 當surname相同時, 使用first_name排序 mysql> select * from sales_rep order by surname, first_name; #遞減排序使用關鍵字desc, 默認使用升序asc mysql> select * from sales_rep order by surname desc; ~~~ 多列排序時, 使用逗號隔開排序規則,?`order by排序優先次序為從左到右` ~~~ mysql> select ename, job, sal from emp order by deptno asc, sal desc; ~~~ 按照字符串部分子串排序 ~~~ #按照job中最后兩個字符進行排序 mysql> select ename, job from emp order by substr(job, length(job) - 1); ~~~ 書中說:?`找到字符串末尾(字符串長度)并減2, 則其實誒之就是字符串中倒數第二個字符` > 但在我測試過程用應該是建1, 則是對最后兩個字符排序(疑問?) 根據數據項的鍵排序使用case語句 ~~~ 如果job是salesman, 按照comm, 否則, 按照sal排序 mysql> select ename, sal, job, comm from emp -> order by case when job = 'salesman' then comm else sal end; ~~~ ### 限制數據數量 limit ~~~ #按surname降序輸出兩行 mysql> select * from sales_rep order by surname desc limit 2; ~~~ 從表中隨機返回n條記錄 * `order by`可以接受函數的返回值, 并使用它來改變結果集的順序 ~~~ select ename, job from emp order by rand() limit 5; ~~~ ### 最大值/最小值/計數/平均/綜合 ~~~ #查詢commission的最大值 mysql> select max(commission) from sales_rep; #查詢最小值 mysql> select min(commission) from sales_rep; #表中不重復surname的個數 mysql> select count(distinct surname) from sales_rep; #commission的平均值 mysql> select avg(commission) from sales_rep; #commission的總和 mysql> select sum(commission) from sales_rep; #right()從字符串右端算起, 按位返回字符串 mysql> select right(date_joined, 5), right(birthday, 5) from sales_rep; ~~~ ### 去重 ~~~ #使用distinct, 去掉查詢字段相同的記錄 ~~~ ### 改變表結構添加列 ~~~ #給表添加一列名為data_joined, 類型為date mysql> alter table sales_rep add date_joined date; #添加一類名為year_born, 類型為year alter table sales_rep add year_born year; ~~~ 修改列定義 ~~~ 將year_born改為列名為birthday, 類型為data mysql> alter table sales_rep change year_born birthday date; ~~~ 重命名表 ~~~ mysql> alter table sales_rep rename cash_flow; #恢復原來表名 mysql> alter table cash_flow rename to sales_rep; ~~~ 刪除列 ~~~ #刪除列名為enhancement_value的一列 mysql> alter table sales_rep drop enhancement_value; ~~~ ### 日期函數 ~~~ #給date類型設置日期 mysql> update sales_rep set date_joined = '2014-02-15', birthday = '2000-02-14' where employee_number = 1; #使用日期函數, 設置顯示日期格式 mysql> select date_format(date_joined, '%m/%d/%y') from sales_rep; # 使用year()輸出年, month()輸出月, dayofmonth()輸出一個月的第幾日 mysql> select year(birthday), month(birthday), dayofmonth(birthday) from sales_rep; ~~~ ### 高級查找(別名, concat, 多表查詢, case表達式) as起別名(類似pytho中import包時用as起別名) ~~~ mysql> select year(birthday) as year, month(birthday) as month, dayofmonth(birthday) as day from sales_rep; ~~~ 在別名的時候用別名做限定條件 > from語句是在where之前完成的 ~~~ #將查詢結果作為內斂視圖 mysql> select * from (select sal as salary, comm as commission from emp) x where salary < 5000; ~~~ concat連接列將多列作為一列進行輸出 ~~~ #將first_name, 一個空格, surname連接在一起輸出 mysql> select concat(first_name, ' ', surname) as name, month(birthday) as month from sales_rep order by month; ~~~ ~~~ mysql> select concat(ename, ' works as a ', job) as msg from emp where deptno = 10; ~~~ ### 多表查詢創建兩個表并插入數據 ~~~ mysql> create table client( -> id int, -> first_name varchar(40), -> surname varchar(30) -> ); mysql> create table sales( -> code int, -> sales_rep int, -> customer int, -> value int -> ); mysql> insert into customer values -> (1, 'Yvaonne', 'Clegg'), -> (2, 'Johnny', 'Chaka'), -> (3, 'Winston', 'Powers'), -> (4, 'Patricia', 'Mankunku'); mysql> insert into sales values -> (1, 1, 1, 2000), -> (2, 4, 3, 250), -> (3, 2, 3, 500), -> (4, 1, 4, 450), -> (5, 3, 1, 3800), -> (6, 1, 2, 500); ~~~ ~~~ code為1, 且兩表中employee_number和sales_rep的記錄輸出, select后面部分列出要返回的字段 mysql> select sales_rep, customer, value, first_name, surname from sales, sales_rep where code = 1 and sales_rep.employee_number= sales.sales_rep; ~~~ case表達式對select中的列值執行`if-else`操作 ~~~ mysql> select ename, sal, -> case when sal <= 2000 then 'underpaid' -> when sal >= 4000 then 'overpaid' -> else 'ok' #else語句是可選的 -> end as status #對case語句返回的列取別名 -> from emp; ~~~ ### 查詢中分組(不懂) group by指的是按照某個屬性分組, 與其他組互不干擾 ~~~ #查詢每個sales_rep的value值的和 mysql> select sales_rep, sum(value) as sum from sales group by sales_rep; ~~~ ## 常用類型數字類型 * int(整型), 表示整數 * float/double分別表示單精度和雙精度浮點數字符類型 * char(M) 固定長度為M的字符串, 字符串長度不夠會補上空格 ,?`搜索時大小寫無關` * varchar(M), 可變長字符串(`相比char一般比較節省內存`),?`搜索時大小寫無關` * text, 最大65535個字符,?`搜索時大小寫無關` * blob, 最大65535個字符,?`搜索時大小寫相關` 日期和時間類型 * date, 默認格式`YYYY-MM-DD`, 可以使用`date_format()`函數更改輸出方式 * timestamp(M), 時間戳,?`YYYYMMDDHHMMSS`, 可以指定不同長度的時間戳(`M只影響顯示`) * time, 格式`HH:MM:SS` ## 表類型 | 表類型 | 優點 | 缺點 | | --- | --- | --- | | 靜態表 | 速度快, 易緩存 | 要求更多的磁盤空間 | | 動態表 | 占磁盤空間小 | 需要維護, 不易出問題后重建 | | 壓縮表 | 只讀表類型, 占用很少磁盤空間 | 每條記錄分開壓縮, 不能同時訪問 | | merge表 | 表尺寸小, 某些情況下速度快 | eq_ref搜索慢, replace不能工作 | | heap表 | 散列索引, 最快 | 數據存在內存, 出現問題易丟失 |