SQLPractice
2022-03-25 09:44:48 0 举报AI智能生成
登录查看完整内容
为你推荐
查看更多
抱歉，暂无相关内容 SQLPractice
作者其他创作
大纲/内容
数据库索引原理，及MySQL索引类型
参考博客
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length
CREATE INDEX indexName ON mytable(username(length));
方式一
ALTER mytable ADD INDEX [indexName] ON (username(length))
方式二
方式三
创建索引
DROP INDEX [indexName] ON mytable;
删除索引
普通索引
不允许有空值。一般是在建表的时候同时创建主键索引
主键索引
索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一
INDEX全部换成UNIQUE INDEX
创建索引与普通索引不同的是
唯一索引
usernname
相当于分别建立了下面三组组合索引
组合索引
只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引
索引条件
索引类型
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
对表进行INSERT、UPDATE和DELETE，会降低更新表的速度
建立索引会占用磁盘空间的索引文件
索引的缺点
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL
只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为 NULL
索引尽量不要包含有NULL值的列
使用短索引
索引列排序
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
如果通配符 % 不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀在 like “value%” 可以使用索引，但是 like “%value%” 不会使用索引，走的是全表扫描
like语句操作
不要在列上进行运算
不使用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的聚簇索引和非聚簇索引
聚簇索引与非聚簇索引
覆盖索引 是 非聚簇索引基于 组合索引
索引下推是如何实现的
索引上使用聚合函数是否会导致失效
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
分库分表
数据库事务
Mysql 参数 innodb_buffer_pool_size
innodb_buffer
数据库内核
三大范式
主键约束、唯一约束、检查约束、默认约束、外键约束
数据库五大约束
约束需求=业务环境因素 + 使用环境因素 + 构建环境因素 + 技术环境因素
多重实际开发条件或者需求限制
软件工程中的约束
约束
数据库语句的执行流程
mybatis的执行流程
数据库应用
数据库原理
Java学习笔记Java_Practice
Go学习笔记GO_Practice
高效开发工具EfficientDevTools
软件基础知识SoftwareBasic
机器学习
数据结构和算法AlgorithmPractice
数据库(原理和指令)
中间件(指令和原理)
踩坑记录(复盘和总结)
工作项目UML图汇总
高可用/高并发/高性能解决方案(3H)
路径跳转
数据库名-->右键 任务-->生存脚本(注意是选择数据还是选择结构)
SQLServer2008导出表数据为SQL脚本
数据库属性中的模式设置为简单
如果选择数据库清理不明显，就直接选file，对log进行压缩，选第二个选项压缩至xx%
然后右键收缩道数据库进行日志文件清回理
清理完成后再将数据库模式改回原模式
数据库太大，对日志文件进行清理
显示这一行的数据是关于哪张表的
table
system：表只有一行：system表。这是const连接类型的特殊情况。
const ：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待。
eq_ref：在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用。
ref：这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。
range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况。
index：这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）。
ALL：这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。
显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL
type
显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
possible_keys
实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
key
使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_len
显示索引的哪一列被使用了，如果可能的话，是一个常数
ref
MYSQL认为必须检查的用来返回请求数据的行数
rows
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，这就会发生，或者是查询有问题。
关于MYSQL如何解析查询的额外信息
Extra
EXPLAIN语法
示例
mysql的sql执行计划
储存过程
存储过程和执行计划
Mysql实现级联操作（级联更新、级联删除）
输入=\"\
注意 ‘’ 号
注意“ = ”号
insert into () 可以不用写内容
如何将Excel数据转换为SQL脚本
数据库应用软件操作
1
2
2.1
3
SQL必知必会
MySQL技术内幕：innoDB存储引擎
推荐书籍
SQL练习网站数据库指令练习：SQLZOO
MySQL学习
创建库语句
show databases;create database ljdatabase;use student;show tables;show create table <table_name> \\G;select version();
显示所有数据库创建数据库使用某个数据库查看库中的所有表查看某表的创建语句查看数据库版本号
数据库操作指令
增
DELETE FROM Person WHERE LastName = 'Wilson'
DELETE FROM 表名称 WHERE 列名称 = 值
删值
drop table xxxx
删表
drop database RUNOOB
删库
删
UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
UPDATE ddd.xxx set userid = 12 WHERE id in ( SELECT a.id from ( SELECT id from ddd.xxx Where dede = 12 ) a )
select 和 update 合用
注意
改
SELECT * FROM Persons WHERE City='Beijing'
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
select * from 数据库.表名
查询某表数据
查
增删改查/表/库
GROUP表示分组，BY后面写字段名，就表示根据哪个字段进行分组
GROUP BY必须得配合聚合函数来用，分组之后你可以计数（COUNT），求和（SUM），求平均数（AVG）
font color=\"#fdb813\
group by
每个部门人数都有了，那如果我们想要进一步知道员工人数大于30000的部门是哪些，这个时候就得用到HAVING了
例子
HAVING
order by
分类
SELECT TOP 2 name FROM student
SELECT name FROM student LIMIT 2;
//是从数据库中的第二条数据开始查询两条数据，即第二条和第三条。selete * from testtable limit 2 offset 1;
limit 和 top
SELECT * FROM student WHERE age BETWEEN '18' And '23';
其功能跟OR类似，一个IN 就相当于好多个OR
NOT 操作符表是否定；其跟在WHERE后面功能类似<>;
SELECT * FROM student WHERE NOT age='25';
replace和replace into
SQL中like与通配符的使用
字段拼接
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 = '乖乖订单' )
内连接（inner join）
自然联结与标准的联结不同就是只返回值唯一的列，不会返回重复的列
右外联结是指 相对于 OUTER JOIN 右边的表，那么这会查询出右边表的所有数据 和根据等值条件匹配左边表的数据，如果左边表的数据不匹配，那么其返回列的值是NULL充当；
SELECT * FROM `order` RIGHT OUTER JOIN customer ON ( customer.userId = `order`.userId );
外联结（左右）
SELECT userId FROM customer UNION SELECT userId FROM `order`
组合查询
join. on
子查询和联结表
mysql中insert…select 死锁
插入检索数据
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 表名
数据库怎么清空一个表中所有数据
基础指令
判断字段是否为nullselect * from table where c is null select * from table where c is not null判断字段是否为空select * from table where c=''select * from talbe where c<>''
判断字段值是否为NULL
修改表时设置主键自增长：ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;修改表时删除主键自增长：ALTER TABLE stu CHANGE sid sid INT;
show create table stu;
怎样查看数据库表主键是否是自增长
如何设置mysql 主键自动增长
truncate table 表名
清空表
join方式
exist方式
in引导的子句只能对一个字段进行限制
select * from A where exists (select 1 from B where A.sid=B.sid and A.tid=B.tid)
对多个字段进行限制 exist
select * from A where exists (select 1 from B where A.sid=B.sid and A.tid=B.tid)
子查询与外表的字段有关系时
select * from A where exists (select * from B where B.id=‘条件‘)
只要子查询的条件成立，就会查询出表1中的所有记录，反之，如果子查询中没有查询到记录，则表1不会查询出任何的记录
子查询与外表的字段没有任何关联
如果not exists子查询只有自己本身的查询条件，这样只要子查询中有数据返回，就证明是false，结果在整体执行就无返回值；一旦跟外面的查询关联上，就能准确查出数据
子查询会分成两种情况
原理
exists 和 not exists 和 in
表关联查询
SELECT * FROM hfw_dd WHERE concat(需要搜索的字段) LIKE CONCAT(关键字)
表全字段 条件搜索
库全表 字段 条件搜索
数据库中某两个字段保持一致，比如使用NEWID函数
数据库指令中阶
Mac brew 安装mysql
//第一处修改：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
aliyun源
cd /usr/local/Homebrewgit remote set-url origin git://mirrors.ustc.edu.cn/brew.gitcd \"$(brew --repo)/Library/Taps/homebrew/homebrew-core\"git remote set-url origin git://mirrors.ustc.edu.cn/homebrew-core.gitbrew update
ustc源
~/.bash_profile 也需要修改
brew下载mysql提示Error: Failed to download resource \"protobuf\"
安装中的问题
service mysql start
启动mysql
service mysql restart
重启MySQL服务
mysql -u root -p
进入mysql shell界面：
exit
退出
(1)编辑配置文件。sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf(2)在[mysqld]下添加一行character_set_server=utf8。如下图
安装完成后记得修改mysql字符集
修改ubuntu的时区
Host：localhost（如果是本机没有必要改）user：mysql用户名password：MySQL密码将url后面加上?serverTimezone=GMT
连接mysql数据库出现，Connection to @localhost failed. [08001] Could not create connection to database
查找bind-address所在的文件，并修改
主机连接虚拟机MySQL报错：Can't connect to Mysql server on '192.168.33.10' (10061 \"Unknown error\")
GRANT ALL PRIVILEGES ON *.* TO 'root'@'MS-HOLFSSIPZZME' IDENTIFIED BY '123456' WITH GRANT OPTION;
主机无法连接虚拟机上的mysqlhostxxx is not allowed to this mysql server”；
连接问题
问题
Ubuntu16.04上安装MySQL
卸载
注意：brew install mysql@5.7
brew安装
安装
1：在终端下：mysql -V(大写)
2：在mysql中：mysql> status;(引号要加)
3：在help里面查找 $ mysql --help | grep Distrib
4：使用mysql的函数 mysql> select version();
查看mysql版本的几种方法
版本
安装和卸载
1、mysql.server start
2、mysql -h localhost -u root -p
启动
mysql安装管理
SQLPractice
0 条评论
回复 删除
下一页
