Mysql笔记1

1、什么是数据库?什么是数据库管理系统?什么是SQL?他们之间的关系是什么?


数据库:
        英文单词DateBase,简称DB。按照一定格式存储数据的一些文件的组合。
        顾名思义:存储数据的仓库,实际上就是一堆文件。这些文件中存储了具有特定格式的数据

数据库管理系统:
    DateBaseManagement,简称DBMS。
    数据库管理系统是专门用来管理数据库中数据的,数据库管理系统可以对数据库当中的数据进行增删改查。

常见的数据库管理系统:
    MySQL Oracle Ms sqlServer DB2 sybase等

SQL:数据化查询语言
程序员需要学习SQL语句,程序员通过编写SQL语句,然后DBMS负责执行SQL语句,最终来完成数据库中数据的
增删改查操作。

三者之间的关系?
DBMS --执行--> SQL --->操作---->DB

2、安装MySQL数据库管理系统


第一步:先安装,选择“经典版”
第二部:需要进行MySQL数据库实例配置。
注意:一路下一步就行了
需要注意的事项?
    端口号:
     端口号port是任何一个软件/应用都会有的,端口号是应用的唯一代表,端口号通常和IP地址在一块
     IP地址用来定位计算机的,端口号port在同一台计算机上,端口号不能重复,具有唯一性

    mysql数据库启动的时候,这个服务占有的默认端口号是3306

    字符编码方式?
     设置mysql数据库的字符编码方式为UTF8
     一定要注意:先选中第3个单选按钮,然后再选择utf8字符集

    服务名称?
     默认是:MySQL
     
    设置密码的同时,可以激活root账户远程访问。
    激活:表示root账号可以在外地登录
    不激活:表示root账号只能在本机上使用    

3、看一下计算机上的服务,找一找MySQL的服务在哪里?


计算机 --> 右键 -->管理 -->服务和应用程序 -->服务 --->找MySQL服务
MySQL的服务,默认是“启动的状态”,只有启动了MySQL才能用,默认情况下是“自动”启动
自动启动表示下一次重启操作系统的时候 自动启动该服务

可以在服务上点击右键:
    启动
    重启服务
    停止服务

还可以改变服务的默认配置:
    服务上点击右键,属性,任何可以选择启动方式:
        自动(延迟启动)
        自动
        手动
        禁用

4、在windows操作系统当中,怎么使用命令来启动和关闭MySQL服务呢?


    语法:
         net stop 服务名称;
         net start 服务名称;


5、mysql常用命令:


    登录mysql:    mysql -uroot -p密码


    隐藏密码登录:mysql -uroot -p 


    退出mysql:exit


    查看mysql中有哪些数据库: show datebases; 
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | mysql              |
        | performance_schema |
        | test               |
        +--------------------+
        mysql默认自带了四个数据库


    如何选择使用某个数据库呢?
        use test;


    如何创建数据库:create database 名字;

    查看数据库中有哪些表:show tables;

    查看mysql数据库的版本号:
    mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 5.5.36    |
    +-----------+
    1 row in set (0.00 sec)

    查看当前使用的是哪个数据库
    mysql> select database();
    +------------+
    | database() |
    +------------+
    | zheng      |
    +------------+

    \c 用来终止语句
    mysql> show
        ->
        ->
        ->
        ->
        -> \c


6、数据库当中最基本的单元是表:table


    什么是表table?为什么用表来存储数据呢?

         姓名           性别           年龄 (列:字段)
----------------------------------------
         崔铮            男                 21 ------->行(数据、记录)
         张三            男                 20 ------->行(数据、记录)
         李四            女                 18 ------->行(数据、记录)

数据库当中是以表格的形式表示数据的,因为表比较直观

任何一张表都有行和列:
   行(row):被称为数据/记录
   列(colomn):被称为字段

9、关于SQL语句的分类?


