02-數(shù)據(jù)庫MySQL[Python]

一、數(shù)據(jù)的完整性之實體完整性

  • 數(shù)據(jù)的完整性
    作用：保證用戶輸入的數(shù)據(jù)保存到數(shù)據(jù)庫中是正確的。
    實質(zhì)：創(chuàng)建表的時候給表中的字段添加約束。

  • 實體完整性
    實體：表中的一行或者一條記錄代表一個實體
    實體完整性的作用：標(biāo)識每一行數(shù)據(jù)不重復(fù)
    約束類型：主鍵約束【primary key】、唯一約束【unique】、自動增長列【auto_increment】

  • 主鍵約束【primary key】
    特點：數(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ū)別?

    • 二者都強調(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)系

    1. 一對一, 通過嵌套的方式
    2. 一對多(多對一), 添加外鍵
    3. 多對多, 單獨創(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)連接：左右連接的交集；


    1.1內(nèi)連接
    語法:
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ù);


    1.2.1左連接

    左連接2: 得到的是A中的所有數(shù)據(jù)減去"與B滿足同一條件 的數(shù)據(jù)"，然后得到的A剩余數(shù)據(jù)；


    1.2.2左連接
語法:
  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ù)；


    1.3.1右連接

    右連接2: 得到的是B中的所有數(shù)據(jù)減去 "與A滿足同一條件的數(shù)據(jù)"，然后得到的B剩余數(shù)據(jù);


    1.3.2右連接
    語法:
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補齊；
    推薦左連接來代替右連接，兼容性會好一些；

  • 自然連接natural join
    通過MySql自己的判斷完成連接過程，不需要指定連接條件。MySql會使用表內(nèi)的，相同的字段，作為連接條件。

    select * from A natural join B;

五、多表查詢

表1-studet

表2-score
1、男同學(xué)的考試科目及對應(yīng)成績
2、姓張同學(xué)的考試科目及對應(yīng)成績
3、既有英語又有計算機成績的學(xué)生信息
4、姓王的同學(xué)并且有一科以上成績大于90分的學(xué)生信息

5、查詢李四的考試科目（c_name）和考試成績（grade）
6、查詢計算機成績低于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="計算機" 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="計算機" 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])
# 注: 不能同時使用，因為游標(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()
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 1. Java基礎(chǔ)部分 基礎(chǔ)部分的順序：基本語法，類相關(guān)的語法，內(nèi)部類的語法，繼承相關(guān)的語法，異常的語法，線程的語...
    子非魚_t_閱讀 34,697評論 18贊 399
  • 一、數(shù)據(jù)庫系統(tǒng) 數(shù)據(jù)庫DataBase【DB】，指的是長期保存到計算機上的數(shù)據(jù)，按照一定順序組織，可以被各種用戶或...
    EndEvent閱讀 2,084評論 2贊 3
  • MYSQL 基礎(chǔ)知識 1 MySQL數(shù)據(jù)庫概要 2 簡單MySQL環(huán)境 3 數(shù)據(jù)的存儲和獲取 4 MySQL基本操...
    Kingtester閱讀 8,060評論 5贊 115
  • 觀其大綱 page 01 基礎(chǔ)知識 1 MySQL數(shù)據(jù)庫概要 2 簡單MySQL環(huán)境 3 數(shù)據(jù)的存儲和獲取 4 M...
    周少言閱讀 3,255評論 0贊 33
  • （一）Oracle數(shù)據(jù)庫 1.oracle中row_id理解 ORACLE的row_id是一個偽列,其個是為18個...
    獨云閱讀 5,735評論 0贊 10

友情鏈接更多精彩內(nèi)容