本文基于学校的班级、学生、学科、成绩等场景,实践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

更多推荐

常用SQL语句(完整范例)