linux mysql基本操作指令

rpm 安裝的mysql

1.linux下啟動mysql的指令：

service mysqld start

2.linux下重新開機mysql的指令：

service mysqld restart

3.linux下關閉mysql的指令：

server mysqld stop

4.進入mysql

mysql //在沒有設定密碼和賬戶的情況下

5.修改mysql密碼：

mysqladmin -u root -p password "abc123" //給root賬号設定密碼為abc123 ，提示輸入的是初始密碼時，直接回車

mysql -uroot -p 設定有密碼的情況下，要加-p

6.增加新使用者。（注意：mysql環境中的指令後面都帶一個分号作為指令結束符）

grant select on 資料庫. to 使用者名@登入主機 identified by "密碼"

如增加一個使用者test密碼為123，讓他可以在任何主機上登入， 并對所有資料庫有查詢、插入、修改、删除的權限。首先用以root使用者連入mysql，然後鍵入以下指令：

grant select,insert,update,delete on .* to " Identified by "123"; //給使用者設定權限

二、有關mysql資料庫方面的操作

必須首先登入到mysql中，有關操作都是在mysql的提示符下進行，而且每個指令以分号結束

1、顯示資料庫清單。

show databases;

2、顯示庫中的資料表：

use mysql； //打開表

show tables; //檢視表

3、顯示資料表的結構：

describe 表名;

4、建庫：

create database 庫名;

GBK: create database test2 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

UTF8: CREATE DATABASE

test2

DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

5、建表：

use 庫名；//進入庫

create table 表名(字段設定清單)； //建立資料庫表

6、删庫和删表:

drop database 庫名;

drop table 表名；

7、将表中記錄清空：

delete from 表名;

truncate table 表名;

8、顯示表中的記錄：

select * from 表名;

9、編碼的修改

如果要改變整個mysql的編碼格式：

啟動mysql的時候，mysqld_safe指令行加入

--default-character-set=gbk

如果要改變某個庫的編碼格式：在mysql提示符後輸入指令

alter database db_name default character set gbk;

10.重命名表

alter table t1 rename t2;

11.檢視sql語句的效率

explain < table_name >

例如：explain select * from t3 where id=3952602;

12.用文本方式将資料裝入資料庫表中(例如D:/mysql.txt)

mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE MYTABLE;

三、資料的導入導出

1、文本資料轉到資料庫中

文本資料應符合的格式：字段資料之間用tab鍵隔開，null值用來代替。例：

1 name duty 2006-11-23

資料傳入指令 load data local infile "檔案名" into table 表名;

2、導出資料庫和表

mysqldump --opt news > news.sql（将資料庫news中的所有表備份到news.sql檔案，news.sql是一個文本檔案，檔案名任取。）

mysqldump --opt news author article > author.article.sql（将資料庫news中的author表和article表備份到author.article.sql檔案， author.article.sql是一個文本檔案，檔案名任取。）

mysqldump --databases db1 db2 > news.sql（将資料庫dbl和db2備份到news.sql檔案，news.sql是一個文本檔案，檔案名任取。）

mysqldump -h host -u user -p pass --databases dbname > file.dump

就是把host上的以名字user，密碼pass的資料庫dbname導入到檔案file.dump中

mysqldump --all-databases > all-databases.sql（将所有資料庫備份到all-databases.sql檔案，all-databases.sql是一個文本檔案，檔案名任取。）

3、導入資料

mysql < all-databases.sql（導入資料庫）

mysql>source news.sql;（在mysql指令下執行，可導入表）

一、連接配接MySQL

格式： mysql -h主機位址 -u使用者名 －p使用者密碼

1、例1：連接配接到本機上的MYSQL。

首先在打開DOS視窗，然後進入目錄 mysqlbin，再鍵入指令mysql -uroot -p，回車後提示你輸密碼，如果剛安裝好MYSQL，超級使用者root是沒有密碼的，故直接回車即可進入到MYSQL中了，MYSQL的提示符是： mysql>。

