前言
為啥學習MySQL呢？因為MySQL是最流行的關係型資料庫管理系統之一，在web應用方面，MySQL是最好的軟體。MySQL所使用的sql語言是用於訪問資料庫的最常用標準化語言。
放心，讀這期內容的朋友們都適合：網站開發，軟體開發或者愛好者。
1.MySQL的入門
什麼是資料庫呢？
- 資料庫，它是按照資料結構來組織，儲存和管理資料的倉庫。
- 資料庫管理系統, 指資料庫系統中對資料進行管理的軟體系統。
讓我來整理一張思維導圖：
細節掌握：
- 安裝配置，常用命令，運算元據庫；
- 整型與浮點型，日期時間型與字元型；
- 建立與檢視資料庫表，修改資料庫表，刪除資料庫表；
- 非空約束，主鍵約束，唯一約束，預設約束，外來鍵約束；
- 管理工具：
MySQL Workbench，SQLyog； - 單表資料記錄的插入與自動編號，單表資料記錄的更新，單表資料記錄的刪除，單表資料記錄的查詢，對查詢結果進行分組，對查詢結果進行排序，通過limit語句限制查詢記錄的數量；
- mysql的運算子，數值函式，字元函式，日期時間函式，聚合函式，資訊函式與加密函式；
- 使用比較運算子引發的子查詢，插入記錄時使用的子查詢
- 多表連線，內連線，外連線，自連線，多表更新，多表刪除
- 建立，使用自定義函式
- 建立儲存過程，使用儲存過程
mysql官網：
安裝包下載：（安裝操作）
點選安裝：
產品配置的操作：
開啟服務框用win+r，輸入services.msc
2. mysql目錄結構
bin目錄：用於儲存一些可執行檔案include目錄：用於儲存包含的一些標頭檔案lib目錄：用於儲存一些庫檔案share目錄：用於儲存錯誤資訊，字符集檔案等data目錄：用於放置一些日誌檔案以及資料庫my.ini檔案：資料庫的配置檔案
啟動與停止：
mysql引數：
| 引數 | 描述 |
|---|---|
-u | 使用者名稱 |
-p | 密碼 |
-V | 輸出版本資訊並且退出 |
-h | 主機地址 |
3.常用命令
修改使用者密碼的命令：
mysqladmin 命令用於修改使用者密碼
mysqladmin 命令格式：
mysqladmin -u使用者名稱 -p舊密碼 password新密碼
顯示資料庫的命令
show databases;
使用資料庫的命令
use 資料庫的名稱
顯示當前連線的資訊
- 顯示當前連線的資料庫：
select database(); - 顯示當前伺服器版本：
select version(); - 顯示當前日期時間：
select now(); - 顯示當前使用者：
select user();
4.運算元據庫（建立，修改，刪除）
建立資料庫SQL：
create database [if not exists] db_name
[default] character set [=] charset_namecreate database database_name;
修改資料庫的語法格式：
alter database db_name
[default] character set [=] charset_name
刪除資料庫語法格式：
drop database [if exitsts] db_name;
5.資料庫-資料型別
瞭解資料型別：（藉助圖書管理系統）
圖書類別表：
類別編號（category_id） 類別名稱(category) 父類別(parent_id)
1 計算機 0
2 醫學 0圖書資訊表：
圖書編號(book_id) 類別編號(book_category_id) 書名（book_name） 作者(author) 價格(price) 出版社（press） 出版時間（pubdate） 庫存（store）借閱資訊表：
圖書編號（book_id） 身份證號(card_id) 借出日期（borrow_date） 歸還日期(return_date) 是否歸還(status)讀者資訊表
身份證號（card_id） 姓名(name) 性別(sex) 年齡(age) 聯絡電話(tel) 餘額(balance)資料型別：
整型：TINYINT-1位元組 SMALLINT-2位元組 MEDIUMINT-3位元組 INT-4位元組 BIGINT-8位元組
浮點數型別和定點數型別：
float-4個位元組
double-8個位元組
decimal日期時間型別：
字元型：
6.資料庫表結構的操作
- 建立和檢視資料表
建立資料表:create table
create table <表名>
(
列名1 資料型別[列級別約束條件][預設值],
列名2 資料型別[列級別約束條件][預設值],
...
[表級別約束條件]
);
- 檢視資料庫表：
show tables [from db_name];- 檢視資料表基本結構：
show columns from tbl_name;
describe <表名> /DESC<表名>
show create table tbl_name;
- 修改資料庫表
新增列：
alter table <表名>
add <新列名> <資料型別>
[ 約束條件 ] [first | after 已存在列名];
修改列名：
alter table <表名>
change <舊列名> <新列名> <新資料型別>;
修改列的資料型別：
alter table <表名> MODIFY <列名> <資料型別>
修改列的排列位置
alter table<表名>
MODIFY <列1> <資料型別> FIRST|AFTER<列2>
刪除列：
alter table <表名> drop <列名>;
修改表名：
alter table <舊錶名> RENAME [TO] <新表名>;
- 刪除資料庫表
drop table [if exists] 表1,表2,...表n;
檢視錶分割槽
建立表分割槽：使用partition by型別（欄位）
使用values less than操作符定義分割槽
create table bookinfo(
book_id int,
book_name varchar(20)
)
partition by range(book_id)(
partition p1 values less than(20101010),
partition p3 values less than MAXVALUE
);
7.子查詢
select price from bookinfo where book_id = 20101010;
select * from readerinfo;
update readerinfo set balance = balance-(select price from bookinfo where book_id = 20101010) * 0.05 where card_id = '2323232342sxxxxx';什麼是子查詢呢？
它是指巢狀在其他sql語句內的查詢語句。
select * from table1 where col1 = (select col2 from table2);insert into bookcategory(category,parent_id)values('x',2),('y',2);
insert into bookinfo(book_id,book_category_id,book_name,author,price,press,pubdate,store)
values
(45245244, 6, 'x', '1,2,3 等', 115, '出版社', '2020-06-01',10),
(45342545, 6, 'y', '1, 2',27.8, '出版社', '2020-07-01', 5);
update readerinfo set balance = 500 where card_id = '683246';
insert into borrowinfo(book_id,card_id,borrow_date,return_date,status)
values
(35452455,'5724154','2020-10-10','2020-11-10','否');查詢借閱資訊表, 顯示借 xx這本書的借閱記錄
select * from borrowinfo where book_id = (select book_id from bookinfo where book_name = 'xx');查詢圖書資訊表, 顯示圖書價格小於圖書平均價格的所有圖書資訊
select * from bookinfo where price < (select round(avg(price),2) from bookinfo);查詢圖書資訊表，顯示圖書類別不是’資料庫’的所有圖書資訊
select * from bookinfo where book_category_id<>(select category_id from bookcategory where category = '資料庫');查詢圖書資訊表，顯示圖書類別為’計算機’的所有圖書資訊
select * from bookcategory;
select * from bookinfo where book_category_id = ANY(select category_id from bookcategory where parent_id = 1);
select * from bookinfo where price > ANY (select price from bookinfo where book_category_id =4);
select * from bookinfo where price > ALL (select price from bookinfo where book_category_id =4); 查詢圖書資訊表，顯示圖書類別為’2’的所有圖書資訊
in 後面的子查詢返回一個資料列，等於資料列裡的任意一個值都是滿足條件的
select * from bookinfo where book_category_id in (select category_id from bookcategory where parent_id = 2);
select * from bookinfo where book_category_id = any (select category_id from bookcategory where parent_id = 2);檢視圖書類別表中是否有’y’的類別，如果有，則檢視圖書資訊表
select * from bookinfo where exists (select category_id from bookcategory where category='y');
select * from bookinfo where exists (select category_id from bookcategory where category='x');insert into select 語句從一個表複製資料，然後把資料插入到一個已存在的表中。
insert into table2 select * from table1;需要建立一張罰款記錄資訊表，包含如下資訊：圖書編號、身份證號、應還日期、實際還書日期，罰款金額
記錄來源於借閱資訊表超出還書時間還未還書的讀者
create table readerfee(
book_id int,
card_id char(18),
return_date date,
actual_return_date date,
book_fee decimal(7,3),
primary key(book_id,card_id)
);
select book_id,card_id,return_date from borrowinfo where datediff(sysdate(),return_date)>0 and status = '否';
insert into readerfee(book_id,card_id,return_date) select book_id,card_id,return_date from borrowinfo where datediff(sysdate(),return_date)>0 and status = '否';
select * from readerfee;身份證號為5461xxxxxxx的讀者將超限的圖書20201101歸還，根據描述實現如下需求：
- 更新借閱資訊表，將借閱狀態（status）更新為‘是’。
- 更新罰款記錄資訊表，更新實際還書日期和罰款金額，罰款金額為每超出一天扣0.2元。
update borrowinfo set status = '是' where book_id = 20201101 and card_id = '5461xxxxxxx';
select * from borrowinfo;
update readerfee set actual_return_date=sysdate(), book_fee=datediff(sysdate(),return_date)*0.2 where book_id = 20201101 and card_id = '5461xxxxxxx';
select * from readerfee;8.mysql的約束
它事一種限制，通過對錶的行或列的資料做出限制，來確保表的資料的完整性，唯一性。
表結構：
圖書（圖書編號book_id，類別編號book_category_id，書名book_name，作者author)
在mysql中常用的幾種約束型別：
| 約束型別 | 非空約束 | 主鍵約束 | 唯一約束 | 預設約束 | 外來鍵約束 |
|---|---|---|---|---|---|
| 關鍵字 | not null | primary key | unique | default | foreign key |
圖書資訊表：
(圖書編號book_id，類別編號book_category_id，書名book_name，作者author，價格price，出版社press，出版時間pubdate，庫存store)
圖書類別表：
（類別編號category_id - 主鍵，類別名稱category - 唯一，父類別parent_id -非空）
讀者資訊表：
(身份證號card_id，姓名name，性別sex，年齡age，聯絡電話tel，餘額balance)
借閱資訊表：
(圖書編號book_id,身份證號card_id,借出日期borrow_date，歸還日期return_date,是否歸還status)
非空約束
null欄位值可以為空
not null欄位值禁止為空
非空約束
非空約束指欄位的值不能為空。對於使用了非空約束的欄位如果使用者在新增資料時，沒有指定值，資料庫系統會報錯。
列名 資料型別 not null建立表時新增非空約束
create table bookinfo(
book_id int,
book_name varchar(20) not null
);刪除非空約束
alter table bookinfo modify book_name varchar(20);通過修改表新增非空約束
alter table bookinfo modify book_name varchar(20) not null;
主鍵約束
主鍵約束：要求主鍵列的資料唯一，並且不允許為空，主鍵能夠唯一地標識表中的一條記錄。
主鍵的型別：
主鍵分為單欄位主鍵和多欄位聯合主鍵
單欄位主鍵：是由一個欄位組成
在定義列的同時指定主鍵
列名 資料型別 primary key;
在列定義的後邊指定主鍵
[constraint<約束名>] primary key(列名);建立表時新增主鍵約束
create table bookinfo(
book_id int primary key,
book_name varchar(20) not null
);create table bookinfo(
book_id int,
book_name varchar(20) not null,
constraint pk_id primary key(book_id)
);刪除主鍵約束
ALTER TABLE bookinfo DROP PRIMARY KEY;通過修改表的方式新增主鍵約束
ALTER TABLE bookinfo ADD PRIMARY KEY(book_id);
多欄位聯合主鍵，複合主鍵
主鍵有多個欄位聯合組成。primary key(欄位1，欄位2，...欄位n);
create table borrowinfo(
book_id int,
card_id char(18),
primary key(book_id,card_id)
);通過修改表為列新增主鍵
create table bookinfo(
book_id int,
book_name varchar(20) not null
);
alter table bookinfo modify book_id int primary key;
alter table bookinfo add primary key(book_id);
alter table bookinfo add constraint pk_id primary key(book_id);
唯一約束
唯一約束要求該列唯一，允許為空，唯一約束可以確保一列或者幾列不出現重複值。
語法規則：
列名 資料型別 unique
[constraint <約束名>] unique(<列名>)建立表時新增唯一約束
CREATE TABLE bookinfo(
book_id INT PRIMARY KEY,
book_name VARCHAR(20) NOT NULL UNIQUE
);
或：
create table bookinfo(
book_id int primary key,
book_name varchar(20) not null,
constraint uk_bname unique(book_name)
);通過修改表的方式新增唯一約束
alter table bookinfo modify book_name varchar(20) unique;
ALTER TABLE bookinfo ADD UNIQUE(book_name);
alter table bookinfo
add constraint uk_bname unique(book_name);刪除唯一約束
ALTER TABLE book_info DROP KEY uk_bname;
ALTER TABLE book_info DROP INDEX uk_bname;
唯一約束和主鍵約束的區別
- 一個表中可以有多個
unique宣告，但只能有一個primary key宣告 - 宣告為
primary key的列不允許有空值 - 宣告為
unique的列允許空值
預設約束
預設約束是指某列的預設值
列名 資料型別 default 預設值建立表時新增預設約束
CREATE TABLE bookinfo(
book_id INT PRIMARY KEY,
press VARCHAR(20) DEFAULT '出版社'
);通過修改表的方式新增預設約束
ALTER TABLE bookinfo
ALTER COLUMN press SET DEFAULT '出版社';
alter table bookinfo
modify press varchar(10) default '出版社';刪除預設約束
alter table bookinfo modify press varchar(20);
ALTER TABLE bookinfo
ALTER COLUMN press DROP DEFAULT;外來鍵約束
外來鍵是用來在兩個表的資料之間建立連結，可以是一列或者多列，一個表可以有一個或者多個外來鍵。
外來鍵對應的是參照完整性，一個表的外來鍵可以為空值，若不為空值，則每一個外來鍵必須等於另一個表中主鍵的某個值。
作用：保持資料的一致性，完整性。
建立表時新增外來鍵約束
圖書類別表(父表)
CREATE TABLE bookcategory(
category_id INT PRIMARY KEY,
category VARCHAR(20),
parent_id INT
);
圖書資訊表(子表)
CREATE TABLE bookinfo(
book_id INT PRIMARY KEY,
book_category_id INT,
CONSTRAINT fk_cid FOREIGN KEY(book_category_id) REFERENCES bookcategory(category_id)
);通過修改表的方式新增外來鍵約束
ALTER TABLE bookinfo
ADD FOREIGN KEY(book_category_id) REFERENCES bookcategory(category_id);刪除外來鍵約束
ALTER TABLE bookinfo DROP FOREIGN KEY fk_cid;
外來鍵約束的參照操作
cascade，從父表刪除或更新且自動刪除或更新子表中匹配的行
create table bookinfo(
book_id int primary key,
book_category_id int,
constraint fk_cid foreign key (book_category_id) references bookcategory(category_id) on delete cascade);建立圖書管理系統表
圖書類別表
create table bookcategory(
category_id int primary key,
category varchar(20) not null unique,
parent_id int not null
);圖書資訊表
create table bookinfo(
book_id int primary key,
book_category_id int,
book_name varchar(20) not null unique,
author varchar(20) not null,
price float(5,2) not null,
press varchar(20) default '機械工業出版社',
pubdate date not null,
store int not null,
constraint fk_bcid foreign key(book_category_id) references bookcategory(category_id)
);讀者資訊表
create table readerinfo(
card_id char(18) primary key,
name varchar(20) not null,
sex enum('男','女','保密') default '保密',
age tinyint,
tel char(11) not null,
balance decimal(7,3) default 200
);借閱資訊表
create table borrowinfo(
book_id int,
card_id char(18),
borrow_date date not null,
return_date date not null,
status char(11) not null,
primary key(book_id,card_id)
);9.資料庫表記錄的操作
單表資料記錄的插入
語法格式：
insert into table_name(column_list) values(value_list);為表的所有列插入資料
insert into bookcategory
(category_id,category,parent_id)values
(1,'x',0);
insert into bookcategory values(2,'y',0);為表的指定列插入資料
insert into readerinfo
(card_id,name,tel)values('4562135465','張飛','4651354651');同時插入多條記錄
insert into bookcategory(category_id,category,parent_id)values(3,'x',1),(4,'y',1),(5,'z',2);將查詢結果插入的表中
insert into bookcategory select * from test where id>5;自動增加
設定表的屬性值自動增加：
列名 資料型別 auto_increment建立表時新增自增列
create table bookcategory_tmp(
category_id int primary key auto_increment,
category varchar(20) not null unique,
parent_id int not null
)auto_increment=5;測試自增列
insert into bookcategory_tmp(category,parent_id)values('dadaqianduan',0);去掉自增列
alter table bookcategory_tmp modify category_id int;新增自增列
alter table bookcategory_tmp modify category_id int auto_increment;修改自增列的起始值
alter table bookcategory_tmp auto_increment = 15;
insert into bookcategory_tmp(category,parent_id)values('文學',0);刪除圖書資訊表的外來鍵
alter table bookinfo drop foreign key fk_bcid;為圖書類別表新增自動編號的功能
alter table bookcategory modify category_id int auto_increment;恢復關聯
alter table bookinfo add constraint fk_bcid foreign key(book_category_id)references bookcategory(category_id);單表資料記錄的更新
向借閱資訊表插入一條借閱資訊
insert into borrowinfo(book_id,card_id,borrow_date,return_date,status)values(20202010,46516874,'2020-11-29','2020-12-29','否');更新讀者資訊表中的餘額
檢視書的價格 79.80
select price from bookinfo where book_id = 20202010;更新餘額
update readerinfo set balance = balance - 79.80*0.05 where card_id = '46516874';
select * from readerinfo;更新圖書資訊表的庫存
update bookinfo set store = store -1 where book_id = 20150201;
select * from bookinfo;單表資料記錄的刪除
刪除指定條件的記錄
delete from readerinfo where card_id = '46461265464565';刪除表中所有記錄
delete from readerinfo;
truncate table readerinfo;快想要刪除表中的所有記錄，可以使用truncate table語句，truncate將直接刪除原來的表，並重新建立一個表，其語法結構：
truncate table table_name查詢兒科學的類別編號
select category_id from bookcategory where category='兒科學';刪除圖書編號為5的圖書資訊
delete from bookinfo where book_category_id = 5;刪除圖書類別表中兒科學這個類別
delete from bookcategory where category = '兒科學';單表資料記錄的查詢
查詢所有列
select * from bookcategory;
select category_id,category,parent_id from bookcategory;查詢指定列
select category from bookcategory;
select category_id,category from bookcategory;查詢指定條件的記錄
select book_id,book_name,price from bookinfo where press='出版社';查詢結果不重複的記錄
select distinct press from bookinfo;檢視空值
select * from readerinfo where age is null;分組
統計讀者資訊表中男讀者的人數
select count(*) from readerinfo where sex='男';將讀者資訊表中的記錄按性別進行分組
select sex from readerinfo group by sex;將讀者資訊表中的記錄按性別進行分組，並統計每種性別的人數
select sex,count(*) from readerinfo group by sex;將讀者資訊表中的記錄按性別進行分組，分組後人數大於的性別
select sex from readerinfo group by sex having count(sex)>2;排序
通過order by子句對查詢的結果進行排序
order by 列名 [asc|desc]排序方向：
- 排序分為升序和降序，預設為升序
- 升序
asc - 降序
desc
單列排序
select * from bookinfo order by price;多列排序
select * from bookinfo order by price,store;指定排序方向
select * from bookinfo order by price,store desc;limit語句限制查詢記錄的數量
前3行記錄
select * from bookinfo limit 3;從第3條記錄開始的後2條記錄
select * from bookinfo limit 2,2;
select * from bookinfo limit 2 offset 2;insert into bookinfo(book_id,book_category_id,book_name,author,price,press,pubdate,store)
values
(454235424,4, '123', 'xxx',85.8, '出版社', '2020-04-01', 10),
(452454542,4, '456', 'xxx', 35.5, '出版社', '2020-08-01', 20),
(454578754,4, '789', 'xxx', 46.6, '出版社', '2020-05-01',8);將圖書資訊按照庫存進行分組，統計每組庫存下的個數，然後按庫存進行降序排序，並檢視結果中的前四條記錄
select store,count(*)from bookinfo
group by store
order by store desc
limit 4;10.運算子與函式
MySQL 主要有以下幾種運算子：
- 算術運算子
- 比較運算子
- 邏輯運算子
- 位運算子
算術運算子
比較運算子
邏輯運算子
位運算子
運算子優先順序
讀者的身份證號，姓名，電話，餘額。
select card_id, name, tel, balance from readerinfo where balance-200<=0;檢視讀者資訊表中，餘額大於200的讀者資訊。
select * from readerinfo where balance>200;檢視讀者資訊表中，餘額不等於200的讀者資訊。
select * from readerinfo where balance <> 200;檢視讀者資訊表中，年齡不為空的讀者資訊。
select * from readerinfo where age is not null;檢視讀者資訊表中，餘額在350到450之間的讀者資訊。
select * from readerinfo where balance between 350 and 450;select * from readerinfo where name in('dada','dada1','dada2');
select * from readerinfo where name like '張_';
select * from readerinfo where tel like '135%';
select * from bookinfo where price>50 and store<5;
select * from bookinfo where price>80 or press = '出版社';
select * from bookinfo where price not between 50 and 100;數值函式
ceil返回大於x的最小整數值
select ceil(28.55); // 29floor返回小於x的最大整數值
select floor(28.55); // 28四捨五入
round返回最接近於引數x的整數，對引數x進行四捨五入
select round(28.55); // 29
select round(28.55,1),round(28.55,0),round(28.55,-1);
// 28.6 29 30截斷函式
select truncate(28.55,1),truncate(28.55,0),truncate(28.55,-1);
// 28.5 28 20取模，返回x被y除後的餘數
select mod(11,2); // 1select book_id,book_name,price, round(price) from bookinfo;
select * from bookinfo where mod(book_id,2)=0;字元函式
字串連線
select concat('hello','world');
select concat_ws('-','hello','world');字母轉換大小寫
select lower('Hello World');
select upper('Hello World');求長度
select length(' hello ');刪除空格
select ltrim(' hello '),length(ltrim(' hello '));
select rtrim(' hello '),length(rtrim(' hello '));
select trim(' hello '),length(trim(' hello '));擷取字串
select substring('hello world',1,5);
select substring('hello world',-5,2);獲取指定長度的字串
select left('hello world', 5); // hello
select right('hello world', 5); // world替換函式
select replace('hello world','world','mysql'); // hello mysql格式化函式
select format(1234.5678,2),format(1234.5,2),format(1234.5678,0);
//1234.57 1234.50 12345
select book_id,book_name,format(price,2)from bookinfo;日期和時間函式
檢視當前的系統日期
select curdate();
// 2020-02-02select curdate()+0;
select curtime()+0;檢視當前的系統日期和時間
select now(); // 2020-10-10 12:12:12
select sysdate(); // 2020-10-10 12:12:12date_add(date,interval expr type): year,month,day,week,hour
日期的加運算
select date_add('2020-01-01', interval 5 month); // 2020-06-01
計算兩個日期之間間隔的天數
select datediff('2020-02-10','2020-02-01');
日期格式化
select date_format('2020-02-01', '%Y%m');聚合函式（分組函式）
| 名稱 | 描述 |
| avg() | 返回某列的平均值 |
| count() | 返回某列的行數 |
| max() | 返回某列的最大值 |
| min() | 返回某列的最小值 |
| sum() | 返回某列值的和 |
求圖書資訊表中，所有圖書的平均價格。
select avg(price) from bookinfo;求圖書資訊表中，所有圖書的總價格。
select sum(price) from bookinfo;求圖書資訊表中的最大庫存。
select max(store) from bookinfo;求圖書資訊表中的最小庫存。
select min(store) from bookinfo;求圖書資訊表中有多少種圖書。
select count(*) from bookinfo;按類別進行分組, 查詢每種類別下有多少種圖書以及每種類別圖書的庫存總和。
select book_category_id as '圖書類別',count(book_id) as '圖書種類', sum(store) as '庫存總和' from bookinfo group by book_category_id;資訊函式與加密函式
系統資訊函式
檢視當前MySQL伺服器版本的版本號
select version();
檢視MySQL伺服器當前連線的次數
select connection_id();
檢視當前的資料庫名
select schema();
檢視當前登入的使用者名稱
select user();加密函式
select md5('test');
create table myuser(
username varchar(10),
password varchar(35)
);
insert into myuser values('user1',md5('pwd1'));
select * from myuser;
select * from myuser where username = 'user1' and password = md5('pwd1');
select password('rootpwd');
set password = password('rootpwd');
select user,authentication_string from mysql.user;11.多表連線查詢
多表連線查詢是從多個表中獲取資料。
由圖書資訊表：（圖書編號book_id，類別編號book_category_id，書名book_name）
由圖書類別表：（類別編號category_id，類別名稱category，父類別parent_id)
獲取表：（圖書編號book_id,書名book_name,類別名稱category)
多表連線的語法結構：
table_reference
[INNER] JOIN | {LEFT|RIGHT} [OUTER] JOIN
table_reference
on conditional_expr多表連線
通過檢視圖書資訊表和圖書類別表
來獲取圖書編號、圖書名稱、圖書類別
select book_id,book_name,category from bookinfo inner join bookcategory on bookinfo.book_category_id = bookcategory.category_id;內連線
根據連線條件從多個表中查詢選擇資料，顯示這些表中與連線條件相匹配的資料行，組合成新記錄。（內連線就是兩者共同都有的）
內連線的語法結構：
select column_list
from t1
[INNER] JOIN t2 ON join_condition1
[INNER] JOIN t3 ON join_condition2
...]
where where_conditions;由於圖書借閱統計的需要，想查詢未歸還圖書的圖書編號，圖書名稱，身份證號，姓名，電話，歸還日期, 是否歸還。
select borrowinfo.book_id,book_name,borrowinfo.card_id, name, tel, return_date, status from borrowinfo
inner join bookinfo on borrowinfo.book_id = bookinfo.book_id
inner join readerinfo on borrowinfo.card_id = readerinfo.card_id
where borrowinfo.status = '否';
select t1.book_id,book_name,t1.card_id, name, tel, return_date, status from borrowinfo t1
join bookinfo t2 on t1.book_id = t2.book_id
join readerinfo t3 on t1.card_id = t3.card_id
where t1.status = '否';外連線
外連線將查詢多個表中相關聯的行。
外連線分為：左外連線 left outer join；右外連線right outer join
根據業務需要，我們需要檢視圖書類別表中的所有類別下都有哪些圖書。
select book_id, book_name, category from bookcategory
left join bookinfo on bookcategory.category_id = bookinfo.book_category_id
where parent_id<>0;
select book_id, book_name, category from bookinfo a
right join bookcategory b on b.category_id = a.book_category_id;
select * from bookcategory;左外連線：顯示左表全部記錄，右表滿足連線條件的記錄。
右外連線：顯示右表全部記錄，左表滿足連線條件的記錄。
語法結構：
select column_list
from t1
left | right [outer] join t2 on join_condition1;自連線
如果在一個連線查詢中，涉及的兩個表都是同一個表，這種查詢稱為自連線
查詢所有圖書類別的圖書類別編號，類別名稱，上級分類名稱。
select * from bookcategory;
select s.category_id as'圖書類別編號', s.category as '圖書類別名稱', p.category as'圖書的上級分類名稱' from bookcategory s
inner join bookcategory p
on s.parent_id = p.category_id;多表更新
update
table1 {[inner] join | {left|right} [outer] join} table2
on conditional_expr
set col1 = {expr1|default}
[,col2 = {expr2|default}]...
[where where_condition]身份證號為432xxxxxx的讀者將超時的圖書86154歸還，根據描述實現如下需求：
- 更新借閱資訊表，將借閱狀態（status）更新為‘是’。
- 更新罰款記錄資訊表，更新實際還書日期和罰款金額，罰款金額為每超出一天扣0.2元。
- 同時更新讀者資訊表的餘額。（在餘額中扣除罰款金額）
update readerfee t1 join readerinfo t2 on t1.card_id = t2.card_id
set actual_return_date = sysdate(),book_fee=datediff(sysdate(),return_date)*0.2,balance = balance - book_fee
where t1.book_id = 86154 and t1.card_id = '432xxxxxx';
select * from readerinfo;多表刪除
delete table1[.*], table2[.*]
from table1 {[inner]join|{left|right}[outer]join} table2
on conditional_expr
[where where_condition]圖書類別表，圖書資訊表：
由於業務需求，需要刪除圖書類別表中在圖書資訊表中沒有圖書記錄的類別。
select book_id,book_name,category from bookcategory_bak t1
left join bookinfo_bak t2
on t1.category_id = t2.book_category_id
where parent_id<>0;
delete t1 from bookcategory_bak t1
left join bookinfo_bak t2
on t1.category_id = t2.book_category_id
where parent_id<>0 and book_id is null;
select * from bookcategory_bak;需要刪除圖書類別表的程式語言的類別，以及圖書資訊表中關於程式語言的圖書記錄。
select book_id,book_name,category_id,category from bookcategory_bak t1
inner join bookinfo_bak t2
on t1.category_id = t2.book_category_id;
delete t1,t2 from bookcategory_bak t1
inner join bookinfo_bak t2
on t1.category_id = t2.book_category_id
where t1.category_id = 3; 多表連線
根據連線查詢返回的結果：內連線（inner join），外連線（outer join），交叉連線(cross join)。
根據連線條件所使用的操作符：相等連線，不等連線。
12.自定義函式
建立函式
CREATE FUNCTION 函式名(引數列表) RETURNS 返回型別
BEGIN
函式體
END呼叫函式
SELECT 函式名(引數列表)檢視函式
SHOW FUNCTION STATUS;刪除函式
DROP FUNCTION IF EXISTS function_name;函式：需要有返回值，可以指定0~n個引數
建立自定義函式：
create function function_name([func_parameter])
returns type
[characteristics..] routine_bodyCharacteristics指定儲存函式的特性，取值舉例：
sql security{definer|invoker}指明誰有許可權來執行。
definer表示只有定義者才能執行。
invoker表示擁有許可權的呼叫者才可以執行，預設情況下，系統指定為definer。
comment 'string':註釋資訊，可以用來描述儲存函式。函式體是由sql程式碼構成，可以簡單的sql語句。如果為複合結構需要使用begin...end語句，複合結構可以包含宣告，流程控制。
select length('hello');
select date_format(pubdate,'%Y-%m') from bookinfo;
delimiter //
create function ym_date(mydate date)
returns varchar(15)
begin
return date_format(mydate,'%Y-%m');
end//
delimiter;
select ym_date(pubdate) from bookinfo;建立自定義函式：
語法格式：
create function function_name([func_parameter])
returns type
[characteristics...] routine_bodyselect length('hello');
select date_format(pubdate,'%Y-%m') from bookinfo;delimiter //
create function ym_date(mydate date)
returns varchar(15)
begin
return date_format(mydate,'%Y-%m');
end//
delimiter ;
使用（呼叫）自定義函式
select ym_date(pubdate) from bookinfo;例項分析函式：
建立一個函式
delimiter $$ --定界符
--- 開始建立函式
create function user_main_fn(v_id int)
returns varchar(50)
begin
--定義變數
declare v_userName varchar(50);
--給定義的變數賦值
select f_userName info v_userName from t_user_main
where f_userId = v_id;
--返回函式處理結果
return v_userName;
end $$ --函式建立定界符
delimiter;自定義函式兩個必要條件：引數，返回值
建立自定義函式
create function function_name
returns
{string|integer|real|decimal}
routine_body
語法格式：
CREATE FUNCTION function_name([func_parameter])
RETURNS type
[characteristics … ] routine_body- function_name : 函式名稱
- func_parameter : 函式的引數列表
- RETURNS type : 指定返回值的型別
- Characteristics : 指定儲存函式的特性
- routine_body : 函式體
建立無參的自定義函式：
刪除自定義函式
DROP FUNCTION [IF EXISTS] func_name;
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H點：%i分：%s秒')
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(), '%Y年%m月%d日 %H點：%i分：%s秒');
SELECT f1();複合結構體的函式
-- 將語句結束符改為$$,為了防止下面的函式將;看成是語句的結束
DELIMITER $$
CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
RETURN
BEGIN
INSERT INTO table_1(username) VALUES(username);
LAST_INSERT_ID();
END;
-- 將分隔符改回來
DELIMITER ;流程控制的使用
常用的流程控制語句：
- IF條件判斷語句-if
- CASE條件判斷語句-case
- WHILE迴圈語句-while
- LOOP迴圈語句-loop
- REPEAT迴圈語句-repeat
13.儲存過程
區域性變數以關鍵字DECLARE宣告
DECLARE var_name [, varname2, varname3 …] date_type [DEFAULT value];
例：DECARE num INT DEFAULE 10;
內部BEGIN…END塊中定義的變數只在該塊內有效
會話變數的作用範圍為整個程式
語法結果
create procedure proc_name([proc_parameter])
[characteristics...] routine_bodydelimiter //
create procedure selectproc1()
begin
select book_id, book_name, price, store from bookinfo;
end //
delimiter;
call selectproc();刪除儲存過程：
drop procedure [if exists] proc_name;建立一個查詢圖書的編號、書名、價格和庫存的儲存過程。
delimiter //
create procedure selectproc1()
begin
select book_id,book_name,price,store from bookinfo;
end//
delimiter ;
呼叫儲存過程
call selectproc1();建立查詢圖書編號、書名、圖書類別的儲存過程
delimiter //
create procedure proc1()
begin
select book_id,book_name,category from bookinfo t1
join bookcategory t2
on t1.book_category_id = t2.category_id;
end//
delimiter ;
call proc1();
設計一個儲存過程，刪除一個讀者，並輸出剩餘讀者的個數。
delimiter //
create procedure proc2(in cid char(18), out num int)
begin
delete from readerinfo where card_id = cid;
select count(card_id) into num from readerinfo;
end//
delimiter ;
select * from readerinfo;
call proc2('6545xx', @num);
select @num;
設計一個儲存過程，實現交換兩個數的處理。
delimiter //
create procedure proc3(inout num1 int, inout num2 int)
begin
declare t int default 0;
set t = num1;
set num1 = num2;
set num2 = t;
end//
delimiter ;
set @n1 = 3, @n2 = 5;
call proc3(@n1,@n2);
select @n1,@n2;
刪除儲存過程
drop procedure proc1;
drop procedure if exists proc2;儲存過程和函式的區別
儲存過程，儲存過程實現的功能比較複製，功能強大，可以執行包括修改表等一系列資料庫操作。
儲存函式，實現的功能針對性比較強。
返回值上的不同
儲存過程：可以返回多個值，也可以不返回值，只是實現某種效果或動作。
儲存函式：必須有返回值，而且只能有一個返回值。
引數不同
儲存過程：儲存過程的引數型別有三種，in,out,inout。
儲存函式：引數型別只有一種，類似於in引數，呼叫函式時需要按照引數的型別指定值即可。
語法結構
儲存過程，儲存過程宣告時不需要指定返回型別。
儲存函式，函式宣告時需要指定返回型別，且在函式體中必須包含一個有效的return語句。
呼叫方式
儲存過程，用call語句進行呼叫
儲存函式，嵌入在sql中使用的，可以在select中呼叫
14.事務
事務必須滿足的四個條件：
atomicity 原子性
consistency 一致性
lsolation 隔離性
durability 永續性控制事務處理
rollback，回滾會結束使用者的事務，並撤銷正在進行的所有未提交的修改
commit，會提交事務，並使已對資料庫進行的所有修改稱為永久性的
savepoint identifier，允許在事務中建立一個儲存點，一個事務中可以有多個savepoint
rollback to identifier，把事務回滾到標記點
事務處理主要有兩種方法
用begin, rollback, commit來實現
begin,start transaction開始一個事務rollback事務回滾commit事務確認
直接用set來改變mysql的自動提交模式
set autocommit = 0禁止自動提交set autocommit = 1開始自動提交
innodb使用事務
從Mysql5.5版本開始,InnoDB是預設的表儲存引擎。
innodb是事務型資料庫的首選引擎，支援事務安全表。
MySql中 delimiter
預設下，delimiter是分號，在命令列客戶端中，如果有一行命令以分號結束，那麼回車後，mysql將會執行該命令。
(告訴mysql直譯器，該段命令是否已經結束了，mysql是否可以執行了。 )
什麼是儲存引擎：資料庫儲存引擎是資料庫底層軟體元件。資料庫管理系統使用資料引擎進行建立，查詢，更新和刪除資料的操作。
mysql的核心就是儲存引擎。
innodb儲存引擎
- 它為
mysql提供了具有提交，回滾和崩潰恢復能力的事務安全儲存引擎。 - 對於處理巨大資料量的資料擁有很好的效能
innodb儲存引擎支援外來鍵完整性約束innodb被用在眾多需要高效能的大型資料庫站點上
設定儲存引擎：
- 設定伺服器的儲存引擎
- 在配置檔案my.ini中的mysqld下面設定需要的儲存引擎
default-storage-engine=InnoDB- 重啟mysql伺服器
建立表（單個）設定儲存引擎
create table mytest(
id int primary key,
name varchar(10)
) engine = innodb default charset = utf8;
修改表的儲存引擎
alter table tablename engine = engineName
15.管理與維護
管理使用者
USE mysql;
select user from user;許可權表：儲存賬號的許可權資訊表：user,db,host,tables_priv,columns_priv和procs_priv
各個許可權表的作用
tables_priv表用來對錶設定操作許可權；columns_priv表用來對錶的某一列設定許可權；procs_priv表可以對儲存過程和儲存函式設定操作許可權。
使用CREATE USER語句建立新使用者
語法格式：
CREATE USER “user”@“host” [IDENTIFIED BY “password”];使用DROP USER 語句刪除使用者
語法格式：
DROP USER user[, user];
例：使用DROP USER刪除賬戶"rose"@"localhost":
DROP USER "rose"@"localhost";示例：
檢視日誌檔案的路徑
show variables like 'log_error';
建立新的日誌資訊表
flush logs;
建立新的日誌資訊表
mysqladmin -uroot -p flush-logs點關注，不迷路
好了各位，以上就是這篇文章的全部內容，能看到這裡的人都是人才。我後面會不斷更新技術相關的文章，如果覺得文章對你有用，歡迎給個“贊”，也歡迎分享，感謝大家 ！！