E1 基于Mysql的SQL应用

E1 基于Mysql的SQL应用

• 实验目的：完成对基本表、视图创建、删除、数据插入、查询、更新和删除等数据管理工作。

• Group 1: 用命令“Select”查询相关数据：

  • 1、检索所有的职工姓名与年薪

    Select ename,12*sal from emp;

  • 2、检索所有的工作和相应的部门编号

    Select distinct job，deptno from emp

  • 3、检索工作是salesman的员工姓名；

    select ename from emp where job =‘salesmen’;

  • 4、检索员工津贴comm为null或comm小于300的员工姓名和津贴值；

    select ename, comm from emp where comm is null or comm < 300;

  • 5、检索所有职员的姓名和所在部门名称；

    select emp.ename, dept.Dname from emp, dept where
    emp.deptno=dept.deptno;

  • 6、检索所有员工及其相关领导的姓名。

    select e.ename, m.ename from emp e, emp m where m.empno = e.mgr;

  • 7、查询聘用日期早于他们的领导的雇员姓名。

    select ename from emp e where hiredate<(select hiredate from emp
    where empno=e.mgr);

  • 8、检索在任何位置有字母“A”的员工姓名;

    select ename from emp where ename like ‘%A%’;

  • 9、检索名字没有字母串‘%R’的员工姓名;

    select ename from emp where ename not like ‘%%R%’;

  • 10、检索所有员工姓名的前三个字母；

    select substr(ename,1,3) from emp;

  • 11、查询部门2所有的经理、部门3所有clerks以及所有其他所有工资超过2000元的员工姓名；

    select ename from emp where deptno = 2 and job = ‘manager’ or deptno =
    3 and job = ‘clerk’ or sal > 2000;

  • 12、查询所有manager岗位而非3号部门的员工姓名；

    select ename from emp where job = ‘manager’ except deptno = 3；

  • 13、检索所有员工的姓名、职位和工资，按职位降序排列；

    select ename, job, sal from emp order by job desc;

  • 14、检索所有员工的姓名、年收入(sal+comm) * 12（要求重命名），按升序显示；

    select ename,(sal+comm) * 12 as annual_salry from emp order by
    annual_salry

  • 15、检索1号部门所有员工的人数和平均年薪；

    select count(*), avg(sal) from emp where emp.deptno=1;

  • 16、检索每个部门的名称和人数。

    select dname, count(*) from emp, dept where dept.deptno = emp.deptno
    group by emp.deptno;

  • 17、检索每一种工作的最低工资和工作。

    select min(sal), job from emp group by job;

  • 18、查询各个部门经理职位（manager）的员工最低工资。

    select min(sal), deptno from emp where job = ‘manager’ group by
    deptno;

  • 19、查询各部门员工人数、平均工资、平均参加工作的天数；

    select dname, count(*), avg(sal), avg(timestampdiff(day, hiredate,
    now())) from emp, dept where dept.deptno = emp.deptno group by
    emp.deptno;

  • 20、检索最低工资在1500元的工作岗位；

    select job, min(sal) from emp group by job having min(sal) > 1500;

  • 21、检索至少有两个人的部门名称和人数；

    select dname, count() from emp, dept where dept.deptno = emp.deptno
    group by emp.deptno having count() > 2;

  • 22、检索与SCOTT的工作岗位相同的所有员工的姓名；

    select ename from emp where job=(select job from emp where ename =
    ‘scott’) and ename<>’SCOTT’;

  • 23、检索所有工资高于Smith的工资的员工姓名和所在部门；

    select ename, Dname from emp, dept where sal > (select sal from emp
    where ename = ‘Smith’) and emp.deptno = dept.deptno;

  • 24、检索所有工资高于整个公司平均工资的员工的姓名和工资；

    select ename, sal from emp where sal > (select avg(sal) from emp);

  • 25、检索所有工资高于1号部门所有职工工资水平的员工姓名和工资(>any或>all)；

    select ename, sal from emp where sal >all(select avg(sal) from emp
    where deptno=1);

  • 26、检索所有工资高于1号部门某职工工资水平的员工姓名和工资(>any或>all)；

    select ename, sal from emp where sal >any(select avg(sal) from emp
    where deptno=1);

  • 27、查询与3号部门某个员工工作岗位相同的员工姓名和工资；

    select ename, sal from emp where job in (select job from emp where
    deptno = 3);

  • 28、检索所有部门名称和所有员工，包括那些没有任何员工的部门。

    select dname,ename from dept left join emp on dept.deptno =
    emp.deptno;

  • 29、创建一个包括1号部门员工所有信息的视图。

    Create view view_1 as select * from emp where emp.deptno=1;

