MySQL基礎(chǔ)

數(shù)據(jù)類型

數(shù)據(jù)類型是指列、存儲過程參數(shù)、表達(dá)式和局部變量的數(shù)據(jù)特征擎鸠，它決定了數(shù)據(jù)的存儲格式，代表了不同的信息類型

整數(shù)型

浮點(diǎn)型

定點(diǎn)型

字符串類型

char的優(yōu)缺點(diǎn)：存取速度比varchar更快止吁，但是比varchar更占用空間
varchar的優(yōu)缺點(diǎn)：比char省空間。但是存取速度沒有char快

時(shí)間型

命令行模式常用操作

MySQL操作語句

DDL 數(shù)據(jù)定義語言 (Data Definition Language)

如：建庫、建表

庫相關(guān)

表相關(guān)

約束條件

表相關(guān)

建表語句

/*第一種*/
create table 表名 (
    字段名1 字段數(shù)據(jù)類型 約束條件 說明畴椰，
    字段名2 字段數(shù)據(jù)類型 約束條件 說明
);

/*第二種 -- 表名1不存在臊诊，表名2存在，將表名2的表結(jié)構(gòu)及數(shù)據(jù)復(fù)制到表名1中 -- 復(fù)制可能不完全*/
create table 表名1 as select * from 表名2

/*第三種 -- 表名1不存在迅矛，表名2存在妨猩，將表名2的column1, column2結(jié)構(gòu)復(fù)制到表名1中 --  復(fù)制可能不完全*/
create table 表名1 as select column1, column2 from 表名2 

/*第四種 -- 表名1不存在，表名2存在秽褒，將表名2的表結(jié)構(gòu)復(fù)制到表名1中 --  復(fù)制可能不完全*/
create table 表名1 as select * from 表名2 where 1=2

/*第五種 -- -- 表名1不存在壶硅，表名2存在，將表名2的表結(jié)構(gòu)復(fù)制到表名1中*/
create table 表名1 like 表名2

表結(jié)構(gòu)維護(hù)

• 重命名

  rename table 舊表名 to 新表名销斟；
  

• 表刪除

  drop table 表名庐椒；
  drop table if exists 表名；/*判斷表是否存在蚂踊，存在就刪除*/
  

• 添加列

  /*添加一列*/
  alter table 表名 add 字段名 字段類型 約束條件 說明约谈；
  /*表最前面添加一列*/
  alter table 表名 add 字段名 字段類型 約束條件 說明 first；
  /*給表某個(gè)字段后添加一列*/
  alter table 表名 add 字段名 字段類型 約束條件 說明 after 已存在字段名犁钟；
  

• 修改列類型

  alter table 表名 modify 字段名 字段新類型棱诱；
  

• 修改列名

  alter table 表名 change 舊字段名 新字段名 新字段類型；
  

• 刪除列

  alter table 表名 drop 字段名涝动；
  

DML 數(shù)據(jù)操縱語言(Data Manipulation Language)

如：對表中的數(shù)據(jù)進(jìn)行增刪改操作

insert

• 普通插入

  /* 第一種 */
  insert into 表名 (column1, column2) values (column1值, column2值);
  /* 第二種 */
  insert into 表名 values (全部字段值);
  

• 蠕蟲復(fù)制

  /* 將t2中的數(shù)據(jù)復(fù)制到t1中 -- t1表和t2表的字段完全對應(yīng)*/
  insert into t1 select * from t2;
  /* 將t2中的column1,column2字段數(shù)據(jù)復(fù)制到t1中*/
  insert into t1 (column1, column2) select column1, column2 from t2;
  

• 批量插入

  insert into t1 
  (c1, c2, c3)
  values 
  (c1_v1, c2_v1, c3_v1),
  (c1_v2, c2_v2, c3_v2),
  (c1_v3, c2_v3, c3_v3)
  

update

update t1 set c1=值, c2=值 where c3 = 約束條件迈勋；

delete

delete from t1 where c = 約束條件;
truncate table t1；

DQL 數(shù)據(jù)查詢語言(Data Query Language)

簡單查詢

/* 查詢所有 */
select * from stu;
/* 查詢指定字段 */
select name from stu;
/* 查詢指定字段別名 */
select name as '姓名' from stu;

條件查詢-比較條件

條件查詢-邏輯條件

條件查詢-判空查詢

模糊查詢

% 替代任意個(gè)字符

_ 替代一個(gè)字符

select * from stu where name like 'li%';
select * from stu where name like '%li%';
select * from stu where name like 'li_%';

去重查詢

select distinct class from stu;
select distinct(class) from stu;

范圍查詢

between ... and ...

/*查詢age在15-18范圍的學(xué)生*/
select * from stu where age between 15 and 18;

離散查詢

in

/*查詢age是15和18的學(xué)生*/
select * from stu where age in (15, 18);

聚合查詢

分組查詢（group by）

