函数，多表查询，外连接，约束，自增长，索引，事务，引擎，视图，管理

wtlzzz

已于 2023-03-14 12:46:26 修改

阅读量120

点赞数

文章标签： java 数据库开发语言

于 2023-03-09 21:41:58 首次发布

本文链接：https://blog.csdn.net/m0_68263308/article/details/129382702

版权

五.函数

1.统计函数

count， sum， avg， max/min

2.字符串函数

在这里插入图片描述

3.数学函数

说明：如果使用rand()每次返回不同的随机数（0~1.0）
如果使用rand(seed)返回随机数，如果seed(随便一个数字)不变，随机数不变

4.日期函数

在这里插入图片描述
说明：
①.DATE_ADD和DATE_SUB中的interval后面可以是year，minute，second，day等
②.DATEDIFF(date1,date2)得到的是天数，而且是date1-date2的天数，因此可以取负数
例子：

补充：
unix_timestamp()：返回的是1970-1-1到现在的秒数
FROM_UNIXTIME()：可以把一个unix_timestamp秒数，转成指定格式的日期

5.加密函数和系统函数

例子：
在这里插入图片描述

6.流程控制函数

六.多表查询

说明：多表查询是指基于两个和两个以上的表查询

1.笛卡尔集

在这里插入图片描述
注意：多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集
例子：

2.自连接

自连接是指在同一张表的连接查询（将一张表看作两张表）
例子：
在这里插入图片描述

3.子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

（1）.单行子查询

返回一行数据的子查询语句
例子：
在这里插入图片描述

（2）.多行子查询

返回多行数据的子查询，使用关键字in
例子：
在这里插入图片描述

（3）.临时表

子查询当作临时表使用 P777
all和any的使用：
all：

在这里插入图片描述
any：

（4）.多列子查询

查询返回多个列数据的子查询语句
例子：

补充：表.*表示把该表的所有列显示出来

4.表复制和去重

自我复制数据（蠕虫复制）
有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据
例子：
在这里插入图片描述
经典题：如何删除掉一张表重复数据

也可直接改名my_tab

5.合并查询

为了合并多个select语句的结果，可以使用集合操作符号union，union all

（1）.union all

该操作符用于取得两个结果集的并集，当使用该操作符时，不会取消重复行
在这里插入图片描述

（2）.union

与union all类似，但是会自动去掉结果中的重复行

七.外连接

1.左外连接：左侧的表完全显示
语法：select…from 表1 left join 表2 on条件【表1：左表，表2：右表】
2.右外连接：右侧的表完全显示
语法：select…from 表1 right join 表2 on条件

注意：绝大多数情况下，我们使用的是前面学的连接（内连接）

八.约束

1.基本介绍

约束用于确保数据库的数据满足特定的商业规则，在MySQL中，约束包括：not null , unique , primary key , foreign , key和check五种

2.primary key（主键）

字段名字段类型 primary key
用于唯一的标示表行的数据，当定义主键约束后，该列不能重复
例子：

在这里插入图片描述
第三条记录添加失败

细节说明：
（1）.primary key不能重复而且不能为null
（2）.一张表最多只能有一个主键，但可以是复合主键

id和name都相同时，才添加失败

（3）.主键的指定方式有两种：
1）.直接在字段名后指定：字段名 primary key
2）.在表定义最后写primary key（列名）
（4）.使用desc 表名，查看表的情况，也可以看到primary key的情况
（5）.在实际开发中，每个表往往都设计一个主键

3.unique（唯一）

not null（非空）：如果在列上定义了not null，那么当插入数据时，必须为列提供数据
unique（唯一）：当定义了唯一约束后，该列值是不能重复的
字段名字段类型 unique
细节说明：
（1）.如果没有指定not null ，则unique可以有多个null，如果一个字段（列）是unique not nuli使用效果类似于主键primary key
（2）.一张表可以有多个unique字段

4.foreign key（外键）

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

foreign key （本表字段名） references 主表名（主键名或unique字段名）
例子：

细节说明：
（1）.外键指向的表的字段，要求是primary key或者是unique
（2）.表的类型是innodb，这样的表才支持外键
（3）.外键字段的类型要和主键字段的类型一致（长度可以不同）
（4）.外键字段的值，必须在主键字段中出现过，或者为null（前提是外键字段允许为null）
（5）.一旦建立主外键的关系，数据不能随意删除了

5.check

用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000~2000之间，如果不在则会提示出错
Oracle和sql server均支持check，但是MySQL5.7目前还不支持check，只做语法校验，不会生效

