/*
Navicat MySQL Data Transfer
Source Server : ce
Source Server Version : 50549
Source Host : localhost:3306
Source Database : st
Target Server Type : MYSQL
Target Server Version : 50549
File Encoding : 65001
Date: 2020-12-01 23:00:12
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for courses
-- ----------------------------
DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses` (
`corseNO` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`corseNO`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of courses
-- ----------------------------
INSERT INTO `courses` VALUES ('1', 'linux');
INSERT INTO `courses` VALUES ('2', 'python');
INSERT INTO `courses` VALUES ('3', 'java');
INSERT INTO `courses` VALUES ('4', 'C#');
INSERT INTO `courses` VALUES ('5', null);
-- ----------------------------
-- Table structure for scores
-- ----------------------------
DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`corseNo` int(10) DEFAULT NULL,
`studenNo` varchar(10) DEFAULT NULL,
`score` int(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of scores
-- ----------------------------
INSERT INTO `scores` VALUES ('1', '1', '001', '80');
INSERT INTO `scores` VALUES ('2', '2', '002', '45');
INSERT INTO `scores` VALUES ('3', '1', '003', '60');
INSERT INTO `scores` VALUES ('4', '3', '004', '78');
INSERT INTO `scores` VALUES ('5', '4', '005', '89');
INSERT INTO `scores` VALUES ('6', '2', '006', '82');
INSERT INTO `scores` VALUES ('7', '3', '007', '72');
INSERT INTO `scores` VALUES ('8', '4', '008', '67');
INSERT INTO `scores` VALUES ('9', '2', '009', '100');
INSERT INTO `scores` VALUES ('10', '3', '010', '73');
INSERT INTO `scores` VALUES ('11', '4', '011', '87');
INSERT INTO `scores` VALUES ('12', '2', '012', '70');
INSERT INTO `scores` VALUES ('13', null, null, null);
-- ----------------------------
-- Table structure for stu
-- ----------------------------
DROP TABLE IF EXISTS `stu`;
CREATE TABLE `stu` (
`studenNo` varchar(20) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` varchar(4) DEFAULT NULL,
`hometown` varchar(50) DEFAULT NULL,
`age` varchar(10) DEFAULT NULL,
`class` varchar(10) DEFAULT NULL,
`card` varchar(50) DEFAULT NULL,
PRIMARY KEY (`studenNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of stu
-- ----------------------------
INSERT INTO `stu` VALUES ('001', '诸葛亮', '男', '广东', '12', '01班', '421282819945041345');
INSERT INTO `stu` VALUES ('002', '刘备', '女', '黑龙江', '23', '02班', '421282819945041345');
INSERT INTO `stu` VALUES ('003', '张飞', '女', '河南', '19', '01班', '421282819945041345');
INSERT INTO `stu` VALUES ('004', '孙尚香', '男', '上海', '34', '02班', '421282819945041345');
INSERT INTO `stu` VALUES ('005', '孙权', '男', '河南', '23', '03班', '421282819945041345');
INSERT INTO `stu` VALUES ('006', '关羽', '女', '北京', '34', '04班', '421282819945041345');
INSERT INTO `stu` VALUES ('007', '曹操', '男', '山东', '34', '01班', '421282819945041345');
INSERT INTO `stu` VALUES ('008', '李世民', '男', '河南', '45', '03班', '421282819945041345');
INSERT INTO `stu` VALUES ('009', '曹丕', '男', '河南', '24', '02班', '421282819945041345');
INSERT INTO `stu` VALUES ('010', '刘禅', '男', '广西', '26', '04班', '421282819945041345');
INSERT INTO `stu` VALUES ('011', '周瑜', '男', '河南', '26', '02班', '421282819945041345');
INSERT INTO `stu` VALUES ('012', '公孙大娘', '女', '天津', '30', '02班', null);
INSERT INTO `stu` VALUES ('013', '李白', '男', '北京', '20', '02班', ' ');
- 去重
select distinct sex from stu
select age from stu where name ='诸葛亮3'
-- 条件查询
select * from stu where age <'20'
select * from stu where hometown !='河南'
-- 查询学号为“003”学生身份证号
select card from stu where studenNo='003'
-- 查询“01班”以外的学生信息
select * from stu where class !='01班'
-- 查询年龄大于“20岁”学生的姓名和性别
select name ,sex from stu where age >'20'
-- 查询年龄小于20岁的女同学
select * from stu where age <'20' and sex='女'
-- 查询女学生或者为01班的学生
select * from stu where sex ='女' or class ='01班'
-- 查询家乡不是河南的学生
select * from stu where not hometown ='河南'
-- 查询广东或河南的学生
select * from stu where hometown ='广东' or hometown ='河南'
-- 查询01班的河南学生
select * from stu where class='01班' and hometown ='河南'
-- 查询非20岁的学生
select * from stu where not age ='20'
-- 查询姓刘的学生
select * from stu where name like '刘%'
-- 查询姓曹且名字是一个字的学生
select * from stu where name like '曹_'
-- 查询叫婵的学生
select * from stu where name like '%禅'
-- 查询家乡是广东或者河南或者山东
select * from stu where hometown ='广东' or hometown ='河南' or hometown ='山东'
select * from stu where hometown in ('广东','河南','山东')
-- 查询年龄大于18岁小于40岁的学生信息
select * from stu where age >=18 and age <= 40
select * from stu where age between 18 and 40
-- 查询年龄在19或23或34的女生
select * from stu where age in ('19','23','34') and sex ='女'
-- 查询年龄在18到30岁以外的学生信息
select * from stu where NOT age between 18 and 30
-- 查询没有填写身份证号的学生信息
select * from stu where card is NULL
-- 查询空字符串的身份证号的学生信息
select * from stu where card =''
-- 查询填写身份证的学生信息
select * from stu where NOT card is null
-- 查询学生所有信息，按照年龄从小到大排序（默认不写asc也是升序），即升序
select * from stu group by age asc
-- 查询学生所有信息，按照年龄从大到小排序，即降序
select * from stu group by age desc
-- 按照名字进行排序【对中文进行排序】
select * from stu group by CONVERT(name USING gbk)
-- 【聚合函数】
-- 查询学生总数
select count(*) from stu
-- 查询女生最大年龄
select max(age) from stu where sex ='女'
-- 查询1班最小年龄
-- 查询所有学生的最大年龄、最小年龄、平均年龄
select max(age),min(age),avg(age) from stu
-- 01班共有多少个学生
select count(*) from stu where class= '01班'
-- 查询3班小于50岁的年龄有多少
select count(*) from stu where age < 50 and class='03班'
-- 查询各种性别
select sex as 性别, count(*) as 性别总人数 from stu group by sex
-- 查询各种年龄的人数
select age , count(*) from stu group by age
-- 查询各个班级学生的平均年龄、最大年龄、最小年龄
select class as 班级, count(class) as 班级, avg(age) as 平均年龄,max(age) as 最大年龄,min(age) 最小年龄 from stu group by class
-- 分别查询别个班下面的男女各多少人
select class ,sex ,count(*) from stu group by class ,sex
-- 查询男生总人数
select count(*) from stu where sex ="男"
select sex, count(*) from stu group by sex having sex='男'
-- 查询除01班以外其他班级的平均年龄、最大年龄、最小年龄
select class, avg(age),max(age),min(age) from stu where class !="01班" group by class
select class, avg(age),max(age),min(age) from stu group by class having not class='01班'
-- 统计每个班级中每种性别的学生人数，且按照班级进行升序
select class,sex,count(*) from stu group by class , sex order by class
-- 查询前3条数据
select * from stu LIMIT 0,3
select * from courses
select * from scores
-- 等值查询
select * from stu,scores where stu.studenNo=scores.studenNo
-- 内连接
select * from stu inner join scores on stu.studenNo=scores.studenNo
-- 查询课程信息及课程的成绩
select * from courses inner join scores on courses.cuseNo=scores.curseNo
-- 查询学生信息及学生的课程对应的成绩
select * from stu
inner join scores on stu.studenNo=scores.studenNo
inner join courses on courses.corseNo=scores.corseNo
-- 查询张飞的成绩，要求显示姓名，课程号，成绩
SELECT
stu.name as 姓名,courses.corseNO as 课程号 , stu.studenNo as 学号 , scores.score as 成绩
FROM
stu
INNER JOIN scores ON stu.studenNo = scores.studenNo
INNER JOIN courses ON courses.corseNo = scores.corseNo
where stu.`name`='张飞'
-- 查询男生中最高成绩，要求显示姓名，课程名，成绩
select stu.`name` as 姓名,courses.`name` as 课程名 , scores.score as 成绩 from stu inner join scores on stu.studenNo=scores.studenNo
inner join courses ON courses.corseNO=scores.corseNo where sex='男'order by score desc LIMIT 0,1
-- 查询所有学生的成绩，包括没有成绩的学生
-- 左连接join 前面的表示为左表，join 后面的表示为右表
select * from stu left join scores on stu.studenNo=scores.studenNo
-- 查询所有学生的成绩，包括没有成绩的学生,显示课程名
select * from stu left join scores on stu.studenNo=scores.studenNo
left join courses on courses.corseNO=scores.corseNo
