Mysql数据库——02内置约束与函数

最新推荐文章于 2024-06-18 09:18:55 发布
最新推荐文章于 2024-06-18 09:18:55 发布
阅读量229 收藏
点赞数
分类专栏： mysql 文章标签： 数据库 sql mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_37544121/article/details/126173004
版权

1.表约束

在数据库中为了保证数据库插入时按照指定规则插入，引入表约束，常见表约束有

1.主键约束 primary key [ˈpraɪməri]（不允许为空，不允许重复）如果是整数类型一般定义主键自动增长 auto_increment [ˈɔːtəʊ] [ˈɪŋkrəmənt]，一般通过可视化连接工具添加。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10),
  `password` char(10),
  PRIMARY KEY (`id`)
)

2.唯一约束，也叫唯一索引，用于设置该字段不可重复。

ALTER TABLE `user`.`user` ADD UNIQUE INDEX index_name (`name`);

3.非空约束 not null 用于指定该字段不能为空一般在创表时指定或可视化连接工具添加。也可以和 DEFAULT 配合使用，表示不输入时该字段的默认值。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) NOT NULL,
  `password` char(10) NOT NULL DEFAULT 123456,
  PRIMARY KEY (`id`)
)

4.外键约束，在创建外键约束时一定要保证两个表的数据库引擎相同且不能为 MYISAM，一般通过可视化连接工具添加。

ALTER TABLE `client` ADD CONSTRAINT `emp_id_fk` FOREIGN KEY (`emp_id`) REFERENCES `emp`(`id`);

一般创表命令如下

CREATE TABLE mytb(
    id INT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    pwd VARCHAR(40) NOT NULL,
    birth DATE,
    PRIMARY KEY ( id )
 )ENGINE=INNODB DEFAULT CHARSET=utf8;
  • 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为 NULL ，就会报错。
  • AUTO_INCREMENT 定义列为自增的属性，一般用于主键，每次插入数据后数值会自动加 1。
  • PRIMARY KEY 关键字用于定义列为主键。 可以使用多列来定义主键称为复合主键，列间以逗号分隔。
  • ENGINE 设置存储引擎，使用 show engines 命令可以查看 MySQL 支持的数据库引擎，CHARSET 设置编码。

注意：utf8 与 utf8mb4 的区别在于 utf8mb4 兼容部分图标字体如：☺◐❀♬▯ 。如果引擎和字符编码在安装时设置好的则在创表时可以不指定。

2.数据类型 

MySQL 中为了满足各种业务需要，提供很多数据类型，常用类型如下：

  • INT                        整数                                         4 字节 
  • BIGINT                  长整数                                      8 字节
  • FLOAT                   单精度小数                              单精度浮点数值
  • DOUBLE                双精度小数                              双精度浮点数值
  • DECIMAL               小数值                                     对DECIMAL(P,D) 
  • DATE                     日期值                                      YYYY-MM-DD 
  • TIME                      时间值                                      HH:MM:SS
  • DATETIME             混合日期和时间值                     YYYY-MM-DD HH:MM:SS
  • CHAR                    定长字符串                               0-255字节
  • VARCHAR             变长字符串                               0-65535 字节
  • TEXT                     长文本数据                               0-65535 字节
  • BLOB                    二进制形式的数据                     0-65535 字节

注意：

1.在 DECIMAL(P,D) 的语法中： P 是表示有效数字数的精度。 P 范围为 1〜65。 D 是表示小数点后的位数。 D 的范围是 0~30。MySQL 要求 D 小于或等于(<=)P。如：DECIMAL(6,2)中最多可以存储 6 位数字，小数位数为 2 位；因此，列的范围是从-9999.99 到 9999.99。

2.char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。char 是一种定长字符串。它一旦声明后无论存储的值是否达到该长度，都占有相应的字节长度。varchar 是一种变长字符串，它的长度由存储的内容决定，为存满的时以实际存储内容为主。

3.BLOB 是一个二进制大对象，可以容纳可变数量的二进制数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

4.TEXT 同样也有 4 种类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。它们的区别也是可存储的最大长度不同。具体使用哪种类型可根据实际情况选择。

3.视图

视图其实就是一张虚拟表，一张从别的表中导出的一张不完整或经过运算的虚拟表。

