首页  思维导图  详情

MYSQL的拆解

2021-04-27 23:25:00   0  举报





AI智能生成

本文将详细描述安装与配置MySQL，从表、列、行等维度分别讲解，供学习者参考

MySQL工作流程

数据库表关系图

主从复制

java

作者其他创作

大纲/内容

自定义函数

创建 CREATE FUNCATION funcation_name[fun_parameters] RETURNS{STRING | INTEGER | REAL | DECIMAL} routine_body 使用 SELECT f1();

创建不带有参数的函数f1  eg CREATE FUNCATION fi RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(),'%Y年%M月%D日 %H点%I分%S秒');

创建带参数的函数f2 eg CREATE FUNCATION f2 (num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED) RETURNS FLOAT(10,2)UNSIGNED RETURN (num1+num2)/2

创建带有复合结构的函数f3 CREATE FUNCATION f3 (username VARCHAR(20)) RETURNS INT BEGIN INSERT test(username) VALUES(username); RETURN  LAST_INSERT_ID() END//  #临时修改分隔符为//

 删除函数 DORP FUNCATION [IF EXISTS] funcation_name;

存储过程

优点：增强SQL功能和灵活性；较快处理速度；减少网络流量

语法结构  CREATE [DIFINER ={user | current_user}] PROCEDURE sp_name ([proc_parameter[,...]] [characteristic...] routine_body

存储过程VS自定义函数

1.存储过程实现的功能更复杂，函数则针对性更强

2.存储过程可以返回多个值，函数只能返回一个值

3.存储过程一般独立来执行，函数作为SQL语句的组成部分出现

存储引擎

知识铺垫

并发处理

解决方案：锁

锁分类

共享锁（读锁）：同一时间内多个用户可以读取同一资源，此过程数据不会发生变化

排他锁（写锁）：任何时候只能有一个用户写入资源，当其写锁时会阻碍其他的读锁和写锁操作

锁颗粒

表锁，开销最小的策略

行锁，开销最大的策略

事务处理

特性

原子性（Atomicity）

一致性（Consistency）

隔离性（Isolation）

持久性（Durability）

外键和索引

引擎比较

MyISAM 适用事务处理不多的情况 InnoDB 适用事务处理较多，且需要外键的情况

设置引擎

性能优化

海量数据库表做优化

配置Mysql

DBMS

基于共享文件系统 Access

基于服务器-客户端 MySQL Oracle 

安装与配置MySQL

mysql官网提供安装版.msi和解压版.zip两种方式

mysql目录结构

bin，存储可执行文件

data，存储数据文件

include，存储包含的头文件

docs，文档

lib，存储库文件

share,错误信息与字符集文件

环境变量配置：win7计算机属性-高级系统设置-环境变量-找到系统变量Path-编辑添加mysql.exe的路径 如：C:\Program Files\MySQL\MySQL Server 8.0\bin

修改编码方式一：在C:\programdata中找到并修改配置文件mysql.ini

修改编码方式二：SET NAMES utf8mb4 ;SET NAMES gbk;

查看编码情况 SHOW VARIABLES LIKE "%char%"

启动与停止MySQL服务

cmd,net start mysql57;net stop mysql57;(注册在windows的服务名)

登录与退出

cmd下 mysql+参数

客户端操作参数说明

修改提示符

连接客户端时通过参数指定 shell>-uroot -proot --prompt 提示符

连接客户端后通过命令指定 mysql>prompt 提示符

语法规范：关键字和函数全部大小；数据库名、表名、字段名全部小写(以便于区别代码与对象)；语句以分号结尾（否则会一直提示 ->  渴望得到一个分号）

可视化数据库管理工具Navicat

导入数据不完整：

数据库操作

规范说明

本mind map代码花括号｛｝为必选；竖线| 为二选一；方括号[ ]为可选

数据类型

整型integer：TINYINT（1）;SMALLINT（2）;MEDIUMINT（4）;INT（4）BIGINT（5 ）

浮点型：单精度FLOAT(M,D) ; 双精度DOUBLE(M,D)  M为数字总位数，D为小数点后数字位数

时间日期型：YEAR(1);TIME(3);DATE(3);DATATIME(8);TIMESTAMP(4)

字符型：CHAR;VARCHAR;ENUM（枚举值，N选一）;SET(VALUE1.....)（集合值，排列组合）

基于性能和功能考虑：限制可存储数据；更有效存储；允许变换排序 

库

创建数据库：CREATE ｛DATABASE|SCHEMA} [IF NOT EXIST] database-name （column_name datatype） [DEFAULT] CHARCTER SET [=] charset_name

