MySQL||数据库查询操作（一）基本查询与基本操作

向右看齐 2021-11-29 16:44 490阅读 0赞

## 建立范例数据 ##

为了方便执行操作，我们需要建立一个范例表进行操作，如我们建立一张员工表，表中包含员工的编号，姓名，职务，入职时间，当月收入（美金），当月奖金，系属部门等。

**代码如下：**

-- 删除数据表  
    DROP TABLE IF EXISTS emp ;  
    DROP TABLE IF EXISTS dept;    
    DROP TABLE IF EXISTS salgrade;  
      
    -- 创建数据表
    -- 员工表
    CREATE TABLE dept (  
        deptno  INT PRIMARY KEY,  -- 部门编号
        dname   VARCHAR(14) ,  -- 部门名称
        loc VARCHAR(13)   -- 部门地址
    ) ;  
    -- 员工表
    CREATE TABLE emp (  
        empno   INT PRIMARY KEY,  -- 员工编号
        ename   VARCHAR(10),  -- 员工姓名
        job VARCHAR(9), -- 员工职务
        mgr INT, -- 经理的员工编号
        hiredate DATE,  -- 入职日期
        sal DOUBLE,  -- 员工收入
        comm DOUBLE,  -- 奖金
        deptno  INT -- 部门表外键，代表当前员工属于哪个部门   
    );  
    alter table emp add foreign key (deptno) references dept (deptno); 
    -- 工资等级
    CREATE TABLE salgrade (   
        grade INT,   -- 工资等级
        losal DOUBLE,  -- 当前等级最低收入
        hisal DOUBLE   -- 当前等级最高收入
    );  
      
    -- 插入测试数据 —— dept  
    INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');  
    INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');  
    INSERT INTO dept VALUES (30,'SALES','CHICAGO');  
    INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');  
      
    -- 插入测试数据 —— emp  
    INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,str_to_date('17-12-1980','%d-%m-%Y'),800,NULL,20);  
    INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,str_to_date('20-2-1981','%d-%m-%Y'),1600,300,30);  
    INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,str_to_date('22-2-1981','%d-%m-%Y'),1250,500,30);  
    INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,str_to_date('2-4-1981','%d-%m-%Y'),2975,NULL,20);  
    INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,str_to_date('28-9-1981','%d-%m-%Y'),1250,1400,30);  
    INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,str_to_date('1-5-1981','%d-%m-%Y'),2850,NULL,30);  
    INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,str_to_date('9-6-1981','%d-%m-%Y'),2450,NULL,10);  
    INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,str_to_date('19-04-1987','%d-%m-%Y'),3000,NULL,20);  
    INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,str_to_date('17-11-1981','%d-%m-%Y'),5000,NULL,10);  
    INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,str_to_date('8-9-1981','%d-%m-%Y'),1500,0,30);  
    INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,str_to_date('23-05-1987','%d-%m-%Y'),1100,NULL,20);  
    INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,str_to_date('3-12-1981','%d-%m-%Y'),950,NULL,30);  
    INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,str_to_date('3-12-1981','%d-%m-%Y'),3000,NULL,20);  
    INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,str_to_date('23-1-1982','%d-%m-%Y'),1300,NULL,10);  
      
    -- 插入测试数据 —— salgrade  
    INSERT INTO salgrade VALUES (1,700,1200);  
    INSERT INTO salgrade VALUES (2,1201,1400);  
    INSERT INTO salgrade VALUES (3,1401,2000);  
    INSERT INTO salgrade VALUES (4,2001,3000);  
    INSERT INTO salgrade VALUES (5,3001,9999);  
    
    -- 事务提交  
    COMMIT;

**结果如下：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70][]

## 查询操作： ##

select 语句是数据库的查询语句，该语句返回用指定的条件在一个数据库中查询的结果，返回的结果被看作记录的集合。

*  查询所有列：

在示例表中，我们想查看表里边的全部的数据，或则某一列的数据，这就需要执行以下的语句了

select * from emp;
    select ename,job,mgr,hiredate,sal,comm,deptno,empno from emp;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70 1][]

*  投影查询

投影查询就是按照用户需求查询的指定列

select ename,job,hiredate,sal,comm from emp; -- 投影查询

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70 2][]

*  查询中的运算

--查询每个员工的姓名及年收入
    select ename 员工姓名,sal * 12 年收入 from emp;-- 举例
    --查询每个员工的姓名及年（收入+奖金)
    select ename 员工姓名,(sal + COALESCE(comm,0)) * 12 年收入 from emp;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70 3][]

*  统计查询

concat()函数功能：

将多个字符串连接成一个字符串。

语法：

concat(str1, str2,...)返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

--五个统计函数
    --求最大值
    select max(sal) from emp;
    select CONCAT('最大值 ',max(sal),'$') from emp;
    --求最小值
    select CONCAT('最小值',min(sal),'$') from emp;
    --求平均值
    select CONCAT('平均值',avg(sal),'$') from emp;
    --求和
    select CONCAT('总共',sum(sal),'$') from emp;
    --求总记录数
    select CONCAT('总共',count(*),'条') from emp;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70 4][]

*  分组统计

-- 查询各部门员工的平均年收入
    select deptno , avg((sal + COALESCE(comm,0)) * 12) 平均年收入 from emp group by deptno
    -- 查询不同职务的员工的总年收入
    select job , sum((sal + COALESCE(comm,0)) * 12) 平均年收入 from emp group by job
    -- 查询各部门下各职务员工的平均年收入
    select deptno,job,avg((sal + COALESCE(comm,0)) * 12) 平均年收入 from emp group by deptno,job
    -- 查询10，20部门下各职务员工的平均年收入
    select deptno,job,avg((sal + COALESCE(comm,0)) * 12) 平均年收入 from emp group by deptno,job having deptno in (10,20) -- 不推荐
    select deptno,job,avg((sal + COALESCE(comm,0)) * 12) 平均年收入 from emp where deptno in (10,20) group by deptno,job -- 推荐

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70 5][]

*  查询排序

SQL ORDER BY 关键字

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。

ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。

coalesce()解释：

返回参数中的第一个非空表达式（从左向右依次类推）；

select * from emp order by sal +  COALESCE(comm,0) desc;
    select * from emp order by sal +  COALESCE(comm,0),hiredate ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70 6][]

具体的实践，读者可以自行验证，参考的仅仅只是示例方法，具体内容以具体示例相结合

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70]: /images/20211129/5e204496fbc249d890cabb02e2a37132.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70 1]: /images/20211129/9ad6aeb6e30149e09b8af3f417f10ad3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70 2]: /images/20211129/9176cbd9847747e0a5e9e512057da971.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70 3]: /images/20211129/fd7ac82759a54e2fb19c174a61635f76.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70 4]: /images/20211129/123dc927c343449e91e0d7fc0ad3a0e3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70 5]: /images/20211129/94ffdae3ca0a426aad9fee76c690e9c5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvY2tMaXU_size_16_color_FFFFFF_t_70 6]: /images/20211129/422cde2bb37c4a5d8671d0482264b7e0.png