精华内容
下载资源
问答
  • 2017-02-12 19:45:00

    Mysql 索引

     

    1. 索引的概念

      索引是创建在表上的,对数据库表中的一列或多列的值进行排序的一种结构。

      索引有2种存储类型:B型树索引 和 哈希索引。InnoDB和MyISAM存储引擎支持B型树索引,MEMORY存储引擎支持哈希索引。

      优点:提高检索数据的速度。对于有依赖关系的子表和父表之间的联合,可提高查询速度;使用分组和排序子句进行数据查询,可显著节省查询时间。

      缺点:创建和维护需要耗费时间,耗费的时间随着数据量的增加而增加;索引需要占用物理空间;增加、修改和删除索引时,需要动态的维护索引,会造成数据维护速度降低。

     

    2.索引的分类

      1)普通索引:

        不加任何限制条件的索引。

      2)唯一性索引:

        UNIQUE参数设置唯一性索引。

      3)全文索引:

        使用FULLTEXT参数设置为全文索引。(全文索引只能创建在CHAR、VARCHAR或TEXT类型字段上)

      4)单列索引:

        为表的单个字段创建索引。

      5)多列索引:

        为表的多个字段创建一个索引。

      6)空间索引:

        使用SPATIAL参数设置。

     

    3. 索引的设计原则

      1)选择唯一性索引;

      2)为经常需要排序、分组和联合操作的字段建立索引;

      3)为常作为查询条件的字段建立索引;

      4)限制索引的数目;

      5)尽量使用数据量少的索引;

      6)尽量使用前缀索引;

      7)删除不再使用或很少使用的索引。

     

    4. 创建索引

      1)创建表的时候创建索引

    CREATE  TABLE  表名 ( 属性名   数据类型   约束条件,
                          属性名   数据类型   约束条件,
                          ...
                          UNIQUE | FULLTEXT | SPATIAL    INDEX | KEY
                                   别名  ( 属性名1 (长度)   ASC | DESC)
                        );

        INDEX和KEY表示指定字段为索引,两者作用相同选其一即可。

        ASC/DESC表示升序排列和降序排列。

      2)为已经存在的表创建索引

    CREATE   UNIQUE | FULLTEXT | SPATIAL   INDEX   索引名
                ON   表名   ( 属性名 (长度)   ASC | DESC );

      3)用ALTER TABLE语句创建索引

    ALTER  TABLE   表名   ADD   UNIQUE | FULLTEXT | SPATIAL   INDEX          
                              索引名   ( 属性名 (长度)   ASC | DESC );

     

    5. 删除索引

    DROP  INDEX   索引名   ON   表名;

     

    转载于:https://www.cnblogs.com/jx-yangbo/p/6391623.html

    更多相关内容
  • 很多时候我们看到一些表字符串类型的字段定义为varchar(255),开始以为varchar只能定义为255这个长度值,其实不然。官方文档所说,varchar有效的最大长度取决于行的容量,...

    很多时候我们看到一些表字符串类型的字段定义为varchar(255),开始以为varchar只能定义为255这个长度值,其实不然。

    官方文档所说,varchar有效的最大长度取决于行的容量,以及用的字符集,整行的所有列的定义长度不能超过65535字节(bytes),text、blob等大字段类型除外,

    d75dbf7f4d21c0c5a20323a35153d1ad.png

    P.S. https://dev.mysql.com/doc/refman/5.7/en/char.html

    小白学习MySQL - 变通创建索引的案例一则》提到了,

    InnoDB,如果需要建索引,就不能超过767bytes,utf8编码,255*3=765 bytes,是能建索引情况下的最大值,utf8mb4编码,默认字符长度则应该是767除以4向下取整,就是191。如果设置了innodb_large_prefix,最大长度是3072字节,utf8编码,1024*3=3072 bytes,utf8mb4编码,768*4=3072。

    MyISAM,如果需要建索引,就不能超过1000bytes,utf8编码,333*3=999 bytes,是能建索引情况下的最大值,utf8mb4编码,默认字符长度则应该是1000除以4,就是250。

    一般情况下,我们用的是InnoDB引擎,utf8则是常用字符集,因此varchar类型字段定义为255比较合适。

    但实际上,varchar(255)并不是最优的字符定义长度,究竟定成多少,还是要根据实际需求来决定,例如这个字段我就不需要创建索引,定义超过255,是可以的,只能说255是常规情况下较少出错的一个值。

    小白学习MySQL,

    小白学习MySQL - 变通创建索引的案例一则

    小白学习MySQL - “投机取巧”统计表的记录数

    小白学习MySQL - 一次慢SQL的定位

    小白学习MySQL - TIMESTAMP类型字段非空和默认值属性的影响

    小白学习MySQL - 聊聊数据备份的重要性

    小白学习MySQL - InnoDB支持optimize table?

    小白学习MySQL - table_open_cache的作用

    小白学习MySQL - 表空间碎片整理方法

    小白学习MySQL - 大小写敏感问题解惑

    小白学习MySQL - only_full_group_by的校验规则

    小白学习MySQL - max_allowed_packet

    小白学习MySQL - mysqldump保证数据一致性的参数差异

    小白学习MySQL - 查询会锁表?

    小白学习MySQL - 索引键长度限制的问题

    小白学习MySQL - MySQL会不会受到“高水位”的影响?

    小白学习MySQL - 数据库软件和初始化安装

    小白学习MySQL - 闲聊聊

    近期更新的文章:

    介绍一款Oracle的利器小工具-SQLcl

    ClickHouse的ontime测试数据集

    表和索引统计信息自动采集的问题

    小白学习MySQL - 变通创建索引的案例一则

    ClickHouse安装和使用

    文章分类和索引:

    《公众号800篇文章分类和索引

    展开全文
  • mysql 联合唯一索引失效

    千次阅读 2019-04-29 21:01:10
    如下图所示,在mysql 一张表中 联合唯一索引按照业务要求由三个字段构成 , 其中name和isbn是varhar,type是tinyint 然后又一次查询接口返回报错,提示返回了多个结果,然后我一看表里面,有同样的两条数据,其中...

    如下图所示,在mysql 一张表中 联合唯一索引按照业务要求由三个字段构成 , 其中name和isbn是varhar,type是tinyint

    然后又一次查询接口返回报错,提示返回了多个结果,然后我一看表里面,有同样的两条数据,其中name和type有值,但是isbn是NULL。这个时候我去查询资料发现联合唯一索引中,要是有一个存在null那么整个都会失效。可是,为什么会这样呢?要是是联合索引的话会不会也失效呢?这就需要我们来探索下索引这个东西了。

    参考一篇写的很好的文章关于索引的基本都讲到了,

    http://blog.codinglabs.org/articles/theory-of-mysql-index.html

    但是对于b_tree和b+tree实际上讲的不是很详细,可以看这篇文章

    https://www.cnblogs.com/gym333/p/6877023.html

    这个图就一目了然了

    另外看的途中我们自己要在本机做测试的话需要安装mysql或者native的,但是如果是drds的话他的expalin的展现形式会变成这样

    对于一般正常的数据库会是这样

    所以,这个可能需要其他的途径解决

    展开全文
  • mysql高级知识(linux安装mysql+索引+视图+存储过程和函数+触发器)

    一、linux系统安装Mysql

    1、mysql安装包:

    MySQL :: Download MySQL Community Server

    2、mysql安装

    linux安装在vmware(虚拟机)上,先打开虚拟机

    获取ip地址,ifconfig

    通过远程连接工具secureCRT连接

    1)查找当前mysql安装包版本 rpm -qa | grep -i mysql

    2)版本过低卸载

    Rpm -e mysql安装包版本 --nodeps

    3)mysql安装包上传

    将mysql安装包放在D盘setup目录下

    Alt+P进入到上传界面,使用put指令上传安装包

    put D:/setup/mysql安装包名

    上传成功后,就可以在Linux系统中看到此安装包

    4)解压mysql压缩包,tar

    创建目录名为mysql的文件夹:mkdir mysql

      ll

    将mysql压缩包解压到mysql文件中

     tar -xvf mysql压缩包 -C mysql/

    解压完成

    5)安装依赖包(第三方的依赖库)

    在线安装:

     yum -y install libaio.so.1 libgcc_s.so.1 libstdc++.so.6 libncurses.so.5 --setoopt=protected_multilib=false

     yum update libstdc++-4.4.7-4.el6.x86_64

    6)安装mysql客户端和服务端,client.rpm,server.rpm

     rpm -ivh MySQL-client-***.rpm

     rpm -ivh MySQL-server-***.rpm

    3、mysql启动和停止

    mysql状态 service mysql status

    mysql启动 service mysql start

    mysql停止 service mysql stop

    4、mysql登录

     mysql -u root -p

    密码:查看安装mysql服务端的日志,随机生成了一个密码  cat /root/.mysql-secret

    修改密码:set password=password('root');

    重新登陆: mysql -u root -p

     root

    5、windows使用工具连接linux上mysql,sqlyog

    连接不了,原因是:未授权远程访问

    授权远程访问:grant all privileges on *.* to 'root' @'%' identified by 'root';

    刷新权限列表使授权生效:flush privileges;

    6、再次连接,连接不了

    是linux的防火墙导致的

    查看防火墙的状态:service iptables status

    关闭防火墙:service iptables stop

    7、windows远程连接linux成功

    二、索引

    1、索引概述

    索引(index)是帮助mysql高效获取数据的数据结构

    索引的作用:高效查询

                   根节点

    比根节点小     比根节点大

    以二叉树为例,数据:34   77   5   91   22   59   3

                   34

            5               77

    3          22      59     91

    在数据之外,数据系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引向(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。

    2、索引优缺

    优势:

    1)类似于书籍的目录索引,提高数据检索的效率,降低数据库的io成本即快速查询数据

    2)通过索引列对数据进行排序,降低数据排序的成本,降低cpu的消耗

    劣势:

    1)实际上索引也是一张表,该表中保存了主键与索引字段,并指向实体类的积累,所以索引列也是要占用空间的即索引需要占用磁盘空间

    2)虽然索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行insert、update、delete.因为更新表时,mysql不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息即索引字段并不是越多越好,索引字段越多会降低查询数据的效率

    3、索引结构

    索引是在mysql的存储引擎层中实现的,而不是在服务器层实现。所以每种索引引擎的索引都不一定完全相同,也不是所有的存储引擎都支持所有的索引类型。

    主流的四种索引结构:

    *btree索引:innoDB引擎支持btree索引

    hash索引

    R-tree索引

    Full-text(全文索引)

    我们平常所说的索引,如果没有特别指明,都是指B+树(多路搜索树,并不一定是二叉树)结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+tree索引,统称为索引。

    1)btree结构

    btree又叫多路平衡搜索树,一棵m叉的btree特性如下:

    • 树中每个节点最多包含m个孩子
    • 除根节点与叶子节点外,每个节点至少有[cell(m/2)]个孩子 注:向上取整
    • 若根节点不是叶子接待你,则至少有两个孩子
    • 所有的叶子节点都在同一层
    • 每个非叶子节点由n个key与n+1个指针组成,其中[cell(m/2)-1]<=n<=m-1

    以5叉为例,公式推导[cell(m/2)-1]<=n<=m-1所有2<=n<4,当n>4时,中间节点分裂到父节点,两边节点分裂

    插入C N G A H E K Q M F W L T Z D P R X Y S 数据为例

    演变过程:

    1.1)插入前4个字母 C N G A

    A

    C

    G

    N

    1.2插入Hn>4,中间元素G字母向上分裂到新的节点

                 G

    A    C           H   N

    1.3)插入E K Q 不需要分裂

                    G

    A  C E        H  K N Q

    标黄代表n的个数,大于4就要向上分裂

    1.4)插入Mn>4,中间元素M向上分裂到父节点G

                      G         M

    A   C  E        H  K       N Q

    1.5)插入F W L T 不需要分裂

                      G         M

    A C E F       H  K L      N QTW

    1.6)插入Z,中间元素T向上分裂到父节点中

                      G         M       T

    A C E F       H  K L      N Q     WZ

    1.7)插入D,中间元素向上分裂到父结点中,然后插入PRXY不需要分裂

            D      G         M       T

    A C      E F       H  K L      NP QR     WXYZ

    1.8)最后插入SNPQRn>4,中间节点Q向上分裂,但分裂后父节点DGMTn>4,中间节点M向上分裂

                                   M

                 DG                              Q T

    A C      E F       H  K L      NP      RS     WXYZ

    到此,该tree树就已经构建完成,btree树和二叉树相比,查询数据的效率更高,因为对于相同的数据量来说,btree的层级结构比二叉树小,因此搜索速度快

    2)B+tree结构

    B+tree为btree的变种,B+tree与btree的区别为:

    优点:

    由于b+tree只有叶子节点保存key信息,查询任何key都要从root走到叶子,所以B+tree的查询效率更加稳定。

    3)mysql中的b+tree

    mysql索引数据结构对经典的B+tree进行了优化,在原有B+tree的基础上,增加一个指向相邻叶子节点的链表指针(指针之间关联),就形成了带有顺序指针的B+tree,提高区间访问的性能。

    目的:方便范围搜索

    4、索引分类

    1)单值索引(单列索引):一个索引只包含单个列,一个表可以有多个单列索引

    2)唯一索引:索引列的值必须唯一,但允许有空值

    3)聚合索引:一个索引包含多个列

    5、索引语法

    索引在创建表的时候,可以同时创建,也可以随时增加新的索引

    环境准备工作:

    在之前的Linux环境中,创建demo_01数据库

     create database demo_01 default charset=utf8mb4;

    切换数据库

     use demo_01

    创建两张表

    • n叉B+tree最多包含有n个key,而btree最多含有n-1个key
    • B+tree的叶子节点保存所有的key信息,依key大小顺序排列
    • 所有的非叶子节点都可以看作时key的索引部分

     CREATE TABLE 'city'(

    'city_id' int(11) NOT NULL AUTO_INCREMENT,

    'city_name' varchar(50) NOT NULL,

    'county_id' int(11) NOT NULL,

    PRIMARY KEY('city_id')

    )engine=InnoDB DEFAULT CHARSET=utf8;

    CREATE TABLE 'county'(

    'county_id' int(11) NOT NULL AUTO_INCREMENT,

    'county_name' varchar(100) NOT NULL,

    PRIMARY KEY('county_id')

    )engine=InnoDB DEFAULT CHARSET=utf8;

    插入数据

     insert into 'city' ('city_id','city_name','county_id') values(1,'xian',1);

     insert into 'city' ('county_id','county_name') values(1,'xian');

    查看表

     show tables;

     select * from city;

    1)创建索引

    语法:

    CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

    [USING index_type]

    ON tb1_name(index_col_name,…)

     index_col_name:colum_name[(length)][ASC|DESC]

    示例:为city表中的city_name字段创建索引(普通索引)

     create index idx_city_name on city(city_name);

    注意事项:city_id是主键,默认有主键索引

    2)查看索引

    语法:

     show index from table_name;

    示例:查看city表中的索引信息

     show index from city; #横向展示

     show index from city\G;#纵向展示

     注意事项:index_type默认btree

    3)删除索引

    语法:

    DROP INDEX index_name ON tbl_name;

    示例:想要删除city表上的索引idx_city_name

     drop index idx_city_name on city;

    4)alert命令,修改表对表创建索引

    4.1alert table tb_name add primary key(colum_list);

    该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL

    4.2alert table tb_name add unique index_name(colum_list);

    这条语句创建索引的值必须是唯一的(除了NULL之外,NULL可能会出现多次)

    4.3alert table tb_name add index index_name(colum_list);

    添加普通索引,索引值可以出现多次

    4.4alert table tb_name add fulltext index_name(colum_list);

    该语句指定了索引为FULLTEXT,用于全文检索

    示例:针对city表中city_name字段创建唯一索引

     alert table city add unique idx_city_name(city_name);

    注意事项:创建普通索引的Non_unique:1

    创建唯一索引的Non_unique:0

    6、索引设计原则

    • 对查询频次较高,且数据量比较大的表建立索引
    • 索引字段的选择,最佳候选列应当从where子句的条件中提取,如果where子句中的组合比较多,那么应当挑选最常用、过滤效果最好的列的组合。
    • 使用唯一索引,区分度越高,使用索引的效率越高即尽量使用唯一索引
    • 索引可以有效的提升查询数据的效率,但索引数量不是多多益善,索引越多,维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说,索引过多,会引入相当高的维护代价,降低DML操作的效率,增加相应操作的时间消耗。另外索引过多的话,MYSQL也会犯选择困难病,虽然最终仍然会找到一个可用的索引,但无疑提高了选择的代价,即索引数量要适量
    • 使用短索引,索引创建之后也是使用硬盘来存储的,因此替身索引访问的I/O效率,也可以提升总体的访问效率。假如构成索引的字段总长度比较短,那么在给定大小的存储块内可以存储更多的索引值,相应的可以有效的提升mysql访问所以的I/O效率即尽量使用短索引
    • 利用最左前缀(针对复合索引),N个列组合而成的组合索引,那么相当于创建了N个索引,如果查询时where子句中使用了该索引的前几个字段,那么这条查询SQL可以利用组合索引来提升查询效率

    创建复合索引:

     create index idx_name_email_status ON tb_seller(name,email,status);

    相当于:

    对name创建索引

    对name,email创建索引

    对name,email,status创建索引

    理解:只要包含了第一个字段name就可以

    三、视图

    1、视图概述

    视图(view)是一种虚拟存在的表,视图并不在数据库中实际存在,行和列数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的,通俗的讲,视图就是一条select语句执行后返回的结果集。所以我们在创建视图的时候,主要的工作就落在创建这条sql查询语句上。

    视图相对于普通的表的优势:

    • 简单:使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件,对用户来说是过滤好的复合条件的结果集即用户只关心视图有哪几个字段,类似于单表操作。
    • 安全:使用视图的用户只能访问他们被允许访问查询的结果集,对表的权限管理并不能限制到某个行某个列,但是通过视图就可以简单的实现
    • 数据独立:一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响;源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响

    2、视图操作基本语法

    1)创建视图语法:

    CREATE [OR REPLACE] [ALGORIHM={UNDEFINED|MERGE|TEMPTABLE}]

    VIEW view_name [(column_list)]

    AS select_statement

    [WITH [CASEADED|LOCAL]CHECK OPTION]

    示例:在视图中展示中国的城市名称,创建city_county_view视图

     create view view_city_county as select c.*,t.county_name from city c,county t where c.county_id=t.county_id;

    注意事项:不建议更新视图

    2)修改视图语法:

    ALERT [ALGORIHM={UNDEFINED|MERGE|TEMPTABLE}]

    VIEW view_name [(column_list)]

    AS select_statement

    [WITH [CASEADED|LOCAL]CHECK OPTION]

    选项:

    WITH [CASEADED|LOCAL] CHECK OPTION决定了是否允许更新数据使记录不再满足视图条件

    LOCAL:只要满足本视图的条件就可以更新

    CASEADED:必须满足所有针对该视图的所有视图的条件才可以更新

    3)查看视图

    show tables;

     show view_city_county ;

    或者通过sqlyog工具,视图-view_city_county

    查看创建视图时执行的语句:show create view view_city_county;

    4)删除视图

    DROP VIEW [IF EXISTS] view_name [,view_name]…[RESTRIVT|CASCADE]

    示例:删除视图view_city_county

     drop view view_city_county;

    四、存储过程和函数

    1、存储过程和存储函数的概述

    存储过程和函数时事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程和函数可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是由好处的。

    存储过程和函数的区别:

    函数:是一个有返回值的过程

    过程:是一个没有返回值的函数

    2、创建存储过程

    CREATE PROCEDURE procedure_name ([proc_parameter[…]])

     begin

     --sql语句

     end;

    示例:

     create procedure pro_test1()

     begin

     select 'Hello MySQL'

     end;

    不清楚sql语句没有添加;能不能执行,大概率不能

    正确语法:

    delimiter $

     cretate procedure pro_test1()

     begin

     select 'Hello MySQL';

     end$

     delimiter;

    注意事项:

     create procedure pro_test1()

     begin

     select 'Hello MySQL';

    运行此报错,原因是;分隔符结束语法

     将分隔符替换成$,分隔符不生效delimiter $

      delimiter,该关键字用来声明sql语句的分隔符,告诉mysql解释器,该段命令是否已经结束了,mysql是否可以执行了,默认情况下,delimiter是分号;在命令行客户端中,如果有一行命令以分号结束,那麽回车后,mysql将会执行该命令

    3、调用存储过程

    语法:call procedure_name();

    示例: call pro_test1()$

    4、查看存储过程

    1)#查询db_name数据库中的所有的存储过程

    select name from mysql.proc where db='db_name';

    示例:

    查看demo_01数据库里的所有的存储过程

     select name from mysql.proc where db='demo_01'$

    注意事项:上面代表sqlyog中查看存储过程,mysql-proc

    2)查询存储过程的状态信息

     show procedure status;

    示例: show procedure status$

    查看到存储过程的详细信息 show procedure status \G $

    3)查询某个存储过程的定义

     show create procedure test.pro_test1 \G;

    示例:

     查看创建存储过程的语法:show create procedure test.pro_test1 \G$

    注意事项:

    在sqlyog中查看存储过程的创建语句:demo_01/存储过程/pro_test1/信息

    5、删除存储过程

    DROP DROCEDURE [IF EXISTS] sp_name;

    示例:drop procedure pro_test1$

    6、语法

    存储过程是可以编程的,意味着可以使用变量,表达式,控制结构,来完成比较复杂的功能

    1)变量的声明以及变量的赋值

      1.1)DECLARE

    通过DECLARE可以定义一个局部变量,该变量的作用范围只能在BEGIN…END块中

    语法:DECLARE var_name[…] type [default value]

    示例:

     delimiter $

     create procedure pro_test2()

     begin

          declare num int default 10;#声明变量

          select conact('num的值为:',num);#查找变量

     end $

     delimiter;

    调用存储过程查看运行结果: call pro_test2()$

     1.2)set 赋值

    语法:SET var_name = expr [,var_name=expr]…

    示例:

     create procedure pro_test3()

     begin

          declare num int default 0;

          set num = num+10;

          select num;

     end $

    也可以通过select … into进行赋值:

    示例:

     create procedure pro_test4()

     begin

          declare num int;

          select count(*) into num from city;

          select concat('city表中的记录数为:',num);

     end $

    2)if条件判断

    语法:

     if search_condition then statement_list

    [elseif search_condition then statement_list]…

    [else statement_list]

    End if;

    需求:根据定义的身高变量,判断当前身高所属的身材类型

    180及以上   身材高挑

    170-180         标准身材

    170以下      一般身材

    示例:

     create procedure pro_test5()

     begin

            declare height int default 175;

    declare description varchar(50) default '';

    if height >=180 then

       set  description='身材高挑';

             elseif 170<= height <180 then

                set  description='标准身材';

             else height <170

                set description='一般身材';

             end if;

             select concat('身高',height,'对应的身材类型为',dexcription);

     end $

    需求:将default 175变为参数,引入下个知识点传递参数

    3)传递参数

    语法:

     cretae procedure procedure_name([in/out/inout] 参数名 参数类型)

    IN:该参数可以作为输入,也就是需要调用方传入值,默认

    OUT:该参数作为输出,也就是该参数可以作为返回值

    INOUT:既可以作为输入参数,也可以作为输出参数

    3.1IN输入

    需求:根据定义的身高变量,判定当前身高所属的身材类型

     create procedure pro_test6(in height int)

     begin

    declare description varchar(50) default '';

    if height >=180 then

       set  description='身材高挑';

             elseif 170<= height <180 then

                set  description='标准身材';

             else height <170

                set description='一般身材';

             end if;

             select concat('身高',height,'对应的身材类型为',dexcription);

     end $

    调用存储过程:call pro_test6(198)$

    3.2)OUT输出

    相当于存储过程执行完毕的返回值

    需求:根据定义的身高变量,判定当前身高所属的身材类型(以返回值类型返回)

     create procedure pro_test7(in height int,out description varhar(10))

     begin

    declare description varchar(50) default '';

    if height >=180 then

       set  description='身材高挑';

             elseif 170<= height <180 then

                set  description='标准身材';

             else height <170

                set description='一般身材';

             end if;

     end $

    调用存储过程:call pro_test7(178,@description)$

     select @description

    注意事项:@+变量名,代表用户会话变量

    @description:这种变量要在变量名称前面加上‘@’符号,叫做用户会话变量,代表整个会话过程他都是有作用的,这个类似于全局变量一样

    @@global.sort_buffer_size:这种在变量前加上‘@@’符号叫做系统变量

     set name='itcast';$ #会报错,name不是一个系统变量

     set @name='itcast'$#用户会话变量声明成功

    4)case结构

    语法:方式一

    CASE case_value

        WHEN when_value THEN statement_list

        [WHEN when_value THEN statement_list]…

        [ELSE statement_list]

    END CASE;

    方式二

    CASE

        WHEN search_condition THEN statement_list

        [WHEN search_condition  THEN statement_list]…

        [ELSE statement_list]

    END CASE;

    需求:给定一个月份,然后计算出所在的季度

     create procedure pro_test8(mon int)

     begin

         declare result varchar(10);

         case

               when mon>=1 and mon<=3 then

                   set result='第一季度';

            when mon>=4 and mon<=6 then

                   set result='第二季度';

    when mon>=7 and mon<=9 then

         set result='第三季度';

     ELSE

                   set result='第四季度';

        end case;

     select concat('传递的月份为:',mon,'计算出所在季度为:',result)as content;

     end$

    调用存储过程:call pro_test8(11)$

    尝试修改:

     create procedure pro_test8(mon int,out  result varchar(10))

     begin

         case mon

               when mon>=1 and mon<=3 then

                   set result='第一季度';

            when mon>=4 and mon<=6 then

                   set result='第二季度';

    when mon>=7 and mon<=9 then

         set result='第三季度';

     ELSE

                   set result='第四季度';

        end case;

     end$

    调用存储过程:call pro_test8(11,@result)$

     select @result

    5)while循环

    语法:

     while search_condition db

           statement_list

     end while;

    需求:计算从1加到n的值

    示例:

     create procedure pro_test9(n int)

     begin

          declare total int default 0;

          declare num int defaule 1;

          while num<=n do

               set total=total+num;

               set num=num+1;

          end while;

          select total;

     end$

    调用存储过程:call pro_test9(3)$

    注意事项:如果存储过程中编写错误,先删除存储过程再重新执行

    6)repeat循环结构

    有条件的循环控制语句,当满足条件的时候退出循环,while是满足条件才执行,repeat是满足条件就退出循环

    语法:

    REPEAT

        statement_list

        UNTIL search_condition

    END REPEAT

    需求:计算从1加到n的值

    示例:

     create procedure pro_test10(n int)

     begin

          declare total int default 0;

          repeat

               set total=total+n;

               set n=n-1;

       until n=0

          end repeat;

          select total;

     end$

    调用存储过程:call pro_test10(3)$

    注意事项:满足条件就退出

    尝试修改:大概率出错,因为语法结构中的until没有

     create procedure pro_test10(n int)

     begin

          declare total int default 0;

          declare num int defaule 1;

          repeat num>n do

               set total=total+num;

               set num=num+1;

          end repeat;

          select total;

     end$

    调用存储过程:call pro_test10(3)$

    7)loap循环语句

    loap实现简单的循环,退出循环的条件需要使用其他的语句定义,通常可以使用leave语句实现

    语法:

    [begin_label:] LOOP

         statement_list

    END LOOP [end_label]

    如果不在statement_list中增加退出循环的语句,那么LOOP语句可以用来实现简单的死循环

    8)leave语句

    用来从标注的流程构造中退出,通常和begin…end 或者循环一起使用

    需求:计算从1加到n的值  loop+leave

    示例:

     create procedure pro_test11(n int)

     begin

           declare total int default 0;

           c:loop

                 set total=total+n;

                 n=n-1;

                 if n<=0 then

                     leave c;

                 end if;

           end loop c;

     select total;

    end $

    调用存储过程:call pro_test11(3)$

    注意事项:在循环的过程中什么时候退出循环

    9)游标/光标

    游标是用来存储查询结果集的数据类型,在存储过程和函数中可以使用光标对结果集进行循环的处理,光标的使用包括光标的声明、open、fetch和close

    语法:

    声明光标:

    DECLARE cursor_name CURSOR FOR select_statement;

    open光标:

    OPEN cursor_name;

    fetch光标:

    FETCH cursor_name INFO var_name [,var_name]…

    close光标:

    CLOSE cursor_name;

    示例:

    初始化脚本(创建了一张表,并导入四条数据)

     create table emp(

     id int(11) not null auto_increment,

     name varchar(50) not null comment '姓名',

     age int(11) comment'年龄',

     salary int(11) comment '薪水',

     primary key('id')

     )engine=innodb default cahrset=utf8;

    Insert into emp(id,name,age,salary) values(null,'金毛狮王',55,3800),(null,'白眉鹰王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800);

    注意事项:$结束

    查询emp表中数据,并逐行获取进行展示

     create procedure pro_test12()

     begin

          declare e_id int(11);

          declare e_name varchar(50);

          declare e_age int(11);

          declare e_salary int(11);#必须和数据库表保持一致

           declare emp_result cursor for select * from emp;#声明游标

           open emp_result;#开启游标

          fetch emp_result into e.id,e.name,e.age,e.salary;#获取游标

           select concat('id=',e_id,',name=',e.name,'age',e.age,'salary',e.salary);

           #fetch一次只会显示一条记录,如果fetch5次会报错

         fetch emp_result into e.id,e.name,e.age,e.salary;#获取游标

           select concat('id=',e_id,',name=',e.name,'age',e.age,'salary',e.salary);

          fetch emp_result into e.id,e.name,e.age,e.salary;#获取游标

           select concat('id=',e_id,',name=',e.name,'age',e.age,'salary',e.salary);

         fetch emp_result into e.id,e.name,e.age,e.salary;#获取游标

           select concat('id=',e_id,',name=',e.name,'age',e.age,'salary',e.salary);

           close emp_result;#关闭游标

    end $

    调用存储过程:call pro_test12

    通过循环结构,获取游标中的数据

    循环退出条件:循环到最后一条退出

     方法一:count(*) 获得总记录数num

     num-1当num减到0退出

     方法二:mysql机制

    当抓取不到数据的时候会触发句柄(相当于触发事件),将这个触发句柄设置成一个变量(边界变量),通过边界变量控制退出

     create procedure pro_test13()

     begin

          declare e_id int(11);

          declare e_name varchar(50);

          declare e_age int(11);

          declare e_salary int(11);#必须和数据库表保持一致  

          declare has_data int default 1;

          declare emp_result cursor for select * from emp;#声明游标

          declare exit handler for not found set has_data=0;#当拿不到数据时触发句柄将has_data=0

           open emp_result;#开启游标

           repeat

           fetch emp_result into e.id,e.name,e.age,e.salary;#获取游标

           select concat('id=',e_id,',name=',e.name,'age',e.age,'salary',e.salary);

                   until has_data=0

           end repeat

           close emp_result;#关闭游标

    end $

    注意事项:

     declare emp_result cursor for select * from emp;#声明游标

     declare exit handler for not found set has_data=0;#当拿不到数据时触发句柄将has_data=0

    句柄声明条件必须在游标声明之后,不然报错

    7、存储函数:有返回值的过程

    存储过程有in和out,out可以返回数据

    语法:

    CREATE  FUNCTION function_name([param type…])

    RETURNS type

    BEGIN

    END;

    示例:定义一个存储函数,获取满足条件city的总记录数

     create function fun1(countyId int)

     returns int

     begin

         declare cnum int;

         select count(*) into cnum from city where county_id=countyId;

         retuen cnum;

     end$

    调用存储函数:select fun1(1)$

    删除存储过程:drop fun1()$

    注意事项:存储过程和存储函数在sqlyog中都可以直观的看到

    五、触发器

    1、简单介绍

    触发器是与表有关的数据对象,指在insert/update/delete之前或之后,触发并执行触发器中定义的sql语句集合。

    和事件有点类似

    作用:

    触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作

    行记录变量:OLD 和NEW(使用OLD 和NEW来引用触发器中发生变化的记录内容)

    触发器类型

    OLD 和NEW的使用

    insert

    NEW表示将要或者已经新增的数据

    update

    OLD表示修改之前的数据,NEW表示将要卓已经修改后的数据

    delete

    OLD表示将要或者已经删除的数据

    2、创建触发器

    语法:

     create trigger triggrt_name

     before/after insert/update/delete

     on tb1_name

     [ for each row]#行触发器

     begin

     trigger_stmt;

     end;

    注意事项:mysql只支持行触发器不支持语句触发器,oracle既支持行触发器又支持语句触发器

    示例:

    通过触发器记录emp表的数据变更日志emp_logs,包含增加、修改】删除

    1)创建日志表

     create table emp_logs(

       id int(11) not null auto_increment,

       operation varchar(20) not null comment '操作类型,insert/update/delete',

       operate_time datetime not null comment '操作时间',

      operate_id int(11) not null comment '操作表的ID',

      operate_parames varchar(500) comment '操作参数',

      primary key('id')

    )engine=innodb default cahrset=utf8;

    2)创建insert型触发器,完成插入数据时的日志记录(创建三个触发器)

     create trigger emp_insert_trigger

     after insert

     on emp

     for each row

     begin

        insert into emp_logs(id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat('插入后(id:',new.id,'name:','new.name','age:',new.age,',salary:',new.salary,')'));

     end$

    测试:插入一条数据

     select * from emp_logs $

    注意事项:select * from emp_logs$会报错

     create trigger emp_update_trigger

     after update

     on emp

     for each row

     begin

        insert into emp_logs(id,operation,operate_time,operate_id,operate_params)  values(null,'update',now(),new.id, concat('修改前(id:',old.id,'name:','old.name','age:',old.age,',salary:',old.salary,')','修改后(id:',new.id,'name:','new.name','age:',new.age,',salary:',new.salary,')'));

     end$

    测试:执行一条update语句

     select * from emp_logs $

     create trigger emp_delete_trigger

     after delete

     on emp

     for each row

     begin

        insert into emp_logs(id,operation,operation_time,operation_id,operation_params) values(null,'insert',now(),new.id,concat('修改前(id:',old.id,'name:','old.name','age:',old.age,',salary:',old.salary,')'));

     end;

    测试:执行一条delete语句

     select * from emp_logs $

    3、删除触发器

    语法:

     drop trigger [schema_name.]trigger_name;

    如果没有指定schema_name,默认为当前数据库

    4、查看触发器

    可以通过执行show triggers命令查看触发器的状态、语法等信息

    语法:

     show triggers;

     转换信息格式:show triggers \G;

    注意事项:sqlyog也可以直观查看触发器

    展开全文
  • 包含的最大的字符数,可以省略,默认为1 固定长度的字符数 比较耗费 高 varchar varhar(M) 包含的最大的字符数,不可省略 可变长度的字符数 比较节省 低 比如:性别只有男女,char(1)就好 其他: (1)枚举型,...
  • 使用 redis 进行计数,并定期将计数结果沉淀到 mysql 中,遇到一个问题,...唯一索引。 但是我在redis中存储使用的是hash,不可能出现重复的,后来打印日志发现redis中存储的key出现了大小写的问题,也就是 User,...
  • 3. 如果此字段加上索引,MySQL 不会自己做类型转换,只能二进制来过滤。 示例 7 创建表 c1, 字段性别定义一个比特位。mysql-(ytt/3305)->create table c1(gender bit(1));Query OK, 0 rows affected (0.02 sec) ...
  • Mysql简单使用

    2019-12-03 11:02:56
    MySQL使用 允许外面机器(各种客户端)连接mysql 允许用户root从任意ip的主机连接到mysql服务器,并使用mysqladmin作为密码 mysql> grant all privileges on *.* to root@'%' identified by 'mysqladmin'; Query ...
  • 《阿里巴巴 Java开发手册》常用规范

    千次阅读 2021-03-05 16:17:47
    前言参考自: 微信公众号"java3y"中的文章 - 《阿里巴巴 Java开发手册》读后感都是日常能的一些规范, 还是很有用处的!一、Java相关1.POJO是DO/DTO/BO/VO的统称,禁止命名为xxxPOJO2.获取多个对象的方法中list作为...
  • 什么80%的码农都做不了架构师?>>> ...
  • Oracle怎么定义VARCHAR2()的长度

    千次阅读 2021-03-08 14:17:16
    也看到一些例子,说定义过大,会带来一些限制,主要有如下几点: 1、导致索引超出字符限制 由于数据库的限制(参见Logical Database Limits),每个索引字段的总长度不能超过75% * the database block size再减去...
  • 数据库中的char、varchar、nvarchar类型

    千次阅读 2017-08-22 19:49:39
    char主要用于存储定长数据,char字段上的索引效率级高,比如定义char(10),那么不论你存储的数据是否达到了10个字节,都要占去10个字节的空间,不足的自动空格填充,所以在读取的时候可能要多次用到trim() ...
  • 前言 只有光头才能变强 前一阵子一直在学Redis,结果在黄金段位被虐了,暂时升不了段位了,每天都拿不到首胜(好烦)。 趁着学校校运会,合理地给自己放了一个小长假,然后就回家了。...书上很多的规范是可以ID...
  • MySQL

    2021-07-24 18:47:26
    1.1、为什么学习数据库 数据库是所有软件体系中最核心的存在 1.2、什么是数据库(DB,DataBase) 概念:数据仓库,软件,安装在操作系统(window,Linux,mac等)之上!SQL,可以存储大量的数据(500万) 作用:存储...
  • 表是Oracle数据库中最基本的对象之一。万丈高楼从平地起,这个基础对象对于数据库来说,非常重要。因为其设计是否合理,直接跟数据库的性能相关。从Oracle数据库菜鸟到...在数据库表设计过程中,有些字段要求必...
  • 3. 如果此字段加上索引,MySQL 不会自己做类型转换,只能二进制来过滤。 示例 7 创建表 c1, 字段性别定义一个比特位。mysql-(ytt/3305)->create table c1(gender bit(1));Query OK, 0 rows affected (0.02 sec) ...
  • 参考:https://blog.csdn.net/wudinaniya/article/details/81094578 1、首先去mysql官网下载rpm包,一个是server包一个是client包。... 这里mysql community server里边的。这里使用的是5.7.x版本了,不使用Mysql8...
  • hibernate对于枚举类型(Enum) 的映射比较麻烦, 因为JDBC 不支持枚举类型. 但我们想保存Enum ... 按枚举的索引保存,关于"-6"怎么来的,参看java.sql.Types <param name="type">-6 -->
  • MySQL数据库管理(1)

    2022-06-14 13:53:07
    显示数据表的结构(字段)5. 常用的数据类型二. SQL语句1. SQL语言分类三. DDL1. 创建新的数据库​编辑2. 创建新的表3. 删除指定的数据表 4. 删除指定的数据库 四. DML1. 插入新数据:insert五. DQL1. 查询数据记录...
  • 3. 如果此字段加上索引,MySQL 不会自己做类型转换,只能二进制来过滤。 示例 7 创建表 c1, 字段性别定义一个比特位。mysql-(ytt/3305)->create table c1(gender bit(1));Query OK, 0 rows affected (0.02 sec) ...
  • 每篇一句 无规矩不成方圆 无规范不能协作 拥抱规范,远离伤害 前言 对于编码规范,本人一直是公司里的大力推崇者。其实不乏发现有很多人并不注重...一个成熟的项目要想长足发展(其实我还没看过什么项目只考虑...
  • 所有的抽象方法(包括接口中的方法)必须要 Javadoc 注释,除了返回值、参数、异常说明外,还必须指出该方法做什么事情,实现什么功能。所有的类都必须添加创建者和创建日期。 18.对于暂时被注释掉,后续可能恢复...
  • 3. 如果此字段加上索引,MySQL 不会自己做类型转换,只能二进制来过滤。 示例 7 创建表 c1, 字段性别定义一个比特位。mysql-(ytt/3305)->create table c1(gender bit(1));Query OK, 0 rows affected (0.02 sec) ...
  • orcal数据库技巧和注意事项

    千次阅读 2017-02-13 10:45:20
     在数据库表设计过程中,有些字段要求必须为非空,如表的关键字,单据编号字段等等。在数据库表创建的时候,往往需要把这些字段设置为非空。如此的话,就可以强制用户输入数据,以增强数据的一致性。  同时,在...
  • 如在设计表的时候,一开始不确定那些字段要添加约束,哪些字段不能为空,哪些字段需要什么默认值等等。等到需要的时候,再随意添加。这种做事方式,往往会给数据库带来不稳定的隐患。  如某个数据库管理人员在建立...
  • 3. 如果此字段加上索引,MySQL 不会自己做类型转换,只能二进制来过滤。 示例 7 创建表 c1, 字段性别定义一个比特位。mysql-(ytt/3305)->create table c1(gender bit(1));Query OK, 0 rows affected (0.02 sec) ...
  • 3. 如果此字段加上索引,MySQL 不会自己做类型转换,只能二进制来过滤。 示例 7 创建表 c1, 字段性别定义一个比特位。mysql-(ytt/3305)->create table c1(gender bit(1));Query OK, 0 rows affected (0.02 sec) ...

空空如也

空空如也

1 2 3
收藏数 52
精华内容 20
关键字:

varhar字段用什么索引