mysql1

数据库介绍

按照一定的数据结构来组织,管理和存储数据的仓库

用仓库的方式来管理数据的.一个仓库下会有很多表.一个表由行和列构成.

数据的分类

1. 关系型数据库

   表格型数据库 - 有行和列来构成一张表,表中存放数据

   常见的产品:MS-sqlserver,主流的免费的Oracle-mysql(中小型的应用),收费的Oracle-oracle(大型的应用)

2. nosql - 不仅仅是sql - 非关系型数据库

   • 键值对数据库 - redis
   • 文档型数据库 - mongodb
   • 列式数据库 - hbase
   • 图形数据库

数据库安装注意点

1. server和client

   当我们安装好db之后,自带安装好了mysql-server端和mysql-client端

   db正常使用,一定要保障mysql-server端处于运行状态

   • 右击计算机 - 管理 - 服务 - 服务和应用程序 - 👉🏻右边窗口 - Mysql - 启动方式设置开启自启.

   • 可以通过终端输入命令来启动mysql-server端

     net start mysql
     net stop mysql

2. mysql-client客户端
   • 第三方的收费navicat
   • idea
   • java代码
   • 自带的控制台

专业术语

1. DB - DataBase 数据库

2. DBA - 数据库管理员(侧重于运维),DE - 数据库研发工程师

3. DBMS - 数据库管理系统,安装在操作系统.用户通过dbms来操作db,dba通过dbms来管理db,保证db在7*24h高效运转

   人 - dbms - db(os)

4. RDBMS - 关系型数据库管理系统

5. DBS - 数据库系统,大的范畴,dbs = dba+db+dbms

连接数据库

前提:mysql安装并且配置好了环境变量

mysql -u用户名 -p密码

mysql -uroot -proot

修改密码

alter user '用户名'@'db所在主机ip' identified by '新的密码';

use mysql

alter user 'root'@'localhost' identified by 'root';

-- 退出重新登录
exit;

导入脚本

-- 1. 创建数据库  create dabatase 数据库名
create database dy;

-- 查看所有的db
show databases;

-- 2. 使用数据库
use dy

-- 3. 导入脚本 - .sql为后缀的sql文件
-- 3-1. 在dy数据库中生成5张表
-- 3-2. 每张表中模拟一些数据
source 脚本文件的绝对路径;

-- 4. 查看该库下所有的表
show tables;

+--------------+
| Tables_in_dy |
+--------------+
| s_customer   |
| s_dept       |
| s_emp        |
| s_ord        |
| s_region     |
+--------------+

--5. 查看表结构
desc s_emp

设置数据库编码

1. 查看编码

   show variables like 'character_set%';
   
   +--------------------------+-----------------------------------------------------------+
   | Variable_name            | Value                                                     |
   +--------------------------+-----------------------------------------------------------+
   | character_set_client     | utf8                                                      |
   | character_set_connection | utf8                                                      |
   | character_set_database   | utf8                                                      |
   | character_set_filesystem | binary                                                    |
   | character_set_results    | utf8                                                      |
   | character_set_server     | utf8                                                      |
   | character_set_system     | utf8                                                      |
   | character_sets_dir       | /usr/local/mysql-5.7.28-macos10.14-x86_64/share/charsets/ |
   +--------------------------+-----------------------------------------------------------+

2. 修改编码

   2-1. 临时修改 - set命令 - 仅仅是对当前会话(一次连接,窗口)有效

   set names utf8;

   2-2. 永久生效 - 找到my.ini文件

   [client]
   # 设置mysql客户端默认字符集
   default-character-set=utf8
   [mysqld]
   character_set_server=utf8

   需要重启mysql-server(mysql服务器端),才能生效.

   1. 通过gui方式

   2. 通过命令

      net stop mysql
      net start mysql

SQL介绍

