mysql基本操作

標籤：mysql   學習   database   

1.   登陸與退出

（1）       登陸

mysql –u使用者名稱–p密碼

此命令密碼是明文

mysql –u使用者名稱–p

Enter Password

登陸的同時指明主機號：

mysql –h主機名稱–u使用者名稱 –p密碼

登陸的同時指明連接埠號碼

mysql –h主機名稱–u使用者名稱 –p密碼–P連接埠號碼（3306）

登陸的同時修改命令提示字元：

mysql –h主機名稱–u使用者名稱 –p密碼–P連接埠號碼（3306）--prompt=

命令提示字元包括：

\u目前使用者名

\h當前主機名稱

\D當前日期時間

\d當前開啟的資料庫

也可以在登陸後，修改命令提示字元：

Promptkaikeba>

命令提示字元改為kaikeba>

登陸的同時修改命令分隔字元：

mysql –h主機名稱–u使用者名稱 –p密碼–P連接埠號碼（3306）--prompt=  --delimiter=

也可以再登陸之後修改命令分隔字元

Delimiter @

命令結束符變為：@

在登陸的同時開啟資料庫：

Mysql -u使用者名稱 -p密碼 -h主機名稱 -D資料庫名

Eg:

Mysql -uroot-proot -hlocalhost -Dtest;

（2）       退出

Exit

\q

Quit

Ctrl+C快速鍵

（3）       SQL規範

資料庫名、表名小寫；關鍵字、保留字、函數名稱大寫；

SQL命令支援折行操作，但不能將單詞、名稱、或成對引號折行寫

當名稱與保留字衝突時，需用‘’括上名稱

（4）       開啟輸出日誌的形式

\T 檔案儲存位置及檔案名稱

\t 結束輸出日誌

按上下鍵調查之前寫過的命令

2.   DDL操作

（1）       建立資料庫

CREATE{DATABASE|SCHEMA} db_name;

CREATE{DATABASE|SCHEMA} [IF NOT EXISTS] db_name;

CREATE{DATABASE|SCHEMA} [IF NOT EXISTS] db_name [[DEFAULT] CHARACTER SET [=] 字元集];

（2）       查看當前伺服器下已有的資料庫

SHOW{DATABASES|SCHEMA};

（3）       查看上一步操作得到的警告

SHOW WARNINGS

（4）       查看已建立資料庫的編碼方式

SHOW CREATEDATABASE db_name;

綜合案例1：

mysql> CREATEDATABASE randongmei;

Query OK, 1 rowaffected (0.00 sec)

 

mysql> SHOWDATABASES;

+--------------------+

| Database           |

+--------------------+

|information_schema |

| mysql              |

|performance_schema |

|randongmei         |

| test               |

+--------------------+

5 rows in set(0.00 sec)

 

mysql> CREATESCHEMA king;

Query OK, 1 rowaffected (0.00 sec)

 

mysql> SHOWSCHEMAS;

+--------------------+

| Database           |

+--------------------+

|information_schema |

| king               |

| mysql              |

| performance_schema|

|randongmei         |

| test               |

+--------------------+

6 rows in set(0.00 sec)

 

mysql> CREATESCHEMA king;

ERROR 1007(HY000): Can‘t create database ‘king‘; database exists

mysql> CREATESCHEMA IF NOT EXISTS king;

Query OK, 1 rowaffected, 1 warning (0.00 sec)

 

mysql> SHOWWARNINGS;

+-------+------+-----------------------------------------------+

| Level | Code |Message                                      |

+-------+------+-----------------------------------------------+

| Note  | 1007 | Can‘t create database ‘king‘;database exists |

+-------+------+-----------------------------------------------+

1 row in set(0.00 sec)

 

mysql> SHOWDATABASES;

+--------------------+

| Database           |

+--------------------+

| information_schema|

| king               |

| mysql              |

|performance_schema |

|randongmei         |

| test               |

+--------------------+

6 rows in set(0.00 sec)

 

mysql> SHOWCREATE DATABASE randongmei;

+------------+---------------------------------------------------------------------+

| Database   | Create Database                                                    |

+------------+---------------------------------------------------------------------+

