sql练习

sql练习

创建表语句

#创建部门
CREATE TABLE IF NOT EXISTS dept (
    did int not null auto_increment PRIMARY KEY,
    dname VARCHAR(50) not null COMMENT '部门名称'
)ENGINE=INNODB DEFAULT charset utf8;

#添加部门数据
INSERT INTO `dept` VALUES ('1', '教学部');
INSERT INTO `dept` VALUES ('2', '销售部');
INSERT INTO `dept` VALUES ('3', '市场部');
INSERT INTO `dept` VALUES ('4', '人事部');
INSERT INTO `dept` VALUES ('5', '鼓励部');

-- 创建人员
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` tinyint(4) DEFAULT '0',
  `sex` enum('男','女','人妖') NOT NULL DEFAULT '人妖',
  `salary` decimal(10,2) NOT NULL DEFAULT '250.00',
  `hire_date` date NOT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- 添加人员数据


INSERT INTO `person` VALUES ('1', 'alex', '28', '人妖', '53000.00', '2010-06-21', '1');
INSERT INTO `person` VALUES ('2', 'wupeiqi', '23', '男', '8000.00', '2011-02-21', '1');
INSERT INTO `person` VALUES ('3', 'egon', '30', '男', '6500.00', '2015-06-21', '1');
INSERT INTO `person` VALUES ('4', 'jingnvshen', '18', '女', '6680.00', '2014-06-21', '1');

-- 销售部
INSERT INTO `person` VALUES ('5', '歪歪', '20', '女', '3000.00', '2015-02-21', '2');
INSERT INTO `person` VALUES ('6', '星星', '20', '女', '2000.00', '2018-01-30', '2');
INSERT INTO `person` VALUES ('7', '格格', '20', '女', '2000.00', '2018-02-27', '2');
INSERT INTO `person` VALUES ('8', '周周', '20', '女', '2000.00', '2015-06-21', '2');

-- 市场部
INSERT INTO `person` VALUES ('9', '月月', '21', '女', '4000.00', '2014-07-21', '3');
INSERT INTO `person` VALUES ('10', '安琪', '22', '女', '4000.00', '2015-07-15', '3');

-- 人事部
INSERT INTO `person` VALUES ('11', '周明月', '17', '女', '5000.00', '2014-06-21', '4');

-- 鼓励部
INSERT INTO `person` VALUES ('12', '苍老师', '33', '女', '1000000.00', '2018-02-21', null);

查询数据表

SELECT * FROM person ;
SELECT * FROM dept ;

person表

dept表

练习题

1. 查询人员和部门所有信息

   SELECT * FROM person,dept where person.dept_id=dept.did ;
   

2. 查询出 教学部 年龄大于20岁,并且工资小于40000的员工,按工资倒序排列.(要求:分别使用多表联合查询和内连接查询)

SELECT did from dept WHERE dname = '教学部';

SELECT * FROM person WHERE age>20 and dept_id=1 and salary<40000 ORDER BY salary desc;

 -- 子查询
SELECT * FROM person WHERE age>20 and dept_id=(SELECT did from dept WHERE dname = '教学部') and salary<40000 ORDER BY salary desc;

 -- 多表
SELECT * FROM person,dept WHERE person.dept_id=dept.did and dname='教学部' and age>20 and salary<40000 ORDER BY salary DESC;

 -- 多表子查询
SELECT * from(SELECT * from person,dept WHERE person.dept_id=dept.did)res WHERE dname='教学部' and age>20 and salary<40000 ORDER BY salary DESC;

 -- 内连接
SELECT * from person INNER JOIN dept WHERE person.dept_id=dept.did and dname='教学部' and age>20 and salary<40000 ORDER BY salary DESC;

3. 查询每个部门中最高工资和最低工资是多少,显示部门名称

   SELECT dept.dname,res.max,res.min FROM(SELECT dept_id,MAX(salary) max,MIN(salary) min from person GROUP BY dept_id)res RIGHT JOIN dept on res.dept_id=dept.did;
   

4. 求最大工资那个人的姓名和薪水

   SELECT * FROM person ;
   
   INSERT INTO person VALUES(13,'娜娜子',20,'女',2300,'2000-07-02',6);
   
   UPDATE person set salary=1000 WHERE `name`='苍老师';
   
   SELECT name,salary from person WHERE salary = (SELECT MAX(salary) FROM person)
   

5. 求工资高于所有人员平均工资的人员

   SELECT name,salary from person WHERE salary>(SELECT AVG(salary) from person )
   

6. 查询平均年龄在20岁以上的部门名

   SELECT dept_id,AVG(age) avg from person GROUP BY dept_id HAVING avg>20;
   
   SELECT dname,res.avg from (SELECT dept_id,AVG(age) avg from person GROUP BY dept_id HAVING avg>20)res INNER JOIN dept on res.dept_id=dept.did
   

   

7. 查询教学部 下的员工信息

   SELECT * from person,dept where dept_id=did and dept_id=(SELECT did from dept where dname='教学部');
   

8. 查询大于所有人平均工资的人员的姓名与年龄

SELECT name,age from person WHERE salary>(SELECT AVG(salary) from person )

9. 查询高于本部门平均工资的人员

SELECT dept_id,AVG(salary) avg from person GROUP BY dept_id

SELECT person.name,salary,avg from (SELECT dept_id,AVG(salary) avg from person GROUP BY dept_id)res INNER JOIN person on person.dept_id=res.dept_id and person.salary>res.avg

10. 根据工资高低,将人员划分为两个级别,分别为 高端人群和低端人群。显示效果:姓名,年龄,性别,工资,级别

    SELECT name, IF(salary>5000,'高端人群','低端人群') 级别 FROM person
    
    SELECT name, 
    CASE WHEN salary>5000 THEN
    		'高端人群'
    	ELSE
    		'低端人群'
    END 级别
    FROM person