柚子快報激活碼778899分享：MySQL 筆記

http://yzkb.51969.com/

一、儲存數(shù)據(jù)的演變

隨意的存到一個文件中、數(shù)據(jù)格式也是千差萬別的完全取決于我們自己

雖然不同的人可以根據(jù)自己喜歡的格式進行存儲，但是代碼給別讀取的話就會變得困難 軟件開發(fā)目錄規(guī)范

限制了存儲數(shù)據(jù)的具體位置

'''

bin

conf

core

dbd

lib

readme

'''

1、數(shù)據(jù)庫的本質(zhì)

'''

數(shù)據(jù)庫的本質(zhì)就是一款基于網(wǎng)絡(luò)通信的應(yīng)用程序

其實每個人都可以開發(fā)一款數(shù)據(jù)庫軟件，因為它僅僅就是一個基于網(wǎng)絡(luò)通信的應(yīng)用程序

也就意味著數(shù)據(jù)庫軟件其實有很多，如：

關(guān)系型數(shù)據(jù)庫：

MySQL、oracle、db2、access、SQL server

菲關(guān)系數(shù)據(jù)庫：

redis、MongoDB、memcache

'''

# 關(guān)系型：

1.數(shù)據(jù)之間彼此有關(guān)系或者拘束，如：

前女友表和男生表（一個男生對應(yīng)著多個前女友）

2.儲存數(shù)據(jù)的表現(xiàn)形式通常是以表格的方式儲存

name password hobby

vyj 132 eatbasketball

zcy 123 play game

每個字段還可以設(shè)置儲存的數(shù)據(jù)類型的限制

如名字只能儲存字符串?dāng)?shù)據(jù)類型

#非關(guān)系型：

儲存的數(shù)據(jù)都是以key，value的形式

MySQL

'''

任何基于網(wǎng)絡(luò)通信的應(yīng)用程序底層都是socket

服務(wù)器：

-基于socket通信

-收發(fā)消息

客戶端：

-基于socket通信

'''

mysql不單單支持MySQL自己的客戶端app還支持其他編程語言來充當(dāng)客戶端操作

如何解決語言溝通的障礙：

#1、 讓服務(wù)端兼容所有的語言（一個人精通多國語言）

#2、 采用統(tǒng)一的語言（SQL語句）

重要概念介紹

'''

庫 對應(yīng) 文件夾

表 對應(yīng) 文件

記錄 對應(yīng) 文件里面一行行的數(shù)據(jù)

表頭指的是表格的第一行的字段

字段 name，password，hobby

表單就是表頭下面的所有數(shù)據(jù)

'''

MySQL的安裝

'''

注意事項：不要隨意的更新版本，因為新版本可能會出現(xiàn)各種問題（可能你原來的mysql可以運行但是你更新版本之后可能就會崩潰）

'''

#MySQL版本有（5.6,5.7,8.0）

mysql官網(wǎng):https://www.mysql.com/

各個版本的sql語句一般來說都是一樣的

1、在mysql官網(wǎng)中下載mysql到本地

注意：在公司中是有兩個程序，一個是服務(wù)端，一個是客戶端，修改數(shù)據(jù)需要客戶端訪問服務(wù)端，服務(wù)端是專門的在一臺服務(wù)器上運行，所有人都是基于網(wǎng)絡(luò)連接服務(wù)器來實現(xiàn)修改數(shù)據(jù)庫

2、mysql服務(wù)端和客戶端

'''

服務(wù)端

mysqld.exe

客戶端

mysql.exe

'''

注意

'''

在前期配置MySQL的時候 cmd終端盡量以管理員的身份運行

win+r 輸入cmd 進入的是普通用戶終端，在一些命令是無法執(zhí)行的

輸入cmd右鍵 以管理員身份運行

'''

啟動

在cmd切換到mysqld所在的bin目錄下，然后輸入mysqld即可保留原來的cmd窗口重新打開一個

'''

常見軟件的默認端口號（需要背）

MSQL 3306

redis 6379

MongoDB 27017

django 8000

flask 5000

Tomcat 8080

mysql第一次以管理員身份進入是沒有密碼的，直接回車即可

客戶端連接服務(wù)端完整命令

mysql -h 127.0.0.1 -p 3306 -uroot -p

如果你連接的地址為本地地址127.0.0.1那么可以簡寫為

mysql -uroot -p

當(dāng)你輸入的指令不對，但是不想要服務(wù)器執(zhí)行返回報錯的話，那么可以在后面輸入\c取消，不需要使用分號

fadfaf \c

客戶端退出指令（不需要使用分號結(jié)束）

quit

exit

當(dāng)你的在連接服務(wù)端的時候，可以直接輸入mysql來直接mysql，但是這個時候的訪問為游客模式，很多功能都不能使用

'''

SQL語句初識

'''

1、MySQL中的sql語句是以分號作為結(jié)束的標(biāo)志

2、基本命令

show databases; 查看所有的數(shù)據(jù)庫名

'''

環(huán)境變量配置及系統(tǒng)服務(wù)制作

小知識點補充

'''

1、如何查看當(dāng)前具體進程

tasklist #查看所有的進程

tasklist |findstr mysqld #指定查看mysql服務(wù)端的進程

2、如何殺死具體進程（只有在管理員身份運行cmd才能成功）

tasklist /F /PID PID號

'''

環(huán)境變量配置

'''

每次啟動mysqld需要先切到對應(yīng)的文件路徑下才能操作太繁瑣

將mysqld所在的文件路徑添加到系統(tǒng)環(huán)境變量中

'''

但是完成上面的還是需要兩個cmd窗口來運行，所以可以

將mysqld服務(wù)端制作成系統(tǒng)服務(wù)（開機自啟）

'''

查看當(dāng)前計算機的運行進程數(shù)（在win+r中輸入）

services.msc

先將服務(wù)器初始化

mysqld --initialize-insecure

將mysql制作成服務(wù)端

mysqld --install #mysqld為服務(wù)端啟動文件

移除myql系統(tǒng)服務(wù)

mysqld --remove

'''

