mysql入门
2018-01-16 09:37:36 161 举报AI智能生成
登录查看完整内容
mysql 入门的一些基础知识
作者其他创作
大纲/内容
数据存储方式演进
数据库 范式
数据库在开发中作用
1.1数据库理论基础
数据库系统由数据库、数据库管理系统、应用开发工具构成
数据库系统
定义数据库、表、视图、索引和触发器等create、alter、drop
DDL
插入、查询、更新和删除数据insert、select、update、delete
DML
控制用户访问权限grant、revoke
DCL
SQL
C# ADO.NET
java JDBC
ODBC
数据库访问技术
1.2数据库技术构成
甲骨文的Oracle
开源Mysql
IBM的DB2
微软的Access和SQL Server
开源的PostgreSQL
常见数据库
开源
跨平台性
价格便宜
功能强大方便
mysql优势
1.3mysql基础
basedir=“”表示Mysql安装路径
datadir=\"\"表示mysql数据文件存储位置
default-character-set=latin1服务端默认字符集
port=3306表示mysql数据库的端口，默认为3306
default-storage-engine=INNODB # 参数表示默认的村塾引擎。存储引擎表示数据的存储方式
sql-mode=\
max_connections=100# 参数标识允许同时访问MySql服务的最大连接数，其中一个将保留，作为管理员登录。如果希望改变最大连接数，可以在此处进行修改
query_cache_size=0# 参数标识查询时
table_cache=256表示所有进程打开表的总数
tmp_table_size=9M表示内存中临时表的最大值
thread_cache_size=8表示保留客户端线程的混村
myisam_max_sort_file_size=100G表示mysql重建索引时允许的最大临时文件的大小，默认为100G
myisam_sort_buffer_size=18M表示参数重建索引的缓存大小
key_buffer_size表示关键词缓存大小，该缓冲区一般来缓冲MyISAM表的索引块
read_buffer_size=64K表示将排序好的数据存入该缓存中
sort_buffer_size=256K表示用于排序的缓存的大小
服务器端各个参数
port=3306
password=your password表示用户的登录密码，用户可以将密码存入该文件中，登陆时就可以不用输入密码
客户端各个参数
innodb_additional_mem_pool_size=2M表示附加的内存池，用来存储InnoDB表的内容
innodb_flush_log_at_trx_commit=1设置为1，表示会在每次提交后将事务日志写到磁盘上
innodb_log_buffer_size=1M表示用来存储日志数据的缓冲区的大小
innodb_buffer_pool_size=18M表示缓存的大小，InnoDB使用一个缓冲池来保存索引和原始数据
innodb_log_file_size=10M表示日志文件的大小
innodb_thread_concurrency=8表示在InnoDB存储引擎允许的线程最大数
InnoDB存储引擎参数
1.4mysql配置文件
1、数据库概述
TINYINT 字节数: 1 无符号的取值范围: 0~255 有符号范围：-128~127
SMALLINT 字节数: 2 无符号的取值范围: 0~65535 有符号范围：-32768~32767
MEDIUMINT 字节数: 3 无符号的取值范围: 0~16777125 有符号范围：-8388608~8388607
INT 字节数: 4 无符号的取值范围: 0~4294967295 有符号范围：-2147483648~2147483647
INTEGER 字节数: 4 无符号的取值范围: 0~4294967295 有符号范围：-2147483648~2147483647
BIGINT 字节数: 8 无符号的取值范围: 0~2的64次方-1 有符号范围：-2的63次方~2的63次方-1
整数类型
FLOAT 字节数 4
DOUBLE 字节数 8
浮点数类型和定点数类型
以YYYY的形式显示YEAR类型的值。输入格式‘YYYY’或 YYYY
YEAR 字节 1 范围1901-2155 超过为0000
YYYY-MM-DD
YYYYMMDD
YYYY/MM/DD
YYYY@MM@DD
YY.MM.DD
CURRENT_DATE NOW()
DATE 字节 4
D HH:MM:SS D代表天数 保存时小时的值等于D*24+HH
HHMMSS 保存时转化为HH:MM:SS
CURRENT_TIME NOW() 系统时间
TIME 字节 3
YYYY-MM-DD HH:MM:SS
YYYYMMDDHHMMSS
YYMMDDHHMMSS
NOW()
DATETIME 字节 8
与DATETIME类型不同：1、使用CURRENT_TIMESTAMP来输入当前日志与时间2、输入NULL时，系统会输入系统当前日期与时间3、无任何输入时，系统会输入系统当前日期与时间
TIMESTAMP 字节 4
日期与时间类型
字符串类型（M)
CHAR的字节长度是M ，VARCHAR的字节长度是M+1
CHAR会执行trim()功能， VARCHAR不会
CHAR长度范围0~255 VARCHAR长度范围0~65535
CHAR VARCHAR
存储空间 值的长度+2字节
0~225字节
TINYTEXT
0~65535字节
TEXT
存储空间 值的长度+3字节
0~167772150字节
MEDIUMTEXT
存储空间 值的长度+4字节
0~4294967295字节
LONGTEXT
没加上NOT NULL 允许插入NULL，而且NULL为默认值
ENUM
SET类型的值可以取一个元素或者多个元素，取多个元素时，不同元素之间用逗号隔开，并且会按照定义的顺序排列
SET
字符串类型
允许长度为M的二进制字符串
不足位补0
BINARY(M)
允许为0~M的变长二进制字符串，字节数为值的长度加1
VARBINARY(M)
M位二进制数据，M最大值为64
BIT(M)
可变长二进制数据，最多256个字节
TINYBLOB
可变长二进制数据，最多2的16次方-1个字节
BLOB
可变长二进制数据，最多2的24次方-1个字节
MEDIUMBLOB
可变长二进制数据，最多2的32次方-1个字节
LONGBLOB
二进制类型
区别在于能否表达小数。
整数和浮点数
浮点数四舍五入系统不会报警
定点数四舍五入系统会出现警告
浮点数和定点数（decimal）
CHAR长度固定，VARCHAR长度是范围内可变，所以VARCHAR类型占用的空间比CHAR类型小
VARCHAR类型灵活
CHAR处理速度更快
CHAR VARCAHR
YEAR只记录年份
TIME只表示时间
DATE只表示日期
DATETIME范围比TIMESTAMP大
TIMESTAMP是根据时区来显示的
时间和日期类型
ENUM最多65535个成员，SET最多64个成员
ENUM只能选一个，SET可以选多个
ENUM SET
TEXT只能存储字符数据
BLOB可以存储二进制数据
纯文本选择TEXT
图片等二进制选择BLOB
TEXT和BLOB
如何选择数据类型
MySQL中，CHAR，VARCHAR和TEXT等字符串类型都可以存储路径，但是如果路径中使用\"\\\"符号时，这个符号会被过滤。解决办法是，路径中用\"/\"或者\"\\\\\"来代替\"\\\"，这样MySQL就不会自动过滤路径的分隔符号，可以完整的表示路径
1、mysql中什么数据类型能够存储路径
2、MySQL中如何使用布尔类型
一般存储图片和音频的路径。实在需要，选择BLOB类型存储
3、MySQL中如何存储JPG图片和MP3音乐
常见问题
mysql数据类型
SHOW DATABASE;
查看数据库
CREATE DATABASE 数据库名;
创建数据库
DROP DATABASE 数据库名;
删除数据库
存储引擎名称
Engine
是否支持
Support
评论
Comment
是否支持事务
Transactions
是否分布式交易处理的XA规范
XA
是否支持保存点，以便事务回滚到保存点
Savepoints
参数含义
SHOW ENGINES;
variable_name
支持情况
value
SHOW VARIABLES LIKE 'have%';
SHOW VARIABLES LIKE 'storage_engine';
查看支持的存储引擎
支持自动增长列AUTO_INCREMENT
支持外键 FOREIGN KEY
创建的表存储在.frm文件中，数据和索引存储在innodb_data_home_dir 和 innodb_data_file_path定义的表空间中
优点：提供良好的事务管理、奔溃修复和并发控制
缺点：读写效率稍差，占用数据空间相对比较大
InnoDB存储引擎
frm为扩展名的文件 【存储表结构】
MYD为扩展名的文件 【存储数据】 MYData
MYI为扩展名的文件 【存储索引】 MYIndex
MyISAM存储引擎的表存储成3个文件。文件的名称与表名相同。扩展名包含frm、MYD和MYI
静态型 为MyISAM存储引擎的默认存储格式，其字段是固定长度的
动态型包含变成字段，记录的长度不是固定的
压缩型需要使用myisampack工具创建，占用的磁盘空间较小
基于MyISAM存储引擎的表，支持三种不同的存储格式。包含静态型、动态型和压缩型。
优点：占用空间小、处理速度快
缺点：不支持事务的完整性和并发性
MyISAM存储引擎
使用存储在内存中的内容来创建表，而且所有数据也放在内存中。每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件存储在内存中，有利于对数据快速的处理，提高整个表的处理效率。
使用哈希（HASH）索引。速度要比B型树（BTREE）索引快。
max_rows可以在创建表时指定
max_heap_table_size的大小默认为16MB
表的大小是受限制的。表的大小主要取决于max_rows 和 max_heap_table_size
优点：处理速度非常快
缺点：数据易丢失，生命周期短
MEMORY存储引擎
存储引擎对比
数据库存储引擎
操作数据库
标识该属性为该表的主键，可以唯一的标识对应的元组。
PRIMARY KEY
标识该属性为该表的外键，是与之联系的某表的主键。
FOREIGN KEY
标识该属性的值不能为空
NOT NULL
标识该属性的值是唯一的
UNIQUE
标识该属性的值自动增加，这是mysql的SQL语句的特色
AUTO_INCREMENT
为该属性设置默认值
DEFAULT
完整性约束条件
CREATE TABLE 表名(属性名 数据类型 [完整性约束条件]，属性名 数据类型 [完整性约束条件]，...属性名 数据类型 [完整性约束条件]);
单字段： 属性名 数据类型 PRIMARY KEY
多字段： PRIMARY KEY(属性名1， 属性名2， ... ，属性名n)
设置表的主键
字表的外键关联的必须是父表的主键，而且数据类型必须是一致的。
设置表的外键
属性名 数据类型 NOT NULL
设置表的非空约束
属性名 数据类型 UNIQUE
设置表的唯一性约束
属性名 数据类型 AUTO_INCREMENT
设置表的属性自动增加
属性名 数据类型 DEFAULT 默认值
设置表的属性的默认值
创建表
查看表的基本定义，字段名、字段数据类型、是否为主键和默认值等
DESCRIBE 表名;
查看表的详细定义，查看表的字段名，字段的数据类型，完整性约束条件等，还可以查看默认的存储引擎和字符编码。
SHOW CREATE TABLE 表名;
查看表
ALTER TABLE 旧表名 RENAME [TO] 新表名
ALTER TABLE 表名 MODIFY 属性名 数据类型 [约束性条件];
修改字段的数据类型
ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型 [约束性条件];
修改字段名
ALTER TABLE 表名 ADD 属性名1 数据类型 [约束性条件] [FIRST | AFTER 属性名2];
增加字段
ATLER TABLE 表名 DROP 属性名;
删除字段
ALTER TABLE 表名 MODIFY 属性名1 数据类型 FIRST | AFTER 属性名2;
修改字段的排列位置
ALTER TABLE 表名 ENGINE=存储引擎名;
更改表的存储引擎
ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;
删除表的外键约束
修改表
DROP TABLE 表名;
删除没有被关联的普通表
1、show create table 子表名; 找到外键约束。
2、删除子表外键约束
3、删除父表
删除被其他表关联的父表
删除表
创建修改删除表
索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种结构。
优点：提高检索数据的速度，这是创建索引的主要原因。对于有依赖关系的父子表之间的联查是，可以提高查询速度；可以显著节省查询中分组和排序的时间。
缺点：创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加。索引需要占用物理空间，每一个索引占用一定的物理空间；增加、删除、修改索引数据时，要动态的维护索引，造成数据的维护速度降低了。
索引能提高查询速度，但是会影响插入记录的速度。因为向有索引的表中插入记录时，数据库系统会按照索引进行排序。
含义和特点
不附加任何限制条件，可以创建在任何数据类型中，其值是否唯一和非空有字段本身的完整性约束条件决定。
普通索引
使用UNIQUE参数可以设置索引为唯一性索引。在创建唯一性索引时，限制该索引的值必须是唯一的。注：主键就是一种特殊唯一性索引。
唯一性索引
全文索引
在表中的某个字段上创建索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。
单列索引
多列索引是在表的多个字段上创建一个索引。只有查询中使用了这些字段中的第一个字段时，索引才会被使用。
多列索引
使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL空间数据类型包含GEOMETRY 和 POINT、LINESTRING、POLYGON等。只有MyISAM支持空间检索，而且索引的字段不能为空值。
空间索引
分类
值是唯一的，可以更快速的通过索引确定某条记录
1、选择唯一性索引
经常需要GROUP BY、ORDER BY、DISTINCT、UNION等操作的字段
2、为经常需要排序、分组和联合操作的字段建立索引
3、为常作为查询条件的字段建立索引
4、限制索引的数量
5、尽量使用数据量少的索引
字段名很长的使用前缀作为索引名
6、尽量使用前缀来索引
7、删除不再使用或很少使用的索引
设计索引
UNIQUE 标识索引为唯一性索引
FULLTEXT 标识索引为全文索引
SPATIAL标识索引为空间索引
INDEX和KEY参数指定字段为索引，二选一
别名 索引的取的新名称
属性1 参数指定索引对应的字段名
长度 索引的长度，必须是字符串类型才可以使用
ASC DESC 升降序
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [别名] (属性名1 [(长度)] [ASC|DESC])
在创建表的时候创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名 (属性名 [(长度)] [ ASC|DESC])
在已经存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 (属性名 [(长度)] [ ASC|DESC])
通过ALTER创建索引
创建索引
DROP INDEX 索引名 ON 表名;
删除索引
索引
视图是一种虚拟的表，是从数据库中一个或多个表中导出来的表。视图还可以从已经存在的视图的基础上定义。数据库中只存放了视图的定义，而没有存放视图的数据，这些数据存在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。视图数据与原来表的数据同步。
含义
视图的目的就是所见即所需
1、使数据简单化
通过视图，用户只能查询和修改指定的数据。指定数据之外的信息，用户接触不到。
2、增加数据的安全性
视图可以屏蔽原来表结构变化带来的影响。
3、提高表的逻辑独立性
作用
含义和作用
表示MySQL自动选择所要使用的算法
UNDEFINED
表示将使用视图的语句和视图定义合并起来，使得视图定义的某一部分取代语句的对应部分
MERGE
表示将视图的结果存入临时表，然后使用临时表执行语句
TEMPTABLE
视图的算法
ALGORITHM
视图名
指定视图中各个属性的名词
属性清单
标识更新视图时保证在该视图的权限范文内
表示更新视图时要满足所有相关视图和表的条件
CASCADED
表示更新视图时，要满足该视图本身的定义的条件即可。
LOCAL
WITH CHECK OPTION
CREATE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名 [(属性清单)] AS SELECT 语句 [WITH [CASCADED|LOCAL] CHECK OPTION];
语法
单表创建视图
多表创建视图
创建视图
DESCRIBE 视图名;
DESC 视图名;
SHOW TABLE STATUS LIKE '视图名';
SHOW CREATE VIEW 视图名;
SELECT * FROM information_schema.views;
查看视图
没有就创建，有就修改
CREATE OR REPLACE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEW 视图名[(属性清单)]AS SELECT 语句[WITH [CASCADED | LOCAL] CHECK OPTION];
ALTER [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEW 视图名[(属性清单)]AS SELECT 语句[WITH [CASCADED | LOCAL] CHECK OPTION];
修改视图
DROP VIEW [IF EXISTS] 视图名称列表 [RESTRICT | CASCADE]
删除视图
视图虽然可以更新，但是有很多限制，一般情况，最好将视图作为查询数据的虚拟表。
更新视图
视图与表的区别与联系。
视图
触发器是由INSERT UPDATE DELETE等事件来出发某种特性操作。满足触发器的条件时，数据库系统就会执行触发器重定义的程序语句。
含义作用
指定了出发器执行的时间
BEFORE | AFTER
指触发的条件，包含 INSERT UPDATE DELETE
触发事件
触发事件操作的表的名称
表名
每一行记录的操作满足触发事件都会触发该触发器
FOR EACH ROW
触发器被触发后执行的语句
执行语句
CREATE TRIGGER 触发器名 BEFORE | AFTER 出发事件 ON 表名 FOR EACH ROW 执行语句.
一个执行语句
多条执行语句结尾不能用分号结束，必须用 DELIMITER 来修改程序结束符
CREATE TRIGGER 触发器名 BEFORE | AFTER 触发事件 ON 表名 FOR EACH ROW BEGIN 执行语句列表 END
多个执行语句
创建
SHOW TRIGGERS;
SELECT * FROM information_schema.triggers;
查看
DROP TRIGGER 触发器名
删除
触发器
2、数据库基本操作
基本语法
不建议这种写法
查询所有字段
推荐
查询指定字段
[NOT] IN (元素1， 元素2， 元素3...元素n)
[NOT] BETWEEN 取值1 AND 取值2
%：代表任意长度的字符串，长度可以为0
_ :只能表示单个字符
[NOT] LIKE '字符串'
IS [NOT] NULL
条件表达式1 AND 条件表达式2 [... AND 条件表达式n]
条件表达式1 OR 条件表达式2 [... OR 条件表达式n]
结果不重复
SELECT DISTINCT 属性名 ......
如果属性记录有null值，则升序null是第一个，降序null值是最后一个
ORDER BY 属性名 [ASC|DESC]
GROUP BY 属性名 HAVING 条件表达式
GROUP BY 属性名
WHERE
单表查询
统计记录的条数
COUNT()
求和函数
SUM()
平均值的函数
AVG()
最大值函数
MAX()
最小值函数
MIN()
聚合函数查询
内连接查询
进行左连接查询时，可以查出'表名1'所指的表中的所有记录。而'表名2'所指的表中，只能查询出匹配的记录
LEFT JOIN
进行右连接查询时，可以查出'表名2'所指的表中的所有记录。而'表名1'所指的表中，只能查询出匹配的记录。
RIGHT JOIN
外连接查询
增加更多的限制条件
复合条件连接查询
多表联查
带IN的子查询
带比较运算符的子查询
EXISTS关键字表示存在，使用EXISTS关键字时，内存查询语句不返回查询的记录，而是返回一个真假值。如果内层查询语句返回true，外层将进行查询，否则，外层语句不进行查询或者查询不到任何记录。
带EXIST的子查询
带ANY的子查询
带ALL的子查询
子查询
去重
UNION
不去重
UNION ALL
合并查询结果
表取别名
字段取别名
为表和字段取别名
匹配字符串开始的部分
^
匹配字符串结束的部分
$
匹配字符串中任意一个字符，包含回车与换行
.
匹配“字符集合”中的任何一个字符
[字符集合]
匹配除了“字符集合”以外的任意一个字符
[^字符集合]
匹配S1、S2和S3中的任意一个字符串
S1|S2|S3
代表多个该字符之前的字符，包含0和1个
*
代表多个该字符之前的字符，包含1个
+
字符串出现N次
字符串{N}
字符串出现至少M次，最多N次
字符串[M，N]
使用正则查询
查询数据
插入
更新
插入更新删除数据
加 | 减 | 乘 | 除
算术运算符
true返回1 false返回0
大于 小于 | 等于 | 不等于 | LIKE | IN | BETWEEN AND | IS NULL | REGEXP
比较运算符
与、或、飞、异或等
逻辑运算符
只能在二进制的基础上操作
按位与、按位或、按位取反、按位异或、按位左移、按位右移等
位运算符
运算符的优先级
mysql运算符
返回x的绝对值
ABS(X)
向下取整
向上取整
FLOOR(X)
返回0-1的随机数
RAND()
RAND(X)
SIGN(X)
圆周率
PI()
四舍五入到整数
ROUND(X)
四舍五入到y位小数点
返回x的y次方
返回x的平方根
SORT(X)
返回e的x次方
EXP(X)
返回x除以y以后的余数
返回自然对数，以e为底的对数
LOG(X)
返回以10为底的对数
LOG10(X)
将角度转化为弧度
RADIANS(X)
将弧度转化为角度
DEGREES(X)
正弦
SIN(X)
反正弦
ASIN(X)
余弦
COS(X)
反余弦
ACOS(X)
正切
TAN(X)
反正切
余切
COT(X)
数学函数
返回字符串s的字符数
CHAR_LENGTH(s)
返回字符串s的长度
LENGTH(s)
字符串连接
指定分隔符连接字符串
将字符串s2替换s1的x位置开始长度为len的字符串
大写
小写
返回字符串s的前n个字符
返回字符串s的后n个字符
比如二进制前面填充0，比较适合
用字符串s2来填充s1的开始处，使字符串长度达到len
用字符串s2来填充s1的结尾处，是字符串长度达到len
将字符串s左边的空格去掉
LTRIM(s)
将字符串s右边的空格去掉
RTRIM(s)
将字符串s两端的空格去掉
TRIM(s)
将字符串s两端的s1字符串去掉
TRIM(s1 FROM s)
返回重复n次字符串s的新字符串
返回n个空格
SPACE(n)
用字符串s2替换字符串s中的s1
比较字符串
截断字符串，从n开始的len长度
同上
从字符串s中获取s1的开始位置
倒过来
REVERSE(s)
返回第n个字符串
x转化为二进制，0用s1代替，1用s2代替，分隔符为s，总共长度为len
返回第一个字符串s匹配的字符串的位置，从1开始
返回字符串s2与s1匹配的字符串的位置
字符串函数
返回当前日期
CURDATE()CURRENT_DATE()
返回当前时间
CURTIME()CURRENT_TIME()
返回当前日期和时间
NOW()CURRENT_TIMESTAMP()LOCALTIME()SYSDATE()LOCALTIMESTAMP()
也就是long类型的数字形式
以UNIX时间戳的形式返回当前时间
UNIX_TIMESTAMP()
将时间d以UNIX时间戳的形式返回
UNIX_TIMESTAMP(d)
上面的逆向操作
把UNIX时间戳的时间转换为普通格式的时间
FROM_UNIXTIME(d)
UTC也就是英国伦敦的时区来返回时间
UTC_DATE()
返回UTC时间
UTC_TIME()
返回月 1-12
MONTH(d)
月名称 January February...
MONTHNAME(d)
DAYNAME(d)
返回星期几 1星期天 2星期一
DAYOFWEEK(d)
星期几 0星期一 1星期二
WEEKDAY(d)
本年第几个星期 0-53
WEEK(d)
本年第几个星期 1-53
WEEKOFYEAR(d)
本年第几天 1-366
DAYOFYEAR(d)
本月第几天 1-31
DAYOFMONTH(d)
年
YEAR(d)
季度
QUARTER(d)
小时
HOUR(t)
分
MINUTE(t)
秒
SECOND(t)
从日期d中获取指定的值，type为指定返回的类型，如YEAR，HOUR等
EXTRACT(type FROM d)
将时间t转化为秒
TIME_TO_SEC(t)
以秒为单位的时间s转化为分秒的格式
SEC_TO_TIME(s)
计算日期d~0000年1月1日的天数
TO_DAYS(d)
计算从0000年1月1日的开始n天后的日期
FROM_DAYS(n)
计算d1~d2之间相隔的天数
计算起始日期d加上n天的日期
计算起始日期d加上一个时间段后的日期
计算起始日期d减去n天后的日期
计算起始日期d减去一个时间段后的日期
计算起始时间t加上n秒的时间
计算起始时间t减去n秒的时间
按照表达式f的格式显示日期d
按照表达式f的格式显示时间t
'%m.%d.%Y'
USA
'%Y-%m-%d'
JIS
ISO
'%Y%m%d'
INTERNAL
'%d.%m.%Y'
EUR
根据字符串s获取type类型数据的显示格式，s的取值包含：
日期和时间函数
如果表达式expr成立，返回v1，否则返回v2
如果v1不为空，显示v1，否则显示v2
CASE表示函数开始，END表示函数结束，如果expr1成立，返回v1的值。如果expr2成立返回v2，...最后ELSE 返回vn的值
CASE WHEN expr1 THEN v1 [WHEN expr2 THEN v2 ...] [ELSE vn] END
CASE expr WHEN e1 THEN v1 [WHEN e2 THEN v2...] [ELSE vn] END
CASE
条件判断函数
返回数据库的版本号
VERSION()
返回服务器的连接数
CONNECTION_ID()
返回当前数据库名
返回当前用户
返回字符串str的字符集
CHARSET(str)
返回字符串str的字符串排列方式
COLLATION(str)
返回最近生成的AUTO_INCREMENT值
LAST_INSERT_ID()
系统信息函数
主要用来给mysql用户密码加密，存到user表
PASSWORD(str)
对普通数据进行加密
MD5(str)
使用字符串pswd_str来加密字符串str，加密的结果是一个二进制数，必须使用BLOB类型的字段保存
与加密函数逆向
加密函数
将数字x进行格式化，将x保留到小数后n位，四舍五入
返回ASCII码
ASCii(s)
BIN(x)
HEX(x)
OCT(x)
将x从f1进制转化为f2进制
进制转换函数
定一个name，时间为time的锁
解锁
RELEASE_LOCKname)
判断是否存在name的锁
IS_FREE_LOCK(name)
加锁解锁
将s的字符集变为cs
CONVERT(s USING cs)
改变字符集
将表达式expr执行count次
重复执行
CASE(x AS type)
改变字段数据类型
其他函数
mysql函数
存储过程的名称
sp_name
IN输入 OUT输出 INOUT既是输入又是输出
param_name 存储过程的参数名称
type 存储过程的参数类型
[IN | OUT | INOUT] param_name type
存储过程的参数列表
proc_parameter
说明routine_body部分是由SQL语言的语句组成，系统默认的语言
LANGUAGE SQL
DETERMINISTIC 同样的输入得到同样的结果
[默认情况]NOT DETERMINISTIC 同样的输入可能得到不同的结果
指存储过程的执行结果是否是确定的
[NOT] DETERMINISTIC
指明子程序使用SQL语句的限制CONTAINS SQL 表示子程序包含SQL语言，但不包含读或者写数据的语句（默认）NO SQL表示子程序不包含SQL语句READS SQL DATA表示子程序中包含读数据的语句 MODIFIES SQL DATA表示子程序中包含写数据的语句
{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
指明谁有权限来执行，DEFINER表示只有定义者才能够执行（默认）INVOKER表示调用者可以执行
NO SECURITY {DIFINER | INVOKER}
注释信息
COMMENT 'string'
指定存储过程的特性
characteristic
SQL代码的内容，可以用BEGIN..END来标记开始和结束
routine_body
创建存储过程
存储函数的参数
func_paramter
返回值的类型
RETURNS type
SQL代码的内容
创建存储函数
声明变量
DECLARE
变量的名称，可以定义多个
var_name
变量的类型
type
变量默认值
DEFAULT value
定义变量
为变量赋值
变量的名称
复制表达式
expr
变量赋值
变量的使用
条件名字
condition_name
条件类型
condition_value
ERROR 1146(42S02)
sqlstate_value/mysql_error_code
定义条件
遇到错误不进行处理
CONTINUE
遇到错误马上退出
EXIT
遇到错误撤回之前的操作[暂不支持]
UNDO
错误处理方式
handler_type
错误类型
同条件定义
表示一些存储过程或函数的执行语句
sp_statement
条件名称
定义处理程序
实现定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。可以增强程序处理问题的能力，避免程序异常停止。
定义条件和处理程序
光标名称
cursor_name
SELECT 语句内容
select_statement
声明光标
打开光标
将光标对应的查询结果存入INTO的字段内
使用光标
关闭光标
光标的使用
条件判断语句
search_conditon
不同条件的执行语句
statement_list
IF
条件判断的变量
case_value
变量的取值
when_value
search_condition
使特定的语句重复执行，实现一个简单的额循环，没有停止循环的语句，必须遇到LEAVE语句才能停止。
LOOP
结束循环
LEAVE
跳出本次循环，进入到下一次循环
ITERATE
条件循环语句，当满足特性条件时，就会跳出循环
REPEAT
条件控制的循环语句，满足条件就执行语句
WHILE
流程控制的使用
调用存储过程和函数
查看存储过程和函数
与创建相同，只是将create 换成 alter
修改存储过程和函数
删除存储过程和函数
存储过程和函数
3、SQL查询语句
用户、权限、安全、资源控制
user表
db表储存某个用户对一个数据库的权限
db表、host表
tables_priv表可以对单个表进行权限设置columns_priv表可以对单个数据列进行权限设置
table_priv表、columns_prv表
对存储过程和存储函数进行权限设置
procs_priv表
权限表
mysql -h hostname|hostIP -P port -u username -p DatabaseName -e 'SQL语句'
exit、quit
登录、退出MySQL服务器
新建
创建删除普通用户
root用户
root修改普通用户
普通用户修改密码
普通用户和root用户密码管理
被授权的用户可以将这些权限赋予给别的用户
GRANT_OPTION
设置每个小时允许执行的count次查询
MAX_QUERIES_PER_HOUR count
设置每个小时允许执行的count次更新
MAX_UPDATES_PER_HOUR count
设置每个小时允许执行的count次连接数
MAX_CONNECTIONS_PER_HOUR count
设置每个用户同时count个连接
MAX_USER_CONNECTIONS count
with_option
授权 GRANT
收回权限REVOKE
查看权限
权限管理
用户管理
备份一个数据库
备份多个数据库
备份所有数据库
使用msqldump命令备份
直接复制整个数据库目录（不推荐）
使用mysqlhotcopy工具快速备份，不需要停止MySQL服务器（不能备份InnoDB类型的表）
数据备份
使用mysql命令还原
必须保证数据库主版本号一致
直接复制到数据库目录
数据还原
相同版本迁移
从低版本到高版本可以用上面的方法
不同版本迁移
可以生成sql文件导入到新的数据库
不同数据库迁移
数据库迁移
用SELECT ... INTO OUTFILE 导出文本文件
用mysqldump命令导出文本文件
用mysql命令导出文本文件
导出
用LOAD DATA INFILE方式导入文本文件
导入
表的导入和导出
数据备份与还原
my.cnf(linux) my.ini(windows) 配置的日志文件log-error=
mysqladmin -u root -p flush-logs
错误日志
my.cnf(linux) my.ini(windows) 配置的日志文件log=
通用日志
配置中 log-slow-queries=
配置中 long_query_time=n
慢查询日志
mysql日志
value常用的几个统计参数Connections:连接MySQL服务器的次数Uptime: MySQL服务器的上线时间Slow_queries:慢查询次数Com_select:查询操作的次数Com_insert:插入操作的次数Com_update:更新操作的次数Com_delete:删除操作的次数
SHOW STATUS LIKE 'value'
简介
id: 标识SELECT语句的编号
标识简单查询，不包含子查询和连接查询；
SIMPLE
表示主查询，或者最外层的查询语句；
PRIMARY
表示连接查询的第二个或后面的查询语句；
select_type: 标识SELECT语句的类型。
table: 表示查询的表
表示只有一条记录；
system
表示表中有多条记录，但只能从表中查询一条记录；
const
表示对表进行了完整的扫描；
ALL
表示多表连接时，后面的表使用了unique或者primary key
eq_ref
表示多表查询时，后面的表使用了普通索引
ref
表示子查询中使用了unique或者primary key
unique_subquery
表示子查询中使用了普通索引
index_subquery
表示查询语句中给出了查询范围
range
表示对表中索引进行了完整的扫描
index
type: 代表表的连接类型
possible_keys: 表示查询中可能使用的索引
key: 表示查询使用的索引
key_len: 表示索引字段的长度
ref: 表示使用哪个列或常熟与索引一起来查询记录
rows: 表示查询的行数
Extra: 表示查询过程的附件信息
EXPLAIN SELECT 语句;
分析查询语句
使用索引可以提高数据库查询的速度，从而提高数据库的性能
索引对查询速度的影响
如果匹配字符串的第一个字符为'%'，索引不会被使用，如果不在第一位，索引就会使用。
查询语句中使用LIKE关键字
多列索引是在表的多个字段上创建一个索引，只有查询中使用这些字段中第一个字段时，索引才会被使用
查询语句中使用多列索引
如果OR前后的两个条件都是索引时，查询中将使用索引，如果前后有一个不是索引，那么查询将不使用索引
查询中使用OR关键字
使用索引查询
子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询在临时表中查询记录。查询完毕后，MySQL需要撤销这些临时表。因此子查询速度会受到一定影响。-- 使用连接查询来代替子查询，连接查询不需要建立临时表。，速度比子查询快。
优化子查询
优化查询
有些表设计时设置了很多的字段，有些字段使用频率很低，数据量很大时，查询就会很慢。通过分表查询需要的字段。
将字段很多的表分解成多个表
有时需要同时查询某两个表中的几个字段。如果经常进行联表查询，会降低MySQL数据库的查询速度。可以建立中间表来提高查询速度
增加中间表
增加冗余字段
禁用索引
禁用唯一性检查
一个INSERT插入多条语句
执行多个INSERT语句插入多条记录
优化INSERT语句
优化插入记录的速度
分析关键字的分布
分析表
检查表是否存在错误
检查表
消除删除或者更新造成的空间浪费
优化表
分析表、检查表、优化表
优化数据库结构
优化硬件
索引缓存大小，值越大，使用索引进行查询更快
key_buffer_size
同时打开表的个数，值越大，能够打开的表个数越多。
table_cache
查询缓存区大小，可以提高查询速度，只有使用了 SELECT SQL_CACHE * FROM table才会生效
query_cache_size
查询缓冲区的开启状态，0关闭，1开启，2按要求使用
query_cache_type
max_connections
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
innodb_buffer_pool_size
innodb_flush_log_at_trx_commit
优化MySQL参数
优化mysql服务器
性能优化
4、MySQL数据库高级管理
mysql入门
0 条评论
回复 删除
下一页
