【Java学习20170510】MySQL考试 _陈述

MySQL复习
导入xs_kc.sql
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');
导入xs.sql
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);
导入kc.sql
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');
查询语句
-- 查询王燕的信息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 '备注' FROM xs 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 给当前字段取的别名-- 统计101课程有多少个学生选择select count(xs_kc.kc_id) from xs_kc where xs_kc.kc_id ='101'-- 删除delete from test where name='a';
查询作业
SELECT xs_id as '学号',AVG(kc_cj) AS '平均成绩' FROM xs_kc GROUP BY xs_id;-- 修改上面的语句，使之能显示id，名字，平均成绩SELECT xs.xs_id AS '学号',xs_name AS '名字',AVG(kc_cj) AS '平均成绩' FROM xs,xs_kc WHERE xs.xs_id=xs_kc.xs_id GROUP BY xs.xs_id;-- 查询学生的平均成绩，xs_id,xs_name,avg(kc_cj)-- 查询修了“计算机基础” 课程并且成绩在80分以上的学生学号、姓名、课程名及成绩-- 查询数据库中课程不同、成绩相同的学生的学好、课程号及成绩SELECT xs.xs_id AS '学好',xs.xs_name AS '名字',xs_kc.kc_cj AS '成绩' FROM xs,xs_kc WHERE xs_kc.kc_cj>80 AND xs_kc.kc_id=101 and xs.xs_id =xs_kc.xs_id;-- 查询学生的平均成绩，xs_id,xs_name,avg(kc_cj)SELECT xs.xs_id as '学号',xs.xs_name as '姓名',avg(xs_kc.kc_cj) AS '平均成绩' FROM xs JOIN xs_kc ON xs.xs_id = xs_kc.xs_id GROUP BY xs.xs_id;-- 查询修了“计算机基础” 课程并且成绩在80分以上的学生学号、姓名、课程名及成绩SELECT xs.xs_id as '学号',xs.xs_name as '姓名',kc.kc_name as '课程名',xs_kc.kc_cj as '成绩' FROM xs join xs_kc on xs.xs_id=xs_kc.xs_id JOIN kc ON xs_kc.kc_id=kc.kc_id WHERE kc.kc_name='计算机基础' and xs_kc.kc_cj>80;-- 查询数据库中课程不同、成绩相同的学生的学好、课程号及成绩SELECT a.xs_id as '学号',a.kc_id as '课程号',a.kc_cj as '成绩' from xs_kc as a join xs_kc as b on a.xs_id=b.xs_id where a.kc_id<>b.kc_id AND a.kc_cj=b.kc_cj;SELECT * FROM xs join xs_kc on xs.xs_id=xs_kc.xs_id JOIN kc ON xs_kc.kc_id=kc.kc_id ;SELECT * from xs_kc as a join xs_kc as b on a.xs_id=b.xs_id ;
考试
数据库部分考试
1、列出居住在MA或者DC州的所有的出版商，其语句是 ( b )
A、,City FROMWHERE State = （‘MA’，‘DC’）
B、 ,City FROMWHERE State IN （‘MA’，‘DC’）
C、 ,City FROMWHERE State‘MA’ AND‘DC’
D、 ,City FROMWHERE State=‘MA’ AND State=‘DC’
2、公司中有多个部门和多名职员，每个职员只能属于一个部门，一个部门可以有多名职员，从职员到部门的联系类型是 ( c )
A、多对多
B、一对一
C、多对一
D、一对多
3、陈述A：任何两个NULL值都不可能相等。陈述B：在以升序排序的输出中，NULL值总是第一个被显示的项。关于对以上陈述的描述中，正确的是 (b )
A、陈述A正确，陈述B错误
B、陈述A错误，陈述B正确
C、都正确
D、都是错误的
脚本语言空值可以进行对比，sql也是脚本语言，（弱类型语言？）
4、有一表：DEPT (dno,dname) ,如果要找出倒数第三个字母为W，并且至少包含4个字母的dname，则查询条件子句应写成where dname like ( b )
A、’_ _W_% ’
B、’_%W_ _’
C、’_W_ ’
D、’_W_%’
%零个或多个，_任意一个
5、要想对表中记录分组查询，可以使用哪一子句？ ( a )
A、GROUP BY
B、AS GROUP

