7.1資料庫
7.1.1資料庫定義
資料庫是存放資料的電子倉庫，以某種方式存盤百萬條，上億條資料，供多個用戶訪問共享，
7.2資料庫型別
7.2.1主流的關系型資料庫
欄位和值，屬性和屬性值存在一定關系，兩張表之間通過欄位建立連接，
欄位:欄位每一行稱為一個“記錄”，每一個記錄包含這行中的所有資訊，欄位集合組成記錄，
★關系型資料庫型別
1、商用資料庫：例如Oracle(甲骨文公司)(pl-Oracle語言)，只適用于大型企業公司、SQL server（微軟公司）、DB2（IBM）等
2、開源資料庫：例如：mysql(需要重點掌握mysql增刪改查)、postgresql等
★關系型資料庫特點：
1.安全
2.保持資料的一致性
3.實作對表與表進行復雜的資料查詢
7.2.2非關系型資料庫
非關系型資料庫定義：非關系型資料庫是一個新的理念，以key-value形式呈現
非關系型資料庫有哪些？
1、hbase(列模型) 2、Redis資料庫 3、MongoDB資料庫
★非關系型資料庫型別
1、hbase（列模型） 2、redis（鍵值對模型）
3、mongodb（檔案類模型）
not only sql 翻譯：不僅是sql
可以使用json的方式來進行資料存盤，沒有表結構
★非關系型資料庫特點：
1、沒有表結構
2、可通過階層形式進行存盤
3、一般還以物件形式的形式進行存盤，比如鍵值對，根據這個鍵獲取對應值，
7.2.3 MySQL資料庫原理：
用戶平臺輸入注冊資訊，然后進行提交，注冊成功提交后，系統會通過sql插入陳述句往服務器去插入這些資訊，京東服務器就會生成這些表，下次進行登錄的時候就會讀取這些表，如果表中有你的注冊資訊，就能輸入資訊直接登錄，
約束定義：約束用于對表中欄位進行限制，保證表中資料的正確性和唯一性
★常用的sql陳述句
1、查看當前服務狀態：service mysqld status
2、開啟服務：service mysqld start 停止服務：net stop mysql
3、修改MySQL資料庫密碼命令：
mysqladmin -uroot password“123456”
4、創建一個資料庫 create database +庫名
5、顯示有哪些資料庫 show databases
6、 進入某個資料庫 use +庫名
7、洗掉某個資料庫 drop database +庫名
8、創建一個表 create table +表名(欄位1名稱，資料型別,約束,欄位2名稱,資料型別,約束,欄位3名稱,資料型別，約束,）
9、退出資料庫操作：exit
7.2.4 mysql常用的資料型別
1、int 存盤整數 ==》超過2147483647用bigint
2、float 默認保存6位精度（包括小數位和整數位）Float(20,2)指小數點后面2位
3、char 存盤字符 char(10)可能占用了10個位元組
4、varchar(10) 存盤字符 varchar(10)只占了6個位元組，把其余的4個位元組留給了下一個字符
5、date 建表的時候在日期的后面接 date，并且加引號
注意： int(3) int(M) M指最大顯示寬度
char(20) char(M) M指最大能存盤20個字符
7.3 約束
7.3.1★約束定義
約束定義：用于對表中欄位進行限制，保證表中資料的正確性和唯一性，
7.3.2★約束型別
primary key 主鍵約束
說明：非空，唯一，用于唯一標識對應的記錄，類似身份證，
foreign key 外鍵約束
說明：用于表與表建立關系模型，使表與表緊密的結合起來，
Default 默認值約束
說明：默認給欄位指定默認值
auto_increment 自增長約束
說明：作用在整數型別，欄位默認從1開始自增
7.4.修改表結構
1.查看表結構 desc +表名
顯示欄位名稱，資料型別，約束，備注
field---欄位名稱 extra----額外的備注
type ----資料型別 date格式：“2022-07-10"
null---是否可以為空
2、修改表名 alter table +表名 rename + 新表名
3.修改表欄位
alter table +表名 change +原欄位名 +新欄位名 資料型別，約束
4.添加表欄位，并放到第一個欄位前
alter table +表名 add +欄位名 資料型別 約束 first
5.添加表欄位，并放到某個欄位后
alter table +表名 add +欄位名 資料型別 約束 after +欄位名
6、同時添加兩個欄位，默認添加到欄位最后
alter table +表名 add(欄位1 資料型別,欄位2 資料型別)
7. 洗掉表欄位 alter table +表名 drop + 欄位
8.洗掉表兩個欄位 alter table +表名 drop 欄位1,drop 欄位2
9.修改主鍵id為自增長
alter table +表名 change +欄位名 資料型別 auto_increment
10.洗掉表 drop table +表名
Mysql增刪改陳述句
1.表中插入資料
insert into +表名 values(欄位1value,欄位2value,欄位3value...)
查詢表中插入資料：select * from+表名
2、一次性表中插入多條資料:
insert into +表名 values(欄位1value,欄位2value,欄位 3value...),(欄位1value,欄位2value,欄位3value...)
3.對表中指定欄位插入資料
insert into +表名(欄位1，欄位2) values(欄位1值，欄位2值)
4. 洗掉表中指定資料 delete from +表名 where 條件
5. 洗掉表（清除表中資料，表結構在） truncate +表名
6. 洗掉表（清除資料表結構不在） drop table +表名
注：TRUNCATE,DELETE,DROP放在一起比較：
TRUNCATE TABLE：快速洗掉表資料，不洗掉表結構，
DELETE TABLE: 按條件洗掉表資料
DROP TABLE：洗掉表結構和表資料
7.更新表中指定欄位資料
update +表名 set 欄位名=值 where 條件
★測驗欄位需要測驗哪些內容(資料庫中給你一個欄位怎么測驗)？
資料型別，約束型別、存盤的值取邊界值
★備份表，備份資料，備份資料庫,還原資料庫
1.備份表(備份表的結構)，創建一個表與某個表結構相同，但資料沒有備份 create table +表1 like +表2
2.備份資料，把一個表的資料插入到另一個表
insert into +表名 select * from +表名
注意①插入的表必須要存在②插入的表是新表,沒有資料，
3、把一個表的某些欄位插入到一個新表中
insert into +表1(欄位1，欄位2) select 欄位1，欄位2 from 表2
注意1. 插入的表必須存在 2. 插入的表是新表,沒有資料，
4、備份資料庫
mysqldump -uroot -p 新資料庫名>原腳本名
5、還原資料庫
mysql -uroot -p +新資料庫名<(已備份)腳本名
7.5、單表查詢
1.查詢表中所有資料： select * from +表名
“*”代表所有
2. 查詢某個欄位的資料 select 欄位 from +表名
3. 查詢多個欄位的資料
select 欄位1，欄位2 from +表名
4.查詢滿足某個條件的所有資料
select * from +表名 where 欄位=值
注：where 后面接滿足的條件
5.查詢不滿足某個條件的所有資料
select * from +表名 where 欄位！=value
“！=” 代表不等于 ，也可以用符號 ”<>“代表不等于
6.查詢同時滿足多個條件資料
select * from +表名 where 條件1 and 條件2
and 關鍵字左右的兩個條件必須同時滿足
7.查詢滿足至少1個條件的資料
select * from +表名 where 條件1 or 條件2
or 關鍵字左右的兩個條件至少滿足1個，否則回傳空
8.查詢一個條件范圍內的資料
select * from +表名 where 欄位 between m and n
between...and ... 指定一個范圍
9.查詢欄位滿足在指定的集合中的資料
select * from +表名 where 欄位 in(值1，值2，值3)
10.查詢欄位不滿足在指定集合中的資料
select * from +表名 where 欄位 not in (值1,值2,值3)
11.查詢欄位值為空的資料
select * from +表名 where 欄位 is null
注意：欄位是空不能寫成 欄位=null
12.查詢欄位不為空的資料
select * from +表名 where 欄位 is not null
13.查詢某個欄位模糊匹配成功的資料
select * from +表名 where 欄位 like “%值%”
%用于匹配欄位開頭和結尾
14.查詢限定的數量的資料
select * from +表名 where 欄位 limit m,n
m 指下標，n指限定的數量，從下標為m下一個的開始的n條資料
15.查詢的資料根據某個欄位從小到大排序
select * from +表名 order by 欄位 asc
order by ...asc 從小到大排序
16.查詢的資料根據某個欄位從大到小排序
select * from +表名 order by 欄位 desc
order by ... desc 從大到小排序
- 查詢的資料根據某個欄位進行分組
select * from +表名 group by 欄位
group by ... 根據條件分組
18.查詢的資料根據某個欄位進行分組再條件過濾
select * from +表名 group by 欄位 having 條件
having跟在group by 后面，作用相當于where
★mysql聚合函式
19. 統計查詢資料的數量 select count(*) from +表名
20. 查詢某個欄位求和 select sum(欄位) from +表名
21. 查詢某個欄位進行平均值
select avg(欄位) from +表名
22. 查詢某個欄位最大值 select max(欄位) from +表名
23. 查詢某個欄位最小值 select min(欄位) from +表名
24 對某個欄位進行 (distinct)去重
select distinct(欄位) from +表名
25、&& 和，且
★單表查詢總結
1、where 不能放在GROUP BY 后面
where 子句的作用是在對查詢結果進行分組前，將不符合where條件的行去掉，即在分組之前過濾資料，條件中不能包含聚組函式比如SUM(),AVG()等，使用where條件顯示特定的行，
2、having 是跟GROUP BY 連在一起用的，放在GROUP BY 后面，此時的作用相當于WHERE，
3、having 子句的作用是篩選滿足條件的組，即在分組之后過濾資料，條件中經常包含聚組函式，使用having 條件顯示特定的組，也可以使用多個分組標準進行分組，
例1：求出每個班級中數學成績大于80分的人數？
2.求出每個班級中性別為1的數學總成績
3.求出每個班級的數學總成績大于200的班級和成績資訊
★資料庫用戶權限操作
如何給普通的資料庫用戶賦予相應的權限（如增刪改查）：
1.進入mysql資料庫：use mysql;
2.查詢mysql資料庫服務器已經創建了哪些用戶：
select host,user from user;
3.創建用戶但是未授權（方法一）
Insert into user(host,user,password)values('localhost','wang',password('123456'));
創建用戶后需要重繪下:flush privileges;
4、創建用戶后進行授權（方法二）
% 代表遠程權限 local代表本地權限
grant select,update,delete,drop on *.* to 'wang'@'localhost' identified by '123456'
5、創建用戶同時授權
grant all privileges on *.* to 'dcs'@'%' identified by '123456';
授予一個普通用戶dcs及密碼為123456，允許其可以通過所有客戶機訪問本資料庫下所有的庫及所有的表,假如為localhost只能在本地進行訪問其下面所有的表，權限為所有權限，
6、flush privileges ---重繪
show grants for 'wang'@'%'; //查看資料庫的指定授權用戶的權限
revoke all on *.* from 'dcs'@'%' ;//取消所有權限
洗掉用戶:delete from user where user='zhongguo'and host='localhost';
修改root用戶的密碼：update user set password=password('123456') where user='root';
◆安裝報錯注意：
1、輸入service 關閉防護墻 service iptables stop
2、grant all privileges on *.* to 'root'@'%' identified by '123456';
◆取別名
或
或
7.6 多表關聯查詢
7.6.1. 內連接查詢
(查詢兩個表都符合條件的資料) 兩個表中有欄位相同的值進行拼接 關鍵字 inner join
select 欄位串列 from 表1 inner join 表2 on 表1.欄位=表2.欄位
7.6.2. 左連接查詢
左連接：(左邊表中資料顯示全部) 以左表為主顯示全部，右邊表符合條件的顯示，右表在左表沒有的資料自動以null填充， 關鍵字:left join
基本格式：select 欄位串列 from 表1 left join 表2 on 表1.欄位=表2.欄位
7.6.3. 右連接查詢
右連接查詢：以右表為主顯示全部，左表顯示符合條件的資料，左表在右表沒有的資料以null填充，關鍵字:right join
基本格式:select 欄位串列 from 表1 right join 表2 on 表1.欄位=表2.欄位
7.6.4. 基本連接
select * from aa,cc where aa.id=cc.s_id; //基本連接查詢
7.6.5. 硬連接
select * from aa union select * from cc; //union連接（前提條件，多個關系表的表欄位數目必須相同）
7.6.6. 交叉連接
7.7、索引
7.7.1.索引定義
一個索引是存盤在表中的資料結構，索引在表的列名上創建，索引中包含了一個列的值，這些值保存在一個資料結構中，資料庫使用索引的方式與使用書的目錄很相似：通過搜索索引找到特定的值，然后隨指標到達包含該值的行，
重點：索引是一種資料結構
7.7.2.索引作用
可以利用索引快速訪問資料庫中的特定資訊.
7.7.3.索引分類
1、普通索引2、唯一索引3、主鍵索引
7.7.4.索引陳述句
◆查索引:show * from dcs
◆創建索引：單個欄位創建索引：create index tt on dcs(sid); 給sid創建索引
多個欄位創建索引：create unique(唯一）index tt on dcs(sid，name,id);
◆洗掉索引：
alter table table_name drop index index_name ;
◆普通索引：是最基本的索引，它沒有任何限制
◆唯一索引：它與前面的普通索引類似，不同的就是：索引列的值必須唯一,但允許有空值
◆主鍵索引：它是一種特殊的唯一索引，不允許有空值，一般是在建表的時候同時創建主鍵索引
注意：主鍵索引和主鍵約束其實是綁在一起的，如果有了主鍵索引，那么必會產生主鍵約束，或者說主鍵約束是主鍵索引的一個特征，添加一個主鍵就是添加了主鍵索引，添加主鍵索引就添加了主鍵，
7.8、資料庫視圖
7.8.1視圖定義
視圖是一個虛擬的表，它不在資料庫中以存盤資料的
形式保存，是在使用視圖的時候動態生成，
7.8.2視圖特點
1、視圖是由基本表產生的虛表
2、視圖的更新和洗掉會影響基礎表
3、基礎表的更新和洗掉也會影響到視圖
7.8.3.視圖的作用
對視圖的操作與對基本表的操作都是一樣的（包括alter,create,insert into ,update ,delete ,select）,且二者任意一方的欄位值被修改，都會實時影響到對方（如修改view的欄位值，會同步修改table相應的欄位值）；但是視圖的創建是基于基本表的，它的作用可以對基本表的敏感資訊進行保護；在實際作業中，出于安全考慮，將用戶常用的資訊創建成視圖給用戶呼叫，避免了直接操作基本表！
7.8.4.視圖命令
1、顯示目前有哪些視圖:show tables;
創建的視圖都在表的最下面，資料庫視圖 VIEW
2、將查詢結果集創建為視圖yy
create view yy as (select id,name from dcs)
3、查看視圖show CREATE VIEW yy（視圖名）
查看視圖：Show tables select * from 視圖名
洗掉視圖：drop view 視圖名
7.8.5資料庫中的DDL和DML
◆DDL //資料庫定義語言 （對表結構和表欄位進行操作）
如：alter、create、table、drop
◆DML //資料庫操作語言 （對表資料進行操作）
如：insert into、delete、select、update，truncate
7.8.6資料的約束型別
◆對表結構進行約束：主鍵約束：primary key 非空約束：not null 默認值約束：default自增長約束：auto_increment
◆對表與表之間進行約束： 外鍵約束：foreign key
7.8.7外鍵約束
◆外鍵用來干什么
為了一張表記錄的資料不要太過冗余，這和軟體project的模塊化思想類似，僅僅只是在資料庫中是對表關系進行解耦，盡量讓表記錄的資料單一化，
★外鍵的主要作用
保持資料的一致性、完整性
注意：
1、主表一般存放的是基礎資料，如果主表不存在的資料，在任何一張子表是無法插入跟該資料相關的任何資料；
2、如果要洗掉主表的資料，需要洗掉與主表相關的任何子表資料
實體講解：
1、現在我們創建兩個表dcs1、dcs2
2、所以在設計的時候，就給表1加入一個外鍵，這個外鍵就是表2中的sid欄位，那
么這樣表1就是主表，表2就是子表
3、創建dcs1表
create table dcs1(id int(4) PRIMARY key,name varchar(10))
ENGINE=INNODB ;
4、創建dcs2表（創表時創建外鍵）
dcs2表，里面包含sid，name，然后建立外鍵
create table dcs2(sid int(4)primary key,sname varchar(10),CONSTRAINT fk_sid
FOREIGN key(sid)REFERENCES dcs1(id))ENGINE=INNODB;
給子表創建一個外鍵，外鍵名字叫xx 并且子表的外鍵欄位為sid，對應主表的主鍵名字為id
FK_***：是外鍵名 --對應dcs2表中的：fk_sid
foreign key(子表的外鍵欄位) --對應dcs2表中的：sid
references 父表的表名（父表的主鍵的欄位名） ---父表名對應dcs1，父表的主鍵的欄位名對應dcs1中的id欄位
外鍵之增加資料：
如果表1和表2之間有外鍵約束 且表1是主表 表2為子表 如果主表不存在的資料 在任何一張子表是無法插入跟該資料相關的任何資料，
5、資料庫有的版本有時默認存盤引擎為MyISAM，這時我們外鍵就會不起作用，所有我們在建表時要將存盤引擎改為Innodb;具體操作見上創表的sql陳述句中有體現
洗掉外鍵: alter table 子表名drop foreign key 外鍵名
洗掉主鍵：洗掉之前先洗掉自增長 alter table dcs1 change id id int(10);
alter table 表名 drop primary key;
注意:只有在定義外鍵時,用constraint 外鍵名 foreign key .... 方便進行外鍵的洗掉
6、為什么說外鍵能保持資料的一致性、完整性
外鍵之資料洗掉場景：
7.9、存盤程序
7.9.1.存盤程序定義
存盤程序是完成特定功能的sql陳述句集合，通過編譯后存盤在資料庫中，通過指定的存盤程序名稱呼叫執行它，
存盤程序=sql陳述句集合+控制陳述句
Mysql5.0版本后支持存盤程序
7.9.2使用存盤程序的優點
◆存盤程序創建可以多次呼叫，不需要重新撰寫存盤程序陳述句，
◆假如要往表中插入大量的測驗資料，用insert into語法執行速度太慢，效率較低，那么這個時候可以通過撰寫存盤程序，把需要插入的資料sql集合放入存盤程序的函式體當中，創建存盤程序，下次即可呼叫，
◆存盤程序加快程式的運行速度
◆存盤程序增加sql陳述句的功能和靈活性、創建的存盤程序可以重復使用，
7.9.3存盤相關命令
1>◆創建一個存盤程序：
create procedure 存盤程序名稱()
create procedure 存盤程序名稱(引數名，資料型別)
◆洗掉一個存盤程序：drop procedure 存盤程序名稱
◆加強代碼的健壯性：
drop procedure if exists 存盤程序名稱
◆增強存盤的健壯性:
drop TABLE if EXISTS+存盤程序名稱
◆自動檢測統計資料行數:DECLARE i int DEFAULT
2>呼叫存盤程序：call 存盤程序名稱()
注意:在定義存盤的時候帶引數，必須用引數查詢，如果定義了形式引數、不傳入實際引數會報錯
3>begin...end 代表存盤程序體的開始和結束
4>if陳述句的格式：
◆單分支
if 條件 THEN
執行sql
else
執行sql
end if
◆多分支 （有多少的if 就需要寫多少個end if）
if 條件 THEN
執行sql
else if 條件 THEN
執行sql
else if 條件 THEN
執行sql
.............
else
執行sql
end if
end if
end if
5>while陳述句（回圈陳述句）
while 陳述句的格式：
while 條件 do
執行回圈體（
sql）
end while；
★注意:什么時候進入(退出)回圈：當條件成立時，進入回圈；當條件不成立時，退出回圈，
7.9.4創建存盤程序
create procedure 存盤程序名稱(引數名，資料型別)
begin
存盤程序體
end
例：
如果傳入得引數大于表中實際總記錄的數量，則往表中補齊
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/501723.html
標籤：其他