day39 python 学习数据库学习五个约束，数据库设计（一对一，一对多等等）

2023-08-21 08:10:34

删除重复数据： 注意数据库不能又查又删　

*******#删除作者为重复数据并保留ID最大的是数据

delete from ren where author in (select * from(select  author from ren GROUP BY author having count(author)>1) as a)

#先按分组找出作者重复数量大于1的作者放到a中，然后再删除里边的内容并且符合下边要求的内容

and id not in (select * from(select max(id) from ren GROUP BY author having count(author)>1) as b)

MySQL 之【约束】【数据库设计】

1.MySQL 约束:

　1.约束的概念:

　　　约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。

MySQL中，常用的几种约束：

约束类型: 非空主键唯一外键 默认值

关键字：
NOT NULL
PRIMARY KEY UNIQUE FOREIGN KEY DEFAULT

1.非空约束(NOT NULL),听名字就能理解，被非空约束的列，在插入值时必须非空。　
create table t1(

       id int(10) not null primary key

 );
2.主键(PRIMARY KEY)是用于约束表中的一行，作为这一行的标识符，在一张表中通过主键就能准确定位到一行，因此主键十分重要。

通过主键查找的速度比通过其他键查找速度要快的多

　　注意: 主键这一行的数据不能重复且不能为空。

注意在不指定主键的时候，使用innodb存储引擎时，会指定一个不为空且唯一的字段为主键，当也没有不为空且唯一的字段时，innodb会造一个字段成为主键
create table t2(

    id int(10) not null primary key

);    
还有一种特殊的主键——复合主键。主键不仅可以是表中的一列，也可以由表中的两列或多列来共同标识
create table t3(

    id int(10) not null,

    name varchar(255) ,

    primary key(id,name)

);

//alter删除主键约束

alter table t3 drop primary key;

//alter添加主键约束

alter table t3 add primary key(name, pwd);

//alter 修改列为主键

alter table t3 modify id int primary key;
设置完双主键的效果

设置双主键保证两个两个键组合的形式不重复就不会出问题

3.唯一约束(UNIQUE)比较简单，它规定一张表中指定的一列的值必须不能有重复值，即这一列每个值都是唯一的。

　　当INSERT语句新插入的数据和已有数据重复的时候，如果有UNIQUE约束，则INSERT失败.
create table t3( id int not null primary KEY ,

            name varchar(4) not null,

               UNIQUE id_name(id,name))   

//添加唯一约束

alter table t4 add unique id_name(id,name);

//删除唯一约束

alter table t4 drop index id_name;
        
4,*****外键(FOREIGN KEY)既能确保数据完整性，也能表现表之间的关系。

　　注意: 一个表可以有多个外键，被外键约束的列，取值必须在它参考的列中有对应值

1、cascade一般用在级联保存，级联更新，级联删除上。
-- 部门表

create table dept(

    dept_id int(30) not null auto_increment primary key,

    dept_name varchar(255) not null

);

-- 员工表

create table emp(

    id int(10) not null auto_increment primary key,

    name varchar(50) not null,

    dept_id int(10) not null,

    constraint f_key foreign key (dept_id) references dept(dept_id) on delete cascade

);
关联的数就直接能联想到被关联的表中的数据，不能写里边不存在的值

名：这个外键的名字可以随意命名，

栏位：是本表那个栏位要关联外键

参考数据库，参考表，参考栏位：都是要参考的表的信息

删除时：

RESTRICT :限制，约束已经被关联后的内容，的表中的内容不可以删除