SQL语句有很多,最好进行分门别类,这样更容易记忆
分为:
     DQL:
    数据查询语言(凡是带有select关键字的都是查询语句)
    select
     
     DML:
    数据操作语言(凡是对表当中的数据进行增删改查的都是DML)
    insert delete update
    这个主要是操作表中的数据data

     DDL:
    数据定义语言
    但是带有create、drop、alter的都是DDL
    DDL主要操作的是表的结构,不是表中的数据
    create:新建、等同于曾
    drop:删除
    alter:修改
    这个增删改和DML不同,这个主要是对表结构进行操作
    
     TCL:
    是事务控制语言
    包括:
         事务提交:commit
         事务回滚:rollback
     DCL:
    是数据控制语言
    例如:授权grant、撤销权限revoke


10、导入提前准备好的数据:


    source 地址(可以拖入)
    注意:路径不要有中文

11、关于导入的这几张表


mysql> show tables;
+-----------------+
| Tables_in_zheng |
+-----------------+
| dept            |
| emp             |
| salgrade        |
+-----------------+

dept是部门表
emp是员工表
salgrade 是工资等级表

怎么查看表中的数据呢?
    select * from 表名; //统一执行这个sql语句
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 |
+-------+-------+-------+

12、不看表中的数据,只看表的结构,有一个命令:


    desc 表名;


    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 salgrade;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | GRADE | int(11) | YES  |     | NULL    |       |
    | LOSAL | int(11) | YES  |     | NULL    |       |
    | HISAL | int(11) | YES  |     | NULL    |       |
    +-------+---------+------+-----+---------+-------+


13、简单查询


    13.1、查询一个字段?


        select 字段名 from 表名;
        其中要注意:
            select和from都是关键字。
            字段名和表名都是标识符。
        强调:
            对于SQL语句来说,是通用的,
            所有的SQL语句以“;”结尾。
            另外SQL语句不区分大小写,都行

        查询部门名字?
            mysql> select Dname from dept;
            +------------+
            | dname      |
            +------------+
            | ACCOUNTING |
            | RESEARCH   |
            | SALES      |
            | OPERATIONS |
            +------------+

    13.2、查询二个字段或多个字段怎么查呢?


        使用逗号隔开“,”
        查询部门编号和部门名?
        mysql> select deptno,dname from dept;
        +--------+------------+
        | deptno | dname      |
        +--------+------------+
        |     10 | ACCOUNTING |
        |     20 | RESEARCH   |
        |     30 | SALES      |
        |     40 | OPERATIONS |
        +--------+------------+

    13.3、查询所有字段怎么办?


        第一种方式:可以把每个字段都写上
            select a,b,c,d,e,f.... from 名字;
        
        第二种方式:可以使用*
            select * from 名字;

            这种方式的缺点:1、可读性差 2、效率低
            在实际开发中不建议

    13.4、给查询的列起别名?


        mysql> select deptno,dname as deptname from dept;
        +--------+------------+
        | deptno | deptname   |
        +--------+------------+
        |     10 | ACCOUNTING |
        |     20 | RESEARCH   |
        |     30 | SALES      |
        |     40 | OPERATIONS |
        +--------+------------+
        使用as关键字起别名。
        注意:只是将显示的查询结果列名显示为deptname,原表列名还是叫:dname
        记住:select语句是永远都不会进行修改操作的,因为只负责查询

        as关键字可以省略吗?可以的,可以将as省略为空格
        mysql> select deptno,dname deptname from dept;
        +--------+------------+
        | deptno | deptname   |
        +--------+------------+
        |     10 | ACCOUNTING |
        |     20 | RESEARCH   |
        |     30 | SALES      |
        |     40 | OPERATIONS |
        +--------+------------+

        如果你的别名里面有空格怎么办呢?
        这样肯定报错
        select deptno,dname dept name from dept;
        别名可以使用''单引号括起来
        select deptno,dname 'dept name' from dept;
        mysql> select deptno,dname 'dept name' from dept;
        +--------+------------+
        | deptno | dept name  |
        +--------+------------+
        |     10 | ACCOUNTING |
        |     20 | RESEARCH   |
        |     30 | SALES      |
        |     40 | OPERATIONS |
        +--------+------------+
        数据库中的字符串都是采用单引号括起来的。这是标准的。双引号不标准

    13.5、计算员工年薪? sal * 12
    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 |
        +--------+----------+

    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 |
    +--------+----------+

    别名要是中文的话,一定要用单引号括起来
    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、条件查询