• Group 2请使用你的账户创建两个表，包括属性、数据类型、主键和外键。

  ddept (deptno, dname loc) deptno:integer;dname: varchar (20); loc:
  varchar (30); primary key:deptno; eemp (empno,
  ename,job,hiredate,salary,comm,deptno) empno: integer;ename:
  varchar(20);job:varchar(20),hiredate:datetime, salary:double;
  comm:double;deptno: integer, not null; Primary key:empno;forign
  keys:deptno reference ddept(deptno)，mgr reference EEmp(empno)。
  1.create table ddept(deptno integer, dname varchar(20), loc varchar(30), primary key(deptno));
  2.create table eemp(empno integer, ename varchar(20), job varchar(20), hiredate datetime, salary double, mgr int, comm double, deptno integer
  not null, primary key(empno), foreign key(deptno) references
  ddept(deptno), foreign key(mgr) references EEmp(empno));

• Group 3: 完成以下操作:

  • 1.、通过alter table添加一个check约束(comm在1和3000之间);

    alter table emp add check(comm between 1 and 3000);

  • 2、 向表eemp中插入至少5条员工记录;

    insert into eemp values(1, ‘a’, ‘Manager’, ‘2019-3-12’, 5000, null,
    500, 1); insert into eemp values(2, ‘b’, ‘Office’, ‘2019-3-11’, 4000,
    null, 400, 2); insert into eemp values(3, ‘c’, ‘Finance’, ‘2019-3-10’,
    3000, null, 300, 3); insert into eemp values(4, ‘d’, ‘Factory’,
    ‘2019-3-9’, 2000, null, 200, 4); insert into eemp values(5, ‘e’,
    ‘Market’, ‘2019-3-8’, 1000, null, 100, 5);

  • 3、从eemp中删除名为“SMITH”的记录;

    delete from eemp where ename = ‘smith’;

  • 4、创建部门研究信息视图，包括empno、ename、salary和comm。

    create index researchindex on eemp(empno, ename, salary, comm);

  • 5、将所有员工的工资提高20%。

    update eemp set salary = salary * 1.2;

  • 6、将1号部门的职工工资提高20%，其他提高10…%；

    update emp set sal=case when deptno=1 then sal*1.2 else sal *1.1 end;

  • 7、将2号部门员工的信息写入eemp。

    Insert into eemp select * from emp;

• Group 4: 综合设计
  根据某个应用的需求，创建一个新的数据库。其中至少包括3个表，每个表至少定义4个合理的属性，分别具有适合的数据类型，并确定表的主键。然后，根据您设计的数据库，将每个表中至少插入4行数据。

  create table stu(name varchar(5), number int, age int, classno int,
  primary key(number)); create table score(number int, math int, chinese
  int, english int, primary key(number)); create table message(classno
  int, classname varchar(10), location varchar(10), persons int);

  insert into stu values(‘a’, 1, 17, 1); insert into stu values(‘b’, 2,
  18, 2); insert into stu values(‘c’, 3, 19, 3); insert into stu
  values(‘d’, 4, 19, 4);

  insert into score values(1, 80, 80, 80); insert into score values(2,
  60, 70, 80); insert into score values(3, 90, 90, 90); insert into
  score values(4, 30, 40, 20);

  insert into message values(1, ‘一班’, ‘东边’, 5); insert into message
  values(2, ‘二班’, ‘西边’, 10); insert into message values(3, ‘三班’, ‘南边’,
  8); insert into message values(4, ‘四班’, ‘北边’, 6);