設(shè)置密碼

'''

mysqladmin -uroot -p 原密碼 password 新密碼

在終端中直接運行即可，不需要進入mysql

'''

破解密碼

如果忘記了密碼的話那么就需要重置密碼

'''

你可以將mysql獲取用戶名和密碼校驗的功能看成是一個裝飾器

裝飾在了客戶端請求訪問的功能上

我們?nèi)绻麑⒃撗b飾器移除，那么mysql服務(wù)端就不會校驗用戶名和密碼了

'''

# 1 、先關(guān)閉當(dāng)前mysql服務(wù)端

命令行的方式啟動（讓mysql跳過用戶名和密碼驗證功能）

mysqld --skip -grant -tables #這個是啟動服務(wù)器使用的指令，這樣之后就不會使用密碼了

# 2、直接以無密碼的方式連接

mysql -uroot -p 直接回車

# 3、修改當(dāng)前用戶的密碼

update mysql.uer set password=password(密碼) where user='root' and host='localhost'

#后面的where 表示的是用戶名為root 端口名為localhost

#password()是把里面的數(shù)據(jù)加密

'''

真正儲存用戶表的密碼字段，儲存的肯定是密文

只有用戶自己知道明文是什么，其他人都不知道，這樣比較安全

密碼比對也只能比對密文

'''

# 4、立刻將修改數(shù)據(jù)刷到硬盤這

flush privileges;

# 5、關(guān)閉當(dāng)前服務(wù)器，然后以正常校驗授權(quán)表的形式啟動

統(tǒng)一編碼

mysql默認的配置文件

'''

my-defaule.ini

ini結(jié)尾的一般都是配置文件

程序啟動一般先會加載配置文件中的配置

'''

[mysqld] #一旦服務(wù)端啟動的時候會立刻加載下面的配置

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysql]#一旦客戶端啟動立刻加載下面的配置

...

[client] #其他語言的客戶端

#需要新建一個配置文件

#修改配置文件后一定要重啟服務(wù)才能生效

#統(tǒng)一編碼的配置 無需掌握 直接拷貝

#可以將用戶的賬戶和密碼加入配置文件中

'''

[mysqld]

#設(shè)置3306端口

datadir=D:\\mysql-5.7.34-winx64\\data

port = 3306

# 設(shè)置mysql的安裝目錄

basedir=D:\\mysql-5.7.34-winx64

# 允許最大連接數(shù)

max_connections=200

# 服務(wù)端使用的字符集默認為8比特編碼的latin1字符集

character-set-server=utf8

default-storage-engine=INNODB

collation-server=utf8_general_ci

[mysql]

default-character-set=utf8

[client]

default-character-set=utf8

[mysql]

# 設(shè)置mysql客戶端默認字符集

default-character-set=utf8

MySQL操作

ps：大部分程序的業(yè)務(wù)邏輯其實都是增刪改查

針對庫的增刪改查（文件夾）

#增

create databases 數(shù)據(jù)庫名;

create databases lb charset='gbk';#后面的表示指定數(shù)據(jù)庫的編碼格式

#查

show databases;#表示查找所有的數(shù)據(jù)庫名

show create databse 數(shù)據(jù)庫名;#表示查找單個數(shù)據(jù)庫

#改

alter database 要修改的數(shù)據(jù)庫名 charset='編碼格式';#修改數(shù)據(jù)庫的編碼格式

#刪

drop database 要被刪除的數(shù)據(jù)庫名; #刪除指定的數(shù)據(jù)庫

針對表的增刪改查（文件）

'''

在操作（文件）的時候，需要指定所在的庫（文件夾）

'''

# 查看當(dāng)前所在的庫的名字

select database();

# 切換庫

use db1;

# 增

create table t1(id int,name char(4)) #id和name表示的表頭的數(shù)據(jù)，int和char表示的是數(shù)據(jù)的類型,char里面的數(shù)據(jù)為數(shù)據(jù)的長度

show create table 被查詢的表名;#表示在數(shù)據(jù)庫里面查詢指定表的名字

describe 被查詢的表名; #也是查詢表的信息，但是格式和上面的不一樣，支持簡寫可以寫成 desc 被查詢表名

#改

alter table 被修改表單 modify name char(16);#表示修改表單數(shù)據(jù)name為16位的字符類型

#刪

drop table 被刪除的表名; #刪除數(shù)據(jù)庫里面指定的表的名字

#小知識可以用指定路徑來創(chuàng)建表

#在t1里面直接創(chuàng)建l3表

create table t1.l3(id int) #但是記住要指定表頭

針對數(shù)據(jù)的增刪改查（一行行數(shù)據(jù)）

'''

一定先有庫，有表，最后才能操作記錄

