MySQL(中)

ALTER TABLE emp ADD CONSTRAINT fk_emp FOREIGN KEY(did) REFERENCES dept(did)

dept

CASCADE

ALTER TABLE emp ADD CONSTRAINT fk_emp1 FOREIGN KEY(did) REFERENCES dept(did) ON DELETE CASCADE

ALTER TABLE emp ADD CONSTRAINT fk_emp2 FOREIGN KEY(did) REFERENCES dept(did) ON DELETE SET nullcategorycategoryresult

ALTER TABLE emp DROP FOREIGN KEY fk_emp1

#从学生表里查询出学号和学生姓名

SELECT s.studentno,s.studentname FROM student s;
SELECT r.subjectno,r.studentresult FROM result 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

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 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.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 subjectsub ON r.SubjectNo=sub.SubjectNo

SELECT sub.SubjectName,r.StudentResult FROM subjectsub INNER JOIN result r ON sub.SubjectNo=r.SubjectNo WHERE sub.SubjectName="数据库结构-1"ORDER BY r.StudentResult DESC

SELECT s.studentno,s.studentname,r.studentresult ,sub.subjectname FROM student s INNER JOIN result r ON s.StudentNo=r.StudentNo INNER JOIN subjectsub ON r.SubjectNo=sub.SubjectNo WHERE sub.SubjectName=“数据库结构-2” ORDER BY r.StudentResult DESC LIMIT 0,10;

SELECT sub.SubjectName AS “科目” ,AVG(r.StudentResult) AS “平均分”,MAX(r.StudentResult)
AS “最高分”,MIN(r.StudentResult) AS “最低分” FROM result r LEFT JOIN subjectsub ON r.SubjectNo=sub.SubjectNo GROUP BY sub.SubjectName HAVING AVG(r.StudentResult)>=60;

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
	categoryCode

) 

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 d WHERE a.status= d.valueid AND typecode= “APP_STATUS”) AS valuemame,
a.flatformId,
(SELECT valuename FROM data_dictionary d WHERE a.status=d.valueid AND typecode= “APP_FLATFORM”) AS flatformname,
a.categoryLevel1,
(SELECT cetegoryName FROM ad_category d WHERE a.status=d.valueid AND parentId IS NULL) AS categoryLevel1name

FROM app_info adata_dictionary
data_dictionary

索引：方便查询数据，加快查询速度。

四种索引
第一种：
主键索引，是唯一的，一张表里只能有一个主键索引；

ALTER TABLE dept ADD PRIMARY KEY(sid)

增加唯一索引（主键索引只能添加一个，唯一索引可以添加多个）

常规索引

ALTER TABLE dept ADD INDEX(spwd)

查看索引

SHOW INDEX FROM dept;

删除索引

DROP spwd ON dept;

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

视图的好处：
使我们查询出来的数据更加直观，
如何创建一个视图：
CREATE VIEW view_name AS SELECT * FROM dept WHERE sid=1；

查看视图：

SELECT * FROM view_name;

删除视图：

DROP VIEW view_name;

以上代码结合数据库使用，要这个数据库加Q：719109729