2、例2：連接配接到遠端主機上的MYSQL。假設遠端主機的IP為：110.110.110.110，使用者名為root,密碼為abcd123。則鍵入以下指令：

mysql -h110.110.110.110 -uroot -pabcd123

（注:u與root可以不用加空格，其它也一樣）

3、退出MYSQL指令： exit （回車）。

二、修改密碼

格式：mysqladmin -u使用者名 -p舊密碼 password 新密碼

1、例1：給root加個密碼ab12。首先在DOS下進入目錄mysqlbin，然後鍵入以下指令：

mysqladmin -uroot -password ab12

注：因為開始時root沒有密碼，是以-p舊密碼一項就可以省略了。

2、例2：再将root的密碼改為djg345。

mysqladmin -uroot -pab12 password djg345

三、增加新使用者。（注意：和上面不同，下面的因為是MySQL環境中的指令，是以後面都帶一個分号作為指令結束符）

格式：grant select on 資料庫.* to 使用者名@登入主機 identified by \"密碼\"

例1、增加一個使用者test1密碼為abc，讓他可以在任何主機上登入，并對所有資料庫有查詢、插入、修改、删除的權限。首先用以root使用者連入MySQL，然後鍵入以下指令：

grant select,insert,update,

delete on . to test1@\"%\" Identified by \"abc\";

但例1增加的使用者是十分危險的，你想如某個人知道test1的密碼，那麼他就可以在internet上的任何一台電腦上登入你的MySQL資料庫并對你的資料可以為所欲為了，解決辦法見例2。

例2、增加一個使用者test2密碼為abc,讓他隻可以在localhost上登入，并可以對資料庫mydb進行查詢、插入、修改、删除的操作 （localhost指本地主機，即MySQL資料庫所在的那台主機），這樣使用者即使用知道test2的密碼，他也無法從internet上直接通路資料 庫，隻能通過MySQL主機上的web頁來通路。

delete on mydb.* to test2@localhost identified by \"abc\";

如果你不想test2有密碼，可以再打一個指令将密碼消掉。

grant select,insert,update,delete on mydb

.* to test2@localhost identified by \"\";

啟動：net start mySql;

進入：mysql -u root -p/mysql -h localhost -u root -p databaseName;

列出資料庫：show databases;

選擇資料庫：use databaseName;

列出表格：show tables；

顯示表格列的屬性：show columns from tableName；

建立資料庫：source fileName.txt;

比對字元：可以用通配符_代表任何一個字元，％代表任何字元串;

增加一個字段：alter table tabelName add column fieldName dateType;

增加多個字段：alter table tabelName add column fieldName1 dateType,add columns fieldName2 dateType;

多行指令輸入:注意不能将單詞斷開;當插入或更改資料時，不能将字段的字元串展開到多行裡，否則硬回車将被儲存到資料中;

增加一個管理者帳戶：grant all on . to user@localhost identified by "password";

每條語句輸入完畢後要在末尾填加分号';'，或者填加'\g'也可以；

查詢時間：select now();

查詢目前使用者：select user();

查詢資料庫版本：select version();

查詢目前使用的資料庫：select database();

1、删除student_course資料庫中的students資料表：

rm -f student_course/students.*

2、備份資料庫：(将資料庫test備份)

mysqldump -u root -p test>c:\test.txt

備份表格：(備份test資料庫下的mytable表格)

mysqldump -u root -p test mytable>c:\test.txt

将備份資料導入到資料庫：(導回test資料庫)

mysql -u root -p test

3、建立臨時表：(建立臨時表zengchao)

create temporary table zengchao(name varchar(10));

4、建立表是先判斷表是否存在

create table if not exists students(……);

5、從已經有的表中複制表的結構

create table table2 select * from table1 where 1<>1;

6、複制表

create table table2 select * from table1;

7、對表重新命名

alter table table1 rename as table2;

8、修改列的類型

