SQL语言
DQL:数据查询语言,凡是带有select的都是查询语句
(select。。。)
DML:数据操纵语言(Data Manipulation Language)凡是对数据进行操作的都是数据操作语言,
insert
delete
updata
这些主要用于操作表中的数据data
DDL:数据定义语言,凡是带有create,drop,alter都是DDL,
DDL主要操作的表的结构,不是表中的数据
create 新建,等同于增
drop 删除
alter 修改
TCL:Tool Command Language事务控制/命令语言,
commit 事务提交
rollback 事务回滚
DCL:Dialog Control Language事务控制语言
grant 授权
revoke 撤销授权
查看表中的数据 select * from +表名
不看数据,只看表结构 desc 表名(varchar就是java中的String)
(注意:mysql不见分号不执行。 \c(反斜杠)用来终止一条命令终止)
mysql> show
->
->
-> /c
-> \c
mysql>
查看mysql数据库的版本号:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.54 |
+-----------+
查看当前使用的是哪个数据库
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
查看数据库(不见分号不执行)
mysql> show
-> databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| liu |
| mysql |
| performance_schema |
| test |
+--------------------+
13:简单查询
13.1/查询一个字段
select 字段名 from 表名; 其中要注意select和from都是关键字字段名和表民都是标识符
强调:对于SQL语句来说,是通用的,对于所有SQl语句都以分号结尾,例外SQL语句不区分大小写
13.2/查询2个或者多个字段
使用逗号隔开。select 字段名,字段名 from 表名;
13.3/查询所有字段
法一:可以把所有字段写上
法二:可以使用*
如:Select * from xxx ;
缺点:效率低,可读性差,在实际开发中不建议,
13.4/给查询的列起别名
使用as关键字起别名。(as可以省略)
Select 字段名1,字段名2 as 别名 from 表名;
Select 字段名1,字段名2 别名 from 表名;
注:只是将显示的查询结果列名显示为别名,原表列名哈hi是不变
(!!Select语句永远不会进行修改操作,只负责查询)
起别名时别名有空格怎么办?
Select deptno,dname dept deptname from表名;
DBMS看到这样语句,进行SQl语句编译,不符合语法,报错
怎么解决?
别名加双引号或者单引号都行
Select deptno,dname ‘dept deptname’ from表名;
Select deptno,dname “dept deptname ”from表名;
注意:在所有的数据库中,字符串统一使用单引号括起来,单引号是标准,双引号在oracle数据库中用不了,但是在mysql中可以使用,(最好使用单引号,)
13.5/计算员工年薪
Select ename,sal*12 from emp;(则会出现2列,一列是ename的数据,例外一列是sal乘12的数据。)
Select ename,sal*12 as ‘年薪’ from emp;(计算和改别名同时使用注:中文记得加单引号)
字段可以使用数学表达式!
- 条件查询
14.1/什么是条件查询?
不是将表中所有数据查询出来,而是查询符号条件的
语法格式:
(只显示符合条件的字段)
Select
字段一,字段2。。。。
From
表名
Where
条件;
14.2/都有哪些条件查询?
= 等于
查询工资等于800的字段信息(只显示符合条件的字段,如不显示sal那一列)
Select empno,ename from emp where sal=800;
查询simith的字段信息(字符串比较用单引号)
Select empno,sal from emp where ename=‘simith’;
!=不等于(等同于<> 小于和大于号组成的)
Between ... and。。 2个值之间,等同于>=and<=
查询薪资在200-300的员工字段信息
Select empno ename from emp where sal>=200 and sal<=300;
Select empno ename from emp where between 200 and 300;
注意:使用between and 的时候,必须遵循小左大右!!且Between是闭区间,包含2端的值。
Is null 为null(is not null :不为空)
查询哪些员工的津贴/补助为null?
Select empno,ename,sal,comm from emp where comm is null;
Select empno,ename,sal,comm from emp where comm is not null;
注意:数据库中null不能使用等号进行衡量(如where comm = null)因为数据库中的null代表什么也没有,不是一个值,所以不能使用等号衡量
And 并且
查询工作岗a'na位是manager且工资大于2500的
Select empno,ename,sal,comm from emp where job=‘manager’ and sal>2500;
Or 或者
查询工作岗位是manager或salesman的员工
Select empno,ename,sal,comm from emp where job=‘manager’or job=’salesman’;
And和or同时出现有优先级吗?
查询工资大于2500并且部门编号为10或者20的员工
Select * from emp where sal > 2500 and deptno=10 or deptno = 20;
以上语句出现的问题:
And语句比or优先级高,先执行and再执行or
意为:找出工资大于2500并且部门编号为10的员工,或者编号为20的所有员工
Select * from emp where sal > 2500 and (deptno=10 or deptno = 20;)
And和or同时出现,and优先级高如果想让or先执行,加括号来实现优先级问题,
在开发中不确定优先级,加小括号就行
In包含相当于多个or(not in :不在这个范围内)
查询工作岗位是manager和salesman的员工?
Select empno,ename,job from emp where job in(‘manager’, ’salesman’);
Select empno,ename,job from emp where job =‘manager’ or job=’salesman’
注意 in 不是一个区间,in后面跟着具体的值。
查询工资是800和3000和200的员工
Select empno,ename,job from emp where sal in(800 ,3000,200);//不是800-3000啥的
Select empno,ename,job from emp where sal =800 or sal=3000 or sal=200;
Select empno,ename,job from emp where sal not in(800 ,3000,200);
:is null 。Is not null,in ,not in
Not 可以取非,主要用在is或者in中:is null 。Is not null,in ,not in
Like 称为模糊搜索,支持%或下划线匹配
% 匹配任意多个字符
_ 下划线,匹配任意一个字符一个下划线只匹配一个字符
(%是一个特殊符号,_也是一个特殊符号)
找出名字里面含有‘o'的
Select ename from emp where ename like ’%o%‘;
找出名字以’T‘结尾的
Select ename from emp where ename like ’%T‘;
找出名字以’K‘开始的
Select ename from emp where ename like ’K%‘;
找出第二个字母是’A‘的
Select ename from emp where ename like ’_a%‘;
找出第三个字母是’R‘的
Select ename from emp where ename like ’__R%‘;
注意
找出名字中有’_‘的?
Select ename from emp where ename like ’%_%‘;//这样不行
Select ename from emp where ename like ’%\_%‘;//只能这样 ,’\‘反斜杠转义字符
- 排序
15.1查询所有员工薪资,排序?
Select ename ,sal from emp order by sal; //默认升序由上到下从小到大排序
15.2怎么降序?
Select ename ,sal from emp order by sal desc;//指定降序
Select ename ,sal from emp order by sal asc;//指定升序
15.3,可以2个字段排序吗?或者说按照多个字段排序?
擦查询员工名字和薪资,要求按照薪资升序,如果薪资一样的话,再按照名字升序排序。
Select ename,sal from emp order by sal asc,ename asc;//sal在前起主导左右,只有sal相等才会考虑启用ename排序。
15.4了解:根据字段的位置也可以排序
Select ename,sal from emp order by 2; //2表示第二列,是sal;
按照查询的第二例sal排序
(了解一下,不建议在开发中这样写,因为不健壮,因为列的顺序很容易发生变 化,列顺序修改了之后 ,2就废了)
- 综合一点的案例:
找出工资在1250到3000之间的员工的信息,要求薪资降序排列
Select ename,sal from emp where sal between 1250 and 3000 order by desc;
关键字顺序不能变
Select
....
From
...
Where
...
Order by
...
以上的语句执行顺序必须掌握:
第一步:from
第二部:where
第三步:select
第四步:order by(排序总是在最后执行)
- 数据处理函数
17.1数据处理函数又被称为单行处理函数
单行处理函数的特点是:一个输入对应一个输出,
和单行处理函数相对的是多行处理函数:多个输入,对应一个输出
17.2单行处理函数常见的问题有哪些?
Lower 转换小写
Select lower(ename) from emp; //会把ename那一列作为小写显示出来,同时自动别名改为lower(ename);所以通常为下面这种,把自动改的别名再改回来,不让它改别名
Select lower(ename) as ename from emp;
Upper 转换大写
Concat函数 对字符串进行拼接
select concat(empno,ename) from emp;
Length 取长度
Select length(ename) as enamelength from emp;
Substr 取子串(substr(被截取的字符,起始下标,截取的长度))
Select substr(ename, 0, 1) as name from emp; //无数据
Select substr(ename, 1, 1) as name from emp; //要注意,起始下标从1开始没有0.
找出员工名字第一个字母是A的员工?
法一:模糊查询
Select ename from emp where ename like ’A%’;
法二:substr函数
Select ename emp from emp where substr(ename,1,1)=’A’
如何实现首字母大写?
Select concat(upper(substr(name,1,1)),substr(name,2,length(name)-1)) as result from emp;
Trim 去空格
Select * from emp where ename=’ king’; //可能查不到
Select * from emp where ename=trim(’ king’ );//去掉空格更大概率能查到
Str_to_date 将字符转换为日期
Data_format 格式化日期
Format 设置千分位
Case...when...then...when..then...else...end
当员工的工作岗位是manager时候,工资上调10%,当岗位是salesman时,
工资上调50%
Select
ename,job,
sal as oldsal
(case job when ‘manager’ then sal*1.1 when ‘salesman’ then sal*1.5 else sal end)
as newsal
From emp;
Round 四舍五入
Select 字段 from 表名;
Select ename from emp;
Select ‘abc’ as bieming from emp; //select 后面直接跟“字面量/字面值”
Select abc from emp// 肯定报错,会把emp当作一个字段的名字,去emp表中找abc字段起来
结论:select后面可以跟某个表的字段名(可以等同看作变量名),也可以跟字面量/字面值(数据),
Select round(1237.122,0)as result from emp; //出现一列名为result 数据全为1237的。
Select round(1237.122,-1)as result from emp; //1240保留到10位
Select round(1237.122,-2)as result from emp;//1200 保留到百位
Select round(xx,x);后面的x为保留几位小数
Rand() 函数用于产生0(包含)到1(不包含)的随机数(包头不包尾)
Select rand() from emp;//生成一列名位rand()的随机数
Select round(rand()*100,0) from emp;//生成100以内的随机数
Ifnull 可以将null 转换成为一个具体值,空处理函数专门处理空的
所有数据库中,只要有NULL参与的数据运算,最终结果就是NULL!
为了避免这个现象,需要使用ifnull函数
用法:ifnull(数据,x) 如果数据为NULL,将数据当作x计算
Select ename,sal+comm as salcomm from emp;//若有comm为NULL则计算的salcomm为NULL。
计算员工的年薪:年薪=(月薪+月补助)*12
Select ename ,(sal+comm)*12 as yearsal from emp; //出现NULL
补助为NULL时,把补助记为0.
Select ename ,(sal+ifnull(comm,0))*12 as yearsal from emp //正确
- 分组函数(多行处理函数)
多行处理函数的特点:输入多行,最终输出一行。
5个:
Count 计数
Sum 求和
Avg 平均值average
Max 最大值
Min 最小值
注意:分组函数都是在使用的时候必须先进行分组才能使用。如果没有进行分组
整张表默认为一组
找出最高工资?
Select max(sal) from emp;
计算工资和?
Select sum(sal) from emp;
计算工资平均数?
Select avg(sal)from emp;
计算员工数量
Select count(ename) from emp;
分组函数在使用时有什么注意事项?
Select sum(comm) from emp;
第一点: 分组函数自动忽略null,不需要用ifnull对null进行处理
Select count(comm) from emp;//忽略了null,并不是把null看作0,所以会导致计 数不准(若补助为null则不会计数,是0会计数 null不是数值,不能用= 要用is null)
第二点:分组函数中count(*)和count(具体字段)有什么区别?
count(*):表示统计表当中的总行数(只要有一行数据就count++)
因为每一行记录不可能都为null,一行中有一列不为null则这行数据就是有效的
count(具体字段):表示该字段中所有不为NULL的元素的总数。
第三点:分组函数不能直接出现在where子句中
找出比最低工资高的员工信息?
Select ename,sal,from emp where sal >min(sal);
//ERROR 1111(HY000):Invalid use of group function
????????????????????????
表面上没问题,但是报错,学完分组查询(group by)就知道了
第四点:所有的分组函数可以结合起来使用。
Select sum(sal),min(sal),max(sal),count(*) from emp:
- 分组查询(非常重要!!!!!!!!!! 五颗星*****)
19.1什么是分组查询?
在实际的应用中可能有这样的需求,需要先进行分组,然后对每一组数据进行操作,这个时候需要分组查询
Select 。。。 from 。。。Group by。。。
计算每个部门的工资和?
计算每个工作岗位的平均薪资?
找出每个工作岗位的最高薪资?
。。。
19.2将之前的关键字全部组合在一起,来看一下执行顺序?
Select 。。。From。。。Where。。。Group by 。。。Order by。。。
以上关键字顺序不能颠倒
执行顺序1:from 2.where 3. group by 4.select 5.order by
先拿表(from),然后判断并拿到符合要求的数据 行(where),然后再分组(group by),然后根据要求去拿数据行中的数据(select),最后排序(order by)
为什么分组函数不能直接使用在where 后面?
Select ename ,sal ,from emp where sal>min(sal);//报错
//ERROR 1111(HY000):Invalid use of group function
因为分组函数在使用的时候必须先分组再使用。
Where在执行的时候还没有进行分组,所有where后面不能出现分组函数
执行顺序1:from 2.where 3. group by 4.select 5.order by
在group by之前的都不能使用分组函数
Select sum(sal) from emp ;
这个没有分组为什么sum可以用?
因为select在group by后执行,没有分组默认为整张表
19.3找出每个工作岗位的工资和
实现思路:按照工作岗位分组,然后对工资求和。
Select job,sum(sal) From emp group by job;
//结果:
Job sum(sal)
Analust 6000.00
Clerk 4250.00
Manager 8275.00
Salesman 5600.00
执行顺序?
先从emp表(from)中查询数据,根据job字段(group by)进行分组,然后(select) 对每一组字段进行sum(sal)。
Select ename,job,sum(sal) From emp group by job;
//在mysql中可以执行,但是没有意义(因为名字不好跟着job分组,没有意义),在oracle会报错。(oracle的语法更严格)
重点结论:在一条select语句中,如果有group by 语句的话,
select后面只能跟:参加分组的字段,以及分组函数。其他的一律不能跟
19.4找出每个部门(depno)的最高薪资
实现思路:
按照编号分组,求每一组最大值。
Select后面跟ename字段没有意义,例外oracle会报错。
Select ename ,depno,max(sal) From emp group by depno;//不行
Select depno,max(sal) From emp group by depno;
19.5找出每个部门(depno),不同工作岗位(job)的最高薪资?
技巧:2个字段联合成1个字段看(2个字段联合分组)。
Select depno,job, max(sal) from emp group by depno,job;
19.6找出每个部门最高薪资,要求最高薪资大于三千。
(使用having 可以对分完组后的数据进一步过滤,having不能单独使用,不能代替where,必须和group by联合使用)
第一步:找出每个部门最高薪资
按照部门分组,求每一组最大值。
Select depno,max(sal) from emp group by depno;
第二步:最高薪资大于三千。
先分组再帅选
Select depno,max(sal) from emp group by depno having max(sal)>3000;
//要用max(sal)>3000,因为已先分组再having,此时需要从max(sal)里找大于3000的。
思考:以上的sql语句效率是不是低?
比较低,实际上,先将大于3000的都找出来,在分组。
先筛选再分组
Select depno,max(sal) From emp where sal >3000 group by depno;
//这里where里面一定不能用max(sal),因为是先where 再group by ,没分组不能使用分组函数max,
优化策略:where 和 having ,优先使用where ,where 实在完成不了,再选择having。,
19.7 where 没有办法实现的??
找出每个部门的平均薪资,要求显示!!平均薪资!!高于2500的,
第一步:先找出每个部门平均薪资。
Select depno ,avg(sal) from emp group by depno;
第二步:
Select depno ,avg(sal) from emp where avg(sal)>2500 group by depno;
//显然报错,where里不能使用分组函数
Select depno ,avg(sal) from emp group by depno having avg(sal)>2500;
//筛选分组处理后的数据用having
- 大总结(单表查询学完了)
Select ...from...where... group by...having...order by...
执行顺序1:from 2.where 3. group by 4.having 5,select 6.order by
先拿表(from),然后筛选数据 行(where 这里不能使用分组函数,因为再分组前执行),然后再分组(group by),再继续筛选(having 这里可以使用分组函数),然后根据要求去拿数据行中的数据(select),最后排序(order by)
(综合运用):要求找出每个岗位的平均薪资,要求显示平均薪资大于1500的,除manager岗位之外,要求按照平均薪资降序排列。
Select
job,avg(sal)
from
emp
Where
job <>’manager’
group by
job
Having
Avg>1500
order by
Avgsal desc;