一、簡(jiǎn)介

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

編輯搜圖

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

• 庫(kù)操作

• 表操作

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

• 運(yùn)算符

• 視圖

• 函數(shù)

• 存儲(chǔ)過(guò)程

• 觸發(fā)器

• 序列

• 用戶(hù)權(quán)限

由于整部?jī)?nèi)容過(guò)長(zhǎng)，在本篇文章中，主要介紹上半部分內(nèi)容!

二、庫(kù)操作

2.1 新增庫(kù)

創(chuàng)建數(shù)據(jù)庫(kù)比較簡(jiǎn)單，在創(chuàng)建的時(shí)候直接指定字符集、排序規(guī)則即可！

CREATE DATABASE IF NOT EXISTS `庫(kù)名` 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 修改庫(kù)名

數(shù)據(jù)庫(kù)修改庫(kù)名的有三種方法，如果是MyISAM存儲(chǔ)引擎，那么可以直接去數(shù)據(jù)庫(kù)目錄mv就可以了，如果是Innodb完全不行，會(huì)提示相關(guān)表不存在。

方法一：

RENAME database olddbname TO newdbname1.

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

方法二：

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

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

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

• 刪除原來(lái)的舊庫(kù)(確定是否真的需要)

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

例子：

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

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

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

方法三：

直接跑一個(gè) shell 腳本！

#!/bin/bash# 假設(shè)將db1數(shù)據(jù)庫(kù)名改為db2# MyISAM直接更改數(shù)據(jù)庫(kù)目錄下的文件即可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的簡(jiǎn)稱(chēng)，123456表示數(shù)據(jù)庫(kù)密碼值！

2.3 刪除庫(kù)名

刪除庫(kù)，比較簡(jiǎn)單，直接刪除即可!

DROP DATABASE db1;

2.4 使用庫(kù)

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 '用戶(hù)姓名',  mobile varchar(11) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '手機(jī)號(hào)',  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='用戶(hù)表';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) 查詢(xún)表字段

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 '用戶(hù)手機(jī)號(hào)';1.

(4) 刪除字段

ALTER TABLE ts_user drop column gender;1.

3.5 索引操作

(1) 查詢(xún)表索引

 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 查詢(xún)操作

(1) 單表查詢(xún)

select * from ts_user;1.

或者：

select id, name from ts_user;1.

(2) 關(guān)鍵字查詢(xún)

• and 查詢(xún)

select id, name from ts_user where name = '張三'1.

• or 查詢(xún)

select id, name from ts_user where name = '張三'1.

• in 查詢(xún)(參數(shù)個(gè)數(shù)不能超過(guò)1000)

select id, name from ts_user where name in ('張三', '李四')1.

• like 模糊查詢(xún)(%屬于通配符)

select id, name from ts_user where name like '張%'1.

• 非空查詢(xún)

select id, name from ts_user where name is not null1.

• 區(qū)間字段查詢(xún)

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) 連表查詢(xún)

左連接查詢(xún)：

select tu.id, tu.name,tr.role_namefrom ts_user tuleft join ts_role tr on tu.id = tr.user_id1.2.3.

右連接查詢(xún)：

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)連接查詢(xún)：

select tu.id, tu.name,tr.role_namefrom ts_user tuinner join ts_role tr on tu.id = tr.user_id1.2.3.

滿(mǎn)連接查詢(xún)：

select tu.id, tu.name,tr.role_namefrom ts_user tufull join ts_role tr on tu.id = tr.user_id1.2.3.

(4) 分組查詢(xún)

統(tǒng)計(jì)學(xué)生總數(shù)：

select count(id) from ts_user1.

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

select max(age) from ts_user1.

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

select min(age) from ts_user1.

查詢(xún)各個(gè)學(xué)生各項(xiàng)成績(jī)的總和：

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

查詢(xún)各個(gè)學(xué)生各項(xiàng)成績(jī)的平均分：

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

查詢(xún)各個(gè)學(xué)生各項(xiàng)成績(jī)的平均分大于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 刪除操作

# 刪除表全部?jī)?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)算符

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

5.2 比較運(yùn)算符

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

編輯搜圖

編輯搜圖

說(shuō)明：mysql中，IN 語(yǔ)句中參數(shù)個(gè)數(shù)是不限制的。不過(guò)對(duì)整段 sql 語(yǔ)句的長(zhǎng)度有了限制，最大不超過(guò) 4M!

5.3 邏輯運(yùn)算符

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

5.4 位運(yùn)算符

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

編輯搜圖

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

編輯搜圖

說(shuō)明：在無(wú)法確定優(yōu)先級(jí)的情況下，可以使用圓括號(hào)()來(lái)改變優(yōu)先級(jí)，并且這樣會(huì)使計(jì)算過(guò)程更加清晰。

六、視圖

視圖(view)是一種虛擬存在的表，是一個(gè)邏輯表，本身并不包含數(shù)據(jù)。作為一個(gè)select語(yǔ)句保存在數(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ù)說(shuō)明：

• OR REPLACE：表示替換已有視圖

• ALGORITHM：表示視圖選擇算法，默認(rèn)算法是UNDEFINED(未定義的)：MySQL自動(dòng)選擇要使用的算法 ;merge合并;temptable臨時(shí)表

• select_statement：表示select語(yǔ)句

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

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

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

基本格式：

create view <視圖名稱(chēng)>[(column_list)]       as select語(yǔ)句
       with check option;1.2.3.

創(chuàng)建視圖示例：

create view v_user(用戶(hù)名,年齡)asselect user_name,age from t_userwith check option;1.2.3.4.

6.2 查看視圖

使用show create view語(yǔ)句查看視圖信息：

show create view v_user;1.

視圖一旦創(chuàng)建完畢，就可以像一個(gè)普通表那樣使用，視圖主要用來(lái)查詢(xún)：

select * from v_user;1.

6.3 刪除視圖

刪除視圖是指刪除數(shù)據(jù)庫(kù)中已存在的視圖，刪除視圖時(shí)，只能刪除視圖的定義，不會(huì)刪除數(shù)據(jù)，也就是說(shuō)不動(dòng)基表：

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

刪除示例：

drop view IF EXISTS v_user;1.

七、小結(jié)

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