MYSQL学习地图-美数-2020.2.11更

基于共享文件系统 Access

基于服务器-客户端 MySQL Oracle 

DBMS

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

bin，存储可执行文件

data，存储数据文件

include，存储包含的头文件

docs，文档

lib，存储库文件

mysql目录结构

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

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

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

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

安装与配置MySQL

启动与停止MySQL服务

cmd下 mysql+参数

客户端操作参数说明

登录与退出

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

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

修改提示符

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

解决方案

创建连接时 提示错误1251： plugin非mysql_native_password 需要修改密码

导入数据不完整：

可视化数据库管理工具Navicat

配置Mysql

本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 旧表\u00A0

复制表结构及数据到新表

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

CREATE TABLE 新表 \u00A0LIKE 旧表\u00A0

只复制表结构到新表\u00A0

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

复制旧表的数据到新表

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

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

SELECT * INTO 表2 FROM 表1\u00A0

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

复制表结构，表数据

表

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

添加单列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()

排序

查（读取操作）

行（记录）

数据库操作

操作符

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

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

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

主键约束：PRIMARY KEY

非空约束：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

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

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

连接

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

创建索引：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 DISTINCT(birth) FROM student//检索出来的birth没有重复值

DISTINCT

\u00A0限制结果条数

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

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

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

过滤数据

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

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

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

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

匹配多个实例

正则表达式进行搜索

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

日期和时间处理函数

创建计算字段

检索数据

高级查询

字符函数

数值运算符与函数

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

聚合函数

加密函数

内置函数

创建 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功能和灵活性；较快处理速度；减少网络流量

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

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

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

存储过程VS自定义函数

存储过程

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

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

锁分类

表锁，开销最小的策略

行锁，开销最大的策略

锁颗粒

解决方案：锁

并发处理

原子性（Atomicity）

一致性（Consistency）

隔离性（Isolation）

持久性（Durability）

特性

事务处理

外键和索引

知识铺垫

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

引擎比较

设置引擎

存储引擎

海量数据库表做优化

性能优化

MYSQL5.7&8.0