SQL 是 Structured Query Language 的缩写，中文译为“结构化查询语言”。SQL 是一种计算机语言，用来存储、检索和修改关系型数据库中存储的数据。

①mysql-client输入合法的sql语句,如果sql非法,直接报错

②输入回车- 客户端将sql语句发送到mysql-server端,编译和解析sql语句的.

③mysql-server将执行完sql语句的结果返回到mysql-client进行展示.

按照功能SQL分类

1. DDL(Data Definition Language) - 数据定义语言

   create drop alter truncate rename comment(注释)

2. DML(Data Manipulation Language) - 数据操纵语言

   update delete insert

3. DQL(Data Query Language) - 数据查询语言

   select

4. TCL/DTL(Transaction Control Language):事务控制语言

   commit savepoint rollback

简单查询

1. 查询的本质

   • 到哪里查
   • 查什么

2. 语法 - 关键字是大小写不敏感

   SELECT 列名1,列名2,..列n
   FROM 表名;

列取别名

1. 查询员工的名字,薪水以及职称.

   select first_name,salary,title from s_emp;

   方式一

   SELECT 列名1 列别名,列名2 列别名,..列n
   FROM 表名;
   
   select first_name 名字,salary 薪水,title 职称 from s_emp;

   方式二

   SELECT 列名1 as 列别名,列名2 as 列别名,..列n
   FROM 表名;
   
   select first_name as 名字,salary as 薪水,title as 职称 from s_emp;

   方式三

   列别名中间有空格,需要使用双引号来包裹格式
   select first_name "名 字",salary "薪 水",title as "职 称" from s_emp;

2. 查询所有列*

   -- 格式化输出
   select * from s_emp;
      
   select * from s_emp \G;

去重关键字

distinct

1. 查询员工表中所有的职称的信息

   select distinct title from s_emp;

列的计算

列是允许计算的

1. 查询员工的名字,年薪(不带提成commission_pct)

   select first_name,salary*12 年薪 from s_emp;

2. 查询员工的名字,年薪(带提成commission_pct)

   select first_name,salary*(1+commission_pct/100)*12 年薪 from s_emp;
      
   查询出来只有5个人有年薪,原因是因为只有5个人有提成.其他人的提成都是null值
   mysql - null值计算的结果还是一个null值
      
   使用到mysql中提供的空值置换函数
   coalesce(m,n) - 如果m为null,返回n,否则返回m
      
   select first_name,salary*(1+coalesce(commission_pct,0)/100)*12 年薪 from s_emp;

练习

1. 找出员工的姓名,工资以及入职日期

   select last_name,first_name,salary,start_date from s_emp;
   
   -- 列的拼接 concat(m,n,a,b,c,..)
   -- mysql中的字符串统一使用单引号
   select concat(last_name,'  ',first_name) 姓名,salary,start_date from s_emp;

2. 找出所有的客户名以及他的联系方式

   select name,phone from s_customer;
   
   -- 查询姓名的时候
   -- 蔡根花 - 蔡**

3. 找出每个订单的费用,支付方式以及出货日期

   select total,payment_type,date_ordered from s_ord;

条件查询

SELECT 列名1,列名2,..列n
FROM 表名
WHERE 条件表达式;

运算符

比较运算符

> < >= <= != <> =

其他运算符

1. between … and .. 在…到…之间
2. in(集合) - 在某个集合内
3. not in(集合) - 不在某个集合内
4. is - 判断是否为null,判断null值不能用=
5. is not - 判断是否不为null

逻辑连接符

1. and 条件同时成立,返回true
2. or 有一个条件成立,就返回true

练习

1. 查询部门编号(dept_id)为41,42,45的员工找出来

   select first_name,dept_id from s_emp
   where dept_id=41 or dept_id=42 or dept_id=45;
   
   select first_name,dept_id from s_emp where dept_id in(41,42,45);

2. 查询没有提成的员工

   select first_name,commission_pct from s_emp where commission_pct is null;

