MySQL学习

一，安装MySQL

配置my.ini

以管理员身份运行cmd进入解压文件的bin目录执行一下命令

mysqld --initialize --console  安装

记录其中的初始密码
net start MySQL  启动数据库
mysql -u root -P 3306 -h localhost -p

初始等录 此时需要初始密码登录，需要进行修改
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

设置访问数据库权限(需关闭防火墙)

update user set host = '%' where user = 'root';

 二，MySQL基本语法

• SQL指令不区分大小写

• 每条SQL表达式结束之后都以;结束

• SQL关键字之间以空格进行分隔

• SQL之间可以不限制换行（可以有空格的地方就可以有换行）

• MySQL注释：

• 单行注释：-- 注释内容

• 单行注释：#注释内容

• 多行注释：/*注释内容*/

1.DDL数据定义语言

创建数据库

• 创建数据库

create database 库名;

• 创建数据库，当指定名称的数据库不存在时执行创建

create database if not exists 库名;

• 在创建数据库的同时指定数据库的字符集（字符集：数据存储在数据库中采用的编码格式 utf8mb4、gbk）

create database 库名 character set 字符集;

修改数据库字符集

• 修改数据库的字符集

alter database 库名 character set 字符集;

删除数据库

• 删除数据库

drop database 库名;

• 如果数据库存在则删除数据库

drop database if exists 库名;

使用/切换数据库

use 库名;

创建数据表

create table students(
   stu_num char(8) primary key AUTO_INCREMENT ,
   stu_name varchar(20) not null unique foreign key,
   stu_gender char(2) not null,
   stu_qq varchar(11) unique
);

• 非空约束（not null）：限制此列的值必须提供，不能为null

• 唯一约束（unique）：在表中的多条数据，此列的值不能重复

• 主键约束（primary key）：非空+唯一，能够唯一标识数据表中的一条数据

• 外键约束（foreign key）：建立不同表之间的关联关系

查询数据表

show tables;

查询表结构

desc 表名;

删除数据表

• 删除数据表

drop table 表名;

• 当数据表存在时删除数据表

drop table if  exists 表名;

修改数据表

• 修改表名

alter table 旧表名 rename to 新表名;

• 数据表也是有字符集的，默认字符集和数据库一致

alter table 表名 character set 字符集;

• 添加列（字段）

alter table 表名 add 列名 类型;

• 修改列（字段）的列表和类型

alter table 表名 change 旧列名 新列名 类型;

• 只修改列（字段）类型

alter table 表名 modify 列名 类型;

• 删除列（字段）

alter table 表名 drop 列名;

（关于删除）：

1. 使用TRUNCATE命令

TRUNCATE命令可以快速删除表中的所有数据，并重置自增长ID。它比DELETE命令更快，因为它不会记录日志和触发器。但是，它不支持WHERE子句，因此您无法选择要删除的行。

2. 使用DELETE命令

DELETE命令可以删除表中的数据，并支持WHERE子句。它比TRUNCATE命令更慢，因为它会记录日志和触发器。但是，它可以选择要删除的行。

3. 使用DROP TABLE命令

DROP TABLE命令可以删除整个表，包括表结构和数据。如果您只想删除表中的数据，请不要使用此命令。

4. 使用备份和恢复

ysqldump命令将表导出到文件中，并在需要时使用导入命令将其恢复。这种方法需要更多的时间和空间。

联合主键

create table grades(
    stu_num char(8),
    course_id int,
    score int,
    primary key(stu_num,course_id)
);

2.DML数据操作语言

• 插入数据

insert into 表名(列名1, 列名2....) values(值1,值2....);

• 修改数据

update 表名 set 列名1=值1，列名2=值2 [where 条件];

• 删除数据

delete from 表名 [where 条件];

3.DQL复杂查询

1、where⼦句 在删除、修改及查询的语句后都可以添加where⼦句（条件），⽤于筛选满⾜特定的添加的数据进 ⾏删除、修改和查询操作。

