MySQL基础

一，SQL的介绍

1，数据库的介绍

        用于存储和管理数据的仓库

        英文单词为DataBase，简称DB

        它的存储空间很大，可以存放百万条，千万条，上亿条数据

        使用一种同意的方式操作数据库----SQL

2，SQL介绍

        SQL：结构化查询语言，其实就是定义了操作所有关系型数据库的一种规则

通用语法规则：

        SQL语句可以单行或多行书写，以分号结尾

        可以使用空格和缩进来整强语句的可读性

        MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

        单行注释：-- ， #

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

SQL分类

        DDL：数据定义语言，用来操作数据库，表，列等

        DML：数据操作语言，用来对数据库中表的数据进行增删改

        DQL：数据查询语言，用来查询数据库中表的记录（数据）

        DCL：数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户

        

二，DDL数据定义语言

查询和创建数据库

1，查询所有数据库

show databases;

2，查询数据库的创建语句

show create database 数据库名称;

3，创建数据库

create database 数据库名称;

4，创建数据库（判断，如果不存在则创建）

create database if not exists 数据库名称;

5，创建数据库（指定字符集）

create database 数据库名称 character set 字符集名称;

修改、删除、使用数据库

1，修改数据库（修改字符集）

alter database 数据库名称 character set 字符集名称;

2，删除数据库

drop database 数据库名称;

3，删除数据库（判断，如果存在则删除）

drop database if exists 数据库名称;

4，使用数据库

use 数据库名称;

5，查看当前使用的数据库

select database();

查询数据表

1，查询所有的数据表

show tables;

2，查询表结构

desc 表名;

3，查询表字符集

show table status from 库名 like '表名';

创建数据表

create table 表名(
    列名 数据类型 约束，
    列名 数据类型 约束，
    ...
    列名 数据类型 约束
);

数据类型

        int ：整数类型

        double ：小数类型

        date ： 日期类型。包含年月日，格式yyyy-MM-dd

        datetime：日期类型。包含年月日时分秒，格式yyyy-MM-dd HH：mm：ss

        timestamp：时间戳类型。包含年月日时分秒，

                                如果不给该列赋值，或赋值为null，则默认使用当前系统时间自动赋值

        varchar（长度）：字符串类型

修改数据表

1，修改表名

alter table 表名 rename to 新表名;

2，修改表的字符集

alter table 表名 character set 字符集名称;

3，单独添加一列

alter table 表名 add 列名 数据类型;

4，修改某列的数据类型

alter table 表名 modify 列名 新数据类型;

5，修改列名和数据类型

alter table 表名 change 列名 新列名 新数据类型;

6，删除某一列

alter table 表名 drop 列名;

删除数据表

删除数据表

drop table 表名;

删除数据表（判断，如果存在则删除

drop table if exists 表名;

三，DML数据操作语言

DML新增表数据

给指定列添加数据

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

给全部列添加数据

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

批量添加数据

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

 列名和值的数量以及数据类型要对应，除了数字类型，其他类型的数据都需要加引号（推荐单引号）

DML修改和删除表数据

修改表中的数据

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

修改语句中必须加条件，如果不加，则会将所有数据都修改

删除表中的数据

delete from 表名  [where 条件];

删除语句中必须加条件，如果不加条件，则会将所有数据都删除

四，DQL数据查询语言

1，DQL表数据查询——查询语法

select
    字段列表
from
    表名列表
where
    条件列表
group by
    分组字段
having
    分组后的过滤条件
order by
    排序
limit
    分页；

1，查询全部的表数据

select * from 表名;

2,查询指定字段的表数据

select 列名1，列名2，... from 表名;

3,去除重复查询

select distinct  列名1，列名2，...from 表名;

4，计算列的值（四则运算）

select 列名1 运算符（+，-，*，/）列名2 from 表名;

如果某一列为null，可以进行替换

ifnull（表达式1，表达式2）

表达式1：想替换的列

表达式2：想替换的值

