常用网站开发语言,网站建设公司 资讯,建站公司推荐首推万维科技,网站怎么做404文章目录 一、MySQL服务的安装二、数据库基础1.什么是数据库#xff1f;#xff08;基于CS模式的一套数据存取的网络服务#xff09;2. Linux文件系统和数据库的关系 主流数据库3.MySQL架构 SQL分类 MySQL存储引擎 三、MySQL操作库1.库结构… 文章目录 一、MySQL服务的安装二、数据库基础1.什么是数据库基于CS模式的一套数据存取的网络服务2. Linux文件系统和数据库的关系 主流数据库3.MySQL架构 SQL分类 MySQL存储引擎 三、MySQL操作库1.库结构的CURD操作2.库的备份与恢复 数据库的连接情况 四、MySQL操作表1.表结构的CURD操作2.对库结构和表结构操作的SQL语句的小总结DDL语句 五、MySQL数据类型1.数值类型1.1 整数类型1.2 小数类型 2.字符串类型2.1 char和varchar2.2 日期和时间类型2.3 枚举和集合类型 六、MySQL表的约束1.not null约束 default约束2.comment约束 zerofill3.primary key约束 auto_increment约束4.unique key约束 foreign key references约束5.综合案例 一、MySQL服务的安装
1. 安装mysql服务器的时候我们将用户身份切换为root安装好之后普通用户也是可以通过MySQL命令行式的客户端来访问mysqld服务初期学习MySQL的时候建议直接使用root的身份来进行学习快速上手基本的sql语句编写之后我们在后期学习用户管理时可以自己手动新建普通用户给普通用户赋予一定的数据库管理权限。
2. 安装MySQL需要分为两个步骤首先需要卸载干净掉云服务器上可能预先内置的MySQL服务我们需要将原有的环境先卸载干净然后在服务器上重新安装布置MySQL服务。 (1)云服务器上可能内置了mariadb(MySQL的一个开源分支)或MySQL所以先查看一下是否有这些服务如果有则将这些服务停止掉。查看是否存在服务的指令 ps axj | grep mariadb 停止服务的指令systemctl stop mariadb.service 停止掉服务之后我们可以继续ps axj | grep mariadb 或 ps axj | grep mysql 查看系统中是否有这些存储服务如果没有说明我们停止成功。 (2)如果你曾经安装过mysql或mariadb服务则系统中一定会留有yum源安装时从远端拉取到本地的安装包这些安装包的后缀都是rpm查看所有的yum残留安装包的指令rpm -qa查看mariadb或mysql残留安装包的指令rpm -qa | grep mysql 和 rpm -qa | grep mariadb卸载时如果一个一个卸载可能有些麻烦所以我们可以通过rpm查询后的结果通过管道传输然后将管道传输的内容传递给xargs指令xargs再将这些文件名传递给yum remove指令让yum remove统一卸载这些残留的安装包卸载的指令为rpm -qa | grep mysql | xargs yum -y remove卸载成功后我们可以再次调用rpm -qa | grep mysql查看是否还有剩余的安装包如果没有则说明我们已经卸载残留的安装包成功了。 (3)当系统中已经停止掉了mysqld服务并且卸载了所有的剩余安装包则说明原有环境已经干净了。为了检验原有环境是否完全干净我们还可以ls /etc/my.cnf查看是否存在my.cnf这个文件如果该文件不存在则环境已经干净了。ls /var/lib/mysql中的内容是上一个mysql数据库中所残留的数据MySQL服务在卸载的时候默认不会将数据删除掉这些数据我们可以不用管他们并不影响我们后续MySQL服务的安装和使用。 3. (4)接下来我们就可以正式的在我们linux机器上部署MySQL服务了首先我们需要去官方下载官方的mysql yum源mysql yum源地址打开地址后会有很多版本的mysql yum源确保我们下载正确的mysql版本之前我们需要查看一下自己linux机器的版本cat /etc/redhat-release点开网页之后其实是找不到centos 7.6版本的yum源的我们需要右键查看页面源代码显示出完整信息之后就找el7的el代表centos至于MySQL服务的版本不需要选择太新的选择MySQL5.7版本的即可147行的yum源就正好符合我的linux机器版本以及我所需要的mysql服务版本。 下载之后我们可以将该文件ctrlx剪切到桌面上(方便查找)然后在xshell下可以通过sudo rz命令将桌面上的yum源文件上传到linux机器上注意xshell不支持从桌面拖拉文件到xshell界面的方式来上传文件必须通过rz指令来上传文件到linux机器上。 上传之后我们就会得到一个以.rpm为后缀的yum源文件然后我们需要将该yum源安装到linux机器上的yum源清单中通过rpm -ivh mysql57-community-release-el7.rpm安装之后ls /etc/yum.repos.d/ -al可以查看到系统的yum源清单更新yum源之后我们可以通过yum list | grep mysql查看系统中相关的mysql的资源。更新yum源之后我们就可以将上传到linux上的mysql yum文件删除掉了这个.rpm文件现在就没什么用了。 (5)现在我们就可以使用yum命令一键安装mysql服务了使用sudo yum install -y mysql-community-server此时yum就会按照自己的yum源自动的帮我们一键安装MySQL服务安装过程中可能会遇到密钥过期的问题我们可以从网络中拿取新的密钥进行更新linux机器的密钥rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022执行该指令即可更新Linux机器的密钥更新之后重新yum安装MySQL服务即可。 安装成功之后ls /etc/my.cnf可以看到my.cnf文件并且netstat -nltp还可以看到守护进程mysqld启动或者停止这样的守护进程mysqld我们可以通过systemctl stop mysqldsystemctl start mysqldsystemctl restart mysqld(重启守护进程)。 上传好的yum源文件以.rpm为文件后缀 将yum源安装到linux机器上的yum源清单中
安装MySQL服务之后可以看到一个守护进程mysqld该进程即为MySQL的服务进程为我们提供MySQL数据存储服务。 在bin和sbin目录下分别存在两个可执行文件一个是mysql代表登录mysqld服务的客户端一个是mysqld代表提供网络数据存储服务的服务端。 3. (6)安装好MySQL的整个服务之后我们就可以通过命令行式的客户端来使用和登录mysqld服务了mysql -uroot -p即可登录MySQL服务但在登录时由于我们没有密码所以还需要将my.cnf配置文件的内容更改一下在文件末尾加入一行skip-grant-tables即跳过鉴权这样在登录的时候我们就不需要输入密码直接连续点两下回车即可登录MySQL服务了登录MySQL服务之后show databases;即可看到当前数据库中已有的database。注意修改my.cnf配置文件后需要让配置的内容生效但此时mysqld服务进程早就已经启动了所以我们需要让mysqld守护进程重新启动一下这样才能使跳过鉴权的操作生效 4. 至此为止我们就搞定了MySQL的登录问题但还差一件事就是配置my.cnf文件主要配置的是MySQL客户端和服务端的编码格式我们统一使用utf8的编码格式保证客户端和服务端使用的编码是一致的。 mysql客户端的编码格式我们先不设置只设置mysqld服务端的即可配置之后还是老样子重启mysqld服务让我们的配置生效即可。 systemctl enable mysqld 和 systemctl daemon-reload可以设置mysqld服务为开机自启动不过因为我们使用的是云服务器云服务器不会关机所以这个指令设置不设置都没什么区别如果你用的是虚拟机则可以设置。 二、数据库基础
1.什么是数据库基于CS模式的一套数据存取的网络服务
1. 使用命令行来登录MySQLmysql -h127.0.0.1 -P3306 -uroot -p来进行登录但今天我们的客户端和服务端都是在同一台linux机器上上面的-h和-P选项可以不带因为mysql客户端默认连接的就是本地的MySQL服务另外my.cnf配置文件中已经有了mysqld服务的端口号3306所以我们的客户端在连接MySQL服务时是可以找到本地主机上的mysqld网络服务的。 所以实际在登录mysql服务时只需要指明-u用户是谁以及-p密码是什么即可由于我们在my.cnf中加了skip-grant-tables所以-p也不需要我们输密码直接回车就可以登录。
2. MySQL是一种基于CS(client and server)模式的网络数据存储服务提供了客户端mysql以及服务端mysqld客户端可以通过网络连接到服务器上并向服务器发送SQL语句请求服务器负责执行SQL语句并将执行结果返回到客户端上。 服务器是数据库的管理者负责存储管理维护数据库中的数据同时MySQL支持多种客户端的连接方式除了我们现在所使用的命令行式的客户端连接方式外还支持图形化界面workbench以及语言级别的API来连接mysql_init() mysql_real_connect()所以mysqld可以允许多个客户端同时连接到一个数据库服务器上实现多个客户端之间的协同操作以及数据共享。 而我们口语上所说的数据库大多数是指在内存中或磁盘中存储的特定结构组织的数据表和表之间互相关联而成的数据存储结构但实际上数据库是一套在内存上运行的网络服务用于实现数据存取。
3. 存储数据用文件就可以了为什么还要搞个数据库呢 (1)文件不利于数据的查询和管理同时文件不利于存储海量数据其实这两点说的是同一点。 因为普通的文件存储方式只适合于小规模的数据存储例如存储文本文件图像、视频等文件一旦存储海量数据文件不会对数据进行管理和组织如果我们要查询海量数据中的某些数据时则需要自己手动编写文件IO的代码这太低效了每次查询都需要重新编写文件IO的代码同时文件存储方式也没有提供数据索引和查询功能一旦查询则需要在海量数据中一个一个遍历的查询效率太低。 (2)文件非常不安全因为文件没有加密和权限控制等方式反观数据库通过权限认证加密等方式来保证数据的安全性。 (3)文件在程序控制中非常的不方便想必这个缺点大家都感同身受吧文件操作简直太恶心了要处理读取内容末尾的\n并且读取上来的内容也都是一大块放到缓冲区里的数据同样没有对数据进行组织和管理。 所以数据库对我们来说相当于一个中间件以往我们对数据进行存取可能只能通过文件读写的方式来进行但现在有了数据库之后我们可以将自己的需求通过mysql客户端交给mysqld服务器mysqld会和磁盘打交道进行我们的sql语句的执行从磁盘中拿取对应的数据或其他处理结果将这个处理结果返回到mysql客户端这样就完成了程序员对数据的存取需求。 程序员直接操纵mysql客户端即可完成对磁盘上数据处理的工作不用在繁琐的进行文件IO你给mysqld请求mysqld给你返回结果。 2. Linux文件系统和数据库的关系 主流数据库
1. 实际上所谓的在MySQL中建立database实际就是在对应linux文件系统中的/var/lib/mysql路径下创建一个目录所谓的在database中建立一个或多个table实际就是在linux的mysql路径下的目录中创建对应的文件。这些工作都是由谁做的呢实际就是由mysqld服务器做的服务器会在linux机器的磁盘上创建出对应的目录和文件。 所以数据库的本质就是文件每个database都会对应linux磁盘上的一个文件只不过这些文件不应该由程序员直接手动操控而是应该由数据库mysqld服务器来进行操控让mysqld来进行磁盘上数据的存取。 2. 下面这张图刻画了MySQL服务和Linux文件系统的关系对于普通的数据我们可以直接操纵文件系统来处理磁盘上的数据而对于需要管理和组织的数据我们则通过数据库管理系统即CS模式下来进行数据的存取。 一般开发人员会对每个特定业务都分配一个database在每个database内部会创建多个相互级联和组织到一起的表结构用于存储该业务所需要的数据。 3. SQL Sever 微软的产品.Net程序员的最爱中大型项目。 Oracle 甲骨文产品适合大型项目复杂的业务逻辑并发一般来说不如MySQL。 MySQL世界上最受欢迎的数据库属于甲骨文并发性好不适合做复杂的业务。主要用在电 商SNS论坛。对简单的SQL处理效果好。 PostgreSQL :加州大学伯克利分校计算机系开发的关系型数据库不管是私用商用还是学术研 究使用可以免费使用修改和分发。 SQLite 是一款轻型的数据库是遵守ACID的关系型数据库管理系统它包含在一个相对小的C库 中。它的设计目标是嵌入式的而且目前已经在很多嵌入式产品中使用了它它占用资源非常的 低在嵌入式设备中可能只需要几百K的内存就够了。 H2 是一个用Java开发的嵌入式数据库它本身只是一个类库可以直接嵌入到应用项目中 上面的数据库管理系统中国内用的最多的还是MySQL银行金融业用oracle比较多MySQL的生态很完整经过时间的考量已经暴露出很多的bug并且有相关活跃的社区修改这些bug数据库也比较稳定。
3.MySQL架构 SQL分类 MySQL存储引擎
1. 除MySQL客户端外MySQL服务器整体的架构分为三层。 第一层是连接管理也就是connection pool连接池这一层会进行连接处理connection handle身份认证authentication安全策略security这一层也是和MySQL客户端打交道最近的一层对MySQL客户端发起的连接做处理。 第二层是SQL语句的接口SQL interfacesql语句的解析器parser以及sql语句的优化optimizer因为MySQL的sql语句的标准格式是全大写的而我们敲的sql语句一般是小写的所以mysqld服务在收到sql语句之后还要对sql语句做一定的优化optimizer除此之外mysqld也有自己的caches缓存策略比如某一个database使用innodb存储引擎时该缓存层会开辟一大块的buffer pool用于数据的缓存该缓存层可以很好的解决由于多次IO导致的程序性能降低问题他可以根据局部性原理在加载数据时同时加载一批相关的数据除了缓存数据之外caches还会进行sql语句查询的缓存以及表信息的缓存等是很重要的一层。 第三层是支持热拔插的存储引擎值得注意的是这里的热拔插并不是指物理上的某些插头进行拔插而是指软件层面上的支持动态添加删除和替换存储引擎的能力可以在数据库运行时动态的添加或删除存储引擎而无需停止数据库服务或重启服务器实现数据的平滑迁移。 例如我们的某个database现在使用的是MyISAM存储引擎可以支持大量的非事务性大文件数据的存储和读取如果我们想让该database的数据保证完整性和一致性则需要使用innodb存储引擎它可以支持ACID(Atomicity、Consistency、Isolation、Durability)的事务以及快速查找时需要的B索引结构。 而这三层站在操作系统的角度来看依旧是应用层应用层下面才会到内核层也就是接近linux内核的文件系统进程管理驱动管理内存管理等等。如果站在网络角度来看的话那也是应用层在下面才会到传输层。 2. MySQL的sql语句大体分为三类分别是DDL(data definition language)数据定义语言DML(data manipulation language)数据操纵语言DCL(data control language)数据控制语言在DML中又单独分了一个DQL(data query language)数据查询语言。 DDL用来维护存储数据的结构例如表table结构数据库database结构常用的sql语句有create drop alter DML用来对结构中的数据内容进行操纵常用的sql语句有insert delete update等其中单独分出来的select语句就属于DQL。 DCL主要负责权限和事务的管理不负责处理库或表结构数据也不负责处理库或表中存储内容的数据主要进行一些连接用户的权限或事物的回滚提交等操作常用的sql语句有grant、revoke、create user、commitrollback等。
3. 存储引擎是MySQL数据存取服务的最重要的组成部分是mysqld进行数据存储为存储的数据建立索引对数据进行CURD(create update read delete)等技术的具体实现方法mysqld数据存储服务的核心实现就是依靠热插拔的存储引擎来实现的。 通过show engines就可以看到我们当前的MySQL服务使用的默认的存储引擎就是我们当时在my.cnf中配置好的InnoDB存储引擎除InnoDB外另一个常用的存储引擎就是MyISAM我们主要学习和使用的是InnoDB存储引擎因为他支持事物transaction行级锁定row-level locking可以支持高并发读写的场景而MyISAM不支持事务和行级锁定只支持表级锁定也就是如果有客户端对表做CUD操作则必须串行化执行无法进行并发式的执行这会降低效率。所以如果有大量读写并发的场景则建议使用InnoDB存储引擎该存储引擎支持行级锁定只锁定表中的某一行数据其他未锁定的行依旧可以进行高并发的CURD操作效率会变得比较高但同时会增加数据库系统的开销和负责度。 4. 下面是各个存储引擎的功能feature包括存储空间限制storage limits事务transaction锁定的粒度大小locking granularity多版本并发控制MVCCB树索引B-tree indexesT树索引T-tree indexes哈希索引hash indexes数据缓存data caches索引缓存index caches数据复制技术replication support外键foreign key support查询缓存query cache support等。
三、MySQL操作库
1.库结构的CURD操作
查看MySQL中所有的数据库show databases; 创建数据库的指令和显示创建数据库时的指令 !40100 default… / 这个不是注释表示当前mysql版本大于4.01版本就执行这句话其实就是设定数据库的编码集为utf8
删除数据库的指令。记住绝对不要以手动直接删除linux文件系统中目录的方式来删除数据库打死都不要这么干因为mysqld负责管理这些特殊的文件程序员不应该干涉mysqld的工作而应该通过mysql客户端发送sql语句的方式来对linux磁盘上的文件进行CURD。 注意删除数据库这样的指令非常的危险不要轻易的删除因为删除数据库后是无法恢复出来的除非你提前做了数据库的备份这条sql语句就和rm -rf ./*这样的指令一样非常的危险请谨慎执行 1. 上面的sql语句说完之后我们需要来谈一个重要的话题就是编码集charset和校验集collate在创建数据库的时候指定这两个字段的值。 编码集指的是数据库存储数据时所使用的编码规则校验集指的是数据库在查找数据进行数据比对的时候所使用的比对规则。database必须保证编码集和校验集所使用的编码格式是统一的。
2. MySQL5.7默认的编码集和校验集分别是utf8和utf8_general_ci在MySQL 8.0及以上版本中utf8_general_ci被替换为utf8mb4_general_ci作为默认的校验规则这样可以支持更为广泛的字符集和表情符号。 数据库支持的字符集和校验集但默认都是utf8 配置文件中数据库的编码默认就是utf8
3. 除了使用默认的编码和校验规则之外我们也可以在创建数据库的时候指定编码和校验规则例如下面创建数据库的时候使用到了gbk的校验规则。 4. 下面对比了utf8_general_ci和utf8_bin校验规则不同时数据库查询时所造成的查询结果的不同其实就是区不区分大小写默认数据库是不区分大小写的也就是utf8_general_ci校验规则 5. 使用数据库use database_name确认当前自己在哪个数据库里面select database()修改数据库的编码或校验规则alter database database_name charsetxxx或collate xxx 注意大部分情况下我们都不会去修改数据库的编码或校验规则只使用my.cnf配置文件中的编码规则即可。
2.库的备份与恢复 数据库的连接情况
1. 数据库的重命名MySQL是不支持的并且这是非常合理的因为数据库的名字是量级很重的一旦数据库的名字发生改变则上层所有使用数据库的代码都需要做出调整代价特别大所以一般在项目前期讨论协商的时候一定要确定好数据库的命名等工作等到项目开始开发的时候就不要更改这些量级比较重的地方了。 除非万不得已必须要对数据库重新命名的时候一般会采用备份与恢复的方式来进行重命名备份的指令mysqldump -P3306 -u root -p 密码 -B 数据库名 数据库备份存储的文件路径备份成功后在指定的路径下面就会存在.sql文件打开.sql文件其实就可以看到其中备份的内容其实就是sql语句和相对应的数据。恢复数据库的指令source 文件路径文件名恢复数据库实际就是将当初创建数据库时所执行的sql语句全部重新执行一遍这样就恢复好数据库了。 2. 如果备份的不是整个数据库的所有内容而是一张表则可以通过指令mysqldump -u root -p 数据库名 表名1 表名2 路径/mytest.sql也可以备份多个数据库 mysqldump -u root -p -B 数据库名1 数据库名2 … 数据库存放路径。 如果备份一个数据库时没有带上-B参数 在恢复数据库时需要先创建空数据库然后使用数据 库再使用source来还原。所以如果你想对数据库重命名的话则可以在备份数据库的时候不带上-B参数备份成功后删除现有的数据库然后在恢复数据库的时候先自己创建一个数据库指定自己想要的数据库名称然后在执行source sql语句进行数据库内容的恢复。
3.查看数据库的连接情况
四、MySQL操作表
1.表结构的CURD操作
1. 创建表的时候()内的内容为列名和列的属性()后面的内容为表的属性字段例如编码集校验集存储引擎等myisam将数据和索引分开存储innodb将数据和索引一块存储。等到后面我们会学到MyISAM的主键索引和非主键索引都是非聚簇索引而InnoDB的主键索引是聚簇索引非主键索引是非聚簇索引。后面在学习到聚簇和非聚簇时我们会详谈。 2. 查看表就是下面这三条sql语句分号;和\G都可以作为sql语句的结束只不过显示的方式不同前者是以行为分隔符进行显示后者是以二维形式的表结构来进行显示。 3. 修改表名alter table old_table_name rename to new_table_nameto可以省略直接rename 向表中新增一列alter table table_name add 列名 列属性 after 表中的某一列名如果不带after的话则新增的一列默认是在表的尾部。 修改表中某一列的属性alter table table_name modify 列名 新属性 删除表中的某一列alter table table_name drop 列名 修改表中某一列的名字alter table table_name change 新列名 新属性 4.删除表drop table 表名 值得注意的是删除表删除库修改库名修改表名这样的操作都很危险如果在项目开发一半的时候产品经理告知要修改表名或者库名那就很有可能在上层出现逻辑问题或编译问题这对程序员是一种莫大的伤害所以在前期讨论项目构建的时候一定要确定好库结构表结构库名表名等到项目开始开发的时候这些就不要改动了。
2.对库结构和表结构操作的SQL语句的小总结DDL语句
1. 总结一下如果我们要对表结构做修改使用的关键字是alter改表名字是rename增加列是add修改列属性是modify修改列名是change删除某一列是drop。 对库或表结构做查看的时候一般会用到show和show create两个sql语句对表结构信息查看还多了一个desc语句。 删除库或表统一使用的是drop语句
2. 我们上面所学的都是DDL语句即数据定义语言不是DML更不是DCL语句在学习完数据类型之后我们紧接着学习的就是DML语句对表中存储数据的增删查改语句。
五、MySQL数据类型
1.数值类型
1.1 整数类型
1. 下面是MySQL中的整数类型常用的数据类型是int在MySQL里面定义列的时候是列名在前列属性在后如果数据类型后面什么都没有带则默认是有符号的只有显示带了unsigned则才是有符号的。 2. 在C/C语言中如果我们将一个整数初始化一个char类型的变量则默认进行隐式类型转换发生截断然后进行存储但在MySQL里面并不会这样例如下面我创建了tinyint类型的num列当我插入的数据是合法的时候则可以顺利的插入到表中而如果插入的数据不合法时MySQL会直接拦截我们不让我们做对应数据的插入反过来如果有数据插入到MySQL中则已经插入的数据一定都是合法的数据。 所以在MySQL中数据类型本身也是一种约束约束使用者进行合法数据的插入通过这样的约束就可以保证数据库里面数据的可预期性和完整性。 3. 实际在建表给某一列字段分配具体数据类型的时候一定要结合具体使用场景来分配如果你分配的不够合理则很可能会出现空间浪费的情况比如某一列只需要1字节即可完成数据的存储但你却用了bigint属性那存储一条数据就会浪费7字节的空间而数据库存储的数据又是海量的数据最后浪费的空间则会非常巨大平白无故给系统带来很大的消耗。 所以在实际分配数据类型时一定要合理的结合具体使用场景来分配。
4. 还有一个常用的数值类型就是bit我们可以用一个比特位来充当某些列字段的类型bit位的个数最多是64位如果在列名后面加bit不带括号指定位数的话则默认是1bit从右边插入数据的过程可以看到当只有一个比特位的时候插入的数据只能是0或1其他数据均无法正常插入被MySQL拦截。 在显示表中内容的时候我们可能看不到类型为bit的列字段的值其实主要是因为当存储的数据大小小于1字节时utf8编码和ascll编码是相同的而0和1在ascll编码中属于不可见字符所以在显示表t3内容的时候我们看不到online的字段值。如果想要显示则可以通过函数hex()来进行显示该函数可以将online字段改为16进制显示这样就可以看到online的值了。 1.2 小数类型
1. float double decimal的使用方式均相同float[(m, d)] [unsigned] : M指定显示长度d指定小数位数占用空间4个字节decimal(m, d) [unsigned] : 定点数m指定长度d表示小数点的位数括号中都是两位前者表示整体的小数长度后者是小数点后面的位数。 2. 当类型为float(4,2)的时候可以看到下面的数值越界测试MySQL是允许小数部分进行四舍五入的进行插入的但必须要满足四舍五入之后总体的长度位数不能超过4下面插入的数据有正有负允许插入的数据范围为-99.99到99.99如果你插入的小数位数大于2则先进行四舍五入在四舍五入之后的数值如果还在数据范围内则允许插入。 当数值类型加上unsigned修饰的时候负数不允许插入可插入的数值范围减半。 2. float的精度大约是7位如果插入的数据大于7位的话则实际保存在数据库中的数据位数可能会不准。 3. 如果你想让存储的数据的精度更高的话则可以使用decimal数值类型可以看到当相同数值向不同类型的列字段f1和d1进行插入的时候decimal存储的精度显然更高一些。不同的MySQL版本decimal的精度可能会有差异 2.字符串类型
2.1 char和varchar
1. 当创建表的第二个列字段为char(2)的时候可以看到两个汉字和两个字母都可以插入到表中但三个字母却无法插入如果这里的字符和C语言中的字符概念相同的话那就是2个字节的大小那就不应该插入两个汉字因为两个汉字在utf8编码下共占用6字节应该会溢出的呀 所以MySQL中的字符和C/C语言中的字符概念是不同的MySQL中的字符指的是一个字节序列这个字节序列可以由多个字节构成例如一个字符一个ascll字符或一个四字节的象形文字表情符号等等都算是一个字节序列。 char最大允许255个字符长度进行插入如果你想要更大的数值类型则可以使用blob(binary large object)和textblob适合存储二进制数据例如图像声音视频等多媒体数据text适合存储文本数据例如文章日志等数据。 文本数据在存储时一般会先按照对应的编码格式例如utf8或ascll等进行文本数据的编码然后将编码后的数据按照对应编码的二进制表示形式存储到磁盘上在取数据时按照对应的编码格式进行解码即可得到原始的文本数据。 2. varchar是可变长度字符串支持的最大字节数为65535同时varchar还需要1-3个字节来记录数据的大小所以有效字节数为65532utf8中一个普通字符最大所占字节数为3字节所以varchar的参数最大值是21844。
3. 21844个字符长度指的是表中一行的长度如果表中一行里面还有其他的字段则创建varchar时的参数还要比21844再小一些。值得注意的是如果你使用某一数据类型频繁的达到临界存储条件的话则应该更换更大的数据类型进行存储。 4. char和varchar的使用要结合具体场景来看比如存储某些长度不变的数据时则可以直接使用char如果要存储长度变化的数据时则可以使用varchar但同时要保证varchar能够存储的下长度变化最大时的数据。 2.2 日期和时间类型
1. 常用的日期时间类型分别为datedatetimetimestamp前两个为固定值一旦插入到表后除非你用update语句进行修改否则不会发生变化而timestamp是时间戳只不过这里的时间戳的表示形式是年月日时分秒而不是一长串表示秒的数字当时间戳的数值插入到表后该时间戳会随着表中任意数值内容的改动而发生改动换句话说只要表中内容被修改则时间戳会自动更新为当前最新的时间。 timestamp字段由自己的默认值所以在插入数据的时候我们可以不用管这一列的值后面我们学到约束的时候就知道Default字段的含义了。 当我对表中第一行信息更新的时候timestamp自动更新为当前的时间。
2. 时间戳一般可以用来实现评论比如某个用户发表了自己的评论当他对自己评论做修改的时候该评论对应的timestamp会自动更新。 比如下面的例子中我将评论”C好找工作吗“修改为“学好的话肯定是好找工作的”则对应的timestamp类型的time字段的值会自动更新为当前最新时间。 3. datetime时间日期类型一般用于记录某个固定的时间日期比如员工的入职时间办理身份证的时间这些时间是不能随表的内容更改而更改的。 而date日期类型一般用于记录某个不需要时间只需要日期的固定数值例如你女朋友的生日只需要记录日期即可不需要记录几分几时几秒。
2.3 枚举和集合类型
1. enum是枚举类型可以在枚举的选项中单选出一个作为数值插入到表中set是集合类型可以在集合的选项中多选或单选出某些数值插入到表中。 enum在插入数值时除插入枚举所给的选项外也可以插入每个选项值对应的数字从1开始依次向后为每个选项对应的数字下标。 set在插入数值时除插入集合所给的选项外也可以插入每个选项值对应的数字每个选项值通过唯一的比特位来标识也就是位图用一个比特位来标识当前选项是否被选中。 2. 可以看到在插入数据时如果你插入的gender字段不在枚举选项里面则MySQL会直接拦截你对数据的插入这再一次说明了类型本身就是约束同时也可以看到枚举选项也可以用数字下标来代替例如12分别代表枚举选项中的男和女下标一般从1开始这里与C或C语言有所不同。 插入set集合的选项时如果要插入多个则需要用逗号分隔开来注意不能含有空格必须只有逗号来作为多个选项之间的分隔同时所有选项外面用单引号括住或双引号括住无论是单引号还是双引号MySQL都是支持的。
3. 值得注意的是在插入set选项时是可以用0来作为数字插入的只不过插入的是空串在MySQL这里空串和NULL是由区别的NULL指的是该列字段彻底为空没有该列字段如果赵六的hobby是NULL的话则说明赵六没有hobby这一字段值如果赵六的hobby是的话则说明赵六有hobby这一字段值只不过hobby为空这两个是不同的概念。 在插入set选项时强烈不建议插入数字因为插入数字的可读性太差。
4. 枚举和集合的查询枚举在进行查询的时候直接在where子句中添加筛选条件即可完成查询不过where子句中的筛选条件是一种绝对的匹配适用于枚举的严格匹配查询因为枚举只能单选比如gender只能有男或女则匹配的时候直接绝对匹配即可。 如果在集合中进行查询时则就不符合条件了例如我想找出爱好中包括打篮球的人如果使用where子句进行筛选的话则查询出来的人的爱好就只有打篮球所以仅仅使用where是不行的。还需要配合一个函数叫做find_in_set(sub,str_list) 如果 sub 在 str_list 中则返回下标如果不在返回0str_list 是用逗号分隔的字符串。 则使用select * from votes where find_in_set(‘打篮球’, hobby)即可查询到hobby里面包含打篮球的人find_in_set会遍历表中所有人的hobby只要包含打篮球字段则返回大于等于1的值则where条件判断为真select此时会显示出来如果不包含打篮球字段则返回0where条件判断为假select就不会显示出来了这也就是为什么枚举的下标是从1开始的了因为MySQL中某些函数想要用0来作为查询结果是否存在的判断值。 find_in_set的第二个参数为以逗号分隔的字符串也可以是set集合或enum枚举他俩也都是以逗号分隔的多个字符串。 六、MySQL表的约束
1.not null约束 default约束
1. 约束的唯一目的其实就是为了保证数据库中数据的有效性可预期性和完整性一旦插入的数据不符合表约束则MySQL直接拦截数据的插入倒逼程序员向数据库中插入有效的数据。让数据库中的数据都是符合约束的。
1. not null一般约束的是当我们在插入对应的列字段时该列字段的数值不能为null值必须是有效值同时当我们创建一个表添加列字段的时候如果什么属性都不带则会有一个默认属性defaultdefault的值为null这点我们可以通过show create table xxx \G SQL语句看到。 同时null是指什么都没有并且不参与任何计算。
2. other列什么属性都没有带则sql默认会给他带上一个default属性该属性字段值为null所以在插入数据的时候values的右边可以忽略掉other列忽略掉这一列则实际插入数据时该列字段使用默认值null来进行插入。 我们也可以选择不忽略掉other列如果不忽略的话则使用我们自己插入的数据不使用默认值other列也没有not null约束所以在插入数据的时候我们也可以显示的插入null值。 通过下面的报错也可以看到default和not null分别控制的是什么当我们忽略掉class_name或class_room列字段时MySQL报错的是doesn’t have a default value告诉我们该列字段没有默认值所以你是不可以忽略这列字段的。 而当values左面的括号进行全列插入的时候插入class_name或class_room列字段值为null时此时MySQL报错的才是cannot be null不允许为空值因为有not null非空约束。 3. default默认值约束如果用户在插入数据时想要忽略这一列字段的插入则可以使用default例如一个程序员相亲网站大部分用户肯定都是男性所以我们可以默认用户的gender为男。 default的使用方式有两种一种是忽略该字段插入默认使用default的值一种是不忽略该字段插入手动的插入自己想要的值。 4. 当not null和default属性叠加到一块时其实就是如果你忽略该列则直接使用default的值如果你不忽略该列则插入的值不能为null值。就这么简单。 5. 有一些MySQL的默认行为还需要给大家说一下当创建一个表的时如果什么属性都不带则默认会有一个defaultnull的属性一旦添加任意一个属性则MySQL给该列自带的default属性就会消失。 not null和default分别约束的是插入数据的值不能为null和忽略该字段的插入时直接使用default值。
2.comment约束 zerofill
1. comment约束并没有什么实际的含义在建表的时候可以在列字段后面加上一些内容上的描述此时就可以使用comment约束来实现。 不过这些字段值在desc查看表结构的时候并不会显示出来只有在show create table xxx显示创建表结构时的sql语句时候才会显示出来所以comment的值不是给数据库看的而是给数据库管理员(DBA)或维护数据库的程序员看的。 2. zerofill是关于显示方面的约束并不会更改数据实际在磁盘上的存储结果仅仅只是更改显示的结果而已默认的int unsigned数值类型在desc查看时int后面会有个括号里面的数字是10其实10代表的是数字字符的个数如果你在创建表的时候不显式指定括号里面的数字的话则MySQL默认会认为int unsigned是10位此时如果给列字段增加zerofill则显示出来的结果会变成10位的数字高位用0来进行填充。 3. 如果显示的数字位数小于int括号里面的数字的话则空余位全部用zero填充如果显示的数字位数大于int括号里面的数字的话该怎么显示就怎么显示此时zerofill约束不起作用。 当你不指定int后面的括号时如果是有符号int则括号内的数字默认是11位如果是无符号int则括号内的数字默认是10位。 因为int是4字节所以无符号int默认的最大数字字符个数就是10个如果是有符号则还要多个符号字符所以默认就是11位。 3.primary key约束 auto_increment约束
1. primary key主键用来唯一的约束该列字段里面的数据这些数据不能重复同时也不能为空并且主键约束的字段都是整数类型不会是浮点数、字符串、日期时间等类型一张表里面最多只能由一个主键。 例如在建表test_key的时候一旦给id加上了primary key约束则表还会自动的增加not null属性这点可以通过show create table test_key\G看出来sql优化后的主键标准格式为在表括号内的最后一行加上primary key(‘id’)。 如果在表中第二次插入id为1的数据则MySQL会直接拦截并报错duplicate entry ‘1’ for key ‘PRIMARY’指的是条目1对于主键重复不允许我们插入数据这就是primary key的约束。 2. 除了在建表的时候添加主键我们也可以在建表之后添加主键或删除主键使用sql语句alterdrop primary key或alteradd primary key(xxx)。 3. 我们除了给一个字段添加主键外也可以给多个字段同时添加上主键这样的主键我们称之为复合主键一张表确实只能有一个主键但这一个主键可以添加到多列上 例如id是学生的学号course是课程的课号我们不想让一个学生重复的选择一门课程多次但我们可以允许一个学生选择多门课程或不同的学生选择同一门课程在这样的场景下就适合用复合主键在表中添加primary key(id, course)复合主键 4. 自增长auto_increment可以和主键或唯一键搭配使用与主键相同的是一张表中只能有一个自增长auto_increment自增长的字段必须是整数。 当自增长与主键搭配使用时我们称主键为自增主键。 建表的时候可以在圆括号外面指定自增长的初始值如果没指明也没有关系默认从1开始在插入数据的时候我们可以忽略有自增长字段的列该列会自动每次从当前字段中已有的最大值加上1然后分配给新插入的数据。show create table时也可以看到下一个插入数据时对应的auto_increment的值。 实际上像QQ号这样的注册模式使用的就是自增长保证QQ号具有唯一性不允许出现重复的QQ号
5. 与主键相同的是一张表也只能有一个自增长如果一张表出现多个自增长则建表也会不成功。 除此之外还有一个函数last_insert_id()该函数用于获取上次插入的自增长的值如果上次是批量化插入的则获取插入的第一条数据所对应的自增长的值。 4.unique key约束 foreign key references约束
1. 一张表中往往有多个字段需要唯一性标识一张表只能有一个主键但一张表可以有多个唯一键所以唯一键就可以很好的约束表中多个需要唯一性标识的字段唯一键和主键比较相似从功能上来讲唯一键允许为空null主键不允许为空null不过空字段是不做唯一性比较的判断表中唯一键是否重复时NULL肯定是不作比较的这非常合理。 例如student表中的唯一键约束的id可以是多个NULL因为我们知道NULL不参与任何比较和计算所以可以出现id为多个NULL的情况。
2. 举一个例子一张表中假设有两列信息一个是员工的身份证号码一个是员工的工号这两个信息都需要唯一性标识我们便可以将身份证号码设置为主键约束员工工号设置为唯一键约束一般而言我们都会将主键设置成为和当前业务无关的字段这样的话当业务发生变化的时候主键可以不用做过多的调整。 3. 一张表中主键只能有一个唯一键可以有多个所以例如学生id电话号码QQ号码等都可以进行唯一键约束如果你不想让唯一键中出现NULL值则可以在唯一键约束的基础上再多添加一个not null非空约束。 设置为not null约束的唯一键从功能上来讲已经和主键没有区别了都是保证字段的唯一性但从数量上来讲一个表中主键只能有一个唯一键可以有多个。从业务上来讲主键一般设置为和当前业务无关的字段唯一键一般设置为和当前业务挂钩的字段。 3. 外键约束是innodb存储引擎的一个重要特性外键一般用于进行表和表之间的关联用于约束具有关联性的表比如下面的学生表和班级表学生是隶属于班级的比较合理的做法就是创建外键约束外键为学生表中的class_id引用自班级表中的主键列id。 4. 下面是外键约束的探究过程。 如果stu和class表之间没有外键约束的话则向stu表中插入数据时有可能误操作将一个学生插入到两个班级或者将学生插入到不存在的班级以及删除班级但班级此时还有学生如果不添加约束则学生又到了不存在的班级中了这些都是不合理的所以显而易见班级表和学生表之间一定需要某种约束关系而这个约束实际就是外键约束 5. 当建立外键约束后外键为stu表中的class_id引用自class表中的id如果此时将学生插入到不存在的班级或者删除某个班级一个学生插入到两个班级等等不合逻辑的操作都会被MySQL拦截掉保证表与表之间正确的关联关系。 其实这里的外键个人觉得和复合主键有那么一点点相似必须保证外键和引用之间的唯一性但外键的约束显然是要比复合主键更严格的比如删除引用引用中还存在着外键是不被允许的一个外键列中的字段只能配一个引用列中的字段必须是一对一的就像离散数学里面的双射关系一样严格的一对一。 5.综合案例
1. 有一个商店的数据记录客户及购物情况由以下三个表组成 商品goods(商品编号goods_id商品名goods_name, 单价unitprice, 商品类别category, 供应商provider) 客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex身份证card_id) 购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums) 要求 每个表的主外键。客户的姓名不能为空值。邮箱不能重复。客户的性别(男女)
2. 订单中的商品编号和客户编号需要外键约束引用到goods和customer各自的goods_id和customer_id中。
