1、數(shù)據(jù)庫(kù)連表

1.1， 一對(duì)多

使用外鍵做約束。注意：外鍵列的數(shù)據(jù)類型要一致。

命令的方式創(chuàng)建外鍵
CREATE table part1(
nid int not null auto_increment primary key,
caption VARCHAR(32) not null
)

CREATE TABLE person1(
nid int not null auto_increment,
name VARCHAR(32) not null,
email VARCHAR(32) not null,
part1_nid INT not null,
primary key (nid), -- 可用來(lái)創(chuàng)建聯(lián)合主鍵
CONSTRAINT `fk_person1_part1` FOREIGN KEY (part1_nid) REFERENCES part1(nid)
)
連表操作：
#找到所有CEO部門的所有人員

SELECT person.`name`,part.caption from person LEFT JOIN part ON person.part_nid = part.nid WHERE part.caption = 'CEO'

a left join b on a.xx = b.xxx
#以A為主，將A中所有的數(shù)據(jù)都羅列出來(lái)，b表顯示a表相關(guān)的數(shù)據(jù)
a right join b on a.xx = b.xxx
#以b為主，將b中所有的數(shù)據(jù)都羅列出來(lái)，A表顯示b表相關(guān)的數(shù)據(jù)
a inner join b on a.xx = b.xxx
#自動(dòng)忽略未建立連接的數(shù)據(jù)
eg：
SELECT
person.nid as pidl,
person.name as pname,
part.caption as cp,
color.title as title
FROM
person
LEFT JOIN part ON person.part_nid = part.nid
LEFT JOIN color on person.color_nid = color.nid
WHERE
part.caption = 'CEO' and color.title = 'red'

可以將 part、color 稱之為字典表

**多對(duì)多
需要有第三張表來(lái)做關(guān)聯(lián)
eg：
SELECT * from relation
left JOIN t_nan on relation.nan_nid = t_nan.nid
LEFT JOIN t_nv on relation.nv_nid = t_nv.nid
WHERE t_nan.`name` = 'alex'

2、SQL注入

發(fā)生場(chǎng)景

以上情況都會(huì)發(fā)生腳本執(zhí)行的情況。

?

3、視圖

虛擬表，

是一個(gè)虛擬表，通過(guò)對(duì)真實(shí)表的查詢處理得到的一張臨時(shí)虛擬表
創(chuàng)建視圖：
CREATE VIEW v1 AS
SELECT relation.nid,t_nan.`name` as nan,t_nv.`name` as nv FROM relation
LEFT JOIN t_nan on relation.nan_nid = t_nan.nid
LEFT JOIN t_nv on relation.nv_nid = t_nv.nid
WHERE t_nan.`name` = 'alex'
or
CREATE VIEW v1 as SELECT 1
修改視圖：
ALTER VIEW v1 as
SELECT * from person WHERE nid >5
刪除視圖：
drop VIEW v1;
5、存儲(chǔ)過(guò)程
類似于函數(shù)，將邏輯存儲(chǔ)在數(shù)據(jù)中
創(chuàng)建最簡(jiǎn)單的存儲(chǔ)過(guò)程
DROP PROCEDURE if EXISTS proc_p1;
CREATE PROCEDURE proc_p1()
BEGIN
SELECT * from t_nan;
END
調(diào)用存儲(chǔ)過(guò)程：
CALL proc_p1()
刪除存儲(chǔ)過(guò)程
drop PROCEDURE proc_p1;

示例：
delimiter $$
drop PROCEDURE if EXISTS proc_p1 $$

CREATE PROCEDURE proc_p1(
in i1 int,
out i2 INT
)

BEGIN
DECLARE d2 int DEFAULT 3;

if i1 = 1 THEN
set i2 = 100 + d2;
ELSEIF i2 = 2 THEN
set i2 = 200 + d2;
else
set i2 = 1000 + d2;
end IF;

end $$

delimiter ；

eg2：
delimiter $$
drop PROCEDURE if EXISTS proc_p1 $$
CREATE PROCEDURE proc_p1(
in i1 int,
INOUT ii INT,
out i2 INT
)
BEGIN
DECLARE d2 int DEFAULT 3;
set ii = ii + 1;
if i1 = 1 THEN
set i2 = 100 + d2;
ELSEIF i2 = 2 THEN
set i2 = 200 + d2;
else
set i2 = 1000 + d2;
end IF;
end $$
delimiter ；

