In the online collation of a cow's information, collected with this to share with June
To add a delete modification field with an SQL statement
1. Add fields
ALTER TABLE [Yourtablename] ADD [newcolumnname] newcolumntype (length) Default 0 not null;2. Delete Field
ALTER TABLE [Yourtablename] DROP COLUMN [ColumnName];
3. Modify the field type
ALTER TABLE [YOURTABLENAME] Alter COLUMN [ColumnName] newcolumntype (length) 4. Change the name of the user-created object (such as a table, column, or user-defined data type) in the current database. (sp_rename)
Grammar
sp_rename [@objname =] ' object_name ',
[@newname =] ' New_name ' such as: EXEC sp_rename ' newname ', ' Partstock ' 5. Show some basic information about the table (sp_help)
Sp_help ' object_name '
such as: EXEC sp_help [yourtablename]6. To determine whether a field [ColumnA] exists in a table [Yourtablename]
if exists (SELECT * from syscolumns where
id=object_id (' [Yourtablename] ') and name= ' ColumnA ')
print ' [Yourtablename] exists '
Elseprint ' [yourtablename] not exists '
Another method:
To determine the existence of a table:
Select COUNT (*) from sysobjects where type= ' U ' and name= ' your table name '
To determine the existence of a field:
Select COUNT (*) from syscolumns
WHERE id = (select id from sysobjects where type= ' u ' and name= ' your table name ') and name = ' You want to judge the field name '
A small example
--Suppose the table name to be processed is:
Tb
--Determine if the table in which you want to add a column has a primary key
if exists (select 1 from sysobjects where
parent_obj=object_id (' TB ') and xtype= ' PK ')
Begin
Print
' There are already primary keys in the table and columns can only be added as normal columns '
--Add a column of type int, the default value is 0
ALTER TABLE TB add column name int default
0
End
Else
Begin
No primary key in print ' table, add primary key column '
--Add a column of type int, the default value is 0
Alter
Table TB Add column name int primary key default 0
End
7. Random reading of several records
Access syntax: SELECT top * FROM table name ORDER by
Rnd (ID)
SQL server:select Top n * FROM table name ORDER by NEWID ()
MySQL select * FROM table name Order by rand () Limit n
8. Description: Schedule five minutes advance reminder
Sql:
SELECT * FROM Schedule where DATEDIFF (minute,f start time, GETDATE ()) >5
9. Top 10 Records
Select Top Ten * form table1 where range
10. A result table is derived from all rows in TableA but not in TableB and TableC and all duplicate rows are eliminated
(Select a
From TableA) except (select a from TableB) except (select a from TableC)
11. Description: Randomly remove 10 data
Select Top * FROM tablename ORDER by NEWID ()
12. List all table names in the database
Select name from sysobjects where type=u
13. List all the field names in the table
Select name from syscolumns where
ID=OBJECT_ID (TableName)
14. Description: The type, Vender, and PCs fields are listed in the Type field, and case can be easily implemented with multiple choices, similar to select
Case in the.
Select Type,sum (Case vender if A then PCs else 0
End), sum (case vender if C then PCs else 0 end), sum (case vender if B then PCs
else 0 end) from the TableName group by type
15. Description: Initialization table table1
TRUNCATE
TABLE table1
16. Description: Several advanced query arithmetic words
A:union operator
The UNION operator combines the other two table of results (for example,
TABLE1 and TABLE2) and eliminate any duplicate rows in the table to derive a result table. When all is used with the Union (that is, the Union
All), do not eliminate duplicate rows. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.
B:except operator
The EXCEPT operator by including all in TABLE1 but not in TABLE2
The rows in a row and eliminate all duplicate rows to derive a result table. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated.
C:intersect operator
The INTERSECT operator by including only TABLE1 and TABLE2
A result table is derived from all rows in the row and all duplicate rows are eliminated. When all is used with INTERSECT (INTERSECT
All), do not eliminate duplicate rows.
Note: Several query result rows that use an operation word must be consistent. 17. Description: Online view query (table name 1:a)
SELECT * FROM (select A,b,c from a) T where
T.A > 1;
18. Description: The use of between, between limits the query data range when the boundary value is included, not between does not include
SELECT * FROM
Table1 where time between time1 and time2
Select A,b,c, from table1
Where a not between value 1 and value 2
19. Description: How to use
SELECT * FROM table1 where a [not] in
(' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')
20. Description: Two related tables, delete information in the primary table that is not already in the secondary table
Delete from table1 where NOT exists (
SELECT * FROM Table2 where table1.field1=table2.field1
)
21. Description: Copy table (copy structure only, source table name: A new table name: B) (Access available)
Law one: SELECT * into B from a
where 1<>1
Law II: SELECT top 0 * into B from a
22. Description: Copy table (copy data, source table name: A target table name: B) (Access available)
Insert into B (A, B, c)
Select d,e,f from B;
23. Description: Copy of table across databases (use absolute path for specific data) (Access available)
Insert into B (A, B, c)
Select d,e,f from B in ' specific database ' where condition
Example:.. From B in
"&server.mappath" (".") & "\data.mdb" & "where".
24. Create a Database
CREATE DATABASE Database-name
25. Description: Delete Database
Drop Database dbname
26. Description: Back up SQL Server
---Create
Device for backing up data
Use master
EXEC sp_addumpdevice disk, Testback,
C:\mssql7backup\MyNwind_1.dat
---start Backup
BACKUP DATABASE pubs to Testback
27. Description: Create a new table
CREATE TABLE TabName (col1 type1 [NOT NULL] [primary
Key],col2 type2 [NOT NULL],..)
To create a new table from an existing table:
A:create Table Tab_new
Like Tab_old (create new table with old table)
B:create table tab_new as Select Col1,col2 ...
From Tab_old definition only
28. Description:
Delete new table: Drop table TabName
29. Description:
Add a column: Alter table tabname add column col type
Note: Columns cannot be deleted after they are added. DB2 the column plus the data type can not be changed, the only change is to increase the length of the varchar type.
30. Description:
Add primary key: Alter table TabName Add primary key (COL)
Description
Delete primary key: Alter table tabname drop primary key (COL)
31. Description:
Creating an index: Create [unique] index idxname on tabname (col ...)
Drop INDEX: Idxname
Note: The index is immutable and you must remove the rebuild if you want to change it.
32. Description:
Creating views: Create VIEW viewname AS SELECT statement
Delete view: Drop view
ViewName
33. Description: A few simple basic SQL statements
Select: SELECT * FROM table1 where range
Insert: INSERT INTO
Table1 (field1,field2) VALUES (value1,value2)
Delete: Delete from table1 where
Range
Updated: Update table1 set field1=value1 where range
Find: SELECT * FROM table1
where field1 like '%value1% '---the syntax of like is very subtle, check the information!
Sort: SELECT * FROM table1
ORDER BY FIELD1,FIELD2 [DESC]
Total: SELECT Count * as TotalCount from
Table1
Sum: Select SUM (field1) as Sumvalue from table1
Average: Select
AVG (field1) as Avgvalue from table1
Maximum: Select Max (field1) as MaxValue from
Table1
Min: select min (field1) as MinValue from table1
Note: Delete the default value for a field in a table (first name of this field default constraint is queried and then deleted)
1. Query the name of the field default constraint (T1 is the table name, ID is the field name)
Select A.name as User table, b.name as field name, D.name as field default value constraint
From sysobjects a,syscolumns b,syscomments c,sysobjects D
where A.id=b.id and B.cdefault=c.id and C.id=d.id
and a.name= ' T1 ' and b.name= ' id '
2. Delete the default value constraint for the ID field (df_t1_id is the constraint name)
ALTER TABLE T1 DROP CONSTRAINT df_t1_id
Modify field default values
--(1) See if a field in a table has a default value constraint
Select A.name as User table, b.name as field name, D.name as field default value constraint
From sysobjects a
INNER JOIN syscolumns B on (a.id=b.id)
INNER JOIN syscomments C on (b.cdefault=c.id)
INNER JOIN sysobjects D on (c.id=d.id)
where A.name= ' tb_fqsj ' and b.name= ' discharge slogan '
--(2) If there is a default value constraint, delete the corresponding default value constraint
DECLARE @tablename varchar (30)
DECLARE @fieldname varchar (50)
DECLARE @sql varchar (300)
Set @tablename = ' TB_FQSJ '
Set @fieldname = ' Drain number '
Set @sql = ' '
Select @[email protected]+ '
ALTER TABLE [' +a.name+ '] drop constraint [' +d.name+ ']
From sysobjects a
Join syscolumns B on a.id=b.id
Join syscomments C on b.cdefault=c.id
Join sysobjects D on c.id=d.id
where [email protected] and [email protected]
EXEC (@sql)
--(3) Adding a default value constraint
ALTER TABLE TB_FQSJ
ADD DEFAULT ('% ') for drain number with VALUES
--Create tables and descriptive information
Create TABLE table (A1 varchar), A2 char (2))
--Add descriptive information to the table
EXECUTE sp_addextendedproperty n ' ms_description ', ' Personnel information table ', n ' user ', n ' dbo ', n ' table ', n ' tables ', NULL, NULL
--Add descriptive information for field A1
EXECUTE sp_addextendedproperty n ' ms_description ', ' name ', n ' user ', n ' dbo ', n ' table ', n ' tables ', n ' column ', n ' A1 ‘
--Add descriptive information for field A2
EXECUTE sp_addextendedproperty n ' ms_description ', ' Gender ', n ' user ', n ' dbo ', n ' table ', n ' tables ', n ' column ', n ' A2 ‘
--Update the Description property of the column A1 in the table:
EXEC sp_updateextendedproperty ' ms_description ', ' Field 1 ', ' user ', dbo, ' table ', ' table ', ' column ', A1
--Delete The Description property of the column A1 in the table:
EXEC sp_dropextendedproperty ' ms_description ', ' user ', dbo, ' table ', ' Tables ', ' column ', A1
--Delete test
drop table
T-SQL operation table structure (GO)
