開啟終端機輸入如下指令:
/usr/local/MySQL/bin/mysql -u root -p
其中root為使用者名稱。
這時會出現如下指令:
Enter password:
此時如果你沒有改密碼,直接敲回車子。否則,輸入你的密碼。
這樣就可以存取你的資料庫伺服器了。
資料表的基底
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)
我們先建立一個資料庫,提供我們往後的使用:
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)
#本操作,包含增、刪、改、查資料。
以下命令均在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 a26d98d33123a70024fa8ba5642906c6,*代表所有欄位。
查詢資料時也可指定顯示的(列)欄位:
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 bcc9d6e8e653c759263b053ce93e6814 from a26d98d33123a70024fa8ba5642906c6。
select查詢命令還有很多的高級用法,例如用來查找不重複(distinct)的數據,使數據按條件排序(order by),按查詢條件顯示數據(where)等等。這些都會在下一篇文章作重點介紹,請大家繼續留意我的博客,謝謝。
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路径
以上是Mac上實現終端管理MySQL資料庫的詳細內容。更多資訊請關注PHP中文網其他相關文章!