DQL 查询操作-CSDN博客

本文链接：https://blog.csdn.net/binzai1232/article/details/125848125

本文详细介绍了如何使用SQL创建和管理四张关键表：部门、员工、薪资等级和奖金，涵盖了外键约束、数据插入、基本查询与筛选。通过实例展示了如何操作DEPT、EMP、SALGRADE和BONUS表，涉及数据筛选、排序和别名应用等数据库操作技巧。

摘要由CSDN通过智能技术生成

-- 准备四张表：dept（部门表），emp（员工表）,salgrade(薪资等级表)，bonus（奖金表）
create table DEPT(
DEPTNO int(2) not null,
DNAME VARCHAR(14),
LOC VARCHAR(13)

);
alter table DEPT add constraint PK_DEPT PRIMARY KEY (DEPTNO) -- 外键查询·可以写在表的里面ps: create table DEPT(

DEPTNO int(2) not null,
DNAME VARCHAR(14),
LOC VARCHAR(13),

constraint PK_DEPT primary key (DEPTNO)

)

---

create table EMP
(
EMPNO int(4) primary key,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR int(4),
HIREDATE DATE,
SAL double(7,2),
COMM double(7,2),
DEPTNO int(2)
);

alter table EMP add constraint FK_DEPTNO foreign key (DEPTNO) references DEPT (DEPTNO);

create table SALGRADE
(
GRADE int primary key,
LOSAL double(7,2),
HISAL double(7,2)
);
create table BONUS
(
ENAME VARCHAR(10),
JOB VARCHAR(9),
SAL double(7,2),
COMM double(7,2)
);

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

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

insert into SALGRADE (GRADE, LOSAL, HISAL)
values (1, 700, 1200);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (2, 1201, 1400);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (3, 1401, 2000);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (4, 2001, 3000);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (5, 3001, 9999);
drop table EMP;

-- 单表查询

-- 查看表
select * from dept;

-- 部门表：dept:department 部分，loc - location 位置

select * from emp;

select * from salgrade;

select * from bonus;

-- 对Emp表进行查询
select *from emp; -- 代表所有数据
-- 显示部分列
select empno,ename,sal from emp;
-- 显示部分行
select * from emp where sal > 2000;
-- 起别名
select empno 员工编号,ename 姓名,sal 工资 from emp;
-- as省略了
-- as alias 别名
select empno as 员工编号,ename as 姓名,sal as 工资 from emp;
select empno as '员工编号',ename as '姓名',sal as 工资 from emp;

-- 算术运算符
select empno,ename,sal,sal+1000 as '张新后',deptno from emp where sal<2500;

-- 去重操作
select job from emp;
select distinct job from emp;
-- 排序
select * from emp order by sal; -- 默认都是升序排列
select * from emp order by sal asc; -- asc -- 升序，可以默认不写
select * from emp order by sal desc;-- 降序
select * from emp order by sal asc,deptno desc; -- 在工资升序的情况下 deptno按照降序排列