Use an SQL statement to add or delete a modified Field
1. Add a field
Alter table docdsp Add dspcode
Char (200)
2. delete a field
Alter table table_name drop Column
Column_name
3. Modify the field type
Alter table table_name Alter Column
Column_name new_data_type
4. Change the name of sp_rename.
Change the name of the user-created object (such as a table, column, or user-defined data type) in the current database.
Syntax
Sp_rename [@ objname =]
'Object _ name ',
[@ Newname =] 'new _ name'
[, [@ Objtype =
] 'Object _ type']
For example, exec sp_rename
'Newname', 'partstock' 5. sp_help displays some basic information about the table.
Sp_help 'object _ name'
For example, exec sp_help 'Partstock'
6. Check whether the partvelocity field exists in partstock of a table.
If
Exists (Select * From Syscolumns Where
Id = object_id ('partstock ') And Name = 'partvelocity ')
Print
'Partvelocity exists'
Else print 'partvelocity not
Exists'
Another method:
Determine the existence of a table:
Select count (*) from sysobjects where type = 'U'
And name = 'your table name'
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 = 'field name to be judged'
A small example
-- Assume that the table to be processed is named:
TB
-- Determines whether the table to be added has a primary key.
If exists (select 1 from sysobjects where
Parent_obj = object_id ('tb') and xtype = 'pk ')
Begin
Print
'The table already has a primary key, and the column can only be added as a normal column'
-- Add an int column. The default value is 0.
Alter table tb add column name int default
0
End
Else
Begin
Print 'table with no primary key, add primary key column'
-- Add an int column. The default value is 0.
Alter
Table TB add column name int primary key default 0
End
7. Read several records randomly
Access Syntax: Select top 10 * from table name order
RND (ID)
SQL Server: Select Top N * from table name order by newid ()
MySQL select * from table name order by rand () limit n
8. Note: Five minutes ahead of schedule reminder
SQL:
Select * from Schedule where datediff (minute, F Start Time, getdate ()> 5
9. The first 10 records
Select top 10 * Form Table1 where range
10. Include all rows in tablea but not in tableb and tablec and eliminate all repeated rows to derive a result table.
(Select
From tablea) Before t (select a from tableb) Before t (select a from tablec)
11. Description: 10 data records are randomly taken out.
Select top 10 * From tablename order by newid ()
12. list all table names in the database
Select name from sysobjects where type = u
13. list all field names in the table
Select name from syscolumns where
Id = object_id (tablename)
14. Description: lists the type, vender, and PCs fields, which are arranged by the Type field. Multiple options can be easily selected by case, similar to select
.
Select Type, sum (Case vender when a then PCs else 0
End), sum (Case vender when C then PCs else 0 end), sum (Case vender when B then PCs
Else 0 end) from tablename group by type
15. Description: Initialize table 1
Truncate
Table Table1
16. Description: several advanced query Operators
A: Union operator
The Union operator combines two other result tables (for example
Table1 and table2) and remove any duplicate rows in the table to generate a result table. When all is used with union
All). Duplicate rows are not eliminated. In either case, each row of the derived table is from either Table1 or table2.
B: Random t operator
The except t operator includes all
And eliminate all duplicate rows to derive a result table. When all is used with distinct T (distinct t all), duplicate rows are not eliminated.
C: intersect Operator
Intersect Operator
All rows in the table and all duplicate rows are eliminated to derive a result table. When all is used with intersect (intersect
All). Duplicate rows are not eliminated.
Note: The query results of several computation words must be consistent.17. Description: Online View query (table name 1:)
Select * from (select a, B, c from a) t where
T. A> 1;
18. Description: between usage. When between restricts the range of queried data, it includes the boundary value. 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 in
Select * From Table1 where a [not] In
('Value 1', 'value 2', 'value 4', 'value 6 ')
20. Note: two associated tables are used to delete information that is not in the primary table.
Delete from Table1 where not exists (
Select * From Table2 where table1.field1 = table2.field1
)
21. Description: copy a table (only copy structure, source table name: a new table name: B) (access available)
Method 1: Select * into B from
Where 1 <> 1
Method 2: Select top 0 * into B from
22. Description: copy a 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. Note: Table Copying across databases (absolute path for specific data) (access is 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 a database.
Drop database dbname
26. Description: Back up SQL Server
--- Create
Device for Data Backup
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],...)
Create a new table based on an existing table:
A: Create Table tab_new
Like tab_old (use the old table to create a new table)
B: Create Table tab_new as select col1, col2...
From tab_old definition only
28. Note:
Delete a new table: Drop table tabname
29. Note:
Add a column: alter table tabname add column col
Type
Note: Columns cannot be deleted after they are added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the length of the varchar type.
30. Note:
Add primary key: alter table tabname add primary key (COL)
Note:
Delete primary key: alter table tabname drop primary key (COL)
31. Note:
Create an index: Create [unique] index idxname on tabname (COL ....)
Delete index: drop index idxname
Note: The index cannot be changed. To change the index, you must delete it and recreate it.
32. Note:
Create view: Create view viewname as select statement
Delete view: Drop View
Viewname
33. Description: several simple basic SQL statements
Select: Select * From Table1 where range
Insert: insert
Table1 (field1, field2) values (value1, value2)
Delete: delete from Table1 where
Range
Update: Update Table1 set field1 = value1 where range
Search: Select * From Table1
Where field1 like '% value1 %' --- the like syntax is exquisite. query 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
MAX: Select max (field1) as maxvalue from
Table1
Min: select Min (field1) as minvalue from Table1 Note: delete the default value of a field in a table (first query the name of the default value constraint for this field, and then delete it)
1. Name of the default query field constraint (T1 is the table name and 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 of the ID field (df_t1_id is the constraint name)
Alter table T1 drop constraint df_t1_id
Modify Field Default Value
-- (1) check whether 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
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 = 'sewage slog'
-- (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 = 'sewage slog'
Set @ SQL =''
Select @ SQL = @ SQL +'
Alter table ['+ A. Name +'] Drop constraint ['+ D. Name +']'
From sysobjects
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 a. Name = @ tablename and B. Name = @ fieldname
Exec (@ SQL)
-- (3) add default value Constraints
Alter table tb_fqsj
Add default ('01') for sewage slogan with values
-- Create a table and its description
Create Table (A1 varchar (10), A2 char (2 ))
-- Add description information for the table
Execute sp_addextendedproperty n 'Ms _ description', 'personnel info table ', N 'user', N 'dbo', N 'table', N 'table', null, null
-- Add description information for field A1
Execute sp_addextendedproperty n 'Ms _ description', 'name', N 'user', N 'dbo', N 'table', N 'table', N 'column ', N 'a1'
-- Add description information for field A2
Execute sp_addextendedproperty n 'Ms _ description', 'gender', N 'user', N 'dbo', N 'table', N 'table', N 'column ', N 'a2'
-- Update the description attribute of column A1 in the table:
Exec sp_updateextendedproperty 'Ms _ description', 'field 1', 'user', DBO, 'table', 'table', 'column', A1
-- Delete the description attribute of column A1 in the table:
Exec sp_dropextendedproperty 'Ms _ description', 'user', DBO, 'table', 'table', 'column', A1
-- Delete test
Drop table
