mysql详细演示学习

Mysql学习

一、数据库分类

1.关系型数据库：

• Mysql，Oracle,sql Server,DB2.SQLite
• 通过表和表之间，行和列的关系进行数据的存储

2.非关系型数据库（NoSQL）No=NOTONLY

• Redis,MongDB
• 非关系型数据库，对象存储，通过对象的自身的属性来决定。

二、名词解释

• DBMS：数据库管理系统

• DDL（DataDefinitionLanguage）：数据定义语言，用来定义数据库对象：库、表、列等；

• DML（DataManipulationLanguage）：数据操作语言，用来定义数据库记录（数据

• DQL（DataQueryLanguage）：数据查询语言，用来查询记录（数据）；

• DCL（DataControlLanguage）：数据控制语言，用来定义访问权限和安全级别。

• 原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

三、数据库列的类型

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小的数据 3个字节
• int 标准的整数 4个字节（常用）
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节
• decimal 字符串形式的浮点数 金融计算的时候，一般是使用decimal

字符串

• char 字符串固定大小的 0~255
• varchar 可变字符串 0~65535
• tinytext 微型文本 2⁸-1
• text 文本串 2¹⁶-1

时间日期

• datetime 年月日时分秒 常用的时间格式
• timestrap 时间戳 1970.1.1到现在的毫秒数
• year 年份表示

四、数据库和表

• 创建和查看

crate database school--创建数据库
use school--使用数据库
create table if not exists `student`(
	`id` int(4) not null primary key auto_increment comment '学号',
    `name` varchar(30) not null default '匿名' comment '姓名',
    `pwd` varchar(20) not null  default '123456' comment '密码',
    `sex` varchar(2)  not null default '女' comment '性别',
    `birthday` datetime default null comment '出生日期',
    `address` varchar(100) default null comment '家庭地址',
    `email` varchar(50) default null comment '邮箱'
)engine=innodb default charset=utf8 --默认的字符集不支持中文
show create database school--查看建数据库的sql语句
show create table student--查看创建表的sql语句
desc student--显示表结构

• 数据库引擎类型

1.INNODB（默认使用）
2.MYISAM 早些年使用 

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持（5.6.4支持英文全文索引）
表空间的大小	较小	较大，约为前者的2倍

常用使用操作

• MYISAM 节约空间，速度较快
• INNODB 安全性较高，事务支持，，多表多用户操作

在物理空间存在的位置

所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库

本质还是文件的存储！

MySQL引擎在物理文件上的区别

• InnoDB 在数据表中只有一个*.frm文件，以及上级目录下的ibdata1文件

• MYISAM对应文件

  • *.frm 表结构的定义文件
  • *.MYD 数据文件
  • *.MYI 索引文件(index)

五、修改列以及增删改查操作

5.1、修改列

--   新增一列
alter table student add column ename varchar(255) null comment '学生英文名';
-- 修改字段的属性
alter table student modify column ename varchar(255) not null comment '学生英文名';
--  修改字段的属性或名字
alter table student change  ename  englishName varchar(255) not null comment '学生英文名';
--   删除字段
alter table student drop englishName;

5.2、增加数据

insert into studnet values(20200707,'刘德华','1234567890','男',str_to_date('1990-08-08','%Y-%m-%d'),'中国','7238@qq.com');--增加一个


insert into studnet values(20200707,'刘德华','1234567890','男',str_to_date('1990-08-08','%Y-%m-%d'),'中国','7238@qq.com'),
(20200708,'刘德华','23423677567','女',str_to_date('1985-11-12','%Y-%m-%d'),'中国','232352@qq.com'),
(20200709,'刘德华','1343255620','男',str_to_date('1980-06-15','%Y-%m-%d'),'中国','23522@qq.com'),
(20200710,'刘德华','1142141241','女',str_to_date('1988-04-15','%Y-%m-%d'),'中国','1234@qq.com')
;--增加多个

5.3、删除数据(delete、truncate)

• 不同
  • truncate 重新设置自增列 计数器会归零
  • truncate不会影响事务

delete from studnet--删除整个表的数据，不会影响自增
truncate table studnet --自增会归零
delete(truncate) from studnet where id=1 --删除id为1的数据

5.4、修改数据

update student  set name='刘一手' where id=20200709;--修改id为20200709的名字为刘一手
update student  set name='刘一手';--修改所有数据的名字为刘一手

5.5、查询数据

select * from student  where id=20200709;--查询表中id为20200709的数据
select * from student ;--查询表中所有的数据

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时(00-23)
%h	小时(01-12)
%I	小时(01-12)
%i	分钟，数值(00-59)
%j	年的天(001-366)
%k	小时(0-23)
%l	小时(1-12)
%M	月名
%m	月，数值(00-12)
%p	AM或PM
%r	时间，12-小时（hh:mm:ssAM或PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间,24-小时(hh:mm:ss)
%U	周(00-53)星期日是一周的第一天
%u	周(00-53)星期一是一周的第一天
%V	周(01-53)星期日是一周的第一天，与%X使用
%v	周(01-53)星期一是一周的第一天，与%x使用
%W	星期名
%w	周的天（0=星期日,6=星期六）
%X	年，其中的星期日是周的第一天，4位，与%V使用
%s	年，其中的星期一是周的第一天，4位，与%v使用
%Y	年，4位
%y	年，2位

​
​

六、聚合函数

6.1、查询计数

select count(`id`) from student;--Count(字段),会忽略所有的null值

select count(*) from student;--Count(*),不会忽略null值,本质计算行数
select count(1) from result;--Count(1),不会忽略所有的null值，本质计算行数

