首页
网站首页
公司简介
资讯中心
推荐内容
返回顶部
Server排名函数,7安装指南
发布时间:2019-12-28 20:40
浏览次数:

目录

-- 创建聚集索引
create table [dbo].[pub_stocktest] add  constraint [pk_pub_stocktest] primary key clustered 
(
[sid] asc
)with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, 
online = off, allow_row_locks = on, allow_page_locks = on) on [primary]

-- 创建非聚集索引
 create nonclustered index [ix_model] on [dbo].[pub_stocktest]
(
    [model] asc
)
include (     [name]) with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, 
online = off, allow_row_locks = on, allow_page_locks = on, FILLFACTOR = 85) on [primary]

最近在MySQL中遇到分组排序查询时,突然发现MySQL中没有row_number() over(partition by colname)这样的分组排序。
并且由于MySQL中没有类似于SQL Server中的row_number()、rank()、dense_rank()等排名函数,所有找到以下实现方法,在此简单记录一下。

1.下载

1.1 Filefactor参数

 

1)进入官网下载5.7.23压缩包

下载地址:https://dev.mysql.com/downloads/mysql/5.7.html#downloads

图片 1

图片 2

1.事务

事务在SQL Server中相当于一个工作单元,可以确保同时发生的行为与数据的有效性不发生冲突,并且维护数据的完整性。在实际应用中,多个用户在同一时刻对同一部分数据进行操作时,可能会由于一个用户的操作使其他用户的操作和数据失效。事务可以很好地解决这一点。事务总是确保数据库的完整性。

  使用Filefactor可以对索引的每个叶子分页存储保留一些空间。对于聚集索引,叶级别包含了数据,使用Filefactor来控制表的保留空间,通过预留的空间,避免了新的数据按顺序插入时,需腾出空位而进行分页分隔。
  Filefactor设置生效注意,只有在创建索引时才会根据已经存在的数据决定预留的空间大小,如里需要可以alter index重建索引并重置原来指定的Filefactor值。
  在创建索引时,如果不指定Filefactor,就采用默认值0 也就是填充满,可通过sp_configure 来配置全局实例。Filefactor也只就用于叶子级分页上。如果要在中间层控制索引分页,可以通过指定pad_index选项来实现.该选择会通知到索引上所有层次使用相同的Filefactor。Pad_index也只有索引在新建或重建时有用。

首先创建一个表并插入测试数据。

2.安装与配置

1.1.事务的ACID属性

  • 原子性(Atomicity):事务是工作单元。事务内的所有工作要不全部完成,要不全部没完成,不存在完成一部分的说法。
  • 一致性(Consistency):事务完成时,所有的数据都必须是一致的。事务结束时,所有内部数据结构都必须是正确的。
  • 隔离性(Isolation):由并发事务所做的修改必须与其他并发事务所做的修改隔离。事务识别数据时数据所处的状态,要不是另一并发事务修改前的状态,要不是另一并发事务修改后的状态,不存在中间状态。
  • 持久性(Durability):事务提交后,事务所完成的工作结果会得到永久保存。

示例1:情况如下2个代码

--语句1:
UPDATE student
SET stu_birthday='1993-02-01',
stu_native_place='山西',
stu_phone='15729810290'
WHERE stu_no='20180101'
--语句2:
UPDATE student
SET stu_birthday='1993-02-01'
WHERE stu_no='20180101'
UPDATE student
SET stu_native_place='山西'
WHERE stu_no='20180101'
UPDATE student
SET stu_phone='15729810290'
WHERE stu_no='20180101'

在语句1中,只有一个事务,对列的更新要不全部成功更新,要不全部更新失败。而语句2中,有三个事务,就算其中有某个列更新失败,也不会影响其他列的更新。

1.2 Drop_existing 参数

create table demo.Student (
   ID int(11) NOT NULL AUTO_INCREMENT,
   StuNo varchar(32) NOT NULL,
   StuName varchar(10) NOT NULL,
   StuAge int(11) DEFAULT NULL,
   PRIMARY KEY (ID)
 )
 engine=innodb auto_increment=1 default charset=utf8 collate=utf8_general_ci;

insert into demo.Student(StuNo,StuName,StuAge) values('A001','小明',22);
insert into demo.Student(StuNo,StuName,StuAge) values('A005','小李',23);
insert into demo.Student(StuNo,StuName,StuAge) values('A007','小红',24);
insert into demo.Student(StuNo,StuName,StuAge) values('A003','小明',22);
insert into demo.Student(StuNo,StuName,StuAge) values('A002','小李',23);
insert into demo.Student(StuNo,StuName,StuAge) values('A004','小红',24);
insert into demo.Student(StuNo,StuName,StuAge) values('A006','小王',25);

select * from demo.Student;

1)将下载的压缩包解压到任意目录,如:

图片 3

图片 4

1.2.事务分类

  删除或重建一个指定的索引作为单个事务来处理。该项在重建聚集索引时格外有用,当删除一个聚集索引时,sqlserver会重建每个非聚集索引以便将书签从聚集索引键改为RID。如果再新建或者重建聚集索引,Sql server会再一次重建全部的非聚集索引,如果再新建或重建的聚集索引键值相同,可以设置Drop_existing=ON。

测试数据如下:

2)在根目录下创建my.ini配置文件

图片 5

图片 6

1.2.1.系统提供的事务

系统提供的事务是指执行某些T-SQL语句时,一条语句段构成了一个事务,如ALTER TABLE,CREATE,DELETE,DROP,FETCH等。

