2021-07-06 MySQL笔记

---

前言

主要是学习MySQL数据库时的一些笔记，加强记忆，并获取重点，方便以后复习

---

一、基本概念

数据库（DB）：按照一定格式存储的一些文件的组合。（存储数据的仓库，一堆文件）
数据库管理系统（DBMS）：管理数据库中的数据，可以对数据库中的数据进行增删改查。（常见的有：MySQL、Oracle、MS、SqlServer、DB2、sybase等）
SQL：结构化查询语句。（程序员编写SQL语句，DBMS执行SQL语句，完成对数据库的增删查改）
三者关系：DBMS－执行－SQL－操作－DB
端口号（port）：任何一个软件、应用都会有，端口号是唯一代表。端口号是用来定位计算机中的某个服务。（MySQL默认是3306）

二、安装

用户名为root，为默认的。
激活账户：可以远程访问。

三、卸载

1. 双击安装包进行卸载
2. 删除目录：C盘中的ProgramData中的MySQL文件夹 和 ProgramFile(x86)中的MySQL文件夹 删除。

四、服务启动和关闭

通过cmd进行启动服务
启动：net start mysql
关闭：net stop mysql

五、常用命令

1.mysql相关操作

登录：mysql -u root -p
退出：exit
终止：\c
查看版本号：select version();

2. 数据库操作

查看包含了哪些数据库：show databases;（最后又分号，而且是复数）
使用数据库：use test;（test是已有的数据库）
创建数据库：create database hrk;（创建一个名为hrk的数据库）
导入数据库：source c:\**.sql（navicat调用source语句会出错，控制台不会；路径中不能包含中文；必须先use了该数据库才能导入数据）
查看当前使用的的数据库：select database();

3.表的操作

数据库中最基本的单元是表table，数据库中是以表格的形式进行标识数据。
其中包含行和列：行（row）：数据/记录；列（column）：字段（每一个字段都有字段名、数据类型、约束等属性）；
查看某个数据库下有哪些表：show tables;
查看表中数据：select * from tablename;
只看表中结构，不看表中数据：desc tablename;
修改表名：ALTER TABLE emp_temp RENAME TO emp_temp2;
清除表中数据：delete from tablename

4、数据的操作

1、mysql执行顺序

(8)  SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1)  FROM <left_table> 
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition> 
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list> 
(6)  WITH {CUBE | ROLLUP} 
(7)  HAVING <having_condition> 
(10) ORDER BY <order_by_list> 

2、in和exists

in相当于多个or叠加，in查询的子条件结果必须只有一个字段

select *
from table_name
where col_name in (value1, value2,...);

exists对外表进行逐条查询，每次查询都会查看exists条件语句。当exists的条件语句能够返回记录行时，条件为真，返回当前loop到的这条记录。exists像是条件语句，当能返回结果集则为true，不能返回结果集就是false。

select *
from table_a a
where exists (select 1 from table_b b where b.id = b.id);

in 和 exists 的区别：in只执行一次，而exists执行n次（外表行数）； 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用 in， 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用 exists。not exists 比 not in 效率高。

5、子查询

WHERE子句中进行使用查询
HAVING子句是对分组统计函数进行过滤的子句，也可以在HAVING子句中使用子查询
FROM子查询就是将一个查询结构（一般多行多列）作为主查询的数据源
SELECT子查询在SELECT子句中使用查询的结果(一般会和dual空表一起使用)

SELECT (SELECT COUNT(*) FROM EMP WHERE JOB = 'SALESMAN')/(SELECT COUNT(*) FROM EMP)
FROM DUAL;

将主查询的数据带到子查询中验证，如果成功则返回true，否则发水false。主查询接收true是就会显示这条数据,flase就不会显示。

6、插入

insert into
select

可以把select的结果插入，而且可以同时插入很多行

select *
from table1
insert table2

把一个表的内容加入另一个表

7、修改

update tablename
set ****
where ***

8、建立表格

直接建立

CREATE TABLE 表
( 
     字段名称 varchar(4) primary key,
     字段名称 varchar(4) default('设定的默认值'),
     字段名称 date default('设定的默认值')
)

根据已有表格建立

-----复制表结构及数据到新表
CREATE TABLE T_NEW SELECT * FROM T_OLD
-----只复制表结构到新表
CREATE TABLE T_NEW SELECT * FROM T_OLD WHERE 1=2

9、字段

插入新字段
alter table 表名 add [column] 字段名 数据类型 [列属性][位置]
修改原有字段
alter table 表名 modify column 字段名 字段类型
删除字段
alter table 表名 drop column 字段名
添加约束：
alter table emp_temp2 modify column EMPLOYEE_ID decimal(6,0) primary key;

