欧美97色伦欧美一区二区日韩,国产福利片在线观看,freexxx性欧美vide0高清,西西亚洲,日本欧美国产精品第一页久久,成人18免费软件

當前位置： 維易PHP培訓學院 > MYSQL教程 > 內(nèi)容正文

Mysql入門MySQL之終端Terminal（dos界面）管理數(shù)據(jù)庫、數(shù)據(jù)表、數(shù)據(jù)的基本操作

作者：VEPHP   時間 2017-10-27

《Mysql入門MySQL之終端Terminal（dos界面）管理數(shù)據(jù)庫、數(shù)據(jù)表、數(shù)據(jù)的基本操作》要點：
本文介紹了Mysql入門MySQL之終端Terminal（dos界面）管理數(shù)據(jù)庫、數(shù)據(jù)表、數(shù)據(jù)的基本操作，希望對您有用。如果有疑問，可以聯(lián)系我們。

MySQL有很多的可視化管理工具,比如“mysql-workbench”和“sequel-pro-”. 現(xiàn)在我寫MySQL的終端命令操作的文章,是想強化一下自己對于MySQL的理解,總會比使用圖形化的理解透徹,因為我本來就比較喜歡寫代碼.同時寫出來這些文章,是想要給大家當個參考,希望也能對大家有所幫助,有所提升,這就是我為什么要寫終端操作MySQL的文章了.MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

注意：MySQL數(shù)據(jù)庫命令不區(qū)分大小寫.但在MAC的終端,如果你想使用tab自動補全命令,那么你就必須使用大寫,這樣MAC的終端才會幫你補全命令,否則你按N遍tab都不會有響應.MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

1、數(shù)據(jù)庫(database)管理MYSQL數(shù)據(jù)庫

1.1 create 創(chuàng)建數(shù)據(jù)庫MYSQL數(shù)據(jù)庫

1.2 show 查看所有數(shù)據(jù)庫MYSQL數(shù)據(jù)庫

1.3 alter 修改數(shù)據(jù)庫MYSQL數(shù)據(jù)庫

1.4 use 使用數(shù)據(jù)庫MYSQL數(shù)據(jù)庫

1.5 查看當前使用的數(shù)據(jù)庫MYSQL數(shù)據(jù)庫

1.6 drop 刪除數(shù)據(jù)庫MYSQL數(shù)據(jù)庫

2、數(shù)據(jù)表(table)管理MYSQL數(shù)據(jù)庫

2.1?create?創(chuàng)建表MYSQL數(shù)據(jù)庫

2.2?show 顯示表MYSQL數(shù)據(jù)庫

2.3 desc 查看表結構MYSQL數(shù)據(jù)庫

2.4 alter 修改表結構(增、刪、改)MYSQL數(shù)據(jù)庫

2.4.1 insert 在表中添加列(字段)MYSQL數(shù)據(jù)庫

2.4.2 alter 修改表(列)字段MYSQL數(shù)據(jù)庫

2.4.3 delete 刪除表(列)字段MYSQL數(shù)據(jù)庫

2.4.4 rename 重命名表名MYSQL數(shù)據(jù)庫

2.5 create 利用已有數(shù)據(jù)創(chuàng)建新表MYSQL數(shù)據(jù)庫

3、數(shù)據(jù)的操作及管理MYSQL數(shù)據(jù)庫

3.1 增加數(shù)據(jù)(增)MYSQL數(shù)據(jù)庫

3.2 刪除數(shù)據(jù)(刪)MYSQL數(shù)據(jù)庫

3.3 修改數(shù)據(jù)(改)MYSQL數(shù)據(jù)庫

3.4 查詢數(shù)據(jù)(查)MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

1、數(shù)據(jù)庫(database)管理MYSQL數(shù)據(jù)庫

1.1 create 創(chuàng)建數(shù)據(jù)庫MYSQL數(shù)據(jù)庫

create database firstDB;

?MYSQL數(shù)據(jù)庫

1.2 show 查看所有數(shù)據(jù)庫MYSQL數(shù)據(jù)庫

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

?MYSQL數(shù)據(jù)庫

1.3 alter 修改數(shù)據(jù)庫MYSQL數(shù)據(jù)庫

alter 命令修改數(shù)據(jù)庫編碼：MYSQL數(shù)據(jù)庫

默認創(chuàng)建的數(shù)據(jù)庫默認不支持中文字符,如果我們需要它支持中文字符,則將它的編碼設置為utf8格式：MYSQL數(shù)據(jù)庫

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

?MYSQL數(shù)據(jù)庫

1.4 use 使用數(shù)據(jù)庫MYSQL數(shù)據(jù)庫

mysql> use firstDB;
Database changed

?MYSQL數(shù)據(jù)庫

1.5 查看當前使用的數(shù)據(jù)庫MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

1.6 drop 刪除數(shù)據(jù)庫MYSQL數(shù)據(jù)庫

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

?MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

2、數(shù)據(jù)表(table)管理MYSQL數(shù)據(jù)庫

我們首先創(chuàng)建一個數(shù)據(jù)庫,提供我們往后的使用：MYSQL數(shù)據(jù)庫

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

創(chuàng)建跋文得用use命令進入(使用)數(shù)據(jù)庫,不然后面的操作都會不成功的.MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