select ... from 表名 where 条件;

条件关系运算符

# = 等于
select * from stus where stu_num = '20210101';
# != <> 不等于
select * from stus where stu_num != '20210101';
select * from stus where stu_num <> '20210101';
# > ⼤于
select * from stus where stu_age>18;
# < ⼩于
select * from stus where stu_age<20;
# >= ⼤于等于
select * from stus where stu_age>=20;
# <= ⼩于等于
select * from stus where stu_age<=20;
# between and 区间查询 between v1 and v2 [v1,v2]
select * from stus where stu_age between 18 and 20;

条件逻辑运算符

# and 并且 筛选多个条件同时满⾜的记录
select * from stus where stu_gender='⼥' and stu_age<21;
# or 或者 筛选多个条件中⾄少满⾜⼀个条件的记录
select * from stus where stu_gender='⼥' or stu_age<21;
# not 取反
select * from stus where stu_age not between 18 and 20;

2、like⼦句 在where⼦句的条件中，我们可以使⽤like关键字来实现模糊查询 语法：

select * from 表名 where 列名 like 'reg';

•在like关键字后的reg表达式中

◦ % 表⽰任意多个字符

◦ _ 表⽰任意单个字符

# 查询学⽣姓名包含字⺟o的学⽣信息
select * from stus where stu_name like '%o%';
# 查询学⽣姓名第⼀个字为`张`的学⽣信息
select * from stus where stu_name like '张%';
# 查询学⽣姓名最后⼀个字⺟为o的学⽣信息
select * from stus where stu_name like '%o';
# 查询学⽣姓名中第⼆个字⺟为o的学⽣信息
select * from stus where stu_name like '_o%';

3、计算列

# 出⽣年份 = 当前年份 - 年龄
select stu_name,2021-stu_age from stus;
+-----------+--------------+
| stu_name | 2021-stu_age |
+-----------+--------------+
| omg | 2000 |
| Peter | 2003 |
| Tom | 2001 |
| Lucy | 2000 |
| Polly | 2000 |
| Theo | 2004 |
+-----------+--------------

as字段起别名

select stu_name,2021-stu_age as stu_birth_year from stus;
+-----------+----------------+
| stu_name | stu_birth_year |
+-----------+----------------+
| omg | 2000 |
| Peter | 2003 |
| Tom | 2001 |
| Lucy | 2000 |
| Polly | 2000 |
| Theo | 2004 |
+-----------+----------------+
select stu_name as 姓名,2021-stu_age as 出⽣年份 from stus;
+-----------+--------------+
| 姓名 | 出⽣年份 |
+-----------+--------------+
| omg | 2000 |
| Peter | 2003 |
| Tom | 2001 |
| Lucy | 2000 |
| Polly | 2000 |
| Theo | 2004 |
+-----------+--------------+

distinct消除重复⾏

4、排序order by 将查询到的满⾜条件的记录按照指定的列的值升序/降序排列 语法：

select * from 表名 where 条件 order by 列名 asc|desc;

•order by 列名 表⽰将查询结果按照指定的列排序

◦ asc 按照指定的列升序（默认）

◦ desc 按照指定的列降序

