sql语句
参考https://sleele.com/2019/01/23/
基础建表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- Table structure for Course
DROP TABLE IF EXISTS `Student`;
CREATE TABLE `Student` (
`SId` varchar(10) DEFAULT NULL,
`Sage` datetime DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- INSERT
INSERT INTO `Student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
进阶
select
engine=innodb和engine=myisam的区别
MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。 MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持已经外部键等高级数据库功能。
修改:ALTER TABLE tablename ENGINE = MyISAM ;
选择:如果执行大量 的SELECT,MyISAM是更好的选择。如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表
参考:https://blog.csdn.net/feng88724/article/details/6829416
sql语句
-
--构造表为新表,用where,条件用and;
SELECT t1.SId,t2.score FROM (SELECT SId,score FROM SC WHERE CId='01') t1, (SELECT SId,score FROM SC WHERE CId='02') t2 WHERE t1.SId=t2.SId AND t1.score>t2.score
-
AVG,GROUP BY,HAVING
-
INNER JOIN..on
group by s.sno即以sno为基准筛选出avg成绩,然后having进行判断,having一般跟group by,如果我非不用再group by后面,那就是起到一个where的作用。这个没啥好说的,继续了
SELECT Student.*,t1.scavg FROM Student INNER JOIN (SELECT SC.SId,AVG(SC.score)AS scavg FROM SC GROUP BY SC.SId HAVING AVG(SC.score)>=60)AS t1 ON Student.SId=t1.SId
-
where sno in (select ..from where..)
select sno, sname from student where sno in (select sno from sc where sc.cno = 1) and sno in (select sno from sc where sc.cno = 2);
每次使用的时候都要去遍历主查询中的数据是否存在于子查询,
下面的效率更高
5
select DISTINCT s1.sno,s1.sname from student s1,sc sc1 where s1.sno = sc1.sno and s1.sno <> '1' and sc1.cno in(select sc.cno from sc sc,student s where s.sno = sc.sno and s.sno = '1');