mysql文档

一、mysql简介

1.1 mysql是什么？

       mysql是完全网络化的跨平台的关系型数据库管理系统（RDBMS），也是基于客户端/服务器体系的分布式数据库管理系统。

1.2 关系型数据库的两个原则

       实体完整性原则和引用完整性原则。

    实体完整性原则：数据库表的主键不能全部或者部分为空（主键可以是一列，也可以是多列，多列组成的主键被称为联合主键）。

    引用完整性原则：数据库表的外键必须为空或者必须与它所引用的其他表的主键相一致。

一、数据库内容介绍

1.1数据库表支持的数据类型

       大体包含三类，字符串、数字、日期和null值。

      

1.1.1字符串（枚举）

字符串既可以使用单引号，也可以使用双引号。mysql不仅支持普通的字符，还支持转义序列表示的特殊字符。

例如：

insertinto t_stock(stock_name,stock_price,stock_salePrice)

values('股票\r\n一号',99.99,99.99);保存到数据库后是换行。

mysql支持的特殊字符

1.1.2 数字值

mysql提供了五种整数类型：tinyint , smallint , mediumint , int ,bigint .

mysql提供了三种浮点类型：float , double , decimal .

1.1.3 日期值

日期和时间值是一些类似于“1999-06-17”或“12:30:43”或“1999-06-1712:30:43”这样的值，而且mysql是以“年-月-日”的顺序来表示日期。

日期类型具体分为四种：1.datetime：日期时间类型（2017-10-25 11:11:11）；2.date：日期类型（2017-10-25）；3.time:时间类型（11:11:11）；4.timestamp:如果设置为not null，则默认为当前的日期时间，一张表只有第一个该类型的字段在添加和更新数据时会自动把这个字段更改为当前时间。

 

1.2数据库表的字段组成

       表的字段由两部分组成，字段的数据类型以及字段长度。对于numeric数据类型的字段，还可以指定小数位的精确程度。例如：numeric(9,2)表示这个浮点数的长度是9，小数点后位数为2，取值范围是（-9999999.99~99999999.99）。

    在创建表时，要为每个列定一个类型，这个类型比数据类型相对应，但是更具体，用列类型描述该列可能存储的数据种类和范围。例如char(16)就规定了存储的字符串必须是16位。当然不是必须存储16个字符，而是该列在表中占16个字符的宽度。

1.3 函数

       函数是存储在数据库中的代码块，也可以创建自己的函数，在sql语句中调用。

三、数据库的操作

1.1 显示数据库管理系统包含的数据库实例

       show databases;

1.2 创建数据库实例

       create database demo;

1.3 使用数据库实例

       use demo;

1.4 没有修改数据库实例名这一说

1.5 删除数据库实例

       drop database if exists demo;

1.6 查找数据库

       show databases like ‘%demo%’

 

 

四、表的操作

1.1  显示该数据库实例有哪些表

show tables;

1.2创建表

create table t_teacher

(

       `id`int(11) not null auto_increment,

       `teacher_name`varchar(255) default null,

       `gender`enum(‘男’,’女’) default null,

       `is_delete`tinyint default null unsigned,

       `create_time`datetime default null,

       `update_time`timestamp not null,

       primary_key(`id`)      

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

1.3修改表名

        alter table `t_teacher` rename `t_tea`;

1.4删除表

       droptable if exists `t_teacher`;

五、字段的操作

1.1添加字段

alter table `t_teacher` add `age`smallint(3);

1.2修改字段的名字

       alter table `t_teacher` change `gender``sex` varchar(2);

1.3修改表字段的类型

alter table `t_teacher`

modify `create_time` timestamp

值得注意的是：设计表时，如果timestamp字段不为null，增加修改某条信息时（信息真的被改了），这个字段会自动更新为当前时间；而且倘若一张表中有多个字段都是timestamp类型，默认只有第一个该字段会更新为当前时间。

1.4删除字段

       altertable ` t_teacher ` drop column `teacher_name`;

      

六、高级特性

1.1集合函数（group by）

       使用场景：统计最大值、最小值、求和、平均值等，集合函数通常与group by 结合使用，值得注意的是，集合函数不能与非分组的列组合使用。

1.     count():count函数通常与group by组合使用（单用也行），用于统计不同

种类的数量。

       例：算出动物表中每种动物的数量

select animal_type ,count(1) from animal group by animal_type;

2.     avg():计算平均值，如果某个值是null,计算时会忽略这条数据，所以需要

考虑是否把null值转换为0。avg()函数只适用于数值类型。

       例：计算每个班的学生的平均年龄

              selectclazzId,avg(age) from t_student group by clazzId;

3.     sum():计算和。

4.     min():例如找出生日最小的是几月几号

5.     max():例如找出生日最大的是几月几号

例：SELECT species,MIN(birth) FROM pet GROUP BY species;

       总结；这些函数之所以称作集合函数，是因为作用于多条数据中，所以集合函数最常见的用法是跟 group by 子句组合使用，最重要的是集合函数不能跟未分组的列混合使用（新版的mysql不会报错，能查出数据，但是应该遵循规范）

 

1.2时间函数

       1.  curdate():返回当前的日期（年月日）

       例：insert intot_clazz(date) values(curdate())

       2.  curtime():返回当前的时间（时分秒）

       例：insert intot_clazz(time) values(curtime());

       3.  now():返回当前的日期时间

       4.  to_days(date):给出一个日期date，返回一个天数（从0年0月0日开始算），

       例：selectto_days('0000-01-01') from dual;    最终结果是1

       查看21世纪已经过去了多少天如下：

       select to_days(now())-to_days('20010101');

1.3其他函数

       1.ifnull(stu_name,expr2)  如果stu_name字段是null,返回expr2；不是null时，返回本身的值

1.4字符串模式匹配

      1 标准的sql模式匹配

       SQL 的模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零个字符)。