把行按字段分組醋粟，常用于統(tǒng)計(jì)場合靡菇，一般和聚合函數(shù)連用

select class_name, count(class_name) from stu group by class_name;

having條件查詢

對查詢的結(jié)果進(jìn)行篩選操作，一般跟在group by之后

/* having 的條件必須是查詢結(jié)果中的 */
select class_name, count(class_name) from stu group by class_name having count(class_name) > 2;

排序（order by）

對查詢的結(jié)果進(jìn)行排序操作

asc 升序米愿；desc 降序厦凤；默認(rèn)asc

select * from stu order by age;
select class_name, COUNT(*) as 'sumTotal' from stu group by class_name having COUNT(*) > 2 order by COUNT(*) desc;

分頁查詢（limit）

對查詢結(jié)果起到限制條數(shù)的作用

limit n，m n:代表起始條數(shù)值育苟，不寫默認(rèn)為0较鼓；m代表：取出的條數(shù)

select * from stu limit 2, 5;

子查詢（exists）

exists型子查詢后面是一個(gè)受限的select查詢語句；如果exists后的內(nèi)層查詢能查出數(shù)據(jù)宙搬，則返回 TRUE 表示存在笨腥；為空則返回 FLASE則不存在

分為倆種：exists跟 not exists
/* select 1 from employee where 1=1; */
select * from 表名 a where exists (select 1 from 表名2 where 條件);
select * from 表名 a where not exists (select 1 from 表名2 where 條件);
eg: 
/* 查詢已經(jīng)分班的學(xué)生 */
select * from stu a where exists (select 1 from class_table b where a.class_id = b.class_id);
/* 查詢未分班的學(xué)生 */
select * from stu a where exists (select 1 from class_table b where a.class_id = b.class_id);

外連接

外連接分為左外連接（左連接）、右外連接（右連接）兩種

左連接

left join 是left outer join的簡寫勇垛，左(外)連接，左表(a_table)的記錄將會全部表示出來士鸥， 而右表(b_table)只會顯示符合搜索條件的記錄闲孤。右表記錄不足的地方均為NULL

語法：left join 表名 on 條件 / left outer 表名 join on 條件

/*查詢已經(jīng)分班的學(xué)生信息*/
select b.class_name, a.* from stu a left join class_table b on a.class_id = b.class_id;

右連接

right join是right outer join的簡寫，與左(外)連接相反，右(外)連接讼积，左表(a_table)只會顯示符合搜索條件的記錄肥照，而右表(b_table)的記錄將會全部表示出來。左表記錄不足的地方均為NULL

語法：right join 表名 on 條件/ right outer 表名 join on 條件

/*查詢已經(jīng)分班的學(xué)生信息*/
select a.class_name, b.* from class_table a left join stu b on b.class_id = a.class_id;

內(nèi)連接

獲取兩個(gè)表中字段匹配關(guān)系的記錄（交集）

語法：INNER JOIN 表名 ON 條件;

/*查詢已經(jīng)分班的學(xué)生信息*/
select b.class_name, a.* from stu a left join class_table b on a.class_id = b.class_id;

聯(lián)合查詢

多個(gè)查詢語句的查詢結(jié)果結(jié)合在一起

語法：

• ... UNION ... （去除重復(fù)）
• ... UNION ALL ...（不去重復(fù)）

注意事項(xiàng)：

• 兩個(gè)select語句的查詢結(jié)果的“字段數(shù)”必須一致勤众；
• 通常舆绎，也應(yīng)該讓兩個(gè)查詢語句的字段類型具有一致性；
• 也可以聯(lián)合更多的查詢結(jié)果们颜；
• 用到order by排序時(shí)吕朵，需要加上limit（加上最大條數(shù)就行），需要對子句用括號括起來

(select * from stu where class_id = 2 order by scope limit 999)
union
(select * from stu where class_id = 2 order by scope desc limit 999)

DCL 數(shù)據(jù)控制語言(Data Control Language)

修改用戶密碼

以root用戶為例

已知密碼修改

1. SET PASSWORD FOR 用戶@ip = '密碼';
   -- 使用root用戶先登錄
   -- SET PASSWORD FOR root@localhost = '123456';
   

2. update mysql.user set authentication_string=password('密碼') where user='用戶';
   -- 使用root用戶先登錄 8版本以上失效
   -- update mysql.user set authentication_string=password('123456') where user='root';
   

3. mysqladmin -uroot -p舊密碼 password 新密碼;
   -- 無需root用戶登錄
   -- mysqladmin -uroot -p123456 password;
   -- 確認(rèn)密碼
   

忘記密碼修改

1. 修改配置文件在[mysqld]下面加上 skip-grant-tables（跳過權(quán)限驗(yàn)證）
2. 重啟mysql服務(wù)
3. 無密碼登錄
4. 修改密碼

限制root用戶登錄方式

指定Ip登錄

