MySQL

数据库

Database，简称DB。按照一定的数据结构来组织、存储和管理数据的仓库。

SQL

SQL（Structured Query Language），结构化查询语言。是一种操作关系型数据库的编程语言，定义了一套操作关系型数据库统一SQL标准。

关系型数据库

关系型数据库： 由多张相互连接的二维表组成的数据库，就像Excel一样，由行和列组成的二维表。
特点：

• 使用表存储数据，格式统一，便于维护
• 使用SQL语句操作，标准统一，使用方便
• 数据存储在磁盘中，安全
  代表：
• Mysql
• Oracle

Mysql概述

MySQL是一种广泛使用的开源关系型数据库管理系统(RDBMS)，它是最流行的开源数据库之一。MySQL由瑞典的MySQL AB公司开发，后来被sun收购，最终被Oracle收购。MySQL以其开源免费、简单易用、高性能和可靠性而闻名。
Oracle收费，性能稳定。

数据库操作

连接数据库

1. 打开navicate
2. 点击连接
3. 选择Mysql
4. 填写数据库连接信息
5. 确认连接（连接前也可以测试连接）

创建数据库

1. 选择数据库
2. 右键选择新建数据库
3. 填写数据库名、字符集、排序规则

创建表

数据类型

• 数字

类型	大小	描述
tinyint	1 byte	小整数值
int或integer	4 bytes	整数值
float	4 bytes	单精度浮点数值
decimal

• 字符串

类型	大小	描述
char	0-255 bytes	定长字符串
varchar	0-65535 bytes	变长字符串
text	0-65535 bytes	长文本数据

• 日期类型

类型	大小	描述
date	YYYY-MM-DD	日期值
time	HH:MM:SS	时间值或持续时间
year	YYYY	年份值
datetime	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

CHAR和VARCHAR的区别：

• char 不论你实际存储了多少的字符，都会占用设置的n个字符空间 ， 比如手机号码，性别，身份证号等
• varchar 它存储的长度并不固定， 实际使用多少就占用多少

DATETIME和TIMESTAMP区别：

• TIMESTAMP： 1970-01-01 00:00:01.000000’到 2038-01-19 03:14:07.999999。
• DATETIME： 1000-01-01 00:00:00.000000到 9999-12-31 23:59:59.999999。
  对于跨时区的业务，TIMESTAMP更为合适。

创建表

• 打开数据库，右键表–新建表
• 填写字段名，类型，长度等信息

删除表

• 直接将表删除

SQL通用语法

• SQL语句可以单行或多行书写，以分号结尾。
• SQL语句可以使用空格/缩进来增强语句的可读性
• 注释
  • 单行注释：-- 注释内容 或 # 注释内容
  • 多行注释： /* 注释内容 */

表操作

添加数据 insert into

1. 给指定字段添加数据
   insert into 表名 (字段1,字段2…) values (值1,值2…);
2. 给全部字段添加数据
   insert into 表名 values (值1,值2…);
3. 批量添加数据
   insert into 表名 (字段1,字段2…) values (值1,值2…),(值1,值2…),(值1,值2…);
   insert into 表名 values (值1,值2…),(值1,值2…),(值1,值2…);
   修改数据 update
   update 表名 set 字段1=值1,字段2=值2,… where 条件;
   删除数据 delete
   delete from 表名 where 条件;
   查询数据 delete
   select * from 表名 where 条件;

条件

sql语句中，where后面跟条件

• 运算符
  =、！=、<>、<、<=、>、>=
• 关键字
  between…and、in(set)、is null、and、or、not

mysql查询

基本查询

select 字段列表 from 表名

条件查询

1. 用where表示查询的条件
   select 字段列表 from 表名 where 条件
2. %匹配0或多个字符，一般不用左模糊(%放在左边，导致索引失效，降低查询效率)
   select 字段列表 from 表名 where 字段名 like ‘%字’
   select 字段列表 from 表名 where 字段名 like ‘字%’
   select 字段列表 from 表名 where 字段名 like ‘%字%’
3. _代表匹配一个字符
   select 字段列表 from 表名 where 字段 like ‘字’
   select 字段列表 from 表名 where 字段 like '字’
   select 字段列表 from 表名 where 字段 like '字’

分页查询

语法：limit 起始索引，个数
分页查询第m页，每页展示n条数据 limit (m-1)*n，n
select 字段列表 from 表名 limit 0,5
select 字段列表 from 表名 limit 5,5
select 字段列表 from 表名 limit 10,5

