简单的MySQL查询语句

简单的MySQL查询语句

-- 对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;

-- 起别名
select empno 员工编号,ename 姓名, sal 工资 from emp;
-- as alias 别名
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;特殊字符会报错
-- 算数运算符
select empno ,ename,sal,sal+1000,deptno from emp where sal <2500;
select empno,ename,sal,comm,sal+comm from emp; -- ???

-- 去重操作：
select distinct job from emp;
select distinct job from emp;

select distinct job,deptno from emp; -- 对后面的所有列去重，而不是单列
-- 排序：默认升序排列
select * from emp order by sal;
select * from emp order by sal asc;-- 升序 可以默认不写
select * from emp order by sal desc;-- desc 降序
select * from emp order by sal asc, deptno desc; -- 在工资升序的情况下按照降序排序