# 单字段排序
select * from stus where stu_age>15 order by stu_gender desc;
+----------+-----------+------------+---------+-------------+--------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq |
+----------+-----------+------------+---------+-------------+--------+
| 20210101 | omg | 男 | 21 | 13030303300 | NULL |
| 20210103 | Tom | 男 | 20 | 13030303302 | 777777 |
| 20210105 | Polly | 男 | 21 | 13030303304 | 666666 |
| 20210106 | Theo | 男 | 17 | 13232323322 | NULL |
| 20210102 | Peter | ⼥ | 18 | 13131313311 | 999999 |
| 20210104 | Lucy | ⼥ | 21 | 13131323334 | NULL |
+----------+-----------+------------+---------+-------------+--------+
# 多字段排序 ： 先满⾜第⼀个排序规则，当第⼀个排序的列的值相同时再按照第⼆个列的规则排序
select * from stus where stu_age>15 order by stu_gender asc,stu_age desc;
+----------+-----------+------------+---------+-------------+--------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq |
+----------+-----------+------------+---------+-------------+--------+
| 20210104 | Lucy | ⼥ | 21 | 13131323334 | NULL |
| 20210102 | Peter | ⼥ | 18 | 13131313311 | 999999 |
| 20210101 | omg | 男 | 21 | 13030303300 | NULL |
| 20210105 | Polly | 男 | 21 | 13030303304 | 666666 |
| 20210103 | Tom | 男 | 20 | 13030303302 | 777777 |
| 20210106 | Theo | 男 | 17 | 13232323322 | NULL |
+----------+-----------+------------+---------+-------------+--------+

.5、聚合函数

SQL中提供了⼀些可以对查询的记录的列进⾏计算的函数

•count
 

# 统计学⽣表中学⽣总数

select count(stu_num) from stus;

# 统计学⽣表中性别为男的学⽣总数

select count(stu_num) from stus where stu_gender='男';

•max

#统计表中年龄最大值

select max(stu_age) from stus;

#统计表中性别为女的年龄的最大值

select max(stu_age) from stus where stu_gender='⼥';

•min

#统计表中年龄最小值

select min(stu_age) from stus;

#统计表中性别为女的年龄的最小值

select min(stu_age) from stus where stu_gender='⼥';

•sum

# 计算所有学⽣年龄的综合

select sum(stu_age) from stus;

# 计算所有性别为男的学⽣的年龄的综合

select sum(stu_age) from stus where stu_gender='男';

•avg

# 计算所有学⽣年龄的平均值

select avg(stu_age) from stus;

# 计算所有性别为男的学⽣的年龄的平均值

select avg(stu_age) from stus where stu_gender='男';

6、⽇期函数和字符串函数

⽇期函数 当我们向⽇期类型的列添加数据时，可以通过字符串类型赋值（字符串格式必须为：yyyy-MM-dd hh:mm:ss）

我们可以通过以下函数获取当前系统时间：

•now()

NOW() 函数在执行查询时只会返回一次当前日期和时间。也就是说，如果在同一个查询中多次使用 NOW() 函数，它们返回的值将是相同的。

•sysdate()

SYSDATE() 函数在每次调用时都会返回当前的日期和时间。也就是说，如果在同一个查询中多次使用 SYSDATE() 函数，它们返回的值将是不同的。

# 通过now和sysdate获取当前系统时间

select now();

select sysdate();

7、分组函数group by

语法：

select 分组字段/聚合函数

from 表名 [where 条件]

group by 分组列名 [having 条件] [order by 排序字段]

•select 后通常显⽰分组字段和聚合函数(对分组后的数据进⾏统计、求和、平均值等)

•语句执⾏顺序：

        a. 先根据where条件从数据库查询记录

        b. group by对查询记录进⾏分组

        c. 执⾏having对分组后的数据进⾏筛选

# 先对查询的学⽣信息按性别进⾏分组（分成了男、⼥两组），然后再分别统计每组学⽣的个数

select stu_gender,count(stu_num) from stus group by stu_gender;

# 先对查询的学⽣信息按性别进⾏分组（分成了男、⼥两组），然后再计算每组的平均年龄

select stu_gender,avg(stu_age) from stus group by stu_gender;

# 先对学⽣按年龄进⾏分组（分了16、17、18、20、21、22六组），然后统计各组的学⽣数量，还可
以对最终的结果排序

select stu_age,count(stu_num) from stus group by stu_age order by stu_age;