| randongmei |CREATE DATABASE `randongmei` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+------------+---------------------------------------------------------------------+

1 row in set(0.00 sec)

 

mysql> CREATEDATABASE IF NOT EXISTS test1 DEFAULT CHARACTER SET =‘gbk‘;

Query OK, 1 rowaffected (0.00 sec)

 

mysql> SHOWCREATE DATABASE test1

    -> ;

+----------+---------------------------------------------------------------+

| Database |Create Database                                              |

+----------+---------------------------------------------------------------+

| test1    | CREATE DATABASE `test1` /*!40100 DEFAULTCHARACTER SET gbk */ |

+----------+---------------------------------------------------------------+

1 row in set(0.00 sec)

 

mysql> CREATESCHEMA IF NOT EXISTS test3 CHARACTER SET ‘GBK‘;

Query OK, 1 rowaffected (0.00 sec)

 

mysql> \t

（5）       修改資料庫的編碼方式

ALTER{DATABASE|SCHEMA} db_name [[DEFAULT] CHARACTER SET [=] 字元集];

 

mysql> SHOWDATABASES;

+--------------------+

| Database           |

+--------------------+

|information_schema |

| king               |

| mysql              |

|performance_schema |

|randongmei         |

| test               |

| test1              |

| test3              |

+--------------------+

8 rows in set(0.02 sec)

 

mysql> SHOWCREATE DATABASE test1;

+----------+---------------------------------------------------------------+

| Database |Create Database                                              |

+----------+---------------------------------------------------------------+

| test1    | CREATE DATABASE `test1` /*!40100 DEFAULTCHARACTER SET gbk */ |

+----------+---------------------------------------------------------------+

1 row in set(0.00 sec)

 

mysql> ALTERDATABASE test1 DEFAULT CHARACTER SET ‘UTF8‘;

Query OK, 1 rowaffected (0.00 sec)

 

mysql> SHOWCREATE DATABASE test1;

+----------+----------------------------------------------------------------+

| Database |Create Database                                               |

+----------+----------------------------------------------------------------+

| test1    |CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+----------------------------------------------------------------+

1 row in set(0.00 sec)

 

mysql> SHOWCREATE SCHEMA test3;

+----------+---------------------------------------------------------------+

| Database |Create Database                                              |

+----------+---------------------------------------------------------------+

| test3    | CREATE DATABASE `test3` /*!40100 DEFAULTCHARACTER SET gbk */ |

+----------+---------------------------------------------------------------+

1 row in set(0.00 sec)

 

mysql> ALTERDATABASE test3 CHARACTER SET ‘UTF8‘;

Query OK, 1 rowaffected (0.00 sec)

 

mysql> SHOWCREATE DATABASE test3;

+----------+----------------------------------------------------------------+

| Database |Create Database                                               |

+----------+----------------------------------------------------------------+

| test3    | CREATE DATABASE `test3` /*!40100 DEFAULTCHARACTER SET utf8 */ |

+----------+----------------------------------------------------------------+

1 row in set(0.00 sec)

 

mysql> \t

（6）       開啟指定資料庫

USE db_name;

（7）       得到當前已經開啟的資料庫

SELECT{DATABASE()|SCHEMA()};

 

mysql> SHOWDATABASES;

+--------------------+

| Database           |

+--------------------+

|information_schema |

| king               |

| mysql              |

|performance_schema |

|randongmei         |

| test               |

| test1              |

| test3              |

+--------------------+

8 rows in set(0.00 sec)

 

mysql> USEtest1;

Database changed

mysql> SELECTDATABASE();

+------------+

| DATABASE() |

+------------+

| test1      |

+------------+

1 row in set(0.00 sec)

 

mysql> USErandongmei;

Database changed

mysql> SELECTSCHEMA();

+------------+

| SCHEMA()   |

+------------+

| randongmei |

+------------+

1 row in set(0.00 sec)

 

mysql> \t

（8）       刪除指定的資料庫

      DROP {DATABASE|SCHEMA} db_name;

      DROP {DATABASE|SCHEMA} [IF EXISTS] db_name;

      不可以一次刪除多個資料庫

mysql基本操作