MySql学习之基础入门篇

我哪知道啊¿

已于 2022-03-10 20:23:12 修改

阅读量484

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库学习

于 2022-02-20 20:29:05 首次发布

本文链接：https://blog.csdn.net/qq_28356977/article/details/122785076

版权

mysql 专栏收录该内容

1 篇文章 1 订阅

订阅专栏

1.登录MySQL

首先，先把mysql安装目录的bin目录添加到系统变量path中，再以管理员身份打开cmd即可，这里就不说怎么安装了，网上那么多教程可以看，我们直接进入mysql的学习。

登录格式：mysql -uroot -p+你的密码

出现如图所示的代码说明你登陆成功了
在这里插入图片描述
退出登录

exit

2.数据库的常用命令

注意：所有的sql语句都以分号结尾，且不区分大小写
查看当前有哪些数据库

show databases;

使用某个数据库

use +数据库名;

创建数据库

create database +数据库名;

删除数据库

drop database +数据库名;

查看某个数据库下的表

show tables;

导入sql数据

source +sql文件路径

查看表的结构

desc +表名;

查看数据库版本号

select version();

查看当前使用的数据库

select database();

3.数据库查询语言（DQL）

3.1简单查询

查询一个字段名

select 字段名 from 表名;

查询两个或多个字段名
用逗号隔开即可

select 字段名1，字段名2，……字段名n from 表名;

查询所有字段名

select * from 表名;

给查询的列起别名
注意：只是将查询的结果名字改变，不改变原表，select语句是不会进行任何修改操作的
若别名中有空格或中文，要用单引号把别名括起来（在数据库中，字符串是使用单引号括起来的）

select 字段名1 as 修改结果 from 表名;

加减乘除

select 字段名+运算符 from 表名;

3.2条件查询

概念：不是将所有数据都查出来，是查询出来符合条件的
格式： select … from … where 条件

都有哪些条件

= 等于
<>或!= 不等于
> 大于
< 小于
between … and … 两个值之间
is (not) null 判（非）空
and 并且(优先级大于or)
or 或者
in() 包含，相当于多个or
not in 不包含
like 模糊查询，支持%与_匹配，%：匹配任意多个字符，_：任意一个字符

下面举几个like的栗子
1.在表emp中找出名字以K开始的

select name from emp where name like ‘K%’;

2.在表emp中找出名字第二个是A的

select name from emp where name like ‘_A%’;

2.在表emp中找出名字中有_的，_是特殊符号，要用\转义

select name from emp where name like ‘%\_%’;

3.3排序

语句执行顺序：
1.from
2.where
3.select
4.order by（排序总是在最后执行）

默认是升序查找

select 字段名1,字段名2…… from 表名 order by 字段名;

升序查找

select 字段名1,字段名2…… from 表名 order by 字段名 asc;

降序查找

select 字段名1,字段名2…… from 表名 order by 字段名 desc;

按照多个字段排序
查找两个字段名，按字段名1升序，若字段名1一样，再按字段名2升序排列

select 字段名1,字段名2 from 表名 order by 字段名1 asc, 字段名2 asc;

3.4函数

3.4.1数据处理函数（单行处理函数）

单行处理函数：一个输入对应一个输出
多行处理函数：多个输入对应一个输出

常见的数据处理函数：
lower：转成小写

栗子：select lower(字段名) from 表名;
后面的函数以此类推

upper：转成大写
substr(字段名，起始下标，截取的长度)：取子串，下标从1开始
length：取长度
concat：(字符串1，字符串2)：拼接字符串
trim：去空格
round：四舍五入
rand：生成随机数
ifnull(字段名，被当做的值)：可以将null转成一个具体值
case…when…then…when…then…else…end：相当于switch

3.4.2分组函数（多行处理函数）

注意：分组函数在使用前必须先进行分组，然后才能使用。若没有对数据进行分组整张表默认为一组。

常见的分组函数
count：计数

栗子：select count(字段名) from 表名;
下面的函数以此类推

sum：求和
avg：平均值
max：最大值
min：最小值

注意事项：
1.分组函数会自动忽略null
2.分组函数中count(*)和count(具体字段的区别)：前者：统计所有行数，后者：统计该字段下所有不为null的元素的总数
3.分组函数不能直接使用在where子句之中
4.所有的分组函数可以组合起来一起用

分组查询

什么是分组查询？
答：在实际的应用中可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作，这个时候我们需要使用分组查询，怎么进行分组查询呢？
格式
select … from… group by… having…
having:对分组的数据再进行过滤，与where类似，但是效率低，能用where就有优先用where

