mysql常用的語(yǔ)句有哪些

本篇內(nèi)容介紹了“mysql常用的語(yǔ)句有哪些”的有關(guān)知識(shí)，在實(shí)際案例的操作過程中，不少人都會(huì)遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

mysqld 常規(guī)MySQL服務(wù)器
mysqld-opt 優(yōu)化mysql服務(wù)器,提供一些功能可以挖掘更好的功能
mysqld-max 與mysqld一樣,但可以支持更新,更具實(shí)驗(yàn)性質(zhì)的功能(更不穩(wěn)定)

++安裝mysql
參見自帶的INSTALL-SOURCE文件
$ ./configure ?prefix=/app/mysql-5.0.51a ?with-charset=utf8 ?with-extra-charsets=utf8,gb2312,utf8

++啟動(dòng)/關(guān)閉mysql
$ path/mysqld_safe -user=mysql &
$ /mysqladmin -p shutdown

++修改root口令
$ mysqladmin -u root -p password ‘新密碼’

++查看服務(wù)器狀態(tài)
$ path/mysqladmin version -p

++連接遠(yuǎn)端mysql服務(wù)器
$ path/mysql -u 用戶名 -p #連接本機(jī)
$ path/mysql -h 遠(yuǎn)程主機(jī)IP -u 用戶名 -p#連接遠(yuǎn)程MYSQL服務(wù)器

++創(chuàng)建/刪除 數(shù)據(jù)庫(kù)或表
$ mysqladmin -u root -p create xxx
mysql> create database 數(shù)據(jù)庫(kù)名;
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] 數(shù)據(jù)庫(kù)名
mysql> create table 表名;
mysql> drop table 表名;

++查看數(shù)據(jù)庫(kù)和查看數(shù)據(jù)庫(kù)下的表
mysql> show databases;
mysql> show tables;
mysql> show table status;
mysql> desc 表名; #查看具體表結(jié)構(gòu)信息
mysql> SHOW CREATE DATABASE db_name #顯示創(chuàng)建db_name庫(kù)的語(yǔ)句
mysql> SHOW CREATE TABLE tbl_name #顯示創(chuàng)建tbl_name表的語(yǔ)句

++創(chuàng)建用戶
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”; #刪除用戶帳號(hào)
mysql> SHOW GRANTS FOR user1; #顯示創(chuàng)建user1用戶的grant語(yǔ)句

++mysql數(shù)據(jù)庫(kù)的備份和恢復(fù)
$ mysqldump -uuser -ppassword -B DB_name [--tables table1 --tables table2] > exportfile.sql
$ mysql -uroot -p xxx < aaa.sql #導(dǎo)入表
$ mysqldump -u 用戶名 -p 數(shù)據(jù)庫(kù)名 表名> 導(dǎo)出的文件名 ##導(dǎo)出單獨(dú)的表

++導(dǎo)出一個(gè)數(shù)據(jù)庫(kù)結(jié)構(gòu)
$ mysqldump -u wcnc -p -d ?add-drop-table smgp_apps_wcnc >wcnc_db.sql
-d 沒有數(shù)據(jù) ?add-drop-table 在每個(gè)create語(yǔ)句之前增加一個(gè)drop table

++忘記mysql密碼
先停止所有mysql服務(wù)進(jìn)程
$ mysqld_safe ?skip-grant-tables & mysql
mysql> use mysql;
mysql> update user set password=password(’111111′) where user=’root’;
mysql> flush privileges;
然后重啟mysql并以新密碼登入即可

++當(dāng)前使用的數(shù)據(jù)庫(kù)
mysql> select database();

===數(shù)據(jù)庫(kù)日常操作維護(hù)====
++創(chuàng)建表
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可查看表結(jié)構(gòu)信息
# TIMESTAMP(8) YYYYMMDD 其中(2/4/6/8/10/12/14)對(duì)應(yīng)不同的時(shí)間格式
mysql> SHOW CREATE TABLE tbl_name #顯示創(chuàng)建tbl_name表的語(yǔ)句

++創(chuàng)建索引
可以在建表的時(shí)候加入index indexname (列名)創(chuàng)建索引,
也可以手工用命令生成 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] #顯示現(xiàn)有索引
mysql> repair TABLE date QUICK; #索引列相關(guān)變量變化后自動(dòng)重建索引

++查詢及常用函數(shù)
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關(guān)鍵字可以除去重復(fù)的記錄]
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正序

* 函數(shù)count,AVG,SUM,MIN,MAX,LENGTH字符長(zhǎng)度,LTRIM去除開頭的空頭,RTRIM去尾部空格,TRIM(str)去除首部尾部空格,LETF/RIGHT(str,x)返回字符串str的左邊/右邊x個(gè)字符,SUBSTRING(str,x,y)返回str中的x位置起至位置y的字符mysql> select BINARY ‘ross’ IN (’Chandler’,’Joey’, ‘Ross’); #BINARY嚴(yán)格檢查大小寫

* 比較運(yùn)算符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; #分組并統(tǒng)計(jì)次數(shù)/平均值

++UNIX_TIMESTAMP(date)
返回一個(gè)Unix時(shí)間戳記(從’1970-01-01 00:00:00′GMT開始的秒數(shù))
mysql> select UNIX_TIMESTAMP();
mysql> select UNIX_TIMESTAMP(’1997-10-04 22:23:00′);
mysql> select FROM_UNIXTIME(875996580); #根據(jù)時(shí)間戳記算出日期

