0x00
作為一個(gè)前端, 平時(shí)很少接觸數(shù)據(jù)庫. 當(dāng)需要自己在數(shù)據(jù)庫中創(chuàng)建用戶, 授權(quán)和創(chuàng)建表結(jié)構(gòu)時(shí), 就有點(diǎn)不知所措了, 更不要說什么字符編碼格式設(shè)置, 數(shù)據(jù)庫備份了. 每次自己想要折騰點(diǎn)什么的時(shí)候, 總是會(huì)被這些開發(fā)前的基礎(chǔ)操作卡住. 這次終于痛下決心, 將mysql使用中常用的一些命令做一個(gè)總結(jié), 以便在之后再次碰到時(shí)可以有一個(gè)查詢的地方, 不至于再在google上查的死去活來, 勞心又勞力. 好了, 正文開始.
0x01: mysql服務(wù)啟動(dòng)和停止
在linux上, 可以使用service命令來查看mysql服務(wù)的運(yùn)行狀態(tài).
service mysql status
如果Mysql服務(wù)是開啟狀態(tài)的, 可以看到類似如下信息.
● mariadb.service - MariaDB database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Sun 2018-11-04 10:05:23 CST; 26min ago
Process: 685 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 682 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Process: 472 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $?
Process: 438 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 392 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Main PID: 583 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 27 (limit: 4915)
CGroup: /system.slice/mariadb.service
└─583 /usr/sbin/mysqld
Nov 04 10:05:21 iZ8vb19h08028cm32sq645Z systemd[1]: Starting MariaDB database server...
Nov 04 10:05:23 iZ8vb19h08028cm32sq645Z mysqld[583]: 2018-11-04 10:05:23 139923048784448 [Note] /usr/sbin/mysqld (mysqld 1
Nov 04 10:05:23 iZ8vb19h08028cm32sq645Z systemd[1]: Started MariaDB database server.
這里使用的Mysql的一個(gè)衍生版: mariadb
service命令的基本使用格式如下:
service < option > | --status-all | [ service_name [ command | --full-restart ] ]
除了staus命令, 還有start, stop, restart命令等, 如果想要查看service命令的更多用法, 需要使用man命令來查看詳細(xì)說明.
man service
如果系統(tǒng)的版本比較老或者是非debian系的linux, 可能沒有service命令, 這里有一個(gè)替代命令: systemctl,具體的使用方法可以google一下, 在這里就不過多介紹了.
0x02: 登錄, 創(chuàng)建用戶和授權(quán)
登錄Mysql
一般在安裝mysql后, 如果沒有特殊設(shè)置, 一般只有一個(gè)root用戶. 如果需要?jiǎng)?chuàng)建新的用戶, 需要使用這個(gè)root用戶登錄mysql, 并執(zhí)行相應(yīng)的操作. 登錄mysql的命令如下:
mysql -u root -p
回車后提示輸入密碼, 輸入完成之后回車就登錄進(jìn)mysql了. 這里只是列出了登錄本地Mysql需要的基本命令. 如果需要登錄遠(yuǎn)程Mysql, 則需要通過Msql幫助獲取更多的參數(shù)說明.
mysql --help
創(chuàng)建新用戶
使用root用戶登錄Mysql后, 接下來就是創(chuàng)建新用戶了. 下面就是一條最簡(jiǎn)單的創(chuàng)建命令.
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
這樣就創(chuàng)建了一個(gè)能在本地登錄, 密碼是mypass的用戶jeffery. 接下來就是給用戶授權(quán)了.
授權(quán)
給用戶授權(quán), 則需要使用 GRANT 關(guān)鍵字. 如給剛剛已經(jīng)創(chuàng)建的jeffery授予數(shù)據(jù)庫db1的SELECT權(quán)限, 則只需要如下操作.
GRANT SELECT ON db1.* TO 'jeffery'@'localhost';
如果需要將SELECT權(quán)限擴(kuò)大到全部數(shù)據(jù)庫, 則只需要將db1換成*. 如果需要授予所有權(quán)限, 則需要將SELECT換成ALL.
授權(quán)時(shí)創(chuàng)建用戶
如果授權(quán)時(shí), 該用戶并未被創(chuàng)建, 授權(quán)語句并不會(huì)報(bào)錯(cuò), 與此同時(shí), Mysql會(huì)創(chuàng)建這個(gè)新用戶, 并將權(quán)限賦予它. 所以, 一般情況下, 我們可以在同時(shí)進(jìn)行用戶的創(chuàng)建和授權(quán). 那么就是下面的這種格式:
GRANT PRIVILEGES ON DATABASE.TABLE TO 'USERNAME'@'LOGINHOST' IDENTIFIED BY 'PASSWORD';
那么, 上面創(chuàng)建和授權(quán)的過程就可以簡(jiǎn)寫為這樣:
GRANT SELECT ON db1.* ON 'jeffery'@'localhost' IDENTIFIED BY 'mypass';
0x03: 數(shù)據(jù)庫信息查看
- 顯示數(shù)據(jù)庫列表:
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| resource |
+--------------------+
4 rows in set (0.01 sec)
- 進(jìn)入某個(gè)數(shù)據(jù)庫:
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
- 顯示庫中的數(shù)據(jù)表:
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
30 rows in set (0.00 sec)
- 查看某個(gè)表的表結(jié)構(gòu):
MariaDB [mysql]> describe host;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
20 rows in set (0.00 sec)
0x04: 數(shù)據(jù)庫和表的創(chuàng)建與刪除
- 數(shù)據(jù)庫的創(chuàng)建與刪除
# 創(chuàng)建
create database databaseName;
# 刪除
drop database databaseName;
- 表的創(chuàng)建與刪除
表創(chuàng)建之前, 必須要先指定數(shù)據(jù)庫, 否則是無法創(chuàng)建的. 刪除同理.
#創(chuàng)建
create table tableName(create_definition,...)
[table_options]
[partition_options]
#刪除
drop table tableName;
數(shù)據(jù)庫的創(chuàng)建與刪除相對(duì)來說, 比較簡(jiǎn)單, 但對(duì)于表來說, 因?yàn)樯婕暗矫總€(gè)字段的定義, 和對(duì)表的整體描述, 外加主鍵和外鍵關(guān)聯(lián)等, 表的創(chuàng)建與刪除更加復(fù)雜. 一般來說, 都會(huì)選擇可視化工具來完成對(duì)表的創(chuàng)建和刪除, 而不是手工去寫sql語句, 這里只是對(duì)建表的語句的結(jié)構(gòu)進(jìn)行一個(gè)簡(jiǎn)要的介紹, 詳細(xì)內(nèi)容可以參見Mysql的幫助文檔.
0x05: 系統(tǒng)工具的利用
這里忽略了如何對(duì)表及字段進(jìn)行的一些操作, 不是不重要, 只是全部介紹清楚太過于繁瑣, 即使看完了也只能是有個(gè)大致的印象, 等過個(gè)兩天估計(jì)又忘了. 但是, 如果我們能夠熟練的使用系統(tǒng)內(nèi)的幫助, 即使記不住這些繁瑣的結(jié)構(gòu), 也能很快的完成各種操作. 其中一個(gè)上面已經(jīng)說過了, 就是系統(tǒng)內(nèi)的man命令.
man mysql
MYSQL(1) MariaDB Database System MYSQL(1)
NAME
mysql - the MariaDB command-line tool
SYNOPSIS
mysql [options] db_name
DESCRIPTION
mysql is a simple SQL shell (with GNU readline capabilities). It supports interactive and non-interactive use.
When used interactively, query results are presented in an ASCII-table format. When used non-interactively (for
example, as a filter), the result is presented in tab-separated format. The output format can be changed using
command options.
...
這里對(duì)Mysql進(jìn)行了詳細(xì)的介紹. 不過由于沒有目錄結(jié)構(gòu), 可能會(huì)顯得比較多. 但是, 還有一個(gè)更好用的幫助文檔, 那就是Mysql終端里的help命令. 普通的help命令會(huì)顯示一些常用的快捷命令.
MariaDB [(none)]> help
General information about MariaDB can be found at
http://mariadb.org
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
For server side help, type 'help contents'
如果需要更加詳細(xì)的幫助, 則需要help contents命令的幫助.
MariaDB [(none)]> help contents;
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
Account Management
Administration
Compound Statements
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Help Metadata
Language Structure
Plugins
Procedures
Table Maintenance
Transactions
User-Defined Functions
Utility
在這里, 有更多細(xì)分的幫助說明. 在只有系統(tǒng)幫助的情況下, 就完成了以前需要在網(wǎng)上搜索很長(zhǎng)時(shí)間才能完成的前期準(zhǔn)備工作.
0x06: 后記
授之以魚, 不如授之以漁, 真正學(xué)會(huì)了如何利用系統(tǒng)幫助, 才能真正的事半功倍. 優(yōu)秀的軟件本身就是最好的幫助文檔, 通過這次對(duì)Mysql基本操作的學(xué)習(xí)和總結(jié), 更加深了我對(duì)這句話的理解.
