MySQL Structured Query Language SQL簡介 SQL (Structured Query Language) 是具有數據操 縱和數據定義等多種功能的資料庫語言，這種語言具有交互性特點，能為用戶提供極大的便利，資料庫管理系統應充分利用SQL語言提高電腦應用系統的工作質量 ...
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 開啟自動提交
資料庫函數
日期函數
| 名稱 | 含義 | 案例 |
|---|---|---|
| now() | 返回當前的日期和時間 | select now(); |
| sysdate() | 返回系統時間 | select sysdate(); |
| current_timestamp() | 返回當前時間 | select current_timestamp(); |
| curdate() | 返回當前的日期 | select curdate(); |
| curtime() | 返回當前的時間 | select curtime(); |
| to_days(p1) | 返回從0年到p1年的天數 | select to_days(now()); |
| from_days(p1) | 給出一個天數p1，返回一個日期 | select from_days(1000013); |
| str_to_date(p1, p2) | 字元串轉換為日期 | select str_to_date('08/09/2008', '%m/%d/%Y'); |
| time_to_sec(p1) | 將時間轉換為秒值 | select time_to_sec('01:00:05'); |
| makedate(p1, p2) | 拼湊日期、時間函數 | select makedate(2001,31); |
| data_add(p1, p2) | p1日期加上指定的時間間隔 | select date_add(now(),interval 1 day); |
| date_sub(p1, p2) | p1日期減去指定的時間間隔 | select date(date_sub(now(), interval -1 day)); |
| date_format(p1, p2) | 格式化時間轉換為字元串 | select date_format(now(), '%Y%m%d%H%i%s'); |
| period_diff(p1,p2) | 返回周期P1和P2之間的月數 | select period_diff(9802,199703); |
| quarter(p1) | 返回p1時間的季度 | select quarter(now()); |
| year(p1) | 返回p1所在的年份 | select year(now()); |
| month(p1) | 返回p1所在年的月份 | select month(now()); |
| day(p1) | 返回p1所在月份的天數 | select day(now()); |
| week(p1) | 返回p1所在的年的第幾周 | select week(now()); |
| datadiff(p1, p2) | 返回兩個時間相差的天數 | select datediff("2020-04-18",now()); |
| dayname(p1) | 獲取指定日期的工作日的名稱 | select dayname(sysdate()); |
| dayofweek(p1) | 返回日期的工作日索引 | select dayofweek(current_timestamp()); |
| extract(p1) | 提取日期的一部分 | select extract(day from curdate()); |
| timediff(p1, p2) | 計算兩個TIME或DATETIME值之間的差值 | select timediff("2020-04-19 17:00:00", now()); |
| timestampdiff(p1, p2) | 計算兩個DATE或DATETIME值之間的差值 | select timestampdiff(second,curdate(), now()); |
| weekday(p1) | 返回一個日期表示為工作日/星期幾的索引 | select weekday(now()); |
聚合函數
| 名稱 | 含義 | 案例 |
|---|---|---|
| avg() | 計算一組值或表達式的平均值 | select id,avg(salary) from t_user group by id; |
| count() | 計算表中的行數 | select count(*) from t_user; |
| instr() | 返回子字元串在字元串中第一次出現的位置 | select instr("hello","h"); |
| sum() | 計算一組值或表達式的總和 | select id,sum(salary) from t_user group by id; |
| min() | 在一組值中找到最小值 | selec id,mix(salary) from t_user group by id; |
| max() | 在一組值中找到最大值 | select id,max(salary) from t_suer group by id; |
| group_concat() | 將字元串從分組中連接成具有各種選項 | select group_concat(country) from t_user; |
字元串函數
| 名稱 | 含義 | 案例 |
|---|---|---|
| concat(p1,p1,...) | 將兩個或多個字元串組合成一個字元串 | select concat("aa","bb","cc"); |
| length(p1) | 返回位元組長度 | select length("你"); |
| char_lenght(p1) | 返回字元長度 | select char_length("你"); |
| left(p1,p2) | 獲取指定長度的字元串的左邊部分 | select left("hello",2); |
| right(p1,p2) | 獲取指定長度的字元串的右邊部分 | select right("hello",2); |
| replace(p1,p2,p3) | 搜索並替換字元串中的子字元串 | select replace("hello","h","o"); |
| substring(p1,p2,p3) | 從具有特定長度的位置開始提取一個子字元串 | select substring("hello",1,2); |
| trim(p1) | 從字元串中刪除不需要的字元 | select trim(" hello "); |
| find_in_set(p1,p2) | 在逗號分隔的字元串列表中找到一個字元串 | select find_in_set("a","a,b,c"); |
| format(p1,p2,p3) | 格式化具有特定區域設置的數字，舍入到小數位數。 | select format(14500.2018, 2); |
控制流函數
| 名稱 | 含義 | 案例 |
|---|---|---|
| case() | 如果滿足WHEN分支中的條件，則返回THEN分支中的相應結果，否則返回ELSE分支中的結果。 | |
| if | 根據給定的條件返回一個值。 | |
| ifnull() | 如果第一個參數不為NULL，則返回第一個參數，否則返回第二個參數。 | |
| nullif() | 如果第一個參數等於第二個參數，則返回NULL，否則返回第一個參數。 |
數學函數
| 名稱 | 含義 | 案例 |
|---|---|---|
| abs(x) | 返回X的絕對值 | select abs(-1); |
| floor(x) | 返回不大於x的最大整數 | select floor(100.1); |
| ceil(x)、ceiling(x) | 返回不小於x的最小整數 | select ceil(20.1),ceiling(32.4); |
| truncate(x,d) | 返回數值x保留到小數點後d位的值，截斷時不進行四捨五入 | select truncate(12.22043,2); |
| round(x) | 返回離x最近的整數，截斷時要進行四捨五入 | select round(23.12); |
| round(x,d) | 保留x小數點後d位的值，截斷時要進行四捨五入 | select round(18.1202,2); |
| rand() | 返回0~1的隨機數 | select rand(); |
| sign(x) | 返回x的符號(負數，零或正)對應-1，0或1 | select sign(0); |
| pi() | 返回圓周率的值。預設的顯示小數位數是7位 | select pi(); |
| pow(x,y)、power(x,y) | 返回x的y次乘方的值 | select pow(2, 3), power(3,3); |
| sqrt(x) | 返回非負數的x的二次方根 | select sqrt(5); |
| exp(x) | 返回e的x乘方後的值 | select exp(3); |
| mod(n,m) | 返回n除以m以後的餘數 | select mod(3,4); |
| log(x) | 返回x的自然對數，x相對於基數2的對數 | select log(6); |
| log10(x) | 返回x的基數為10的對數 | select log10(90); |
| radians(x) | 返回x由角度轉化為弧度的值 | select radians(12); |
| degrees(x) | 返回x由弧度轉化為角度的值 | select degrees(23); |
| sin(x)、asin(x) | 前者返回x的正弦，其中x為給定的弧度值；後者返回x的反正弦值，x為正弦 | select sin(23), asin(120); |
| cos(x)、acos(x) | 前者返回x的餘弦，其中x為給定的弧度值；後者返回x的反餘弦值，x為餘弦 | select cos(12), acos(12); |
| tan(x)、atan(x) | 前者返回x的正切，其中x為給定的弧度值；後者返回x的反正切值，x為正切 | select ,atan(12) tan(2); |
| cot(x) | 返回給定弧度值x的餘切 | select cot(14); |
比較函數
| 名稱 | 含義 | 案例 |
|---|---|---|
| coalesce(p1,p2,...) | 返回第一個非空的參數如果都為空則返回null | select coalesce(null,2,3); |
| greatest(p1,p2,...) | 返回最大的數 | select greatest(1,4,0); |
| least(p1,p2,...) | 返回最小值 | select least(1,4,0); |
| isnull(p1) | 判斷p1是否為null是返回1，否則0 | select isnull(1); |
其他函數
| 名稱 | 含義 | 案例 |
|---|---|---|
| cast(p1) | 將任何類型的值轉換為具有指定類型的值。 | select cast('2013-01-01' as datetime); |
| last_insert_id() | 獲取最後插入的記錄的最後生成的序列號。 | select last_insert_id(); |
| sleet(p1) | 睡眠 | select sleep(2) |
| version() | 查看資料庫版本 | select version(); |
| database()、schema() | 查看當前使用的資料庫 | select database(); |
| user()、system_user()、session_user() | 查看當前用戶 | select user(); |
| connection_id() | 返回連接的客戶端中唯一的ID | select connection_id(); |
| charset() | 返回字元串str的字元集 | select charset("hello"); |
| collation() | 返回字元串str的字元排列方式 | select collation("hello"); |
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
