mysql练习

表名：worker-- 表中字段均为中文，比如 部门号 工资 职工号 参加工作 等
CREATE TABLE `worker` (
 `部门号` int(11) NOT NULL,
 `职工号` int(11) NOT NULL,
 `工作时间` date NOT NULL,
 `工资` float(8,2) NOT NULL,
 `政治面貌` varchar(10) NOT NULL DEFAULT '群众',
 `姓名` varchar(20) NOT NULL,
 `出生日期` date NOT NULL,

1.显示所有职工的基本信息。

Select * from wroker;     

2.查询所有职工所属部门的部门号，不显示重复的部门号。

select distinct `部门号` from worker;  

3.求出所有职工的人数。

select count(`姓名`) from worker;  

4.列出最高工和最低工资。

select max(`工资`),min(`工资`) from worker;   

5.列出职工的平均工资和总工资。

select sum(`工资`),avg(`工资`) from worker;   

6.创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。

 create table `工作日期表`(

    `职工号` int(10) not null,

    `姓名` varchar(50) not null,

    `参加工作` varchar(50) not null

    );

7.显示所有女职工的年龄。

 Select age from worker where sex = `女`

8.列出所有姓刘的职工的职工号、姓名和出生日期。

select `职工号`,`姓名`,`出生日期` from worker where `姓名` like '刘%';

9.列出1960年以前出生的职工的姓名、参加工作日期。

select `姓名`,`工作时间` from worker where `出生日期` < '1960-01-01';

10.列出工资在1000－2000之间的所有职工姓名。

 select `姓名` from worker where `工资` between 1000 and 2000;

11.列出所有陈姓和李姓的职工姓名。

 select `姓名` from worker where `姓名` like '陈%' or `姓名` like '李%';

12.列出所有部门号为2和3的职工号、姓名、党员否。

select `职工号`,`姓名`,`政治面貌` from worker where `部门号` like '%2' or `部门号` like '%3';  

13.将职工表worker中的职工按出生的先后顺序排序。

select * from worker order by `出生日期` asc;

14.显示工资最高的前3名职工的职工号和姓名。

select `职工号`,`姓名`,`工资` from worker order by `工资` desc limit 3;

15.求出各部门党员的人数。

select count(`政治面貌`) from worker where `政治面貌` = '党员';

16.统计各部门的工资和平均工资

select `部门号`,sum(`工资`),avg(`工资`) from worker group by `部门号`;

17.列出总人数大于4的部门号和总人数

select `部门号`,count(`部门号`) from worker group by `部门号` having count(`部门号`) > 4;

1.创建student和score表
CREATE  TABLE student (
id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY ,
name  VARCHAR(20)  NOT NULL ,
sex  VARCHAR(4) ,
birth  YEAR,
department  VARCHAR(20) ,
address  VARCHAR(50)
);
创建score表。SQL代码如下：
CREATE  TABLE score (
id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  AUTO_INCREMENT ,
stu_id  INT(10)  NOT NULL ,
c_name  VARCHAR(20) ,
grade  INT(10)
);

1.查询student表的所有记录

Select * from student;

2.查询student表的第2条到4条记录

Select * from student limit 1,3;

3.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

Select id,name,department from student;

4.从student表中查询计算机系和英语系的学生的信息

select * from student where department in ('计算机系','英语系');

5.从student表中查询年龄18~22岁的学生信息

Select * from student where age between 18 and 22;

6.从student表中查询每个院系有多少人

select department,count(id) from student group by department;

7.从score表中查询每个科目的最高分

select c_name,max(grade) from score group by c_name;

8.查询李四的考试科目（c_name）和考试成绩（grade）

select c_name,grade from score inner join student on score.stu_id = student.id where name = '李四';

9.用连接的方式查询所有学生的信息和考试信息

select * from student inner join score on student.id = score.stu_id;

10.计算每个学生的总成绩

select name,sum(score.grade) from student inner join score on student.id = score.stu_id group by name;

11.计算每个考试科目的平均成绩

select c_name,avg(grade) from score group by c_name;

12.查询计算机成绩低于95的学生信息

select student.* from student inner join score on student.id = score.stu_id where score.c_name = '计算机' and score.grade < 95;

13.查询同时参加计算机和英语考试的学生的信息

select a.* from student a,score b,score c where a.id = b.stu_id and b.c_name = '计算机' and a.id = c.stu_id and c.c_name = '英语';

14.将计算机考试成绩按从高到低进行排序

Select c_name,grade from score where c_name = `计算机` order by grade desc;

15.从student表和score表中查询出学生的学号，然后合并查询结果

select id from student union select stu_id from score;

16.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

 select name,department,a.c_name,a.grade from student inner join score a on student.id = a.stu_id where name like '张%' or name like '王%';

17.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

select name,year(now())-birth as `年龄`,department,a.c_name,a.grade from student inner join score a on student.id = a.stu_id where address like '湖南%';

面试题：

第一题：

第一道面试题：
1、
select s_id,s_name,count(c_id),sum(s_score) from (select * from student inner join score on(student.s_id=score.s_id));
2、
select s_id,s_name from (select * from (select * from student as stu inner join score as sc on(stu.s_id=sc.s_id)) as stu inner join course as cou on(stu.c_id=cou.c_id)) as stu inner join teacheer as tea on(stu.t_id=tea.t_id) where t_name='张三';
3、 
select * from (select * from student as stu inner join score as sc on(stu.s_id=sc.s_id)) as stu inner join (select * from (select * from student inner join score on(student.s_id=score.s_id)) where s_id='02') as stu02 on(stu02.c_id=stu.c_id);
4、
select s_id as 学生ID,c_id='04' as 数据库,c_id='01' as 企业管理,c_id='06' as 英语,count(c_id) as 有效课程数,sum(s_score) as 有效平均分 from (select * from student inner join score on(student.s_id=score.s_id));

5.

select

a.c_id,
 b.c_name,
 sum( CASE WHEN 成绩 BETWEEN 85 AND 100 THEN 1 ELSE 0 END ) AS '[100-85]',
 sum( CASE WHEN 成绩 >= 70 AND 成绩 < 85 THEN 1 ELSE 0 END ) AS '[85-70]',
 sum( CASE WHEN 成绩 >= 60 AND 成绩 < 70 THEN 1 ELSE 0 END ) AS '[70-60]',
 sum( CASE WHEN 成绩 < 60 THEN 1 ELSE 0 END ) AS '[<60]' 
from
score AS a
RIGHT JOIN course AS b ON a.c_id = b.c_name 
group by
 a.c_id,
 b.c_name;

第二题：

第二道面试题：
select dept_no,dept_name,count(dept_no) from departments as depa inner join (select * from dept_emp as dept inner join salaries as sal on(dept.emp_no=sal.emp_no)) as s_table on(s_table.dept_no=depa.dept_no) order by dept_no;