alter table table1 modify id int unsigned;//修改列id的類型為int unsigned

alter table table1 change id sid int unsigned;//修改列id的名字為sid，而且把屬性修改為int unsigned

9、建立索引

alter table table1 add index ind_id (id);

create index ind_id on table1 (id);

create unique index ind_id on table1 (id);//建立唯一性索引

10、删除索引

drop index idx_id on table1;

alter table table1 drop index ind_id;

11、聯合字元或者多個列(将列id與":"和列name和"="連接配接)

select concat(id,':',name,'=') from students;

12、limit(選出10到20條)<第一個記錄集的編号是0>

select * from students order by id limit 9,10;

13、MySQL不支援的功能

事務，視圖，外鍵和引用完整性，存儲過程和觸發器

14、MySQL會使用索引的操作符号

<,<=,>=,>,=,between,in,不帶%或者_開頭的like

15、使用索引的缺點

1)減慢增删改資料的速度；

2）占用磁盤空間；

3）增加查詢優化器的負擔；

當查詢優化器生成執行計劃時，會考慮索引，太多的索引會給查詢優化器增加工作量，導緻無法選擇最優的查詢方案；

16、分析索引效率

方法：在一般的SQL語句前加上explain；

分析結果的含義：

1）table：表名；

2）type：連接配接的類型，(ALL/Range/Ref)。其中ref是最理想的；

3）possible_keys：查詢可以利用的索引名；

4）key：實際使用的索引；

5）key_len：索引中被使用部分的長度（位元組）；

6）ref：顯示列名字或者"const"（不明白什麼意思）；

7）rows：顯示MySQL認為在找到正确結果之前必須掃描的行數；

8）extra：MySQL的建議；

17、使用較短的定長列

1）盡可能使用較短的資料類型；

2）盡可能使用定長資料類型；

a）用char代替varchar，固定長度的資料處理比變長的快些；

b）對于頻繁修改的表，磁盤容易形成碎片，進而影響資料庫的整體性能；

c）萬一出現資料表崩潰，使用固定長度資料行的表更容易重新構造。使用固定長度的資料行，每個記錄的開始位置都是固定記錄長度的倍數，可以很容易被檢測到，但是使用可變長度的資料行就不一定了；

d）對于MyISAM類型的資料表，雖然轉換成固定長度的資料列可以提高性能，但是占據的空間也大；

18、使用not null和enum

盡量将列定義為not null，這樣可使資料的出來更快，所需的空間更少，而且在查詢時，MySQL不需要檢查是否存在特例，即null值，進而優化查詢；

如果一列隻含有有限數目的特定值，如性别，是否有效或者入學年份等，在這種情況下應該考慮将其轉換為enum列的值，MySQL處理的更快，因為所有的enum值在系統内都是以辨別數值來表示的；

19、使用optimize table

對于經常修改的表，容易産生碎片，使在查詢資料庫時必須讀取更多的磁盤塊，降低查詢性能。具有可變長的表都存在磁盤碎片問題，這個問題對blob資料類型更為突出，因為其尺寸變化非常大。可以通過使用optimize table來整理碎片，保證資料庫性能不下降，優化那些受碎片影響的資料表。 optimize table可以用于MyISAM和BDB類型的資料表。實際上任何碎片整理方法都是用mysqldump來轉存資料表，然後使用轉存後的檔案并重建立資料表；

20、使用procedure analyse()

可以使用procedure analyse()顯示最佳類型的建議，使用很簡單，在select語句後面加上procedure analyse()就可以了；例如：

select from students procedure analyse();

select from students procedure analyse(16,256);

第二條語句要求procedure analyse()不要建議含有多于16個值，或者含有多于256位元組的enum類型，如果沒有限制，輸出可能會很長；

21、使用查詢緩存

1）查詢緩存的工作方式：

