SQL Server is not very common. tsql statements that may be common but have doubts

Source: Internet
Author: User
At 16:01:58, January 1, October 29, 2013, when the data has a time column, such as the call duration, it is inconvenient for us to query. we can add a redundant column to calculate the number of seconds, now, I want to change the time for example 00:12:23 to the second. For example, if the column bridgeDuration is the duration, we will change it to the second and save it here.

At 16:01:58, January 1, October 29, 2013, when the data has a time column, such as the call duration, it is inconvenient for us to query. we can add a redundant column to calculate the number of seconds, now, I want to change the time for example 00:12:23 to the second. For example, if the column bridgeDuration is the duration, we will change it to the second and save it here.

October 29, 2013 16:01:58

When the data has a time column, such as the call duration, it is inconvenient for us to query. we can add a redundant column to calculate the number of seconds, now I want to change the time for example 00:12:23 to seconds.

For example, if the column bridgeDuration is a duration, we replace it with seconds and save it here.

UPDATE dbo. phoneRecords SET bridgeDurationInt = (CAST (LEFT (bridgeDuration, 2) AS int) * 3600) + (CAST (RIGHT (LEFT (bridgeDuration, 5), 2) AS int) * 60) + CAST (RIGHT (LEFT (bridgeDuration, 8), 2) AS int)

September 24, 2013 16:47:55

For cross-server queries, you must first add a server Link

The following IP addresses can be replaced
EXEC sp_droplinkedsrvlogin '192. 168.10.150 ', NULL
EXEC sp_dropserver '192. 168.10.150'

EXEC sp_addrole server '192. 168.10.150 ', '', 'sqloledb'," 10.50.1790.0"
EXEC sp_add1_srvlogin '192. 168.10.150 ', 'false', null, 'it', 'its123'

September 16, 2013 9:58:40 increase

View the number of objects in SQL server:
Number of tables
Select count (1) from sysobjects where xtype = 'U'
View:
Select count (1) from sysobjects where xtype = 'V'
Stored Procedure
Select count (1) from sysobjects where xtype = 'P'

I wrote a small example myself. When publishing and subscribing, I compared the tables in the two databases and gave them the same name.

Select * from database 1. dbo. sysobjects as crminner join database 2. dbo. sysobjects as cppon crm. name = cpp. namewhere crm. xtype = 'U' and cpp. xtype = 'U'

1. Add columns to a table

For example

Alter table dbo. table name add EmployeeName nvarchar (50) default 'none' not null, EmployeeId int default 0 not null

2. Copy the new table

SELECT * INTO new table FROM old table

Of course, the new table = database name. Architecture name. Table name, the old table is also

You can copy tables across databases.

3. View All column names in a table

Select Name from SysColumns where id = Object_id ('table name ')

4. Calculate the percentage of two columns, denominatorPossible0

Select conv = case when
Field = 0 then 0
Else
1/cast (field as numeric (5, 2 ))
End
From table where other conditions

5. Add an auto-increment column (sort column) to the result)

Assume that the name of a temporary table is # tempT.

Select identity (int, 1, 1) as id, * into # tempT from (another select result set, or table name)

Delete temporary table

If object_id ('tempdb .. # tempt') is not null
Begin
Drop table # tempT
End

6. database role Permissions

Db_owner executes all maintenance and configuration activities in the database.
Db_accessadmin adds or deletes Windows users, groups, and SQL Server logon permissions.
Db_datareader reads all data from all user tables.
Db_datawriter adds, deletes, or changes data in all user tables.
Db_ddladmin runs any Data Definition Language (DDL) command in the database.
Db_securityadmin: Modify the role Member identity and manage permissions.
Db_backupoperator: Back up the database.
Db_denydatareader cannot read any data in the database user table.
Db_denydatawriter cannot add, modify, or delete data in any user table or view.

7. Modify the sa User Password

Exec sp_password null, '000', 'sa'

8. SQLServer Update multi-condition multi-table join Update

update Customer set employeeid=0,employeeName='' where Id in(14775,14776,14778)select employeeid,employeeName from dbo.Customer  as c where c.Id in(14775,14776,14778)update A set A.EmployeeId=B.EmployeeId,A.EmployeeName=B.EmployeeName from (SELECT *  FROM dbo.Customer ) Ainner join (SELECT * FROM Contact as bb WHERE bb.CustomerId in(14775,14776,14778))B on A.Id=B.CustomerIdselect employeeid,employeeName from dbo.Contact  as c where c.CustomerId in(14775,14776,14778)select employeeid,employeeName from dbo.Customer  as c where c.Id in(14775,14776,14778)

9. mysql writing

UPDATE `visitlegalizelog`,`visituser` SET visitlegalizelog.Tel = visituser.Tel WHERE visitlegalizelog.PhoneMac = visituser.PhoneMac 

  

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.