50個mysql數據庫的常用操作

1 登錄資料庫

格式: mysql -h主機地址 -u用戶名 -p用戶密碼–P端口 –D資料庫–e “SQL 內容”

>mysql -uroot -p 資料庫名稱

2 修改密碼

格式:mysqladmin -u用戶名 -p舊密碼 password 新密碼

Mysqladmin -uroot -password ab12

註:因為開始時root沒有密碼,所以-p舊密碼一項就可以省略瞭。

例2:再將root的密碼改為djg345。

mysqladmin-uroot -pab12 password djg345

shell>mysql -u root -p

mysql> update user setpassword=password(”xueok654123″) where user=’root’;

3 添加用戶

格式:grant select on 資料庫.* to 用戶名@登錄主機 identified by \"密碼\"

例1、增加一個用戶test1密碼為abc,讓他可以在任何主機上登錄,並對所有資料庫有查詢、插入、修改、刪除的權限。首先用以root用戶連入MySQL,然後鍵入以下命令:

grant select,insert,update,deleteon *.* to test2@localhost identified by\"abc\";

如果你不想test2有密碼,可以再打一個命令將密碼消掉。

grantselect,insert,update,delete on mydb.* to test2@localhostidentified by \"\";

4 創建資料庫

create databasename; 創建資料庫

usedatabasename; 選擇資料庫

5 備份/恢復資料庫

shell>mysqldump -h host -u root -p dbname >dbname_backup.sql

shell>mysqladmin -h myhost -u root -p create dbname

shell>mysqldump -h host -u root -p dbname < dbname_backup.sql

如果隻想卸出建表指令,則命令如下:

shell>mysqladmin -u root -p -d databasename > a.sql

mysqldump -uroot -p test mytable>./test.txt

將備份數據導入到資料庫:(導回test資料庫)

備份資料庫staffer

mysqldump -uroot-proot staffer > staffer.sql

得到的staffer.sql是一個sql腳本,不包括建庫的語句,所以你需要手工

創建資料庫才可以導入

恢復資料庫staffer,需要創建一個空庫staffer

mysql -uroot-proot staffer<staffer.sql

</staffer.sql

如果不希望後來手工創建staffer,可以

mysqldump -uroot-proot –databases staffer> staffer.sql

mysql -uroot-proot > staffer.sql

但這樣的話系統種就不能存在staffer庫,且無法導入其他名字的資料庫,

當然你可以手工修改staffer.sql文件

如果隻想卸出插入數據的sql命令,而不需要建表命令,則命令如下:

shell>mysqladmin -u root -p -t databasename > a.sql

那麼如果我隻想要數據,而不想要什麼sql命令時,應該如何操作呢?

mysqldump -T./phptest driver

其中,隻有指定瞭-T參數才可以卸出純文本文件,表示卸出數據的目錄,./表示當前目錄,即與mysqldump同一目錄。如果不指定driver 表,則將卸出整個資料庫的數據。每個表會生成兩個文件,一個為.sql文件,包含建表執行。另一個為.txt文件,隻包含數據,且沒有sql指令。

可將查詢存儲在一個文件中並告訴mysql從文件中讀取查詢而不是等待鍵盤輸入。可利用外殼程式鍵入重定向實用程式來完成這項工作。例如,如果在文件my_file.sql 中存放有查

6 導入.sql文件命令(例如D:/mysql.sql)

mysql>use database;

mysql>source /home/mysql.sql;

UPDATE以新的值更新現存表中行的列。SET 子句指出要修改哪個列和他們應該給定的值。WHERE

子句如果被給出,指定哪個記錄行應該被更新。否則,所有的記錄行被更新。如果 ORDER BY 子句被指定,記錄行將被以指定的次序更新。

如果你指定關鍵詞LOW_PRIORITY,UPDATE 的執行將被延遲,直到沒有其它的客戶端正在讀取表。

