mysql 初级练习题

时间：2023-02-23 17:35:52

1.题目

第一题：

tb_user：

User_id

User_name

User_phone

1

张三

13800138000

2

李四

13800138001

 

tb_customer：

Customer_id

User_id

Customer_name

Company_name

1

1

王先生

中国移动广州分公司

2

2

林先生

中通服建设有限公司

 

tb_project

Project_id

Customer_id

Project_name

1

1

EOMS系统

2

2

ME2.0机务维修系统

 

根据以上三个表，要求输出：

Project_id

User_name

User_phone

Customer_name

Company_name

Project_name

1

张三

13800138000

王先生

中国移动广州分公司

EOMS系统

2

李四

13800138001

林先生

中通服建设有限公司

ME2.0机务维修系统

 

 1.请按上表内容写出对应的sql语句？

 

 

 

2.表结构及数据

mysql 初级练习题mysql 初级练习题
 1 CREATE TABLE `tb_user` (
 2   `id` int(11) DEFAULT NULL,
 3   `user_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
 4   `user_phone` varchar(255) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL
 5 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 6 
 7 INSERT INTO `test`.`tb_user` (`id`, `user_name`, `user_phone`) VALUES ('1', '张三', '13800138000');
 8 INSERT INTO `test`.`tb_user` (`id`, `user_name`, `user_phone`) VALUES ('2', '李四', '13800138001');
 9 
10 
11 
12 CREATE TABLE `tb_customer` (
13   `Customer_id` int(11) DEFAULT NULL,
14   `user_id` int(11) DEFAULT NULL,
15   `Customer_name` varchar(255) DEFAULT NULL,
16   `Company_name` varchar(255) DEFAULT NULL
17 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
18 INSERT INTO `test`.`tb_customer` (`Customer_id`, `user_id`, `Customer_name`, `Company_name`) VALUES ('1', '1', '王先生', '中国移动广州分公司');
19 INSERT INTO `test`.`tb_customer` (`Customer_id`, `user_id`, `Customer_name`, `Company_name`) VALUES ('2', '2', '林先生', '中通服建设有限公司');
20 
21 
22 
23 
24 CREATE TABLE `tb_project` (
25   `Project_id` int(11) DEFAULT NULL,
26   `Customer_id` int(11) DEFAULT NULL,
27   `Project_name` varchar(255) DEFAULT NULL
28 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
29 
30 INSERT INTO `test`.`tb_project` (`Project_id`, `Customer_id`, `Project_name`) VALUES ('1', '1', 'EOMS系统');
31 INSERT INTO `test`.`tb_project` (`Project_id`, `Customer_id`, `Project_name`) VALUES ('2', '2', 'ME2.0机务维修系统');
32 
33 
34 
35 CREATE TABLE `tb_dept` (
36   `Dept_id` int(11) DEFAULT NULL,
37   `Dept_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
38   `Dept_loc` varchar(255) CHARACTER SET utf8 DEFAULT NULL
39 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
40 
41 INSERT INTO `test`.`tb_dept` (`Dept_id`, `Dept_name`, `Dept_loc`) VALUES ('10', '教研部', '北京');
42 INSERT INTO `test`.`tb_dept` (`Dept_id`, `Dept_name`, `Dept_loc`) VALUES ('20', 'IT部', '广州');
43 INSERT INTO `test`.`tb_dept` (`Dept_id`, `Dept_name`, `Dept_loc`) VALUES ('30', '销售部', '深圳');
44 INSERT INTO `test`.`tb_dept` (`Dept_id`, `Dept_name`, `Dept_loc`) VALUES ('40', '财务部', '深圳');
45 INSERT INTO `test`.`tb_dept` (`Dept_id`, `Dept_name`, `Dept_loc`) VALUES ('50', '董事会', '上海');
46 INSERT INTO `test`.`tb_dept` (`Dept_id`, `Dept_name`, `Dept_loc`) VALUES ('60', '行政部', '厦门');
47 
48 
49 CREATE TABLE `tb_emp` (
50   `Emp_id` int(11) DEFAULT NULL,
51   `Emp_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
52   `Job` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
53   `Manage` varchar(255) DEFAULT NULL,
54   `Sal` varchar(255) DEFAULT NULL,
55   `Comm` varchar(255) DEFAULT NULL,
56   `Dept_id` int(11) DEFAULT NULL
57 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
58 
59 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1001', '甘宁', '文员', '1013', '8000.00', 'Null', '20');
60 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1002', '刘备', '经理', '1010', '29750.00', 'Null', '20');
61 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1006', '关羽', '经理', '1010', '24500.00', 'Null', '30');
62 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1007', '张飞', 'Java工程师', '1008', '28500.00', 'Null', '20');
63 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1008', '诸葛亮', '经理', '1004', '30000.00', '14000.00', '40');
64 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1009', '张无忌', '老师', '1011', '5000.00', 'Null', '10');
65 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1010', '张三丰', '董事长', 'Null', '58900.00', 'Null', '50');
66 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1011', '庞统', '经理', '1010', '30000.00', 'Null', '10');
67 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1010', '张三8', '文员', 'Null', '123', 'Null', '20');
68 INSERT INTO `test`.`tb_emp` (`Emp_id`, `Emp_name`, `Job`, `Manage`, `Sal`, `Comm`, `Dept_id`) VALUES ('1010', '张三7', '文员', 'Null', '123', 'Null', '30');
View Code

 

 

第二题：

tb_dept

Dept_id

Dept_name

Dept_loc

10

教研部

北京

20

IT部

广州

30

销售部

深圳

40

财务部

深圳

50

董事会

上海

60

行政部

厦门

 

tb_emp

Emp_id

Emp_name

Job

Manage

Sal

Comm

Dept_id

1001

甘宁

文员

1013

8000.00

Null

20

1002

刘备

经理

1010

29750.00

Null

20

1006

关羽

经理

1010

24500.00

Null

30

1007

张飞

Java工程师

1008

28500.00

Null

20

1008

诸葛亮

经理

1004

30000.00

14000.00

40

1009

张无忌

老师

1011

5000.00

Null

10

1010

张三丰

董事长

Null

58900.00

Null

50

1011

庞统

经理

1010

30000.00

Null

10

1、查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数？

2、列出薪金比关羽高的所有员工？

3、列出所有员工的姓名及其直接上级的姓名 ？

4、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门 ？

5、列出每个部门的员工数量、平均工资 ？

6、列出所有文员的姓名及其部门名称，部门的人数 ？

 

答案：

第一题：
请按上表内容写出对应的sql语句：
答题：
select 
P.Project_id ,
U.User_name,U.User_phone,
c.Customer_name,c.Company_name,
p.Project_name
from tb_user u LEFT JOIN tb_customer c on u.id=c.user_id
LEFT JOIN tb_project p  on c.customer_id= p.customer_id
;
 
第二题：
1、查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数
答题：

select * from 
(
select  d.dept_id ,
                d.dept_name,
                d.dept_loc,
                count(e.emp_id) pNum 
from tb_dept d LEFT JOIN tb_emp e on d.dept_id=e.dept_id
group by d.dept_id
) t where t.pNum>=1 ;
 

2、列出薪金比关羽高的所有员工
答题：
select * from tb_emp
where sal>(
select e.sal from tb_emp e 
where e.emp_name='关羽');
 



3、列出所有员工的姓名及其直接上级的姓名
答题：
方法1：
select 
        #    e.emp_id employeeId,
            e.emp_name employee,
        #    e1.emp_id leaderId    ,
            e1.emp_name leader          
from tb_emp e , tb_emp e1
where e.manage = e1.emp_id 
;
方法2：
select 
leader,
GROUP_CONCAT(employee) employee
from (
select 
        #    e.emp_id employeeId,
            e.emp_name employee,
            e1.emp_id leaderId    ,
            e1.emp_name leader          
from tb_emp e , tb_emp e1
where e.manage = e1.emp_id 
) t
group by  t.leaderId
;

 

4、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
答题：
select * 
from tb_dept d LEFT JOIN tb_emp e on d.dept_id=e.dept_id ;

 

5、列出每个部门的员工数量、平均工资
答题：
select e.dept_id 部门,count(e.emp_id) 员工数量,
ROUND(sum(e.sal)/count(e.emp_id),2) 平均工资 
from tb_emp  e
group by e.dept_id ;
 


6、列出所有文员的姓名及其部门名称，部门的人数
答题：

#列出所有文员的姓名及其部门名称
select e.emp_name,e.dept_id,d.dept_name 
from tb_emp e  ,tb_dept d
where e.dept_id=d.dept_id
and  e.job='文员'

#是文员所在部门下的部门人数，还是部门下文员的人数 ？
select e.dept_id,e.emp_name, d.dept_name,count(e.emp_id)
from tb_emp e  ,tb_dept d
where e.dept_id=d.dept_id
and  e.job='文员' 
group by e.dept_id

 

标签：

相关文章