mysql常用的語句有哪些

162次閱讀

共計 6797 個字符，預計需要花費 17 分鐘才能閱讀完成。

本篇內容介紹了“mysql 常用的語句有哪些”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

mysqld 常規 MySQL 服務器
mysqld-opt 優化 mysql 服務器, 提供一些功能可以挖掘更好的功能
mysqld-max 與 mysqld 一樣, 但可以支持更新, 更具實驗性質的功能(更不穩定)

++ 安裝 mysql
參見自帶的 INSTALL-SOURCE 文件
$ ./configure ?prefix=/app/mysql-5.0.51a ?with-charset=utf8 ?with-extra-charsets=utf8,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(8),
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 identified by“abc”;
mysql grant all privileges on *.* to”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 ?add-drop-table smgp_apps_wcnc wcnc_db.sql
-d 沒有數據 ?add-drop-table 在每個 create 語句之前增加一個 drop table

++ 忘記 mysql 密碼
先停止所有 mysql 服務進程
$ mysqld_safe ?skip-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(8),…)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(8) 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
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(20080808),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(’’,’mysql’,’’,’sqlserver’,’’,’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 statusG”

++ 壞庫掃描修復
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 #查看所有變量值
? max_connections 允許的最大可連接數,
# 需要加大 max_connections 可以在 my.cnf 中加入 set-variable = max_connections=32000, 可以對與下面的 threads_connected 值決定是否需要增大.

show status [like ….];
? threads_connected 數據庫當前的連接線程數
#FLUSH STATUS 可以重置一些計數器

show processlist;
kill id;

++my.cnf 配置
?Enable Slow Query Log
long_query_time=1
log-slow-queries=/var/log//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

?others
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 記數器的查詢時間閥值

“mysql 常用的語句有哪些”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！

正文完