MySQL Structured Query Language

SQL簡介

SQL (Structured Query Language) 是具有資料操
縱和資料定義等多種功能的資料庫語言，這種語言具有互動性特點，能為用戶提供極大的便利，資料庫管理系統應充分利用SQL語言提高計算機應用系統的作業質量與效率，SQL語言不僅能獨立應用于終端，還可以作為子語言為其他程式設計提供有效助力，該程式應用中，SQL可與其他程式語言一起優化程式功能，進而為用戶提供更多更全面的資訊，

資料庫操作

1.資料定義：DDL語言，定義資料庫的邏輯結構，包括定義資料庫、基本表、視圖和索引4部分，

資料定義語言（data definition language，DDL）屬于DBMS語言的一種，用于明確定義概念綱要(conceptual schemas)及內部綱要(internal schemas)，DBMS內的DDL編譯器(complier)能夠處理DDL，識別綱要建構物(schema constructs)的描述(description)并加以存入DBMS目錄中的綱要描述(schema description)， 為SQL語言集中負責資料結構定義與資料庫物件定義的語言，由CREATE、ALTER與DROP三個語法所組成，最早是由Codasyl（Conference on Data Systems Languages）資料模型開始，現在被納入SQL指令中作為其中一個子集，

# 創建資料庫 CREATE DATABASE <資料庫名> 〔其它引數〕
> create database if not exists `db_name` character set utf8mb4 collate utf8mb4_0900_ai_ci;

# 修改資料庫 ALTER DATABASE <資料庫名> 〔其它引數〕
> alter database `db_name` character set=utf8 COLLATE=utf8_general_ci;  

# 洗掉資料庫 DROP DATABASE <資料庫名>
> drop database if exists `db_name`;


# 創建表 CREATE
> create table t_user(id int not null default 0 primary key, name varchar(20) default '', age int, country char(20), website varchar(64));

# 修改表
> alter table t_user convert to character set utf8 collate utf8_general_ci;(修改表字符集)

> alter table t_user default character set utf8 collate utf8_general_ci;(只修改默認字符集)

> alter table t_user modify name varchar(20);(修改欄位型別)

> alter table t_user change name username varchar(20);(修改欄位名稱)

> alter table t_user change column_name column_name varchar(255) character set utf8mb4 collate utf8mb4_unicode_ci; (修改表欄位字符集)

> alter table t_user add age int(11);(表添加欄位)

> alter talbe t_user alter column column_name (char) (修改表欄位型別)

> alter table t_user add primary key(id);(添加主鍵)

> alter table t_user drop primary key;(洗掉主鍵)

> alter table t_user add constraint fk_name foreign key(name) fererences t_user(name);(添加外鍵)

> alter table t_user drop foreign key name;(洗掉外鍵)

> alter table t_user rename to user; (修改表名)

# 洗掉表
> drop table t_user;


# 創建索引 CREATE INDEX <索引名稱> on <表名稱(key)>
> create index index_name on t_user(name)

# 洗掉索引 DROP INDEX  <索引名稱> on <表名稱>
> drop index index_name on t_user;

2.資料操縱：DML，包括插入、洗掉和更新三種操作，

資料操縱語言（Data Manipulation Language, DML）是用于資料庫操作，對資料庫其中的物件和資料運行訪問作業的編程陳述句，通常是資料庫專用編程語言之中的一個子集，例如在資訊軟體產業通行標準的SQL語言中，以INSERT、UPDATE、DELETE三種指令為核心，分別代表插入(意指新增或創建)、更新(修改)與洗掉(銷毀)，在使用資料庫的系統開發程序中，其中應用程式必然會使用的指令；而加上 SQL的SELECT陳述句，歐美地區的開發人員把這四種指令，以“CRUD”(分別為 Create, Read, Update, Delete英文四前綴字母縮略的術語)來稱呼；而亞洲地區使用漢語的開發人員，或可能以四個漢字：增 查 改 刪 來略稱，

# 插入資料
> insert into t_user (`column1`, `column2`) values (`value1`, `value2`);

# 修改資料
> update t_user set `column1`=`v1`, `column2`=`v2` where id = 1;
 
# 洗掉資料
> delete from t_user where id = 1;

3.資料查詢：DQL，包括資料查詢操作，

# SELECT 陳述句
> select * from t_user where id = 1;

# SELECT DISTINCT 陳述句（DISTINCT 用于回傳唯一的值，）
> select distinct id, name from t_user;

# WHERE 子句
> select * form t_user where id < 3;

# AND & OR 運算子
> select * from t_user where country = "CN" and age < 30;

# ORDER BY 關鍵字
> select * from t_user order by id, age desc;

# SELECT LIMIT 子句
> select * from t_user order by id desc limit 1;

# LIKE 運算子(% 代表0或多個，_ 代表一個)
> select * from t_user where name like "小_";

# IN 運算子
> select * from t_user where id in (select id from t_user where id < 3);

# BETWEEN \ NOT BETWEEN運算子
> select * from t_user where age between 18 and 30;

# 連接(JOIN)
> select t_user.id, t_user.name, t_user.age, t_user.country, t_score.course_name, t_score.score from t_user  join t_score on t_score.user_id = t_user.id;