# 查询所有学⽣，按年龄进⾏分组，然后分别统计每组的⼈数，再筛选当前组⼈数>1的组，再按年龄
升序显⽰出来

select stu_age,count(stu_num) from stus 
group by stu_age 
having count(stu_num)>1 
order by stu_age;

# 查询性别为'男'的学⽣，按年龄进⾏分组，然后分别统计每组的⼈数，再筛选当前组⼈数>1的组，
再按年龄升序显⽰出来

select stu_age,count(stu_num) from stus
where stu_gender='男' group by stu_age
having count(stu_num)>1
order by stu_age;

8、分⻚查询limit

语法： 

select ...

from ...

where ...

limit param1,param2; 

• param1：表⽰获取查询语句的结果中的第⼀条数据的索引（索引从0开始）

• param2：表⽰获取的查询记录的条数（如果剩下的数据条数<param2，则返回剩下的所有记录）

# 查询第⼀⻚：

select * from stus [where ...] limit 0,3; (1-1)*3

# 查询第⼆⻚：

select * from stus [where ...] limit 3,3; (2-1)*3

# 查询第三⻚：

# 如果在⼀张数据表中：
# pageNum表⽰查询的⻚码
# pageSize表⽰每⻚显⽰的条数
# 通⽤分⻚语句如下：

select * from stus [where ...] limit (pageNum-1)*pageSize,pageSize;

三，关联关系

1.分类

         数据与数据之间的 关联关系 分为三种：

•⼀对⼀关联

 

•⼀对多关联与多对⼀关联

•多对多关联

 2.外键约束

1.基本语法

#创建班级
create table classes(
class_id int primary key auto_increment,
class_name varchar(40) not null unique,
class_remark varchar(200)
);

# 【⽅式⼀】在创建表的时候，定义cid字段，并添加外键约束

# 由于cid 列 要与classes表的class_id进⾏关联，因此cid字段类型和⻓度要与 class_id⼀致

create table students(
stu_num char(8) primary key,
stu_name varchar(20) not null,
stu_gender char(2) not null,
stu_age int not null,
cid int,
constraint FK_STUDENTS_CLASSES foreign key(cid) references
classes(class_id)
);

#【⽅式⼆】先创建表，再添加外键约束
create table students(
stu_num char(8) primary key,
stu_name varchar(20) not null,
stu_gender char(2) not null,
stu_age int not null,
cid int
);

# 在创建表之后，为cid添加外键约束

alter table students add constraint FK_STUDENTS_CLASSES foreign key(cid)
references classes(class_id);

# 删除外键约束

alter table students drop foreign key FK_STUDENTS_CLASSES

2.级联

当学⽣表中存在学⽣信息关联班级表的某条记录时，就不能对班级表的这条记录进⾏修改ID和删除

我们可以额通过级联操作来实现修改和删除：

1. 在添加外键时，设置级联删除和级联修改

# 删除原有的外键

alter table students drop foreign key FK_STUDENTS_CLASSES;

# 重新添加外键，并设置级联修改和级联删除

alter table students add constraint FK_STUDENTS_CLASSES foreign key(cid)
references classes(class_id) ON UPDATE CASCADE ON DELETE CASCADE;

3.连接查询

join按照其功能不同分为三个操作：

• inner join 内连接

• left join 左连接

• right join 右连接

1.inner join

select ... from 表1 inner join 表2 on 匹配条件 [where 筛选条件];

笛卡尔积 •笛卡尔积：

使⽤A中的每个记录⼀次关联B中每个记录，笛卡尔积的总数=A总数*B总数 如果直接执⾏ select ... from 表1 inner join 表2; 会获取两种数据表中的数据集 •合的笛卡尔积（依次使⽤表1中的每⼀条记录去匹配表2的每条数据）

