mysql笔记

DBMS：数据库管理系统
SQL：用于访问和处理数据库的标准的计算机语言
DB：数据库

三者的关系： DBMS数据库管理系统执行SQL语句，来操作DB数据库当中的数据的

条件查询 in(a,b,c,…)
可以替代or，提高检索效率
例：

select id,name,age from student where name = '张三' or name = '李四'
替换为：
select id,name,age from student where name in ('张三' , '李四')

%代表任意多个字符
_代表任意1个字符

例1：查询姓张的同学
select id,name,age from student where name like '张%'

例2：查询姓李且只有两个字的同学
select id,name,age from student where name like '李_'

ps：CSDN遵循Markdown语法，不能打出下划线，所以需要转义字符\，如_

asc 升序（默认）
desc 降序
排序 order by sal 薪资 asc/desc ;

例句：按工资对公司的员工进行分类<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Ex.: sort the employees of the company by salary</font>*</details>
select name,age,sal from emp order by sal desc

select
       *             5
from
     tableName       1
where
     条件(join)       2
group by
     ...             3
having
     ...             4
order by
     ...             6
limit
     ...             7

分组函数：多行处理函数

函数 说明 count 计数 sum 求和 avg 平均值 max 最大值 min 最小值 注意：

• 分组函数一共 5个
• 输入多行，输出结果总是 1行
• 分组函数自动忽略NULL
• NULL参与运算时，结果为NULL，解决办法是：ifnull(comm,0)
• 分组函数 不可直接使用在where子句中，因为分组函数是在group by执行之后才会执行
• count() 统计总记录的条数，count(字段名) 统计某个字段不为 NULL*的数量

group by ：按照某个字段或者某些字段进行分组
having ：对分组之后的数据进行再次过滤

例1：按公司员工的工作岗位进行分组
select name,job from emp group by job;

例2：找出每个部门的最高薪资，要求显示薪资大于2900的数据

select max(sal),deptno from emp group by deptno having max(sal)>2900; 效率低
select max(sal),deptno from emp where sal>2900 group by deptno;
效率高，建议能够使用where过滤的尽量使用能够在分组之前过滤的，就先进行where过滤

例3：找出每个部门的平均薪资，要求显示薪资>2000的数据

步骤1.找出每个部门的平均薪资
 select deptno,avg(sal) from emp group by deptno;

步骤2.要求显示薪资>2000的数据
 select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

select distinct job from emp;
distinct 只能出现在所有字段的最前边，表示字段的联合去重

在实际开发中，大多数情况下，数据不是从单表查询的，而是最终数据通常是由多个表查询的。

一般来说，一个业务会对应多个表，比如学生表和班级表，至少两个表。

连接查询的分类

根据语法出现的年龄进行划分。

• SQL92（一些老的DBA可能还在使用这种语法。DBA：数据库管理员）
• SQL99（比较新的语法）

根据表的连接方式来划分

内连接：

• 等值连接
• 非等值连接 between and
• 自连接

外连接

• 左外连接（左连接）
• 右外连接（右连接）

全连接（很少用）

注：在表的连接查询中存在一种称为笛卡尔积的现象。

在数据库中，两个表的联接查询显示的默认结果条目数是两个表的记录数的乘积。

案例：找出每个员工的部门名称，并要求显示员工名称和部门名称。<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Case: find out the department name of each employee and require the employee name and department name to be displayed.</font>*</details>
  select ename,dname from emp,dept;

如何避免笛卡尔积现象？
避免了笛卡尔乘积现象，不减少记录的匹配次数，但显示有效记录。

select                //SQL92 旧语法
  e.ename,d.dname
from
  emp e,dept d
where
  e.deptno = d.deptno;

**内连接之等值连接：**

特点：条件是等量关系
select            //SQL99 新语法,常用的
  e.ename,d.dname
from
  emp e
join
  dept d
on
  e.deptno = d.deptno;

