MySQL数据库学习笔记

                    insert into 表名(字段1,字段2,...)
               values('表达式11','表达式12',...),
                   ('表达式21','表达式22',...),
                     .......
14、在表中修改单条数据的单个字段的值

update 表名 set 字段1=表达式1 【where 条件】; 没有where时更新所有数据

15、删除数据表的某一行数据

delete from 表名 where 字段名=值;

16、删除所有数据但保留表结构：

delete from 表名

17、数据库相关查询操作及案例

1.查看当前所有的数据库：
show databases;

2.打开指定的库：
use 库名;

3.查看当前库的所有表：
show tables from 库名;

4.查看其他库的所有表：
show tables from 库名;

5.创建表：
   create tables 表名(
       列名1 数据类型字段属性，
       列名2 数据类型字段属性,
        …
      列名n 数据类型字段属性
   )

6.查看表结构：
desc 表名;

7.查看服务器的版本：
方法1：登录到mysql服务器：
   select version( );
方法2：未登录到mysql服务器：
   mysql --version
   或
   mysql --V

8.`字段` #用于区分字段，避免系统混淆，列又叫字段

9.基础查询：
   语法：select 查询列表 from 表名;
   特点：①查询列表可以是：表中的字段、常量值、表达式、函数
       ②查询的结果是一个虚拟的表格

   1）查询表employees中的单个字段（举例）：
       select last_name from employees;
   2）查询表中的多个字段:
       select last_name,salary,email from employees;
   3）查询表中的所有字段：
       select * from employees;
   4）查询常量值：
       select 100;
       select 'john';
       #注：字符型和日期型的常量值必须用单引号引起来，数值型不需要
   5）查询表达式：
       select 100%98；
   6）查询函数：
       select 函数名(参数列表)；
       #例：select version(3)；
       #必须有返回值
   7）起别名：
       方法一：使用as：
           select last_name as 姓,first_name as 名 from employees;
       方法二：使用空格：
           select last_name 姓,first_name 名 from employees;
       #案例：查询salary，显示结果为 out put（因为out为关键字，所以需要双引号）
           select salary as "out put" from employees;
   8）去重：
       #案例：查询员工表中涉及到的所有部门的编号：
           select distinct department_id from employees;
   9）+号的作用：运算符
       ①select 100+90;       #两个操作数都为数值型，则做加法运算
       ②select '123'+90;       #其中一方为字符型，则系统会试图将字符型数值转换成数值型
                           如果转换成功，则继续做加法运算；
                           即：select '123'+90; 结果为213
                           如果转换失败，则将字符型数值转换成0
                           即：select 'john'+90; 结果为90
       ③select null+10;       #只要其中一方为null，则结果肯定为null

       #案例：查询员工名和姓连接成一个字段，并显示为姓名：
           select concat(last_name,first_name) as 姓名 from employees;
   10）concat函数：拼接字符
       select concat(字符1,字符2,...);
   11）ifnull函数：判断某字段或表达式是否为null，如果为null，返回指定的值，否则返回原本的值
       select ifnull(commission,0) from employees;
   12）isnull函数：判断某字段或表达式是否为null，如果是，则返回1，否则返回0；