2扩展正则表达式模式匹配

       首先，mysql的正则表达式与java略有不同。

       .表示匹配任意单个字符

       [a,b,c]表示匹配a或b或c

       [a-zA-Z0-9]表示匹配a到z，或者A到Z，或者0到9

七、注意事项

1.1 在查询时，where关键字后不能使用别名作为查找条件，只能使用字段本身的名字，因为在where代码被执行时，列值可能还没终结。

1.2 注意count(*)和count(列名)的区别，count(*)查询的是总记录数，而count(列名)查询的是该列的总条数，如果该列有null值时，只返回非null记录的数量。所以有的时候count(*)和count(列名)数量不一致。

1.2 关键字like模糊匹配时不区分大小写。

例：select * fromt_student where stu_name like ‘%zhang%’

需要区分大小写时，使用关键字binary

select * fromt_student where stu_name like binary ‘%zhang%’

八、数据库的优化

       数据库的优化主要包含四方面：1.使用索引优化表；2.选择合适的列类型；3.优化sql查询；4.设定服务器参数。

     1.1 索引

      1.1.1 索引的作用

       索引通常被用来快速查找该字段对应的满足查询条件的特定的记录。如果没有索引时，mysql不得不从第一行开始读完整张表直到找到相关的记录。而使用索引后，mysql可以快速定位该条数据的位置，查询效率大幅度提升。

       索引对单张表的作用：例如需要查找学生表中年龄小于20的学生，针对年龄创建索引，创建出来的索引会被保存到索引文件中，搜索时，就不用全局扫描数据库，而是扫描该索引文件，而且索引文件是排序的，当查找到大于20的记录时，就不会再向下查找，避免了全局扫描。

       多列索引的作用：

案例1：例如这样一个sql语句: select *from t_name where col1 = ‘a’ and ‘col2’ = ‘b’，如果有一个多列索引建立在col1和col2上，那么就可以直接取出该数据。

      1.1.2 索引的特点         

       1）所有的列类型都能创建索引。

       2）一张表最多有16个索引，最大索引的长度    不超过256个字节。所以对于varchar/blob/text必须索引列的前缀。

       3）可以在多个列上创建索引，一个索引最多包含15个列。

       随着mysql的进一步开发，创建索引的约束会越来越小。

      1.1.3 索引的类型

       索引分为三种：1.index索引；2.uniqueindex索引；3.primary key索引。

       三种索引的不同点：index索引是普通索引，该索引的列可以包含重复的数据; unique index是唯一索引，被索引的列不能包含相同的数据; primary key索引，实际上是名为primary key的unique index索引，一张表只能有一个主键索引。

      1.1.4 创建索引

       创建索引有两种方式：

       1.创建普通索引：

1)    create index stu_name_index on`t_student`(`stu_name`);

2)    alter table `t_student` addindex stu_name_index(`stu_name `);

2.创建唯一索引

              1)  create unique index stu_name on`t_student`(`stu_name`);

              2)  alter table ` t_student ` add unique indexstu_name(`stu_name`);

       3.创建唯一索引，只有一种方式

              altertable t_student add PRIMARY KEY id_index (`id`);

 

 

1.1.5 删除索引

       1.删除普通索引和唯一索引时

alter table `t_student` drop index stu_name_index;

1.    删除主键索引时

alter table`t_student` drop primary key;

1.2数据类型的问题

1.1.1 使你的数据尽可能小

       最基本的优化之一是使你的数据（或索引）占用的磁盘空间或内存尽可能小，这样能有巨大的改进，因为磁盘读入较快。所以在创建表时，尽可能选择能满足需求的最小的数据类型。

      1.1.2 尽量将字段设置为notnull

       如果项目需求可以为null时，应该毫不犹豫的设置为可以为null。但是如果这个字段存储的值不能为null时，应该显示的生命该字段不能为null，这样处理更快，因为不需要考虑是否存在特例null。