Shulou(Shulou.com)05/31 Report--

This article introduces how to use SQL sentence to add, delete and modify fields and some tables and fields of the basic operation and database backup, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Add, delete and modify fields with SQL statement

1. Add field alter table docdsp add dspcode char (200)

two。 Delete field ALTER TABLE table_NAME DROP COLUMN column_NAME

3. Modify field type ALTER TABLE table_name ALTER COLUMN column_name new_data_type

4.sp_rename renaming changes the name of user-created objects in the current database, such as tables, columns, or user-defined data types. Syntax 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 the 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 way: judge the existence of the table: select count (*) from sysobjects where type='U' and name=' your table name' determine the existence of the field: select count (*) from syscolumns where id= (select id from sysobjects where type='U' and name=' your table name') And name = 'the field name you want to judge' A small example-- suppose the table name to be processed is: tb-- determine whether there is a primary key in the table to which you want to add a column. If exists (select 1 from sysobjects where parent_obj=object_id ('tb') and xtype='PK') begin print' table already has a primary key. A column can only be added as a normal column'- add a column of type int. The default value is 0 alter table tb add column name int default 0 end else begin print'No primary key in the table, add primary key column'- add a column of type int, default value is 0 alter table tb add column name int primary key default 0 end

7. Access syntax for randomly reading several records: 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: schedule five minutes in advance to remind SQL: select * from schedule where datediff (minute,f start time, getdate ()) > 5

9. Select top 10 * form table1 where range of the first 10 records

10. Include all rows in TableA but not in TableB and TableC and eliminate all duplicate rows to derive a result table (select a from tableA) except (select a from tableB) except (select a from tableC)

11. Description: randomly take 10 pieces of data select top 10 * from tablename order by newid ()

twelve。 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: type, vender, pcs fields are listed and arranged in type fields. Case can easily achieve multiple choices, 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: initialization table table1 TRUNCATE TABLE table1

16. Description: several advanced query operators A: UNION operator 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 lines are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2. B: the EXCEPT operator EXCEPT operator derives a result table by including all rows in TABLE1 but not in TABLE2 and eliminating all duplicate rows. When ALL is used with EXCEPT (EXCEPT ALL), duplicate lines are not eliminated. The C: INTERSECT operator INTERSECT operator derives a result table by including only the rows in both TABLE1 and TABLE2 and eliminating all duplicate rows. When ALL is used with INTERSECT (INTERSECT ALL), duplicate lines are not eliminated. Note: several query result rows that use operands must be consistent.

17. Description: online view query (table name 1 select a) select * from (SELECT a meme breco c FROM a) T where t.a > 1; 18. Description: the use of between, between limits the scope of query data including boundary values, not between does not include select * from table1 where time between time1 and time2 select a dint, 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. Description: two associated tables to delete the information delete from table1 where not exists (select * from table2 where table1.field1=table2.field1) that has not been found in the secondary table in the primary table

21. Description: copy table (copy structure only, source table name: a new table name: B) (available for Access) method 1: select * into b from a where 11 method 2: select top 0 * into b from a

twenty-two。 Description: copy table (copy data, source table name: a target table name: B) (available for Access) insert into b (a, b, c) select drech e from f from b; 23. Description: copies of tables across databases (specific data use absolute paths) (Access is available) insert into b (a, b, c) select dRecires f from b in 'specific database' where conditions example:.. from b in "& Server.MapPath (". ") &"\ data.mdb "&" where.. "

24. Create a database CREATE DATABASE database-name

25. Description: delete database drop database dbname

twenty-six。 Description: backup sql server-create device USE master EXEC sp_addumpdevice disk, testBack, c:\ mssql7backup\ MyNwind_1.dat-start backing up BACKUP DATABASE pubs TO testBack

twenty-seven。 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 (create a new table using the old table) B:create table tab_new as select col1,col2... From tab_old definition only

twenty-eight。 Description: delete new table: drop table tabname

twenty-nine。 Note: add a column: Alter table tabname add column col type Note: columns can not be deleted after the increase. The data type cannot be changed after the column is added in DB2, and the only thing that can change is to increase the length of the varchar type.

thirty。 Description: add primary key: Alter table tabname add primary key (col) description: delete primary key: Alter table tabname drop primary key (col) 31. Description: create an index: create [unique] index idxname on tabname (col … .) delete index: drop index idxname Note: the index is immutable and must be deleted and rebuilt if you want to change it.

thirty-two。 Description: create view: create view viewname as select statement delete view: drop view viewname

thirty-three。 Description: several simple basic sql statement choices: select * from table1 where range insertion: insert into table1 (field1,field2) values (value1,value2) deletion: delete from table1 where scope update: update table1 set field1=value1 where scope lookup: select * from table1 where field1 like'% value1%'-like syntax is very subtle Check the data! 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 minimum: select min (field1) as minvalue from table1

thirty-four。 Database backup: the copy 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 = 'Zhuan SQL Server backups, NAME =' Full Backup of DB_ndmspmasterdb'; GO

On how to use SQL statements to add, delete, modify fields and some tables and fields of the basic operations and database backup to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.