数据库设计

一、定义表的约束
    一】表的完整性
        1》实体完整性：每条记录有一个唯一标识符，通常用无任何业务含义的字段表示
        2》参照完整性：一张表的某个字段必须引用另一张表的某个字段值
        3》域完整性：域即单元数据，域中的数值必须符合一定的规则
    二】键的概念
        1》主键：唯一的字段
        2》组合主键：由多个字段组合起来，形成唯一字段
        3》外键：针对多张表之间的关联，引用外来字段的本地字段叫做外键。
        
    三】主键
        语法：
            1》定义主键约束
                primary key:不允许为空，不允许重复
                
                code:
                1）单个主键

  　　　　　　　　create table teacher(
 　　                    id int primary key,
                        name varchar(20),
                        birthday date
                    );
                    

                2）组合主键

　　　　　　　　　　 create table if not exists middle(
                        sid int,
                        tid int,
                        constraint sid_FK foreign key(sid) references student(id),
                        constraint tid_FK foreign key(tid) references teacher(id),
                        primary key(sid,tid)            //设置组合主键
                    );

                特点：
                1》主键不能重复
                2》主键不能为空
                3》主键在表中只有一个，要么1列，要么多列
            
            2》删除主键
                alter table tablename drop primary key；
            
            3》定义主键自动增长(MySQL特有)
                auto_increment:MySQL特有/UUID类生成
                注意：
                    1》默认从1开始，该ID值与表同生亡。删除完记录并不影响序号。
                    2》多人项目中通常由UUID类生成唯一的主键值，便于多个表合并时依然保持实体完整性。
                    
                code:

 　　　　　　　　create table teacher(
                    id int primary key auto_increment,
                        name varchar(20),
                        birthday date
                    );

                   
            4》定义唯一约束
                关键字：
                    unique:非NULL值外，不能重复。
                code:

 　　　　　　　　create table teacher(
                    id int primary key auto_increment,
                        name varchar(20) unique,//唯一
                        birthday date
                    );

                注意：
                    1》非NULL值外，不能重复
                    2》可以插入多个NULL。
                    3》'null'空串和null是不同的。
                    
            5》定义非空约束
                    关键字：
                      not null：不能插入null值。
                  code：   

  　　　　　 　　　　　create table teacher(
                            id int primary key auto_increment,
                                name varchar(20) not null,//非空
                                birthday date
                            );

                    注意：
                        主键约束=非null约束+唯一约束。
                    
            6》定义外键约束
                关键字：
                    constraint pid_FK foreign key(pid) references Person(id)
                    
                    pid_FK是给数据库的系统看的！！一般格式就是如此：外键名_FK
                    
                详见图！！！
二、表的关联设计
            一】一对一(人和身份证)

　　　　　　　　　　
                        创建：先创建被引用的表，再创建引用的表。
                        删除：先删除引用的表，再删除被引用的表。
                        1》(方案一)单独用一个字段来表示外键：         

 　　　　　　　　　　　　create table card(
                                     id int primary key auto_increment,
                                     location varchar(20) not null,
                                     pid int,
                                     constraint pid_FK foreign key(pid) references person(id)
                                    );

                        2》(方案二)用主键来表示外键:此时，id既是主键，又是外键。               

　　　　　　　　　　　　 create table card(
                                    id int primary key auto_increment,
                                    location varchar(20) not null,
                                    constraint id_FK foreign key(id) reference person(id)
                                );

            二】一对多/多对一(部门和员工)
                        外键放置在多方。

　　　　　　　　　　　　
                    code：

 　　　　　　　　　　 create table department(
                         id int primary key auto_increment,
                         name varchar(20) not null
                        );
                        insert into department(name) values('软件部');
                        insert into department(name) values('销售部');
                        
                        create table employee(
                         id int primary key auto_increment,
                         name varchar(20) not null,
                         did int,
                         constraint did_FK foreign key(did) references department(id)
                        );
                        insert into employee(name,did) values('jack',1);
                        insert into employee(name,did) values('marry',1);

                        
                    问题：查询"软件部"的所有员工（组合式）
                                select d.name as 部门名,e.name as 员工名
                                from department as d,employee as e
                                where d.name = '软件部'  and e.did=d.id;
                                
                                思考：还有没有其它方法？
                                
                                分解：
                                (1)select id from department where name='软件部';
                                (2)select name from employee where did = 1;
                                (总)嵌入式SQL
                                
                                  select name as 员工
                                  from employee
                                  where did = (
                                    select id
                                    from department
                                    where name='软件部'
                                  );
                                            
            三】多对多(老师和同学)    
                外键放置在关联表中，即：单独创建一个关系表。
                即将一个多对多拆分成两个一对多关系。
                
                code:

                    drop table if exists middle;
                    drop table if exists student;
                    drop table if exists teacher;
                    
                    create table if not exists student(
                     id int primary key auto_increment,
                     name varchar(20) not null
                    );
                    insert into student(name) values('jack');
                    insert into student(name) values('marry');
                    
                    create table if not exists teacher(
                     id int primary key auto_increment,
                     name varchar(20) not null
                    );
                    insert into teacher(name) values('赵');
                    insert into teacher(name) values('蔡');
                    
                    create table if not exists middle(
                     sid int,
                     tid int,
                     constraint sid_FK foreign key(sid) references student(id),
                     constraint tid_FK foreign key(tid) references teacher(id),
                     primary key(sid,tid)//组合主键
                    );
                    insert into middle(sid,tid) values(1,1);
                    insert into middle(sid,tid) values(1,2);
                    insert into middle(sid,tid) values(2,1);
                    insert into middle(sid,tid) values(2,2);

                    
                    问题？查询"赵"所教过的所有学员
                    select t.name as 老师, s.name as 学员
                    from teacher as t,student as s,middle as m
                    where t.name = '赵'and m.sid=s.id and m.tid=t.id;
                注意：
                    1）外键的值必须来源于所引用的表的任一主键值,或者为NULL。
        
            【重点：】
                1》组合查询的模式:
                    select 列出需要显示的字段
                    from    列出所设计到的所有表，建议写别名
                    while 业务条件 表关联条件
                2》组合主键
                    primary key(column1,column2..)