MySQL

青女染寒酥

已于 2024-09-07 19:35:01 修改

阅读量1.8k

点赞数 26

文章标签： mysql 数据结构

于 2024-08-31 15:26:29 首次发布

本文链接：https://blog.csdn.net/2301_81026736/article/details/141753315

版权

MySQL

文章目录

MySQL

安装及使用

直接去官网下载社区版，注意可以不登录对应账号直接下载。

我们就可以得到一个exe文件，这个exe文件保留，因为它可以对我们安装过的MySQL进行管理。如果我们没有安装MySQL，点击就会直接进入安装MySQL界面，如果我们安装过了，就可以出现一个管理界面，它可以管理我们安装的MySQL，可以对我们安装的MySQL进行add,update,remove等操作。

进行安装时可以选择安装服务端，客户端，都安装。安装客户端可选择安装路径。但是数据保存的路径不能在这换，一般的数据保存路径：C:\ProgramData\MySQL\MYSQL Server8.0\Data

管理界面

管理下载的MySQL。

点击那个exe文件然后就可以看到管理界面：

在这里插入图片描述

可以看到，点击某一个Server，就能有对应的信息。这里显示安装路径是在c，我也不知道怎么改。

服务界面

可以控制服务的开始和结束

当然，也可以点击开始->服务，找到MySQL也能看到这个服务。

`cmd`界面

可以在这个界面进行一些语句的执行，算是对数据库的一种使用。

简洁方式，可以直接打开MySQL 8.0 Command Line Client然后输入密码就行。这个MySQL 8.0 Command Line Client可以在开始里面，或者直接在开始里面搜。

比较麻烦的方式：

我们需要先配置系统环境变量，C:\ProgramData\MySQL\MYSQL Server8.0\bin，这个bin其实和刚才提到的数据保存是一个目录。
打开设置->系统->系统信息->高级系统设置->高级下的环境系统变量。点击系统变量下的Path,然后点击编辑，然后点击新建把这个目录放进去就配置好了。（记得点确定）

在这里插入图片描述

打开cmd界面（win+R输入cmd,或者直接在开始输入cmd）

输入：mysql -u root -p(-u:user用户，-p:password:密码)

然后输入root密码就行。

使用SQL

先进入cmd界面，然后就可以进行一些语句的使用。但是cmd界面不好看，所以会使用一些图形化工具。

名词介绍

SQL：Structured Query Language:结构化查询语言，且对大小写不敏感。

RDBMS：Relational Database Management System。关系型数据库管理系统

DBMS：DataBase Management System。数据库管理系统。

DBS:DataBase System。

MySql语句：

注：sql对大小写不敏感。

数据库：

#数据库
CREATE DATABASE test; #创建test数据库
show databases; #显示所有数据库，注意末尾的s
DROP DATABASE test;#删除test
USE test; #进入test数据库

表：

#表
CREATE TABLE test(
   id int NOT NULL AUTO_INCREMENT PRIMARY KEY, #NOT NULL AUTO_INCREMENT PRIMARY KEY：非空自增并作为主键（not null auto_increment primary key）这个就是约束条件
   NAME VARCHAR(100) ,
   age int，
   foreign key(myname) references test2(myname)
   #设置外键myname,参照test2表的myname列来设置的
   #设置外键是用于建立两个表的联系，方便join操作
    
);#创建表test，规定三个字段及类型。
DESC test; #查看表结构
ALTER TABLE test ADD email varchar(50);#ALTER:改变，add添加
ALTER TABLE test DROP column  email;#colimn:列，drop：删除 
ALTER TABLE test RENAME COLUMN NAME TO full_name; #修改列的字段名
ALTER TABLE test MODIFY COLUMN age TO SMALLINT; #修改类型为smallint
ALTER TABLE test ALTER COLUMN age SET DEFAULT 18;#设置默认值
DROP TABLE test;#删除test表

视图

#创建视图（view）用于交互
CREATE VIEW v_test（） AS SELECT * FROM test where age>0;
#创建v_test视图，并填充查询的数据
#v_test()中的字段是对应的列名，若省略则直接添加所查询的字段名
#其余的和table一样，不做赘述。

插入数据：

#插入数据
INSERT into test VALUES (3,'name3',20);#默认数据插入
INSERT into test (id,name)VALUES (4,'name4');#指定插入数据

