MySQL必會基礎命令

標籤：mysql 基礎命令

1.1 登陸資料庫。

mysql -uroot -poldboy123 -S /data/3306/mysql.sock

1.2 查看資料庫版本及當前登入使用者是什麼。

select version();select user();

1.3 建立GBK字元集的資料庫oldboy，並查看已建庫的完整語句。

create database oldboy character set gbk collate gbk_chinese_ci;show create database oldboy\G

1.4 建立使用者oldboy，使之可以管理資料庫oldboy。

grant all on oldboy.* to ‘oldboy‘@‘localhost‘ identified by ‘oldboy123‘;

1.5 查看建立的使用者oldboy擁有哪些許可權。

show grants for [email protected]\G

1.6 查看當前資料庫裡有哪些使用者。

select user,host from mysql.user;

1.7 建立系統管理員帳戶admin

grant all on *.* to ‘admin‘@‘localhost‘ identified by ‘admin123‘ with grant option;

1.8 進入oldboy資料庫

use oldboy;

1.9 建立test表：innodb引擎，字元集為GBK，欄位為id int(4)和name varchar(16)，查看建表結構及SQL語句。

create table test (id int(4),name varchar(16))ENGINE=innodb DEFAULT CHARSET=gbk;desc test; #<==等價於命令：show columns from test;show create table test\G

1.10 插入一條資料 1,oldboy

insert into test values(‘1‘,‘oldboy‘);

1.11 批量插入資料 2,老男孩，3,etiantian。要求中文不能亂碼。

insert into test values(‘2‘,‘老男孩‘),(‘3‘,‘etiantian‘);

1.12 查詢插入的所有記錄，查詢名字為oldboy的記錄。查詢id大於1的記錄。

select * from test where name=‘oldboy‘;select * from test where id>1;

1.13 把資料id等於1的名字oldboy更改為oldgirl。

update test set name=‘oldgirl‘ where id=1;

1.14 在欄位name前插入age欄位，類型tinyint(2)。

alter table test add name tinyint(2) after id;

1.15 備份oldboy庫及mysql庫。

mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock --events -B oldboy mysql >/opt/bak_$(date +%F).sql

egrep -v "#|^$|--|\/" /opt/bak_2017-06-06-13時36分37秒.sql

1.16 刪除表中的所有資料，並查看。

truncate table test; #<==物理刪除，一次性清空，不可以rollbackdelete from test; #<==邏輯刪除，一行一行的刪，比較慢，可以rollback

1.17 刪除表test和oldboy資料庫並查看

drop table test;drop database oldboy;

1.18 Linux命令列恢複以上刪除的資料。

mysql -uroot -poldboy123 -S /data/3306/mysql.sock </opt/bak_2017-06-07-22時13分20秒.sql

1.19 把GBK字元集修改為UTF8（可選，注意，此題有陷阱）。

1.先匯出表中資料

mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -B oldboy >/opt/test.sqlegrep -v "^$|--|\/" /opt/test.sql #<==可以看到這一行：ENGINE=MyISAM DEFAULT CHARSET=gbk;

2.修改字元集

sed -i ‘s#CHARSET=gbk#CHARSET=utf8#g‘ /opt/test.sqlegrep -v "^$|--|\/" /opt/test.sql #<==驗證：) ENGINE=MyISAM DEFAULT CHARSET=utf8;

3.恢複資料

在sql檔案中添加一條 set names utf8；並恢複mysql -uroot -poldboy123 -S /data/3306/mysql.sock  oldboy </opt/test.sql

1.20 MySQL密碼丟了，如何找回實戰？