10.进阶查询：
   语法：
       select
           查询列表
       from
           表名
       where
           筛选条件;    #判断为true，则显示，否则pass掉
                   #执行顺序为：表名👉筛选条件👉查询列表
   分类：
       1.按条件表达式筛选：
           ①条件运算符：> < = != <>(不等于） >= <= <=>(安全等于）
       2.按逻辑表达式筛选：
           ①逻辑运算符：&& || ! （Java）
                   and or not （MySQL）
       3.模糊查询：
           like：
           between and：
           in：
           is null /is not null：

   1）按条件表达式筛选：
   #案例1：查询工资>12000的员工信息：
       select
           *
       from
           employees
       where
           salary>12000;
   #案例2：查询部门编号不等于90号的员工名和部门编号：
       select
           last_name,
           department_id
       from
           employees
       where
           department_id<>90;

   2）按逻辑表达式筛选：
   #案例1：查询工资在10000到20000之间的员工名、工资以及奖金：
       select
           last_name,
           salary,
           commission_pct
       from
           employees
       where
           salary>=10000 and salary<=20000;

   #案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息：
       select
           *
       from
           employees
       where
           not(department_id>=90 and department_id<=110) or salary>15000;

   3）模糊查询：
   ①like :
       特点：一般和通配符搭配使用
           通配符： % 任意多个字符，包含0个字符
               _ 任意单个字符
   #案例1：查询员工名包含字符a的员工信息：
       select
           *
       from
           employees
       where
           last_name like '%a%';

   #案例2：查询员工名中第三个字符为n，第五个字符为l的员工名和工资：
       select
           last_name,
           salary
       from
           employees
       where
           last_name like '__n_l%';

   #案例3：查询员工名中第三个字符为_的员工名：
       select
           last_name
       from
           employees
       where
           last_name like '_\_%';   #转义字符：\
       （或者这样写：last_name like '_$_%' escape '$'; #即自定义转义字符）

   ②between and:
   #案例1：查询员工编号在100到120之间的员工信息：
       select
           *
       from
           employees
       where
           employees_id between 100 and 120;

   ③in: 用于判断某字段中的值是否属于in列表中的某一项
   案例1：查询员工的工种编号是：IT、AD、VP中的一个员工名和工种编号：
       select
           last_name,
           job_id
       from
           employees
       where
           job_id in( 'IT' , 'AD' , 'VP' );   #in列表的值必须为同类型或兼容

   ④is null： =或<>不能用于判断null值
       is null 或 is not null 可以判断null值
   #案例1：查询没有奖金的员工和奖金率：
       select
           last_name,
           commission_pct
       from
           employees
       where
           commission_pct is null;   #不能直接用=，因为=不能判断null值
       （若查询有奖金的，改为：commission_pct is not null;）

   ⑤安全等与 <=>（即为等于）：既可以判断null值，又可以判断普通数值
   #案例1：查询没有奖金的员工和奖金率：
       select
           last_name,
           commission_pct
       from
           employees
       where
           commission_pct <=> null;
   #案例2：查询工资>12000的员工信息：
       select
           *
       from
           employees
       where
           salary <=> 12000;

11.排序查询：
   引入：select 字段名 from 表名;
   语法：
       select 查询列表
       from 表名
       【where 筛选条件】
       order by 排序列表【asc/desc】
   特点：

        ①asc升序、desc降序，若不写，默认升序
       ②order by子句中可以支持单个字段、多个字段、表达式、函数、别名
       ③order by子句中一般是放在查询语句的最后面，limit字句除外

#案例1：查询员工信息，要求工资从高到低排序：
select * from employees order by salary desc;

   #案例2：查询部门编号>=90的员工信息，按入职时间的先后进行排序【添加筛选条件】
       select
           *
       from
           employees
       where
           department_id>=90
       order by
           hiredate asc;

   #案例3：按年薪的高低显示员工的信息和年薪【按表达式排序】
       select
           *,
           salary*12*(1+ifnull(commission_pct,0)) 年薪
       from
           employees
       order by
           salary*12*(1+ifnull(commission_pct,0)) desc;

   #案例4：按年薪的高低显示员工的信息和年薪【按别名排序】
       select
           *,
           salary*12*(1+ifnull(commission_pct,0)) 年薪
       from
           employees
       order by
           年薪 desc;

   #案例5：按姓名的长度显示员工的姓名和工资【按函数排序】
       select
           length(last_name) 字节长度,
           last_name,
           salary
       from
           employees
       order by
           length(last_name) desc;

   #案例6：查询员工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】
       select
           *
       from
           employees
       order by
           salary asc,
           employees_id desc;
       #以前面的为主以此排序

12.常见函数：
   概念：类似java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
   调用：select 函数名(实参列表) 【from 表】;
   #若参数列表用到了表中的字段，则需要加中括号中的语句
   分类：

①单行函数（处理）如 concat、length、ifnull等
②分组函数（统计）：又称统计函数、聚合函数、组函数

   1）字符函数：

       ①length()：获取参数值的字节个数
       #案例1：select length('john'); #4
       #案例2：select length('张三丰hahaha'); #15，汉字占3个字节

②concat()：拼接字符串
#案例1：select concat(last_name,'_',first_name) 姓名 from employees;

       ③upper()转大写、lower()转小写：
       #案例1：select upper('john');   #JOHN
       #案例2：将姓转换成大写，名转换成小写，然后拼接：
           select concat(upper(last_name),lower(first_name)) 姓名 from employees;

       ④substr()/substring()：⭐索引从1开始
       #案例1：截取从指定索引处开始到后面所有字符：
           select substr('123456789',7) out_put;   #789
       #案例2：截取从指定索引处指定字符长度的字符：
           select substr('123456789',1,3) out_put; #123

       #案例3：姓名中首字符大写，其他字符小写，并用_拼接，显示出来：⭐#有疑问
           select concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2))) 姓名
           from employees;

       ⑤instr()：返回字串第一次出现的索引，若找不到则返回0
       #案例1：select instr('122334','3');   #4

       ⑥trim()：去掉前后空格或指定字符
       #案例1：select trim(' xxx ');   #xxx
       #案例2：select trim('1' from '111xx11x1111');   #xx11x

       ⑦lpad()：用指定的字符实现左填充指定长度
       #案例1：select lpad('123',5,'*');   #**123
       #案例2：select lpad('123',2,'*');   #12