++控制條件函數(shù)
mysql> select if(1<10,2,3), IF(55>100,’true’,’false’);
#IF()函數(shù)有三個(gè)參數(shù),第一個(gè)是被判斷的表達(dá)式,如果表達(dá)式為真,返回第二個(gè)參數(shù),如果為假,返回第三個(gè)參數(shù).
mysql> select CASE WHEN (2+2)=4 THEN “OK” WHEN (2+2)<>4 THEN ‘NOT OK’ END AS status;

++系統(tǒng)信息函數(shù)
mysql> select DATABASE(),VERSION(),USER();
mysql> select BENCHMARK(9999999,LOG(RAND()*PI())) AS PERFORMANACE; #一個(gè)測(cè)試mysql運(yùn)算性能工具

++將wp_posts表中post_content字段中文字”old”替換為”new”
mysql> update wp_posts set post_content=replace(post_content,’old’,’new’)

++改變表結(jié)構(gòu)
mysql> alter table table_name alter_spec [, alter_spec ...]
例：alter table dbname add column userid int(11) not null primary key auto_increment;
這樣，就在表dbname中添加了一個(gè)字段userid，類型為int(11)。

++調(diào)整列順序
mysql> alter table tablename CHANGE id id int(11) first;

++修改表中數(shù)據(jù)
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 新表名;

++表的數(shù)據(jù)更新
mysql> update table01 set field04=19991022[, field05=062218] where field01=1;

++刪除數(shù)據(jù)
mysql> delete from table01 where field01=3;
#如果想要清空表的所有紀(jì)錄,建議用truncate table tablename而不是delete from tablename.

++SHELL提示符下運(yùn)行SQL命令
$ mysql -e “show slave statusG ”

++壞庫(kù)掃描修復(fù)
cd /var/lib/mysql/xxx && myisamchk playlist_block

++insert into a (x) values (’11a’)
出現(xiàn): 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了

++復(fù)制表
mysql> create table target_table like source_table

++innodb支持事務(wù)
新表:create TABLE table-name (field-definitions) TYPE=INNODB;
舊表: alter TABLE table-name TYPE=INNODB;
mysql> start transaction #標(biāo)記一個(gè)事務(wù)的開始
mysql> insert into….. #數(shù)據(jù)變更
mysql> ROLLBACK或commit #回滾或提交
mysql> SET AUTOCOMMIT=1; #設(shè)置自動(dòng)提交
mysql> select @@autocommit; #查看當(dāng)前是否自動(dòng)提交

++表鎖定相關(guān)
mysql> LOCK TABLE users READ; # 對(duì)user表進(jìn)行只讀鎖定
mysql> LOCK TABLES user READ, pfolios WRITE #多表鎖控制
mysql> UNLOCK TABLES; #不需要指定鎖定表名字, MySQL會(huì)自動(dòng)解除所有表鎖定

=====一些mysql優(yōu)化與管理======
++管理用命令
mysql> show variables #查看所有變量值
? max_connections 允許的最大可連接數(shù),
#需要加大max_connections可以在my.cnf中加入set-variable = max_connections=32000,可以對(duì)與下面的threads_connected值決定是否需要增大.

show status [like ....];
? threads_connected 數(shù)據(jù)庫(kù)當(dāng)前的連接線程數(shù)
#FLUSH STATUS 可以重置一些計(jì)數(shù)器

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 #訪問次數(shù)最多的20個(gè)sql語(yǔ)句
# mysqldumpslow -s r -t 20 host-slow.log #返回記錄集最多的20個(gè)sql

?others
max_connections=500 #用過的最大連接數(shù)SHOW Status like ‘max_used_connection’;
wait_timeout=10 #終止所有空閑時(shí)間超過 10 秒的連接
table_cache=64 #任何時(shí)間打開表的總數(shù)
ax_binlog_size=512M #循環(huán)之前二進(jìn)制日志的最大規(guī)模
max_connect_errors = 100

query_cache_size = 256M #查詢緩存
#可用 SHOW STATUS LIKE ‘qcache%’;查看命中率
#FLUSH STATUS重置計(jì)數(shù)器, FLUSH QUERY CACHE清緩存

thread_cache = 40
#線程使用,SHOW STATUS LIKE ‘Threads_created %’; 值快速增加的話考慮加大

key_buffer = 16M
#show status like ‘%key_read%’; Key_reads 代表命中磁盤的關(guān)鍵字請(qǐng)求個(gè)數(shù)
#A: 到底 Key Buffer 要設(shè)定多少才夠呢? Q: MySQL 只會(huì) Cache 索引(*.MYI)，因此參考所有 MYI文件的總大小

sort_buffer_size = 4M #查詢排序時(shí)所能使用的緩沖區(qū)大小,每連接獨(dú)享4M
#show status like ‘%sort%’; 如sort_merge_passes很大，就表示加大

sort_buffer_sizesort_buffer_size = 6M #查詢排序時(shí)所能使用的緩沖區(qū)大小,這是每連接獨(dú)享值6M
read_buffer_size = 4M　#讀查詢操作所能使用的緩沖區(qū)大小
join_buffer_size = 8M #聯(lián)合查詢操作所能使用的緩沖區(qū)大小
skip-locking #取消文件系統(tǒng)的外部鎖
skip-name-resolve
thread_concurrency = 8　　#最大并發(fā)線程數(shù),cpu數(shù)量*2
long_query_time = 10 #Slow_queries記數(shù)器的查詢時(shí)間閥值

“mysql常用的語(yǔ)句有哪些”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！