一、数据库常识
数据库中最基本的单元:表
表以表格形式表示数据
每一张表都有行和列
行(row):被称为数据或记录
列(column):被称为字段
SQL语句分类
1.DQL 数据查询语言:凡是带有select关键字的都是查询语句
2.DML数据操作语言:对表中的数据进行操作
insert,delete,update
insert:增
delete:删
update:改
3.DDL数据定义语言:对表的结构进行操作
常见命令:create,drop,alter
crate:新建
drop:删除
alter:修改
4.TCL事务控制语言
事务提交:commit
事务回滚:rollback
5.DCL数据控制语言
授权:grant
撤销权限:revoke
二、语句
查询语句
-
查一个表的所有信息
select * from 表名
例如:create table abc
select * from abc -
查一个表的结构
desc 表名
例如:create table abc
desc from abc -
查一个表的多个多个字段信息
例如:create table abc
SELECT a,b FROM abc -
给字段起别名
例如:create table abc
SELECT a,b as bbb FROM abc
如果别名内有空格(加引号,例如:‘b b’ )
例如:create table abc
SELECT a,b as ‘b b’FROM abc -
字段加减乘除,数值
SELECT TenantId*10 as T10 FROM abpauditlogs
SELECT TenantId*10 as ‘我的’ FROM abpauditlogs
- 条件查询
select 字段名 from 表明 where…
语法格式
select 字段1,字段2…字段n from 表名 where 条件
select name,age from abc where age >= 20 年龄大于等于20
select name,age from abc where age <> 20 年龄不等于20
select name,age from abc where age >= 10 and age <= 40 【10,40】
select name,age from abc where age between 10 and 40 【10,40】
null && not null
注意:数据库中的null不能使用等号,要用is null 或 is not null
select name,age from abc where name is null
select name,age from abc where name is not null
## and && or
select name,age from abc where name <> ‘张三’ and age >= 20
name不是张三和年龄大于等于20
select name,age from abc where name = ‘张三’ or name = “李四”
name是张三或者是李四
and 和 or 同时出现 有优先级问题吗
and 优先级比 or 高
例如,现在 我想找出 身高大于175cm,并且体重为70kg或80kg的学生
select height,weight from student where height > 175 and weight = 70 or weight = 80
这个语句是否正确呢???
首先从语法上没有错,但是,它所表达的意思却不是题目的意思
它所表达的意思是:找出身高大于175并且体重等于70kg的学生
和
体重等于80kg所有的学生
q:那我们怎么实现正确的呢?
a:加个括号就行了
select height,weight from student where height > 175 and (weight = 70 or weight = 80)
## in
in相当于多个or(not in 表示不在这个范围中)
例如:查询名字(name)是张三和李四和王五的学生
select name,weight,height from student where name in (‘张三’,‘李四’,‘王五’)
注意:in不是一个区间,in后面跟的是具体的值
## like
like被称为模糊查询,支持%或下划线匹配
%匹配任意个字符
下划线,一个下划线只匹配一个字符
找出学生名字中含有o的
select name from student where name like ‘%o%’
找出学生名字是以J开头的
select name from student where name like ‘J%’
找出学生名字是以s结尾的
select name from student where name like ‘%s’
找出学生名字是第二个字母是A的
select name from student where name like ‘_A%’
找出学生名字是第三个字母是B的
select name from student where name like ‘__B%’
现在,假设有一张students表 ,其中,有name字段
name 字段
zhangsan
lisi
wangwu
john_son
找出名字中有"_"的
select * from students where name like ‘%_%’
说明:_要经过转义
## 排序
给学生身高进行排序
select * from students order by height
默认是升序(小到大)
怎么降序
select * from students order by height desc
升序还有一种写法
select * from students order by height asc
如果升高一样的话,还要判断名字(也是升序排列)
select * from students order by height asc,name asc
了解:根据字段的位置也可以排序
select name,height from students order by 2
2表示第二列,第二列是height
按照查询结果的第二列height ,但是不建议这样写,因为不健壮
找出身高在160到180的学生信息,要求按照身高降序排列
select height from students where height >= 160 and height <= 180 order by height desc
select height from students where height between 160 and 180 order by height desc
以上语句执行顺序
from
where
select
order by
函数
数据处理函数–单行处理函数
特点:一个输入对应一个输出
多行处理函数
特点:多个输入对应一个输出
单行处理函数
lower 转成小写
select lower(name) from students
upper 转成大写
select upper(name) from students
substr 取子串
select substr(name,1,1) from students
注意:起始下标从1开始 没有0
小题1:找出学生名字第一个字母是A的学生信息
第一种方式:模糊查询
select name from students where name like ‘J%’
第二种方式:substr函数
select name from students where substr(name,1,1) = ‘A’
concat 字符串拼接
select concat(name,number) from students
lenth 取长度
select lenth(name)from sudents
trim 去除空格
select name from students where name = trim(’ king ')
round 四舍五入
select round(money,0) from emp 保留整数
select round(money,1) from emp 保留一位小数
select round(money,2) from emp 保留两位小数
select round(money,-1) from emp 保留到十位
select round(money,-2) from emp 保留到百位
rand 生成随机数
select rand() from emp
select round(rand()*100,0) from emp 生成100以内的随机整数
ifnull----空处理函数
null只要参与运算,最终结果一定是null,为了避免这个现象,需要使用ifnull函数
ifnull函数用法:ifnull(数据,被当做哪个值)
select name,(height+ifnull(weight,0)) from students
case…when…then…when…then…else…end
当岗位是A的时候,工资上调20%,B上调30%,其他上调10%
select name,job,sal as oldsal,(case job when ‘A’ sal1.2 when ‘B’ sal1.3 else sal*1.1) as newsal from emp
## 分组函数(多行处理函数)
一共5个
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
注意:分组函数自动忽略null
分组函数中count()和count(具体字段)有什么区别???
count() 行记录
count(num) 不为null的元素的个数
分组函数不能够直接使用在where子句中
所有的分组函数可以组合起来一起用
select sum(sal),min(sal),count(name) from emp
分组查询(非常重要)
什么是分组查询?
在实际的应用中,可能有这样的需求,需要先进行分组,然后对每一组的数据进行操作
这个时候我们就要用分组查询,那么怎么进行分组查询呢???
select
…
from
…
group by
计算每个部门的工资和
计算每个工作岗位的平均薪资
找出每个工作岗位的最高薪资
将之前的关键字全部组合在一起
select
...
from
....
group by
....
order by
....
以上的关键字的顺序不能颠倒,需要记忆
执行顺序
1.from
2.where
3.group by
4.select
5.order by
分组函数在使用的时候必须进行分组,才能够使用
找出每个工作岗位的工资和
实现思路:按照工作岗位分组,然后对工资求和。
select
job,sum(sal)
from
emp
group by
job
以上语句执行顺序???
先从表中查询数据
根据job字段进行分组
然后对每一组的数据进行sum(sal)
select ename,job,sum(sal) from emp group by job
以上语句在MySQL中可以执行,但是毫无意义
在Oracle中执行报错
重要结论
在一条select 语句当中,如果有group by 语句的话
select 后面只能跟:参加分组的字段,以及分组函数
其它的一律不能跟
现在我想找出每个部门的最高薪资
实现思路是什么/??/
select x,max(sal) from emp group by x
找出每个部门,不同工作岗位的最高薪资
技巧:两个字段联合成一个字段看(两个字段联合分组)
select
x,job,max(sal)
from
emp
group by
x,job
问题:找出每个部门最高薪资,要求显示最高薪资大于3000的
第一步:找出每个部门最高薪资
按照部门编号分组,求每一组最大值
select deptno,max(sal) from emp group by deptno
第二步:要求显示最高薪资大于3000
select deptno,max(sal) from emp group by deptno having max(sal)>3000
效率比较低,考虑,现将大于3000的都找出来。然后再分组
select xxx from emp where sal>3000 group by deptno
优化策略:where和having优先选择where
where没办法的???
找出每个部门平均薪资,要求显示平均薪资高于2500的
第一步:找出每个部门平均薪资
select deptno,avg(sal) from emp group by deptno
第二步:要求显示平均薪资高于2500的
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2500
三、总结(单表查询结束)
select
…
from
…
where
…
group by
…
having
…
order by
…
执行顺序
1.from
2.where
3.group by
4.having
5.select
6.order by
问题:现在想找出每个岗位的平均薪资,要求显示平均薪资大于1500的,除manager岗位之外,要求按照平均薪资降序排列
select
job,avg(sal) as avgsal
from
emp
where
job <> ‘manager’
group by
job
having
avg(sal) > 1500
order by
avgsal desc