视图有什么用，当我们经常查询一个表中的某几个字段的时候，我们可以把这几个字段单独导出一个视图，以后查询的时候直接可以 select *，在数据库中只存放了视图的定义，没有存放视图的数据，视图是依赖原来表中的数据的，原表中的数据一旦发生变化，视图查询出的结果也会跟着变化。下面进行创建一个视图。 

案例：给语文老师创建一个视图

CREATE VIEW vi_chinese_user 
AS SELECT id,`name`,`chinese` FROM `student`;

视图在查询时和普通表查询语句完全相同。删除视图使用命令如下：

DROP VIEW `vi_chinese_user`;

4.触发器

触发器是由事件来触发某个操作，这些事件包括 insert、delete、update 语句。当数据库执行这些事件时，就会激活触发器执行相应的操作。 

我们需要一个表，这里用 user 表，用来对该表进行增删改操作，另外需要一个 user_log 表，当对 user 表操作的时候，会激活我们的触发器，对 user_log 表做出相应的操作。

CREATE TRIGGER auto_save_time BEFORE INSERT 
ON `user` FOR EACH ROW 
INSERT INTO user_log(log_time) VALUES(NOW());

这条语句的意思是，创建一个触发器名字是 auto_save_time，在 user 表的任意行执行 insert 语句之前会激活触发器，并执行 insert into user_log(log_time) values(now()); 语句。下面我们对 user 表进行一个 insert 操作，可以看出，激活了触发器，并执行了那条语句。now() 表示当前时间括号前不能有空格。

查看触发器使用命令 show triggers；删除触发器使用命令 drop trigger 触发器名；触发器有 6 个 分别是插入前后，删除前后，修改前后六个，与前面案例中的 before 对应的还有 after。

例：创建一张表 student(id,name) 和 student_log (id,log_time,method)，创建 3 个触发器，完成 student 表的所有修改操作的记录。

