MySQL介绍及CRUD操作。

清疚 2023-10-02 20:28 27阅读 0赞

**目录**

一、基本介绍

1、数据库结构

2、数据库增删改查

二、增删改查语句

1、insert 添加数据

2、update 修改操作

3、delete 删除操作

4、select 查询操作

5、加密和系统函数

6、流程控制函数

三、表的增删改查

1、创建表

2、修改表

3、表的查询

4、多表查询

5、外连接

--------------------

## 一、基本介绍 ##

> ### 1、数据库结构 ###
> 
> 1、所谓安装Mysql数据库，就是在主机安装一个数据库管理系统(DBMS)，这个管理程序可以管理多个数据库。
> 
> 2、DBMS(database manage system)一个数据库中可以创建多个表，以保存数据(息)。
> 
> ![9b6c9408f5414d47b024ecf7ee210cf1.jpeg][]
> 
> 3、数据在数据库中的存储方式
> 
> ![c0b947fd7e904a05927e8304786ffd32.png][]
> 
> 4、SQL语句分类
> 
> DDL : 数据定义语句\[create表, 库...\]  
> DML : 数据操作语句\[增加 insert, 修改 update, 删除 delete\]  
> DQL : 数据查询语句 \[select\]  
> DCL : 数据控制语匀\[管理数据库 : 比如用户权限 grant revoke\]
> 
> ### 2、数据库增删改查 ###
> 
> **▶ 创建数据库**
> 
>     create database 数据库名
> 
> **character set :** 指定数据库采用的字符集, 如果不指定字符集 默认utf8
> 
> **collate :** 指定数据库字符集的校对规则(常用的 utf8\_bin \[区分大小写\]、utf8\_general\_ci \[不区分大小写\](注意默认是utf8\_general\_ci)
> 
> **▶ 删除数据库**
> 
>     drop database 数据库名
> 
> **▶ 显示数据库**
> 
>     #查看当前数据库服务器中的所有数据库 
>     show databases
>     
>     #查看前面创建的 hsp_db01 数据库的定义信息 
>     show create database 数据库名
> 
> 注意：在创建数据库,表的时候，为了规避关键字，可以使用反引号解决
> 
> **▶ 备份数据库**
> 
>     #备份, 要在 Dos 下执行 mysqldump 指令，其实在 mysql 安装目录\bin 
>     #这个备份的文件，就是对应的 sql 语句 
>     
>     mysqldump -u root -p -B 数据库1 数据2 > 文件名.sql
> 
> **▶ 恢复数据库**
> 
>     #恢复数据库(注意：进入 Mysql 命令行再执行) 
>     source 文件名.sql 
>     
>     #第二个恢复方法， 直接将 bak.sql 的内容放到查询编辑器中，执行
> 
> **▶ 恢复数据库的表**
> 
>     mysqldump -u 用户名 -p密码 数据库 表1 表2... > 文件名.sql

## 二、增删改查语句 ##

> ### 1、insert 添加数据 ###
> 
>     CREATE TABLE `goods` (
>     	id INT ,
>     	goods_name VARCHAR(10), 
>     	price DOUBLE NOT NULL DEFAULT 100 );
>     
>     -- 添加数据
>     INSERT INTO `goods` (id, goods_name, price) 
>     	VALUES(10, '华为手机', 2000);
>     
>     INSERT INTO `goods` (id, goods_name, price) 
>     	VALUES(20, '苹果手机', 3000);
>     
>     SELECT * FROM goods;
> 
> **▶ insert 语句的细节**
> 
>  *  插入的数据应与字段的数据类型相同。比如 把 'abc' 添加到 int 类型会错误
>  *  数据的长度应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
>  *  在values中列出的数据位置必须与被加入的列的排列位置相对应。
>  *  字符和日期型数据应包含在单引号中。
>  *  列可以插入空值\[前提是该字段允许为空\]，如：insert into table value(null)
>  *  insert into 列名.. values (),(),() 形式添加多条记录
>  *  如果是给表中的所有字段添加数据，可以不写前面的字段名称
>  *  默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错。如果某个列 没有指定 not null ,那么当添加数据时，没有给定值，则会默认给null，如果我们希望指定某个列的默认值，可以在创建表时指定。
> 
> ### 2、update 修改操作 ###
> 
>     # 如果没有带where 条件，会修改所有的记录，因此要小心
>     UPDATE 表 SET 要修改的数据
>     
>     # 加上条件
>     UPDATE 表
>     	SET 要修改的数据 
>     	WHERE 条件 
>     
>     # 可以修改多个列的值
>     UPDATE 表
>     	SET 数据1 数据2...
>     	WHERE 条件
> 
> **▶ 使用细节**
> 
>  *  UPDATE语法 可以用新值更新原有表行中的各列。
>  *  SET子句指示要修改哪些列和要给予哪些值。
>  *  WHERE子句指定应更新哪些行。 如没有WHERE子句，则更新所有的行(记录）
>  *  如果需要修改多个字段，可以通过 set字段1-值1,字段2-值2...
> 
> ### 3、delete 删除操作 ###
> 
>     # 有条件的删除
>     DELETE FROM 表名
>     	WHERE 条件;
>     
>     # 删除表中所有记录
>     DELETE FROM 表名;
>     
>     # Delete语句不能删除某一列的值（可使用update 设为 null 或者 ''）
>     UPDATE 表名
>         SET 要修改的列
>         WHERE 条件;
>     
>     # 要删除表
>     DROP TABLE 表名;
> 
> **▶ 使用细节**
> 
>  *  如果不使用where子句，将删除表中所有数据。
>  *  Delete语句不能删除某一列的值(可使用update 设为null或者“)
>  *  使用delete语句仅删除记录, 不删除表本身。如要删除表，使用drop table语句。 drop table 表名。
> 
> ### 4、select 查询操作 ###
> 
>     # 查询表中所有信息。
>     SELECT * FROM 表名;
>     
>     # 查询表中某些信息。
>     SELECT 列名1 列名2.. FROM 表名;
>     
>     # 过滤表中重复数据 distinct 。
>     SELECT DISTINCT 列名 FROM 表名;
>     
>     # 要查询的记录，每个字段都相同，才会去重
>     SELECT DISTINCT 列名1 列名2... FROM 表名;
> 
>     # 统计
>     SELECT COUNT(*) FROM 表名;
>     
>     SELECT COUNT(*) FROM 表名
>     	WHERE 条件
>     
>     # count(*) 和 count(列) 的区别：
>     #     count(*) 返回满足条件的记录的行数
>     #     count(列): 统计满足条件的某列有多少个，但是会排除 为null的情况
> 
>     SELECT column_name as 别名 from 表名;
> 
>     # sum 统计总和
>     SELECT SUM(列名) FROM 表名;
> 
>     # avg 求平均数
>     SELECT AVG(列名) FROM 表名;
> 
>     # max 找最大值
>     SELECT MAX(列名) FROM 表名;
>     
>     # min 找最小值
>     SELECT MIN(列名) FROM 表名;
> 
> **▶ where 语句常用运算符**
> 
> ![23613f2478dc4726b890f694ffad8a25.png][]
> 
> **▶ order by 排序**
> 
>  *  Order by指定排序的列，排序的列既可以是表中的列名,也可以是select语句后指定的列名。
>  *  Asc升序\[默认\]、Desc降序
>  *  ORDER BY子句应位于SELECT语句的结尾。
> 
>     SELECT * FORM 表名
>        wHERE 条件
>        ORDER BY 列名 ASC/DESC
> 
> **▶ group by 对列进行分组**
> 
>     SELECT 列名 FORM 表名
>         GROUP BY 列名
> 
> **▶ having 分组后过滤**
> 
>     SELECT 列名 FORM 表名
>         GROUP BY 列名 HAVING
> 
> **▶ 字符串相关函数**
> 
> ![1dd1038219924be6a12ec9a1f1f4412e.png][]
> 
> **▶ 数学相关函数**
> 
> ![dec595aee7eb44508cbdd146e19b391d.png][]
> 
> **▶ 日期函数**
> 
> ![3d509b056a724429bb5b3039e55be513.png][]
> 
> ![28c699a429fe445684b2e6070bc27ee1.png][]
> 
> ![48bc2eb2ce2348a0b9b535a13e710f65.png][]
> 
> ![d6395ad258e54fe3ad2f31ecb8ca150e.png][]
> 
> ### 5、加密和系统函数 ###
> 
> ![39a4afa94d164b42a1c5ff550fd3f19b.png][]
> 
> ### 6、流程控制函数 ![87a5dd81ecb240edaaf65f9d6e3d7d87.png][] ###

## 三、表的增删改查 ##

> ### 1、创建表 ###
> 
> **▶ 创建表**
> 
>     create table 表名 (
>             field1 datatype,
>     
>             field2 datatype,
>     
>             field3 datatype
>     
>     ) character set 字符集 collate 校对规则 engine 存储引擎
> 
>  **▷ field :** 指定列名
> 
>  **▷ datatype :** 指定列类型(字段类型)
> 
>  **▷ character set :** 如不指定则为所在数据库字符集  
>  **▷ collate :** 如不指定则为所在数据库校对规则  
>  **▷ engine :** 存储引擎
> 
>     CREATE TABLE `user` ( 
>         id INT, 
>         `name` VARCHAR(255), 
>         `password` VARCHAR(255), 
>         `birthday` DATE) 
>          CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
> 
> **▶ 常用数据类型**
> 
> ![34d06e6b41734ff880a48b041cc50a28.png][]
> 
> **▶ 数值类型**
> 
> ![bd32e180d933497b924e10354f028f27.png][]
> 
> ▷ 如果没有指定 unsinged , 则 tinyint 就是有符号 
> 
>  ▷ 如果指定  unsinged ,  则  tinyint  就是无符号  0-255
> 
> **▶ 定义无符号数值**
> 
>     create table 表1 (id tinyint) //默认是有符号的
>     
>     create table 表2 (id tinyint unsigned) //有符号的
> 
> **▶ bit 的使用**
> 
>  ▷ bit(m为指定位数) m范围 在  1-64 
> 
>  ▷ 添加数据 范围 按照你给的位数来确定，比如  m = 8  表示一个字节  0~255 
> 
>  ▷ 显示按照  bit 
> 
>  ▷ 查询时，仍然可以按照数来查询 
> 
>     CREATE TABLE t (num BIT(8));
>     INSERT INTO t VALUES(255);
>     SELECT * FROM t;
>     SELECT * FROM t WHERE num = 1;
> 
> **▶ 小数的使用**
> 
>  **FLOAT / DOUBLE \[UNSIGNED\]** ：Float 单精度精度，Double 双精度  
>  **DECIMAL\[M , D\] \[UNSIGNED\] :**  
> 可以支持更加精确的小数位。M是小数位数(精度)的总数，D是小数点(标度）  
> 后面的位数。如果D是0, 则值没有小数点或分数部分。M最大65。 D最大是30。如果D被省略， 默认是0。 如果M被省略, 默认是10。建议:如果希望小数的精度高, 推荐使用decimal。
> 
> **▶ 字符串基本使用**
> 
> **▷ CHAR(size) ：固定长度字符串 最大 255**  **字符** 
> 
> **▷ VARCHAR(size) ：可变长度字符串 ，0~65535 字节** 
> 
>  *  可变长度字符串 最大 65532 字节 【utf8 编码最大 21844 字符 1-3 个字节用于记录大小】 
>  *  如果表的编码是 utf8 varchar(size)， size = (65535-3) / 3 = 21844 
>  *  如果表的编码是 gbk varchar(size)， size = (65535-3) / 2 = 32766
> 
> **▷ 使用细节：**
> 
>  *  char(4)：这个4表示字符数(最大255)，不是字节数，不管是中文还是字母都是放四个,按字符计算
>  *  varchar(4)：这个4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据  
>     不管是中文还是英文字母，都是最多存放4个，是按照字符来存放的.
>  *  char(4)是定长(固定的大小)，就是说，即使你 插入'aa'，也会占用分配的4个字符的空间；varchar(4) 是变长（变化的大小)， 就是说， 如果你插入了'aa',实际占用空间大小并不是4个字符，而是按照实际占用空间来分配（varchar本身还需要占用1-3个字节来记录存放内容长度) ；\[ L(实际数据大小)+(1-3)字节 \]
>  *  什么时候使用 char, 什么时候使用varchar : ①如果数据是定长，推荐使用char, 比如md5的密码，邮编，手机号，身份证号码等.char(32) ; ②如果一个字段的长度是不确定，我们使用varchar. 比如留言,文章。
>  *  查询速度: char > varchar
>  *  在存放文本时，也可以使用Text 数据类型.可以将TEXT列视为VARCHAR列，注意 Text不能有默认值.大小0-2\*16字节如果希望存放更多字符，可以选择MEDIUMTEXT：0-2\*24 或者 LONGTEXT：0～2\*32 。
> 
> **▶ 日期类型基本使用**
> 
>     # timestamp : 时间戳
>     
>     CREATE TABLE birthday (
>         t1 DATE, 
>         t2 DATETIME,
>         t3 TIMESTAMP NOT NULL DEFAULT
>         CURRENT TIMESTAMP ON UPDATE
>         CURRENT TIMESTAMP ); 
>     
>     
>     INSERT INTO birthday (t1,t2)
>         VALUES( 2022-11-11  2022-11-11 10:10:10):
> 
> ### 2、修改表 ###
> 
>     # 增加一个列，varchar 类型(要求在 resume 后面)。
>     
>     ALTER TABLE 表名
>         ADD 列名 VARCHAR(32) NOT NULL DEFAULT ''
>         AFTER RESUME
> 
>     # 显示表结构，可以查看表的所有列
>     
>     DESC 表名
> 
>     # 修改列，使其长度为 60。
>     
>     ALTER TABLE 表名
>         MODIFY 列名 VARCHAR(60) NOT NULL DEFAULT ''
> 
>     # 删除列。
>     
>     ALTER TABLE 表名
>         DROP 列名
> 
>     # 表名修改。
>     
>     RENAME TABLE 原表名 TO 新表名
> 
>     # 修改表的字符集
>     
>     ALTER TABLE 表名 CHARACTER SET 字符集
> 
>     # 修改列名
>     
>     ALTER TABLE 表名
>     CHANGE 原列名 新列名 VARCHAR(64) NOT NULL DEFAULT ''
> 
> ### 3、表的查询 ###
> 
> **▶ 表查询加强**
> 
> **% :** 表示  0  到多个任意字符 
> 
> **\_ :** 表示单个任意字符
> 
>     SELECT 列名 FROM 表名
>         WHERE 列名 LIKE 'S%' -- 从列名中找 S 开头的数据
>     
>     SELECT 列名 FROM 表名
>         WHERE 列名 LIKE '__O%'  -- 从列中找 第二个字母为 O 的数据
> 
> **▶ 分页查询**
> 
>     # 推导公式   
>     SELECT * FROM 表名
>         ORDER BY 列名
>         LIMIT 每页显示记录数 * (第几页 - 1) , 每页显示记录数
> 
> **▶ 数据分组总结**
> 
> 如果 select语句 同时包含有 group by , having, limit，order by那么他们的顺序是group by, having , order by, limit
> 
> ![668e870047ff4539bec4ffb4c70a056e.png][]
> 
> ### 4、多表查询 ###
> 
> **▶ 基本说明**
> 
> 多表查询是指基于两个或两个以上的表查询。在实际应用中，查询单个表可能不能满足你的需求。
> 
> 多表查询的条件不能少于 表的个数 \-1,  否则会出现笛卡尔集。
> 
>     SELECT 列名1，列名2.... 
>         FROM 表1，表2.... 
>         WHERE 查询条件  -- 多表查询的条件不能少于 表的个数-1
> 
> **▶ 自连接**
> 
> 自连接是指在同一张表的连接查询，（将同一张表看做两张表来查询）
> 
>  特点：①把同一张表当做两张表使用，②需要给表取别名， (表名 as 表别名)，③列名不明确，可以指定列的别名 (列名 as 列的别名)
> 
>     SELECT 列名 AS 列的别名 , 列名 AS 列的别名
>         FROM 表中的某列, 表中的某列
>         WHERE 条件
> 
> **▶ 表的子查询**
> 
> ▷ 基本介绍：子查询是指嵌入在其它 sql 语句中的 select 语句,也叫嵌套查询
> 
> ▷ 单行子查询：是指只返回一行数据的子查询语句
> 
> ▷ 多行子查询：指返回多行数据的子查询，使用关键字 in
> 
>     SELECT * 
>         FROM 表 
>         WHERE 某个列 = ( -- 括号中为子查询 
>             SELECT 列名
>                 FROM 表名
>                 WHERE 条件
>             )
> 
>     SELECT * 
>         FROM 表 
>         WHERE 某个列 in ( -- 多行子查询用关键字 in
>             SELECT 列名
>                 FROM 表名
>                 WHERE 条件
>             ) and 条件
> 
> **▶ all 和 any 的使用**
> 
>     SELECT 列名...
>         FROM 表名
>         WHERE 列名 > ALL( -- 表示大于all子查询中的最大的
>             SELECT 列名
>                 FROM 表名
>                 WHERE 条件
>     )
> 
>     SELECT 列名...
>         FROM 表名
>         WHERE 列名 > ANY( -- 表示大于any子查询中的最小的
>             SELECT 列名
>                 FROM 表名
>                 WHERE 条件
>     )
> 
> **▶ 表的复制**
> 
>     INSERT INTO 表1 
>         (列名1，列名2，列名3....) 
>         SELECT 列名1，列名2，列名3.... FROM 表2;
> 
>     # 自我复制
>     INSERT INTO 表1
>         SELECT * FROM 表1;
>     
>     SELECT COUNT(*) FROM 表1;
> 
> **▶ 如何删除一张表的重复记录**
> 
> 1.  先创建一张临时表 , 该表的结构和 原表 一样 
> 2.  把 临时表 的记录 通过 distinct 关键字 处理后 把记录复制到 临时表（即自我复制）
> 3.  清除掉 原表 记录 
> 4.  把 临时 表的记录复制到 原表
> 5.  drop 掉 临时表 
> 
> **▶ 合并查询**
> 
>  union all 就是将两个查询结果合并，不会去重
> 
>     SELECT 列名.... FROM 表名 WHERE 条件 
>     UNION ALL 
>     SELECT 列名.... FROM 表名 WHERE 条件
> 
>  union 就是将两个查询结果合并，会去重
> 
>     SELECT 列名.... FROM 表名 WHERE 条件 
>     UNION  
>     SELECT 列名.... FROM 表名 WHERE 条件
> 
> ### 5、外连接 ###
> 
> 左外连接 (如果左侧的表完全显示我们就说是左外连接）
> 
>     select.. from 表1 
>         left join 表2 
>         on 条件[表1:就是左表，表2:就是右表]
> 
> 右外连接(如果右侧的表完全显示我们就说是右外连接）
> 
>     select.... from 表1 
>         right join 表2 
>         on 条件[表1:就是左表，表2：就是右表]

##  ##

[9b6c9408f5414d47b024ecf7ee210cf1.jpeg]: https://img-blog.csdnimg.cn/9b6c9408f5414d47b024ecf7ee210cf1.jpeg
[c0b947fd7e904a05927e8304786ffd32.png]: https://img-blog.csdnimg.cn/c0b947fd7e904a05927e8304786ffd32.png
[23613f2478dc4726b890f694ffad8a25.png]: https://img-blog.csdnimg.cn/23613f2478dc4726b890f694ffad8a25.png
[1dd1038219924be6a12ec9a1f1f4412e.png]: https://img-blog.csdnimg.cn/1dd1038219924be6a12ec9a1f1f4412e.png
[dec595aee7eb44508cbdd146e19b391d.png]: https://img-blog.csdnimg.cn/dec595aee7eb44508cbdd146e19b391d.png
[3d509b056a724429bb5b3039e55be513.png]: https://img-blog.csdnimg.cn/3d509b056a724429bb5b3039e55be513.png
[28c699a429fe445684b2e6070bc27ee1.png]: https://img-blog.csdnimg.cn/28c699a429fe445684b2e6070bc27ee1.png
[48bc2eb2ce2348a0b9b535a13e710f65.png]: https://img-blog.csdnimg.cn/48bc2eb2ce2348a0b9b535a13e710f65.png
[d6395ad258e54fe3ad2f31ecb8ca150e.png]: https://img-blog.csdnimg.cn/d6395ad258e54fe3ad2f31ecb8ca150e.png
[39a4afa94d164b42a1c5ff550fd3f19b.png]: https://img-blog.csdnimg.cn/39a4afa94d164b42a1c5ff550fd3f19b.png
[87a5dd81ecb240edaaf65f9d6e3d7d87.png]: https://img-blog.csdnimg.cn/87a5dd81ecb240edaaf65f9d6e3d7d87.png
[34d06e6b41734ff880a48b041cc50a28.png]: https://img-blog.csdnimg.cn/34d06e6b41734ff880a48b041cc50a28.png
[bd32e180d933497b924e10354f028f27.png]: https://img-blog.csdnimg.cn/bd32e180d933497b924e10354f028f27.png
[668e870047ff4539bec4ffb4c70a056e.png]: https://img-blog.csdnimg.cn/668e870047ff4539bec4ffb4c70a056e.png