语法：
  ...

    A
 (inner) join        //inner 可读性更强，但可以省略
    B
 on
  连接条件
 where
  ...

SQL99语法结构更清晰，表的连接条件和后来的where条件分离了

特征：连接条件中的关系不相等。

案例：找出每个员工的薪资等级，并要求显示员工的姓名、薪资和薪资等级。<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Case: find out the salary grade of each employee, and ask to display the employee's name, salary and salary grade.</font>*</details>
  select
    e.ename,e.sal,s.grade
  from
    emp e
  join
    salgrade s
  on
    e.sal between s.losal and hisal;

特点：一张桌子就是两张桌子，自己连接

案例：找出每个员工的上级领导，并要求显示员工姓名和对应的领导姓名。<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Case: find out the superior leader of each employee, and ask to display the employee name and the corresponding leader name.</font>*</details>
  select
    a.ename,b.ename
  from
    emp a
  inner join
    emp b
  on
    a.mgr = b.empno;

内连接：
假设A表和B表进行连接，使用内连接，凡是A表和B表能够匹配上的记录查询出来
AB两张表没有主副之分，两张表是平等的
外连接：
假设A表和B表进行连接，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的
数据，捎带着查询副表，当副表的数据没有和主表数据匹配上，副表自动模拟出NULL与之匹配
外连接的分类
LEFT OUTER JOIN(LEFT JOIN)：表示左侧的表是主表

Right Out Join(Right Join)：表示右侧的表是主表

案例：找出每位员工的上级领导<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Case: find out the superior leader of each employee</font>*</details>
   select
     a.ename,b.ename
   from
     emp a
   left (outer) join  //left 左边是主表 outer可以省略
     emp b
   on
     a.mgr = b.empno;

在实际开发中，大部分是外连接

外部连接最大的特点是：无条件查询主表的数据。

案例研究：找出哪个部门没有员工？<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Case study: find out which department has no employees?</font>*</details>
 select
  d.deptno,d.dname
 from
    emp e
 right outer join
    dept d
 on
    e.deptno = d.deptno
 where
    e.empno is null;

案例研究：找出每个员工的部门名称和工资等级<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Case study: find out the department name and salary grade of each employee</font>*</details>
  select
   e.ename,d.dname,s.grade
  from
    emp e
  join
    dept d
  on
    e.deptno=d.deptno
  join
    salgrade s
  on
    e.sal between s.losal and hisal;

案例：找出每个员工和上级领导的部门名称和工资等级<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Case: find out the department name and salary grade of each employee as well as the superior leader</font>*</details>
  select
   e.ename '员工',d.dname '部门',s.grade '工资等级',em.ename '领导'
  from
    emp e
  join
    dept d
  on
    e.deptno=d.deptno
  join
    salgrade s
  on
    e.sal between s.losal and hisal
  left join
    emp em
  on
    e.mgr = em.empno;

select语句当中嵌套select语句，被嵌套的select语句是子查询

  select
    ..(select)    在select中嵌套
  from
    ..(select)    在from中嵌套
  where
    ..(select)    在where中嵌套

• 在where子句中使用子查询

案例：查找工资高于平均水平的员工的信息<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Case: find information about employees with higher-than-average salary</font>*</details>
select
  *
from
  emp
where
  sal > (select avg(sal) from emp);

• from后面嵌套子查询

案例研究：找出每个部门的平均工资的工资等级<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Case study: find out the salary grade of the average salary in each department</font>*</details>
  select
    t.*,s.grade
  from
    (select deptno,avg(sal) avgsal from emp group by deptno) t
  join
    salgrade s
  on
    t.avgsal between s.losal and hisal;

案例研究：找出每个部门的平均薪资等级<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Case study: find out the average salary grade of each department</font>*</details>
   select
     e.ename,e.sal,e.deptno,s.grade,avg(s.grade)
   from
     emp e
   join
     salgrade s
   on
     e.sal between s.losal and s.hisal
   group by
     e.deptno

• 在select后面嵌套子查询

