- 啟動(dòng) mysql 服務(wù)
- 命令行輸入
source ~/.bash_profile加載配置文件 - 命令行輸入
mysql -u root -p - 輸入 password
- 進(jìn)入數(shù)據(jù)庫;
數(shù)據(jù)庫操作語句
- 顯示全部數(shù)據(jù)庫
SHOW DATABASES; - 創(chuàng)建名為
new的數(shù)據(jù)庫CREATE DATABASE new; - 刪除名為
new的數(shù)據(jù)庫DROP DATABASE new; - 選擇
new數(shù)據(jù)庫USE new;
表操作語句
顯示當(dāng)前數(shù)據(jù)庫中存在什么表
SHOW TABLES;創(chuàng)建數(shù)據(jù)庫表
zhangyan：在mysql>后粘貼以下SQL語句剩盒，存儲(chǔ)引擎為MYISAM，字段id為主鍵、唯一索引。
CREATE TABLE `zhangyan` (
`id` INT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`username` VARCHAR( 20 ) NOT NULL ,
`password` CHAR( 32 ) NOT NULL ,
`time` DATETIME NOT NULL ,
`number` FLOAT( 10 ) NOT NULL ,
`content` TEXT NOT NULL ,
PRIMARY KEY ( `id` ) ) ENGINE = MYISAM ;
- 查看
zhangyan表結(jié)構(gòu)
DESCRIBE zhangyan;
- 從表中檢索信息
4.1 從zhangyan表中檢索所有記錄
SELECT * FROM zhangyan;
4.2 從zhangyan表中檢索特定的行：字段username等于abc趾撵，字段number等于1洋机，按字段id降序排列
SELECT * FROM zhangyan WHERE username = abc AND number=1 ORDER BY id DESC;
4.3 從zhangyan表中檢索指定的字段：username和password
SELECT username, password FROM zhangyan;
4.4 從zhangyan表中檢索出唯一的不重復(fù)記錄：SELECT DISTINCT username FROM zhangyan;
- 插入信息到
zhangyan表
INSERT INTO zhangyan (id, username, password, time, number, content) VALUES (, abc, 123456, 2007-08-06 14:32:12, 23.41, hello world);
更新
zhangyan表中的指定信息
UPDATE zhangyan SET content = hello china WHERE username = abc;刪除
zhangyan表中的指定信息
DELETE FROM zhangyan WHERE id = 1;
- 清空
zhangyan表
DELETE FROM zhangyan;
- 刪除
zhangyan表
DROP TABLE zhangyan;
- 更改表結(jié)構(gòu)，將
zhangyan表username字段的字段類型改為CHAR(25)
ALTER TABLE zhangyan CHANGE username username CHAR(25);
- 將當(dāng)前目錄下的
mysql.sql導(dǎo)入數(shù)據(jù)庫
SOURCE ./mysql.sql;
- 為一個(gè)表增加字段蒋歌，刪除字段
alter table table_name add colume_name datatypealter table table_name drop colume colume_name
- 復(fù)制一個(gè)字段
update table_name set z_colume=n_colume
數(shù)據(jù)庫權(quán)限操作SQL語句
- 創(chuàng)建一個(gè)具有root權(quán)限摧阅，可從任何IP登錄的用戶sina汰蓉，密碼為zhangyan
GRANT ALL PRIVILEGES ON *.* TO sina@% IDENTIFIED BY zhangyan;
- 創(chuàng)建一個(gè)具有"數(shù)據(jù)操作"、"結(jié)構(gòu)操作"權(quán)限棒卷，只能從192.168.1.***登錄的用戶sina顾孽，密碼為zhangyan
GRANT SELECT , INSERT , UPDATE , DELETE , FILE , CREATE , DROP , INDEX , ALTER , CREATE TEMPORARY TABLES , CREATE VIEW , SHOW VIEW , CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON *.* TO sina@192.168.1.% IDENTIFIED BY zhangyan;
- 創(chuàng)建一個(gè)只擁有"數(shù)據(jù)操作"權(quán)限祝钢，只能從192.168.1.24登錄，只能操作
rewin數(shù)據(jù)庫的zhangyan表的用戶sina若厚，密碼為zhangyan
GRANT SELECT , INSERT , UPDATE , DELETE ON rewin.zhangyan TO sina@192.168.1.24 IDENTIFIED BY zhangyan;
- 創(chuàng)建一個(gè)擁有"數(shù)據(jù)操作"太颤、"結(jié)構(gòu)操作"權(quán)限，可從任何IP登錄盹沈，只能操作
rewin數(shù)據(jù)庫的用戶sina，密碼為zhangyan
GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , INDEX , ALTER , CREATE TEMPORARY TABLES , CREATE VIEW , SHOW VIEW , CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON rewin.* TO sina@% IDENTIFIED BY zhangyan;
- 刪除用戶
DROP USER sina@%;
- MySQL中將字符串a(chǎn)aa批量替換為bbb的SQL語句
UPDATE 表名 SET 字段名 = REPLACE (字段名, aaa, bbb);
- 修復(fù)損壞的表
7.1 用root帳號(hào)從命令行登錄MySQL：mysql -u root -p
7.2 輸入root帳號(hào)的密碼吃谣。
7.3 選定數(shù)據(jù)庫名（本例中的數(shù)據(jù)庫名為student）:use student;
7.4 修復(fù)損壞的表（本例中要修復(fù)的表為smis_user_student）:repair table smis_user_student;