4、

使用pysql調(diào)用存儲(chǔ)過(guò)程
#!/usr/bin/envpython
#-*-coding:utf-8-*-
importpymysql
conn=pymysql.connect(host='10.10.8.12',port=3306,user='sysadmin',passwd='password01!',db='q2')
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
#執(zhí)行存儲(chǔ)過(guò)程
row=cursor.callproc('proc_p1',(1,2,3))
#打印存儲(chǔ)過(guò)程得查詢結(jié)果
print(cursor.fetchall())
#獲取存儲(chǔ)過(guò)程的值
effect_row=cursor.execute("select@_proc_p1_0,@_proc_p1_1,@_proc_p1_2")
#取存儲(chǔ)過(guò)程得返回值
result=cursor.fetchone()
print(result)
conn.commit()
cursor.close()
conn.close()

5、存儲(chǔ)過(guò)程查詢結(jié)果的傳遞

?

6、觸發(fā)器

對(duì)某個(gè)表進(jìn)行【增/刪/改】操作的前后如果希望觸發(fā)某個(gè)特定的行為時(shí)，可以使用觸發(fā)器，觸發(fā)器用于定制用戶對(duì)表的行進(jìn)行【增/刪/改】前后的行為

# 插入前 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN... END# 插入后 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN... END# 刪除前 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW BEGIN... END# 刪除后 CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW BEGIN... END# 更新前 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW BEGIN... END# 更新后 CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW BEGIN... END delimiter // CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGINIF NEW. NAME == 'alex' THENINSERT INTO tb2 (NAME) VALUES('aa') END END// delimiter ; eg2： delimiter // CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGINIF NEW. num = 666 THENINSERT INTO tb2 (NAME)VALUES('666'),('666') ;ELSEIF NEW. num = 555 THENINSERT INTO tb2 (NAME)VALUES('555'),('555') ;END IF; END// delimiter ; 刪除： DROP TRIGGER tri_after_insert_tb1; 觸發(fā)： insert into tb1(num) values(666) demo

觸發(fā)器有個(gè)特殊的值 old 、new

delimiter $$
drop TRIGGER if EXISTS tri_before_insert_color $$
CREATE TRIGGER tri_before_insert_color BEFORE INSERT on color for EACH ROW
BEGIN
INSERT INTO t_nan(name) VALUES(new.title);
END $$
delimiter ;

new可用來(lái) 獲取插入的值
當(dāng)做刪除的數(shù)據(jù)時(shí)用old來(lái)獲取

7、函數(shù)：