'''

#增

insert into t1 values(1,'1')#表示在t1表中添加兩個 1 ， '1'的數(shù)據(jù)

insert into t1 values(1,'1'),(2,'3') #添加多個數(shù)據(jù)的書寫方式

#查

select * from t1; #t1表示被查詢表的名字，*表示所有的數(shù)據(jù)名，數(shù)據(jù)較多不建議使用、

select id from t1;#表示的是查詢表所以數(shù)據(jù)的id信息、

#改

update t1 set name='DSB' where id >1;#表示的是修改t1表里id大于1的name字段里面的數(shù)據(jù)為'DSB'

#刪

delete from t1 where id>1;#表示的是刪除表里面id大于1的所有數(shù)據(jù)、

delete from t1 where name ='1' #表示的是刪除t1表里面name屬性為1底的數(shù)據(jù)

# 將表所有的數(shù)據(jù)清空

delete from t1;# t1表示別清空數(shù)據(jù)的表的名字

#當(dāng)表里面的數(shù)據(jù)過多的時候可以使用\G分行展示

創(chuàng)建表的完整語法

#語法

create table 表名(

字段名1 類型(寬度) 約束條件,

字段名2 類型(寬度) 約束條件,

字段名3 類型(寬度) 約束條件

)

#注意

1 、在同一張表中字段名不能重復(fù)

2、寬度和約束條件是可選的（可寫可不寫）而字段名和字段類型是必須的約束條件寫的話 也支持寫多個如：

字段名1 類型(寬度) 約束條件1 約束條件2 ...,

create table t5(id); #這個語句在mysql中運行會直接報錯因為他沒有指定id的數(shù)據(jù)類型

3、最后一行不能有逗號

create table t6(

id int,

name char

);

'''補充'''

#寬度

一般情況下指的是對存儲數(shù)據(jù)的限制

create table t7(name char); #char的默認長度為1

insert into t7 values('libai');

針對不同的版本會出現(xiàn)不同的效果

5.6版本默認沒有開啟嚴格模式，規(guī)定只能存儲一個字符，你給了多少個字符，那么我會自動幫你截取

5.7版本級以上或者開啟了嚴格模式，那么規(guī)定只能存幾個就不能超，一旦超出范圍就會立刻報錯

'''嚴格模式到底該不該開呢'''

MySQL5.7版本默認都是開啟嚴格模式的

使用數(shù)據(jù)庫準(zhǔn)則：

能盡量少的讓數(shù)據(jù)庫干活就盡量少一點，不要給數(shù)據(jù)庫增加額外的壓力（所以一般開嚴格模式）

# 約束條件 null not null 設(shè)定數(shù)據(jù)不能為空

create table t8(id int ,name char not null);#這個數(shù)據(jù)里面的name屬性是不能為空的

'''寬度和約束條件到底是什么關(guān)系

寬度是用來限制數(shù)據(jù)的存儲

約束條件是在寬度的基礎(chǔ)之上增加的額外的約束

'''

嚴格模式

# 如何查看嚴格模式

show variables like "%mode"; #like 表示查詢的語句，而%mode表示模糊匹配匹配的數(shù)據(jù)，和Python的正則匹配一樣 里面的variables表示查詢的東西

#如你想要查詢對應(yīng)的表可以寫成 show tables like "%mysql";

模糊匹配/查詢

關(guān)鍵字 like

%:匹配任意多個字符

_:匹配任意單個字符

# 修改嚴格模式

set session #只在當(dāng)前窗口有效

set global #全局有效

set global sql_mode ='STRICT_TRANS_TABLES' #表示把數(shù)據(jù)庫的模式轉(zhuǎn)換為嚴格模式（注意5.7版本以上默認就為嚴格模式所以不需要轉(zhuǎn)換）

set global sql_mode ='NO_ENGINE_SUBSTITUTION'#表示包數(shù)據(jù)類型轉(zhuǎn)換為非嚴格模式

#修改完之后重新進入mysql就會生效

注意在嚴格模式下如果存入數(shù)據(jù)過大會直接報錯，而不是像非嚴格模式下一樣會切割你的數(shù)據(jù)

基本數(shù)據(jù)類型

整型

分類 tinyint(范圍：(-128,127),(0,255)) smallint(范圍：(-32768,32767),(0,65535)) mediumint(范圍：(-8388608,8388607),(0,16777215)) int或integer(范圍：(-2147483648,2147483647),(0,4294967295)) bigint(范圍：(-9233372036854775808,9233372036854775807),(0,18446744073709551615)) 作用 儲存年齡、等級、id、號碼等等

'''

以TINYINT

是否有符號

超出會如何

'''

create table t9(id tinyint);

insert into t9 values(-129,257);#當(dāng)數(shù)據(jù)超出限制只存最大的接收值

# 約束條件之unsigned 無符號

create table t10(id tinyint unsigned);

create table t11(id int)

#int 默認也是帶符號的

# 整型默認情況下都是帶有符號的

# 針對整型 括號內(nèi)的寬度到底是干嘛的

create table t12(id int(8) zerofill);#如果想看到數(shù)據(jù)里面的0補全可以在后面加zerofill

insert into t12 values(123456789);

'''

特例：只有整型括號里面的書架子不是表示限制位數(shù)而是顯示長度

id int(8)

如果數(shù)字沒有超出8位 那么默認用0填充

如果數(shù)字超出了8位 那么有幾位存幾位（但是還是要遵守數(shù)據(jù)的最大范圍）

'''

create table t13(id int(8) unsigned zerofill);

# 用0填充至8位

# 總結(jié)：

針對整型字段 括號內(nèi)無需指定寬度 因為它默認的寬度足夠顯示所有的數(shù)據(jù)了

浮點型

分類 float 、double 、decimal 作用 可以來存儲：身高、體重、薪資

'''

存儲限制

'''

float(255,30) #總共255位 小數(shù)部分最多30位

double(255,30) #總共255位 小數(shù)部分最多30位

decimal(65,30) #總共65位 小數(shù)部分最多30位

#這三者的區(qū)別就是它們的精度是不同的

# 精準(zhǔn)度測試：

create table t15(id float(255,30));

create table t16(id double(255,30));

create table t17(id decimal(65,30));

insert into t15 values(1.111111111111111111111111111111);

insert into t16 values(1.111111111111111111111111111111);

insert into t17 values(1.111111111111111111111111111111);

# 在上面插入數(shù)據(jù)里面的時候發(fā)現(xiàn)float只能精確到7位，而double能精確到15位，decimal能精確30位

# 要結(jié)合實際應(yīng)用場景 三者都能使用

字符類型

分類

'''

char

定長

char(4) 數(shù)據(jù)超過四個字符直接報錯，數(shù)據(jù)不夠四個字符空格補全

varchar

變長

varchar(4) 數(shù)據(jù)超過四個字符直接報錯，數(shù)據(jù)不夠四個有幾個存幾個

總結(jié)這兩個的優(yōu)缺點位：

