加强网站和新媒体建设管理的意义,互联网推广和传统营销有哪些相同点,app推广策划书模板,网站制作要花多少钱一、表的准备
查询操作的SQL演示将基于下面这四张表进行#xff0c;我们先创建好这四张数据表#xff0c;并为其添加数据。
1、第一张表为部门表#xff0c;名称为包含三个字段#xff1a;部门编号#xff08;deptno#xff09;#xff0c;部门名称#xff08;dname我们先创建好这四张数据表并为其添加数据。
1、第一张表为部门表名称为包含三个字段部门编号deptno部门名称dname部门位置loc
create table DEPT( DEPTNO int(2) not null, DNAME VARCHAR(14), LOC VARCHAR(13)
);
为部门编号字段deptno添加主键约束
alter table DEPT add constraint PK_DEPT primary key (DEPTNO);
向部门表dept中插入数据
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); 2、第二张表为员工表emp该表包含8个字段分别为员工编号empno员工姓名ename职位job直属领导mgr入职日期hiredate薪水sal补助comm所属部门编号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)
);
为员工表emp的字段deptno添加外键约束与部门表dept的字段部门编号deptno关联
alter table EMP add constraint FK_DEPTNO foreign key (DEPTNO) references DEPT (DEPTNO);
向员工表emp中插入数据
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); 3、第三张表为薪资等级表该表包含3个字段分别为薪资等级grade最低薪资losal最高薪资hisal
create table SALGRADE
( GRADE int primary key, LOSAL double(7,2), HISAL double(7,2)
);
向薪资等级表salgrade中插入数据
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); 4、第四张表为奖金表该表包含4个字段分别为:员工姓名ename员工工作job薪资salcomm
create table BONUS
( ENAME VARCHAR(10), JOB VARCHAR(9), SAL double(7,2), COMM double(7,2)
); 二、单表查询
在数据库操作中单表查询就是在一张表中进行数据的查询。
1、简单的单表查询
对员工表emp表查询*代表所有数据
select * from emp;
显示部分列
select empno,ename,sal from emp;
显示部分行where子句
select * from emp where sal 2000;
显示部分列部分行
select empno,ename,job,mgr from emp where sal 2000; 2、为查询的字段起别名
为查询的字段起别名可以省略关键字as 也可以省略了别名带的单引号和双引号
select empno 员工编号,ename 姓名,sal 工资 from emp;
为查询的字段起别名时使用关键字as别名也可以携带单引号或者双引号两者没有区别
select empno as 员工编号,ename as 姓名,sal as 工资 from emp;
select empno as 员工编号,ename as 姓名,sal as 工资 from emp;
在别名中有特殊符号的时候单引号或者双引号不可以省略不写如下员工 编号之间有一个空格空格属于特殊符号
select empno as 员工 编号,ename as 姓 名,sal as 工资 from emp;
如果别名员工 编号不使用单引号和双引号就会返回1064错误 -- 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 编号,ename as 姓 名,sal as 工资 from emp at line 1 3、 查询字段时可以使用算术运算符
select empno,ename,sal,sal1000 as 涨薪后,deptno from emp where sal 2500;
select empno,ename,sal,comm,salcomm from emp; 4、去重操作 对查询到的数据进行去重操作需要使用关键字distinct
select distinct job from emp;
如果关键字distinct后包含多个列表示的是对后面的所有列组合去重 而不是单独的某一列去重
select distinct job,deptno from emp; 5、排序
为查询到的数据排序需要使用关键字order by
默认情况下是按照升序排列的
select * from emp order by sal;
asc 表示升序可以默认不写
select * from emp order by sal asc;
desc 表示降序
select * from emp order by sal desc;
在sal工资升序的情况下deptno部门编号按照降序排列
select * from emp order by sal asc ,deptno desc;
