作业
作业
创建表
##创建class表
mysql> show create table class ;
| Table | Create Table
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(30) DEFAULT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------
mysql>
##创建course 表
mysql> show create table course;
| course | CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(10) NOT NULL DEFAULT '',
`tearch_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `teacher_id` (`tearch_id`),
CONSTRAINT `teacher_id` FOREIGN KEY (`tearch_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
## 创建score 表
| Table | Create Table
| score | CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`corse_id` int(11) NOT NULL,
`number` int(11) NOT NULL,
PRIMARY KEY (`sid`),
KEY `student1_id` (`student_id`),
KEY `corse1_id` (`corse_id`),
CONSTRAINT `corse1_id` FOREIGN KEY (`corse_id`) REFERENCES `course` (`cid`),
CONSTRAINT `student1_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
## 创建student表
| Table | Create Table
| student | CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`sname` varchar(30) DEFAULT NULL,
`gender` enum('男','女') DEFAULT NULL,
`class_id` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`sid`),
KEY `student_class` (`class_id`),
CONSTRAINT `student_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------------------------------
1 row in set (0.00 sec)
## 创建teacher表
mysql> show create table teacher;
| Table | Create Table
| teacher | CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(30) NOT NULL DEFAULT '',
PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
- 查询所有大于60分的学生的姓名和学号 (DISTINCT: 去重)
mysql> select distinct * from score left join student on student_id = student.sid where number > 60 ;
+-----+------------+----------+--------+------+-------+--------+----------+
| sid | student_id | corse_id | number | sid | sname | gender | class_id |
+-----+------------+----------+--------+------+-------+--------+----------+
| 10 | 11 | 8 | 100 | 11 | 山 | 男 | 2 |
+-----+------------+----------+--------+------+-------+--------+----------+
1 row in set (0.00 sec)
mysql>
-- 2.查询每个老师教授的课程数量 和 老师信息
mysql> select tname,count(cname) from course left join teacher on tearch_id = teacher.tid group by tname ;
+-------+--------------+
| tname | count(cname) |
+-------+--------------+
| 波多 | 2 |
| 苍空 | 3 |
+-------+--------------+
2 rows in set (0.00 sec)
mysql> select * from teacher;
+-----+-------+
| tid | tname |
+-----+-------+
| 1 | 波多 |
| 2 | 苍空 |
| 3 | 饭岛 |
+-----+-------+
3 rows in set (0.00 sec)
mysql> select sid,sname,gender,caption from student left join class on class_id=class.cid;
+-----+-------+--------+------------+
| sid | sname | gender | caption |
+-----+-------+--------+------------+
| 7 | 钢蛋 | 女 | 三年级二班 |
| 9 | 铁锤 | 男 | 三年级二班 |
| 10 | 山炮 | 女 | 三年级二班 |
| 8 | 钢蛋 | 女 | 一年级三班 |
| 11 | 山 | 男 | 一年级三班 |
+-----+-------+--------+------------+
5 rows in set (0.00 sec)
