02-数据库MySQL[Python]

一、数据的完整性之实体完整性

  • 数据的完整性
    作用：保证用户输入的数据保存到数据库中是正确的。
    实质：创建表的时候给表中的字段添加约束。

  • 实体完整性
    实体：表中的一行或者一条记录代表一个实体
    实体完整性的作用：标识每一行数据不重复
    约束类型：主键约束【primary key】、唯一约束【unique】、自动增长列【auto_increment】

  • 主键约束【primary key】
    特点：数据唯一，且不能为null;
    它的值用来唯一标识表中的某一条记录;
    场景：在多个表的关联关系中;

    // id设置为主键
create table student(id int, name varchar(50), primary key(id));

  • 唯一约束【unique】
    作用：在非主键列中不能输入重复的值;

    create table student(id int primary key, name varchar(50) unique);

    primary key和unique之间的区别?

    • 二者都强调的是唯一性
    • 在同一个表中，只能出现一个primary key，可以出现多个unique
    • primary key不允许为null，但是unique是允许的

  • 自动增长【auto_increment】
    给主键添加添加自动增长性，字段只能是整数类型；
    场景: 一般添加给主键；

    create table student(id int primary key auto_increment, name varchar(50) unique);

二、数据的完整性之域完整性

  • 域完整性
    作用：限制单元格数据的正确性，域代表当前单元格；
    约束类型：数据类型、非空约束【not null】、默认值约束【default】

  • 数据类型

    数字类型：int float doule decimal
日期类型：date
字符串类型：char varchar

  • 非空约束【not null】

    create table student( id int primary key auto_increment, name varchar(50) not null);

    非空，即表示插入数据时不能为空；为空时，数据库立即报错。

  • 默认值约束【default】

    create table student(id int primary key auto_increment, name varchar(50), addr varchar(50) default "shenzhen")

// 不使用默认值
insert into student (id,name, addr) values(1,'aaa','guagnzhou');
// 使用默认值
insert into student(id,name, addr) values(2,'bbb',default);
insert into student(id,name) values(3,'ccc');

二、数据的完整性之引用完整性

  • 外键约束【foreign key】
    注意: 添加外键必须先有主键，主键和外键的类型必须保持一致；
    作用: 将两个甚至多个毫无关联的表产生联系
    备注: 一张表中可以有多个外键
    例如: 学生表，成绩表 
    // 创建学生表
create table student(stu_id int primary key, s_name varchar(50)) charset=utf8;

// 添加学生数据
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, '老九');

// 创建成绩表
// 外键stu_id，外键约束对应的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);

四、多表查询

  • 表与表之间的关系

    1. 一对一, 通过嵌套的方式
    2. 一对多(多对一), 添加外键
    3. 多对多, 单独创建一张新的表

  • 合并结果集
    作用：将两个select语句的查询结果合并到一起;
    两种方式：union去除重复记录【并集】、union all获取所有的结果；

    // 创建表A和表B
create table A( name varchar(10), score int );
create table B( name varchar(10), score int );

// 插入数据
insert into A values('a',10),('b',20),('c',30);
insert into B values('a',10),('d',40),('c',30);

// union合并结果集
select * from A
union
select * from B;
+------+-------+
| name | score |
+------+-------+
| a    |    10 |
| b    |    20 |
| c    |    30 |
| d    |    40 |
+------+-------+

// union all合并结果集
select * from A
union all
select * from B;
+------+-------+
| name | score |
+------+-------+
| a    |    10 |
| b    |    20 |
| c    |    30 |
| a    |    10 |
| d    |    40 |
| c    |    30 |  
+------+-------+


// 问题: 如果遇到列数不相同的情况
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 |
+------+-------+

    注意：被合并的两个结果，字段、字段类型必须相同;

  • 内连接inner join
    内连接：查询左右表都有的数据，不要左右中空的那一部分；
    内连接：左右连接的交集；


    1.1内连接
    语法:
select 列1,列2,列N from
tableA 
inner join 
tableB
on tableA.列 = tableB.列  (此处表连接成一张大表，完全当成一张普通表看)
where,having,group by.... (条件照常写)

例如:
查询每个学生的具体信息
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的所有数据，和满足某一条件的B的数据;


    1.2.1左连接

    左连接2: 得到的是A中的所有数据减去"与B满足同一条件 的数据"，然后得到的A剩余数据；


    1.2.2左连接