char類型:

缺點：浪費空間

有點：存取都很簡單

直接按照固定的字符存儲數(shù)據(jù)即可

varchar類型：

優(yōu)點：節(jié)省空間

缺點：存取都比較麻煩

varchar類型的存和取和網(wǎng)絡(luò)通信中的自定義協(xié)議差不多，不過里面存儲的頭文件為數(shù)據(jù)的長度

建議用vachar，因為當(dāng)用戶數(shù)據(jù)過大的時候vachar會更省內(nèi)存

'''

create table t18(name char(4));

create table t19(name varchar(4));

insert into t18 values('a');

insert into t19 values('a');

# 字符類型可以通過char_length來統(tǒng)計字段的長度

select char_length(name) from t18;

select char_length(name) from t19;

'''

因為mysql會自動的把你存儲的數(shù)據(jù)中的空格自動剔除所以要取消mysql自動剔除字符類型里面的空格的操作

'''

# 再次修改sql_mode 讓mysql 不要做自動剔除操作

set global sql_mode=

'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';

date時間類型

分類 date:年月日 2021-5-13 datetime:年月日時分秒 2021-5-13 10:29:10 time:時分秒 10:29:10 year:年2021

create table student(

id int,

name varchar(16),

birth date,

student_time datetime

);

insert into student values('1','陳穎杰','2001-12-7',now());

enum和set枚舉和集合類型

分類

'''

枚舉(enum) 多選一

集合(set) 多選多

'''

具體使用

create table user(

id int,

name varchar(16),

gender enum('male','female','secrecy')

);

insert into user values(1,'陳穎杰','female');

insert into user values(2,'張程宇','女');#可以看到這個會報錯，因為在枚舉中只能再這里面的數(shù)據(jù)中選其中的一個

create table teacher(

id int,

name varchar(16),

gender enum('male','female','secrecy'),

hobby set('read','running','swimming','playing')

);

insert into teacher values(1,'陳穎杰','female','read'),

(2,'張程宇','female','running,playing')#在集合里面多個數(shù)據(jù)的話是通過在字符串里面的逗號來選擇多個數(shù)據(jù)

;

約束條件

default 默認值

# 補充知識點 插入數(shù)據(jù)的時候可以指定字段，這個東西不寫有默認字段數(shù)據(jù)

create table t1(

id int unsigned not null,#not null設(shè)置不能為空,unsigned設(shè)置無字符型

name varchar(16),

gender enum('male','female','secrecy') default 'female'

);

insert into t1(id,name) values(1,'陳穎杰');

insert into t1(gender,id,name) values('female',3,'劉昆')

unique唯一

# 單列唯一

create table t3(

id int unique,

name char(16)

);

insert into t3 values(1,'陳穎杰'),(1,'張程宇');#因為設(shè)置id字段為唯一的所以當(dāng)兩個id同時為1的時候這個會報錯

insert into t3 values(1,'陳穎杰'),(2,'張程宇');

#聯(lián)合唯一

'''

聯(lián)合唯一指的是：單個數(shù)據(jù)是可以重復(fù)的，但是兩個數(shù)據(jù)加起來不能完全一樣

'''

create table t4(

id int,

nickname varchar(16),

unique(id,nickname), #表示id和nickname不能完全一樣

name varchar(8) unique #表示名字不能重復(fù)

);

insert into t4 values(1,'小張子','張程宇'),(1,'張小子','張杰');

primary key主鍵

'''

單單從約束的效果來看primary key的效果等價于not null + unique，非空切唯一

'''

create table t5(id int primary key);

insert into t5 values(null);

insert into t5 values(1),(1);

#可以看到上面的結(jié)果全部錯了表示主鍵表面上的效果真的是這兩個效果的結(jié)合

create table t5(

id int primary key,

nameid int primary key);

'''

它除了有約束效果之外，它還是innodb存儲引擎組織數(shù)據(jù)的依據(jù)

innodb存儲引擎在創(chuàng)建表的時候必須要有primary key

因為它類似于書的目錄，能夠幫助提示查詢效率并且也是建表的依據(jù)

1、一個表中有且只有一個主鍵，如果你沒有設(shè)置主鍵，那么會從上往下搜索直到遇到一個非空且唯一的字段將它自動升級為主鍵

2、如果表中沒有主鍵也沒有其他任何的非空且唯一字段，那么innodb會采用自己內(nèi)部提供的一個隱藏字段作為主鍵，隱藏意味著你無法使用到它，就無法提示查詢速度

3、一張表中通常情況都是應(yīng)該只有一個主鍵，并且通常將id/uid/sid字段作為主鍵，單字段為主鍵

'''

#聯(lián)合主鍵（多個字段聯(lián)合起來作為表的主鍵，本質(zhì)還是一個主鍵）

create table t7(

id int,

port int,

name varchar(8),

primary key(id,port)

);

'''

創(chuàng)建字段的時候一定要加主鍵

'''

auto_increment自增

'''

當(dāng)編號特別多的時候，人為的去維護太麻煩，所以就出現(xiàn)了自增

'''

create table t8(

id int primary key auto_increment,

name varchar(8)

);

insert into t8(name) values('陳穎杰'),('張程宇'),('劉昆傻逼');

#注意auto_increment字段只能加在key鍵上，不能給普通字段添加

#刪除表里面的數(shù)據(jù)的時候，主鍵的自增不會停止，如果想重置數(shù)據(jù)就要輸入

truncate t8;#清除表數(shù)據(jù)并且重置主鍵

外鍵

'''

外鍵就是用來幫助我們建立表和表之間的關(guān)系的

foregin key

'''

一對多關(guān)系

'''

判斷表與表之間的關(guān)系，可以用換位思考的方式來判斷，如：

員工表和部門表：

先站在員工表中：

思考一個員工能否對應(yīng)多個部門（一條員工數(shù)據(jù)能否對應(yīng)多條部門數(shù)據(jù)）

