MYSQL從入門到精通教程

Mysql從入門到精通筆記

來源于有道云筆記

終端操縱Mysql數據庫

以管理員身份運行cmd

net start mysql --------啟動服務

編輯

發生系統錯誤 5。

拒絕訪問。

啟動：service mysqld start--------------------net start mysql(管理員身份運行)

停止：服務 mysqld 停止

重啟：服務 mysqld restart

重載配置：service mysqld?reload

mysql -uroot -p 進入本地數據庫

help 幫助手冊

編輯

show variables like "%char%";----查看數據庫編碼情況

利用標準SQL語句對數據庫操作

show databases 查看所有數據庫

show engines 查詢存儲引擎

describe 數據表名 查看表結構，簡寫desc 查看單列 desc 數據表名 列名

modify 修改子句定義字段

show variables like "%char%";------查看數據庫編碼

編輯

新建數據庫

create database new_databases;

刪除數據庫

drop database new_database;

操作數據表

show tables;(需指定數據庫，use 數據庫)

show columns from 數據表 顯示數據表屬性，類型等其它信息

新建數據表

use new_databases; create table new_table( -> id int, -> username char(10));

if not exists 屏蔽錯誤

查詢指定行 select * from 數據表 where 條件表達式

刪除數據表

drop table new_table;

復制表

create table 數據表名

as like 源數據表名

不加as只是復制表的結構不復制內容

刪除表

drop table 數據表名

刪除多張數據表可用逗號“，”分隔

重命名表

rename table 源數據表名 to 新數據表名

可對多個數據表進行重命名，多個表之間用逗號“，”分隔

查看表數據

select * from 數據表

運算符

^開頭

$結尾

mysql> select id=2,id,name,pwd from tb_admin;

+------+----+-----------+-----+

| id=2 | id | name | pwd |

+------+----+-----------+-----+

| 0 | 1 | mr | 111 |

| 1 | 2 | 肖承聰 | 222 |

| 0 | 3 | 張三 | 333 |

+------+----+-----------+-----+

查看id等于2的，id等于2返回值為1，否則0

插入數據

insert into 數據表 values(值1,值2...);-----------------------（完整插入）

insert into 數據表 (列1,列2....)values(值1,值2....);------------（部分插入）

insert into 數據表 (列1,列2,列3....)

values(值1,值1,值1)

,（值2,值2,值2）

,.........

insert into 數據表

set id=8,name='xiao',pwd=888;-------------------(指定插入)

插入查詢結果

insert into 數據庫名.插入到的表

（列1，列2....）

select 列1，列2 from 插入到的表

修改數據

update 數據表 set 列=666 where 條件表達式

查詢指定行 select * from 數據表 where 條件表達式

刪除數據

delete from 數據表 where 條件表達式

truncate table 數據表名--------------清空數據表內容

更新數據

update new_table -> set id=2,username="cat" -> where id =1;

數據查詢

*代表所有列，可單獨查列1,列2 之間用逗號隔開

查詢一個數據表

select * from 數據表

查詢表中的一列或多列

select 列1，列2 from 數據表

從一個表或者多個表獲取數據

select 數據表1.列，數據表1.列，數據表2.列，數據表2.列 from 數據表1，數據表

查詢指定數據

select * from 數據表 where 列=‘查詢的內容’

select *from 表名 where 列 [not] in (元素1，元素2...)---not可選項 ，相反的意思

去空格查詢

rtim

BETWEEN范圍查詢

select *from 數據表 where 列 [not] between 取值1 and 取值2

LIKE字符匹配查詢

select * from 數據表 where 列 like ‘內容%’----------- %匹配一個或多個---------------- _匹配一個

IS NULL查詢空值

select * from 數據表 where 列 is null

AND,OR多條件查詢

select * from 數據表 where 條件1 and 條件2....-----需兩個都滿足

select * from 數據表 where 條件1 or 條件2....-----滿足一個即可

DISTINCT去除結果中的重復行

select distinct 列 from 數據表

ORDER　BY對查詢結果排序

order by 字段名 [asc(升)|esc(降)]

GROUP BY 分組查詢

in 查詢（規定多值）=規定一個值

SELECT?column_name(s)

FROM?table_name

WHERE?column_name?IN (value1,value2,...);

LIMIT限制查詢結果數量

select * from 數據表 order by id asc limit 3;

按照id 編號進行升序排序，顯示前3條記錄

select * from 數據表 order by id asc limit 1,2;