删除数据：

truncate table test;#把表数据初始化（数据清空），id自增重置，保留关联，索引，表结构。
delete from test where age>20;#删除where指定数据，id自增不重置
drop table test;#删除整个表，啥都没了。
drop view v_test;#删除视图

更新数据

#更新，删除数据
UPDATE test SET name = '123' WHERE id = 2;#把id=2的name修改为'123'
UPDATE test SET name = '123';#把test所有的name修改为'123'
DELETE FROM test WHERE FULL_name = '123'#删除name=123的行数据

查询数据:

#查询
SELECT distinct name FROM test;#查询所有从test中,distinct:去重name。
SELECT * FROM test WHERE id>2;#查询id大于2的数据
SELECT * FROM test WHERE id IN (1,2,3);  #查询id为1，2，3的数据，也有not in
SELECT * FROM test WHERE id>2 AND id<5;#查询id在2和5之间的数据
SELECT * FROM test WHERE BETWEEN 2 and 5  ;
#查询id为2到5之间的数据（包含）也可也not betweed

SELECT * FROM test WHERE name LIKE 's%';#模糊搜索，查询s开头的数据
SELECT * FROM test WHERE name LIKE '%s%';#查询包含s的数据
SELECT * FROM test WHERE name LIKE 's%d';#查询s开头d结尾的数据
SELECT * FROM test WHERE name LIKE '李__';#查询李某（__代表单字符，一个汉字两个字符）
SELECT age FROM test where gerad = '50\%' ;#'\'为转义字符，给'%'转译为百分号字符

SELECT DISTINCT age FROM test; #去重查询age从test。
SELECT DISTINCT age FROM test LIMIT 3; #查询前三个数据，一般用于分页操作
SELECT DISTINCT age FROM test LIMIT 2，3; #跳过前两个数据，再查询三个数据

SELECT COUNT(*) FROM test ; #count(1),count(*)：查询总行数，
                            #count(列名)：查询这一列非空数据的行数。
SELECT SUBSTR(name,1,2) FROM test; #查询full_name的第1第2字符
SELECT SUBSTR(name,1,2) AS BieMing FROM test; #as把它字段做一个别名显示

SELECT age,COUNT(*) FROM test GROUP BY age;#以age分组查询每一个不同age出现的次数
SELECT age,COUNT(*) FROM test GROUP BY age HAVING age>20 ;
#用having对分完组后的结果做筛选

SELECT age,COUNT(*) FROM test ORDER BY age ASC;#以age排升序（asc）
SELECT age,COUNT(*) FROM test ORDER BY age DESC;#以age排降序序（desc）

SELECT 2024-age,COUNT(*) FROM test;    #select后面可以输出表达式。
SELECT avg(age)+sum(age)+max(age)+min(age),COUNT(*) FROM test; 
# avg平均值，sum,max,min对应函数

select test *,test1 *from test,test1 where test.name = test1.name
#可以直接查多个表的数据，注意标注对应的表名就可以了。

select * from test where age>2 union select * from test1 where age<20;
#用集合的方式连接多个表，union:并集，intersect:交集,except：差集。

子查询：

#子查询(查询语句可以嵌套使用)：
#主要处理的问题就是where不能用函数，加强了查询的灵活性
SELECT * FROM test WHERE age > ( SELECT AVG(age) FROM test );#avg：获取平均值，age>查询的数据
SELECT *,( SELECT AVG(age) FROM test ) AS AvgAge FROM test 
WHERE age > ( SELECT AVG(age) FROM test );#多显示一个名为AvgAge的字段
CREATE TABLE test1 SELECT *FROM test WHERE age >(SELECT AVG(age) FROM test);
#创建表test1，并把查询的数据存入
INSERT INTO test1  SELECT * FROM test WHERE age < (SELECT AVG(age)FROM test1);
#把查询的数据插入进去
SELECT * FROM test WHERE exists( age > ( SELECT AVG(age) FROM test ) );
#exists:存在，用于两个表的存在与否数据判定

表关联

#表关联查询：
# inner join 内连接:依次以左表为基础，输出右表条件匹配的数据
EXPLAIN SELECT t.age ,t1.id FROM test AS t INNER JOIN test1 as t1 
#将test和test1内联，explain拓展
ON t.age = t1.age; #匹配条件
#left join 左连接，先输出左表所有数据，再以及左表为基础输出右表匹配的数据，right join同理
EXPLAIN SELECT t.age,t1.id FROM test AS t LEFT JOIN test1 as t1 #将test和test1左连接
ON t.age = t1.age; #匹配条件

