SQLPractice 思维导图模板_ProcessOn思维导图、流程图

数据库原理

索引

参考博客

数据库索引原理，及MySQL索引类型

索引类型

普通索引

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length

创建索引

方式一

CREATE INDEX indexName ON mytable(username(length));

方式二

ALTER mytable ADD INDEX [indexName] ON (username(length))

方式三

CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);

删除索引

DROP INDEX [indexName] ON mytable;

主键索引

不允许有空值。一般是在建表的时候同时创建主键索引

创建索引

CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
PRIMARY KEY(ID)
);

唯一索引

索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一

创建索引与普通索引不同的是

INDEX全部换成UNIQUE INDEX

组合索引

创建索引

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

相当于分别建立了下面三组组合索引

usernname,city,age

usernname,city

usernname

索引条件

只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引

索引的缺点

对表进行INSERT、UPDATE和DELETE，
会降低更新表的速度

因为更新表时，MySQL不仅要保存数据，
还要保存一下索引文件

建立索引会占用磁盘空间的索引文件

使用索引的注意事项

索引尽量不要包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，
那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL

只要列中包含有 NULL 值都将不会被包含在索引中，
复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。
所以在数据库设计时不要让字段的默认值为 NULL

使用短索引

索引列排序

like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。
like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

如果通配符 % 不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀
在 like “value%” 可以使用索引，但是 like “%value%” 不会使用索引，走的是全表扫描

不要在列上进行运算

不使用NOT IN和<>操作

尽量使用覆盖索引

不要在列上进行运算

如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描
例如 select * from user where YEAR(birthday) < 1990
可以改造成 select * from users where birthday <’1990-01-01′

范围查询

mysql 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。
范围列可以用到索引，但是范围列后面的列无法用到索引。
即，索引最多用于一个范围列，因此如果查询条件中有 两个范围列 则无法全用到索引

尽量选择区分度高的列作为索引

区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，
唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0。
一般需要 join 的字段都要求区分度 0.1 以上，即平均 1 条扫描 10 条记录

索引原理

聚簇索引与非聚簇索引

聚簇索引与非聚簇索引（也叫二级索引）

MySQL中Innodb的聚簇索引和非聚簇索引

数据库索引原理，及MySQL索引类型

覆盖索引是非聚簇索引基于组合索引

索引下推是如何实现的

索引上使用聚合函数是否会导致失效

group by是否走索引

索引失效的场景

数据库锁

幂等

实现

如何加锁

MySQL常用引擎有MyISAM和InnoDB，
+ InnoDB是mysql默认的引擎。
+ MyISAM不支持行锁，而InnoDB支持行锁和表锁

显式加锁

上共享锁（读锁）的写法：lock in share mode，例如：
select math from zje where math>60 lock in share mode；

上排它锁（写锁）的写法：for update，例如：
select math from zje where math >60 for update；

表锁
不会出现死锁，发生锁冲突几率高，并发低

MyISAM

查询

会自动给涉及的所有表加读锁

增删改

自动给涉及的表加写锁

表级锁有两种模式

表共享读锁
表独占写锁

读锁会阻塞写，写锁会阻塞读和写

MyISAM不适合做写为主表的引擎，因为写锁后，其它线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞

行锁
会出现死锁，发生锁冲突几率低，并发高

数据库应用

分库分表

操作

分库分表下的唯一ID

数据库事务

数据库内核

innodb_buffer

Mysql 参数 innodb_buffer_pool_size

三大范式

约束

数据库五大约束

主键约束、唯一约束、检查约束、默认约束、外键约束

软件工程中的约束

约束需求=业务环境因素 + 使用环境因素 + 构建环境因素 + 技术环境因素

多重实际开发条件或者需求限制

数据库语句的执行流程

mybatis的执行流程

路径跳转

软件基础知识
SoftwareBasic

Java学习笔记
Java_Practice

Go学习笔记
GO_Practice

高效开发工具
EfficientDevTools

数据结构和算法
AlgorithmPractice

机器学习

高可用/高并发/高性能
解决方案(3H)

数据库(原理和指令)

中间件(指令和原理)

踩坑记录(复盘和总结)

工作项目UML图汇总

数据库应用软件操作

SQLServer2008导出表数据为SQL脚本

数据库名-->右键任务-->生存脚本(注意是选择数据还是选择结构)

数据库太大，对日志文件进行清理

数据库属性中的模式设置为简单

然后右键收缩道数据库进行日志文件清回理

如果选择数据库清理不明显，
就直接选file，对log进行压缩，选第二个选项
压缩至xx%

