美文网首页
linux下mysql操作进阶

linux下mysql操作进阶

作者: 潘雪雯 | 来源:发表于2020-05-30 08:04 被阅读0次

    实战操作

    • 创建数据库
    create database bbb;

    用数据库之前要指定

    use bbb;
    • 部门表
    1. 创建部门表字段
    create table dept (
deptno int primary key,
dname nvarchar(30),
loc nvarchar(30));
    1. 插入数据
    insert into dept (deptno, dname, loc) values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc) values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc) values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc) values (40, 'OPERATIONS', 'BOSTON');
    image.png
    • 职员表
    1. 创建职员表字段
    create table emp (
  empno int primary key,
  ename nvarchar(30),
  job nvarchar(30),
  mgr int,
  hiredate datetime,
  sal numeric(9,2),
  comm numeric(9,2),
  deptno int);
    1. 插入数据
    insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500.00,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7566,'JONES','MANAGER',7839,'1981-4-2',2975,null,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-4-19', 3000, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7876, 'ADAMS', 'CLERK', 7788, '1987-5-23', 1100, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);
    image.png

    SQL操作

    • 删除某字段记录
    --删除雇员表中工资为2000和2500的记录
delete from emp where sal=2000 or sal=2500;
    • 查询操作
    1. 查询表
    select * from emp;
select * from dept;
    1. 查询列
    --查询指定列
select ename from emp;
    image.png
    • MySQL 中where 的使用
    1. 查询某一具体字段在SQL中不区分大小写 
    --查询SMITH的薪水、工作和所在部门
select sal,job,deptno from emp where ename='SMITH';--在sql server 中不区分大小写,在oracle中区分
select sal,job,deptno from emp where ename='SmiTH';--此语句和上一句效果一样
select sal,job,dname  from emp, dept where emp.ename='smITH' and emp.deptno=dept.deptno ;
    image.png
    1. 显示工资高于(>和>=区别)3000的员工
    select ename from emp where sal>3000;
select ename from emp where sal>=3000;
    1. 查找1982-1-1后入职的员工
    select * from emp where hiredate>'1982-1-1;
    image.png
    1. 查找工资在2000-2500员工的情况
      方法一: > 和<
    select * from emp where sal>2000 and sal<2500;

    方法二: between包含边界即包含2000和2500

    select * from emp where sal between 2000 and 2500;
    image.png
    1. 在where 条件中使用in
      如何显示empno为123,345,800的情况
      方法一:
    select * from emp where empno=123 or empno=345 or empno=800;

    方法二:

    select * from emp where empno in(123,345,800,7782,7900);
    image.png
    1. 使用逻辑操作符号
    --查询工资高于500或是岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为j
select * from emp where (sal>500 or job='MANAGER') and ename like 'j%';
    image.png
    • MySQl中distinct的使用
    1. 统计部门编号
    select distinct deptno from emp;
    image.png
    • MySQL中like的使用
    1. 查询首字母为s的员工和员工工资
    select ename,sal from emp where ename like 's%';--%表示0到多个字符
    image.png
    1. 查询第三个字母为o的员工姓名和工资
    select ename,sal from emp where ename like '__o%';--_(下划线)表示单个字符
    image.png
    • MySQL排序-----order by
    1. order by操作 asc升序排列
    --按照工资从低到高的顺序显示员工的信息
select * from emp order by sal asc; --从低到高排列,在此处asc写不写都可以，因为默认就是asc
select * from emp order by sal desc;--从高到低排列
    image.png
    image.png
    1. order by操作 desc降序排列
    --按照入职先后顺序排列  默认就是asc
select * from emp order by hiredate;
    image.png
    1. order by根据不同字段排序，先排第一个字段，再排第二个字段
    --按照部门号升序而雇员工资降序排列
select * from emp order by deptno,sal desc;
    image.png
    1. order by和ifnull组合使用
    --使用列的别名，年薪排序，->>先把年薪算出来，再排序
select ename,sal*13+ifnull(comm,0)*13 年薪 from emp order by 年薪 desc;
    image.png
    • MySQL GROUPBY语句
      group by语句根据一个或多个列对结果集进行分组，在分组的列上可以使用COUNT、SUM、AVG等函数
    1. count和group by结合使用
    -- 统计每个部门多少人
select count(*) from emp group by deptno;
    image.png

    复杂查询

    • max和min的实际用法
    --1、如何显示所有员工中的最高工资和最低工资？
select max(sal) 最高工资 ,min(sal) 最低工资 from emp;
    image.png
    • avg()和sum()的实际用法
    --2、显示所有员工的平均工资和工资总和？
select avg(sal) 平均工资 ,sum(sal) 工资总和 from emp;
    image.png
    • count()的实际用法
    --3、计算共有多少员工？
select count(empno) 员工数量 from emp;
select count(*) 员工数量 from emp;
    image.png
    • SQL语句执行顺序
    --4、显示工资最高的员工的姓名、工作岗位？--SQL语句执行时是从右到左
select ename,job from emp where sal=(select max(sal) from emp);
    image.png 
    --5、显示工资高于平均工资的员工信息？
select ename,sal from emp where sal>(select avg(sal) from emp);
    image.png
    • group by用于对查询的结果分组统计
    1. 显示每个部门的平均工资和最高工资？
    select avg(sal) 平均工资,max(sal) 最高工资,deptno from emp group by deptno;
    image.png
    1. 显示每个部门的平均工资和最高工资？并显示部门名称
    --如何显示部门名称  多表查询