如果你指定關鍵詞IGNORE,該更新語句將不會異常中止,即使在更新過程中出現重復鍵錯誤。導致沖突的記錄行將不會被更新。

如果在一個表達式中從tbl_name 中訪問一個列,UPDATE 使用列的當前值。舉例來說,下面的語句設置 age 列值為它的當前值加 1 :

mysql> UPDATE persondata SETage=age+1;

UPDATE賦值是從左到右計算的。舉例來說,下列語句將 age 列設置為它的兩倍,然後再加 1 :

mysql> UPDATE persondata SETage=age*2, age=age+1;

如果你設置列為其當前的值,MySQL 註意到這點,並不更新它。

UPDATE返回實際被改變的記錄行數目。在 MySQL 3.22 或更新的版本中,C API 函數 mysql_info()

返回被匹配並更新的記錄行數目,以及在 UPDATE 期間發生的警告的數目。

在 MySQL3.23 中,你可以使用 LIMIT # 來確保隻有給定的記錄行數目被更改。

如果一個 ORDER BY 子句被使用(從 MySQL4.0.0 開始支持),記錄行將以指定的次序被更新。這實際上隻有連同 LIMIT

一起才有用。

從 MySQL 4.0.4 開始,你也可以執行一個包含多個表的UPDATE 的操作:

UPDATE items,month SETitems.price=month.price

WHERE items.id=month.id;

註意:多表 UPDATE 不可以使用 ORDER BY 或LIMIT。

關鍵字: mysql

啟動:net start mySql;

進入:mysql -u root -p/mysql -hlocalhost -u root -p databaseName;

列出資料庫:show databases;

選擇資料庫:use databaseName;

列出表格:show tables;

顯示表格列的屬性:show columns fromtableName;

建立資料庫:source fileName.txt;

匹配字符:可以用通配符_代表任何一個字符,%代表任何字符串;

7 用文本方式將數據裝入資料庫表中(例如D:/mysql.txt)

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

8 刪除資料庫

drop database name 直接刪除資料庫

mysqladmin drop database name 刪除資料庫前,有提示

9 資料庫授權

GRANT ALL PRIVILEGES ON bdc.* TObdc@'%' IDENTIFIEDBY 'bdc123' WITH GRANT OPTION;

flush privileges

授權訪問bdc 的所有表 用戶為bdc 任何主機 ,密碼為bdc@123

創建一個用戶custom在特定客戶端it363.com登錄,可訪問特定資料庫fangchandb

mysql >grant select, insert,update, delete, create,drop on fangchandb.* to custom@it363.com identified by ‘passwd’

10 刪除授權:

mysql> revoke all privilegeson *.* from root@”%”;

mysql> delete from user whereuser=”root” and host=”%”;

mysql> flush privileges;

11 顯示當前mysql版本和當前日期

selectversion(),current_date;

查詢時間:select now();

查詢當前用戶:select user();

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

查詢當前使用的資料庫:select database();

12 查詢所有數據表

>show tables;

13 查詢表的字段信息

>desc 表名稱;

14 創建表

create table s_position

(

id int not null auto_increment,

name varchar(20) not nulldefault '經理', #設定默認值

description varchar(100),

primary key PK_positon (id) #設定主鍵

);

create table department

(

id int not null auto_increment,

name varchar(20) not nulldefault '系統部', #設定默認值

description varchar(100),

primary key PK_department (id) #設定主鍵

);

create table depart_pos

(

department_id int not null,

position_id int not null,

primary key PK_depart_pos(department_id,position_id) #設定復和主鍵

);

create table staffer

(

id int not null auto_incrementprimary key, #設定主鍵

name varchar(20) not nulldefault '無名氏', #設定默認值

department_id int not null,

position_id int not null,

unique (department_id,position_id)#設定唯一值

);

15 修改結構

mysql>

#表position增加列test

alter tableposition add(test char(10));