從編號1開始，查詢兩條記錄

count（）函數 查看（）有多少記錄

sum（）函數 計算字段數值總和

avg（ ）函數 求表中某個字段取值的平均值

max（）函數 求最大值

min（）函數 最小值

連接查詢

EXISTS 子查詢 ，返回一個真假值，判斷括號內的子查詢是否真，真就執行外層查詢

select *from 數據表 where exists (select * from 數據表 where id =27)

查詢表中是否存在id值為27，存在則執行括號外層數據表內容

當exists與其它查詢條件一起使用時，需使用and或者or來連接表達式exists關鍵字

UNION合并查詢

select user from數據表1

union

select user from 數據表2---------------合并表1和表2user的內容去除重復值

union all 不去除重復項

定義表和字段的別名

為表取別名

select * from tb_program p where p.talk='PHp';

tb_program表的別名為p；p.talk表示tb_program表中的talk字段

為字段取別名

select talk AS new from tb_program

正則表達式

字段名 regexp '匹配方式'

* 任意多少個

^開頭

$結尾

. 匹配任意字符包括回車換行符

[字符集合] 匹配字符集中任意一個字符

[^字符集合] 匹配除字符集合以外的任意一個字符

s1|s2|s3 匹配s1，s2和s3中的任意一個字符串

+ 匹配多個該字符之前的字符，包括一個-----------'j+a';查詢字段中a字符前面至少一個j字符的記錄

字符串{n}匹配字符串出現n次

字符串{m，n}匹配字符串出現至少m次，最多n次

CREATE TABLE 時的 SQL PRIMARY KEY 約束

下面的 SQL 在 "Persons" 表創建時在 "P_Id" 列上創建 PRIMARY KEY 約束：

MySQL：CREATE TABLE Persons

( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (P_Id) )

SQL Server / Oracle / MS Access：CREATE TABLE Persons

( P_Id int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )

ALTER TABLE 時的 SQL PRIMARY KEY 約束

當表已被創建時，如需在 "P_Id" 列創建 PRIMARY KEY 約束，請使用下面的 SQL：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons

ADD PRIMARY KEY (P_Id)

如需命名 PRIMARY KEY 約束，并定義多個列的 PRIMARY KEY 約束，請使用下面的 SQL 語法：

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons

ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

注釋：如果您使用 ALTER TABLE 語句添加主鍵，必須把主鍵列聲明為不包含 NULL 值（在表首次創建時）。

撤銷 PRIMARY KEY 約束

如需撤銷 PRIMARY KEY 約束，請使用下面的 SQL：

MySQL：

ALTER TABLE Persons

DROP PRIMARY KEY

SQL Server / Oracle / MS Access：

ALTER TABLE Persons

DROP CONSTRAINT pk_PersonID

常用函數

備份數據庫

備份一個數據庫（終端操作無需進入mysql）

mysqldump -uroot -p dbname table1 table2....>BackuoName.sql

dbname:數據庫的名稱

table1和table2參數表示表的名稱，沒有該參數時將備份整個數據庫

BackNme.sql 參數表示備份文件的名稱，文件名前可加一個絕對路徑

使用rot用戶備份tes數據庫下的studen表

mysqldump -uroot -p test student >D:\student.sql

備份多個數據庫

mysqldump -u username -p --databases dbname1 dbname2 >BackName.sql

加上databases這個選項，后面可以加上很多數據庫的名稱

備份所有數據庫

mysqldump -u root -p --all -databases >D:\all.sql

數據恢復

mysql -uroot -p [dbname]

dbname 參數表示數據庫名稱

指定數據庫名，表示還原該數據庫下的數據表，不指定數據庫名時，表示還原特定一個數據庫

mysql -u root -p

表的導入與導出

musqldump

mysqldump -u root -p --xml dbname table >D:/name.xml

將website數據庫下的jsj表導出為xml格式到D盤，文件名為xsgl.xml

mysqldump -u root -p --xml website jsj >D:/xsgl.xml

mysql

mysql -u root -p -e "select * from 語句" dbname >D:/sfzh.txt

使用-e選項就可執行sql語句，select用來查詢記錄

mysql -u root -p -e "select * from sfzh" website >D:/sfzh.txt

編輯

可用mysql導出xml文件和htnl文件

mysql -u root -p --xml或html -e "select * from sfzh" website >D:/sfzh.txt

存儲過程與存儲函數

創建存儲過程

create procedure proc_name ([proc_parameter[....]])

