MySQL回顾-约束、常用函数和视图

约束

  • 主键约束:不允许重复记录,避免数据冗余
  • 外键约束:保证本事务所关联的其他事务是存在的(主键表中的这个字段)
    • 实际开发中通常不使用外键约束,影响效率。
  • check约束:限制值在某一个范围之内
  • default约束: 确定默认值( 可以更改) 保证事务的某个属性一定会有一个值
  • unique约束:唯一键,不可重复,但允许为空
    • unique 和 not null 可以组合使用,顺序任意。
    • 主键和唯一键的关系:不要用业务逻辑字段当做主键,应添加一个没有任何实际意义的字段(代理主键)当做主键。
  • not null约束 要求用户必须为该字段赋一个值,否则出错

MySQL常用函数

文本处理函数

  • LEFT(x,len) 返回串左边指定长度的字串(长度为 len)
  • RIGHT(x,len) 返回串右边指定长度的字串(长度为 len)
  • LENGTH(x)/CHAR_LENGTH(str) – 返回串的长度
  • LOCATE(x,sub_x) – 找出串的一个子串
  • SUBSTRING(x, from, to) 返回子字符串
  • LOWER(x) 全部转小写
  • UPPER(x) 全部转大写
  • LTRIM(x) 左边所有的空格被删除
  • RTRIM(x) 右边所有的空格被删除
  • TRIM(x) 删除两边的空格
  • CONCAT(s1,s2…sn) 字符串 s1,s2 等多个字符串合并为一个字符串

日期时间处理函数

  • CURDATE()/CURRENT_DATE() 返回当前日期,格式2019-02-19
  • CURTIME()/CURRENT_TIME() 返回当前时间,格式11:40:45
  • CURRENT_TIMESTAMP() 返回当前日期和时间,格式2019-02-19 11:41:32
  • ADDDATE(d,n) 计算起始日期 d 加上 n 天的日期
  • ADDTIME(t,n) 时间 t 加上 n 秒的时间
  • DATE(str) 从日期或日期时间表达式中提取日期值
  • DAY(d) 返回日期值 d 的日期部分,也可以使用EXTRACT(type FROM d) type可以取MONTH,DAY,HOUR,MINUTE等
  • DATEDIFF(d1,d2) 计算日期d1->d2之间相隔的天数
  • DATE_FORMAT(d,f) DATE_FORMAT按表达式 f的要求显示日期 d
  • DAYOFMONTH(d) 计算日期 d 是本月的第几天
  • DAYOFWEEK(d) 日期 d 今天是星期几,1 星期日,2 星期一,以此类推
  • DAYOFYEAR(d) 计算日期 d 是本年的第几天
  • UNIX_TIMESTAMP() 得到时间戳
  • FROM_UNIXTIME() 时间戳转日期

数值处理函数

  • ABS(x) 返回绝对值
  • AVG(expression) 返回一个表达式的平均值,expression是一个字段
  • EXP(x) 返回e的x次方
  • CEIL(x)/FLOOR(x) 向上取整、向下取整
  • MOD()(取余)
  • PI() 圆周率
  • RAND() 返回0到1的随机数
  • SQRT(x) 返回x的平方根
  • ROUND(x) 返回离 x 最近的整数

视图

视图是虚拟的表,与包含数据的表不同,视图只包含使用时动态检索数据的查询,主要是用于查询。

为什么使用视图

  • 重用 sql 语句
  • 简化复杂的 sql 操作,在编写查询后,可以方便地重用它而不必知道他的基本查询细节。
  • 使用表的组成部分而不是整个表。
  • 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
  • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

注意:

  • 在视图创建之后,可以用与表基本相同的方式利用它们。可以对视图执行 select 操作,过滤和排序数据,将视图联结到其他视图或表,甚至能添加和更新数据。
  • 重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据,因此它们返回的数据时从其他表中检索出来的。在添加和更改这些表中的数据时,视图将返回改变过的数据。
  • 因为视图不包含数据,所以每次使用视图时,都必须处理查询执行时所需的任一检索。如果你使用多个连接和过滤创建了复杂的视图或者嵌套了视图,可能会发现性能下降得很厉害。因此,在部署使用了大量视图的应用前,应该进行测试。

视图的规则和限制

  • 与表一样,视图必须唯一命名;
  • 可以创建任意多的视图;
  • 为了创建视图,必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
  • 视图可以嵌套,可以利用从其他视图中检索数据的查询来构造一个视图。
  • Order by 可以在视图中使用,但如果从该视图检索数据 select 中也是含有 order by,那么该视图的 order by 将被覆盖。
  • 视图不能索引,也不能有关联的触发器或默认值
  • 视图可以和表一起使用

视图操作的SQL语句

  • 利用 create view view_name as 查询语句 语句来进行创建视图
  • 使用 show create view view_name 来查看创建视图的语句
  • drop view view_name 来删除视图
  • 更新视图可以先 drop 再 create,也可以使用 create or replace view_name as ...

视图的更新

视图是否可以更新,要视情况而定。

通常情况下视图是可以更新的,可以对他们进行 insert,update 和 delete。更新视图就是更新其基表(视图本身没有数据)。如果你对视图进行增加或者删除行,实际上就是对基表进行增加或者删除行。

但是,如果 MySQL 不能正确的确定更新的基表数据,则不允许更新(包括插入和删除),这
就意味着视图中如果存在以下操作则不能对视图进行更新:

  1. 分组(使用 group by 和having )
  2. 联结
  3. 子查询
  4. 聚集函数
  5. dictinct
  6. 导出(计算)列

不推荐更改视图,会使得业务逻辑混淆。

原创文章,作者:彭晨涛,如若转载,请注明出处:https://www.codetool.top/article/mysql%e5%9b%9e%e9%a1%be-%e7%ba%a6%e6%9d%9f%e3%80%81%e5%b8%b8%e7%94%a8%e5%87%bd%e6%95%b0%e5%92%8c%e8%a7%86%e5%9b%be/

发表评论

电子邮件地址不会被公开。