Mac上實現終端管理MySQL數據庫

365

打開終端輸入如下命令：

/usr/local/mysql/bin/mysql?-u?root?-p

其中root為用戶名。

這時會出現如下命令：

Enter?password:

此時如果你沒有改密碼，直接敲回車。否則，輸入你的密碼。
這樣就可以訪問你的數據庫服務器了。

1、的操作及管理

數據表的基

數據庫（database）管理

1.1 create 創建數據庫

create?database?firstDB;

1.2 show 查看所有數據庫

mysql>?show?databases;+--------------------+|?Database???????????| +--------------------+|?information_schema?| |?firstDB????????????| |?mysql??????????????| |?performance_schema?|+--------------------+rows?in?set?(0.00?sec)

1.3 alter 修改數據庫

alter 命令修改數據庫編碼：

默認創建的數據庫默認不支持中文字符，如果我們需要它支持中文字符，則將它的編碼設置為utf8格式：

mysql>?ALTER?DATABASE?testDB?CHARACTER?SET?UTF8;Query?OK,?1?row?affected?(0.00?sec)

1.4 use 使用數據庫

mysql>?use?firstDB; Database?changed

1.5 查看當前使用的數據庫

mysql>?select?database(); +------------+|?database()?| +------------+|?firstdb????| +------------+1?row?in?set?(0.00?sec)

1.6 drop 刪除數據庫

mysql>?drop?database?firstDB; Query?OK,?0?rows?affected?(0.00?sec)

2、數據表（table）管理

我們首先創建一個數據庫，提供我們往后的使用：

mysql>?create?database?testDB;Query?OK,?1?row?affected?(0.00?sec)

創建后記得用use命令進入（使用）數據庫，不然后面的操作都會不成功的。

2.1 create 創建表

mysql>?create?table?PEOPLE?( ????->?ID?int?AUTO_INCREMENT?PRIMARY?KEY,????->?NAME?varchar(20)?not?null,????->?AGE?int?not?null,????->?BIRTHDAY?datetime);????????????????????????????????????????????????????? Query?OK,?0?rows?affected?(0.01?sec)

2.2 show 顯示表

顯示當前數據庫所有的數據表

mysql>?show?tables; +------------------+|?Tables_in_testdb?| +------------------+|?PEOPLE???????????| +------------------+1?row?in?set?(0.00?sec)

2.3 desc 查看表結構

mysql>?desc?PEOPLE????->?; +----------+-------------+------+-----+---------+----------------+|?Field????|?Type????????|?Null?|?Key?|?Default?|?Extra??????????| +----------+-------------+------+-----+---------+----------------+|?ID???????|?int(11)?????|?NO???|?PRI?|?NULL????|?auto_increment?| |?NAME?????|?varchar(20)?|?NO???|?????|?NULL????|????????????????| |?AGE??????|?int(11)?????|?NO???|?????|?NULL????|????????????????| |?BIRTHDAY?|?datetime????|?YES??|?????|?NULL????|????????????????| +----------+-------------+------+-----+---------+----------------+ 4?rows?in?set?(0.01?sec)

2.4 alter 修改表結構（增、刪、改）?
默認創建的表不支持中文字符，所以需將表編碼設置為utf8：

mysql>?ALTER?TABLE?KEYCHAIN?CONVERT?TO?CHARACTER?SET?UTF8; Query?OK,?1?row?affected?(0.02?sec) Records:?1??Duplicates:?0??Warnings:?0

2.4.1 insert 在表中添加列（字段）

mysql>?alter?table?PEOPLE?add?star?BOOL;Query?OK,?0?rows?affected?(0.02?sec)Records:?0??Duplicates:?0??Warnings:?0

提示：在MySQL里，布爾類型會自動轉換為tinyint(1)類型。

我們不妨使用desc去查看一下PEOPLE表結構：

mysql>?desc?PEOPLE; +----------+-------------+------+-----+---------+----------------+|?Field????|?Type????????|?Null?|?Key?|?Default?|?Extra??????????| +----------+-------------+------+-----+---------+----------------+|?ID???????|?int(11)?????|?NO???|?PRI?|?NULL????|?auto_increment?| |?NAME?????|?varchar(20)?|?NO???|?????|?NULL????|????????????????| |?AGE??????|?int(11)?????|?NO???|?????|?NULL????|????????????????| |?BIRTHDAY?|?datetime????|?YES??|?????|?NULL????|????????????????| |?star?????|?tinyint(1)??|?YES??|?????|?NULL????|????????????????| +----------+-------------+------+-----+---------+----------------+ 5?rows?in?set?(0.00?sec)