两张表时⽤inner join连接查询之后⽣产的笛卡尔积数据中很多数据都是⽆意义的，我们可以添加
两张进⾏连接查询时的条件来消除⽆意义的数据
使⽤ on 设置两张表连接查询的匹配条件，查询结果只获取两种表中匹配条件成⽴的数据，任何⼀张表在另⼀种表如果没有找到对应匹配则不会出现在查询结果中



# 使⽤where设置过滤条件：先⽣成笛卡尔积再从笛卡尔积中过滤数据（效率很低）

select * from students INNER JOIN classes where students.cid =
classes.class_id;


# 使⽤ON设置连接查询条件：先判断连接条件是否成⽴，如果成⽴两张表的数据进⾏组合⽣成⼀条结
果记录


select * from students INNER JOIN classes ON students.cid = classes.class_id;

2.left join

select * from leftTabel LEFT JOIN rightTable ON 匹配条件 [where 条件];

3.right join

select * from leftTabel RIGHT JOIN rightTable ON 匹配条件 [where 条件];

4.数据别名

数据表别名 如果在连接查询的多张表中存在相同名字的字段，我们可以使⽤ 表名.字段名 来进⾏区分，如果 表名太⻓则不便于SQL语句的编写，我们可以使⽤数据表别名

select s.*,c.class_name
from students s
INNER JOIN classes c
ON s.cid = c.class_id;

5.子查询嵌套

⼦查询 — 先进⾏⼀次查询，第⼀次查询的结果作为第⼆次查询的源/条件（第⼆次查询是基于第⼀ 次的查询结果来进⾏的）

⼦查询返回单个值（单⾏单列）
⽰例： 查询班级名称为'Java2104'班级中的学⽣信息


# 如果⼦查询返回的结果是⼀个值（单列单⾏），条件可以直接使⽤关系运算符（= != ....）

select * from students where cid = (select class_id from classes where
class_name='Java2105');


⼦查询返回多个值（多⾏单列）
⽰例： 查询所有Java班级中的学⽣信息


# 如果⼦查询返回的结果是多个值（单列多⾏），条件使⽤IN / NOT IN

select * from students where cid IN (select class_id from classes where
class_name LIKE 'Java%');


⼦查询返回多个值（多⾏多列）
⽰例： 查询cid=1的班级中性别为男的学⽣信息


# 先查询cid=1班级中的所有学⽣信息，将这些信息作为⼀个整体虚拟表(多⾏多列)，再基于这个虚
拟表查询性别为男的学⽣信息（‘虚拟表’需要别名）

select * from (select * from students where cid=1) t where t.stu_gender='男';

四，视图

1、概念

视图，就是 由数据库中⼀张表或者多张表根据特定的条件查询出得数据构造成得虚拟表

2、视图的作用

安全性：如果我们直接将数据表授权给⽤⼾操作，那么⽤⼾可以CRUD数据表中所有数据，加⼊ 我们想要对数据表中的部分数据进⾏保护，可以将公开的数据⽣成视图，授权⽤⼾访问视图；⽤ ⼾通过查询视图可以获取数据表中公开的数据，从⽽达到将数据表中的部分数据对⽤⼾隐藏。

简单性：如果我们需要查询的数据来源于多张数据表，可以使⽤多表连接查询来实现；我们通过 视图将这些连表查询的结果对⽤⼾开放，⽤⼾则可以直接通过查询视图获取多表数据，操作更便 捷。

3、创建视图

create view <view_name>
AS
select_statement


# 查询视图结构
desc view_test2;

4、修改视图

# ⽅式1
create OR REPLACE view view_test1
AS
select * from students where stu_gender='⼥';


# ⽅式2
alter view view_test1
AS
select * from students where stu_gender='男';

5、视图数据的特性

视图是虚拟表，查询视图的数据是来源于数据表的。当对视图进⾏操作时，对原数据表中的数据是 否由影响呢？

• 查询操作：如果在数据表中添加了新的数据，⽽且这个数据满⾜创建视图时查询语句的条件， 通过查询视图也可以查询出新增的数据；当删除原表中满⾜查询条件的数据时，也会从视图中 删除。

