一、练习
创建用户表
create table user(id int not null unique auto_increment,
-> username varchar(20) not null,
-> password varchar(20) not null,
-> primary key(username,password)
-> );
insert into user values(null,'root','123'),
-> (null,"egon","456"),
-> (null,"alex","alex3714");
select *from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | root | 123 |
| 2 | egon | 456 |
| 3 | alex | alex3714 |
+----+----------+----------+
用户组表
create table usergroup(id int primary key auto_increment,
-> groupname varchar(20) not null unique);
insert into usergroup(groupname) values("IT"),
-> ("Sale"),
-> ("Finance"),
-> ("Boss");
Query OK, 4 rows affected (1.83 sec)
Records: 4 Duplicates: 0 Warnings: 0
select *from usergroup;
+----+-----------+
| id | groupname |
+----+-----------+
| 4 | Boss |
| 3 | Finance |
| 1 | IT |
| 2 | Sale |
+----+-----------+
主机表
create table host(id int primary key auto_increment,
-> ip char(15) not null unique default "127.0.0.1");
insert into host(ip) values("127.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");
select *from host;
+----+---------------+
| id | ip |
+----+---------------+
| 1 | 127.16.45.2 |
| 5 | 172.10.45.3 |
| 6 | 172.10.45.4 |
| 7 | 172.10.45.5 |
| 2 | 172.16.31.10 |
| 4 | 172.16.31.11 |
| 3 | 172.16.45.3 |
| 8 | 192.168.1.20 |
| 9 | 192.168.1.21 |
| 10 | 192.168.1.22 |
| 13 | 192.168.2,24 |
| 12 | 192.168.2.223 |
| 11 | 192.168.2.23 |
| 14 | 192.168.3.22 |
| 15 | 192.168.3.23 |
| 16 | 192.168.3.24 |
+----+---------------+
业务线表
create table business(id int primary key auto_increment,
-> business varchar(20) not null unique);
insert into business(business) values("轻松贷"),("随便花"),("大富翁"),("穷一生");
select *from business; ,"穷一生");生");
+----+-----------+
| id | business |
+----+-----------+
| 3 | 大富翁 |
| 4 | 穷一生 |
| 1 | 轻松贷 |
| 2 | 随便花 |
+----+-----------+
建关系：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)
-> );
insert into user2usergroup(user_id,group_id) values(1,1),
-> (1,2),
-> (1,3),
-> (1,4),
-> (2,3),
-> (2,4),
-> (3,4);
select *from user2usergroup;
+----+---------+----------+
| id | user_id | group_id |
+----+---------+----------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 2 | 3 |
| 6 | 2 | 4 |
| 7 | 3 | 4 |
+----+---------+----------+
建关系：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)
-> );
insert into host2business(host_id,business_id) values
-> (1,1),
-> (1,2),
-> (1,3),
-> (2,2),
-> (2,3),
-> (3,4);
select *from host2business;
+----+---------+-------------+
| id | host_id | business_id |
+----+---------+-------------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 2 |
| 5 | 2 | 3 |
| 6 | 3 | 4 |
+----+---------+-------------+
建关系：uesr与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));
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);
select * from user2host;
+----+---------+---------+
| id | user_id | host_id |
+----+---------+---------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 1 | 5 |
| 6 | 1 | 6 |
| 7 | 1 | 7 |
| 8 | 1 | 8 |
| 9 | 1 | 9 |
| 10 | 1 | 10 |
| 11 | 1 | 11 |
| 12 | 1 | 12 |
| 13 | 1 | 13 |
| 14 | 1 | 14 |
| 15 | 1 | 15 |
| 16 | 1 | 16 |
| 17 | 2 | 2 |
| 18 | 2 | 3 |
| 19 | 2 | 4 |
| 20 | 2 | 5 |
| 21 | 3 | 10 |
| 22 | 3 | 11 |
| 23 | 3 | 12 |
+----+---------+---------+
二、作业
按照如下表的设计写出SQL语句
1. 建班级表
create table class(
cid int primary key auto_increment,
caption varchar(20) not null
);
insert into class(caption) values("三年级二班"),("一年级三班"),("三年级一班");
select *from class;
+-----+-----------------+
| cid | caption |
+-----+-----------------+
| 1 | 三年级二班 |
| 2 | 一年级三班 |
| 3 | 三年级一班 |
+-----+-----------------+
2. 建学生表
create table student(
sid int primary key auto_increment,
sname char(10) not null,
gender enum('男','女'),
class_id int not null,
foreign key(class_id) references class(cid)
);
insert into student(sname,gender,class_id) values
("钢蛋","女",1),
("铁锤","女",1),
("山炮","男",2);
select *from student;
+-----+--------+--------+----------+
| sid | sname | gender | class_id |
+-----+--------+--------+----------+
| 1 | 钢蛋 | 女 | 1 |
| 2 | 铁锤 | 女 | 1 |
| 3 | 山炮 | 男 | 2 |
+-----+--------+--------+----------+
3. 建老师表
create table teacher(
tid int primary key auto_increment,
tname varchar(20) not null
);
insert into teacher(tname) values("egon"),("nick"),("jerry");
select *from teacher;
+-----+-------+
| tid | tname |
+-----+-------+
| 1 | egon |
| 2 | nick |
| 3 | jerry |
+-----+-------+
4. 建课程表
create table course(
cid int primary key auto_increment,
cname char(20) not null,
teacher_id int not null,
foreign key(teacher_id) references teacher(tid)
);
insert into course(cname,teacher_id) values
("生物",1),
("体育",1),
("物理",2);
select * from course;
+-----+--------+------------+
| cid | cname | teacher_id |
+-----+--------+------------+
| 1 | 生物 | 1 |
| 2 | 体育 | 1 |
| 3 | 物理 | 2 |
+-----+--------+------------+
5. 建成绩表
create table score(
sid int not null unique auto_increment,
student_id int not null,
course_id int not null,
score int not null,
primary key(student_id,course_id),
foreign key(student_id) references student(sid),
foreign key(course_id) references course(cid)
);
insert into score(student_id,course_id,score) values
(1,1,60),
(1,2,59),
(2,2,100);
select *from score;
+-----+------------+-----------+-------+
| sid | student_id | course_id | score |
+-----+------------+-----------+-------+
| 1 | 1 | 1 | 60 |
| 2 | 1 | 2 | 59 |
| 3 | 2 | 2 | 100 |
+-----+------------+-----------+-------+
6. 查询成绩
select score from score where student_id = ( ;
select sid from student where sname="钢蛋")
and course_id =(
select cid from course where cname = "生物");
+-------+
| score |
+-------+
| 60 |
+-------+