MySQL笔记汇总篇
MYSQL简介
MySQL是一个开源的关系型数据库管理系统。
MYSQL的目录结构
1、bin目录：用于存储一些可执行文件，如mysql.exe等。
2、目录：用于存储包含的一些头文件，如mysql.h等。
3、lib目录：用于存储一些库文件。
4、share目录：用于存储错误信息、字符集文件等。
5、data目录：用于放置一些日志文件以及数据库。
6、my.ini文件：数据库的配置文件。
命令行启动mysql
net start →启动服务
net stop →停止服务
MySql的登录：
参数
描述
-u
用户名
-p
密码
-V
输出版本信息并且退出
-h
主机地址
MySql的退出：exit
quit
\q
MySql的常用命令
1、命令用于修改用户密码
命令格式： -u用户名 -p旧密码 新密码
2、show 命令用于显示所有数据库
命令格式：show ;
3、use命令使用数据库
命令格式：use ;
4、命令用于显示当前连接（选择）的信息
显示当前连接的数据库： ();
显示当前服务器版本： ();
显示当前日期时间： now();
显示当前用户： user();
创建数据库：在系统磁盘上划分一块区域用于数据的存储和管理。
[if not ] ; //创建数据库
[] set[=] ;//指定默认字符集
修改数据库
alter ; //修改数据库
[] set[=] ;//指定默认字符集
删除数据库
drop [if not ] ; //删除数据库
MYSQL的数据结构
数据类型：指数据列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。
整型： 1字节
2字节
3字节
int4字节
8字节
浮点数类型： float[(m,d)] 4字节
[(m,d)] 8字节
定点数类型： [(m,d)]
m是精度（=整数位数+小数位数），d是标度（小数点后的位数）
浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值
日期时间型： 类型 字节数 表示形式
year 1 yyyy
time 3 hh:mm:ss
date 4 yyyy-mm-dd
8 yyyy-mm-dd hh:mm:ss
4 yyyy-mm-dd hh:mm:ss
字符型： 类型 存储需求
char(m) 定长型，m个字节
(m) 变长型，l+1个字节
0~255
text 0~65535
0~2的24次方-1
0~2的32次方-1
enum(‘’,’’,…)取决于枚举值的个数（最多65,535个值）选单个值
例：性别 enum(‘男’,’女’)
set(‘’,’’,…)取决于set成员的数目（最多64个成员）选多个值
数据库表结构的操作
创建数据库表
列名1 数据类型[列级别约束条件][默认值],
列名2 数据类型[列级别约束条件][默认值],
……
[表级别约束条件]
);
复制数据库表
table 表1 as * from 表2；
查看数据库表
show [from ];
查看数据表基本结构
1．show from ;
2． ;/desc ;
查看表详细结构语句，可以用来显示数据表的创建语句
show table ;
①添加列
alter table
add
[约束条件][first|after已存在列名];
②修改列名
alter table
;
③修改列的数据类型
alter table ;
alter table ;
④修改列的排列位置
alter table
first|after ;
⑤删除列
alter table drop ；
⑥修改表名
alter table [to] ;
删除数据库表
使用drop table可以一次删除一个或多个没有被其他表关联的数据表。
drop table [if ] 表1,表2,…表n;
表分区：就是把一张表的数据分成多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上，但所有的数据还在一个表中。
show ; //判定数据库版本是否支持表分区
显示以下信息表示支持
创建表分区
在创建表的时候使用： by 类型 (字段)
range分区：
根据指定某列的范围值进行分区。
使用 less than操作符定义分区。
例： table (//表定义
int,
(20)
by range()(//定义分区类型和字段
p1 less than (),//分区的定义
p2 less than (),
p3 less than
);
MYSQL约束
约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的完整性、唯一性。
约束类型非空约束主键约束 唯一约束 默认约束 外键约束
关键字 not null key key
非空约束：指字段的值不能为空。对于使用了非空约束的字段如果用户在添加数据时，没有指定值，数据库系统会报错。
语法规则：列名 数据类型 not null;
null：字段值可以为空。
not null：字段值禁止为空。
主键约束：要求主键列的数据唯一，并且不允许为空，主键能够唯一的标识表中的一条记录。
1、单字段主键
①在定义列的同时指定主键
列名 数据类型 key;
②在列定义的后面指定主键
[ ] key (列名);
2、多字段联合主键（或者叫复合主键）
主键有多个字段（列）联合组成。
key(字段1,字段2,…字段n);
修改表时添加主键约束
① alter table key;
② alter table add key (列名);
③ alter table add key (列名);
删除主键：alter table drop key;
唯一约束：要求该列唯一，允许为空，唯一约束可以确保列或者几列不出现重复指。
修改表时添加唯一约束
① alter table ;
② alter table add (列名);
③ alter table add (列名);
删除唯一约束
① alter table drop index ;
② alter table drop key ;
默认约束：指定某列的默认值。
语法规则：列名 数据类型 默认值;
修改表时添加唯一约束
① alter table 默认值;
② alter table alter set 默认值;
删除默认约束
① alter table ;
② alter table alter drop ;
外键约束
外键：用来在两个表的数据之间建立链接，它可以使一列或者多列。
外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。
语法格式： [ ] key(列名)
(主键);
修改表时添加外键约束
alter table add key(列名) (主键);
删除外键约束
alter table drop key ;
外键约束的参照操作
：从父表删除或更新且自动删除或更新子表中匹配的行
实现级联删除
[ ] key(列名)
(主键) on ;
图形化管理工具
MySql 是一款专门为用户提供了用于创建、修改、执行和优化SQL的可视化工具，通过它开发人员可以很轻松的管理数据库数据。
是一个易于使用的、快速而简洁的图形化管理MYSQL数据库的工具，它能够在任何地点有效的管理数据库。
数据库表记录的操作
① 为表的所有列插入数据
into 表名(数据列) (数据);
② 插入多条记录
into 表名(数据列) (数据1),(数据2),…,(数据n);
③ 将查询结果插入到表中
into 表名(数据列) (数据列) from 表名 where 条件;
设置表的属性自动增加
语法格式：列名 数据类型
注：约束的字段可以是任何整数类型。
建表时可用” =n”选项来指定一个自增的初始值。
为已有的表添加自增列
alter table 表名 列名 数据类型 ;
修改自增列的起始值
alter table 表名 = x;（修改后列起始值从x开始）
去掉自增列
alter table 列名 数据类型;
单表数据记录的更新
语法格式： 表名 set 列名1=数据值1,…,列名n=数据值n where(条件);
单表记录的删除
① from 表名[where ];
② table 表名;
将直接删除原来的表，并重新创建一个表。
查询结果不重复
列名 from 表名;
使用关键字指示MySQL消除重复的记录值。
查询空值
* from 表名 where 列名 is null;
可以查询某列内容为空的记录。
分组查询
[group by 列名][ ];
group by通常和聚合函数一起使用。
限定显示记录所需满足的条件，只有满足条件的分组才会被显示。
对查询结果排序
order by 列名 [asc|desc];
用limit限制查询结果的数量
limit关键字可以返回指定位置的记录
语法格式：limit [位置偏移量,]行数;
说明：第一条记录的位置偏移量为0，第二条为1，… 以此类推。
运算符与函数
算术运算符用于各类数值运算
+ - * / %
加法 减法 乘法 除法 取余（取模）
比较运算符
>、=、D:test.sql
备份数据库表：
例： --no- -uroot -p > xxx.sql
备份多个数据库：
例： --no- -uroot -p -- mysql > xxx.sql
备份全部数据库：
例： --no- -uroot -p --all- > xxx.sql
数据的恢复
使用mysql命令恢复，语法格式：
mysql –u user –p [] < .sql
如果已经登录没有到期了服务器，还可以使用命令导入SQL文件，语法：
导出：就是将mysql数据库中的数据导出到外部存储文件中。
导入：就是将外部存储文件中的数据导入到mysql数据库中。
数据表的导出
使用…into 导出文本文件，语法格式： from table where into ‘’ []
[]为可选参数选项，比如：
① 参数 by ‘value’：设置字段之间的分割字符，可以为单个或多个字符，默认情况下为制表符’\t’。
② 参数lines by ‘value’：设置每行数据结尾的字符，可以为单个或多个字符，默认情况下为制表符’\n’。
文件导入导出路径：
my.ini→ -file-priv="e" //指定mysql导入导出路径
-file-priv=null //限制mysql导入导出
-file-priv=’’ //mysql导入导出不做任何限制
例：mysql> * from . into '…/class.txt'
-> by ','
-> lines by '\r\n';
使用命令导出文本文件
创建一个包含创建表的 table语句的.sql文件和一个包含其数据的.txt文件。
语法格式： –T path –u root –p [] []
[]为可选参数选项，比如：
1、----by=value：设置字段之间的分隔字符，可以为单个或多个字符，默认情况下为制表符’\t’。
2、--lines--by=value：设置每行数据结尾的字符，可以为单个或多个字符，默认情况下为制表符’\n’。
使用mysql命令导出文本文件
语法格式：mysql –u root –p --=”语句” > .txt
（导出文件第一行多出字段名）
数据表的导入
使用load data 方式导入文本文件，语法格式： load data ‘.txt’ into table [] [ lines]
[]为可选参数选项，比如：
① 参数 by ‘value’：设置字段之间的分割字符，可以 为单个或多个字符，默认情况下为制表符’\t’。
② 参数lines by ‘value’：设置每行数据结尾的字符，可以为单个或多个字符，默认情况下为制表符’\n’。
使用命令导入文本文件
语法格式： –u root –p .txt []
[]为可选参数选项，比如：
1、----by=value：设置字段之间的分隔字符，可以为单个或多个字符，默认情况下为制表符’\t’。
2、--lines--by=value：设置每行数据结尾的字符，可以为单个或多个字符，默认情况下为制表符’\n’。
补充：
USE数据库名:
选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。
SHOW :
列出 MySQL 数据库管理系统的数据库列表。
SHOW :
显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库。
SHOW FROM数据表:
显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。
SHOW INDEX FROM数据表:
显示数据表的详细索引信息，包括 KEY（主键）。
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'] \G:
该命令将输出Mysql数据库管理系统的性能及统计信息。
# 加上 \G，查询结果按列打印
UNION 操作符用于连接两个以上的 语句的结果组合到一个结果集合中。
UNION [ALL | DISTINCT]
:可选，删除结果集中重复的数据。
ALL:可选，返回所有结果集，包含重复数据。
没有使用百分号%, LIKE 子句与等号=的效果是一样的。
当提供的查询条件字段为 NULL 时，MySQL提供了三大运算符:
事务
在MySQL 中只有使用了 数据库引擎的数据库或表才支持事务。
索引分单列索引和组合索引
单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。
组合索引，即一个索引包含多个列。
创建索引
CREATE INDEX indexName ON table_name (column_name)
修改表结构（添加索引）
ALTER table tableName ADD INDEX indexName(columnName)
临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
如果使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然也可以手动销毁。
INTO 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据
查看MySQL的安装目录和数据存放目录
@@; #安装目录
@@; #数据存放目录
show like '%%'; #查看-file-priv值
？MySQL导出文件时，遇到报错“The MySQL is with the ---file-priv so it this ”
！找到my.ini配置文件，搜索，修改-file-priv路径（注意斜杠和反斜杠），重启MySQL服务。
使用 ... INTO 语句导出数据
通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：
mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
-> FIELDS TERMINATED BY ',' [optionally] ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';
MySQL 中 where in 语句的子查询限制
