- 最近在帮一些同事培训学习SQL,综合网上的例子,我这边自己选了一些经典的过来做为例子然后整理了一版,以下查询基于MySQL 8
- 表建立和数据准备
CREATE TABLE `teacher` (
`tno` varchar(20) NOT NULL,
`tname` varchar(20) NOT NULL,
`tsex` varchar(20) NOT NULL,
`tbirthday` datetime DEFAULT NULL,
`prof` varchar(20) DEFAULT NULL,
`depart` varchar(20) NOT NULL,
PRIMARY KEY (`tno`)
)
INSERT INTO `teacher` VALUES ('804','李诚','男','1958-12-02 00:00:00','副教授','计算机系')
,('825','王萍','女','1972-05-05 00:00:00','助教','计算机系')
,('831','刘冰','女','1977-08-14 00:00:00','助教','电子工程系')
,('856','张旭','男','1969-03-12 00:00:00','讲师','电子工程系');
CREATE TABLE `course` (
`cno` varchar(20) NOT NULL,
`cname` varchar(20) NOT NULL,
`tno` varchar(20) NOT NULL,
PRIMARY KEY (`cno`)
)
INSERT INTO `course` VALUES
('3-105','计算机导论','825'),('3-245','操作系统','804')
,('6-166','数字电路','856'),('9-888','高等数学','831');
CREATE TABLE `student` (
`sno` varchar(20) NOT NULL,
`sname` varchar(20) NOT NULL,
`ssex` varchar(20) NOT NULL,
`sbirthday` datetime DEFAULT NULL,
`class` varchar(20) DEFAULT NULL,
PRIMARY KEY (`sno`)
)
INSERT INTO `student` VALUES ('101','曾华','男','1977-09-01 00:00:00','95033')
,('102','匡明','男','1975-10-02 00:00:00','95031')
,('103','王丽','女','1976-01-23 00:00:00','95033')
,('104','李军','男','1975-02-10 00:00:00','95033')
,('105','王芳','女','1974-06-03 00:00:00','95031')
,('106','陆君','男','1974-06-03 00:00:00','95031')
,('107','王尼玛','男','1976-02-20 00:00:00','95033')
,('108','张全蛋','男','1975-02-10 00:00:00','95031')
,('109','赵铁柱','男','1974-06-03 00:00:00','95031')
,('110','赵英俊'