[characteristic.....] routine_body

sp_name參數時存儲過程的名稱；proc_parameter 表示存儲過程的參數列表；characteristic參數指定存儲過程的特性；routine_body 參數時sql代碼的內容，可以用begin.....end來標識sql代碼的開始和結束。

proc_parameter中的參數由3部分組成，它們分別時輸入輸出類型，參數名稱和參數類型。其中形式為[in | out | inout ]param_name type。param_name 參數時存儲過程的參數名稱；type參數指定存儲過程的參數類型，可以為mysql數據庫的任意類型。

mysql存儲過程的語句塊以begin開始，以end結束。內部以分號結束，分號“；”應更改為其它字符，更改結束標志可以用關鍵字delimiter定義，例如：將myql結束符設置為//

mysql>delimiter //

編輯

定義一個輸出變量count_num，存儲過程應用select語句從jsj表中獲取記錄總數，最后將結果傳遞給變量count_num。

show create count_of_student 查看存儲過程

創建存儲函數

create function sp_name ([proc_parameter[,....]])

returns type

[characteristic.....] routine_body

參數

說明

sp_name

存儲函數的名稱

fun_parameter

存儲函數的參數列表

returns type

指定返回值的類型

characteristic

指定存儲過程的特性

routine_body

sql代碼的內容

變量的應用

1.局部變量

declare a int

使用default為變量指定默認值

declare a int default 10

2全局變量（會話變量）

會話變量名以字符’@‘作為起始符

光標的應用

創建觸發器

create trigger 觸發器名 before | after 觸發事件

on 表名 for each row 執行語句

for each row 表示任何一條記錄上的操作滿足觸發事件都會觸發該觸發器（包括插入，更新，刪除）

執行語句指觸發器被觸發后執行的程序

例：

創建一個由插入命令insert觸發的觸發器auto_save_time

（1）創建一個名稱為timelog的表格

編輯

（2）創建名稱為auot_save_time的觸發器

定義了結束符為//

編輯

接下來通過向jsj表插入一條信息來查看觸發器的作用

編輯

編輯

創建具有多條執行語句的觸發器

create trigger 觸發器名 before | after 觸發事件

on 表名 for each row 執行語句

begin

執行語句列表

end

begin與end之間的多條執行語句需以結束分隔符“；”分開

創建一個delete觸發多條執行語句的觸發器delete_time_info

------創建timeinfo數據表

create table timeinfo( id int(11) primary key auto_increment, info varchar(50) not null )//

-------創建由delete觸發多條執行語句的觸發器 delect_time_info

create trigger delect_time_info after delete on jsj for each row begin insert into timelog(savetime)values(now()); insert into timeinfo(info)values('deleteact'); end //

---------執行delete并查看觸發結果

編輯

查看觸發器

show triggers;-------選擇xsgl數據庫查看該數據庫中存在的觸發器

select * from information_schema.triggers;-------查看數據庫中所有觸發器的詳細信息

information_schema是mysql中默認存在的庫

select * from information_schema.reiggers where trigger_name='觸發器名稱'；---------查看指定觸發器內容

刪除觸發器

drop trigger 觸發器名稱；

事務的應用

MyISAM類型的數據表不能支持事務，只有InnoDB或BDB

create table table_name(field_defitions)type=innodb/bdb

table_name為表名，而field_defitions為表內定義的字段等屬性，type指定數據表的類型，既可以是inodb也可以是bdb類型

alter table table_name type=innodb/bdb;----------在原有的數據表中可以更改支持的事務處理的類型

初始化事務

start transaction

創建事務

向名稱為jsj的數據表插入一條記錄，講解事務的從創建。首先打開數據庫，選定某個數據庫，然后初始化事務，最后創建事務，向指定的數據表中添加記錄

編輯

在用戶插入新表為innodb類型或更新原來表類型為innodb時，輸入命令卻提示“The InnoDB feature is disabled;you need 'InnoDB' to have it working”警告，則說明InnoDB表類型并沒有被開啟，用戶需要在MySQL文件目錄下找到my.ini文件，定位skip_innodb選項位置，改為#skip_innodb后保存文件，重新啟動MySQL服務器，即可令數據庫支持innodb類型表。

提交事務

commit

因為事務具有孤立性，并未把結果寫入磁盤中

撤銷事務

rollback

如果執行一個回滾操作，則在start transction（初始化事務）命令后的所有SQL語句都將執行回滾操作。如果用戶開啟事務后，沒有提交事務，則默認為自動回滾狀態，既不保留用戶之前的任何操作

