mysql 随机选择列表中的某个值更新某个字段

一、数据库相关的SQL语句

1. 查询所有数据库

show databases;

2. 创建数据库

create database db1;

3. 删除数据库

drop database db1;

4. 查看数据库详情

show create database db1;

5. 创建数据库时指定字符集

create database db2 character set gbk;

6. 使用数据库

use db1;

二、MySQL表的引擎

1. InnoDB：支持数据库的高级操作,如：事务，外键等
2. MyIsam：只支持数据基础的增删改查

三、表相关SQL 语句
注： 执行表相关SQL需要先使用数据库 use d eb1
创建表

create table t1(name varchar(10)) engine=myisam charset=gbk;

查询所有表

show tables;

表详情

show create table t1;

查看表字段

desc t1;

删除表

drop table t1;

四、修改表的相关sql

1. 修改表名
   -格式：

rename table 原名 to 新名;
	rename table student to t_stu;
	show tables;

2.修改表的引擎和字符集

-alter table 表名 engine=myisam/innodb charset=utf8/gbk;

alter table stu engine=myisam charset=gbk;

3. 添加表字段
   -在表的最后位置添加
   alter table 表名 add 新字段名 类型;

alter table hero add age int;

• 在表的最前面添加
  -格式：alter table 表名 add 新字段名 类型 first;

alter table hero add first;

• 在某个字段的后面添加
  -格式：alter table 表名 add id int 新字段名 新字段类型 after 老字段名;

alter table hero add sal int after name;

4. 删除表字段
   -格式：alter table 表名 drop 字段名;

alter table hero drop sal;

5. 修改表字段的名称和类型
   -格式：alter table 表名 change 老字段名 新字段名 新字段类型;

alter table hero change name heroname varchar(5);

6. 修改字段类型和字段的位置
   -格式： alter table 表名 modify 字段名 新类型 first/after xxx;

alter table hero modify age int first;

数据相关

插入数据

create table emp(id int,name varchar(10),age int,sal int);

1. 插入数据
   -全表插入格式：insert into 表名 values (值1,值2,值3…);

insert into emp values(1,'李白',18,3000);

-指定字段插入格式： insert into 表名 (字段名1,字段名2) values (值1,值2);

insert into emp (id,name) values(2,'刘备');
	insert into emp (name) values ('张飞');

中文字符问题

set names gbk;

- 批量插入数据格式：insert into 表名 values (值1,值2,值3......),(值1,值2,值3......),(值1,值2,值3......);

insert into emp values(4,'貂蝉',88),(5,'吕布',18);
	insert into emp (name,age) values("悟空",5000),("唐僧",400);

2.查询数据
查询格式
查询全部数据的全部字段信息
select * from 表名;
例如：

select * from emp;

查询所有员工的姓名和年龄(-指定字段查询)

select name,age from emp;

-条件查询格式；
格式：select * from 表名 where 条件
例如：

select * from emp where id<10;

查询工资3000块钱的员工姓名 年龄 工资

select name,age,sal from emp where sal=3000;

• 修改数据

修改Tom的工资为3333;

update emp set sal=3333 where name="Tom";

修改多个值(id=3的名字为吕布,年龄为55,工资为20000)

update emp set name="吕布",age=55,sal=20000 where id=3;

修改工资是NULL的为800(null不能用等号 用is)

update emp set sal=800 where sal is null;

• 删除数据
  删除id=1的员工
  -格式：delete from 表名 where 条件;

delete from emp where id=1;

删除年龄在25岁以下的员工

delete from emp where age<25;

删除全部数据

delete from emp;

练习

1.创建hero表如果存在则先删除再创建,id 姓名name 类型type 金币money
2.插入以下数据 1 诸葛亮 法师18888 2 孙悟空 打野 18888 3 小乔 法师6888 4黄忠 射手8888 5刘备 战士6888
3.

第三章约束

主键约束 primary key

• 什么是主键：
  表示表中数据唯一性的字段称之为主键（主键只有一个）
• 什么是约束： 约束就是创建表的时候给表中字段添加的限制条件
• 主键约束：添加了主键约束的字段，保存的数值必须唯一而且非空
• 格式：

create table t1(id int primary key,name varchar(10));

create table t1(id int primary key,name varchar(10));
insert into t1 values(1,"tom");
insert into t1 values(1,"jerry");//失败 不能重复
insert into t1 values(null,"abc")//失败 不能为null

自增：auto_increment

creat table t2(id int primary key auto_increment,name varchar(10));

• 当字段值赋值为null时 字段会值会自动增长
• 自增字段值也可以手动赋值
• 删除数据数值不会减
• 数值会从曾经的最大值基础上+1
• 可以使用truncate 使自增数值清零

