MYSQL5.7&8.0
2018-08-08 15:56:31 26 举报
AI智能生成
Mysql图谱,整合了慕课网在线课程、《mysql必知必会》书籍知识点
作者其他创作
大纲/内容
基于共享文件系统 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
收藏
0 条评论
回复 删除
下一页