不能

再站在部門表中：

思考一個部門是否可以對應(yīng)多個員工（一個數(shù)據(jù)能否對應(yīng)多個員工）

可以

最后判斷結(jié)果為：

一對多關(guān)系

'''

#在上面的一對多關(guān)系中，因為外鍵的位置在員工表中，所以先建立部門表

create table dep(

id int primary key auto_increment,

dep_name varchar(16),

dep_desc varchar(32)

);

create table emp(

id int primary key auto_increment,

name varchar(16),

gender enum('male','female') default 'male',#使用枚舉類型，默認值設(shè)置為男性

dep_id int,

foreign key(dep_id) references dep(id)

);

insert into dep(dep_name,dep_desc) values('測試部','測試軟件的'),('開發(fā)部','書寫代碼的'),('項目設(shè)計部','設(shè)計項目的'),('外交部','提項目要求的');

insert into emp(name,gender,deP_id) values('陳穎杰','male',2),('張程宇','male',1),('曾靜','male',1),('小王','male',3),('小麗','female',4),('小莉','female',3),('小李','male',2),('小美','female',2);

#修改emp里面的dep_id字段

'''

先刪除數(shù)據(jù)然后在重新寫入

'''

#刪除dep表里面的數(shù)據(jù)

'''

當(dāng)刪除員工表里面的數(shù)據(jù)的時候可以直接刪除，但是當(dāng)你想刪除庫的時候需要把需要刪除的部門所對因的員工全部刪除，才能在刪除你想要刪除的部門

這種刪除十分的麻煩所以就出現(xiàn)了同步跟新和同步刪除的設(shè)置：

在外鍵中寫 on update cascade on delete cascade 表示同步跟新和同步刪除出，

'''

create table dep(

id int primary key auto_increment,

dep_name varchar(16),

dep_desc varchar(32)

);

create table emp(

id int primary key auto_increment,

name varchar(16),

gender enum('male','female') default 'male',#使用枚舉類型，默認值設(shè)置為男性

dep_id int,

foreign key(dep_id) references dep(id)

on update cascade

on delete cascade

);

多對多關(guān)系

'''

圖書表和作者表：

站在書的角度中看一本書可以有多個作者，

站在作者的角度看一個作者可以有多本書，

所以這種關(guān)系就是多對多關(guān)系

注意：

多對多關(guān)系石創(chuàng)建第三張表，然后第三張表來儲存圖書表和作者表的關(guān)系圖

'''

create table author(

id int primary key auto_increment,

name varchar(16) unique,

age tinyint,

)

create table book(

id int primary key auto_increment,

name varchar(64),

price int

)

create table book_id_author_id(

id int primary key auto_increment,

book_id int,

author_id int,

foreign key(author_id) references author(id)

on update cascade

on delete cascade,

foreign key(book_id) references book(id)

on update cascade

on delete cascade

)

一對一關(guān)系

'''

學(xué)校的學(xué)生姓名和對應(yīng)的學(xué)號：

從學(xué)號來看一個學(xué)號對應(yīng)一個學(xué)生

從學(xué)生來看一個學(xué)生對應(yīng)一個學(xué)號

所以這個表和表的關(guān)系為一對一關(guān)系

一對一關(guān)系就是在一對多關(guān)系中外鍵的地方加一個unique唯一就可以了

'''

create table student_information(

id int primary key auto_increment,

student_id int unique not null,

phone int unique

)

create table student(

id int primary key auto_increment,

name varchar(16),

student_information_id int unique,#在后面外鍵unique唯一那么這兩個表就是一對一關(guān)系

foreign key(student_information_id) references student_infromation(id)

on update cascade

on delete cascade

)

針對表的字段名的修改

#1、修改表名

alter table 表名 rename 新表名;

#2、增加字段

alter table 表名 add 字段名 字段類型(寬度) 約束條件;#默認添加在表的尾部

alter table 表名 add 字段名 字段類型(寬度) 約束條件 first; #將字段添加在表的頭部

alter table 表名 add 字段名 字段類型(寬度) 約束條件 after 字段名;#表示新建表在指定字段的后面

#3、刪除字段

alter table 表名 drop 字段名;

#4、修改字段

alter table 表名 modify 字段名 字段類型(寬度) 約束條件;

alter table change 舊字段名 新字段名 字段類型(寬度) 約束條件;

復(fù)制表

'''

我們sql語句的查詢結(jié)果其實也是一張?zhí)摂M表

注意：

復(fù)制表是不能復(fù)制主鍵和外鍵的

'''

create table 舊表名 select * from 新表名;

where過濾

# 作用：對整體數(shù)據(jù)的一個篩選

# 1.查詢id大于等于3小于等于6的數(shù)據(jù)

select * from emp where id>=3 and id<=6; #第一種寫法

select * from emp where id between 3 and 6; #第二種寫法

# 2.查詢薪資是20000或者18000或者17000的數(shù)據(jù)’

select * from emp where salary=20000 or salary=18000 or salary=17000;

select * from emp where salary in (20000,18000,17000);#第二種寫法比上面的簡潔

# 3.查詢員工姓名表中含字母o的員工的姓名和薪資

'''

模糊查詢

like

% 匹配任意多個字符

_ 匹配任意單個字符

'''

select name,salary from emp where name like '%o%';

# 4.查詢員工姓名是由4個字符組成的 姓名和薪資

select name,salary from emp where char_length(name)=4;

select name,salary from emp where name like '____'

# 5.查詢id小于3或者id大于6的數(shù)據(jù)

select * from where id not between 3 and 6;

select * from where id<3 or id>6;

# 6.查詢薪資不在20000，18000，17000范圍的數(shù)據(jù)

select * from emp where salary not in (20000,18000,17000);

# 7.查詢崗位描述為空的員工姓名和崗位名 針對null不用等號用is

select name,post from emp where post_comment is Null;

group by分組

'''

