首頁  >  文章  >  資料庫  >  MySQL基本指令

MySQL基本指令

黄舟
黄舟原創
2017-01-16 13:27:091056瀏覽

Mysql安裝方式
MySQL安裝方式有三種,一種是RPM方式安裝,一種是原始碼編譯安裝,一種是通用二進位格式套件安裝。

RPM方式安裝,可以直接使用yum指令安裝或下載RPM套件後再安裝

yum安裝指令:
yum -y install mysql-server
系統會自動解決依賴關係,並將mysql客戶端也安裝上。

mysql互動模式中的命令類別:
客戶端命令:在客戶端執行的命令

伺服器命令:在伺服器上執行,並將結果傳回給客戶端。必須使用語句結束符,預設為封號「;」

MySQL資料庫:

MySQL基本指令

mysql安裝完成後,預設有3個資料庫
information_schema:是mysql運行過程中位於記憶體運行時數據,只有mysql啟動時才有數據,平常是空的。

test:測試時才用到的資料庫

mysql:mysql 的資料庫

MySQL資料庫目錄:

[root@host2 ~]# ls /var/lib/mysql/
ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.sock  test

可以看到沒有information_schema資料庫,因為它是存在記憶體中的。

MySQL常用指令:

DDL:定義資料庫物件:
MySQL常用指令:

DDL:定義資料庫物件:
CREATE:

ALTER:

DROP:

DML:資料操弄

REVOKE:

建立資料庫:
CREATE DATABASE db_name;

CREATE DATABASES IF NOT EXISTS testdb; 

mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| test_db            |
+--------------------+
4 rows in set (0.01 sec)

(col1,col2,. ..) col1,col2為欄位名稱

注意:表格是依賴資料庫的,所以在建立表格之前,一定要指定預設資料庫,使用指令
USER DATABASE; 來指定資料庫

建立一個表,名為student,包含三個字段,Name、Age、Gender

mysql> DROP DATABASE test_db;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

查看某個資料庫中的表:
SHOW TABLES FROM db_name;

查看表結構:
DESC table_name;

mysql> CREATE TABLE students(Name CHAR(20) NOT NULL, Age TINYINT UNSIGNED,Gender CHAR(1) NOT NULL);
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| students          |
+-------------------+
1 row in set (0.00 sec)

tb案例:
DESC table_name;修改表:
ALTER TABLE tb_name;

MODIFY:修改某個字段,修改字段屬性,字段名不改

CHANGE:改變某個字段,改變字段名稱

ADD:添加字段

DR
可以透過help指令查詢來幫助資訊:

help CREATE TABLE;

增加一個欄位
例如:修改前面建立的student表,增加一個欄位課程course,

mysql> DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name   | char(20)            | NO   |     | NULL    |       |
| Age    | tinyint(3) unsigned | YES  |     | NULL    |       |
| Gender | char(1)             | NO   |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

修改欄位名稱,例如將上面新增的course欄位名改為Course
mysql> ALTER TABLE students ADD course VARCHAR(100);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name   | char(20)            | NO   |     | NULL    |       |
| Age    | tinyint(3) unsigned | YES  |     | NULL    |       |
| Gender | char(1)             | NO   |     | NULL    |       |
| course | varchar(100)        | YES  |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

刪除一個字段:
DROP col_name;

插入數據: 
mysql> ALTER TABLE students CHANGE course Course VARCHAR(100);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name   | char(20)            | NO   |     | NULL    |       |
| Age    | tinyint(3) unsigned | YES  |     | NULL    |       |
| Gender | char(1)             | NO   |     | NULL    |       |
| Course | varchar(100)        | YES  |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


上面命令意思是:往某個表中插入數據,字段名為col1,col2,... 插入的值為字串則,用引號引起來,如果為數值,則直接用數字。如果每個欄位都給值,則不用寫欄位名稱

例如:在students表中插入兩個數據,張三和李四

