MySQL Structured Query Language

SQL簡介

SQL (Structured Query Language) 是具有數(shù)據(jù)操
縱和數(shù)據(jù)定義等多種功能的數(shù)據(jù)庫語言，這種語言具有交互性特點(diǎn)，能為用戶提供極大的便利棒口，數(shù)據(jù)庫管理系統(tǒng)應(yīng)充分利用SQL語言提高計(jì)算機(jī)應(yīng)用系統(tǒng)的工作質(zhì)量與效率。SQL語言不僅能獨(dú)立應(yīng)用于終端裹驰，還可以作為子語言為其他程序設(shè)計(jì)提供有效助力己儒，該程序應(yīng)用中衷笋，SQL可與其他程序語言一起優(yōu)化程序功能逸月，進(jìn)而為用戶提供更多更全面的信息。

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

1.數(shù)據(jù)定義：DDL語言纪隙，定義數(shù)據(jù)庫的邏輯結(jié)構(gòu)赊豌，包括定義數(shù)據(jù)庫、基本表绵咱、視圖和索引4部分碘饼。

數(shù)據(jù)定義語言（data definition language，DDL）屬于DBMS語言的一種悲伶，用于明確定義概念綱要(conceptual schemas)及內(nèi)部綱要(internal schemas)艾恼，DBMS內(nèi)的DDL編譯器(complier)能夠處理DDL，識別綱要建構(gòu)物(schema constructs)的描述(description)并加以存入DBMS目錄中的綱要描述(schema description)麸锉。 為SQL語言集中負(fù)責(zé)數(shù)據(jù)結(jié)構(gòu)定義與數(shù)據(jù)庫對象定義的語言钠绍，由CREATE、ALTER與DROP三個語法所組成花沉，最早是由Codasyl（Conference on Data Systems Languages）數(shù)據(jù)模型開始柳爽，現(xiàn)在被納入SQL指令中作為其中一個子集。

# 創(chuàng)建數(shù)據(jù)庫 CREATE DATABASE <數(shù)據(jù)庫名> 〔其它參數(shù)〕
> create database if not exists `db_name` character set utf8mb4 collate utf8mb4_0900_ai_ci;

# 修改數(shù)據(jù)庫 ALTER DATABASE <數(shù)據(jù)庫名> 〔其它參數(shù)〕
> alter database `db_name` character set=utf8 COLLATE=utf8_general_ci;  

# 刪除數(shù)據(jù)庫 DROP DATABASE <數(shù)據(jù)庫名>
> drop database if exists `db_name`;


# 創(chuàng)建表 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;(只修改默認(rèn)字符集)

> 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;


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

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

2.數(shù)據(jù)操縱：DML碱屁，包括插入磷脯、刪除和更新三種操作。

數(shù)據(jù)操縱語言（Data Manipulation Language, DML）是用于數(shù)據(jù)庫操作娩脾，對數(shù)據(jù)庫其中的對象和數(shù)據(jù)運(yùn)行訪問工作的編程語句赵誓，通常是數(shù)據(jù)庫專用編程語言之中的一個子集，例如在信息軟件產(chǎn)業(yè)通行標(biāo)準(zhǔn)的SQL語言中柿赊，以INSERT俩功、UPDATE、DELETE三種指令為核心碰声，分別代表插入(意指新增或創(chuàng)建)绑雄、更新(修改)與刪除(銷毀)。在使用數(shù)據(jù)庫的系統(tǒng)開發(fā)過程中奥邮，其中應(yīng)用程序必然會使用的指令；而加上 SQL的SELECT語句罗珍，歐美地區(qū)的開發(fā)人員把這四種指令洽腺，以“CRUD”(分別為 Create, Read, Update, Delete英文四前綴字母縮略的術(shù)語)來稱呼；而亞洲地區(qū)使用漢語的開發(fā)人員覆旱，或可能以四個漢字：增 查 改 刪 來略稱蘸朋。

# 插入數(shù)據(jù)
> insert into t_user (`column1`, `column2`) values (`value1`, `value2`);

# 修改數(shù)據(jù)
> update t_user set `column1`=`v1`, `column2`=`v2` where id = 1;
 
# 刪除數(shù)據(jù)
> delete from t_user where id = 1;

3.數(shù)據(jù)查詢：DQL，包括數(shù)據(jù)查詢操作扣唱。

# 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 運(yùn)算符
> select * from t_user where country = "CN" and age < 30;