• 删除主键:alter table 表名 drop primary key;
• 删除非空约束:alter table 表名 modify 字段名 null;
• 删除外键:alter table 表名 drop foreign key fk_name;
• 删除唯一键:alter table 表名 drop index index_name;
• 添加表、列级约束:alter table 表名 modify (column) 字段名 字段类型 新约束
• 添加表级约束: alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用]

十、视图

六、SQL语句的分类（记一下）

DQL：数据查询语言（带有select关键字的都是查询语言）
DML：数据操作语言（对表中数据进行增删改的都是DML）（insert、delete、update）
DDL：数据定义语言（带有create、drop、alter的都是DDL，DDL主要操作的是表的结构而不是表中数据）
TCL：事务控制语言（事务提交：commit；事务回滚：rollback）
DCL：数据控制语言（权限grant、撤销权限revoke）

七、一些杂项

concat连接字符串：CONCAT(‘Hello’,‘World’)——HelloWorld
别名中如果有特殊字符需要把别名加上双引号
mysql的默认字符集是latin1
注意代码美化：都大写或者都小写，代码的对齐，缩进
全模糊查询（like）会破坏索引
查询默认是升序
降序排列：order by lieming desc
ORDER BY department_id, salary DESC句子的理解：先升序id，再每个组中用salary降序
where子句中不能使用组函数
在进行更新和修改时，一定要注意where条件
使用 Explain/ DESC来分析SQL的执行情况
使用 SHOW PROCESSLIST来查看当前MySQL服务器线程执行情况，是否锁表，查看相应的SQL语句
设置 my.cnf 中的 long-query-time 和 log-slow-queries 能够记录服务器那些SQL执行速度比较慢
另外有用的几个查询：SHOW VARIABLES、SHOW STATUS、SHOW ENGINES
使用 DESC TABLE xxx来查看表结构，使用 SHOW INDEX FROM xxx来查看表索引
使用 LOAD DATA导入数据比INSERT INTO快多了
SELECT COUNT(*) FROM Tbl在InnoDB中将会扫描全表MyISAM 中则效率很高

八、表连接

1、笛卡尔积

又叫做交叉连接，其结果就是所有列相加，行数相乘。
表连接一共四种：等连接、非等连接、外连接、自连接

2、等连接（内连接）

一般语句

select * 
from test1
inner join test2 on(test1.i=test2.i);

On后的语句是连接条件，当连接条件中的列名称相同时，可以使用using，但是在使用using语句时，在select语句中的列名前不能出现修饰符：

select * 
from test1
inner join test2 using( i);
#不会报错
select test1.name
from test1
inner join test2 using( i);
#会报错

多表连接时，再后面继续加inner join即可

3、非等连接

相当于在交叉连接的基础上对查出的值进行筛选。

4、外连接

左外连接，右外连接，全外连接

5、自连接

九、常见函数

单行函数分为五种类型：字符函数、数值函数、日期函数、转换函数、通用函数

1、字符函数（知道有哪些功能，具体用的时候再查）

• lower：转换为小写
• upper：转换为大写
• concat：连接两个字符串
• substr：截取字符串
• length：获取字符串长度
• instr：获取某个字符在字符串中的位置
• LPAD|RPAD：设置数字位数，多出来的用其他字符代替
• TRIM：删除字符串中的某个字符
• REPLACE：amcd
  从第n个字符截取到最后一个字符substr(n, length(string))

2、数字函数

• round：四舍五入，round(453.236234, 2)——453.24
• truncate：截断，truncate(453.236234, 2)——453.23如果是保留到整数部分，就使用负数，如保留到千位truncate(213432.35, -3)——213000
• mod：求余，mod(1600，300)——100
• sign：判断大于零小于零等于零sign(3.6)——1，sign(0)——0，sign(-4)—— -1
• floor：向下取整floor(4.3)——4

3、日期函数

获取时间差：datediff(date,date)这个只能计算天数，还有timestampdiff(year, date, date)
当前日期：now()记得加（）
时间相加：DATE_ADD(now(), INTERVAL 1 MONTH)可以加DAY，MONTH，YEAR
改变日期类型：CONVERT(VARCHAR(10),GETDATE(),110)第一个参数是获取的日期格式长度，第二个参数是日期，第三个参数是日期格式

4、聚合函数

除了 COUNT 以外，聚合函数忽略空值。
聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。
所有聚合函数都具有确定性。任何时候用一组给定的输入值调用它们时，都返回相同的值。
标量函数：只能对单个的数字或值进行计算。主要包括字符函数、日期/时间函数、数值函数和转换函数这四类。
1、求个数/记录数/项目数等：count()
2、求某一列平均数 :avg()
3、求总和，总分等：sum()–必须为数字列
4、求最大值，最高分，最高工资等：max()
5、求最小值，最低分，最低工资等：min()

十、存储引擎

1、MyISAM

MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。
MyISAM特点：

