vlambda博客
学习文章列表

一、数据准备

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 = "孙老师" ) )
	)