打开数据库：USE db_name;

删除数据库：DORP DATABASE [IF EXIST] dbname

修改数据库：ALTER ｛DATABASE|SCHEMA} [IF NOT EXIST] database-name [DEFAULT] CHARCTER SET [=] charset_name

表

创建新表并设置字段属性：CREATE TABLE tb_name [IF NOT EXIST] (column1 datatype [UNSIGNED][NULL| NOT NULL][ADD PRIMARY KEY][AUTO_INCREMENT],.....)

创建新表并将查询结果写入 CTREATE TABLE tb_name [IF NOE EXIST] [(create_definition,...)] select_statement;

创建无限分类的表    CREATE TABLE tdb_goods_types(<div>     type_id   SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,</div><div>     type_name VARCHAR(20) NOT NULL,</div><div>     parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 ); </div>

删除表 DROP TABLE tb_name

复制表结构，表数据

复制表结构及数据到新表

CREATE TABLE 新表 SELECT * FROM 旧表

只复制表结构到新表

CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2

CREATE TABLE 新表 LIKE 旧表

复制旧表的数据到新表

(假设两个表结构一样) ：INSERT INTO 新表 SELECT * FROM 旧表

(假设两个表结构不一样) ：INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表

可以将表1结构复制到表2

SELECT * INTO 表2 FROM 表1 WHERE 1=2

可以将表1内容全部复制到表2

SELECT * INTO 表2 FROM 表1

列（字段）

字段设置：申明字段类型（有误符号位）；设置约束（非空、主键等）；自动编号

添加单列ALTER TABLE tb_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]

添加多列ALTER TABLE tb_name ADD [COLUMN] （col_name column_definition, ..... ）

修改列定义 ALTER TABLE tb_name MODIFY[COLUMN] col_name column_definition [FIRST|AFTER col_name]

修改列名称ALTER TABLE tb_name CHANGE[COLUMN] old_col_name  new_col_name  column_definition [FIRST|AFTER col_name]

删除列：ALTER TABLE  tb_name DROP col1_name,DROP col2_name;

行（记录）

增（写操作）

插入多条记录 INSERT [INTO] tb_name[col_name,......] VALUES(value1,value2) 

可嵌入子查询的单条记录插入 INSERT [INTO] tb_name SET col_name={EXPR|DEFAULT},...; eg. INSET tb1(col_name) SELECT col_name(same) FROM tb2 WHERE expr

将查询结果插入记录 INSERT [INTO] tb_name[(col_name,...)SELECT....

删（写操作）

删除单表记录 DELETE FROM tb_name[WHERE where_condition]  不加[WHERE]则删除整张表中所有记录记录，删除某条记录，其他主键记录ID号不变

删除多表记录

改（写操作）

单表更新 UPDATE [LOW_PRIORITY][IGNORE] SET col_name1={EXPR1|DEFAULT},col_name2={EXPR2|DEFAULT}...[WHERE EXPR]

多表更新 UPDATE table_references SET col_name={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}] WHERE where_condition 

查（读取操作）

查看