• 数据存储方式简单，使用 B+ Tree 进行索引
• 使用三个文件定义一个表：.MYI .MYD .frm
• 少碎片、支持大文件、能够进行索引压缩
• 二进制层次的文件可以移植 (Linux Windows)
• 访问速度飞快，是所有MySQL文件引擎中速度最快的
• 不支持一些数据库特性，比如 事务、外键约束等
• Table level lock，性能稍差，更适合读取多的操作
• 表数据容量有限，一般建议单表数据量介于 50w–200w

2、InnoDB

InnoDB：用于事务处理应用程序，具有众多特性 ，包括ACID事务支持。
只有这个存储引擎包含事务
InnoDB特性：

• 具有较好的事务支持：支持4个事务隔离级别，支持多版本读
• 行级锁定：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响
• 读写阻塞与事务隔离级别相关
• 具有非常高效的缓存特性：能缓存索引，也能缓存数据
• 整个表和主键以Cluster方式存储，组成一颗平衡树
• 所有Secondary Index都会保存主键信息

3、如何选择

MyISAM适合：
（1）做很多count 的计算；
（2）插入不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择；
（3）没有事务。
InnoDB适合：
（1）可靠性要求比较高，或者要求事务；
（2）表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定数据引擎的创建；
（3）如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表；
（4）DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的 删除；
（5）LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

十一、事务

原子性
一致性
持久性
隔离性

十二、优化

优化方式：

• 设计合理的数据表结构：适当的数据冗余
• 对数据表建立合适有效的数据库索引
• 数据查询：编写简洁高效的SQL语句

高效SQL：

• 能够快速缩小结果集的where条件写在前面，如果有恒量条件（即固定值），也尽量写在前面
• 尽量避免使用 GROUP BY、DISTINCT 、OR、IN 等语句的使用，避免使用联表查询和子查询，因为将使执行效率大大下降
• 能够使用索引的字段尽量进行有效的合理排列，如果使用了联合索引，请注意提取字段的前后顺序（什么叫做合理的？不知道）
• 针对索引字段使用 >, >=, =, <, <=, IF NULL和BETWEEN 将会使用索引， 如果对某个索引字段进行 LIKE 查询，使用 LIKE ‘%abc%’ 不能使用索引，使用 LIKE ‘abc%’ 将能够使用索引。
• 如果在SQL里使用了MySQL部分自带函数，索引将失效，同时将无法使用 MySQL 的 Query Cache，比如 LEFT(), SUBSTR(), TO_DAYS()，DATE_FORMAT(), 等，如果使用了 OR 或 IN，索引也将失效
• 使用 Explain 语句来帮助改进我们的SQL语句
• 不要在where 子句中的“=”左边进行算术或表达式运算，否则系统将可能无法正确使用索引
• 尽量不要在where条件中使用函数，否则将不能使用索引
• 避免使用 select *, 只取需要的字段
• 对于大数据量的查询，尽量避免在SQL语句中使用order by 字句，避免额为的开销，替代为使用ADO.NET 来实现。
• 如果插入的数据量很大，用select into 替代 insert into 能带来更好的性能
• 采用连接操作，避免过多的子查询，产生的CPU和IO开销
• 只关心需要的表和满足条件的数据

十三、设计原则

1、表结构设计原则

• 表名：ORACLE中限制为30位，但是设计时最长最多27位，因为还需要设置索引；索引的命名规则：tablename_pk，索引的最大长度为30位。
• 选择合适的数据类型，能够定长尽量定长。
• 使用ENUM而不是使用VARCHAR，ENUM类型时非常快和紧凑的，实际上保存的时TINYINT，但是外表上显示为字符串。
• 不要使用不能加索引的关键字段，如text类型
• 为了避免联表查询，可以适当进行数据冗余。
• 选择何种引擎，MyISAM还是InnoDB
• 表中需要有主键，有五个字段是必须的，关于表的创建和修改相关信息。
• 最好每个字段都设定default值。

2、索引建立原则

