MYSQL的拆解

MYSQL的拆解

自定义函数

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

创建带有复合结构的函数f3CREATE 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功能和灵活性；较快处理速度；减少网络流量

存储过程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，存储库文件

环境变量配置：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下 mysql+参数

客户端操作参数说明

修改提示符

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

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

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

可视化数据库管理工具Navicat

导入数据不完整：

数据库操作

规范说明

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

数据类型

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

时间日期型：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

表

删除表 DROP TABLE tb_name

复制表结构，表数据

复制表结构及数据到新表

CREATE TABLE 新表 SELECT * FROM 旧表 

只复制表结构到新表 

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

CREATE TABLE 新表  LIKE 旧表 

复制旧表的数据到新表

(假设两个表结构一样) ：INSERT INTO 新表 SELECT * 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 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]

行（记录）

增（写操作）

删（写操作）

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

删除多表记录

改（写操作）

查（读取操作）

查看

查看库存在：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         要返回的列或表达式        FROM           从中检索数据的表        WHERE         行级过滤        GROUNP BY  分组说明        HAVING         组级过滤        ORDER BY    输出排序顺序        LIMIT           要检索的行数

对查询题目结构化思考

执行阶段逻辑

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

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

合并结果集 UNION [ALL]span style=\

子查询

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

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

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

排序

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

随机选择记录：select newid()

重要操作概念

操作符

约束

主键约束：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

外键约束：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

四表联查问题：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\

高级查询

检索数据

DISTINCT

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

 限制结果条数

过滤数据

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

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

“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

匹配多个实例

 列：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 * from table1 where time between time1 and time2

日期时间函数

信息函数

聚合函数

总数：select count as totalcount from table1

求和：select sum(field1) as sumvalue from table1

平均：select avg(field1) as avgvalue from table1

最大：select max(field1) as maxvalue from table1

最小：select min(field1) as minvalue from table1

加密函数