cascade 级联当删除时，被关联的数据相关内容也会被删除(千万不要用）

set null 当被关联的键内容被删除时，关联表的数据会显示null

no acrtion
5.默认值约束(DEFAULT)规定，当有DEFAULT约束的列，插入数据为空时该怎么办。

　　 DEFAULT约束只会在使用INSERT语句时体现出来，INSERT语句中，如果被DEFAULT约束的位置没有值，那么这个位置将会被DEFAULT的值填充
create table t5(

    id int(10) not null primary key,

    name varchar(255) default '张三'

);
2.数据库设计

1.概念

　　简单来说，数据库设计就是对需求进行分析、逻辑设计、物理设计以及维护和优化的过程。可以看到，数据库设计不仅仅体现在软件开发过程中，还体现在软件后期的维护上。（时间周期）
这里的软件需求分析与软件开发过程中的需求分析不太一样，数据库设计中的需求分析更侧重数据源（什么数据）、数据的属性以及数据和属性的特点。

总结:

　　1.有效存储数据.

　　2.满足用户的多种需求

2.规则:

　　数据库设计三范式:
　　　　1.第一范式:确保每列保持原子性
　　　　2.第二范式:确保表中的每列都和主键相关
　　　　3.第三范式:确保表中每一列都和主键列直接相关,而不是间接相关.

　　　　详情参考http://www.cnblogs.com/wangfengming/p/7929118.html
1.一对一关系
CREATE TABLE IF NOT EXISTS person(

    id INT PRIMARY KEY AUTO_INCREMENT,

    sname VARCHAR(10),

    sex CHAR(1),

    husband INT,

    wife INT

);

INSERT INTO person VALUES(1,'小花','0',3,0);

INSERT INTO person VALUES(2,'小明','1',0,4);

INSERT INTO person VALUES(3,'张三','1',0,1);

INSERT INTO person VALUES(4,'小丽','0',2,0);

INSERT INTO person VALUES(5,'王五','1',0,0);
sql代码对应的person表:

从表中可以看出，小花和张三是夫妻，小明和小丽是夫妻。

通常为了查询方便，需要两个表，但实际项目中为了省空间，通常只建一个表.

　2、一对多关系

　　主表不动，从表中添加主表的键（大多是主键）

　　例如:一个人可以拥有多辆汽车，要求查询某个人拥有的所有车辆。
　　分析：这种情况其实也可以采用一张表，但因为一个人可以拥有多辆汽车，如果采用一张表，会造成冗余信息过多。好的设计方式是，人和车辆分别单独建表，那么如何将两个表关联呢？有个巧妙的方法，在车辆的表中加个外键字段(人的编号)即可。
　　* (思路小结：’建两个表，一’方不动，’多’方添加一个外键字段)*
 //建立人员表

CREATE TABLE people(

    id VARCHAR(12) PRIMARY KEY,

    sname VARCHAR(12),

    age INT,

    sex CHAR(1)

);

INSERT INTO people VALUES('H001','小王',27,'1');

INSERT INTO people VALUES('H002','小明',24,'1');

INSERT INTO people VALUES('H003','张慧',28,'0');

INSERT INTO people VALUES('H004','李小燕',35,'0');

INSERT INTO people VALUES('H005','王大拿',29,'1');

INSERT INTO people VALUES('H006','周强',36,'1');

 //建立车辆信息表

CREATE TABLE car(

    id VARCHAR(12) PRIMARY KEY,

    mark VARCHAR(24),

    price NUMERIC(6,2),

    hid VARCHAR(12),

    CONSTRAINT fk_people FOREIGN KEY(hid) REFERENCES human(id)

);

INSERT INTO car VALUES('C001','BMW',65.99,'H001');

INSERT INTO car VALUES('C002','BenZ',75.99,'H002');

INSERT INTO car VALUES('C003','Skoda',23.99,'H001');

INSERT INTO car VALUES('C004','Peugeot',20.99,'H003');

INSERT INTO car VALUES('C005','Porsche',295.99,'H004');

INSERT INTO car VALUES('C006','Honda',24.99,'H005');

INSERT INTO car VALUES('C007','Toyota',27.99,'H006');

INSERT INTO car VALUES('C008','Kia',18.99,'H002');

INSERT INTO car VALUES('C009','Bentley',309.99,'H005');
sql代码对应的人员表:

sql代码对应的车辆信息表:

　3.多对多关系　

　　两张主表结果不变，利用第三张表表示两张主表的关系

　　例如：学生选课，一个学生可以选修多门课程，每门课程可供多个学生选择。
　　分析：这种方式可以按照类似一对多方式建表，但冗余信息太多，好的方式是实体和关系分离并单独建表，实体表为学生表和课程表，关系表为选修表，其中关系表采用联合主键的方式(由学生表主键和课程表主键组成)建表。
  //建立学生表

CREATE TABLE student(

    id VARCHAR(10) PRIMARY KEY,

    sname VARCHAR(12),

    age INT,

    sex CHAR(1),

    class VARCHAR(6)

);

INSERT INTO student VALUES('p0001','王军',20,1,'c101');

INSERT INTO student VALUES('p0002','张宇',21,1,'c101');

INSERT INTO student VALUES('p0003','刘飞',22,1,'c102');

INSERT INTO student VALUES('p0004','赵燕',18,0,'c103');

INSERT INTO student VALUES('p0005','曾婷',19,0,'c103');

INSERT INTO student VALUES('p0006','周慧',21,0,'c104');

INSERT INTO student VALUES('p0007','小红',23,0,'c104');

INSERT INTO student VALUES('p0008','杨晓',18,0,'c104');

INSERT INTO student VALUES('p0009','李杰',20,1,'c105');

INSERT INTO student VALUES('p0010','张良',22,1,'c105');

    //建立课程表

CREATE TABLE course(

    id VARCHAR(10) PRIMARY KEY,

    sname VARCHAR(12),

    credit NUMERIC(2,1),

    teacher VARCHAR(12)

);

INSERT INTO course VALUES('C001','Java',3.5,'李老师');

INSERT INTO course VALUES('C002','高等数学',5.0,'赵老师');

INSERT INTO course VALUES('C003','JavaScript',3.5,'王老师');

INSERT INTO course VALUES('C004','离散数学',3.5,'卜老师');

INSERT INTO course VALUES('C005','数据库',3.5,'廖老师');

INSERT INTO course VALUES('C006','操作系统',3.5,'张老师');

    //建立选修表

CREATE TABLE sc(

    sid VARCHAR(10),

    cid VARCHAR(10)

);

ALTER TABLE sc ADD CONSTRAINT pk_sc PRIMARY KEY(sid,cid);

ALTER TABLE sc ADD CONSTRAINT fk_student FOREIGN KEY(sid) REFERENCES student(id);

ALTER TABLE sc ADD CONSTRAINT fk_course FOREIGN KEY(cid) REFERENCES course(id);

INSERT INTO sc VALUES('S0001','C001');

INSERT INTO sc VALUES('S0001','C002');

INSERT INTO sc VALUES('S0001','C003');

INSERT INTO sc VALUES('S0002','C001');

INSERT INTO sc VALUES('S0002','C004');

INSERT INTO sc VALUES('S0003','C002');

INSERT INTO sc VALUES('S0003','C005');

INSERT INTO sc VALUES('S0004','C003');

INSERT INTO sc VALUES('S0005','C001');

INSERT INTO sc VALUES('S0006','C004');

INSERT INTO sc VALUES('S0007','C002');

INSERT INTO sc VALUES('S0008','C003');

INSERT INTO sc VALUES('S0009','C001');

INSERT INTO sc VALUES('S0009','C005');
sql代码对应的学生表：

sql代码对应的课程表：

sql代码对应的选课表：

1. 1-1 特殊的一对多，通过在字段上添加唯一约束产生一对一关系
2. 1-n 主表不动。从表中添加主表的主键
3. n-n 两张主表结构不变，然后利用第三张表表示两张主表的关系
id name 。。。 id name 。。。
1 华联 1 老王
2 加利福 2 小王

超市顾客关系表
id c_id u_id
1 1 1
2 2 1

3. 索引：
1.概念：相当于书的目录，快速找到数据

好处：可以帮助你提高查询效率，数据量越大越明显
缺点: 新增和删除数据时，效率较低

2.索引方法：
1.hash 是以key-value 的形式进行索引存储
2.BTree 是以二叉树方式进行索引存储。（默认存储索引类型）

3.索引分类
1. 普通索引 create INDEX name_index on person(name);
2. 唯一索引 create unique INDEX name_age on person(name,age);
3. 主键索引 alter table person MODIFY id int PRIMARY key;
4. 组合索引 create unique INDEX name_age on person(name,age);
5. 全文索引 full text ：原理是分词查找