栗子：
按一个字段名分组
select 字段名1,sum(字段名2) from 表名 group by字段名1;
作用：按照字段名1进行分组，然后对每一组字段名2对应的数据进行求和
按多个字段名分组
技巧：多个字段名看做一个字段名
select 字段名1,字段名2,sum(字段名3) from 表名 group by 字段名1，字段名2;
作用：先按字段名1进行分组，再对每一组按字段名2进行分组，然后对每一组字段名2对应的数据进行求和

目前为止的关键字的书写格式
select
…
from
…
where
…
group by
…
having
…
order by
…
以上关键字顺序不能颠倒，需要记忆
执行顺序
1.from
2.where
3.group by
4.having
5.select
6.order by

下面介绍一个关键字distinct（去除重复）
注意：这个关键字只能出现在所有字段名的最前面

select distinct 字段名 from 表名;

3.5连接查询

什么是连接查询？
答：跨表查询，多张表联合起来查询时数据，被称为连接查询

笛卡尔积现象

若两张表连接且没有任何限制，最终查询条数是两张表条数的乘积，为了避免这个现象，在表的连接时，我们要加上条件。同样也是使用where语句

格式：
select 字段名1,字段名2 from 表名1,表名2 where 表名1.字段名3=表名2.字段名3;

上述代码优化：
上述代码的执行效率很低，为了提高效率，我们可以类似新建对象一样来提高执行效率，下面的e和d就相当于表1和表2的对象

select e.字段名1,d.字段名2 from 表名1 e,表名2 d where e.字段名3=d.字段名3

3.5.1内连接

内连接之等值连接

为什么叫等值连接？
答：因为连接条件是一个等量关系

语法格式与上文类似，上文是sql92的语法版本

select e.字段名1,d.字段名2 from 表名1 e,表名2 d where e.字段名3=d.字段名3

sql92的语法缺点：结构不清晰，表的连接条件和后期进一步筛选的条件都放到了where后面

下面是sql99的版本：

select e.字段名1,d.字段名2 from 表名1 e join 表名2 d on e.字段名3=d.字段名3

sql99的语法优点：表连接的条件是独立的，如果还需要进一步筛选，再往后面添加where就行了。on后面的就是表连接的条件，join左右跟两个表名，表示连接的两个表。

内连接之非等值连接

非等值连接与等值连接的区别：
on后面的连接条件不是一个等量关系，比如说用!= 或者between … and … 连接，两者格式类似。

内连接之自连接

自连接，即我连我自己，诀窍是把一张表看成两张表，上述代码中的表名1 e join 表名2 d改成表名1 e join 表名1 d即可，把e和d看成同一个表的两个不同对象，在on里面写上连接条件即可。

3.5.2外连接

内连接的特点：能够匹配上这个条件的数据查询出来
外连接的特点：没匹配上的数据也能显示出来：

格式：

select e.字段名1,d.字段名2 from 表名1 e right join 表名2 d on…
right的作用：将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。left同理类推

3.5.3三张或以上的表怎么连接

注：多表连接可以混合使用内外连接
语法格式：

select … from a join b on a和b的连接条件 join c on a和c的连接条件 join d on a和d的连接条件

3.6子查询

什么是子查询：
答：select语句中嵌套select语句，被嵌套的select与成为子查询。

where中的子查询

select … from … where 字段名条件查询符号 (select … from …);

from中的子查询
注：from后的子查询，可以将子查询的结果当成一张临时表
后面的t就相当于临时表的对象

select t.*,s.grade from (select …) t join 表名 s on…

select中的子查询
注：select后的子查询最多只能有一条结果

select … (select… from… where…) from …;

3.7union合并查询结果集

union可以减少匹配的次数，提高查询效率
注：union在进行结果集合并时，要求两个结果集的列数相同，列和列的数据类型也相同

select … from …
union
select … from …

3.8 limit的的使用

limit的作用：将查询结果的一部分取出来，在order by之后执行

select … from … limit(起始下标（0开始），长度)

通用分页：
页码：pageNo
每页长度：pageSize
可得出每一页所展现的信息公示

limit (pageNo-1)*pageSize,pageSize

4.数据库定义语言（DDL）

4.1 DDL之表的操作

表的创建

数据库字段名的命名规范：
所有的标识符全是小写，单词与单词之间使用下划线进行衔接。

语法格式：

