一、簡介

本文主要以 Mysql 數(shù)據(jù)庫為基礎(chǔ)，對常用 SQL 語句進(jìn)行一次深度總結(jié)！

編輯搜圖

整體內(nèi)容，主要有以下幾個部分：

• 庫操作

• 表操作

• 數(shù)據(jù)操作

• 運(yùn)算符

• 視圖

• 函數(shù)

• 存儲過程

• 觸發(fā)器

• 序列

• 用戶權(quán)限

由于整部內(nèi)容過長，在本篇文章中，主要介紹上半部分內(nèi)容!

二、庫操作

2.1 新增庫

創(chuàng)建數(shù)據(jù)庫比較簡單，在創(chuàng)建的時(shí)候直接指定字符集、排序規(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完全不行，會提示相關(guān)表不存在。

方法一：

RENAME database olddbname TO newdbname1.

這個語法在 mysql-5.1.7 中被添加進(jìn)來，到了mysql-5.1.23又去掉了，官方不推薦，會有丟失數(shù)據(jù)的危險(xiǎn)!

方法二：

思路是先創(chuàng)建一個新庫，之后將舊庫的數(shù)據(jù)導(dǎo)入到新庫，即可完成修改庫名！

• 創(chuàng)建需要改成新名的數(shù)據(jù)庫

• mysqldum 導(dǎo)出要改名的數(shù)據(jù)庫

• 刪除原來的舊庫(確定是否真的需要)

當(dāng)然這種方法雖然安全，但是如果數(shù)據(jù)量大，會比較耗時(shí)，同時(shí)還需要考慮到磁盤空間等硬件成本。

例子：

# 將db1庫備份到db1.sql文件mysqldump -u root -p db1 > /usr/db1.sql;

# 導(dǎo)入備份文件到新庫db2mysql -u root -p db2 < /root/db1.sql;

# 刪除舊庫（如果真的需要）DROP DATABASE db1;1.2.3.4.5.6.7.8.

方法三：

直接跑一個 shell 腳本！

#!/bin/bash# 假設(shè)將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 '手機(jī)號',  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)建時(shí)間',  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 '更新時(shí)間',  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 '用戶手機(jī)號';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) 關(guān)鍵字查詢

• 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)計(jì)學(xué)生總數(shù)：

select count(id) from ts_user1.

查詢學(xué)生最大的年紀(jì)：

select max(age) from ts_user1.

查詢學(xué)生最大的年紀(jì)：

select min(age) from ts_user1.

查詢各個學(xué)生各項(xiàng)成績的總和：

select id, sum(score) from ts_user group by id1.

查詢各個學(xué)生各項(xiàng)成績的平均分：

select id, avg(score) from ts_user group by id1.

查詢各個學(xué)生各項(xiàng)成績的平均分大于100的學(xué)生信息：

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ù)判斷條件進(jìn)行刪除delete from ts_user where id = '1'1.2.3.4.5.

五、運(yùn)算符

MySQL 主要有以下幾種運(yùn)算符：

• 算術(shù)運(yùn)算符

• 比較運(yùn)算符

• 邏輯運(yùn)算符

• 位運(yùn)算符

5.1 算術(shù)運(yùn)算符

說明：在除法運(yùn)算和模運(yùn)算中，如果除數(shù)為0，將是非法除數(shù)，返回結(jié)果為NULL。

5.2 比較運(yùn)算符

SELECT 語句中的條件語句經(jīng)常要使用比較運(yùn)算符。通過這些比較運(yùn)算符，可以判斷表中的哪些記錄是符合條件的。比較結(jié)果為真，則返回 1，為假則返回 0，比較結(jié)果不確定則返回 NULL。

編輯搜圖

編輯搜圖

說明：mysql中，IN 語句中參數(shù)個數(shù)是不限制的。不過對整段 sql 語句的長度有了限制，最大不超過 4M!

5.3 邏輯運(yùn)算符

邏輯運(yùn)算符用來判斷表達(dá)式的真假。如果表達(dá)式是真，結(jié)果返回 1。如果表達(dá)式是假，結(jié)果返回 0。

5.4 位運(yùn)算符

位運(yùn)算符是在二進(jìn)制數(shù)上進(jìn)行計(jì)算的運(yùn)算符。位運(yùn)算會先將操作數(shù)變成二進(jìn)制數(shù)，進(jìn)行位運(yùn)算。然后再將計(jì)算結(jié)果從二進(jìn)制數(shù)變回十進(jìn)制數(shù)。

編輯搜圖

5.5 運(yùn)算符優(yōu)先級

編輯搜圖

說明：在無法確定優(yōu)先級的情況下，可以使用圓括號()來改變優(yōu)先級，并且這樣會使計(jì)算過程更加清晰。

六、視圖

視圖(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：表示視圖選擇算法，默認(rèn)算法是UNDEFINED(未定義的)：MySQL自動選擇要使用的算法 ;merge合并;temptable臨時(shí)表

• select_statement：表示select語句

• **[WITH [CASCADED | LOCAL] CHECK OPTION]**：表示視圖在更新時(shí)保證在視圖的權(quán)限范圍之內(nèi)

• cascade：是默認(rèn)值，表示更新視圖的時(shí)候，要滿足視圖和表的相關(guān)條件

• local：表示更新視圖的時(shí)候，要滿足該視圖定義的一個條件即可

基本格式：

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í)，只能刪除視圖的定義，不會刪除數(shù)據(jù)，也就是說不動基表：

DROP VIEW [IF EXISTS]   
view_name [, view_name] ...1.2.

刪除示例：

drop view IF EXISTS v_user;1.

七、小結(jié)

本文主要圍繞 Mysql 中常用的語法進(jìn)行一次梳理和介紹，這些語法大部分也同樣適用于其他的數(shù)據(jù)庫，例如 oracle、sqlserver、postgres 等等，在下篇文字中，我們會重點(diǎn)介紹剩下的部分。