現在，你該相信我了吧？

2.4.2 alter 修改表（列）字段

mysql>?alter?table?PEOPLE?MODIFY?star?int;Query?OK,?0?rows?affected?(0.01?sec)Records:?0??Duplicates:?0??Warnings:?0

也可以指定 int(n) 的長度，比如 int(2)。

我們再次使用desc查看PEOPLE表結構：

mysql>?desc?PEOPLE; +----------+-------------+------+-----+---------+----------------+|?Field????|?Type????????|?Null?|?Key?|?Default?|?Extra??????????| +----------+-------------+------+-----+---------+----------------+|?ID???????|?int(11)?????|?NO???|?PRI?|?NULL????|?auto_increment?| |?NAME?????|?varchar(20)?|?NO???|?????|?NULL????|????????????????| |?AGE??????|?int(11)?????|?NO???|?????|?NULL????|????????????????| |?BIRTHDAY?|?datetime????|?YES??|?????|?NULL????|????????????????| |?star?????|?int(11)?????|?YES??|?????|?NULL????|????????????????| +----------+-------------+------+-----+---------+----------------+ 5?rows?in?set?(0.00?sec)

2.4.3 delete 刪除表（列）字段

mysql>?alter?table?PEOPLE?DROP?column?star;Query?OK,?0?rows?affected?(0.02?sec)Records:?0??Duplicates:?0??Warnings:?0

刪除后，再次查看PEOPLE表結構：

mysql>?desc?PEOPLE; +----------+-------------+------+-----+---------+----------------+|?Field????|?Type????????|?Null?|?Key?|?Default?|?Extra??????????| +----------+-------------+------+-----+---------+----------------+|?ID???????|?int(11)?????|?NO???|?PRI?|?NULL????|?auto_increment?| |?NAME?????|?varchar(20)?|?NO???|?????|?NULL????|????????????????| |?AGE??????|?int(11)?????|?NO???|?????|?NULL????|????????????????| |?BIRTHDAY?|?datetime????|?YES??|?????|?NULL????|????????????????| +----------+-------------+------+-----+---------+----------------+ 4?rows?in?set?(0.00?sec)

刪除字段成功，現在我們已經不能看到star的字段了。

2.4.4 rename 重命名表名

mysql>?RENAME?TABLE?PEOPLE?TO?NEW_PEOPLE; Query?OK,?0?rows?affected?(0.00?sec)

2.4.5 null or not null?
修改表字段允許為空或不允許為空：

mysql>?ALTER?TABLE?PEOPLE?MODIFY?AGE?INT(3)?NULL; Query?OK,?0?rows?affected?(0.01?sec)Records:?0??Duplicates:?0??Warnings:?0

把 PEOPLE 表的 AGE 字段設置成“允許為空”，即插入記錄時這個字段可以不錄入。否則相反。?
它的格式為：ALTER TABLE ?MODIFY

mysql>?create?table?newTable?select?*?from?PEOPLE; Query?OK,?0?rows?affected?(0.01?sec) Records:?0??Duplicates:?0??Warnings:?0

我們查看一下目前數據庫存在的表：

mysql>?show?tables; +------------------+|?Tables_in_testdb?| +------------------+|?PEOPLE???????????||?newTable?????????| +------------------+2?rows?in?set?(0.00?sec)

3、數據的操作及管理

本操作，包含增、刪、改、查數據。
以下命令均在PEOPLE表上操作。

3.1 增加數據（增）
PEOPLE表目前是沒有數據的，它是空的數據表，我們現在先添加一些數據。
insert into 命令添加數據：

mysql>?insert?into?PEOPLE?VALUES?(null,?'Anny',?22,?'1992-05-22'); Query?OK,?1?row?affected?(0.00?sec)

使用select命令查看表（會在后面介紹），現在我們查看PEOPLE數據表的數據：

mysql>?select?*?from?PEOPLE; +----+------+-----+---------------------+|?ID?|?NAME?|?AGE?|?BIRTHDAY????????????| +----+------+-----+---------------------+|??1?|?Anny?|??22?|?1992-05-22?00:00:00?| +----+------+-----+---------------------+1?row?in?set?(0.00?sec)

