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'
Select count (1) from sysobjects where xtype = 'V'
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
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
1/cast (field as numeric (5, 2 ))
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
Drop table # tempT
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