课程回顾
- 数据库相关SQL
- 查询所有 show databases;
- 创建 create database db1 character set utf8/gbk;
- 查询详情 show create database db1;
- 删除数据库 drop database db1;
- 使用数据库 use db1;
- 表相关SQL
- 创建表 create table t1(name varchar(10),age int)charset=utf8;
- 查询所有 show tables;
- 查询详情 show create table t1;
- 表字段 desc t1;
- 删除表 drop table t1;
- 修改表名 rename table t1 to t2;
- 添加字段 alter table t1 add 字段名 类型 first/after xxx;
- 删除字段 alter table t1 drop 字段名;
- 修改字段 alter table t1 change 原名 新名 新类型;
- 数据相关SQL
- 插入数据 insert into t1(name,age) values(xxx,xxx),(xxx,xxx);中文问题 如果存在中文问题 执行 set names gbk;
- 查询数据 select 字段信息 from t1 where 条件;
- 修改数据 update t1 set xxx=xxx where 条件;
- 删除数据 delete from t1 where 条件;
- 数据类型
- 整数 int(m) 和 bigint(m) m代表显示长度 需要结合zerofill关键字使用
- 浮点数 double(m,d) m代表总长度 d代表小数长度 超高精度浮点数decimal
- 字符串: char:不可变长度 执行效率高 255 varchar 可变长度 节省空间,建议保存255以下 实际最大长度65535 text 可变长度 建议保存大于255 并且65535以内
- 日期: date年月日 time时分秒 datetime默认值为null 最大9999-12-31 timestamp: 默认值为系统时间,最大 2038-1-19
- 导入*.sql 文件source 路径;
- is null 和 is not null
- 去重 distinct
- 比较运算符 > < >= <= = !=和<>
- and和or
- in(x,y,z)
- between x and y 包含x和y
综合练习
- 查询有上级领导并且是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 sal>2000 and deptno in(1,2);
- 查询员工表中出现的部门编号有哪几个select distinct deptno from emp;
练习:
- 创建数据库mydb2 字符集utf8 并使用create database mydb2 character set utf8; use mydb2;
- 在数据库中创建员工表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(10))charset=utf8;
- 部门表插入以下数据: 1 神仙部 天庭 2 妖怪部 盘丝洞 insert into dept values(1,'神仙部','天庭'),(2,'妖怪部','盘丝洞');
- 员工表插入一下数据: 1 悟空 5000 1 2 八戒 2000 1 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 name='神仙部';
- 给员工添加奖金comm字段 alter table emp add comm int;
- 修改部门id为1的部门奖金为500 update emp set comm=500 where deptId=1;
- 把取经部的地址改成五台山update dept set loc='五台山' where name='取经部';
- 删除两个表 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'); 报错:Column 'id' cannot be null id值不能为null
自增 auto_increment
- 自增字段给null值会触发自增
- 从历史最大值+1
- 举例: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
模糊查询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 ename,job from emp where job like '%销售%';
- 查询名字以精结尾的员工姓名select ename from emp where ename like '%精';
- 查询工作中第二个字是"序"的员工信息select * from emp where job like '_序%';
排序
- 格式: order by 字段名 asc升序(默认)/desc降序
- 查询2号部门员工姓名和工资按照工资升序排序select ename,sal from emp where deptno=2 order by sal;
- 查询所有员工信息 安装工资降序select * from emp order by sal desc;
- 查询销售人员信息按照工资降序排序select * from emp where job like '%销售%' order by sal desc;
- 多字段排序格式: order by 字段名,字段名
- 查询员工姓名 工资和部门编号 按照部门编号降序排序select ename,sal,deptno from emp order by deptno desc,sal;
分页查询limit
- 格式: limit 跳过条数(跳过页数*每页条数),请求条数(每页条数)
- 查询员工表第一页的3条数据select * from emp limit 0,3;
- 查询员工表第2页的3条数据select * from emp limit 3,3;
- 查询员工表第3页的2条数据(每页2条)select * from emp limit 4,2;
- 查询工资最高的前三名的员工信息select * from emp order by sal desc limit 0,3;
- 查询1号部门工资最少的员工信息select * from emp where deptno=1 order by sal limit 0,1;
数值计算+ - * /
- 查询每个员工的姓名 工资及年终奖(5个月的工资)select ename 名字,sal 工资,sal*5 年终奖 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='程序员';
- 最大值max(字段名)
- 查询2号部门的最高工资select max(sal) from emp where deptno=2;
- 最小值min(字段名)
- 查询有上级领导的员工中的最低工资select min(sal) from emp where mgr is not null;
- 求和sum(字段名)
- 查询3号部门的工资总和select sum(sal) from emp where deptno=3;
- 计数count(*)
- 查询工资大于2000的员工人数select count(*) from emp where sal>2000;
- 查询1号部门工资大于2000的员工人数select count(*) from emp where deptno=1 and sal>2000;
练习题
- 查询员工表中工资高于2000的员工姓名和工资,按照工资升序排序,查询第二页 的2条数据select ename,sal from emp where sal>2000 order by sal limit 2,2;
- 查询和销售相关的工作的工资总和select sum(sal) from emp where job like '%销售%';
- 查询程序员人数 select count(*) from emp where job='程序员';
- 查询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 ename like '%空%' and deptno=1;
分组查询 group by
- 以某个字段作为分组条件,这个字段相同的值为一组,对该组数据进行统计查询
- 查询每个部门的平均工资select deptno,avg(sal) from emp group by deptno ;
- 查询每个部门的最高工资select deptno,max(sal) from emp group by deptno;
- 查询每种工作的人数select job,count(*) from emp group by job;
- 查询每个部门工资高于1500的人数select deptno,count(*) from emp where sal>1500 group by deptno;
- 查询1号部门和2号部门的最高工资select deptno,max(sal) from emp where deptno in(1,2) group by deptno;
- 查询1号部门每种工作的人数select job,count(*) from emp where deptno=1 group by job;
- 查询1号和2号部门中每种工作的工资总和select job,sum(sal) from emp where deptno in(1,2) group by job;
- 查询每种工作的最低工资select job,min(sal) from emp group by job;
having
- where后面只能写普通字段的条件,不能写聚合函数条件
-
having后面写聚合函数的条件,需要结合分组查询使用.
-
查询每个部门的平均工资,只查询平均工资大于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; select job,count(*) c from emp group by job having c=1;
- 查询每个部门的平均工资,只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的部门 select deptno,avg(sal) a from emp where sal between 1000 and 3000 group by deptno having a>=2000;
各个关键字的顺序
- select 字段信息 from 表名 where 条件 group by 字段 having 聚合函数条件 order by字段 limit....;
子查询(嵌套查询)
- 查询工资高于1号部门平均工资的员工信息
- 得到1号部门的平均工资select avg(sal) from emp where deptno=1; 2325
- 高于1号平均工资的员工信息select * from emp where sal>(select avg(sal) from emp where deptno=1);
- 查询最高工资的员工信息select * from emp where sal=(select max(sal) from emp);
- 查询工资高于2号部门最低工资的员工信息select * from emp where sal>(select min(sal) from emp where deptno=2);
-
查询和孙悟空相同工作的其他员工信息
- 得到孙悟空工作select job from emp where ename='孙悟空';
select * from emp where job=(select job from emp where ename='孙悟空') and ename!='孙悟空';
- 查询拿最低工资员工的同事们的信息(同事指同一部门的员工)
- 得到最低工资select min(sal) from emp;
- 通过最低工资得到部门编号select deptno from emp where sal=(select min(sal) from emp);
- 根据部门编号查询员工信息 还要去掉最低工资的select * from emp where deptno=(select deptno from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);
-
查询白骨精的部门信息(需要用到dept部门表)-得到白骨精的部门编号 select deptno from emp where ename='白骨精'; select * from dept where deptno=(select deptno from emp where ename='白骨精');
-
查询所有员工的部门信息(部门只有1,2,3但是部门表里面有1,2,3,4 只根据员工表中出现的部门编号去查询部门信息)
- 查询员工表中出现的部门编号select distinct deptno from emp;
select * from dept where deptno in(select distinct deptno from emp);
综合练习
- 查询没有上级领导的员工编号empno，姓名，工资
- 查询有奖金的员工姓名和奖金
- 查询名字中包含精的员工姓名和工资
- 查询名字中第二个字是八的员工信息
- 查询1号部门工资大于2000的员工信息
- 查询2号部门或者工资低于1500的员工信息
- 查询工资为3000，1500，5000的员工信息按照工资升序排序
- 查询3号部门的工资总和
- 查询每个部门工资大于1000的员工人数，按照人数升序排序
- 查询每种工作中有领导的员工人数按照人数降序排序
- 查询所有员工信息，按照部门编号升序排序，如果部门编号一致则工资降序
- 查询有领导的员工，每个部门的编号和最高工资
- 查询有领导的员工，按照工资升序排序，第3页的2条数据
- 查询每个部门的工资总和，只查询有上级领导的员工并且要求工资总和大于5400，最后按照工资总和降序排序,只查询结果中的第一条数据