14.1、什么是条件查询?


    不是将表中所有数据都查出来。是查询出来符合条件的
    语法格式:
        select
            字段1,字段2,字段3....
        from
            表名
        where
            条件;


14.2、都有哪些条件?


    = 等于
    查询薪资等于800的员工姓名和编号?
    mysql> select empno,ename from emp where sal=800;
    查询SMITH的编号和薪资?
    select empno,sal from emp where ename='SMITH';
    
    <>或!=不等于
    查询薪资不等于800的员工姓名和编号
    select empno,sal from emp where sal!=800;
    select empno,sal from emp where sal<>800;
    
    <小于
    查询薪资小于2000的员工姓名和编号
    select empno,ename from emp where sal < 2000;
    

    >大于
    查询薪资大于3000的员工姓名和编号
    select empno,ename from emp where sal>3000;

    <=小于等于
    查询薪资小于等于3000的员工姓名和编号
    select empno,ename from emp where sal<=3000;

    
    >=大于等于
    查询薪资大于等于3000的员工姓名和编号
    select empno,ename from emp where sal>=3000;


    between...and...两个值之间,等同于>= and <=
    查询薪资在2450和3000之间的员工信息?包括2450和3000
        第一种方式: >= and <=(and是并且的意思)
            select empno,ename from emp where sal>=2450 and sal<=3000;
        第二种方式:between...and
            select empno,ename from emp where sal between 2450 and 3000;
        注意:使用between的时候必须遵循左小右大

    is null 为null(is not null 不为空)
    查询哪些员工的津贴/补助为null
    mysql> select empno,ename,sal,comm from emp where comm is null;
    +-------+--------+---------+------+
    | empno | ename  | sal     | comm |
    +-------+--------+---------+------+
    |  7369 | SMITH  |  800.00 | NULL |
    |  7566 | JONES  | 2975.00 | NULL |
    |  7698 | BLAKE  | 2850.00 | NULL |
    |  7782 | CLARK  | 2450.00 | NULL |
    |  7788 | SCOTT  | 3000.00 | NULL |
    |  7839 | KING   | 5000.00 | NULL |
    |  7876 | ADAMS  | 1100.00 | NULL |
    |  7900 | JAMES  |  950.00 | NULL |
    |  7902 | FORD   | 3000.00 | NULL |
    |  7934 | MILLER | 1300.00 | NULL |
    +-------+--------+---------+------+

    注意:在数据库当中null不能使用等号进行衡量,需要使用is null 因为数据库中的null代表什么也没有,
    他不是一个值,所以不能用等号衡量


    查询哪些员工的津贴/补助不为null
    mysql> select empno,ename,sal,comm from emp where comm is not null;
    +-------+--------+---------+---------+
    | empno | ename  | sal     | comm    |
    +-------+--------+---------+---------+
    |  7499 | ALLEN  | 1600.00 |  300.00 |
    |  7521 | WARD   | 1250.00 |  500.00 |
    |  7654 | MARTIN | 1250.00 | 1400.00 |
    |  7844 | TURNER | 1500.00 |    0.00 |
    +-------+--------+---------+---------+


    
    and 并且
    查询工作岗位是manager并且工资大于2500的员工信息?
    mysql> select empno,ename,job,sal from emp where job = 'manager' and sal >= 2500;
    +-------+-------+---------+---------+
    | empno | ename | job     | sal     |
    +-------+-------+---------+---------+
    |  7566 | JONES | MANAGER | 2975.00 |
    |  7698 | BLAKE | MANAGER | 2850.00 |
    +-------+-------+---------+---------+

    or 或者
    查询工作岗位是manager和salesman的员工
    select empno,ename,job,sal from emp where job = 'manager' or job = 'salesman';
    mysql> select empno,ename,job,sal from emp where job = 'manager' or job = 'salesman';
    +-------+--------+----------+---------+
    | empno | ename  | job      | sal     |
    +-------+--------+----------+---------+
    |  7499 | ALLEN  | SALESMAN | 1600.00 |
    |  7521 | WARD   | SALESMAN | 1250.00 |
    |  7566 | JONES  | MANAGER  | 2975.00 |
    |  7654 | MARTIN | SALESMAN | 1250.00 |
    |  7698 | BLAKE  | MANAGER  | 2850.00 |
    |  7782 | CLARK  | MANAGER  | 2450.00 |
    |  7844 | TURNER | SALESMAN | 1500.00 |
    +-------+--------+----------+---------+

    and和or同时出现的话,有优先级问题吗?(如果想要or先执行,需要加“小括号”)

    查询工资大于2500,并且部门编号为10或者20的员工?
    mysql> select empno,ename,sal,deptno from emp where sal >= 2500 and deptno = 10 or deptno = 20;
    +-------+-------+---------+--------+
    | empno | ename | sal     | deptno |
    +-------+-------+---------+--------+
    |  7369 | SMITH |  800.00 |     20 |
    |  7566 | JONES | 2975.00 |     20 |
    |  7788 | SCOTT | 3000.00 |     20 |
    |  7839 | KING  | 5000.00 |     10 |
    |  7876 | ADAMS | 1100.00 |     20 |
    |  7902 | FORD  | 3000.00 |     20 |
    +-------+-------+---------+--------+
    这个是不对的 and 的优先级高于 or  上面这段sql的意识是 找到sal >= 2500 和deptno = 10 的 或者deptno = 20;

    mysql> select empno,ename,sal,deptno from emp where sal >= 2500 and (deptno = 10 or deptno = 20);
    +-------+-------+---------+--------+
    | empno | ename | sal     | deptno |
    +-------+-------+---------+--------+
    |  7566 | JONES | 2975.00 |     20 |
    |  7788 | SCOTT | 3000.00 |     20 |
    |  7839 | KING  | 5000.00 |     10 |
    |  7902 | FORD  | 3000.00 |     20 |
    +-------+-------+---------+--------+


    in 包含,相当于多个or(not in 不在这个范围中)
    not in在使用的时候,记的除去掉括号里面内容的null
    empno(不能有null)
    查询工作岗位是manager和salesamn的员工?
    select empno,ename,sal,job from emp where job in('manager','salesman');
    mysql> select empno,ename,sal,job from emp where job in('manager','salesman');
    +-------+--------+---------+----------+
    | empno | ename  | sal     | job      |
    +-------+--------+---------+----------+
    |  7499 | ALLEN  | 1600.00 | SALESMAN |
    |  7521 | WARD   | 1250.00 | SALESMAN |
    |  7566 | JONES  | 2975.00 | MANAGER  |
    |  7654 | MARTIN | 1250.00 | SALESMAN |
    |  7698 | BLAKE  | 2850.00 | MANAGER  |
    |  7782 | CLARK  | 2450.00 | MANAGER  |
    |  7844 | TURNER | 1500.00 | SALESMAN |
    +-------+--------+---------+----------+
    注意:in不是一个区间。in后面跟的是具体的值

    
    not not 可以取非,主要用在is 或 in 中
    is null
    is not null
    in
    not in
    


    
    like like称为模糊查询,支持%或下划线匹配
    
    %匹配任意多个字符

    下换线,一个下划线只匹配一个字符
    (% _ 是特殊符号)

    找出名字含有o的?
    mysql> select empno,ename from emp where ename like '%O%';
    +-------+-------+
    | empno | ename |
    +-------+-------+
    |  7566 | JONES |
    |  7788 | SCOTT |
    |  7902 | FORD  |
    +-------+-------+
    
    找出名字以T结尾的
    
    select empno,ename from emp where ename like '%T';
    mysql> select empno,ename from emp where ename like '%T';
    +-------+-------+
    | empno | ename |
    +-------+-------+
    |  7788 | SCOTT |
    +-------+-------+
    
    找出第二个字母是A的?
    select empno,ename from emp where ename like '_A%';
    mysql> select empno,ename from emp where ename like '_A%';
    +-------+--------+
    | empno | ename  |
    +-------+--------+
    |  7521 | WARD   |
    |  7654 | MARTIN |
    |  7900 | JAMES  |
    +-------+--------+

    如果名字中有下划线怎么办呢?
    可以使用转义字符'\' 反斜杠
    select name from student where name like '%\_%';


