MySQL第八章学习笔记

/*第八章 简单查询*/

/**/

create table emp(

empno int(4) primary key,

ename varchar(10),

job varchar(9),

mgr int(4),

hiredate date,

sal decimal(7,2),

comm decimal(7,2),

deptno int(2)

);

/*插入准备数据*/

insert into emp values

(7369, 'Smith', 'clerk', 7902, '1980-12-17', 800, null, 20),

(7499, 'Allen', 'salesman', 7698, '1981-02-20', 1600, 300, 30),

(7521, 'Ward', 'salesman', 7698, '1981-02-22', 1250, 500, 30),

(7566, 'Jones', 'manager', 7839, '1981-04-02', 2975, null, 20),

(7654, 'Maritn', 'salesman', 7698, '1981-09-28', 1250, 1400, 30),

(7698, 'Blake', 'manager', 7839, '1981-05-01', 2850, null, 30),

(7782, 'Clark', 'manager', 7839, '1981-06-09', 2450, null, 10),

(7788, 'Scott', 'analyst', 7566, '1987-04-19', 3000, null, 20),

(7839, 'King', 'president', null, '1981-11-17', 5000, null, 10),

(7844, 'Turner', 'salesman', 7698, '1981-09-08', 1500, 0, 30),

    (7876, 'Adams', 'clerk', 7788, '1987-05-23', 1100, null, 20),

(7900, 'James', 'clerk', 7698, '1981-12-03', 950, null, 30),

(7902, 'Ford', 'analyst', 7566, '1981-12-03', 3000, null, 20),

(7934, 'Miller', 'clerk', 7782, '1982-01-23', 1300, null, 10);

/*查询（指定所有字段）*/

select empno, ename, job, mgr, hiredate,sal,comm, deptno from emp;

select * from emp;

/*查询指定字段*/

select ename, depton, job, sal from emp;

/*去除重复记录*/

select deptno from emp ;

/*去重根据两个字段值判断*/

select distinct deptno,job from emp;

/*算术运算*/

/*根据sal 字段计算员工的年薪*/

select ename,job,deptno,sal*12 from emp;

/*查询字段别名*/

select ename,job,deptno,sal*12 yearsal from emp;

/*设置数据显示格式查询*/

select concat('姓名:',ename,',部门:',deptno,',职位:',job,',年薪:',sal@12) info from emp;

/*对查询数据进行排序*/

select ename,hiredate,sal from emp order by sal;

select ename,hiredate,sal from emp order by sal asc;

/*降序 desc */

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

/*按照指定的多字段排序*/

selec ename,hiredate,sal from emp order by sal, hiredate deac;