• 新增数据：如果在视图中添加数据，数据会被添加到原数据表

• 删除数据：如果从视图删除数据，数据也将从原表中删除

• 修改操作：如果通过修改数据，则也将修改原数据表中的数据 视图的使⽤建议 : 对复杂查询简化操作，并且不会对数据进⾏修改的情况下可以使⽤视图。

五，索引

索引，就是⽤来提⾼数据表中数据的查询效率的。是将数据表中某⼀列/某⼏列的值取出来构造成 便于查找的结构进⾏存储，⽣成数据表的目录 。 当我们进⾏数据查询的时候，则先在目录中进⾏查找得到对应的数据的地址，然后再到数据表中 根据地址快速的获取数据记录，避免全表扫描。

1、索引的分类

MySQL中的索引，根据创建索引的列的不同，可以分为：

•主键索引：在数据表的主键字段创建的索引，这个字段必须被primary key修饰，每张表只能有

⼀个主键

•唯⼀索引：在数据表中的唯⼀列创建的索引(unique)，此列的所有值只能出现⼀次，可以为 NULL

•普通索引：在普通字段上创建的索引，没有唯⼀性的限制

•组合索引：两个及以上字段联合起来创建的索引

注意：

1. 在创建数据表时，将字段声明为主键（添加主键约束），会⾃动在主键字段创建主键索引

2. 在创建数据表时，将字段声明为唯⼀键（添加唯⼀约束），会⾃动在唯⼀字段创建唯⼀索引

2、创建索引

1.唯一索引

# 创建唯⼀索引: 创建唯⼀索引的列的值不能重复

# create unique index <index_name> on 表名(列名);

create unique index index_test1 on tb_testindex(tid);

2.普通索引

# 创建普通索引: 不要求创建索引的列的值的唯⼀性

# create index <index_name> on 表名(列名);

create index index_test2 on tb_testindex(name);

3.组合索引

# 创建组合索引

# create index <index_name> on 表名(列名1,列名2...);

create index index_test3 on tb_testindex(tid,name);

4.全⽂索引

MySQL 5.6 版本新增的索引，可以通过此索引进⾏全⽂检索操作，因为MySQL全⽂检索不⽀持中
⽂，因此这个全⽂索引不被开发者关注，在应⽤开发中通常是通过搜索引擎（数据库中间件）实现
全⽂检索


1 create fulltext index <index_name> on 表名(字段名);

3、使用索引

索引创建完成之后⽆需调⽤，当根据创建索引的列进⾏数据查询的时候，会⾃动使⽤索引； 组合索引需要根据创建索引的所有字段进⾏查询时触发。

explain select * from tb_testindex where tid=250000\G

4、查看索引

# 命令⾏

show create table tb_testindex\G;

# 查询数据表的索引

show indexes from tb_testindex;

# 查询索引

show keys from tb_testindex;

5、删除索引

# 删除索引：索引是建⽴在表的字段上的，不同的表中可能会出现相同名称的索引，因此删除索引时
需要指定表名

drop index index_test3 on tb_testindex;

6、索引使用总结

•优点

◦ 索引⼤⼤降低了数据库服务器在执⾏查询操作时扫描的数据，提⾼查询效率

◦ 索引可以避免服务器排序、将随机IO编程顺序IO

•缺点

◦ 索引是根据数据表列的创建的，当数据表中数据发⽣DML操作时，索引⻚需要更新

◦ 索引⽂件也会占⽤磁盘空间 •注意事项 ◦ 数据表中数据不多时，全表扫⾯可能更快吗，不要使⽤索引

◦ 数据量⼤但是DML操作很频繁时，不建议使⽤索引

◦ 不要在数据重复读⾼的列上创建索引（性别）

◦ 创建索引之后，要注意查询SQL语句的编写，避免索引失效