SELECT TOP 1000 --创建时间 QS.creation_time, --查询语句 SUBSTRING(ST.text,(QS.statement_start_offset/2)+1, ((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1 ) AS statement_text, --执行文本 ST.text, --执行计划 QS.total_worker_time, QS.last_worker_time, QS.max_worker_time, QS.min_worker_time FROM sys.dm_exec_query_sta...
[WITH<common_tale_expression>] SELECT select_list [INTO new_table_name] [FROM table_source][where search_condition] [GROUP BY group_by_expression] [HAVING search_condition] [ORDER BY order_expression [ ASC | DESC ]] WITH子句 WITH子句用于指定临时命名的结果集，这些结果集成为公用表表达式（CTE）。该表达式源自简单查询，并且在单条SELECCT、INSERT、UPDATE或DELETE语句的执行范围内定义。use web; with AgeRep...
SELECT is_enabled, [path], max_size, max_filesFROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) OPTION (RECOMPILE); -- Knowing this information is important for troubleshooting purposes-- Also shows you the location of other error and diagnostic log files Query #14 Cluster Node Properties-- Get information about your cluster nodes and their status-- (if your database server is i...
SELECT @@SERVERNAME AS [Server Name], @@VERSIONAS [SQL Server and OS Version Info]; Query #2 is Core Counts. Get socket, physical core and logical core count from the SQL Server Error log.EXEC sys.xp_readerrorlog 0, 1, N‘detected‘, N‘socket‘; Query #3 is Server Properties. Get selected server properties.SELECT SERVERPROPERTY(‘MachineName‘) AS [MachineName], SERVERPROPERTY(‘ServerName‘...
--返回总行数AS BEGIN -- 存储过程開始 -- 该存储过程须要用到的几个变量 -- DECLARE @strTemp varchar(1000) DECLARE @strSql varchar(4000) --该存储过程最后运行的语句 DECLARE @strSql01 varchar(4000) DECLARE @strOrderType varchar(1000) --排序类型语句 (order by column asc或者order by column desc)BEGIN IF @bitOrderType = 1 -- bitOrderType=1即运行降序 BEGIN SET @strOrderType = ‘...
a.*,e.NoRead,e.AlRead,ToUserName= STUFF(( SELECT ‘,‘ + NameFROM Users dwhere PATINDEX(‘%,‘ + RTRIM(Account) + ‘,%‘,‘,‘ + a.ToUserID + ‘,‘)>0ORDER BY PATINDEX(‘%,‘ + RTRIM(Account) + ‘,%‘,‘,‘ + a.ToUserID + ‘,‘) --加上排序不变（如:2,1,3）FOR XML PATH(‘‘)), 1, 1,‘‘),case a.NoticeTypeID when 2 then ‘调查问卷‘ when 3 then ‘奖品订单‘ else ‘普通通知‘ end as Notice...
-- Breaks down buffers used by current database by object (table, index) in the buffer cache -- Note: This query could take some time on a busy instance SELECT OBJECT_NAME(p.[object_id]) AS [Object Name], p.index_id, CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)], COUNT(*) AS [BufferCount], p.Rows AS [Row Count], p.data_compression_desc AS [Compression Type] FROM sys.allocatio...
查询表结构start SELECT 序号 = a.colorder,字段名称 = a.name,字段描述 = f.value, 标识 = case when COLUMNPROPERTY( a.id,a.name,‘IsIdentity‘) = 1 then ‘√‘ else ‘‘ end, 主键 = case when exists(SELECT 1 FROM sysobjects where xtype = ‘PK‘ and parent_obj = a.id and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid ))) then ‘√...
正好最近在读【SQL注入攻击与防御】，感觉这真是一个值得实践的好目标！ 为了简化测试步骤，所以文章分为5次进行记录；0x1 sqlmap常用语句测试 测试1目的：执行cmd命令 测试1结果,测试初期无法连接； ----------------------------------------------------- C:\Users\Administrator>sqlmap -u "http://www.****.com/Index/SearchResult.aspx?KeyName=1&KeyWord=1"--level 5--risk 3--technique BST --batch --os-shell回显数...
高级查询在数据库中用得是最频繁的，也是应用最广泛的。 基本常用查询--select select * from student; --all 查询所有 select all sex from student; --distinct 过滤重复 select distinct sex from student; --count 统计 select count(*) from student; select count(sex) from student; select count(distinct sex) from student; --top 取前N条记录 select top 3 * from student; --alias column name 列重命名 se...
username=N‘天才夏雨‘ where id=5--新增一条语句，并且立刻获取他的主键id（这个用的很多啊，比如餐厅管理）insert into gb_data( username, body, ip) values (‘xiayu222‘,‘ewdfdf‘,‘172.1.1.0‘);select @@IDENTITY--查询语句十分强大，几乎可以查任何东西 --查询SqlServer版本select @@VERSION as 版本 --查询日期select GETDATE()as 日期下面我们来重点说说查询语句 --查询符合条件的记录select *from gb_data where us...
COUNT(*) from person select COUNT(*) from person where age in(18,19,20)--可以跟想要的上一节讲的所有函数--MIN，MAX，Avg，SUMselect MIN(age) from person select max(age) from person select avg(age) from person select sum(age) from person --数据分组---根据年龄分组，然后取出分组后的数据select age,COUNT(*) from person group by age注：如果所用字段没有出现在group by后面，是不能用带select语句中使用的（但是...
ROW_NUMBER() over(order by id)as num,*from gb_data where num>5and num<10--这行代码是错误的，因为系统识别不出来num是多少，为什么呢？ --是因为执行顺序的问题，sql语句是先执行from，然后执行where，最后执行select --正确的写法如下 select *from (select ROW_NUMBER() over(order by id)as num,*from gb_data) as t where t.num>5and t.num<10--子查询（以查询的表作为条件，在基础上进行第二次的查询）查询所有书出版的年...
* from Roles where RoleName like ‘%系统%‘呐尼，怎么一条都没有查出来，明明应该有好几条的。 难道SQL Server不支持模糊查询吗。不可能啊，毕竟是2016版本的啊。 于是就尝试的只用通配符进行查询，有结果。 将SQL语句中的中文部分换成合适的英文，有结果。 估计就是万恶的编码问题喽，先上搜索引擎，得到了一种解决方案，如下：select * from Roles where RoleName like N‘%系统%‘试了一下，查询结果果然正常了。 微软的...
sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on 星期四 3月 13 16:11:03 2014Copyright (c) 1982, 2010, Oracle. All rights reserved.连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> alter user scott identified by tiger;用户已更改。SQL> alter user scott account unlock;用户...