create table 表名 (字段名数据类型,字段名数据类型…);

指定某个字段名的默认值：default关键字

快速复制表

create table 表名 as select * from 被复制的表名;

表中的数据类型
varchar——可变长度的字符串，按实际的数据动态分配空间，速度慢
char——定长字符串，分配的空间是固定的，速度快
int——数字中的整数型
bigint——数字中的长整型
float——单精度浮点型数据
double——双精度浮点型数据
date——短日期类型（年月日）
datetime——长日期类型（年月日时分秒）
clob——字符大对象（可存储4G的字符串）
blob——二进制大对象（专门用来存储图片、视频、声音等），往blob上插入数据时，需要使用IO流。

删除表

drop table 表名;

5.数据库操作语句（DML）

5.1增删改操作

insert的用法:

insert into 表名(字段名1,字段名2,…) values(值1,值2…);

日期函数:
str_to_date：将字符串varchar类型转换成date类型
date_format：将date类型转换成具有一定格式的varchar类型

insert插入日期类型:
使用str_to_date(‘字符串日期’,‘日期格式’)

mysql的日期格式
%Y 年
%m 月
%d 日
%h 时
%i 分
%s 秒
mysql中date默认的格式：’%Y-%m-%d’
mysql中datetime默认的格式：’%Y-%m-%d %h:%i:%s’
获取系统当前时间now()，是datetime类型
查询日期
date_format(date类型字段名,‘日期格式’)
update的用法:

update 表名 set 字段名1=值1,字段名2=值2… where 条件；

注意：没有条件限制会导致所有数据全部更新

delete的用法：

delete from 表名 where 条件;

注意：没有条件，整张表的数据会全部删除！

快速删除大表
delete的缺点：删除效率低
delete的优点：支持回滚，防止删库跑路

快速删除格式：

truncate table 表名;

缺点：无法回滚数据，便于删库跑路。

修改表的结构

添加字段：
alter table 表名 add 字段名数据类型;
修改字段：
alter table 表名 modify 要修改的字段名修改后的数据类型 ;
删除字段：
alter 表名 drop 要删除的字段;

6.数据控制语言（DCL）

6.1约束

什么是约束

在创建表的时候，可以给表中的字段加上一些约束，保证数据的有效性，完整性。

非空约束（not null）

not null 约束的字段不能为NULL，格式：
create table 表名（字段名数据类型 not null, …）;

唯一性约束（unique）

唯一性约束约束的字段不能重复，但是可以为null
create table 表名（字段名数据类型 unique, …）;
两个字段联合唯一：
create table 表名（字段名1 数据类型, 字段名2 数据类型，unique（细字段名1，字段名2）…）;

主键约束（primary key）
主键约束的相关术语
主键约束：一种约束
主键字段：添加了主键约束的字段
主键值：主键字段中的每一个值
什么是主键
主键值是每一行记录的唯一标识（类似于人的身份证号），任何一张表都应该有主键，否则表无效，注意，一张表只能添加一个主键！
添加主键约束：
主键的特征：not null+unique（主键不能是null，同时也不能重复）

create table 表名 (字段名数据类型 primary key, …);
两个字段联合主键：
create table 表名（字段名1 数据类型, 字段名2 数据类型，primary key（字段名1，字段名2）…）;

自增主键：
primary key auto_increment：表示自增，从1开始，以1递增

外键约束（foreign key）
外键约束的相关术语：
外键约束：一种约束
外键字段：添加了外键约束的字段
外键值：外键字段中的每一个值
外键约束的作用：
简而言之，就是限制一张表中的某个字段名，其值只能是另一张表中某个字段名的值，防止数据无效，被引用的表叫做父表，引用其他表中字段值的叫子表，删表：先删子表，创建表：先创建父，删除数据：先删子，插入数据：先插父。
添加外键约束
注：子表中外键引用的父表中的某个字段，被引用的这个字段不一定是主键，但至少具有unique约束。

create table 表名 (字段名数据类型,
foreign key(要外键约束的字段名) references 引用的表名(表中字段名)…);

7.存储引擎（了解即可）

什么是存储引擎？有什么用？

存储引擎是mysql中特有的一个术语，其他数据库中没有，存储引擎是一个表存储数据的方式，不同的存储引擎存储数据的方式不同

给表指定存储引擎
mysql默认的存储引擎是InnoDB
mysql默认的编码方式是utf8

create table 表名(…) engine=引擎类型 charset=编码方式;