• 查看允許root登錄的IP地址

  select user, host from mysql.user where user = 'root';
  -- 結(jié)果 %代表不限登錄的ip地址
  +------+------+
  | user | host |
  +------+------+
  | root | %    |
  +------+------+
  1 row in set (0.17 sec)
  

• 指定ip地址

  update mysql.user set host = 'localhost' where user = 'root';
  

• 刷新權(quán)限

  flush privileges;
  

用戶管理

創(chuàng)建用戶

語法：

create user '用戶名'@'IP地址' identified by '密碼';
-- create user 'testUser'@'%' identified by '123456';
-- identified by '密碼' 省略之后密碼默認(rèn)為空

-- 創(chuàng)建用戶時(shí)指定網(wǎng)段
create user '用戶名'@'192.%.%.%' identified by '密碼';

查看用戶權(quán)限

語法：

show grants for '用戶名'@'IP地址'窥突；

示例：

mysql> show grants for 'testUser';
+--------------------------------------+
| Grants for testUser@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO `testUser`@`%` |
+--------------------------------------+
-- 解釋
-- USAGE：無權(quán)限
-- *.*  ： 所有庫.所有表

mysql> show grants for 'root'@'localhost'\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
3 rows in set (0.00 sec)
-- 解釋
-- WITH GRANT OPTION:表示這個(gè)用戶擁有g(shù)rant權(quán)限努溃，即可以對其他用戶授權(quán)

刪除用戶

1. drop user '用戶名'@'IP地址';
   -- drop user 'testUser'@'%';
   flush privileges;
   

2. delete from mysql.user where user='用戶名';
   flush privileges;
   

權(quán)限控制

授權(quán)

1. 對已有用戶收取

   語法1：grant 權(quán)限1,權(quán)限2..... on 數(shù)據(jù)庫名.表名 to '用戶'@'Ip地址';

   示例：

   -- 為testUser賦予db1庫中的stu表的查詢權(quán)限
   grant select on db1.stu for 'testUser'@'localhost';
   flush privileges;
   -- 為testUser@localhost用戶賦予超級用戶權(quán)限：
   grant all privileges on *.* for 'testUser'@'localhost' with grant option;
   flush privileges;
   

   說明：

   grant：授權(quán)
   all privileges:所有的權(quán)限
   on *.*：在哪個(gè)數(shù)據(jù)庫的那個(gè)表
   to username@localhost：對哪個(gè)用戶的哪個(gè)主機(jī)
   with grant option: 是不是 將username用戶自己本身的權(quán)限賦給其他賬戶
   

回收

語法1：revoke 權(quán)限1,權(quán)限2..... on 數(shù)據(jù)庫名.表名 from '用戶'@'Ip地址';

示例：

revoke select on db1.stu from 'testUser'@'localhost';
flush privileges;

語法2：delete from mysql.user where user='用戶名'; （直接回收登錄權(quán)限）

事務(wù)

作為單個(gè)邏輯工作單元執(zhí)行的一系列操作（對數(shù)據(jù)庫的相關(guān)增刪改查的操作），要么完全地執(zhí)行阻问，要么完全地不執(zhí)行

mysql中使用事務(wù)時(shí)梧税，必須使用innodb引擎（engine=innodb）

事務(wù)的特性

• 原子性(Atomicity)：事務(wù)必須是原子工作單元，一個(gè)事務(wù)中的所有語句称近，應(yīng)該做到：要么全做第队，要么一個(gè)都不做；
• 一致性(Consistency):讓數(shù)據(jù)保持邏輯上的“合理性”刨秆，比如：小明給小紅打10000塊錢凳谦，既要讓小明的賬戶減少10000，又要讓小紅的賬戶上增加10000塊錢坛善；
• 隔離性(Isolation)：如果多個(gè)事務(wù)同時(shí)并發(fā)執(zhí)行晾蜘，但每個(gè)事務(wù)就像各自獨(dú)立執(zhí)行一樣。
• 持久性(Durability)：一個(gè)事務(wù)執(zhí)行成功眠屎，則對數(shù)據(jù)來說應(yīng)該是一個(gè)明確的硬盤數(shù)據(jù)更改（而不僅僅是內(nèi)存中的變化）剔交。

事務(wù)開啟提交回滾

• 事務(wù)的開啟：begin; start transaction;
• 事務(wù)的提交：commit;
• 事務(wù)的回滾：rollback;

autocommit設(shè)置

臨時(shí)設(shè)置

mysql> set autocommit=0;
mysql> show variables like 'autocommit';
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | autocommit    | OFF   |
  +---------------+-------+
-- 解釋 OFF（0）：表示關(guān)閉 
--      ON （1）：表示開啟

永久設(shè)置

修改配置文件：vi /etc/my.cnf 在[mysqld]下面加上：autocommit=1 記得重啟服務(wù)才會生效

視圖