排序

1. ASC升序
   select 字段列表 from 表名 order by 字段名 ASC;
2. DESC降序
   select 字段列表 from 表名 order by 字段名 DESC;

聚合函数

1. count()：查询共有多少条数据
   select count(0) from 表名
2. sum()：查询某一字段的总和
   select sum(字段名) from 表名
3. avg()：查询某一字段的平均值
   select avg(字段名) from 表名
4. max()：查询某一字段的最大值
   select min(字段名) from 表名
5. min()：查询某一字段的最小值
   select max(字段名) from 表名

分组查询

1. 分组查询
   select 字段列表 from 表名 group by 字段名;
2. having条件查询 where分组前,having分组后的条件
   select 字段列表 from 表名 where 字段条件 group by 字段名 having 聚合函数;

子查询

把查询到的结果当作另一个查询的条件
select 字段列表 from 表名 where 字段 in (select 字段名 from 表名 where 字段名=’值‘)

内连接

1. 笛卡尔积 查询出来的结果没有意义
   select * from 表名一，表名二
2. 显示内连接 inner可以省略
   • select * from 表名一 inner join 表名二 on 表名一.字段名=表名二.字段名
   • select * from 表名一 join 表名二 on 表名一.字段名=表名二.字段名
   • select 表名一字段列表，表名二字段列表 from 表名一 join 表名二 on 表名一.字段名=表名二.字段名
3. 隐式内连接
   select * from 表名一，表名二 where 表名一.字段名=表名二.字段名

注意： 也可以给表名起别名，如果两个表中有相同的字段名称，那么需要用别名+点号来区别，否则报错。

外连接

1. 左外连接 left [outer] join 查询的是左边表的所有部分和右边表的交集
   select 别名.字段名,别名.字段名,别名 from 表名一 别名 left outer join 表名二 别名 on 表一别名.字段名=表二别名.字段名
2. 右外连接 right [outer] join 查询的是右边表的所有部分和左边表的交集
   select 别名.字段名,别名.字段名,别名 from 表名一 别名 right outer join 表名二 别名 on 表一别名.字段名=表二别名.字段名

左外连接和右外连接的区别

左外连接是以左边的表为主表，查询的结果只关注左表，不在乎右表。

索引和函数

概念

索引，使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。 在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

优点和缺点

优点：

1. 创建唯一性索引，保证数据库表中每一行数据的唯一性
2. 大大加快数据的检索速度，这也是创建索引的最主要的原因
3. 减少磁盘IO（向字典一样可以直接定位）

缺点：

1. 索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
2. 索引需要占用额外的物理空间
3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

索引类型

• 普通（Normal）：也叫非唯一索引，是最普通的索引，没有任何的限制。
• 唯一（Unique）：唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用 primay key创建。
• 全文（Fulltext）：针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数 据的这种情况，如果要解决like 查询效率低的问题，可以创建全文索引。只有文本类型 的字段才可以创建 全文索引，比如 char、varchar、text。
• 聚合索引：由多个字段共同组成的索引。

索引优化

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描
2. 尽量避免使用in 和not in，会导致引擎走全表扫描
3. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描
4. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描
5. 尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描
6. 当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描
7. 查询条件不能用 < > 或者 !=
8. where条件仅包含复合索引非前置列
9. 隐式类型转换造成不使用索引

事务

事务的四大特征：ACID

• 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
• 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
• 隔离性：多个事务之间。相互独立。
• 一致性：事务操作前后，数据总量不变。

函数

字符串

函数	功能
concat(s1,s2,…sn)	字符串拼接，将 s1，s2 ，sn 拼接成一个字符串
lower(str)	将字符串全部转为小写
upper(str)	将字符串全部转为大写
lpad(str,n,pad)	左填充，用字符串pad对的左边进行填充，达到n个字符串长度
rpad(str,n,pad)	右填充，用字符串pad对的右边进行填充，达到n个字符串长度
trim(str)	去掉字符串头部和尾部的空格
substring(str,start,len)	返回从字符串 str从start位置起的len个长度

数值函数

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回 x/y的模
rand()	返回 0~1内的随机数
round(x,y)	求参数 的四舍五入的值，保留y位小数

日期函数

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定 date的年份
month(date)	获取指定 date的月份
day(date)	获取指定 date的日期
date_add(date,interval expr type)	返回一个日期时间值加上一个时间间隔
datediff(date1,date2)	返回时间date1和时间date2之间的天数