數據表現在有一條數據。

我們多添加幾條數據，如：

mysql>?select?*?from?PEOPLE; +----+--------+-----+---------------------+|?ID?|?NAME???|?AGE?|?BIRTHDAY????????????| +----+--------+-----+---------------------+|??1?|?Anny???|??22?|?1992-05-22?00:00:00?| |??2?|?Garvey?|??23?|?1991-05-22?00:00:00?| |??3?|?Lisa???|??25?|?1989-05-22?00:00:00?| |??4?|?Nick???|??24?|?1990-05-22?00:00:00?||??5?|?Rick???|??24?|?1991-05-22?00:00:00?| +----+--------+-----+---------------------+5?rows?in?set?(0.00?sec)

3.2 刪除數據（刪）

delete 命令刪除數據：

mysql>?delete?from?PEOPLE?where?name?=?'Lisa'; Query?OK,?1?row?affected?(0.01?sec)

再次查詢PEOPLE表：

mysql>?select?*?from?PEOPLE; +----+--------+-----+---------------------+|?ID?|?NAME???|?AGE?|?BIRTHDAY????????????| +----+--------+-----+---------------------+|??1?|?Anny???|??22?|?1992-05-22?00:00:00?| |??2?|?Garvey?|??23?|?1991-05-22?00:00:00?| |??4?|?Nick???|??24?|?1990-05-22?00:00:00?||??5?|?Rick???|??24?|?1991-05-22?00:00:00?| +----+--------+-----+---------------------+4?rows?in?set?(0.00?sec)

已經看不到名為“Lisa”的數據了。

3.3 修改數據（改）

update 命令修改數據：

mysql>?update?PEOPLE?set?name='Calvin'?where?name?=?'Garvey'; Query?OK,?1?row?affected?(0.00?sec)Rows?matched:?1??Changed:?1??Warnings:?0

查詢PEOPLE表內容：

mysql>?select?*?from?PEOPLE; +----+--------+-----+---------------------+|?ID?|?NAME???|?AGE?|?BIRTHDAY????????????| +----+--------+-----+---------------------+|??1?|?Anny???|??22?|?1992-05-22?00:00:00?| |??2?|?Calvin?|??23?|?1991-05-22?00:00:00?| |??4?|?Nick???|??24?|?1990-05-22?00:00:00?||??5?|?Rick???|??24?|?1991-05-22?00:00:00?| +----+--------+-----+---------------------+4?rows?in?set?(0.00?sec)

名為“Garvey”的記錄已經修改為“Calvin”。

3.4 查詢數據（查）

select 命令查詢數據，最簡單的就是查詢表的所有數據，也就是我們最初使用到的那條命令：

mysql>?select?*?from?PEOPLE; +----+--------+-----+---------------------+|?ID?|?NAME???|?AGE?|?BIRTHDAY????????????| +----+--------+-----+---------------------+|??1?|?Anny???|??22?|?1992-05-22?00:00:00?| |??2?|?Calvin?|??23?|?1991-05-22?00:00:00?| |??4?|?Nick???|??24?|?1990-05-22?00:00:00?||??5?|?Rick???|??24?|?1991-05-22?00:00:00?| +----+--------+-----+---------------------+4?rows?in?set?(0.00?sec)

格式：select * from ，*代表所有字段。

查詢數據時也可指定顯示的（列）字段：

mysql>?select?NAME,?AGE,?BIRTHDAY?from?PEOPLE; +--------+-----+---------------------+|?NAME???|?AGE?|?BIRTHDAY????????????| +--------+-----+---------------------+|?Anny???|??22?|?1992-05-22?00:00:00?| |?Calvin?|??23?|?1991-05-22?00:00:00?| |?Nick???|??24?|?1990-05-22?00:00:00?||?Rick???|??24?|?1991-05-22?00:00:00?| +--------+-----+---------------------+4?rows?in?set?(0.00?sec)

格式：select from 。

select查詢命令還有很多的高級用法，比如用來查找不重復（distinct）的數據，使數據按條件排序（order by），按查詢條件顯示數據（where）等等。這些都會在下一篇文章作重點介紹，請大家繼續留意我的博客，謝謝。

4、管理視圖

4.1 創建視圖

視圖是從數據庫里導出一個或多個表的虛擬表，是用來方便用戶對數據的操作。