視圖（view）是一種虛擬存在的表，是一個(gè)邏輯表改衩，它本身是不包含數(shù)據(jù)的岖常；可以展現(xiàn)基表（用來創(chuàng)建視圖的表叫做基表base table）的部分或全部數(shù)據(jù)。

使用視圖的大部分情況是為了保障數(shù)據(jù)安全性葫督，提高查詢效率竭鞍。

創(chuàng)建

語法：

create view <視圖名稱> [(字段)] as select 語句;
create or replace view <視圖名稱> [(字段)] as select 語句;
-- [(字段)] 可選

修改

alter view <視圖名稱> [(字段)] as select 語句;
-- [(字段)] 可選

刪除

drop view <視圖名稱> ;

優(yōu)缺點(diǎn)

• 優(yōu)點(diǎn)
  • 簡單：使用視圖的用戶完全不需要關(guān)心后面對應(yīng)的表的結(jié)構(gòu)、關(guān)聯(lián)條件和篩選條件橄镜，對用戶來說已經(jīng)是過濾好的復(fù)合條件的結(jié)果集偎快。
  • 安全：使用視圖的用戶只能訪問他們被允許查詢的結(jié)果集，對表的權(quán)限管理并不能限制到某個(gè)行某個(gè)列洽胶，但是通過視圖就可以簡單的實(shí)現(xiàn)晒夹。
  • 數(shù)據(jù)獨(dú)立：一旦視圖的結(jié)構(gòu)確定了，可以屏蔽表結(jié)構(gòu)變化對用戶的影響，源表增加列對視圖沒有影響;源表修改列名丐怯，則可以通過修改視圖來解決喷好，不會造成對訪問者的影響。
  • 不占用空間：視圖是邏輯上的表读跷，不占用內(nèi)存空間
• 缺點(diǎn)
  • 性能差：sql server必須把視圖查詢轉(zhuǎn)化成對基本表的查詢梗搅，如果這個(gè)視圖是由一個(gè)復(fù)雜的多表查詢所定義，那么效览，即使是視圖的一個(gè)簡單查詢无切，sql server也要把它變成一個(gè)復(fù)雜的結(jié)合體，需要花費(fèi)一定的時(shí)間钦铺。
  • 修改限制：當(dāng)用戶試圖修改試圖的某些信息時(shí)订雾，數(shù)據(jù)庫必須把它轉(zhuǎn)化為對基本表的某些信息的修改，對于簡單的試圖來說矛洞，這是很方便的洼哎，但是，對于比較復(fù)雜的試圖沼本，可能是不可修改的噩峦。

觸發(fā)器

觸發(fā)器就是監(jiān)視某種情況，并觸發(fā)某種操作

新建語法：

create trigger 觸發(fā)器名稱 before/after insert/update/delete on 表名
for each row
begin
sql語句;
end;
-- 解釋
-- after/before:可以設(shè)置為事件發(fā)生前或后
-- insert/update/delete:它們可以在執(zhí)行insert抽兆、update或delete的過程中觸發(fā)
-- for each row:每隔一行執(zhí)行一次動作
-- sql語句中可以使用 `new` 來獲取被監(jiān)控表的觸發(fā)監(jiān)控的數(shù)據(jù)

刪除語法：

drop trigger 觸發(fā)器名稱;

存儲過程

存儲過程就是把復(fù)雜的一系列操作识补，封裝成一個(gè)過程

創(chuàng)建語法

create procedure 名稱 (參數(shù)...)
begin
過程體;
過程體;
...
end

說明：

• 參數(shù)

  格式：in|out|inout 參數(shù)名稱 類型（長度）
  in：   表示調(diào)用者向過程傳入值（傳入值可以是字面量或變量）
  out：  表示過程向調(diào)用者傳出值(可以返回多個(gè)值)（傳出值只能是變量）        
  inout：    既表示調(diào)用者向過程傳入值，又表示過程向調(diào)用者傳出值（值只能是變量）
  

• 聲明變量

  declare 變量名 類型(長度) default 默認(rèn)值;
  

• 外部變量賦值

  set @變量名=值;
  

• 調(diào)用

  call 名稱(@變量名);
  

樣例：

-- 定義mysql命令行結(jié)束符
delimiter //
-- 外部申明變量
create procedure queryTotal(in n int)
begin
select * from stu limit n;
end
//
-- 變量復(fù)制
set @n = 5//
-- 調(diào)用
call queryTotal(@n)//

----------------------------------------------------

-- 內(nèi)部申明變量
create procedure queryTotal()
begin
declare n int default 5;
select * from stu limit n;
end
//
-- 調(diào)用
call queryTotal()//

刪除語法

drop procedure 名稱;

查看語法

show create procedure 名稱\G

引擎

數(shù)據(jù)庫引擎是數(shù)據(jù)庫底層軟件組件辫红，不同的存儲引擎提供不同的存儲機(jī)制凭涂，索引技巧，鎖定水平等功能贴妻，使用不同的數(shù)據(jù)庫引擎切油，可以獲得特定的功能