清理完成后再将数据库模式改回原模式

存储过程和执行计划

mysql的sql执行计划

EXPLAIN语法

table

显示这一行的数据是关于哪张表的

type

显示连接使用了何种类型。
从最好到最差的连接类型为
const、eq_reg、ref、range、index和ALL

system：表只有一行：system表。这是const连接类型的特殊情况。

const ：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。
因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待。

eq_ref：在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，
它在查询使用了索引为主键或惟一键的全部时使用。

ref：这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分
（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。
这个类型严重依赖于根据索引匹配的记录多少—越少越好。

range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况。

index：这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）。

ALL：这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。

possible_keys

显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key

实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。
这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len

使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

rows

MYSQL认为必须检查的用来返回请求数据的行数

Extra

关于MYSQL如何解析查询的额外信息

Distinct ：一旦mysql找到了与行相联合匹配的行，就不再搜索了。

Not exists ：mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。

Range checked for each Record（index map:#）：没有找到理想的索引，因此对从前面表中来的每一个行组合，
mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。

Using filesort ：看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。
它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。

Using index ：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，
这发生在对表的全部的请求列都是同一个索引的部分的时候。

Using temporary ：看到这个的时候，查询需要优化了。这里，mysql需要创建一个临时表来存储结果，
这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。

Where used ：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。
如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题。

示例

储存过程

示例

CREATE PROCEDURE insert_person()
begin
declare c_id integer default 1;
while c_id<=100000 do
insert into person values(c_id, concat('name',c_id), c_id+100, date_sub(NOW(), interval c_id second));
set c_id=c_id+1;
end while;
end

Mysql实现级联操作（级联更新、级联删除）

如何将Excel数据转换为SQL脚本

输入="",在双引号里面写入INSERT语句脚本，这里举例oracle语句的insert into语句，格式如
INSERT INTO USER(USER_CODE,USER_NAME,USER_TYPE,IS_ENABLED,IN_DATE)VALUES('"&&"','"&&"','"&&"','"&&"',TO_DATE('"&&"','YYYY-MM-DD'));

注意 ‘’ 号

注意“ = ”号

insert into () 可以不用写内容

MySQL学习

1

2

2.1

3

推荐书籍

SQL必知必会

MySQL技术内幕：innoDB存储引擎

SQL练习网站数据库指令练习：SQLZOO

数据库操作指令

显示所有数据库
创建数据库
使用某个数据库
查看库中的所有表
查看某表的创建语句
查看数据库版本号

show databases;
create database ljdatabase;
use student;
show tables;
show create table <table_name> \G;
select version();

创建库语句

Create Table: CREATE TABLE `stu` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=gbk

基础指令

增删改查/表/库

增

INSERT INTO 表名称 VALUES (值1, 值2,....)

INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

删

删值

DELETE FROM 表名称 WHERE 列名称 = 值

DELETE FROM Person WHERE LastName = 'Wilson'

删表

drop table xxxx

删库

drop database RUNOOB

改

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'

注意

select 和 update 合用

UPDATE ddd.xxx
set userid = 12
WHERE id in (
    SELECT a.id from (
        SELECT id from ddd.xxx  Where dede = 12
    ) a
)

查

SELECT 列名称 FROM 表名称 WHERE 列运算符值

SELECT * FROM Persons WHERE City='Beijing'

查询某表数据

select * from 数据库.表名

分类

group by

GROUP表示分组，BY后面写字段名，就表示根据哪个字段进行分组

GROUP BY必须得配合聚合函数来用，分组之后你可以计数（COUNT），求和（SUM），求平均数（AVG）

示例

SELECT dept_no as 部门, count( emp_no) as 人数
FROM dept_emp
WHERE to_date = '9999-01-01'
GROUP BY dept_no

统计每日签到人数
SELECT COUNT(uid) as signPeople , FROM_UNIXTIME(sign_index) as signday
FROM t_user_sign
Where XXX = ‘a'
GROUP by sign_index DESC

HAVING

例子

示例

SELECT
( SELECT d.dept_name FROM departments d WHERE de.dept_no = d.dept_no ) AS 部门,
count( de.emp_no ) AS 人数

FROM dept_emp de
WHERE de.to_date = '9999-01-01'
GROUP BY de.dept_no
HAVING count( de.emp_no ) > 30000

每个部门人数都有了，那如果我们想要进一步知道员工人数大于30000的部门是哪些，这个时候就得用到HAVING了

order by

SELECT name,age FROM student ORDER BY age DESC, name ASC;

