-
老杜mysql笔记_老杜MySQL学习记录 — 查询
2021-02-06 02:18:07老杜MySQL学习记录 — 查询完整的DQL语句:select … from … where … group by … having … order by … limit …执行顺序 5 1 2 3 4 6 71. ...老杜MySQL学习记录 — 查询
完整的DQL语句:select … from … where … group by … having … order by … limit …
执行顺序 5 1 2 3 4 6 7
1. 普通查询语句
基础语法: select 字段1,字段2,字段3,… from 表名;
select ename from emp;
select ename, sal*12 as ‘年薪’ from emp;//创建别名‘年薪’,单引号,as可省略
select * from emp; 查询所有字段
1.1 条件查询
运算符:
—————————————————————
= 等于
<>或!= 不等于
或>= 大于、小于
between…and… 两个值之间,左小右大,等同>=and<=。还可以用在字符串:between ‘A’ and ‘C’,[A,C),开区间
is null或is not null 为不为空
and 并且第二个条件
or 或者第二个条件
in 包含,相当于多个or,in(100,1000)指是否等于100或1000这两个值,而不是100到1000的范围
not 取非,主要用在is或者in中,is not, not in
like 模糊查询,%代表任意多个字符,_代表任意单个字符。%A%代表包含A的子串,_A%代表第二个字符是A的。
若要查询包含’%’或’_’,需要转义’\%’,’\_’
————————————————————————–
语法: select 字段1,字段2,字段3,… from 表名 where 条件;
执行顺序: 先from,然后where,最后select
select sal from emp where ename = ‘SMITH’;//VARCHAR类型字符串需要加‘’
select ename, sal from emp where sal>=1000 and sal <= 3000;//查询工资在1000和3000之间
select ename, sal from emp where sal between 1000 and 3000; //功能同上,between..and是闭区间[1000,3000],左小右大
select ename, sal from emp where sal <> 3000; // !=和<>都可以表示不等于
select ename, sal from emp where sal > 1000 and (deptno = 20 or deptno = 30);//and优先级比or高,不确定就加括号
select ename, sal from emp where sal > 1000 and deptno in (20, 30);//功能同上,in等同or
select ename from emp where ename like ‘%A%’;//找出名字中含有A字符的
Note: NULL在数据库中不是一个值,做赋值运算结果总是NULL:300+NULL=NULL,使用is null或is not null
1.2 数据排序
语法: select 字段1,字段2,…from 表名 order by 规则
asc表示升序,desc表示降序。
select ename, sal from emp order by sal;//默认按工资升序排列
select ename, sal from emp order by sal asc;//同上,升序
select ename, sal from emp order by sal desc;//降序排
select ename, sal from emp order by sal desc, ename asc;//按工资降序,工资相同按名字升序
多个条件排序,优先按前面规则,后面规则可能用不上
组合: select 字段 from 表名 where 条件 order by xxx;
执行顺序: from最先,where第二,select第三,order by最后
例子: select ename, sal*12 as ‘年薪’ from emp where job = ‘salesman’ order by ‘年薪’ desc;
例子中select语句先定好年薪,order by是按select定的别名排列
1.3 单行处理函数(输入一行输出一行)
ifnull() 空处理函数
用法: ifnull(可能为NULL的数据,被当做什么)
select ename,(sal+ifnull(comm, 0))*12 from emp;//列出所有人的年薪(工资加补贴)
1.4 分组函数(多行处理函数) 一般和group by联合使用,在group by之后执行
count计数
sum求和
avg平均值
max最大值
min最小值
所有的分组函数都是对‘某一组’数据进行操作的
select sum(sal) from emp;//求和
select max(sal) from emp;//找最大值
select count(ename) from emp;//找出ename的总人数
select count(*) from emp;//找出总人数
NOTE:分组函数自动忽略NULL
select ename, sal from emp where sal > avg(sal);//语法错误!分组函数不能直接出现在where语句中
select ename, sal from emp where sal > (select avg(sal) from emp);//子查询,查询工资高于平均工资的员工
count(*)和count(字段)区别:
count(*): 不是统计某个字段个数,是统计总条数(和某个字段无关)
count(字段): 统计某个字段中不为NULL的总条数
1.5 group by和having
group by: 按照某个字段或某些字段进行分组
having: 对分组后的数据进行再次过滤,只能在group by后联合使用
找出每个工作岗位的最高薪资:先分组再查询
select max(sal),job from emp group by job;
NOTE: 当查询语句中有group by时候,select之后只能跟分组函数和被分组的字段!!
select ename,max(sal),job from emp group by job;//错误! ename没参加分组,此时不能加入查询
多个字段联合分组:(找出每个部门不同工作岗位的最高薪资)
select max(sal), job, deptno from emp group by job, deptno;
找出每个工作岗位的最高薪资,要求显示薪资大于2500的。
select max(sal), job from emp group by job having max(sal)>2500;//效率低
select max(sal), job from emp where sal>2500 group by job;//where先过滤,减少数据查询,效率高
能使用where过滤的不要使用having,除非where不能用。
比如:找出每个岗位的平均薪资,要求显示薪资大于2000的岗位。
select avg(sal), job from emp group by job having avg(sal)>2000;//此处where后不能跟分组函数,只能having
1.6 查询结果去重 distinct
select distinct job from emp;
Note: distinct只能出现在所有字段最前面,代表所有字段联合起来去重
select distinct deptno,job from emp;
select count(distinct job) from emp;//统计岗位的数量
2. 连接查询(多张表联合查询)
根据表的连接方式划分:
内连接 假设表A和表B连接,把A和B能够匹配的记录查询出来,AB两张表没有主副之分
等值连接
非等值连接
自连接
外链接 假设表A和表B连接,AB中有一张是主表,一张副表,主要查询主表数据,捎带查询附表。
假如副表中数据没有和主表匹配上,副表自动模拟出NULL与之匹配。
左外连接(左连接):左边表是主表
右外连接(右连接):右边表是主表
全连接 (很少用)
连接查询,一般用别名,来区分两张表内相同名字的字段(类似类中成员变量)
select e.ename,d.dname from emp as e, dept as d;
2.1 笛卡尔乘积现象:如果连接两张表联合查询没有条件限制,查询结果就变成了两个表乘起来
避免了笛卡尔乘积现象,不能减少匹配次数,只是显示的结果是有效记录
select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;//SQL92, 老语法
2.2 内连接–等值连接:最大特点是,条件是等量关系
SQL99: join…on…语法
语法: …from A表 (inner 可以省略)join B表 on 连接条件 where … (表连接和where条件分离,SQL99语法更清晰)
select e.ename, d.dname from emp e inner join dept d on e.deptno=d.deptno;
2.3 内连接–非等值连接:最大特点是,条件是非等量关系
//找出员工工资所在的等级,员工表+工资等级表
select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
2.4 内连接–自连接: 最大特点是,一张表看做两张表,自己连自己
//找出每个员工的上级领导,要求显示员工名和对应领导名:
//可以分成两个表,一个员工表,一个领导表。关键:员工的领导编号,等于领导的员工编号
select
a.ename as ‘员工名’, b.ename as ‘领导名’
from
emp as a
join
emp as b
on
a.mgr = b.empno;
2.5 外连接:比内连接使用多
//找出每个员工的上级领导,要求显示员工名和对应领导名,King没有领导,也要查出来:
select
a.ename as ‘员工名’, b.ename as ‘领导名’
from
emp as a
left join //左外连接, outer可以省略
emp as b
on
a.mgr = b.empno;
//找出没有员工的部门
select
d.*
from
emp as e
right join
dept as d
on
e.deptno=d.deptno
where
e.ename is null;
NOTE:外连接最重要特点是:主表数据无条件查询出来
2.6 三张表联合查询
A join B on … join C on …表示A先和B连接,连接之后再和C连接
//找出每一个员工的部门名称和工资等级
select
e.ename, d.dname, s.grade
from
emp as e
join
dept as d
on
e.deptno = d.deptno
join
salgrade as s
on
e.sal between s.losal and s.hisal;
//找出每一个员工的部门名称和工资等级以及上级领导
select
e.ename ‘员工’, d.dname, s.grade, m.ename ‘领导’
from
emp as e
join
dept as d
on
e.deptno = d.deptno
join
salgrade as s
on
e.sal between s.losal and s.hisal
left join
emp as m
on
e.mgr = m.empno;
3. 子查询
select语句当中嵌套select语句,被嵌套的select语句是子查询,可以出现在select/from/where之后
3.1 where子语句中使用子查询
select ename,sal from emp where sal > (select avg(sal) from emp);//找出工资比平均工资高的
3.2 from后面嵌套子查询
//找出每个部门平均薪水的薪资等级
select d.deptno, s.grade from
1. select avg(e.sal) from emp as e;//找出所有人平均工资
2. select deptno, avg(sal) as average from emp group by deptno;//找出每个部门平均工资
3. 把上一步查询结果当做一个表temp
select temp.*, s.grade
from temp
join salgrade as s
on temp.average between s.losal and hisal;
也就是
select temp.*, s.grade
from (select deptno, avg(sal) as average from emp group by deptno) temp
//right join dept as d
//on temp.deptno=d.deptno
join salgrade as s
on temp.average between s.losal and hisal;
//找出每个部门薪资的平均等级
select e.deptno, s.grade from emp as e join salgrade as s on e.sal between s.losal and s.hisal;
//select temp.deptno, avg(temp.s.grade) from temp group by temp.e.deptno;不用当做临时表。。
select
e.deptno, avg(s.grade)
from
emp as e
join
salgrade as s
on
e.sal between s.losal and s.hisal
group by
e.deptno;
3.3 在select后面嵌套子查询
//找出每个员工所在的部门名,显示员工名和部门名
原来的方法:
select e.ename, d.dname from emp join dept on e.deptno = d.deptno;
新方法:
select
e.ename, (select dname from dept as d where e.deptno=d.deptno)
from
emp as e;
4. union查询, 拼接查询结果
第一个查询列的数量和第二个查询列的数量要一致!
//找出工作岗位是salesman和manager的员工
方法1: select ename, job from emp where job=’salesman’ or job=’manager’;
方法2: select ename, job from emp where job in (‘salesman’,’manager’);
方法3: select ename, job from emp where job = ‘salesman’
union
select ename, job from emp where job = ‘manager’;
5. limit(分页查询)
5.1 limit是mysql特有的,不通用。Oracle中有个类似的rownum
5.2 limit取结果集中的部分数据
5.3 语法机制
limit startIndex,length 从起始位置取几个,startIndex不写默认从0
例子: 取出工资前5名的员工(先降序,再取)
select ename, sal from emp order by dest limit 0, 5;
5.4 通用的分页sql
每页显示pagesize条记录,第n页的内容是 (n-1)*pagesize, pagesize
-
老杜mysql笔记_[每天进步一点点]mysql笔记整理(一)
2021-02-12 12:01:34mysql的一条sql是如何执行的image-20200324134345377说明:mysql8.0版本已经移除了查询缓存。通信协议mysql是支持多种通信协议的,可以使用同步/异步的方式,支持长链接/短链接。通信类型同步同步通信依赖于被调用方...mysql的一条sql是如何执行的
image-20200324134345377
说明:mysql8.0版本已经移除了查询缓存。
通信协议
mysql是支持多种通信协议的,可以使用同步/异步的方式,支持长链接/短链接。
通信类型
同步
同步通信依赖于被调用方,受限于被调用方的性能。当应用操作数据库,线程会阻塞,等待数据库的返回结果。
同步方式一般只能做到一对一。
异步
异步可以避免线程的阻塞等待,但是不能减少sql的执行时间。
异步在高并发的情况下,会为每一个sql的执行创建一个连接,避免数据混乱。缺点是服务器压力会变大(线程间的切换会占用cpu资源)。
由于异步方式比较复杂,对服务器压力较大,所以一般情况下都会采用同步的方式。
连接方式
长连接:长连接可以保持打开,减少服务端创建和释放连接的消耗。但是长时间不关闭的情况下会消耗内存,mysql默认超时时间是28800s。
短连接:短连接是操作完成后立刻close掉。
通信方式
单工:在两台计算机之间通信的时候,数据传输是单向的。
半双工:在两台计算机之间通信的时候,数据传输是双向的,但是同一时间只能有一台发送数据,一台接受数据,不能两台同时发送或接收数据。
全双工:全双工就是可以同时发送或接收数据传输。
mysql采用的通信方式是半双工的通信方式。
查询缓存
查询缓存是mysql内部自带的一个缓存模块。mysql缓存模块默认是关闭的,不推荐使用,主要原因是mysql自带的缓存应用场景有限,它要求sql语句必须完全一致,其次表里面的数据发生变化,这张表的缓存就会全部失效。一般情况下缓存都是交给ORM框架或者其他独立缓存服务的。mysql8.0版本已经将缓存模块移除了。
语法解析和预处理
语法解析(Parser)
语法解析第一步是将一条完整的sql打散成为一个一个单词,这步也被称作为词法解析。
然后才会进行语法解析,语法解析会对sql做一些语法检查,然后根据sql的语法规则,生成一个数据结构,也就是解析树(select_lex)
预处理(Preprocessor)
预处理会检查生成的解析树,解决解析器无法解析的语义。例如:它会检查表和表的列是否存在,检查别名等。
查询优化器
一条sql语句会存在多种执行方式,最终以那种方式来执行是查询优化器来决定的。
查询优化器的目的就是根据解析树生成的不同的执行计划(Execution Plan),然后选择一种最优的执行计划,mysql里面使用的是基于开销(cost)的优化器,那种开销最小,就使用那种。
优化器最终会把解析树变成一个查询执行计划,查询执行计划一个数据结构。这个执行计划不一定是最优的结果,因为mysql也有可能覆盖不到所有的执行计划。
注意:当使用Explain查看执行计划的时候,这个结果不一定是最终的执行方式。
存储引擎
在关系型数据库里,数据是存放在Table里的,这个Table在存放数据的同时,还要组织数据结构,这个存储结构是由存储引擎决定的,所以我们也可以把存储引擎叫做表类型。
在mysql里是支持多种存储引擎的,他们是可以替换的,所以也叫做插件式的存储引擎。
存储引擎列表
image-20200324130409507
注意:这里引用mysql官网
MyISAM
通常情况下应用于只读或者以读为主的工作。适合只读类的数据分析项目。
特点
支持表级别的锁,插入和更新都会锁表,所以限制了读/写的性能。
拥有较高的插入和查询速度。(例如,可以先设定存储引擎为MyISAM,然后插入100W的数据,然后在修改存储引擎为InnoDB来提高效率)
存储了表的行数,count速度快。
不支持事物。
InnoDB
Mysql5.7版本中的默认存储引擎。适合更新较频繁的表,存在并发读写或者有事物的场景。
特点
支持事物、外键,所以数据的完整性,一致性更高。
支持行锁和表锁。
支持读写并发,写不阻塞读(MVCC)。
特殊的索引存储方式,减少I/O,提高查询效率
Memory
将所有数据存储在RAM中来提高查找非关键数据的速度。适合做临时表。
特点
优点:数据存在在内存中,读写速度快,
缺点:当数据库重启或者断电情况下,数据会全部消失。
执行引擎
执行引擎是使用执行计划来操作存储引擎,它利用存储引擎提供的API来完成操作,最后饭数据返回给客户端。
-
mysql动力节点老杜_动力节点老杜 day02课堂笔记
2021-02-02 06:12:21需要配套资源的同学可以加群737676214获取1、常用的DOS命令(续)1.1、del命令,删除一个或者多个文件删除T1.class文件C:\Users\Administrator>del T1.class删除所有.class结尾的文件,支持模糊匹配C:\Users\...需要配套资源的同学可以加群737676214获取
1、常用的DOS命令(续)
1.1、del命令,删除一个或者多个文件
删除T1.class文件
C:\Users\Administrator>del T1.class
删除所有.class结尾的文件,支持模糊匹配
C:\Users\Administrator>del *.class
T1.class
T1.glass
del *ass 这个命令就会将T1.class和T1.glass都删除。
删除的一定是能匹配上的。
del *.class 这个命令中的那个“.”不要特殊化,这个“.”其实就是一个普通的字母
1.2、怎么查看本机的IP地址?
什么是IP地址?有什么用呢?
A计算机在网络当中要想定位到(连接到)B计算机,那么必须要先
知道B计算机的IP地址,IP地址也可以看做计算机在同一个网络当中的
身份证号(唯一标识)。
IP地址就相当于电话号码是一个意思。
ipconfig(ip地址的配置信息。)
ipconfig /all 该命令后面添加一个/all参数可以查看更详细的网络信息。
这个详细信息中包括网卡 的物理地址,例如:70-8B-CD-A7-BA-25
这个物理地址具有全球唯一性。物理地址通常叫做MAC地址。
1.3、怎么查看两台计算机是否可以正常通信?
ping命令
语法格式:
ping IP地址
ping 域名
ping www.baidu.com
ping 61.135.169.121 (61.135.169.121是百度的IP地址)
ping 61.135.169.121 -t (-t参数表示一直ping)
一直ping的目的可以查看网络是否稳定。
在一个DOS命令窗口中如果有一个命令一直在执行,想强行终止怎么办?
ctrl + c 组合键
http://www.baidu.com 可以打开百度(这种方式比较方便,域名更容易记忆。)
http://61.135.169.121 也可以打开百度
域名底层最终还是会被解析成IP地址的形式。
2、文本编辑快捷键:
2.1、掌握常用的通用的文本编辑快捷键很重要,可以大大提升开发效率。
所以,必须熟练掌握,从此刻开始强迫自己少用鼠标,用组合键快捷键
的方式。
2.2、常用的组合键都有哪些?
复制 ctrl + c
粘贴 ctrl + v
剪切 ctrl + x
保存 ctrl + s
撤销 ctrl + z
重做 ctrl + y
回到行首:home键
回到行尾:end键
当光标在行尾,怎么选中一行?
shift + home键
当光标在行首,怎么选中一行?
shift + end键
回到文件头:ctrl + home
回到文件尾:ctrl + end
全选:ctrl + a
查找:ctrl + f
---------------------------(以上必须会用)--------------------------
选中一个单词:鼠标双击
选中一行:鼠标连续击3次
不用鼠标选中一个单词:ctrl + shift + 右箭头/左箭头
3、计算机编程语言发展史?
第一代语言:机器语言
程序员直接编写二进制,一串二进制代码,例如:10010100010010001000....
计算机是由电流驱动的,电流只能表示两种状态:正、负。
而正可以对应1,负可以对应0.
10010010101010...这些二进制码正好和自然世界中的十进制存在转换关系。
所以很巧妙的是:计算机可以模拟现实世界当中的事物。
机器语言时期非常具有代表性的就是:打孔机。
缺点:
纸带不容易保存
另外打孔的时候是人为操作的,孔有可能打错了。孔打错了纸带就废了。
第二代语言:低级语言
非常具有代表性的:汇编语言。
汇编语言比机器语言更接近人类自然语言。
但是汇编语言还是需要专业人士进行开发,一般人拿到汇编语言也读不懂。
第三代语言:高级语言
高级语言完全接近人类自然语言,具有代表性的:
C语言:面向过程的
C++语言:一半面向过程,一半面向对象
Java语言:完全面向对象(java语言底层实际上是C++实现的。)
Python语言:面向对象
....
计算机编程语言是什么?
是一个团队,或者一个组织制定的一套固定的语法规则,
你可以学习这套语法规则,然后通过这套语法规则和计算机
交互。
我们为什么要学习汉语?
原因是我们学习了汉语之后,可以完成人和人的沟通。
我们为什么要学习日语?
因为我们要和日本人沟通。。。
4、Java语言的概述以及Java语言的发展史。
JDK(Java开发工具箱,做Java开发必须安装的,这是最根本的一个环境。)
JDK不是集成开发环境。
JDK这个开发工具箱中是Java最核心的库。
98年的时候:Java升级到JDK1.2,Java被分为三大块:
J2SE:标准版(基础,要学java,必须先学习SE。基础语法+基础库)
J2EE:企业版(专门为企业开发软件,为企业提供解决方案。例如:OA办公系统,保险行业的系统,金融行业的系统,医院系统....)
J2ME:微型版(专门为微型设备做嵌入式开发的。)
java诞生十周年改了名字:
JavaSE
JavaEE
JavaME
-
数据库MySQL(一)(老杜视频的笔记)
2020-09-10 21:21:47数据库MySQL1.sql、DB、DBMS分别是什么,它们之间的关系?2.什么是表3.学习MySQL主要还是学习通用的SQL语句,那么SQL语句包括增删改查,SQL语句是怎么分类的?4、导入数据5 .sql文件如何插入一段漂亮的代码片生成一...数据库
MySQL
笔记来自于B站老杜数据库课程,这里主要是整理。
1.SQL、DB、DBMS及其中关系?
DB:DataBase(数据库,数据库实际上是在硬盘上以文件的形式存在的)
DBMS:DataBase Management System(数据库管理系统,常见的有:MySQL、Oracle、DB2、Sybase、SqlSever…)
SQL:结构化查询语言,是一门标准通用的语言。标准的SQL语言适合于所有的数据库产品。
SQL属于高级语言。只要能看懂英语单词的,写出来的SQL语句,可以读懂什么意思。
SQL语句在执行的时候,实际上内部也会先进行编译,然后再执行SQL。(SQL语句的编译有DBMS完成)DBMS负责执行SQL语句,通过执行SQL语句来操作DB当中的数据。
DBMS–执行–>SQL–操作–>DB2.什么是表
表:table,是数据库的基本组成单元,所有的数据都以表格的形式组织,目的是可读性强。
一个表包括行和列
行:被称为数据/记录(data)
列:被称为字段学号(int) 姓名(varchar) 110 张三 每一个字段应该包含:
字段名、数据类型、相关的约数3.SQL语句分类
DQL(数据查询语言):查询语句,凡是select语句都是DQL。
DML(数据操作语言):insert、delete、update,对表当中的数据进行增删改。
DDL(数据定义语言):create、drop、alter,对表结构进行增删改。
**TCL(事务控制语言):commit提交事务,rollback回滚事务。(TCL中的T是Transaction)
DCL(数据控制语言):grant授权,revoke撤销权限等。4、导入数据
第一步:登录mysql数据库管理系统
dos命令窗口:mysql -uroot -p333
第二步:查看有哪些数据库
show databases;//这个不是SQL语句,属于MySQL的命令。
结果:
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
±-------------------+第三步:创建属于我们自己的数据库
create database bjpowernode;//这个不是SQL语句,属于MySQL的命令。
第四步:使用bjpowernode数据
use bjpowernode;//这个不是SQL语句,属于MySQL的命令。
第五步:查看当前使用的数据库中有哪些表?
show tables;//(这个不是SQL语句,属于MySQL的命令。)
第六步:初始化数据
mysql> source D:\course\05-MySQL\resources\bjpowernode.sql
注意:数据初始化完成之后,有三张表:
±----------------------+
| Tables_in_bjpowernode |
±----------------------+
| dept |
| emp |
| salgrade |
±----------------------+5 .sql文件
bjpowernode.sql,这个文件以sql结尾,这样的文件被称为“SQL脚本”。什么是SQL脚本呢?
当一个文件的扩展名是.sql,并且该文件中编写了大量的SQL语句,我们称这样的文件为SQL脚本。
注意:直接使用source命令可以执行SQL脚本。
SQL脚本中的数据量太大的时候,无法打开,请使用source命令完成初始化。6.删除数据库
drop database bjpowernode;
7.查看表结构
±----------------------+
| Tables_in_bjpowernode |
±----------------------+
| dept | (部门表)
| emp | (员工表)
| salgrade | (工资等级表)
±----------------------+查看各个表的具体结构
mysql> desc dept;
±-------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------±------------±-----±----±--------±------+
| DEPTNO | int(2) | NO | PRI | NULL | | 部门编号
| DNAME | varchar(14) | YES | | NULL | | 部门名称
| LOC | varchar(13) | YES | | NULL | | 部门位置
±-------±------------±-----±----±--------±------+mysql> desc emp;
±---------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±---------±------------±-----±----±--------±------+
| EMPNO | int(4) | NO | PRI | NULL | | 员工编号
| ENAME | varchar(10) | YES | | NULL | | 员工姓名
| JOB | varchar(9) | YES | | NULL | | 工作岗位
| MGR | int(4) | YES | | NULL | | 上级领导编号
| HIREDATE | date | YES | | NULL | | 入职日期
| SAL | double(7,2) | YES | | NULL | | 月薪
| COMM | double(7,2) | YES | | NULL | | 补助/津贴
| DEPTNO | int(2) | YES | | NULL | | 部门编号
±---------±------------±-----±----±--------±------+mysql> desc salgrade;
±------±--------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±--------±-----±----±--------±------+
| GRADE | int(11) | YES | | NULL | | 等级
| LOSAL | int(11) | YES | | NULL | | 最低薪资
| HISAL | int(11) | YES | | NULL | | 最高薪资
±------±--------±-----±----±--------±------+8.表中的数据
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 |
±------±-------±----------±-----±-----------±--------±--------±-------+mysql> select * from dept;
±-------±-----------±---------+
| DEPTNO | DNAME | LOC |
±-------±-----------±---------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
±-------±-----------±---------+mysql> select * from salgrade;
±------±------±------+
| GRADE | LOSAL | HISAL |
±------±------±------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
±------±------±------+9.常用命令
mysql> select database(); //查看当前使用的是哪个数据库
±------------+
| database() |
±------------+
| bjpowernode |
±------------+mysql> select version(); //查看mysql的版本号。
±----------+
| version() |
±----------+
| 5.5.36 |
±----------+\c 命令,结束一条语句。
exit 命令,退出mysql。
10.查看创建表的语句
show create table emp;
11.DQL
11-1.简单的查询语句
语法格式:
select 字段名1,字段名2,字段名3,.... from 表名;
提示:
- 任何一条SQL语句以“;”结尾。
- SQL语句不区分大小写。
特点:
- 查询列表可以是:表中字段,常量值,表达式,函数。
- 查询的结果是一个虚拟的表格。
例:
查询员工的年薪?(字段可以参与数学运算)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 |
±-------±---------+给查询结果的列重命名?
select ename,sal * 12 **as** yearsal from emp; //as可省略,空格代替
别名中有中文?
select ename,sal * 12 as 年薪 from emp; // 错误 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 |
±-------±---------+PS:标准SQL语句中要求字符串使用单引号括起来。虽然MySQL支持双引号,尽量别用。
查询所有字段
select * from emp; // 实际开发中不建议使用*,效率较低。
去重:
distinct关键字select distinct ename from emp;
distinct只能出现在所有字段的最前面。此时后面所有的字段联合起来去重
统计岗位的数量?
select count(distinct job) from emp;
‘+’号作用
MySQL中的‘+’号作用,只有一个功能,就是运算符- 如果两个操作数都是数值型,则做加法运算;
- 如果其中一方为字符型,试图将字符型数值转换成数值型,如果转换成功,则继续做加法运算,如果转换失败,则将字符型数值转换为0;
- 只要其中一方为null,结果肯定为null。
- 若要连接/拼接两个字符,则要使用函数concat,例:concat(‘你好’,‘MySQL’);
11-2.条件查询
语法格式:
select 字段1,字段2... from 表名 where 条件;
执行顺序:先from,然后where,最后select.
11-2-1.查询工资等于5000的员工姓名?
select ename from emp where sal = 5000;
结果:
±------+
| ename |
±------+
| KING |
±------+11-2-2.查询SMITH的工资?
select sal from emp where ename = 'SMITH';// 字符串使用单引号括起来。
结果:
±-------+
| sal |
±-------+
| 800.00 |
±-------+11-2-3.<,>,<=,>=
找出工资高于3000的员工?
select ename,sal from emp where sal > 3000;
11-2-4.<>,!=
找出工资不等于3000的?
select ename,sal fom emp where sal <> 3000;//sal != 3000也可以
11-2-5.and,between…and…
找出工资在1100和3000之间的员工,包括1100和3000?
select ename,sal from emp where sal > 1100 and sal < 3000;//运用到了and select ename,sal from emp where sal between 1100 and 3000; // between...and...是闭区间 [1100 ~ 3000]
between…and…在使用的时候必须左小右大。
between…and…除了可以使用在数字方面之外,还可以使用在字符串方面。select ename from emp where ename between 'A' and 'C';// 左闭右开
结果:
±------+
| ename |
±------+
| ALLEN |
| BLAKE |
| ADAMS |
±------+11-2-6.is null,is not null
找出哪些人津贴为NULL?
- 在数据库当中NULL不是一个值,代表什么也没有,为空。
- 空不是一个值,不能用等号衡量。
- 必须使用 is null或者is not null
select ename,sal,comm from emp where comm is null;
结果:
±-------±--------±-----+
| 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 |
| ADAMS | 1100.00 | NULL |
| JAMES | 950.00 | NULL |
| FORD | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
±-------±--------±-----+若使用‘=’连接判断
select ename,sal,comm from emp where comm = null;
结果:
Empty set (0.00 sec) //出现错误
找出哪些人津贴不为NULL?
select ename,sal,comm from emp where comm is not null;
结果:
±-------±--------±--------+
| ename | sal | comm |
±-------±--------±--------+
| ALLEN | 1600.00 | 300.00 |
| WARD | 1250.00 | 500.00 |
| MARTIN | 1250.00 | 1400.00 |
| TURNER | 1500.00 | 0.00 |
±-------±--------±--------+11-2-7.or
找出哪些人没有津贴?
select ename,sal,comm from emp where comm is null or comm = 0;//运用到了or
结果:
±-------±--------±-----+
| 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-2-8.in,not in,and和or联合使用
找出工作岗位是MANAGER和SALESMAN的员工?
select ename from emp where job = 'MANAGER' or job = 'SALESMAN';
结果:
±-------±---------+
| ename | job |
±-------±---------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
±-------±---------+in等同于or
select ename,job from emp where job in('SALESMAN', 'MANAGER');// in后面的值不是区间,是具体的值。
not in: 不在这几个值当中。
and和or联合起来使用:找出薪资大于1000的并且部门编号是20或30部门的员工。
select ename,sal from emp where sal > 1000 and (deptno = 20 or deptno = 30);
PS:当运算符的优先级不确定的时候加小括号。
11-2-9.模糊查询like
在模糊查询当中,必须掌握两个特殊的符号,一个是 % ,一个是 _
- % 代表任意多个字符
- _ 代表任意1个字符
找出名字当中含有O的?
select ename from emp where ename like '%O%';
结果:
±------+
| ename |
±------+
| JONES |
| SCOTT |
| FORD |
±------+找出名字中第二个字母是A的?
select ename from emp where ename like '_A%';
找出名字中有下划线的?
select name from t_user where name like '%\_%';//'\'代表转义字符
12.函数
12-1.字符函数
length函数用于获取参数值的字节个数
select length('john');
结果:
4
concat函数拼接字符串
select concat(l_name,'_',f_name) 姓名 from emp;
upper、lower函数改变大小写
substr、substring函数截取,索引从1开始//截取从指定索引处后面的所有字符 select substr('李莫愁爱上了陆展元',7); //截取从指定索引处指定字符长度的字符 select substr('李莫愁爱上了陆展元',1,3);
结果:
陆展元
李莫愁instr函数返回字符串第一次出现的索引,若找不到,则返回0
select instr('杨不悔爱上了殷六侠','殷六侠‘');
结果:
7
trim函数去前后空格
select trim('张翠山'); select trim('a' from 'aaa张aaaa翠山aaaa')//去掉a
结果:
张翠山
张翠山lpad函数用指定字符实现左填充指定长度
select lpad('aaa','5','*');
结果:
**aaa
rpad函数同上
replace函数替换select replace('aaabbbddd','ddd','ccc');
结果:
aaabbbccc
12-2.数学函数
round()函数四舍五入
ceil函数向上取整
floor函数向下取整
truncate函数截断
mod函数取余12-3.日期函数
now函数
select now();
curdate函数返回当前日期,不包含时间
select curdate();
curtime函数返回当前时间,不包含日期
获取指定部分,年,月,日select year(now()) as '年';
str_to_date函数
str_to_date('9-13-1999','%m-%d-%Y');
date_format函数
date_format('2018/6/6','%Y年%m月%d日');
12-4.其他函数
version()
database()
user()13.排序(升序,降序)
按照工资升序,找出员工名和薪资?
select ename,sal from emp order by sal;
PS:默认是升序。怎么指定升序或者降序呢?asc表示升序,desc表示降序。
select ename , sal from emp order by sal; // 升序 select ename , sal from emp order by sal asc; // 升序 select ename , sal from emp order by sal desc; // 降序
按照工资的降序排列,当工资相同的时候再按照名字的升序排列。
select ename,sal from emp order by sal desc , ename asc;
PS:越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候,才会启用后面的字段。
找出工作岗位是SALESMAN的员工,并且要求按照薪资的降序排列。
select ename,job,sal from emp where job = 'SALESMAN' order by sal desc;
总结:
select 字段 3 from 表名 1 where 条件 2 order by .... 4 //order by是最后执行的。
14.分组函数
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
记住:所有的分组函数都是对“某一组”数据进行操作的。找出工资总和?
select sum(sal) from emp;
找出最高工资?
select max(sal) from emp;
找出总人数?
select count(*) from emp; select count(ename) from emp;
count(*)和count(具体的某个字段),他们有什么区别?
- count(*):不是统计某个字段中数据的个数,而是统计总记录条数。(和某个字段无关)
- count(comm): 表示统计comm字段中不为NULL的数据总数量。
分组函数一共5个。
分组函数还有另一个名字:多行处理函数。
多行处理函数的特点:输入多行,最终输出的结果是1行。分组函数也能组合起来用:
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
结果:
±---------±---------±------------±---------±---------+
| count(*) | sum(sal) | avg(sal) | max(sal) | min(sal) |
±---------±---------±------------±---------±---------+
| 14 | 29025.00 | 2073.214286 | 5000.00 | 800.00 |
±---------±---------±------------±---------±---------+分组函数自动忽略NULL。
select count(comm) from emp;
结果:
±------------+
| count(comm) |
±------------+
| 4 |
±------------+select sum(comm) from emp; //select sum(comm) from emp where comm is not null; 不需要额外添加这个过滤条件。sum函数自动忽略NULL。
结果:
±----------+
| sum(comm) |
±----------+
| 2200.00 |
±----------+找出工资高于平均工资的员工?
select ename,sal from emp where sal > avg(sal); //ERROR 1111 (HY000): Invalid use of group function
分析:
以上的错误信息:无效地使用了分组函数。
原因:SQL语句当中有一个语法规则,分组函数不可直接使用在where子句当中。
why——>因为group by是在where执行之后才会执行的。select 5 .. from 1 .. where 2 .. group by 3 .. having 4 .. order by 6 ..
解决:
第一步:找出平均工资select avg(sal) from emp;
±------------+
| avg(sal) |
±------------+
| 2073.214286 |
±------------+第二步:找出高于平均工资的员工
select ename,sal from emp where sal > 2073.214286;
±------±--------+
| ename | sal |
±------±--------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
±------±--------+以上两步合为一步:
select ename,sal from emp where sal > (select avg(sal) from emp);
15.单行处理函数
什么是单行处理函数?输入一行,输出一行。
计算每个员工的年薪?
select ename,(sal+comm)*12 as yearsal from emp;
重点:所有数据库都是这样规定的,只要有NULL参与的运算结果一定是NULL。
使用ifnull函数:select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
ifnull() 空处理函数?
- ifnull(可能为NULL的数据,被当做什么处理)
- 属于单行处理函数
select ename,ifnull(comm,0) as comm from emp;
±-------±--------+
| ename | comm |
±-------±--------+
| SMITH | 0.00 |
| ALLEN | 300.00 |
| WARD | 500.00 |
| JONES | 0.00 |
| MARTIN | 1400.00 |
| BLAKE | 0.00 |
| CLARK | 0.00 |
| SCOTT | 0.00 |
| KING | 0.00 |
| TURNER | 0.00 |
| ADAMS | 0.00 |
| JAMES | 0.00 |
| FORD | 0.00 |
| MILLER | 0.00 |
±-------±--------+16.group by和having
group by : 按照某个字段或者某些字段进行分组。
having : having是对分组之后的数据进行再次过滤。
案例:找出每个工作岗位的最高薪资。select max(sal),job from emp group by job;
±---------±----------+
| max(sal) | job |
±---------±----------+
| 3000.00 | ANALYST |
| 1300.00 | CLERK |
| 2975.00 | MANAGER |
| 5000.00 | PRESIDENT |
| 1600.00 | SALESMAN |
±---------±----------+注意:分组函数一般都会和group by联合使用,这也是为什么它被称为分组函数的原因。
并且任何一个分组函数(count sum avg max min)都是在group by语句执行结束之后才会执行的。
当一条SQL语句没有group by的话,整张表的数据会自成一组。select ename,max(sal),job from emp group by job;
以上在mysql当中,查询结果是有的,但是结果没有意义,在Oracle数据库当中会报错。语法错误。
Oracle的语法规则比MySQL语法规则严谨。
记住一个规则:当一条语句中有group by的话,select后面只能跟分组函数和参与分组的字段。每个工作岗位的平均薪资?
select job,avg(sal) from emp group by job;
±----------±------------+
| job | avg(sal) |
±----------±------------+
| ANALYST | 3000.000000 |
| CLERK | 1037.500000 |
| MANAGER | 2758.333333 |
| PRESIDENT | 5000.000000 |
| SALESMAN | 1400.000000 |
±----------±------------+多个字段能不能联合起来一块分组?
案例:找出每个部门不同工作岗位的最高薪资。select deptno,job,max(sal) from emp group by deptno,job;
找出每个部门的最高薪资,要求显示薪资大于2900的数据。
第一步:找出每个部门的最高薪资select max(sal),deptno from emp group by deptno;
±---------±-------+
| max(sal) | deptno |
±---------±-------+
| 5000.00 | 10 |
| 3000.00 | 20 |
| 2850.00 | 30 |
±---------±-------+第二步:找出薪资大于2900
select max(sal),deptno from emp group by deptno having max(sal) > 2900; // 这种方式效率低。
select max(sal),deptno from emp where sal > 2900 group by deptno; // 效率较高,建议能够使用where过滤的尽量使用where。
找出每个部门的平均薪资,要求显示薪资大于2000的数据。
第一步:找出每个部门的平均薪资select deptno,avg(sal) from emp group by deptno;
±-------±------------+
| deptno | avg(sal) |
±-------±------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
±-------±------------+第二步:要求显示薪资大于2000的数据
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
where后面不能使用分组函数:
select deptno,avg(sal) from emp where avg(sal) > 2000 group by deptno; // 错误了。
这种情况只能使用having过滤。
17.总结
select 5 .. from 1 .. where 2 .. group by 3 .. having 4 .. order by 6 ..