查看引擎

• 查看所有引擎列表

  show engines;
  

• 查看當(dāng)前庫所有表引擎

  show table status\G
  

• 查看表引擎

  show create table 表名;
  
  

設(shè)置引擎

• 默認(rèn)設(shè)置

  修改mysql配置文件，[mysqld]下添加default-storage-engine=MyIsAM 名惩，重啟mysql服務(wù)澎胡。

• 創(chuàng)建表指定引擎

  create table 表名 (...) engine='InnoDB';
  

修改引擎

• 修改表引擎

  alter table 表名 engine = 'MyISAM';
  

引擎對比

索引

索引是一個(gè)單獨(dú)的，存儲在磁盤中上的數(shù)據(jù)庫結(jié)構(gòu)娩鹉，它們包含著對數(shù)據(jù)表里的所有記錄的引用指針攻谁；使用索引可以快速的找出在某列或多列中有特定值的行

• 優(yōu)點(diǎn)
  • 可以加快數(shù)據(jù)的檢索速度
  • 可以保證表數(shù)據(jù)的完整性與準(zhǔn)確性

• 缺點(diǎn)
  • 索引需要占用物理空間
  • 對表中的數(shù)據(jù)進(jìn)行改動時(shí)，索引也需要跟著動態(tài)維護(hù)弯予，降低了數(shù)據(jù)的維護(hù)速度

普通索引

允許出現(xiàn)相同的索引內(nèi)容戚宦，允許空（null）值

• 創(chuàng)建

  1. 建表創(chuàng)建

     create table teacher ( id int(7) zerofill auto_increment not null, name varchar(64) not null, age int(2) default null, create_time datetime, index (id)) default charset=utf8;
     

  2. 添加索引

     語法1：alter table 表名 add index [索引名稱] (字段名稱);

     -- 注意：語法1創(chuàng)建索引時(shí)如果未指定索引名稱，則默認(rèn)的索引名稱就是字段名稱
     alter table teacher add index index_create_time (create_time);
     

     語法2：create index 索引名稱 on 表名 (字段名);

     create index index_create_time on teacher (create_time);
     

• 刪除

  語法1：alter table 表名 drop index 索引名稱;

  alter table teacher drop index index_create_time;
  

  語法2：drop index 索引名稱 on teacher;

  drop index i_c_t on teacher;
  

• 查看

  語法1：show index from 表名\G

  語法2：show create table 表名\G

唯一索引

不允許出現(xiàn)相同的索引內(nèi)容锈嫩，允許空（null）值

• 創(chuàng)建

  1. 建表創(chuàng)建

     create table stu ( id int(7) zerofill auto_increment not null, name varchar(64) not null, age int(2) default null, create_time datetime, unique (id)) default charset=utf8;
     

  2. 添加索引

     語法1：alter table 表名 add unique[索引名稱] (字段名稱);

     -- 注意：語法1創(chuàng)建索引時(shí)如果未指定索引名稱阁苞，則默認(rèn)的索引名稱就是字段名稱
     alter table stu add unique unique_create_time (create_time);
     

• 刪除

  語法1：alter table 表名 drop index 索引名稱

  alter table 表名 drop index unique_create_time;
  

  語法2：drop index 索引名稱 on stu;

  drop index unique_create_time on stu;
  

• 查看

  語法1：show index from 表名\G

  語法2：show create table 表名\G

主鍵索引

一種特殊的唯一索引困檩，不允許有空值祠挫；主鍵是表的某一列那槽，這一列的值是用來標(biāo)志表中的每一行數(shù)據(jù)；

注意：每一張表只能擁有一個(gè)主鍵

• 創(chuàng)建

  1. 建表創(chuàng)建

     create table stu (id int(7) zerofill auto_increment not null, name varchar(64) not null, age int(2) default null, create_time datetime, primary key (id)) default charset=utf8;
     

  2. 添加索引

     語法1：alter table 表名 add primary key (字段名);

     alter table stu add primary key (id);
     

• 刪除

  語法1：alter table 表名 drop primary key;

  -- 刪除主鍵之前必須先刪除自增約束
  -- 每張表只有一個(gè)主鍵等舔，刪除時(shí)直接drop primary key即可
  alter table stu drop primary key;
  

• 查看

  語法1：show index from 表名\G

  語法2：show create table 表名\G

全文索引

全文索引是將存儲在數(shù)據(jù)庫中的文章或者句子等任意內(nèi)容信息查找出來的索引，單位是詞

數(shù)據(jù)準(zhǔn)備

-- 建表
 create table tfulltext(id int(7) unique auto_increment, wdesc varchar(256),fulltext (wdesc)) engine=InnoDB charset=utf8;
