Mysql安裝方式
MySQL安裝方式有三種,一種是RPM方式安裝,一種是原始碼編譯安裝,一種是通用二進位格式套件安裝。
RPM方式安裝,可以直接使用yum指令安裝或下載RPM套件後再安裝
yum安裝指令:
yum -y install mysql-server
系統會自動解決依賴關係,並將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)
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)
查看表結構:
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)
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)
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)
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)
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 條件
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)
选择:指定以某字段作为搜索码,做逻辑比较,筛选符合条件的行。WHERE指定选择条件 投影:指定以某字段作为搜索码,做逻辑比较,筛选符合条件的字段。 mysql> SELECT Name,Course FROM students WHERE Gender='M'; +---------------+---------+ | Name | Course | +---------------+---------+ | ZhangSan | NULL | | ZengChengpeng | Develop | +---------------+---------+
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)!