# INNER JOIN 關鍵字(INNER JOIN 與 JOIN 是相同的，)
> select t_user.*,t_score.* from t_user inner join t_score on t_user.id = t_score.user_id;

# LEFT JOIN 關鍵字
> select t_user.*, t_score.* from t_user left join t_score on t_user.id = t_score.user_id;

# RIGHT JOIN 關鍵字
> select t_user.*, t_score.* from t_user right join t_score on t_user.id = t_score.user_id;

# UNION 運算子
> select country from t_user where id < 3 union select country from t_user where id > 2;
> select country from t_user where id < 3 union all select country from t_user where id > 2;

# SELECT INTO 陳述句
> select * into t_user_bak from t_user;

# INSERT INTO SELECT 陳述句
> insert into t_user_bak select * from t_user;

# CREATE VIEW 陳述句
> create view v_user as select * from t_user;

4.資料控制：DCL，對用戶訪問資料的控制有基本表和視圖的授權及回收，

資料控制語言 (Data Control Language) 在SQL語言中，是一種可對資料訪問權進行控制的指令，它可以控制特定用戶賬戶對資料表、查看表、存盤程式、用戶自定義函式等資料庫物件的控制權，由 GRANT 和 REVOKE 兩個指令組成，

# 創建用戶 create user '用戶名稱'@'主機名稱' indentified by '用戶密碼'
> create user 'jack'@'localhost' identified by 'jack';

# 授予權限  grant 權限 on 資料庫.表 to '用戶名'@'登錄主機'  [indentified by '用戶密碼'];
> grant replication slave on *.* to 'jack'@'localhost';

# 重繪
# $ flush privileges; 

# 修改root用戶可以遠程連接
> update mysql.user set host='%' where user='root';

# 查看mysql所用用戶
> select user,host from mysql.user;

# docker 修改mysql的最大連接數
apt-get update
apt-get install vim
vim /etc/mysql/mysql.conf.d/mysqld.cnf
max_connections=1000

# 解決遠程連接密碼錯誤
> alter user 'root'@'%' identified with mysql_native_password by 'pass';

5.事務控制：TCL，包括事務的提交與回滾，

一般來說，事務是必須滿足4個條件（ACID）：：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨立性）、持久性（Durability），

原子性：一個事務（transaction）中的所有操作，要么全部完成，要么全部不完成，不會結束在中間某個環節，事務在執行程序中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣，

一致性：在事務開始之前和事務結束以后，資料庫的完整性沒有被破壞，這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精確度、串聯性以及后續資料庫可以自發性地完成預定的作業，

隔離性：資料庫允許多個并發事務同時對其資料進行讀寫和修改的能力，隔離性可以防止多個事務并發執行時由于交叉執行而導致資料的不一致，事務隔離分為不同級別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重復讀（repeatable read）和串行化（Serializable），

持久性：事務處理結束后，對資料的修改就是永久的，即便系統故障也不會丟失，

在 MySQL 命令列的默認設定下，事務都是自動提交的，即執行 SQL 陳述句后就會馬上執行 COMMIT 操作，因此要顯式地開啟一個事務務須使用命令 BEGIN 或 START TRANSACTION，或者執行命令 SET AUTOCOMMIT=0，用來禁止使用當前會話的自動提交，

事務控制陳述句：

BEGIN 或 START TRANSACTION 顯式地開啟一個事務；

COMMIT 也可以使用 COMMIT WORK，不過二者是等價的，COMMIT 會提交事務，并使已對資料庫進行的所有修改成為永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，不過二者是等價的，回滾會結束用戶的事務，并撤銷正在進行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允許在事務中創建一個保存點，一個事務中可以有多個 SAVEPOINT；

RELEASE SAVEPOINT identifier 洗掉一個事務的保存點，當沒有指定的保存點時，執行該陳述句會拋出一個例外；

ROLLBACK TO identifier 把事務回滾到標記點；

SET TRANSACTION 用來設定事務的隔離級別，InnoDB 存盤引擎提供事務的隔離級別有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE，

MYSQL 事務處理主要有兩種方法：

1.用 BEGIN, ROLLBACK, COMMIT來實作

BEGIN 開始一個事務
ROLLBACK 事務回滾
COMMIT 事務確認

2.直接用 SET 來改變 MySQL 的自動提交模式:

SET AUTOCOMMIT=0 禁止自動提交
SET AUTOCOMMIT=1 開啟自動提交

資料庫函式

日期函式

聚合函式

字串函式

控制流函式

數學函式

比較函式

其他函式

MySQL 常用操作

1.啟動\關閉 MySQL systemctl start\stop mysqld

2.查看mysql狀態 systemctl status mysqld

3.登陸mysql mysql -uroot -proot -h1127.0.0.1 -P3306

4.查看所有資料庫 show databases;

5.使用某個資料庫 use db01;

6.查看資料庫下所有表 show tables;

7.查看表欄位詳情 describe t_user;

8.匯出mysql資料 mysqldump -uroot -ppass db_name 表名> mysql_bak.sql

標籤：MySQL

上一篇：求大神講解一下如何mongo如何多維度聚合

下一篇：資料庫端查詢的表大小 和 物體物理檔案的大小 相比較 差距較大