1.3 IGNORE_DUP_KEY

图片 7

3)用记事本打开my.ini,填写如下配置信息

(可以把中文注释去掉)

[mysqld]
# 设置服务端使用的字符集为utf-8
character-set-server=utf8
# 绑定IPv4地址
bind-address = 0.0.0.0
# 设置mysql的端口号
port = 3306
# 设置mysql的安装目录
basedir=C:/mysql-5.7.23
# 设置mysql数据库的数据的存放目录
datadir=C:/mysql-5.7.23/data
# 允许最大连接数
max_connections=2000
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 设置mysql以及数据库的默认编码
[mysql]
default-character-set=utf8
[mysql.server]
default-character-set=utf8
# 设置客户端默认字符集
[client]
default-character-set=utf8

 

(注意:上面配置中需要修改成自己相应的basedir和datadir的路径)

1.2.2.用户自定义的事务

实际应用中,经常使用用户自定义的事务。自定义的方法是,以BEGIN TRANSACTION开始,以COMMIT TRANSACTION或ROLLBACK TRANSACTION结束。这两个语句之间所有语句都被视为一体。
示例2:自定义事务的应用

BEGIN TRANSACTION
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180013','贾乃亮','1993-01-20','498')
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180014','周星星','1993-07-20','532')
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180015','雨化田','错误格式数据','570')
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180016','周琪','1993-01-20','653')
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180017','陈璐','1998-01-20','599')
COMMIT TRANSACTION

在上面的事务中,第三条插入数据是错误数据,无法成功插入,执行上面的语句,发现所有插入语句都没有被执行成功。
还有一种用户自定义事务——分布式事务。如果在比较复杂的环境中,有多台服务器,为了保证服务器中数据的完整性和一致性,就必须定义一个分布式事务。举个例子,有2台服务器,一台存放库存数据,另一台存放订单数据,用户下单的逻辑是,下单前先扣除库存数据,再下单。如果没有分布式事务,容易出现扣除库存数量,单下单却没成功,造成两个数据库数据不一致的情况。

  是指如果一个update或者insert语句影响多行数据,但有一行键被发现产生重值时,整个语句就会回滚,IGNORE_DUP_KEY=on时产生重复键值时不会引起整个语句的回滚,重复的行会被舍弃其它的行会被插入或更新。

 

4)安装MySQL服务

以管理员身份打开cmd窗口,进入mysql目录下的bin子目录,然后执行mysqld -install命令,如下图:

图片 8

图片 9

(注意:如果提示找不到dll文件,请安装相关的vc运行库 )

  运行库:

下载地址:https://www.microsoft.com/en-us/download/details.aspx?id=40784

图片 10

 

 

1.3.管理事务

主要使用以下4条语句管理事务:BEGIN TRANSACTION,COMMIT TRANSACTION,ROLLBACK TRANSACTION和SAVE TRANSACTION。此外还有2个全局变量可以用在事务处理语句中:@@ERROR和@@TRANCOUNT。
BEGIN TRANSACTION,COMMIT TRANSACTION,ROLLBACK TRANSACTION不多说了。

1.4 Statistics_norecompute

实现row_number()排名函数,按学号(StuNo)排序。

5)在初始化MySQL数据库

先在MySQL跟目录下创建data子目录,如下图:

图片 11

图片 12

然后继续在命令行的bin目录下执行mysqld --initialze --user=root --console命令执行初始化,如下图:

图片 13

(注意:初始化完成后MySQL会为root用户创建一个默认的密码,后续需要使用此密码登录MySQL,上面白色部分就是初始化的密码)

1.3.1.SAVE TRANSACTION

允许部分地提交一个事务,同时仍能回退这个事务的剩余部分。
示例3:BEGIN TRANSACTION,COMMIT TRANSACTION,ROLLBACK TRANSACTION和SAVE TRANSACTION的结合使用
执行下列语句

BEGIN TRANSACTION changed
INSERT INTO student(stu_no,stu_name,stu_sex,stu_enter_score)
VALUES('20180014','谭晶','男','533')
SAVE TRANSACTION saveinsert--设置保存事务点saveinsert
UPDATE student
SET stu_sex='错误数据'
WHERE stu_no='20180014'
ROLLBACK TRANSACTION saveinsert--回滚到保存事务点saveinsert
COMMIT TRANSACTION changed

上述代码完成了一个这样的功能:设置一个事务,事务名changed,该事务的作用是向student表中插入一条记录并更新该记录的stu_sex字段。如果更新失败,则回滚到插入操作,即保证不管更新是否成功,插入操作都能成功。

  选项决定了是否需要自动更新索引上的统计,每个索引维护着该索引首位字段的数值分布的柱状图,在查询执行计划时,查询优化器利用这些统计信息来判断一个特定索引的有效性。当数据达到一个阀值时,统计值会变。Statistics_norecompute选项允许一个关联的索引在数据修改时不自动更新统计值。该选择覆盖了auto_update_statistics的on值。

-- @row_number:=0,设置变量@row_number的初始值为0。
-- @row_number:=@row_number+1,累加@row_number的值。
select ID,StuNo,StuName,StuAge,@row_number:=@row_number+1 as row_number 
from demo.Student a,
(
    select @row_number:=0
) b
order by StuNo asc;

6)启动MySQL服务

在命令执行net start mysql 命令,如下图:

图片 14

图片 15

友情链接: 网站地图
Copyright © 2015-2019 http://www.nflfreepicks.net. 新葡萄京娱乐场网址有限公司 版权所有