2024/3/29

上午杨子光老师的数据库原理课程，下午计算机网络。

今天学习了sql语句中最难的部分查询语句select

课下查找的查询语句总结：

–查询语句
SELECT * FROM BASE_USER;
–查询前10条语句
SELECT TOP 10 * FROM BASE_USER;
–允许脏读查询
SELECT * FROM BASE_USER WITH(NOLOCK);
–获取当前数据库名称
SELECT DB_NAME(dbid) as dbName FROM master.dbo.sysprocesses WHERE STATUS=‘runnable’;
–获取当前日期与时间
SELECT GETDATE();
–获取当前月
SELECT DATEPART(month, getdate());
–获取当前年
SELECT DATEPART(year, getdate());
–获取倒推一个月的日期
SELECT CONVERT(varchar(10), CONVERT(varchar(8),dateadd(month,-1,getdate()),23)+Datename(day,GetDate()) , 120);
–字符串截取与定位
SELECT substring(SERVICEGROUP_,4,1)，CHARINDEX(‘Partner’,STAFFLEVEL) FROM BASE_USER;
–字符替换
SELECT REPLACE(A.NAME,‘11’,‘22’) FROM TD_STUDENTS;
–检查数据库连接数
select count(*) from master.dbo.sysprocesses ;
–查询数据库版本
SELECT @@VERSION;

–最近七天

select convert(varchar(10),getdate() - 7,120)；

–表与数据复制

select * into DataTable_20150717
from DataTable

–查询表名

SELECT ‘DELETE FROM ’ +name+’;’ FROM sysobjects WHERE sysobjects.type=‘U’;

–获取行号
row_number()over(order by A.id)

–拼接列
CONCAT(tc.operate,tc.operaterType)

–检查锁表
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks t where resource_type=‘OBJECT’ order by spid asc;

–查询表记录数
SELECT a.name, b.rows
FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id
WHERE (a.type = ‘u’) AND (b.indid IN (0, 1))
ORDER BY a.name,b.rows DESC