目录
主键约束 primary key
-
约束: 创建表时给表字段添加的限制条件
-
主键: 表示数据唯一性的字段称为主键
-
主键约束: 唯一且非空
-
举例:
create database day2db charset=utf8;
use day2db;
create table t1(id int primary key,name varchar(20));
insert into t1 values(1,'aaa');
insert into t1 values(2,'bbb');
insert into t1 values(2,'ccc'); 报错:Duplicate entry '2' for key 'PRIMARY'
insert into t1 values(null,'ccc'); 报错: Column 'id' cannot be null
主键约束+自增 auto_increment
-
自增规则: 从历史最大值+1
create table t2(id int primary key auto_increment,name varchar(20));
insert into t2 values(null,'aaa');
insert into t2 values(null,'bbb');
insert into t2 values(10,'ccc');
insert into t2 values(null,'ddd');
delete from t2 where id>=10;
insert into t2 values(null,'eee');
导入*.sql
-
从数据库文件夹中找到emp.zip 复制到某个盘的根目录,并解压到当前文件夹,得到emp.sql文件
-
在命令行中连接上MySQL之后 执行 source 路径; 把文件导入到自己的MySQL数据库中
source D:/emp.sql;
-
show tables; 检查是否出现了两个表 分别是emp和dept
-
select * from emp; 如果查询时出现乱码 执行 set names utf8;
比较运算符 > < >= <= = !=和<>
-
查询工资小于等于3000的员工姓名和工资
select name,sal from emp where sal<=3000;
-
查询程序员的名字
select name from emp where job='程序员';
-
查询2号部门的员工姓名,工资和工作
select name,sal,job from emp where dept_id=2;
-
查询不是人事的员工姓名和工作(两种写法)
select name,job from emp where job!="人事";
select name,job from emp where job<>"人事";
与或非 and or not
-
and: 查询多个条件同时满足时使用
-
or : 查询多个条件满足一个条件时使用
-
not: 取反
-
查询1号部门工资高于2000的员工信息
select * from emp where dept_id=1 and sal>2000;
-
查询三号部门或工资等于5000的员工信息
select * from emp where dept_id=3 or sal=5000;
-
查询有上级领导的员工姓名
select name from emp where manager is not null;
-
查询出CEO和项目经理的名字
select name from emp where job='ceo' or job='项目经理';
-
查询有奖金的销售名字和奖金
select name,comm from emp where comm>0 and job='销售';
between x and y 两者之间 包含x和y
-
查询工资在2000到300之间的员工信息
select * from emp where sal between 2000 and 3000;
-
查询工资在2000到3000以外的员工信息
select * from emp where sal not between 2000 and 3000;
in(x,y,z)
当查询某个字段的值为多个的时候使用
-
查询工资等于3000,1500和5000的员工信息
select * from emp where sal in(3000,1500,5000);
-
查询工作是程序员和销售的员工信息
select * from emp where job in('程序员','销售');
去重 distinct
-
查询1号部门中出现了哪几种不同的工作
select distinct job from emp where dept_id=1;
-
查询员工表中出现了哪几种不同的部门id
select distinct dept_id from emp;
模糊查询 like
-
_: 代表1个未知字符
-
%: 代表0或多个未知字符
-
举例:
-
以x开头 x%
-
以x结尾 %x
-
以x开头y结尾 x%y
-
包含x %x%
-
第二个字符是x _x%
-
第三个是x倒数第二个是y __x%y_
-
-
查询姓孙的员工姓名
select name from emp where name like "孙%";
-
查询名字中包含僧的员工信息
select * from emp where name like "%僧%";
-
查询名字以精结尾的员工姓名
select name from emp where name like "%精";
-
查询工作中包含销售并且工资大于1500的员工信息
select * from emp where job like "%销售%" and sal>1500;
-
查询工作中第二个字是售的员工姓名和工作
select name,job from emp where job like "_售%";
-
查询1号和2号部门中工作以市开头的员工信息
select * from emp where dept_id in(1,2) and job like "市%";
排序 order by
格式: order by 排序的字段名 asc升序(默认)/desc降序;
-
查询每个员工的姓名和工资,按照工资升序
select name,sal from emp order by sal;
select name,sal from emp order by sal asc;
-
查询每个员工的姓名和工资,按照工资降序
select name,sal from emp order by sal desc;
-
查询工资高于2000的员工姓名和工资, 按照工资降序排序
select name,sal from emp where sal>2000 order by sal desc;
-
查询姓名,工资和部门id 按照部门id升序排序,如果部门id一致则按照工资降序排序
select name,sal,dept_id from emp order by dept_id,sal desc;
分页查询 limit
-
格式: limit 跳过的条数,请求的条数(每页的条数)
-
跳过的条数=(请求的页数-1)*每页的条数
-
举例:
-
第一页的5条数据 limit 0,5
-
第2页的5条数据 limit 5,5
-
第3页的5条数据 limit 10,5
-
第4页的10条数据 limit 30,10
-
第8页的7条数据 limit 49,7
-
第3页的9条数据 limit 18,9
-
1.查询所有员工id,姓名和工资,按照工资升序排序请求第一页的5条数据
select id,name,sal from emp order by sal limit 0,5;
2.查询所有员工的姓名和工资,按照工资升序排序,请求第2页的5条数据
select name,sal from emp order by sal limit 5,5;
3.查询工资最高的员工信息
select * from emp order by sal desc limit 0,1;
4.按照入职日期排序 查询第2页的3条数据
select * from emp order by hiredate limit 3,3
5.按照工资升序排序查询第3页的2条数据
select * from emp order by sal limit 4,2;
别名
select name as "名字" from emp;
select name "名字" from emp;
select name 名字 from emp;
聚合函数
-
可以对查询到的多条数据进行统计查询
-
统计方式包括:
-
求平均值
-
求和
-
求最大值
-
求最小值
-
计数
-
1.平均值avg(字段名)
- 查询1号部门的平均工资
select avg(sal) from emp where dept_id=1;
- b. 查询销售的平均工资
select avg(sal) from emp where job='销售';
2.最大值max(字段名)
-
查询最高工资
select max(sal) from emp;
3.最小值min(字段名)
- 查询最低工资
select min(sal) from emp;
4.求和sum(字段名)
-
查询程序员的工资总和
select sum(sal) from emp where job='程序员';
5.计数count(*)
-
查询销售的数量
select count(*) from emp where job="销售";
分组查询 group by
-
分组查询可以将某个字段相同值的数据划分为一组, 然后以组为单位进行统计查询
1.查询每个部门的人数
select count(*) from emp where dept_id=1;
select count(*) from emp where dept_id=2;
select count(*) from emp where dept_id=3;
select dept_id,count(*) from emp group by dept_id;
2.查询每种工作的人数
select job,count(*) from emp group by job;
3.查询每种工作的平均工资
select job,avg(sal) from emp group by job;
4.查询每个部门的最高工资
select dept_id,max(sal) from emp group by dept_id;
5.查询每个部门工资高于2000的人数
select dept_id,count(*) from emp where sal>2000 group by dept_id;
6.查询每种工作的最低工资
select job,min(sal) from emp group by job;
7.查询1号部门和2号部门的人数
select dept_id,count(*) from emp where dept_id in(1,2) group by dept_id;
8.查询平均工资最高的部门id和平均工资
select dept_id,avg(sal) from emp group by dept_id order by avg(sal) desc limit 0,1;
-
通过别名 复用
select dept_id,avg(sal) a from emp group by dept_id order by a desc limit 0,1;
having
-
where后面只能写普通字段的条件, 不能写聚合函数条件
-
having后面专门用来写聚合函数条件, 而且having要和group by 结合使用 写在group by的后面
1.查询每个部门的平均工资,只查询出平均工资大于2000
select dept_id,avg(sal) from emp group by dept_id having avg(sal)>2000;
select dept_id,avg(sal) a from emp group by dept_id having a>2000;
2.查询每种工作的人数,只查询人数大于1的
select job,count(*) c from emp group by job having c>1;
3.查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400
select dept_id,sum(sal) s from emp
where manager is not null group by dept_id having s>5400;
4. 查询每个部门的平均工资, 只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的
select dept_id,avg(sal) a from emp
where sal between 1000 and 3000 group by dept_id having a>=2000;
Idea中配置连接MySQL步骤:
-
添加MySQL连接
-
在页面中输入用户名和密码 然后点击Test Connection 如果不能点击则需要先下载驱动
-
如果点击TestConnection在下面有红色报错 则将URL改成下面的地址
jdbc:mysql://localhost:3306?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false
-
添加完之后在下面窗口中执行SQL语句