select ifnull(列名，替换的值）  from 表名；

5,别名查询

select 列名 as 别名 from  表名;

2，DQL表数据查询——条件查询

查询条件分类

符号	功能
>	大于
<	小于
>=	大于等于
<=	小于等于
=	等于
<>或者！=	不等于
between...and...	在某个范围之内（都包含）
in(...)	多旋一
like  占位符	模糊查询，_单个任意字符，%多个任意字符
is null	是null
is not null	不是null
and或&&	并且
or或||	或者
not或！	非，不是

2，条件查询语法

select 列表名 from 表名 where 条件；

3，DQL表数据查询——聚合函数查询

聚合函数的介绍

        将一列数据作为一个整体，进行纵向的计算

集合函数的分类

        

函数名	功能
count（列名）	统计数量（一般选用不为null的列）
max（列名）	最大值
min（列名）	最小值
sum（列名）	求和
avg（列名）	平均值

聚合函数查询语法

select 函数名（列名） from 表名 [where 条件];

4,DQL表数据查询——排序查询

排序查询语法

select 列名列表 from 表名 [where 条件] order by 列名 排序方式，列名，排序方式...;

排序方式：ASC 升序，DESC 降序；

如果有多个条件，只有当前边条件值一样时，才会判断第二个条件。 

5，DQL表数据查询——分组查询

分组查询语法

select 列名列表 from 表名 [where 条件] group by 分组列名
[hiving 分组后的条件过滤]
[order by 排序列名 排序方式];

6，DQL表数据查询——limit分页查询

分页查询语法

select 列名列表 from 表名
[where 条件]
[group by 分组列名]
[having 分组后的条件过滤]
[order by 排序列名 排序方式]
limit 当前页数，每页显示的条数；

LIMIT 当前页数，每页显示的条数

公式：当前页数 = （当前页数-1）*每页显示的条数

五，约束

什么是约束

        对表中的数据进行限定，保证数据的正确性，有效性，完整性！

约束的分类

约束	作用
PRIMARY KEY	主键约束
PRIMARY KEY AUTO_INCREMENT	主键自增
UNIQUE	唯一约束
NOT NULL	非空约束
FOREIGN KEY	外键约束
FOREIGN KEY ON UPDATE CASCADE	外键级联更新
FOREIGN KEY ON DELETE CASCADE	外键级联删除

1，主键约束

主键约束的特点

        主键约束默认包含非空和唯一两个功能

        一张表只能有一个主键

        主键一般用于表中数据的唯一标识

建表时加主键约束

create table 表名(
    列名 数据类型 primary key，
    ...
    列名 数据类型
);

删除主键约束

slter table 表名 drop primary key；

建表后单独添加主键约束

anter table 表名 modify 列名 数据类型 primary key ；

2，主键自增约束

建表时添加主键自增约束

create table 表名(
    列名 数据类型 primary key auto_increment,
    ...
    列名 数据类型
);

删除主键自增约束

alter table 表名 modify 列名 数据类型；

建表后单独添加主键自增约束

alter table 表名 modify 列名 数据类型 auto_increment;

MySQL中的自增约束，必须配合键的约束一起使用。

3，唯一约束

建表时添加唯一约束

create table 表名 （
    列名 数据类型 unique，
    ...
    列名 数据类型 约束
）；

删除唯一约束

alter table 表名 drop index 列名；

建表后单独添加唯一约束

alter table 表名 modify 列名 数据类型 unique；

4，非空约束

建表的时添加非空约束

create table 表名 （
    列名 数据类型 not null，
    ...
    列名 数据类型 约束
）；

删除非空约束

alter table 表名 modify 列名 数据类型；

建表后单独添加非空约束

alter table 表名 modify 列名 数据类型 not null；

5，外键约束

当表与表之间的数据有相关联性的时候，如果没有相关的数据约束，则无法保证数据的准确性！

外键约束的作用

        让表与表之间产生关联关系，从而保证数据的准确性

建表时添加外键约束

create table 表名（
    列名 数据类型 约束，
    ...
    列名 数据类型 约束，
    constraint 外键名 foreign key （本表外键列名） references 主表名（主表主键列名）
）；

删除外键约束

alter table 表名 drop foreign key 外键名；

建表后添加外键约束

alter table 表名 add
constraint 外键名 foreign key （本表外键列名） references 主表名（主表主键列名）；

6，外键级联更新和级联删除

级联更新：当想把主表中的数据进行修改时，我期望从表中有关联的数据也会随之修改

级联删除：当想把主表中的数据进行删除时，我期望从表中有关联的数据也会随之删除

添加级联更新

alter table 表名 add
constraint 外键名 foreign key （本表外键列名） references 主表名（主键列名）
on update cascade；

添加级联删除

alter table 表名 add
constraint 外键名 foreign key （本表外键列名） references 主表名（主键列名）
on delete cascade；

同时添加级联跟新和删除

alter table 表名 add
constraint 外键名 foreign key （本表外键列名） references 主表名（主键列名）
on update cascade on delete cascade；

六，多表查询

1，多表概述

        就是多张数据表，而表与表之间是可以有一定的关联关系，这种关联关系通过外键约束实现

多表的分类

        一对一：

适用场景：任何身份证，一个人只有一个省份证，一个身份证只能对应一个人

建表原则：在任意一个表建立外键，去关联另外一个表的主键

        一对多：

适用场景：用户和订单。一个用户可以有多个订单。

                商品分类和商品，一个分类下可以有多个商品

建表原则：在多的一方，建立外键约束，来关联另一方的主键

        多对多：

适用场景：学生和课程。一个学生可以选择多个课程，一个课程可以被多个学生选择

建表原则：需要借助第三张表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键

2，多表查询

分类：内连接查询，外连接查询，子查询，自关联查询

1，内连接查询

查询原理：内连接查询的是两张表有交集的部分数据（有主外键关联的数据）

查询语句：

        显式内连接：

select 列名 from 表名1 [inner] join 表名2 on 条件；

        隐式内连接：

select 列名 from 表名1，表名2 where 条件；

2，外连接查询

左外连接：

        查询原理：查询左表的全部数据，和左右两张表有交集部分的数据

        查询语法：

select 列名 from 表名1 lift [outer] join 表名2 on 条件；

右外连接：

        查询原理：查询右表的全部数据，和左右两张表右交集部分的数据

        查询语法：

select 列名 from 表名1 right [outer] join 表名2 on 条件；

3，子查询

子查询概述：查询语句中嵌套了查询语句，就是子查询

结果是单行单列的

        查询作用：可以将查询的结果作为另一条语句的查询条件，使用运算符判断。

        查询语句：

select 列名 from 表名 where 列名=（select 列名 from 表名 [where 条件]）;

结果是多行单列的

        查询作用：可以作为条件，使用运算符in或者ont in 进行判断。

        查询语法：

select 列名 from 表名 where 列名 [not] in （select 列名 from 表名 [where] 条件）;

结果是多行多列的

        查询作用：查询的结果可以作为一张虚拟表参与查询

        查询语法：

select 列名 from 表名 [别名]，（select 列名 from 表名 [where 条件]）[别名] [where 条件]；

4，自关联查询

在同一张表中数据有关联性，我们可以把这张表当成多个表来查询

七，视图

视图：是一种虚拟存在的数据表，这个虚拟表并不在数据库中实际存在

作用：将一些较为复杂的查询语句的结果，封装到一个虚拟表中，后期再有相同的需求时，直接查询该虚拟表即可。

创建视图语法：

create view 视图名称 [(列名列表)] as 查询语句;

查询视图语法：

select * from 视图名称;

修改视图数据语法：

update 视图名称 set 列名=值 where 条件;

 修改视图数据的时候，源表中的数据也会随之修改

修改视图结构语法：

alter view 视图名称（列名列表） as 查询语句;

删除视图语法：

drop view [if exists]  视图名称;

八，数据库备份和恢复

1，备份

        登录MySQL服务器，输入 mysqldump -u root -p 数据库名称 > 文件保存路径

2，恢复

        登录MySQL数据库

        删除已经备份的数据库

        重新创建名称相同的数据库

        使用该数据库

        导入文件执行：source 备份文件全路径