案例：找出每个员工所在部门的名称，并要求显示员工姓名和部门名称。<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Case: find out the name of each employee's department, and require the employee name and department name to be displayed.</font>*</details>
  select
    emp.ename,dept.dname
  from
    emp
  join
    dept
  on
    dept.deptno = emp.deptno;

案例：找出工作岗位是SALESMAN和MANAGER的员工？
  select
    ename,job
  from
    emp
  where
//    job = 'salesman' or job = 'manager';
// or改成in
    job in ('salesman','manager');

方法二：
  select ename,job from emp where job = 'salesman';
    union
  select ename,job from emp where job = 'manager';

特点：mysql特有的，其他数据库没有。
作用：limit取结果集中的部分数据
语法：limit startIndex,length
startIndex 表示起始位置，length 表示取几个

案例：取出工资前5名的员工
   select
     ename,sal
   from
     emp
   order by
     sal desc
   limit
     0,5;

案例：找出工资排名在第4到第9名的员工
  select
    ename,sal
  from
    emp
  order by
    sal desc
  limit
    3,6;

            ** 通用的标准分页sql **

  每页显示3条记录
  第1页：0，3
  第2页：3，3
  第3页：6，3

  每页显示pageSize条记录（如上述的pageSize=3）
  pageNo表示第几页，如上述的第1页、第2页、第3页，pageNO=1，2，3，....

  公式：第pageNo页：(pageNo-1)*pageSize,pageSize

  create table 表名(
     字段名1 数据类型 约束，
     字段名2 数据类型 约束，
     字段名3 数据类型 约束，
     ...

  );
  create table t_student(
    no bigint,
    name varchar(255),
    sex char(1),
    classno varchar(255),
    birth char(10)
  );

主键值自增： auto_increment 从1开始递增

  insert into
    表名(字段名1,字段名2,字段名3,....)
  values
    (值1,值2,值3,...)

  insert into
    t_student(no,name,sex,classno,birth)
  values
    (1,'zhangsan','1','gaosan1ban','1999-07-06');

  create table emp1 as select * from emp;
  创建一张表emp1，将emp表的查询结果放到emp1中

update 表名 set 字段名1=值1,字段名2=值2,... where 条件;

  delete from 表名 where 条件;

  删除大表 truncate table emp;  //表被截断，不可回滚，永久丢失

关于MySQL从删除到跑路的方法 : )

1.删除数据库删除后可能会遗留日志，一些数据还是可以通过日志恢复的，所以索性把日志也一起删了吧
drop database databasename
purge binary logs to '日志名字';
2.直接删除mysql的服务和数据
find / -name mysql
3.删除找到的关于mysql的一切
rm -rf /var/lib/mysql
rm -rf /var/lib/mysql
rm -rf /usr/lib64/mysql
rm -rf /etc/my.cnf

(*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣)
这只是一个玩笑，但多学一点是很好的。<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>It's just a joke, but it's good to learn a little more.</font>*</details>
(*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣)

查看当前mysql支持的存储引擎
  show engines \G

事务是不能再分割的完整业务逻辑单元。

  比如：银行账户转账，从A账户向B账户转账1000，需要执行两条update语句
  update t_act set balance = balance -1000 where actno='act-001';
  update t_act set balance = balance +1000 where actno='act-002';

以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败
事务的存在是为了保证数据的完整性、安全性（insert delete update）

假设一个业务，需要先执行一条Insert，再执行update，最后执行delete

事务的特性：
ACID
A 原子性：事务是最小的工作单元，不可再分
C 一致性：事务必须保证多条DML语句同时成功或者同时失败
I 隔离性：事务A与事务B之间具有隔离
D 持久性：最终数据必须持久化到硬盘文件，事务才算成功的结束

事务之间的隔离：存在事务隔离的隔离级别

