模拟百度搜索词进入网站,海安建设银行网站,vip 支付wordpress,网上商城网站建设公司作者 | cxuan来源 | Java建设者SQL 基础使用MySQL 是一种关系型数据库#xff0c;说到关系#xff0c;那么就离不开表与表之间的关系#xff0c;而最能体现这种关系的其实就是我们接下来需要介绍的主角 SQL#xff0c;SQL 的全称是 Structure Query Language #xff0c;… 作者 | cxuan来源 | Java建设者SQL 基础使用MySQL 是一种关系型数据库说到关系那么就离不开表与表之间的关系而最能体现这种关系的其实就是我们接下来需要介绍的主角 SQLSQL 的全称是 Structure Query Language 结构化的查询语言它是一种针对表关联关系所设计的一门语言也就是说学好 MySQLSQL 是基础和重中之重。SQL 不只是 MySQL 中特有的一门语言大多数关系型数据库都支持这门语言。下面我们就来一起学习一下这门非常重要的语言。查询语言分类在了解 SQL 之前我们需要知道下面这几个概念数据定义语言简称DDL (Data Definition Language)用来定义数据库对象:数据库、表、列等数据操作语言简称DML (Data Manipulation Language)用来对数据库中表的记录进行更新。关键字insert、update、delete等数据控制语言简称DCL(Data Control Language)用来定义数据库访问权限和安全级别创建用户等。关键字grant等数据查询语言简称DQL(Data Query Language)用来查询数据库中表的记录关键字select from where等DDL 语句创建数据库下面就开始我们的 SQL 语句学习之旅首先你需要启动 MySQL 服务我这里是 mac 电脑所以我直接可以启动然后我们使用命令行的方式连接数据库打开 iterm输入下面MacBook:~ mr.l$ mysql -uroot -p就可以连接到数据库了在上面命令中mysql 代表客户端命令- u 表示后面需要连接的用户-p 表示需要输入此用户的密码。在你输入用户名和密码后如果成功登陆会显示一个欢迎界面如上图 和 mysql 提示符。欢迎界面主要描述了这些东西每一行的结束符这里用 ; 或者 \g 来表示每一行的结束「Your MySQL connection id is 4」这个记录了 MySQL 服务到目前为止的连接数每个新链接都会自动增加 1 上面显示的连接次数是 4 说明我们只连接了四次然后下面是 MySQL 的版本我们使用的是 5.7通过 help 或者 \h 命令来显示帮助内容通过 \c 命令来清除命令行 buffer。然后需要做的事情是什么我们最终想要学习 SQL 语句SQL 语句肯定是要查询数据通过数据来体现出来表的关联关系所以我们需要数据那么数据存在哪里呢数据存储的位置被称为 表(table)表存储的位置被称为 数据库(database)所以我们需要先建数据库后面再建表然后插入数据再进行查询。所以我们首先要做的就是创建数据库创建数据库可以直接使用指令CREATE DATABASE dbname;进行创建比如我们创建数据库 cxuandbcreate database cxuandb;注意最后的 ; 结束语法一定不要丢掉否则 MySQL 会认为你的命令没有输出完敲 enter 后会直接换行输出创建完成后会提示 「Query OK, 1 row affected」这段语句什么意思呢Query OK 表示的就是查询完成为什么会显示这个因为所有的 DDL 和 DML 操作执行完成后都会提示这个 也可以理解为操作成功。后面跟着的 **1 row affected ** 表示的是影响的行数()内显示的是你执行这条命令所耗费的时间也就是 0.03 秒。上图我们成功创建了一个 cxuandb 的数据库此时我们还想创建一个数据库我们再执行相同的指令结果提示提示我们不能再创建数据库了数据库已经存在。这时候我就有疑问了我怎么知道都有哪些数据库呢别我再想创建一个数据库又告诉我已经存在这时候可以使用 show databases 命令来查看你的 MySQL 已有的数据库show databases;执行完成后的结果如下因为数据库我之前已经使用过这里就需要解释一下除了刚刚新创建成功的 cxuandb 外informationn_schema 、performannce_schema 和 sys 都是系统自带的数据库是安装 MySQL 默认创建的数据库。它们各自表示informationn_schema主要存储一些数据库对象信息比如用户表信息、权限信息、分区信息等performannce_schemaMySQL 5.5 之后新增加的数据库主要用于收集数据库服务器性能参数。sys: MySQL 5.7 提供的数据库sys 数据库里面包含了一系列的存储过程、自定义函数以及视图来帮助我们快速的了解系统的元数据信息。其他所有的数据库都是作者自己创建的可以忽略他们。在创建完数据库之后可以用如下命令选择要操作的数据库use cxuandb
这样就成功切换为了 cxuandb 数据库我们可以在此数据库下进行建表、查看基本信息等操作。
比如想要看康康我们新建的数据库里面有没有其他表show tables;果然我们新建的数据库下面没有任何表但是现在我们还不进行建表操作我们还是先来认识一下数据库层面的命令也就是其他 DDL 指令删除数据库如果一个数据库我们不想要了那么该怎么办呢直接删掉数据库不就好了吗删表语句是drop database dbname;比如 cxuandb 我们不想要他了可以通过使用drop database cxuandb;进行删除这里我们就不进行演示了因为 cxuandb 我们后面还会使用。但是这里注意一点你删除数据库成功后会出现 「0 rows affected」这个可以不用理会因为在 MySQL 中drop 语句操作的结果都是 「0 rows affected」。创建表下面我们就可以对表进行操作了我们刚刚 show tables 发现还没有任何表所以我们现在进行建表语句CREATE TABLE 表名称
(
列名称1 数据类型 约束,
列名称2 数据类型 约束,
列名称3 数据类型 约束,
....
)这样就很清楚了吧列名称就是列的名字紧跟着列名后面就是数据类型然后是约束为什么要这么设计举个例子你就清楚了比如 cxuan 刚被生出来就被打印上了标签比如我们创建一个表里面有 5 个字段姓名(name)、性别(sex)、年龄(age)、何时雇佣(hiredate)、薪资待遇(wage)建表语句如下create table job(name varchar(20), sex varchar(2), age int(2), hiredate date, wage decimal(10,2));事实证明这条建表语句还是没问题的建表完成后可以使用 DESC tablename 查看表的基本信息DESC 命令会查看表的定义但是输出的信息还不够全面所以如果想要查看更全的信息还要通过查看表的创建语句的 SQL 来得到show create table job \G;可以看到除了看到表定义之外还看到了表的 engine(存储引擎) 为 InnoDB 存储引擎\G 使得记录能够竖着排列如果不用 \G 的话效果如下删除表表的删除语句有两种一种是 drop 语句SQL 语句如下drop table job一种是 truncate 语句SQL 语句如下truncate table job这两者的区别简单理解就是 drop 语句删除表之后可以通过日志进行回复而 truncate 删除表之后永远恢复不了所以一般不使用 truncate 进行表的删除。修改表对于已经创建好的表尤其是有大量数据的表如果需要对表做结构上的改变可以将表删除然后重新创建表但是这种效率会产生一些额外的工作数据会重新加载进来如果此时有服务正在访问的话也会影响服务读取表中数据所以此时我们需要表的修改语句来对已经创建好的表的定义进行修改。修改表结构一般使用 alter table 语句下面是常用的命令ALTER TABLE tb MODIFY [COLUMN] column_definition [FIRST | AFTER col_name];比如我们想要将 job 表中的 name 由 varchar(20) 改为 varchar(25)可以使用如下语句alter table job modify name varchar(25);也可以对表结构进行修改比如增加一个字段alter table job add home varchar(30);将新添加的表的字段进行删除alter table job drop column home;可以对表中字段的名称进行修改比如把 wage 改为 salaryalter table job change wage salary decimal(10,2);修改字段的排列顺序我们前面介绍过修改语法涉及到一个顺序问题都有一个可选项 first | after column_name这个选项可以用来修改表中字段的位置默认 ADD 是在添加为表中最后一个字段而「CHANGE/MODIFY」 不会改变字段位置。比如alter table job add birthday after hiredate;可以对表名进行修改例如将 job 表改为 workeralter table job rename worker;DML 语句有的地方把 DML 语句增删改和 DQL 语句查询统称为 DML 语句有的地方分开我们目前使用分开称呼的方式插入表创建好之后我们就可以向表里插入数据了插入记录的基本语法如下INSERT INTO tablename (field1,field2) VALUES(value1,value2);例如向中插入以下记录insert into job(name,sex,age,hiredate,birthday,salary) values(cxuan,男,24,2020-04-27,1995-08-22,8000);也可以不用指定要插入的字段直接插入数据即可insert into job values(cxuan02,男,25,2020-06-01,1995-04-23,12000);这里就有一个问题如果插入的顺序不一致的话会怎么样呢对于含可空字段、非空但是含有默认值的字段、自增字段可以不用在 insert 后的字段列表出现values 后面只需要写对应字段名称的 value 即可没有写的字段可以自动的设置为 NULL、默认值或者自增的下一个值这样可以缩短要插入 SQL 语句的长度和复杂性。比如我们设置一下 hiredate、age 可以为 null来试一下insert into job(name,sex,birthday,salary) values(cxuan03,男,1992-08-23,15000);我们看一下实际插入的数据我们可以看到有一行两个字段显示 NULL。在 MySQL 中insert 语句还有一个很好的特性就是一次可以插入多条记录INSERT INTO tablename (field1,field2) VALUES
(value1,value2),
(value1,value2),
(value1,value2),
...;可以看出每条记录之间都用逗号进行分割这个特性可以使得 MySQL 在插入大量记录时节省很多的网络开销大大提高插入效率。更新记录对于表中已经存在的数据可以通过 update 命令对其进行修改语法如下UPDATE tablename SET field1 value1, field2 value2 ;例如将 job 表中的 cxuan03 中 age 的 NULL 改为 26SQL 语句如下update job set age 26 where name cxuan03;SQL 语句中出现了一个 where 条件我们会在后面说到 where 条件这里简单理解一下它的概念就是根据哪条记录进行更新如果不写 where 的话会对整个表进行更新删除记录如果记录不再需要可以使用 delete 命令进行删除DELETE FROM tablename [WHERE CONDITION]例如在 job 中删除名字是 cxuan03 的记录delete from job where name cxuan03;在 MySQL 中删除语句也可以不指定 where 条件直接使用delete from job这种删除方式相当于是清楚表的操作表中所有的记录都会被清除。DQL 语句下面我们一起来认识一下 DQL 语句数据被插入到 MySQL 中就可以使用 SELECT 命令进行查询来得到我们想要的结果。SELECT 查询语句可以说是最复杂的语句了这里我们只介绍一下基本语法一种最简单的方式就是从某个表中查询出所有的字段和数据简单粗暴直接使用 SELECT *SELECT * FROM tablename;例如我们将 job 表中的所有数据查出来select * from job;其中 * 是查询出所有的数据当然你也可以查询出指定的数据项select name,sex,age,hiredate,birthday,salary from job;上面这条 SQL 语句和 select * from job 表是等价的但是这种直接查询指定字段的 SQL 语句效率要高。上面我们介绍了基本的 SQL 查询语句但是实际的使用场景会会比简单查询复杂太多一般都会使用各种 SQL 的函数和查询条件等下面我们就来一起认识一下。去重使用非常广泛的场景之一就是 去重去重可以使用 distinct 关键字来实现为了演示效果我们先向数据库中插入批量数据插入完成后的表结构如下下面我们使用 distinct 来对 age 去重来看一下效果你会发现只有两个不同的值其他和 25 重复的值被过滤掉了所以我们使用 distinct 来进行去重条件查询我们之前的所有例子都是查询全部的记录如果我们只想查询指定的记录呢这里就会用到 where条件查询语句条件查询可以对指定的字段进行查询比如我们想查询所有年龄为 24 的记录如下select * from job where age 24;where 条件语句后面会跟一个判断的运算符 除了 号比较外还可以使用 「、、、、!」 等比较运算符例如select * from job where age 24;就会从 job 表中查询出 age 年龄大于或等于 24 的记录除此之外在 where 条件查询中还可以有多个并列的查询条件比如我们可以查询年龄大于等于 24并且薪资大于 8000 的记录select * from job where age 24 and salary 8000;多个条件之间还可以使用 or、and 等逻辑运算符进行多条件联合查询运算符会在以后章节中详细讲解。排序我们会经常有这样的需求按照某个字段进行排序这就用到了数据库的排序功能使用关键字 order by 来实现语法如下SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC] field2 [DESC|ASC]……fieldn [DESC|ASC]]其中 DESC 和 ASC 就是顺序排序的关键字DESC 会按照字段进行降序排列ASC 会按照字段进行升序排列默认会使用升序排列也就是说你不写 order by 具体的排序的话默认会使用升序排列。order by 后面可以跟多个排序字段并且每个排序字段可以有不同的排序顺序。为了演示功能我们先把表中的 salary 工资列进行修改修改完成后的表记录如下下面我们按照工资进行排序SQL 语句如下select * from job order by salary desc;语句执行完成后的结果如下这是对一个字段进行排序的结果也可以对多个字段进行排序但是需要注意一点根据 order by 后面声明的顺序进行排序如果有三个排序字段 A、B、C 的话如果 A 字段排序字段的值一样则会根据第二个字段进行排序以此类推。如果只有一个排序字段那么这些字段相同的记录将会无序排列。限制对于排序后的字段或者不排序的字段如果只希望显示一部分的话就会使用 LIMIT 关键字来实现比如我们只想取前三条记录select * from job limit 3;或者我们对排序后的字段取前三条记录select * from job order by salary limit 3;上面这种 limit 是从表记录的第 0 条开始取如果从指定记录开始取比如从第二条开始取取三条记录SQL 如下select * from job order by salary desc limit 2,3;limit 一般经常和 order by 语法一起实现分页查询。注意limit 是 MySQL 扩展 SQL92 之后的语法在其他数据库比如 Oracle 上就不通用我犯过一个白痴的行为就是在 Oracle 中使用 limit 查询语句。聚合下面我们来看一下对记录进行汇总的操作这类操作主要有汇总函数比如 sum 求和、count 统计数量、max 最大值、min 最小值等group by关键字表示对分类聚合的字段进行分组比如按照部门统计员工的数量那么 group by 后面就应该跟上部门with 是可选的语法它表示对汇总之后的记录进行再次汇总having 关键字表示对分类后的结果再进行条件的过滤。看起来 where 和 having 意思差不多不过它们用法不一样where 是使用在统计之前对统计前的记录进行过滤having 是用在统计之后是对聚合之后的结果进行过滤。也就是说 where 永远用在 having 之前我们应该先对筛选的记录进行过滤然后再对分组的记录进行过滤。可以对 job 表中员工薪水进行统计选出总共的薪水、最大薪水、最小薪水select sum(salary) from job;select max(salary),min(salary) from job;比如我们要统计 job 表中人员的数量select count(1) from job;统计完成后的结果如下我们可以按照 job 表中的年龄来进行对应的统计select age,count(1) from job group by age;既要统计各年龄段的人数又要统计总人数select age,count(1) from job group by age with rollup;在此基础上进行分组统计数量大于 1 的记录select age,count(1) from job group by age with rollup having count(1) 1;表连接表连接一直是笔者比较痛苦的地方曾经因为一个表连接挂了面试现在来认真撸一遍。表连接一般体现在表之间的关系上。当需要同时显示多个表中的字段时就可以用表连接来实现。为了演示表连接的功能我们为 job 表加一个 type 字段表示工作类型增加一个 job_type 表表示具体的工作种类如下所示下面开始我们的演示查询出 job 表中的 type 和 job_type 表中的 type 匹配的姓名和工作类型select job.name,job_type.name from job,job_type where job.type job_type.type;上面这种连接使用的是内连接除此之外还有外连接。那么它们之间的区别是啥呢内连接选出两张表中互相匹配的记录外连接不仅选出匹配的记录也会选出不匹配的记录外连接分为两种左外连接筛选出包含左表的记录并且右表没有和它匹配的记录右外连接筛选出包含右表的记录甚至左表没有和它匹配的记录为了演示效果我们在 job 表和 job_type 表中分别添加记录添加完成后的两表如下下面我们进行左外连接查询查询出 job 表中的 type 和 job_type 表中的 type 匹配的姓名和工作类型select job.name,job_type.name from job left join job_type on job.type job_type.type;查询出来的结果如下可以看出 cxuan06 也被查询出来了而 cxuan06 他没有具体的工作类型。使用右外连接查询select job.name,job_type.name from job right join job_type on job.type job_type.type;可以看出job 表中并没有 waiter 和 manager 的角色但是也被查询出来了。子查询有一些情况我们需要的查询条件是另一个 SQL 语句的查询结果这种查询方式就是子查询子查询有一些关键字比如 「in、not in、、!、exists、not exists」 等例如我们可以通过子查询查询出每个人的工作类型select job.* from job where type in (select type from job_type);如果子查询数量唯一的话还可以用 来替换 inselect * from job where type (select type from job_type);意思是自查询不唯一我们使用 limit 限制一下返回的记录数select * from job where type (select type from job_type limit 1,1);在某些情况下子查询可以转换为表连接联合查询我们还经常会遇到这样的场景将两个表的数据单独查询出来之后将结果合并到一起进行显示这个时候就需要 UNION 和 UNION ALL 这两个关键字来实现这样的功能UNION 和 UNION ALL 的主要区别是 UNION ALL 是把结果集直接合并在一起而 UNION 是将 UNION ALL 后的结果进行一次 DISTINCT 去除掉重复数据。比如select type from job union all select type from job_type;它的结果如下上述结果是查询 job 表中的 type 字段和 job_type 表中的 type 字段并把它们进行汇总可以看出 UNION ALL 只是把所有的结果都列出来了使用 UNION 的 SQL 语句如下select type from job union select type from job_type;可以看出 UNION 是对 UNION ALL 使用了 distinct 去重处理。DCL 语句DCL 语句主要是管理数据库权限的时候使用这类操作一般是 DBA 使用的开发人员不会使用 DCL 语句。关于帮助文档的使用我们一般使用 MySQL 遇到不会的或者有疑问的东西经常要去查阅网上资料甚至可能需要去查 MySQL 官发文档这样会耗费大量的时间和精力。下面教你一下在 MySQL 命令行就能直接查询资料的语句按照层次查询可以使用 ? contents 来查询所有可供查询的分类如下所示? contents;我们输入? Account Management可以查询具体关于权限管理的命令比如我们想了解一下数据类型? Data Types然后我们想了解一下 VARCHAR 的基本定义可以直接使用? VARCHAR可以看到有关于 VARCHAR 数据类型的详细信息然后在最下面还有 MySQL 的官方文档方便我们快速查阅。快速查阅在实际应用过程中如果要快速查询某个语法时可以使用关键字进行快速查询比如我们使用? show能够快速列出一些命令比如我们想要查阅 database 的信息使用SHOW CREATE DATABASE cxuandb;MySQL 数据类型MySQL 提供很多种数据类型来对不同的常量、变量进行区分MySQL 中的数据类型主要是 「数值类型、日期和时间类型、字符串类型」 选择合适的数据类型进行数据的存储非常重要在实际开发过程中选择合适的数据类型也能够提高 SQL 性能所以有必要认识一下这些数据类型。数值类型MySQL 支持所有标准的 SQL 数据类型这些数据类型包括严格数据类型的严格数值类型这些数据类型有INTEGERSMALLINTDECIMALNUMERIC。近似数值数据类型 并不用严格按照指定的数据类型进行存储这些有FLOATREALDOUBLE PRECISION还有经过扩展之后的数据类型它们是TINYINTMEDIUMINTBIGINTBIT其中 INT 是 INTEGER 的缩写DEC 是 DECIMAL 的缩写。下面是所有数据类型的汇总整数在整数类型中按照取值范围和存储方式的不同分为TINYINT 占用 1 字节SMALLINT占用 2 字节MEDIUMINT占用 3 字节INT、INTEGER占用 4 字节BIGINT占用 8 字节五个数据类型如果超出类型范围的操作会发生错误提示所以选择合适的数据类型非常重要。还记得我们上面的建表语句么我们一般会在 SQL 语句的数据类型后面加上指定长度来表示数据类型许可的范围例如int(7)表示 int 类型的数据最大长度为 7如果填充不满的话会自动填满如果不指定 int 数据类型的长度的话默认是 int(11)。我们创建一张表来演示一下create table test1(aId int, bId int(5));/* 然后我们查看一下表结构 */
desc test1;整数类型一般配合 zerofill 来使用顾名思义就是用 0 进行填充也就是数字位数不够的空间使用 0 进行填充。分别修改 test1 表中的两个字段alter table test1 modify aId int zerofill;alter table test1 modify bId int(5) zerofill;然后插入两条数据执行查询操作如上图所示使用zerofill 可以在数字前面使用 0 来进行填充那么如果宽度超过指定长度后会如何显示我们来试验一下向 aId 和 bId 分别插入超过字符限制的数字会发现 aId 已经超出了指定范围那么我们对 aId 插入一个在其允许范围之内的数据会发现aId 已经插进去了bId 也插进去了为什么 bId 显示的是 int(5) 却能够插入 7 位长度的数值呢所有的整数都有一个可选属性 UNSIGNED(无符号)如果需要在字段里面保存非负数或者是需要较大上限值时可以使用此选项它的取值范围是正常值的下限取 0 上限取原值的 2 倍。如果一个列为 zerofill 会自动为该列添加 UNSIGNED 属性。除此之外整数还有一个类型就是 AUTO_INCREMENT在需要产生唯一标识符或者顺序值时可利用此属性这个属性只用于整数字符。一个表中最多只有一个 AUTO_INCREMENT 属性一般用于自增主键而且 NOT NULL并且是 PRIMARY KEY 和 UNIQUE 的主键必须保证唯一性而且不为空。小数小数说的是啥它其实有两种类型一种是浮点数类型一种是定点数类型浮点数有两种单精度浮点型 - float 型双精度浮点型 - double 型定点数只有一种 decimal。定点数在 MySQL 内部中以字符串的形式存在比浮点数更为准确适合用来表示精度特别高的数据。浮点数和定点数都可以使用 (M,D) 的方式来表示M 表示的就是 「整数位 小数位」 的数字D 表示位于 . 后面的小数。M 也被称为精度 D 被称为标度。下面通过示例来演示一下首先建立一个 test2 表REATE TABLE test2 (aId float(6,2) default NULL, bId double(6,2) default NULL,cId decimal(6,2) default NULL)然后向表中插入几条数据insert into test2 values(1234.12,1234.12,1234.12);这个时候显示的数据就是然后再向表中插入一些约束之外的数据insert into test2 values(1234.123,1234.123,1234.123);发现插入完成后还显示的是 1234.12小数位第三位的值被舍去了。现在我们把 test2 表中的精度全部去掉再次插入alter table test2 modify aId float;alter table test2 modify bId double;alter table test2 modify cId decimal;先查询一下发现 cId 舍去了小数位。然后再次插入 1.23SQL 语句如下insert into test2 values(1.23,1.23,1.23);结果如下这个时候可以验证浮点数如果不写精度和标度会按照实际的精度值进行显示定点数如果不写精度和标度会按照 decimal(10,0) 来进行操作如果数据超过了精度和标题MySQL 会报错位类型对于位类型用于存放字段值BIT(M) 可以用来存放多位二进制数M 的范围是 1 - 64如果不写的话默认为 1 位。下面我们来掩饰一下位类型新建一个 test3 表表中只有一个位类型的字段create table test3(id bit(1));然后随意插入一条数据insert into test3 values(1);发现无法查询出对应结果。然后我们使用 hex() 和 bin() 函数进行查询发现能够查询出对应结果。也就是说当数据插入 test3 时会首先把数据转换成为二进制数如果位数允许则将成功插入如果位数小于实际定义的位数则插入失败。如果我们像表中插入数据 2insert into test3 values(2);那么会报错因为 2 的二进制数表示是 10而表中定义的是 bit(1) 所以无法插入。那么我们将表字段修改一下然后再进行插入发现已经能够插入了日期时间类型MySQL 中的日期与时间类型主要包括「YEAR、TIME、DATE、DATETIME、TIMESTAMP」每个版本可能不同。下表中列出了这几种类型的属性。下面分别来介绍一下YEARYEAR 可以使用三种方式来表示用 4 位的数字或者字符串表示两者效果相同表示范围 1901 - 2155插入超出范围的数据会报错。以 2 位字符串格式表示范围为 ‘00’~‘99’。‘00’~‘69’ 表示 2000~2069‘70’~‘99’ 表示1970~1999。‘0’ 和 ‘00’ 都会被识别为 2000超出范围的数据也会被识别为 2000。以 2 位数字格式表示范围为 1~99。1~69 表示 2001~2069, 70~99 表示 1970~1999。但 0 值会被识别为0000这和 2 位字符串被识别为 2000 有所不同下面我们来演示一下 YEAR 的用法创建一个 test4 表create table test4(id year);然后我们看一下 test4 的表结构默认创建的 year 就是 4 位下面我们向 test4 中插入数据insert into test4 values(2020),(2020);然后进行查询发现表示形式是一样的使用两位字符串来表示delete from test4;insert into test4 values (0),(00),(11),(88),(20),(21);使用两位数字来表示delete from test4;insert into test4 values (0),(00),(11),(88),(20),(21);发现只有前两项不一样。TIMETIME 所表示的范围和我们预想的不一样我们把 test4 改为 TIME 类型下面是 TIME 的示例alter table test4 modify id TIME;insert into test4 values (15:11:23),(20:13),(2 11:11),(3 05),(33);结果如下DATEDATE 表示的类型有很多种下面是 DATE 的几个示例create table test5 (id date);查看一下 test5 表然后插入部分数据insert into test5 values (2020-06-13),(20200613),(20200613);DATE 的表示一般很多种如下所示 DATE 的所有形式YYYY-MM-DDYYYYMMDDYYYYMMDDYY-MM-DDYYMMDDYYMMDDDATETIMEDATETIME 类型包含日期和时间部分可以使用引用字符串或者数字年份可以是 4 位也可以是 2 位。下面是 DATETIME 的示例create table test6 (id datetime);insert into test4 values (2020-06-13 11:11:11),(20200613111111),(20200613111111),(20200613080808);TIMESTAMPTIMESTAMP 类型和 DATETIME 类型的格式相同存储 4 个字节比DATETIME少取值范围比 DATETIME 小。下面来说一下各个时间类型的使用场景一般表示年月日通常用 DATE 类型用来表示时分秒通常用 TIME 表示年月日时分秒 通常用 DATETIME 来表示如果需要插入的是当前时间通常使用 TIMESTAMP 来表示TIMESTAMP 值返回后显示为 YYYY-MM-DD HH:MM:SS 格式的字符串如果只表示年份、则应该使用 YEAR它比 DATE 类型需要更小的空间。每种日期类型都有一个范围如果超出这个范围在默认的 SQLMode下系统会提示错误并进行零值存储。下面来解释一下 SQLMode 是什么MySQL 有一个环境变量是 sql_mode sql_mode 支持了 MySQL 的语法、数据校验我们可以通过下面这种方式来查看当前数据库使用的 sql_modeselect sql_mode;一共有下面这几种模式来源于 https://www.cnblogs.com/Zender/p/8270833.html字符串类型MySQL 提供了很多种字符串类型下面是字符串类型的汇总下面我们对这些数据类型做一个详细的介绍CHAR 和 VARCHAR 类型CHAR 和 VARCHAR 类型很相似导致很多同学都会忽略他们之间的差别首先他俩都是用来保存字符串的数据类型他俩的主要区别在于存储方式不同。CHAR 类型的长度就是你定义多少显示多少。占用 M 字节比如你声明一个 CHAR(20) 的字符串类型那么每个字符串占用 20 字节M 的取值范围是 0 - 255。VARCHAR 是可变长的字符串范围是 0 - 65535在字符串检索的时候CHAR 会去掉尾部的空格而 VARCHAR 会保留这些空格。下面是演示例子create table vctest1 (vc varchar(6),ch char(6));insert into vctest1 values(abc ,abc );select length(vc),length(ch) from vctest1;结果如下可以看到 vc 的字符串类型是 varchar 长度是 5ch 的字符串类型是 char长度是 3。可以得出结论varchar 会保留最后的空格char 会去掉最后的空格。BINARY 和 VARBINARY 类型BINARY 和 VARBINARY 与 CHAR 和 VARCHAR 非常类似不同的是它们包含二进制字符串而不包含非二进制字符串。BINARY 与 VARBINARY 的最大长度和 CHAR 与 VARCHAR 是一样的只不过他们是定义字节长度而 CHAR 和 VARCHAR 对应的是字符长度。BLOB 类型BLOB 是一个二进制大对象可以容纳可变数量的数据。有 4 种 BLOB 类型TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。TEXT 类型有 4 种 TEXT 类型TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型可存储的最大长度不同可根据实际情况选择。ENUM 类型ENUM 我们在 Java 中经常会用到它表示的是枚举类型。它的范围需要在创建表时显示指定对 1 - 255 的枚举需要 1 个字节存储对于 255 - 65535 的枚举需要 2 个字节存储。ENUM 会忽略大小写在存储时都会转换为大写。SET 类型SET 类型和 ENUM 类型有两处不同存储方式SET 对于每 0 - 8 个成员分别占用 1 个字节最大到 64 占用 8 个字节Set 和 ENUM 除了存储之外最主要的区别在于 Set 类型一次可以选取多个成员而 ENUM 则只能选一个。MySQL 运算符MySQL 中有多种运算符下面对 MySQL 运算符进行分类算术运算符比较运算符逻辑运算符位运算符下面那我们对各个运算符进行介绍算术运算符MySQL 支持的算术运算符包括加、减、乘、除和取余这类运算符的使用频率比较高下面是运算符的分类运算符作用加法-减法*乘法/, DIV除法返回商%, MOD除法返回余数下面简单描述了这些运算符的使用方法 用于获得一个或多个值的和- 用于从一个值减去另一个值* 用于两数相乘得到两个或多个值的乘积/ 用一个值除以另一个值得到商% 用于一个值除以另一个值得到余数在除法和取余需要注意一点如果除数是 0 将是非法除数返回结果为 NULL。比较运算符熟悉了运算符下面来聊一聊比较运算符使用 SELECT 语句进行查询时MySQL 允许用户对表达式的两侧的操作数进行比较比较结果为真返回 1 比较结果为假返回 0 比较结果不确定返回 NULL。下面是所有的比较运算符运算符描述等于 或者是 !不等于NULL 安全的等于也就是 NULL-safe小于小于等于大于大于等于BETWEEN在指定范围内IS NULL是否为 NULLIS NOT NULL是否为 NULLIN存在于指定集合LIKE通配符匹配REGEXP 或 RLIKE正则表达式匹配比较运算符可以用来比较数字、字符串或者表达式。数字作为浮点数进行比较字符串以不区分大小写的方式进行比较。 号运算符用于比较运算符两侧的操作数是否相等如果相等则返回 1 如果不相等则返回 0 下面是具体的示例NULL 不能用于比较会直接返回 NULL 号用于表示不等于和 号相反示例如下 NULL-safe 的等于运算符与 号最大的区别在于可以比较 NULL 值 号运算符当左侧操作数小于右侧操作数时返回值为 1 否则其返回值为 0。和上面同理只不过是满足 的时候返回 1 否则 返回 0。这里我有个疑问为什么select a b; /* 返回 1 *//*而*/select a b; /* 返回 0 呢*/关于 和 是同理BETWEEN 运算符的使用格式是 「a BETWEEN min AND max」 当 a 大于等于 min 并且小于等于 max 时返回 1否则返回 0 。操作数类型不同的时候会转换成相同的数据类型再进行处理。比如IS NULL 和 IS NOT NULL 表示的是是否为 NULLISNULL 为 true 返回 1否则返回 0 IS NOT NULL 同理IN 这个比较操作符判断某个值是否在一个集合中使用方式是 xxx in (value1,value2,value3)LIKE 运算符的格式是 xxx LIKE %123%比如如下当 like 后面跟的是 123% 的时候 xxx 如果是 123 则返回 1如果是 123xxx 也返回 1如果是 12 或者 1 就返回 0 。123 是一个整体。REGEX 运算符的格式是 s REGEXP str 匹配时返回值为 1否则返回 0 。后面会详细介绍 regexp 的用法。逻辑运算符逻辑运算符指的就是布尔运算符布尔运算符指返回真和假。MySQL 支持四种逻辑运算符运算符作用NOT 或 逻辑非AND 或者是 逻辑与OR 或者是 ||逻辑或XOR逻辑异或下面分别来介绍一下NOT 或者是 ! 表示的是逻辑非当操作数为 0假 则返回值为 1否则值为 0。但是有一点除外那就是 NOT NULL 的返回值为 NULLAND 和 表示的是逻辑与的逻辑当所有操作数为非零值并且不为 NULL 时结果为 1但凡是有一个 0 则返回 0操作数中有一个 null 则返回 nullOR 和 || 表示的是逻辑或当两个操作数均为非 NULL 值时如有任意一个操作数为非零值则结果为 1否则结果为 0。XOR 表示逻辑异或当任意一个操作数为 NULL 时返回值为 NULL。对于非 NULL 的操作数如果两个的逻辑真假值相异则返回结果 1否则返回 0。位运算符一听说位运算就知道是和二进制有关的运算符了位运算就是将给定的操作数转换为二进制后对各个操作数的每一位都进行指定的逻辑运算得到的二进制结果转换为十进制后就说是位运算的结果下面是所有的位运算。运算符作用位与|位或^位异或位取反位右移位左移下面分别来演示一下这些例子位与 指的就是按位与把 双方转换为二进制再进行 操作按位与是一个数值减小的操作位或 指的就是按位或把 | 双方转换为二进制再进行 | 操作位或是一个数值增大的操作位异或 指的就是对操作数的二进制位做异或操作位取反 指的就是对操作数的二进制位做 NOT 操作这里的操作数只能是一位下面看一个经典的取反例子对 1 做位取反具体如下所示为什么会有这种现象因为在 MySQL 中常量数字默认会以 8 个字节来显示8 个字节就是 64 位常量 1 的二进制表示 63 个 0加 1 个 1 位取反后就是 63 个 1 加一个 0 转换为二进制后就是 18446744073709551614我们可以使用 「select bin()」 查看一下位右移 是对左操作数向右移动指定位数例如 50 3就是对 50 取其二进制然后向右移三位左边补上 0 转换结果如下位左移 与位右移相反是对左操作数向左移动指定位数例如 20 2MySQL 常用函数下面我们来了解一下 MySQL 函数MySQL 函数也是我们日常开发过程中经常使用的选用合适的函数能够提高我们的开发效率下面我们就来一起认识一下这些函数字符串函数字符串函数是最常用的一种函数了MySQL 也是支持很多种字符串函数下面是 MySQL 支持的字符串函数表函数功能LOWER将字符串所有字符变为小写UPPER将字符串所有字符变为大写CONCAT进行字符串拼接LEFT返回字符串最左边的字符RIGHT返回字符串最右边的字符INSERT字符串替换LTRIM去掉字符串左边的空格RTRIM去掉字符串右边的空格REPEAT返回重复的结果TRIM去掉字符串行尾和行头的空格SUBSTRING返回指定的字符串LPAD用字符串对最左边进行填充RPAD用字符串对最右边进行填充STRCMP比较字符串 s1 和 s2REPLACE进行字符串替换下面通过具体的示例演示一下每个函数的用法LOWER(str) 和 UPPER(str) 函数用于转换大小写CONCAT(s1,s2 ... sn) 把传入的参数拼接成一个字符串上面把 c xu an 拼接成为了一个字符串另外需要注意一点任何和 NULL 进行字符串拼接的结果都是 NULL。LEFT(str,x) 和 RIGHT(str,x) 函数分别返回字符串最左边的 x 个字符和最右边的 x 个字符。如果第二个参数是 NULL那么将不会返回任何字符串INSERT(str,x,y,instr) 将字符串 str 从指定 x 的位置开始 取 y 个长度的字串替换为 instr。LTRIM(str) 和 RTRIM(str) 分别表示去掉字符串 str 左侧和右侧的空格REPEAT(str,x) 函数返回 str 重复 x 次的结果TRIM(str) 函数用于去掉目标字符串的空格SUBSTRING(str,x,y) 函数返回从字符串 str 中第 x 位置起 y 个字符长度的字符串LPAD(str,n,pad) 和 RPAD(str,n,pad) 函数用字符串 pad 对 str 左边和右边进行填充直到长度为 n 个字符长度STRCMP(s1,s2) 用于比较字符串 s1 和 s2 的 ASCII 值大小。如果 s1 s2则返回 -1如果 s1 s2 返回 0 如果 s1 s2 返回 1。REPLACE(str,a,b) : 用字符串 b 替换字符串 str 种所有出现的字符串 a数值函数MySQL 支持数值函数这些函数能够处理很多数值运算。下面我们一起来学习一下 MySQL 中的数值函数下面是所有的数值函数函数功能ABS返回绝对值CEIL返回大于某个值的最大整数值MOD返回模ROUND四舍五入FLOOR返回小于某个值的最大整数值TRUNCATE返回数字截断小数的结果RAND返回 0 - 1 的随机值下面我们还是以实践为主来聊一聊这些用法ABS(x) 函数返回 x 的绝对值CEIL(x) 函数返回大于 x 的整数MOD(x,y)对 x 和 y 进行取模操作ROUND(x,y) 返回 x 四舍五入后保留 y 位小数的值如果是整数那么 y 位就是 0 如果不指定 y 那么 y 默认也是 0 。FLOOR(x) : 返回小于 x 的最大整数用法与 CEIL 相反TRUNCATE(x,y): 返回数字 x 截断为 y 位小数的结果 TRUNCATE 知识截断并不是四舍五入。RAND() 返回 0 到 1 的随机值日期和时间函数日期和时间函数也是 MySQL 中非常重要的一部分下面我们就来一起认识一下这些函数函数功能NOW返回当前的日期和时间WEEK返回一年中的第几周YEAR返回日期的年份HOUR返回小时值MINUTE返回分钟值MONTHNAME返回月份名CURDATE返回当前日期CURTIME返回当前时间UNIX_TIMESTAMP返回日期 UNIX 时间戳DATE_FORMAT返回按照字符串格式化的日期FROM_UNIXTIME返回 UNIX 时间戳的日期值DATE_ADD返回日期时间 上一个时间间隔DATEDIFF返回起始时间和结束时间之间的天数下面结合示例来讲解一下每个函数的使用NOW(): 返回当前的日期和时间WEEK(DATE) 和 YEAR(DATE) 前者返回的是一年中的第几周后者返回的是给定日期的哪一年HOUR(time) 和 MINUTE(time) : 返回给定时间的小时后者返回给定时间的分钟MONTHNAME(date) 函数返回 date 的英文月份CURDATE() 函数返回当前日期只包含年月日CURTIME() 函数返回当前时间只包含时分秒UNIX_TIMESTAMP(date) : 返回 UNIX 的时间戳FROM_UNIXTIME(date) : 返回 UNIXTIME 时间戳的日期值和 UNIX_TIMESTAMP 相反DATE_FORMAT(date,fmt) 函数按照字符串 fmt 对 date 进行格式化格式化后按照指定日期格式显示具体的日期格式可以参考这篇文章 https://blog.csdn.net/weixin_38703170/article/details/82177837我们演示一下将当前日期显示为「年月日」的这种形式使用的日期格式是 「%M %D %Y」。DATE_ADD(date, interval, expr type) 函数返回与所给日期 date 相差 interval 时间段的日期interval 表示间隔类型的关键字expr 是表达式这个表达式对应后面的类型type 是间隔类型MySQL 提供了 13 种时间间隔类型表达式类型描述格式YEAR年YYMONTH月MMDAY日DDHOUR小时hhMINUTE分mmSECOND秒ssYEAR_MONTH年和月YY-MMDAY_HOUR日和小时DD hhDAY_MINUTE日和分钟DD hh : mmDAY_SECOND日和秒DD hh mm ssHOUR_MINUTE小时和分hh:mmHOUR_SECOND小时和秒hh:ssMINUTE_SECOND分钟和秒mm:ssDATE_DIFF(date1, date2) 用来计算两个日期之间相差的天数查看离 2021 - 01 - 01 还有多少天流程函数流程函数也是很常用的一类函数用户可以使用这类函数在 SQL 中实现条件选择。这样做能够提高查询效率。下表列出了这些流程函数函数功能IF(value,t f)如果 value 是真返回 t否则返回 fIFNULL(value1,value2)如果 value1 不为 NULL返回 value1否则返回 value2。CASE WHEN[value1] THEN[result1] ...ELSE[default] END如果 value1 是真返回 result1否则返回 defaultCASE[expr] WHEN[value1] THEN [result1]... ELSE[default] END如果 expr 等于 value1 返回 result1 否则返回 default其他函数除了我们介绍过的字符串函数、日期和时间函数、流程函数还有一些函数并不属于上面三类函数它们是函数功能VERSION返回当前数据库的版本DATABASE返回当前数据库名USER返回当前登陆用户名PASSWORD返回字符串的加密版本MD5返回 MD5 值INET_ATON(IP)返回 IP 地址的数字表示INET_NTOA(num)返回数字代表的 IP 地址下面来看一下具体的使用VERSION: 返回当前数据库版本DATABASE: 返回当前的数据库名USER : 返回当前登录用户名PASSWORD(str) : 返回字符串的加密版本例如MD5(str) 函数返回字符串 str 的 MD5 值INET_ATON(IP): 返回 IP 的网络字节序列INET_NTOA(num)函数返回网络字节序列代表的 IP 地址与 INET_ATON 相对推荐阅读
不知道路由器工作原理没关系来这看看看不懂你捶我 | 原力计划秋名山老司机从上车到翻车的悲痛经历带你深刻了解什么是 Spark on Hive| 原力计划出道50年乘风破浪的编程语言们能二次翻红吗Service Mesh 如何重定义云原生计算阿里服务网格技术大揭秘国士无双卖掉美国房子回国创办姚班他只为培养一流的程序员万字长文带你入门 GCN真香朕在看了