事務的存在周期

從start transction指令開始直到用戶輸入commit結束

事務不支持嵌套，在多個事務中，它自動默認會提交前一個事務，在MySQL中很多命令都隱藏執行commit命令

編輯

自動提交

set autocommit=0;

關閉自動提交參數

編輯

事務的孤立級

1.serializable(序列號)

2.repeatable read(可重讀)

3.read committed(提交后讀)

4.read uncommitted(未提交讀)

修改事務的孤立級，必須首先獲取super優先權

編輯

獲取當前事務孤立級變量的值

select @@tx_isolation;

偽事務

對MyISAM表進行鎖定操作，以次過程代替事務型表InnoDB，即應用表鎖來實現偽事務

用表鎖定代替事務

在MySQL的MyISMAM類型數據表中，并不支持commit和rollback（回滾）命令，當用戶對數據庫執行插入更新刪除等操作時，這些變化的數據都被立即保存在磁盤中。

（1）為指定數據表添加鎖定

lock tables table_name lock_type1,lock_type2....

其中，table_name為被鎖定的表名，lock_type為鎖定類型，該類型包括以讀方式（read）鎖定表；以寫方式（write）鎖定表

（2）解鎖操作，釋放該表的鎖定狀態

unlock tables；使用之后，所有處于鎖定狀態都會被釋放

1.以讀方式鎖定數據表

lock table tb_admin read;

編輯

當以讀鎖定數據表時插入記錄時會出錯

編輯

解鎖

編輯

lock_type參數中，除了read鎖定，其它的參數可以執行insert操作，或者select，但結果卻不徑相同

2.以寫方式鎖定數據表

lock_tables tb_admin write;

該表是寫鎖定，并不能限制當前鎖定用戶的查詢操作，而再打開一個新用戶會話，重新mysql連接，執行上訴過程查詢，并沒有結果顯示，而釋放鎖定后，其它訪問數據庫的用戶都可以查看數據表的內容。

事件

查看事件是否開啟

show variables like 'event_scheduler'; select @@event_scheduler; show processlist;

編輯

開啟事件

1.通過設置全局參數修改（重啟會恢復原來狀態）

set global event_scheduler=on;

2.更改配置文件（始終設置）

在MySQL的配置文件my.ini(window系統)/my.cng（linux系統）中，找到【mysql】，然后在下面添加event_scheduler=on

保存并重新啟動mysql服務器

編輯

創建事件

在 MySQL 中，可以通過?CREATE EVENT?語句來創建事件，其語法格式如下：

CREATE EVENT [IF NOT EXISTS] event_name ??? ON SCHEDULE schedule ??? [ON COMPLETION [NOT] PRESERVE] ??? [ENABLE | DISABLE | DISABLE ON SLAVE] ??? [COMMENT 'comment'] ??? DO event_body;

編輯

在 ON SCHEDULE 子句中，參數 schedule 的值為一個 AT 子句，用于指定事件在某個時刻發生，其語法格式如下：

AT timestamp [+ INTERVAL interval]... ? ? | EVERY interval ? ? [STARTS timestamp [+ INTERVAL interval] ...] ? ? [ENDS timestamp[+ INTERVAL interval]...]

參數說明如下：

timestamp：一般用于只執行一次，表示一個具體的時間點，后面加上一個時間間隔，表示在這個時間間隔后事件發生。

EVERY 子句：用于事件在指定時間區間內每隔多長時間發生一次，其中 STARTS 子句用于指定開始時間；ENDS 子句用于指定結束時間。

interval：一般用于周期性執行，表示一個從現在開始的時間，其值由一個數值和單位構成。例如，使用“4 WEEK”表示 4 周，使用“'1:10'HOUR_MINUTE”表示 1 小時 10 分鐘。間隔的長短用 DATE_ADD() 函數支配。

interval 參數可以是以下值：

YEAR?| QUARTER |?MONTH?|?DAY?|?HOUR?|?MINUTE?|

WEEK |?SECOND?| YEAR_MONTH | DAY_HOUR | DAY_MINUTE |

DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND一般情況下，不建議使用不標準（以上未加粗關鍵字）的時間單位。

例 1

在 test 數據庫中創建一個名稱為 e_test 的事件，用于每隔 5 秒向表 tb_eventtest 中插入一條數據。

創建 tb_eventtest 表，SQL 語句和運行結果如下：

