我要评论相关文章：
SQL查询语句(DQL)
一、简单查询
| 语法格式 | 功能 |
|---|---|
select 字段名1,字段名2... from 表名; |
查询表中一个或多个字段的记录 |
select * from 表名 |
查询表中所有字段记录 |
二、条件查询
-
语法介绍
语法格式 功能 select 字段名1,字段名2... from 表名 where 条件;条件查询 -
条件运算符
运算符 说明 =判等 <>或!=不等于 <小于 <=小于等于 >大于 >=大于等于 between A and B[A,B],等同于>= A and <= B is null/is not null为空 / 不为空 and并且 or或者 in/not inin等同于or, not in中的in等同于and not取非，主要在is或者in中使用 like模糊查询，支持%或者下划线匹配
三、注意
- 任何一条sql语句以
;结尾 - sql语句不区分大小写
- sql语句中字符串要求用
''单引号括起来 - 条件查询语句执行顺序：from—>where—>select
四、示例：
-
查询表中一个或多个字段的记录
mysql> select ename,sal from emp; +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | ALLEN | 1600.00 | | WARD | 1250.00 | | JONES | 2975.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | FORD | 3000.00 | | MILLER | 1300.00 | +--------+---------+ -
显示字段运算结果
mysql> select ename,sal*12 from emp; +--------+----------+ | ename | sal*12 | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 19200.00 | | WARD | 15000.00 | | JONES | 35700.00 | | MARTIN | 15000.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+ -
将查询结果中的字段按指定字符显示出来
mysql> select ename,sal*12 as '年薪' from emp; +--------+----------+ | ename | 年薪 | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 19200.00 | | WARD | 15000.00 | | JONES | 35700.00 | | MARTIN | 15000.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+ -
显示表中所有记录
mysql> select * from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ -
条件查询：查询年薪小于等于20000的员工，查询结果显示姓名、编号
mysql> select empno,ename,sal*12 from emp where sal*12<=20000; +-------+--------+----------+ | empno | ename | sal*12 | +-------+--------+----------+ | 7369 | SMITH | 9600.00 | | 7499 | ALLEN | 19200.00 | | 7521 | WARD | 15000.00 | | 7654 | MARTIN | 15000.00 | | 7844 | TURNER | 18000.00 | | 7876 | ADAMS | 13200.00 | | 7900 | JAMES | 11400.00 | | 7934 | MILLER | 15600.00 | +-------+--------+----------+ -
条件查询：姓名首字母为A—C的员工姓名
mysql> select ename from emp where ename between 'A' and 'C'; +-------+ | ename | +-------+ | ALLEN | | BLAKE | | ADAMS | +-------+注意：用between…and…查询字符串时，范围是左闭右开，如：上面[A,C)
-
条件查询：查询员工表中谁没有津贴
mysql> select ename,comm from emp where comm is null; +--------+------+ | ename | comm | +--------+------+ | SMITH | NULL | | JONES | NULL | | BLAKE | NULL | | CLARK | NULL | | SCOTT | NULL | | KING | NULL | | ADAMS | NULL | | JAMES | NULL | | FORD | NULL | | MILLER | NULL | +--------+------+ -
条件查询：查询员工表中职位为manager和salesman的员工
mysql> select ename,job from emp where job = 'manager' or job = 'salesman'; +--------+----------+ | ename | job | +--------+----------+ | ALLEN | SALESMAN | | WARD | SALESMAN | | JONES | MANAGER | | MARTIN | SALESMAN | | BLAKE | MANAGER | | CLARK | MANAGER | | TURNER | SALESMAN | +--------+----------+mysql> select ename,job from emp where job in('manager','salesman'); +--------+----------+ | ename | job | +--------+----------+ | ALLEN | SALESMAN | | WARD | SALESMAN | | JONES | MANAGER | | MARTIN | SALESMAN | | BLAKE | MANAGER | | CLARK | MANAGER | | TURNER | SALESMAN | -
条件查询：查询员工表中职位不为manager或者salesman的员工
mysql> select ename,job from emp where job != 'manager' and job != 'salesman'; +--------+-----------+ | ename | job | +--------+-----------+ | SMITH | CLERK | | SCOTT | ANALYST | | KING | PRESIDENT | | ADAMS | CLERK | | JAMES | CLERK | | FORD | ANALYST | | MILLER | CLERK | +--------+-----------+mysql> select ename,job from emp where job not in('manager','salesman'); +--------+-----------+ | ename | job | +--------+-----------+ | SMITH | CLERK | | SCOTT | ANALYST | | KING | PRESIDENT | | ADAMS | CLERK | | JAMES | CLERK | | FORD | ANALYST | | MILLER | CLERK | +--------+-----------+ -
条件查询：找出薪资大于1000并且部门编号是20或30的员工
mysql> select ename,sal,deptno from emp where sal > 1000 and (deptno=20 or deptno=30); +--------+---------+--------+ | ename | sal | deptno | +--------+---------+--------+ | ALLEN | 1600.00 | 30 | | WARD | 1250.00 | 30 | | JONES | 2975.00 | 20 | | MARTIN | 1250.00 | 30 | | BLAKE | 2850.00 | 30 | | SCOTT | 3000.00 | 20 | | TURNER | 1500.00 | 30 | | ADAMS | 1100.00 | 20 | | FORD | 3000.00 | 20 | +--------+---------+--------+ -
条件查询：查询员工姓名中含有’o’字符的员工
mysql> select ename from emp where ename like '%o%'; +-------+ | ename | +-------+ | JONES | | SCOTT | | FORD | +-------+注意：单引号
''中嵌套特殊字符%% -
条件查询：查询员工姓名中第二个字母为A的员工
mysql> select ename from emp where ename like '_A%'; +--------+ | ename | +--------+ | WARD | | MARTIN | | JAMES | +--------+拓展：
(1) 查询名字第一个字母为A ，like 'A%'
(2) 查询名字最后一个字母为T ，like '%T'
(3) 查询名字中含有下划线_，like '%\_%'
本文地址：https://blog.csdn.net/k909397116/article/details/107412981
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！
网友评论