基础命令
各个关键字的顺序:
select .....from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 limit ...
1.复习
- 数据库相关SQL
- 查询所有
show databases;
- 创建
create database db1 character set utf8/gbk;
- 查询详情
show create database db1;
- 删除数据库
drop database db1;
- 使用数据库
use db1;
CREATE TABLE `project_student` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键id',
`user_id` varchar(64) NOT NULL DEFAULT '' COMMENT '飞书用户id',
`dept_id_path` varchar(255) NOT NULL DEFAULT '' COMMENT '用户部门id path',
`employment_state` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '在职状态',
`entry_ts` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '入职时间戳,单位秒',
`mentor_user_id` varchar(64) NOT NULL DEFAULT '' COMMENT '导师飞书用户id',
`project_dataset_check_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '项目唯一标识',
`training_start_ts` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '培训开始时间戳,单位秒',
`training_end_ts` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '培训结束时间戳,单位秒',
`training_state` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '培训状态',
`camp_state` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '出营状态',
`study_state` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '学习状态',
`created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间戳,单位秒',
`updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间戳,单位秒',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_1` (`project_dataset_check_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='项目学员表';
- 表相关SQL
- 创建表
create table t1(name varchar(10),age int) CHARSET=utf8mb4/gbk;
- 查询所有
show tables;
- 查询表详情
show create table t1;
- 查询表字段
desc t1;
- 删除表
drop table t1;
- 添加表字段
alter table t1 add 字段名 类型 first/after xxx;
- 删除表字段
alter table t1 drop 字段名;
- 修表字段
alter table t1 change 原名 新名 新类型;
- 数据相关SQL
- 插入数据:
insert into t1(字段1,字段2) values(值1,值2),(值1,值2);
- 查询数据:
select 字段信息 from t1 where 条件;
- 修改数据:
update t1 set 字段=xxx,字段=xxx where 条件;
- 删除数据:
delete from t1 where 条件;
2.练习:
- 创建数据库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;
ALTER TABLE `mo_config_detail`
ADD COLUMN `config_dayshift_time` char(8) NOT NULL DEFAULT '' COMMENT 'a' AFTER `a_time`,
ADD COLUMN `config_nightshift_time` char(8) NOT NULL DEFAULT '' COMMENT 'b' AFTER `b_time`;
- 修改部门id为1的部门奖金为500
update emp set comm=500 where deptId = 1;
- 把取经部的地址改成五台山
update dept set loc='五台山' where id = 1;
- 删除两个表
drop table emp; drop table dept;
3.主键约束 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
4.主键+自增 auto_increment
- 自增规则:从历史最大值+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
5.导入*.sql文件
链接: https://pan.baidu.com/s/1scR1Hh9OwnLXI4w0UAQn9w 密码: ttqk
- 把下载的emp.sql 放到d盘根目录
mysql下 source d:/emp.sql;
- 如果导入后 执行select * from emp 发现有乱码 执行 set names gbk;
6.去重distinct
- 查询员工表中有哪些不同的工作
select distinct job from emp;
7.is null 和 is not null
- 判断某个字段的值为null时不能使用=
- 查询没有上级领导的员工信息
select * from emp where mgr is null;
- 查询有上级领导的员工姓名和上级领导编号
select ename,mgr from emp where mgr is not null;
8.比较运算符 > < >= <= = !=和<>
- 查询员工工资小于等于3000的员工姓名和工资
select ename,sal from emp where sal<=3000;
- 查询工作不是程序员的员工姓名和工作(两种写法)
select ename,job from emp where job!='程序员';
select ename,job from emp whesre job<>'程序员';
9.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;
10.in(x,y,z)
- 当查询某个字段的值为多个的时候使用in关键字
- 查询工资为1500,3000,5000的员工信息
select * from emp where sal=1500 or sal=3000 or sal=5000;
select * from emp where sal in(1500,3000,5000);
11.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);
12.综合练习
- 查询有上级领导并且是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;
13.模糊查询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 '%销售%';
14.排序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;
mysql> select * from emp order by deptno asc, sal desc;
+-------+-----------+--------------+------+------------+----------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREdate | SAL | COMM | DEPTNO |
+-------+-----------+--------------+------+------------+----------+---------+--------+
| 20 | 灭霸 | NULL | NULL | NULL | NULL | NULL | NULL |
| 8 | 观音 | CEO | NULL | 1981-11-17 | 5000.00 | NULL | 1 |
| 4 | 唐僧 | 销售经理 | 8 | 1981-04-02 | 2975.00 | NULL | 1 |
| 2 | 猪八戒 | 销售 | 4 | 1981-02-20 | 1600.00 | 300.00 | 1 |
| 3 | 沙僧 | 销售 | 4 | 1981-02-22 | 1250.00 | 500.00 | 1 |
| 1 | 孙悟空 | 销售 | 4 | 1980-12-17 | 800.00 | NULL | 1 |
| 21 | 小悟空 | 程序员 | 8 | 1993-09-17 | 33333.00 | 1212.00 | 2 |
| 11 | 黑熊怪 | 市场 | 8 | 1981-12-03 | 3000.00 | NULL | 2 |
| 9 | 白骨精 | 人事 | 8 | 1981-09-08 | 1500.00 | 0.00 | 2 |
| 10 | 蜘蛛精 | 人事 | 8 | 1981-12-03 | 950.00 | NULL | 2 |
| 6 | 关羽 | 程序员 | 5 | 1981-05-01 | 2850.00 | NULL | 3 |
| 7 | 张飞 | 程序员 | 5 | 1981-06-09 | 2450.00 | NULL | 3 |
| 5 | 刘备 | 项目经理 | NULL | 1981-09-28 | 1250.00 | 1400.00 | 3 |
+-------+-----------+--------------+------+------------+----------+---------+--------+
15.分页查询
- 格式: 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 dept_id FROM mo_config_detail_people WHERE mo_config_detail_id = 2 group by dept_id LIMIT pageSize OFFSET pageNumber-1 *(page size);
16.综合练习题
- 查询员工表中名字里第二个字是八的员工姓名和工资
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;
select ename,sal from emp where sal>1000 order by sal desc LIMIT pageSize OFFSET pageNumber-1 *(page size);
17.数值计算+ - * /
- 查询每个员工的姓名,工资和年终奖(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;
18.聚合函数
- 聚合函数是对查询的多条数据进行统计查询,包括:求平均值,最大值,最小值,求和,计数
- 平均值avg
- 查询1号部门的平均工资
select avg(sal) from emp where deptno=1;
- 查询程序员的平均工资
select avg(sal) from emp where job='程序员';
- 查询1号部门的平均工资
- 最大值max
- 查询3号部门的最高工资
select max(sal) from emp where deptno=3;
- 查询3号部门的最高工资
- 最小值min
- 查询销售的最低工资
select min(sal) from emp where job='销售';
- 查询销售的最低工资
- 求和sum
- 查询2号部门的工资总和
select sum(sal) from emp where deptno=2;
- 查询2号部门的工资总和
- 计数count
- 查询工资高于2000的员工人数
select count(*) from emp where sal>2000;
- 查询2号部门的人数
select count(*) from emp where deptno=2;
- 查询工资高于2000的员工人数
19.练习题
- 查询员工表中工资高于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 ‘%空%’;
20.分组查询
- 题目需求中每个xx 就以xx作为分组的字段
- 查询员工表的平均工资
select avg(sal) from emp; - 查询1号部门的平均工资
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;
21.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;
22. limit offset用法
Limit使用方法
limit m, n;
# 表示从跳过 m 条数据开始取n行数据
# m 可选,表示跳过 m 条数据(默认为0)
# n 必选,表示取几行数据
-- 查询前 10 条数据
select * from table_name limit 10;
-- 以上写法等同于
select * from table_name limit 0, 10;
-- 查询 10-20 条数据
select * from table_name limit 10, 10;
limit 结合 offset 用法:
limit m offset n
# 跳过 n 行数据,取 m 行数据
-- 查询 10-20 条数据
select * from table_name limit 10 offset 10;
分页
通过limit和offset 或只通过limit可以实现分页功能
假设 numberperpage 表示每页要显示的条数,pagenumber表示页码,那么 返回第pagenumber页,每页条数为numberperpage的sql语句:
语句3:select * from student limit (pagenumber-1)*numberperpage,numberperpage
语句4:select * from student limit numberperpage offset (pagenumber-1)*numberperpage