Mysql学习笔记–day02\03\04

网络编程学习汇总

1.分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
求和：sum
平均值：avg
最大值：max
最小值：min
计算个数:count
1.简单的使用

select sum(salary) from employees;
select avg(salary) from employees;
select round(min(salary),2) from employees;
select max(salary) from employees;
select count(salary) from employees;

2.参数支持哪些类型
1)sum、avg一般用于处理数值类型
2)max、min、count可以用于处理任何类型
3)都忽略null值
4)可以和distinct搭配使用
3.count函数的详细介绍

select count(salary) from employees;
select count(*) from employees;//一般使用这个统计行数
select count(1) from employees;//统计行数

4.和分组函数一同查询的字段要求是group by后的字段
5.查询员工表中的最大入职时间和最小入职时间的相差天数(DIFFRENCE)

select DATEDIFF(max(hiredate),min(hiredate)) DIFFRENCE
from employees

2.分组查询

select 分组函数，列(要求出现在group by的后面)
from 表
where 筛选条件
group by 分组的列表
order by 子句
having
1.筛选条件说明：
分组前筛选：原始表中的数据，where
分组后筛选:分组后的结果集，having
1)分组函数做条件肯定是放在having子句中
2)能用分组前筛选的，就优先考虑使用分组前筛选
2.group by子句支持单个字段分组，多个字段分组(多个字段之间用逗号隔开没有顺序要求)，表达式或者函数
1.查询每个工种的最高工资

select max(salary),job_id
from employees
group by job_id;

2.查询邮箱中包含a字符的每个部门的平均工资

select department_id,AVG(salary)
from employees
where email like "%a%"
GROUP BY department_id

3.添加分组后的筛选

1.查询哪个部门的员工个数>2

select a.id
from (select count(*) num,department_id id
from employees
GROUP BY department_id) a
where a.num>2;

或者

select count(*) num,department_id id
from employees
GROUP BY department_id
having count(*)>2;

2.查询每个工种有奖金的员工的最高工资>12000的工种的编号和最高工资

select max(salary),job_id
from employees
where employees.commission_pct is not null
GROUP BY job_id
HAVING MAX(salary)>12000;

3.查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资

SELECT min(salary),manager_id
from employees
where employees.manager_id>'102'
GROUP BY manager_id
having min(salary)>5000

5.按员工姓名的长度分组，查询每一组的员工的个数，筛选员工个数>5的有哪些？

select length(last_name),count(*)
from employees
GROUP BY length(last_name)
HAVING count(*)>5

4.按多个字段分组

1.查询每个部门每个工种的员工的平均工资

SELECT department_id,job_id,AVG(salary)
from employees
GROUP BY department_id,job_id
# order by AVG(salary) desc;

5.连接查询

又称为多表查询，当查询的字段来自与多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
1.分类
sql92标准：仅仅支持内连接
sql99标准[推荐]：支持内连接+外连接(左外和右外)+交叉连接
按功能分类：
内连接：
等值连接
非等值连接
自链接
也支持一部分外连接(用于oracle、sqlserver、mysql不支持)
外连接：
左外连接
右外连接
全外连接
交叉连接

6.slq92标准

1.等值连接
查询女神名和对应的男神名

select name,boyname
from boys,beauty
where beauty.boyfriend_id=boys.id;

1)查询每个城市的部门个数

select count(*) 个数,city
from departments d,locations l
where d.location_id=l.location_id
GROUP BY city;

2)查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

select d.department_name,d.manager_id,min(salary)
from departments d,employees e
where d.department_id=e.department_id
and commission_pct is not NULL
GROUP BY department_name,d.manager_id

说明：

1)夺标等值连接的结果为多表的交集部分
2)n表连接，至少需要n-1个连接条件
3)多表的顺序没有要求
4)一般需要为表取别名

2.非等值连接
1)查询员工的工资和工资等级

select salary,grade_level
from employees e,job_grades g
where salary between g.lowest_sal and g.highest_sal

3.自连接
1.查询员工名和上级的名称

select e1.last_name as 员工,e2.last_name as 领导
from employees e1,employees e2
where e1.employee_id=e2.manager_id

练习：
查询员工表的job_id中包含a和e的，并且a在e的前面

select job_id from employees where job_id like "%a%e%"

4.password函数，MD5函数

select password('王世宇');

5.查询每个国家下的部门个数大于2的国家编号