分組實際應(yīng)用場景： 分組應(yīng)用場景非常的廣泛

男女比例

部門的平均薪資

國家之間的數(shù)據(jù)統(tǒng)計

'''

# 1.按照部門分組

select * from emp group by post;

'''

分組之后 最小可操作單位應(yīng)該是組，而不是組內(nèi)的單個數(shù)據(jù)

上述命令在你沒有設(shè)置嚴格模式的時候是可正常執(zhí)行的 返回的是分組之后 每個組的第一條數(shù)據(jù) 但是這不符合分組的規(guī)范L:分組之后不應(yīng)該考慮單個數(shù)據(jù)，而應(yīng)該以組為操作單位（分組之后直接獲取組內(nèi)單個數(shù)據(jù)）

如果設(shè)置了嚴格模式 那么上面的命令會直接報錯

'''

set global sql_mode = 'strict_trans_tables,only_full_group_by';#設(shè)置分組之后的數(shù)據(jù)顯示格式

#設(shè)置嚴格模式之后 分組 默認只能拿到分組的依據(jù)

select post from emp group by post; #因為把post分組所以只能拿到post的數(shù)據(jù)

#如果要獲取其他的數(shù)據(jù)需要用其他的方法

# 1.獲取每個部門的最高薪資

select post,max(salary) from emp group by post;

select post as '部門',max(salary) as '最高薪資' from emp group by post;

# as 字段是給其他的數(shù)據(jù)起別名，as是可以省略的但是一般不省略方便查看

# 2.獲取每個部門的最低薪資

select post,min(salary) from emp group by post;

# 3.獲取每個部門的平均薪資

select post,avg(salary) from emp group by post;

# 4.獲取每個部門的工資總和

select post,sum(salary) from emp group by post;

# 5.獲取每個部門的人數(shù)

select post,count(id) from emp group by post;

#6.查詢分組之后的部門名稱和每個部門下所有的員工姓名 group_concat：獲取到分組之后的普通字段的數(shù)據(jù)

select post,group_concat(name) from emp group by post;

select post,group_concat(name:'_DSB') from emp group by post;#表示在輸出數(shù)據(jù)的每個name后面家一個_DSB字符

select post,group_concat(name,':',salary) from emp group by post;#輸出來個數(shù)據(jù)中間用:隔開

# concat不分組的時候使用

select concat('NAME',name),concat('SAL':salary) from emp;

# 補充 as語法不單單可以給字段起別名，還可以給表起別名

select emp.id,emp.name from emp;

select id,name from emp;#這個和上面的一樣

select t1.id,t1.name from emp as t1;#和上面的結(jié)果也一樣，as把emp表變成了t1所以路徑的數(shù)據(jù)也為t1

#7.查詢每個人的年薪 不算年終獎12

select name,salary*12 from emp; #里面的數(shù)據(jù)可以直接做運算

'''

分組的注意的事項：

where和group by同時出現(xiàn)的時候group by在where的后面

where先對整體的數(shù)據(jù)進行過濾，然后group by在進行分組操作

聚合函數(shù)只能在分組之后使用如 max min avg

'''

having 分組之后篩選

'''

having的語法和where的語法是一致的

只不過having是在分組之后進行的過濾操作

having可以直接進行聚合函數(shù)

'''

# 1.統(tǒng)計各部門年齡在30歲以上的員工，平均工資保留平均工資大于10000的部門

select post,avg(salary) from emp where age>30 group by post having avg(salary) > 10000;

distinct去重

'''

一定要注意，必須是完全一樣的數(shù)據(jù)才可以去重,

數(shù)據(jù)的主鍵的數(shù)據(jù)都是不一樣的所以如果去重的時候把主鍵的數(shù)據(jù)帶入進去的話那么數(shù)據(jù)永遠不能去重成功，、

所以去重的時候一定要注意你要選擇去重的字段

'''

select distinct id,age from emp;#在這個去重的案例中是帶著id的因為id是主鍵，而主鍵是不重復(fù)的所以去重沒有清除一條數(shù)據(jù)

select distinct age from emp; #

order by排序

select * from emp order by salary;

elect * from emp order by salary asc;#沒有省略asc

#order by默認是升序 ，asc 該asc可以省略不寫，也可以設(shè)置降序?qū)懛╠esc

select * from emp order by salary desc;

# 先按照age降序排， 如果碰到age相同 再按照salary升序排

select * from emp order by age desc,salary asc;

#統(tǒng)計各部門年齡在10歲以上的員工的平均工資并且保留薪資大于1000的部門，然后對平均工資降序排序

select post,avg(salary) where age>10 group by post having avg(salary) >1000 order by avg(salary) desc;

limit限制展示條數(shù)

select * from emp;

'''

針對數(shù)據(jù)過多的情況 我們通常都是做分頁處理

'''

select * from emp limit 3;#表示一次性拿三條數(shù)據(jù)

select * from emp limit 0,5;#表示從第一條拿到第五條、

select * from emp limit 5,5;#表示從第六條開始往后取5條

正則

select * from emp where name regexp '^j.*(n|y)$'; #regexp表示使用正則搜索

#正則后面的算法類型的是和Python的正則格式是類似的，只是調(diào)用的方式有點不同

連表操作

select * from emp,dep;

#這種查詢兩個表的話，發(fā)現(xiàn)兩種表示遍歷循環(huán)的來拼接兩張表

select * from emp,dep where emp.dep_id = dep.id;

#這種查詢發(fā)現(xiàn)他們是一對一按要求的來拼接兩張表

'''

MySQL中提供了連表操作的4個方法

inner join 內(nèi)聯(lián)（表示的是多個表之間的交集數(shù)據(jù)）

left join 左聯(lián)（表示的是第一張表有第二張表沒有的數(shù)據(jù)）

right join 右聯(lián)（表示的是第二張表有第一張表沒有的數(shù)據(jù)）