CREATE TABLE `student_log` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `log_time` DATETIME NOT NULL,
  `method` VARCHAR (50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = INNODB CHARSET = utf8mb4 ;

CREATE TABLE `student` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR (50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = INNODB CHARSET = utf8mb4 ;


CREATE TRIGGER student_insert BEFORE INSERT 
ON `student` FOR EACH ROW 
INSERT INTO `student_log`(`log_time`,`method`) VALUES(NOW(),'添加');

CREATE TRIGGER student_update BEFORE UPDATE 
ON `student` FOR EACH ROW 
INSERT INTO `student_log`(`log_time`,`method`) VALUES(NOW(),'修改');

CREATE TRIGGER student_delete BEFORE DELETE 
ON `student` FOR EACH ROW 
INSERT INTO `student_log`(`log_time`,`method`) VALUES(NOW(),'删除');

5.存储过程

存储过程 (Stored Procedure) 是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集 , 存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它，存储过程是数据库中的一个重要对象；存储过程中可以包含逻辑控制语句和 数据操纵语句 , 它可以接受参数，输出参数，返回单个或多个结果集以及返回值；

create table p_user(
	id int primary key auto_increment,
	name varchar(10),
	sex char(2)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into p_user(name,sex) values('A',"男");
insert into p_user(name,sex) values('B',"女");
insert into p_user(name,sex) values('C',"男");

创建存储过程(查询得到男性或女性的数量，如果传入的是 0 就获取女性的数量，否则获取男性的数量)  

CREATE PROCEDURE ges_user_count(IN sex_id INT, OUT user_count INT)
BEGIN
    IF sex_id = 0 THEN
        SELECT COUNT(*) FROM p_user WHERE p_user.sex = '女' INTO user_count;
    ELSE
        SELECT COUNT(*) FROM p_user WHERE p_user.sex = '男' INTO user_count;
    END IF;
END

调用存储过程

SET @user_count = 0;
CALL ges_user_count(1, @user_count);
SELECT @user_count;

查询得到男性或女性的数量, 如果传入的是 0 就女性否则是男性。

6.数据处理函数

在查询数据时有事需要对查出的字段进行处理，如小写转大写，切割等。这时就需要使用数据库的字符串处理函数，如下是 mysql 常用字符串处理函数。

CHARSET(字段)	                            返回字段的字符集
CONCAT(string2  [,... ])	            连接字串
INSTR(string ,substring )	            返回substring在string中出现的位置,没有返回0
UCASE(string2 )	                            转换成大写
LCASE(string2 )	                            转换成小写
LEFT(string2 ,length )	                    从string2中的左边起取length个字符
LENGTH(string )	                            返回string字节数量
REPLACE(str ,search_str ,replace_str )	    在str中用replace_str替换search_str
STRCMP(string1 ,string2 )	            逐字符比较两字串大小,返回1、0和-1
SUBSTRING(str , position  [,length ])	    从str的position开始,取length个字符
LTRIM(string2 ) RTRIM (string2 )            trim 去除前端空格或后端空格

eg:

SELECT CHARSET(`name`) FROM `emp`;
SELECT CONCAT(`name`,`pwd`) FROM `emp`;
SELECT INSTR(`pwd`,'2') FROM `emp`;
SELECT UCASE(`name`) FROM `emp`;
SELECT LCASE(`name`) FROM `emp`;
SELECT LEFT(`pwd`,2) FROM `emp`;
SELECT LENGTH(`pwd`) FROM `emp`;
SELECT STRCMP('b' ,'b' ) FROM `emp`;
SELECT SUBSTRING(`pwd`,2,2) FROM `emp`;
SELECT LTRIM(`name`) FROM `emp`;
SELECT RTRIM(LTRIM(`name`)) FROM `emp`;

数字处理函数

以下是 mysql 常用的数字处理函数

ABS(number2 )	                            绝对值
CEILING (number2 )	                    向上取整
FLOOR (number2 )	                    向下取整
FORMAT (number,小数位数)	            保留小数位数
HEX (DecimalNumber )	                    转十六进制
LEAST (number , number2  [,..])	            求最小值
MOD (numerator ,denominator )	            求余
RAND([seed])	                            随机数

eg:

SELECT `name`,ABS(`english`) FROM `student`;
SELECT `name`,CEILING(100.00000001) FROM `student`;
SELECT `name`,FLOOR (100.00000001) FROM `student`;
SELECT `name`,`english`,FORMAT (`english`,2) FROM `student`;
SELECT `name`,`english`,HEX  (`english`) FROM `student`;
SELECT `name`,RAND() FROM `student`;
SELECT `name`,CEILING(RAND()*10) FROM `student`;

日期时间处理函数

获取当前的日期（包含具体的时分秒）

SELECT NOW(),LOCALTIME(),SYSDATE()

获取当前的日期（不包含具体的时分秒）

SELECT CURRENT_DATE(),CURDATE()

日期与时间戳之间的转化

SELECT UNIX_TIMESTAMP(NOW()),FROM_UNIXTIME(1629882598)

获取当前的时间（时分秒）

SELECT CURRENT_TIME(),CURTIME()

获取月份

MONTH(date)返回月份的数值； MONTHNAME(date)返回月份的英文名称

SELECT MONTH(NOW()),MONTHNAME(NOW())

获取天的函数

DAYOFMONTH(date)返回日期属于当前月的第几天； DAYOFYEAR(date)返回日期属于当前年的第几天

SELECT DAYOFMONTH(NOW()),DAYOFYEAR(NOW())

获取星期的函数

DAYNAME(date)返回星期几的英文名字；DAYOFWEEK(date)返回星期几的数值，返回值的范围是1-7，1表示星期日，以此类推； WEEKDAY(date)返回星期几的数值，返回值的范围是0-6，0表示星期一，以此类推

SELECT DAYNAME(NOW()),DAYOFWEEK(NOW()),WEEKDAY(NOW())

两个日期间的间隔天数

TO_DAYS(date)日期date与默认日期（000年1月1日）之间间隔的天数；FROM_DAYS(number)默认日期经过 number 天后的日期；DATEDIFF(date1，date2)获取指定两个日期间的相隔天数，date1-date2

SELECT TO_DAYS(NOW()),FROM_DAYS(738166),DATEDIFF(NOW(),'2020-01-01')

日期的加减法

DATE_ADD(date,INTERVAL expr type)，在 date 的基础上添加上指定的时间间隔后的日期。

DATE_SUB(date,INTERVAL expr type)在 date 的基础上减去上指定的时间间隔后的日期。

type 值常用的有：year、month、day、hour、minute、second、microsecond（毫秒）、week、quarter

SELECT DATE_ADD(NOW(),INTERVAL 1 DAY),DATE_SUB(NOW(),INTERVAL 1 DAY)

7.索引

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。  拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。 

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。

组合索引，即一个索引包含多个列。 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。  

注意：如果过多的使用索引将会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。 建立索引会占用磁盘空间的索引文件。

索引在 mysql 中分为普通索引，唯一索引和全文索引

1.普通索引

CREATE INDEX indexName ON `users` (username(length))

如果是 CHAR，VARCHAR 类型，length 可以小于字段实际长度；如果是 BLOB 和 TEXT 类型，必须指定 length。

2.唯一索引：它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。

CREATE UNIQUE INDEX indexName ON mytable(username(length))

3.全文索引：针对大文本 TEXT 的内容查找

CREATE FULLTEXT INDEX indexName ON `users` (username)

由于全文索引需要对该字段进行分词，对中文的支持不是很好，所以全文索引在实际开发中不建议使用。在一些大文本的内容管理系统中一般使用 elasticsearch [ɪˈlæstɪk] [sɜːtʃ] 完成全文检索。

使用全文索引的格式：  MATCH (columnName) AGAINST ('string')
eg:
SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')

删除索引：

ALTER TABLE `users` DROP INDEX `indexName`

8.连接查询

两表连接分为内连接和外连接：

内连接（inner join [ˈɪnə(r)] [dʒɔɪn]）：返回连接表中符合连接条件及查询条件的数据行。 

外连接 outer [ˈaʊtə(r)]： 分为

  • 左外连接（left outer join）可以简写 left join
  • 右外连接（right outer join）可以简写为 right join

与内连接不同的是，外连接不仅返回连接表中符合连接条件及查询条件的数据行，也返回左表（左外连接时）或右表（右外连接时）中仅符合查询条件但不符合连接条件的数据行。

外连接查询（重点） 

from 表1 连接类型 表2 [on (连接条件)] [where (查询条件)]

左外连接查询

使用 left outer join 关键字，在 on 子句中设定连接条件不仅包含符合连接条件的数据行，还包含左表全部数据，右表没连上的不显示。左连接时左表优先全部显示。

内连接查询 （重点）

SELECT e.id '员工编号',e.`name` '员工名字',c.`name` '客户名',c.`tel` '客户电话' FROM `emp` e ,`client` c WHERE e.`id`=c.`emp_id`;
SELECT e.id '员工编号',e.`name` '员工名字',c.`name` '客户名',c.`tel` '客户电话' FROM `emp` e INNER JOIN `client` c ON e.`id`=c.`emp_id`;
SELECT e.id '员工编号',e.`name` '员工名字',c.`name` '客户名',c.`tel` '客户电话' FROM `emp` e LEFT OUTER JOIN `client` c ON e.`id`=c.`emp_id`;

9.子查询与联合查询

子查询

子查询也叫嵌套查询，是指在 select 子句或者 where 子句中又嵌入 select 查询语句。

SELECT * FROM `emp` WHERE id IN(SELECT emp_id FROM `client` WHERE id IN(2,4,5))

联合查询 

合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有重复数据行的查询结果。联合查询使用 union 关键字 

SELECT * FROM `client` WHERE emp_id = 2 UNION SELECT * FROM `client` WHERE emp_id = 1;
SELECT `id`,`name` FROM `client` WHERE emp_id = 2 UNION SELECT `id`,`name` FROM `emp`;

注意：两条查询语句返回的结构和字段的数量必须相同，否则无法合并。 

10.报表函数

报表函数也叫聚合函数，一般用于统计汇总数据库表信息。通常和分组函数配合使用，如果不使用分组函数则认为该表的全部数据为一个组。常用报表函数如下：

①计数函数－COUNT，如果参数是字段名，则统计该字段不为空的记录数。

Select count(*)|count(列名) from tablename [WHERE where_definition]

eg:

SELECT COUNT(*) FROM `client`

 ②求和函数－SUM 

Select sum(列名){,sum(列名)…} from tablename [WHERE where_definition]

 eg:

SELECT SUM(`chinese`),SUM(`english`),SUM(`math`) FROM `student` WHERE `name` LIKE '%李%'

③平均函数－AVG 

Select avg(列名){,avg(列名)…} from tablename [WHERE where_definition]

eg:

SELECT AVG(`chinese`),AVG(`english`),AVG(`math`) FROM `student` WHERE `name` LIKE '%李%'

④边角函数－MAX/MIN 

Select max(列名)　from tablename [WHERE where_definition]

eg:

SELECT MAX(`english`) FROM `client`;
SELECT MIN(`english`) FROM `client`;

11.分组查询

分组查询是按一个或多个字段进行分组，字段值相同的为一组进行分组统计，其语法格式为：

[select …] from … [where…] [ group by … [having… ]] [ order by … ][limit n]

其中 group by 子句指定按照哪些字段分组。having 子句设定分组查询条件。

例：

数据准备：

CREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `dept` varchar(10) DEFAULT NULL,
  `salary` double DEFAULT NULL,
  `edlevel` int(11) DEFAULT NULL,
  `hiredate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (1,'张三','开发部',2000,3,'2020-02-10 15:19:59');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (2,'李四','开发部',2500,3,'2019-07-16 15:20:01');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (3,'王五','设计部',2600,5,'2019-09-25 15:20:03');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (4,'王六','设计部',2300,4,'2020-06-05 15:20:05');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (5,'马七','设计部',2100,4,'2019-11-11 15:20:07');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (6,'赵八','销售部',3000,5,'2020-06-05 15:20:19');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (7,'钱九','销售部',3000,7,'2020-06-05 15:20:21');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (8,'孙十','销售部',3000,7,'2020-06-05 15:20:26');

查询：

1.列出每个部门最高薪水的结果
SELECT `dept`, MAX(`salary`) AS '最高薪资' FROM `staff` GROUP BY `dept`;

2.查询每个部门的总的薪水数
SELECT `dept`, SUM( `salary` ) AS '总薪水' FROM `staff` GROUP BY `dept`;

3.查询公司 2020 年入职的各个部门每个级别里的最高薪水
SELECT `dept`, `edlevel`, MAX( `salary` ) AS maximum FROM staff WHERE `hiredate` > '2020-01-01' GROUP BY `dept`, `edlevel`;

4.查询雇员数超过2个的部门的最高和最低薪水并按照部门升序查询
SELECT `dept`, MAX( `salary` ) AS maximum, MIN( `salary` ) AS minimum FROM `staff` GROUP BY `dept` HAVING COUNT(*) > 2 ORDER BY DEPT

5.查询雇员平均工资大于或等于 2300 的部门的最高和最低薪水并按照最高薪水排序
SELECT `dept`, MAX( `salary` ) AS maximum, MIN( `salary` ) AS minimum FROM `staff` GROUP BY `dept` HAVING AVG( `salary` ) >= 2300 ORDER BY MAX(`salary`)

分组连接函数：在 MySQL 中将分组相同的行数据合并起来使用 group_concat 函数，该函数语法如下：

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

案例：以 id 分组，把 name 字段的值打印在一行，逗号分隔(默认)

select id,group_concat(name) from aa group by id;

案例：以id分组，把name字段的值打印在一行，分号分隔

select id,group_concat(name separator ';') from aa group by id;

案例：以id分组，把去冗余的name字段的值打印在一行，并用逗号分隔

select id,group_concat(distinct name) from aa group by id;

案例：以id分组，把name字段的值打印在一行，逗号分隔，以name排倒序

select id,group_concat(name order by name desc) from aa group by id;

12.索引最左匹配原则

创建测试表并建立了复合（联合）索引（a，b，c）

create table test(
    a int ,
    b int,
    c int,
    d int,
    key index_abc(a,b,c)
)engine=InnoDB default charset=utf8;

插入 10000 条数据

DROP PROCEDURE IF EXISTS proc_initData;
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=10000 DO
    INSERT INTO test(a,b,c,d) VALUES(i,i,i,i);
    SET i = i+1;
END WHILE;
END $
CALL proc_initData();

explain 指令查看下面SQL

explain select * from test where a<10 ;
explain select * from test where a<10 and b <10;
explain select * from test where a<10 and b <10 and c<10;

将 a，b出现顺序换一下，a，b，c 出现顺序换一下

explain select * from test where b<10 and a <10;
explain select * from test where b<10 and a <10 and c<10;

查了下资料发现：mysql 查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。所以，当然是我们能尽量的利用到索引时的查询顺序效率最高咯，所以 mysql 查询优化器会最终以这种顺序进行查询执行。

mysql> explain select * from test where b<10 and a <10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 5       | NULL |    9 |    33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

为什么 b<10 and c <10，没有用到索引？而 a<10 and c <10用到了？

mysql> explain select * from test where b<10 and c <10;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10005 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

当 b+ 树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+ 数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+ 树会优先比较 name 来确定下一步的所搜方向，如果 name 相同再依次比较 age 和 sex，最后得到检索的数据；但当(20,F)这样的没有 name 的数据来的时候，b+ 树就不知道下一步该查哪个节点，因为建立搜索树的时候 name 就是第一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+ 树可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是 F 的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

Cindy.li
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
【笔记】【MySQL基础】
Copywang
02-17 4943
北京动力节点，郭鑫 视频地址：https://www.bilibili.com/video/av14547249/#page=158 笔记： 演示环境的建表SQL脚本内容： DROP TABLE IF EXISTS EMP; DROP TABLE IF EXISTS DEPT; DROP TABLE IF EXISTS SALGRADE; CREATE TABLE DEPT (DEP...
关于的函数知识
younger123rtyui9的博客
04-26 165
1、字符串函数 常用函数： 函数 功能 CONCAT(s1, s2, ..., sn) 字符串拼接，将s1, s2, ..., sn拼接成一个字符串 LOWER(str) 将字符串全部转为小写 UPPER(str) 将字符串全部转为大写 LPAD(str, n, pad) 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度 RPAD(str, n, pad) 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度 T.
数据库中的约束
weixin_45956310的博客
07-04 444
1.约束分为列级约束和表级约束 -- 列级约束:列级约束是行定义的一部分，只能应用于一列。 -- 表级约束:表级约束是独立于列的定义，可以应用在一个表中的多列。 -- 主键(primary key) 唯一(unique key) 外键(foreign key) -- 举例：unique（项目名称，项目负责人） 2.外键约束的要求： -- 1.父表和子表必须使用相同的存储引擎，而且禁止使用临时表。 -- 查看表的存储引擎，show create table classes; -- 2.数据
MySQL数据库的约束和函数
weixin_33859231的博客
09-27 179
MySQL数据库的五大约束 NOT NULL ：非空约束，指定某列不能为空； #建表时指定 create table t2(id int(10) not null,name varchar(5)); #已存在的表增加约束 alter table t2 modify name varchar(5) not null; #可以设置默认值，即为非空 alter table t2 constraint ...
【学习笔记分享】MySQL数据库基础篇（中）函数、约束看这里就够了~
Demo的博客
12-09 134
【学习笔记分享】MySQL数据库基础篇（中）MySQL函数、约束看这里就够了~
mysql学习笔记2022-08-23
qq_45737830的博客
08-23 231
Mysql学习笔记
MySQL数据库8——数据库中函数的应用详解
09-09
MySQL数据库中的函数是数据库操作的重要组成部分，它们提供了一种便捷的方式来处理数据，包括日期和时间的处理、字符串操作以及数学计算。以下是对这些函数的详细解释： ### 日期函数 1. `current_date()`：返回...
mysql数据库转瀚高数据库所需要的函数列表
03-15
1、mysql数据库转国产化瀚高数据库； 2、有些函数在瀚高数据库中不存在，需要添加自定义函数。
数据库——mysql如何获取当前时间.docx
01-03
MySQL 是一个功能强大且广泛使用的关系型数据库管理系统，获取当前时间是 MySQL 中一个非常重要的功能。本文将详细介绍 MySQL 中获取当前时间的方法，包括获取当前日期+时间、当前日期和当前时间的函数。 1. 获取...
浅析数据库之效能——MYSQL数据库作用与技巧.pdf
10-10
“浅析数据库之效能——MYSQL数据库作用与技巧” 概述：本文档旨在对MySQL数据库的效能和技巧进行浅析，涵盖了 MySQL 数据库的基本概念、数据类型、数据表、数据操作、系统管理命令等方面的知识点。 一、MySQL...
mysql 常用命令
Gx422954766的博客
06-15 176
注意：一定要加分号结尾创建数据库    creat databases 数据库名;扩展    创建编码为utf-8的数据库，如果存在则不创建CREATE DATABASE IF NOT EXISTS RUNOOB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;显示所有数据库    show database;删除数据库    drop databases...
设置表的字符编码和存储引擎
longdui123的博客
03-02 739
设置表的字符编码 有时候MySQL中的表需要存储中文，需要设置表的字符编码为utf8，否则默认的字符编码有可能不能正确处理中文，那么在MySQL中，如何设置表的字符编码呢？如下： 方法一：创建表的时候指定编码形式和 // 改变整张表的编码 mysql> CREATE TABLE stu_test ( -> id int(11) NOT NULL, -> name var...
powerdesigner12.5 设置表字符集和存储引擎
雅冰石的专栏
07-30 3547
powerdesigner12.5在做建模的时候发现没有找到哪儿设置表的字符集和存储引擎。于是研究了一番。 在菜单上方选择 Database => Edit Current DBMS   然后选择Mysql5.0 => Script => Objects => Table =>  Options     在右边的Value框内最后部分追加： ENGINE = %s : lis
MySQL学习笔记4：建表与修改表操作
Imliao的博客
04-22 240
所谓建表就是一个声明列的过程。 基本语法 create table 表名( 列名1 列类型1 列1参数, 列名2 列类型2 列2参数, … … 列名n 列类型n 列n参数 )engine myisam/… charset gbk/utf8/… EXAMPLE: 建立一个电子商城的商品电子表。 create table ebusiness ( id int unsigned auto_increm...
数据库学习（5）------表的约束，表之间的关系，数据库设计
zdw2388317527的博客
07-19 1135
1.什么是JDBC(Java DataBase Connectivity)
mysql len函数的用法_SQL LEN() 函数
热门推荐
weixin_42237487的博客
01-27 1万+
SQL 中的 LEN() 函数返回文本字段中值的长度SELECT LEN( column_name ) FROM table_name;LEN() 返回的是数据库服务器编码下的字符串长度，如果数据库服务器的编码是 UTF-8，那么 '中国' 将返回 6不过 MySQL 中没有 LEN() 函数，取而代之的是 LENGTH() 函数，但用法都一样SELECT LENGTH( column_name ...
Mysql利用触发器TRIGGER的检查约束
KANEKI的博客
05-05 1695
有一个student表如下所示： create table student ( ID varchar(10) primary key, NAME varchar(20), GENDER varchar(2), check(GENDER in ('F', 'M')), ); SID代表学生的学号，NAME表示学生的名字，GENDER是指学生性别 check(GENDER in ('F',...
mysql8.0 MySQL函数
S_zhangmin的博客
02-02 2312
part1. MySQL函数介绍 函数表示对输入参数值返回一个具有特定关系的值，MySQL提供了大量丰富的函数，在进行数据库管理以及数据的查询和操作时将会经常用到各种函数。各类函数从功能方面主要分为数学函数，字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等其他函数。 part2. 数学函数 绝对值函数、三角函数（正弦函数、余弦函数、正切函数、余切函数等）、对数函数、随机数函数等。 1. 绝对值函数ABS(x)和返回圆周率的函数PI() ABS(X)返回X的绝对值。 PI(.
MySQL数据库笔记（二）
最新发布
2301_78671299的博客
06-18 1096
操作数据对象。接受参数返回一个结果。只对一行进行变换。每行返回一个结果。可以嵌套。参数可以是一列或一个值。聚合函数作用于一组数据，并对一组数据返回一个值。子查询指一个查询语句嵌套在另一个查询语句内部的查询。多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。
java连接mysql数据库实现登录注册_java利用jdbc连接Mysql数据库——实现登录注册功能...
05-27
1.下载并安装MySQL数据库，安装完成后，创建一个名为“test”的数据库。 2.下载并安装JDBC驱动程序，将下载的JDBC驱动程序的jar包添加到项目的classpath中。 3.编写Java代码，实例化JDBC驱动程序，连接MySQL数据库...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值