数据库03-主键约束/自增/导入/比较运算符/与或非/两者之间去重/模糊查询/排序/分页查询/聚合函数/分组查询/having/Idea中配置连接MySQL步骤

目录

主键约束+自增 auto_increment

导入*.sql

比较运算符 > < >= <= = !=和<>

与或非 and or not

between x and y 两者之间 包含x和y

in(x,y,z)

去重 distinct

模糊查询 like

排序 order by

分页查询 limit

别名

聚合函数

分组查询 group by

having

Idea中配置连接MySQL步骤:

---

主键约束 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

1. 从数据库文件夹中找到emp.zip 复制到某个盘的根目录,并解压到当前文件夹,得到emp.sql文件

2. 在命令行中连接上MySQL之后 执行 source 路径; 把文件导入到自己的MySQL数据库中

source D:/emp.sql;

1. show tables; 检查是否出现了两个表 分别是emp和dept

2. 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(字段名)

1. 查询程序员的工资总和

select sum(sal) from emp where job='程序员';

5.计数count(*)

1. 查询销售的数量

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步骤:

1. 添加MySQL连接

1. 在页面中输入用户名和密码 然后点击Test Connection 如果不能点击则需要先下载驱动

• 如果点击TestConnection在下面有红色报错 则将URL改成下面的地址

jdbc:mysql://localhost:3306?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false

1. 添加完之后在下面窗口中执行SQL语句