mysql 07.13

1.用户表
mysql> create database day0713;
Query OK, 1 row affected (0.00 sec)

mysql> use day0713
Database changed

mysql> create table user(
    -> id int null unique auto_increment,
    -> username varchar(20) not null,
    -> password varchar(50) not null,
    -> primary key(username,password));
Query OK, 0 rows affected (0.52 sec)

mysql> insert into user(username,password)values
    -> ('root','123'),
    -> ('egon','123'),
    -> ('alex','alex3714');
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

2.用户组表
mysql> create table usergroup(
    -> id int primary key auto_increment,
    -> groupname varchar(20) not null unique);
Query OK, 0 rows affected (0.28 sec)

mysql> insert into usergroup(groupname) values
    -> ('IT'),
    -> ('Sale'),
    -> ('Finance'),
    -> ('boss')
    -> ;
Query OK, 4 rows affected (0.09 sec)
Records: 4  Duplicates: 0  Warnings: 0

3.主机表
mysql> create table host(
    -> id int primary key auto_increment,
    -> ip char(15) not null unique default '127.0.0.1');
Query OK, 0 rows affected (0.23 sec)

mysql> insert into host(ip) values
    -> ('172.16.45.2'),
    -> ('172.16.31.10'),
    -> ('172.16.45.3'),
    -> ('172.16.31.11'),
    -> ('172.10.45.3'),
    -> ('172.10.45.4'),
    -> ('172.10.45.5'),
    -> ('192.168.1.20'),
    -> ('192.168.1.21'),
    -> ('192.168.1.22'),
    -> ('192.168.2.23'),
    -> ('192.168.2.223'),
    -> ('192.168.2.24'),
    -> ('192.168.3.22'),
    -> ('192.168.3.23'),
    -> ('192.168.3.24')
    -> ;
Query OK, 16 rows affected (0.03 sec)
Records: 16  Duplicates: 0  Warnings: 0

4.业务链表
mysql> create table business(
    -> id int primary key auto_increment,
    -> business varchar(20) not null unique);
Query OK, 0 rows affected (0.39 sec)

mysql> insert into business(business) values
    -> ('轻松贷'),
    -> ('随便花'),
    -> ('大富翁'),
    -> ('穷一生');
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

5.建关系user与usergroup
mysql> 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));
Query OK, 0 rows affected (0.61 sec)

mysql> insert into user2usergroup(user_id,group_id)values
    -> (1,1),
    -> (1,2),
    -> (1,3),
    -> (1,4),
    -> (2,3),
    -> (2,4),
    -> (3,4);
Query OK, 7 rows affected (0.10 sec)
Records: 7  Duplicates: 0  Warnings: 0

6.建关系 host与business
mysql> 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(business_id) references business(id));
Query OK, 0 rows affected (0.33 sec)

mysql> insert into host2business(host_id,business_id)values
    -> (1,1),
    -> (1,2),
    -> (1,3),
    -> (2,1),
    -> (2,3),
    -> (3,4);
Query OK, 6 rows affected (0.11 sec)
Records: 6  Duplicates: 0  Warnings: 0

7.建关系 user与host
mysql> 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));
Query OK, 0 rows affected (0.41 sec)

mysql> insert into user2host(user_id,host_id)values
    -> (1,1),
    -> (1,2),
    -> (1,3),
    -> (1,4),
    -> (1,5),
    -> (1,6),
    -> (1,7),
    -> (1,8),
    -> (1,9),
    -> (1,10),
    -> (1,11),
    -> (1,12),
    -> (1,13),
    -> (1,14),
    -> (1,15),
    -> (1,16),
    -> (2,2),
    -> (2,3),
    -> (2,4),
    -> (2,5),
    -> (3,10),
    -> (3,11),
    -> (3,12);
Query OK, 23 rows affected (0.19 sec)
Records: 23  Duplicates: 0  Warnings: 0

mysql> create database 0713_00;
Query OK, 1 row affected (0.00 sec)

mysql> use 0713_00;
Database changed

mysql> create table class(
    -> cid int primary key auto_increment,
    -> caption char(10) not null unique);
Query OK, 0 rows affected (0.42 sec)

mysql> insert into class(caption) values
    -> ('三年二班'),('一年三班'),('三年一班');
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table student(
    -> sid int primary key auto_increment,
    -> sname char(5) not null unique,
    -> gender enum('男','女') default '女',
    -> class_id int ,
    -> foreign key(class_id) references class(cid));
Query OK, 0 rows affected (0.47 sec)

mysql> create table student(
    -> sid int primary key auto_increment,
    -> sname char(5) not null unique,
    -> gender enum('男','女') default '女',
    -> class_id int ,
    -> foreign key(class_id) references class(cid));
Query OK, 0 rows affected (0.47 sec)

mysql> create table teacher(
    -> tid int primary key auto_increment,
    -> tname char(5) not null);
Query OK, 0 rows affected (0.38 sec)

mysql> insert into teacher(tname) values
    -> ('波多'),('苍空'),('饭岛');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table course(
    -> cid int primary key auto_increment,
    -> cname char(5) not null unique,
    -> teacher_id int,
    -> foreign key(teacher_id) references teacher(tid));
Query OK, 0 rows affected (0.59 sec)

mysql> insert into course(cname,teacher_id) values
    -> ('生物',1),
    -> ('体育',1),
    -> ('物理',2);
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table score(
    -> sid int primary key auto_increment,
    -> student_id int,
    -> course_id int,
    -> number int,
    -> foreign key(student_id) references student(sid),
    -> foreign key(course_id) references course(cid));
Query OK, 0 rows affected (0.49 sec)

mysql> insert into score(student_id,course_id,number) values
    -> (1,1,60),
    -> (1,2,59),
    -> (2,2,100);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0