SQL最简单单表查询

简单创一个单表：

drop TABLE if EXISTS `emp`;
CREATE TABLE `emp`(
##员工编号
`id` INT(20) PRIMARY KEY,
##员工姓名
`name` VARCHAR(5) NOT NULL ,
##职位
`post` varchar(5) not NULL ,
##直属上级
`is` VARCHAR(5) not NULL,
##受雇日期
`date` datetime not null ,
##薪资
`salary` DECIMAL not NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `emp` VALUES ('1','zs','员工','包工头','2011-01-11','3000.0');    
INSERT INTO `emp` VALUES ('2','ls','员工','包工头','2022-02-22','4000.0');
INSERT INTO `emp` VALUES ('3','ww','员工','包工头','2011-03-21','5000.0');
INSERT INTO `emp` VALUES ('4','zl','包工头','主管','2011-04-21','6000.0');
INSERT INTO `emp` VALUES ('5','zd','主管','总经理','2011-05-21','10000.0');
INSERT INTO `emp` VALUES ('6','佟夜月','主管','总经理','2011-06-21','9000.0');
INSERT INTO `emp` VALUES ('7','wd','包工头','主管','2011-07-21','11000.0');
INSERT INTO `emp` VALUES ('8','zd','总经理','董事长','2011-08-21','21000.0');
INSERT INTO `emp` VALUES ('9','bd','董事长','董事长夫人','2000-08-21','101000.0');

单表案例

#4.请给表添加一列 #奖金字段
 ALTER TABLE `emp` ADD bonus DECIMAL ;

 #5.请修改#员工姓名 列字段 名为 emp_name 类型为255 长度
 ALTER TABLE `emp` CHANGE NAME emp_name VARCHAR(255);
 #6.请修改一条员工的 资料，将该员工升至为部门主管
    UPDATE emp set post="部门主管"where id=7;

#添加奖金
UPDATE emp set bonus="1000" where id=1;
UPDATE emp set bonus="2000" where id=2;
UPDATE emp set bonus="3000" where id=3;
UPDATE emp set bonus="4000" where id=4;
UPDATE emp set bonus="5000" where id=5;
UPDATE emp set bonus="6000" where id=6;
UPDATE emp set bonus="7000" where id=7;
UPDATE emp set bonus="8000" where id=8;
UPDATE emp set bonus="9000" where id=9;

 #-7.请查询出总经理职位的员工，并起中文别名
 select emp_name "总督" from emp where post="总经理";
 #8.对总经理职位相同的薪资去重
 SELECT  DISTINCT * from emp where post="总经理";
 #9.查询出奖金 大于1000 以上和 职位是主管的员工
 select * from emp where salary>1000 or post="主管";
 #10.请查出 薪资 1000在5000直接的员工
 select * from emp where salary BETWEEN 1000 AND 5000 ;
 #11.查找出 员工姓名中带蒋的员工信息
 select * from emp where emp_name LIKE "佟%";
 #12.请查找出职位是总经理或者是主管的员工
 SELECT * from emp where post="总经理" OR post="主管";
 #13.对薪资进行降序排序
SELECT * FROM emp ORDER BY salary DESC; 
 #14.得到薪资前三名的员工 
SELECT * FROM emp ORDER BY salary DESC LIMIT 3; 
 #15.得到奖金中最后一名的员工信息
SELECT * from emp ORDER BY bonus ASC LIMIT 1;
 #-16.查询出每名员工的奖金,薪资,总金额
SELECT emp_name,salary,bonus,IFNULL(bonus,0)+salary from emp ;
 #17.-显示出每名员工的总金额以及姓名
 SELECT IFNULL(bonus,0)+salary,emp_name from emp GROUP BY emp_name; 
 #18.查询员工总人数
SELECT COUNT(*) "总人数" from emp;
 #19.员工编号为1的员工奖金总数量,平均薪资
SELECT SUM(bonus),AVG(salary) FROM emp where id=1;
 #20.查出员工中薪资最低的人
SELECT emp_name FROM emp ORDER BY bonus LIMIT 1;
 #21.查出总经理职位的一共有几人
SELECT COUNT(*) from emp where post="总经理";
 #22.查询出比员工姓名为佟夜月薪资高的员工
 SELECT emp_name,bonus from emp WHERE bonus>(SELECT bonus from emp where emp_name="佟夜月");
 #23.查询出每个职位对应的员工人数
 SELECT COUNT("员工人数") from emp GROUP BY post;
 #24.查出每个职位中薪资水平在2000 以上的员工信息 
  SELECT emp_name,post from emp where bonus>2000 GROUP BY post ;
 #25.查询总经理和董事长各自的薪资平均，以及各自人数，薪资低于2000的不参与分组，且取出人数大于2个的
SELECT post,AVG(salary),COUNT(post)FROM emp WHERE `post` in ('总经理','董事长')AND salary > 2000 GROUP BY `post` HAVING COUNT(`post`) > 2;