--给字段起别名,并查询该字段的内容(as可省略)
select 字段名 as 别名 from emp;
--给ename字段起别名为empname,并查询ename字段的内容
select ename as empname from emp;
--别名中含有空格,需要用单引号扩起来
select ename as 'emp ename' from emp;
显示:
+-----------+
| emp ename |
+-----------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+-----------+
--给多个字段同时起别名,并查询这些字段的内容(使用逗号隔开即可)
select job as 'emp job',empno as 'emp empno' from emp;
显示:
+-----------+-----------+
| emp job | emp empno |
+-----------+-----------+
| CLERK | 7369 |
| SALESMAN | 7499 |
| SALESMAN | 7521 |
| MANAGER | 7566 |
| SALESMAN | 7654 |
| MANAGER | 7698 |
| MANAGER | 7782 |
| ANALYST | 7788 |
| PRESIDENT | 7839 |
| SALESMAN | 7844 |
| CLERK | 7876 |
| CLERK | 7900 |
| ANALYST | 7902 |
| CLERK | 7934 |
+-----------+-----------+
1.4 字段计算
--对emp表的sal(月薪)字段进行计算,得到员工的年薪,并对年薪起别名为year sal
select sal*12 as 'year sal' from emp;
显示:
+----------+
| year sal |
+----------+
| 9600.00 |
| 19200.00 |
| 15000.00 |
| 35700.00 |
| 15000.00 |
| 34200.00 |
| 29400.00 |
| 36000.00 |
| 60000.00 |
| 18000.00 |
| 13200.00 |
| 11400.00 |
| 36000.00 |
| 15600.00 |
+----------+
--可以把‘year sal’改为中文的“年薪”
select sal*12 as 年薪 from emp;
1.5 条件查询大于小于
--列出某个表的字段1,字段2,字段3;条件是 字段3>99999
select 字段1,字段2,字段3 from 某表表名 where 字段3>99999;
--查询月薪大于等于1000的员工,并列出员工号,员工姓名,月薪
select empno,ename,sal from emp where sal>=1000;
显示:
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7566 | JONES | 2975.00 |
| 7654 | MARTIN | 1250.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7839 | KING | 5000.00 |
| 7844 | TURNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7902 | FORD | 3000.00 |
| 7934 | MILLER | 1300.00 |
+-------+--------+---------+
--查询月薪在800到1000的员工,并列出员工号,员工姓名,月薪
方法一(字段3>=800 and 字段3<=800):
select empno,ename,sal from emp where sal>=800 and sal<=1000;
方法二(字段3 between 800 and 1000):
mysql> select empno,ename,sal from emp where sal between 800 and 1000;
显示:
+-------+-------+--------+
| empno | ename | sal |
+-------+-------+--------+
| 7369 | SMITH | 800.00 |
| 7900 | JAMES | 950.00 |
+-------+-------+--------+
1.6 is null (注意:null在数据库中与在Java语言中是不同的,不能直接用=)
--查询所有没有津贴的员工(也就是comm值为null),并列出员工号,员工姓名,月薪
select empno,ename,sal from emp where comm is null;
--查询所有有津贴的员工(也就是comm值不为null),并列出员工号,员工姓名,月薪
select empno,ename,sal from emp where comm is not null;
--错误范例
select empno,ename,sal from emp where comm=null;
原因:null在数据库中与在Java语言中是不同的,不能直接用等号判断!
1.7 or (注意:and前后要是两个不同字段才能使用,若是同字段要使用or)
--查询中包含多个条件(where……and……)
--查询职位是manager,月薪大于1000的员工,并列出员工号,员工姓名,月薪
select empno,ename,sal from emp where job='manager' and sal>1000;
--查询职位是manager和clerk的员工,并列出员工号,员工姓名,月薪
select empno,ename,sal from emp where job='manager' or job='clerk';
--查询职位是manager和clerk,月薪大于1000的员工,并列出员工号,员工姓名,月薪,职位
select empno,ename,sal,job from emp where job='manager' or job='clerk' and sal>1000;
显示:
+-------+--------+---------+---------+
| empno | ename | sal | job |
+-------+--------+---------+---------+
| 7566 | JONES | 2975.00 | MANAGER |
| 7698 | BLAKE | 2850.00 | MANAGER |
| 7782 | CLARK | 2450.00 | MANAGER |
| 7876 | ADAMS | 1100.00 | CLERK |
| 7934 | MILLER | 1300.00 | CLERK |
+-------+--------+---------+---------+
--错误范例
select empno,ename,sal from emp where job='manager' and job='clerk';
原因:and前后要是两个不同字段才能使用,若是同字段要使用or!
1.8 and优先级高于or(系统会先执行用and连接的条件,再执行or后的条件)
--查询出部门10与部门20的经理,并列出两位经理的所有信息(deptno是部门编码的意思)
select * from emp where (deptno=10 or deptno=20) and job='manager';
显示:
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
+-------+-------+---------+------+------------+---------+------+--------+
--错误范例一:
select * from emp where job='manager' and deptno=10 or deptno=20;
显示:
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+---------+------+------------+---------+------+--------+
原因:and优先级高于or,系统会先执行完job='manager' and deptno=10,再执行or deptno=20!
其含义是:查询出部门10的经理信息,和查询出部门20所有员工的信息!
--错误范例二:
select * from emp where deptno=10 or deptno=20 and job='manager';
其含义是:查询出部门20的经理信息,和查询出部门10所有员工的信息!
--错误范例三:
select * from emp where deptno=10 and deptno=20 or job='manager';
显示:
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
+-------+-------+---------+------+------------+---------+------+--------+
原因:and前后必须是两个不同字段,系统会直接忽视deptno=10 and deptno=20,只执行job='manager'
其含义是:查询出所有的经理信息!