-- 數(shù)據(jù)
insert into tfulltext (wdesc) values
('he Apache Tomcat software is an open source implementation of the Java Servlet'),
('Java Servlet, JavaServer Pages, Java Expression'),
('Apache Tomcat software powers numerous large-scale, mission-critical web applications '),
('Add new attribute persistAuthentication to both StandardManager'),
('Tomcat 服務(wù)器是一個(gè)免費(fèi)的開放源代碼的Web 應(yīng)用服務(wù)器甚牲，屬于輕量級應(yīng)用服務(wù)器'),
('訣竅是蝶柿，當(dāng)配置正確時(shí)，Apache 為HTML頁面服務(wù)雏赦，而Tomcat 實(shí)際上運(yùn)行JSP 頁面和Servlet'),
('Full details of these changes, and all the other changes'),
('ength AJP secret will now behave as if it has not been specified'),
('is yes no not you me'),
('這樣配置好了，即使以后從一臺服務(wù)器移植到另一臺服務(wù)器芙扎，不做任何修改也能運(yùn)行起來');

• 創(chuàng)建

  1. 建表創(chuàng)建

      create table tfulltext(id int(7) unique auto_increment, wdesc varchar(256),fulltext (wdesc)) engine=InnoDB charset=utf8;
     

  2. 添加索引

     語法：alter table 表名 add fulltext(字段名);

     alter table tfulltext add fulltext(wdesc);
     

• 刪除

  語法1：drop index 索引名稱 on 表名星岗；

  drop index wdesc on tfulltext;
  

  語法2：alter table 表名 drop index 索引名稱;

  alter table tfulltext drop index wdesc;
  

• 查看

  語法1：show index from 表名\G

  語法2：show create table 表名\G

• 查詢語句

  語法：select * from 表名 where match (字段名) against ('查詢條件')

  select * from tfulltext where match(wdesc) against ('Tomcat');
  select * from tfulltext where match(wdesc) against ('屬于輕量級應(yīng)用服務(wù)器');
  

• in boolean mode 查詢模式

  語法：select * from 表名 where match (字段名) against ('*查詢條件' in boolean mode)

  -- 注意通配符*只能放在詞的后面
  select * from tfulltext where match(wdesc) against ('tomca*' in boolean mode);
  select * from tfulltext where match(wdesc) against ('屬于輕量級應(yīng)*' in boolean mode);
  

• 查看匹配度

  mysql> select id, match(wdesc) against ('Tomcat') as matching from tfulltext;
  +----+--------------------+
  | id | matching           |
  +----+--------------------+
  |  1 | 0.2734021842479706 |
  |  2 |                  0 |
  |  3 | 0.2734021842479706 |
  |  4 |                  0 |
  |  5 | 0.2734021842479706 |
  |  6 |                  0 |
  |  7 |                  0 |
  |  8 |                  0 |
  |  9 |                  0 |
  | 10 |                  0 |
  +----+--------------------+
  10 rows in set (0.00 sec)
  -- matching：匹配度，為0不出現(xiàn)
  

• 停止詞

  -- 出現(xiàn)頻率很高的詞戒洼，將會使全文索引失效
  is俏橘，no，not圈浇，you寥掐，me，yes磷蜀，...
  

• 注意

  • 一般情況下創(chuàng)建全文索引的字段數(shù)據(jù)類型為 char召耘、varchar、text 蠕搜。其它字段類型不可以
  • 全文索引不針對非常頻繁的詞做索引怎茫。比如is，no妓灌，not轨蛤，you，me虫埂，yes這些祥山，我們稱之為停止詞
  • 對英文檢索時(shí)忽略大小寫

外鍵索引

外鍵就是作用于兩個(gè)表數(shù)據(jù)之間的鏈接的一列或多列，用來保證表與表之間的數(shù)據(jù)的完整性和準(zhǔn)確性

• 創(chuàng)建

  1. 建表創(chuàng)建

     CREATE TABLE t_stu (
       stu_id int(11) NOT NULL COMMENT '學(xué)生編號',
       stu_name varchar(50) DEFAULT NULL COMMENT '學(xué)生姓名',
       class_id varcher(50) DEFAULT NULL COMMENT '班級編號',
       primary key(stu_id),
       foreign key(stu_id) references t_class(class_id)
     ) engine=InnoDB default charset=utf8;
     

  2. 添加

     語法：alter table 表名 add foreign key(字段名) references 關(guān)聯(lián)表名(關(guān)聯(lián)表字段);

     alter table t_stu add foreign key(stu_id) references t_class(stu_id);
     

• 刪除

  注意：刪除外鍵索引之前掉伏，必須要先刪外鍵約束

  -- 刪除外鍵約束語法
  -- alter table 表名 drop foreign key 約束名稱;
  alter table employee drop foreign key employee_ibfk_1;
  

  語法1：drop index 索引名稱 on 表名;

  語法2：alter table 表名 drop index 索引名稱