# ORDER BY 關(guān)鍵字
> 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 關(guān)鍵字(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 關(guān)鍵字
> select t_user.*, t_score.* from t_user left join t_score on t_user.id = t_score.user_id;

# RIGHT JOIN 關(guān)鍵字
> 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.數(shù)據(jù)控制：DCL炼彪，對用戶訪問數(shù)據(jù)的控制有基本表和視圖的授權(quán)及回收吐根。

數(shù)據(jù)控制語言 (Data Control Language) 在SQL語言中，是一種可對數(shù)據(jù)訪問權(quán)進(jìn)行控制的指令辐马，它可以控制特定用戶賬戶對數(shù)據(jù)表拷橘、查看表、存儲程序喜爷、用戶自定義函數(shù)等數(shù)據(jù)庫對象的控制權(quán)冗疮。由 GRANT 和 REVOKE 兩個指令組成。

# 創(chuàng)建用戶 create user '用戶名稱'@'主機(jī)名稱' indentified by '用戶密碼'
> create user 'jack'@'localhost' identified by 'jack';

# 授予權(quán)限  grant 權(quán)限 on 數(shù)據(jù)庫.表 to '用戶名'@'登錄主機(jī)'  [indentified by '用戶密碼'];
> grant replication slave on *.* to 'jack'@'localhost';

# 刷新
# $ flush privileges; 

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

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

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

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

5.事務(wù)控制：TCL檩帐，包括事務(wù)的提交與回滾术幔。

一般來說，事務(wù)是必須滿足4個條件（ACID）：：原子性（Atomicity湃密，或稱不可分割性）诅挑、一致性（Consistency）、隔離性（Isolation勾缭，又稱獨(dú)立性）揍障、持久性（Durability）。

原子性：一個事務(wù)（transaction）中的所有操作俩由，要么全部完成毒嫡，要么全部不完成，不會結(jié)束在中間某個環(huán)節(jié)幻梯。事務(wù)在執(zhí)行過程中發(fā)生錯誤兜畸，會被回滾（Rollback）到事務(wù)開始前的狀態(tài)，就像這個事務(wù)從來沒有執(zhí)行過一樣碘梢。

一致性：在事務(wù)開始之前和事務(wù)結(jié)束以后咬摇，數(shù)據(jù)庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預(yù)設(shè)規(guī)則煞躬，這包含資料的精確度肛鹏、串聯(lián)性以及后續(xù)數(shù)據(jù)庫可以自發(fā)性地完成預(yù)定的工作。

隔離性：數(shù)據(jù)庫允許多個并發(fā)事務(wù)同時對其數(shù)據(jù)進(jìn)行讀寫和修改的能力恩沛，隔離性可以防止多個事務(wù)并發(fā)執(zhí)行時由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致在扰。事務(wù)隔離分為不同級別，包括讀未提交（Read uncommitted）雷客、讀提交（read committed）芒珠、可重復(fù)讀（repeatable read）和串行化（Serializable）。

持久性：事務(wù)處理結(jié)束后搅裙，對數(shù)據(jù)的修改就是永久的皱卓，即便系統(tǒng)故障也不會丟失裹芝。

在 MySQL 命令行的默認(rèn)設(shè)置下，事務(wù)都是自動提交的娜汁，即執(zhí)行 SQL 語句后就會馬上執(zhí)行 COMMIT 操作嫂易。因此要顯式地開啟一個事務(wù)務(wù)須使用命令 BEGIN 或 START TRANSACTION，或者執(zhí)行命令 SET AUTOCOMMIT=0存炮，用來禁止使用當(dāng)前會話的自動提交炬搭。

事務(wù)控制語句：

BEGIN 或 START TRANSACTION 顯式地開啟一個事務(wù)；

COMMIT 也可以使用 COMMIT WORK穆桂，不過二者是等價的宫盔。COMMIT 會提交事務(wù)，并使已對數(shù)據(jù)庫進(jìn)行的所有修改成為永久性的享完；

ROLLBACK 也可以使用 ROLLBACK WORK灼芭，不過二者是等價的“阌郑回滾會結(jié)束用戶的事務(wù)彼绷，并撤銷正在進(jìn)行的所有未提交的修改；

SAVEPOINT identifier茴迁，SAVEPOINT 允許在事務(wù)中創(chuàng)建一個保存點(diǎn)寄悯，一個事務(wù)中可以有多個 SAVEPOINT；

RELEASE SAVEPOINT identifier 刪除一個事務(wù)的保存點(diǎn)堕义，當(dāng)沒有指定的保存點(diǎn)時猜旬，執(zhí)行該語句會拋出一個異常；

ROLLBACK TO identifier 把事務(wù)回滾到標(biāo)記點(diǎn)倦卖；

SET TRANSACTION 用來設(shè)置事務(wù)的隔離級別洒擦。InnoDB 存儲引擎提供事務(wù)的隔離級別有READ UNCOMMITTED、READ COMMITTED怕膛、REPEATABLE READ 和 SERIALIZABLE熟嫩。

MYSQL 事務(wù)處理主要有兩種方法：

1.用 BEGIN, ROLLBACK, COMMIT來實(shí)現(xiàn)

BEGIN 開始一個事務(wù)
ROLLBACK 事務(wù)回滾
COMMIT 事務(wù)確認(rèn)

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

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

數(shù)據(jù)庫函數(shù)

日期函數(shù)

聚合函數(shù)

字符串函數(shù)

控制流函數(shù)

數(shù)學(xué)函數(shù)

比較函數(shù)

其他函數(shù)

MySQL 常用操作

1.啟動\關(guān)閉 MySQL systemctl start\stop mysqld

2.查看mysql狀態(tài) systemctl status mysqld

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

4.查看所有數(shù)據(jù)庫 show databases;

5.使用某個數(shù)據(jù)庫 use db01;

6.查看數(shù)據(jù)庫下所有表 show tables;

7.查看表字段詳情 describe t_user;

8.導(dǎo)出mysql數(shù)據(jù) mysqldump -uroot -ppass db_name 表名> mysql_bak.sql