JDBC事务练习 MYSQL增删改查

1.安装mysql数据库

2.创建一个mydb1数据库，并查看

3.创建一张表，表名employee ，表结构如下

6.插入如下数据

7.将研发部员工的薪水修改为2500

UPDATE employee SET salary = 2500 WHERE department = '研发部'

8.将姓名为”阿紫”的员工薪水修改为3000元。

UPDATE employee SET salary = 3000 WHERE name ='阿紫'

9.将姓名为”赵灵儿”的员工薪水修改为4000元,sex改为female。

UPDATE employee SET sex = 'female',salary = 4000 WHERE name ='赵灵儿'

10.公司统一给每位员工再发奖金500

UPDATE employee SET bonus = bonus + 500

11.将”张三丰”的薪水在原有基础上增加1000元。

UPDATE employee SET salary = salary + 1000 WHERE name = '张三丰'

12.查询表中所有员工的信息。

SELECT * FROM employee

13.查询表中所有员工的姓名和对应的薪水。

SELECT name,salary FROM employee 

14.使用汉语展示员工信息。(列名翻译成中文展示)

SELECT name 姓名, sex 性别 ,birthday 生日,salary 工资, bonus 奖金, department 部门, resume 备注 FROM employee

15.查询姓名为”杨过”的员工的薪水

SELECT salary FROM employee WHERE name = '杨过' 

16.查询姓名为”杨过”的员工的总收入

SELECT SUM(salary+bonus) FROM employee WHERE NAME ='杨过'

17.查询薪水大于3000的员工信息

SELECT * FROM employee WHERE sex = 'female' and salary > 3000

18.查询总收入大于4000的员工的姓名 部门 薪水 奖金

SELECT name,department,salary,bonus FROM employee WHERE salary+bonus>4000

19.查询80后的员工

SELECT * FROM employee WHERE birthday BETWEEN '1980-0-0' and '1989-12-31'

20.查询所有女性薪水大于4000的员工

SELECT * FROM employee WHERE sex = 'female' AND salary>4000

21…查询所有女性薪水大于4000的员工按薪水降序排列

SELECT * FROM employee WHERE sex = 'female' AND salary>4000 ORDER BY salary DESC

22.查询各个部门员工数量

SELECT COUNT(*),department FROM employee GROUP BY department

23.查询各个部门的员工数量及薪水支出

SELECT COUNT(*),department,salary FROM employee GROUP BY department

24.查询各个部门的员工数量及总支出

SELECT COUNT(*),department,SUM(salary+bonus) FROM employee GROUP BY department

25.查询公司男性和女性的数量

SELECT COUNT(*),sex FROM employee GROUP BY sex

26.查询公司男性员工和女性员工的收入总和

SELECT COUNT(*),sex,SUM(salary+bonus) FROM employee GROUP BY sex

27.查询公司中男性员工的收入总和

SELECT SUM(salary+bonus) FROM employee WHERE sex = 'male'

28.查询公司中总支出大于9000的部门

SELECT SUM(salary+bonus) as coo,department FROM employee GROUP BY department HAVING coo > 9000

29.查询公司中所有”张”姓员工的平均工资

SELECT AVG(salary) FROM employee WHERE name LIKE '张%'

30.查询公司中”张”姓员工的工资总和

SELECT sum(salary) FROM employee WHERE name LIKE '张%'

31.查询公司中”张”姓员工的总收入

SELECT sum(salary+bonus) FROM employee WHERE name LIKE '张%'