语法:
  select 
  列1,列2,列N 
  from
  tableA 
  left join 
  tableB
  on 
  tableA.列 = tableB.列  (此处表连接成一张大表，完全当成一张普通表看)
  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的所有数据，和满足某一条件的A的数据；


    1.3.1右连接

    右连接2: 得到的是B中的所有数据减去 "与A满足同一条件的数据"，然后得到的B剩余数据;


    1.3.2右连接
    语法:
select 
列1,列2,列N 
from
tableA 
right join 
tableB
on 
tableA.列 = tableB.列  (此处表连接成一张大表，完全当成一张普通表看)
where,having,group by.... (条件照常写)

例如:
select 
tableA.*,score
from 
student tableA 
right join 
score tableB
on 
tableA.stu_id=tableB.stu_id;

    左连接：即以左表为基准，到右表找匹配的数据,找不到匹配的用NULL补齐；
    推荐左连接来代替右连接，兼容性会好一些；

  • 自然连接natural join
    通过MySql自己的判断完成连接过程，不需要指定连接条件。MySql会使用表内的，相同的字段，作为连接条件。

    select * from A natural join B;

五、多表查询

表1-studet

表2-score
1、男同学的考试科目及对应成绩
2、姓张同学的考试科目及对应成绩
3、既有英语又有计算机成绩的学生信息
4、姓王的同学并且有一科以上成绩大于90分的学生信息

5、查询李四的考试科目（c_name）和考试成绩（grade）
6、查询计算机成绩低于95的学生信息
7、查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

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 "%湖南%";

找到表与表的对应关系;
如果多张表中有同一个属性名时必须标注是哪个表中的属性;

六、数据库的备份和恢复

  • 备份
    生成SQL脚本，导出数据。

    语法:
$ mysqldump -u root -p  数据库名 > 生成sql脚本的路径

例如:
$ mysqldump -u root -p mydb1 > ~/mydb1.sql

  • 恢复
    执行sql脚本，恢复数据。

    前提: 必须先创建数据库【空的】
注意: 需要先登录数据库，然后进入指定的数据库，执行sql脚本
语法:
  mysql> source sql脚本的路径

例如：
  mysql> create database test;
  mysql> use test;
  mysql> source ~/mydb1.sql;

八、MySQL与Python的交互

- 安装
    pip3 install pymysql

- 使用
import pymysql

# 链接数据库
db = pymysql.Connect(host='127.0.0.1', port=3306, user='root', password='123456', database='test08', charset='utf8')
# 数据库游标
cursor = db.cursor()

# 查询数据
db.begin()
cursor.execute("select * from students_info;")
db.commit()
# 获取所有数据
print(cursor.fetchall())
# 获取一个，根据下标取对应的数据
print(cursor.fetchall()[0])
# 注: 不能同时使用，因为游标会往后移动

# 插入数据
db.begin()
cursor.execute("insert into students_info values ('2000', '老李', '18', '男', '广东深圳', '1703', '89', '90', '81');")
db.commit()

# 更新数据
db.begin()
cursor.execute("update students_info set class='1807' where id=2000")
db.commit()

# 删除数据
db.begin()
cursor.execute("delete from students_info where id=2000")
db.commit()
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,080评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,422评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,630评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,554评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,662评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,856评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,014评论 3赞 408
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,752评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,212评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,541评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,687评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,347评论 4赞 331
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,973评论 3赞 315
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,777评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,006评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,406评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,576评论 2赞 349

推荐阅读更多精彩内容

  • Java初级面试题
    1. Java基础部分 基础部分的顺序：基本语法，类相关的语法，内部类的语法，继承相关的语法，异常的语法，线程的语...
    子非鱼_t_阅读 31,598评论 18赞 399
  • 01-数据库MySQL[Python]
    一、数据库系统 数据库DataBase【DB】，指的是长期保存到计算机上的数据，按照一定顺序组织，可以被各种用户或...
    EndEvent阅读 1,888评论 2赞 3
  • MYSQL基础
    MYSQL 基础知识 1 MySQL数据库概要 2 简单MySQL环境 3 数据的存储和获取 4 MySQL基本操...
    Kingtester阅读 7,787评论 5赞 116
  • 学习笔记:MySQL入门经典(机械工业出版社)
    观其大纲 page 01 基础知识 1 MySQL数据库概要 2 简单MySQL环境 3 数据的存储和获取 4 M...
    周少言阅读 3,155评论 0赞 33
  • Oracle数据库面试题
    （一）Oracle数据库 1.oracle中row_id理解 ORACLE的row_id是一个伪列,其个是为18个...
    独云阅读 5,594评论 0赞 10