union 全聯(lián) （表示的是多張表共有的所有數(shù)據(jù)）

'''

select * from emp inner join dep on emp.dep_id = dep.id; #表示打印兩個表共有的部分

select * from emp left join dep on emp.dep_id =dep.id; #表示打印兩個表中emp表中的數(shù)據(jù)和所對應(yīng)的數(shù)據(jù)

select * from emp right join dep on emp.dep_id = dep.id; #表示打印兩個表中的dep表中和所對應(yīng)的數(shù)據(jù)

#全連接的寫法不一樣其實就是把左連接和有連接合并只不過中間使用union來把它們聯(lián)系起來

select * from emp left join dep on emp.dep_id = dep.id

union

select * from emp right join dep on emp.dep_id = dep.id;

子查詢

'''

子查詢就是我們平時解決問題的思路：把一個查詢語句的結(jié)果當(dāng)做另外一個查詢語句的條件來使用（

'''

# 查詢部門是技術(shù)或者人力資源的員工信息

select * from emp where emp.dep_id in

(select id from dep where name in ('人力資源','技術(shù)'));#這個是篩選出對應(yīng)額部門

視圖（了解）

什么是視圖

'''

視圖就是通過查詢而得到的一張?zhí)摂M表 然后保存起來 下次可以直接使用

'''

為什么要用視圖

'''

如果要頻繁的操作一張?zhí)摂M表（連表查詢拼表組成的等等） 你就可以制作一張?zhí)摂M表 后續(xù)可以直接操作

'''

# 視圖語法

create view 表明 as 虛擬表的查詢sql語句;

# 比如說講部門表和員工表所有的信息創(chuàng)建一個虛擬表

create view emp_dep_all as

select emp.id,emp.name as emp_name,sex,age,dep.name as dep_name from emp right join dep on emp.dep_id = dep.id

union

select emp.id,emp.name as emp_name,sex,age,dep.name as dep_name from emp left join dep on emp.dep_id = dep.id;

#在上面注意事情就是創(chuàng)建視圖的時候要避免數(shù)據(jù)的字段名相同，如果數(shù)據(jù)的字段名相同那么數(shù)據(jù)就會出現(xiàn)問題

'''

視圖其實就是一張表只不過在創(chuàng)建表的時候是沒有約束條件，只有數(shù)據(jù)

視圖是的存儲是只有表結(jié)構(gòu)文件，沒有表數(shù)據(jù)文件，視圖表的數(shù)據(jù)還是來自于原來中的表的數(shù)據(jù)

視圖一般只用于查詢里面的數(shù)據(jù)，不用來修改，因為修改了之后可能會影響原來的表數(shù)據(jù)，所以一般來說是不能修改視圖里面的數(shù)據(jù)

，因為視圖里面的數(shù)據(jù)是來自之前的數(shù)據(jù)所以可以通過修改原來的表的數(shù)據(jù)來修改視圖里面的數(shù)據(jù)

'''

觸發(fā)器（了解）

在滿足在對表數(shù)據(jù)增刪改的情況下 自動觸發(fā)功能

觸發(fā)器可以幫助我們實現(xiàn)監(jiān)控、日志…

觸發(fā)器可以在六中情況下自動觸發(fā)。增前增后 改前改后

觸發(fā)器的語法結(jié)構(gòu)

create trigger 觸發(fā)器的名字 before/after insert/update/delete on 針對的表的名字

for each row

begin

sql語句

end

# 具體使用 針對觸發(fā)器的名字 我們通常需要做到見名如意

# 針對增加數(shù)據(jù)之后觸發(fā)的效果

#注意在調(diào)用的之前和之后要修改結(jié)束符,不然在創(chuàng)建語句的時候就會出現(xiàn)報錯

delimiter &&

create trigger experiment_inshow_after after insert on experiment

for each row

begin

insert into experiment_intime(insert_time) values(now());

end &&

delimiter ;

# 刪除指定的觸發(fā)器

drop trigger 觸發(fā)器名字;

#mysql個人制作添加數(shù)據(jù)日志

create table experiment(

id int primary key auto_increment,

name varchar(20) not null,

gender enum('male','female','secrecy') not null,

modify_time datetime not null default now()

);

create table experiment_intime(

id int primary key auto_increment,

insert_time datetime not null,

);

delimiter &&

create trigger experiment_inshow_after after insert on experiment

for each row

begin

insert into experiment_intime(insert_time) values(now());

end &&

delimiter ;

insert into expriment(name,gender) values

('陳穎杰','female'),('張程宇','female');

事物(需要掌握)

什么是事物 開啟一個事務(wù)可以包含多條sql語句，這些sql語句要么同時成功，要么一條都別想成功 這個稱之為事物的原子性 事物的作用 保證了對數(shù)據(jù)操作的安全性 事物的四大特性

原子性 一個事物是一個不可分割的單位，事物中包含的諸多操作要么同時成功要么同時失敗 一致性 事物必須是使數(shù)據(jù)庫從一個一致性的狀態(tài)變到另外一個一致性的狀態(tài)一致性和原子性密切相關(guān) 隔離性 一個事物的執(zhí)行不能被其他事物干擾（即一個事物內(nèi)部的操作及使用到的數(shù)據(jù)對并發(fā)的其他事物是隔離的，并發(fā)執(zhí)行的事物之間也是互不干擾的） 持久性 一個事物一旦提交成功執(zhí)行成功那么它對數(shù)據(jù)庫中的修改應(yīng)該是永久的接下來的其他操作或者故障不應(yīng)該對其有任何影響 如何使用事物 # 事物相關(guān)的關(guān)鍵字

# 1 開啟事物

start transaction;

# 2 回滾（回到事物執(zhí)行之前的狀態(tài)）

rollback;

# 3 確認（確認之后就無法回滾了）

commit;

# 事物一般作用于修改表的操作，如果只是想查詢表的數(shù)據(jù)就不需要使用到事物

存儲過程

儲存過程就類似于python中的自定義函數(shù)

它的內(nèi)部包含了一系列可以進行執(zhí)行的sql語句，存儲過程存放于MySQL服務(wù)端中，你可以直接通過調(diào)用存儲過程觸發(fā)內(nèi)部sql語句的執(zhí)行

基本使用

create procedure 儲存過程的名字(參數(shù)1,參數(shù)2) #括號里面可以傳遞參數(shù)

begin

sql 代碼

end

#調(diào)用

call 存儲過程的名字();

#案例

delimiter &&

create procedure showta()

begin

show tables;

end&&

delimiter ;

# 定義變量

set @ret = 10;

# 查看變量對應(yīng)的值

select @ret;

#刪除存儲過程

drop procedure 儲存過程的名字;

函數(shù)

和存儲過程是由區(qū)別的，存儲過程是自定義函數(shù)，還這里面的函數(shù)是mysql里面自帶的內(nèi)置函數(shù)

now() # 表示當(dāng)前的時間

max() # 計算里面的最大數(shù)據(jù)

min() # 計算里面最小的數(shù)據(jù)

avg() # 計算里面的平均數(shù)

#其他的看mysql官方

索引

數(shù)據(jù)都是存在與硬盤上的，查詢數(shù)據(jù)不可避免的需要進行io操作

索引就是一種數(shù)據(jù)結(jié)構(gòu)，類似于書的目錄。意味著以后在查詢數(shù)據(jù)的應(yīng)該先找目錄再找數(shù)據(jù)，而不是一頁一頁的翻書，從而提升查詢速度降低io操作索引在MySQL中也叫“鍵”，是存儲引擎用于快速查找記錄的一種數(shù)據(jù)結(jié)構(gòu)

primary keyunique keyindex key

注意foreign key不是用來加速查詢用的，不在我們的而研究范圍之內(nèi)上面的三種key，前面兩種除了可以增加查詢速度之外各自還具有約束條件，而是最會一種index key沒有任何的約束條件，只是用來幫助你快速查詢數(shù)據(jù)

本質(zhì)：

通過不斷的縮小想要的數(shù)據(jù)范圍篩選出最終的結(jié)果，同時將隨機事件（一頁一頁的翻）變成順序事件（先找目錄，再找數(shù)據(jù)）也就是說有了索引機制，我們可以總是用一種固定方式查找數(shù)據(jù)

一張表中可以有多個索引（相當(dāng)于多個目錄）

缺點

當(dāng)表中有大量數(shù)據(jù)存在的前提下，創(chuàng)建索引速度會非常的慢在索引創(chuàng)建完畢之后，對表的查詢的性能會大幅度提升，但是對寫的性能會大幅度的降低 總結(jié) 索引不要隨便的創(chuàng)建

聚集索引（primary key）

'''