注释 comment

• 创建表的时候可以通过comment关键字对字段进行描述
• 格式：

create table t3(id int primary key auto_increment comment '这个字段是主键',name varchar(10),sal int comment '这是工资');

• 通过 show create table t3; 查看字段的注释

`和’的区别

• 的作用：是用来修饰表名和字段名的，可以省略 create tablet4(idint,age` int);

数据冗余

• 由于表设计不够合理，出现大量的重复数据，称为数据冗余。可以通过拆分表的形式，把可能重复的数据保存到一张新的表中，
  在原表中只需要通过一个id建立关系即可。这种建立关系的字段称之为外键。

练习：设计表保存以下数据：

1. 集团总部下面的市场部下的市场A部的员工刘备，工资8000，性别男，年龄25
2. 教学部下 Java教学部的员工苍老师，工资100000，性别男，年龄18

• 创建员工表

create table emp(id int primary key auto_increment,name varchar(10),sal int,gender varchar(5),age int,deptid int);

• 创建部门表

create table dept(id int primary key auto_increment,name varchar(10),parentid int);

• 插入数据：

insert into dept values(null,'集团总部',null);
	insert into dept values(null,'市场部',1);
	insert into dept values(null,'市场部A',2);
	insert into emp values(null,'刘备',8000,'男',25,3);
	insert into dept values(null,'教学部',1);

练习

事务

什么是事务：事务是数据库中执行sql语句的最小工作单元，不能拆分，可以保证事务中的多条SQL语句全部成功或全部失败

事务相关指令

1. 查看数据库自动提交状态

show variables like '%autocommit%';

2. 修改自动提交状态 set autocommit=0/1;(0关闭 1开启)

set autocommit=0/1;

3. 提交 commit;

commit;

4. 回滚 rollback

rollback

5. 设置回滚点 savepoint s1;

savepoint s1;

update person set money=100 where id=1;
savepoint s1;
update person set money=200 where id=2;
savepoint s2;

6. 回滚到指定回滚点： rollback to s1;

rollback to s1;

SQL的分类

DDL Data Definition Language

• 数据定义语言，包括 create,alter,drop,truncate ,不支持事务

DML Data Manipulation Language

• 数据操作语言， 包括 insert,delete,update,select(DQL),支持事务

DQL Data Query Language

• 数据查询语言，只包括select，和事务没有关系因为并没有修改数据

TCL Transaction Control Language

• 事务控制语言，包括 commit,rollback, savepoint,rollback to

DCL Data Control Language

• 数据控制语言，用于处理分配用户权限相关的操作

truncate

• truncate table 表名;
• 删除表并且创建一个新表
• truncate、drop和delete的区别：
• delete用于删除数据，使用delete清空表时自增数值不清零 执行效率最低
• drop 用于删除表 执行效率最高
• truncate 用于删除表并创建新的空表，执行效率比delete要高，而且自增数值会清零

数据库的数据类型

整数

• 常用整数有 int(m)和bigint(m)，m代表显示长度必须和zerofill结合使用

create table t_int(num int(10) zerofill);
	insert into t_int values(123);
	select * from t_int;

浮点数

• 常用浮点数double(m,d) m代表总长度 d代表小数长度 23.346 m=5 d=3
• decimal超高精度浮点数，应用场景：涉及超高精度运算时使用

create table t_double(num double(5,3));
	insert into t_double values(23.5678); 值为23.568
	insert into t_double values(23.5); 值为23.500

字符串

• char(m): 固定长度 m=10 abc 占10，效率高，最大255
• varchar(m):可变长度 m=10 abc 占3，节省空间，最大65535，如果超过255建议使用text
• text(m):可变长度 最大65535

日期

• date:只能保存年月日
• time:只能保存时分秒
• datetime:保存年月日时分秒，默认值为null，最大值9999-12-31
• timestamp(时间戳距离19700101 08：00：00):保存年月日时分秒，默认值为当前系统时间,最大值2038-01-19

create table t_time(t1 date,t2 time,t3 datetime,t4 timestamp);
	insert into t_time values('2018-10-16',null,null,null);
	insert into t_time values
			(null,'20:06:32','2008-11-22 18:22:11',null);

配置URL：
jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=UTF-8

三方SQL工具：sqlyog

------------查询--------------

is null 和 is not null

1. 查询emp表中没有上级领导mgr的员工编号empno，姓名ename，工资sal

select empno,ename,sal from emp where mgr is null;

2. 查询emp表中没有奖金comm的员工姓名，工资，奖金

select ename,sal,comm from emp where comm is null;

3. 查询有奖金的所有员工信息

select * from emp where comm is not null;

别名

1. 将查询到的员工姓名ename改成‘姓名’