⑧rpad()：右填充
#案例1：select rpad('123',5,'*'); #123**

⑨replace()：替换
#案例1：select replace('12345','12','27'); #27345

2）数学函数：

       ①round()：四舍五入
       #案例1：select round(1.65);   #2
       #案例2：select round(1.567,2);   #1.57，保留两位小数

       ②ceil()：向上取整，返回>=该参数的最小整数
       #案例1：select ceil(-1.02);   #-1

       ③floor()：向下取整，返回<=该参数的最大整数
       #案例1：select floor(-9.99);   #-10

④truncate()：截断
#案例1：select truncate(1.669,1); #1.6，从第一个小数开始截断，后面的舍去

       ⑤mod()：取余,与%一样
       #mod(a,b) ：a-a/b*b   ⭐a/b 没有浮点数，结果为整数
       #案例1：select mod(10,3);   #1

   3）日期函数：

       ①now()：返回当前系统日期＋时间

②curdate()：返回当前系统日期，不包含时间

③curtime()：返回当前时间，不包含日期

       ④可以获取指定的年、月、日day、时hour、分minute、秒：
       #案例1：select year(now()) 年;
       #案例2：select year('2002-1-27') 年;
       #案例3：select year(字段名) 年 from 表名;
       #案例4：select month(now()) 月;
       #案例5：select monthname(now()) 月; ⭐结果为月份的英文

       ⑤str_to_date：将日期格式的合法字符转换成指定格式的日期
       #str_to_date('7-29-2022','%m-%d-%Y);   #2022-7-29
       ⭐：
           %Y：四位的年份
           %y：两位的年份
           %m：月份（01，02...12）
           %c：月份（1，2...12）
           %d：日（01，02，....）
           %H：小时（24小时制）
           %h：小时（12小时制）
           %i：分钟（00，01，...59）
           %s：秒（00，01，...59）

       #案例1：查询入职日期为1992-4-3的员工信息：
           ①select * from employees where hirdate = '1992-4-3';
           ②select * from employees where hirdate = str_to_date('4-3 1992','%c-%d %Y');

⑥date_format()：将日期转换成字符
#date_format('2022/7/29','%Y年%m月%d日'); #2022年7月29日

   4）流程控制函数：

       ①if函数：实现 if else 的效果
       # select if(表达式,'true时返回的值','false时返回的值');

       ②case函数：
       #语法1：case 要判断的字段或表达式
           when 常量1 then 要显示的值1或语句1;
           ......
           esle 要显示的值n或语句n;
           end
           ⭐如果是值就不用加分号
           没有其他条件的话else可省略

       ⭐案例：查询员工的工资，要求：
           部门号=30，显示的工资为1.1倍
           部门号=40，显示的工资为1.2倍
           部门号=50，显示的工资为1.3倍
           其他部门显示的工资为原工资

           select salary 原工资,department_id,
           case department_id
           when 30 then salary*1.1
           when 40 then salary*1.2
           when 50 then salary*1.3
           else salary
           end as 新工资
           from employees;

       #语法2：case
           when 条件1 then 要显示的值1或语句1;
           when 条件2 then 要显示的值2或语句2;
           ......
           else 条件n then 要显示的值n或语句n;
           end
           ⭐如果是值就不用加分号
           没有其他条件的话else可省略
           select salary ,
           case
           when salary>20000 then 'A'
           when salary>15000 then 'B'
           when salary>10000 then 'C'
           else 'D'
           end as 工资级别
           from employees;

   5）分组函数：用于统计使用，又称聚合函数或统计函数或组函数
       #分类：sum，avg 平均值，max，min，count 计算个数
       #特点：

              ①sum、avg一般用于处理数值型、max、min、count可以处理任意类型
           ②以上分组函数都忽略null值
           ③可以和distinct搭配
           ④一般使用count(*)用作统计行数
           ⑤和分组函数一同查询的字段要求是group by后的字段

