本文基于学校的班级、学生、学科、成绩等场景，实践SQL语句的使用

创建表

创建班级表

CREATE TABLE class(
class_id INT auto_increment PRIMARY key,
class_name VARCHAR(10));
alter table class AUTO_INCREMENT =1;

创建学生表

CREATE TABLE student(
stu_id INT AUTO_INCREMENT  PRIMARY KEY,
stu_name VARCHAR(10),
stu_sex VARCHAR(1),
stu_age INT,
class_id INT,
foreign key(class_id) references class(class_id)
);
alter table student AUTO_INCREMENT =1;

创建科目表

CREATE TABLE course(
course_id INT auto_increment PRIMARY key,
course_name VARCHAR(10));
alter table course AUTO_INCREMENT =1;

创建成绩表

drop table score;
CREATE TABLE score(
id INT auto_increment PRIMARY key,
stu_id INT,
course_id INT,
mark DECIMAL(3,1),
foreign key(stu_id) references student(stu_id),
foreign key(course_id) references course(course_id)
);
alter table score AUTO_INCREMENT =1;
desc score;

向表里插入数据

操作表为class表

insert into class values(NULL, 一二)
insert into class (`class_id`,`class_name`) values (NULL, 一3)
insert into class (`class_name`) values (一4)

存储过程的使用：批量插入数据

创建存储过程

CREATE PROCEDURE search_sex ( #search_user_name为存储过程的名字
	IN search_sex VARCHAR (20), #传入的参数
	OUT count_number INT #返回的参数
) READS SQL DATA #程序中包含读数据的语句
BEGIN
	SELECT
		COUNT(*) INTO count_number
	FROM
		student
	WHERE
		stu_sex LIKE CONCAT(%, search_sex, %);
END

调用存储过程

CALL search_sex(女, @nameCount);
SELECT @nameCount;

使用存储过程向学生表里插入大量数据

#创建存储过程创建大量数据
drop PROCEDURE  if EXISTS insert_students;
CREATE PROCEDURE insert_students(
IN loop_times INT,
IN stu_name CHAR,
IN stu_sex CHAR,
IN stu_age INT,
IN class_id INT)
BEGIN
    DECLARE var INT DEFAULT 0;	
    WHILE var < loop_times DO
        INSERT INTO student VALUES (NULL,concat(stu_name,CONVERT(var,CHAR)),stu_sex,stu_age,class_id);
        SET var = var + 1;
    END WHILE;
END

CALL insert_students(10,钱,男,16,4);

查找语句

查找所有成绩都大于95分的同学姓名

SELECT student.stu_name FROM score,student
where score.mark>95
and student.stu_id=score.stu_id
GROUP BY score.stu_id
having count(*)>1

查询平均分大于90分的同学名单

SELECT score.stu_id,student.stu_name,AVG(score.mark)FROM score,student 
where student.stu_id=score.stu_id
GROUP BY score.stu_id HAVING AVG(score.mark)>90

查出平均分最高的同学 （如果最高有两个呢？？）

SELECT score.stu_id,student.stu_name,AVG(score.mark)FROM score,student 
where student.stu_id=score.stu_id
GROUP BY score.stu_id
ORDER BY AVG(score.mark) desc LIMIT 1