select ename from emp;
	select ename as '姓名' from emp; 
	select ename '姓名' from emp;
	select ename 姓名,sal 工资 from emp;

去重 distinct

1. 查询emp表中出现的所有职位job

select distinct job from emp;

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

1. 查询工资小于等于1600的所有员工的姓名和工资

select ename,sal from emp where sal<=1600;

2. 查询部门编号是20的所有员工姓名、职位和部门编号deptno

select ename,job,deptno from emp where deptno=20;

3. 查询职位是manager的所有员工姓名和职位

select ename,job from emp where job='manager';

4. 查询部门不是10号部门的所有员工姓名和部门编号(两种写法)

select ename,deptno from emp where deptno!=10;
	select ename,deptno from emp where deptno<>10;

5. 查询商品表t_item中单价price等于23的商品信息

select title,price from t_item where price=23;

6. 查询商品表中单价不等于8443的商品信息

select * from t_item where price!=8443;

and 和 or

• and等效java中的&&
• or等效java中的||
• 查询工资大于2000并且是10号部门的员工信息

select * from emp where sal>2000 and deptno=10;

1. 查询不是10号部门并且工资大于等于1600的员工姓名，工资，部门编号

select ename,sal,deptno from emp where deptno!=10 and sal>=1600;

2. 查询部门是30号部门或者上级领导mgr为7698的员工姓名,职位，上级领导和部门编号

select ename,job,mgr,deptno from emp where deptno=30 or mgr=7698;

3. 查询有上级领导并且工资低于2000并且是20号部门的员工信息

select * from emp where mgr is not null and sal<2000 and deptno=20;

in

1. 查询emp表中工资是5000，1500，3000的员工信息

select * from emp where sal=5000 or sal=1500 or sal=3000;
	select * from emp where sal in(5000,1500,3000);

between x and y 包含x和y

1. 查询工资在2000到3000之间的员工姓名和工资

select ename,sal from emp where sal>=2000 and sal<=3000;
	select ename,sal from emp where sal between 2000 and 3000;

模糊查询 like

• _:代表单个未知字符
• %：代表0或多个未知字符
• 举例：

1. 以a开头的字符串 a%
2. 以m结尾 %m
3. 包含x %x%
4. 第二个字符是a _a%
5. 倒数第三个字母是m %m__
6. 以a开头并且倒数第二个字母是b a%b_

• 案例：

7. 查询名字中包含a的所有员工姓名和工资
   select ename,sal from emp where ename like ‘%a%’;
8. 查询标题中包含记事本的商品标题及商品价格
   select title,price from t_item where title like ‘%记事本%’;
9. 查询单价低于100的记事本(title包含记事本)
   select * from t_item where title like ‘%记事本%’ and price<100;
10. 查询单价在50到200之间的得力商品(title包含得力)
    select * from t_item where price between 50 and 200 and title like ‘%得力%’;
11. 查询商品分类为238和917的商品信息
    select * from t_item where category_id in(238,917);
12. 查询有赠品的商品信息 （卖点sell_point中包含赠字）
    select * from t_item where sell_point like ‘%赠%’;
13. 查询有图片image的得力商品信息
    select * from t_item where image is not null and title like ‘%得力%’;
14. 查询和得力无关的商品信息(title不包含得力)
    select * from t_item where title not like ‘%得力%’;
15. 查询价格在50到100以外的商品信息
    select * from t_item where price not between 50 and 100;

排序 order by

• order by 关键字， by后面写排序的字段名称 默认是升序，
• asc升序 desc降序

1. 查询所有员工的姓名和工资按照工资升序排序
   select ename,sal from emp order by sal;
2. 查询10号部门的所有员工信息，按照工资降序排序
   select * from emp where deptno=10 order by sal desc;
3. 查询所有带燃字的商品，按照单价升序排序
   select * from t_item where title like ‘%燃%’ order by price;
4. 查询所有dell商品按照分类category_id升序排序
   select * from t_item where title like ‘%dell%’ order by category_id;
5. 查询所有员工按照部门升序排序如果部门一致则按照工资降序排序
   select * from emp order by deptno,sal desc;
6. 查询所有商品分类和单价按照分类降序排序，如果分类相同则按照单价升序排序
   select category_id,price from t_item order by category_id desc,price;

分页查询 limit

• limit 跳过的条数,请求的数量（每页的数量）

1. 查询员工表中工资降序的前5条数据
   select * from emp order by sal desc limit 0,5;
2. 查询员工表中工资降序的第3页的4条数据
   select * from emp order by sal desc limit 8,4;
3. 查询商品表中价格升序的前10条数据
   select * from t_item order by price limit 0,10;
4. 查询商品表中价格低于100元的商品信息第三页的三条数据
   select * from t_item where price<100 limit 6,3;
