-------Get table names by field name
SELECT Sb.name
From syscolumns s joins sysobjects SB on s.id=sb.id
WHERE s.name= ' Your field name '
Left join returns records that include all the records in the left table and the equivalent of the junction fields in the right table
Right join returns records that include all records in the right table and the junction fields in the left table
INNER JOIN (equivalent join) returns only rows that have the same join field in two tables
Examples are as follows:
--------------------------------------------
Table A records the following:
AID Anum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115
Table B records the following:
BID bname
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408
--------------------------------------------
1.left Join
The SQL statements are as follows:
SELECT * FROM A
Left JOIN B
On a.aid = B.bid
The results are as follows:
AID Anum BID bname
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 NULL NULL
(The number of rows affected is 5 rows)
Result Description:
The left join is based on the records of Table A, a can be regarded as the right table, and B can be regarded as left table.
In other words, the records of the left table (A) will all be represented, and the right table (B) will only display records that match the search criteria (in the example: A.aid = b.bid).
The low-record of table B is null.
-----------Cycle Table
DECLARE @id int
DECLARE @maxid int
Set @id =3
Select @maxid =max (ID) from table name
Begin
While @id <[email protected]
Begin
Update table Name set avg_value=balance+ (select Avg_value from test where [email protected]) where [email protected]
Set @[email protected]+1
End
End
----------------
Select Sq, count (crbnum) as GS from Mytest3 GROUP by sq
------------------------------------
String converted to int type
Cast (' String type number ' as int)
-------------------------------------
TRUNCATE TABLE table name---empty a single sheet
-------Query a table for the same data in a field
SELECT * from ' table name ' where ' lookup field name ' In (SELECT ' Lookup field name ' from ' table name ' Group by ' lookup field name ' Having (count (*)) >1)
--All table column names
DECLARE @col varchar (1000)
Set @col = ' '
Select @[email protected]+ ', ' +name from syscolumns where id=object_id (' Table name ')
Set @col =stuff (@col, 1, 1, ")
Select @col
Convert (VARCHAR), #SWRQ #,121)--sql turn into month and day
SELECT * FROM sys.servers Querying Database association relationships
--Turn the string upside down
DECLARE @Name NVARCHAR (50)
Set @Name = ' min s-34-25 ' (52-43-s min)
Select Reverse (@Name)
Some notes for SQL Server