• 第一级别：读未提交（read uncommitted）
  对方的交易尚未提交，我们当前的交易可以读取对方的未提交数据。
  [En]

  The other party’s transaction has not been committed yet, and our current transaction can read the other party’s uncommitted data.

  读未提交存在脏读（Dirty Read）现象，表示读到了脏的数据
  未提交的事务，另一个线程用户也可以读取未提交的数据

  [En]

  Uncommitted transactions, another thread user can also read uncommitted data

• 第二级别：读已提交（read committed）
  我们可以在提交对方的交易后读取数据。
  [En]

  We can read the data after the transaction of the other party is submitted.

  解决了脏读现象
  提交读取的问题是不能重复读取(无法从头到尾读取相同的数据)

  [En]

  The problem with reading submitted is that it cannot be read repeatedly (unable to read the same data from beginning to end)

  只有在提交事务时，另一个线程的用户才能读取提交的数据。

  [En]

  Only when the transaction is committed can the user of another thread read the committed data.

• 第三级别：可重复读（repeatable read）
  解决了不可重复读的问题
  但问题是，读取的数据是一种错觉。
  [En]

  But the problem is that the data read is an illusion.

  即使数据已被另一个线程的用户删除，也可以一直读取数据，读取备份数据

  [En]

  The data can be read all the time, reading the backup data, even if the data has been deleted by the user of another thread

• 第四级别：序列化读/串行化读（serializable）
  解决了所有问题
  效率低，需要事务排队
  两个线程的用户不可同时进行事务操作，用户A进行操作事务，用户A必须提交事务，用户B才能进行事务操作，排队 oracle数据库默认的隔离级别是：第二级别
  mysql数据库默认的隔离级别是：第三级别

mysql事务默认情况下是自动提交的（只要执行任意一条DML语句则提交一次）

关闭自动提交 start transaction;(即开启事务)
 set global transaction isolation level read uncommitted;

设置全局的事务隔离级别 读未提交
 select @@global.tx isolation; 查看全局事务隔离级别

创建索引对象
create index 索引名称 on 表名（字段名）;

删除索引对象
drop index 索引名称 on 表名;

主键和具有unique约束的字段自动会添加索引
根据主键查询效率较高，
explain 查看该语句的解释

索引底层采用的数据结构是：B + Tree

    select ename from emp where ename = 'SMITH';
    通过索引转换为：
    select ename from emp where 物理地址 = '0x001';

select ename from emp where ename like '%A%';
模糊查询时，第一个通配符使用%，索引无效<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>When you blur a query, the first wildcard uses%, and the index is invalid</font>*</details>

从不同的角度看数据(同一张表，从不同的角度)

创建视图 create view myview as select empno,ename from emp;
删除视图 drop view myview;

视图的添加、删除、修改、查询都会影响原表数据。(原表的数据受视图影响，而不是直接操作的原表。)

只有DQL语句才能以视图对象的方式创建出来
视图的作用

• 查看可隐藏表的执行详情
  
  [En]

  View can hide the implementation details of the table*

• 保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD

将数据库中的数据导出  mysqldump 数据库名>E:\test.sql -uroot -proot  （DOS命令）导出数据库中的指定表  mysqldump 数据库名 emp>E:\test.sql -uroot -proot导入数据  create database 数据库名;  user 数据库名;  source E:\test.sql   （source，文件拖进来）

设计范式：设计表格的基础。根据三种范例设计的表格将不会有数据冗余。

三范式：

• 第一范式：任何表都应该有一个主键，每个字段的原子性不能再分开。
  
  [En]

  first paradigm: any table should have a primary key, and the atomicity of each field can no longer be separated.*

• 第二范式：所有非主键字段都完全依赖于主键，不能产生部分依赖。
  
  [En]

  second paradigm: all non-primary key fields are completely dependent on the primary key and cannot produce partial dependencies.*

• 第三种范式：所有非主键字段都直接依赖于主键，不能产生传递依赖。
  
  [En]

  the third paradigm: all non-primary key fields directly depend on the primary key and cannot produce transitive dependencies.

  口诀：
  关系表中的多对多、三个表、两个外键

  [En]

  Many-to-many, three tables, two foreign keys in relational tables

  一对多、两个表、多个表加上外键

  [En]

  One to many, two tables, multiple tables plus foreign keys*