5. 查询10号和30号部门的员工工资在前三名的员工信息
   select * from emp where deptno in(10,30)
   order by sal desc limit 0,3;

concat()函数

• 把concat内部的参数拼接到一起

1. 查询员工姓名和工资，要求工资单位是元

select ename,concat(sal,'元') 工资 from emp;

数值计算 + - * / %（mod(7,2)等效 7%2）

1. 查询商品表中每个商品的单价，库存及总价值（单价*库存）

select price,num,price*num from t_item;

2. 查询员工表中每个员工的姓名，工资，及年终奖(五个月的工资)

select ename,sal,sal*5 年终奖 from emp;

日期相关函数

1. 获取当前的年月日时分秒

select now();

2. 获取当前的日期 current

select curdate();

3. 获取当前的时间

select curtime();

4. 从年月日时分秒中提取年月日

select date(now());

5. 从年月日时分秒提取时分秒

select time(now());

6. 从年月日时分秒中提取时间分量 年 月 日 时 分 秒

- extract(year from now())
- extract(month from now())
- extract(day from now())
- extract(hour from now())
- extract(minute from now())
- extract(second from now())
	select extract(year from now());

• 查询员工表中的所有员工姓名和入职的年份

select ename,extract(year from hiredate) from emp;

7. 日期格式化 date_format(时间,格式)

• %Y:四位年 2018
• %y:两位年 18
• %m:两位月 05
• %c:一位月 5
• %d:日
• %H: 24小时
• %h: 12小时
• %i: 分
• %s: 秒

select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒');

• 查询商品名称和商品的上传日期(格式：x年x月x日)

select title,date_format(created_time,'%Y年%m月%d日') from t_item;

• 把非标准的日期字符串转成标准的时间格式 str_to_date(时间字符串,格式)
  14.08.2018 08:00:00

select str_to_date('14.08.2018 08:00:00','%d.%m.%Y %H:%i:%s');

ifnull(x,y)函数

• age=ifnull(x,18) 如果x的值为null 则age=18 如果不为null则 age=x

1. 修改员工表中奖金为null的值为0

update emp set comm=ifnull(comm,0);

聚合函数

• 用于对多条数据进行统计

1. 求和 sum(字段名)

• 查询emp中10号部门的工资总和

select sum(sal) from emp where deptno=10;

2. 平均值 avg(字段名)

• 查询emp表中所有员工的工资平均值