• 一般针对数据分散的关键字进行建立索引，比如ID、QQ， 像性别、状态值等等建立索引没有意义
• 字段唯一，最少，不可为null
• 对大数据量表建立聚集索引，避免更新操作带来的碎片。
• 尽量使用短索引，一般对int、char/varchar、date/time 等类型的字段建立索引
• 需要的时候建立联合索引，但是要注意查询SQL语句的编写，看情况
• 谨慎建立 unique 类型的索引（唯一索引）
• 大文本字段不建立为索引，如果要对大文本字段进行检索，可以考虑全文索引
• 频繁更新的列不适合建立索引
• order by 字句中的字段，where 子句中字段，最常用的sql语句中字段，应建立索引。
• 唯一性约束，系统将默认为该字段建立索引。
• 对于只是做查询用的数据库索引越多越好，但对于在线实时系统建议控制在5个以内。
• 索引不仅能提高查询SQL性能，同时也可以提高带where字句的update，Delete SQL性能。
• Decimal 类型字段不要单独建立为索引，但覆盖索引可以包含这些字段。
• 只有建立索引以后，表内的行才按照特地的顺序存储，按照需要可以是asc或desc方式。
• 如果索引由多个字段组成将最用来查询过滤的字段放在前面可能会有更好的性能。
• 适当使用临时表或表变量
• 对于连续的数值，使用between代替in
• where 字句中尽量不要使用CASE条件
• 尽量不用触发器，特别是在大数据表上（项目中不允许使用触发器，使用时需要想技术总监和项目经理请示，一般在Java应用层中进行相关编写）
• 更新触发器如果不是所有情况下都需要触发，应根据业务需要加上必要判断条件
• 使用union all 操作代替OR操作，注意此时需要注意一点查询条件可以使用聚集索引，如果是非聚集索引将起到相反的结果
• 循环中尽量不要写东西
• 当只要一行数据时使用 LIMIT 1
• 尽可能的使用 NOT NULL填充数据库
• 拆分大的 DELETE 或 INSERT 语句
• 批量提交SQL语句

十三、like

1、多个字符

%
like '%x'找到以x结尾的字符串

2、单字符

？
like '?x'找到以x结尾，且长度为2的字符串
[]
like [a-z]x找到以a到z开头，以x结尾的，长度位2的字符串

3、不包含

[!xxx]
like [!0-9]不包含数字

十四、正则表达式

MySQL采用Henry Spencer的正则表达式实施，其目标是符合POSIX 1003.2。

模式	什么模式匹配
^	字符串的开始
$	字符串的结尾
.	任何单个字符
[. . . ]	在方括号内的字符列表
[^ . . . ]	非列在方括号内的任何字符
p1|p2|p3	交替匹配任何模式p1，p2或p3
*	零个或多个前面的元素
+	前面的元素的一个或多个实例
{n}	前面的元素的n个实例
{m , n}	m到n个实例前面的元素

十五、case、if

1、case

SELECT 
    CASE WHEN salary <= 500 THEN '1' 
    WHEN salary > 500 AND salary <= 600  THEN '2' 
    WHEN salary > 600 AND salary <= 800  THEN '3' 
    WHEN salary > 800 AND salary <= 1000 THEN '4' 
    ELSE NULL END salary_class, -- 别名命名
    COUNT(*)  
FROM    Table_A 

2、if

if(判断语句，x1， x2)：判断语句为真，则返回值为x1，如果为假返回x2

练习

• 建议先建立ER图，再进行做题
• 主键、外键的相关含义
• null value
• 7.显示工作类别非’PRESIDENT’及’MANAGER’的员工数据
• 'S*'和’S%'的区别
• 结合两个字段成为新字段
• 从第n个字符截取到最后一个字符substr(n, length(string))
• 时间的计算差datediff(date,date)
• 显示当月的天数，试写出两种以上方法

select datediff(DATE_ADD(now(), INTERVAL 1 MONTH), now());

• 总计就是在group by后面加个with rollup
• 阶层关系是什么
• between num and num
• dual 空表

按部门分组，平均薪资大于8000，并且取出这些大于8000的人；
常见函数的运用
日期的格式规范
学习存储引擎，特性和什么时候使用该引擎更好
数据库的三范式
聚集索引
短索引
临时表和表变量
触发器：对性能影响较大
LIMIT 1
LOAD DATA

1.由emp表格中，找出为主管的数据 ，此为主管数据
10.同范例8，当部门小计时JOB字段显示’ALL JOB’，当总计时detpno字段显示’ALL DEPT’ 及JOB字段显示’ALL JOB’

DATE_ADD
subdate
sign
FLOOR
ROUND
convert((SALARY/1000),signed)
各种连接方法的区别，什么时候用哪一种连接方式。
count(1), count(*), count(id)有什么区别

8.同上例，找出主管工作类型不为’MANAGER的员工数据’
select a.*
from employees a
inner join jobs c on a.JOB_ID=c.JOB_ID
where exists(select 1 from employees b where b.MANAGER_ID=a.EMPLOYEE_ID and c.job_title not like ‘%manager%’);
用in好还是exists好，去看两者的区别

单行函数

排名：dense_rank() over(partition by e.DepartmentId order by e.salary desc) as rk

count(*) over(partition by

ROW_NUMBER()：窗口函数中partition by 列分组后，每个组的序号
SUM()：窗口函数中partition by 列分组后，每个组的指定列的求和
AVG()：窗口函数中partition by 列分组后，每个组的指定列的平均值

select *,
ROW_NUMBER() over(partition by name order by subject) as no,
SUM(sorce) over(partition by name) as Total,
avg(sorce) over(partition by name) as Avg_sorce  from test