SQL Common Statement Collation

There are three database connection queries on the last page of the written examination, not written, by the examiner's sarcasm. Now I want to, the beginning of the internship, really low. The company has just entered the time, responsible for ETL, so and the database played a lot of dealings, 50 rows of the joint query, hundreds of rows of stored procedures are very common, cursors, views, index frequent contact, including in the SQL breakpoint debugging, measurement and so on, here will contact the understanding of the collation, do a small summary, Slowly accumulate, the cursor, the view and so on also makes a simple explanation. Sometimes I get the same problem, and I forget how I solved the problem last time, until I spent a lot of time and effort to find a solution again. This is very wasteful, so all the problems encountered, the solution to the solution, I forced myself to write a note, the general idea to summarize, in order to facilitate their timely review. After all, good memory is inferior to the written, the ancients sincerity do not bully me also!

1, database common three kinds of data model: level, network, relationship

2. Three types of integrity constraints for relationships: Entity integrity (primary key NOT NULL), referential integrity, user-defined integrity

3, commonly used easy to forget data types:

①int 4byte = 32bit A Chinese character 2 bytes, Chinese punctuation, full-width two bytes; letters, English punctuation, half-width of one byte

②char fixed-length non-Unicode character data with a maximum length of 8,000 characters

③varchar variable-length non-Unicode character data with a maximum length of 8,000 characters and a storage size of the actual length of the input data

④nchar fixed-length Unicode character data with a maximum length of 4,000 characters and a storage size of twice times the actual length

⑤nvarchar variable-length Unicode character data with a maximum length of 4,000 characters and a storage size of twice times the actual length

4. Common Query Operations

--Check the line 8th to 11th Records--SELECT *  fromIBBC.dbo.Table_1 asTWHEREIddbetween 108  and 111SELECT TOP 4 *  fromIBBC.dbo.Table_1 asTWHERET.idd not inch(SELECT TOP 7Idd fromIbbc.dbo.Table_1ORDER  byIbbc.dbo.Table_1.IDD)--determine if a table existsIF exists(Select *  fromDbo.sysobjectswhereId= object_id(N'[dbo]. [Table_22]'))--Determine if a table field existsIF EXISTS(Select *  fromsyscolumnswhereId=object_id('Table_1') andName='IDD')--determine if a stored procedure existsIf exists(Select *  fromDbo.sysobjectswhereId= object_id(N'[dbo]. [Test]')) --To determine whether a view existsif exists(Select *  fromDbo.sysobjectswhereId= object_id(N'[dbo]. [V_test]'))

5. Common table operation

--Add fieldsALTER TABLEIBBC.dbo.Table_1ADDYYYCHAR(5) --Delete FieldALTER TABLEIBBC.dbo.Table_1DROP COLUMNYYY--change Field typeALTER TABLEIBBC.dbo.Table_1ALTER COLUMNb_idINT  not NULL--Specify a field to add a descriptionexecSp_addextendedproperty N'ms_description'N'I'm a descriptive message .'N'User'N'dbo'N'Table'N'Table_1'N'column'N'YYY'--Add primary KeyALTER TABLEIBBC.dbo.Table_2ADD CONSTRAINTTest_mainPRIMARY KEY(b_id)--Delete primary KeyALTER TABLEIBBC.dbo.Table_2DROP CONSTRAINTTest_main--Add foreign KeyAlter TableIBBC.dbo.Table_1Add constraintTest_foreignForeign Key(bas_id)ReferencesIBBC.dbo.Table_2 (b_id)--Delete foreign keyALTER TABLEIBBC.dbo.Table_1DROP CONSTRAINTTest_foreign

SQL Common Statement Collation