一洛史、數(shù)據(jù)的完整性之實(shí)體完整性
數(shù)據(jù)的完整性
作用：保證用戶輸入的數(shù)據(jù)保存到數(shù)據(jù)庫中是正確的荒适。
實(shí)質(zhì)：創(chuàng)建表的時(shí)候給表中的字段添加約束。實(shí)體完整性
實(shí)體：表中的一行或者一條記錄代表一個(gè)實(shí)體
實(shí)體完整性的作用：標(biāo)識(shí)每一行數(shù)據(jù)不重復(fù)
約束類型：主鍵約束【primary key】羞芍、唯一約束【unique】糠聪、自動(dòng)增長列【auto_increment】-
主鍵約束【primary key】
特點(diǎn)：數(shù)據(jù)唯一，且不能為null;
它的值用來唯一標(biāo)識(shí)表中的某一條記錄;
場(chǎng)景：在多個(gè)表的關(guān)聯(lián)關(guān)系中;// id設(shè)置為主鍵 create table student(id int, name varchar(50), primary key(id)); -
唯一約束【unique】
作用：在非主鍵列中不能輸入重復(fù)的值;create table student(id int primary key, name varchar(50) unique);primary key和unique之間的區(qū)別?
- 二者都強(qiáng)調(diào)的是唯一性
- 在同一個(gè)表中声旺，只能出現(xiàn)一個(gè)primary key笔链，可以出現(xiàn)多個(gè)unique
- primary key不允許為null，但是unique是允許的
-
自動(dòng)增長【auto_increment】
給主鍵添加添加自動(dòng)增長性腮猖，字段只能是整數(shù)類型鉴扫；
場(chǎng)景: 一般添加給主鍵；create table student(id int primary key auto_increment, name varchar(50) unique);
二澈缺、數(shù)據(jù)的完整性之域完整性
域完整性
作用：限制單元格數(shù)據(jù)的正確性坪创，域代表當(dāng)前單元格炕婶；
約束類型：數(shù)據(jù)類型、非空約束【not null】莱预、默認(rèn)值約束【default】-
數(shù)據(jù)類型
數(shù)字類型：int float doule decimal 日期類型：date 字符串類型：char varchar -
非空約束【not null】
create table student( id int primary key auto_increment, name varchar(50) not null);非空柠掂，即表示插入數(shù)據(jù)時(shí)不能為空；為空時(shí)依沮，數(shù)據(jù)庫立即報(bào)錯(cuò)涯贞。
-
默認(rèn)值約束【default】
create table student(id int primary key auto_increment, name varchar(50), addr varchar(50) default "shenzhen") // 不使用默認(rèn)值 insert into student (id,name, addr) values(1,'aaa','guagnzhou'); // 使用默認(rèn)值 insert into student(id,name, addr) values(2,'bbb',default); insert into student(id,name) values(3,'ccc');
二、數(shù)據(jù)的完整性之引用完整性
-
外鍵約束【foreign key】
注意: 添加外鍵必須先有主鍵危喉，主鍵和外鍵的類型必須保持一致宋渔；
作用: 將兩個(gè)甚至多個(gè)毫無關(guān)聯(lián)的表產(chǎn)生聯(lián)系
備注: 一張表中可以有多個(gè)外鍵
例如: 學(xué)生表，成績(jī)表// 創(chuàng)建學(xué)生表 create table student(stu_id int primary key, s_name varchar(50)) charset=utf8; // 添加學(xué)生數(shù)據(jù) insert into student values(1001, '張三'); insert into student values(1002, '李四'); insert into student values(1003, '王五'); insert into student values(1004, '趙六'); insert into student values(1005, '田七'); insert into student values(1006, '王八'); insert into student values(1007, '老九'); // 創(chuàng)建成績(jī)表 // 外鍵stu_id辜限，外鍵約束對(duì)應(yīng)的student的stu_id create table score(sco_id int primary key, score int, stu_id int,foreign key(stu_id) references student(stu_id)) charset=utf8; insert into score values(1, 89, 1001); insert into score values(2, 97, 1002); insert into score values(3, 99, 1003); insert into score values(4, 82, 1004); insert into score values(5, 86, 1005);
四皇拣、多表查詢
-
表與表之間的關(guān)系
- 一對(duì)一, 通過嵌套的方式
- 一對(duì)多(多對(duì)一), 添加外鍵
- 多對(duì)多, 單獨(dú)創(chuàng)建一張新的表
-
合并結(jié)果集
作用：將兩個(gè)select語句的查詢結(jié)果合并到一起;
兩種方式：union去除重復(fù)記錄【并集】、union all獲取所有的結(jié)果薄嫡；// 創(chuàng)建表A和表B create table A( name varchar(10), score int ); create table B( name varchar(10), score int ); // 插入數(shù)據(jù) insert into A values('a',10),('b',20),('c',30); insert into B values('a',10),('d',40),('c',30); // union合并結(jié)果集 select * from A union select * from B; +------+-------+ | name | score | +------+-------+ | a | 10 | | b | 20 | | c | 30 | | d | 40 | +------+-------+ // union all合并結(jié)果集 select * from A union all select * from B; +------+-------+ | name | score | +------+-------+ | a | 10 | | b | 20 | | c | 30 | | a | 10 | | d | 40 | | c | 30 | +------+-------+ // 問題: 如果遇到列數(shù)不相同的情況 create table C( name varchar(10), score int, age int); insert into C values('a',100,29),('e',20,18),('c',300,10); insert into C values('a',10,29),('e',20,18),('c',30,10); select * from A union select name,score from C; +------+-------+ | name | score | +------+-------+ | a | 10 | | b | 20 | | c | 30 | | a | 100 | | e | 20 | | c | 300 | +------+-------+注意：被合并的兩個(gè)結(jié)果氧急，字段、字段類型必須相同;
-
內(nèi)連接inner join
內(nèi)連接：查詢左右表都有的數(shù)據(jù)毫深，不要左右中空的那一部分态蒂；
內(nèi)連接：左右連接的交集；語法: select 列1,列2,列N from tableA inner join tableB on tableA.列 = tableB.列 (此處表連接成一張大表费什，完全當(dāng)成一張普通表看) where,having,group by.... (條件照常寫) 例如: 查詢每個(gè)學(xué)生的具體信息 select tableA.*,tableB.score from student tableA inner join score tableB on tableA.stu_id=tableB.stu_id; select tableA.*,score from student tableA inner join score tableB on tableA.stu_id=tableB.stu_id; -
左連接left join
左連接1: 得到的是A的所有數(shù)據(jù)，和滿足某一條件的B的數(shù)據(jù);左連接2: 得到的是A中的所有數(shù)據(jù)減去"與B滿足同一條件 的數(shù)據(jù)"手素，然后得到的A剩余數(shù)據(jù)鸳址；
語法:
select
列1,列2,列N
from
tableA
left join
tableB
on
tableA.列 = tableB.列 (此處表連接成一張大表，完全當(dāng)成一張普通表看)
where,having,group by.... (條件照常寫)
例如,左連接1:
select
tableA.*,score
from
student tableA
left join
score tableB
on
tableA.stu_id=tableB.stu_id;
例如,左連接2:
select
tableA.*
from
student tableA
left join
score tableB
on
tableA.stu_id=tableB.stu_id
where
score is null;
-
右連接right join
右連接1: 得到的是B的所有數(shù)據(jù)泉懦，和滿足某一條件的A的數(shù)據(jù)稿黍；右連接2: 得到的是B中的所有數(shù)據(jù)減去 "與A滿足同一條件的數(shù)據(jù)"，然后得到的B剩余數(shù)據(jù);
語法: select 列1,列2,列N from tableA right join tableB on tableA.列 = tableB.列 (此處表連接成一張大表崩哩，完全當(dāng)成一張普通表看) where,having,group by.... (條件照常寫) 例如: select tableA.*,score from student tableA right join score tableB on tableA.stu_id=tableB.stu_id;左連接：即以左表為基準(zhǔn)巡球，到右表找匹配的數(shù)據(jù),找不到匹配的用NULL補(bǔ)齊；
推薦左連接來代替右連接邓嘹，兼容性會(huì)好一些酣栈； -
自然連接natural join
通過MySql自己的判斷完成連接過程，不需要指定連接條件汹押。MySql會(huì)使用表內(nèi)的矿筝，相同的字段，作為連接條件棚贾。select * from A natural join B;
五窖维、多表查詢
1榆综、男同學(xué)的考試科目及對(duì)應(yīng)成績(jī)
2、姓張同學(xué)的考試科目及對(duì)應(yīng)成績(jī)
3铸史、既有英語又有計(jì)算機(jī)成績(jī)的學(xué)生信息
4鼻疮、姓王的同學(xué)并且有一科以上成績(jī)大于90分的學(xué)生信息
5、查詢李四的考試科目（c_name）和考試成績(jī)（grade）
6琳轿、查詢計(jì)算機(jī)成績(jī)低于95的學(xué)生信息
7判沟、查詢都是湖南的學(xué)生的姓名、年齡利赋、院系和考試科目及成績(jī)
1水评、
SELECT name,sex,c_name,grade
FROM
student,score
WHERE
student.id=score.stu_id AND sex='男';
2、
SELECT name,c_name,grade
FROM
student,score
WHERE
student.id=score.stu_id AND name LIKE "張%";
3媚送、
SELECT student.*
FROM
student,score s1,score s2
WHERE
student.id=s1.stu_id AND student.id=s2.stu_id
AND
s1.c_name="計(jì)算機(jī)" AND s2.c_name="英語";
4中燥、
SELECT student.*
FROM
student,score
WHERE
student.id=score.stu_id AND grade>90 AND name LIKE "王%";
5、
SELECT name,c_name,grade
FROM
student,score
WHERE
student.id=score.stu_id AND name="李四";
6塘偎、
SELECT student.*,c_name,grade
FROM
student,score
WHERE
student.id=score.stu_id AND c_name="計(jì)算機(jī)" AND grade<95;
7疗涉、
SELECT name,2017-birth AS age,department,c_name,grade
FROM
student,score
WHERE
student.id=score.stu_id AND address like "%湖南%";
找到表與表的對(duì)應(yīng)關(guān)系;
如果多張表中有同一個(gè)屬性名時(shí)必須標(biāo)注是哪個(gè)表中的屬性;
六、數(shù)據(jù)庫的備份和恢復(fù)
-
備份
生成SQL腳本吟秩，導(dǎo)出數(shù)據(jù)咱扣。語法: $ mysqldump -u root -p 數(shù)據(jù)庫名 > 生成sql腳本的路徑 例如: $ mysqldump -u root -p mydb1 > ~/mydb1.sql -
恢復(fù)
執(zhí)行sql腳本，恢復(fù)數(shù)據(jù)涵防。前提: 必須先創(chuàng)建數(shù)據(jù)庫【空的】 注意: 需要先登錄數(shù)據(jù)庫闹伪，然后進(jìn)入指定的數(shù)據(jù)庫，執(zhí)行sql腳本 語法: mysql> source sql腳本的路徑 例如： mysql> create database test; mysql> use test; mysql> source ~/mydb1.sql;
八壮池、MySQL與Python的交互
- 安裝
pip3 install pymysql
- 使用
import pymysql
# 鏈接數(shù)據(jù)庫
db = pymysql.Connect(host='127.0.0.1', port=3306, user='root', password='123456', database='test08', charset='utf8')
# 數(shù)據(jù)庫游標(biāo)
cursor = db.cursor()
# 查詢數(shù)據(jù)
db.begin()
cursor.execute("select * from students_info;")
db.commit()
# 獲取所有數(shù)據(jù)
print(cursor.fetchall())
# 獲取一個(gè)偏瓤，根據(jù)下標(biāo)取對(duì)應(yīng)的數(shù)據(jù)
print(cursor.fetchall()[0])
# 注: 不能同時(shí)使用，因?yàn)橛螛?biāo)會(huì)往后移動(dòng)
# 插入數(shù)據(jù)
db.begin()
cursor.execute("insert into students_info values ('2000', '老李', '18', '男', '廣東深圳', '1703', '89', '90', '81');")
db.commit()
# 更新數(shù)據(jù)
db.begin()
cursor.execute("update students_info set class='1807' where id=2000")
db.commit()
# 刪除數(shù)據(jù)
db.begin()
cursor.execute("delete from students_info where id=2000")
db.commit()
作者：西門奄
鏈接：http://www.reibang.com/u/77035eb804c3
來源：簡(jiǎn)書
簡(jiǎn)書著作權(quán)歸作者所有椰憋，任何形式的轉(zhuǎn)載都請(qǐng)聯(lián)系作者獲得授權(quán)并注明出處厅克。
