学生表student 、成绩表test:
- 建表操作
student表
CREATE TABLE student
(
sno INT PRIMARY KEY,
sname CHAR(20) NOT NULL,
ssex CHAR(1) CHECK(ssex='男' OR ssex='女'),
specialty CHAR(20) NOT NULL,
course CHAR(20) NOT NULL
)
test表
CREATE TABLE test
(
tno INT PRIMARY KEY,
tname CHAR(20) NOT NULL,
tcourse CHAR(20) NOT NULL,
score INT NOT NULL,
FOREIGN KEY(tno) REFERENCES student(sno) //成绩表座位号和学生表座位号一样的,所以设置成外键
)
- 插入数据
(1)批量插入学生数据
INSERT INTO student (sno,sname,ssex,specialty,course)
VALUES
(1,'李四','男','计算机科学','计算机网络'),
(2,'张三','男','计算机科学','计算机网络'),
(3,'张晶晶','女','计算机科学','计算机网络'),
(4,'李丽丽','女','计算机科学','计算机网络')
(2)批量插入学生成绩数据
INSERT INTO test (tno,tname,tcourse,score)
VALUES
(1,'李四','计算机网络',80),
(2,'张三','计算机网络',79),
(3,'张晶晶','计算机网络',90),
(4,'李丽丽','计算机网络',86)
- 连接两个表,查找所有信息
(1)不选择特定的字段==全部字段
SELECT * FROM student INNER JOIN test ON student.sno=test.tno
(2)选择特定字段进行查找
SELECT student.sno,student.sname,specialty,course,score FROM student
INNER JOIN test ON student.sno=test.tno
(3)按成绩升序进行查找
SELECT student.sno,student.sname,specialty,course,score FROM student
INNER JOIN test ON student.sno=test.tno
ORDER BY score ASC
(4)查看所有学生计算机网络学科的总分
SELECT SUM(score) AS 总分,tcourse AS 学科 FROM test
GROUP BY tcourse
(5)查看计算机网络学科的平均分
SELECT AVG(score)AS 平均分,tcourse AS 学科 FROM test
GROUP BY tcourse
- 修改student表为:
(1)查找年龄大于20岁的学生,且统计男女人数各为多少
SELECT COUNT(sno) AS 人数,ssex AS 性别 FROM student
WHERE sage>20
GROUP BY ssex
- 修改student表为:
(1)查找专业中人数大于2的专业及人数
SELECT COUNT(sno) AS 人数,specialty AS 学科 FROM student
GROUP BY specialty
HAVING COUNT(sno) > 2
“Where”在结果返回之前起作用,where后面不能使用“聚合函数”
“Having”在结果返回之后起作用,并且having后面可以使用“聚合函数”
having需要分组,where不需要分组
having是要配合group by 使用,在分组之后过滤数据,having一般跟在group by后