文章插图
C、GROUP AS
D、TO GROUP
6、陈述A：型数据表示固定长字符数据。陈述B：char型数据表示不定长字符数据。关于对以上陈述的描述中，正确的是 ( a )
A、都是错误的
B、都正确
C、陈述A正确，陈述B错误
D、陈述A错误，陈述B正确
7、下列哪一种连接保证包含第一个表中的所有行和第二个表中的所有匹配行 ( a )
A、LEFT OUTER JOIN
B、RIGHT OUTER JOIN
【【Java学习20170510】MySQL考试】C、INNER JOIN
D、JOIN
8、SQL 查询中去除重复数据的是 ( c )
A、ORDER BY
B、GROUP BY
C、
D、DESC
9、在SQL 中，语句的完整语法较复杂，但至少包括 ( d )
A、 ,INTO
B、, FROM
C、, GROUP
D、仅
10、使用语句检索带特定条件的数据时，应使用的关键字是 ( d )
A、ORDER
B、
C、LIKE
D、WHERE
11、下列语句中，创建关系表的是 ( b )
A、ALTER
B、
C、
D、
12、使用语句显示表的所有的列时，选用的符号是 ( a )
A、*
B、[]
C、_
D、[^]
13、进行分组检索时，为了去掉不满足条件的分组，应当 ( b )
A、只需要使用WHERE
B、在GROUP BY后面使用
C、先使用WHERE，再使用
D、先使用，再使用WHERE
14、null 等于 ( b )
A、0
B、空白
C、不确定
D、无意义
15、测验中分数在80到100之间的候选人需要面试，合适的查询语句是 ( a)
A、 ,, FROM>=80 ANDAND
C、 ,, FROM=100
D、 ,, FROM
数据库中下标1开始查询（异类），Str.(3,6)前闭后开
横纵表转换？？？？？
上机题
a.建立一个公司数据库（）
b.建立一张部门表（部门编号b_id,部门名称), 其中b_id为主键,自增长，不允许为空

文章插图
c.建立一张员工表(员工编号y_id,姓名,性别y_sex,年龄y_age,住址,部门编号b_id), 其中y_id为主键自增长。住址默认为’不详’，b_id依赖于部门表的b_id

文章插图
录入插入相关数据然后执行下列查询语句：
1、查询年龄在25至30岁之间的男员工的姓名和住址。
2、查询财务部所有40岁以下男员工的所有信息
3、查询人事部年龄最大的女员工姓名
4、2号财务部新到一名员工，已知姓名，性别，年龄，将此员工加入到员工表
5、在员工表中，将人事部年龄大于30岁的女同事，调到后勤部
6：查询每个部门年龄最大的员工，显示部门名字和年龄。
7：查询每个部门各有多少人，显示部门名字和人数，按人数倒序，
如果人数相同，按部门编号正序。
8：将张三的的名字改为李四，并调到财务部。
9：将后勤部年龄大于60岁的员工删除。
10：查询财务部年龄不在20-30之间的男生信息。
答案