INSERT  INTO  tb_name (col1,col2,...) VALUES|VALUE ('STRING',NUM...);

查看資料

mysql> INSERT INTO students (Name,Gender) VALUE ('ZhangSan','M'),('LiSi','F');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

插入一個用戶,所有欄位都有值:
注意,所有欄位都有值,就不需要指定欄位名稱
mysql> SELECT * FROM students;
+----------+------+--------+--------+
| Name     | Age  | Gender | Course |
+----------+------+--------+--------+
| ZhangSan | NULL | M      | NULL   |
| LiSi     | NULL | F      | NULL   |
+----------+------+--------+--------+
2 rows in set (0.01 sec)
修改資料:



UPDATE tb_name SET column=value WHERE 條件

例如:將ZengChengpeng的Course課程名稱改為De SELETE語句:

selete語句分為兩種情況,
mysql> INSERT INTO students VALUES ('ZengChengpeng',28,'M','IT');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM students WHERE Name='ZengChengpeng';
+---------------+------+--------+--------+
| Name          | Age  | Gender | Course |
+---------------+------+--------+--------+
| ZengChengpeng |   28 | M      | IT     |
+---------------+------+--------+--------+
1 row in set (0.00 sec)
刪除資料:

DELECT FROM tb_name WHERE 條件;

建立使用者:

CREATE USER 'USERNAs@'HOST' IDENT@'HOST' IDENTW BYT BY :

DROP USER 'USERNAME'@'HOST';




HOST:

IP:

HOSTNAME:

NETWORK: 172.16.0._

%:匹配任意字元:

jerry@'%' 

使用者權限:
新增權限:
GRANT pri1,pri2,... ON DB_NAME.TB_NME TO 'TB_NME' [IDENTIFIED BY 'PASSWORD'];
pri1 pri2表示權限名稱,所有權限用ALL PRIVILEGES表示
取消權限:
REVOKE pri1,pri2,...ON DB_NAME.TB_NAME FROM 'USERNAME'@'HOST';使用者範例:

mysql> UPDATE students SET Course='Develop' WHERE Name='ZengChengpeng';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM students WHERE Name='ZengChengpeng';
+---------------+------+--------+---------+
| Name          | Age  | Gender | Course  |
+---------------+------+--------+---------+
| ZengChengpeng |   28 | M      | Develop |
+---------------+------+--------+---------+
1 row in set (0.00 sec)

查看使用者授權:
SHOW GRANTS FOR 'USERNAME'@'HOST';
选择:指定以某字段作为搜索码,做逻辑比较,筛选符合条件的行。WHERE指定选择条件
    
投影:指定以某字段作为搜索码,做逻辑比较,筛选符合条件的字段。
mysql> SELECT Name,Course FROM students WHERE Gender='M';   
+---------------+---------+
| Name          | Course  |
+---------------+---------+
| ZhangSan      | NULL    |
| ZengChengpeng | Develop |
+---------------+---------+


範例:給jerry使用者test_db這個資料庫所有資料表的所有權限
mysql> GRANT ALL PRIVILEGES ON test_db.* TO 'jerry'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'jerry'@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for jerry@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jerry'@'%' IDENTIFIED BY PASSWORD '*09FB9E6E2AA0750E9D8A8D22B6AA8D86C85BF3D0' |
| GRANT ALL PRIVILEGES ON `test_db`.* TO 'jerry'@'%'                                                   |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
取消所有权限:
mysql> REVOKE ALL PRIVILEGES ON test_db.* FROM jerry@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'jerry'@'%';                      
+------------------------------------------------------------------------------------------------------+
| Grants for jerry@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jerry'@'%' IDENTIFIED BY PASSWORD '*09FB9E6E2AA0750E9D8A8D22B6AA8D86C85BF3D0' |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

以上就是MySQL基本命令的内容,更多相关内容请关注PHP中文网(www.php.cn)!

陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
上一篇:mysql 基本操作下一篇:mysql 基本操作