#表position修改列test

alter tableposition modify test char(20) not null;

#表position修改列test默認值

alter tableposition alter test set default 'system';

#表position去掉test默認值

alter tableposition alter test drop default;

#表position去掉列test

alter tableposition drop column test;

#表depart_pos刪除主鍵

alter tabledepart_pos drop primary key;

#表depart_pos增加主鍵

alter tabledepart_pos add primary key PK_depart_pos (department_id,position_id);

16 操作數據

#插入表department

insert into department(name,description)values('系統部','系統部');

insert into department(name,description)values('公關部','公關部');

insert into department(name,description)values('客服部','客服部');

insert into department(name,description)values('財務部','財務部');

insert into department(name,description)values('測試部','測試部');

#插入表s_position

insert into s_position(name,description)values('總監','總監');

insert into s_position(name,description)values('經理','經理');

insert into s_position(name,description)values('普通員工','普通員工');

#插入表depart_pos

insert intodepart_pos(department_id,position_id)

select a.id department_id,b.id postion_id

from department a,s_position b;

#插入表staffer

insert intostaffer(name,department_id,position_id) values('陳達治',1,1);

insert into staffer(name,department_id,position_id)values('李文賓',1,2);

insert intostaffer(name,department_id,position_id) values('馬佳',1,3);

insert intostaffer(name,department_id,position_id) values('亢志強',5,1);

insert intostaffer(name,department_id,position_id) values('楊玉茹',4,1);

17 查詢及刪除操作

#顯示系統部的人員和職位

selecta.name,b.name department_name,c.name position_name

from staffera,department b,s_position c

wherea.department_id=b.id and a.position_id=c.id and b.name='系統部';

#顯示系統部的人數

select count(*)from staffer a,department b

wherea.department_id=b.id and b.name='系統部'

#顯示各部門的人數

select count(*)cou,b.name from staffer a,department b

wherea.department_id=b.idgroup by b.name;

#刪除客服部

delete fromdepartment where name='客服部';

#將財務部修改為財務一部

updatedepartment set name='財務一部' wherename='財務部';

18 刪除表

mysql>drop TABLE MYTABLE;

19 清空表

mysql>delete from MYTABLE;

20 添加表字段

alter tabletable1 add transactor varchar(10) not Null;

alter tabletable1 add id int unsigned not Null auto_increment primary key

21 修改某個表的字段類型及指定為空或非空

>alter table 表名稱 change 字段名稱 字段名稱 字段類型 [是否允許非空];

>alter table 表名稱 modify 字段名稱 字段類型 [是否允許非空];

>alter table 表名稱 modify 字段名稱 字段類型 [是否允許非空];

22 如果要刪除某一字段,可用命令:

ALTER TABLEmytable DROP 字段名;

23 添加唯一鍵

ALTER TABLE`test2` ADD UNIQUE ( `userid`)

24 修改主鍵

ALTER TABLE`test2` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `id` )

alter tableversion_template drop primary key ,add primary key version,role,type);

25 增加索引

ALTER TABLE`test2` ADD INDEX ( `id` )

alter tabletable1 add index ind_id (id);

create indexind_id on table1 (id);

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

26 刪除索引

drop indexidx_id on table1;

alter tabletable1 drop index ind_id;

27 重命名表:

alter table t1 rename t2;

28 增加一個字段:

alter tabletabelName add column fieldName dateType;

29 增加多個字段

alter tabletabelName add column fieldName1 dateType,add columns fieldName2 dateType;

多行命令輸入:註意不能將單詞斷開;當插入或更改數據時,不能將字段的字符串展開到多行裡,否則硬回車將被儲存到數據中;

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

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

30 創建臨時表:

create temporarytable zengchao(name varchar(10));

31 創建表是先判斷表是否存在

create table ifnot exists students(……);

32 從已經有的表中復制表的結構

create tabletable2 select * from table1 where 1<>1;