15、排序


15.1查询所有员工薪资,排序?


    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 |
    | FORD   | 3000.00 |
    | SCOTT  | 3000.00 |
    | KING   | 5000.00 |
    +--------+---------+

15.2    怎么进行降序呢? 在最后面加上 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 |
    | MARTIN | 1250.00 |
    | WARD   | 1250.00 |
    | ADAMS  | 1100.00 |
    | JAMES  |  950.00 |
    | SMITH  |  800.00 |
    +--------+---------+

    指定升序:
    select ename,sal from emp order by sal asc;
    因为默认是升序 所以这个asc也可以不写

15.3、可以两个字段排序吗?或者说按照多个字段排序?


    查询员工名字和薪资,要求按照薪资升序,如果薪资一样的话,在按照名字升序排列    
    select 
        ename,sal 
    from 
        emp 
    order by 
        sal asc,ename asc; 

    sal在前,起主导,只有sal相等的时候,才会考虑启用ename排序

    +--------+---------+
    | ename  | sal     |
    +--------+---------+
    | SMITH  |  800.00 |
    | JAMES  |  950.00 |
    | ADAMS  | 1100.00 |
    | MARTIN | 1250.00 |
    | WARD   | 1250.00 |
    | MILLER | 1300.00 |
    | TURNER | 1500.00 |
    | ALLEN  | 1600.00 |
    | CLARK  | 2450.00 |
    | BLAKE  | 2850.00 |
    | JONES  | 2975.00 |
    | FORD   | 3000.00 |
    | SCOTT  | 3000.00 |
    | KING   | 5000.00 |
    +--------+---------+

