程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> MySQL常用命令總結 (Linux)

MySQL常用命令總結 (Linux)

編輯：關於MYSQL數據庫

MySQL下載:
mysqld 常規MySQL服務器
mysqld-opt 優化MySQL服務器,提供一些功能可以挖掘更好的功能
mysqld-max 與mysqld一樣,但可以支持更新,更具實驗性質的功能(更不穩定) ++安裝MySQL
參見自帶的INSTALL-SOURCE文件
$ ./configure Cprefix=/app/mysql-5.0.51a Cwith-charset=utf8 Cwith-extra-charsets=gbk,gb2312,utf8 ++啟動/關閉MySQL
$ path/mysqld_safe -user=MySQL &
$ /MySQLadmin -p shutdown ++修改root口令
$ MySQLadmin -u root -p passWord ‘新密碼’ ++查看服務器狀態
$ path/mysqladmin version -p ++連接遠端MySQL服務器
$ path/MySQL -u 用戶名 -p #連接本機
$ path/mysql -h 遠程主機IP -u 用戶名 -p#連接遠程MySQL服務器 ++創建/刪除數據庫或表
$ MySQLadmin -u root -p create xxx
MySQL> create database 數據庫名;
MySQL> create TABLE items (
id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
symbol CHAR(4) NOT NULL,
username CHAR(,
INDEX sym (symbol),INDEX …..
UNIQUE(username)
) type=innodb;
MySQL> drop database [if exists] 數據庫名
MySQL> create table 表名;
MySQL> drop table 表名; ++查看數據庫和查看數據庫下的表
MySQL> show databases;
MySQL> show tables;
MySQL> show table status;
MySQL> desc 表名; #查看具體表結構信息
MySQL> SHOW CREATE DATABASE db_name #顯示創建db_name庫的語句
MySQL> SHOW CREATE TABLE tbl_name #顯示創建tbl_name表的語句 ++創建用戶
MySQL> grant select,insert,update,delete,alter on mydb.* to test2@localhost identifIEd by “abc”;
MySQL> grant all privileges on *.* to test1@”%” identifIEd by “abc”;
MySQL> flush privileges; ++用戶管理
MySQL> update user set password=passWord (’11111′) where user=’test1′; #修改test1密碼為111111
MySQL> DELETE FROM user WHERE User=”testuser” and Host=”localhost”; #刪除用戶帳號
mysql> SHOW GRANTS FOR user1; #顯示創建user1用戶的grant語句 ++MySQL數據庫的備份和恢復
$ MySQLdump -uuser -ppassWord -B DB_name [--tables table1 --tables table2] > exportfile.sql
$ MySQL -uroot -p xxx < aaa.sql #導入表
$ MySQLdump -u 用戶名 -p 數據庫名表名> 導出的文件名 ##導出單獨的表 ++導出一個數據庫結構
$ MySQLdump -u wcnc -p -d Cadd-drop-table smgp_aPPS_wcnc >wcnc_db.sql
-d 沒有數據 Cadd-drop-table 在每個create語句之前增加一個drop table ++忘記MySQL密碼
先停止所有MySQL服務進程
$ mysqld_safe Cskip-grant-tables & MySQL
mysql> use MySQL;
MySQL> update user set password=passWord(’111111′) where user=’root’;
MySQL> flush privileges;
然後重啟MySQL並以新密碼登入即可 ++當前使用的數據庫
MySQL> select database(); ===數據庫日常操作維護==== MySQL> create table table_name
(column_name datatype {identity |null|not null},f_time TIMESTAMP(,…)ENGINE=MyISAM AUTO_INCREMENT=3811 DEFAULT CHARSET=utf8;
例: CREATE TABLE guest (name varchar(10),sex varchar(2),age int(3),career varchar(10));
# desc guest可查看表結構信息
# TIMESTAMP( YYYYMMDD 其中(2/4/6/8/10/12/14)對應不同的時間格式
MySQL> SHOW CREATE TABLE tbl_name #顯示創建tbl_name表的語句 ++創建索引
可以在建表的時候加入index indexname (列名)創建索引,
也可以手工用命令生成 create index index_name on table_name (col_name[(length)],… )
MySQL> CREATE INDEX number ON guest (number(10));
MySQL> SHOW INDEX FROM tbl_name [FROM db_name] #顯示現有索引
MySQL> repair TABLE date QUICK; #索引列相關變量變化後自動重建索引 ++查詢及常用函數
MySQL> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;
MySQL> select college, region, seed from tournament ORDER BY region, seed;
MySQL> select col_name from tbl_name WHERE col_name > 0;
MySQL> select DISTINCT …… [DISTINCT關鍵字可以除去重復的記錄]
MySQL> select DATE_FORMAT(NOW(),’%m/%d/%Y’) as DATE, DATE_FORMAT(NOW(),’%H:%m:%s’) AS TIME;
MySQL> select CURDATE(),CURTIME(),YEAR(NOW()),MONTH(NOW()),DAYOFMONTH(NOW()),HOUR(NOW()),MINUTE(NOW());
mysql> select UNIX_TIMESTAMP(),UNIX_TIMESTAMP(2008080,FROM_UNIXTIME(UNIX_TIMESTAMP()); MySQL> select PASSWord(”secret”),MD5(”secret”); #加密密碼用
mysql> select count(*) from tab_name order by id [DESC|ASC]; #DESC倒序/ASC正序 * 函數count,AVG,SUM,MIN,MAX,LENGTH字符長度,LTRIM去除開頭的空頭,RTRIM去尾部空格,TRIM(str)去除首部尾部空格,LETF/RIGHT(str,x)返回字符串str的左邊/右邊x個字符,SUBSTRING(str,x,y)返回str中的x位置起至位置y的字符MySQL> select BINARY ‘ross’ IN (’Chandler’,’Joey’, ‘Ross’); #BINARY嚴格檢查大小寫 * 比較運算符IN,BETWEEN,IS NULL,IS NOT NULL,LIKE,REGEXP/RLIKE
mysql> select count(*),AVG(number_xx),Host,user from MySQL.user GROUP by user [DESC|ASC] HAVING user=root; #分組並統計次數/平均值 ++UNIX_TIMESTAMP(date)
返回一個Unix時間戳記(從’1970-01-01 00:00:00′GMT開始的秒數)
MySQL> select UNIX_TIMESTAMP();
MySQL> select UNIX_TIMESTAMP(’1997-10-04 22:23:00′);
MySQL> select FROM_UNIXTIME(875996580); #根據時間戳記算出日期 ++控制條件函數
MySQL> select if(1<10,2,3), IF(55>100,’true’,’false’);
#IF()函數有三個參數,第一個是被判斷的表達式,如果表達式為真,返回第二個參數,如果為假,返回第三個參數.
MySQL> select CASE WHEN (2+2)=4 THEN “OK” WHEN (2+2)<>4 THEN ‘NOT OK’ END AS status; ++系統信息函數
MySQL> select DATABASE(),VERSION(),USER();
mysql> select BENCHMARK(9999999,LOG(RAND()*PI())) AS PERFORMANACE; #一個測試MySQL運算性能工具 ++將wp_posts表中post_content字段中文字”old”替換為”new”
MySQL> update wp_posts set post_content=replace(post_content,’old’,’new’) ++改變表結構
MySQL> alter table table_name alter_spec [, alter_spec ...]
例：alter table dbname add column userid int(11) not null primary key auto_increment;
這樣，就在表dbname中添加了一個字段userid，類型為int(11)。 ++調整列順序
MySQL> alter table tablename CHANGE id id int(11) first; ++修改表中數據
insert [into] table_name [(column(s))] values (expression(s))
例：MySQL>insert into mydatabase values(’PHP’,’MySQL’,’ASP’,’sqlserver’,’JSP’,’Oracle’);
mysql> create table user select host,user from MySQL.user where 1=0;
mysql> insert into user(host,user) select host,user from MySQL.user; ++更改表名
命令：rename table 原表名 to 新表名; ++表的數據更新
MySQL> update table01 set field04=19991022[, field05=062218] where fIEld01=1; ++刪除數據
MySQL> delete from table01 where fIEld01=3;
#如果想要清空表的所有紀錄,建議用truncate table tablename而不是delete from tablename. ++SHELL提示符下運行SQL命令
$ MySQL -e “show slave status\G ” ++壞庫掃描修復
cd /var/lib/MySQL/xxx && myisamchk playlist_block ++insert into a (x) values (’11a’)
出現: ata truncated for column ‘x’ at row 1 在my.ini裡找到
sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_Create_USER,NO_ENGINE_SUBSTITUTION”
把其中的STRICT_TRANS_TABLES,去掉,然後重啟MySQL就ok了 ++復制表
MySQL> create table target_table like source_table ++innodb支持事務
新表:create TABLE table-name (fIEld-definitions) TYPE=INNODB;
舊表: alter TABLE table-name TYPE=INNODB;
MySQL> start transaction #標記一個事務的開始
MySQL> insert into….. #數據變更
MySQL> ROLLBACK或commit #回滾或提交
MySQL> SET AUTOCOMMIT=1; #設置自動提交
MySQL> select @@autocommit; #查看當前是否自動提交 ++表鎖定相關
MySQL> LOCK TABLE users READ; # 對user表進行只讀鎖定
MySQL> LOCK TABLES user READ, pfoliOS WRITE #多表鎖控制
mysql> UNLOCK TABLES; #不需要指定鎖定表名字, MySQL會自動解除所有表鎖定 =====一些mysql優化與管理====== MySQL> show variables #查看所有變量值
C max_connections 數據庫允許的最大可連接數,
#需要加大max_connections可以在my.cnf中加入set-variable = max_connections=32000,可以對與下面的threads_connected值決定是否需要增大. show status [like ....];
C threads_connected 數據庫當前的連接線程數
#FLUSH STATUS 可以重置一些計數器 show processlist;
kill id; ++my.cnf配置
CEnable Slow Query Log
long_query_time=1
log-slow-querIEs=/var/log/MySQL/log-slow-querIEs.log
log-querIEs-not-using-indexes # MySQLdumpslow -s c -t 20 host-slow.log #訪問次數最多的20個sql語句
# MySQLdumpslow -s r -t 20 host-slow.log #返回記錄集最多的20個sql Cothers
max_connections=500 #用過的最大連接數SHOW Status like ‘max_used_connection’;
wait_timeout=10 #終止所有空閒時間超過 10 秒的連接
table_cache=64 #任何時間打開表的總數
ax_binlog_size=512M #循環之前二進制日志的最大規模
max_connect_errors = 100 query_cache_size = 256M #查詢緩存
#可用 SHOW STATUS LIKE ‘qcache%’;查看命中率
#FLUSH STATUS重置計數器, FLUSH QUERY CACHE清緩存 thread_cache = 40
#線程使用,SHOW STATUS LIKE ‘Threads_created %’; 值快速增加的話考慮加大 key_buffer = 16M
#show status like ‘%key_read%’; Key_reads 代表命中磁盤的關鍵字請求個數
#A: 到底 Key Buffer 要設定多少才夠呢? Q: MySQL 只會 Cache 索引(*.MYI)，因此參考所有 MYI文件的總大小 sort_buffer_size = 4M #查詢排序時所能使用的緩沖區大小,每連接獨享4M
#show status like ‘%sort%’; 如sort_merge_passes很大，就表示加大 sort_buffer_sizesort_buffer_size = 6M #查詢排序時所能使用的緩沖區大小,這是每連接獨享值6M
read_buffer_size = 4M　#讀查詢操作所能使用的緩沖區大小
join_buffer_size = 8M #聯合查詢操作所能使用的緩沖區大小
skip-locking #取消文件系統的外部鎖
skip-name-resolve
thread_concurrency = 8　　#最大並發線程數,cpu數量*2
long_query_time = 10 #Slow_querIEs記數器的查詢時間閥值檢查my.cnf設置是否合理的一個工具

關於MYSQL數據庫