搜文章
推荐 原创 视频 Java开发 iOS开发 前端开发 JavaScript开发 Android开发 PHP开发 数据库 开发工具 Python开发 Kotlin开发 Ruby开发 .NET开发 服务器运维 开放平台 架构师 大数据 云计算 人工智能 开发语言 其它开发
Lambda在线 > 瓦尔登小柴胡 > 【练习3】mysql高级查询

【练习3】mysql高级查询

瓦尔登小柴胡 2020-02-20

是胡小柴

练习—mysql高级查询

一起来学习








一、数据准备

1、进入mysql

mysql -uroot -proot

2、查看所有数据库

show databases;

3、新建数据库,并设置其编码格式

create database twocats charset=utf8mb4;

4、进入数据库

 use twocats;

5、新建表students并添加数据

create table students (s_id int(4) unsigned primary key auto_increment not null,name varchar(10) not null,gender enum("",""),cls_id int unsigned not null);
insert into students values(1,"张三","",1);
insert into students values(2,"李四","",4);
insert into students values(3,"王五","",2);
insert into students values(4,"赵六","",3);
insert into students values(5,"田七","",5);
insert into students values(6,"江北","",10);
insert into students values(7,"齐八","",8);
insert into students values(8,"魏九","",9);
insert into students values(9,"老石","",7);
insert into students values(10,"钱电","",6);
insert into students values(11,"张三","",11);

6、新建表teacher并添加数据

CREATE TABLE teacher ( t_id INT UNSIGNED PRIMARY KEY auto_increment NOT NULL, t_name VARCHAR ( 10 ) NOT NULL );
INSERT INTO teacher
VALUES
( 1, "赵老师" );
INSERT INTO teacher
VALUES
( 2, "钱老师" );
INSERT INTO teacher
VALUES
( 3, "孙老师" );

7、创建课程表course并添加数据

CREATE TABLE course ( cid INT UNSIGNED PRIMARY KEY auto_increment NOT NULL, cname VARCHAR ( 10 ) NOT NULL, teacher_id INT UNSIGNED NOT NULL );
INSERT INTO course
VALUES
( 1, "生物", 1 );
INSERT INTO course
VALUES
( 2, "体育", 2 );
INSERT INTO course
VALUES
( 3, "物理", 3 );

8、创建成绩表score,并添加数据

CREATE TABLE score ( student_id INT UNSIGNED NOT NULL, course_id INT UNSIGNED NOT NULL, number INT UNSIGNED NOT NULL );
INSERT INTO score
VALUES
( 1, 1, 80 );
INSERT INTO score
VALUES
( 1, 2, 90 );
INSERT INTO score
VALUES
( 1, 3, 99 );
INSERT INTO score
VALUES
( 2, 1, 70 );
INSERT INTO score
VALUES
( 2, 2, 60 );
INSERT INTO score
VALUES
( 2, 3, 80 );
INSERT INTO score
VALUES
( 3, 1, 80 );
INSERT INTO score
VALUES
( 3, 2, 80 );
INSERT INTO score
VALUES
( 3, 3, 75 );
INSERT INTO score
VALUES
( 4, 1, 50 );
INSERT INTO score
VALUES
( 4, 2, 30 );
INSERT INTO score
VALUES
( 4, 3, 20 );
INSERT INTO score
VALUES
( 5, 1, 76 );
INSERT INTO score
VALUES
( 5, 2, 87 );
INSERT INTO score
VALUES
( 6, 1, 31 );
INSERT INTO score
VALUES
( 6, 3, 34 );
INSERT INTO score
VALUES
( 7, 2, 89 );
INSERT INTO score
VALUES
( 7, 3, 98 );

9、查看表students/teacher/course/score

desc students;
desc teacher;
desc course;
desc score;


二、查询

1、查看当前数据库中所有的表

show TABLES;

2、查询赵姓老师的数量

SELECT COUNT(t_name) FROM teacher where t_name LIKE "%"

2、查询男生、女生人数

SELECT COUNT(gender) FROM students where gender=""
SELECT COUNT(gender) FROM students where gender=""

3、查询名字中有“五”的学生人数

SELECT COUNT(name) FROM students where name like "%%"

4、查询所有授课老师的姓名

select t_name from teacher

5、统计每门课程的选修学生人数

SELECT
course_id,
count( student_id ) "学生人数"
FROM
score
GROUP
BY
course_id

4、查看同名学生名单,并统计人数

SELECT *,count(*) "人数" FROM	students s group by name HAVING count(name)>1

5、查询至少选修了两门课的学生学号

SELECT student_id FROM score GROUP BY student_id having count(*)>=2

6、查询平均成绩大于60分的同学的学号和平均成绩

SELECT
student_id,
avg( number )
FROM
score
GROUP
BY
student_id
HAVING
avg( number ) > 60

7、查询不同课程相同成绩的学生的学生编号,课程编号,学生成绩

SELECT student_id,course_id,number from score s GROUP BY student_id,number having count(*)>1

8、查询没学过钱老师课的同学的学号、姓名;

select s_id as 学号, name as 姓名 from students where s_id not in (select student_id from score where course_id =(select cid from course where teacher_id =(select t_id from teacher where t_name = "钱老师")));

9、查询有课程成绩小于60分的同学的学号、姓名;

SELECT s_id 学号,name 姓名 from students WHERE s_id in (SELECT student_id from score where number<60)

10、查询学过“孙老师”老师授课的课程的同学的信息

SELECT
*
FROM
students
WHERE
s_id IN (
SELECT
student_id
FROM
score
WHERE
course_id IN ( SELECT cid FROM course WHERE teacher_id IN ( SELECT t_id FROM teacher WHERE t_name = "孙老师" ) )
)
-----------瓦尔登小柴胡----------


瓦尔登小柴胡

版权声明:本站内容全部来自于腾讯微信公众号,属第三方自助推荐收录。《【练习3】mysql高级查询》的版权归原作者「瓦尔登小柴胡」所有,文章言论观点不代表Lambda在线的观点, Lambda在线不承担任何法律责任。如需删除可联系QQ:516101458

文章来源: 阅读原文

相关阅读

关注瓦尔登小柴胡微信公众号

瓦尔登小柴胡微信公众号:gh_b5d6c152af53

瓦尔登小柴胡

手机扫描上方二维码即可关注瓦尔登小柴胡微信公众号

瓦尔登小柴胡最新文章

精品公众号随机推荐