MySQL一一sql的视图、索引、约束

Wesley13
• 阅读 487

一、视图

本质上相当于一张**“虚拟表”**,可当作独立的一张表进行操作(增、删、改、查)

**      作用:**

**       a)**可通过权限控制,只将“表中的少数列”暴露给数据库用户,而不让该用户直接操纵数据库中“实际表”

**       b)**可将常用的,较复杂的SQL在数据库中预先定义好,使得外部调用不用每次都编写复杂的SQL语句,直

接当作一张“虚拟表”来调用即可 等等,听说你们都很喜欢我那给个小心心00,为了表扬你们,戳这里有你们想要的完整zl

Customers表中原始数据:

MySQL一一sql的视图、索引、约束

Orders表中的原始数据:

MySQL一一sql的视图、索引、约束

创建“查找运费在40到60之间的客户信息”的视图:

 1 use edisondb; 2 
 3  if  object_id('FortyToSixtyFreightCusts')is  not  null  
4  drop  view FortyToSixtyFreightCusts 
5  go  
6  create  view FortyToSixtyFreightCusts 
7  as  
8 
 9  select C.custid as  '客户ID',C.name as  '客户名', C.age as  '年龄'  
10  from customers as C 
11  where  Exists( 
12  select  *  
13  from Orders as O 
14  where C.custid=O.custid and (O.freight between  40  and  60) 
15 );

创建成功之后:

MySQL一一sql的视图、索引、约束

将该视图当作一张“独立的表”进行查询操作:

1 use edisondb; 2  select 客户ID,客户名,年龄 from FortyToSixtyFreightCusts;

   执行结果如下:

MySQL一一sql的视图、索引、约束

   关于SCHEMABINDING选项的一些说明:

**         作用:使得视图中引用的对象不能被删除,被引用的列不能被删除或者修改(防止:由于引用的列等被删除,造成视图无法使用的情况)**

 修改视图,使其指定SCHEMABINDING选项:

1 alter  view FortyToSixtyFreightCusts with schemabinding
2  as  
3 
4  select C.custid as  '客户ID',C.name as  '客户名', C.age as  '年龄'  
5  from dbo.customers as C 
6  where  Exists(
7  select O.custid 
8  from dbo.Orders as O 
9  where C.custid=O.custid and (O.freight between  40  and  60) 
10 ); 
11  
go

(以上表名,一定要以“dbo.”的形式出现,否则会出现:名称 'customers' 对于架构绑定无效的错误)*

尝试删除Customers表中的age列:     

1 use edisondb; 2 alter  table customers drop  column age;

执行结果:   

**MySQL一一sql的视图、索引、约束

**

**    附:可通过执行以下语句查看SQL Server中某对象的定义:      **

1 exec sp_helptext 'dbo.FortyToSixtyFreightCusts';

执行结果如下:

MySQL一一sql的视图、索引、约束

二、约束

**   1)检查约束【通常认为的“约束”】**

创建检查约束:

1 use edisondb; 2  alter  table staffinfo 3  add  constraint ck_StaffID check(StaffID between  5000  and  5999)

成功创建之后:

MySQL一一sql的视图、索引、约束

此时执行非法的插入行:

1 use edisondb; 2  insert  into StaffInfo(StaffID,StaffName,Department) 3  values(6000,'Wade','Dev');

执行结果为:

MySQL一一sql的视图、索引、约束

**   2)唯一性约束**

 StaffInfo表原始数据:

MySQL一一sql的视图、索引、约束

创建唯一性约束:

1 use edisondb; 2  alter  table staffinfo 3  add  constraint uq_StaffName unique(StaffName);

成功创建后:

MySQL一一sql的视图、索引、约束

 ** 注:**唯一性约束创建成功后,是在“键”中显示,而非“约束”中

 此时执行非法的插入行:

1 use edisondb; 2  insert  into StaffInfo(StaffID,StaffName,Department) 3  values(5003,'keven','Dev');

执行结果为:

MySQL一一sql的视图、索引、约束

**说明:**要使某列的值唯一,既可以通过主键来实现,也可以通过“唯一性约束”来实现

   3)默认约束

创建默认约束:

1 use edisondb; 2  alter  table staffinfo 3  add  constraint df_Department default('部门待定') for Department;

成功创建后:

MySQL一一sql的视图、索引、约束

执行行插入:

1 use edisondb; 2  insert  into StaffInfo(StaffID,StaffName) 3  values(5003,'Murphy');

   执行结果为:

MySQL一一sql的视图、索引、约束

  **注:**主键和外键也属于一种约束

三、索引

**  1.聚集索引**

对应于数据库中数据文件的物理存储方式,每张表只能建立一个

**       适用场合:**a)select次数远大于insert、update的次数(insert、update时需要移动其他数据文件的物理位置)                       b)建立聚合索引的列,既不能绝大多数都相同,又不能只有极少数相同(可从类似二维数组查找时间复杂的方式去理解)

创建一个NewOrders表,用于对索引的测试:

1 use edisondb; 2  3 create  table NewOrders 4 ( orderID numeric(18, 0) identity(1,1) not  null, 5   custID numeric(18, 0) not  null, 6   empID numeric(18, 0) not  null, 7 tradeDate datetime  not  null  8 );

在NewOrders表中插入10万条测试数据:

 1 use edisondb; 
2 set nocount on  
3 declare  @i numeric(18,0) 
 4 declare  @custid numeric(18,0)
 5 declare  @empid numeric(18,0)
 6 declare  @tradeDateTime  datetime  
7 begin  
8  set  @i=0  
9  set  @custid=100000  
10  set  @empid=500000  
11  set  @tradeDateTime=getdate() 
12  while  @i<100000  
13  begin  
14  insert  into neworders(custID,empID,tradeDate) 
15  values(@custid,@empid,@tradeDateTime) 
16  set  @i=@i+1  
17  set  @custid=@custid+1  
18  set  @empid=@empid+1  
19  if (@i%1000)=0  
20  set  @tradeDateTime=dateadd(day,1,@tradeDateTime) 
21  end  
22 end  
23 print  'Insert data over'

插入数据成功之后,NewOrders表中的部分数据如下:

MySQL一一sql的视图、索引、约束

进行查询测试:

1 use edisondb; 2 declare  @startDT  datetime  3 set  @startDT=getdate() 4 select  *  from neworders where tradedate>  dateadd(day,10,'2011-9-20') 5 print  '耗时:'+replace(str(datediff(ms,@startDT,getdate())),'  ','')+' 毫秒'

执行结果为:

MySQL一一sql的视图、索引、约束

现在对建立“聚集索引”的表进行测试

删除表中所有行:

1use edisondb 2truncate  table neworders;

在NewOrders表的tradeDate列上创建“聚集索引”

1use edisondb 2create  clustered  index tradeDate_NewOrders on NewOrders(tradeDate)

执行结果为:

MySQL一一sql的视图、索引、约束

再次插入10万行数据后,进行测试,结果为:

MySQL一一sql的视图、索引、约束

 **聚合索引使用的关键:**在合适的列建立(通常为:最多用作查询条件的列)

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
1星期前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Easter79 Easter79
2年前
sql注入
反引号是个比较特别的字符,下面记录下怎么利用0x00SQL注入反引号可利用在分隔符及注释作用,不过使用范围只于表名、数据库名、字段名、起别名这些场景,下面具体说下1)表名payload:select\from\users\whereuser\_id1limit0,1;!(https://o
Stella981 Stella981
2年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
6个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这