select country_id,count(*) 部门个数
from departments d,locations l
where d.location_id=l.location_id
GROUP BY country_id
HAVING count(*)>2

7.sql99语法

select 查询列表
from 表1 别名 [连接类型] join 表2 别名 
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序列表]
内连接：[inner] #筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
    #inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

外连接：
左外：left [outer]
右外：right [outer]
全外：full [outer] #内连接+表1中有但表2没有的+表2中有但表1没有的数据
交叉连接：cross
1.内连接
1)等值连接
查询员工名、部门名

select last_name,department_name
from employees e inner join department d 
on e.department_id=d.department_id

查询员工名、部门名、工种名，并按部门名降序

select last_name,department_name,job_title
from employees e
INNER JOIN departments d
on e.department_id=e.department_id
INNER JOIN jobs j
on j.job_id=e.job_id
order by department_name desc

2)非等值连接
查询员工的工资级别

select salary,grade_level
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.highest_sal

查询员工的名字、上级的名字

select e.last_name,m.last_name
from employees e
join employees m
on e.manager_id=m.employee_id

2.外连接
特点：
1).外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表中没有的记录
2).左外连接，left join左边的是主表
右外连接，right join右边的是主表
3).左外和右外交换两个表的顺序，可以实现同样的效果
查询男朋友不在男神表的女神名

select b.name,bo.*
from beauty b
left outer join boys bo
on b.boyfriend_id=bo.id
where bo.id is null

查询哪个部门没有员工

select d.*,e.employee_id
from departments d
left OUTER JOIN employees e 
on d.department_id=e.department_id
where e.employee_id is null

或者

select d.*,e.employee_id
from employees e
left OUTER JOIN departments d
on d.department_id=e.department_id
where e.employee_id is null

3.交叉连接#笛卡尔乘积

select b.*,bo.*
from beauty b
cross join boys bo;

8.查询部门名为SAL或IT的员工信息

select e.*,d.department_name
from departments d
LEFT JOIN employees e
on d.department_id=e.department_id
where d.department_name in('SAL','IT')

9.子查询

1.出现在其他语句中的select语句，称为子查询或内查询
2.外部的查询语句，称为主查询或外查询
3.标量子查询:结果集只有一行一列
4.列子查询：结果集只有一列多行
5.行子查询：结果集有一行多列
6.表子查询：结果集一般为多行多列
分类：
按子查询出现的位置：
select后面：
仅仅支持标量子查询
where或having后面：
标量子查询(单行)
列子查询(多行)
行子查询
exists后面(相关子查询)
表子查询
1)where或having后面
标量子查询(单行)
列子查询(多行)
行子查询
特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用>

< >= <= = <>
列子查询，一般搭配着多行操作符使用
in、not in、any/some、all

④子查询的执行优先于主查询的执行，主查询的条件用到了子查询的结果

10.谁的工资比Abel高？

select *
from employees
where salary>(
select salary
from employees
where last_name='Abel'
)

11.返回job_id与141号员工相同，salary比143号员工多的员工姓名、job_id和工资

select e.last_name,e.job_id,e.salary
from employees e
where e.job_id =(
 select e2.job_id
 from employees e2
 where e2.employee_id='141'
)and e.salary>(
 select e3.salary
 from employees e3
 where e3.employee_id='143'
);

12.返回公司工资最少的员工的last_name,job_id和salary

select last_name,job_id,salary
from employees
where salary=(
 select min(salary)
 from employees
)

13.查询最低工资大于50号部门最低工资的部门id和其最低工资

select department_id,min(salary)
from employees
GROUP BY department_id
HAVING min(salary)>(
 select min(salary)
 from employees
 where department_id='50'
)

14.返回location_id是1400或1700的部门编号

select last_name
from employees
where department_id in(
select distinct department_id
from departments
where location_id in(1400,1700)
)

15.返回其它部门中比job_id为’IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary

SELECT last_name,job_id,salary
FROM employees
where salary<ALL(
select DISTINCT salary
from employees
where job_id='IT_PROG'
)and job_id<>'IT_PROG'

或者

SELECT last_name,job_id,salary
FROM employees
where salary<(
select min(salary)
from employees
where job_id='IT_PROG'
)and job_id<>'IT_PROG'

16.查询员工编号最小且工资最高的员工信息

不一定会有查询结果

select *
from employees
where (employee_id,salary)=(
 select min(employee_id),max(salary)
 from employees
)