15.4、了解:根据字段的位置也可以排序;


    select ename,sal from emp order by 2; //2表示第二列,第二列就是sal

16、综合一点的案列:


    16、找出工资在1250到3000之间的员工信息,要求按照薪资降序排列


    mysql> select empno,ename,sal from emp where sal between 1250 and 3000 order by sal desc;
    +-------+--------+---------+
    | empno | ename  | sal     |
    +-------+--------+---------+
    |  7902 | FORD   | 3000.00 |
    |  7788 | SCOTT  | 3000.00 |
    |  7566 | JONES  | 2975.00 |
    |  7698 | BLAKE  | 2850.00 |
    |  7782 | CLARK  | 2450.00 |
    |  7499 | ALLEN  | 1600.00 |
    |  7844 | TURNER | 1500.00 |
    |  7934 | MILLER | 1300.00 |
    |  7654 | MARTIN | 1250.00 |
    |  7521 | WARD   | 1250.00 |
    +-------+--------+---------+

17、数据处理函数

17.1、数据处理函数又被称为单行处理函数

    单行处理函数的特点:一个输入对应一个输出。

    和多行处理函数相对的是:多行处理函数。(多行处理函数特点:多个输入,对应一个输出)

17.2、单行处理函数常见的有哪些?


    lower 转换小写
    mysql> select lower(ename) from emp;
    +--------------+
    | lower(ename) |
    +--------------+
    | smith        |
    | allen        |
    | ward         |
    | jones        |
    | martin       |
    | blake        |
    | clark        |
    | scott        |
    | king         |
    | turner       |
    | adams        |
    | james        |
    | ford         |
    | miller       |
    +--------------+

    upper 转换大写
    mysql> select upper(ename) from emp;
    +--------------+
    | upper(ename) |
    +--------------+
    | SMITH        |
    | ALLEN        |
    | WARD         |
    | JONES        |
    | MARTIN       |
    | BLAKE        |
    | CLARK        |
    | SCOTT        |
    | KING         |
    | TURNER       |
    | ADAMS        |
    | JAMES        |
    | FORD         |
    | MILLER       |
    +--------------+

    substr 取子串(substr(被截取的字符串,起始下标,截取的长度))
        select substr(ename,1,1) as ename from emp;
        注意:起始下标从1开始,没有0
        找出员工名字第一个字母是A的员工信息?
        第一种方式:模糊查询
        select ename from emp where ename like 'A%';
        第二种方式:substr函数
        select ename from emp where substr(ename,1,1) = 'A';

        首字母大写怎么弄?

        分割姓名的首字母
        select substr(ename,1,1) from emp;
        让首字母大写
        select upper(substr(ename,1,1)) from emp;
        除了首字母后面的字母怎么弄
        select substr(ename,2,length(ename)-1) from emp;
        拼接使用concat函数
        mysql> select concat(upper(substr(ename,1,1)),lower(substr(ename,2,length(ename)-1))) as name from emp;
        +--------+
        | name   |
        +--------+
        | Smith  |
        | Allen  |
        | Ward   |
        | Jones  |
        | Martin |
        | Blake  |
        | Clark  |
        | Scott  |
        | King   |
        | Turner |
        | Adams  |
        | James  |
        | Ford   |
        | Miller |
        +--------+

    concat 是负责进行字符串拼接的

        mysql> select concat(ename,sal) from emp;
        +-------------------+
        | concat(ename,sal) |
        +-------------------+
        | SMITH800.00       |
        | ALLEN1600.00      |
        | WARD1250.00       |
        | JONES2975.00      |
        | MARTIN1250.00     |
        | BLAKE2850.00      |
        | CLARK2450.00      |
        | SCOTT3000.00      |
        | KING5000.00       |
        | TURNER1500.00     |
        | ADAMS1100.00      |
        | JAMES950.00       |
        | FORD3000.00       |
        | MILLER1300.00     |
        +-------------------+

        length()   取长度
            mysql> select length(ename) from emp;
            +---------------+
            | length(ename) |
            +---------------+
            |             5 |
            |             5 |
            |             4 |
            |             5 |
            |             6 |
            |             5 |
            |             5 |
            |             5 |
            |             4 |
            |             6 |
            |             5 |
            |             5 |
            |             4 |
            |             6 |
            +---------------+

        trim 去除前后空白
        若是传过来的数据中有空格 查询的时候就查不到
        select * from emp where ename = ' KING';
        所以我们为了保险起见 我们可以加一个trim函数,这样就可以查到了
        select * from emp where ename = trim(' KING');

    str_to_data 将字符串转换成日期
    data_format 格式化日期
    format 设置千分位

    round 四舍五入 rount(数字,保留几位小数)

        select 'abc' as name from emp;
        所以的数据都变成了abc
        mysql> select 'abc' as name from emp;
            +------+
            | name |
            +------+
            | abc  |
            | abc  |
            | abc  |
            | abc  |
            | abc  |
            | abc  |
            | abc  |
            | abc  |
            | abc  |
            | abc  |
            | abc  |
            | abc  |
            | abc  |
            | abc  |
        
            mysql> select round(123.456,0) from emp;

            +------------------+
            | round(123.456,0) |
            +------------------+
            |              123 |
            |              123 |
            |              123 |
            |              123 |
            |              123 |
            |              123 |
            |              123 |
            |              123 |
            |              123 |
            |              123 |
            |              123 |
            |              123 |
            |              123 |
            |              123 |
            +------------------+

            mysql> select round(123.456,1) from emp; //保留一位小数 123.5
            mysql> select round(123.456,2) from emp; //保留两位小数 123.46
            mysql> select round(123.456,-1) from emp; //保留到十位 120

    rand() 生成随机数
        mysql> select rand() from emp;
        +---------------------+
        | rand()              |
        +---------------------+
        |   0.424764703423497 |
        | 0.32616739284821544 |
        |  0.3565428847042312 |
        |  0.8042122757101546 |
        |  0.9514327551717243 |
        |  0.3445269794618031 |
        |  0.8683366625274873 |
        | 0.30810240498567226 |
        |  0.9355020680795443 |
        |  0.7532031561743497 |
        |  0.9595096073667608 |
        |  0.5379385990443999 |
        |   0.811164203855362 |
        | 0.44200525287725523 |
        +---------------------+

        100以内的随机数
        select round(rand()*100,0) from emp;

    ifnull 可以将null 转换为一个具体值
        ifnull是空处理函数,专门处理空的
        在所有数据库当中,只要有NULL参与的数学运算,最终结果就是null
        select ename,sal+comm as salcomm from emp;
        mysql> select ename,sal+comm as salcomm from emp;
        +--------+---------+
        | ename  | salcomm |
        +--------+---------+
        | SMITH  |    NULL |     这里直接为空了,不正确 
        | ALLEN  | 1900.00 |
        | WARD   | 1750.00 |
        | JONES  |    NULL |
        | MARTIN | 2650.00 |
        | BLAKE  |    NULL |
        | CLARK  |    NULL |
        | SCOTT  |    NULL |
        | KING   |    NULL |
        | TURNER | 1500.00 |
        | ADAMS  |    NULL |
        | JAMES  |    NULL |
        | FORD   |    NULL |
        | MILLER |    NULL |
        +--------+---------+

        计算每个员工的年薪 =(年薪月薪+月津贴)* 12

        注意:null只要参与运算,最终结果一定是null,为了避免这个现象,需要使用ifnull函数
        ifnull函数用法:ifnull(数据,被当做哪个值)
            如果“数据”为null的时候,把这个数据当做哪个值

        补助为null的时候,将补助当做0
        mysql> select ename,(ifnull(sal,0) + ifnull(comm,0))*12 as salcomm from emp;
        +--------+----------+
        | ename  | salcomm  |
        +--------+----------+
        | 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 |
        +--------+----------+
        
    case...when...then...when...then...else...end
        当员工的工作岗位是manager的时候,工资上调10%,当工作岗位是salesman的时候,工资上调50%,其他正常。
        (注意:不修改数据库,只是将查询结果为工资上调)
        select ename,job,(case job when 'manager' then sal * 1.1 when 'saleman' then sal*1.5 else sal end) as newsal from emp;
        +--------+-----------+---------+
        | ename  | job       | newsal  |
        +--------+-----------+---------+
        | SMITH  | CLERK     |  800.00 |
        | ALLEN  | SALESMAN  | 2400.00 |
        | WARD   | SALESMAN  | 1875.00 |
        | JONES  | MANAGER   | 2975.00 |
        | MARTIN | SALESMAN  | 1875.00 |
        | BLAKE  | MANAGER   | 2850.00 |
        | CLARK  | MANAGER   | 2450.00 |
        | SCOTT  | ANALYST   | 3000.00 |
        | KING   | PRESIDENT | 5000.00 |
        | TURNER | SALESMAN  | 2250.00 |
        | ADAMS  | CLERK     | 1100.00 |
        | JAMES  | CLERK     |  950.00 |
        | FORD   | ANALYST   | 3000.00 |
        | MILLER | CLERK     | 1300.00 |
        +--------+-----------+---------+