聚集索引指的就是主鍵

innodb 只有兩個文件 直接將主鍵存放在idb表中

myisam 三個文件 單獨將索引存在一個文件中

'''

輔助索引（unique,index）

查詢數(shù)據(jù)的時候不能一直使用到主鍵，也有可能會用到name，password等其他字段那么這個時候你是沒有辦法利用聚集索引。這個時候你就可以根據(jù)情況給其他字段設(shè)置輔助索引（也是一個b+數(shù)）

'''

葉子的節(jié)點存放的是數(shù)據(jù)對應(yīng)的主鍵值

先按照輔助索引拿到數(shù)據(jù)的主鍵值

之后還是需要去主鍵的聚集索引里面查詢數(shù)據(jù)

'''

覆蓋索引和非覆蓋索引

在輔助索引的葉子節(jié)點就已經(jīng)拿到了想要的數(shù)據(jù)

# 給name設(shè)置輔助索引

select name from user where name = '劉昆'

#在name表中查找name里面的數(shù)據(jù)那么就不需要通過聚集索引來查找，可以直接的在name表中查找，這種情況就是覆蓋索引

select * from user where name = '劉昆'

#這種不能直接的通過name表中來進行查詢數(shù)據(jù)的必須裝換為索引來進行二次查詢的這種就是非覆蓋索引

MySQL主要存儲引擎

Innodb

是MySQL5.5及版本之后默認的存儲引擎存儲數(shù)據(jù)更加的安全 myisam

是MySQL5.5版本之的默認存儲引擎速度要比Innodb更快，但是安全性沒Innodb高 memory

內(nèi)存引擎（數(shù)據(jù)全部存儲在內(nèi)存中）斷電數(shù)據(jù)流失 BlackHole

無論存什么，都會立刻消失（黑洞）

#查看所有的存儲引擎語法：

show engines;

#不同的儲存引擎在儲存表的時候 異同點

create table t1(id int) engine=innodb; #這個創(chuàng)建的表具有表結(jié)構(gòu)文件和表數(shù)據(jù)文件

create table t2(id int) engine=myisam; #這個創(chuàng)建的表具有表結(jié)構(gòu)文件和表數(shù)據(jù)文件和表索引文件（索引文件是方便數(shù)據(jù)查找的，加快數(shù)據(jù)查找速度）

create table t3(id int) engine=memory; #只有表結(jié)構(gòu)

create table t4(id int) engine=blackhole;#只有表結(jié)構(gòu)

#存數(shù)據(jù)

insert into t1 values(1);

insert into t2 values(1);

insert into t3 values(1);

insert into t4 values(1);

#查看各個表里面的數(shù)據(jù)情況

select * from t1;

select * from t2;

select * from t3;

select * from t4;

#在存數(shù)據(jù)之后直接查詢可以看到t1、t2和t3都有數(shù)據(jù)，但是重啟服務(wù)器之后只有t1和t2有數(shù)據(jù),這個也可以證明t3表數(shù)據(jù)是在內(nèi)存里面的存儲的

柚子快報激活碼778899分享：MySQL 筆記

http://yzkb.51969.com/

精彩鏈接