02-数据库MySQL[Python]

2018-10-25 本文已影响0人慕杨_

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

数据的完整性
作用：保证用户输入的数据保存到数据库中是正确的。
实质：创建表的时候给表中的字段添加约束。
实体完整性
实体：表中的一行或者一条记录代表一个实体
实体完整性的作用：标识每一行数据不重复
约束类型：主键约束【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左连接](https://img.haomeiwen.com/i13621350/013b6da5cac8e4ec.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

语法:
  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右连接](https://img.haomeiwen.com/i13621350/5862b64948953725.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)


```
语法:
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-student

表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()