1.数据类型：binary image char text datetime smalldatetime decimal
float real bigint money nchar
2.USE bookshopdb
CREATE TABLE Authors
( AuthorID SMALLINT IDENTITY(101,1) NOT NULL,
FirstName VARCHAR(30) NOT NULL DEFAULT 'unknown',
LastName VARCHAR(30) NOT NULL,
YearBorn CHAR(4) NOT NULL DEFAULT ' N/A ',
YearDied CHAR(4) NOT NULL DEFAULT ' N/A ',
Description VARCHAR(200) NOT NULL DEFAULT 'N/A' )
CREATE TABLE BookAuthors
( AuthorID SMALLINT NOT NULL,
TitleID CHAR(8) NOT NULL )
CREATE TABLE BookCondition
( ConditionID TINYINT NOT NULL,
ConditionName CHAR(10) NOT NULL,
Description VARCHAR(50) NOT NULL DEFAULT 'N/A' )
3.sp_rename，sp_changeobjectowner，ALTER TABLE ，
CREATE TABLE doc_exa ( column_a INT)
GO
ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
GO
EXEC sp_help doc_exa
GO
DROP TABLE doc_exa
GO
CREATE TABLE cnst_example
( id INT NOT NULL,
name VARCHAR(10) NOT NULL,
salary MONEY NOT NULL
CONSTRAINT salary_cap CHECK (salary < 100000) )
--- INSERT INTO cnst_example VALUES (2,'Mary Smith',75000)
--- INSERT INTO cnst_example VALUES (3,'Pat Jones',105000)
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
--- INSERT INTO cnst_example VALUES (3,'Pat Jones',105000)
4.Private Temporary Tables (#)
Global Temporary Tables (##)
CREATE TABLE #TEMP002(EID INT) GO
INSERT INTO #TEMP002(EID) VALUES (3) GO
SELECT * FROM #TEMP002 GO
DROP TABLE #TEMP002 GO
CREATE TABLE ##TEMP001(EID INT)
GO
5.CREATE TABLE Table1
( Col1 INT PRIMARY KEY,
Col2 VARCHAR(30) )
CREATE TABLE Table1
( Col1 INT,
Col2 VARCHAR(30),
CONSTRAINT table_pk PRIMARY KEY (Col1) )
ALTER TABLE Table1
ADD CONSTRAINT table_pk PRIMARY KEY (Col1)
6.UNIQUE Constraints
If a column (or combination of columns) is not the primary key. Multiple UNIQUE
constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be
defined on a table.
If a column allows null values. UNIQUE constraints can be defined for columns that
allow null values, whereas PRIMARY KEY constraints can be defined only on columns that
do not allow null values.
7.
CREATE TABLE Authors
( AuthorID SMALLINT IDENTITY(101,1) NOT NULL,
FirstName VARCHAR(8) NOT NULL DEFAULT 'unknown')
8.
A foreign key is a column or combination of columns used to establish and enforce a
link between the data in two tables.
CREATE TABLE Table1
( Col1 INT PRIMARY KEY,
Col2 INT REFERENCES Employees(EmployeeID) )
CREATE TABLE Table1
( Col1 INT PRIMARY KEY,
Col2 INT,
CONSTRAINT col2_fk FOREIGN KEY (Col2)
REFERENCES Employees (EmployeeID) )
ALTER TABLE Table1
ADD CONSTRAINT col2_fk FOREIGN KEY (Col2)
REFERENCES Employees (EmployeeID)
9.
CHECK Constraints
CHECK constraints enforce domain integrity by limiting the values that are
accepted by a column.
CREATE TABLE Table1
( Col1 INT PRIMARY KEY,
Col2 INT CONSTRAINT limit_amount
CHECK (Col2 BETWEEN 0 AND 1000),
Col3 VARCHAR(30) )
CREATE TABLE Table1
( Col1 INT PRIMARY KEY,
Col2 INT,
Col3 VARCHAR(30), CONSTRAINT limit_amount
CHECK (Col2 BETWEEN 0 AND 1000) )
ALTER TABLE Table1 ADD CONSTRAINT limit_amount
CHECK (Col2 BETWEEN 0 AND 1000)
10.
SELECT select_list
[INTO new_table_name]
FROM table_list
[WHERE search_conditions]
[GROUP BY group_by_list]
[HAVING search_conditions]
[ORDER BY order_list [ASC | DESC] ]
The DISTINCT Keyword: eliminates duplicate rows from a result set.
SELECT DISTINCT ShipCity, ShipRegion
FROM Orders
ORDER BY ShipCity
The TOP n Keyword: specifies that the first n rows of the result set are to be
returned.
SELECT DISTINCT TOP 10 ShipCity, ShipRegion
FROM Orders
ORDER BY ShipCity
The AS Keyword: You can improve the readability of a SELECT statement by giving a
table an alias
table_name AS table_alias or table_name table_alias
SELECT p.pub_id, p.pub_name
FROM publishers AS p
Types of Information in the Select List:
SELECT FirstName + ' ' + LastName AS "Employee Name",
IDENTITYCOL AS "Employee ID", HomePhone, Region
FROM Northwind.dbo.Employees
ORDER BY LastName, FirstName ASC
11.
% Any string of zero or more characters.
_ Any single character.
[ ] Any single character within the specified range (for example, [a-f]) or the
specified set (for example, [abcdef]).
[^] Any single character not within the specified range (for example, [^a-f]) or
the specified set (for example, [^abcdef]).
SELECT ContactName
FROM Customers
WHERE ContactName LIKE 'M[^a]%'
12.
SELECT ContactName
FROM dbo.Customers
WHERE ContactName LIKE 'M[^a]%'
UNION ALL
SELECT ContactName
FROM dbo.Customers
WHERE ContactName LIKE 'M[a-d]%'
13.
SELECT FirstName, LastName
INTO EmployeeNames
FROM Employers
14.
SELECT OrdD1.OrderID AS OrderID,
SUM(OrdD1.Quantity) AS "Units Sold",
SUM(OrdD1.UnitPrice * OrdD1.Quantity) AS Revenue
FROM [Order Details] AS OrdD1
WHERE OrdD1.OrderID in
(SELECT DISTINCT OrdD2.OrderID
FROM [Order Details] AS OrdD2
WHERE OrdD2.UnitPrice > $100)
GROUP BY OrdD1.OrderID
HAVING SUM(OrdD1.Quantity) > 100
15.
USE Pubs
SELECT Pub_id, Type, Title_id, Price
FROM Titles
ORDER BY Pub_id DESC, Type, Price
16.
Most joins can be categorized as inner joins or outer joins. Inner joins return rows
only when there is at least one row from both tables that matches the join condition,
eliminating the rows that do not match with a row from the other table. Outer joins,
however, return all rows from at least one of the tables or views mentioned in the
FROM clause as long as these rows meet any WHERE or HAVING search conditions. You can
also create cross-joins and self-joins.
17.
SELECT t.Title, p.Pub_name
FROM Publishers AS p INNER JOIN Titles AS t
ON p.Pub_id = t.Pub_id
ORDER BY Title ASC
18.
SELECT a.Au_fname, a.Au_lname, p.Pub_name
FROM Authors a LEFT OUTER JOIN Publishers p
ON a.City = p.City
ORDER BY p.Pub_name ASC, a.Au_lname ASC, a.Au_fname ASC
USE Pubs
SELECT a.Au_fname, a.Au_lname, p.Pub_name
FROM Authors a RIGHT OUTER JOIN Publishers p
ON a.City = p.City
ORDER BY p.Pub_name ASC, a.Au_lname ASC, a.Au_fname ASC
USE Pubs
SELECT a.Au_fname, a.Au_lname, p.Pub_name
FROM Authors a FULL OUTER JOIN Publishers p
ON a.City = p.City
ORDER BY p.Pub_name ASC, a.Au_lname ASC, a.Au_fname ASC
USE Northwind
SELECT ProductName
FROM Products
WHERE UnitPrice = (
SELECT UnitPrice
FROM Products
WHERE ProductName = 'Sir Rodney''s Scones' )
WHERE <expression> [NOT] IN (<subquery>)
WHERE <expression> <comparison_operator> [ANY | ALL] (<subquery>)
WHERE [NOT] EXISTS (<subquery>)
USE Pubs
SELECT Pub_name
FROM Publishers
WHERE Pub_id IN ( SELECT Pub_id FROM Titles WHERE Type = 'business' )
USE Pubs
SELECT Title
FROM Titles WHERE Advance > ANY (
SELECT Advance
FROM Publishers INNER JOIN Titles ON Titles.Pub_id = Publishers.Pub_id AND Pub_name =
'Algodata Infosystems' )
USE Pubs
SELECT Pub_name
FROM Publishers
WHERE EXISTS (
SELECT *
FROM Titles
WHERE Titles.Pub_id = Publishers.Pub_id AND
Type = 'business' )
AVG(expression)
COUNT(expression)
COUNT(*)
MAX(expression)
MIN(expression)
SUM(expression)
19.
Using the CUBE Operator to Summarize Data
The CUBE operator generates a result set that is a multi-dimensional cube. A
multi-dimensional cube is an expansion of fact data, or data that records individual
events. The expansion is based on columns that the user wants to analyze. These
columns are called dimensions. The cube is a result set containing a cross-tabulation
of all the possible combinations of the dimensions.
The following SELECT statement uses the CUBE operator in the GROUP BY clause:
USE Pubs
SELECT SUBSTRING(Title, 1, 65) AS Title, SUM(qty) AS 'Quantity'
FROM Sales INNER JOIN Titles
ON Sales.Title_id = Titles.Title_id
GROUP BY Title WITH CUBE
ORDER BY Title
20.
USE Pubs
INSERT INTO NewBooks (BookTitle, BookType)
SELECT Title, Type
FROM Titles WHERE Type = 'mod_cook'
CREATE DEFAULT bound_default AS 'Bound default value'
GO
CREATE TABLE T1 ( column_1 int identity,
column_2 varchar(30)
CONSTRAINT default_name DEFAULT ('column default'), column_3
timestamp,
column_4 varchar(30),
column_5 int NULL)
GO
USE master
EXEC sp_bindefault 'bound_default','T1.column_4'
INSERT INTO T1 DEFAULT VALUES
-- SELECT * FROM T1
21.
UPDATE
table_name or view_name
SET
column_name = { expression | DEFAULT | NULL }
FROM table_source
WHERE search_condition
USE Pubs
UPDATE NewBooks
SET Price = Price * 1.1
USE Pubs
UPDATE NewBooks
SET Price = Titles.Price
FROM NewBooks JOIN Titles
ON NewBooks.BookTitle = Titles.Title
USE Pubs
UPDATE NewBooks
SET BookType = 'popular'
WHERE BookType = 'popular_comp‘
DELETE table_or_view
FROM table_source
WHERE search_condition
Example:
USE Pubs
DELETE NewBooks
FROM Titles
WHERE NewBooks.BookTitle = Titles.Title AND Titles.Royalty = 10