You can use SQL statements to add and delete modify fields, perform basic operations on tables and fields, and perform database backup. For more information, see.
You can use SQL statements to add and delete modify fields, perform basic operations on tables and fields, and perform database backup. For more information, see.
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 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: 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 a 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 fields of type, vender, and pcs, which are arranged by the type field. case can be easily selected, similar to case in 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 (such as TABLE1 and TABLE2) and removes any duplicate rows from the table to generate a result table. When ALL is used together with UNION (that is, 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 distinct t operator derives a result table by including all rows in Table 1 but not in table 2 and eliminating all repeated rows. When ALL is used with distinct T (distinct t all), duplicate rows are not eliminated.
C: INTERSECT Operator
The INTERSECT operator derives a result table by only including the rows in TABLE1 and TABLE2 and eliminating all repeated rows. 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 a 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 a device for the backup 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],...)
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 into 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 very subtle, query 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
34. Database Backup:
The Code is as follows:
USE DB_ndmspmasterdb;
GO
Declare @ path varchar (500)
Set @ path = 'd: \ NDM_Data \ DB_NDMSpMasterdb '+ CONVERT (VARCHAR, GETDATE (), 105) +'. Bak'
Select @ path
Backup database DB_ndmspmasterdb
To disk = @ path
With format,
MEDIANAME = 'z _ sqlserverbackup ',
NAME = 'full Backup of db_ndmspmasterdb ';
GO