查看库存在：SHOW DATABASES [Like 'pattern|WHERE expr]

查看当前打开的数据库：SELECT DATABASES( ) 修改提示符可显示当前数据库

查看表存在 SHOW TABLES [FROM db_name] [Like 'pattern|WHERE expr]

查看列存在（查看数据表结构）SHOW COLUMNS FROM tb_name;

查看记录存在：select * from table1 where 范围

查询表达式 SELECT select_expr1 [,select_expr2...]   [    FROM TABLE reference    [WHERE where_condition]    [GROUP BY {col_name|position} [ASC|DESC],...]    [HAVING where_conditon]    [ORDER BY {col_name|expr|position},...]    [LIMIT {[offset,] row_count|row_count OFFSET offset}    ][\G;]

语句解释         SELECT         要返回的列或表达式         FROM           从中检索数据的表         WHERE         行级过滤         GROUNP BY  分组说明         HAVING         组级过滤         ORDER BY    输出排序顺序         LIMIT           要检索的行数

对查询题目结构化思考

执行阶段逻辑

条件表达式WHERE SELECT * FROM tb_name WHERE expr; select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!

查询结果分组 GROUP BY （结合聚合函数进行分组计算） SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

指定分组条件HAVING（指定则只对部分记录分组） [HAVING where_conditon] 要使用此条件要么出现聚合函数，要么条件中字段为查询语句中已有字段

对查询结果排序ORDER BY [ORDER BY {col_name|expr|position},...]

限制查询结果数量LIMIT [LIMIT {[offset,] row_count|row_count OFFSET offset}

合并结果集 UNION [ALL] SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION [ALL | DISTINCT]  SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];

子查询

<div>子查询是指在另一个查询语句中的SELECT子句。</div><div>  SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);</div><div>  其中，SELECT * FROM t1 ...称为Outer Query[外查询](或者Outer Statement),外查询可以是增删改查，</div><div>  SELECT column1 FROM t2 称为Sub Query[子查询]。</div>

使用比较运算符的子查询 = > < !=

子查询返回多个结果时候用ANY SOME ALL返回一个值

使用[NOT]IN的子查询 =ANY等效于IN，！=ALL|<>ALL等效于 NOT IN

使用[NOT]EXISTS的子查询  子查询返回任何行 EXISTS将返回TRUE,否则返回FALSE

排序

前10条记录： select top 10 * form table1 where 范围

选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

随机选择记录：select newid()

重要操作概念

操作符

in 的使用方法：select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

约束

主键约束：PRIMARY KEY

添加主键： ALTER TABLE  tabname add primary key(col)

删除主键： Alter table tabname drop primary key(col)

自动编号：AUTO INCREMNET 必须与主键配合使用（反之不必要），起始值为1步长为1

非空约束：NULL|NOT NULL

唯一约束：UNIQUE KEY

默认约束：如果没有明确为字段赋值，将赋予预先设置的默认值 CREATE TABLE ss(sex ENUM('男'’,'女'，'保密')  DEFAUlT '保密');

外键约束：FOREIGN tb1_name(column1) REFERENCES tb2_name(column1) 

三大使用要求

四个参照操作

连接

语法 tb_reference {[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN} tb_reference ON condition_expr；

数据表参照别名 table_name [AS] alias | table_subquery [AS] alias

外连接查询(表名1：a 表名2：b)：select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

四表联查问题：select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

两张关联表，删除主表中已经在副表中没有的信息：delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

索引

索引是不可更改的，想更改必须删除重新建

创建索引：create [unique] index idxname on tabname(col….)

删除索引：drop index idxname

视图

创建视图：create view viewname as select statement

删除视图：drop view viewname

别名

别名可以和连接同时使用 <div yne-bulb-block="paragraph">select dept_name,emp_id from department d left join employee e on e.dept_id=d.dept_id group by e.emp_id;</div>

高级查询

INSERT INTO student SELECT 909,'张三', sex,birth,department, address FROM student WHERE birth=1986 ;

检索数据

DISTINCT

SELECT DISTINCT(birth) FROM student//检索出来的birth没有重复值

限制结果条数

LIMIT 3,4 从行3开始的后4行 LIMIT 4 OFFSET 3 从行3开始的后4行

排序

SELECT columnOne,columnTwo,columnThire FROM products ORDER BY prod_price,prod_name; 排序是先根据前面的columnOne排序，如果一样再根据后面的columnTwo排序

ASC 升序默认 DESC 降序关键字仅作用到直接位于前面的列名 SELECT columnOne,columnTwo,columnThire FROM table ORDER BY columnOne ASC, columnTwo DESC; //先按columnOne正排序再按columnTwo倒序排序

过滤数据

<> 不等于（数字比较） != 不等于（数字和字符串比较）

BETWEEN 在指定的两个指之间必须指定2个值。这两个值必须使用AND连接 SELECT columnOne,columnTwo, FROM table WHERE columnOne BETWEEN 3 AND 8; // 检索columnOne值为3到8之间的行

满足任意一个条件 SELECT columnOne,columnTwo,columnThire FROM table WHERE columnOne = 2009 OR columnTwo <= 10; //检索columnOne =2009 或 columnTwo <= 10 的行

IN操作符完成与OR相同的功能，优点如下： 1 使用长的合法选项清单时，IN操作符的预防更清楚且直观 2 使用IN时，计算的次序更容易管理（以为使用的操作符更少） 3 IN操作符一般比OR操作符执行更快 4 IN操作符最大的优点可以包含其他SELECT语句，使得能够更动态的创建WHERE子句。

NOT操作符 WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件 SELECT columnOne,columnTwo FROM table WHERE columnOne NOT IN (1002,1005,1006) ORDER BY columnTwo; //检索columnOne不为1002或1005或1006的行并且按columnTwo分组

“where 1=1” 是表示选择全部 “where 1=2”全部不选，

正则表达式进行搜索

检索列prod_name包含1000 的所有行 SELECT columnOne FROM table WHERE columnOne REGEXP '1000' ORDER BY columnOne

进行OR匹配为搜索两个或n个字符串之一 SELECT columnOne FROM table WHERE columnOne REGEXP '1000|2000|3000' ;

匹配单个字符 SELECT columnOne FROM table WHERE columnOne REGEXP '[123] Ton'; //匹配1 Ton 或2 Ton或3 Ton

匹配多个实例

重复元字符元字符说明 * 0个或多个匹配 + 1个或多个匹配 ? 0个或1个匹配 {n} 指定数目匹配 {n,} 不少于n个匹配 {n,m} 匹配数目的范围 m不超过255

列：SELECT columnOne FROM table WHERE columnOne REGEXP '\\([0-9] sticks?)\\'; Sticks?匹配的是 stick 或sticks（？号决定前面的s出现一次或0次）列：SELECT columnOne FROM table WHERE columnOne REGEXP '[[:digit:]]' ORDER BY columnOne; [:digit:]匹配任意的数字，{4}要求前面匹配的数字出现4次

创建计算字段

日期和时间处理函数

AddDate() 增加一个日期天、周等 AddTime() 增加一个时间时、分等 CurDate() 返回当前日期 CurTime() 返回当前时间 Date() 返回日期时间的日期部分 DateDiff() 计算两个日期之差 Date_Add() 高度灵活的日期或时间串 Date_Format() 返回一个格式的日期或时间串 Day() 返回一个日期的天数部分 DayOfWeek() 对于一个日期，返回对于星期几 Hour() 返回一个时间的小时部分 Minute() 返回一个时间的分钟部分 Moth() 返回一个日期的月份部分 Now() 返回当前的日期和时间 Second() 返回一个时间的秒部分 Time() 返回一个日期时间的时间部分 Year() 返回一个日期的年份部分

SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2010-11-05'; 检索出2005-5月份的订单怎么办呢？可以使用BETWEEN SELECT cust_id,order_num FROM orders WHERE Date(order_date) BETWEEN '2005-05-01' AND '2005-05-30';

内置函数

字符函数

数值运算符与函数

比较运算符与函数

between限制查询数据范围时包括了边界值,not between不包括

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 数值1 and 数值2

日期时间函数

日程安排提前五分钟提醒： select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

信息函数

聚合函数

总数：select count as totalcount from table1