18、分组函数(多行处理函数)
    


    多行处理函数的特点:输入多行,最终输出一行

    5个:
        count    计数
        sum        求和
        avg        平均数
        max        最大值
        min        最小值
    
    注意:
        分组函数在使用的时候必须先进行分组,然后才能用。
        如果你没有对数据进行分组,整张表默认为一组

    找出最高工资?
        mysql> select max(sal) from emp;
        +----------+
        | max(sal) |
        +----------+
        |  5000.00 |
        +----------+

    找出最低工资?
        mysql> select min(sal) from emp;
        +----------+
        | min(sal) |
        +----------+
        |   800.00 |
        +----------+

    计算工资和:
        mysql> select sum(sal) from emp;
        +----------+
        | sum(sal) |
        +----------+
        | 29025.00 |
        +----------+

    计算平均工资:
        mysql> select avg(sal) from emp;
        +-------------+
        | avg(sal)    |
        +-------------+
        | 2073.214286 |
        +-------------+
    
    计算员工数量?
        mysql> select count(ename) from emp;
        +--------------+
        | count(ename) |
        +--------------+
        |           14 |
        +--------------+

    分组函数在使用的时候需要注意哪些??
        
        第一点:分组函数会自动忽略null,你不需要提前对null进行处理
        mysql> select sum(comm) from emp;
        +-----------+
        | sum(comm) |
        +-----------+
        |   2200.00 |
        +-----------+

        第二点:分组函数中count(*) 和count(具体字段)有什么区别?
        mysql> select count(*) from emp;
        +----------+
        | count(*) |
        +----------+
        |       14 |
        +----------+
        
        mysql> select count(comm) from emp;
        +-------------+
        | count(comm) |
        +-------------+
        |           4 |
        +-------------+

        count(具体字段):表示统计该字段下所以部位null的元素的总数
        count(*):统计表当中的总行数(只有有数据 count就++)
            因为每一行记录不可能都为null,一行数据中有一列不为null,则这段数据就是有效的

        第三点:分组函数不能够直接使用在where字句中
            找出比最低工资高的员工信息。
                select ename,salfrom emp where sal > min(sal) ;
            报错:ERROR 1111 (HY000): Invalid use of group function
        

