Mysql 8.0 建表添加数据

1）建立表

a )雇员表

drop table if exists emp;

   	create table emp(
   		empno int(4) primary key comment '雇员编号',
   		ename varchar(10) comment '官员姓名',
   		job varchar(9) comment '雇员职位',
   		mgr int(4) comment '领导编号',
   		hiredate datetime comment '入职日期',
   		sal double(7,2) comment '薪资',
   		comm double(7,2) comment '奖金',
   		deptno int(2) comment '部门编号'
   	);

向表中插入数据：

		insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
		insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-01-20',1600,300,30);
		insert into emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
		insert into emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20);
		insert into emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
		insert into emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,null,30);
		insert into emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,null,10);
		insert into emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,null,20);
		insert into emp values(7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10);
		insert into emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
		insert into emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,null,20);
		insert into emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,null,30);
		insert into emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,null,20);
		insert into emp values(7934,'MILLER','CLERK',7782,'1982-12-03',1300,null,10);
		commit;

检查插入数据：

select * from emp;

b) 部门表

drop table if exists dept;
	create table dept(
		deptno int(2) primary key comment '部门编号',
		dname varchar(14) comment '部门名称',
		loc varchar(13) comment '部门位置'
        );

插入数据：

	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');#运营部

c) 工资表

	drop table if exists bonus;
	create table bonus(
		ename varchar(20) comment '雇员姓名',
		job varchar(20) comment '雇员职位',
		sal int(6) comment '雇员薪资',
		comm int(6) comment '雇员奖金'
	);

d) 工资等级表

drop table if exists salgrade;
	create table salgrade(
		grade int comment '工资等级',
		losal int comment '该等级最低工资',
		hisal int comment '该等级最高工资'
	);

插入数据：

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;

8. group by 分组语句

   使用emp表根据部门分组,并查出部门编号及每个部门的人数

   select deptno,count(*) from emp group by deptno;

​ 利用group_concat()函数显示出每组需要显示的数据

    ```sql

select deptno,group_concat(ename) from emp group by deptno;
```

​ 利用with rollup 统计显示内容的记录总和

select deptno,group_concat(ename) from emp group by deptno with rollup;

9. having 关键词

   使用having关键字对分组后的数据进行过滤,having关键字和where关键字都可以用来过滤数据
   where 和 having关键字的不同之处:
   a. where作用于表和试图,having作用与组;
   b. where查询条件中不可以使用聚合函数,having查询条件中可以使用聚合函数
   c. where在数据分组前进行过滤,having在数据分组后进行过滤

​ 根据sal字段进行分组,并使用having查询分组后平均薪资在2000以上的员工部门编号,名字和薪资

select deptno,group_concat(ename),sal from emp group by sal having avg(sal)>2000;

10. 空值查询

    is null 为空
    is not null 不为空

#查询emp表中没有奖金的员工信息
select * from emp where comm is null;

11. • between and 范围查询
      在多少到多少之间
      between 开始 and 结束

#查询emp表中薪资在2000 到 3000 之间的员工信息
 select * from emp where sal between 2000 and 3000;

• ​ limit 分页查询
  ​ limit 起始位置,记录数
  ​ limit 记录数 ;

  	#从第三条数据开始 显示5条
  	select * from emp limit 2,5;
  	
  	#显示前5条
  	select * from emp limit 5;
  	
  	#limit 可以和offset组合使用
  	limit 记录数 offset 起始位置
  	
  	#从第一条记录开始显示5条
  	select * from emp limit 5 offset 0;
  

  ​