mysql安裝方式
mysql安裝方式有三種，一種是rpm方式安裝，一種是源碼編譯安裝，一種是通用二進制格式包安裝。
rpm方式安裝，可以直接使用yum命令安裝或者下載rpm包后再安裝
yum安裝命令：
yum -y install mysql-server
系統會自動解決依賴關系，并將mysql客戶端也安裝上。
mysql交互模式中的命令類別：
客戶端命令：在客戶端執行的命令
服務器命令：在服務器上執行，并將結果返回給客戶端。必須使用語句結束符，默認為封號“;”
mysql數據庫：
mysql安裝完成后，默認有3個數據庫
information_schema：是mysql運行過程中位于內存中的信息，保存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：定義數據庫對象：
CREATE：
ALTER：
DROP：
DML：數據操縱語言
INSERT
UPDATE
DELETE
DCL數據控制語言
GRANT：
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)
刪除數據庫：
DROP DATABASE db_name;
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)
創建表：
CREATE TABLE tb_name(col1,col2,…) col1,col2為字段名
注意：表是依賴于數據庫的，所以在創建表之前，一定要指定默認數據庫，使用命令
USER DATABASE; 來指定數據庫
創建一個表，名為student，包含三個字段，Name、Age、Gender
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)
查看某個數據庫中的表：
SHOW TABLES FROM db_name;
查看表結構：
DESC table_name;
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)
刪除表：
DROP TABLE tb_name;
修改表：
ALTER TABLE tb_name;
MODIFY：修改某個字段，修改字段屬性，字段名不改
CHANGE：改變某個字段，改變字段名稱
ADD：添加字段
DROP：刪除字段
可以通過help命令查詢幫助信息：
help CREATE TABLE;
增加一個字段
例如：修改前面創建的student表，增加一個字段課程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)
修改字段名稱，例如將上面添加的course字段名改為Course
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)
刪除一個字段：
DROP col_name;
插入數據：?
INSERT??INTO??tb_name?(col1,col2,...)?VALUES|VALUE?('STRING',NUM...);
上面命令意思是：往某個表中插入數據，字段名為col1，col2,… 插入的值為字符串則，用引號引起來，如果為數值，則直接用數字。如果每一個字段都給值，則不用寫字段名稱
例如：在students表中插入兩條數據，張三和李四
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)
插入一個用戶，所有字段都有值：
注意，所有字段都有值，就不需要指定字段名
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)
修改數據：
UPDATE tb_name SET column=value WHERE 條件
例如：將ZengChengpeng的Course課程名稱改為Develop
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)
SELETE語句：
selete語句分為兩種情況，
選擇：指定以某字段作為搜索碼，做邏輯比較，篩選符合條件的行。WHERE指定選擇條件 ???? 投影：指定以某字段作為搜索碼，做邏輯比較，篩選符合條件的字段。 mysql>?SELECT?Name,Course?FROM?students?WHERE?Gender='M';??? +---------------+---------+ |?Name??????????|?Course??| +---------------+---------+ |?ZhangSan??????|?NULL????| |?ZengChengpeng?|?Develop?| +---------------+---------+
刪除數據：
DELECT FROM tb_name WHERE 條件；
創建用戶：
CREATE USER ‘USERNAME’@’HOST’ IDENTIFIED BY ‘PASSWORD’;?
刪除用戶：
DROP USER ‘USERNAME’@’HOST’;
HOST:
IP:
HOSTNAME:
NETWORK:
通配符: 通配符用引號引起來
_:下劃線匹配任意單個字符：例如172.16.0._
%：匹配任意字符：
jerry@’%’?
用戶權限：
添加權限：
GRANT pri1,pri2,… ON DB_NAME.TB_NAME TO ‘USERNAME’@’HOST’ [IDENTIFIED BY ‘PASSWORD’];
pri1 pri2表示權限名稱，所有權限用ALL PRIVILEGES表示
取消權限：
REVOKE pri1，pri2,… ON DB_NAME.TB_NAME FROM ‘USERNAME’@’HOST’;
創建用戶示例：
mysql>?CREATE?USER?'jerry'@'%'?IDENTIFIED?BY?'jerry';
查看用戶授權：
SHOW GRANTS FOR ‘USERNAME’@’HOST’;
mysql>?SHOW?GRANTS?FOR?jerry@'%'; +------------------------------------------------------------------------------------------------------+ |?Grants?for?jerry@%???????????????????????????????????????????????????????????????????????????????????| +------------------------------------------------------------------------------------------------------+ |?GRANT?USAGE?ON?*.*?TO?'jerry'@'%'?IDENTIFIED?BY?PASSWORD?'*09FB9E6E2AA0750E9D8A8D22B6AA8D86C85BF3D0'?| +------------------------------------------------------------------------------------------------------+
示例：給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）！
