mysql学习

周周嘿嘿

已于 2022-04-16 23:30:08 修改

阅读量79

点赞数

分类专栏： java 文章标签： mysql 数据库 database

于 2021-10-30 22:22:03 首次发布

本文链接：https://blog.csdn.net/qq_45895133/article/details/121056229

版权

java 专栏收录该内容

88 篇文章 3 订阅

订阅专栏

基本架构：
创建数据库，创建表，展示数据库，展现表，删除数据库，删除表。
创建数据库：
在这里插入图片描述

drop database java01//drop database if exists java01;删除数据库
show databases; 查看有哪些数据库
select database(); 查看正在使用的数据库

在这里插入图片描述
drop table student // drop table if exists student;
show tables;
查看表结构：
desc student;
修改表结构:
修改列名:
alter table 表名 change 原列名新列名原列名类型;
修改列的类型:
alter table 表名 modify 列名新的列名类型,需要和原类型重叠;
删除列:
alter table 表名 drop 列名;
新增列:
alter table 表名 add 新列名列名类型;

关于表中的数据增删改查：
增：insert into 表名或者（列名） values （…);
在这里插入图片描述

改：先通过条件找到对应行，再找到对应列。
update 表名 set 对应列修改 where …
在这里插入图片描述
删：只能按照行删除或者删除所有数据

查询：先通过条件找到对应行，再找到对应列，除了有group by 除外，需要先进行聚合运算，再找对应行，再找对应列。
找对应列：几个列进行运算，得到若干个新列，可以在此基础上起别名，也可以对一个列或者若干个列进行聚合运算，也可以在此基础上起别名：

在这里插入图片描述
也可以是：select score+id as … from student…
找到对应行：
可以通过升序和降序进行排序，或者进行分页，或者where进行筛选：
升序：asc 降序：desc
分页：limit 数量 offest 下标（从0开始）
select namefrom student1 order by score desc limit 2 offset 0;
此时升序降序和分页查询,where语句可以混合使用。

where后跟and时要把能筛选掉大量行数的放在左边，这样查询得更快。 在这里插入图片描述

在这里插入图片描述
当有group by分组时：
需要先进性聚合运算，再筛选行，在选择列（此时的列只能用聚合函数声明的列或者分组的字段），注意：
having 可以对聚合运算得到的结果进行筛选，注意不能用where,where只能用创建表时的列名，不能用运算结果值
注意：这是错误的，不能有where跟聚合运算结果值，一般group by后面需要跟having,不能跟where.

如下：

在这里插入图片描述
这是正确的：
select name,sum(score) as allscore from student1 group by name having allscore<140 order by allscore desc limit 1 offset 0;

但是where是可以和having一起使用的，执行顺序是先通过where筛选行，再进行分组，聚合运算，having根据1聚合运算的值再次进行筛选行。
在这里插入图片描述

where语句：
1.注意模糊查询，一般用在where语句中：
在这里插入图片描述

where语句常用：and ,or,between…and…
and的优先级比or高，如果先算or得加小括号

2. 子查询：一般用where语句嵌入
\查询买的商品茄子价格小于西瓜的顾客姓名(利用子查询)：
select name from custom where id in (select s1.custom_id from ordergood s1,ordergood s2 where s1.custom_id=s2.custom_id and s1.good_id=1 and s2.good_id=3 and s1.price<s2.price);
子查询：当子结果只有一行时直接用等于，当有多行时用in

表和表的关系为一对一，一对多，多对多。
建立在外键和主键的基础上，表中的每一行都是独立的，为一对一关系，两个表，有一个表的主键为另一个表中的外键，为一对多关系，三个表，第三张表中两个外键为另外两个表中的主键。
外键必须是其它表中的主键，否则无法判定对应关系。
多表查询建立在表和表的关系上，通过各个表进行内连接然后查询相关数据。
连接：select … from a,b,c,… …


