• 5.5作业


    练习:账号信息表,用户组,主机表,主机组

    #用户表
    create table user(
    id int not null unique auto_increment,
    username varchar(20) not null,
    password varchar(50) not null,
    primary key(username,password)
    );
    
    #用户组表
    create table usergroup(
    id int primary key auto_increment,
    groupname varchar(20) not null unique
    );
    
    #主机表
    create table host(
    id int primary key auto_increment,
    ip char(15) not null unique default '127.0.0.1'
    );
    
    #业务线表
    create table business(
    id int primary key auto_increment,
    business varchar(20) not null unique
    );
    
    #建关系:user与usergroup
    
    create table user2usergroup(
    id int not null unique auto_increment,
    user_id int not null,
    group_id int not null,
    primary key(user_id,group_id),
    foreign key(user_id) references user(id),
    foreign key(group_id) references usergroup(id)
    );
    
    #建关系:host与business
    create table host2business(
    id int not null unique auto_increment,
    host_id int not null,
    business_id int not null,
    primary key(host_id,business_id),
    foreign key(host_id) references host(id),
    foreign key(business_id) references business(id)
    );
    
    #建关系:user与host
    create table user2host(
    id int not null unique auto_increment,
    user_id int not null,
    host_id int not null,
    primary key(user_id,host_id),
    foreign key(user_id) references user(id),
    foreign key(host_id) references host(id)
    );
    

    练习:

    # 班级表
    cid	caption
    create table class(
    cid int primary key auto_increment,
    caption char(16) not null
    );
    # 学生表
    sid sname gender class_id
    create table class(
    sid int primary key auto_increment,
    sname char(16) not null,
    gender enum('male','female','others'),
    class_id  int ,
    foreign key(class_id) references class(cid)
    on update cascade    
    on delete cascade    
    );
    
    # 老师表
    tid	tname
    create table teacher(
    tid int primary key auto_increment,
    tname char(16) not null   
    );
    # 课程表
    cid	cname	teacher_id
    create table course(
    cid int primary key auto_increment,
    cname char(16) not null,  
    teacher_id int ,
    foreign key(teacher_id) references teacher(tid) 
    on update cascade    
    on delete cascade      
    );
    
    # 成绩表
    sid	student_id course_id number
    create table score(
    sid int primary key auto_increment,
    student_id int,
    course_id int,
    number int,    
    foreign key(student_id)  references student(sid)  
    on update cascade
    on delete cascade,
    foreign key(course_id)  references course(cid)
    on update cascade
    on delete cascade    
    )
    
  • 相关阅读:
    centos 用户指定目录访问
    centos FTP 用户指定目录禁用上级目录
    centos下SVN搭建多个库文件总汇
    listview点击checkbox,修改值
    C#转成时间格式
    nmap 查看主机上开放的端口
    xargs、管道、exec区别
    OSI七层模型,作用及其对应的协议
    linux面试题(重点)
    数据库备份还原 mysqldump
  • 原文地址:https://www.cnblogs.com/chenyoupan/p/12833003.html
Copyright © 2020-2023  润新知