Sqlsever Constraint related statements

--1, adding foreign KEY constraints

ALTER TABLE Product ADD CONSTRAINT fk_product_category FOREIGN KEY (CategoryId) REFERENCES Category (CatId)

--Add a default value constraint to Delflag

--alter TABLE [dbo]. [UserInfo] ADD CONSTRAINT Df_userinfo_delflag Default (0) for Delflag
--alter TABLE dbo. UserInfo DROP CONSTRAINT [Df_userinfo_delflag]
--alter TABLE dbo. UserInfo DROP COLUMN Delflag
--alter TABLE dbo. UserInfo ALTER COLUMN [Address] nvarchar (+) null

--2, de-re-operation

--distinct can only follow the Select, and all of the subsequent columns are repeated operations;

Demo:select DISTINCT title,middlename from dbo. Student ORDER by Title,middlename;

This case de-duplication is to remove all the duplicates in the Title,middlename two column, but the duplicate values in the Tittle column still exist, and to go to the title column you must use the following demo

Demo:select DISTINCT Title from dbo. Student ORDER by Title;

--3, multi-conditional filtering
--not non-or OR and and
--Priority level (NOT>AND>OR)

--not in SQL statements can be! = or <>

Demo:select * FROM dbo. Product WHERE proid > 2 and proname<> ' shampoo ' OR countnumber =100

--4, Interval query

SELECT * from Product WHERE proid between 2 and 4;

--5, Fuzzy query

Demo:select * from the Customer WHERE FirstName like ' _o% ';

WHERE CompanyName like '% '% ';--two single quotes for a single quote

WHERE CompanyName like '%[0-9]% ';--the company name contains 0-9 numbers of queries come out

WHERE CompanyName like '%[[]% '; match company name contains [company

Demo: Match the second letter of the name is O data

--6, NULL handling in SQL

--Query out columns that are empty in proname

SELECT * from Product WHERE proname is null;

Sqlsever Constraint related statements