create database gongsi default charset utf8 COLLATE utf8_general_ci;create table bumen (b_id int primary key auto_increment , b_name varchar(32))create table yuangong (y_id int primary key auto_increment , y_name varchar(32),y_sex varchar(10),y_age tinyint,y_address varchar(255) default '不详',b_id int,constraint fk_b_id foreign key (b_id) references bumen(b_id))insert into bumen(b_name) values("财务部");insert into bumen(b_name) values("后勤部");insert into bumen(b_name) values("人事部");insert into bumen(b_name) values("研发部")select * from bumen;insert into yuangong (y_id,y_name,y_sex,y_age,y_address,b_id) values (null,'王大锤','男',28,'天堂路',1);insert into yuangong (y_id,y_name,y_sex,y_age,y_address,b_id) values (null,'王璐','女',26,null,3);insert into yuangong (y_id,y_name,y_sex,y_age,b_id) values (null,'张三','男',38,1);insert into yuangong (y_id,y_name,y_sex,y_age,y_address,b_id) values (null,'张瑶','女',28,null,3);insert into yuangong (y_id,y_name,y_sex,y_age,y_address,b_id) values (null,'阿朱','女',34,null,3);insert into yuangong (y_id,y_name,y_sex,y_age,y_address,b_id) values (null,'扫地僧','男',63,'天堂路',2);insert into yuangong (y_id,y_name,y_sex,y_age,y_address,b_id) values (null,'乔峰','男',31,'天堂路',4);insert into yuangong (y_id,y_name,y_sex,y_age,y_address,b_id) values (null,'虚竹','男',30,'天堂路',1);insert into yuangong (y_id,y_name,y_sex,y_age,y_address,b_id) values (null,'张三疯','男',28,'天堂路',1);select * from yuangong;-- 第一题select y_name, y_address from yuangong where y_sex = '男' and y_age BETWEEN 25 and 30;select y_name, y_address from yuangong where y_sex = '男' and y_age >= 25 and y_age <= 30;-- 第二题select b_id from bumen where b_name='财务部'select * from yuangong where b_id = (select b_id from bumen where b_name='财务部') and y_age <= 40 and y_sex = '男';select y.y_id, y.y_name, y.y_sex, y.y_age, y.y_address frombumen as b left join yuangong as yon y.b_id = b.b_id where y.y_age <=40 and y.y_sex='男' and b.b_name='财务部';-- 第三题select b_id from bumen where b_name='人事部'select max(y_age) from yuangong where y_sex = '女';select y_name from yuangong where b_id = (select b_id from bumen where b_name='人事部') and y_sex='女' order by y_age DESC LIMIT 1set @d=now(); select y_name from yuangong where b_id = (select b_id from bumen where b_name='人事部') and y_age = (select max(y_age) from yuangong where y_sex = '女');select timestampdiff(second,@d,now()); select y_id, y_name, y_sex, y_age, y_address from yuangong left join bumen on yuangong.b_id = bumen.b_id where y_sex='女'and b_name='人事部'order by y_age DESC LIMIT 1;-- 第四题select b_id from bumen where b_name = '财务部'insert into yuangong(y_name, y_sex, y_age, b_id) values('唐鑫', '女', 18, 1)select * from yuangong;-- 第五题select b_id from bumen where b_name = '人事部';select b_id from bumen where b_name = '后勤部'update yuangong set b_id = (select b_id from bumen where b_name = '人事部') where b_id=(select b_id from bumen where b_name = '后勤部') and y_sex = "女" and y_age >= 30;-- 第六题select * from yuangong;-- select max(y_age) from yuangong group by b_id;select y_name, y_age, b_name from yuangong, bumen where yuangong.b_id = bumen.b_id and y_age in (select max(y_age) from yuangong group by b_id)-- 第七题SELECT * from bumen;-- select * from yuangong;select count(*) as 人数, b_name as 部门 from yuangong, bumen where yuangong.b_id = bumen.b_id group by yuangong.b_id order by 人数 DESC, yuangong.b_id DESC;-- 第八题将张三的的名字改为李四，并调到财务部select b_id from bumen where b_name = '财务部'UPDATE yuangong set y_name='李四', b_id=(select b_id from bumen where b_name = '财务部') where y_name='张三' select * from yuangong;-- 第九题将后勤部年龄大于60岁的员工删除DELETE from yuangong WHERE y_age >= 60 and b_id = (select b_id from bumen where b_name in('后勤部', '财务部'))select * from yuangong;-- 第十题查询财务部年龄不在20-30之间的男生信息SELECT * from yuangong where y_age >= 30 or y_age <= 20SELECT * from yuangong where b_id = (select b_id from bumen where b_name = '财务部') and y_age not BETWEEN 20 and 30 and y_sex='男';

自己回答

-- 1、查询年龄在25至30岁之间的男员工的姓名和住址 。select yg.y_name,yg.y_address from yg where yg.y_age >25 and yg.y_age<30;-- 2、查询财务部所有40岁以下男员工的所有信息select * from yg where yg.b_id=1 and yg.y_sex='男' and yg.y_age<40;-- 3、查询人事部年龄最大的女员工姓名select yg.y_name from yg where yg.y_age in (select MAX(yg.y_age) from yg where yg.b_id =3 and yg.y_sex='女') and yg.y_sex='女';-- 4、2号财务部信道一名员工，已知姓名，性别，年龄，将此员工加入到员工表insert into yg values(10,'段誉','男','18',NULL,1);-- 5、在员工表中，将人事部年龄大于30岁的女同事，调到后勤部update yg set yg.b_id='2' where yg.y_age>30 and yg.y_sex='女'; -- 6、查询每个部门年龄最大的员工，显示部门名字和年龄 。select a.b_name,b.c from bm as a join ( select MAX(yg.y_age) as c,yg.b_id from yg,bm group by yg.b_id) as b on a.b_id=b.b_id;-- 7、查询每个部门各有多少人，显示部门名字和人数，按人数倒序，如果人数相同，按部门编号正序 。select a.b_name,b.c from bm as a join (select count(*) as c,yg.b_id from yg group byyg.b_id ) as b on a.b_id=b.b_id ORDER BY c DESC ;-- 8、将张三的名字改为李四，并调到财务部 。update yg set yg.y_name='李四',yg.b_id=1 where yg.y_name='张三';-- 9、将后勤部年龄大于60岁的员工删除 。delete from yg where yg.y_age>60 and yg.b_id = 2;-- 10、查询财务部年龄不在20-30之间的男生信息 。select * from yg where (yg.y_age >30 or yg.y_age <20) and yg.y_sex='男';