文章目录
Mysql
1.sql DB、DBMS分别是十分,他们之间的关系
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XTDRe2QS-1644461327957)(C:\Users\HackerL-h7\AppData\Roaming\Typora\typora-user-images\1640788266417.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-g9Q8y8cP-1644461327958)(C:\Users\HackerL-h7\AppData\Roaming\Typora\typora-user-images\1640788310929.png)]
2.什么是表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dhvrGPkV-1644461327959)(C:\Users\HackerL-h7\AppData\Roaming\Typora\typora-user-images\1640788499010.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bn64chZf-1644461327959)(C:\Users\HackerL-h7\AppData\Roaming\Typora\typora-user-images\1640788545212.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-orfkjGmM-1644461327960)(C:\Users\HackerL-h7\AppData\Roaming\Typora\typora-user-images\1640788842504.png)]
3.sql语句的分类
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cweSvD2X-1644461327960)(C:\Users\HackerL-h7\AppData\Roaming\Typora\typora-user-images\1640827509505.png)]
4.导入数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PBaM79eB-1644461327961)(C:\Users\HackerL-h7\AppData\Roaming\Typora\typora-user-images\1640828264114.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-X8fsWksI-1644461327962)(C:\Users\HackerL-h7\AppData\Roaming\Typora\typora-user-images\1640828279549.png)]
5.什么是sql脚本
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Z9VmOwKB-1644461327962)(C:\Users\HackerL-h7\AppData\Roaming\Typora\typora-user-images\1640828755527.png)]
注意:直接使用source命令可以执行sql脚本。
SQL脚本中的数据量太大的时候,无法打开,请使用source命令完成初始化。
6.删除数据库
drop databsae 数据库名称;
7.查看表的结构
desc 表的名称
mysql> show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept |
| emp |
| salgrade |
+-----------------------+
3 rows in set (0.00 sec)
mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int | NO | PRI | NULL | |
| DNAME | varchar(14) | YES | | NULL | |
| LOC | varchar(13) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int | NO | PRI | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double(7,2) | YES | | NULL | |
| COMM | double(7,2) | YES | | NULL | |
| DEPTNO | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> desc salgrade;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| GRADE | int | YES | | NULL | |
| LOSAL | int | YES | | NULL | |
| HISAL | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)
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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
9.常用命令
\c 终止语句
exit 退出mysql
10.查看创建表的语句
show create table emp;
11.简单的查询语句(DQL)
语法格式:
select 字段名称1,字段名称2,…from 表名;
1.提示 任何一条sql语句以**“;”**结束
2.sql 不区分大小写
mysql> select EMPNO,ENAME from emp;
+-------+--------+
| EMPNO | ENAME |
+-------+--------+
| 7369 | SMITH |
| 7499 | ALLEN |
| 7521 | WARD |
| 7566 | JONES |
| 7654 | MARTIN |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7788 | SCOTT |
| 7839 | KING |
| 7844 | TURNER |
| 7876 | ADAMS |
| 7900 | JAMES |
| 7902 | FORD |
| 7934 | MILLER |
+-------+--------+
14 rows in set (0.00 sec)
12 查询员工年薪?(字段可以参与数学计算)
select ename,sal*12 from emp;
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 |
+--------+----------+
14 rows in set (0.00 sec)
给查询结果的列重命名
select ename,sal*12 as yearsal from emp;
mysql> select ename,sal*12 as yearsal from emp;
+--------+----------+
| ename | yearsal |
+--------+----------+
| 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 |
+--------+----------+
14 rows in set (0.00 sec)
select ename,sal*12 as 年薪 from emp; //错误 //建议sql语句 要求字符串用单引号
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 |
+--------+----------+
14 rows in set (0.00 sec)
mysql> select empno,ename,sal*12 as yearsal from emp;
+-------+--------+----------+
| empno | ename | yearsal |
+-------+--------+----------+
| 7369 | SMITH | 9600.00 |
| 7499 | ALLEN | 19200.00 |
| 7521 | WARD | 15000.00 |
| 7566 | JONES | 35700.00 |
| 7654 | MARTIN | 15000.00 |
| 7698 | BLAKE | 34200.00 |
| 7782 | CLARK | 29400.00 |
| 7788 | SCOTT | 36000.00 |
| 7839 | KING | 60000.00 |
| 7844 | TURNER | 18000.00 |
| 7876 | ADAMS | 13200.00 |
| 7900 | JAMES | 11400.00 |
| 7902 | FORD | 36000.00 |
| 7934 | MILLER | 15600.00 |
+-------+--------+----------+
14 rows in set (0.00 sec)
13 查询全部字段
select * from emp; //实际开发中不建议使用*,效率较低
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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
14. 条件 查询 between and
语法格式 :
select
字段,字段
from
表名
where
条件;
执行顺序:先from,然后where,最后select
查询工资>3000的员工名字
mysql> select ename from emp where sal>3000;
+-------+
| ename |
+-------+
| KING |
+-------+
1 row in set (0.00 sec)
查询smith的工资
mysql> select sal from emp where ename='smith';
+--------+
| sal |
+--------+
| 800.00 |
+--------+
1 row in set (0.00 sec)
找出工资在1100- 3000的员工
mysql> select ename,sal from emp where sal<3000 and sal >1100;
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+
8 rows in set (0.00 sec)
mysql> select ename,sal from emp where sal between 1100 and 3000;
/*闭区间【1100-3000】between and 数字 左小右大*/
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
11 rows in set (0.00 sec)
/* between and 除了可以使用在数字方面,还可以使用在字符串方面 左闭右开*/
mysql> select ename from emp where ename between 'a' and 'c';
+-------+
| ename |
+-------+
| ALLEN |
| BLAKE |
| ADAMS |
+-------+
3 rows in set (0.00 sec)
找出津贴 为空值的人的名称 null
mysql> select ename from emp where comm is null;
+--------+
| ename |
+--------+
| SMITH |
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+--------+
10 rows in set (0.00 sec)
/*在数据库中 null不是一个值,代表什么也没有 为空 ,空不是一个值没不能用等号衡量,
必须使用 is null 或者is not null;*/
/*不为空*/
mysql> select ename ,sal from emp where comm is not null;
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| TURNER | 1500.00 |
+--------+---------+
4 rows in set (0.00 sec)
/*查询comm*/
mysql> select ename,comm from emp;
+--------+---------+
| ename | comm |
+--------+---------+
| SMITH | NULL |
| ALLEN | 300.00 |
| WARD | 500.00 |
| JONES | NULL |
| MARTIN | 1400.00 |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| TURNER | 0.00 |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
+--------+---------+
14 rows in set (0.00 sec)
/*找出没有津贴的人的名称*/
mysql> select ename,sal,comm from emp where comm is null or comm=0;
+--------+---------+------+
| ename | sal | comm |
+--------+---------+------+
| SMITH | 800.00 | NULL |
| JONES | 2975.00 | NULL |
| BLAKE | 2850.00 | NULL |
| CLARK | 2450.00 | NULL |
| SCOTT | 3000.00 | NULL |
| KING | 5000.00 | NULL |
| TURNER | 1500.00 | 0.00 |
| ADAMS | 1100.00 | NULL |
| JAMES | 950.00 | NULL |
| FORD | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
+--------+---------+------+
11 rows in set (0.00 sec)
/*找出工作岗位是manager 和 salesman的员工*/
SELECT ename,job FROM emp WHERE job='manager' or JOB ='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 |
+--------+----------+
7 rows in set (0.00 sec)
/*找出薪资大于3000的,并且部门编号为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 |
+--------+---------+--------+
9 rows in set (0.00 sec)
/*:运算符的优先级不确定的时候加小括号。*/
/* in 等同于 or:找出工作岗位是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 |
+--------+----------+
7 rows in set (0.00 sec)
mysql> select ename,job from emp where job in ('manager','salesman');/*in 后面不是区间,是值*/
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
not in
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 |
+--------+-----------+
7 rows in set (0.00 sec)
like 模糊查询 找出名字中含有o的
在模糊查询当中,必须账务两个特殊的符号 一个是% 一个是_
mysql> select ename from emp where ename like '%o%';//名字中带o的
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD |
+-------+
3 rows in set (0.00 sec)
/*找出第二个字母是A的*/
mysql> select ename from emp where ename like '_a%'; //第2个字母为a的
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| JAMES |
+--------+
3 rows in set (0.00 sec)
/*找出名字有下划线的 转义字符 \*/
select ename from emp where ename like '%\a%';
/*找出名字有最后一个字母待T的 */
mysql> select ename from emp where ename like '%t';
+-------+
| ename |
+-------+
| SCOTT |
+-------+
1 row in set (0.00 sec)
数据排序(升序 ,降序)order by
语法
select
ename ,sal
from
emp
order by
sal;
/*找出工作岗位为manager ,工资升序排列*/
mysql> select ename ,sal from emp order by sal;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| KING | 5000.00 |
+--------+---------+
14 rows in set (0.00 sec)
mysql> select ename ,sal from emp where job='manager' order by sal;
+-------+---------+
| ename | sal |
+-------+---------+
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
+-------+---------+
3 rows in set (0.00 sec)
/*注意:默认是升序,怎么指定 升序或者降序呢?asc表示升序,desc表示降序*/
mysql> select ename ,sal from emp order by sal desc; /*工资降序*/
+--------+---------+
| ename | sal |
+--------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
14 rows in set (0.00 sec)
mysql> select ename ,sal from emp order by sal desc ,ename asc;
/*多个字段同时排序,越靠前的字段主导的权越大*/
+--------+---------+
| ename | sal |
+--------+---------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
14 rows in set (0.01 sec)
mysql> select ename ,sal from emp order by 2;/*按照第二查询的字段排序*/
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| KING | 5000.00 |
+--------+---------+
14 rows in set (0.00 sec)
/*找出工作岗位为saleman的员工,并要求按照薪资的降序排列
语法: selec 3
字段
from
表 1
where
条件 2
order by 4
字段;
*/
mysql> select ename,sal,job from emp where job='salesman'order by sal desc;
+--------+---------+----------+
| ename | sal | job |
+--------+---------+----------+
| ALLEN | 1600.00 | SALESMAN |
| TURNER | 1500.00 | SALESMAN |
| WARD | 1250.00 | SALESMAN |
| MARTIN | 1250.00 | SALESMAN |
+--------+---------+----------+
4 rows in set (0.00 sec)
分组函数 count sum avg max min
注意:
所有的分组函数都是对”某一组“数据进行操作,一共五个函数,也叫多行处理函数,分组函数自动忽略null,sum函数自动忽略null
/*找出员工工资的总和*/
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.01 sec)
/*找出员工工资的最低*/
mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
| 800.00 |
+----------+
1 row in set (0.00 sec)
/*找出员工工资的最高*/
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
| 5000.00 |
+----------+
1 row in set (0.01 sec)
/*找出员工工资的平均工资*/
mysql> select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)
/*找出员工人数*/
mysql> select count(sal) from emp;
+------------+
| count(sal) |
+------------+
| 14 |
+------------+
1 row in set (0.00 sec)
单行处理函数 所有数据库。只要null参与的计算全都为null
ifnull(可能为null的数据,被当成什么处理)
什么是单行处理函数 ?
输入一行,输出一行。
计算每个员工的年薪?
mysql> select (sal+comm)*12 from emp;
+---------------+
| (sal+comm)*12 |
+---------------+
| NULL |
| 22800.00 |
| 21000.00 |
| NULL |
| 31800.00 |
| NULL |
| NULL |
| NULL |
| NULL |
| 18000.00 |
| NULL |
| NULL |
| NULL |
| NULL |
+---------------+
14 rows in set (0.01 sec)
mysql> select ename ,(ifnull(comm,0)+sal)*12 from emp;
+--------+-------------------------+
| ename | (ifnull(comm,0)+sal)*12 |
+--------+-------------------------+
| SMITH | 9600.00 |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | 35700.00 |
| MARTIN | 31800.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 |
+--------+-------------------------+
14 rows in set (0.00 sec)
找出工资高于平均工资的人
mysql> select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)
mysql> select ename from emp where sal>avg(sal);
ERROR 1111 (HY000): Invalid use of group function
/*分组函数不能出现在where后面*/
count * 和 count(某个字段)的区别
count(*)跟字段没关系,记录总记录的条数
count(某个字段) 字段内容 not null的条数
分组函数也能组合起来用
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 |
±-------±------------------------+
14 rows in set (0.00 sec)
#### 找出工资高于平均工资的人
```sql
mysql> select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)
mysql> select ename from emp where sal>avg(sal);
ERROR 1111 (HY000): Invalid use of group function
/*分组函数不能出现在where后面*/
count * 和 count(某个字段)的区别
count(*)跟字段没关系,记录总记录的条数
count(某个字段) 字段内容 not null的条数
分组函数也能组合起来用