#一般挺少用表关联的，因为join涉及的合并都比较消耗性能，以及更新表的时候join也需要被更新
#其次，join的效率十分依赖索引，否则效率很低，但是关联的表越多索引越难构建，同时也越难理解语句难以维护。
#最后，可以用其他的方式去代替

索引

#索引，快速查找，类似书的目录。
CREATE INDEX idx_age ON test(age);#给test的age添加名为idx_age的索引 
CREATE INDEX idx_full_name_age ON test(name,age);#联合索引 
DROP INDEX idx_age ON test;#删除索引

模式

#模式:
create schema "S-T" authorization user;
#为user定义一个“S-T”模式
drop schema "S-T" cascade;#删除此用户及他所传播用户的此模式
drop schema "S-T" restrict:#限制：只删除此用户的模式

Mysql查询速度优化

索引： 使用了某种数据结构来加快数据的查询。

注：主键也具有索引的性质，但主键同时还具有唯一性，非空性等。

所使用的数据结构

简单介绍如下数据结构：

二叉树：分层的数据结构，以第一个传入的数据作为根节点，其中每个节点最多有左右两个子节点，数值从左到右排升序（逐渐升高）。
红黑树：自平衡的二叉搜索树，在插入删除节点时，把树旋转更换根节点，确保高度大致平衡，加快查找速率。然后给节点加上红黑属性，减少删除插入时，旋转树所需要的时间。
B-Tree:自平衡的树，每个节点可以有多于两个子节点，且所有叶子节点都在同一层，存储大量数据时可以确保高度不高，从而占据优势。

由于，考虑到数据存储一般都很多，百万千万的。所以MySql的索引使用的是类似B-Tree的一种数据结构：B+Tree来加快查询。

B+Tree:相对于B-Tree每一个节点都是(key+value)的做法，B+Tree把value全存进了叶子节点，又由于所有叶子节点都是在同一层，并它门用链表有序的串联起来，于是就加快了范围查询效率。同时，如果我想进行删除和插入操作，也更快速。

到这里就会有疑问：这样不是多此一举？分明有可能中途完成查询非要延迟到最后？？是的，相对于B-树，B+树确实增多了 O/I操作（查询的次数）。但是，由于value得到了整体管理，使得范围查询的速率加快了:比如我要查 >50 的数据,B+树在查的时候，可以直接查到50，然后直接获取50到max的数据。但B-树就麻烦许多，可能需要依次查对应的数据。

故：B-树适合随机查询，B+树适合范围查询。MySQL考虑到使用者偏向于范围查询，于是采用的是B+树。而我们也确实经常select范围数据。

然后，除了B+树，MySQL也会使用Hash表（散列表，将key映射到表中数据，可以快速地进行插入和查找数据）去处理索引。用Hash索引的效率很高，但是不太支持范围查找。所以在查询一些单个数据时才会用到Hash去处理索引。

关于主键和索引

主键所使用的B+树，它的叶子节点存的是所有数据的直接地址（也称之为聚集索引），而普通索引叶子节点存储的就只是这个索引的数据（也称非聚集索引），然后再回表（可以理解为：再用主键索引的方式查一遍）。所以，一般推荐直接使用自增的整形主键，特殊情况再添加索引。

同时，主键的索引被称为一级索引，对应的映像是一级映像，普通索引被称为二级索引，对应二级映像。

那么使用自增的整形主键有什么好处呢？

其一：自然是效率更高，毕竟少了转化的操作。
其二：如果我们没有建立主键，那么MySQL会自己帮我们生成一个唯一识别标志，且我们不可对其进行操作。如果我们建立了主键，且是自增整形，那么这个主键就可以充当这个唯一识别标志。
其三：建立B+树的时候，需要对数据进行比大小，由于字符串还需要转化为ASCII，其他数据也要进行对应的转化，但是整形可以直接比较，加快速度。
其四：自增方便B+树的维护，添加或者删除数据可以直接在尾端操作。

关于索引的建立和使用：

