Configure SQL Server Database EXEC SP_DBOPTION 'PUBS', 'Read Only', 'True' This option sets the database "PUBS" to read-only.
EXEC SP_DBOPTION 'PUBS' AutoShrink True This option sets the eligible "PUBS" database file to automatic periodic contraction.
EXEC SP_DBOPTION 'PUBS' SINGLE User This command only allows only one user to access the database.
DBCC ShrinkDatabase (PUBS, 10) reduces the size of files in the "PUBS" database and allows 10% of available space.
Create Table Create Table MyTable with T-SQL (stdid Int, stdname varchar (50))
Custom data type EXEC SP_ADDTYPE CITY, 'NVARCHAR (15)', NULLEXEC SP_ADDTYPE POSTCODE, 'NVARCHAR (6)', NULEXEC SP_ADDTYPE NCODE, 'NVARCHAR (18)', NULL
Delete Custom Data Types EXEC SP_DROPTYPE CITY
Create Table Create Table Categories using T-SQL (CategoryId Int Id Id Idness (1), CategoryName NVARCHAR (15) Not Null, Description Ntext Null, Picture Image NULL
Entity integrity Realization primary key constraint Create Table Jobs (Job_ID Smallint Primary Key, Job_Desc VARCHAR (50) Not NULL)
Unique constraint Create Table Jobs (JOB_ID Smallint Unique)
Label Column Create Table Jobs (JOB_ID Smallint Identiry (2, 1) Primary Key)
UNIQUEIDENTIFIER Data Type and NewID Function Create Table Custom (Custid Newid (), CustName Char (30) Not NULL)
INSERT Customer Values (NewID (), 'ASB')
Reference Integrity Realization ALTER TABLE DBO.OrDERS Add Constraint Fk_Orders_CustomersFore_ References dbo.customers (Customerid)
Updating the table structure ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR (20) NOT NULLALTER TABLE jobs ADD HIRE_DATE DATETIMEALTER TABLE Doc_ED DROP COLUMN Column_BALTER TABLE Doc_ED WITH NOCHECK ADD CONSTRAINT Exd_Check CHECK (Column_a> 1) increases in the prior identified constraint field must be Delete this field and create a field. ALTER TABLE MYTABLE DROP Column Userid Alter Table MyTable Add UserId Int Idness (1) Remove Table DROP TABLE AIRLINES_MASTER
DEFAULT constraint USE NorthwindCREATE TABLE ABC (ASD int Default 8, ASDE varchar (20) Default 'UNKNOWN') USE NorthwindALTER TABLE dbo.Customers ADD CONSTRAINT DF_contactname DEFAULT 'UNKNOWN' FOR ContactName
Check Constrained Create Table Abcd (ASD INT CHECK (ASD <100), Asde Varchar (80) Default 'UnkNown')
Use northwind
Alter Table Employees Add Constraint CK_BIRTHDATECK (BIRTHDATE> '01 -01-1900 'and birthdate <'01 -01-2010')
Primary Key Constrained Create Table AWC (ASD INT CHECK (ASD <100) Primary Key, Asde Varchar (80) Default 'unknown')
Use Northwind Alter Table Customers Add Constraint PK_CUSTOMERS Primary Key (Customerid)
Unique constraint Create Table AAC (ASD INT CHECK (ASD <100) Primary Key, Asde Int Unique)
Use NorthwindalTer Table Suppliers Add Constraint U_CompanyName Unique (CompanyName)
Foreign Key Constraint Create Table ACC (ASD INT CHECK (ASD <100) Primary Key, ASDE INT Foreign Key References AAC (ASD))
Use northwindalTRAINT FK_OR_CUFOREIGN KEY (CUSTOMERID) References Customers Inserts Data Syntax to Tables: Insert [INTO] Table Name (Field List) VALUES (list)
Example: 1. Insert a record of the specified value Insert Into MyTable (Prikey, Description) Values (123, 'A Description of Part 123.') 2. Getting data from the query (multiple) Insert Into MyTable (Prikey, Description) SELECT Foreignkey, Description from SomeView
Update data line syntax: Update table name set field name = new value, ... Where Condition example: update titles set price = price 0.25 * price where title_id = 'TC777'
Joint: Search the data 1 in the multi-table according to the logical relationship between the tables. Inline SELECT ... FROM table 1 Inner Join table 2 ON table 1. Field 1 = Table 2. Field 1 Where Condition
Example: Update Titles Set Price = Price 10 from Titles Inner Join Titleauthor on Titles.title_is = Titleauthor.title_id Where Titles.title = 'Sushi, Anyone?' 2. Outer joint left: left table with the right table connected to the data and other data of the left table: Select ... from table 1 Left Join table 2 ON table 1. Field 1 = Table 2. Field 1 Where Condition
Example: Update Titles Set Price = Price 10 from Titles Left Outer Join TitleAuthor on Titles.title_is = Titleauthor.title_id where titles.title = 'SUSHI, Anyone?'
Right join: Target and other data of the data and the right table connected to the left table: Select ... from table 1 Right Join table 2 ON table 1. Field 1 = Table 2. Field 1 WHERE condition
Example: Update Titles Set Price = Price 10 from Titles Right Outer Join TitleAuthor ON Titles.title_is = Titleauthor.title_id WHERE TITLES.TITLE = 'Sushi, Anyone?'
Full join: Target and the left table, the right table and other data of the left table: SELECT ... FROM Table 1 Full Join table 2 ON table 1. Field 1 = Table 2. Field 1where Condition
3. Self-join: Table with its own join Select ... from table 1 as a join table 1 as b on A. Field 1 = b. Field 2 Where Condition
Delete Data Delete from Table Where WHERE Condition (Slow) Truncate Table Table (Remove all records in the table, but not log, fast)
Query data 1. Database and owner Limited Select * from northwind.dbo.shippers
2. Use constants and operators in the query SELECT TITLE_ID ':' Title '->' Type As MyTLE from Titles
3. Aggregate function select productid, sum (quantity) as total_quantity from Orderhist Group by ProductID
Show all type values, and press Type to set average SELECT TYPE, AVG (Price) from titles where royalty = 10 Group by all at Type
With a polymerization function, you need to use a Having clause Select ProductId, SUM (Quantity) as total_quantity from orderhistgroup by ProductId Having Sum (Quantity)> = 30
4. Fuzzy query where companyname Like '% restaurant%'
WHERE FAX IS NULL
Where country in ('japan', 'italy')
