17期-什么是MySQL資料庫？看這一篇干貨文章就夠了！-有解無憂

前言

為啥學習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_name

create 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 主要有以下幾種運算子：

算術運算子
比較運算子
邏輯運算子
位運算子

算術運算子

比較運算子

邏輯運算子

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-y7CkZtkK-1600306739447)(https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/17f47bc51e8b48058fb92eb540b7acd8~tplv-k3u1fbpfcp-zoom-1.image)]

位運算子

運算子優先級

讀者的身份證號，姓名，電話，余額，

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); // 29

floor回傳小于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); // 1

select 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-02

select curdate()+0;

select curtime()+0;

查看當前的系統日期和時間

select now(); // 2020-10-10 12:12:12

select sysdate(); // 2020-10-10 12:12:12

date_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_body

Characteristics指定存盤函式的特性，取值舉例：

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_body

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;

實體分析函式：

創建一個函式
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_body

delimiter // 
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

點關注，不迷路

好了各位，以上就是這篇文章的全部內容，能看到這里的人都是人才，我后面會不斷更新技術相關的文章，如果覺得文章對你有用，歡迎給個“贊”，也歡迎分享，感謝大家！！

達達前端

CSDN認證博客專家 CSDN博客專家

達達前端技術社群：囊括前端Vue、JavaScript、資料結構與演算法、實戰演練、Node全堆疊一線技術，緊跟業界發展步伐，一個熱愛前端的達達程式員，個人博客：http://dadaqianduan.cn/

轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/78902.html

標籤：其他

上一篇：自動化辦公之excel教程（6）:資料的分析和處理

下一篇：MySQL利用ODBC轉義（{}）注入手法分析