13.分组查询：
   语法：
       select 分组函数列（要求出现在group by的后面）
       from 表名
       【where 筛选条件】
       group by 分组的列表
       【order by 字句】
   注意：查询列表必须特殊，要求是分组函数和group by 后出现的字段
   特点：

        ①分组前查询：数据源为原始表、位置在group by 字句前面、关键字为where
       ②分组后查询：数据源为分组后的结果集、位置在group by 字句后面、关键字为having
       ③group by 字句支持单、多个字段分组，逗号隔开，无顺序
       ④可添加排序（放在最后）
       ⭐分组函数做条件一定放在having子句中
       #优先考虑分组前筛选
   案例：
       1）查询每个工种的最高工资：
           select
               max(salary),
               job_id
           from
               employees
           group by
               job_id;

       2）查询每个位置上的部门个数：
           select
               count(*),
               location_id
           from
               departments
           group by
               location_id;

       3）查询邮箱中包含a字符的，每个部门的平均工资
           select
               avg(salary),
               department_id
           from
               employees
           where
               email like '%a%'
           group by
               location_id;

       4）查询每个领导手下有奖金的员工的最高工资：
           select
               max(salary),
               manager_id
           from
               employees
           where
               commission_pct is not null
           group by
               manager_id;

       5）查询哪个部门的员工个数>2（需要先查询每个部门的员工个数，在此基础上查询哪个>2）
           ①
           select
               count(*),
               department_id
           from
               employees
           group by
               manager_id
           having⭐
               count(*)>2;

⭐where是对分组前的每一行数据进行筛选
⭐having是对分组后的每一行进行筛选，筛选条件是每一组的群体特征，如：一组的总和、平均值

       6）查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
           ①查询每个工种有奖金的员工的最高工资
           select
               max(salary),
               job_id
           from
               employees
           where
               commission_pct is not null
           group by
               job_id;
           ②根据①结果继续筛选，最高工资>12000
           select
               max(salary),
               job_id
           from
               employees
           where
               commission_pct is not null
           group by
               job_id
           having
               max(salary)>12000;

       7）查询领导编号>102且其手下员工的最低工资>5000的领导编号是什么，以及其中的最低工资
           ①查询每个领导手下员工的最低工资：
           select
               min(salary),
               manager_id
           from
               employees
           group by
               manager_id;
           ②领导编号>102：
           select
               min(salary),
               manager_id
           from
               employees
           where
               manager_id>102
           group by
               manager_id;
           ③最低工资>5000：
           select
               min(salary),
               manager_id
           from
               employees
           where
               manager_id>102
           group by
               manager_id
           having
               min(salary)>5000;

   #按表达式或函数分组：

       案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
           ①查询每个长度的员工个数：
           select
               count(*),
               length(last_name) 名字长度
           from
               employees
           group by
               length(last_name);
           ②员工个数>5：
           select
               count(*),
               length(last_name) 名字长度
           from
               employees
           group by
               length(last_name) （支持别名）
           having count(*)>5;       （支持别名）

   #按多个字段查询:

       案例：查询每个部门中每个工种的员工的平均工资（可添加排序）：
           select
               avg(salary),
               department_id,
               job_id
           from
               employees
           group by
               job_id,
               department_id;

⭐⭐⭐⭐⭐查差距用difference
如果给表起了别名，则查询的字段就不能使用原来的表名去限定

14.连接查询：
   含义：又称多表查询。当查询的字段涉及多个表时可用
   笛卡尔乘积现象：表1 m行，表2 n行，则结果有m*n行
   分类：
       1）等值连接：

①多表等值连接的结果为多表的交集部分
               ②n表连接，至少需要n-1个连接条件
               ③建议为表起别名
       #案例：查询员工名和对应的部门名
           select last_name,department_name
           from empolyees,departments
           where employees.department_id=department.department_id;

