一奖磁、數(shù)據(jù)的完整性之實(shí)體完整性
數(shù)據(jù)的完整性
作用：保證用戶輸入的數(shù)據(jù)保存到數(shù)據(jù)庫中是正確的茎用。
實(shí)質(zhì)：創(chuàng)建表的時候給表中的字段添加約束秒梅。實(shí)體完整性
實(shí)體：表中的一行或者一條記錄代表一個實(shí)體
實(shí)體完整性的作用：標(biāo)識每一行數(shù)據(jù)不重復(fù)
約束類型：主鍵約束【primary key】、唯一約束【unique】隅要、自動增長列【auto_increment】-
主鍵約束【primary key】
特點(diǎn)：數(shù)據(jù)唯一报账，且不能為null;
它的值用來唯一標(biāo)識表中的某一條記錄;
場景：在多個表的關(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)的是唯一性
- 在同一個表中研底，只能出現(xiàn)一個primary key，可以出現(xiàn)多個unique
- primary key不允許為null透罢，但是unique是允許的
-
自動增長【auto_increment】
給主鍵添加添加自動增長性榜晦，字段只能是整數(shù)類型；
場景: 一般添加給主鍵羽圃；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ù)據(jù)庫立即報錯吨掌。
-
默認(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】
注意: 添加外鍵必須先有主鍵，主鍵和外鍵的類型必須保持一致膜宋；
作用: 將兩個甚至多個毫無關(guān)聯(lián)的表產(chǎn)生聯(lián)系
備注: 一張表中可以有多個外鍵
例如: 學(xué)生表窿侈，成績表// 創(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)建成績表 // 外鍵stu_id，外鍵約束對應(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)系
- 一對一, 通過嵌套的方式
- 一對多(多對一), 添加外鍵
- 多對多, 單獨(dú)創(chuàng)建一張新的表
-
合并結(jié)果集
作用：將兩個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 | +------+-------+注意：被合并的兩個結(jié)果肛著，字段圆兵、字段類型必須相同;
-
內(nèi)連接inner join
內(nèi)連接：查詢左右表都有的數(shù)據(jù)，不要左右中空的那一部分枢贿；
內(nèi)連接：左右連接的交集殉农；[圖片上傳失敗...(image-212632-1540206313688)]
語法: select 列1,列2,列N from tableA inner join tableB on tableA.列 = tableB.列 (此處表連接成一張大表，完全當(dāng)成一張普通表看) where,having,group by.... (條件照常寫) 例如: 查詢每個學(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ù);[圖片上傳失敗...(image-b84439-1540206313688)]
左連接2: 得到的是A中的所有數(shù)據(jù)減去"與B滿足同一條件 的數(shù)據(jù)"超凳，然后得到的A剩余數(shù)據(jù)愈污；
[圖片上傳失敗...(image-765c85-1540206313688)]
語法:
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ù)暂雹；[圖片上傳失敗...(image-eee17-1540206313688)]
右連接2: 得到的是B中的所有數(shù)據(jù)減去 "與A滿足同一條件的數(shù)據(jù)"，然后得到的B剩余數(shù)據(jù);
[圖片上傳失敗...(image-bb571b-1540206313687)]
語法: 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ǔ)齊簿盅；
推薦左連接來代替右連接挥下，兼容性會好一些； -
自然連接natural join
通過MySql自己的判斷完成連接過程桨醋，不需要指定連接條件棚瘟。MySql會使用表內(nèi)的，相同的字段喜最，作為連接條件偎蘸。select * from A natural join B;
五、多表查詢
[圖片上傳失敗...(image-b7a186-1540206313694)]
[圖片上傳失敗...(image-70b1a4-1540206313694)]
1瞬内、男同學(xué)的考試科目及對應(yīng)成績
2迷雪、姓張同學(xué)的考試科目及對應(yīng)成績
3、既有英語又有計(jì)算機(jī)成績的學(xué)生信息
4虫蝶、姓王的同學(xué)并且有一科以上成績大于90分的學(xué)生信息
5章咧、查詢李四的考試科目（c_name）和考試成績（grade）
6、查詢計(jì)算機(jī)成績低于95的學(xué)生信息
7能真、查詢都是湖南的學(xué)生的姓名赁严、年齡、院系和考試科目及成績
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 "%湖南%";
找到表與表的對應(yīng)關(guān)系;
如果多張表中有同一個屬性名時必須標(biāo)注是哪個表中的屬性;
六织鲸、數(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())
# 獲取一個，根據(jù)下標(biāo)取對應(yīng)的數(shù)據(jù)
print(cursor.fetchall()[0])
# 注: 不能同時使用丘损，因?yàn)橛螛?biāo)會往后移動
# 插入數(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()
