常用SQL语句（完整范例）

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