九.自增长

1.基本介绍

在某张表中，存在一个id列（整数），我们希望在添加记录的时候，该列从1开始，自动的增长
语法：
字段名整型 primary key auto_increment

2.使用细节

（1）.一般来说自增长是和primary key配合使用的
（2）.自增长也可以单独使用但是需要配合一个unique
（3）.自增长修饰的字段为整数型（虽然小数也可以但是非常非常少这样用）
（4）.自增从默认从1开始，可以通过如下命令修改
alter table 表名 auto_increment = xxx
（5）.如果添加数据时，给自增长字段指定有值，则以指定的值为准，如果指定了自增从，一般来说，就按照自增长的规则来添加数据

十.索引

提高数据库性能——索引，不用加内存，不用改程序，不用调sql，查询速度就可能提高百倍千倍

1.索引机制

当没有索引时，select * from emp where id = 1进行全表扫描，查询速度慢
在这里插入图片描述
使用索引为什么会快？【形成了一个索引的数据结构，比如二叉树】
索引的代价：
①.磁盘占用
②.如果对表进行dml（修改update，删除delete，增加insert）会对索引进行维护，对速度有影响
在实际项目中，select操作基本占90%，而update，delete，insert只占10%左右（利大于弊）

2.索引的类型

（1）.主键索引：主键自动的为主索引（类型primary key）

（2）.唯一索引（UNIQUE）

（3）.普通索引（INDEX）
（4）.全文索引（FULLTEXT）适用于MyISAM
一般开发中不使用MySQL自带的全文索引，而是使用：全文搜索Solr和ElasticSearch（ES）

3.索引的使用

（1）.添加索引

如果某列的值是不会重复的，则优先考虑使用unique索引，否则使用普通索引
查询表是否有索引：SHOW INDEXES FROM 表名
在这里插入图片描述
或者直接在创建表时id INT PRIMARY KEY也是添加主键索引

（2）.删除索引

在这里插入图片描述
修改索引：先删除再添加新的索引

（3）.查询索引

在这里插入图片描述

（4）.小结

十一.事务

1.基本介绍

事务用于保证数据的一致性，它由一组相关的dml（增删改）语句组成，该组的dml语句要么全部成功，要么全部失败。
如：转账就要用事务处理，用以保证数据的一致性
事务和锁：当执行事务操作时（dml语句），MySQL会在表上加锁，防止其他用户改表的数据，这对用户来讲是非常重要的

2.事务操作

在这里插入图片描述
使用：

savepoint保存点：事务中的点，用于取消部分事务，当结束事务（commit）时，会自动地删除该事务所定义的所有保存点
rollback回退事务：当执行回退事务时，通过指定保存点可以回退到指定的点
commit提交事务：当执行了提交事务后，会确认事务的变化，结束事务，删除保存点，释放锁，数据生效。当使用commit语句结束事务之后，其他会话（其他连接）将可以查看到事务变化后的新数据（所有的数据就正式生效）

3.事务细节

（1）.如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚（rollback）
（2）.如果开始一个事务，没有创建保存点，可以执行rollback，默认就是回退到你事务开始的状态
（3）.也可以在这个事务中（还没有提交时），创建多个保存点，比如savepoint aaa；执行dml，savepoint bbb；
（4）.可以在事务没有提交前，选择回退到哪个保存点
（5）.MySQL的事务机制需要InnoDB的存储引擎，而MYISAM不支持
（6）.开始一个事务start transaction或set autocommit = off;

4.隔离级别

多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性
如果不考虑隔离性，可能会引发如下问题：
（1）.脏读dirty read：当一个事务读取另一个事务尚未提交（commit）的改变（update，delectable，insert）时，产生脏读
（2）.不可重复读nonrepeatable read：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读
（3）.幻读phantom read：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读
（2）（3）已提交（commit）
在这里插入图片描述
√可能出现，×不会出现

5.设置隔离级别

6.事务的ACID特性

十二.表类型和存储引擎

1.基本介绍

（1）.MySQL的表类型由存储引擎（Storage Engines）决定，主要包括MyISAM,innoDB,Memory等
（2）.MySQL数据表主要支持六种类型，分别是：CSV,Memory，ARCHIVE,MRG_MYISAM,MYISAM,InnoBDB
（3）.这六种又分为两类，一类是“事务安全型”transaction-safe，比如InnoDB；其余都属于第二类，称为“非事务安全型”non-transaction-safe，MYISAM和Memory