• 查看

  語法1：show index from 表名\G

  語法2：show create table 表名\G

• 注意

  • 倆個(gè)表澳窑，主鍵跟外鍵的字段類型一定要相同
  • 要使用外鍵約束表的引擎一定得是InnoDB引擎，MyISAM是不起作用的
  • 在干掉外鍵索引之前必須先把外鍵約束刪除麻裁，才能刪除索引

組合索引

聯(lián)合索引又稱組合索引或者復(fù)合索引煎源，是建立在倆列或者多列以上的索引

• 創(chuàng)建

  1. 建表創(chuàng)建

     create table stu ( id int(7) zerofill auto_increment not null, name varchar(64) not null, age int(2) default null,scope int(3) default null, class_id varchar (64) default null, create_time datetime, primary (id), index(name, age, scope)) default charset=utf8;
     

  2. 添加索引

     語法1：alter table 表名 add index [索引名稱] (字段1, 字段2, ...);

     -- 注意：語法1創(chuàng)建索引時(shí)如果未指定索引名稱，則默認(rèn)的索引名稱就是字段名稱
     alter table teacher add index name (name, age, scope);
     

     語法2：create index 索引名稱 on 表名 (字段1, 字段2, ...);

     create index name on teacher (name, age, scope);
     

• 刪除

  語法1：alter table 表名 drop index 索引名稱;

  alter table stu drop index name;
  

  語法2：drop index 索引名稱 on teacher;

  drop index name on stu;
  

• 查看

  語法1：show index from 表名\G

  語法2：show create table 表名\G

• 最左原則

  聯(lián)合索引是以創(chuàng)建聯(lián)合索引最左字段為基礎(chǔ)進(jìn)入查詢，如果查詢條件中沒有最左字段姑隅，則該聯(lián)合索引失效

  -- 最左字段為 (name, age, scope) 的name字段
  create index name on teacher (name, age, scope);
  

索引注意事項(xiàng)

• 索引并非越多越好，過多的索引會增加數(shù)據(jù)的維護(hù)速度還有磁盤空間的浪費(fèi)鄙陡。

sql優(yōu)化

慢查詢?nèi)罩?/h3>

臨時(shí)設(shè)置

mysql服務(wù)重啟后趁矾，慢查詢設(shè)置恢復(fù)默認(rèn)設(shè)置

• 查看慢查詢配置 show variables like '%slow%';

  mysql> show variables like '%slow%';
  +---------------------------+------------------------------------------------------+
  | Variable_name             | Value                                                |
  +---------------------------+------------------------------------------------------+
  | log_slow_admin_statements | OFF                                                  |
  | log_slow_extra            | OFF                                                  |
  | log_slow_slave_statements | OFF                                                  |
  | slow_launch_time          | 2                                                    |
  | slow_query_log            | OFF                                                  |
  | slow_query_log_file       | /usr/local/software/mysql/mysqldb/localhost-slow.log |
  +---------------------------+------------------------------------------------------+
  6 rows in set (0.00 sec)
  
  mysql>
  

• 打開慢查詢 set global slow_query_log = on;

• 設(shè)置慢查詢?nèi)罩灸夸洠?code>set global slow_query_log_file = '/usr/local/software/mysql/mysqldb/localhost-slow.log';

• 查看慢查詢的時(shí)間臨界值show variables like '%long%';

  mysql> show variables like '%long%';
  +----------------------------------------------------------+-----------+
  | Variable_name                                            | Value     |
  +----------------------------------------------------------+-----------+
  | long_query_time                                          | 10.000000 |
  | performance_schema_events_stages_history_long_size       | 1000      |
  | performance_schema_events_statements_history_long_size   | 1000      |
  | performance_schema_events_transactions_history_long_size | 1000      |
  | performance_schema_events_waits_history_long_size        | 1000      |
  +----------------------------------------------------------+-----------+
  5 rows in set (0.01 sec)
  
  mysql> 
  

• 設(shè)置慢查詢的時(shí)間標(biāo)準(zhǔn) set long_query_time = 0.05

永久設(shè)置

## 修改配置文件/etc/my.cnf 添加
[mysqld]
slow_query_log = 1
long_query_time = 0.05
slow_query_log_file =/usr/local/software/mysql/mysqldb/localhost-slow.log
## 重啟服務(wù)

explain

語法：explain 查詢語句;

mysql> explain select * from stu\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: stu
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from stu where id = '1'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: stu
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql>

-- possible_keys 可能使用到的索引列表
-- key 查詢使用的索引名稱

性能分析

• 查看性能詳情配置 show variables like '%profiling%';

  mysql> show variables like '%profiling%';
  +------------------------+-------+
  | Variable_name          | Value |
  +------------------------+-------+
  | have_profiling         | YES   |
  | profiling              | OFF   |
  | profiling_history_size | 15    |
  +------------------------+-------+
  3 rows in set (0.00 sec)
  
  mysql>
  