mysql> CREATE TABLE tb_eventtest( -> id INT(11) PRIMARY KEY AUTO_INCREMENT, -> user VARCHAR(20), -> createtime DATETIME); Query OK, 0 rows affected (0.07 sec)

創建 e_test 事件，SQL 語句和運行結果如下：

mysql> CREATE EVENT IF NOT EXISTS e_test ON SCHEDULE EVERY 5 SECOND -> ON COMPLETION PRESERVE -> DO INSERT INTO tb_eventtest(user,createtime)VALUES('MySQL',NOW()); Query OK, 0 rows affected (0.04 sec)

創建事件后，查詢 tb_eventtest 中的數據，SQL 語句和運行結果如下：

mysql> SELECT * FROM tb_eventtest; +----+-------+---------------------+ | id | user | createtime | +----+-------+---------------------+ | 1 | MySQL | 2020-05-21 10:41:39 | | 2 | MySQL | 2020-05-21 10:41:44 | | 3 | MySQL | 2020-05-21 10:41:49 | | 4 | MySQL | 2020-05-21 10:41:54 | +----+-------+---------------------+ 4 rows in set (0.01 sec)

從結果可以看出，系統每隔 5 秒插入一條數據，這說明事件創建執行成功了。

修改事件

在 MySQL 中，事件創建之后，可以使用?ALTER EVENT?語句修改其定義和相關屬性。

修改事件的語法格式如下：

ALTER EVENT event_name

ON SCHEDULE schedule

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE | DISABLE ON SLAVE]

[COMMENT 'comment']

DO event_body;ALTER EVENT 語句中的子句與創建事務一節中講解的基本相同，這里不再贅述。另外，ALTER EVENT 語句還有一個用法就是讓一個事件關閉或再次讓其活動。

例 1

修改 e_test 事件，讓其每隔 30 秒向表 tb_eventtest 中插入一條數據，SQL 語句和運行結果如下所示：

mysql> ALTER EVENT e_test ON SCHEDULE EVERY 30 SECOND -> ON COMPLETION PRESERVE -> DO INSERT INTO tb_eventtest(user,createtime) VALUES('MySQL',NOW()); Query OK, 0 rows affected (0.04 sec) mysql> TRUNCATE TABLE tb_eventtest; Query OK, 0 rows affected (0.04 sec) mysql> SELECT * FROM tb_eventtest; +----+-------+---------------------+ | id | user | createtime | +----+-------+---------------------+ | 1 | MySQL | 2020-05-21 13:23:49 | | 2 | MySQL | 2020-05-21 13:24:19 | +----+-------+---------------------+ 2 rows in set (0.00 sec)

由結果可以看出，修改事件后，表 tb_eventtest 中的數據由原來的每 5 秒插入一條，變為每 30 秒插入一條。

使用 ALTER EVENT 語句還可以臨時關閉一個已經創建的事件。

例 2

臨時關閉事件 e_test 的具體代碼如下所示：

mysql> ALTER EVENT e_test DISABLE;

Query OK, 0 rows affected (0.00 sec)查詢 tb_eventtest 表中的數據，SQL 語句如下：

SELECT * FROM tb_eventtest;

為了確定事件已關閉，可以查詢兩次（每次間隔 1 分鐘）tb_eventtest 表的數據，SQL 語句和運行結果如下所示：

mysql> TRUNCATE TABLE tb_eventtest;

Query OK, 0 rows affected (0.05 sec)

mysql> SELECT * FROM tb_eventtest;

Empty set (0.00 sec)

mysql> SELECT * FROM tb_eventtest;

Empty set (0.00 sec)由結果可以看出，臨時關閉事件后，系統就不再繼續向表 tb_eventtest 中插入數據了。

刪除事件

在 MySQL 中，可以使用 DROP EVENT 語句刪除已經創建的事件。語法格式如下：

DROP EVENT [IF EXISTS] event_name;

例 3

刪除事件 e_test，SQL 語句和運行結果如下：

mysql> DROP EVENT IF EXISTS e_test; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM information_schema.events \G Empty set (0.00 sec)

MySQL性能優化

查詢mysql性能

show status like 'value';

connection:連接mysql服務器的次數

uptime:mysql服務器上線時間

slow_queries:慢查詢的次數

com_select:查詢操作的次數

com_insert:插入操作的次數

com_delete:刪除操作的次數

mysql中存在查詢InnoDB類型的表的一些參數。例如，Innodb_row_read參數表示select語句查詢的記錄數；Innodb_row_insert參數表示insert語句插入的記錄數等。

