索引:提高查询速度
创建、删除索引creat index index_sname on student(sname); drop index index_sname on student;
SQL支持关系数据库三级模式结构
等值链接,把不同表通过相同的属性连接到一起
把user表和menu表连接到一起select user.id,user.name,menu.mname from user,menu where user.id=menu.id
内连接:inner join 返回两个表的交集部分
将student表和sc表通过学号连接起来select s.sno,s.sname,sc.grade from student as s inner join sc on s.sno=sc.sno
左连接:left join 左表的全部都会表示出来
将student表和sc表左连接select s.sno,s.sname,sc.cno,sc.grade from student as s left join sc s.sno=sc.sno
所有选课的学生的成绩信息
select s.sno,s.sname,sc.cno,sc.grade from student as s left jion sc s.sno=sc.sno where sc.cno is not null
查出每个学生的总分
select s.sno,s.sname,sc.cno,sum(sc.grade) from student as s left jion sc s.sno=sc.sno where sc.cno is not null group by s.sno
视图是一张假表,可以修改,不能增加删除
创建一个视图,显示学号,姓名,课程名,成绩信息creat view student_info as SELECT s.sno,s.sname,c.cname,sc.grade from sc INNER JOIN student as s INNER JOIN course as c on s.sno=sc.sno and c.cno=sc.cno
查询李丽的数学成绩
select * from student_info as so where so.sname='李丽' and so.cname='数学'
函数
创建一个函数返回学生地址delimiter && creat function getAddress(myname varchar(12)) returns varchar(12) begin return(select sprovince from student where sname=myname); end && select getAddress('李丽');
存储过程是为了完成特定功能的SQL语句集
创建一个存储过程可以通过名字获取地址delimiter && creat procedure getAddressByName(in myname VARCHAR(12),out addr VARCHAR(12)) begin select sprovince into addr from student where sname=myname; end && @name='李丽'; @addr=''; call getAddressByName(@name,@addr); select @addr;
输入姓名和学科查出成绩,并判断等级
delimiter && CREATE procedure getScoreLevel(in myname VARCHAR(12),inout cname_res VARCHAR(12)) BEGIN DECLARE score int default(0); DECLARE sid VARCHAR(12) default(''); DECLARE cid VARCHAR(12) default(''); select sno into sid from student where sname=myname; SELECT cno into cid from course where cname=cname_res; SELECT grade into score from sc where sc.sno=sid and sc.cno=cid; if score<60 THEN set cname_res='不及格'; elseif score>=60 and score<80 THEN set cname_res='及格'; else set cname_res='优秀'; end IF; END && set @name='李丽'; set @cname='数学'; call getScoreLevel(@name,@cname); SELECT @cname;
循环加分直到所有人及格
delimiter && CREATE PROCEDURE setAllPass(inout num int) BEGIN declare min_score int default(-1); SELECT min(grade) into min_score from sc; if min_score>=60 then set num=1; ELSE while min_score<60 do UPDATE sc set grade=grade+num where grade=min_score; SELECT min(grade) into min_score from sc; end WHILE; end if; END && set @num=4; call setAllPass(@num);
SQL多表查询,视图、函数、存储过程
最新推荐文章于 2023-06-05 20:44:09 发布