Discuz! Board

 找回密码
 立即注册
搜索
热搜: 活动 交友 discuz
查看: 483|回复: 2

mysql数据库面试题,可作复习用

[复制链接]

62

主题

360

帖子

1077

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
1077
发表于 2016-7-18 11:59:13 | 显示全部楼层 |阅读模式
1:现有salary和emplyl两表结构及表数据如下,请依据题目写出正确的SQL语句:

/*mysql> select * from salary;
+----------+-------------+---------+------------+-------------+--------+
| salaryid | salary_date | emplyid | basesalary | bonussalary | remark |
+----------+-------------+---------+------------+-------------+--------+
|     1602 | 2016-07-15  |    1000 | 808.00     | 4000.00     | z      |
|     1603 | 2016-07-11  |    1604 | 808.00     | 1200.00     | z      |
|     1604 | 2016-06-01  |    1601 | 1650.00    | 300.00      | NULL   |
+----------+-------------+---------+------------+-------------+--------+
3 rows in set
mysql> select * from emply;
+---------+----------+-----+-----+-----------+---------+--------+
| emplyid | ename    | sex | age | stationid | remark  | deptid |
+---------+----------+-----+-----+-----------+---------+--------+
|    1000 | wangxiao | nv  |  30 |       110 | caiwu   |    111 |
|    1601 | lixiao   | nan |  30 |       111 | jintian |    111 |
|    1604 | wangjuan | nv  |  35 |       115 | caiwu   |     11 |
+---------+----------+-----+-----+-----------+---------+--------+
3 rows in set




1.1)查询部门A(部门id=111)的所有员工id、性别、年龄、部门id、岗位id信息:
mysql> select emplyid,sex,age,stationid,deptid from emply;
+---------+-----+-----+-----------+--------+
| emplyid | sex | age | stationid | deptid |
+---------+-----+-----+-----------+--------+
|    1000 | nv  |  30 |       110 |    111 |
|    1601 | nan |  30 |       111 |    111 |
|    1604 | nv  |  35 |       115 |     11 |
+---------+-----+-----+-----------+--------+
3 rows in set

1.2)查询员工姓名为“王晓”的员工姓名、员工id、部门id、岗位id,
及其所有基本薪水、奖金、薪水发放日期,并按薪水发放日期倒序排序:
sql1:
mysql> select e.ename,e.emplyid,e.deptid,e.stationid,s.basesalary,s.bonussalary,s.salary_date from salary s,emply e where e.emplyid=s.emplyid and e.ename='wangxiao' ORDER BY salary_date desc;
+----------+---------+--------+-----------+------------+-------------+-------------+
| ename    | emplyid | deptid | stationid | basesalary | bonussalary | salary_date |
+----------+---------+--------+-----------+------------+-------------+-------------+
| wangxiao |    1000 |    111 |       110 | 808.00     | 4000.00     | 2016-07-15  |
+----------+---------+--------+-----------+------------+-------------+-------------+
1 row in set

sql2:
mysql> select e.ename,e.emplyid,e.deptid,e.stationid,s.basesalary,s.bonussalary,s.salary_date from salary s inner join emply e on e.emplyid=s.emplyid and e.ename='wangxiao' ORDER BY salary_date desc;
+----------+---------+--------+-----------+------------+-------------+-------------+
| ename    | emplyid | deptid | stationid | basesalary | bonussalary | salary_date |
+----------+---------+--------+-----------+------------+-------------+-------------+
| wangxiao |    1000 |    111 |       110 | 808.00     | 4000.00     | 2016-07-15  |
+----------+---------+--------+-----------+------------+-------------+-------------+
1 row in set

sql3:
select temp.* from (select e.ename,e.emplyid,e.deptid,e.stationid,s.basesalary,s.bonussalary,s.salary_date
from salary s,emply e
where s.emplyid in(select emplyid from emply  where ename='wangxiao') ORDER BY s.salary_date desc)temp where TEMP.ename='wangxiao';


1.3)将一“王”姓、性别为女的员工所有薪水记录的奖金修改为当前奖金的80%,
存在多条“王”姓、性别为女的员工时取员工id最大的一条记录修改:

mysql> select s.bonussalary*0.8 from salary s,(select max(emplyid) as id from emply where ename like 'wang%')temp1 where temp1.id=s.emplyid;
+-------------------+
| s.bonussalary*0.8 |
+-------------------+
| 960.000           |
+-------------------+
1 row in set




1.4)在员工信息表中有记录、但新水表中无该员工信息的员工,
将员工的薪水信息(薪水发放日期为“2016-06-01”,员工id以员工信息表为准,
基本薪水1650,奖金300)插入薪水信息表中:


FAQ1:如何将select语句的查询结果动作insert语句的values的值,以下语句报错:
insert into salary(salary_date,emplyid,basesalary,bonussalary)
values('2016-06-01',select emplyid from emply
where emplyid not in (select emplyid from salary),1650,300);



#1.5)删除姓“李”员工的所有信息(新水表、员工信息表):
delete e,s from emply e,salary s where s.salaryid=e.emplyid and e.ename like 'li%';


动手做,胜过任何完美的想象!
回复

使用道具 举报

62

主题

360

帖子

1077

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
1077
 楼主| 发表于 2016-7-18 12:02:36 | 显示全部楼层
据实可见:
delete 语句删除的对象是实体表(即无法对临时表进行删除)
动手做,胜过任何完美的想象!
回复 支持 反对

使用道具 举报

62

主题

360

帖子

1077

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
1077
 楼主| 发表于 2019-11-16 21:23:03 | 显示全部楼层
mysql 在Linux的在线安装默认是5.1 版本:
而且root用户初始密码是空,修改密码的两种方法:1.外部修改的方法:msyqladmin -u root -password '123456';
2.内部修改方法:先登录:mysql -u root 回车;输入,set password=password('123456');
动手做,胜过任何完美的想象!
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|多测师软件培训|Archiver|手机版|小黑屋|duoceshi Inc. ( 16058051 )

GMT+8, 2019-12-13 15:34 , Processed in 0.095744 second(s), 24 queries .

Powered by Discuz! X3.2 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表