3. 找出工资在[1200,1500]之间的员工

   select first_name,salary from s_emp where salary between 1200 and 1500;
   
   -- 更加灵活
   select first_name,salary from s_emp where salary>=1200 and salary<=1500;

4. 查找工资大于1500并且没有提成的员工

   select first_name,salary from s_emp where salary>1500 and commission_pct is null;

5. 查找年薪(包括提成)超过15000的员工

   select first_name,salary*(1+coalesce(commission_pct,0)/100)*12 _year from s_emp
   where salary*(1+coalesce(commission_pct,0)/100)*12>15000;
   
   -- 给列取别名的意义
   -- 子查询 - 查询的结果作为虚拟表 - 子查询一定要放在()中
   select * from X where _year>15000;
   
   查询的结果来替代X,子查询的结果作为表的话 - 需要给表取别名
   select * from (select first_name,salary*(1+coalesce(commission_pct,0)/100)*12 _year from s_emp) core_ where _year>15000;

6. 找出除了41部门的员工

   select first_name,dept_id from s_emp where dept_id <> 41;
   
   select first_name,dept_id from s_emp where dept_id not in(41);

7. 找出亚洲区域的信息

   -- mysql中采用单引号
   select * from s_region where name='Asia';

模糊查询

大小写不敏感的

1. 关键字like
2. 占位符
   • % 代表的任意多个任意字符
   • _ 代表的是任意单个字符

转义字符

• first_name名称正好包含了%或者_

  update s_emp set first_name='Mar%k' where id=4;

• 查询first_name包含%的员工

  -- 定义转义字符,默认的是\
  select first_name from s_emp where first_name like '%\%%';
    
  -- 自定义转义字符,用escape 新的转义字符
  select first_name from s_emp where first_name like '%?%%' escape '?';

练习

1. 查询first_name包含m的

   select first_name from s_emp where first_name like '%m%';

2. 查询以C开头的员工

   select first_name from s_emp where first_name like 'C%';

3. 查询第三个字母是’e’的员工

   select first_name from s_emp where first_name like '__e%';

4. 查询入职时间是6月份的员工 - 模糊查询 - 不是正规的

   select first_name,start_date from s_emp where start_date like '%-06-%';

排序

SELECT 列名1,列名2,..列n
FROM 表名
WHERE 条件表达式
ORDER By语句 [asc|desc]

asc可以省略不写 - 升序
desc必须要写 - 降序

1. 根据年薪降序排列

   方式一: order by 列名

   select first_name,salary*(1+coalesce(commission_pct,0)/100)*12 from s_emp 
   order by salary*(1+coalesce(commission_pct,0)/100)*12 desc;

   方式二: order by 列别名

   select first_name,salary*(1+coalesce(commission_pct,0)/100)*12 年薪 from s_emp 
   order by 年薪 desc;

   方式三: order by 列的序号 列的序号从1开始 => 推荐方式

   select first_name,salary*(1+coalesce(commission_pct,0)/100)*12 年薪 from s_emp 
   order by 2 desc;

2. 根据年薪降序排.如果年薪一样,则继续按照first_name升序排列

   select first_name,salary*(1+coalesce(commission_pct,0)/100)*12 年薪 from s_emp 
   order by 2 desc,1;

转换函数

数字和字符串

1. 字符串转换成数字

   select '1'+0;
   
   mysql中字符数字遇到数字进行+,自动将字符转换成数字来进行计算.

2. 数字转换成字符串 - concat函数 - 拼接删除

   select concat(1,2);  // 12

字符串和日期

关于日期模板

