Add a delete modify field with an SQL statement
1. Add Field
ALTER TABLE DOCDSP add Dspcode char (200)
2. Delete fields
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.sp_rename renamed
Changes the name of a user-created object (such as a table, column, or user-defined data type) in the current database.
Grammar
sp_rename [@objname =] ' object_name ',
[@newname =] ' New_name '
[, [@objtype =] ' object_type ']
such as: EXEC sp_rename ' newname ', ' Partstock '
5.sp_help shows some basic information about the table
Sp_help ' object_name ' such as: EXEC sp_help ' Partstock '
6. Determine whether a field partvelocity exists in a table Partstock
if exists (SELECT * from syscolumns where id=object_id (' Partstock ') and Name= ' partvelocity ')
print ' partvelocity exists '
else print ' partvelocity NOT EXISTS '
Another law:
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 = ' field name you want to judge '
A small example
--Assume the table name to be processed is: TB
--Determine if there is a primary key in the table to add the column
if exists (select 1 from sysobjects where parent_obj=object_id (' TB ') and xtype= ' PK ')
Begin
The print ' table already has a primary key and the column can only be added as a normal column '
--Add a column of type int with a default value of 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 with a default value of 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. Note: Schedule five minutes advance reminder
Sql:select * from Schedule where DATEDIFF (minute,f start time, GETDATE ()) >5
9. First 10 records
Select top * form table1 where scope
10. To include all rows in TableA but not in TableB and TableC and to eliminate all duplicate rows and derive a result table
(select a from TableA) except (select a-TableB) except (select a from TableC)
11. Note: Randomly remove 10 data
Select Top * FROM tablename ORDER by NEWID ()
12. List all the 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: Listing type, Vender, PCs fields, arranged in type fields, case can be easily implemented with multiple selections, similar to the case in select.
Select Type,sum (case vender then 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. Note: Initialize table table1
TRUNCATE TABLE table1
16. Note: Several advanced query operators
A:union operator
The UNION operator derives a result table by combining the other two result tables (such as TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When all is used with union (that is, union ALL), duplicate rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.
B:except operator
The EXCEPT operator derives a result table by including all rows that are in TABLE1 but not in TABLE2, and all duplicate rows are eliminated. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated.
C:intersect operator
The INTERSECT operator derives a result table by including only the rows in TABLE1 and TABLE2 and eliminates all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated.
Note: Several query result rows that use an operator must be consistent.
17. Note: Online view query (table name 1:a)
SELECT * FROM (select A,b,c from a) T where t.a > 1;
18. Note: The use of between, between limit the range of query data include 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 is not between numeric 1 and value 2
19. Note: How to use in
SELECT * FROM table1 where a [isn't] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')
20. Description: Two related tables, delete the information in the primary table that has not been in the secondary table
Delete from table1 where does exists (SELECT * from table2 where table1.field1=table2.field1)
21. Note: Copy table (copy only structure, source table name: A new table name: B) (Access available)
Law one: SELECT * into B from a where 1<>1
Method Two: Select top 0 * into B from a
22. Note: 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. Note: Copies of tables across databases (use absolute paths 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. Note: Delete database
Drop Database dbname
26. Note: Back up SQL Server
Device to create backup data---
Use master
EXEC sp_addumpdevice disk, Testback, C:\mssql7backup\MyNwind_1.dat
---start Backup
BACKUP DATABASE pubs to Testback
27. Note: 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 a new table using the 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 one column: Alter table tabname add column col type
Note: The column will not be deleted after it has increased. The data type can not be changed when the column in the DB2 is added, and the only change is 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 index: Create [unique] index idxname on tabname (col ...)
Deleting indexes: Drop INDEX Idxname
Note: The index is not to be changed and you want to change the rebuild must be deleted.
32. Description:
Creating a View: Create VIEW viewname AS SELECT statement
Delete view: Drop View ViewName
33. Description: A few simple basic SQL statements
Selection: SELECT * FROM table1 where
Inserting: INSERT INTO table1 (field1,field2) VALUES (value1,value2)
Delete: Delete from table1 where scope
Update: UPDATE table1 set field1=value1 where scope
Find: SELECT * FROM table1 where field1 like '%value1% '---the syntax of like is very subtle, look for information!
Sort: SELECT * from table1 ordered 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
34. Database backup:
Copy Code code as follows:
use Db_ndmspmasterdb;
Go
Declare @path varchar
Set @path = ' D:\NDM_Data\DB_NDMSpMasterdb ' +convert (varchar, GETDATE (), 1 05) + '. Bak '
Select @path
BACKUP DATABASE db_ndmspmasterdb
to DISK = @path
with FORMAT,
MEDIANAME = ' z_sqlserverbackups ',
NAME = ' full Backup of Db_ndmspmasterdb ';
Go
