1. 基本概念

本文出處：http://andrewliu.in/2015/05/24/MySQL-Small-Cookbook
作者：Andrew Liu

MySQL是一種關(guān)系型數(shù)據(jù)庫(RDBMS), 數(shù)據(jù)庫可以理解為相關(guān)文件的集合. 數(shù)據(jù)庫和控制器數(shù)據(jù)庫的軟件稱為數(shù)據(jù)庫管理系統(tǒng)(DBMS)

數(shù)據(jù)庫提供處理數(shù)據(jù)的方法:?SQL

基本概念

• 每個表由多個行和列組成
• 每行包含一個單獨實體的數(shù)據(jù), 稱為記錄
• 每一列包含與該記錄相關(guān)的一項數(shù)據(jù), 稱為屬性

安裝

本博文中所有的SQL語句遵循小寫書寫風(fēng)格, 不喜勿噴

$ brew install mysql
$ mysql -u root mysql
#設(shè)置開機啟動
$ ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents 
#加載mysql
$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
#啟動mysql服務(wù)器
$ mysql.server start
#關(guān)閉mysql服務(wù)器
$ mysql.server stop
#使用根用戶
$ mysql -u root 
#創(chuàng)建用戶密碼
mysql> set password=password('123456');
#創(chuàng)建數(shù)據(jù)庫 
mysql> create database firstdb;
#添加用戶和密碼, 并賦予firstdb的完全訪問權(quán)限, 賬戶名為amdin, 密碼為123456
mysql> grant all on firstdb.* to admin@localhost identified by '123456';  
#退出
mysql> exit

初試數(shù)據(jù)庫

#使用非根用戶登陸數(shù)據(jù)庫, 并使用firstdb
mysql> mysql -u admin -p123456 firstdb

創(chuàng)建表

#創(chuàng)建
mysql> create table sales_rep(
    -> employee_number int,
    -> surname varchar(40),
    -> first_name varchar(30),
    -> commission tinyint
    -> );

#顯示已有表
mysql> show tables;
#describe來檢查表結(jié)構(gòu)
mysql> describe sales_rep;

sales_rep為表名, employee_number, surname, first_name, commission為屬性, int表示整型, varchar表示變長字符, tinyint表示小整數(shù)

刪除表和數(shù)據(jù)庫

#創(chuàng)建一個表
mysql> create table com(id int);
#刪除表使用drop關(guān)鍵字
mysql> drop table com;
#切換root用戶, 創(chuàng)建數(shù)據(jù)庫com
mysql> create database com;
#刪除數(shù)據(jù)庫
mysql> drop database com;

插入/刪除/修改記錄

#插入數(shù)據(jù) 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);
#一行添加數(shù)據(jù)
mysql> insert into sales_rep values
     >(1, 'Rive', 'Sol', 10),
     >(2, 'Gordimer', 'Charlens', 15),
     >(3, 'Serote', 'Mike', 10);
#從文件中加載數(shù)據(jù), 格式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;

數(shù)據(jù)檢索

#檢索所有插入的數(shù)據(jù)
mysql> select * from sales_rep;
#檢索surname為'Gordimer'的記錄
mysql> select * from sales_rep where surname='Gordimer';

模式匹配

like和%進行模糊查找

# 輸出已surname已R開頭的數(shù)據(jù)
mysql> select * from sales_rep where surname like 'R%';

排序

order by

#數(shù)據(jù)按照surname排序輸出, 當(dāng)surname相同時, 使用first_name排序
mysql> select * from sales_rep order by surname, first_name;
#遞減排序使用關(guān)鍵字desc, 默認(rèn)使用升序asc
mysql> select * from sales_rep order by surname desc;

多列排序時, 使用逗號隔開排序規(guī)則,?order by排序優(yōu)先次序為從左到右

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, 則其實誒之就是字符串中倒數(shù)第二個字符

但在我測試過程用應(yīng)該是建1, 則是對最后兩個字符排序(疑問?)

根據(jù)數(shù)據(jù)項的鍵排序

使用case語句

如果job是salesman, 按照comm, 否則, 按照sal排序
mysql> select ename, sal, job, comm from emp    -> order by case when job = 'salesman' then comm else sal end;

限制數(shù)據(jù)數(shù)量

limit

#按surname降序輸出兩行
mysql> select * from sales_rep order by surname desc limit 2;

從表中隨機返回n條記錄

• order by可以接受函數(shù)的返回值, 并使用它來改變結(jié)果集的順序

select ename, job from emp order by rand() limit 5;

最大值/最小值/計數(shù)/平均/綜合

#查詢commission的最大值
mysql> select max(commission) from sales_rep;
#查詢最小值
mysql> select min(commission) from sales_rep;
#表中不重復(fù)surname的個數(shù)
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, 去掉查詢字段相同的記錄

改變表結(jié)構(gòu)

添加列

#給表添加一列名為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;
#恢復(fù)原來表名
mysql> alter table cash_flow rename to sales_rep;

刪除列

#刪除列名為enhancement_value的一列
mysql> alter table sales_rep drop enhancement_value;

日期函數(shù)

#給date類型設(shè)置日期
mysql> update sales_rep set date_joined = '2014-02-15', birthday = '2000-02-14' where employee_number = 1;
#使用日期函數(shù), 設(shè)置顯示日期格式
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之前完成的

#將查詢結(jié)果作為內(nèi)斂視圖
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;

多表查詢

創(chuàng)建兩個表并插入數(shù)據(jù)

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中的列值執(zhí)行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;

常用類型

數(shù)字類型

• int(整型), 表示整數(shù)
• float/double分別表示單精度和雙精度浮點數(shù)

字符類型

• char(M) 固定長度為M的字符串, 字符串長度不夠會補上空格 ,?搜索時大小寫無關(guān)
• varchar(M), 可變長字符串(相比char一般比較節(jié)省內(nèi)存),?搜索時大小寫無關(guān)
• text, 最大65535個字符,?搜索時大小寫無關(guān)
• blob, 最大65535個字符,?搜索時大小寫相關(guān)

日期和時間類型

• date, 默認(rèn)格式YYYY-MM-DD, 可以使用date_format()函數(shù)更改輸出方式
• timestamp(M), 時間戳,?YYYYMMDDHHMMSS, 可以指定不同長度的時間戳(M只影響顯示)
• time, 格式HH:MM:SS

表類型