我们建立单个索引的时候，MySQL会根据单个字段来进行B+树的创建，建立联合索引的时候，会把联合的多个字段拼接成一个字段，再去排序，完成B+树的创建。

然后，查询的时候，我们需要根据创建这个索引的字段去设置where条件，才能使用这个索引完成查询，毕竟B+树的结构在那。举例：

CREATE INDEX idx_name_age_grade ON test(name,age,grade);
EXPLAIN SELECT * FROM test WHERE name = '1',age = 20; #explain可以查看kay
#会使用索引,验证方式：key = idx_name_age_grade 就是使用了这个索引。

EXPLAIN SELECT * FROM test WHERE name = '1';
#会使用索引，可以直接查询name。

EXPLAIN SELECT * FROM test WHERE age = 20; 
#不会使用索引，没有筛选name

EXPLAIN SELECT * FROM test WHERE age = 20， name = '1';
#取决于所使用的编译器，如果可以优化为第一种形式，则可以使用索引。
#如果编译这个语句，则不能使用索引，因为它没有先筛选name。

EXPLAIN SELECT * FROM test WHERE name = '1' and ABS(age)>0; #abs：绝对值
#会对name条件使用idx_name_age_grade索引筛选，然后对筛选的数据普通查询abs(age)
#因为name是可以使用索引的，但是abs(age)不是age，不能使用索引。尽管我们知道age>0。

EXPLAIN SELECT * FROM test WHERE name = '1' and age+10>0;
#同理，age+10不是age,后面也不会使用索引。
#但是age>0-10是会用索引的，因为age还是age

所以，建立索引的时候推荐建立联合索引，因为联合索引也可以进行单个筛选。同时，我们使用数据库去处理大量数据时，筛选条件一般是多个。

索引跳跃扫描

MySQL 8.0.13 后对联合索引更新的一种扫描方式，使得索引idx_name_age_grade可以被where age使用，而不用额外加上name。

实现方式：毕竟这个索引的name是123排好序的，然后每一个name里的age也是排好序的，于是就在name1找age，name2找age…。找完后再整合。

适用场景：name的重复率比较高，只有几个name不同，毕竟如果每一个name都不一样，那和不用索引区别不大。

如果我要直接where grade呢？还能跳跃搜索吗？简单算一下：我有10个数据，有5个不同的name和age,于是跳跃搜索就要先分 5*5=25 > 10。。。emm,估计是不能的。

使用索引就一定更好吗？

当然不一定！

举个不靠谱的列子：我要查id = 1，不用索引第一下就找到了。用了索引我还得经过一些其他的判定。
用索引需要回表操作，这个也是要时间的，对于小表而言不要索引反而更快。
用“=”读取大部分行数据时。用Hash索引要一个一个的去从头查，而不用索引可以一行接着一行读取
如果一个字段区分度不大，比如性别男女其实用索引筛选后作用不大。如：99%是男，1%是女，查男的时候，反而可能因为回表导致效率降低。但是查女的时候，效率就很高。
如果一个表时常更新，那么也要维护索引，负担比较重。

关于like模糊索引

如果是name%的话，它的前缀可以走索引，直接加快效率。

如果我们需要后缀匹配的话，那就构建一个辅助字段，这个字段存的是需要匹配条件字段的反向字段，再创建反向索引，使用的时候就（%name->eman%）这样就转化为了前缀匹配。

参考代码：

alter table test add reversed_name varchar(50);#添加反向字段
update test set reversd_name = REVERSE(name);#获得name的反向字段并存进去
create index idx_reversed_name on test(reversed_name);#做反向索引
select * from test  where reversd_name like 'eman%';

有时候，即便是前缀索引依旧不够快，毕竟需要挨个遍历全表。于是，我们可以多加限制条件来缩小搜索范围：

select * from test LIMIT 3 where  age>20 and reversd_name like 'eman%';
#它会先执行 age>20的条件，再在缩小的范围中模糊搜索。以及得到3条数据就罢休。

青女染寒酥

关注

26
点赞
踩
15

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

MySQL

MySQL

文章目录

安装及使用

管理界面

服务界面

cmd界面

使用SQL

名词介绍

MySql语句：

Mysql查询速度优化

所使用的数据结构

关于主键和索引

关于索引的建立和使用：

索引跳跃扫描

使用索引就一定更好吗？

关于like模糊索引

`cmd`界面