MySQL典型示例

1.使用环境

2.设计表

3.创建表

4.准备数据

5.查询

1.使用环境

数据库：MySQL 8.0.30

客户端：Navicat 15.0.12

2.设计表

假设我们已经建好了一个名为test的数据库。我们添加如下几个表：教师、课程、学生、班级、成绩。实体联系图设计如下：

3.创建表

CREATE TABLE class (
  cid int(10) PRIMARY KEY AUTO_INCREMENT,
  cname varchar(50) NOT NULL
);

> OK
> 时间: 0.006s

CREATE TABLE teacher(
  tid int(10) PRIMARY KEY AUTO_INCREMENT,
  tname varchar(50) NOT NULL
);

> OK
> 时间: 0.006s

CREATE TABLE course(
  cid int(11) PRIMARY KEY AUTO_INCREMENT,
  cname varchar(32) NOT NULL,
  teacher_id int(11) NOT NULL,
  FOREIGN KEY (teacher_id) REFERENCES teacher (tid)
) ;

> OK
> 时间: 0.011s

CREATE TABLE student(
  sid int(10) PRIMARY KEY AUTO_INCREMENT,
	sname varchar(50) NOT NULL,
  gender char(1) NOT NULL,
  c_id int(10) NOT NULL,
  FOREIGN KEY (c_id) REFERENCES class (cid)
);

> OK
> 时间: 0.009s

CREATE TABLE score (
  sid int(10) PRIMARY KEY AUTO_INCREMENT,
  c_id int(10) NOT NULL,
  s_id int(10) NOT NULL,
  snum int(10) NOT NULL,
  FOREIGN KEY (c_id) REFERENCES course(cid),
  FOREIGN KEY (s_id) REFERENCES student(sid)
);

> OK
> 时间: 0.012s

通过以上的语句，就分别把表创建好了。

4.准备数据

创建表以后，只是有了结构，还没有数据，所以我们要准备一些测试数据。

INSERT INTO class VALUES
(1,'一年一班'), 
(2,'二年一班'), 
(3,'三年一班'), 
(4,'三年二班'),
(5,'三年三班');

> Affected rows: 5
> 时间: 0.001s

INSERT INTO teacher VALUES
(1, '张三老师'), 
(2, '李四老师'), 
(3, '王五老师'), 
(4, '赵六老师'), 
(5, '刘七老师');

> Affected rows: 5
> 时间: 0.001s

INSERT INTO course VALUES
(1, '语文', 1), 
(2, '物理', 2), 
(3, '英语', 3), 
(4, '化学', 2);

> Affected rows: 4
> 时间: 0.002s

INSERT INTO student VALUES
(1, '张一一', '男', 1), 
(2, '张一二', '女', 1), 
(3, '张一三', '男', 1), 
(4, '王一一', '男', 1), 
(5, '王一二', '女', 1), 
(6, '王一三', '男', 1), 
(7, '李一一', '女', 2), 
(8, '李一二', '男', 2), 
(9, '李一三', '男', 2), 
(10, '李一四', '女', 2), 
(11, '赵一', '男', 2), 
(12, '赵二', '女', 3), 
(13, '赵三', '男', 3), 
(14, '刘一一', '男', 3), 
(15, '刘一二', '女', 3), 
(16, '刘一三', '男', 3);

> Affected rows: 16
> 时间: 0.001s

INSERT INTO score VALUES
(1, 1, 1, 70),
(2, 2, 1, 90),
(3, 3, 1, 87),
(4, 4, 1, 69),
(5, 1, 2, 66),
(6, 2, 2, 98),
(8, 3, 2, 68),
(9, 4, 2, 99),
(10, 1, 3, 77),
(11, 2, 3, 66),
(12, 3, 3, 87),
(13, 4, 3, 99),
(14, 1, 4, 79),
(15, 2, 4, 11),
(16, 3, 4, 67),
(17, 4, 4, 100),
(18, 1, 5, 79),
(19, 2, 5, 11),
(20, 3, 5, 67),
(21, 4, 5, 100),
(22, 1, 6, 29),
(23, 2, 6, 100),
(24, 3, 6, 67),
(25, 4, 6, 100),
(26, 1, 7, 91),
(27, 2, 7, 100),
(28, 3, 7, 67),
(29, 4, 7, 88),
(30, 1, 8, 69),
(31, 2, 8, 100),
(32, 3, 8, 67),
(33, 4, 8, 88),
(34, 1, 9, 91),
(35, 2, 9, 88),
(36, 3, 9, 67),
(37, 4, 9, 62),
(38, 1, 10, 90),
(39, 2, 10, 77),
(40, 3, 10, 43),
(41, 4, 10, 87),
(42, 1, 11, 90),
(43, 2, 11, 77),
(44, 3, 11, 43),
(45, 4, 11, 87),
(46, 1, 12, 90),
(47, 2, 12, 77),
(48, 3, 12, 43),
(49, 4, 12, 97),
(50, 1, 13, 63),
(51, 2, 13, 98),
(52, 3, 13, 65),
(53, 4, 13, 79);

> Affected rows: 52
> 时间: 0.003s

5.查询

查询所有课程的名称以及对应的任课老师姓名：

select c.cname,t.tname from course as c left join teacher as t on c.t_id = t.tid;

查询男女学生各有多少人：

select gender,count(1) as count from student group by gender;

查询姓李老师的数量：

select count(1) as count from teacher where tname like '李%';

查询物理成绩等于100的学生姓名：

select t.sname from student as t inner join score as s on s.s_id = t.sid
inner join course as c on c.cid = s.c_id where s.snum = 100 and c.cname = '物理';

查询平均成绩大于八十分的同学姓名和平均成绩：

select t.sname,avg(snum) as savg from student as t inner join score as s on s.s_id = t.sid group by t.sid having avg(snum) > 80;

查询报了物理也报了化学这两门课程的学生学号和对应科目的分数：

select * from
(select s_id,snum as wulisum  
from score as s inner join course as c on s.c_id = c.cid
where cname = '物理') as wuli inner join
(select s_id,snum as huaxuesum from score as s inner join course as c on s.c_id = c.cid
where cname = '化学') as huaxue on wuli.s_id = huaxue.s_id;

查询物理成绩不及格的学生姓名和对应分数：

select t.sname,snum from student as t inner join score as s on s.s_id = t.sid 
inner join course as c on c.cid = s.c_id where c.cname = '物理' and snum < 60;