新手学做网站步骤,桂林漓江游船攻略,织梦网站模块,知名电子商务网站文章目录 数据库基础为什么需要数据库#xff1f;创建数据库mysql架构SQL语句分类编码集修改数据库属性数据库备份 表的基本操作存在时更新#xff0c;不存在时插入 数据类型日期类型enum和set 数据库基础
以特定的格式保存文件#xff0c;叫做数据库#xff0c;这是狭义上… 文章目录 数据库基础为什么需要数据库创建数据库mysql架构SQL语句分类编码集修改数据库属性数据库备份 表的基本操作存在时更新不存在时插入 数据类型日期类型enum和set 数据库基础
以特定的格式保存文件叫做数据库这是狭义上的概念提供较为便捷的数据存取服务的软件集合解决方案的事物这是广义上的数据库
mysql 的本质是一个进程client通过网络向目标机的守护进程mysqldserver发送sql语句目标机的守护进程执行sql操作所以mysql是一个应用程序其运行在操作系统之上
操作mysql内部的数据时本质上是操作文件内容操作文件内容需要通过cpu提供的指令。根据冯诺依曼体系cpu只能与内存进行交互所以文件必须被加载到内存中 那么mysqld启动时就需要对OS申请一块空间以执行sql操作数据库的数据远大于可以使用的空间时mysql就需要拥有自己的内存管理策略即数据的换入与换出 所以mysql也需要进行内存管理
为什么需要数据库
使用文件保存数据即可为什么需要数据库 例子大量的数据如10000个IP地址文件和数据库都能存储 问题需要将这10000个IP中以120开头的IP修改为119并统计修改的IP个数。 使用文件解决以上问题需要人为的对数据进行管理 数据库的本质也是文件其特别之处在于提供了对文件的特定操作不需要手动管理文件中的数据。即mysql为用户和磁盘文件之间的一层软件层能够控制磁盘文件以减少使用成本 特点方便安全高效
只是用文件的缺点
安全性问题海量数据的存储与处理不方便的数据控制
mysql的存储介质
磁盘为主内存为辅
连接
mysql -h IP -P 端口 -u 用户 -p
mysql -h 127.0.0.1 -P 3306 -u root -p-p表示输入密码
不指明IP与端口默认都是本地与3306
mysql -u root p在配置文件中添加port 端口号修改mysql服务绑定的端口号一般建议修改防止他人恶意攻击。默认端口号为3306 mysql是一个网络服务是一个关系型数据库noSql不是关系型数据库如redis
创建数据库
创建数据库在Linux中是在做什么 本质是在指定目录下建立一个目录默认目录是/var/lib/mysql/。甚至可以用mkdir指令直接创建一个目录此时在mysql中show databases也能看到该目录 通过网络服务将创建指令发送给mysql服务端服务端接收到指令后解析指令进行特定的操作
在mysql中system可以执行系统命令如system clear 创建数据库
create database 库名删除数据库
drop databse 库名该数据库下的文件都会被删除谨慎操作
mysql大小写不敏感。一般数据库的命令使用大写但是小写字母更好看懂所以我都是使用小写 数据库名字可以用但引号表示库名。加了引号库名就能使用特殊字符
选择数据库类比cd目录
use 库名查看当前选择的数据库
select database()展示 show tables展示表项show databases展示数据库
建表后添加了两个文件所以建表的本质是创建文件
通过查询创建语句来了解该数据库的细节信息
mysql架构
mysql分为三层
连接层负责客户端的连接mysql的本质是一个应用层软件连接层识别请求连接的IP用来保护数据库。即通过IP地址和同户名密码保护mysql语法分析层词法语法分析性能调优编译级别的性能优化存储引擎层通过系统调用与磁盘打交道插件式的存储引擎。该层有多个存储引擎通过先描述后组织的管理方案将存储引擎进行抽象管理。需要添加存储引擎时就创建相关结构不需要某个存储引擎时就释放调该存储结构
站在系统的角度mysql就是一个应用进程 站在网络的角度mysql就是应用层协议的一种
除了mysql的客户端其他的模块基本都是在Linux环境上运行的 SQL语句分类
DDLdata definition language数据定义语言属性操作。用来维护存储数据的结构createdropalterDMLdata manipulation language数据操纵语言内容操作。insertdeleteupdate DQLdata query language数据查询语言select 以上是数据库的基本功能接着是数据库的安全功能临界资源网络攻击 DCLData Control Language数据控制语言整个mysql的系统安全与账户管理工作使mysql在主动和被动的情况下都是可靠的。grantrevokecommit
存储引擎的查看
show engines编码集
mysql有两个编码集一个是编码格式集一个是校验格式集 存数据要用到编码格式取数据需要校验格式 通过命令查看字符集/编码集
show variables like character_set_%;
show variables like collation_database;有些类似环境变量的查看 show variables like character_set_%编码字符集的查看 show variables like collation_database校验集的查看 这些值都是在conf配置文件中设置好了的 show charset支持的字符集 show collation支持的校验集 每个字符集都有对应的校验集必须匹配 编码集和校验集都是针对表使用的 配置文件不指明这两集合那么mysql也有默认方案 db.opt文件中保存的就是默认的字符集和校验集
create database 数据库名 charestgbk collateutf8_bin设置字符集为gbk校验集为utf8_bin 若数据库需要备份到远端其他机器上则最好指明字符集与校验集其他情况下不需要指明 创建数据库时设置编码和校验规则本质上是影响数据库下的表所对应的编码和校验规则 创建表时也能设置编码和校验规则若不设置则会采用数据库的编码和校验规则 就近原则
查看表的列属性
desc person查找person表的内容
select * from person查找person表中含有a字符的数据
select * from person where namea当前连接mysql的用户
show processlist命令可以忽略大小写但是命令的参数需要根据数据库的编码要求进行严格的大小写 utf8_general_ci该校验集需要数据库遵守大小写 bin_db该校验集不需要数据库遵循大小写 select database();查看当前所在的数据库相当于pwd
修改数据库属性
修改数据库字符集
alter database 数据库名 charsetgbk修改只是修改数据库的编码与校验集 mysql不支持修改库名但可以用shell直接mv修改数据库名拷贝也是同理但这是违规的
数据库备份
查询mysqldump工具是否存在
which mysqldump执行以下命令
mysqldump -P 3306 -u root -p -B 库名 db_name.sqlP标识数据库使用的端口号u表示执行拷贝操作的用户p表示要输入该用户的密码B表示要拷贝的数据库名称 将数据库重定向到sql文件中不重定向的话拷贝的数据将打印到屏幕 mysqldump -P 3306 -u root -p -B 数据库名 表名1 表名2 db_name.sql只备份对应的表 以此说明mysql会保存历史输入的指令并对指令做优化合并处理
备份有两种方式备份数据与备份执行语句 恢复数据库
source .sql的绝对/相对路径表的基本操作
建表
create table 表名(属性, 属性);圆括号后的三个属性若没有指明则以mysql的默认配置为主就近原则 若创建的表名已经存在mysql将抛出错误
其中comment是一种类似备注的信息这些信息将被忽略
show create table 表名\G展示创建表时的语句\G是mysql的一种扩展语法不需要加;
不同引擎创建的表的逻辑结构是相同的但是底层的物理结构却是不同的体现在文件数量的不同如InnoDb会创建两个文件而MyISAM会创建三个文件 .frm为表的列属性 .ibdinnodb中存储表数据和索引数据的文件 .MYDMyISAM中存储表数据的文件 .MYIMyISAM中存储索引数据的文件 故MyISAM为非聚簇索引
插入数据
insert into 表名 (属性, 属性) values(属性值, 属性值);添加列属性
alter table 表名 add 属性名 属性类型 after 属性调整列属性类型
alter table 表名 modify 属性名 要调整的数据类型删除列属性 删除操作不应该经常使用无论是表的删除还是库的删除
alter table 表名 drop 要删除的属性名只剩最后一个属性时该属性无法删除只能使用删除表的指令进行删除
表属性的修改
alter table 表名 change 原属性名 新属性名 新属性的类型其中image_path为原属性名称path为新属性名称varchar(128)表示新属性的类型 同样这也是覆盖式的
表名的修改
alter table 旧表名 rename 新表名表的删除
drop table 表名表记录的删除
delete from 表名 where 属性值存在时更新不存在时插入
出现主键/唯一键冲突时插入已经存在的主键/唯一键会导致失败
在insert语句最后加上on duplicate key update 属性值, 属性值表示若插入失败则更新 0 row affected表中存在冲突数据但冲突的数据与update数据一样没有数据被更新 1 row affected表中没有冲突数据数据被插入 2 row affected表中存在冲突数据但数据被update更新
select row_count()可以查看最近一条插入语句的结果-1表示失败其他值与上述情况一样 在一般insert语句中将insert换成replace也能达到同样的效果推荐使用replace 只要存在唯一键或者主键冲突产生冲突的记录都将被修改可能是一条也可能是多条
数据类型
### 数值范围
mysql的数据类型除了满足不同的应用场景还有一个特别重要的点就是约束 tinyint类比c语言char类型一个字节
若插入的数据超出了数据的表达范围那么mysql将直接报错因为不允许这样的操作 这就倒逼程序员插入数据时必须遵守mysql的规范
有些数据类型可以带上unsigned修饰表示无符号 bit类型后带上()表示该数据类型使用几个bit位bit类型的数据以ASCII码的格式显示 可以用where这样的语句读取bit类型的数据
salary float(M, D)M表示显示长度数字的总数d表示小数位数
如salary float(4,2)表示的范围是(-99.99, 99.99)
插入数据时遵守四舍五入规则
salary float(4,2) unsigned的范围是(0, 99.99)负数不允许存储 decimal(4,2)规则和float一样 decimal存储的数据精度更高float的精度大约是7位 decimal的m最大为65默认为10d最大为30默认为0
char(L)L表示字符串的长度最多是255 mysql中的字符与字节不等价
高级语言中的字符表示一个字节而mysql的长度不是字节的意思而是字符的意思。一个汉字虽然是一个字符但是汉字的大小通常不是一个字节。但是在mysql中长度为2的char可以存储两个汉字
varchar(L)的使用和char一样其中L表示的是varchar的长度L和表的编码有密切关系 varchar需要用1~3个字节表示存储的字符串长度varchar的最多能存储65535字节数据因此varchar的有效存储字节数为65532 当编码是utf8时L的最大值为65532/321844因为utf8中一个字符占3个字节。编码是gbk时L的最大值是65532/232766因为gbk中一个字符占用2个字节
变长指的是在固定长度中变长相比于charvarchar可以有效的提高磁盘空间的使用 当长度不固定时尽量使用变长字符串
日期类型
dateyyyy-mm-dd占用三字节 datetime:yyyy-mm-dd HH:ii:ss占用8字节 timestamp时间戳格式和datetime一样占用4字节
插入表时无法对时间戳类型进行数据插入时间戳的数据会在你插入修改的一瞬间进行更新 t1t2t3分别为datadatetime和timestamp类型的数据 enum和set
enum和set的约束性更强 enum多选一set多选多 若enum为(‘男’, ‘女’)插入时可以用具体的数据也可以用123代替具体的数据…注意从1开始 set是二进制结构的选择因为是多选多 对于set用where查询时只能查询完全相同的set比如爱好为’唱跳’只能查出爱好为’唱跳’的人唱跳rap’的人则查不出 此时需要用函数find_in_set(sub, str_list)如果sub在str_list中则返回其下标不在返回0
select * from votes where find_in_set(rap, hobby)); select * from votes where find_in_set(rap, hobby) and find_in_set(唱, hobby)); select * from votes where find_in_set(rap, hobby) or find_in_set(唱, hobby));