select avg(sal) 平均工资,max(sal) 最高工资,dname,emp.deptno from emp,dept 
 where emp.deptno=dept.deptno group by emp.deptno;
    image.png
    1. 如何显示每个部门的每种岗位的平均工资和最低工资？
    select avg(sal) 平均工资,min(sal) 最低工资,deptno,job from emp group by deptno,job order by deptno;
    image.png
    • having和group by结合使用，对分组查询到的结果进行筛选

    显示平均工资低于2000的部门和它的平均工资？

    select deptno,avg(sal) from emp group by deptno having avg(sal)<2000
    image.png

    注意:如果在select 语句中同时包含有group by、having、order by 那么它们的顺序是 group by、having、order by 。

    • 复杂查询
      多表匹配原理笛卡尔集，从右往左匹配
    1. 如果两张表都有相同的字段，则需要带表名（或表的别名） ->不是相同的字段，表名可带可不带
    --显示雇员名、雇员工资和其所在部门的名字，部门号 <下两句话效果一样>
select emp.ename,emp.sal,dept.dname,dept.deptno from emp,dept where emp.deptno=dept.deptno
select ename,sal,dname,dept.deptno from emp,dept where emp.deptno=dept.deptno
    image.png
    1. 用表的别名代替表名(在表名较长较复杂时)
    select ename,sal,dname,d.deptno from emp e,dept d where e.deptno=d.deptno
    image.png
    1. 显示sales部门位置和其员工的姓名
    select dept.loc,emp.ename from dept,emp where dept.dname='sales' and emp.deptno=dept.deptno
    image.png
    1. 如何显示部门号为10的部门名字、员工名和工资
    select dept.dname,emp.ename,emp.sal from dept,emp where dept.deptno=10 and emp.deptno=dept.deptno
    image.png
    1. 显示雇员名，雇员工资及所在部门的名字，并按部门排序
    select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno order by dept.dname
    image.png
    1. 显示某个员工的上级领导的姓名->比如显示‘ford’的上级
    首先知道'frod'的上级编号
select mgr from emp where ename='ford'
select ename from emp where empno=(select mgr from emp where ename='ford')
    image.png
    1. 如何显示公司每个员工名字和他上级的名字
    把emp看作两张表 worker 和 boss
select worker.ename 员工,boss.ename 上级 from emp worker,emp boss where boss.empno=worker.mgr
    image.png
    • 子查询
      单行子查询用 =，多行子查询用 in
    1. 单行子查询
    如何查找与’smith’同一部门的所有员工
select * from emp where deptno=(select deptno from emp where ename='smith')
select * from emp where deptno in (select deptno from emp where ename='smith')
    image.png image.png
    1. 多行子查询
    如何查询和部门10的工作相同的雇员的名字，岗位、工资、部门号  ->>如何排除10号部门?
select ename,job,sal,deptno from emp 
where job in (select distinct job from emp where deptno=10) 
and deptno<>10  --在此语句中把 in 换为 = 会出错
--不等号 <> 换为 != 也可以
    image.png
    1. 在from子句中使用子查询
      如何显示高于部门平均工资的员工姓名、工资、部门平均工资
    3.1、先查找部门平均工资
select avg(sal),emp.deptno aa from emp group by deptno
    image.png 
    3.2、把上面的查询结果当作一张临时表对待
--查找高于部门平均工资员工姓名、工资、部门平均工资、部门编号
select emp.ename,emp.sal,qq.aa,emp.deptno from emp,
(select avg(sal) aa,emp.deptno from emp group by deptno) qq --qq为别名->>必须取
where emp.sal>qq.aa and emp.deptno=qq.deptno
    image.png
    • MySQL NULL 值处理
    1. 使用is null操作符
    select * from emp where mgr is null;--千万不能等于即mgr='null'
    image.png
    1. 显示每个雇员的年工资=13个月工资+奖金
      ifnull(comm,0),判别为null，为null返回0，不为null返回本身数值
      年工资是指定的列的别名
      别名即年工资 加不加单引号 ，或双引号 ，或啥也不加 都可以,即下面三句话效果一样
    select ename,sal*13+ifnull(comm,0)*13 年工资 from emp;
select ename,sal*13+ifnull(comm,0)*13 '年工资' from emp;
select ename,sal*13+ifnull(comm,0)*13 "年工资" from emp;
    image.png
    1. isnull && is not null
    isnull(exper) 判断exper是否为空，是则返回1 ，否则返回0
is not null(exper) 判断exper是否为空，否则返回1 ，是则返回0
    1. isnull VS ifnull
    isnull(exper) 判断exper是否为空，是则返回1 ，否则返回0
ifnull(exper1,exper2) 判断exper1是否为空，是则用exper2代替
    • MySQL连接的使用
    1. 内连接:显示左右两边共有的
    显示每个员工和他上级的名字
select worker.ename 员工, boss.ename 上级 from emp worker, emp boss where worker.mgr=boss.empno
    image.png
    1. 左连接:左边有的，右边没有的为null
    显示每个员工和他上级的名字，要求没有上级的人的名字也要显示出来
select worker.ename 员工, boss.ename 上级 from emp worker left join emp boss on worker.mgr=boss.empno
    image.png
    1. 右连接:右边有的，左边没有的为null
    显示每个员工和他上级的名字，要求没有下级的人的名字也要显示出来
select worker.ename 员工, boss.ename 上级 from emp worker right join emp boss on worker.mgr=boss.empno
    image.png

    相关文章

      网友评论

          本文标题：linux下mysql操作进阶

          本文链接：https://www.haomeiwen.com/subject/bbjfzhtx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|linux下mysql操作进阶|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！