精华内容
下载资源
问答
  • 老杜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

    20200723203454-5f19f46e7c3b0.png

    20200723203459-5f19f473e7da5.png

    20200723203501-5f19f475dbaca.png

    展开全文
  • mysql的一条sql是如何执行的image-20200324134345377说明:mysql8.0版本已经移除了查询缓存。通信协议mysql是支持多种通信协议的,可以使用同步/异步的方式,支持长链接/短链接。通信类型同步同步通信依赖于被调用方...

    mysql的一条sql是如何执行的

    6222744d24e62bb0d0cb449fee3639d0.pngimage-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里是支持多种存储引擎的,他们是可以替换的,所以也叫做插件式的存储引擎。

    存储引擎列表

    e37c8f8946e53c28c6237dcddf563e11.pngimage-20200324130409507

    注意:这里引用mysql官网

    MyISAM

    通常情况下应用于只读或者以读为主的工作。适合只读类的数据分析项目。

    特点

    支持表级别的锁,插入和更新都会锁表,所以限制了读/写的性能。

    拥有较高的插入和查询速度。(例如,可以先设定存储引擎为MyISAM,然后插入100W的数据,然后在修改存储引擎为InnoDB来提高效率)

    存储了表的行数,count速度快。

    不支持事物。

    InnoDB

    Mysql5.7版本中的默认存储引擎。适合更新较频繁的表,存在并发读写或者有事物的场景。

    特点

    支持事物、外键,所以数据的完整性,一致性更高。

    支持行锁和表锁。

    支持读写并发,写不阻塞读(MVCC)。

    特殊的索引存储方式,减少I/O,提高查询效率

    Memory

    将所有数据存储在RAM中来提高查找非关键数据的速度。适合做临时表。

    特点

    优点:数据存在在内存中,读写速度快,

    缺点:当数据库重启或者断电情况下,数据会全部消失。

    执行引擎

    执行引擎是使用执行计划来操作存储引擎,它利用存储引擎提供的API来完成操作,最后饭数据返回给客户端。

    展开全文
  • 需要配套资源的同学可以加群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

    展开全文
  • 数据库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–操作–>DB

    2.什么是表

    表: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 
    	表名;
    

    提示:

    1. 任何一条SQL语句以“;”结尾。
    2. 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
    		..
    
    展开全文

空空如也

空空如也

1 2 3 4 5
收藏数 93
精华内容 37
关键字:

老杜mysql笔记

mysql 订阅