精华内容
下载资源
问答
  • SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '数据库名' ORDER BY create_time DESC; 该语句可以查询出,数库什么时候增加了什么表,和修改了那个表。
    SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '数据库名' ORDER BY create_time DESC;
    

    该语句可以查询出,数库什么时候增加了什么表,和修改了那个表。

    展开全文
  • 查询数据库修改记录的sql语句

    万次阅读 2018-12-03 16:21:41
    SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '数据库名称' ORDER BY create_time DESC; 该语句可以查询出,数库什么时候增加了什么表,和修改了那个表。

    SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '数据库名称' ORDER BY create_time DESC;

    该语句可以查询出,数库什么时候增加了什么表,和修改了那个表。

    展开全文
  • 各种数据库查询前几条数据的方法

    万次阅读 2018-09-05 10:08:42
    sql在不同数据库查询前几条数据 关键字: sql 前几条结果  sql在不同数据库查询前几条数据  1. ORACLE   SELECT * FROM TABLE1 WHERE ROWNUM<=N    select * from stu_info where rownum<=10 ...

    sql在不同数据库查询前几条数据
    关键字: sql 前几条结果 
    sql在不同数据库查询前几条数据 
    1. ORACLE 
      SELECT * FROM TABLE1 WHERE ROWNUM<=N 

     

    select * from stu_info where rownum<=10 (查询学生信息表的前10条数据)
      HQL: from table1 t order by t.createTime desc where rownum<=n 

    HQL:from stu_info s order by s.creatTime desc where rownum<=10


    2. INFORMIX 
       SELECT FIRST N * FROM TABLE1 
    3. DB2 
    SELECT * ROW_NUMBER() OVER(ORDER BY COL1 DESC) AS ROWNUM WHERE ROWNUM<=N 
    或者 
    SELECT COLUMN FROM TABLE FETCH FIRST N ROWS ONLY 
    4. SQL SERVER 
    SELECT TOP N * FROM TABLE1       select TOP 10 * from stu_info   (前10条数据)
    5. SYBASE 
    SET ROWCOUNT N 
    GO 
    SELECT * FROM TABLE1 
    6. MYSQL (mySql)
    SELECT * FROM TABLE1 LIMIT N       select * fron stu_info limit 10
    hibernate查询记录的前10条记录 
    就像mysql的SQL语句的"select * from table limit 10" hql 不支持limit 
    query.setFirstResult(0);//从第0条开始取 
    query.setMaxResults(10);//取十条记录 
    7. FOXPRO 
    SELECT * TOP N FROM TABLE ORDER BY COLUMN 
    8.postgres查询前几条记录SQL 
    SELECT * FROM TABLE LIMIT 
    跟我学SQL分为三部分,其中涵盖了有关SQL标准的基本知识。在上一篇文章里我们讨论了一些数据库术语和4种最基本的数据查询类型。此外,我们还解释了WHERE子句和条件语句的用法,同时我们提供了各类查询的具体示例。
    在这篇文章里,我们将就其他一些SQL函数和子句进行阐述,供你用于基本的SELECT数据查询中。
    SELECT选项精制结果
    正如我们从上一篇文章中所读到的那样,SELECT语句具有种类繁多的各类选项,这些选项可以用来控制数据返回的方式。这些选项以子句、关键词和函数的形式存在。
    子句是一种修改结果的语句。子句不是必要的语句但它对数据的内容及其显示进行了提炼。WHERE子句就是这样的子句。
    关键词触发数据库的内在功能。这些关键词在有时甚至是查询所必需的。例如“INSERT INTO table_name (column1) VALUES (‘data1’);”语句中的INTO和VALUE就是如此。我们将了解DISTINCT关键词,它能触发一些非常有用的可选功能。
    下面总结了一些最常用的子句、关键词和函数。然后我会对每一部分举例说明。
    ·ORDER BY – 按照指定列排序返回结果的子句
    ·DISTINCT – 只返回结果集合内唯一行的关键词 
    ·COUNT -- 返回匹配查询的数据行总数数值的函数 
    ·AVG – 该函数返回指定列的平均值
    ·SUM –该函数把指定的列中的数字加起来
    ·MIN – 该函数返回列中最小的非NULL值
    ·MAX –该函数返回列中的最大值
    ·GROUP BY – 按列汇集查询函数结果的子句
    用ORDER BY对查询结果排序
    ORDER BY子句让数据库对查询结果排序,这样你就无须自己编写应用程序进行“手工”排序了。ORDER BY子句必须放在查询语句的结尾。其基本用法如下:
    SELECT * FROM Contacts ORDER BY first_name;
    你可以随意在任何选择语句中使用ORDER BY 子句返回多列结果。你还可以用它连接其他子句:
    SELECT first_name, last_name FROM Contacts WHERE first_name BETWEEN ‘a’ AND ‘k’ ORDER BY last_name;
    你可以对多列数据排序。优先顺序按从左到右依次降低,所以查询语句中各列的排列顺序很重要。
    SELECT * FROM Contacts ORDER BY company, last_name, first_name;
    查询结果默认按数字或者字母的升序排序。你可以在ORDER BY 子句后面加上DESC关键词改成降序排列。在下面的例子中,最高的net_amount排在最先(降序)。假如两行或者两行以上数据都包含了同样的net_amount值,那么同行中last_name值在字母表中最先出现的排先,因为last_name一列还是按照升序排序的。
    SELECT * FROM Sales ORDER BY net_amount DESC, last_name, first_name;
    在按照定义的列名排序以后,大多数数据库随后将按照数据表内的第一列排序然后顺序向右再排序。具体的实现各有变化,因此,如果排序在应用中比较重要那么你应该明确地定义所要排序的列。
    另外一值得注意的问题是,采用ORDER BY子句(以及WHERE子句),你正在用来排序结果的数据列并不一定得是返回结果集合的一部分。只要所有引用的列都在数据表内存在则下例完全有效:
    SELECT company, first_name, net_amount FROM Sales ORDER BY start_date, last_name;
    DISTINCT返回不重复结果
    DISTINCT关键词只返回结果集合内不重复的数据行。例如,有时你可能需要找出Sales表内的公司,但是你又不想看见每个条目。于是你可以用DISTINCT对应每一公司名返回一行数据:
    SELECT DISTINCT company FROM Sales;
    在使用DISTINCT时,它适用于所有的请求列。如果你打算列出表内的所有销售人员和他们所代表的公司而非每一销售记录,那么你可以使用下列语句。注意,这样操作还可能返回同一公司的若干条目等等。
    SELECT DISTINCT company, last_name, first_name FROM Sales;
    你 还可以在对结果缩小范围和进行排序时结合SELECT语句使用DISTINCT。为了确定显示的内容,数据库首先会证实精练的请求是否匹配数据行,然后应 用DISTINCT功能。在全部结果集合都得以确定之后即处理ORDER BY子句。如下例所示,只有net_amount大于100的数据行才被返回。由于DISTINCT保留遇见的第1个匹配查询条件的数据行而丢弃其他匹配 行,所以ORDER BY语句所引用的net_amount看起来就好象产生了随机的结果。
    SELECT DISTINCT company, last_name, first_name FROM Sales WHERE net_amount > 100 ORDER BY company, net_amount;
    函数应用逻辑
    返回单一值的函数称做聚集函数(aggregate function)。通过应用程序访问下列聚集函数的结果时,包含结果的“字段名”就是你所使用的实际函数。例如,在分析你的数据库结果时,结果数组的键值可能如下所示:
    $keyname = “COUNT(*)”;
    $resultkey = “AVG(net_amount)”;
    COUNT
    COUNT函数计算出结果集合中的数据行数。和其他函数一样它接受一个参数。以下的基本示例能告诉你数据表内的行数:SELECT COUNT(*) FROM Sales;
    你也可以用它来计算任何结果集合中的行数。
    SELECT COUNT(*) FROM Sales WHERE net_amount > 100;
    如果你想看看某特定列有多少行包含非空值,那你不妨对该列使用COUNT函数。注意,除非数据库设置为字段为空时缺省填充NULL否则将返回表内数据行的总数。另外,列出的列在超出一个的情况下会引起错误。
    SELECT COUNT(company) FROM Sales;
     COUNT还可以用来计算DISTINCT结果集合中的行数。
    SELECT COUNT(DISTINCT company, last_name) FROM Sales;
    COUNT语句通常用在程序中确定FOR循环的循环次数。
    AVG
    AVG返回某列所有字段的平均值,该列必须是数字数据类型。该函数用列的名字作为其参数,如果列字段数据类型是非数字类型的则函数返回“0”。SELECT AVG(net_amount) FROM Sales;
    你可以结合子句限制该函数的应用范围。
    SELECT AVG(net_amount) FROM Sales WHERE company LIKE ‘%ABCD Co%’;
    就象所有聚集函数一样,ORDER BY语句将被忽略。
    SUM
    SUM的工作方式和AVG差不多,只不过该函数返回结果集合中所有字段值的和。
    SELECT SUM(net_amount) FROM Sales WHERE net_amount > 100;
    AVG、SUM、MIN和MAX函数在没有指定列的情况下都会返回错误,所以你不能使用“*”通配符。
    MIN
    MIN返回指定列中最小的非空值。如果指定列是数字数据类型则结果将是最小的数字。如果它是一种字符串数据类型则函数将返回按字母表顺序出现的第1个值。SELECT MIN(net_amount) FROM Sales WHERE last_name = “Smith”;
    SELECT MIN(last_name) FROM Sales;
     MAX
    MAX的工作方式和MIN函数一样,只不过该函数返回最大的非空值。该函数也可以用于字符串或者数字列
    SELECT MAX(net_amount) FROM Sales;
    SELECT MAX(company) FROM Sales WHERE net_amount > 100;
    MAX函数有时还用在包含自动递增键字段的列上确定下一条目的键ID。除非你正在运行一个非公开的数据库,否则在使用这一信息插入下一条目时务必谨慎,以防其他用户先你执行数据操作。
    GROUP BY 令函数更有用
    虽然以上提到的所有这些函数都能提供相当有用的信息,但是,如果有GROUP BY子句帮忙的话更能让你在列的字段子集中应用这些函数。不要对你的Sales表中每一家公司一次又一次地执行MAX函数查询——你完全可以带GROUP BY子句获得同样的结果:
    SELECT company, MAX(net_amount) FROM Sales GROUP BY company;
    这样做可以获得每家公司net_amount的的最大值。在选择多列名的时候也可以采用该语句,你还可以用多列来对函数结果分组。
    下面的例子演示了以上各种方式。首先,包括GROUP BY子句可以令你指定要显示的其他列。然而,你得知道这个例子将返回在组中遇到的第1个last_name值;Sum( net_amount )将显示全部公司的结果而不仅仅针对匹配姓氏的数据行。这是因为,我们只使用了Company字段来定义我们的组。
    SELECT company, last_name, SUM(net_amount) FROM Sales GROUP BY company;
    在上面的例子中,last_name列实际上并没有提供什么有用的信息,但这样做是为了在下一个例子中要用到的功能做准备。你可以创建多列定义的组。这样就可以在结果集合中产生针对特定行的函数结果,而结果集合则是由所有指定的GROUP BY列联合起来创建的:
    SELECT company, AVG(net_amount), last_name FROM Sales GROUP BY company, last_name;
     上面的例子给每家公司中每一姓氏给出了平均的net_amount。你列出GROUP BY列的顺序控制着结果的排序,但是实际的函数值结果是一样的。
    下面的例子表明如何组织结果而不显示分组的列。在有些场合这样做是很有用的,例如,如果要显示个人的销售量但却不显示姓名就能用上下面的例子了:
    SELECT company, COUNT(sale_id) FROM Sales GROUP BY company, last_name;
    限制使用GROUP BY的查询
    如你在以上示例中所看到的那样,你可以结合WHERE字句利用以上的概念限制查询的范围。WHERE子句会首先被计算,然后执行函数。在使用组的时候就是这样的。
    SELECT company, AVG(net_amount), FROM Sales WHERE net_amount > 100 GROUP BY company;
    上面的例子只对那些满足WHERE限制条件的数据行适用AVG函数。注意,WHERE子句必须放在GROUP BY子句之前。你还可以用HAVING语句对分组计算之后限制返回的结果集合。
    SELECT company, AVG(net_amount), FROM Sales WHERE last_name BETWEEN ‘a’ AND ‘m’ GROUP BY company HAVING AVG(net_amount) > 500;
     
    上面的语句计算每家公司net_amount的平均值,而且只计算那些姓氏满足限制条件的销售人员的销售量,同时只显示大于500的结果。
    MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更好的性能, 我们可以将表中字段的宽度设得尽可能小。例如,在定义邮政编码这个字段时,如果将其设置为CHAR(255),显然给数据库增加了不必要的空间,甚至使用 VARCHAR这种类型也是多余的,因为CHAR(6)就可以很好的完成任务了。同样的,如果可以的话,我们应该使用 MEDIUMINT而不是BIGIN来定义整型字段。 
    另外一个提高效率的方法是在可能的情况下,应该尽量把字段设置为NOT NULL,这样在将来执行查询的时候,数据库不用去比较NULL值。 
    对于某些文本字段,例如“省份”或者“性别”,我们可以将它们定义为ENUM类型。因为在MySQL中,ENUM类型被当作数值型数据来处理,而数值型数据被处理起来的速度要比文本类型快得多。这样,我们又可以提高数据库的性能。 
    2、使用连接(JOIN)来代替子查询(Sub-Queries) 
    MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询 中。例如,我们要将客户基本信息表中没有任何订单的客户删除掉,就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来,然后将结果传递给主查 询,如下所示: 
    DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) 
    使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查 询可以被更有效率的连接(JOIN).. 替代。例如,假设我们要将所有没有订单记录的用户取出来,可以用下面这个查询完成: 
    SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) 
    如果使用连接(JOIN).. 来完成这个查询工作,速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话,性能将会更好,查询如下: 
    SELECT * FROM customerinfo LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo. CustomerID WHERE salesinfo.CustomerID IS NULL 
    连接(JOIN).. 之所以更有效率一些,是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。 
    3、使用联合(UNION)来代替手动创建的临时表 
    MySQL 从 4.0 的版本开始支持 UNION 查询,它可以把需要使用临时表的两条或更多的 SELECT 查询合并的一个查询中。在客户端的查询会话结束的时候,临时表会被自动删除,从而保证数据库整齐、高效。使用 UNION 来创建查询的时候,我们只需要用 UNION作为关键字把多个 SELECT 语句连接起来就可以了,要注意的是所有 SELECT 语句中的字段数目要想同。下面的例子就演示了一个使用 UNION的查询。 
    SELECT Name, Phone FROM client UNION SELECT Name, BirthDate FROM author 
    UNION 
    SELECT Name, Supplier FROM product 
    4、事务 
    尽管我们可以使用子查询(Sub-Queries)、连接(JOIN)和联合(UNION)来创建各种各样的查询,但不是所有的数据库操作都可以只用一 条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下,当这个语句块中的某一条语句运行出错的时候, 整个语句块的操作就会变得不确定起来。设想一下,要把某个数据同时插入两个相关联的表中,可能会出现这样的情况:第一个表中成功更新后,数据库突然出现意 外状况,造成第二个表中的操作没有完成,这样,就会造成数据的不完整,甚至会破坏数据库中的数据。要避免这种情况,就应该使用事务,它的作用是:要么语句 块中每条语句都操作成功,要么都失败。换句话说,就是可以保持数据库中数据的一致性和完整性。事物以BEGIN 关键字开始,COMMIT关键字结束。在这之间的一条SQL操作失败,那么,ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。 
    BEGIN; 
    INSERT INTO salesinfo SET CustomerID=14; 
    UPDATE inventory SET Quantity=11 
    WHERE item='book'; 
    COMMIT; 
    事务的另一个重要作用是当多个用户同时使用相同的数据源时,它可以利用锁定数据库的方法来为用户提供一种安全的访问方式,这样可以保证用户的操作不被其它的用户所干扰。 
    5、锁定表 
    尽管事务是维护数据库完整性的一个非常好的方法,但却因为它的独占性,有时会影响数据库的性能,尤其是在很大的应用系统中。由于在事务执行的过程中,数据库将会被锁定,因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户 
    来使用,事务造成的影响不会成为一个太大的问题;但假设有成千上万的用户同时访问一个数据库系统,例如访问一个电子商务网站,就会产生比较严重的响应延迟。 
    其实,有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。 
    LOCK TABLE inventory WRITE 
    SELECT Quantity FROM inventory 
    WHEREItem='book'; 
    ... 
    UPDATE inventory SET Quantity=11 
    WHEREItem='book'; 
    UNLOCK TABLES 
    这里,我们用一个 SELECT 语句取出初始数据,通过一些计算,用 UPDATE 语句将新值更新到表中。包含有 WRITE 关键字的 LOCK TABLE 语句可以保证在 UNLOCK TABLES 命令被执行之前,不会有其它的访问来对 inventory 进行插入、更新或者删除的操作。 
    6、使用外键 
    锁定表的方法可以维护数据的完整性,但是它却不能保证数据的关联性。这个时候我们就可以使用外键。例如,外键可以保证每一条销售记录都指向某一个存在的 客户。在这里,外键可以把customerinfo 表中的CustomerID映射到salesinfo表中CustomerID,任何一条没有合法CustomerID的记录都不会被更新或插入到 salesinfo中。 
    CREATE TABLE customerinfo 

    CustomerID INT NOT NULL , 
    PRIMARY KEY ( CustomerID ) 
    ) TYPE = INNODB; 
    CREATE TABLE salesinfo 

    SalesID INT NOT NULL, 
    CustomerID INT NOT NULL, 
    PRIMARY KEY(CustomerID, SalesID), 
    FOREIGN KEY (CustomerID) REFERENCES customerinfo 
    (CustomerID) ON DELETECASCADE 
    ) TYPE = INNODB; 
    注意例子中的参数“ON DELETE CASCADE”。该参数保证当 customerinfo 表中的一条客户记录被删除的时候,salesinfo 表中所有与该客户相关的记录也会被自动删除。如果要在 MySQL 中使用外键,一定要记住在创建表的时候将表的类型定义为事务安全表 InnoDB类型。该类型不是 MySQL 表的默认类型。定义的方法是在 CREATE TABLE 语句中加上 TYPE=INNODB。如例中所示。
    Mysql Limit操作 
    select * from table LIMIT 5,10; #返回第6-15行数据
    select * from table LIMIT 5; #返回前5行
    select * from table LIMIT 0,5; #返回前5行 
    性能优化: 
    基于MySQL5.0中limit的高性能,我对数据分页也重新有了新的认识.
    1.
    Select * From cyclopedia Where ID>=(
    Select Max(ID) From (
     Select ID From cyclopedia Order By ID limit 90001
    ) As tmp
    ) limit 100;
    2.
    Select * From cyclopedia Where ID>=(
    Select Max(ID) From (
     Select ID From cyclopedia Order By ID limit 90000,1
    ) As tmp
    ) limit 100;
    同样是取90000条后100条记录,第1句快还是第2句快?
    第1句是先取了前90001条记录,取其中最大一个ID值作为起始标识,然后利用它可以快速定位下100条记录
    第2句择是仅仅取90000条记录后1条,然后取ID值作起始标识定位下100条记录
    第1句执行结果.100 rows in set (0.23) sec
    第2句执行结果.100 rows in set (0.19) sec
    很明显第2句胜出.看来limit好像并不完全像我之前想象的那样做全表扫描返回limit offset+length条记录,这样看来limit比起MS-SQL的Top性能还是要提高不少的.
    其实第2句完全可以简化成
    Select * From cyclopedia Where ID>=(
    Select ID From cyclopedia limit 90000,1
    )limit 100;
    直接利用第90000条记录的ID,不用经过Max运算,这样做理论上效率因该高一些,但在实际使用中几乎看不到效果,因为本身定位ID返回的就是1条记录,Max几乎不用运作就能得到结果,但这样写更清淅明朗,省去了画蛇那一足.
    可是,既然MySQL有limit可以直接控制取出记录的位置,为什么不干脆用Select * From cyclopedia limit 90000,1呢?岂不更简洁?
    这样想就错了,试了就知道,结果是:1 row in set (8.88) sec,怎么样,够吓人的吧,让我想起了昨天在4.1中比这还有过之的"高分".Select * 最好不要随便用,要本着用什么,选什么的原则, Select的字段越多,字段数据量越大,速度就越慢. 上面2种分页方式哪种都比单写这1句强多了,虽然看起来好像查询的次数更多一些,但实际上是以较小的代价换取了高效的性能,是非常值得的.
    第1种方案同样可用于MS-SQL,而且可能是最好的.因为靠主键ID来定位起始段总是最快的.
    Select Top 100* From cyclopedia Where ID>=(
    Select Top 90001 Max(ID) From (
     Select ID From cyclopedia Order By ID
    ) As tmp
    )
    但不管是实现方式是存贮过程还是直接代码中,瓶颈始终在于MS-SQL的TOP总是要返回前N个记录,这种情况在数据量不大时感受不深,但如果成百上千万,效率肯定会低下的.相比之下MySQL的limit就有优势的多,执行:
    Select ID From cyclopedia limit 90000
    Select ID From cyclopedia limit 90000,1
    的结果分别是:
    90000 rows in set (0.36) sec
    1 row in set (0.06) sec
    而MS-SQL只能用Select Top 90000 ID From cyclopedia 执行时间是390ms,执行同样的操作时间也不及MySQL的360ms.

    展开全文
  • 查询数据库各种历史记录

    千次阅读 2019-05-05 12:35:00
    查询数据库各种历史记录 在SQL Server数据库中,从登陆开始,然后做了什么操作,以及数据库里发生了什么,大多都是有记录可循的,但是也一些确实无从查起。 一.数据库启动记录 1.最近一次启动SQL Server...

    转自:11. 查询数据库各种历史记录

     

    在SQL Server数据库中,从登陆开始,然后做了什么操作,以及数据库里发生了什么,大多都是有记录可循的,但是也有一些确实无从查起。

    数据库启动记录

    1. 最近一次启动SQL Server的时间

    复制代码
    select sqlserver_start_time from sys.dm_os_sys_info;
    
    --也可参考系统进程创建的时间,比服务启动时间略晚(秒级)
    select login_time from sysprocesses where spid = 1 
    select login_time from sys.dm_exec_sessions where session_id = 1
    
    --也可参考tempdb数据库创建的时间,比服务启动时间略晚(秒级)
    select create_date from sys.databases 
    where database_id=2
    复制代码

     

    2. 最近几次启动SQL Server的时间

    复制代码
    --参考error log,系统默认保留6个归档,共7个文件
    exec xp_readerrorlog 0,1, N'SQL Server is starting'
    exec xp_readerrorlog 1,1, N'SQL Server is starting'
    exec xp_readerrorlog 2,1, N'SQL Server is starting'
    exec xp_readerrorlog 3,1, N'SQL Server is starting'
    exec xp_readerrorlog 4,1, N'SQL Server is starting'
    exec xp_readerrorlog 5,1, N'SQL Server is starting'
    exec xp_readerrorlog 6,1, N'SQL Server is starting'
    --之前关键字用N'Server process ID is'并不严谨,改为N'SQL Server is starting'
    复制代码

     

    3. 历史上更多次启动SQL Server的时间

    查看windows event log,SQL语句无法直接读取event log,如果想用命令行,可以试试VBS,Powershell。

    Event Viewer/Windows logs下Application 或者 System 事件里都有服务启动的记录。

     

    二. 登录数据库记录

    1. 查看error log

    默认情况下,只有失败的登录会被记录在error log里,如果想登录失败/成功都被记录到error log,需要开启如图选项:

     

    用SQL语句修改注册表,也同样可以开启,键值对应关系如下:

    0, None

    1, Failed

    2, Successful

    3, Both failed and successful

    USE [master]
    GO
    EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3
    GO

    在error log里查看登录记录:

    exec xp_readerrorlog 0,1, N'Login', N'for user', null, null, N'DESC'

     

    2. 利用LOGON 触发器进行记录

    从SQL Server 2005 SP2开始引入了LOGON Trigger,可以用它在登录时做个记录,实现如下:

    复制代码
    --创建LOGON触发器
    CREATE database DBA
    GO
    
    USE DBA
    GO
    
    IF OBJECT_ID('login_history','U') is not null
        DROP TABLE login_history
    GO
    
    CREATE TABLE login_history
    (
    FACT_ID         bigint IDENTITY(1,1) primary key,
    LOGIN_NAME      nvarchar(1024),
    LOGIN_TIME      datetime
    )
    GO
    
    IF EXISTS(select 1 from sys.server_triggers where name = 'login_history_trigger')
        DROP TRIGGER login_history_trigger ON ALL SERVER
    GO
    
    CREATE TRIGGER login_history_trigger
    ON ALL SERVER
    FOR LOGON
    AS
    BEGIN
        --IF SUSER_NAME() NOT LIKE 'NT AUTHORITY\%' AND 
        --   SUSER_NAME() NOT LIKE 'NT SERVICE\%'
        IF ORIGINAL_LOGIN() NOT LIKE 'NT AUTHORITY\%' AND
           ORIGINAL_LOGIN() NOT LIKE 'NT SERVICE\%'
        BEGIN
            INSERT INTO DBA..login_history
            VALUES(ORIGINAL_LOGIN(),GETDATE());
        END;
    END;
    GO
    
    --登录后查看记录
    SELECT * FROM login_history
    复制代码

     

    3. 实例:查询某login的最后一次登录

    系统表/试图里,并没有这样的字段记录,syslogins里accdate也是不对的,如果要查可以通过上面2个方法里的一种:

    (1) ERROR LOG,得设置记录Login Auditing 的“Both failed and successful” 选项,默认为”Failed”;

    (2) Logon Trigger;

     

    创建,修改,删除记录 (DDL)

    1. 服务器对象的创建,修改

    复制代码
    --创建数据库
    select name, create_date from sys.databases
    
    --创建,修改登录
    select name, createdate, updatedate from syslogins
    select name, create_date, modify_date from sys.server_principals 
    
    --创建,修改LOGON触发器
    select name, create_date, modify_date from sys.server_triggers 
    复制代码

     

    2. 数据库对象创建,修改

    --创建,修改数据库对象
    select name, create_date, modify_date from sys.objects 
    
    --创建,修改触发器,DDL触发器不在sys.objects里
    select name, create_date, modify_date from sys.triggers 

    注意:

    (1) 索引的创建,修改并没有记录

    复制代码
    sys.objects --里面没有0,1 之外的索引
    sys.indexes --里面没有日期
    objectproperty() --没有日期属性
    indexproperty()  --没有日期属性
    
    sys.dm_db_index_operational_stats 
    sys.dm_db_index_usage_stats
    sys.dm_db_index_physical_stats --也都没有
    
    STATS_DATE (table_id, index_id) --是索引的统计信息最后更新时间
    复制代码

    (2) 关于creator和owner

    SQL Server里只有owner,数据库里对象的owner必须是一个有效的database principal (user或者role),没有creator,很难知道是谁创建了这个对象,因为owner并不准确:

    首先,数据库对象的owner可以被修改,ALTER AUTHORIZATION或者sp_changeobjectowner都行;

    其次,就算owner没被修改过,默认情况下数据库对象的owner沿用schema的owner,除非在创建schema时特意指定了某个owner;

    最后,系统表并没有记录creator,如果想要查询,也许得利用DDL 触发器来记录。

    关于owner简单举例如下: 

      object owner

     

    3. 默认跟踪里的创建,修改,删除对象 (create, alter, drop)

    从sql server 2005开始引入了默认跟踪,这是sql server默认开启的跟踪,并定义了事件、文件大小,个数,查看定义如下:

    复制代码
    --系统定义好的默认跟踪事件
    select t.eventid, te.name
    from (select distinct eventid from sys.fn_trace_geteventinfo(1)) t
    inner join sys.trace_events te
    on t.eventid = te.trace_event_id
    
    --最多5个文件,每个文件20MB,依次滚动覆盖
    select * from sys.traces
    where id = 1
    复制代码

    示例,利用默认跟踪查看删除数据库记录如下:

    复制代码
    DECLARE @path varchar(1024)
    
    SELECT @path = path
    FROM sys.traces
    WHERE id = 1
    
    SELECT *
    FROM fn_trace_gettable(@path, default) --default读取当前所有trace文件,包括正在用的
    WHERE DatabaseName = 'DBA'
    and EventClass = 47    --46表示Create对象,47表示Drop对象,164表示修改对象
    and ObjectType = 16964 --16964表示数据库
    复制代码

    注意:

    (1) 其他对象比如表的删除等也都可以查到;

    (2) 默认跟踪返回的列值有很多定义,没有系统表记载,需要去翻帮助,比如ObjectType列值参考这个列表:

    https://msdn.microsoft.com/en-us/library/ms180953.aspx

    (3) 注意默认跟踪的时效性,5个20MB的文件,也许想要看的信息很快就被覆盖了;

    (4)  truncate table并没有被默认跟踪记录。

     

    数据库表的各种记录

    汇总一下对表的各种历史操作记录的查看:

    (1) create table, alter table记录,查看sys.objects 或者默认跟踪;

    (2) drop table记录,查看默认跟踪;

    (3) truncate table 也许只有去打开数据库log文件查看了,最后会简单介绍下;

    (4) DML操作表中数据的记录,查看sys.dm_db_index_usage_stats,如下:

    复制代码
    SELECT o.name as table_name, 
           s.last_user_seek,
           s.last_user_scan,
           s.last_user_lookup,
           s.last_user_update
    from sys.indexes i 
    left join sys.dm_db_index_usage_stats s 
    on s.object_id = i.object_id and 
       s.index_id = i.index_id 
    inner join sys.objects o
    on i.object_id = o.object_id
    where i.index_id <= 1
    and o.is_ms_shipped = 0
    order by o.name
    复制代码

    注意:动态管理视图(DMV) 中采集来的信息都是从sql server启动后开始的,也就是说重启后就没了。

     

    历史SQL语句记录

    有些数据库本身,会记录所有历史的SQL命令。比如:mysql和pgsql都有专门的log文本文件来存放所有历史的SQL命令;

    也有些数据库在保存log文本的同时,还保留最近的N条SQL命令在数据库里,以方便查询。

     

    SQL Server并没有这样的实现,只有sys.dm_exec_query_stats缓存了一部分 (sql server服务开启后执行的语句,某些不被缓存执行计划的语句并不记录)。

    这个视图主要是对执行计划的统计,包含消耗成本,运行次数等等,并没有session,user,每次被执行的时间等信息:

    复制代码
    SELECT st.text as sql_statement,
           qs.creation_time as plan_last_compiled,
           qs.last_execution_time as plan_last_executed,
           qs.execution_count as plan_executed_count,
           qp.query_plan
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    order by total_elapsed_time/execution_count desc
    复制代码

    当然,开启跟踪,审计之类的方法,是可以记录所有操作的,但是这个开销有可能会影响系统性能,所以一般并不在生产环境启用。

     

    数据库备份还原历史记录

    备份还原的记录都在msdb里。

    1. 备份记录

    复制代码
    SELECT 
         bs.backup_set_id,
         bs.database_name,
         bs.backup_start_date,
         bs.backup_finish_date,
         CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],
         CAST(DATEDIFF(second, bs.backup_start_date,
         bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],
         CASE bs.[type]
             WHEN 'D' THEN 'Full Backup'
             WHEN 'I' THEN 'Differential Backup'
             WHEN 'L' THEN 'TLog Backup'
             WHEN 'F' THEN 'File or filegroup'
             WHEN 'G' THEN 'Differential file'
             WHEN 'P' THEN 'Partial'
             WHEN 'Q' THEN 'Differential Partial'
         END AS BackupType,
         bmf.physical_device_name,
         CAST(bs.first_lsn AS VARCHAR(50)) AS first_lsn,
         CAST(bs.last_lsn AS VARCHAR(50)) AS last_lsn,
         bs.server_name,
         bs.recovery_model
     FROM msdb.dbo.backupset bs
     INNER JOIN msdb.dbo.backupmediafamily bmf 
     ON bs.media_set_id = bmf.media_set_id
     ORDER BY bs.server_name,bs.database_name,bs.backup_start_date;
    GO
    复制代码

    如果server_name是本机,那么备份是在本机生成的;

    如果server_name是别的主机名,那么备份是被拿到本机做过数据库还原;

     

    2. 还原纪录

    复制代码
    SELECT 
         rs.[restore_history_id],
         rs.[restore_date],
         rs.[destination_database_name],
         bmf.physical_device_name,
         rs.[user_name],
         rs.[backup_set_id],
         CASE rs.[restore_type]
             WHEN 'D' THEN 'Database'
             WHEN 'I' THEN 'Differential'
             WHEN 'L' THEN 'Log'
             WHEN 'F' THEN 'File'
             WHEN 'G' THEN 'Filegroup'
             WHEN 'V' THEN 'Verifyonly'
         END AS RestoreType,
         rs.[replace],
         rs.[recovery],
         rs.[restart],
         rs.[stop_at],
         rs.[device_count],
         rs.[stop_at_mark_name],
         rs.[stop_before]
    FROM [msdb].[dbo].[restorehistory] rs
    INNER JOIN [msdb].[dbo].[backupset] bs
    --on rs.backup_set_id = bs.media_set_id
    ON rs.backup_set_id = bs.backup_set_id
    INNER JOIN msdb.dbo.backupmediafamily bmf 
    ON bs.media_set_id = bmf.media_set_id
    GO
    复制代码

    还原数据库的时候是会写backupset和backupmediafamily系统表的,用来记录还原所用到的备份文件信息。

     

    作业,维护计划,数据库邮件历史记录

    作业,维护计划,数据库邮件的历史记录,也都在msdb里。

    1. 作业历史记录

    复制代码
    if OBJECT_ID('tempdb..#tmp_job') is not null
        drop table #tmp_job
    
    --只取最后一次结果
    select job_id,
           run_status,
           CONVERT(varchar(20),run_date) run_date,
           CONVERT(varchar(20),run_time) run_time,
           CONVERT(varchar(20),run_duration) run_duration
      into #tmp_job
      from msdb.dbo.sysjobhistory jh1
     where jh1.step_id = 0
       and (select COUNT(1) from msdb.dbo.sysjobhistory jh2 
            where jh2.step_id = 0 
              and (jh1.job_id = jh2.job_id)
              and (jh1.instance_id <= jh2.instance_id))=1
    
    --排除syspolicy_purge_history这个系统作业
    select a.name job_name,
           case b.run_status when 0 then 'Failed'
                             when 1 then 'Succeeded'
                             when 2 then 'Retry'
                             when 3 then 'Canceled'
           else 'Unknown' 
           end as job_status,
           LEFT(run_date,4)+'-'+SUBSTRING(run_date,5,2)+'-'+RIGHT(run_date,2)
           +SPACE(1)
           +LEFT(RIGHT(1000000+run_time,6),2)+':'
                +SUBSTRING(RIGHT(1000000+run_time,6),3,2)+':'
                +RIGHT(RIGHT(1000000+run_time,6),2) as job_started_time,
           +LEFT(RIGHT(1000000+run_duration,6),2)+':'
                +SUBSTRING(RIGHT(1000000+run_duration,6),3,2)+':'
                +RIGHT(RIGHT(1000000+run_duration,6),2) as job_duration
      from msdb.dbo.sysjobs a 
      left join    #tmp_job b 
        on a.job_id=b.job_id 
     where a.name not in ('syspolicy_purge_history')
       and a.enabled = 1
     order by b.run_status asc,a.name,b.run_duration desc
    复制代码

     

    2. 维护计划历史记录

    select * from msdb..sysdbmaintplan_history
    
    --新的系统表也可以
    select * from msdb..sysmaintplan_log
    select * from msdb..sysmaintplan_logdetail

    维护计划最终是作为作业在运行的,也可以直接查看同名作业的历史记录。

     

    3. 数据库邮件历史记录

    复制代码
    --直接查系统表
    select * from msdb..sysmail_mailitems
    select * from msdb..sysmail_log
    
    --也可查看基于这2个系统表的系统视图
    select * from msdb..sysmail_allitems
    select * from msdb..sysmail_sentitems
    select * from msdb..sysmail_unsentitems
    select * from msdb..sysmail_faileditems
    select * from msdb..sysmail_event_log
    
    --更多系统表和视图
    use msdb
    GO
    select * from sys.objects 
    where name like '%sysmail%'
    and type in('U','V')
    order by type,name
    复制代码

     

    查看数据库日志文件

    数据库日志文件里对于DDL,DML操作肯定是有记录的,有2个内置函数可以用来解析,但是并不那么轻松,简单介绍如下:

    1. fn_dblog 读取当前在线的日志

    select * from fn_dblog(null,null) --2个null代表起始的日志LSN

    返回的结果集中字段定义:

    (1) AllocUnitName: 对象名

    (2) Operation: 操作类型,常见的有 'LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW'

    (3) [RowLog Contents 0], [RowLog Contents 1], 2,3,4,5: 字段内容,但是是二进制的,和dbcc page看到的类似

     

    试着查看truncate table记录如下:

    复制代码
    IF OBJECT_ID('test_truncate','U') is not null
        DROP TABLE test_truncate
    GO
    
    CREATE TABLE test_truncate(ID int)
    INSERT INTO test_truncate values(1)
    TRUNCATE TABLE test_truncate
    
    --查看truncate table记录
    select * from fn_dblog(null,null)
    where AllocUnitName like '%test_truncate%'
      and Description like 'Deallocated%'
    复制代码

     

    2. fn_dump_dblog 读取数据库备份里的日志

    参数介绍:前面两2个NULL和fn_dblog一样代表起始的日志LSN,DISK表示设备类型,1表示备份文件个数,最多64个,这里以1个文件为例:

    复制代码
    backup database DBA to disk = 'C:\backup\dba.bak'
    
    SELECT *
    FROM
        fn_dump_dblog (
            NULL, NULL, N'DISK', 1, N'C:\backup\dba.bak',
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
    GO
    复制代码

    这2个函数返回的信息量很大,如果有兴趣,不妨多做点测试。

    转载于:https://www.cnblogs.com/gered/p/10812320.html

    展开全文
  • 相信大家想查看对orcl的操作历史记录的时候,那么大家就要了解v$sqlarea 这个表它记录了所有用户对数据库操作的所有历史记录废话不多说直接上sqlselect * from v$sqlarea t 查询出来一些字段以及数据,发现你要...
  • 修改数据 语法: update 表名 set 列名1 =值1,列名2=值2,…[where 条件];
  • 最近要查询一些数据库的基本情况,由于以前用oracle数据库比较多,现在换了MySQL数据库,就整理了一部分语句记录下来。  1、查询数据库表数量 #查询MySQL服务中数据库数据量 SELECT COUNT(*) TABLES, table_...
  • 易语言教程数据库修改记录

    千次阅读 2020-07-06 09:30:44
    本命令一次性修改当前数据库中当前记录内多个字段的内容,当前记录指针保持不变。成功返回真,失败返回假。本命令为初级命令。命令参数表中最后一个参数可以被重复添加。 参数<1>的名称为“修改数据”,类型为...
  • 要点:数据库、表的创建、修改、删除方法;记录的插入、修改、删除方法解析。
  • 数据库重复数据时,用到哪些sql语句? 这里若干数据,并掺杂了重复数据 1. 查看过滤重复后的数据 思路, group by 分组可以对多个列进行分组, 分组后可以过滤掉重复的数据 sql语句: SELECT id,`name`,...
  • 理解数据库和关系型数据库的概念 什么是数据库数据库就是一个文件系统,通过标准的SQL语句获取数据什么是MySQL数据库什么是关系型数据库关系型数据库存放的是实体之间的关系 常见的关系型数据库l MySQLl Oraclel...
  • 简单查询数据3.修改数据4.删除数据5.还原数据数据库的设计主键的选择外键的选择 创建数据库关系图 生成数据库关系图 二.数据的操作 1.添加数据 添加单行数据 INSERT [INTO] 目标表名 [(字段)] VALUES (字段对应...
  • 各位高手: 在不能写数据库触发器,数据库没有时间戳字段的情况下,Oracle数据库如何查询指定表最近一天或者一个小时的变更记录? 包括新增、修改、删除的数据
  • 这天我做了一个方法是根据两个字段查询数据库数据列表,其中一个字段是类型(type) 。方法很简单,不一会儿就做出来了,这里不得不感叹mybatis-plus的强大,代码量很小!接下来就是测试了,由于数据库已经一条...
  • 数据库表中插入一条数据,先列举出需要插入的字段名称,然后在VALUES子句中依次写出对应字段的值 INSERT INTO <表名> (字段1, 字段2, …) VALUES (值1, 值2, …);(注意VALUES紧跟括号不要换行和加分号) ...
  • 二、需求:假设DB1dBa中pre__close=0的所有行为A,将A每一条记录的前一个交易日rClose赋值给A,并更新数据库。 三、要求:给出测试正确的demo 四、参考: 1、给日期加序号:select code, rDate,rClose,ROW_NUMBER()...
  • 我郁闷啊,这个东西要查起来可真棘手啊,更郁闷的还有就是他们服务器我们公司的网络...要来个中转,不过总比连不上好,这个密码被修改了,他们没有操作,我们操作,系统里自动记录修改记录的,查了一下数据库没有
  • 请问,我想将现在表单的修改前后的数据保存数据库供历史查询修改记录数据,前台还是显示最新的修改记录,请问怎么实现,说具体点?
  • 使用Statement执行sql的对象完成数据库添加、修改、删除动作。 数据库添加数据: 语法:insert into 表名 values(值1,值2,...值n) package cn.itcast.jbdc; import java.sql.Connection; import java.sql....
  • 在保证需操作的数据库中已经存在XSKC模式下的数据表student、course、sc 1、修改数据表信息 1)修改student表,将cs系姓名为“李咏”的学生姓名为“李勇” UPDATE XSKC.student SET sname = ‘李勇’ WHERE ...
  • 简单的说,记录集实际上缓存了从数据库获得的记录,应用程序可以从记录集中获得每条记录的字段。①、记录集对象 Recordset 的属性:⑴ RecordCount 属性:返回记录集中记录的个数;⑵ BOF、EOF 属性:BOF 指示当前...
  • 二、修改数据 1、修改某一个元组的值 2、修改多个元组的值 3、带子查询修改语句 三、删除数据 1、删除某一个元组的值 2、删除多个元组的值 3、带子查询的删除语句 一、插入数据 1、插入元组 语法:...
  • 数据库中列名为:created 用于自动记录 数据添加 的时间。  其数据类型为: timestamp  默认为:CURRENT_TIMESTAMP 修改成功后,下次添加新的数据,该列会自动记录系统时间。 CREATE TABLE `example` ( `id` ...
  • 层次数据模型     定义:层次数据模型是用树状<...其实层次数据模型就是的图形表示就是一个倒立生长的树,由基本数据结构中的树(或者二叉树)的定义可知,每棵树都且仅一个根节点,其余的...
  • 查询oracle数据库操作记录

    万次阅读 2018-02-02 11:36:07
    1、查询oracle修改记录 select t.SQL_TEXT, t.FIRST_LOAD_TIME from v$sqlarea t where t.FIRST_LOAD_TIME like '2010-06-30%' order by t.FIRST_LOAD_TIME desc2、查看oracle会话 select * from v
  • PLSQL查询数据库操作历史记录

    千次阅读 2019-04-16 17:58:09
    PLSQL查询数据库操作历史记录: 1、在PLSQL按键Ctrl+E 可以查询我们在PLSQL执行的历史SQL,包括时间、用户、语句; 2、SELECT * FROM V$SQL 执行sql查询查询内容包含所有用户和应用系统对数据库的操作,执行...
  • 数据库修改表中的数据(SqlServer)

    千次阅读 2020-05-26 11:11:05
    修改表中的数据 DML:update 语法: update 表名 set 字段1=新的值,字段2=新的值,… where 记录的匹配条件 说明:如果不写where子句,默认是修改所有的行 -- 准备数据 use worker go create table worker( id ...
  • 最近在做一个关于数据库方面的任务,需要从相关联的几张表中查询需要读取的数据,再将这些数据合并到一起显示,通过查询资料,找到了处理方法,在此简单记录。 首先创建做测试需要使用的三张数据表apps、apps1、...
  • 时候会遇到这样的问题:数据库有一张表,属于某种配置信息表,用户可以从前台管理员界面直接进行数据配置,即对该数据库表的增删查改操作。由于存在一定的风险,我们需要将该表的历史数据完整的保存,并实现这些...
  • 请问各位大佬,在不更改表结构和不添加表及相应的触发器前提下,使用不含Spring的JAVA代码,如何得知数据已被修改,并重新获取最新数据数据没被修改时,不用重新获取数据) 使用的数据库是:Oracle,不是本机上的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 823,116
精华内容 329,246
关键字:

数据库查询有过修改记录的数据