在实际开发中，主要目的是满足客户的需求，有时会以冗余换取执行速度。

• rank() over(业务逻辑)

select name,score, rank() over(order by score desc) 'rank' from student
功能：找出指定条件后的排名，相同条件，相同排名，不连续排名。<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Function: find out the ranking after the specified conditions, the same conditions, the same ranking, discontinuous ranking.</font>*</details>
描述：以学生排名为例，使用此函数，分数相同的两个名次被打成平手。<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Description: for example, the ranking of students, using this function, the two places with the same scores are tied.</font>*</details>
    下一位同学空出所占的名次。即：1 1 3 4 5 5 7

• dense_rank() over(业务逻辑)

select name,score, dense_rank() over(order by score desc) 'rank'
功能：找出指定条件后的排名，相同条件，相同排名，不连续排名。<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Function: find out the ranking after the specified conditions, the same conditions, the same ranking, discontinuous ranking.</font>*</details>
说明：和rank() over 的作用相同，区别在于dense_rank() over 排名是密集连续的。
    例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学接着下一个名次。即：1 1 2 3 4 5 5 6

• row_number() over(业务逻辑)

select name,score, row_number() over(order by score desc) 'rank'
功能：找出指定条件后的排名，相同的条件也不同，排名是间歇性的、不连续的。<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Function: find out the ranking after the specified conditions, the same conditions are also different, the ranking is intermittent and discontinuous.</font>*</details>
注：此函数不需要考虑是否并列，即使根据条件查询的值相同，也会连续排序。<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Note: this function does not need to consider whether it is juxtaposed or not, and it will be sorted continuously even if the values queried according to the conditions are the same.</font>*</details>
    即：1 2 3 4 5 6

• 面试官对你的项目说了些什么？
  
  [En]

  what did the interviewer say about your project?

  A：我的项目中有一个模块。我负责哪些模块，这个模块使用了什么技术，我当时做这个遇到了什么困难，这个困难是怎么解决的？

  [En]

  A: there is a module in my project.. Which modules am I responsible for, what technologies are used in this module, what difficulties did I encounter in doing this at that time, and how did this difficulty be solved?*

• 您的项目的哪个部分已编入索引？
  
  [En]

  which part of your project is indexed?

  答：当时的业务是什么？你为什么会考虑使用索引？指标项的使用效率有哪些提高？

  [En]

  Answer: what was the business at that time? why did you consider using an index? what is the improvement in the efficiency of using index items?*

1.取得每个部门最高薪水的人员名称select  e.ename,t.deptno,t.maxsalfrom  (select    deptno,max(sal) maxsal  from    emp  group by    deptno) tjoin  emp eon  t.maxsal=e.sal and t.deptno=e.deptno;2.哪些人的薪水在部门的平均薪水之上  select    e.ename,e.sal,t.*  from    (select       deptno,avg(sal) avgsal     from       emp e     group by       deptno) t   join     emp e   on     e.sal>avgsal and e.deptno = t.deptno;3.取得部门中所有人的平均的薪水等级  select    e.deptno,avg(s.grade)  from    emp e  join    salgrade s  on    e.sal betweeen s.losal and s.hisal  group by    deptno;4.不用max函数，取得最高薪水  select    sal  from    emp  order by    sal desc  limit    0,15.取得平均薪水最高的部门的部门编号  select    deptno,avg(sal) avgsal,  from    emp  group by    deptno  order by    avgsal desc  limit    1;6.取得平均薪水最高的部门的部门名称  select    d.dname,avg(e.sal) avgsal  from    emp e  join    dept d  on    d.deptno = e.deptno  group by    d.dname  order by    avgsal desc  limit    1;

Original: https://www.cnblogs.com/wbustu/p/16304684.html
Author: 多少梦境难成真
Title: mysql笔记