33 復制表

create tabletable2 select * from table1;

34 對表重新命名

alter table table1 rename astable2;

35 修改列的類型

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

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

36 聯合字符或者多個列(將列id與":"和列name和"="連接)

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

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

select * from students order byid limit 9,10;

38 MySQL會使用索引的操作符號

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

39 存儲過程

1)查看存儲過程

select name from mysql.proc where db=’資料庫名’;

或者

select routine_name frominformation_schema.routines where routine_schema='資料庫名';

或者

show procedure status where db='資料庫名';

40 MySQL存儲過程的修改

ALTER PROCEDURE

更改用CREATEPROCEDURE 建立的預先指定的存儲過程,其不會影響相關存儲過程或存儲功能。

41 MySQL存儲過程的刪除

刪除一個存儲過程比較簡單,和刪除表一樣:

DROP PROCEDURE

從MySQL的表格中刪除一個或多個存儲過程。

42 存儲過程基本函數

(1).字符串類

CHARSET(str) //返回字串字符集

CONCAT (string2 [,… ]) //連接字串

INSTR (string ,substring ) //返回substring首次在string中出現的位置,不存在返回0

LCASE (string2 ) //轉換成小寫

LEFT (string2 ,length ) //從string2中的左邊起取length個字符

LENGTH (string ) //string長度

LOAD_FILE (file_name ) //從文件讀取內容

LOCATE (substring , string[,start_position ] ) 同INSTR,但可指定開始位置

LPAD (string2 ,length ,pad ) //重復用pad加在string開頭,直到字串長度為length

LTRIM (string2 ) //去除前端空格

REPEAT (string2 ,count ) //重復count次

REPLACE (str ,search_str ,replace_str) //在str中用replace_str替換search_str

RPAD (string2 ,length ,pad) //在str後用pad補充,直到長度為length

RTRIM (string2 ) //去除後端空格

STRCMP (string1 ,string2 ) //逐字符比較兩字串大小,

SUBSTRING (str , position [,length ]) //從str的position開始,取length個字符,

註:mysql中處理字符串時,默認第一個字符下標為1,即參數position必須大於等於1

mysql> select substring('abcd',0,2);

+———————–+

| substring('abcd',0,2) |

+———————–+

| |

+———————–+

1 row in set (0.00 sec)

mysql> select substring('abcd',1,2);

+———————–+

| substring('abcd',1,2) |

+———————–+

| ab |

+———————–+

1 row in set (0.02 sec)

TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符

UCASE (string2 ) //轉換成大寫

RIGHT(string2,length) //取string2最後length個字符

SPACE(count) //生成count個空格

(2).數學類

ABS (number2 ) //絕對值

BIN (decimal_number ) //十進制轉二進制

CEILING (number2 ) //向上取整

CONV(number2,from_base,to_base) //進制轉換

FLOOR (number2 ) //向下取整

FORMAT (number,decimal_places ) //保留小數位數

HEX (DecimalNumber ) //轉十六進制

註:HEX()中可傳入字符串,則返回其ASC-11碼,如HEX('DEF')返回4142143

也可以傳入十進制整數,返回其十六進制編碼,如HEX(25)返回19

LEAST (number , number2 [,..]) //求最小值

MOD (numerator ,denominator ) //求餘

POWER (number ,power ) //求指數

RAND([seed]) //隨機數

ROUND (number [,decimals ]) //四舍五入,decimals為小數位數]

註:返回類型並非均為整數,如:

(1)默認變為整形值

mysql> select round(1.23);

+————-+

| round(1.23) |

+————-+

| 1 |

+————-+

1 row in set (0.00 sec)

mysql> select round(1.56);

+————-+

| round(1.56) |

+————-+

| 2 |

+————-+

1 row in set (0.00 sec)

(2)可以設定小數位數,返回浮點型數據

mysql> select round(1.567,2);

+—————-+