查询名称，年龄来自学生表，按照年龄降序，名称升序进行排序；关键字 DESC(descending) 意指降序，

字母默认Z-A; ASC（ascending）意指升序，字母默认A-Z；多列情况下，每个列后面指定使用DESC，使用逗号（,）隔开，如果不写，默认升序；

SELECT name,age FROM student ORDER BY 2 DESC, 1 ASC;

按位指查询字段的位置，2 对应字段age,1对应字段name；

范围查询

limit 和 top

SELECT TOP 2 name FROM student

SELECT name FROM student LIMIT 2;

select * from table limit 10; // limit n; 返回查询结果的前n条数据
//等同于
select * from table limit 0,10; //limit offset, n; 返回从offset + 1开始的n条数据

//是从数据库中的第二条数据开始查询两条数据，即第二条和第三条。
selete * from testtable limit 2 offset 1;

SELECT * FROM student WHERE age BETWEEN '18' And '23';

SELECT * FROM student WHERE age IN (18,23,25);

其功能跟OR类似，一个IN 就相当于好多个OR

SELECT * FROM student WHERE NOT age='25';

NOT 操作符表是否定；其跟在WHERE后面功能类似<>;

其他函数

DATEDIFF ( datepart , startdate , enddate )

SELECT DATEDIFF( Day, 2009-8-25 12:15:12', 2009-9-1 7:18:20')

replace和replace into

SQL中like与通配符的使用

字段拼接

SELECT concat('你好啊',name,'327今天心情怎么样') FROM student WHERE id = '1' ;

去除空白字符串

SELECT RTRIM(' 哥，今天管饱 ') FROM student WHERE id = '1' ;

RTRIM(STR) 函数是去掉右边的字符串；

TRIM(STR)是去掉字符串两边的空白字符；

LTRIM(STR)是去掉字符串左边的空白字符；

计算

SELECT 2 * 8;

子查询和联结表

SELECT
userName
FROM
customer
WHERE
userId = ( SELECT userId FROM `order` WHERE orderName = '乖乖订单' )

join. on

内连接（inner join）

自然联结与标准的联结不同就是只返回值唯一的列，不会返回重复的列

区别在于 SELECT userName, orderName

还是 SELECT *

外联结（左右）

SELECT
*
FROM
`order`
RIGHT OUTER JOIN customer ON ( customer.userId = `order`.userId );

右外联结是指相对于 OUTER JOIN 右边的表，

那么这会查询出右边表的所有数据和根据等值条件匹配左边表的数据，

如果左边表的数据不匹配，那么其返回列的值是NULL充当；

组合查询

SELECT
userId
FROM
customer UNION
SELECT
userId
FROM
`order`

插入检索数据

INSERT INTO `user` ( id, `name`)
SELECT id, `name` FROM student WHERE id = '4';

mysql中insert…select 死锁

复制表

SELECT id , `name` INTO student_copy FROM student;

CREATE TABLE student_copy AS
SELECT * FROM student;

数据库怎么清空一个表中所有数据

1、delete from tableName2、truncate table tableName

相较而言，bai完全删除一个du表所有记录，truncate 比 delete速度快的zhi多。

1.DELETE・DML语言bai・可以回退・可以有条件的删除。 DELETE FROM 表名WHERE 条件
2.TRUNCATE TABLE・DDL语言・无法回退・默认所有的表内容都删除・删除速度比delete快。 TRUNCATE TABLE 表名

数据库指令中阶

判断字段值是否为NULL

判断字段是否为null
select * from table where c is null
select * from table where c is not null

判断字段是否为空
select * from table where c=''
select * from talbe where c<>''

如何设置mysql 主键自动增长

修改表时设置主键自增长：
ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

修改表时删除主键自增长：
ALTER TABLE stu CHANGE sid sid INT;

怎样查看数据库表主键是否是自增长

show create table stu;

清空表

truncate table 表名

表关联查询

join方式

select 待查找值
form
(select * from xx where id = 2) t1 //()内是条件，由条件组成表t1
inner join
(select id from xxx where name like '%520%') t2 //()内是条件，由条件组成表t2
on t1.param like '%'+t2.id+'%' //on 是筛选条件,字符串拼接成like的条件

exist方式

select 待查找值
from [xxdatabase].[xxtable] t1
where 筛选条件1 and exists () //()内写筛选条件2,该条件可以关联t1

//例如：筛选条件2可以写成：
(select [id]
from [yydatabase].[yytable]
where name like '%cba%' and t1.param like concat('%',[id],'%'))
//用concat拼接字符串，拼接串中的[id]对应查村条件的id
//也可以把查询的表命名为t2，用t2.id去拼接字符串

