练习:
创建数据库mydb4 字符集utf8 并使用
create database mydb4 character set utf8;
use mydb4;
在数据库中创建员工表emp 字段:id,name,sal,deptId(部门id) 字符集utf8
create table emp( id int , name varchar ( 10 ) , sal int , deptId int ) charset = utf8;
创建部门表dept 字段:id,name,loc(部门地址) 字符集utf8
create table dept( id int , name varchar ( 10 ) , loc varchar ( 20 ) ) charset = utf8;
部门表插入以下数据: 1 神仙部 天庭 2 妖怪部 盘丝洞
insert into dept values ( 1 , '神仙部' , '天庭' ) , ( 2 , '妖怪部' , '盘丝洞' ) ;
员工表插入一下数据: 1 悟空 5000 1 , 2 八戒 2000 1 , 3 蜘蛛精 8000 2, 4 白骨精 9000 2
insert into emp values ( 1 , '悟空' , 5000 , 1 ) , ( 2 , '八戒' , 2000 , 1 ) , ( 3 , '蜘蛛精' , 8000 , 2 ) , ( 4 , '白骨精' , 9000 , 2 ) ;
查询工资6000以下的员工姓名和工资
select name, sal from emp where sal< 6000 ;
修改神仙部的名字为取经部
update dept set name= '取经部' where id= 1 ;
给员工添加奖金comm字段
alter table emp add comm int ;
修改部门id为1的部门奖金为500
update emp set comm= 500 where deptId= 1 ;
把取经部的地址改成五台山
update dept set loc= '五台山' where id= 1 ;
删除两个表
drop table emp; drop table dept;
主键约束 primary key
主键: 表示数据唯一性的字段称为主键 约束: 约束是创建表时给表字段添加的限制条件 主键约束: 限制主键的值唯一且非空 测试:
create table t1( id int primary key , name varchar ( 10 ) ) ;
insert into t1 values ( 1 , 'aaa' ) ;
insert into t1 values ( 1 , 'bbb' ) ; //报错 主键值不能重复 Duplicate entry '1' for key 'PRIMARY'
insert into t1 values ( null , 'ccc' ) ; //报错 主键值不能为null Column 'id' cannot be null
###主键+自增 auto_increment
create table t2( id int primary key auto_increment , name varchar ( 10 ) ) ;
insert into t2 values ( null , 'aaa' ) ; 1
insert into t2 values ( null , 'bbb' ) ; 2
insert into t2 values ( 10 , 'ccc' ) ; 10
insert into t2 values ( null , 'ddd' ) ; 11
delete from t2 where id>= 10 ;
insert into t2 values ( null , 'eee' ) ; 12
导入*.sql文件
把下载的emp.sql 放到d盘根目录 source d:/emp.sql; 如果导入后 执行select * from emp 发现有乱码 执行 set names gbk;
去重distinct
查询员工表中有哪些不同的工作
select distinct job from emp;
is null 和 is not null
查询没有上级领导的员工信息
select * from emp where mgr is null ;
查询有上级领导的员工姓名和上级领导编号
select ename, mgr from emp where mgr is not null ;
比较运算符 > < >= <= = !=和<>
查询员工工资小于等于3000的员工姓名和工资
select ename, sal from emp where sal<= 3000 ;
查询工作不是程序员的员工姓名和工作(两种写法)
select ename, job from emp where job!= '程序员' ;
select ename, job from emp where job<> '程序员' ;
and 和 or
如果查询数据时使用了多个条件,多个条件同时满足使用and, 多个条件满足一个就可以使用or and类似java中的&& , or类似java中的||
查询1号部门工资大于1500的员工信息
select * from emp where deptno= 1 and sal> 1500 ;
查询工作是人事或者工资大于3000的员工姓名,工作和工资.
select ename, job, sal from emp where job= '人事' or sal> 3000 ;
in(x,y,z)
查询工资为1500,3000,5000的员工信息
select * from emp where sal= 1500 or sal= 3000 or sal= 5000 ;
select * from emp where sal in ( 1500 , 3000 , 5000 ) ;
between x and y
查询工资在1000到2000之间的员工信息(包括1000和2000)
select * from emp where sal>= 1000 and sal<= 2000 ;
select * from emp where sal between 1000 and 2000 ;
查询工资在1000到2000以外的员工信息
select * from emp where sal not between 1000 and 2000 ;
查询工资不等于5000,3000,800的员工信息
select * from emp where sal not in ( 800 , 3000 , 5000 ) ;
综合练习
查询有上级领导并且是3号部门的员工信息
select * from emp where mgr is not null and deptno= 3 ;
查询2号部门工资在1000到2000之间的员工姓名 工资和部门编号
select ename, sal, deptno from emp where deptno= 2 and sal between 1000 and 2000 ;
查询1号部门工资为800和1600的员工信息
select * from emp where deptno= 1 and sal in ( 800 , 1600 ) ;
查询1号和2号部门工资高于2000的员工信息
select * from emp where deptno in ( 1 , 2 ) and sal> 2000 ;
查询员工表中出现的部门编号有哪几个
select distinct deptno from emp;
模糊查询like
_:代表1个未知字符 %:代表0或多个未知字符 举例:
以x开头 x% 以x结尾 %x 包含x %x% 第二个字符是x _x% 倒数第三个是x %x__ 以x开头 倒数第二个是y x%y_
查询员工表中姓孙的员工姓名
select ename from emp where ename like '孙%' ;
查询名字以精结尾的员工信息
select * from emp where ename like '%精' ;
查询工作中包含销售的员工姓名和工作
select ename, job from emp where job like '%销售%' ;
排序 order by
格式: order by 字段 asc(默认升序)/desc降序 写在SQL语句的后面
查询工资高于2000的员工信息,按照工资升序排序
select * from emp where sal> 2000 order by sal;
查询每个员工的姓名,工资和部门编号,按照部门编号降序排序
select ename, sal, deptno from emp order by deptno desc ;
多字段排序,在order by后面写多个字段 通过逗号分隔
查询每个员工的姓名,工资和部门编号,按照部门编号降序排序,如果部门编号一致则按照工资降序排序
select ename, sal, deptno from emp order by deptno desc , sal desc ;
查询1号和2号部门的员工信息按照部门编号升序排序,如果部门编号一致则按照工资降序排序
select * from emp where deptno in ( 1 , 2 ) order by deptno, sal desc ;
分页查询
格式: limit 跳过的条数,请求条数(也代表每页条数) , 写在SQL语句的最后
请求员工表中按照工资降序排序前3条数据(请求第一页的3条数据)
select * from emp order by sal desc limit 0 , 3 ;
查询员工表中工资降序排序的第4 5 6条数据
select * from emp order by sal desc limit 3 , 3 ;
查询员工表中第三页的2条数据(请求第5和第6条数据)
select * from emp limit 4 , 2 ;
查询工资最低的员工信息
select * from emp order by sal limit 0 , 1 ;
查询员工表中工资升序第四页的2条数据
select * from emp order by sal limit 6 , 2 ;
综合练习题
查询员工表中名字里第二个字是八的员工姓名和工资
select ename, sal from emp where ename like '_八%' ;
查询工作中包含售字的员工姓名和工作
select ename, job from emp where job like '%售%' ;
查询工资高于1000块钱的员工姓名和工资,按照工资降序排序,查询第二页的3条数据
select ename, sal from emp where sal> 1000 order by sal desc limit 3 , 3 ;
数值计算+ - * /
查询每个员工的姓名,工资和年终奖(3个月的工资)
select ename, sal, sal* 3 from emp;
- 别名,可以对查询的字段起别名
select ename as '名字' , sal as '工资' , sal* 3 as '年终奖' from emp;
select ename '名字' , sal '工资' , sal* 3 '年终奖' from emp;
select ename 名字, sal 工资, sal* 3 年终奖 from emp;
查询每个员工姓名,工资和涨薪5块钱之后的工资
select ename, sal, sal+ 5 涨薪后 from emp;
聚合函数
聚合函数是对查询的多条数据进行统计查询,包括:求平均值,最大值,最小值,求和,计数 平均值avg
查询1号部门的平均工资
select avg ( sal) from emp where deptno= 1 ;
查询程序员的平均工资
select avg ( sal) from emp where job= '程序员' ;
查询3号部门的最高工资
select max ( sal) from emp where deptno= 3 ;
查询销售的最低工资
select min ( sal) from emp where job= '销售' ;
查询2号部门的工资总和
select sum ( sal) from emp where deptno= 2 ;
查询工资高于2000的员工人数
select count ( * ) from emp where sal> 2000 ;
查询2号部门的人数
select count ( * ) from emp where deptno= 2 ;
练习题
查询员工表中工资高于2000的员工姓名和工资,按照工资升序排序,查询第二页的2条数据
select ename, sal from emp where sal> 2000 order by sal limit 2 , 2 ;
查询和销售相关的工作的工资总和
select sum ( sal) from emp where job like '%销售%' ; 6625
查询程序员人数
select count ( * ) from emp where job= '程序员' ; 2
查询1号部门中有领导的员工中的最高工资
select max ( sal) from emp where deptno= 1 and mgr is not null ;
查询2号部门的最高工资和最低工资 起别名
select max ( sal) 最高工资, min ( sal) 最低工资 from emp where deptno= 2 ;
查询1号部门里面名字中包含空字的员工姓名
select ename from emp where deptno= 1 and ename like '%空%' ;
分组查询
题目需求中每个xx 就以xx作为分组的字段 查询员工表的平均工资
select avg ( sal) from emp;
select avg ( sal) from emp where deptno= 1 ;
查询每个部门的平均工资
select deptno, avg ( sal) from emp group by deptno;
查询每个工作的最高工资
select job, max ( sal) from emp group by job;
查询每个部门的人数
select deptno, count ( * ) from emp group by deptno;
查询每种工作的工资总和
select job, sum ( sal) from emp group by job;
查询每个部门工资高于1000块钱的员工人数
select deptno, count ( * ) from emp where sal> 1000 group by deptno;
查询1号和2号部门的最高工资
select deptno, max ( sal) from emp where deptno in ( 1 , 2 ) group by deptno;
查询1号和2号部门中每种工作的工资总和
select job, sum ( sal) from emp where deptno in ( 1 , 2 ) group by job;
having
where后面只能写普通字段条件,聚合函数不能写在where后面 having后面专门写聚合函数的条件,而且是和分组查询结合使用 各个关键字的顺序: select …from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 limit …;
查询每个部门的平均工资,只查询平均工资高于2000的信息
select deptno, avg ( sal) from emp group by deptno having avg ( sal) > 2000 ;
查询每种工作的人数,只查询人数为1的信息
select job, count ( * ) from emp group by job having count ( * ) = 1 ;
查询每个部门的平均工资,只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的信息.
select deptno, avg ( sal) from emp where sal between 1000 and 3000 group by deptno having avg ( sal) >= 2000 ;
综合练习
查询没有上级领导的员工编号empno,姓名,工资 select empno,ename,sal from emp where mgr is null;
查询有奖金的员工姓名和奖金 select ename,comm from emp where comm>0;
查询名字中包含精的员工姓名和工资 select ename,sal from emp where ename like '%精%';
查询名字中第二个字是八的员工信息 select * from emp where ename like '_八%';
查询1号部门工资大于2000的员工信息 select * from emp where deptno=1 and sal>2000;
查询2号部门或者工资低于1500的员工信息 select * from emp where deptno=2 or sal<1500;
查询工资为3000,1500,5000的员工信息按照工资升序排序 select * from emp where sal in(3000,1500,5000) order by sal;
查询3号部门的工资总和 select sum(sal) from emp where deptno=3;
查询每个部门工资大于1000的员工人数,按照人数升序排序 select deptno,count() from emp where sal>1000 group by deptno order by count();
查询每种工作中有领导的员工人数按照人数降序排序 select job,count() from emp where mgr is not null group by job order by count() desc;
查询所有员工信息,按照部门编号升序排序,如果部门编号一致则工资降序 select * from emp order by deptno,sal desc;
查询有领导的员工,每个部门的编号和最高工资 select deptno,max(sal) from emp where mgr is not null group by deptno;
查询有领导的员工,按照工资升序排序,第3页的2条数据 select * from emp where mgr is not null order by sal limit 4,2;
查询每个部门的工资总和,只查询有上级领导的员工并且要求工资总和大于5400,最后按照工资总和降序排序,只查询结果中的第一条数据 deptno,sum(sal) from emp where mgr is not null group by deptno having sum(sal)>5400 order by sum(sal) desc limit 0,1;
Java、Python、算法知识体系 | PPT、简历模板 | 游戏源码 | IT行业趣味资讯 | 装机必备软件
关注下方公号回复【资料】获取 👇🏻👇🏻👇🏻