2.1?create?創(chuàng)建表MYSQL數(shù)據(jù)庫

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)

?MYSQL數(shù)據(jù)庫

2.2 show 顯示表MYSQL數(shù)據(jù)庫

顯示當前數(shù)據(jù)庫所有的數(shù)據(jù)表MYSQL數(shù)據(jù)庫

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

?MYSQL數(shù)據(jù)庫

2.3 desc 查看表結構MYSQL數(shù)據(jù)庫

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)

?MYSQL數(shù)據(jù)庫

2.4 alter 修改表結構(增、刪、改)MYSQL數(shù)據(jù)庫

默認創(chuàng)建的表不支持中文字符,所以需將表編碼設置為utf8：
MYSQL數(shù)據(jù)庫

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

?MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

2.4.1 insert 在表中添加列(字段)MYSQL數(shù)據(jù)庫

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

提示：在MySQL里,布爾類型會自動轉換為tinyint(1)類型.MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

我們不妨使用desc去查看一下PEOPLE表結構：MYSQL數(shù)據(jù)庫

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)

現(xiàn)在,你該相信我了吧?MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

2.4.2 alter 修改表(列)字段MYSQL數(shù)據(jù)庫

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

?MYSQL數(shù)據(jù)庫

我們再次使用desc查看PEOPLE表結構：MYSQL數(shù)據(jù)庫

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)

?MYSQL數(shù)據(jù)庫

2.4.3 delete 刪除表(列)字段MYSQL數(shù)據(jù)庫

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

?MYSQL數(shù)據(jù)庫

刪除后,再次查看PEOPLE表結構：MYSQL數(shù)據(jù)庫

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)

刪除字段成功,現(xiàn)在我們已經(jīng)不能看到star的字段了.MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

2.4.4 rename 重命名表名MYSQL數(shù)據(jù)庫

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

?MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

2.5 create 利用已有數(shù)據(jù)創(chuàng)建新表MYSQL數(shù)據(jù)庫

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

?MYSQL數(shù)據(jù)庫

我們查看一下目前數(shù)據(jù)庫存在的表：MYSQL數(shù)據(jù)庫

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

?MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

3、數(shù)據(jù)的操作及管理MYSQL數(shù)據(jù)庫

數(shù)據(jù)表的基本操作,包含增、刪、改、查數(shù)據(jù).MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

以下命令均在PEOPLE表上操作.MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

3.1 增加數(shù)據(jù)(增)MYSQL數(shù)據(jù)庫

PEOPLE表目前是沒有數(shù)據(jù)的,它是空的數(shù)據(jù)表,我們現(xiàn)在先添加一些數(shù)據(jù).MYSQL數(shù)據(jù)庫

insert into 命令添加數(shù)據(jù)：MYSQL數(shù)據(jù)庫

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

?MYSQL數(shù)據(jù)庫

使用select命令查看表(會在后面介紹),現(xiàn)在我們查看PEOPLE數(shù)據(jù)表的數(shù)據(jù)：MYSQL數(shù)據(jù)庫

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

數(shù)據(jù)表現(xiàn)在有一條數(shù)據(jù).MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

我們多添加幾條數(shù)據(jù),如：MYSQL數(shù)據(jù)庫

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)

?MYSQL數(shù)據(jù)庫

3.2 刪除數(shù)據(jù)(刪)MYSQL數(shù)據(jù)庫

delete 命令刪除數(shù)據(jù)：MYSQL數(shù)據(jù)庫

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

?MYSQL數(shù)據(jù)庫

再次查詢PEOPLE表：MYSQL數(shù)據(jù)庫

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)

已經(jīng)看不到名為“Lisa”的數(shù)據(jù)了.MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

3.3 修改數(shù)據(jù)(改)MYSQL數(shù)據(jù)庫

update 命令修改數(shù)據(jù)：MYSQL數(shù)據(jù)庫

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

?MYSQL數(shù)據(jù)庫

查詢PEOPLE表內(nèi)容：MYSQL數(shù)據(jù)庫

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”的記錄已經(jīng)修改為“Calvin”.MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

3.4 查詢數(shù)據(jù)(查)MYSQL數(shù)據(jù)庫

select 命令查詢數(shù)據(jù),最簡單的就是查詢表的所有數(shù)據(jù),也就是我們最初使用到的那條命令：MYSQL數(shù)據(jù)庫

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數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

查詢數(shù)據(jù)時也可指定顯示的(列)字段：MYSQL數(shù)據(jù)庫

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 <表名>.MYSQL數(shù)據(jù)庫

?MYSQL數(shù)據(jù)庫

select查詢命令還有很多的高級用法,比如用來查找不重復(distinct)的數(shù)據(jù),使數(shù)據(jù)按條件排序(order by),按查詢條件顯示數(shù)據(jù)(where)等等.這些都會在下一篇文章作重點介紹,請大家繼續(xù)把穩(wěn)我的博客,謝謝.MYSQL數(shù)據(jù)庫

維易PHP培訓學院每天發(fā)布《Mysql入門MySQL之終端Terminal（dos界面）管理數(shù)據(jù)庫、數(shù)據(jù)表、數(shù)據(jù)的基本操作》等實戰(zhàn)技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養(yǎng)人才。

轉載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/13678.html


    標簽：