17.查询每个部门的员工个数

select d.*,(
 select count(*)
 FROM employees e
 where e.department_id=d.department_id
)
from departments d;

18.查询每个部门的平均工资的工资等级

SELECT ag_dep.*,g.grade_level
from(
 select avg(d.salary) ag,d.department_id
 from employees d
 GROUP BY d.department_id
) ag_dep
INNER JOIN job_grades g
on ag_dep.ag BETWEEN lowest_sal and highest_sal

19.exists后面(相关子查询)

select exists(select employee_id from employees)# 结果1或0

20.查询有员工的部门

select department_name
from departments d
where exists(
 select *
 from employees e
 where d.department_id=e.department_id
)

或者

select department_name
from department d
where d.department_id in(
 select department_id
 from employees
)

20.查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资

select  e.department_id,e.last_name,e.salary
from employees e
INNER JOIN(
SELECT AVG(salary),department_id
from employees e2
GROUP BY department_id
) e3 on e.department_id=e3.department_id

21.分页查询

格式：
select 查询列表
from 表
[join type] join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段]
limit offset,size;
说明：
1)offset:要显示条目的起始索引(起始索引从0开始)
2)size:要显示的条目个数
3)limit语句放在查询语句的最后
4)要显示的页数page，每页的条目数size
select 查询列表
from 表
limit (page-1)*size,size;
例子：
1)有奖金的员工信息，并且工资较高的前10名显示出来

select *
from employees
where commission_pct is not null
order by salary desc
limit 10;

2)查询多有学员的邮箱的用户名

select substr(email,1,instr(email,'@'-1)) 用户名
from stuinfo;

3)查询哪个年级的学生最小年龄>20岁

select min(age),gradeid
from stuinfo
group by gradeid
having min(age)>20

4)试说出查询语句中涉及到的所有的关键字，以及执行先后顺序
select 查询列表 ⑥
from 表 ①
连接类型 join 表2 ②
on 连接条件 ③
where 筛选条件 ④
group by 分组列表 ⑤
having 分组后的筛选 ⑦
order by 排序列表 ⑧
limit 偏移，条目数 ⑨

22.查询平均工资最低的部门信息

select d.*
from departments d
where department_id 
in(
 select d2.department_id
 from(
  select avg(salary),e.department_id
  from employees e
  group by department_id
  order by avg(salary) asc
  limit 1
 ) d2
);

或者

select d.*
from departments d
where department_id =(
 select department_id
 from employees
 group by department_id
 order by avg(salary)
 limit 1
)

23.查询平均工资最低的部门信息和该部门的平均工资

select d.*,d2.avg2
from departments d
INNER JOIN (
 select department_id,AVG(salary) avg2
 from employees
 group by department_id
 order by avg(salary)
 limit 1
) d2
on d.department_id=d2.department_id

24.查询平均工资高于公司平均工资的部门有哪些？

select AVG(salary),department_id
from employees
GROUP BY department_id
having avg(salary)>(
 select avg(salary)
 from employees
)

25.各个部门中最高工资中最低的那个部门的最低工资是多少？

select min(salary),department_id
from employees
where department_id =(
SELECT department_id
from employees
GROUP BY department_id
ORDER BY MAX(salary)
limit 1
)
GROUP BY department_id

26.联合查询

union 联合 合并：将多条查询语句的结果合并成一个结果
语法：
查询语句1
union
查询语句2
union
…
应用场景：
要查询的结果来自多个表，且多个表没有直接的连接关系时
特点：
1.要求多条查询语句的查询列数是一致的！
2.要求多条查询语句的查询的每一列的类型和顺序最好一致
3.union关键字默认去重，如果使用union all可以包含重复项

select a,b from t1
union 
select c,d from t2

27.DML语言(数据操作语言)

插入：insert
修改：update
删除：delete

28.插入语法

方式一：insert into 表名(列名,…) values(值1,…);

#支持多行
#支持子查询
insert into beauty(id,name,phone)
select id,boyname,'1234567'
from boys where id<3;

方式二：insert into 表名 set 列名=值，列名=值

#不支持多行
#不支持子查询
#1.插入的值的类型要与列的类型一致或兼容
insert into beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
values(13,'唐艺昕','女','1990-4-23','189888888888888',null,2),
(14,'唐艺昕','女','1990-4-23','189888888888888',null,2);
#2.列的顺序可以颠倒
#3.可以为null的列可以省略
#4.列数的值的个数必须一致
#5.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致

