作者介紹
索甯，擅長python開發、mysql、前端等衆多技術領域，曾負責衆多企業安全架構解決方案 ，涉獵行業有媒體、出版社、航空運輸、醫療、軍隊、政府、教育等。
一、資料庫操作
1、檢視資料庫
show databases;
# 預設資料庫：
mysql - 使用者權限相關資料
test - 用于使用者測試資料
information_schema - mysql本身架構相關資料
2、建立資料庫
# utf-8 編碼
create database 資料庫名稱 default charset utf8 collate utf8_general_ci;
# gbk 編碼
create database 資料庫名稱 default character set gbk collate gbk_chinese_ci;
3、使用資料庫
use db_name;
# 可以不使用分号
4、使用者管理
# 建立使用者
create user '使用者名'@'ip位址' identified by '密碼';
# 删除使用者
drop user '使用者名'@'ip位址';
# 修改使用者
rename user '使用者名'@'ip位址'; to '新使用者名'@'ip位址';;
# 修改密碼
set password for '使用者名'@'ip位址' = password('新密碼')
ps：使用者權限相關資料儲存在mysql資料庫的user表中，是以也可以直接對其進行操作（不建議）
# 檢視目前使用者
select user();
# 檢視所有使用者
select host,user from mysql.user;
# 人性化顯示所有使用者
select distinct concat('user: ''',user,'''@''',host,''';') as query from mysql.user;
# 檢視使用者的所有權限
show grants for 'nick'@'%';
view code
5、授權管理
# 檢視權限
show grants for '使用者'@'ip位址'
# 授權
grant 權限 on 資料庫.表 to '使用者'@'ip位址'
# 取消權限
revoke 權限 on 資料庫.表 from '使用者'@'ip位址'
常用權限：
all privileges 除grant外的所有權限
select 僅查權限
select,insert 查和插入權限
usage 無通路權限
對于目标資料庫以及内部其他：
對于使用者和ip：
更多權限
添加額外管理者
簡單示例
建立使用者一般流程
6、授權區域網路内主機遠端連接配接資料庫
#百分号比對法
grant all on *.* to 'test'@'192.168.200.%' identified by 'test123';
#子網路遮罩配置法
grant all on *.* to 'test'@'192.168.200.0/255.255.255.0' identified by 'test123';
#重新整理權限
flush privileges;
#遠端登陸連接配接
mysql -utest -ptest123 -h 192.168.200.96
二、表操作
1、建立表
# 基本文法：
create table 表名(
列名 類型 是否可以為空 預設值 自增 主鍵，
列名 類型 是否可以為空
)engine=innodb default charset=utf8
not null # 不可以為空
default 1 # 預設值為1
auto_increment # 自增
primary key # 主鍵
constraint 外鍵名 foreign key (從表字段’自己‘) references 主表(主鍵字段) # 外鍵
是否可空，null表示空，非字元串
not null - 不可空
null - 可空
預設值，建立列時可以指定預設值，當插入資料時如果未主動設定，則自動添加預設值
create table tb1(
nid int not null defalut 2,
num int not null
)
自增，如果為某列設定自增列，插入資料時無需設定此列，預設将自增（表中隻能有一個自增列）
nid int not null auto_increment primary key,
num int null
或
nid int not null auto_increment,
num int null,
index(nid)
注意：1、對于自增列，必須是索引（含主鍵）。
2、對于自增可以設定步長和起始值
主鍵，一種特殊的唯一索引，不允許有空值，如果主鍵使用單個列，則它的值必須唯一，如果是多列，則其組合必須唯一。
外鍵，一個特殊的索引，隻能是指定内容
2、删除表
drop table 表名
3、清空表
# 表還存在，表内容清空
delete from 表名
truncate table 表名
4、修改表
# 添加列：
alter table 表名 add 列名 類型
# 删除列：
alter table 表名 drop column 列名
# 修改列：
alter table 表名 modify column 列名 類型; -- 類型
alter table 表名 change 原列名 新列名 類型; -- 列名，類型
# 添加主鍵：
alter table 表名 add primary key(列名);
# 删除主鍵：
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;
# 添加外鍵：
alter table 從表 add constraint 外鍵名稱（形如：fk_從表_主表） foreign key 從表(外鍵字段) references 主表(主鍵字段);
# 删除外鍵：
alter table 表名 drop foreign key 外鍵名稱
# 修改預設值：
alter table testalter_tbl alter i set default 1000;
# 删除預設值：
alter table testalter_tbl alter i drop default;
# 更改表名
rename table 原表名 to 新表名;
增删改表的字段
#增加表字段，altertable法。
1> 文法： altertable 表名 add 字段 類型 其他；
2> 插入列，名為sex。
3> 插入名為suo列在name後面。
4> 插入名為qq列在第一。
#更改表名字，rename法。
1> 文法: rename table 原表名 to 新表名；
2> 更改oldsuo表為oldning。
#删除表
1> 文法：drop table <表名>；
2> 删除表名為oldsuo表。
三、表内容操作
1、增
文法：insert into 表 (列名,列名...) values (值,值,值...)
# 插入單條資料
insert into 表 (列名,列名...) values (值,值,值...)
# 插入多條資料
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
# 插入另一條語句的查詢結果
insert into 表 (列名,列名...) select 列名,列名... from 表
2、删
文法：delete from 表
delete from 表;
delete from 表 where id＝1;
3、改
文法：update 表 set name ＝ 'nick' where id>1
update 表 set name ＝ 'nick' where id>1
4、查
文法：select * from 表
select * from 表
select * from 表 where id > 1
select nid,name,gender as gg from 表 where id > 1
# as 做别名
5、條件
文法：select * from 表 where id > 1
6、通配符
文法：select * from 表 where name like '_n%'
7、限制
文法：select * from 表 limit 9,5;
8、排序
文法：select * from 表 order by 列1 desc,列2 asc
9、分組
文法：select num from 表 group by num
注：group by 必須在where之後，order by之前
10、連表
文法：inner join . on、left join . on、right join . on
11、組合
文法：union、union all
查詢表資料
1> 指令文法：select<字段1，字段2，…>from<表名>where<表達式>
2> 查詢所有
3> 查詢某列。不用*，查詢的列列出來。
4> 指定條件查詢
#升序
#倒叙
表中插入資料
1> 插入單個資料，student為表的名稱。
２> 批量插入資料，student為表的名稱。
表中删除資料
1> 删除所有資料，student為表的名稱。
2> 删除表中的某行或某些
3> 直接清空某張表
四、其它指令
1、檢視建表語句
2、檢視表結構
desc 表名;
3、檢視是否走索引
explain select * from 表名 where name ='nick' \g
用此指令檢視是否sql語句是否還有優化的餘地
五、資料類型
學習新的東西自然離不開資料類型，mysql中的資料類型還算簡單；大緻分為數字、字元串、時間。
那就詳細看看吧：
六、索引
1、索引概述
索引是表的索引目錄，在查找内容之前先查目錄中查找索引位置，進而快速定位查詢資料；
可以了解成新華字典中的索引；
索引會儲存在額外的檔案中。
2、索引種類
一般的索引種類及功能：
普通索引：僅加速查詢
唯一索引：加速查詢 + 列值唯一（可以有null）
主鍵索引：加速查詢 + 列值唯一 + 表中隻有一個（不可以有null）
組合索引：多列值組成一個索引，專門用于組合搜尋，其效率大于索引合并
全文索引：對文本的内容進行分詞，進行搜尋
索引合并：使用多個單列索引組合查詢搜尋
覆寫索引：select的資料列隻用從索引中就能夠取得，不必讀取資料行，換句話說查詢列要被所建的索引覆寫
a、普通索引
# 建立表 + 索引
# 建立索引
create index index_name on table_name(column_name)
# 删除索引
drop index_name on table_name;
# 檢視索引
show index from table_name;
#注意：對于建立索引時如果是blob 和 text 類型，必須指定length。
create index ix_extra on in1(extra(32));
b、唯一索引
# 建立表 + 唯一索引
# 建立唯一索引
create unique index 索引名 on 表名(列名)
# 删除唯一索引
drop unique index 索引名 on 表名
c、主鍵索引
# 建立表 + 建立主鍵
# 建立主鍵
alter table 表名 add primary key(列名);
# 删除主鍵
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;
d、組合索引
組合索引是多個列組合成一個索引來查詢
應用場景：頻繁的同時使用多列來進行查詢，如：where name = 'nick' and age = 18。
# 建立表
# 建立組合索引
create index ix_name_age on mess(name,age);
如上建立組合索引之後，查詢一定要注意：
name and email -- >使用索引，name一定要放前面
name -- >使用索引
email -- >不使用索引
注意：同時搜尋多個條件時，組合索引的性能效率好過于多個單一索引合并。
3、相關指令
show index from 表名
# 檢視執行時間
set profiling = 1; # 開啟profiling
sql... # 執行sql語句
show profiles; # 檢視結果
4、如何正确使用索引
# like '%xx'，避免%_寫在開頭
select * from tb1 where name like '%n';
# 使用函數
select * from tb1 where reverse(name) = 'nick';
# or
select * from tb1 where nid = 1 or email = '[email protected]';
注：當or條件中有未建立索引的列才失效，否則會走索引
# 類型不一緻
如果列是字元串類型，傳入條件是必須用引号引起來。
select * from tb1 where name = 999;
# !=，不等于
select * from tb1 where name != 'nick'
注：如果是主鍵，則還是會走索引
select * from tb1 where nid != 123
# >，大于
select * from tb1 where name > 'nick'
注：如果是主鍵或索引是整數類型，則還是會走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123
# order by
select email from tb1 order by name desc;
當根據索引排序時候，選擇的映射如果不是索引，則不走索引
注：如果對主鍵排序，則還是走索引：
select * from tb1 order by nid desc;
# 組合索引最左字首
如果組合索引為：(name,email)，查詢使用：
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
5、注意事項
# 避免使用select *
# count(1)或count(列) 代替 count(*)
# 建立表時盡量時 char 代替 varchar
# 表的字段順序固定長度的字段優先
# 組合索引代替多個單列索引（經常使用多個條件查詢時）
# 盡量使用短索引
# 使用連接配接（join）來代替子查詢(sub-queries)
# 連表時注意條件類型需一緻
# 索引散列值（重複少）不适合建索引，例：性别不适合
6、執行計劃
explain + 查詢sql 用于顯示sql執行資訊參數，根據參考資訊可以進行sql優化
id
查詢順序辨別
如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as b;
特别的：如果使用union連接配接氣值可能為null
<b></b>
<b>本文來自雲栖社群合作夥伴"dbaplus"，原文釋出時間：2016-10-26</b>