关系型数据库
关系型数据库最典型的数据结构就是表，由二维表及其之间的联系所组成的一个数据组织。可以采用结构化查询语言（SQL）对数据库进行操作。
优点：
- 易于维护：都是使用表结构，格式一致
- 使用方便：SQL语言通用，可用于复杂查询
- 复杂操作：支持SQL，可用于一个表及多个表之间非常复杂的查询
缺点：
- 读写性能比较差，尤其是海量数据的高效率读写
- 固定的表结构，灵活度稍欠
- 高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈
非关系型数据库
非关系型数据库也成为NoSQL数据库，是一种数据结构化存储方法的集合，可以是文档或者键值对等。
优点：
- 格式灵活：存储数据的格式可以是键值对形式、文档形式、图片形式等等，应用场景广泛。
- 速度快：NoSQL可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘
- 高扩展性
- 成本低：NoSQL数据库部署简单，基本都是开源软件
缺点：
- 不提供sql支持，学习和使用成本较高
- 无事务处理
- 数据结构相对复杂，复杂查询方面稍欠
基础操作
创建数据库
CREATE DATABASE 数据库名 DEFAULT CHARACTER SET 字符编码;删除数据库
drop database 数据库名称;选择数据库
use 数据库名;创建表
create table 表名(列名 类型,列名 类型......);
create table employees(employee_id int, employee_name varchar(10), salary float(8,2));删除表
drop table 表名;修改表名
alter table 旧表名 rename 新表名;修改列名
alter table 表名 change column 旧列名 新列名 类型;修改列类型
alter table 表名 modify 列名 新类型;添加新列
alter table 表名 add column 新列名 类型;删除指定列
alter table 表名 drop column 列名;MySQL中的约束
数据库约束是对表中的数据进行进一步的限制，保证数据的正确性、有效性和完整性。
主键约束（Primary Key）
主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。
外键约束（Foreign Key）
外键约束经常和主键约束一起使用，用起来确保数据的一致性
唯一性约束（Unique）
唯一性约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是唯一约束在一个表中可以有多个，并且设置唯一约束的列可以允许有空值。
非空约束（Not Null）
非空约束用来约束表中的字段不能为空
检查约束（Check）
检查约束也叫用户自定义约束，是用来检查数据表中字段值是否有效的一个手段，但目前MySQL数据库不支持检查约束。
添加主键约束
- 单一主键：使用一列作为主键列，当该列有重复时，则违反唯一约束
- 联合主键：使用多列作为主键列，当多列的值都相同时，则违反唯一约束
alter table 表名 add primary key(列名);主键自增长
MySQL中的自增长类型要求：
- 一个表中只能有一个列为自动增长
- 自动增长的列的类型必须是整数类型
- 自动增长只能添加到具备主键约束与唯一性约束的列上
- 删除主键约束或唯一性约束，如果该列拥有自动增长能力，则需要先去掉自动增长然后删除约束
alter table 表名 modify 主键 类型 auto_increment;删除主键
alter table 表名 drop primary key;如果主键有自增长
# 先去掉自增长
alter table emp modify emp_id int;
# 删除主键
alter table emp drop primary key;添加外键约束
alter table 表名 add constraint 约束名 foreign key(列名) references 参照的表名(参照的列名);删除外键约束
alter table 表名 drop foreign key 约束名;添加唯一性约束
alter table 表名 add constraint 约束名 unique(列名);删除唯一性约束
alter table 表名 drop key 约束名;添加非空约束
alter table 表名 modify 列名 类型 not null;删除非空约束
alter table 表名 modify 列名 类型 null;查询表中的约束信息
show keys from 表名;MySQL中的DML操作
添加数据
- 选择插入
insert into 表名(列名1, 列名2, 列名3,...) values(值1, 值2, 值3,...);- 完全插入
insert into 表名 values(值1, 值2, 值3,......);如果主键是自动增长，需要使用default或者null或者0占位
默认值处理
在MySQL中可以使用default为列设定一个默认值。如果在插入数据时并未指定该列的值，那么MySQL会将默认值添加到该列中。
创建表时指定列的默认值
create table 表名(列名 类型 default 默认值,...);修改表添加新列并指定默认值
alter table 表名 add column 列名 类型 default 默认值;插入数据时的默认值处理
如果插入数据时未指定该列的值，那么MySQL会将默认值添加到该列中。如果完全项插入需要使用default占位。
更新数据
更新语句中一定要给定更新条件，否则表中的所有数据都会被更新
update 表名 set 列名=值, 列名=值 where 条件;删除数据
在delete语句中，如果没有给定删除条件，则会删除表中的所有数据
delete from 表名 where 条件;清空表
truncate table 表名;清空表时delete与truncate的区别
- truncate是整体删除，速度较快，delete是逐条删除，速度较慢
- truncate不写服务器log，delete写服务器log，也就是truncate比delete效率高的原因
- truncate是会重置自增值的，相当于自增列会被置为初始列，又从1开始记录，而不是接着原来的值。而delete删除以后，自增值仍会继续增加。
查询数据
select基本查询
select语句的基本功能
- 列选择：能够使用select语句的列选择功能选择表中的列
- 行选择：能够使用select语句的行选择功能选择表中的行
- 连接：能够使用select语句的连接功能集合数据，这些数据被存储在不同的表中，在它们之间创建连接，查询我们关心的数据
MySQL中的别名
使用列别名
select 列名 as 列别名 from 表名 where 条件;使用表别名
select 表别名.列名 from 表名 as 表别名 where 条件;MySQL中去除重复
select默认显示所有的行，包括相同的行。在select语句中用DISTINCT关键字除去相同的行
select distinct 列名 from 表名;MySQL中的比较条件
| 运算 | 含义 |
|---|---|
| = | 等于 |
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| <> | 不等于 |
其他比较条件
| 操作 | 含义 |
|---|---|
| between...and... | 在两个值之间（包含） |
| in(set) | 匹配一个任意值列表 |
| like | 匹配一个字符模板 |
| is null | 是一个空值 |
like搜索条件既可以包含文字，也可以包含数字：
- %表示零个或多个字符
- _表示一个字符
逻辑条件
| 运算 | 含义 |
|---|---|
| AND | 都为真返回True |
| OR | 有一个为真或一个以上为真返回True |
| NOT | 都为假，返回True |
MySQL中的排序
order by排序
- asc：升序排序，默认
- desc：降序排序
order by子句在select语句的最后
SQL函数
函数的作用：
- 执行数据计算
- 修改单个数据项
- 操纵是输出进行分组
- 格式化显示的日期和数字
- 转换列数据类型
单行函数
- 操纵数据项
- 接受多个参数并返回一个值
- 作用于每一个返回行
- 每行返回一个结果
- 可以修改数据类型
- 可以嵌套
- 接受多个参数，参数可以是一个列或者一个表达式
单行函数包含：字符函数、数字函数、日期函数、转换函数、通用函数
字符函数
| 函数 | 描述 |
|---|---|
| lower(s) | lcase(s) | 将字符串s转换为小写 |
| upper(s) | ucase(s) | 将字符串s转换为大写 |
字符处理函数
| 函数 | 描述 |
|---|---|
| length(s) | 返回字符串s的长度 |
| concat(s1,s2,...,sn) | 将多个字符串合并为一个 |
| lpad(s1,len,s2) | 在字符串s1的开始处填充字符串s2，使字符串长度达到len |
| ltrim(s) | 去掉字符串s开始处的空格 |
| replace(s,s1,s2) | 用s2替换s中的s1 |
| reverse(s) | 字符串反转 |
| rpad(s1,len,s2) | 在s1结尾处添加字符串s2，使达到长度len |
| rtrim(s) | 去掉字符串s结尾处的空格 |
| substr(s,start,length) | 从字符串s的start位置截取长度为length的子串 |
| trim(s) | 去掉字符串s开始和结尾处的空格 |
数字函数
| 函数 | 描述 |
|---|---|
| abs(x) | 返回x的绝对值 |
| avg(expression) | 返回一个表达式的平均值 |
| ceil(x) | ceiling(x) | 向上取整 |
| count(experssion) | 返回查询的记录总数 |
| n div m | 整除。n为被除数，m为除数 |
| exp(x) | 返回e的x次方 |
| floor(x) | 向下取整 |
| greatest(expr1,expr2,expr3,...) | 返回列表中的最大值 |
| least(expr1,expr2,expr3,...) | 返回列表中的最小值 |
| max(expression) | 返回字段expression中的最大值 |
| min(expression) | 返回字符expression中的最小值 |
| mod(x,y) | 返回x除以y以后的余数 |
| rand() | 返回0到1的随机数 |
| round(x) | 返回离x最近的整数 |
| sign(x) | 返回x的符号（-1、0、1） |
| sqrt(x) | 返回x的平方根 |
| sum(expression) | 返回指定字段的总和 |
| truncate(x,y) | 返回数值x保留到小数点后y位的值 |
日期函数
MySQL中允许直接使用字符串表示日期，但是要求字符串的日期格式必须位YYYY-MM-DD HH:MI:SS或者YYYY/MM/DD HH:MI:SS
| 函数 | 描述 |
|---|---|
| curdate() | current_date() | 返回当前日期 |
| curtime() | current_time() | 返回当前时间 |
| date() | 从日期或者日期时间表达式中获取日期值 |
| datediff(d1,d2) | 计算日期d1，d2之间相隔的天数（d1-d2） |
| day(d) | dayofmonth(d) | 返回d的日部分 |
| dayname(d) | 返回d是星期几 |
| dayofweek(d) | 计算今天是星期几，1周日，2周一，... |
| dayofyear(d) | 计算d是今年的第几天 |
| hour(t) | 获取t中的小时值 |
| last_day(d) | 返回给定日期的那个月的最后一天 |
| monthname(d) | 返回日期当中的月份名称 |
| month(d) | 返回d中的月份值 |
| now() | 返回当前日期和时间 |
| second(t) | 返回t中的秒 |
| sysdate() | 返回当前日期和时间 |
| timediff(t1,t2) | 计算时间差 |
| week(d) | weekofyear(d) | 计算日期d是本年的第几个星期 |
| weekday(d) | 计算d是星期几，0表示星期一，1表示星期二... |
| year(d) | 返回年份 |
转换函数
隐式数据类型转换：MySQL服务器能够自动地进行类型转换，可以将标准的字符串日期自动转换为日期类型
显式数据类型转换：依赖转换函数来完成相关类型的转换
date_format(date, format)将日期转换成字符串
str_to_date(str, format)将字符串转换为日期
通用函数
| 函数 | 描述 |
|---|---|
| if(expr, v1, v2) | 如果expr成立，返回v1，否则v2 |
| ifnull(v1, v2) | 如果v1不为null，则返回v1，否则v2 |
| isnull(expression) | 判断表达式是否为null |
| nullif(expr1, expr2) | 比较两个参数是否相同，如果expr1于expr2相等返回null，否则返回expr1 |
| coalesce(expr1, expr2, expr3, ..., exprn) | 返回参数中的第一个非空表达式 |
case expression when condition1 then result1 when condition2 then result2 ... when conditionn then resultn else result end |
case标志函数开始，end结束。condition1成立则返回result，依次类推。全都不成立返回result |
多表查询
等值连接
两个表之间的某一列的值必须相等。等值连接也称简单连接或内连接。
- 多表等值连接的结果为多表的交集部分
- n表连接，至少需要n-1个连接条件
- 多表部分主次，没有顺序要求
- 一般为表起别名，提高阅读性和性能
- 可以搭配排序、分组、筛选...等子句使用
非等值连接
自连接
一个表连接它自己。
交叉连接
select * from table1 cross join table2;自然连接
连接只能发生在两个表中有相同名字和数据类型的列上。
select table1.column1, table2.column2 from table1 natural join table2;内连接（交集）
select 查询列表
from table1 别名
inner join table2
on 连接条件;左外连接
select 查询列表
from table1 left outer join table2
on 连接条件;右外连接
select 查询列表
from table1 right outer join table2
on 连接条件;全外连接
MySQL中不支持full outer join，但是可以使用union实现。union可以将两个查询结果合并，返回的行都是唯一的。union all只是将两个结果合并后就返回，会包含重复数据。
(select 投影列 from 表名 left outer join 表名 on 连接条件)
union
(select 投影列 from 表名 left outer join 表名 on 连接条件)聚合函数
avg、count、min、max
使用聚合函数的原则：
- distinct使得函数只考虑不重复的值
- 所有聚合函数忽略空值，为了用一个值代替空值，用ifnull或者coalesce函数
数据分组（group by）
select 数据列
from 表名
group by 列名;约束分组结果（having）
having子句是对查询出结果集分组后的结果进行过滤。
where子句和having子句的区别
where子句：
- 用于在数据从表中检索出来之前进行过滤
- 适用于对行进行过滤
- 不能用于聚合函数
having子句：
- 用于数据被分组后对分组进行过过滤
- 通常与group by子句一起使用
- 可以用于聚合函数
子查询
使用子查询相当于执行连个连续查询并且用第一个查询的结果作为第二个查询的搜索值。子查询（内查询）在主查询之前执行一次，子查询的结果被用于主查询（外查询）
单行子查询
仅返回一行，使用单行比较符（=、>、>=、<、<=、<>）。
多行子查询
返回多行，使用多行比较符（in、any、all）
any运算符比较一个值与一个子查询返回的每个值
- <any就是小于最大值
- >any就是大于最小值
- =any等同于in
all运算符比较一个值与一个子查询返回的每个值
- <all意思是小于最小值
- >all意思是大于最小值
MySQL中的索引
索引是对数据库表中的一列或多列进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。
索引的优点：
- 通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性
- 可以加快数据的检索速度
- 可以加速表与表之间的连接
- 在使用分组和排序进行检索时，可以减少查询中分组和排序的时间
索引的缺点：
- 创建索引和维护索引需要耗费时间，这种时间随着数据量的增加而增加
- 索引需要占用物理空间，数据量越大占用空间越大
- 会降低表的增删改的效率，因为每次增删改索引都需要进行动态维护
什么时候需要创建索引：
- 频繁作为查询条件的字段应该创建索引
- 查询中排序的字段船舰索引大大提高排序的速度
- 查询中统计或分组的字段
什么时候不需要索引：
- 频繁更新的字段不适合创建索引
- where条件里用不到的字段不创建索引
- 表记录太少不需要创建索引
- 经常增删改的表
- 数据重复且分布平均的字段
索引类型
普通索引
最基本的索引，没有任何限制。在创建索引时，可以指定索引长度，length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度，如果是blob和text类型，必须指定length。
查询索引
show index from table_name;直接创建索引
create index 索引名 on 表名(列(length));修改表添加索引
alter table table_name add index index_name(column(length));创建表时指定索引列
create table 表名(column 类型, primary key("列名"), index 索引名 (列(length)));删除索引
drop index 索引名 on 表名;唯一索引
索引列的值必须唯一，但允许有空值，如果是组合索引，则列的组合必须唯一。
创建唯一索引
create unique index 索引名 on 表名(列(length));修改表添加唯一索引
alter table 表名 add unique 索引名(列(length));创建表时指定唯一索引
create table 表名(列 类型, primary key(列), unique 索引名(列(length)));主键索引
特殊索引，唯一的标识一条记录，不能为空，一般用primary key来约束。
修改表添加主键索引
alter table 表名 add primary key(列名);创建表时指定主键索引
create table 表名(
列名 类型,
primary key(列)
);组合索引
多个字段建立索引，能够加速查询的速度。只有查询条件中使用了创建索引的第一个字段，索引才会被使用（最左前缀原则）
添加组合索引
alter table 表名 add index 索引名(列(length), 列(length));创建表时创建组合索引
create table 表名(
列名 类型,
index 索引名(列名(length),列名(length))
);DDL、DCL、TCL、DML
DDL（Data Definition Language）-数据定义语言：
DDL语句用于定义和管理数据库对象的结构，这些语句会自动提交
常见的DDL语句有：create、alter、drop、truncate
DCL（Data Control Language）-数据控制语言：
DCL语句用于控制对数据库的访问权限和安全性
常见的DCL语句有：
- grant：授予用户权限
- revoke：收回用户权限
TCL（Transaction Control Language）-事务控制语言
TCL语句用于管理事务。事务是一个或多个SQL语句的集合，这些语句作为一个单元执行，要么全部成功，要么全部失败。
常见的TCL语句有：
- start transaction或begin：开始一个事务
- commit：提交事务，保存所有更改
- rollback：回滚事务，撤销所有更改
- savepoint：设置事务中的保存点，可以回滚到该点
DML（Data Manipulation Language）-数据操作语言
DML语句用于查询和修改数据库中的数据。这些语句不会自动提交，需要显式提交或回滚
常见的DML：select、insert、update、delete
MySQL事务
事务是指作为单个逻辑工作单元执行的一系列操作，要么完美地执行，要么完全地不执行。
事务定义（Transaction）
- 事务是一个最小的不可再分的工作单元，通常一个事务对应一个完整的业务（例如银行账户转帐业务）
- 一个完整的业务需要批量的DML（insert、update、delete）语句联合完成
- 事务只和DML语句有关，或者说DML语句才有事务。
事务四大特征（ACID）
- 原子性（ATOMICITY）：事务中的操作要么都不做，要么就全做
- 一致性（CONSISTENCY）：一个事务应该保护所有定义在数据上的不变的属性。在完成一个成功的事务时，数据应该处于一致的状态
- 隔离性（ISOLATION）：一个事务的执行不能被其他事务干扰
- 持久性（DURABILITY）：一个事务一旦提交，它对数据库中数据的改变就应该是永久性的
事务类型
- 显式事务：需要手动提交或回滚。DML语言中的所有操作都是显式事务操作
- 隐式事务：数据库自动提交，同时也不具备回滚性，DDL、DCL语言都是隐式事务操作
使用事务
| TCL语句 | 描述 |
|---|---|
| start transaction | 事务开启 |
| commit | 事务提交 |
| rollback | 事务回滚 |
MySQL分页查询
分页查询原则：
- 在MySQL数据库中使用limit子句进行分页查询
- MySQL分页中开始位置为0
- 分页子句在查询语句的最后侧
limit子句
select 投影列 from 表名 where 条件 order by 列名 limit 开始位置, 查询数量;limit offset子句
select 投影列 from 表名 where 条件 order by 列名 limit 查询数量 offset 开始位置;