select avg(sal) from emp;`

3. 最大值 max(字段名)

• 查询30号部门的员工的最高奖金

select max(comm) from emp where deptno=30;

4. 最小值 min(字段名)

• 查询商品表中价格最便宜的商品的单价

select min(price) from t_item;

5. 统计数量 count(字段名) 一般使用count(*)

• 统计30号部门有多少人

select count(*) from emp where deptno=30;

聚合函数练习：sum(),max(),min(),avg(),count(*)

1. 统计工资在2500以上的员工人数

select count(*) from emp where sal>2500;

2. 查询工资在1000到3000以内的最大奖金

select max(comm) from emp where sal between 1000 and 3000;

3. 查询30号部门的最大奖金，最高工资，工资平均值，工资总和，并对查询结果起别名

select max(comm) 最大奖金,max(sal) 最高工资, avg(sal) 平均工资,sum(sal) 工资总和 from emp where deptno=30;

4. 查询价格在100元以内的商品数量

select count(*) from t_item where price<100;

5. 查询名字包含a的员工数量

select count(*) from emp where ename like '%a%';

和字符串相关函数

1. 获取字符串的长度 char_length(str);
   获取所有员工的姓名和姓名的字符长度

select ename,char_length(ename) from emp;

2. 获取字符串在另外一个字符串中出现的位置 instr(str,substr)

select instr('abcdefg','d');

3. 插入字符串 insert(str,start,length,newStr)

select insert('abcdefg',3,2,'m');  //abmefg

4. 转大写 转小写

select upper('abc'),lower('NBA');

5. 左边截取和右边截取

select left('abcdefg',2) , right('abcdefg',2);

6. 去两端空白

select trim(' a b ');

7. 截取字符串

select substring('abcdefg',3,2);

8. 重复 repeat(str,count)

select repeat('ab',2);

9. 替换 replace(str,old,new)

select replace('This is mysql','my','your');

10. 反转 reverse(str)

select reverse('abc');

案例：

1.案例：查询没有上级领导的员工的编号，姓名，工资
	select empno,ename,sal from emp where mgr is null;
2.案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金
	select ename,job,sal,comm from emp where comm is null or comm=0;
3.案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金
	select empno,ename,job,comm from emp where comm is not null and comm>0;
4.案例：查询含有上级领导的员工的姓名，工资以及上级领导的编号
	select ename,sal,mgr from emp where mgr is not null;
5.案例：查询emp表中名字以‘S’开头的所有员工的姓名
	select ename from emp where ename like 's%';
6.案例：查询emp表中名字的最后一个字符是'S'的员工的姓名
	select ename from emp where ename like '%s';
7.案例：查询倒数的第2个字符是‘E’的员工的姓名
	select ename from emp where ename like '%e_';
8.案例：查询emp表中员工的倒数第3个字符是‘N’的员工姓名
	select ename from emp where ename like '%n__';
9.案例：查询emp表中员工的名字中包含‘A’的员工的姓名	
	select ename from emp where ename like '%a%';
10.案例：查询emp表中名字不是以'K'开头的员工的所有信息
	select * from emp where ename not like 'k%';
11.案例：查询emp表中名字中不包含‘A’的所有员工的信息
	select * from emp where ename not like '%a%';
12.案例：做文员的员工人数（job 中 含有 CLERK 的）
	select count(*) from emp where job='clerk';
13.案例：销售人员 job: SALESMAN 的最高薪水
	select max(sal) from emp where job='SALESMAN';
14.案例：最早和最晚入职时间
	select min(hiredate),max(hiredate) from emp;
15.案例：查询类别 163的商品总库存量
	select sum(num) from t_item where category_id=163;
16.案例：查询 类别 163 的商品
	select * from t_item where category_id=163;
17.案例：查询商品价格不大于100的商品名称列表
	select * from t_item where price<=100;
18.案例：查询品牌是联想,且价格在40000以上的商品名称和价格
	select title,price from t_item where price>40000 and title like '%联想%';
19.案例：查询品牌是三木,或价格在50以下的商品名称和价格
	select title,price from t_item where price<50 or title like '%三木%';
20.案例：查询品牌是三木、广博、齐心的商品名称和价格
	select title,price from t_item where title like '%三木%' or title like '%广博%' or title like '%齐心%';
21.案例：查询品牌不是联想、戴尔的商品名称和价格
	select title,price from t_item where title not like '%联想%' and title not like '%戴尔%';
22.案例：查找品牌是联想且价格大于10000的电脑名称
	select title from t_item where price>10000 and title like '%联想%';
23.案例：查询联想或戴尔的电脑名称列表
	select title from t_item where title like '%联想%' or title like '%戴尔%'; 
27.案例：查询品牌是末尾字符是'力'的商品的品牌、名称和价格
	select title,price from t_item where title like '%力';
30.案例：查询emp表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。
	select empno,ename,job,sal from emp where sal between 1000 and 2000;
31.案例：查询emp表中员工在10号部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门的编号
	select ename,job,mgr,deptno from emp 
	where deptno=10 and mgr is not null;
32.案例：查询emp表中名字中包含'E'，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。
	select empno,ename,job,sal from emp where ename like '%e%' and job!='manager';	
33.案例：查询emp表中10号部门或者20号部门中员工的编号，姓名，所属部门的编号
	select empno,ename,deptno from emp where deptno in(10,20);
34.案例：查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金
	select empno,ename,job,comm from emp where comm>0 or ename not like '%t_';
35.案例：查询工资高于3000或者部门编号是30的员工的姓名，职位，工资，入职时间以及所属部门的编号	
	select ename,job,sal,hiredate,deptno from emp
	where sal>3000 or deptno=30;
36.案例：查询不是30号部门的员工的所有信息
	select * from emp where deptno!=30;
37.案例：查询奖金不为空的员工的所有信息
	select * from emp where comm is not null;
38.案例：查询emp表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列
	select empno,ename,job from emp order by empno desc;
39.案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列
	select ename,job,sal from emp where deptno in(10,30) order by sal;
40.案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列
	select * from emp order by deptno,empno desc;
41.案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。
	select empno,ename,sal,deptno,mgr from emp where sal>1000 or mgr is null order by deptno desc,sal ;
42.案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
	select empno,ename,sal,comm from emp where ename not like '%s%' order by sal,empno desc;
43.案例：统计emp表中员工的总数量
	select count(*) from emp;
44.案例：统计emp表中获得奖金的员工的数量
	select count(*) from emp where comm>0;
45.案例：求出emp表中所有的工资累加之和
	select sum(sal) from emp;
46.案例：求出emp表中所有的奖金累加之和
	select sum(comm) from emp;
47.案例：求出emp表中员工的平均工资
	select avg(sal) from emp;
48.案例：求出emp表中员工的平均奖金
	select avg(comm) from emp;
49.案例：求出emp表中员工的最高工资
	select max(sal) from emp;
50.案例：求出emp表中员工编号的最大值
	select max(empno) from emp;
51.案例：查询emp表中员工的最低工资。
	select min(sal) from emp;
52.案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。
	select count(*) 人数,sum(sal) 工资总和,avg(sal) 平均工资, max(comm) 奖金最大值,min(comm) 奖金最小值 from emp;

数学相关函数

1. 向下取整 floor(num)

select floor(3.84);  //3

2. 四舍五入 round(num);
   select round(3.84); // 4
   -round(num,m) m代表小数位数
   select round(3.84567,3); //3.846
3. 非四舍五入 truncate(num,m)

select truncate(3.84567,3);

4. 随机数 rand() 0-1 5-10 0-5
   select floor(rand()*6) + 5;
   3-8 0-5 select floor(rand()*6) +3;

分组查询

• 分组查询通常和聚合函数结合使用，以组为单位进行统计
• 一般情况下，题目中每个xxx 就在group by后面写xxx

1. 查询每个部门的最高工资
   select deptno,max(sal) from emp group by deptno;
2. 查询每个部门的平均工资
   select deptno,avg(sal) from emp group by deptno;
3. 查询每个分类下商品的最低价格
   select category_id,min(price) from t_item group by category_id;
4. 查询每个部门中工资大于1500的人数
   select deptno,count(*) from emp where sal>1500 group by deptno;
5. 查询每个领导的手下人数
   select mgr,count(*) from emp
   where mgr is not null group by mgr;
6. 查询每个商品分类的库存总量
   select category_id,sum(num) from t_item group by category_id;
7. 案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。
   select deptno,count(),sum(sal) from emp group by deptno order by count(),sum(sal) desc;
   select deptno,count(*) c,sum(sal) s from emp group by deptno order by c,s desc;
8. 案例：查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。
   select deptno,avg(sal) a,min(sal),max(sal) from emp
   where sal between 1000 and 3000
   group by deptno
   order by a;
9. 案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列
   select job,count(*) c,sum(sal),avg(sal) a,min(sal) from emp
   where mgr is not null
   group by job
   order by c desc,a;

• 如果需要使用多个字段进行分组 直接在group by 后面写多个字段名通过逗号分隔

10. 查询每个部门下每个领导的手下人数
    select deptno,mgr,count(*) from emp
    where mgr is not null group by deptno,mgr;

having

• where后面只能写普通字段的条件不能写聚合函数
• having后面可以写普通字段条件也可以写聚合函数，但是推荐在having后面只写聚合函数
• having写在group by 的后面
• select * from 表名 where … group by xxx having … order by … limit …;

2. 查询每个部门的平均工资，要求平均工资大于2000
   select deptno,avg(sal) a from emp
   group by deptno having a>2000;
3. 查询每个分类的平均单价，过滤掉平均单价低于100的。
   select category_id,avg(price) a from t_item
   group by category_id
   having a>=100;

练习：

1. 查询每个分类商品的库存总量，要求总量高于19999
   select category_id,sum(num) s from t_item group by category_id having s>19999;
2. 查询分类id为238和917的两个分类的平均单价各是多少
   select category_id,avg(price) from t_item where category_id in (238,917) group by category_id;
3. 查询emp表中每个部门的平均工资高于2000的部门编号，部门人数，平均工资 最后根据平均工资降序排序
   select deptno,count(*),avg(sal) a from emp group by deptno
   having a>2000 order by a desc;
4. 查询emp表中工资在1000-3000之间的员工，每个部门的编号，工资总和，平均工资，要求过滤掉平均工资低于2000的部门，按照工资总和降序排序
   select deptno,sum(sal) s,avg(sal) a from emp
   where sal between 1000 and 3000 group by deptno
   having a>=2000 order by s desc;
5. 查询emp表中不是以s开头每个职位的名字，人数，工资总和，最高工资，过滤掉平均工资是3000的职位，根据人数升序排序，如果一致根据工资总和降序排序
   select job,count(*) c,sum(sal) s,max(sal) from emp
   where job not like ‘s%’ group by job having avg(sal)!=3000
   order by c,s desc;
6. 查询emp表每年入职的人数
   select extract(year from hiredate) y,count(*) from emp group by y;

子查询

1. 查询emp表工资最高的员工信息
   select max(sal) from emp;
   select * from emp where sal=5000;

• 子查询
  select * from emp where sal=(select max(sal) from emp);

2. 查询emp表中工资高于平均工资的员工信息
   select * from emp where sal>(select avg(sal) from emp);
3. 查询和Jones相同工作的员工信息
   select * from emp where job=(select job from emp where ename=‘jones’) and ename!=‘jones’;
4. 查询工资最低的员工的所在部门同事信息
   -求最低工资
   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);
5. 查询最后入职的员工信息
   select max(hiredate) from emp;
   select * from emp where hiredate=(select max(hiredate) from emp);

• having 要写在 group by 后面

6. 查询king的部门编号和部门名称
   -得到部门编号
   select deptno from emp where ename=‘king’
   -通过编号得到部门名称
   select deptno,dname from dept where deptno=(select deptno from emp where ename=‘king’);
7. 查询有员工的部门信息（需要两张表）
   -得到员工表出现的部门编号
   select distinct deptno from emp;
   -通过部门编号得到部门信息
   select * from dept where deptno in (select distinct deptno from emp);
8. 查询平均工资最高的部门信息（史诗级难度！！）
   -得到每个部门的平均工资
   select deptno,avg(sal) from emp group by deptno;
   -得到最高的平均工资
   select avg(sal) a from emp group by deptno order by a desc limit 0,1;
   -通过最高的平均工资得到对应的部门编号
   select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1);
   -通过部门编号得到部门信息
   select * from dept where deptno in(select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1));

• 子查询可以写在什么位置

9. 写在where或having后面 当做查询条件的值
10. 写在创建表的时候 把查询结果保存成一张新的表
    create table emp_20 as (select * from emp where deptno=20);
11. 写在from后面 当成一个虚拟表 必须有别名
    select * from emp where deptno=20;
    select ename,sal from (select * from emp where deptno=20) newtable;

关联查询

• 同时查询多张表的数据称为关联查询

1. 查询每一个员工的姓名和对应的部门名称
   select e.ename,d.dname
   from emp e,dept d
   where e.deptno=d.deptno;
2. 查询在new york工作的员工信息
   select *
   from emp e,dept d
   where e.deptno=d.deptno
   and d.loc=‘new york’;
3. 查询商品标题和所对应的分类名称
   select i.title,c.name
   from t_item i,t_item_category c
   where i.category_id=c.id;

笛卡尔积

• 如果关联查询不写关联关系则结果为两张表的乘积，这个乘积称为笛卡尔积。
• 笛卡尔积为一种错误的查询结果，切记工作中不要出现
  ###等值连接和内连接
• 等值连接和内连接查询到的内容一样，都为两张表中有关联关系的数据(交集部分)
• 等值连接： select * from A,B where A.x=B.x and A.age=18;
• 内连接： select * from A [inner] join B on A.x=B.x where A.age=18;

1. 查询每一个员工的姓名和对应的部门名称
   select e.ename,d.dname
   from emp e join dept d
   on e.deptno=d.deptno;

外连接

• 内连接和等值连接查询到的是交集部分的数据，外连接查询到的是某一张表的全部数据+另外一张表的交集数据
• 左/右外连接： select * from A left/right join B on A.x=B.x where A.age=18;
  insert into emp (empno,ename) values(10010,‘Tom’);

1. 查询所有员工姓名和对应的部门名称
   select e.ename,d.dname
   from emp e left join dept d
   on e.deptno=d.deptno;

关联查询总结

• 关联查询的查询方式： 等值连接 内连接和外连接
• 如果想查询的数据为两张表的交集数据使用等值连接或内连接(推荐)
• 如果查询的数据是一张表的全部数据和另外一张表的交集数据则使用外连接

表设计之关联关系

一对一

• 什么是一对一关系： 有AB两张表，其中A表的一条数据对应B表的一条数据，同时B表的一条数据也对应A表中的一条数据
• 应用场景： 用户表和用户信息扩展表、商品表和商品信息扩展表
• 外键： 表中用于建立关系的字段称为外键，一张表有可能有多个外键，但只会有一个主键
• 如何建立关系：在从表中添加外键指向主表的主键
• 练习： 创建表保存以下数据 表名 user 和 userinfo
  用户名wukong 密码abcd 昵称悟空 性别男 地址花果山
  wzt admin 武则天 女 大陆
  Superman 123456 超人 男 铁岭
  create table user(id int primary key auto_increment,username varchar(10),password varchar(10));
  create table userinfo(userid int,nick varchar(10),gender varchar(5), loc varchar(20));
  insert into user values(null,‘wukong’,‘abcd’),(null,‘wzt’,‘admin’),(null,‘superman’,‘123456’);
  insert into userinfo values(1,‘悟空’,‘男’,‘花果山’),(2,‘武则天’,‘女’,‘大陆’),(3,‘超人’,‘男’,‘铁岭’);

1. 查询每个用户名对应的昵称
   select u.username,ui.nick
   from user u join userinfo ui
   on u.id=ui.userid;
2. 查询超人的用户名
   select u.username
   from user u join userinfo ui
   on u.id=ui.userid where ui.nick=‘超人’;
3. 查询性别是男的用户名和密码
   select u.username,u.password
   from user u join userinfo ui
   on u.id=ui.userid where ui.gender=‘男’;
4. 查询是否存在 用户名：wukong 密码：abc的用户 (查询符合条件的数据条数)
   select count(*) from user where username=‘wukong’ and password=‘abc’;

一对多

• 什么是一对多：有AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的一条
• 应用场景： 用户表和部门表，商品表和分类表
• 如何建立关系：在多的一端添加外键指向另外一张表的主键
• 练习： 创建emp表和dept表
  create table emp(id int primary key auto_increment,name varchar(10),deptid int);
  create table dept(id int primary key auto_increment,name varchar(10));
  insert into dept values(null,‘神仙’),(null,‘妖怪’);
  insert into emp values(null,‘猪八戒’,1),(null,‘白骨精’,2),(null,‘蜘蛛精’,2);

1. 查询每个员工的姓名和对应的部门名称
   select e.name,d.name
   from emp e join dept d
   on e.deptid=d.id;
2. 查询妖怪部的员工姓名
   select e.name
   from emp e join dept d
   on e.deptid=d.id
   where d.name=‘妖怪’;

多对多

• 什么是多对多：有AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的多条
• 应用场景： 老师表和学生表
• 如何建立关系：通过第三张关系表保存两张主表的关系
• 练习：
  创建老师表，学生表和关系表
  create table teacher(id int primary key auto_increment,name varchar(10));
  create table student(id int primary key auto_increment,name varchar(10));
  create table t_s(tid int,sid int);

1. 往以上表中保存苍老师的学生小刘和小丽，传奇老师的学生小刘，小王和小丽
   insert into teacher values(null,‘苍老师’),(null,‘传奇老师’);
   insert into student values(null,‘小刘’),(null,‘小王’),(null,‘小丽’);
   insert into t_s values(1,1),(1,3),(2,1),(2,2),(2,3);
2. 查询每个学生姓名和对应的老师姓名
   select s.name,t.name
   from student s join t_s ts
   on s.id=ts.sid
   join teacher t
   on t.id=ts.tid;
3. 查询苍老师的学生都有谁
   select s.name
   from student s join t_s ts
   on s.id=ts.sid
   join teacher t
   on t.id=ts.tid where t.name=‘苍老师’;
4. 查询小丽的老师是谁
   select t.name
   from student s join t_s ts
   on s.id=ts.sid
   join teacher t
   on t.id=ts.tid where s.name=‘小丽’;

自关联

• 在当前表中添加外键外键的值指向当前表的主键，这种关联方式称为自关联
  create table person(id int primary key auto_increment,name varchar(10),mgr int);
  保存以下数据： 如来->唐僧->悟空->猴崽子
  insert into person values(null,‘如来’,null),(null,‘唐僧’,1),(null,‘悟空’,2),(null,‘猴崽子’,3);

1. 查询每个人的名字和上级的名字
   select p.name,m.name 上级
   from person p left join person m
   on p.mgr=m.id;

表设计案例：权限管理

• 实现权限管理功能需要准备三张主表和两张关系表
• 创建表：
  create table user (id int,name varchar(10));
  create table role (id int,name varchar(10));
  create table module (id int,name varchar(10));
  create table u_r (uid int, rid int);
  create table r_m (rid int, mid int);
• 插入数据：
  insert into user values(1,‘刘德华’),(2,‘凤姐’);
  insert into role values(1,‘男游客’),(2,‘男会员’),(3,‘女游客’),(4,‘女管理员’);
  insert into module values(1,‘男浏览’),(2,‘男发帖’),(3,‘女浏览’),(4,‘女发帖’),(5,‘女删帖’);
  -保存角色和权限的关系：
  insert into r_m values(1,1),(2,1),(2,2),(3,3),(4,3),(4,4),(4,5);
  -保存用户和角色的关系 刘德华男会员和女游客 凤姐：女管理员和男游客
  insert into u_r values(1,2),(1,3),(2,1),(2,4);

1. 查询每个用户的权限有哪些
   select u.name,m.name
   from user u join u_r ur
   on u.id=ur.uid
   join r_m rm
   on rm.rid=ur.rid
   join module m
   on m.id=rm.mid;
2. 查询凤姐的权限
   select u.name,m.name
   from user u join u_r ur
   on u.id=ur.uid
   join r_m rm
   on rm.rid=ur.rid
   join module m
   on m.id=rm.mid where u.name=‘凤姐’;
3. 查询拥有男浏览权限的用户有谁
   select u.name,m.name
   from user u join u_r ur
   on u.id=ur.uid
   join r_m rm
   on rm.rid=ur.rid
   join module m
   on m.id=rm.mid where m.name=‘男浏览’;