| round(1.567,2) |

+—————-+

| 1.57 |

+—————-+

1 row in set (0.00 sec)

SIGN (number2 ) //

(3).日期時間類

ADDTIME (date2 ,time_interval )//將time_interval加到date2

CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //轉換時區

CURRENT_DATE ( ) //當前日期

CURRENT_TIME ( ) //當前時間

CURRENT_TIMESTAMP ( ) //當前時間戳

DATE (datetime ) //返回datetime的日期部分

DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或時間

DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式顯示datetime

DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上減去一個時間

DATEDIFF (date1 ,date2 ) //兩個日期差

DAY (date ) //返回日期的天

DAYNAME (date ) //英文星期

DAYOFWEEK (date ) //星期(1-7) ,1為星期天

DAYOFYEAR (date ) //一年中的第幾天

EXTRACT (interval_name FROM date ) //從date中提取日期的指定部分

MAKEDATE (year ,day ) //給出年及年中的第幾天,生成日期串

MAKETIME (hour ,minute ,second ) //生成時間串

MONTHNAME (date ) //英文月份名

NOW ( ) //當前時間

SEC_TO_TIME (seconds ) //秒數轉成時間

STR_TO_DATE (string ,format ) //字串轉成時間,以format格式顯示

TIMEDIFF (datetime1 ,datetime2 ) //兩個時間差

TIME_TO_SEC (time ) //時間轉秒數]

WEEK (date_time [,start_of_week ]) //第幾周

YEAR (datetime ) //年份

DAYOFMONTH(datetime) //月的第幾天

HOUR(datetime) //小時

LAST_DAY(date) //date的月的最後日期

MICROSECOND(datetime) //微秒

MONTH(datetime) //月

MINUTE(datetime) //分返回符號,正負或0

SQRT(number2) //開平方

43 使用索引的缺點

1) 增刪改數據的速度;

2)占用磁盤空間;

3)增加查詢優化器的負擔;

當查詢優化器生成執行計劃時,會考慮索引,太多的索引會給查詢優化器增加

44 分析索引效率

方法:在一般的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的建議;

45 使用較短的定長列

1)盡可能使用較短的數據類型;

2)盡可能使用定長數據類型;

a)用char代替varchar,固定長度的數據處理比變長的快些;

b)對於頻繁修改的表,磁盤容易形成碎片,從而影響資料庫的整體性能;

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

d)對於MyISAM類型的數據表,雖然轉換成固定長度的數據列可以提高性能,但是占據的空間也大;

46 使用not null和enum

盡量將列設定為notnull,這樣可使數據的出來更快,所需的空間更少,而且在查詢時,MySQL不需要檢查是否存在特例,即null值,從而優化查詢;

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

47 使用optimize table

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

48 使用procedure analyse()

可以使用procedure analyse()顯示最佳類型的建議,使用很簡單,在select語句後面加上procedureanalyse()就可以瞭;例如:

select * from students procedureanalyse();

select * from students procedureanalyse(16,256);

第二條語句要求procedure analyse()不要建議含有多於16個值,或者含有多於256字節的enum類型,如果沒有限制,輸出可能會很長;

49 使用查詢緩存

1)查詢緩存的工作方式:

第一次執行某條select語句時,伺服器記住該查詢的文本內容和查詢結果,存儲在緩存中,下次碰到這個語句時,直接從緩存中返回結果;當更新數據表後,該數據表的任何緩存查詢都變成無效的,並且會被丟棄。

2)配置緩存參數:

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

50 調整硬件

1)在機器上裝更多的內存;

2)增加更快的硬盤以減少I/O等待時間;

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

3)在不同的物理硬盤設備上重新分配磁盤活動;

如果可能,應將最繁忙的資料庫存放在不同的物理設備上,這跟使用同一物理設備的不同分區是不同的,因為它們將爭用相同的物理資源(磁頭)。

You May Also Like