Day2学习总结

Day2数据库学习总结

用内连接查询（取得是两个表的交集）
selete s.studentNo,s.studentName from student as s;
selete r.SbujectNo,r.studentResult from result as r;

SELECT s.studentNo,s.studentName,r.SubjectNo,r.studentResult FROM student s ,
INNER JOIN result r ON s.StudentNo=r.studentNo;
非等值查询

select s.studentNo,s.studentName,r.SubjectNo,r.studentResult from student s,result r
where s.studentNo=r.studentNo

等值查询
select s.studentNo,s.studentName,r.SubjectNo,r.studentResult from
student s,result r where s.studentNo=r.studentNo;

(主表student 从表result)左外连接 匹配的规则是：一左表为基准，
select s.studentNo,s.studentName,r.SubjectNo,r.studentResult from student s left join result r
on s.studentNo=r.studentNo;

//成绩不为空
select s.studentNo,s.studentName,r.SubjectNo,r.studentResult from student s left join result r
on s.studentNo=r.studentNo where r.r.studentResult is not null;
//成绩为空
select s.studentNo,s.studentName,r.SubjectNo,r.studentResult from student s left join result r
on s.studentNo=r.studentNo where r.r.studentResult is null;

三表关联
SELECT s.studentNo,s.studentName,r.studentResult,sub.SubjectName FROM student s INNER JOIN
result r ON s.StudentNo=r.StudentNo
INNER JOIN subject sub ON r.SubjectNo=sub.SubjectNo;

SELECT s.studentNo,s.studentName,sub.SubjectName,r.StudentResult FROM student s INNER JOIN result r
ON s.StudentNo=r.StudentNo INNER JOIN subject sub
ON r.SubjectNo=sub.SubjectNo WHERE sub.SubjectName=“数据库结构-2” ORDER BY r.studentResult DESC;

SELECT sub.subjectName,r.StudentResult FROM subject sub,result r WHERE sub.subjectNo=13
ORDER BY r.StudentResult DESC;

右外连接：匹配的规则：以右表为基准，左表一一匹配，如果匹配上的显示 如果左表匹配不上的为null。

SELECT s.sudentNo,s.studentName,sub.subjectName,r.StudentResult FROM subject sub,result r ,student s,
WHERE sub.subjectNo=13 ORDER BY r.StudentResult DESC limit 0,10;

select s.SubjectName as “课程名”,max(studentresult) as"最高分",min(studentresult) as"最低分"，
avg(studentresult) as “平均分” from result r left join subject sub on r.studnetreult=sub.studentresult
group by sub.studentresult having avg(r.studentresult)>=60;

/查询密码长度不足6位的学员信息(学员编号、姓名、密码、年级名称)/
SELECT s.StudentNo,s.StudentName,s.LoginPwd,s.GradeId FROM student s WHERE LENGTH(s.LoginPwd)<6;

SELECT s.StudentNo,s.StudentName,s.LoginPwd,s.GradeId FROM student s WHERE LENGTH(s.LoginPwd)<6;

#查询课程为（高等数学-2）且分数不小于80分的学生的学号和姓名
#子查询就是一个查询嵌套另一个查询
SELECT s.studentNo,s.studentName FROM student s WHERE s.StudentNo IN(
SELECT r.studentNo FROM result r WHERE r.SubjectNo=
(SELECT sub.subjectNo FROM subject sub WHERE sub.SubjectName=“高等数学-2”)
AND R.StudentResult>80
)
子查询的应用
SELECT a.id,a.softwareName,a.APKName,a.supportROM,a.softwareSize,a.devId,
(SELECT devName FROM dev_user d WHERE d.id=a.devId) AS devName,
a.status,
(SELECT valueName FROM data_dictionary dat WHERE dat.typeCode=“APP_STATUS” AND dat.valueId=a.status) AS statusName,
a.flatformId,
(SELECT valueName FROM data_dictionary dat WHERE dat.typeCode=“APP_FLATFORM” AND dat.valueId=a.flatformId) AS flatformName,
a.categoryLevel1,
(SELECT categoryName FROM app_category app WHERE app.id=a.categoryLevel1) AS categoryLevel1Name,
a.categoryLevel2,
(SELECT categoryName FROM app_category app WHERE app.id=a.categoryLevel2) AS categoryLevel2Name,
a.categoryLevel3,
(SELECT categoryName FROM app_category app WHERE app.id=a.categoryLevel3) AS categoryLevel3Name,
a.downloads,
a.onSaleDate,
a.versionId,
(SELECT versionNo FROM app_version ave WHERE ave.id=a.versionId) AS versionNo

FROM app_info a

索引：方便查询数据，加快查询速度
四种索引（主键索引，它是唯一，而且每一张表里面只能有一个主键索引）
创建索引语法：
create index 索引名称 on 表名（字段）；
//添加主键
alter table dept(键名) add primary key(sid);
//删除外键
ALTER TABLE eyy DROP FOREIGN KEY fk_eyy1
//增加唯一索引（主键索引只能添加一个，唯一索引可以添加多个）
常规索引
alter table dept add index(spwd);
//查看索引
show index from dept;
//删除索引
drop index spwd on dept ;

一般一张表里面什么字段会加上索引：主键，这个字段经常被使用的时候，关联的数据一般不建议加上索引。
select * from dept;

//视图：
好处：使我们查询出来的数据更加直观。
//创建视图
create view 数据库.视图名
as
(select *from tableName)

使用视图
select * from 视图名；

//删除视图
drop view tableName;

例子
CREATE TRIGGER t_mon BEFORE INSERT ON dede FOR EACH ROW
INSERT INTO eyy (ename,did,jixiao)VALUES(NEW.dname,new.did,NEW.salary*0.5);