文件保存数据缺点:
文件的安全性问题
文件不利于数据查询和管理
文件不利于存储海量数据
文件在程序中控制不方便
连接服务器
mysql -h 127.0.0.1(默认本地连接) -P 3306(端口号) -u root(用户名)-p xxx(密码,明文,不推荐)
注意:连接前需要打开mysql服务 (运行窗口:services.msc 打开服务)或者
命令行 net start/stop MySQL57(你的服务名)开启/关闭服务
MYSQL架构
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jlkxt8TN-1585360158449)(F:\courseware_note\MySQL课件\mysql_framework.png)]
(1)MySQL向外提供的交互接口(Connectors)
Connectors组件,是MySQL向外提供的交互组件,如java,.net,php等语言可以通过该组件来操作SQL语 句,实现与SQL的交互。
(2)管理服务组件和工具组件(Management Service & Utilities)
提供对MySQL的集成管理,如备份(Backup),恢复(Recovery),安全管理(Security)等
(3)连接池组件(Connection Pool)
负责监听对客户端向MySQL Server端的各种请求,接收请求,转发请求到目标模块。每个成功连接MySQL Server的客户请求都会被创建或分配一个线程,该线程负责客户端与MySQL Server端的通信,接收客户端发送的命令,传递服务端的结果信息等。
(4)SQL接口组件(SQL Interface)
接收用户SQL命令,如DML,DDL和存储过程等,并将最终结果返回给用户。
(5)查询分析器组件(Parser)
首先分析SQL命令语法的合法性,并尝试将SQL命令分解成数据结构,若分解失败,则提示SQL语句不合理。
(6)优化器组件(Optimizer)
对SQL命令按照标准流程进行优化分析。
(7)缓存主件(Caches & Buffers)
缓存和缓冲组件
(8)插件式存储引擎(Pluggable Storage Engines)
MySQL存储引擎在MySQL中扮演重要角色,其作比较重要作用,大致归结为如下两方面:
作用一:管理表创建,数据检索,索引创建等
作用二:满足自定义存储引擎开发。
(9)物理文件(File System)
实际存储MySQL 数据库文件和一些日志文件等的系统,如Linux,Unix,Windows等。
存储引擎的查看及对比
查看存储引擎
show engines;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rpFcgYse-1585360158451)(F:\courseware_note\MySQL课件\showengines.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BJ07tGVq-1585360158452)(F:\courseware_note\MySQL课件\enginnes.png)]
不同的存储引擎,创建表的文件不一样。
MyISAM 在数据目中有三个不同的文件,分别是
.frm:表结构
.MYD:表数据
.MYI:表索引
innodb 在数据目录中有两个不同的文件,分别是
.frm:表结构
.ibd:表数据,表索引
总结:
常用的三种引擎对比(innodb,MyISAM,memory)
//TODO
SQL分类
DDL数据定义语言
用来维护存储数据的结构
代表指令: create, drop, alter
DML数据操纵语言
用来对数据进行操作
代表指令: insert,delete,update
DML中又单独分了一个DQL数据查询语言
代表指令: select
DCL数据控制语言
主要负责权限管理和事务
代表指令: grant,revoke,commit
库的操作
创建库
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name //指定数据库采用的字符集
[DEFAULT] COLLATE collation_name //指定数据库字符集的校验规则
查看系统默认字符集以及校验规则
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SxuVU3kZ-1585360158452)(F:\courseware_note\MySQL课件\charset_collation.png)]
查看数据库支持的字符集/校验规则
show charset;
show collation;
注意:
utf8_ general_ ci [不区分大小写]
utf8_ bin [区分大小写]
操作库
显示创建语句
mysql> show create database mytest;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| mysql | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
说明:
MySQL 建议我们关键字使用大写,但是不是必须的。
数据库名字的反引号``, 是为了防止使用的数据库名刚好是关键字
/*!40100 default… */ 这个不是注释,表示当前mysql版本大于4.01版本,就执行这句话
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-l2SDD0bx-1585360158454)(F:\courseware_note\MySQL课件\without_keyword.png)]
修改数据库
对数据库的修改主要指的是修改数据库的字符集,校验规则
create—>alter 语法相同
删除数据库
DROP DATABASE [IF EXISTS] db_ name;
执行删除之后的结果:
数据库内部看不到对应的数据库
对应的数据库文件夹被删除,级联删除,里面的数据表全部被删
不要随意删除数据库!!!!
备份和恢复
备份操作(退出连接) 不用连接数据库也可执行
备份库
mysqldump -P3306 -u root -p 密码 -B 数据库名1 数据库名2 ... > 数据库备份存储的文件路径.sql
备份表
mysqldump -P3306 -u root -p 密码 -B 数据库名 表1 表2 ... > 数据库备份存储的文件路径.sql
恢复
source 备份的路径.sql;
注意:
如果备份一个数据库时,没有带上-B参数, 在恢复数据库时,需要先创建空数据库,然后使用数据库,再使
用source来还原。
查看连接情况
show processlist
告诉我们当前有哪些用户连接到我们的MySQL,如果查出某个用户不是你正常登陆的,很有可能你的数据库被 人入侵了。如果发现自己数据库比较慢时,可以用这个指令来查看数据库连接情况。
表的操作
创建表
CREATE TABLE table_name (
field1 datatype [commit],
field2 datatype [commit],
field3 datatype ) character set 字符集 collate 校验规则 engine 存储引擎;
//commit 注释 对字段的描述
查看表结构
desc 表名;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VSTR6bi6-1585360158454)(F:\courseware_note\MySQL课件\tablestructure.png)]
修改表
修改表的结构,比如字段名字,字段大小,字段类型,表的字符集类型,表的存储引擎等等。
添加(add)/修改(modify)/删除(drop)字段等等。这时我们就需要修改表。
ALTER TABLE tablename ADD (column datatype [DEFAULT expression][,column datatype]...);
ALTER TABLE tablename MODIfy (column datatype [DEFAULT expression][,column datatype]...);
ALTER TABLE tablename DROP (column);
定位添加一个字段(对原表数据没有影响) add
alter table 表名 add field datatype [comment 'xxx' after field(已存在字段)];
修改一个字段类型或字段类型长度 modify
alter table 表名 modify field datatype 约束条件;
删除一个字段 drop(删除字段会将对应列的数据都删除)
alter table 表名 drop field;
修改表名 rename (to) to可以省略
alter table 表名 rename (to) 新名称;
修改字段名(列名) change eg. 将name 改为 xingming。
alter table 表名 change name xingming varchar(60); --新字段需要完整定义
删除表
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...
数据类型
数据类型分类
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bi2y0FW3-1585360158455)(F:\courseware_note\MySQL课件\datatype.png)]
数值类型
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bU0KCuCO-1585360158456)(F:\courseware_note\MySQL课件\int.png)]
默认有符号
尽量不使用unsigned,对于int类型可能存放不下的数据,int unsigned同样可能存放不下,直接将int类型提升为bigint类型。
bit类型
bit[(M)] : 位字段类型。M表示每个值的位数,1到64。如果M被忽略,默认为1。
bit字段在显示时,是按照ASCII码对应的值显示。(若插入65,则显示A)
值只存放0或1,这时可以定义bit(1)。这样可以节省空间。
float类型
float[(m, d)] [unsigned] : M指定显示长度,d指定小数位数,占用空间4个字节
float(4,2)表示的范围是-99.99 ~ 99.99,MySQL在保存值时会进行四舍五入。
decimal
decimal(m, d) [unsigned] : 定点数m指定长度,d表示小数点的位数 m省略默认为10 d省略默认为0
和float的区别:
decimal的精度更准确,比float精度更高(float表示的精度大约是七位)
decimal最大整数位m为65,支持小数最大尾数d是30
如果需要更高的精度,使用decimal
字符串类型
char
char(L): 固定长度字符串,L是可以存储的长度,单位为字符,最大长度值可以为255
varchar
varchar(L): 可变长度字符串,L表示字符长度,最大长度65535个字节
char(2)/varchar(2) 可存储 ab 或者 工大 按个数算。
关于varchar(len),len到底是多大,这个len值,和表的编码密切相关:
varchar长度可以指定为0到65535之间的值,但是有1 - 3 个字节用于记录数据大小,所以说有效字节数是 65532。
当我们的表的编码是utf8时,varchar(n)的参数n最大值是65532/3=21844[因为utf中,一个字符占用3个字 节]
如果编码是gbk,varchar(n)的参数n最大是65532/2=32766(因为gbk中,一个字符占用2字节)。
char和varchar的比较
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GQcbEfTI-1585360158456)(F:\courseware_note\MySQL课件\char_varchar.png)]
如何选择?
总结: (char)定长的磁盘空间比较浪费,但是效率高 。如果数据确定长度一样,使用char
例如:身份证号,手机号
(varchar)变长的磁盘空间比较节省,但是效率低。如果数据长度有变化,使用varchar
例如:姓名,地址。 但是要保证最长的数据可以存储。
日期和时间类型
常用的日期有如下三个:
datetime 时间日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范围从1000到9999,占用八字节
timestamp:时间戳,从1970年开始的 yyyy-mm-dd HH:ii:ss格式和datetime完全一致,占用四字节
date:日期 ‘yyyy-mm-dd’,占用三字节
timestamp(时间戳)不需要赋值,显示当前时间
enum和set
enum
枚举,单选类型
enum('选项1','选项2','选项3',...); 最多 65535个选项
set
集合,多选类型
set('选项值1','选项值2','选项值3', ...); 最多 64个选项
该设定只是提供了若干个选项的值,最终一个单元格中,实际只存储了其中一个值;
而且出于效率考虑,这些值实 际存储的是“数字”
枚举enum所对应的数字是1,2,3,4…65535,添加枚举值的时候,也可以添加对应的编号 (但不建议,不利于阅读)
集合set所对应的的数字是1,2,4,8,16,32…最多64个选项
表的约束
约束字段的是字段的数据类型,但单数据类型约束很单一。
为了更好地保证数据的合法性,从业务逻辑角度保证数据的正确性,需要表的约束
空属性
两个值:null(默认值)/ not null
因为空数据没办法参与运算,实际开发中尽可能保证字段不为空
not null 约束的字段 插入数据时不给该字段插值则插入失败
默认值
mysql> create table tt10 (
-> name varchar(20) not null,
-> age tinyint unsigned default 0,
-> sex char(2) default '男'
-> );
数据在插入的时候不给该字段赋值,就使用默认值
列描述
comment,专门用来表述字段,根据创建语句保存,方便程序员或DBA(数据库管理员)进行了解
注意:
comment 注释信息通过 show create 命令查看 , desc 命令查看不到
zerofill(补零)
int(5) unsigned zerofill;
如果宽度小于设定的宽度(int(5),这里设置的是 5),自动填充0。
若插入1,则显示00001,zerofill 只影响显示结果,实际储存的还是1 (可用hex()函数证明&