mysql支持的存储应引擎
查看所有引擎
show engines;
MyISAM存储引擎
它管理的表具有以下特征：
– 使用三个文件表示每个表：
• 格式文件 — 存储表结构的定义（mytable.frm）
• 数据文件 — 存储表行的内容（mytable.MYD）
• 索引文件 — 存储表上索引（mytable.MYI）
– 灵活的AUTO_INCREMENT字段处理
– 可被转换为压缩、只读表来节省空间
InnoDB存储引擎
• InnoDB存储引擎是MySQL的缺省引擎。
• 它管理的表具有下列主要特征：
– 每个InnoDB表在数据库目录中以.frm格式文件表示
– InnoDB表空间tablespace被用于存储表的内容
– 提供一组用来记录事务性活动的日志文件
– 用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
– 提供全ACID兼容
– 在MySQL服务器崩溃后提供自动恢复
– 多版本（MVCC）和行级锁定
– 支持外键及引用的完整性，包括级联删除和更新
最大的特点：支持事物，保证数据的安全性

MEMORY存储引擎
• 使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快。
• MEMORY存储引擎管理的表具有下列特征：
– 在数据库目录内，每个表均以.frm格式的文件表示。
– 表数据及索引被存储在内存中。（查询效率高但不安全）
– 表级锁机制。 – 不能包含TEXT或BLOB字段。
• MEMORY存储引擎以前被称为HEAP引擎。

8.事物

什么是事物？
注：只有DML语句和事物有关（增删改），数据安全是第一位！

一个事物其实就是一个完整的业务逻辑，是一个最小的工作单元，不可再分，一个事物就是多条DML语句同时成功或同时失败。

事物是如何实现的？

InnoDB存储引擎：提供一组用来记录事务性活动的日志文件
在事物执行过程中，每一条DML语句的操作都会记录到该日志文件中
提交事务：代表事物成功，存储到数据库中
回滚事物：代表事物失败。

怎么提交和回滚事务？

提交：commit;
回滚（只能回滚到上一次的提交点）：rollback;

mysql默认情况下会默认提交事务：每执行一条DML语句自动提交事务。

关闭自动提交机制：

先执行： start transaction;

事务特性（面试）
A：原子性，说明事务是最小的工作单元，不可再分
C：一致性，在同一个事务中，所有操作必须同时成功或同时失败
I：隔离性，A事物和B事物之间具有一定的隔离，即一个事物的内部操作与其他事物是无关
D：持久性，事物一旦被提交，他对数据库的改变是永久性的，接下来的操作和数据库故障不会对其有任何的影响。

数据库的并发问题
脏读：对于两个事物T1，T2，T1读取了已经被T2更新单还没有被提交的字段，之后若T2回滚，则T1读取的字段就是无效的
不可重复读：对于两个事物T1，T2，T1读取了一个字段，然后T2更新了该字段，之后T1再读取同一个字段，值就不同了。
幻读：对于两个事物T1，T2，T1从表中读取了一个字段，然后T2在该表中插入了一些新的行，之后，如果T1再次读取同一个表，就会多出几行。

事物的隔离级别

数据库系统必须具备隔离并发运行各个事物的能力，使他们不会相互影响，来避免上述的并发问题。

级别1：读未提交（read uncommitted）：事物A可以读取到事物B为提交的数据
级别2：读已提交（read committed）：事物A只能读取到事物B已提交的数据，每一次读到的都是真实的数据
级别3：可重复读（repeatable read）：事物A开启之后，不管是多久，每一次在事物A中读取到的数据还是没有发生改变，即使事物B已经将数据修改并提交。
级别4：序列化（serializable）：最高隔离级别，效率最低，表示事物排队，不能并发。
查看隔离级别：

SELECT @@transaction_isolation;

设置隔离级别：

set global transaction isolation level 隔离级别;

9.索引

索引概述

索引在数据库表的字段上添加，是为了提高查询效率存在的一种机制，多个字段联合起来也可以添加索引，索引相当于目录的作用，在mysql中，索引是一个B-Tree结构，遵循左小右大原则存放，采用中序遍历。

索引的存储
注意：在任何数据库中，主键都会自动添加索引对象。
在mysql中，一个字段上如果有unique约束的话，也会自动创建索引对象。根据不同的引擎，索引的存储位置也不同，MyIsam中，存储在.MIY文件中，在InnoDB中，存储在一个逻辑名称叫tablespace中，在MEMORY中存储在内存中。

添加索引的条件