第一次執行某條select語句時，伺服器記住該查詢的文本内容和查詢結果，存儲在緩存中，下次碰到這個語句時，直接從緩存中傳回結果；當更新資料表後，該資料表的任何緩存查詢都變成無效的，并且會被丢棄。

2）配置緩存參數：

變量：query_cache _type，查詢緩存的操作模式。有3中模式，0：不緩存；1：緩存查詢，除非與 select sql_no_cache開頭；2：根據需要隻緩存那些以select sql_cache開頭的查詢； query_cache_size：設定查詢緩存的最大結果集的大小，比這個值大的不會被緩存。

22、調整硬體

1）在機器上裝更多的記憶體；

2）增加更快的硬碟以減少I/O等待時間；

尋道時間是決定性能的主要因素，逐字地移動磁頭是最慢的，一旦磁頭定位，從磁道讀則很快；

3）在不同的實體硬碟裝置上重新配置設定磁盤活動；

如果可能，應将最繁忙的資料庫存放在不同的實體裝置上，這跟使用同一實體裝置的不同分區是不同的，因為它們将争用相同的實體資源（磁頭）。

删除MySQL資料庫

1、建立資料庫

mysql> create database drop_database;

Query OK, 1 row affected (0.00 sec)

2、删除一個已經确定存在的資料庫

mysql> drop database drop_database;

Query OK, 0 rows affected (0.00 sec)

3、删除一個不确定存在的資料庫

複制代碼

ERROR 1008 (HY000): Can't drop database 'drop_database'; database doesn't exist

//發生錯誤，不能删除'drop_database'資料庫，該資料庫不存在。

mysql> drop database if exists drop_database;

Query OK, 0 rows affected, 1 warning (0.00 sec)//産生一個警告說明此資料庫不存在

mysql> drop database if exists drop_database;//if exists 判斷資料庫是否存在，不存在也不産生錯誤

mysql增删改查

針對資料記錄增删改查

向資料表中插入資料：

添加資料記錄

INSERT INTO 表名 [(字段名清單)] VALUES(值清單);

例子：

insert into 'a'("1","2","3") VALUES('12','xiao','123');

插入多行資料:

INSERT INTO 表名(字段名清單) VALUES(值清單),(值清單)…；

insert into aa VALUES('zhang','1','20'),('li','2','30');

修改資料記錄

更新資料記錄:

UPDATE 表名 SET 列名=更新值 [WHERE 更新條件];

注：

SET後面可以緊随多個“列名=更新值”已修改多個資料列的值，不限一個，不同列之間使用逗号分隔

删除資料記錄：

DELETE FROM 表名 [WHERE <删除條件>];

沒有where條件，則删除資料庫中的所有資料記錄。

資料查詢

SELECT * FROM 表名 [WHERE 條件語句];

沒有where 條件語句，則預設檢視表中的所有内容。

針對資料表增删改查

2、MySQL資料表基本操作

建立資料庫表：

CREATE TABLE 表名(

字段1 資料類型 [字段屬性|限制] [索引] [注釋],

字段2 資料類型 [字段屬性|限制] [索引] [注釋]

……)

CREATE TABLE "student"("student" INT(4),"name" CHAR(10));

檢視資料表清單：

SHOW tables；

檢視表中的定義字段：

DESCRIBE 表名 或者 DESC 表名

删除表：

DROP TABLE 表名;

修改表名：

ALTER TABLE 舊表名 RENAME 新表名;

向已存在的字段添加新的字段：

ALTER TABLE 表名 ADD 字段名 資料類型 屬性;

修改表中已存在的字段名和資料類型：

ALTER TABLE 表名 CHANGE 原字段名 新字段名 資料類型 屬性;

資料類型指的是修改後的資料類型，如果不需要修改，則和原資料類型保持一緻，但‘資料類型’不能為空。

将表中的某個字段删除：

ALTER TABLE 表名 DROP 字段名;

針對庫的增删查

1、MySQL資料庫進行操作

建立資料庫：

CREATE DATEBASE 資料庫名;