MySQL中表基本操作实例一(简单操作)

最新推荐文章于 2024-07-19 00:12:38 发布
Wxin1997
最新推荐文章于 2024-07-19 00:12:38 发布
阅读量235
点赞数
文章标签： mysql 数据库 sql
本文链接：https://blog.csdn.net/m0_46550052/article/details/127522806
版权
员工信息表建立以及表的基本查询

create DATABASE emp;
use emp;
create table dept(
deptno int(2) comment '部门编号',
DNAME varchar(14) comment '部门名称',
LOC varchar(13) comment '部门职位',
CONSTRAINT pk_dept PRIMARY KEY(deptno)
)engine=INNODB default charset=utf8;

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');
select * from dept;


create table EMP(
EMPNO int(4) not null PRIMARY key comment '雇员编号',
ENAME varchar(10) comment '雇员姓名',
JOB varchar(9) comment '工作职位',
MGR int(4) comment '雇员的领导编号',
HIREDATE DATE comment '雇佣日期',
SAL FLOAT(7,2) comment '工资',
COMM float(7,2) comment '奖金',
DEPTNO int(2) comment '部门编号',
constraint fk_deotno foreign key (deptno) references dept(deptno)
)ENGINE=INNODB default charset=utf8;

insert into emp values(7369,'SMITH','SALESMAN',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,'1987-07-13',3000,NULL,20); 
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-07',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,'1987-07-13',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);
select * from emp;


create table salgrade(
grade int comment '等级名称',
losal int comment '此等级最低工资',
HISAL int comment '此等级最高工资'
)engine=innodb default charset=utf8;
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);
select * from salgrade;

-- 1.选择部门30中的雇员
select * from emp where deptno=30;

-- 2.检索emp表中的员工姓名，月收入及部门编号
select ename,sal,deptno from emp;

-- 3.检索emp表中员工姓名、及雇佣时间(雇佣时间按照yyyy-mm-dd显示)
select ename 员工姓名,HIREDATE 入职日期,concat('￥',round(sal*12)) 年收入 from emp;

-- 4.检索emp表中的部门编号及工种，并去掉重复 
select DISTINCT deptno 部门编号,job 职位名称 from emp;
select distinct job from emp;

-- 5.检索emp表中的员工姓名及全年的收入 
select ename,sal*12 from emp;

-- 6.用姓名显示员工姓名，用年收入显示全年收入
select ename 姓名,concat('￥',round(sal*12)) 年收入 from emp;
-- 7.检索月收入大于2000的员工姓名以及全年收入
select ename 姓名,sal 月薪,concat('￥',sal*12) 年薪 from emp where sal>2000;
-- 8.检索月收入在1000到2000的员工姓名及月收入
select ename 姓名,sal 月薪 from emp where sal BETWEEN 1000 and 2000;
-- 9.检索以S开头的员工姓名及月收入
select ename 姓名,sal 月薪 from emp where ename like '%s';
-- 10.检索emp表种月收入是800的或是1250的员工姓名及部门
select ename 姓名,deptno,sal from emp where sal=800 or sal=1000;
select ename 姓名,deptno 部门编号,sal 月薪 from emp where sal in(800,1000);
-- 11.显示在部门20种岗位是clerk的所有雇员信息
select * from emp where deptno=20 and job='clerk';
-- 12.显示工资高于2500或岗位为MANAGER的所有雇员信息
select *from emp where sal>2500 or job='MANAGER';
-- 13.检索emp表中有江景的员工姓名，月收入及奖金
select ename,sal,comm from emp where comm is not null;
--  14.检索emp中部门编号是30员工姓名，月收入及提成，并要求结果按月收入升序，然后按提成降序显示
select ename,sal,comm from emp where deptno=30 order by sal asc,comm desc;
-- 15.列出所有办事员(clerk)的姓名 编号 部门
select ename,empno,deptno from emp where job='clerk';
-- 16.找出奖金高于薪资的雇员
select * from emp where comm>sal;
-- 17.找出部门10中所有经理和部门20中所有办事员的详细资料
select * from emp where(deptno=10 and job='MANAGER') or (deptno=20 and job='clerk');
-- 18.找出部门10中所有经理，部门20中所有办事员，既不是经理又不是办事员但薪资>=2000的所有雇员的详细资料
select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='clerk') or (sal>=2000 and (job!='manager'and job!='clerk'));

select * from emp where deptno=10 and job='MANAGER' or deptno=20 and job='CLERK' or sal>=2000 and (job!='MANAGER' and job!='CLERK');

