-
安装mysql数据库
-
创建一个mydb1数据库,并查看。
-
创建一张表,表名employee ,表结构如下
*字段名* | *字段类型* | *长度* |
---|---|---|
id | 整形 | 11 |
name | 字符串 | 20 |
sex | 字符串 | 10 |
birthday | 日期 | |
salary | 浮点型 | 10(2位小数) |
bonus | 浮点型 | 10(2位小数) |
department | 字符串 | 20 |
resume | 字符串 |
- 插入如下数据
*id* | *name* | *sex* | *birthday* | *salary* | *bonus* | *department* | *resume* |
---|---|---|---|---|---|---|---|
1 | 杨过 | male | 1980-11-25 | 2000 | 100 | 总裁办 | good body |
2 | 李逍遥 | male | 1980-04-25 | 1000 | 200 | 研发部 | good body |
3 | 小龙女 | female | 1978-11-25 | 4000 | 100 | 财务部 | good girl |
4 | 阿紫 | male | 1981-01-25 | 4000 | 400 | 人事部 | good girl |
5 | 王语嫣 | male | 1978-12-25 | 2000 | 人事部 | good girl | |
6 | 赵灵儿 | female | 1998-05-25 | 2000 | 100 | 人事部 | good girl |
7 | 任盈盈 | male | 1968-10-25 | 3000 | 500 | 财务部 | good girl |
8 | 张三丰 | male | 1968-10-25 | 2000 | 100 | 财务部 | good body |
9 | 张无忌 | male | 1988-10-25 | 3000 | 100 | 研发部 | good body |
-
将研发部员工的薪水修改为2500
update employee set salary=2500 WHERE department='研发部';
-
将姓名为”阿紫”的员工薪水修改为3000元。
update employee set salary=3000 where `name`='阿紫';
-
将姓名为”赵灵儿”的员工薪水修改为4000元,sex改为female。
update employee set salary=4000,sex='female' where `name`='赵灵儿';
-
公司统一给每位员工再发奖金500
update employee set bonus=bonus+500;
-
将”张三丰”的薪水在原有基础上增加1000元。
update employee set salary=salary+1000 WHERE `name`='张三丰';
-
查询表中所有员工的信息。
SELECT * from employee;
-
查询表中所有员工的姓名和对应的薪水。
SELECT name,salary from employee;
-
使用汉语展示员工信息。(列名翻译成中文展示)
SELECT id 编号, name 名字,sex 性别,birthday 生日, salary 薪水,bonus 奖金,department 部门,resume 备注 from employee;
-
查询姓名为”杨过”的员工的薪水
SELECT salary FROM employee WHERE name='杨过';
-
查询姓名为”杨过”的员工的总收入
SELECT SUM(salary+bonus) from employee where `name`='杨过';
-
查询薪水大于3000的员工信息
SELECT * from employee where salary>3000;
-
查询总收入大于4000的员工的姓名 部门 薪水 奖金
SELECT name,department,salary,bonus FROM employee where salary+bonus>4000;
-
查询80后的员工
SELECT * from employee WHERE birthday>'1980-01-01';
-
查询所有女性薪水大于4000的员工
SELECT * from employee WHERE sex='female' and salary>4000;
-
查询所有女性薪水大于4000的员工按薪水降序排列
SELECT * from employee WHERE sex='female' and salary>4000 ORDER BY salary DeSC;
-
查询各个部门员工数量
SELECT COUNT(*) from employee GROUP BY department;
-
查询各个部门的员工数量及薪水支出
SELECT COUNT(*),sum(salary) from employee GROUP BY department;
-
查询各个部门的员工数量及总支出
SELECT COUNT(*),sum(salary+bonus) from employee GROUP BY department;
-
查询公司男性和女性的数量
SELECT sex,sum(salary+bonus) FROM employee GROUP BY sex;
-
查询公司男性员工和女性员工的收入总和
SELECT sum(salary+bonus),sex FROM employee GROUP BY sex;
-
查询公司中男性员工的收入总和
SELECT sum(salary+bonus)AS '男生总收入' FROM employee where sex='male';
-
查询公司中总支出大于9000的部门
SELECT department,SUM(salary+bonus)AS'count' FROM employee GROUP BY department HAVING count>9000 ;
-
查询公司中所有”张”姓员工的平均工资
SELECT AVG(salary) FROM employee where `name` like '张%';
-
查询公司中”张”姓员工的工资总和
SELECT sum(salary) FROM employee where `name` like '张%';
-
查询公司中”张”姓员工的总收入
SELECT sum(salary+bonus) FROM employee where `name` like '张%';