(1)基礎筆試命令考察

1.開啟MySQL服務

/etc/init.d/mysqld start

service mysqld start

systemctl start mysqld

2.檢測端口是否運行

lsof -i :3306

netstat -lntup |grep 3306

3.為MySQL設置密碼或者修改密碼

設置密碼

mysql -uroot -ppassword -e "set passowrd for root = passowrd('passowrd')"

mysqladmin -uroot passowrd "NEWPASSWORD"

更改密碼

mysqladmin -uroot passowrd oldpassowrd "NEWPASSWORD"

use mysql;

update user set passowrd = PASSWORD('newpassword') where user = 'root';flush privileges;

msyql 5.7以上版本修改默認密碼命令

alter user 'root'@'localhost' identified by 'root'

4.登陸MySQL數據庫

mysql -uroot -ppassword

5.查看當前數據庫的字符集

show create database DB_NAME;

6.查看當前數據庫版本

mysql -V

mysql -uroot -ppassowrd -e "use mysql;select version();"

7.查看當前登錄的用戶

select user();

8.創建GBK字符集的數據庫mingongge，并查看已建庫完整語句

create database mingongge DEFAULT CHARSET GBK COLLATE gbk_chinese_ci;

#查看創建的庫

show create database mingongge;

9.創建用戶mingongge，使之可以管理數據庫mingongge

grant all on mingongge.* to 'mingongge'@'localhost' identified by 'mingongge';

10.查看創建的用戶mingongge擁有哪些權限

show grants for mingongge@localhost

11.查看當前數據庫里有哪些用戶

select user from mysql.user;

12.進入mingongge數據庫

use mingongge

13.創建一innodb GBK表test，字段id int(4)和name varchar(16)

create table test (

id int(4),

name varchar(16)

)ENGINE=innodb DEFAULT CHARSET=gbk;

14.查看建表結構及表結構的SQL語句

desc test;

show create table test\G

15.插入一條數據“1,mingongge”

insert into test values('1','mingongge');

16.再批量插入2行數據 “2,民工哥”，“3,mingonggeedu”

insert into test values('2','民工哥'),('3','mingonggeedu');

17.查詢名字為mingongge的記錄

select * from test where name = 'mingongge';

18.把數據id等于1的名字mingongge更改為mgg

update test set name = 'mgg' where id = '1';

19.在字段name前插入age字段，類型tinyint(2)

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

20.不退出數據庫,完成備份mingongge數據庫

system mysqldump -uroot -pMgg123.0. -B mingongge >/root/mingongge_bak.sql

21.刪除test表中的所有數據，并查看

delete from test;

select * from test;

22.刪除表test和mingongge數據庫并查看

drop table test;

show tables;

drop database mingongge;

show databases;

23.不退出數據庫恢復以上刪除的數據

system mysql -uroot -pMgg123.0.

24.把庫表的GBK字符集修改為UTF8

alter database mingongge default character set utf8;

alter table test default character set utf8;

25.把id列設置為主鍵，在Name字段上創建普通索引

alter table test add primary key(id);

create index mggindex on test(name(16));

26.在字段name后插入手機號字段(shouji)，類型char(11)

alter table test add shouji char(11);

#默認就是在最后一列后面插入新增列

27.所有字段上插入2條記錄(自行設定數據)

insert into test values('4','23','li','13700000001'),('5','26','zhao','13710000001');

28.在手機字段上對前8個字符創建普通索引

create index SJ on test(shouji(8));

29.查看創建的索引及索引類型等信息

show index from test;

show create table test\G

#下面的命令也可以查看索引類型

show keys from test\G

30.刪除Name，shouji列的索引

drop index SJ on test;

drop index mggindex on test;

31.對Name列的前6個字符以及手機列的前8個字符組建聯合索引

create index lianhe on test(name(6),shouji(8));

32.查詢手機號以137開頭的，名字為zhao的記錄(提前插入)

select * from test where shouji like '137%' and name = 'zhao';

33.查詢上述語句的執行計劃(是否使用聯合索引等)

explain select * from test where name = 'zhao' and shouji like '137%'\G

34.把test表的引擎改成MyISAM

alter table test engine=MyISAM;

35.收回mingongge用戶的select權限

revoke select on mingongge.* from mingongge@localhost;

36.刪除mingongge用戶

drop user migongge@localhost;

37.刪除mingongge數據庫

drop database mingongge

38.使用mysqladmin關閉數據庫

mysqladmin -uroot -pMgg123.0. shutdown

lsof -i :3306

39.MySQL密碼丟了，請找回？

mysqld_safe --skip-grant-tables & #啟動數據庫服務

mysql -uroot -ppassowrd -e "use mysql;update user set passowrd = PASSWORD('newpassword') where user = 'root';flush privileges;"

總結

以上是生活随笔為你收集整理的MySQL运维知识点_mysql运维必备知识点（转载至其他作者）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。