6.2、SUM:计算和

select sum('age') from student;--计算学生总年龄

6.3、AVG:平均值

select avg('age') from student;--计算学生年龄平均值

6.4、MAX:最大值

select max('age') from student;--计算学生年龄最大值

6.5、Min：最小值

select min('age') from student;--计算学生年龄最小值

6.6、str_to_date:字符串准换为时间类型

str_to_date('1990-08-08','%Y-%m-%d')

6.7.ABS():绝对值

6.8.PI()：圆周率

6.9.SQRT(x)：x的平凡根

6.10.MOD(x,y):x除的余数

6.11.CEIL(x)：向上取整

6.12.FLOOR(x)：向下取整

6.13.Round(x)：四舍五入

6.14.SIN(x)、ASIN(x)：前者返回x的正弦，其中x为给定的弧度值；后者返回x的反正弦值，x为正弦

6.15.COS(x)、ACOS(x)：前者返回x的余弦，其中x为给定的弧度值；后者返回x的反余弦值，x为余弦

6.16.CHAR_LENGTH(str)：计算字符串长度

6.17.CONCAT(s1,s2，…)：拼接多个字符串

6.18.INSERT(s1,x,len,s2)：返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符

6.19.LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str)：前两者将str中的字母全部转换成小写，后两者将字符串中的字母全部转换成大写

6.20.LTRIM(s)、RTRIM(s)：前者返回字符串s，其左边所有空格被删除；后者返回字符串s，其右边所有空格被删除

6.21.TRIM(s)：返回字符串s删除了两边空格之后的字符串

6.22.REPLACE(s,s1,s2)：返回一个字符串，用字符串s2替代字符串s中所有的字符串s1

6.23.SUBSTRING(s,n,len)：从字符串s中返回一个第n个字符开始、长度为len的字符串

6.24.REVERSE(s)：将字符串s反转

6.25.CURDATE()、CURRENT_DATE()：将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具体格式根据函数用在字符串或是数字语境中而定

6.26.CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()：这四个函数作用相同，返回当前日期和时间值，格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定

6.27.MONTH(date)：返回指定日期中的月份

6.28.WEEK(d)：计算日期d是一年中的第几周

6.29.YEAR(date)：回指定日期对应的年份

6.30.MD5:加密函数

七、索引

Mysql官方索引的定义为：索引（index）是帮助Mysql高效是帮助MySql高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构

7.1索引的分类

• 主键索引（primary key）

  • 唯一的标识、主键不可重复、只能有一个列作为主键

• 唯一索引（unique key）

  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识位

• 常规索引（key/index）

  • 默认的，index,key关键字来设置

• 全文索引

  • 在特定的数据库引擎才有，如：mylsam
  • 快速定位数据

show idnex from student --显示所有的索引信息
alter table school.student add fulltext index `studentName`(`studentName`)--给student的studentName添加一个全文索引
select * from student where match(studentName) against('刘德华')--

八、事务的隔离级别

8.1、数据库事务隔离级别（脏读、幻读、不可重复读）

8.2、事务的ACID原则：

• 原子性：事务是一个原子操作，由一系列动作组成。事务的原子性确保动作要么全部完成，要么完全不起作用。
• 一致性：一旦事务完成（不管成功还是失败），系统必须确保它所建模的业务处于一致的状态，而不会是部分完成部分失败。在现实中的数据不应该被破坏。
• 隔离性：可能有许多事务会同时处理相同的数据，因此每个事务都应该与其他事务隔离开来，防止数据损坏。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性：一旦事务完成，无论系统发生什么问题，它的结果都不应该受到影响，事务的结果被写到持久化存储器中。

8.3、事务隔离级别： 隔离级别是指若干个并发的事务之间的隔离程度。

DEFAULT：这是默认值，表示使用底层数据库的默认隔离级别。对大部分数据库而言，通常这值就是READ_COMMITTED。

READ_UNCOMMITTED（读未提交）：该隔离级别表示一个事务可以读取另一个事务修改但还没有提交的数据。该级别不能防止脏读，不可重复读和幻读，因此很少使用该隔离级别。

READ_COMMITTED（读提交）：该隔离级别表示一个事务只能读取另一个事务已经提交的数据。该级别可以防止脏读，这也是大多数情况下的推荐值。

REPEATABLE_READ（可重复读）：该隔离级别表示一个事务在整个过程中可以多次重复执行某个查询，并且每次返回的记录都相同。该级别可以防止脏读和不可重复读。

SERIALIZABLE（串行化）：所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。
事务传播行为：所谓事务的传播行为是指，如果在开始当前事务之前，一个事务上下文已经存在，此时有若干选项可以指定一个事务性方法的执行行为。在TransactionDefinition定义中包括了如下几个表示传播行为的常量：

8.4、事务的传播行为

1. REQUIRED：如果当前存在事务，则加入该事务；如果当前没有事务，则创建一个新的事务。这是默认值。
2. REQUIRES_NEW：创建一个新的事务，如果当前存在事务，则把当前事务挂起。
3. SUPPORTS：如果当前存在事务，则加入该事务；如果当前没有事务，则以非事务的方式继续运行。
4. NOT_SUPPORTED：以非事务方式运行，如果当前存在事务，则把当前事务挂起。
5. NEVER：以非事务方式运行，如果当前存在事务，则抛出异常。
6. MANDATORY：如果当前存在事务，则加入该事务；如果当前没有事务，则抛出异常。
7. NESTED：如果当前存在事务，则创建一个事务作为当前事务的嵌套事务来运行；如果当前没有事务，则该取值等价于REQUIRED。