sql语句（电信面试）

电信面试时小姐姐纹理数据库的知识，但我基础不扎实，回答错了，回来痛定思痛，好好搞

原博主在这：https://www.cnblogs.com/shenxiaoquan/p/6115070.html

我学习一下

1、创建表

代码

 SELECT AVG(score) AS 平均成绩 FROM student
 SELECT * FROM student WHERE score> (SELECT  AVG(score)FROM student)
SELECT * FROM zhangwu
 
建表
老师表
 CREATE TABLE `tblteacher` (
  `TeaId` VARCHAR(3) NOT NULL,
  `TeaName` VARCHAR(20) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY  (`TeaId`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

CREATE TABLE `tblstudent` (
  `StuId` VARCHAR(5) NOT NULL,
  `StuName` VARCHAR(10) CHARACTER SET utf8 NOT NULL,
  `StuAge` INT(11) DEFAULT NULL,
  `StuSex` CHAR(1) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY  (`StuId`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

 CREATE TABLE `tablcourse` (
  `CourseId` VARCHAR(4) NOT NULL,
  `CourseName` VARCHAR(10) CHARACTER SET utf8 NOT NULL,
  `TeaId` VARCHAR(3) NOT NULL,
  PRIMARY KEY  (`CourseId`),
  KEY `FK_tablcourse` (`TeaId`),
  CONSTRAINT `FK_tablcourse` FOREIGN KEY (`TeaId`) REFERENCES `tblteacher` (`TeaId`)
) ENGINE=INNODB DEFAULT CHARSET=latin1
  
  
  CREATE TABLE `talscore` (
  `StuId` VARCHAR(5) NOT NULL,
  `CourseId` VARCHAR(4) NOT NULL,
  `Score` FLOAT DEFAULT NULL,
  KEY `FK_Courseid` (`CourseId`),
  KEY `FK_Stuid` (`StuId`),
  CONSTRAINT `FK_Courseid` FOREIGN KEY (`CourseId`) REFERENCES `tablcourse` (`CourseId`),
  CONSTRAINT `FK_Stuid` FOREIGN KEY (`StuId`) REFERENCES `tblstudent` (`StuId`)
) ENGINE=INNODB DEFAULT CHARSET=latin1


INSERT INTO tblStudent
SELECT '1000','张无忌',18,'男' UNION
SELECT '1001','周芷若',19,'女' UNION
SELECT '1002','杨过',19,'男' UNION
SELECT '1003','赵敏',18,'女' UNION
SELECT '1004','小龙女',17,'女' UNION
SELECT '1005','张三丰',18,'男' UNION
SELECT '1006','令狐冲',19,'男' UNION
SELECT '1007','任盈盈',20,'女' UNION
SELECT '1008','岳灵珊',19,'女' UNION
SELECT '1009','韦小宝',18,'男' UNION
SELECT '1010','康敏',17,'女' UNION
SELECT '1011','萧峰',19,'男' UNION
SELECT '1012','黄蓉',18,'女' UNION
SELECT '1013','郭靖',19,'男' UNION
SELECT '1014','周伯通',19,'男' UNION
SELECT '1015','瑛姑',20,'女' UNION
SELECT '1016','李秋水',21,'女' UNION
SELECT '1017','黄药师',18,'男' UNION
SELECT '1018','李莫愁',18,'女' UNION
SELECT '1019','冯默风',17,'男' UNION
SELECT '1020','王重阳',17,'男' UNION
SELECT '1021','郭襄',18,'女' 
go

INSERT  INTO tblTeacher
SELECT '001','姚明' UNION
SELECT '002','叶平' UNION
SELECT '003','叶开' UNION
SELECT '004','孟星魂' UNION
SELECT '005','独孤求败' UNION
SELECT '006','裘千仞' UNION
SELECT '007','裘千尺' UNION
SELECT '008','赵志敬' UNION
SELECT '009','阿紫' UNION
SELECT '010','郭芙蓉' UNION
SELECT '011','佟湘玉' UNION
SELECT '012','白展堂' UNION
SELECT '013','吕轻侯' UNION
SELECT '014','李大嘴' UNION
SELECT '015','花无缺' UNION
SELECT '016','金不换' UNION
SELECT '017','乔丹'
go

INSERT INTO tablCourse
SELECT '001','企业管理','002' UNION
SELECT '002','马克思','008' UNION
SELECT '003','UML','006' UNION
SELECT '004','数据库','007' UNION
SELECT '005','逻辑电路','006' UNION
SELECT '006','英语','003' UNION
SELECT '007','电子电路','005' UNION
SELECT '008','思想概论','004' UNION
SELECT '009','西方哲学史','012' UNION
SELECT '010','线性代数','017' UNION
SELECT '011','计算机基础','013' UNION
SELECT '012','AUTO CAD制图','015' UNION
SELECT '013','平面设计','011' UNION
SELECT '014','Flash动漫','001' UNION
SELECT '015','Java开发','009' UNION
SELECT '016','C#基础','002' UNION
SELECT '017','Oracl数据库原理','010'
go

INSERT INTO talscore
SELECT '1001','003',90 UNION
SELECT '1001','002',87 UNION
SELECT '1001','001',96 UNION
SELECT '1001','010',85 UNION
SELECT '1002','003',70 UNION
SELECT '1002','002',87 UNION
SELECT '1002','001',42 UNION
SELECT '1002','010',65 UNION
SELECT '1003','006',78 UNION
SELECT '1003','003',70 UNION
SELECT '1003','005',70 UNION
SELECT '1003','001',32 UNION
SELECT '1003','010',85 UNION
SELECT '1003','011',21 UNION
SELECT '1004','007',90 UNION
SELECT '1004','002',87 UNION
SELECT '1005','001',23 UNION
SELECT '1006','015',85 UNION
SELECT '1006','006',46 UNION
SELECT '1006','003',59 UNION
SELECT '1006','004',70 UNION
SELECT '1006','001',99 UNION
SELECT '1007','011',85 UNION
SELECT '1007','006',84 UNION
SELECT '1007','003',72 UNION
SELECT '1007','002',87 UNION
SELECT '1008','001',94 UNION
SELECT '1008','012',85 UNION
SELECT '1008','006',32 UNION
SELECT '1009','003',90 UNION
SELECT '1009','002',82 UNION
SELECT '1009','001',96 UNION
SELECT '1009','010',82 UNION
SELECT '1009','008',92 UNION
SELECT '1010','003',90 UNION
SELECT '1010','002',87 UNION
SELECT '1010','001',96 UNION

SELECT '1011','009',24 UNION
SELECT '1011','009',25 UNION

SELECT '1012','003',30 UNION
SELECT '1013','002',37 UNION
SELECT '1013','001',16 UNION
SELECT '1013','007',55 UNION
SELECT '1013','006',42 UNION
SELECT '1013','012',34 UNION
SELECT '1000','004',16 UNION
SELECT '1002','004',55 UNION
SELECT '1004','004',42 UNION
SELECT '1008','004',34 UNION
SELECT '1013','016',86 UNION
SELECT '1013','016',44 UNION
SELECT '1000','014',75 UNION
SELECT '1002','016',100 UNION
SELECT '1004','001',83 UNION
SELECT '1008','013',97
go