2）非等值连接

       3）自连接：
       #案例：查询员工名和其上级的名字
           select e.employees_id, e.last_name, m.employees_id, m.last_name
           from employees e, employees m
           where e.manager_id = m.employees_id;

   ⭐sql99语法：
       select 查询列表
       from 表1 别名【连接类型：内、外等】
       join 表2 别名
       on 连接条件
       【where 筛选条件】
       【group by 分组】
       【having 筛选条件】
       【order by 排序列表】

       1）内连接：inner（可省略）（交集部分）
           分类：等值连接、非等值连接、自连接
           ①等值连接：
           案例1：查询部门个数>3的城市名和部门个数（添加分组和筛选）
               select city, count(*) 部门个数
               from location l
               inner join departerment d
               on d.location_id=l.location_id
               group by city
               having count(*)>3;

           案例2：查询哪个部门的员工个数>3的部门名和员工个数，按个数降序
               select count(*) 部门个数,department_name
               from employees e
               inner join departerments d
               on d.departerment_id=e.departerment_id
               group by department_name
               having count(*)>3
               order by count(*) desc;

           案例3：查询员工名、部门名、工种名，按部门名降序（三表连接）
               select last_name,department_name，job_title
               from employees e
               inner join departerments d on d.departerment_id=e.departerment_id
               inner join jobs j on j.job_id=e.job_id
               order by department_name desc;

           ②非等值连接：
           案例：查询每个工资级别个数>20的个数，按工资降序
               select count(*),grade_level
               from employees e
               inner join job_grades g
               on e.salary between g.lowest_sal and g.highest_sal
               group by grade_level
               having count(*)>20
               order by grade_level desc;

           ③自连接：
           案例：查询姓名中包含字符k的员工名和其上级的名字
               select e.last_name, m.last_name
               from employees e
               join employees m
               on e.manager_id = m.employees_id
               where e.last_name like '%k%';
       2）外连接：用于查询一个表中有，另一个表没有
           特点：

                ①外连接的查询结果为主表中的所有记录
                    如果从表中有和它匹配的，则显示匹配的值
如果从表中没有与它匹配的，则显示null值
                    外连接查询结果=内连接结果+主表中有而从表中没有的记录

               ②左外连接：left join 左边的是主表
               右外连接：right join 右边的是主表

               ③左外和右外交换两个表的顺序，可以实现同样的效果

               左外：left 【outer 可省略】
                   查询哪个部门没有员工
                   select d.*,e.employees_id
                   from departments d
                   left outer join employees e
                   on d.department_id=e.department_id
                   where e.employee_id is null;

               右外：right 【outer】
                   查询哪个部门没有员工
                   select d.*,e.employees_id
                   from employees e
                   right outer join departments d
                   on d.department_id=e.department_id
                   where e.employee_id is null;

全外：full 【outer】（两表并集+内连接结果）

3）交叉连接：cross （结果为笛卡尔乘积）

15.子查询：出现在其他语句中的select语句
   分类：
       按子查询出现的位置：
           select后面：只支持标量子查询
           from后面：支持表子查询（将子查询结果充当一张表，要求必须起别名）
           where或having后面：支持标量子查询（单行）⭐、列子查询（多行）⭐、行子查询
           exists后面（相关子查询）：表子查询
               exists（完整的查询语句），结果为1或0
       按结果集的行列数不同：
           标量子查询（结果集只有一行一列）
           列子查询（结果集为一列多行）
           行子查询（结果集为一行多列）
           表子查询（结果集一般为多行多列）
   特点：
       标量子查询，一般搭配单行操作符。如：> < >= <= = <>
       列子查询，一般搭配多行操作符。如：in/not in any/some all
       子查询优先于主查询执行，主查询的条件用到子查询结果
   案例1：where或having后面
       ⭐标量子查询：
       1：谁的工资比Abel高？
           ①查询Abel工资
           select salary
           from employees
           where last_name='Abel';

           ②查员工，满足salary>①结果
           select * from employees
           where salary >
           (
               select salary
               from employees
               where last_name='Abel'
           );

       ⭐列子查询（多行）
       1：返回location_id是1400或1700部门中的所有员工姓名
           ①查询location_id是1400或1700的部门编号
           select department_id
           from departments
           where location_id in(1400,1700);

           ②查询员工姓名，要求部门号是①列表中的某一个
           select last_name
           from employees
           where department_id in
           (
               select department_id
               from departments
               where location_id in(1400,1700)
           );

       行子查询（结果为一行多列或多行多列）
       1：查询员工编号最小并且工资最高的员工信息
           select *
           from employees
           where (employees_id,salary)=
           (
               select min(employees_id),max(salary)
               from employees
           );

   案例2：在select后面
       ①查询每个部门的员工个数
           select d.*,
           (
               select count(*)
               from employees e
               where e.department_id=d.department_id
           ) 个数
           from department d;

   案例3：在from后面
       查询每个部门的平均工资的工资等级
       ①查询每个部门的平均工资：
           select avg(salary),department_id
           from employees
           group by departmrnt_id;

       ②连接①的结果集和job_grades表，筛选条件平均工资between lowest_sal and highest_sal
           select ag_dep.*,g.grade_level
           from
           (
               select avg(salary),department_id
               from employees
               group by departmrnt_id
           ) ag_dep
           inner join job_grades g
           on ag_dep.ag between lowest_sal and highest_sal;

案例4：在exists后面（相关子查询）（是否存在）

----------------------------我是一条分割线-----------------------------