編輯

優化查詢

explain select 語句;

explain select * from jsj; 或describe select * from sfzh;可縮寫desc

編輯

各字段所代表的意義：

id 列：指出在整個查詢中select的位置。

table 列：存放所查詢的表名。

type 列：連接類型，該列中存儲很多值，范圍從const到all。

possible_keys 列：指出為了提高查找速度，在mysql中可以使用的索引。

key 列：指出實際使用的鍵。

rows 列：該表的總行數。

extra 列：包含一些其它信息，設計mysql如何處理查詢。

索引對查詢速度的影響

mysql> select * from tb_admin; +----+-----------+-----+ | id | name | pwd | +----+-----------+-----+ | 1 | mr | 111 | | 2 | 肖承聰 | 222 | | 3 | 張三 | 333 | | 4 | 易超超 | 666 | | 7 | 王無 | 777 | | 8 | xiao | 888 | | 5 | zhege | 555 | | 6 | feiwu | 666 | | 9 | huigun | 999 | | 10 | commit | 444 | | 11 | huigun | 121 | | 12 | tijjiao | 123 | | 13 | fule | 134 | +----+-----------+-----+ 13 rows in set (0.00 sec) mysql> explain select * from tb_admin where pwd=666; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tb_admin | NULL | ALL | NULL | NULL | NULL | NULL | 13 | 10.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ------------------------創建索引前rows是13，意味著數據庫存在的13條數據讀別查詢了一遍 mysql> create index index_name on tb_admin(pwd);------創建索引 Query OK, 13 rows affected (0.09 sec) Records: 13 Duplicates: 0 Warnings: 0 mysql> explain select * from tb_admin where pwd='666'; +----+-------------+----------+------------+------+---------------+------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | tb_admin | NULL | ref | index_name | index_name | 137 | const | 2 | 100.00 | NULL | +----+-------------+----------+------------+------+---------------+------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.05 sec) --------------------------創建索引后查詢從7行減少到2行，減少了服務器的開銷

使用索引查詢

1.用關鍵字like優化索引查詢

explain select * from tb_admin where pwd='6%'; ------- %在6前就起不到優化作用

2.查詢語句中使用多列索引

create index index_name on tb_admin(name,pwd); ----應用set時，索引不能正常使用，意味著索引并未在mysql優化中起到作用必須使用第一字段name，索引才可以正常使用

3.查詢語句中使用關鍵字or

explain select * from tb_admin where name='張三 or pwd='666';

優化數據庫結構

將字段很多的表分解成多個表

mysql> desc tb_admin1; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | pwd | varchar(30) | NO | | NULL | | +-------+-------------+------+-----+---------+----------------+

聯表查詢：

select * from tb_admin,tb_admin1 where tb_admin.id=tb_admin1.id;

編輯

增加中間表

優化插入記錄的速度

1.禁用索引

alter table 表名 disable keys； 重新開啟 alter table 表名 enable keys；

新創建的表可等記錄導入以后再創建索引可提高導入數據的速度

2.禁用唯一性檢查

set unique_checks=0； 重新開啟 set unique_checks=1;

3,優化insert語句

當插入大量數據時，建議使用一個select語句插入多條記錄的方式

1.分析表

analyze table 表名1[表名2...];

在分析過程中，數據庫系統會對表加一個只讀鎖。

編輯

上面結果顯示了4條信息

table：表示表的名稱。

op：表示執行的操作。analyze表示分析操作，check表示進行檢查查找，optimize表示進行優化操作

msg_type：表示信息類型，其顯示的值通常為狀態，警告，錯誤或信息。

msg_text：顯示信息。

2.檢查表

check table 表名1[表名2...];

3.優化表

optimize table 表名1[表名2...];

查詢高速緩存

檢查高速緩存是否開啟

show variables like '%query_cache%';

參數說明：

have_query_cahce:表名服務器在默認安裝條件下，是否已經配置查詢高速緩存。

query_cache_size:高速緩存分配空間，如果該空間為86，則分配給高速緩存空間的大小為86MB。如果為0，則表名高速緩存關閉

query_cache_type:判斷高速緩存開啟狀態，范圍為0-2，如果為0或0ff時，表名高速緩存已經關閉；當該值為1或on時表名高速緩存已經打開

使用高速緩存

select sql_cache * from 表名；

* 一旦表由變化，使用這個表的高速緩存就會失效

Linux MySQL SQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。