MySQL语法的基本练习

最新推荐文章于 2024-05-26 10:59:48 发布

老母猪的产后护理

最新推荐文章于 2024-05-26 10:59:48 发布

阅读量89

点赞数

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/weixin_53591358/article/details/125316765

版权

dept 部门表

  create table dept (
  deptno numeric(2),--部门编号
  dname varchar(14),--部门名称
  loc varchar(13) --地点
);


   insert into dept values (10, 'ACCOUNTING', 'NEW YORK');
   insert into dept values (20, 'RESEARCH', 'DALLAS');
   insert into dept values (30, 'SALES', 'CHICAGO');
   insert into dept values (40, 'OPERATIONS', 'BOSTON');

salgrade工资等级表

    create table salgrade (
    grade numeric,--等级
    losal numeric,--最低等级
    hisal numeric --最高等级
  );


  insert into salgrade values (1, 700, 1200);
  insert into salgrade values (2, 1201, 1400);
  insert into salgrade values (3, 1401, 2000);
  insert into salgrade values (4, 2001, 3000);
  insert into salgrade values (5, 3001, 9999);

emp员工表
工资＝薪金＋佣金

    create table emp (
    empno numeric(4) not null,--员工号
    ename varchar(10),--员工姓名
    job varchar(9),--工作
    mgr numeric(4),--上级编号
    hiredate datetime,--受雇日期
    sal numeric(7, 2),--薪金
    comm numeric(7, 2),--佣金
    deptno numeric(2) --部门编号
);

    insert into emp values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
    insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
    insert into emp values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
    insert into emp values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
    insert into emp values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
    insert into emp values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
    insert into emp values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
    insert into emp values (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, null, 20);
    insert into emp values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
    insert into emp values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
    insert into emp values (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, null, 20);
    insert into emp values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
    insert into emp values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
    insert into emp values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

需求1. 查询出部门编号为30的所有员工的编号和姓名

    select  empno ,ename ,deptno  from  emp 
    where deptno=30;

需求2. 找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。

  	select  * from emp 
       where (deptno = 10 and job='MANAGER')
    or 
             (deptno = 20 and job='SALESMAN');

需求3.查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序

select  *, (sal+ifnull(comm,0)) as earing
   from emp 
       order by earing desc ,hiredate asc;

需求 4.列出薪金大于1500的各种工作及从事此工作的员工人数。

select  job,
    count(1) as cnt
   from emp 
      where 
            sal > 1500
                   group by job;

需求 5.列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

            selsct ename from emp
                where job = 'SALESMAN'；

需求6.查询姓名以S开头的\以S结尾\包含S字符\第二个字母为L

    select  * from emp 
    where ename like "S%"

    union all

    select  *  from emp 
    where ename like "%S"

    union all

    select  *  from emp
    where ename like "%S%"

    union all

    select  *   from emp 
    where ename like "_L%";

第二种方法

   select *  from emp where  ename like "S%"
   or  ename like "%S%" or  ename like "_L%";

7.查询每种工作的最高工资、最低工资、人数

    select max(hisal) as max_hisal ,min(losal) as min_losal, count(1) as cnt,//统计人数
    from salgrade group by job;

8.列出薪金高于公司平均薪金的所有员工号，员工姓名，所在部门名称，上级领导，工资，工资等级

9.列出薪金高于在各自部门工作的员工的平均薪金的员工姓名和薪金、部门名称。

附加50道语法练习题网址
https://blog.csdn.net/GodSuzzZ/article/details/106935523?utm_
medium=distribute.pc_aggpage_search_result.none-task-blog-2~
aggregatepage_{first_rank_ecpm_v1}rank_v31_ecpm-29-106935523.
pc_agg_new_rank&utm_term=hive%E9%80%89%E6%8B%A9%E9%A2%98&spm=1000.2123.3001.4430