29.修改表单的记录

update 表名
set 列=新值,列=新值,…
where 筛选条件
1.修改beauty表中姓唐的女神的电话13899888899

use girls;
update beauty 
set phone='13899888899'
where name like '唐%'

30.修改多表的记录

sql92语法：
update 表1 别名,表2 别名
set 列=值,…
where 连接条件
and 筛选条件
sql99语法：
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,…
where 筛选条件;
案例1：修改张无忌的女朋友的手机号114

update boys bo
inner join beauty b on bo.id=b.boyfriend_id
set b.phone='114'
where bo.boyname='张无忌'

31.删除语句

1.单表的删除

delete from 表名 where 筛选条件

2.多表的删除
sql92语法：
delete 表1的别名[,表2的别名]
from 表1 别名，表2 别名
where 连接条件
and 筛选条件
sql99语法：
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件；
删除表的所有数据：truncate table 表名；

32.删除张无忌的女朋友的信息

delete b
from beauty b
inner join boys bo on b.boyfriend_id=bo.id
where bo.boyname='张无忌'

33.delete和truncate的区别

delete from b
1.delete可以加where条件，truncate不能加
2.truncate删除，效率高一丢丢
3.假如要删除的表中有自增长列，
如果使用delete删除后，再插入数据，自增长列的值从断点开始
而truncate删除后，再插入数据，自增长列的值从1开始
4.truncate删除没有返回值，delete删除有返回值
5.truncate删除不能回滚，delete删除可以回滚

34.插入样例

insert into my_employees
select 1,'patel','Ralph','Rpatel',895;

35.将3号员工的last_name修改为"drelxer"

update my_employees set last_name='drelxer' where id=3;

36.将userid为Bbiri的user表和my_employees表的记录全部删除

delete u,e
from users u
join my_employees e on u.userid=e.userid
where u.userid='Bbiri'

37.清空表my_employees

truncate table my_employees

38.数据定义语言

库和表的管理
创建：create
修改：alter
删除：drop

39.创建库 create database 库名

create database if not exists books;

更改库的字符集

alert database books character set gbk;

库的删除

drop database books;

40.表的管理

create table 表名(
列名 列的类型[(长度)约束],
列名 列的类型[(长度)约束],
列名 列的类型[(长度)约束],
…
列名 列的类型[(长度)约束]
)
创建表book

use books;
create table if not exists BOOK(
 id int,
 bName VARCHAR(20),
 price double,
 authorId int,
 publishDate DATETIME
)

表的修改
①修改列名

alter table book change column publishdate pubDate datetime;

②修改列的类型

alter table book modify column pubdate timestamp;

③添加新列

alter table author add column annual double;

④删除列

alter table author drop COLUMN annual;

⑤修改表名

alter table author rename to book_author;

41.查看当前库的所有表

drop table if exists book_author;
show tables;

42.复制表的结构

CREATE table copy like book;

43.复制表的结构加数据

create table copy2
select * from author;

只复制部分数据

create table copy3
select id,au_name
from author
where nation='中国';

44.仅仅复制某些字段

create table copy4
select id,au_name
from author
where 1=2;

45.将表depars中的数据插入新表dept2中

create table dept2
select department_id,department_name
from myemployees.departments;

46.常见的数据类型

1.数值类型：
整型
小数
定点数
浮点数
2.字符型
较短的文本：char、varchar
较长的文本：text、blob(较长的二进制数据)
3.日期类型

47.整型

tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8
特点：
①如果不设置是否有符号，默认是有符号的，如果想设置无符号，需要添加unsigned关键字
②如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值
③如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但是必须搭配zerofill使用
1.如何设置无符号和有符号

drop table if exists tab_int
create table tab_int(
 t1 int,
 t2 int unsigned#定义无符号
)

48.小数

浮点整数
float(m,d)、double(m,d)
4 8
定点整数型(字节M+2)
dec(m,d)
decimal(m,d)
特点：
①
m:整数部位+小数部位
d:小数部位
如果超出范围，则插入临界值
②
M和D可以省略
如果是decimal，则M默认为10，D默认为0
如果是float和double,则会根据插入的数值的精度来决定精度
③定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用
④选取类型原则
所选择的类型越简单越好，能保存数值的类型越小越好