【Java学习20170503】MySQL

MySQL和SQL

导入sql文件

1、xs_kc.sql

/*Navicat MySQL Data TransferSource Server         : tangxinSource Server Version : 50540Source Host           : localhost:3306Source Database       : tangxinTarget Server Type    : MYSQLTarget Server Version : 50540File Encoding         : 65001Date: 2017-05-03 08:51:43*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for xs_kc-- ----------------------------DROP TABLE IF EXISTS `xs_kc`;CREATE TABLE `xs_kc` (  `xs_id` varchar(255) DEFAULT NULL,  `kc_id` varchar(255) DEFAULT NULL,  `kc_cj` varchar(255) DEFAULT NULL,  `kc_xf` varchar(255) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- ------------------------------ Records of xs_kc-- ----------------------------INSERT INTO `xs_kc` VALUES ('81101', '101', '80', '5');INSERT INTO `xs_kc` VALUES ('81101', '102', '78', '4');INSERT INTO `xs_kc` VALUES ('81101', '206', '76', '4');INSERT INTO `xs_kc` VALUES ('81103', '101', '62', '5');INSERT INTO `xs_kc` VALUES ('81103', '102', '70', '4');INSERT INTO `xs_kc` VALUES ('81103', '206', '81', '4');INSERT INTO `xs_kc` VALUES ('81104', '101', '90', '5');INSERT INTO `xs_kc` VALUES ('81104', '102', '84', '4');INSERT INTO `xs_kc` VALUES ('81104', '206', '65', '4');INSERT INTO `xs_kc` VALUES ('81102', '102', '78', '4');INSERT INTO `xs_kc` VALUES ('81102', '206', '78', '4');INSERT INTO `xs_kc` VALUES ('81106', '101', '65', '5');INSERT INTO `xs_kc` VALUES ('81106', '102', '71', '4');INSERT INTO `xs_kc` VALUES ('81108', '206', '87', '4');INSERT INTO `xs_kc` VALUES ('81107', '101', '78', '5');INSERT INTO `xs_kc` VALUES ('81107', '102', '80', '4');INSERT INTO `xs_kc` VALUES ('81107', '206', '68', '4');INSERT INTO `xs_kc` VALUES ('81108', '102', '64', '4');INSERT INTO `xs_kc` VALUES ('81108', '101', '85', '5');INSERT INTO `xs_kc` VALUES ('81109', '101', '66', '5');INSERT INTO `xs_kc` VALUES ('81109', '102', '83', '4');INSERT INTO `xs_kc` VALUES ('81109', '206', '70', '4');INSERT INTO `xs_kc` VALUES ('81110', '101', '95', '5');INSERT INTO `xs_kc` VALUES ('81110', '102', '90', '4');INSERT INTO `xs_kc` VALUES ('81110', '206', '89', '4');INSERT INTO `xs_kc` VALUES ('81111', '101', '91', '5');INSERT INTO `xs_kc` VALUES ('81111', '102', '70', '4');INSERT INTO `xs_kc` VALUES ('81111', '206', '76', '4');INSERT INTO `xs_kc` VALUES ('81113', '101', '63', '5');INSERT INTO `xs_kc` VALUES ('81113', '102', '79', '4');INSERT INTO `xs_kc` VALUES ('81113', '206', '60', '4');INSERT INTO `xs_kc` VALUES ('81201', '101', '80', '5');INSERT INTO `xs_kc` VALUES ('81202', '101', '65', '5');INSERT INTO `xs_kc` VALUES ('81203', '101', '87', '5');INSERT INTO `xs_kc` VALUES ('81204', '101', '87', '5');

2、kc.sql

/*Navicat MySQL Data TransferSource Server         : tangxinSource Server Version : 50540Source Host           : localhost:3306Source Database       : tangxinTarget Server Type    : MYSQLTarget Server Version : 50540File Encoding         : 65001Date: 2017-05-03 08:51:26*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for kc-- ----------------------------DROP TABLE IF EXISTS `kc`;CREATE TABLE `kc` (  `kc_id` varchar(255) DEFAULT NULL,  `kc_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,  `kc_rq` varchar(255) DEFAULT NULL,  `kc_xs` varchar(255) DEFAULT NULL,  `kc_xf` varchar(255) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- ------------------------------ Records of kc-- ----------------------------INSERT INTO `kc` VALUES ('101', '计算机基础', '1', '80', '5');INSERT INTO `kc` VALUES ('102', '程序设计及语言', '2', '68', '4');INSERT INTO `kc` VALUES ('206', '离散数学', '4', '68', '4');INSERT INTO `kc` VALUES ('208', '数据结构', '5', '68', '4');INSERT INTO `kc` VALUES ('209', '操作系统', '6', '68', '4');INSERT INTO `kc` VALUES ('210', '计算机原理', '5', '85', '5');INSERT INTO `kc` VALUES ('212', '数据库原理', '7', '68', '4');INSERT INTO `kc` VALUES ('301', '计算机网络', '7', '51', '3');INSERT INTO `kc` VALUES ('302', '软件工程', '7', '51', '3');

3、xs.sql

/*Navicat MySQL Data TransferSource Server         : tangxinSource Server Version : 50540Source Host           : localhost:3306Source Database       : tangxinTarget Server Type    : MYSQLTarget Server Version : 50540File Encoding         : 65001Date: 2017-05-03 08:51:34*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for xs-- ----------------------------DROP TABLE IF EXISTS `xs`;CREATE TABLE `xs` (  `xs_id` int(11) DEFAULT NULL,  `xs_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,  `xs_zy` varchar(255) CHARACTER SET utf8 DEFAULT NULL,  `xs_xb` varchar(255) CHARACTER SET utf8 DEFAULT NULL,  `xs_rq` varchar(255) DEFAULT NULL,  `xs_xf` int(11) DEFAULT NULL,  `xs_bz` varchar(255) CHARACTER SET utf8 DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- ------------------------------ Records of xs-- ----------------------------INSERT INTO `xs` VALUES ('81101', '??1', '???', '1', '1990-02-10', '50', '');INSERT INTO `xs` VALUES ('81102', '程明', '计算机', '1', '1991-02-01', '50', null);INSERT INTO `xs` VALUES ('81103', '王燕', '计算机', '0', '1989-10-06', '50', null);INSERT INTO `xs` VALUES ('81104', '伟严平', '计算机', '1', '1990-08-26', '50', null);INSERT INTO `xs` VALUES ('81106', '李方方', '计算机', '1', '1990-11-20', '50', null);INSERT INTO `xs` VALUES ('81107', '李明', '计算机', '1', '1990-05-01', '54', '提前修完《数据结构》，并获得学分');INSERT INTO `xs` VALUES ('81108', '林一凡', '计算机', '1', '1989-08-05', '52', '提前修完一门课程');INSERT INTO `xs` VALUES ('81109', '张强民', '计算机', '1', '1989-08-11', '50', null);INSERT INTO `xs` VALUES ('81110', '张蔚', '计算机', '0', '1991-07-22', '50', null);INSERT INTO `xs` VALUES ('81111', '赵琳', '计算机', '0', '1990-03-18', '50', null);INSERT INTO `xs` VALUES ('81113', '严红', '计算机', '1', '1989-08-11', '48', '一门课不及格');INSERT INTO `xs` VALUES ('81201', '王敏', '通讯工程', '0', '1989-06-10', '42', null);INSERT INTO `xs` VALUES ('81205', '李记', '通讯工程', '1', '1989-09-10', '42', null);INSERT INTO `xs` VALUES ('81203', '吴薇华', '通讯工程', '0', '1990-03-18', '42', null);INSERT INTO `xs` VALUES ('81204', '刘燕敏', '通讯工程', '0', '1989-11-12', '42', null);

查询：

-- 查询王燕的信息SELECT * FROM xs as stu WHERE stu.xs_name ='王燕';-- 查询学生表里面专业为计算机并且是女士的人员信息SELECT * FROM xs WHERE xs.xs_zy ='计算机' OR xs.xs_xb='0';-- 查询并重命名SELECT xs_id as '学号',xs_name as '姓名',xs_zy as '专业',xs_rq as '出生日期',xs_xf as '学分',xs_bz as '备注' FROMxs WHERE xs_name='王燕';-- 模糊查询发 '-'代表一个字符，'%代表多个字符SELECT * FROM xs WHERE xs_name LIKE '张_';SELECT * FROM xs WHERE xs_name LIKE '李%';SELECT * FROM xs WHERE xs_name LIKE '张_民';-- 查询在某一个范围内between A AND b：查询a和b之间的数据SELECT * FROM xs WHERE xs_rq BETWEEN '1990-02-10' and '1990-12-13';-- 等价于SELECT * FROM xs WHERE xs_rq > '1990-01-01' and xs_rq < '1990-12-31';-- 指定具体几个范围数值SELECT * FROM xs WHERE xs_rq in('1990-02-10','1990-11-20');-- 不在指定范围内的SELECT * FROM xs WHERE xs_rq NOT in('1990-02-10','1990-11-20');-- 空值判断 is NULLSELECT * FROM xs WHERE xs_bz is null;-- 非空判定SELECT * FROM xs WHERE xs_bz is not null;-- 去重复 distinct 去重复，现实名字与id，过滤掉名字相同的数据？SELECT DISTINCT * from xs WHERE xs_xb='1';  -- 对查询结果进行排序  asc:正序   desc：倒序SELECT * FROM xs ORDER BY xs_name asc;SELECT * FROM xs ORDER BY xs_rq ASC;SELECT * from xs ORDER BY xs_name DESC;-- 复合排序 先按专业排序，当专业相同时，按日期排序SELECT * FROM xs ORDER BY xs_xf,xs_rq;-- 查询前几条记录  LIMIT-- 查询学号小于81110的学生信息SELECT * FROM xs WHERE xs_id<81110 LIMIT 5;-- 查询学号小于81110的学生信息，列出从下标为2开始的三个学生信息，下标从0 开始SELECT * FROM xs WHERE xs_id<81110 LIMIT 2,3;-- 分组查询 GROUP BY 将查询结果分为男女两个组SELECT * FROM xs WHERE xs_id<81110 GROUP BY xs_xb;-- 查询所有学生中男生和女生年龄最大的两个学生的信息-- 先将学生分组，获得男生、女生最大的日期SELECT MIN(xs_rq) FROM xs GROUP BY xs_xb;-- select xs.xs,xs.xs_name,xs.xs_xb,max(xs_rq) as '出生日期' FROM xs GROUP BY xs_xb;-- 将上一步的查询结果作为条件，合并SELECT * FROM xs WHERE xs_rq in(SELECT min(xs_rq) FROM xs GROUP BY xs_xb);-- having 语句的用法 ---where 语句里面不能有统计函数，而且where条件不能对group by进行分组SELECT * FROM xs WHERE xs_id<81110 GROUP BY xs_xb;SELECT * FROM xs HAVING xs_xf>50;-- having 一般与group by结合使用-- 查询所有学生的平均成绩SELECT xs_id as '学号',AVG(kc_cj) as '平均成绩' FROM xs_kc GROUP BY xs_id;-- 查询平均成绩大于80的学生信息-- 1、查询平均成绩大于80的学生idSELECT xs_id,AVG(kc_cj) FROM xs_kc GROUP BY xs_id HAVING AVG(kc_cj)>80;-- 2、利用查询到的id查询学生的基本信息SELECT * FROM xs WHERE xs_id in(SELECT xs_id FROM xs_kc GROUP BY xs_id HAVING AVG(kc_cj)>80);-- 多表查询-- 查询所有学生的课程信息SELECT xs.xs_name,kc_name,xs_kc.kc_cj FROM xs,xs_kc,kc WHERE xs_kc.kc_id=kc.kc_id AND xs_kc.xs_id = xs.xs_id;-- 多表查询-- 查询选修了206课程且成绩在80分以上的学生姓名和成绩SELECT xs.xs_id,xs.xs_name,xs_kc.kc_cj FROM xs,xs_kc WHERE xs_kc.kc_id=206 and xs_kc.kc_cj>80 and xs.xs_id =xs_kc.xs_id ;-- jion on 当前数据表a连接数据表b，指定a和b的数据关系  写法：a jion b on a.id=b.id SELECT xs.xs_name,xs_kc.kc_cj FROM xs JOIN xs_kc on xs.xs_id=xs_kc.xs_id WHERE xs_kc.kc_cj>80 AND xs_kc.kc_id=206;

-- LEFT OUTER JOIN :左外连接。。。在结果表里除了匹配外，还包括左表有的但右表不匹配的行，对于这样的行，从右表被选择的列设置为NULL-- 查询所有学生情况及他们选修的课程号，若学生未选修任何课，也要包括其情况select xs.*,kc_id from xs left outer join xs_kc on xs.xs_id = xs_kc.xs_id;-- 交叉连接：cross join范例：列出所有学生所有可能的选课情况select xs.xs_id as '学号',xs_name as '姓名',kc.kc_id as '课程号',kc.kc_name as '课程名' from xs cross join kc;-- 去重复distinct 范例：查找所有学生选过的课程名和课程号select distinct xs_kc.kc_id as '课程号',kc.kc_name as '课程名' from xs_kc,kc where kc.kc_id=xs_kc.kc_id;-- 查询所有课程中没有学生选的课程-- 1、查询学生课程表里所有被选择的课程idselect distinct xs_kc.kc_id from xs_kc;-- 2、查询所有课程中没有在某一个范围内的课程【有人的课程】select * from kc where kc.kc_id not in (select distinct xs_kc.kc_id from xs_kc);-- 替换查询结果中的数据select xs.xs_id as '学号',xs.xs_name as '姓名',xs.xs_xf as '学分', case when xs.xs_xf is null then '未选课'when xs.xs_xf < 50 then '不及格'when xs.xs_xf >=50 and xs.xs_xf<=52 then '合格'else '优秀'end as '等级'from xs where xs.xs_zy='计算机';语法格式：case when 条件 then 结果when 条件 then 结果when 条件 then 结果else 结果ENDas 给当前字段取的别名