-- 19.找出收取奖金的雇员的不同工作 salesman 推销员
select distinct job from emp where comm is not null;
-- 20.找出不收取奖金或收取奖金低于100的雇员
select * from emp where comm is null or comm <100;
-- 21.找出各月倒数第三天受雇的员工 
select * from emp where hiredate = date_add(last_day(hiredate),interval-2 day);
-- 23.找出早与25年之前受雇的雇员
select * from emp where hiredate < date_add(LAST_DAY(CURRENT_DATE),interval-35 year);
-- 24.显示正好为6个字符的雇员姓名
select ename from emp where CHAR_LENGTH(ename)=6;
select ename from emp where ename like '______';
select ename from emp where ename regexp '^.{6}$';
-- 25.显示不到有'R'的雇员姓名
select ename from emp where ename not like '%R%';
select ename from emp where not ename like '%r%';
select ename from emp where instr(ename,'R')=0;

select instr('hellor','r');
-- 26.显示雇员的详细资料，按姓名排序
select *from emp ORDER BY ename asc; #升序
select *from emp ORDER BY ename desc; #降序
-- 27.显示雇员姓名，根据其服务年限，将最老的雇员排在前面
SELECT ename,hiredate from emp ORDER BY hiredate asc;
-- 28.显示所有雇员的姓名、工作和薪资，按工作的降序顺序排序，二工作相同按薪资升序
select ename,job,sal from emp order by job desc,sal asc;
-- 29.显示所有雇员的姓名和加入公司的年份月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面
select ename,year(hiredate),month(hiredate) from emp order by year(hiredate) asc,MONTH(hiredate) asc;
-- 30.显示一个月为30天的情况下所有雇员的日薪资
select ename,sal,round(sal/30) 日薪资 from emp;
-- 31.找出在(任何年份的) 2月受聘的所有雇员
select * from emp where MONTH(hiredate)=2;
select * from emp where hiredate between '1981-2-1' and last_day('1981-2-28');
select last_day('2000-2-28');
-- 32.对于每个雇员，显示其加入公司的天数
select ename,timestampdiff(day,hiredate,now()) from emp;

select datediff(current_date,'1985-1-1');
select timestampdiff(day,'1985-1-1',now());
select timestampdiff(day,'2021-12-28',now());
select timestampdiff(year,'1985-1-1',now());

-- 33.显示姓名字段的任何位置，包含“a”的所有雇员的姓名
select ename from emp where ename like '%a%';
-- 34.以年、月和日显示所有雇员的服务年限
select ename,timestampdiff(year,hiredate,now()) 年,
       timestampdiff(month,date_add(hiredate,interval timestampdiff(year,hiredate,now()) year),now()) 月,
       timestampdiff(day,date_add(date_add(hiredate,interval timestampdiff(year,hiredate,now()) year),interval timestampdiff(month,date_add(hiredate,interval timestampdiff(year,hiredate,now()) year),now()) month),now()) 天
from emp;


set @hiredate = '2021-3-1';
select timestampdiff(year,@hiredate,now()) 年,
       timestampdiff(month,date_add(@hiredate,interval timestampdiff(year,@hiredate,now()) year),now()) 月,
       timestampdiff(day,date_add(date_add(@hiredate,interval timestampdiff(year,@hiredate,now()) year),interval timestampdiff(month,date_add(@hiredate,interval timestampdiff(year,@hiredate,now()) year),now()) month),now()) 天
-- 35.选择公司中有奖金(comm不能为空，且不为0)的员工姓名，工资和奖金比例，按工资逆排序，奖金比例逆排序
select ename,sal,round(comm/sal,2) from emp where comm is not null and comm>0 order by sal desc,round(comm/sal )desc;
-- 36.选择公司中没有管理者的员工姓名及job PRESIDENT　king　
select ename,job from emp where mgr is null;
-- 37.选择在1987年雇用的员工的姓名和雇佣时间
select ename,hiredate from emp where year(hiredate)=1987;
select ename,hiredate from emp where hiredate between '1987-1-1' and '1987-12-31';
-- 38.选择在20或10号部门工作的员工姓名和部门号
select ename,deptno from emp where deptno in (10,20);
select ename,deptno from emp where deptno =20 or deptno =10;
-- 39.选择雇佣时间在1981-02-01到1981-05-01之间的员工姓名，职位和雇佣时间，按从早到晚排序
select ename,job,hiredate from emp where hiredate between '1981-02-01' and '1981-05-01' order by hiredate asc;
-- 40. 选择工资不在5000到12000的员工的姓名和工资
select ename,sal from emp where sal not between 5000 and 12000;
select ename,sal from emp where sal<5000 or sal>120000;
-- 41.查询员工为7943的员工的姓名和部门 
select ename,deptno from emp where empno=7934;
-- 42.c查询工资带1200的员工的姓名和工资 
select ename,sal from emp where sal>1200;