```handlebars
create table if not exists good(
    id int primary key auto_increment,
    name varchar(10)
);
create table if not exists custom(
    id int primary key auto_increment,
    name varchar(10),
    phone varchar(15)
);
create table if not exists ordergood(
    id int primary key auto_increment,
    price decimal(5,2),
    good_id int,
    custom_id int,
    foreign key (good_id) references good(id),
    foreign key (custom_id) references custom(id)
);
insert into good values (null,'西瓜'),
(null,'香蕉'),
(null,'茄子'),
(null,'铅笔'),
(null,'石油');
insert into custom values 
(null,'张飞',12345),
(null,'关羽',938912),
(null,'刘备',9329),
(null,'诸葛亮',12910);
insert into ordergood values 
(null,100,1,2),
(null,104,1,3),
(null,321,5,4),
(null,922,4,1),
(null,93.42,2,3),
(null,19.34,3,1)
(null,140,3,2),
(null,104.4,3,3),
(null,321,3,4);

结果：
在这里插入图片描述

自连接：
当同一列的不同行进行比较时
查询买的商品茄子价格小于西瓜的顾客id：
select s1.custom_id from ordergood s1,ordergood s2 where s1.custom_id=s2.custom_id and s1.good_id=1 and s2.good_id=3 and s1.price<s2.price;
结果：
在这里插入图片描述

\时间变量：date,time,datetime,timestamp:
time:小时-分-秒
date:年-月-日
datetime；年-月-日小时-分-秒
timestamp:同上
now()函数表示此时北京时间
insert into timedemo3 values (now(),now(),now(),‘2032-12-23 21:21:31’);
…timestamp占用四个字节更节省内存，datetime占用八个字节
在这里插入图片描述

注意：含有null的运算结果都为null,除了null<=>null为真。一般不会涉及null运算

索引:
什么是索引?
索引是为了提高数据库查询效率的一种数据结构.
优点:提升了查询效率
缺点:减慢了增加,删除,修改数据的效率,牺牲了更多磁盘空间存储索引文件.
使用的数据结构?
使用了B+树
为什么使用B+树:
1 B+树是一种多叉平衡树,这种数据结构因为一个节点包含了多个数据,所以和二叉树相比,进一步降低了二叉树的高度**,降低了时间复杂度**,小于O(logn),增加了查询效率
2.B+树支持范围查找,主键和对应的行地址都放在叶子节点上,叶子节点使用链表连接,范围查找只需要找到最小主键,然后遍历链表.
索引查找数据过程:
通过IO操作将根节点加载到内存中,然后遍历节点找到下一个域,然后再通过`IO操作得到下一个节点,遍历节点,循环往复,直到找到叶子节点中的索引列值,然后根据索引列值的行地址找到需要的行,再将行数据返回给客户端.
关键: B+树中,每比较一次就要进行一次IO操作,减少IO次数.
为什么不适用hash表:
hash表无法进行范围查找.
为什么不使用二叉搜索树?
二叉搜索树的范围查找效率太低,为O(n),需要找到最小索引列值,再找到最大索引列值,然后进行中序遍历,得到需要的所有索引列值,效率太低.
为什么不使用B树?
B树也是多叉平衡树,但是没有将所有索引列值和对应的行地址信息放到子节点中,相对B+树范围查找速率慢了一些.
索引应用场景:
将会频繁进行查找,增加,删除,修改使用频率不高的字段设置为索引.

创建索引:
普通索引:ALTER TABLE table_name ADD INDEX index_name (column list);
唯一索引:ALTER TABLE table_name ADD UNIQUE index_name (column list);
唯一索引要求列的值不能重复,主键索引是特殊的唯一索引,因为不为空.

删除索引:
ALTER TABLE table_name DROP INDEX index_name column list;
ALTER TABLE table_name DROP UNIQUE index_name column list;
查看索引:
show index from 表名;
查看查询效率:
explain select …

如果没有索引,查询将会在数据库中遍历所有的行,因为是在文件磁盘上遍历,效率很低,比在内存中慢多了…
有了索引之后,只用两三次IO操作就可以得到对应的行,效率会提高很多,因为不用在磁盘上遍历表了.