exists 和 not exists 和 in

in引导的子句只能对一个字段进行限制

select * from A where sid in (1,2,3)

对多个字段进行限制 exist

select *
from A
where exists
(select 1 from B where A.sid=B.sid and A.tid=B.tid)

原理

exists做为where条件时,是先对where 前的主查询询进行查询,
然后用主查询的结果一个一个的代入exists的查询进行判断，
如果为真则输出当前这一条主查询的结果，否则不输出

子查询会分成两种情况

子查询与外表的字段有关系时

select * from A
where exists
(select 1 from B where A.sid=B.sid and A.tid=B.tid)

它先执行A表的查询,再将查询结果一条一条放到B表的条件中去查询,如果存在,则显示此条

子查询与外表的字段没有任何关联

select * from A
where exists
(select * from B where B.id=‘条件‘)

只要子查询的条件成立，就会查询出表1中的所有记录，
反之，如果子查询中没有查询到记录，则表1不会查询出任何的记录

如果not exists子查询只有自己本身的查询条件，这样只要子查询中有数据返回，就证明是false，
结果在整体执行就无返回值；一旦跟外面的查询关联上，就能准确查出数据

表全字段条件搜索

SELECT * FROM hfw_dd WHERE concat(需要搜索的字段) LIKE CONCAT(关键字)

例子1:全字段关键字搜索'呵呵'
SELECT * FROM hfw_dd WHERE CONCAT(id,name,china,english) like CONCAT('%', '呵呵', '%')

例子2:全字段关键字搜索'小',在结果的基础上再搜索 70
SELECT * FROM hfw_dd WHERE CONCAT(id,name,china,english) like CONCAT('%', '小','%', '%', 70, '%')

例3: id小于4,全字段关键字搜索'小',在结果的基础上再搜索 70
SELECT * FROM hfw_dd WHERE id < 4 AND CONCAT(id,name,china,english) like CONCAT('%', '小','%', '%', 70, '%')

库全表字段条件搜索

数据库中某两个字段保持一致，
比如使用NEWID函数

DECLARE @myid uniqueidentifier
SET @myid = NEWID()

insert into table1 zhangsan
values (@myid,"zhangsan",@myid)

mysql安装管理

安装和卸载

Mac brew 安装mysql

安装中的问题

brew下载mysql提示
Error: Failed to download resource "protobuf"

aliyun源

//第一处修改：
cd "$(brew --repo)"
git remote set-url origin https://mirrors.aliyun.com/homebrew/brew.git
//第二处修改
cd"$(brew --repo)/Library/Taps/homebrew/homebrew-core"
git remote set-url origin https://mirrors.aliyun.com/homebrew/homebrew-core.git
//第三步
brew update

ustc源

cd /usr/local/Homebrew
git remote set-url origin git://mirrors.ustc.edu.cn/brew.git
cd "$(brew --repo)/Library/Taps/homebrew/homebrew-core"
git remote set-url origin git://mirrors.ustc.edu.cn/homebrew-core.git
brew update

~/.bash_profile 也需要修改

Ubuntu16.04上安装MySQL

启动mysql

service mysql start

重启MySQL服务

service mysql restart

进入mysql shell界面：

mysql -u root -p

退出

exit

问题

安装完成后记得修改mysql字符集

(1)编辑配置文件。sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

(2)在[mysqld]下添加一行character_set_server=utf8。如下图

连接问题

连接mysql数据库出现，
Connection to @localhost failed. [08001] Could not create connection to database

修改ubuntu的时区

Host：localhost（如果是本机没有必要改）
user：mysql用户名
password：MySQL密码
将url后面加上?serverTimezone=GMT

主机连接虚拟机MySQL报错：
Can't connect to Mysql server on '192.168.33.10' (10061 "Unknown error")

查找bind-address所在的文件，并修改

主机无法连接虚拟机上的mysql
hostxxx is not allowed to this mysql server”；

GRANT ALL PRIVILEGES ON *.* TO 'root'@'MS-HOLFSSIPZZME' IDENTIFIED BY '123456' WITH GRANT OPTION;

卸载

安装

brew安装

注意：brew install mysql@5.7

版本

查看mysql版本的几种方法

1：在终端下：mysql -V(大写)

2：在mysql中：mysql> status;(引号要加)

3：在help里面查找
$ mysql --help | grep Distrib

4：使用mysql的函数
mysql> select version();

启动

1、mysql.server start

2、mysql -h localhost -u root -p