[[email protected] ~]# netstat -tunlp|grep 3306 #<==先查看服務是否正常tcp        0      0 0.0.0.0:3306            0.0.0.0:*          LISTEN      62358/mysqld[[email protected] ~]# kill 62358 #<==kill掉進程的pid[[email protected] ~]# netstat -tunlp|grep 3306 #<==mysql進程已關閉[[email protected] ~]# mysqld_safe --help #<==利用mysqld_safe命令指定設定檔，跳過授權表來破密碼[[email protected] ~]# mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables 2>&1 >/dev/null &[[email protected] ~]# mysql -S /data/3306/mysql.sock #<==無密碼登入進多執行個體3306mysql> select user,host,password from mysql.user where user=‘root‘ and host=‘localhost‘;+------+-----------+-------------------------------------------+| user | host      | password                                  |+------+-----------+-------------------------------------------+| root | localhost | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |+------+-----------+-------------------------------------------+1 row in set (0.00 sec)#<==先看下mysql庫，user表裡的欄位內容mysql> update mysql.user set password=PASSWORD("oldboy123") where user=‘root‘ and host=‘localhost‘; #<==利用update命令來更新[email protected]使用者的密碼mysql> flush privileges; #<==記得重新整理授權表，否則不會立馬生效的Query OK, 0 rows affected (0.00 sec)[[email protected] ~]# sed -i ‘s#mysql_pwd="oldboy456"#mysql_pwd="oldboy123"#g‘ /data/3306/mysql[[email protected] ~]# grep mysql_pwd= /data/3306/mysql #<==修改啟動指令碼的密碼，才能利用命令來停止服務mysql_pwd="oldboy123"[[email protected] ~]# /data/3306/mysql stop #<==先停掉服務，因為有跳過授權表的參數在Stoping MySQL...[1]+  Done   mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables 2>&1 > /dev/null[[email protected] ~]# /data/3306/mysql start #<==啟動Starting MySQL...[[email protected] ~]# ss -tunlp|grep 3306 #<==偵聽正常tcp    LISTEN     0      600          *:3306        *:*      users:(("mysqld",66695,12))[[email protected] ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock #<==成功登入

1.21 MySQL內中文資料亂碼的原理及如何防止亂碼？（可選）。

保證以下的字元集一致即可

mysql> show VARIABLES like ‘character_set%‘;               +--------------------------+--------------+| Variable_name            | Value        |+--------------------------+--------------+| character_set_client     | utf8         |#<==用戶端字元集| character_set_connection | utf8         |#<==用戶端串連字元集，設定檔指定或建庫表指定| character_set_database   | utf8         |#<==資料庫的字元集| character_set_filesystem | binary       |#<==檔案系統字元集| character_set_results    | utf8         |#<==用戶端返回結果字元集| character_set_server     | utf8         |#<==伺服器字元集，設定檔指定或建庫表指定| character_set_system     | utf8         |#<==Linux系統的字元集| character_sets_dir       | /application/mysql-5.5.49/share/charsets/ |+--------------------------+-------------------------------------------+8 rows in set (0.01 sec)

1.22 在把id列設定為主鍵，在name欄位上建立普通索引。

alter table test add primary key (id);alter table test add index index_name (name);

1.23 在欄位name後插入手機號欄位(shouji)，類型char(11)。

alter table test add shouji char(11);

1.24 所有欄位上插入2條記錄（自行設定資料）

insert into test val-ues(‘4‘,‘18‘,‘chen‘,‘15298914487‘),(‘5‘,‘19‘,‘he‘,‘15298913929‘);

1.25 在手機欄位上對前8個字元建立普通索引。

alter table test add index index_shouji (shouji(8));

1.26 查看建立的索引及索引類型等資訊。

desc test;show create table test\Gshow index from test\G

1.27 刪除name，shouji列的索引。

alter table test drop index index_name;alter table test drop index index_shouji;

1.28 對name列的前6個字元以及手機列的前8個字元組建聯合索引。

alter table test drop index index_shouji;

1.29 查詢手機號以152開頭的，名字為chen的記錄（此記錄要提前插入）。

select * from test where name=‘chen‘ and shouji like ‘152%‘;

1.30 查詢上述語句的執行計畫（是否使用聯合索引等）。

explain select * from test where name=‘chen‘ and shouji like ‘152%‘\G

1.31 把test表的引擎改成MyISAM。

alter table test ENGINE=MYISAM;

本文出自 “陳發哥007” 部落格，請務必保留此出處http://chenfage.blog.51cto.com/8804946/1933390

MySQL必會基礎命令