19、分组查询(非常重要)
    


    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

    19.3、找出每个工作岗位的工资和?


        实现思路:按照工作岗位分组,然后对工资求和
            select
                job(),sum(sal)
            from
                emp
            group by
                job;

        mysql> select job,sum(sal) from emp group by job;
        +-----------+----------+
        | job       | sum(sal) |
        +-----------+----------+
        | ANALYST   |  6000.00 |
        | CLERK     |  4150.00 |
        | MANAGER   |  8275.00 |
        | PRESIDENT |  5000.00 |
        | SALESMAN  |  5600.00 |
        +-----------+----------+
        以上这个语句的执行顺序?
            先从emp表中查询数据
            根据job字段进行分组
            然后对每一组数据进行sum(sal)

        重点结论:
            在一条select语句当中,如果有group by语句的话,select后面只能跟:参加分组的字段
            以及分组函数,其他一律不能跟
        

    19.4、找出每个部门的最高薪资


        实现思路是什么?
            按照部门编号分组,求每一组的最大值
                mysql> select job,max(sal) from emp group by deptno;
                +----------+----------+
                | job      | max(sal) |
                +----------+----------+
                | MANAGER  |  5000.00 |
                | CLERK    |  3000.00 |
                | SALESMAN |  2850.00 |
                +----------+----------+


    
    19.5、使用havaing可以对分完组之后后的数据进一步过滤。


        having不能单独使用,having不能代替where,having必须和group by联合使用
        
        找出每个部门,不同工作岗位的最高薪资?
        select job,deptno,max(sal) from emp group by job,deptno;
        mysql> select job,deptno,max(sal) from emp group by job,deptno order by deptno asc;
        +-----------+--------+----------+
        | job       | deptno | max(sal) |
        +-----------+--------+----------+
        | MANAGER   |     10 |  2450.00 |
        | PRESIDENT |     10 |  5000.00 |
        | CLERK     |     10 |  1300.00 |
        | ANALYST   |     20 |  3000.00 |
        | CLERK     |     20 |  1100.00 |
        | MANAGER   |     20 |  2975.00 |
        | SALESMAN  |     30 |  1600.00 |
        | CLERK     |     30 |   950.00 |
        | MANAGER   |     30 |  2850.00 |
        +-----------+--------+----------+
    


    19.6、找出每个部门最高薪资,要求显示最高显示大于3000的?


        select job,max(sal) where sal>3000 group by deptno order by deptno asc;
        +---------+----------+
        | job     | max(sal) |
        +---------+----------+
        | MANAGER |  5000.00 |
        +---------+----------+
        这一种方式效率比较低,因为先分完组 将max(sal)的数据找出来了 最后在进行having筛选 有一部分数据白找了,所以说
        效率比较低
        select job,max(sal) from emp group by deptno having max(sal) > 3000 order by deptno;

        优化策略:where和having,优先选择where,where实在完成不ll,在选择having    

    19.7、where没办法的???


        找出每个部门平均薪资,要求显示平均薪资大于2500的
        mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)>2500 order by deptno asc;
        +--------+-------------+
        | deptno | avg(sal)    |
        +--------+-------------+
        |     10 | 2916.666667 |
        +--------+-------------+


20、总结
    select....from......where......group by.....having......order by......

    执行顺序:
        1、from
        2、where
        3、group by
        4、having
        5、select
        6、order by
        从某张表查询数据 首先where进行筛选数据 然后group by进行分组  分组之后可以使用having继续进行筛选
        筛选完了select输出 最后order by进行排序

    找出每个岗位的平均薪资,要求显示平均薪资大于1500的,除manager之外,
    要求按照平均薪资降序排列
    select job,avg(sal) as '平均薪资'from emp where job != 'manager' group by job having avg(sal)>1500 order by '平均薪资' desc;
    +-----------+-------------+
    | job       | 平均薪资          |
    +-----------+-------------+
    | PRESIDENT | 5000.000000 |
    | ANALYST   | 3000.000000 |
    +-----------+-------------+

  • 24
    点赞
  • 21
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值