Mysql：数据库

一、数据库概念？

    mysql是一套基于CS模式，给我们一个提供数据存取服务的网络步伐。日常口语中的数据库实在是指在磁盘或内存中存储的特定构造布局的数据。
 存储数据最常见的方式就是直接用文件存储。但文件存储存在诸多缺陷：
  

• 安全性问题！普通文件没有权限约制，数据可以随意被访问，存在数据安全问题。
  
• 文件倒霉于海量数据的查询和管理。比如必要在100万数据中查找主机ip为198.1.1.0的ip地址的个数。对于文件而言必要步伐员将文件打开，然后按行遍历、判断、统计等工作。非常麻烦，效率低下。 但对于数据库而言，数据库本质实在就是一套对数据内容存取的一套解决方案。步伐员将相关字段和要求传给数据库即可，数据库会将结果直接反馈呈现给步伐员！
  
• 文件在步伐中不方便控制。文件系统缺乏事务管理、并发控制等机制，难以保证数据的同等性和完整性。MySQL架构
  

   宏观上数据库是指：数据库客户端、服务端以及数据库文件。而口语中思的数据库指数据在磁盘上存取的一套解决方案（create databases创建出来的数据库）
  在Linux中，数据库本质就是/var/lib/mysql目次下的一个目次文件，而建表本质就是该该目次下的一些普通文件。
二、MySQL架构

 第一层主要解决毗连和安全问题。第二层类似于编译器主要进行词法分析、语法分析、以及mysql语句优化。第三次为各种存储引擎，是真正和OS系统进行交换，实现功能的。
 存储引擎是：数据库管理系统怎样存储数据、怎样为存储的数据建立索引和怎样更新、查询数据等技能的实现方法（show engines;查看）

三、SQL语句分类

 sql语言分为以下3类：

• 数据界说语言（DDL）,用于维护存储数据的布局。代表指令：create、drop。
  
• 数据利用语言（DML）,用来对数据进行操作。代表指令：insert、delete、update。
  
• 数据控制语言（DCL）,主要负责权限管理和事务。代表指令： grant、revoke、commit。
  

四、数据库操作

4.1 数据库创建

 指令：create database [if not exists] db_name [charset = xxx] [collate = xxx];

• charset （或character set）:数据库字符集，数据库存储数据的编码格式！
  
• collate ：数据库校验集，数据库将数据读取出来时所接纳的编码格式！
  

4.2 数据库字符集和校验规则

作用指令查看系统默认字符集show variables like 'character_set_database';查看系统默校验规则show variables like 'collation_database';查看数据库支持的字符集show charset;查看数据库支持的校验规则show collation; 4.3 数据库修改

 指令:alter database db_name [charset = xxx] [collate = xxx];

• 数据库的修改主要是对数据库的字符集和校验规则进行修改，但必要保证修改后的字符集和校验规则匹配，否则数据的存储或读取可能出现乱码。
  

4.4 数据库删除

 指令:drop database db_name [if not exists];

• 数据库的创建本质就是在/var/lib/mysql下创建的目次文件，而数据库删除的本质就是该目次文件被删除。
  
• 数据库删除后，对应的数据库文件夹被删除，级联删除，内里的数据表全部被删。
  

4.4 数据库备份和恢复

作用指令样例数据库备份mysqldump -P3306 -u root -p -B 数据库名 > 文件路径mysqldump -P3306 -u root -p123456 -B mytest > mytest.sql数据库恢复source 文件路径;source D:/mysql-5.7.22/mytest.sql;备份表mysqldump -u root -p 数据库名 表名1 表名2 > mytest.sql;备份多个数据库mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路径

• 假如备份一个数据库时，没有带上-B参数， 在恢复数据库时，必要先创建空数据库，然后利用数据
  库，再利用source来还原。
  

其他

• 查看毗连环境指令：show processlist;
  

五、表操作

5.1 创建表

1. CREATE TABLE table_name (
2. field1 datatype,
3. field2 datatype,
4. field3 datatype
5. ) character set 字符集 collate 校验规则 engine 存储引擎;

复制代码

• field指明表布局的列名，datatype指明列名类型。
  
• 对于字符集、校验规则、存储引擎假如没有显示指定，默认接纳表地点的数据库相关属性。
  

5.2 修改表

 修改表的布局有，比如字段名字，字段大小，字段类型，表的字符集类型，表的存储引擎等等。我以及添加字段，删除字段等等。
作用指令样例添加字段alter table tablename add [field datatype] [,field datatype]....;在原有表布局基础上，即birthday列之后添加assets 列：alter table users add assets varchar(100) comment '图片路径' after birthday;删除字段alter table tablename drop (column);删除name列： alter table users dropname ;修改字段类型alter table tablename modify [field datatype] [,field datatype]....;将name属性修改为60： alter table users modify name varchar(60);修改表名alter table 旧命 rename to 新名对某列进行重定名alter table tablename xxx name xxx alter table employee change name xingming varchar(60); --新字段必要完整界说 5.3 删除表

1. DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

复制代码

六、表的增删改查

6.1 Create(创建):数据新增

1）普通插入

insert into table (必要插入数据的类名) values (插入数据);
多行数据 + 指定列插入:

1. INSERT INTO students (id, sn, name) VALUES
2. (102, 20001, '曹孟德'),
3. (103, 20002, '孙仲谋');

复制代码

2）插入否则更新

由于 主键 或者 唯一键 对应的值已经存在而导致插入失败。以是可以选择性的进行同步更新操作语法：
指令：INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ...
实例：

1. // 向students表中插入数据，不存在直接插入。如果发送冲突，则执行UPDATE 之后的语句进行数据更新
2. INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师')
3. ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';
5. Query OK, 2 rows affected (0.47 sec)
6. -- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
7. -- 1 row affected: 表中没有冲突数据，数据被插入
8. -- 2 row affected: 表中有冲突数据，并且数据已经被更新

复制代码

3） 更换方式二：replace

指令：REPLACE INTO students ...
实例：

1. -- 主键 或者 唯一键 没有冲突，则直接插入；
2. -- 主键 或者 唯一键 如果冲突，则删除后再插入
4. REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');
5. Query OK, 2 rows affected (0.00 sec)
7. -- 1 row affected: 表中没有冲突数据，数据被插入
8. -- 2 row affected: 表中有冲突数据，删除后重新插入

复制代码

6.2 Retrieve(读取)

1. SELECT
2. [DISTINCT] {* | {column [, column] ...}
3. [FROM table_name]
4. [WHERE ...]
5. [ORDER BY column [ASC | DESC], ...]
6. LIMIT ...

复制代码

• DISTINCT关键字用于对结果去重。
  
• WHERE 后接筛选条件
  
• ORDER BY：按照指定列进行排序，ASC升序， DESC降序。
  
• LIMIT ：输出结果显示限制
  

1）WHERE条件

2） 筛选分页结果

1. -- 起始下标为 0
3. -- 从 s 开始，筛选 n 条结果
4. SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
6. -- 从 0 开始，筛选 n 条结果
7. SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
9. -- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
10. SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

复制代码

6.3 Update(更新)

1. UPDATE table_name SET column = expr [, column = expr ...]
2. [WHERE ...] [ORDER BY ...] [LIMIT ...]

复制代码

• 将查询到的结果进行更新。假如后面没有筛选条件，则外貌对整表进行操作。
  

6.4 Delete（删除）

删除查到的数据： DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
截断表：TRUNCATE [TABLE] table_name;

• delete只是对数据进行删除，表布局依然存在。
  
• truncate截断不走事物，不会对日志进行操作（记录删除操作），以是速度更快。但由于不颠末事物，以是无法归滚。
  
• truncate会重置AUTO_INCREMENT 项，而delete不会！
  

6.5 插入查询结果

1. INSERT INTO table_name [(column [, column ...])] SELECT ..

复制代码

实例：对表进行重定名

1. // 创建表和新增数据
2. CREATE TABLE duplicate_table (id int, name varchar(20));
3. INSERT INTO duplicate_table VALUES (100, 'aaa'), (100, 'aaa');
5. //创建一张空表 no_duplicate_table，结构和 duplicate_table 一样
6. CREATE TABLE no_duplicate_table LIKE duplicate_table;
8. //将 duplicate_table 的去重数据插入到 no_duplicate_table
9. INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table
11. // 通过重命名表，实现原子的去重操作
12. RENAME TABLE duplicate_table TO old_duplicate_table, no_duplicate_table TO duplicate_table;

复制代码

• 末了通过rename进行，目的在于该操作是原子的，等一切都做好后，进行统一放入、更新、生效。
  

七、mysql聚合函数

 聚合函数是先将数据查询到后，在进行聚合。聚合时必须保证数据是可以被聚合的。

执行顺序样例：

八、查询分类

1）多表查询

 多表查询是指对多张表进行笛卡儿积，然后通过内链接on或where筛选出我们必要的表。
 在mysql中，不仅仅物理磁盘上存在的表布局叫表，中央筛选出来的，以及终极结果也是逻辑上的表。也可以直接将其重定名后直接当作单独的表利用，即Mysql下一切皆表！

2）自毗连

 自毗连是指在同一张表毗连查询！
 自毗连的典范利用场景就是：一张表中保存公司全部职员的id以及上级id。现在必要查找某个员工的上级向导名字，那我们会利用自毗连。
3） 子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。子查询可以跟在where和from后面。
4）归并查询

• union：该操作符用于取得两个结果集的并集。当利用该操作符时，会主动去掉结果集中的重复行。但必要保证两个结果的字段必须一样。
  

实例：将工资大于2500或职位是MANAGER的人找出来

• union all: 该操作符用于取得两个结果集的并集。当利用该操作符时，不会去掉结果集中的重复行。
  
  
  
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。