网站 多语言,设计制作中国第一架飞机的人,建站精灵网站模板,网络架构师证书Mysql 一、数据库概念#xff1f;二、MySQL架构三、SQL语句分类四、数据库操作4.1 数据库创建4.2 数据库字符集和校验规则4.3 数据库修改4.4 数据库删除4.4 数据库备份和恢复其他 五、表操作5.1 创建表5.2 修改表5.3 删除表 六、表的增删改查6.1 Create(创建):数据新增1#… Mysql 一、数据库概念二、MySQL架构三、SQL语句分类四、数据库操作4.1 数据库创建4.2 数据库字符集和校验规则4.3 数据库修改4.4 数据库删除4.4 数据库备份和恢复其他 五、表操作5.1 创建表5.2 修改表5.3 删除表 六、表的增删改查6.1 Create(创建):数据新增1普通插入2插入否则更新3 替换方式二replace 6.2 Retrieve(读取)1WHERE条件 2 筛选分页结果6.3 Update(更新)6.4 Delete删除6.5 插入查询结果 七、mysql聚合函数八、查询分类1多表查询2自连接3 子查询4合并查询 一、数据库概念 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 创建表
CREATE TABLE table_name (
field1 datatype,
field2 datatype,
field3 datatype
) 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 删除表
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...六、表的增删改查
6.1 Create(创建):数据新增
1普通插入
insert into table (需要插入数据的类名) values (插入数据);
多行数据 指定列插入:
INSERT INTO students (id, sn, name) VALUES
(102, 20001, 曹孟德),
(103, 20002, 孙仲谋);2插入否则更新
由于 主键 或者 唯一键 对应的值已经存在而导致插入失败。所以可以选择性的进行同步更新操作语法
指令INSERT ... ON DUPLICATE KEY UPDATE column value [, column value] ...
实例
// 向students表中插入数据不存在直接插入。如果发送冲突则执行UPDATE 之后的语句进行数据更新
INSERT INTO students (id, sn, name) VALUES (100, 10010, 唐大师)
ON DUPLICATE KEY UPDATE sn 10010, name 唐大师;Query OK, 2 rows affected (0.47 sec)
-- 0 row affected: 表中有冲突数据但冲突数据的值和 update 的值相等
-- 1 row affected: 表中没有冲突数据数据被插入
-- 2 row affected: 表中有冲突数据并且数据已经被更新3 替换方式二replace
指令REPLACE INTO students ... 实例
-- 主键 或者 唯一键 没有冲突则直接插入
-- 主键 或者 唯一键 如果冲突则删除后再插入REPLACE INTO students (sn, name) VALUES (20001, 曹阿瞒);
Query OK, 2 rows affected (0.00 sec)-- 1 row affected: 表中没有冲突数据数据被插入
-- 2 row affected: 表中有冲突数据删除后重新插入6.2 Retrieve(读取)
SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...DISTINCT关键字用于对结果去重。WHERE 后接筛选条件ORDER BY按照指定列进行排序ASC升序 DESC降序。LIMIT 输出结果显示限制
1WHERE条件 2 筛选分页结果
-- 起始下标为 0-- 从 s 开始筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;-- 从 0 开始筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;-- 从 s 开始筛选 n 条结果比第二种用法更明确建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;6.3 Update(更新)
UPDATE table_name SET column expr [, column expr ...]
[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 插入查询结果
INSERT INTO table_name [(column [, column ...])] SELECT ..实例对表进行重命名
// 创建表和新增数据
CREATE TABLE duplicate_table (id int, name varchar(20));
INSERT INTO duplicate_table VALUES (100, aaa), (100, aaa);//创建一张空表 no_duplicate_table结构和 duplicate_table 一样
CREATE TABLE no_duplicate_table LIKE duplicate_table;//将 duplicate_table 的去重数据插入到 no_duplicate_table
INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table// 通过重命名表实现原子的去重操作
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: 该操作符用于取得两个结果集的并集。当使用该操作符时不会去掉结果集中的重复行。
