本文對常用的sql語句做了整理胧瓜，持續(xù)更新……
MySQL 版本：5.7.13
操作系統(tǒng)：Ubuntu 16.0.4
1.創(chuàng)建數(shù)據(jù)庫
db_name: 數(shù)據(jù)庫名稱
tb_name: 數(shù)據(jù)表名
tb_field: 數(shù)據(jù)字段
1.1 數(shù)據(jù)庫登錄
# 數(shù)據(jù)庫服務(wù)啟動(dòng) 關(guān)閉 重啟
service mysql start/stop/restart
# 數(shù)據(jù)庫登錄(通過密碼登錄)
mysql -u [username] -p
# 查看數(shù)據(jù)庫存儲引擎
show engines;
# 顯示所有數(shù)據(jù)庫
show databases;
# 選擇需要使用的數(shù)據(jù)庫
use db_name;
# 顯示數(shù)據(jù)所有的表
show tables;
# 顯示列屬性
show columns from db_table;
1.2 編碼問題
設(shè)置數(shù)據(jù)庫UTF-8編碼，修改數(shù)據(jù)庫配置文件蒲肋，或者修改數(shù)據(jù)默認(rèn)編碼方式
1.
修改全局配置文件my.cnf (/etc/mysql/my.cnf)
# 文件末尾添加下述默認(rèn)項(xiàng)配置
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
# 數(shù)據(jù)庫查詢忽略大小寫
lower_case_table_names = 1
2. 修改數(shù)據(jù)庫默認(rèn)編碼
# 設(shè)置數(shù)據(jù)庫db_name默認(rèn)為utf8:
ALTER DATABASE `db_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
# 設(shè)置表tb_name默認(rèn)編碼為utf8:
ALTER TABLE `tb_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
# 創(chuàng)建表時(shí)聲明編碼
CREATE TABLE tb_name
(
id CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci
) CHARACTER SET latin1 COLLATE latin1_bin;
***提示: ***查看數(shù)據(jù)庫當(dāng)前編碼方式兜粘，使用命令：
show variables like 'char%';
你會(huì)看到類似的輸出：
數(shù)據(jù)庫編碼.jpg
1.3 數(shù)據(jù)庫創(chuàng)建
#例子
CREATE TABLE tb_name(
id int NOT NULL AUTO_INCREMENT, # 非空、自增 聲明
name VARCHAR(30) UNIQUE, #添加字段的唯一約束
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, #設(shè)置時(shí)間戳默認(rèn)值為當(dāng)前時(shí)間
last_updated TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,#設(shè)置自動(dòng)添加更新時(shí)間
size ENUM( 'small', 'medium', 'large') DEFAULT 'small', #枚舉類型聲明
PRIMARY KEY (id), #主鍵聲明
FOREIGN KEY (id) REFERENCES db_name(tb_field) #聲明外鍵
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci
COMMENT = 'XXXX表';
***擴(kuò)展: ***Microsoft Access剃法、MySQL 以及 SQL Server 所使用的數(shù)據(jù)類型和范圍
http://www.w3school.com.cn/sql/sql_datatypes.asp
2. 數(shù)據(jù)查詢
# 基本查詢
SELECT * FROM tb_name;
SELECT * FROM tb_name WHERE id = XXX;
# 多條查詢
SELECT * FROM tb_name WHERE id in (xx, xx);
# MD5 加密路鹰，加密比較
INSERT INTO user(name,password) VALUES("admin",md5("12345"));
SELECT * FROM user WHERE name="admin" AND password=md5("12345");
# 查詢最 新 的一條數(shù)據(jù)
1) SELECT * FROM tb_name ORDER BY date DESC LIMIT 1;
2) SELECT * FROM tb_name WHERE date IN (SELECT max(tb_field) FROM tb_name);
3) SELECT * FROM tb_name WHERE date = (SELECT max(tb_field) FROM tb_name);
# 查詢記錄總數(shù)
SELECT count(*) FROM tb_name;
# 查詢前十條數(shù)據(jù)
SELECT * FROM tb_name LIMIT 10;
# 查詢平均值
SELECT AVG(tb_field) FROM tb_name;
SELECT name, AVG(tb_field) FROM tb_name GROUP BY name;
3. 數(shù)據(jù)添加
# 插入數(shù)據(jù)
INSERT INTO tb_name VALUES ('Gates', 'Bill', 'ACC', 'Beijing');
# 插入指定列的數(shù)據(jù)
INSERT INTO tb_name (tb_field1, tb_field2) VALUES ('Gates', 'Bill');
4. 數(shù)據(jù)修改
包括數(shù)據(jù)表屬性的修改晋柱，一個(gè)記錄的數(shù)據(jù)的修改
4.1 數(shù)據(jù)修改
UPDATE tb_name SET tb_field1 = 'One', tb_field2 = 'Two' WHERE tb_field3 = '111';
4.2 屬性修改
# 添加列
ALTER TABLE tb_name ADD COLUMN state ENUM('1','0') NOT NULL DEFAULT '1';
# 添加唯一約束
1) ALTER TABLE tb_name ADD UNIQUE (name);
2) CREATE UNIQUE INDEX [索引名稱] ON tb_name(tb_field);
# 更改列屬性
ALTER TABLE tb_name MODIFY COLUMN tb_field VARCHAR(50);
# 更改列名
ALTER TABLE tb_name RNAME COLUMN tb_fieldA to tb_fieldB;
ALTER TABLE tb_name CHANGE tb_fieldA tb_fieldB varchar(255);
5. 數(shù)據(jù)刪除
# 刪除一個(gè)記錄
DELETE FROM tb_name WHERE name = 'Bill';
# 刪除所有行
DELETE FROM tb_name;
# 刪除表
DROP TABLE tb_name;
# 刪除數(shù)據(jù)庫
DROP DATABASE db_name;
# 刪除索引
ALTER TABLE tb_name DROP INDEX [索引名稱];
# 刪除列
ALTER TABLE tb_name DROP COLUMN column_name;
6. 其他
# 導(dǎo)出數(shù)據(jù)庫
mysqldump -u [用戶名] -p [數(shù)據(jù)庫名] > [導(dǎo)出的文件名]
mysqldump -u username -p db_name > db_name.sql;
# 導(dǎo)出數(shù)據(jù)表
mysqldump -u username -p db_name tb_name> db_name.sql;
# 導(dǎo)入數(shù)據(jù)庫
1) mysql -u username -p db_name < [xxx.sql];
2) use db_name;
source xxx.sql;