• 開啟性能記錄功能 set profiling = on

• 查看性能的記錄 show profiles;

  mysql> show profiles;
  +----------+------------+-----------------------------------------------------+
  | Query_ID | Duration   | Query                                               |
  +----------+------------+-----------------------------------------------------+
  |        1 | 0.05210575 | select * from stu                                   |
  |        2 | 0.00084050 | select * from stu where id = '1'                    |
  +----------+------------+-----------------------------------------------------+
  2 rows in set, 1 warning (0.00 sec)
  
  mysql>
  

• 查看語句的執(zhí)行性能詳情 show profile for query Query_ID

  參考：https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

  mysql> show profile for  query 1;
  +--------------------------------+----------+
  | Status                         | Duration |
  +--------------------------------+----------+
  | starting                       | 0.049974 |
  | Executing hook on transaction  | 0.000023 |
  | starting                       | 0.000018 |
  | checking permissions           | 0.000023 |
  | Opening tables                 | 0.000091 |
  | init                           | 0.001285 |
  | System lock                    | 0.000044 |
  | optimizing                     | 0.000019 |
  | statistics                     | 0.000053 |
  | preparing                      | 0.000034 |
  | executing                      | 0.000008 |
  | Sending data                   | 0.000245 |
  | end                            | 0.000013 |
  | query end                      | 0.000007 |
  | waiting for handler commit     | 0.000021 |
  | closing tables                 | 0.000025 |
  | freeing items                  | 0.000187 |
  | cleaning up                    | 0.000039 |
  +--------------------------------+----------+
  18 rows in set, 1 warning (0.11 sec)
  mysql>
  

mysqldump備份恢復(fù)

備份

語法：mysqldump -u 用戶 -h host -p 密碼 庫名 表名> 路徑

1. 單庫（無建庫語句）

   mysqldump -uroot -proot -h139.224.101.91 db1 > /opt/mysqlback/db1.sql
   

2. 單庫（有建庫語句）

   mysqldump -uroot -proot -h139.224.101.91 --databases db1 > /opt/mysqlback/db1-db.sql
   

3. 單庫單表

   mysqldump -uroot -proot -h139.224.101.91 db1 stu > /opt/mysqlback/db1-stu.sql
   

4. 多庫

   mysqldump -uroot -proot -h139.224.101.91 --database db1 db2 > /opt/mysqlback/db12.sql
   

5. 全庫

   mysqldump -uroot -proot -h139.224.101.91 --all-databases > /opt/mysqlback/all.sql
   

恢復(fù)

語法：mysql -u用戶名 -p密碼 -hIP地址 < 備份文件路徑

mysql -uroot -proot -h139.224.101.91  < /opt/mysqlback/db1-db.sql

字符集

• 查看數(shù)據(jù)庫字符集

  mysql> show variables like 'character%';
  +--------------------------+---------------------------------------------------------+
  | Variable_name            | Value                                                   |
  +--------------------------+---------------------------------------------------------+
  | character_set_client     | gbk                                                     |
  | character_set_connection | gbk                                                     |
  | character_set_database   | utf8mb4                                                 |
  | character_set_filesystem | binary                                                  |
  | character_set_results    | gbk                                                     |
  | character_set_server     | utf8mb4                                                 |
  | character_set_system     | utf8                                                    |
  | character_sets_dir       | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
  +--------------------------+---------------------------------------------------------+
  8 rows in set, 1 warning (0.00 sec)
  mysql>
  

• 字符集配置解釋

  # 客戶端字符集
  character_set_client #客戶端請求數(shù)據(jù)的字符集
  character_set_connection #客戶端與服務(wù)器連接的字符集
  character_set_results #返回給客戶端的字符集(從數(shù)據(jù)庫讀取到的數(shù)據(jù)是什么編碼的)
  
  # 服務(wù)端字符吉
  character_set_database #數(shù)據(jù)庫服務(wù)器中某個(gè)庫使用的字符集設(shè)定，如果建庫時(shí)沒有指明则北，將默認(rèn)使用配置上的字符集
  character_set_server #為服務(wù)器安裝時(shí)指定的默認(rèn)字符集設(shè)定尚揣。
  
  # 系統(tǒng)字符集
  character_set_system #系統(tǒng)字符集(修改不了的蒿辙，就是utf8)
  character_sets_dir #mysql字符集文件的保存路徑
  

• 客戶端字符集修改

  • 臨時(shí)

    set names 字符集
    

  • 永久

    ## 配置文件中修改
    [client]
    default-character-set=gbk
    

• 服務(wù)端字符集修改

  • 永久

    # 配置文件中修改，會作用于創(chuàng)建庫表時(shí)默認(rèn)字符集
    [mysqld]
    character_set_server=gbk
    

• 庫的字符集修改

  alter database 庫名 default character set utf8;
  

• 表字符集修改

  alter table 表名 default character set utf8;