CHAR_LENGTH(str)返回值為字符串str 的長(zhǎng)度，長(zhǎng)度的單位為字符。一個(gè)多字節(jié)字符算作一個(gè)單字符。對(duì)于一個(gè)包含五個(gè)二字節(jié)字符集, LENGTH()返回值為 10, 而CHAR_LENGTH()的返回值為5。CONCAT(str1,str2,...)字符串拼接如有任何一個(gè)參數(shù)為NULL ，則返回值為 NULL。CONCAT_WS(separator,str1,str2,...)字符串拼接（自定義連接符）CONCAT_WS()不會(huì)忽略任何空字符串。 (然而會(huì)忽略所有的 NULL）。CONV(N,from_base,to_base)進(jìn)制轉(zhuǎn)換例如：SELECT CONV('a',16,2); 表示將 a 由16進(jìn)制轉(zhuǎn)換為2進(jìn)制字符串表示FORMAT(X,D)將數(shù)字X 的格式寫為'#,###,###.##',以四舍五入的方式保留小數(shù)點(diǎn)后 D 位， 并將結(jié)果以字符串的形式返回。若 D 為 0, 則返回結(jié)果不帶有小數(shù)點(diǎn)，或不含小數(shù)部分。例如：SELECT FORMAT(12332.1,4); 結(jié)果為： '12,332.1000'INSERT(str,pos,len,newstr)在str的指定位置插入字符串pos：要替換位置其實(shí)位置len：替換的長(zhǎng)度newstr：新字符串特別的：如果pos超過(guò)原字符串長(zhǎng)度，則返回原字符串如果len超過(guò)原字符串長(zhǎng)度，則由新字符串完全替換INSTR(str,substr)返回字符串 str 中子字符串的第一個(gè)出現(xiàn)位置。LEFT(str,len)返回字符串str 從開(kāi)始的len位置的子序列字符。LOWER(str)變小寫UPPER(str)變大寫LTRIM(str)返回字符串 str ，其引導(dǎo)空格字符被刪除。RTRIM(str)返回字符串 str ，結(jié)尾空格字符被刪去。SUBSTRING(str,pos,len)獲取字符串子序列LOCATE(substr,str,pos)獲取子序列索引位置REPEAT(str,count)返回一個(gè)由重復(fù)的字符串str 組成的字符串，字符串str的數(shù)目等于count 。若 count <= 0,則返回一個(gè)空字符串。若str 或 count 為 NULL，則返回 NULL 。REPLACE(str,from_str,to_str)返回字符串str 以及所有被字符串to_str替代的字符串from_str 。REVERSE(str)返回字符串 str ，順序和字符順序相反。RIGHT(str,len)從字符串str 開(kāi)始，返回從后邊開(kāi)始len個(gè)字符組成的子序列SPACE(N)返回一個(gè)由N空格組成的字符串。SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)不帶有l(wèi)en 參數(shù)的格式從字符串str返回一個(gè)子字符串，起始于位置 pos。帶有l(wèi)en參數(shù)的格式從字符串str返回一個(gè)長(zhǎng)度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式為標(biāo)準(zhǔn) SQL 語(yǔ)法。也可能對(duì)pos使用一個(gè)負(fù)值。假若這樣，則子字符串的位置起始于字符串結(jié)尾的pos 字符，而不是字符串的開(kāi)頭位置。在以下格式的函數(shù)中可以對(duì)pos 使用一個(gè)負(fù)值。mysql> SELECT SUBSTRING('Quadratically',5);-> 'ratically'mysql> SELECT SUBSTRING('foobarbar' FROM 4);-> 'barbar'mysql> SELECT SUBSTRING('Quadratically',5,6);-> 'ratica'mysql> SELECT SUBSTRING('Sakila', -3);-> 'ila'mysql> SELECT SUBSTRING('Sakila', -5, 3);-> 'aki'mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);-> 'ki'TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)返回字符串 str ， 其中所有remstr 前綴和/或后綴都已被刪除。若分類符BOTH、LEADIN或TRAILING中沒(méi)有一個(gè)是給定的,則假設(shè)為BOTH 。 remstr 為可選項(xiàng)，在未指定情況下，可刪除空格。mysql> SELECT TRIM(' bar ');-> 'bar'mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');-> 'barxxx'mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');-> 'bar'mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');-> 'barx'部分內(nèi)置函數(shù) 常用函數(shù)

函數(shù)用select執(zhí)行，內(nèi)部沒(méi)有結(jié)果集

8、事務(wù)

事務(wù)用于將某些操作的多個(gè)SQL作為原子性操作，一旦有某一個(gè)出現(xiàn)錯(cuò)誤，即可回滾到原來(lái)的狀態(tài)，從而保證數(shù)據(jù)庫(kù)數(shù)據(jù)完整性。

pymysql 默認(rèn)就是事務(wù)性得，只有所有得cursonr.execute 都執(zhí)行成功后才會(huì)提交數(shù)據(jù)庫(kù)
9、條件語(yǔ)句、循環(huán)預(yù)計(jì)
10、動(dòng)態(tài)執(zhí)行sql語(yǔ)句
寫一個(gè)存儲(chǔ)過(guò)程，將sql語(yǔ)句（帶占位符）提交給存儲(chǔ)過(guò)程

?

?

delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql ()
BEGIN
declare p1 int;
set p1 = 11;
set @p1 = p1;

PREPARE prod FROM 'select * from person where nid > ?';
EXECUTE prod USING @p1;
DEALLOCATE prepare prod;

END\\
delimiter ;

CALL proc_sql();

delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql (
in strSQL VARCHAR(128),
in nid int
)
BEGIN
set @sqll = strSQL ;
set @p1 = nid;

PREPARE prod FROM @sqll;
EXECUTE prod USING @p1;
DEALLOCATE prepare prod;

END\\
delimiter ;

?call proc_sql('select * from person where nid > ?',11) -- 實(shí)際測(cè)試發(fā)現(xiàn)不能防止注入

?

?

轉(zhuǎn)載于:https://www.cnblogs.com/workherd/p/8520265.html

《新程序員》：云原生和全面數(shù)字化實(shí)踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結(jié)

以上是生活随笔為你收集整理的day38--MySQL基础二的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。