免费建立网站平台,怎么用h5做网站,网站建设技术风险,wordpress 短代码 嵌套前言#xff1a;
学习和使用数据库可以说是程序员必须具备能力#xff0c;这里将更新关于MYSQL的使用讲解#xff0c;大概应该会更新30篇#xff0c;涵盖入门、进阶、高级(一些原理分析);这一篇是讲解SELECT语句使用#xff0c;包括基本、条件、聚合函数查询#xff0c;…前言
学习和使用数据库可以说是程序员必须具备能力这里将更新关于MYSQL的使用讲解大概应该会更新30篇涵盖入门、进阶、高级(一些原理分析);这一篇是讲解SELECT语句使用包括基本、条件、聚合函数查询并且结合案例进行讲解虽然MYSQL命令很多但是自己去多敲一点到后面忘记了查一下就可以回忆起来使用了这一系列也是本人学习MYSQL做的笔记也是为了方便后面忘记查询参考资料尚硅谷、黑马、csdn和知乎博客欢迎收藏 关注本人将会持续更新。 文章目录 基本查询查询需求 条件查询运算符表查询需求(案例)结合运算符结合空/非空运算符正则表达式 聚合函数(统计函数) 课程使用数据文件: 后台私信获取。 基本查询 MySQL 数据库使用SELECT语句来查询数据。 1. 查询多个字段
以下为在MySQL数据库中查询数据通用的 SELECT 语法
SELECT 字段名,字段名... FROM 表名;
SELECT * FROM 表名; #查询所有字段查询语句中可以使用一个或者多个表表之间使用逗号(,)分割SELECT 命令可以读取一条或者多条记录。你可以使用星号*来代替其他字段SELECT语句会返回表的所有字段数据注意 星号少用
2.设置别名
SELECT 字段1 [AS 别名],字段2 [AS 别名]... FROM 表名;3.去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;4.四则运算查询
运算符描述加法-减法*乘法/DIV除法%MOD求余
查询需求
查询指定字段 enamejobsal的数据
select ename,job,sal from emp;/*
mysql select ename, job, sal from emp;
-------------------------
| ename | job | sal |
-------------------------
| SMITH | CLERK | 800 |
| ALLEN | SALESMAN | 1600 |
| WARD | SALESMAN | 1250 |
| JONES | MANAGER | 2975 |
| MARTIN | SALESMAN | 1250 |
| BLAKE | MANAGER | 2850 |
| CLARK | MANAGER | 2450 |
| SCOTT | ANALYST | 3000 |
| KING | PRESIDENT | 5000 |
| TURNER | SALESMAN | 1500 |
| ADAMS | CLERK | 1100 |
| JAMES | CLERK | 950 |
| FORD | ANALYST | 3000 |
| MILLER | CLERK | 1300 |
-------------------------
*/查询所有字段
# 方法一将所有字段名写出
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;# 方法二select * from 表名; 注意少用*
select * from emp; #注意不直观 影响效率 遵循开发规范最好不要写* 吧所有字段写出了一目了然/*
mysql select * from emp;
----------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
----------------------------------------------------------------
| 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 | MARTIN | 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 |
----------------------------------------------------------------
14 rows in set (0.00 sec)
*/查询所有员工的职位并起别名
# 方法一在字段名后面加上 as 别名
select job as 工作 from emp;# 方法二在字段名后直接加上别名
select job 工作 from emp; #as可以省略/*
mysql select job as 工作 from emp;
-----------
| 工作 |
-----------
| CLERK |
| SALESMAN |
| SALESMAN |
| MANAGER |
| SALESMAN |
| MANAGER |
| MANAGER |
| ANALYST |
| PRESIDENT |
| SALESMAN |
| CLERK |
| CLERK |
| ANALYST |
| CLERK |
-----------
14 rows in set (0.00 sec)
*/查询员工的的职位有哪些(不要重复)
# 去重关键字DISTINCT只需要在最前面加一个即可
SELECT DISTINCT job FROM emp;/*
mysql select distinct job from emp;
-----------
| job |
-----------
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
-----------
5 rows in set (0.00 sec)
*/查询员工年薪 即 sal* 12
select ename, sal, sal*10 年终奖 from emp;/*mysql select ename, sal, sal*10 年终奖 from emp;
----------------------
| ename | sal | 年终奖 |
----------------------
| SMITH | 800 | 8000 |
| ALLEN | 1600 | 16000 |
| WARD | 1250 | 12500 |
| JONES | 2975 | 29750 |
| MARTIN | 1250 | 12500 |
| BLAKE | 2850 | 28500 |
| CLARK | 2450 | 24500 |
| SCOTT | 3000 | 30000 |
| KING | 5000 | 50000 |
| TURNER | 1500 | 15000 |
| ADAMS | 1100 | 11000 |
| JAMES | 950 | 9500 |
| FORD | 3000 | 30000 |
| MILLER | 1300 | 13000 |
----------------------
14 rows in set (0.00 sec)*/条件查询 我们知道从 MySQL 表中使用 SELECT 语句来查询数据如需有条件地从表中选取数据可将 WHERE 子句添加到 SELECT 语句中。 语法
SELECT 字段名 FROM 表名 WHERE 条件;运算符表
关系运算符功能大于大于等于小于小于等于等于 或 !不等于
逻辑运算符功能AND 或 并且多个条件同时成立OR 或 ||或者多个条件任意成立一个NOT 或 非不是
其他功能BETWEEN…AND…在某个范围之间含最小、最大值IN(…)在in之后的列表中的值多选一LIKE模糊匹配_匹配单个字符%匹配任意个字符IS [NOT] NULL是 NULL_通配符占位符%通配符占位如%S
查询列表里面的元素如果遇到冲突要加
查询需求(案例)
结合运算符
查询工资等于3000的员工
SELECT * FROM emp WHERE sal3000;/*
mysql select * from emp where sal3000;
-------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
-------------------------------------------------------------
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
-------------------------------------------------------------
2 rows in set (0.00 sec)
*/查询工资小于1000的员工
SELECT * FROM emp WHERE sal1000; /*
mysql select * from emp where sal 1000;
-----------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
-----------------------------------------------------------
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
-----------------------------------------------------------
2 rows in set (0.00 sec)
*/查询工资小于等于1000的员工
SELECT * FROM emp WHERE sal1000;/*
mysql select * from emp where sal 1000;
-----------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
-----------------------------------------------------------
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
-----------------------------------------------------------
2 rows in set (0.00 sec)*/结合空/非空运算符
查询没有奖金的员工
SELECT * FROM emp WHERE comm IS NULL;/*
mysql select * from emp where comm is null;
----------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
----------------------------------------------------------------
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 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 |
| 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 |
----------------------------------------------------------------
10 rows in set (0.00 sec)
*/查询有奖金的员工
# 语法 is not
SELECT * FROM emp WHERE comm IS NOT NULL;/*
mysql select * from emp where comm is not null;
---------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
---------------------------------------------------------------
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
---------------------------------------------------------------
4 rows in set (0.00 sec)*/结合逻辑运算符
查询工资在1200到1800之间的员工(包含1200和1800岁)
# 并且三种写法
--
-- and
-- between(用于两个条件的并且)
SELECT * FROM emp WHERE sal1200 sal1800;
SELECT * FROM emp WHERE sal1200 AND sal1800;
SELECT * FROM emp WHERE sal BETWEEN 1200 AND 1800;/*
mysql select * from emp where sal1200 and sal 1800;
---------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
---------------------------------------------------------------
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
---------------------------------------------------------------
5 rows in set (0.00 sec)
*/查询职位为推销员且工资小于1500的员工
SELECT * FROM emp WHERE jobsalesman AND sal 1500;/*
mysql SELECT * FROM emp WHERE jobsalesman AND sal 1500;
---------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
---------------------------------------------------------------
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
---------------------------------------------------------------
2 rows in set (0.00 sec)
*/查询工资为800 或 3000 或 5000的员工
# 或
-- or
-- in(…………)
SELECT * FROM emp WHERE sal800 OR sal3000 OR sal5000;
SELECT * FROM emp WHERE sal IN(800,3000,5000);/*
mysql select * from emp where sal800 or sal3000 or sal5000;
---------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
---------------------------------------------------------------
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
---------------------------------------------------------------
4 rows in set (0.00 sec)
*/正则表达式
这个现用现查即可。
查询姓名为四个字的员工
SELECT * FROM emp WHERE ename LIKE ____;/*
mysql select * from emp where ename like ____;
---------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
---------------------------------------------------------------
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
---------------------------------------------------------------
3 rows in set (0.00 sec)
*/查询姓名最后一位是S的员工
SELECT * FROM emp WHERE ename LIKE %S;/*
mysql select * from emp where ename like %s;
-------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
-------------------------------------------------------------
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
-------------------------------------------------------------
3 rows in set (0.00 sec)
*/聚合函数(统计函数) SQL 允许对表中的数据进行计算将一列数据作为一个整体进行纵向计算。 函数名作用MAX(column)返回某列的最低值没有则返回NULLMIN(column)返回某列的最高值没有则返回NULLCOUNT(column)返回某列的行数不包括 NULL 值COUNT(*)返回被选列行数包括NULLSUM(column)求和AVG(column)求平均值
注意
其中 COUNT 函数可用于任何数据类型 (因为它只是计数)而 SUM 、AVG 函数都只能对数值类型做计算MAX 和 MIN 可用于数值、字符串或是日期时间数据类型
统计该企业员工数量
select count(ename) 人数 from emp;
/*
------
| 人数 |
------
| 14 |
------
1 row in set (0.01 sec)
*/统计该企业员工的平均工资
select avg(sal) 平均工资 from emp; /*
-----------
| 平均工资 |
-----------
| 2073.2143 |
-----------
1 row in set (0.00 sec)
*/查询该企业员工的最高工资
select max(sal) 最高工资 from emp;
/*
----------
| 最高工资 |
----------
| 5000 |
----------
1 row in set (0.00 sec)
*/查询该企业员工的最低工资
select min(sal) 最低工资 from emp;
/*
----------
| 最低工资 |
----------
| 800 |
----------
1 row in set (0.00 sec)
*/计算所有销售的工资之和
select sum(sal) 总工资 from emp;
/*
--------
| 总工资 |
--------
| 29025 |
--------
1 row in set (0.00 sec)
*/