(在format字符串中可用标志符:  
　%m 月名字(january……december)    
　%w 星期名字(sunday……saturday)    
　%d 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）    
　%Y 年, 数字, 4 位    
　%y 年, 数字, 2 位    
　%a 缩写的星期名字(sun……sat)    
　%d 月份中的天数, 数字(00……31)    
　%e 月份中的天数, 数字(0……31)    
　%m 月, 数字(01……12)    
　%c 月, 数字(1……12)    
　%b 缩写的月份名字(jan……dec)    
　%j 一年中的天数(001……366)    
　%H 24时制小时(00……23)    
　%k 小时(0……23)    
　%h 12时小时(01……12)    
　%i 小时(01……12)    
　%l 小时(1……12)    
　%i 分钟, 数字(00……59)    
　%r 时间,12 小时(hh:mm:ss [ap]m)    
　%t 时间,24 小时(hh:mm:ss)    
　%s 秒(00……59)       
　%p am或pm    
　%w 一个星期中的天数(0=sunday ……6=saturday ）  

1. 获取当前系统的日期

   select now();
   
   +---------------------+
   | now()               |
   +---------------------+
   | 2022-08-05 11:09:49 |
   +---------------------+

2. 日期转换成指定格式的字符串 - date_format(日期,模板)

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

3. 字符串转成日期 - str_to_date(字符串,模板) - 字符串格式要和模板的格式匹配

   -- 删除表
   drop table tt;
   -- 创建表
   create table tt(
   	id int(7) primary key,
     name varchar(20),
     birthday date
   );
      
   -- date - 年月日
   -- 插入数据
   -- mysql中默认支持的格式 - 会将字符串自动转成date类型
   insert into tt values(1,'admin','2022-09-01');//ok
   insert into tt values(2,'tom','2022/09/01');//ok
   insert into tt values(3,'jack','20221001');//ok
      
   -- 不支持的时候
   insert into tt values(4,'jack','aa');
   ERROR 1292 (22007): Incorrect date value: 'aa' for column 'birthday' at row 1
      
   insert into tt values(4,'jack','01-05-2022');
   ERROR 1292 (22007): Incorrect date value: '01-05-2022' for column 'birthday' at row 1
      
   -- 非要使用'01-05-2022'字符串格式的,才需要使用str_to_date函数进行转换
   insert into tt values(4,'jack',str_to_date('01-05-2022','%d-%m-%Y'));

   导入脚本的时候 - 失败场景 - 字符串日期格式不支持

单行函数

• select 函数(参数);

• 函数是可以应用在查询列上的.

• 函数是允许嵌套使用的.

1. 字符串函数

1. instr(str,substr) - 返回字符串substr在字符串str第一次出现的位置(str不包含substr时返回0)

2. lpad(str,len,padstr) - 用字符串padstr填补str左端直到字串长度为len并返回

   select lpad('ab',5,'cd');//cdcab

3. rpad(str,len,padstr) - 用字符串padstr填补str右端直到字串长度为len并返回

   select rpad('ab',5,'cd');//abcdc   5代表的是总的长度

4. left(str,len) - 返回字符串str的左端len个字符

5. right(str,len) - 返回字符串str的右端len个字符

6. substring(str,pos,len) - 返回字符串str的位置pos起len个字符

7. substring(str,pos) - 返回字符串str的位置pos起后面的子串

   序号从1开始
   select substring('mysql',2);  //ysql

8. ltrim(str) - 返回删除了左空格的字符串str

9. rtrim(str) - 返回删除了右空格的字符串str

10. space(n) - 返回由n个空格字符组成的一个字符串

11. replace(str,from_str,to_str) - 用字符串to_str替换字符串str中的子串from_str并返回

    select replace('i love java','java','mysql');

12. reverse(str) - 颠倒字符串str的字符顺序并返回

13. insert(str,pos,len,newstr) - 把字符串str由位置pos起len个字符长的子串替换为字符串

    select insert('xxxxx',1,2,'**');

14. lower(str) - 返回小写的字符串str

15. upper(str) - 返回大写的字符串str

16. char_length(str) - 不管汉字还是数字或者是字母都算是一个字符。

17. length(str);//汉字占3个,其他占1个.

练习

1. 配合表 - 将first_name全部转换成大写

   select first_name,upper(first_name) from s_emp;

2. first_name - 模拟银行账户显示,只显示首字母,其余每个字母用*表示

   -- substring和rpad
   select first_name,rpad(substring(first_name,1,1),char_length(first_name),'*') from s_emp;
   
   -- concat + substring + rpad
   select first_name,concat(substring(first_name,1,1),rpad('*',char_length(first_name)-1,'*')) from s_emp;
   
   -- replace + substring + rpad
   select first_name,replace(first_name,substring(first_name,2),rpad('*',char_length(first_name)-1,'*'))
   from s_emp;
   
   -- insert + rpad
   select first_name,insert(first_name,2,char_length(first_name)-1,rpad('*',char_length(first_name)-1,'*'))
   from s_emp;

2. 数字函数

1. abs(n) - 求绝对值
2. mod(n,m) - 取模运算,返回n被m除的余数(同%操作符)
3. floor(n) - 返回不大于n的最大整数值 - 向下取整
4. ceiling(n) - 返回不小于n的最小整数值 - 向上取整
5. round(n[,d]) - 返回n的四舍五入值,保留d位小数(d的默认值为0)
6. pow(x,y) - 返回值x的y次幂
7. sqrt(n) - 返回非负数n的平方根
8. pi() - 返回圆周率
9. rand() - 返回在范围[0到1.0)内的随机浮点值
10. truncate(n,d) - 保留数字n的d位小数并返回 - 直接截取

3. 日期函数

查询当前系统的日期 - select now();

1. dayofweek(date) - 返回日期date是星期几(1=星期天,2=星期一,……7=星期六,odbc标准)

2. weekday(date) - 返回日期date是星期几(0=星期一,1=星期二,……6= 星期天)

3. year(date) - 返回date的年份(范围在1000到9999)

4. month(date) - 返回date中的月份数值

5. dayofmonth(date) - 返回date是一月中的第几日(在1到31范围内)

6. hour(time) - 返回time的小时数(范围是0到23)

7. minute(time) - 返回time的分钟数(范围是0到59)

8. second(time) - 返回time的秒数(范围是0到59)

9. period_add(p,n) - 增加n个月到时期p并返回(p的格式yymm或yyyymm)

   mysql>select period_add(‘202008’,3) ;

   注意:p可以是字符串,一定要满足一定的格式,或者可以直接使用date类型.

10. period_diff(p1,p2) - 返回在时期p1和p2之间月数(p1和p2的格式yymm或yyyymm) p1-p2

11. curdate() - 以’yyyy-mm-dd’或yyyymmdd格式返回当前日期值(根据返回值所处上下文是字符串或数字)

12. curtime() - 以’hh:mm:ss’或hhmmss格式返回当前时间值(根据返回值所处上下文是字符串或数字)

13. now() - 以’yyyy-mm-dd hh:mm:ss’或yyyymmddhhmmss格式返回当前日期时间(根据返回值所处上下文是字符串或数字)

14. last_day(date) - date日期所在月的最后一天是什么时候

15. datediff(d1,d2) - 两个日期d1,d2之间相差的天数

16. timestampdiff(type,d1,d2) - type是YEAR,d1和d2相差的年份,MONTH,月份

    d1,d2 - yyyymmdd

3-1. 补充

• date_add(date,interval expr type)

• date_sub(date,interval expr type)

• 关于type

  ype:
  year
  month
  day
  hour
  minute
  second

#关联查询

关联查询就是多表查询

错误的写法

s_emp表中25rows,s_dept表中12rows

1. 查询员工的名字以及这个员工的部门名称

   select first_name,name from s_emp,s_dept;
   
   300 rows 
   
   发生了笛卡尔积 - 错误的结果

纠正 - 传统的写法

select first_name,name from s_emp,s_dept where dept_id=id;

ERROR 1052 (23000): Column 'id' in where clause is ambiguous

多张表中出现同名的列,需要对表取别名

正确的写法:
select e.first_name,d.name from s_emp e,s_dept d where e.dept_id=d.id;

-- 用来表示表与表之间的关系foreign key - 外键

-- 查询这个员工的first_name以及这个员工所在的区域名.
-- 一个区域对应多个部门,一个部门对应多个员工.
select e.first_name,r.name from s_emp e,s_dept d,s_region r
where d.region_id = r.id and e.dept_id = d.id;

for( ...  s_emp e){
	for(... s_dept d){
	    if(e.dept_id==d.id){
	      e+":"+d
	    }
	}
}

join和on写法

如果一条sql语句涉及到的表比较多的话,传统的写法,就不能很直观的看出来哪个表和哪个表之间有直接的关系.

表 join 表 on 去除笛卡尔积

外键一定是出现在多的一方

on 多的一方的外键 = 一的一方主键

但是也不一定非要使用外键来去除笛卡尔积

规则: 能够用join连接的表之间必须要有直接的关系.

练习

1. 找出查询员工的名字以及这个员工的部门名称

   select e.first_name,d.name from s_emp e join s_dept d on e.dept_id = d.id;

2. 找出’Asia’区域上的所有的员工

   select e.first_name,r.name from 
   s_emp e join s_dept d
   on e.dept_id = d.id
   join s_region r
   on d.region_id = r.id
   where r.name='Asia';

3. 找出客户的id,名称,联系方式以及这个客户的订单信息

   ‘问题 - 15rows - [201,215]’

   select c.id,c.name,c.phone,o.* from s_customer c
   join s_ord o on c.id = o.customer_id;
   
   查询出来的结果缺了id=207和215俩个客户,因为这俩个客户没有订单,所以这俩个客户没有查询出来.

连接的方式

1. 内连接 - [inner] join

   如果join左边的表在右边的表中如果不存记录,则不会被查询出来.

   select c.id,c.name,c.phone,o.id from s_customer c
   inner join s_ord o on c.id = o.customer_id;

2. 左连接 - left join

   以左边的表为基准表.即使左边的表在右边的表中没有记录,那么左边表中的这条记录也要能够被查询出来

   查询客户和订单信息,即使这个客户没有订单,那么这个客户也要能够被查询出来

   select c.id,c.name,c.phone,o.id from s_customer c
   left join s_ord o on c.id = o.customer_id;

3. 右连接 - right join

   以右边的表为基准表.即使右边的表在左边的表中没有记录,那么右边表中的这条记录也要能够被查询出来

   查询客户和订单信息,即使这个客户没有订单,那么这个客户也要能够被查询出来

   select c.id,c.name,c.phone,o.id from s_ord o
   right join s_customer c on c.id = o.customer_id;

4. 交叉连接 - cross join - 不会使用的,产生的是一个错误的结果集 - 笛卡尔积

   select c.id,c.name,c.phone,o.id from s_customer c
   cross join s_ord o;

5. 全连接 full outer join - mysql不支持

   全连接 = 左连接 + 右连接

   drop table t_user;
   drop table t_info;
   create table t_user(
   	id int primary key,
     name varchar(20)
   );
   insert into t_user values(1,'admin');
   insert into t_user values(2,'python');
   
   create table t_info(
   	id int primary key,
   	remark varchar(20),
   	user_id int
   );
   insert into t_info values(1,'aaa',1);
   insert into t_info values(2,'bbb',3);
   
   -- 分析 - 左连接
   select u.*,info.* from t_user u left join t_info info on u.id = info.user_id;
   +----+--------+------+--------+---------+
   | id | name   | id   | remark | user_id |
   +----+--------+------+--------+---------+
   |  1 | admin  |    1 | aaa    |       1 |
   |  2 | python | NULL | NULL   |    NULL |
   +----+--------+------+--------+---------+
   
   -- 右连接
   select info.*,u.* from t_user u right join t_info info on u.id = info.user_id;
   +----+--------+---------+------+-------+
   | id | remark | user_id | id   | name  |
   +----+--------+---------+------+-------+
   |  1 | aaa    |       1 |    1 | admin |
   |  2 | bbb    |       3 | NULL | NULL  |
   +----+--------+---------+------+-------+
   
   -- 全连接
   union - 两张表中的重复的结果保留了一条 
   
   union all - 两张表中的重复的结果都会展示出来 , 并集
   
   select u.*,info.* from t_user u left join t_info info on u.id = info.user_id
   union all
   select u.*,info.* from t_user u right join t_info info on u.id = info.user_id;
   
    id   | name   | id   | remark | user_id |
   +------+--------+------+--------+---------+
   |    1 | admin  |    1 | aaa    |       1 |
   |    2 | python | NULL | NULL   |    NULL |
   |    1 | admin  |    1 | aaa    |       1 |
   | NULL | NULL   |    2 | bbb    |       3 |
   +------+--------+------+--------+---------+
   
   
   select u.*,info.* from t_user u left join t_info info on u.id = info.user_id
   union 
   select u.*,info.* from t_user u right join t_info info on u.id = info.user_id;
   
   +------+--------+------+--------+---------+
   | id   | name   | id   | remark | user_id |
   +------+--------+------+--------+---------+
   |    1 | admin  |    1 | aaa    |       1 |
   |    2 | python | NULL | NULL   |    NULL |
   | NULL | NULL   |    2 | bbb    |       3 |
   +------+--------+------+--------+---------+

练习

查询员工的名称以及他对应的上司的名称,即使这个员工没有上司,那么也要查询出来 - 自关联

select e1.first_name 员工,e2.first_name 上司
from s_emp e1 left join s_emp e2 on e2.id = e1.manager_id;

多行函数

1. count - 统计个数,null值不会统计,重复的数据,有一个算一个

   select count(*) from s_emp;
   
   select count(e.id) from s_emp e;
   
   select count(e.commission_pct) from s_emp;//5个
   
   -- 统计职称的个数,但是重复的职称只能算1个
   select count(distinct title) from s_emp;

2. sum - 总和,忽略null值

3. avg,max,min

分组查询

分组统计

语法

SELECT 语句
WHERE 语句
GROUP BY 语句
HAVING 语句
ORDER BY 语句

练习

1. 统计每个部门的员工的个数 - 单表

   select e.dept_id,count(e.id) from s_emp e group by e.dept_id;

2. 统计部门的id,name以及这个部门的员工的个数

   -- group by + d.id  --- ok   id是主键列 - 唯一性
   select d.id,d.name,count(e.id) from s_emp e
   join s_dept d on e.dept_id = d.id group by d.id;
   
   
   select d.id,d.name,count(e.id) from s_emp e
   join s_dept d on e.dept_id = d.id group by d.name;
   ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dy.d.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
   
   -- 推荐select后面跟的列(除了组函数/多行函数)应该和group by 跟的列保持一致
   select d.id,d.name,count(e.id) from s_emp e
   join s_dept d on e.dept_id = d.id group by d.id,d.name;

3. 统计每个职称的人数

   select title,count(*) from s_emp group by title;

4. 统计区域id,区域名,以及这个区域上的员工的个数

   select r.id,r.name,count(e.id) from s_region r join s_dept d
   on d.region_id = r.id join s_emp e on e.dept_id = d.id group by r.id,r.name;

5. 统计客户的id,name以及这个客户的订单的数量,没有订单,则显示0

   select c.id,c.name,count(o.id) from s_customer c left join s_ord o
   on o.customer_id = c.id group by c.id,c.name;

6. 统计各年份入职的员工(年份,员工数量)

   select year(start_date),count(e.id) from s_emp e
   group by year(start_date);

having使用方式

having和where区别

1. where条件筛选,但是分组之前,并且where不会跟组函数
2. having分组之后的进一步过滤,使用组函数进行过滤的

练习

1. 查询出平均工资超过1300的部门 - 部门id,name,平均工资

   select d.id,d.name,avg(e.salary) from s_dept d join s_emp e
   on d.id = e.dept_id group by d.id,d.name having avg(e.salary)>1300;

2. 统计除了’Asia’区域之外的各个区域的id,name和区域上的人数[大于3个],最后再根据区域的id进行降序排列

   select r.id,r.name,count(e.id) from s_region r 
   join s_dept d on r.id = d.region_id
   join s_emp e on e.dept_id = d.id
   where r.name<>'Asia'
   group by r.id,r.name
   having count(e.id)>3
   order by 1 desc;

3. 查询没有订单的客户

   select c.id,c.name,count(o.id) from s_customer c
   left join s_ord o on c.id = o.customer_id group by c.id,c.name having count(o.id)=0;

子查询

子查询必须使用()括起来

• 条件列
• 查询列
• 子查询的结果看做是”一张表”

练习

1. 查找和Mark工资一样的员工.

   -- 分解 - mark工资 - 1450
   select salary from s_emp where first_name='Mark';
   
   -- 找出1450薪资的员工
   select first_name,salary from s_emp where salary = 1450 and first_name<>'Mark';
   
   -- 整合
   -- 子查询出现在条件部分
   select first_name,salary from s_emp where salary = (select salary from s_emp where first_name='Mark')  and first_name<>'Mark';

2. 统计每个部门的id,name以及部门的员工的个数

   select d.id,d.name,(
   	select count(e.id) from s_emp e where e.dept_id = d.id
   ) from s_dept d;

练习2

1. 统计客户的id,name以及客户的订单数量

select c.id,c.name,(select count(o.id) from s_ord o where o.customer_id = c.id) from s_customer c;

2. 找出部门平均工资高于公司平均工资的部门信息

select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>(select avg(salary) from s_emp);

3. 找出与’Ben’同部门的员工信息

-- 方式一
select first_name,dept_id from s_emp where dept_id=(select dept_id from s_emp where first_name='Ben')
and first_name<>'Ben';

-- 方式二
select e.first_name,(select e.dept_id from s_emp e1 where e1.first_name='Ben' and e1.dept_id=e.dept_id) did from s_emp e where e.first_name<>'Ben';

-- 方式二整合 - 把子查询的结果作为一张表,必须要别名
select * from (select e.first_name,(select e.dept_id from s_emp e1 where e1.first_name='Ben' and e1.dept_id=e.dept_id) did from s_emp e where e.first_name<>'Ben') core_
where core_.did is not null;

总结

1. 能用分组查询查询就不要使用子查询(嵌套越多,查询效率越低)

2. 无关子查询(子查询可以独立运行)和相关子查询(子查询使用到了外部查询的东西)

   无关子查询>相关子查询

3. 子查询会比分组查询更加灵活.

exists和not exists

SELECT 语句
WHERE exists (子查询)

规则: 不关心子查询查到的内容是什么,子查询是否能够查询出结果.
如果查询出结果,等同于where true

select id,first_name from s_emp where exists(select 1 from s_emp where 1=1);

找出和'Ben'同一个部门的员工

select e.first_name,e.dept_id from s_emp e 
where exists(
select 1 from s_emp e1 where e1.first_name='Ben' and e1.dept_id = e.dept_id
) and e.first_name<>'Ben';

练习

找出各个部门工资排名前二的员工

思路: 在’我’这个部门中,有人的工资比我高,但是数量不能超过1个

select e.id,e.salary,e.dept_id from s_emp e
where exists(
	select 1 from s_emp e1 where e1.dept_id = e.dept_id and
e1.salary>e.salary having count(*)<=1
) order by 3;

练习

找出每个部门中工资最高的那个人