SQL Structured Query Language notes (i)

1. New table:
CREATE TABLE table_name

(
[AutoNumber field] int IDENTITY (PRIMARYKEY),
[Field 1] NVarChar (+) default \ ' defaults \ ' null,
[Field 2] ntext null,
[Field 3] datetime,
[Field 4] money null,
[Field 5] int default 0,
[Field 6] Decimal (12,4) default 0,
[Field 7], image null,
)

2. Delete the table:

DROP Table table_name

3. Insert Data:
INSERT into table_name (field 1, Field 2) VALUES (value 1, value 2)

4. Delete data:
DELETE from [table name] WHERE condition

5. Update data:
UPDATE [table name] SET [Field 1] = value 1,[Field 2] = value 2 WHERE condition

6. New fields:
ALTER table [table name] ADD [field name] NVARCHAR () NULL

7. Delete fields:
ALTER table [table name] dropcolumn [field name]

8. Modify the fields:
ALTER table [table name] altercolumn [field name]nvarchar () NULL

9. Modify the table name:

EXEC sp_rename old table name, new table name

10. New constraint:
ALTER table [table name] addconstraint constraint name CHECK ([constraint field] <=\ ' 2000-1-1\ ')

11. Delete the constraint:
ALTER table [table name] dropconstraint constraint name

12. Check whether the table exists

1) Determine if the data table exists

Method One:

If object_id (n ' tablename ', n ' U ') is not null
print ' exists '
Else
print ' does not exist '

For example:
Use Fireweb;
Go

If object_id (n ' temp_tbl ', n ' U ') is not null
print ' exists '
Else
print ' does not exist '

Method Two:

IF EXISTS (SELECT * FROM dbo. SysObjects WHERE ID =object_id (N ' [table name] ') and OBJECTPROPERTY (ID, ' istable ') = 1)
PRINT ' exists '
ELSE
PRINT ' does not exist '

For example:
Use Fireweb;
Go

IF EXISTS (SELECT * FROM dbo. SysObjects WHERE ID =object_id (N ' temp_tbl ') and OBJECTPROPERTY (ID, ' istable ') = 1)
PRINT ' exists '
ELSE
PRINT ' does not exist '

2) whether the temporary table exists:

Method One:
if exists (SELECT * from tempdb. sysobjects whereid=object_id (' tempdb.. # #TEMP_TBL '))
PRINT ' exists '
ELSE
PRINT ' does not exist '

Method Two:
Use Fireweb;
Go

Ifexists (SELECT * from tempdb.dbo.sysobjects where ID =object_id (N ' tempdb: #TEMP_TBL ') and type= ' U ')
PRINT ' exists '
ELSE
PRINT ' does not exist '

13. Display table structure, fields, constraints

EXEC sp_help [table name]

14. Connector + and | |

+:sql Server String Connector

|| : Orcale string Connector

If it is not a string, you need to convert the other type to a string type with the cast function before you can continue to use the connector;

15. Null value Query

Is null and is not NULL

16. Random Sorting

Sqlserver:order by NEWID ()

SQL Structured Query Language notes (i)