打開終端輸入如下命令:
/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)
相關推薦: