一、簡介
本文主要以 Mysql 數(shù)據(jù)庫為基礎，對常用 SQL 語句進行一次深度總結！
編輯搜圖
整體內(nèi)容，主要有以下幾個部分：
-
庫操作
-
表操作
-
數(shù)據(jù)操作
-
運算符
-
視圖
-
函數(shù)
-
存儲過程
-
觸發(fā)器
-
序列
-
用戶權限
由于整部內(nèi)容過長，在本篇文章中，主要介紹上半部分內(nèi)容!
二、庫操作
2.1 新增庫
創(chuàng)建數(shù)據(jù)庫比較簡單，在創(chuàng)建的時候直接指定字符集、排序規(guī)則即可！
CREATE DATABASE IF NOT EXISTS `庫名` default charset utf8mb4 COLLATE utf8mb4_unicode_ci;1.
例子：
CREATE DATABASE IF NOT EXISTS test_db default charset utf8mb4 COLLATE utf8mb4_unicode_ci;1.
2.2 修改庫名
數(shù)據(jù)庫修改庫名的有三種方法，如果是MyISAM存儲引擎，那么可以直接去數(shù)據(jù)庫目錄mv就可以了，如果是Innodb完全不行，會提示相關表不存在。
方法一：
RENAME database olddbname TO newdbname1.
這個語法在 mysql-5.1.7 中被添加進來，到了mysql-5.1.23又去掉了，官方不推薦，會有丟失數(shù)據(jù)的危險!
方法二：
思路是先創(chuàng)建一個新庫，之后將舊庫的數(shù)據(jù)導入到新庫，即可完成修改庫名！
-
創(chuàng)建需要改成新名的數(shù)據(jù)庫
-
mysqldum 導出要改名的數(shù)據(jù)庫
-
刪除原來的舊庫(確定是否真的需要)
當然這種方法雖然安全，但是如果數(shù)據(jù)量大，會比較耗時，同時還需要考慮到磁盤空間等硬件成本。
例子：
# 將db1庫備份到db1.sql文件mysqldump -u root -p db1 > /usr/db1.sql; # 導入備份文件到新庫db2mysql -u root -p db2 < /root/db1.sql; # 刪除舊庫（如果真的需要）DROP DATABASE db1;1.2.3.4.5.6.7.8.
方法三：
直接跑一個 shell 腳本！
#!/bin/bash# 假設將db1數(shù)據(jù)庫名改為db2# MyISAM直接更改數(shù)據(jù)庫目錄下的文件即可mysql -uroot -p123456 -e 'create database if not exists db2'list_table=$(mysql -uroot -p123456 -Nse "select table_name from information_schema.TABLES where TABLE_SCHEMA='db1'")for table in $list_tabledo mysql -uroot -p123456 -e "rename table db1.$table to db2.$table"done1.2.3.4.5.6.7.8.9.10.11.
其中p123456，p是password的簡稱，123456表示數(shù)據(jù)庫密碼值！
2.3 刪除庫名
刪除庫，比較簡單，直接刪除即可!
DROP DATABASE db1;
2.4 使用庫
USE db2;1.
三、表操作
3.1 創(chuàng)建表
CREATE TABLE ts_user ( id bigint(20) unsigned NOT NULL COMMENT '編碼', name varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用戶姓名', mobile varchar(11) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '手機號', create_userid varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '創(chuàng)建人', create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間', update_userid varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '更新人', update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間', PRIMARY KEY (id), KEY idx_create_time (create_time) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用戶表';1.2.3.4.5.6.7.8.9.10.11.
3.2 修改表名
ALTER TABLE ts_user RENAME TO ts_new_user;1.
3.3 刪除表
DROP TABLE ts_new_user;1.
3.4 字段操作
(1) 查詢表字段
show full columns from ts_user;1.
(2) 新增字段
ALTER TABLE ts_user add column gender tinyint(4) NOT NULL DEFAULT '1' COMMENT '性別，1，男；2，女' AFTER mobile;1.
(3) 修改字段
ALTER TABLE ts_user modify column mobile varchar(30) NOT NULL DEFAULT '' COMMENT '用戶手機號';1.
(4) 刪除字段
ALTER TABLE ts_user drop column gender;1.
3.5 索引操作
(1) 查詢表索引
SHOW INDEXES FROM ts_user;1.
(2) 新增普通索引
alter table ts_user add index idx_id (id);1.
(3) 新增唯一索引
alter table ts_user add unique idx_id (id);1.
(4) 新增主鍵索引
alter table ts_user add primary key idx_id (id) ;1.
(5) 新增多列索引
alter table ts_user add index idx_id_name (id,name) ;1.
(6) 新增全文索引
alter table ts_user add fulltext idx_id (id) ;1.
(7) 刪除索引
# 刪除普通索引alter table ts_user drop index idx_id; # 刪除主鍵索引alter table ts_user drop primary key;1.2.3.4.5.
四、數(shù)據(jù)操作
4.1 查詢操作
(1) 單表查詢
select * from ts_user;1.
或者：
select id, name from ts_user;1.
(2) 關鍵字查詢
-
and 查詢
select id, name from ts_user where name = '張三'1.
-
or 查詢
select id, name from ts_user where name = '張三'1.
-
in 查詢(參數(shù)個數(shù)不能超過1000)
select id, name from ts_user where name in ('張三', '李四')1.
-
like 模糊查詢(%屬于通配符)
select id, name from ts_user where name like '張%'1.
-
非空查詢
select id, name from ts_user where name is not null1.
-
區(qū)間字段查詢
select id, name, age from ts_user where age >= 18 and age <= 301.
select id, name, age from ts_user where age between 18 and 301.
-
多條件判斷
select name, (casewhen scope >= 90 then '優(yōu)'when 80 <= scope < 90 then '良'when 80 > scope >= 70 then '中'else '差'end) as judgefrom ts_user1.2.3.4.5.6.7.8.9.10.11.
(3) 連表查詢
左連接查詢：
select tu.id, tu.name,tr.role_namefrom ts_user tuleft join ts_role tr on tu.id = tr.user_id1.2.3.
右連接查詢：
select tu.id, tu.name,tr.role_namefrom ts_user turight join ts_role tr on tu.id = tr.user_id1.2.3.
內(nèi)連接查詢：
select tu.id, tu.name,tr.role_namefrom ts_user tuinner join ts_role tr on tu.id = tr.user_id1.2.3.
滿連接查詢：
select tu.id, tu.name,tr.role_namefrom ts_user tufull join ts_role tr on tu.id = tr.user_id1.2.3.
(4) 分組查詢
統(tǒng)計學生總數(shù)：
select count(id) from ts_user1.
查詢學生最大的年紀：
select max(age) from ts_user1.
查詢學生最大的年紀：
select min(age) from ts_user1.
查詢各個學生各項成績的總和：
select id, sum(score) from ts_user group by id1.
查詢各個學生各項成績的平均分：
select id, avg(score) from ts_user group by id1.
查詢各個學生各項成績的平均分大于100的學生信息：
select id, avg(score) from ts_user group by id having avg(score) > 1001.
4.2 插入操作
(1) 單列插入
INSERT INTO ts_user(id, name) VALUES ('1', '張三');1.
(2) 多列插入
INSERT INTO ts_user(id, name)VALUES('1', '張三'),
('2', '李四'),
('3', '王五');1.2.3.4.5.
4.3 修改操作
update ts_user set name = '李四1', age = '18' where id = '1'1.
4.4 刪除操作
# 刪除表全部內(nèi)容delete from ts_user# 根據(jù)判斷條件進行刪除delete from ts_user where id = '1'1.2.3.4.5.
五、運算符
MySQL 主要有以下幾種運算符：
-
算術運算符
-
比較運算符
-
邏輯運算符
-
位運算符
5.1 算術運算符
說明：在除法運算和模運算中，如果除數(shù)為0，將是非法除數(shù)，返回結果為NULL。
5.2 比較運算符
SELECT 語句中的條件語句經(jīng)常要使用比較運算符。通過這些比較運算符，可以判斷表中的哪些記錄是符合條件的。比較結果為真，則返回 1，為假則返回 0，比較結果不確定則返回 NULL。
編輯搜圖
編輯搜圖
說明：mysql中，IN 語句中參數(shù)個數(shù)是不限制的。不過對整段 sql 語句的長度有了限制，最大不超過 4M!
5.3 邏輯運算符
邏輯運算符用來判斷表達式的真假。如果表達式是真，結果返回 1。如果表達式是假，結果返回 0。
5.4 位運算符
位運算符是在二進制數(shù)上進行計算的運算符。位運算會先將操作數(shù)變成二進制數(shù)，進行位運算。然后再將計算結果從二進制數(shù)變回十進制數(shù)。
編輯搜圖
5.5 運算符優(yōu)先級
編輯搜圖
說明：在無法確定優(yōu)先級的情況下，可以使用圓括號()來改變優(yōu)先級，并且這樣會使計算過程更加清晰。
六、視圖
視圖(view)是一種虛擬存在的表，是一個邏輯表，本身并不包含數(shù)據(jù)。作為一個select語句保存在數(shù)據(jù)字典中的。
6.1 創(chuàng)建視圖
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]1.2.3.4.
參數(shù)說明：
-
OR REPLACE：表示替換已有視圖
-
ALGORITHM：表示視圖選擇算法，默認算法是UNDEFINED(未定義的)：MySQL自動選擇要使用的算法 ;merge合并;temptable臨時表
-
select_statement：表示select語句
-
**[WITH [CASCADED | LOCAL] CHECK OPTION]**：表示視圖在更新時保證在視圖的權限范圍之內(nèi)
-
cascade：是默認值，表示更新視圖的時候，要滿足視圖和表的相關條件
-
local：表示更新視圖的時候，要滿足該視圖定義的一個條件即可
基本格式：
create view <視圖名稱>[(column_list)] as select語句 with check option;1.2.3.
創(chuàng)建視圖示例：
create view v_user(用戶名,年齡)asselect user_name,age from t_userwith check option;1.2.3.4.
6.2 查看視圖
使用show create view語句查看視圖信息：
show create view v_user;1.
視圖一旦創(chuàng)建完畢，就可以像一個普通表那樣使用，視圖主要用來查詢：
select * from v_user;1.
6.3 刪除視圖
刪除視圖是指刪除數(shù)據(jù)庫中已存在的視圖，刪除視圖時，只能刪除視圖的定義，不會刪除數(shù)據(jù)，也就是說不動基表：
DROP VIEW [IF EXISTS] view_name [, view_name] ...1.2.
刪除示例：
drop view IF EXISTS v_user;1.
七、小結
本文主要圍繞 Mysql 中常用的語法進行一次梳理和介紹，這些語法大部分也同樣適用于其他的數(shù)據(jù)庫，例如 oracle、sqlserver、postgres 等等，在下篇文字中，我們會重點介紹剩下的部分。