SQL Notes/Paging stored procedures

[Email protected]
The + + operation in C # can be an integer or a decimal, and only the integer + + operation in SQL.
char type suitable for storage length fluctuation small non-recyclable efficiency high
varchar type suitable for storage length fluctuation large can be recycled
NCHAR represents Unicode storage content including Chinese characters when considering adding n

SQL statement Features
1 case insensitive
2 No double quotes all strings are enclosed in single quotes
3 There is no logical equality, and the same is true for logical equality and assignment.
4 no bool is worth the concept, but you can enter true/false in the view
5 also has a relational operator.
6 There are also logical operators &&--and | | --or! --not
7 Explanatory language, not a compiled type

--ensure data integrity: Ensure that the data is real and effective--Four types of integrity:--1. Entity integrity: An entity is a row of records that refers to a table. It is the guarantee that each row of the table is not duplicated and is unique. --PRIMARY key: Unique, non-null. A table can have only one primary key. A key combination is a primary key that consists of multiple fields. --identity columns: The system automatically generates, never repeats, and often identifies columns that are used for primary keys. --Unique Key: Tag The value of this field cannot be duplicated. But can be null, only one time    --2. Domain integrity: A domain is a field that guarantees that a column's values are accurate and valid. --Implementation mode: type, non-null, check constraint, default value, relationship (primary foreign key)    --3. Custom Integrity:--CHECK constraint "stored procedure trigger"--4. Referential integrity: The value of one field of a table refers to a field value from another table--primary FOREIGN KEY constraint--relationship--1. The type of field to establish the relationship needs to be consistent--2. The meaning of the field that establishes the relationship is consistent--3. The referenced table is the primary key table, and the referenced table is the foreign key table--4. Creating a primary foreign key relationship requires the creation of an external key table. --5. The field in which the primary foreign key relationship is created must be a primary key or a unique key--6. Add the primary key table before adding the foreign key table when adding data--7. Delete the data from the foreign key table before deleting the data from the primary key table.--To create data integrity using code:--PRIMARY KEY constraint (primary key PK) Uniqueness key constraint (unique UQ) default value constraint (default DF) Check constraint (check CK) primary FOREIGN KEY constraint (foreign key FK)--Syntax:--ALTER TABLE table name--add constraint constraint name (prefix + custom name) constraint type constraint description (field name expression value)--set a PRIMARY KEY constraint for an IDAlter TableTeacherAdd constraintpk_teacher_idPrimary Key(Id)--add a unique key constraint to an emailif exists(Select *  fromsysobjectswhereName='Uq_teacher_email') Alter TableTeacherDrop constraintUq_teacher_emailGo Alter TableTeacherAdd constraintUq_teacher_emailUnique(email)--Add a default value for payroll, add a Check constraint for ageAlter TableTeacherAdd constraintDf_teacher_salarydefault( the) forsalary,constraintCk_teacher_ageCheck(age>0  andAge<= -)---Add primary foreign KEY constraintAlter TableTeacher with Nocheck  --do not check existing dataAdd constraintFk_teacher_classes_classidForeign Key(ClassID)Referencesclasses (CID) on Delete Set NULL--On delete|update--no action|cascade|set null|set default--for relationships, there are cascading actions:--1. Do nothing: Delete If you can delete the error if you can't delete it--2. Cascade: Delete The main table record, will also delete the corresponding foreign key table record--3. Set NULL: Deleting the primary table record sets the value of the record corresponding to the foreign key table to null. If this field value can be null--4. Set default: Deleting a primary table record sets the value of the record corresponding to the foreign key table to the default value. If the default value is set before this field value

 UseMasterif exists(Select *  fromsysdatabaseswhereName='School')Drop DatabaseSchoolexecsp_configure'Show advanced Options',1Reconfigureexecsp_configure'xp_cmdshell',1Reconfigureexecxp_cmdshell'mkdir D:\database automatically created'Create DatabaseSchool on Primary(Name='School_data.mdf', size=3MB, FileGrowth=Ten%, MaxSize=100mb, filename='d:\database automatically create \school_data.mdf')Log  on(Name='School_log.ldf', size=3MB, FileGrowth=Ten%, MaxSize=100mb, filename='d:\database automatically create \school_log.ldf')

Code Creation Database

Paging Stored Procedure ***************************************
If exists(Select *  fromsysobjectswhereName='Usp_person')Drop procedureUsp_personGoCreate procedureUsp_person@pagtotlecount intoutput,@pageindex int=1,@pagecount int =Ten asSelect *  from(SelectRow_number () Over(Order  bypid asId*  from [ Person]) as TempwhereIdbetween@pagecount*@pageindex-@pagecount+1 and@pageindex*@pagecountSelect @pagtotlecount=CEILING(COUNT(*)*1.0/@pagecount) from [ Person]Print @pagtotlecountGoDeclare @pagetotlenumber intExecuteUsp_person@pagetotlenumberOutput1,TenGo

Insert duplicate value trigger *************************************************

--Person Table Insert Duplicate value trigger if exists(Select *  fromsysobjectswhereName='Tr_person_insert') Drop TriggerTr_person_insertGo Create TriggerTr_person_insert onPerson afterInsert  as Declare @pcid int,@pid int Select @pcid=Inserted. PCID,@pid=Inserted. Pid frominsertedif( not exists(Select *  fromClasseswhereCid=@pcid)) begin Delete  from [ Person] wherePid=@pcid Print 'inserting a class that fails to exist' End Go

Insert focused non-focused index

--Create an index
--Index: To improve the efficiency of the query. Equivalent to a reference that can quickly find the location of the data
--Clustered index: The order of the indexes corresponds to the order of the content, so the clustered index means the ordering
--Nonclustered indexes: there is no intrinsic correlation between the order of indexes and the order of content
--Syntax:
--create clustered|nonclustered Index Ix_ name
--on table (column)

if exists (SELECT * from sysindexes where name= ' Ix_personname ')
Drop INDEX Person.ix_personname
Create nonclustered index Ix_personname
On [person] (Pcname)

SQL Notes/Paging stored procedures