SQL内功心法之乾坤大挪移
SQL： 指结构化查询语言，全称是 Structured Query Language。 SQL语言包括三种主要程序设计语言类别的语句：数据定义语言(DDL)，数据操作语言(DML)及数据控制语言(DCL)。 DCL：数据库控制语言，Data Control Language， 用来设置或更改数据库用户或角色权限的语句，【grant、remove,deny】 DML：数据操纵语言，Data Manipulation Language ，命令使用户能够操作已有数据库中的数据的计算机语言 【Insert、delete、update】，处理表数据 DDL：数据库模式定义语言，Data Definition Language ，是用于描述数据库中要存储的现实世界实体的语言【create，add,alter,drop】处理表结构 DQL：数据查询语言，Data Query Language SELECT ，【select】
----------------------------------表结构---------------------------------------------------- --学生表 Student（编号student_code、姓名student_name、年龄student_age、性别student_sex） --课程表 Course（课程编号course_code、课程名称course_name、教师编号teacher_code） --成绩表 Score（学生编号student_code、课程编号course_code、成绩score） --教师表 Teacher（教师编号teacher_code、姓名teacher_name）
创建表：
CREATE TABLE Student ( `student_code` varchar(255) NOT NULL COMMENT '学号' , `student_name` varchar(255) NULL COMMENT '学生姓名' , `student_sex` char(1) NULL COMMENT '性别' , `student_age` varchar(255) NULL COMMENT '年龄' , `create_by` varchar(255) NULL COMMENT '创建人' , `create_date` datetime NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建日期' , `update_by` varchar(255) NULL COMMENT '更新人' , `updtae_date` datetime NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' , PRIMARY KEY (`student_code`) );
CREATE TABLE Course ( `course_code` varchar(255) NOT NULL COMMENT '课程号' , `course_name` varchar(255) NULL COMMENT '课程姓名' , `teacher_code` varchar(255) NULL COMMENT '教师编码' , `create_by` varchar(255) NULL COMMENT '创建人' , `create_date` datetime NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建日期' , `update_by` varchar(255) NULL COMMENT '更新人' , `updtae_date` datetime NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' , PRIMARY KEY (`course_code`) );
CREATE TABLE Score ( `student_code` varchar(255) NOT NULL COMMENT '学号' , `course_code` varchar(255) NULL COMMENT '课程编码' , `score` int(100) NULL COMMENT '成绩' );
CREATE TABLE Teacher ( `teacher_code` varchar(255) NOT NULL COMMENT '教师编号' , `teacher_name` varchar(255) NULL COMMENT '教师姓名' , `create_by` varchar(255) NULL COMMENT '创建人' , `create_date` datetime NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建日期' , `update_by` varchar(255) NULL COMMENT '更新人' , `updtae_date` datetime NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' , PRIMARY KEY (`teacher_code`) );
插入数据：
---插入数据--
INSERT INTO `student` (`student_code`, `student_name`, `student_sex`, `student_age`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('201709001', 'A001', '男', '22', 'system', '2017-01-03 15:08:23', 'system', '2017-01-03 15:08:23');
INSERT INTO `student` (`student_code`, `student_name`, `student_sex`, `student_age`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('201709002', 'A002', '女', '22', 'system', '2017-01-03 15:08:38', 'system', '2017-01-03 15:08:38');
INSERT INTO `student` (`student_code`, `student_name`, `student_sex`, `student_age`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('201709003', 'A003', '女', '22', 'system', '2017-01-03 15:08:38', 'system', '2017-01-03 15:08:38');
INSERT INTO `student` (`student_code`, `student_name`, `student_sex`, `student_age`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('201709004', 'A004', '男', '22', 'system', '2017-01-03 15:08:23', 'system', '2017-01-03 15:08:23');
INSERT INTO `student` (`student_code`, `student_name`, `student_sex`, `student_age`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('201709005', 'A005', '男', '22', 'system', '2017-01-03 15:08:23', 'system', '2017-01-03 15:08:23');
INSERT INTO `student` (`student_code`, `student_name`, `student_sex`, `student_age`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('201709006', 'A006', '女', '22', 'system', '2017-01-03 15:08:23', 'system', '2017-01-03 15:08:23');
INSERT INTO `student` (`student_code`, `student_name`, `student_sex`, `student_age`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('201709007', 'A007', '男', '22', 'system', '2017-01-03 15:08:23', 'system', '2017-01-03 15:08:23');
INSERT INTO `student` (`student_code`, `student_name`, `student_sex`, `student_age`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('201709008', 'A008', '女', '22', 'system', '2017-01-03 15:08:23', 'system', '2017-01-03 15:08:23');
INSERT INTO `student` (`student_code`, `student_name`, `student_sex`, `student_age`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('201709009', 'A009', '男', '22', 'system', '2017-01-03 15:08:23', 'system', '2017-01-03 15:08:23');
INSERT INTO `student` (`student_code`, `student_name`, `student_sex`, `student_age`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('201709010', 'A010', '女', '22', 'system', '2017-01-03 15:08:23', 'system', '2017-01-03 15:08:23');
INSERT INTO `student` (`student_code`, `student_name`, `student_sex`, `student_age`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('201709011', 'A011', '男', '22', 'system', '2017-01-03 15:08:23', 'system', '2017-01-03 15:08:23');
INSERT INTO `student` (`student_code`, `student_name`, `student_sex`, `student_age`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('201709012', 'A012', '男', '22', 'system', '2017-01-03 15:08:23', 'system', '2017-01-03 15:08:23');
---插入数据--
INSERT INTO `course` (`course_code`, `course_name`, `teacher_code`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('Chinese', '语文', 'T001', 'system', '2017-01-03 15:19:18', 'system', '2017-01-03 15:19:32');
INSERT INTO `course` (`course_code`, `course_name`, `teacher_code`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('Math', '数学', 'T002', 'system', '2017-01-03 15:19:18', 'system', '2017-01-03 15:19:32');
INSERT INTO `course` (`course_code`, `course_name`, `teacher_code`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('English', '英语', 'T003', 'system', '2017-01-03 15:19:18', 'system', '2017-01-03 15:19:32');
INSERT INTO `course` (`course_code`, `course_name`, `teacher_code`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('Physics', '物理', 'T004', 'system', '2017-01-03 15:19:18', 'system', '2017-01-03 15:19:32');
INSERT INTO `course` (`course_code`, `course_name`, `teacher_code`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('Political', '政治', 'T005', 'system', '2017-01-03 15:19:18', 'system', '2017-01-03 15:19:32');
INSERT INTO `course` (`course_code`, `course_name`, `teacher_code`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('Organism', '生物', 'T006', 'system', '2017-01-03 15:19:18', 'system', '2017-01-03 15:19:32');
INSERT INTO `course` (`course_code`, `course_name`, `teacher_code`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('Geography', '地理', 'T007', 'system', '2017-01-03 15:19:18', 'system', '2017-01-03 15:19:32');
INSERT INTO `course` (`course_code`, `course_name`, `teacher_code`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('History', '历史', 'T008', 'system', '2017-01-03 15:19:18', 'system', '2017-01-03 15:19:32');
INSERT INTO `course` (`course_code`, `course_name`, `teacher_code`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('Chemistry', '化学', 'T009', 'system', '2017-01-03 15:19:18', 'system', '2017-01-03 15:19:32');
---插入数据--
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709001', 'Chemistry', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709001', 'Chinese', '90');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709001', 'English', '97');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709001', 'Geography', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709001', 'History', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709001', 'Math', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709001', 'Organism', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709001', 'Physics', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709001', 'Political', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709002', 'Chemistry', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709002', 'Chinese', '90');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709002', 'English', '97');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709002', 'Geography', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709002', 'History', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709002', 'Math', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709002', 'Organism', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709002', 'Physics', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709002', 'Political', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709003', 'Chemistry', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709003', 'Chinese', '90');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709003', 'English', '97');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709003', 'Geography', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709003', 'History', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709003', 'Math', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709003', 'Organism', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709003', 'Physics', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709003', 'Political', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709004', 'Chemistry', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709004', 'Chinese', '90');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709004', 'English', '97');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709004', 'Geography', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709004', 'History', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709004', 'Math', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709004', 'Organism', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709004', 'Physics', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709004', 'Political', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709005', 'Chemistry', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709005', 'Chinese', '90');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709005', 'English', '97');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709005', 'Geography', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709005', 'History', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709005', 'Math', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709005', 'Organism', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709005', 'Physics', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709005', 'Political', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709006', 'Chemistry', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709006', 'Chinese', '90');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709006', 'English', '97');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709006', 'Geography', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709006', 'History', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709006', 'Math', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709006', 'Organism', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709006', 'Physics', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709006', 'Political', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709007', 'Chemistry', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709007', 'Chinese', '90');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709007', 'English', '97');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709007', 'Geography', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709007', 'History', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709007', 'Math', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709007', 'Organism', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709007', 'Physics', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709007', 'Political', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709008', 'Chemistry', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709008', 'Chinese', '90');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709008', 'English', '97');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709008', 'Geography', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709008', 'History', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709008', 'Math', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709008', 'Organism', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709008', 'Physics', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709008', 'Political', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709009', 'Chemistry', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709009', 'Chinese', '90');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709009', 'English', '97');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709009', 'Geography', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709009', 'History', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709009', 'Math', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709009', 'Organism', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709009', 'Physics', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709009', 'Political', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709010', 'Chemistry', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709010', 'Chinese', '90');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709010', 'English', '97');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709010', 'Geography', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709010', 'History', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709010', 'Math', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709010', 'Organism', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709010', 'Physics', '99');
INSERT INTO `score` (`student_code`, `course_code`, `score`) VALUES ('201709010', 'Political', '99');
---插入数据--
INSERT INTO `teacher` (`teacher_code`, `teacher_name`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('T001', '王羲之', 'system', '2017-01-03 15:42:06', 'system', '2017-01-03 15:42:16');
INSERT INTO `teacher` (`teacher_code`, `teacher_name`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('T002', '赵匡胤', 'system', '2017-01-03 15:42:06', 'system', '2017-01-03 15:42:16');
INSERT INTO `teacher` (`teacher_code`, `teacher_name`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('T003', '赵明', 'system', '2017-01-03 15:42:06', 'system', '2017-01-03 15:42:16');
INSERT INTO `teacher` (`teacher_code`, `teacher_name`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('T004', '燕十三郎', 'system', '2017-01-03 15:42:06', 'system', '2017-01-03 15:42:16');
INSERT INTO `teacher` (`teacher_code`, `teacher_name`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('T005', '龙吟', 'system', '2017-01-03 15:42:06', 'system', '2017-01-03 15:42:16');
INSERT INTO `teacher` (`teacher_code`, `teacher_name`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('T006', '方言', 'system', '2017-01-03 15:42:06', 'system', '2017-01-03 15:42:16');
INSERT INTO `teacher` (`teacher_code`, `teacher_name`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('T007', '彭松', 'system', '2017-01-03 15:42:06', 'system', '2017-01-03 15:42:16');
INSERT INTO `teacher` (`teacher_code`, `teacher_name`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('T008', '唐宋', 'system', '2017-01-03 15:42:06', 'system', '2017-01-03 15:42:16');
INSERT INTO `teacher` (`teacher_code`, `teacher_name`, `create_by`, `create_date`, `update_by`, `updtae_date`) VALUES ('T009', '张三丰', 'system', '2017-01-03 15:42:06', 'system', '2017-01-03 15:42:16');场景测试：
-- 1.查询“A”课程比“B”课程成绩高的所有学生的学号 SELECT student.student_code AS "StudentCode", student.student_name AS "StudentName" FROM Student student WHERE ( SELECT score1.score FROM Score score1 WHERE score1.student_code = student.student_code AND score1.course_code = 'Chemistry' -- A课程 ) > ( SELECT score2.score FROM Score score2 WHERE score2.student_code = student.student_code AND score2.course_code = 'Chinese' -- B课程 ); --2.查询平均成绩大于90分的同学的学号和平均成绩 SELECT score.student_code AS "StudentCode", AVG(score.score) AS " AVGScore" FROM Score score GROUP BY score.score HAVING AVG(score.score) > 90 --3.查询所有同学的学号、姓名、选课数、总成绩； SELECT student.student_code AS "StudentCode",--学号 student.student_name AS "StudentName",--姓名 COUNT(score.course_code) AS "SelectCourse",--选课数 SUM(score.score) AS "SumScore"--总成绩 FROM Student student LEFT JOIN Score score ON score.student_code = student.student_code GROUP BY student.student_code, student.student_name; --4、查询姓“张”的老师的个数； SELECT Count(*) FROM Teacher teacher WHERE teacher.teacher_name LIKE '张%'; --5.查询没有学过“张三丰”老师课的同学的学号、姓名； SELECT student.student_code AS "StudentCode", student.student_name AS "StudentName" FROM Student student WHERE student.student_code NOT IN ( SELECT DISTINCT (score.student_code) FROM Score score, Course course, Teacher teacher WHERE score.course_code = course.course_code AND teacher.teacher_code = course.course_code AND teacher.teacher_name = '张三丰' );
分享标题：SQL内功心法之乾坤大挪移
本文地址：http://myzitong.com/article/ipjjpp.html