mysql>?CREATE?VIEW?PEOPLE_VIEW?( ????->?NAME,?AGE) ????->?AS?SELECT?NAME,?AGE?FROM?PEOPLE;

創建成功后查看視圖。

PEOPLE??????????PEOPLE.AGE??????PEOPLE.BIRTHDAY?PEOPLE.ID???????PEOPLE.NAME????mysql>?SELECT?*?FROM?PEOPLE_VIEW ????->?;+--------+-----+ |?NAME???|?AGE?| +--------+-----+ |?Anny???|??22?| |?Calvin?|??23?| |?Nick???|??24?| |?Rick???|??24?| +--------+-----+4?rows?in?set?(0.00?sec)

我們也可以使用 DESC 命令查看視圖的結構。

mysql>?DESC?PEOPLE_VIEW; +-------+---------+------+-----+---------+-------+|?Field?|?Type????|?Null?|?Key?|?Default?|?Extra?| +-------+---------+------+-----+---------+-------+|?ID????|?int(11)?|?NO???|?????|?0???????|???????| +-------+---------+------+-----+---------+-------+1?row?in?set?(0.01?sec)

4.2 替換視圖

創建或替換原有視圖。

mysql>?CREATE?OR?REPLACE?VIEW?PEOPLE_VIEW(PEOPLE_ID,PEOPLE_NAME,PEOPLE_AGE)?AS?SELECT?ID,NAME,AGE?FROM?PEOPLE; Query?OK,?0?rows?affected?(0.00?sec)

創建或替換后查看視圖。

mysql>?SELECT?*?FROM?PEOPLE_VIEW; +-----------+-------------+------------+|?PEOPLE_ID?|?PEOPLE_NAME?|?PEOPLE_AGE?| +-----------+-------------+------------+|?????????1?|?Anny????????|?????????22?| |?????????2?|?Calvin??????|?????????23?| |?????????4?|?Nick????????|?????????24?||?????????5?|?Rick????????|?????????24?| +-----------+-------------+------------+4?rows?in?set?(0.00?sec)

4.3 操作視圖

當視圖數據有變化時（增、刪、改），真實的表數據也會隨著改變。也就是說，對視圖的操作就是對表的數據，所以我們可以把視圖當作表。

例：往視圖插入一條數據。

mysql>?INSERT?INTO?PEOPLE_VIEW?VALUES(NULL,?'Kerry',?'33'); Query?OK,?1?row?affected?(0.00?sec)

插入數據成功后查看視圖。

mysql>?SELECT?*?FROM?PEOPLE_VIEW?; +-----------+-------------+------------+|?PEOPLE_ID?|?PEOPLE_NAME?|?PEOPLE_AGE?| +-----------+-------------+------------+|?????????1?|?Anny????????|?????????22?| |?????????2?|?Calvin??????|?????????23?| |?????????4?|?Nick????????|?????????24?| |?????????5?|?Rick????????|?????????24?||?????????6?|?Kerry???????|?????????33?| +-----------+-------------+------------+5?rows?in?set?(0.00?sec)

可以在視圖上看到我們剛剛插入的數據，現在我們就來驗證一下真實的表是否也會作出變化。

mysql>?SELECT?*?FROM?PEOPLE; +----+--------+-----+---------------------+|?ID?|?NAME???|?AGE?|?BIRTHDAY????????????| +----+--------+-----+---------------------+|??1?|?Anny???|??22?|?1992-05-22?00:00:00?| |??2?|?Calvin?|??23?|?1991-05-22?00:00:00?| |??4?|?Nick???|??24?|?1990-05-22?00:00:00?| |??5?|?Rick???|??24?|?1991-05-22?00:00:00?||??6?|?Kerry??|??33?|?NULL????????????????| +----+--------+-----+---------------------+5?rows?in?set?(0.00?sec)

可見，真實的表數據也已經有所改變，剛剛往視圖里插入的那一條數據存在于真實表中，真理便是：對視圖的操作就是對表的數據。

4.4 刪除視圖

mysql>?DROP?VIEW?PEOPLE_VIEW;Query?OK,?0?rows?affected?(0.00?sec)

相關推薦：

mac使用終端運行mysql,mysql終端,mysql?mac,mysql目錄,mysql路徑，macmysql

mac使用終端運行mysql,mysql終端,mysql?mac,mysql目錄,mysql路徑，macmysql

? 版權聲明
THE END
數據庫
# mysql
喜歡就支持一下吧
點贊5 分享