Sql Server 2005 学习笔记

---SELECT------------

SELECT result-set 

[FROM Tables] 

[WHERE row-filter] 

[GROUP BY column names] 

[HAVING after-filter on groups] 

[ORDER BY column names]

USE 数据库

SELECT * FROM 表

 

SELECT field_name1,field_name2 FROM table ORDER BY field_name ASC/DESC

 

SELECT dname, sex, age

FROM   Dependent

ORDER  BY sex, age DESC

 

SELECT dname, sex, age

FROM   Dependent

ORDER  BY sex DESC, age DESC

 

--显示系统当前日期和时间

SELECT GETDATE()

 

 

SELECT col1=100, col2=200

 

SELECT 'A', 'B'

 

SELECT 4+3, 4-3, 4*3, 4/3

 

SELECT Additions=4+3, Subtractions=4-3, Multiplications=4*3, Divisions=4/3

 

 

-------WHERE--------------

SELECT column-names

FROM   Table

WHERE  criteria

> (greater than)

 

<> not equal

 

= equal

 

>= greater than or equal to

 

<= less than or equal to

 

 

---ORDER BY---------

 

SELECT   dname, age

FROM     Dependent

WHERE    age <= 5

ORDER BY age

 

 

---AND-------

 

SELECT *

FROM   Dependent

WHERE  age <= 5

AND    sex = 'F'

 

 

--OR--------

 

SELECT *

FROM   Dependent

WHERE  age >20

OR     sex = 'F'

 

 

---BETWEEN---------

 

SELECT  dname, age

FROM    Dependent

WHERE   age

BETWEEN 3 AND 5

 

 

SELECT dname, age

FROM   Dependent

WHERE  age

NOT BETWEEN 3 AND 15

 

 

---COUNT(字段)------

 

SELECT COUNT(*)

FROM   Dependent

 

 

---NULL----------

 

SELECT dname, age

FROM   Dependent

WHERE  age IS NULL

 

 

---NOT NULL--------

 

SELECT dname, age

FROM   Dependent

WHERE  age IS NOT NULL

 

 

---ROWCOUNT---------

 

SET ROWCOUNT 10

SELECT *

FROM   Dependent

 

After using ROWCOUNT, you should reset the ROWCOUNT property by:

 

SET ROWCOUNT 0

 

 

---Aliases(别名)---------

 

SELECT dname AS "Dependent Name", age AS "Dependent Age", sex AS "Dependent Sex"

FROM   Dependent

WHERE  age > 5

 

 

SELECT dname 'Dependent Name', age 'Dependent Age', sex 'Dependent Sex'

FROM   Dependent

WHERE  age > 5

 

 

SELECT [Dependent Name] = dname, [Dependent Age] = age, [Dependent Sex] = sex

FROM   Dependent

WHERE  age > 5

 

 

SELECT Name = dname, Age = age, Sex = sex

FROM   Dependent

WHERE  age > 5

 

 

SELECT d.dname

FROM   Dependent d

WHERE  d.age > 5

 

 

---Synonyms(同义词)-----------

 

CREATE SYNONYM synonym_name

FOR Table_name

 

 

CREATE SYNONYM s1

FOR Student

 

 

DROP SYNONYM synonym_name

 

DROP SYNONYM s1

 

 

---加注释------------

 

SELECT *            -- displays "all" attributes

FROM   Dependent d  -- of the Dependent table

WHERE  d.age > 5    -- where the age of the dependent is greater than 5.

 

SELECT dname, age            /* displays the dependent name and age  */

FROM   Dependent d          /* from the Dependent table     */

WHERE  d.age > 5              /* where the age of the dependent is greater than 5 */

 

 

---数据类型--------

 

1.整型

 

BIGINT, which uses 8 bytes of storage and can be used to store numbers from -263 to 263 -1. Avoid using the BIGINT data type unless you really need its additional storage capacity.

 

INT(INTEGER), which uses 4 bytes of storage and can be used to store numbers from -231 to 231 -1.

 

SMALLINT, which uses 2 bytes of storage and can be used to store numbers from -215 to 215 -1.

 

TINYINT, which uses 1 byte of storage and can be used to store numbers from 0 to 255.

 

MONEY, which uses 8 bytes of storage.

 

SMALLMONEY, which uses 4 bytes of storage.

 

 

2.小数

 

REAL, which uses 4 bytes for storage and has a precision of 7 digits. The synonym for REAL is FLOAT[(n)] for n = 1 to 7.

 

FLOAT, which uses 8 bytes for storage and has a precision of 15 digits. The synonym for FLOAT is DOUBLE PRECISION and FLOAT[(n)] for n = 8 to 15.

 

DECIMAL, whose storage size varies based on the specified precision and uses 217 bytes for storage. The synonyms for DECIMAL are DEC and NUMERIC.

 

 

3.字符型

 

CHAR(CHARACTER),CHAR(n)s are fixed-length single-byte character strings that can be used to store up to 8,000 bytes of data.

 

VARCHAR()

 

 

TEXT . TEXTs are also variable-length single-byte character strings, but may be used to store more than 8,000 bytes.

 

NCHAR(NATIONAL CHAR) . NCHARs are fixed-length Unicode character strings .

 

NVARCHAR(NATIONAL CHARACTER VARYING) . NVARCHARs are variable-length Unicode character strings.

 

4.时间类型

 

DATETIME

SMALLDATETIME

5.混合类型

BINARY

BINARY data types are used to store strings of bits, and values are entered and displayed using their hexadecimal (hex) representation. The maximum length of the BINARY data type is 8,000 bytes. You can specify the maximum byte length of BINARY data with n.

 

 

VARBINARY

The VARBINARY data type can store up to 8,000 bytes of variable-length binary data. Once again, you can also specify the maximum byte length with n. The VARBINARY data type should be used (instead of the BINARY data type) when you expect to have null values or a variation in data size.

 

IMAGE

The IMAGE data type is a large object binary data type that stores more than 8000 bytes. The IMAGE data type is used to store binary values and is also used to store pictures.

BIT 

The BIT data type is actually an integer data type that can store only a 0 or a 1 and can consume only a single bit of storage space. However, if there is only a one bit column in a table, it will actually take up a whole byte. Up to 8-bit columns are stored in a single byte. The BIT data type is usually used for true/false or yes/no types of data. BIT columns cannot be NULL and cannot have indexes on them.

MONEY, which uses 8 bytes of storage

SMALLMONEY, which uses 4 bytes of storage

TABLE 

 

The TABLE data type can be used to store the result of a function and can be used as the data type of local variables. Columns in tables, however, cannot be of type TABLE. Table variables are sometimes preferable to temporary tables, because table variables are cleaned up automatically at the end of a function or stored procedure.

 

SQL_VARIANT

 

Values stored in a SQL_VARIANT column can be any data type except TEXT or IMAGE. The usage of the SQL_VARIANT data type should be avoided for several reasons: (a) a SQL_VARIANT column cannot be part of a primary or foreign key; (b) a SQL_VARIANT column cannot be part of a computed column; (c) a SQL_VARIANT column can be used in indexes or as other unique keys only if they are shorter than 900 bytes; (d) a SQL_VARIANT column must convert the data to another data type when moving data to objects with other data types.

 

UNIQUEIDENTIFIER 

 

The UNIQUEIDENTIFIER data type, also referred to as globally unique identifier (GUID) or universal unique identifier (UUID), is a 128-bit generated value that guarantees uniqueness worldwide, even among unconnected computers.

 

XML 

 

The XML data type is a new data type that has been added to SQL Server 2005 to handle XML data. XML can model complex data. The XML column can be typed or untyped. Like other data types, the XML data type must meet specific formatting criteria. It must conform to well-formatted XML criteria (which is untyped) and you can optionally add additional conformance criteria by specifying a Schema collection (typed). SQL Server will also allow you to store XML documents associated with multiple schema definitions. The XML data type will allow you to store complete XML documents or fragments of XML documents. XML documents are limited to two gigabytes of data.

 

---创建表----------

CREATE TABLE Tablename (column_name type, column_name, type, .....)

 

CREATE TABLE Employee (names 　　　　       VARCHAR(20), 

                       address 　　　　     VARCHAR(20),

                       employee_number 　　 INT,

                       salary               SMALLMONEY)

 

CREATE TABLE Test2 (name VARCHAR(20), 

                    ssn CHAR(9), 

                    dept_number INT NOT NULL, 

                    acct_balance SMALLMONEY)

 

ALTER TABLE Tablename ALTER COLUMN column_name column_type(size) NOT NULL

 

CREATE TABLE Test2a (ssn CHAR(9) CONSTRAINT ssn_pk PRIMARY KEY, 

                     name VARCHAR2(20), etc.

 

CREATE TABLE Test2a (ssn CHAR(9), blah blah .., acct_balance NUMBER, CONSTRAINT ssn_pk PRIMARY KEY (ssn))

 

ALTER TABLE Tablename ADD CONSTRAINT constraint_name PRIMARY KEY (column_name(s))

 

ALTER TABLE Test2 ADD CONSTRAINT ssn_pk PRIMARY KEY (ssn)

 

ALTER TABLE Test2 

ALTER COLUMN ssn CHAR(9) NOT NULL

 

CREATE TABLE Test2a (ssn CHAR(9) PRIMARY KEY, 

                     salary INT PRIMARY KEY)

 

---INDEX-----------------------------------

CREATE INDEX index_name 

ON Tablename (column [ASC | DESC])

 

CREATE UNIQUE INDEX ssn_ndx1 

ON Test1 (ssn DESC)

 

DROP INDEX Table_name.index_name

 

---插入数据-------

INSERT INTO TableName VALUES ('character_attribute_value', numeric_attribute_value, ...)

 

INSERT INTO Names VALUES ('Joe Smith')

 

INSERT INTO Employee VALUES (2500, 'Joe Smith', 101, '123 4th St.'

 

INSERT INTO Employee VALUES ('Joe Smith', null, 101, null)

 

----

INSERT INTO target_table(column1, column2, column3, ...) "SELECT clause"

 

INSERT INTO Names(fullname) SELECT names FROM Employee WHERE salary > 2600

 

INSERT INTO Emp2 SELECT * FROM Emp1

 

---更新数据---------

UPDATE TableName SET fieldname...

 

UPDATE Employee SET salary = 0 WHERE employee_number=101

 

---ALERT-------

 

ALTER TABLE Tablename ADD column-name type -- 增加列

 

ALTER TABLE Employee ADD bonus SMALLMONEY

 

ALTER TABLE Tablename ALTER COLUMN column-name new_type -- 修改列的类型

 

ALTER TABLE EMPLOYEE ALTER COLUMN bonus FLOAT

 

ALTER TABLE Tablename DROP column column-name

 

---删除数据----------

DELETE FROM Table WHERE (condition)

 

DELETE FROM Employee

 

DROP TABLE Tablename

 

DROP TABLE Names

 

---JOIN------------

SELECT columns FROM table1 JOIN table2 ON table1.column1=table2.column1

 

SELECT * FROM Course c JOIN Prereq p ON c.course_number=p.course_number

 

---INER JOIN-----------

SELECT columns FROM table1 INNER JOIN table2 ON table1.column1=table2.column1

 

-- 用WHERE替代JOIN

SELECT * FROM Course c, Prereq p WHERE c.course_number= p.course_number

 

 

 

 

 

 

 

 

 

---CROSS JOIN-------------

SELECT * FROM Table1 CROSS JOIN Table2

 

---Self Joins(自身连接)--------------

SELECT 'SENIORITY' = x.sname + ' is in a higher class than ' + y.sname FROM Student AS x, Student AS y WHERE y.class = 3 AND x.class > y.class

 

---ORDER BY----------

SELECT c.course_name, c.course_number, c.credit_hours, c.offering_dept, p.prereq FROM Course c JOIN Prereq p ON c.course_number=p.course_number ORDER BY c.course_number

 

SELECT c.course_name, c.course_number, c.credit_hours, c.offering_dept, p.prereq FROM Course c JOIN Prereq p ON c.course_number=p.course_number ORDER BY 2

 

--ORDER BY 2 means to order by the second column of the result set.

 

 

---Joining Multiple Tables Using a Nested JOIN------------------------------

SELECT columns FROM table1 JOIN (table2 JOIN table3 ON table3.column3=table2.column2) ON table1.column1=table2.column2

 

SELECT c.course_name, c.course_number, d.dname FROM (course c JOIN department_to_major d ON c.offering_dept = d.dcode) JOIN prereq p ON p.course_number = c.course_number

 

---OUTER JOIN-----------------

 

SELECT columns FROM table1 LEFT OUTER JOIN table2 ON table1.column1=table2.column1

 

SELECT columns FROM table1 RIGHT OUTER JOIN table2 ON table1.fieldcolumn1=table2.column1

 

SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column1=table2.column1

 

---Aggregate Functions(聚合函数)-------------------

COUNT(*)

SUM

AVG

MIN

MAX

---Row-Level Functions-------------

SELECT wage, (wage + 5) AS [wage + 5] FROM Employee

ROUND -- 四舍五入

CEILING(attribute), which returns the next larger integer value when a number contains decimal places.

FLOOR(attribute), which returns the next lower integer value when a number contains decimal places.

SQRT(attribute), which returns the square root of positive numeric values. -- 平方根

ABS(attribute), which returns the absolute value of any numeric value. -- 绝对值

SQUARE(attribute), which returns a number squared. -- 开平方

 

---ISNULL Function----------

DECLARE @a FLOAT, @b FLOAT SET @a = 3 SET @b = 2 SELECT @a + @b AS 'A + B = '

 

ISNULL(expression1, ValueIfNull)

DECLARE @a FLOAT, @b FLOAT SET @a = NULL SET @b = 2 SELECT ISNULL(@a, 0) + ISNULL(@b, 0) AS 'A + B = '

 

---NULLIF Function-----------------

NULLIF(expression1, expression2)

 

SELECT names, wage, new_wage = NULLIF(wage, 15) FROM Employee -- 等于15的就变为NULL

 

SIN, COS, TAN, LOG

---TOP Function------------

SELECT TOP 2 names, wage FROM Employee ORDER BY wage ASC -- 取最上面两行

SELECT TOP 2 names, wage FROM Employee ORDER BY wage DESC -- 取最下面两行

SELECT TOP 10 PERCENT sname FROM Student ORDER BY sname ASC -- 取百分之10

SELECT TOP 10 PERCENT WITH TIES sname FROM Student ORDER BY sname DESC

 

---DISTINCT Function-----------

SELECT DISTINCT grade FROM Grade_report -- 唯一

 

---String Functions----------

+

SUBSTRING(stringexpression, startposition, length)

LEFT(stringexpression, n)

RIGHT(stringexpression, n)

LTRIM(' Ranu')

RTRIM('Ranu ')

CHARINDEX(' ',names)

UPPER(names)

LOWER(names)

LEN(names)

 

 

---LIKE------------

UPDATE Employee SET WAGE = 10 WHERE names LIKE '%Sudip%' -- %多字符

 

SELECT DISTINCT student_number, grade 

 FROM Grade_report 

 WHERE grade LIKE '[^c-f]' -- ^是非的意思

 AND student_number > 100

 

SELECT sname 

 FROM Student 

 WHERE sname LIKE '[BG]ill' -- B或G开头

 

SELECT DISTINCT student_number, grade 

 FROM Grade_report 

 WHERE student_number LIKE '13_' -- _单字符

 

SELECT * FROM Section WHERE course_num NOT LIKE 'MATH%' AND Course_num NOT LIKE 'C%'

 

---CONVERSION Functions---------

CAST (original_expression AS desired_datatype)

STR(float_expression, character_length, number_of_decimal_places)

CONVERT(desired_datatype[(length)], original_expression [, style])

 

---DATE Functions-------------

SET DATEFORMAT dmy 
SELECT 'Format is yyyy/mon/dd' = CONVERT(datetime, '10/2/2003')

 

 

The date parts are: dd for day, mm for month, and yy for year.

 

DATEADD(datepart, number, date_field)

SELECT names, 'Add 2 days to birthday' = DATEADD(dd, 2, birthdate) FROM Datetable

SELECT names, 'Add 2 days to birthday' = DATEADD(dd, -2, birthdate) FROM Datetable

 

DATEDIFF(datepart, date_field1, date_field2)

SELECT names, 'Months between birth date and school date' = DATEDIFF(mm, birthdate, school_date) FROM Datetable

 

DATEPART(datepart, date_field)

SELECT names, 'YEARS' = DATEPART(yy, birthdate) FROM Datetable

 

YEAR(column)

SELECT names, YEAR(school_date) AS [Kindergarten Year] FROM Datetable

SELECT names, YEAR(school_date)-YEAR(birthdate) AS [Age in Kindergarten] FROM DateTable

 

 

MONTH

SELECT names, birthdate, MONTH(birthdate) AS [Birth Month], ((MONTH(birthdate)) + 6 ) AS [Sixth month] FROM DateTable

DAY

SELECT names, birthdate, DAY([birthdate]) AS [Date] FROM DateTable

GEtdATE

SELECT 'Today ' = GETDATE( )

---VIEW(视图)------------------

CREATE VIEW view_name AS SELECT ...

CREATE VIEW namemaj AS 

 SELECT sname, major 

 FROM Student

 

 

 

 

 

SELECT * FROM namemaj

 

UPDATE Employee_view SET names = 'Mala Saha' WHERE names LIKE 'Priya%'

 

DROP VIEW Employee_view

 

---local temporary tables-----------

SELECT column_name, ..., column_name INTO #local_temporary_tablename 

 FROM permanent_tablename 

 WHERE...

 

SELECT s.sname, s.stno, d.dname, s.class INTO #Temp1 

 FROM Student s, Department_to_major d 

 WHERE s.major = d.dcode 

 AND (s.class = 1 or s.class = 2) 

 AND s.major = 'COSC';

 

---global temporary tables-------------

SELECT column_name, ..., column_name INTO ##global_temporary_tablename 

 FROM permanent_tablename 

 WHERE...

 

SELECT s.sname, s.stno, d.dname, s.class INTO ##Temp1 

 FROM Student s, Department_to_major d 

 WHERE s.major = d.dcode 

 AND (s.class = 1 or s.class = 2) 

 AND s.major = 'COSC';

 

DROP TABLE ##Temp1

 

---UNION--------

SELECT * FROM TableA UNION SELECT * FROM TableB

 

SELECT sname 

 FROM Student 

 WHERE major = 'COSC' 

 UNION 

 SELECT sname 

 FROM Student 

 WHERE major = 'MATH'

 

SELECT sname 

 FROM Student 

 WHERE major = 'COSC' 

 UNION ALL 

 SELECT sname 

 FROM Student 

 WHERE major = 'MATH'

 

---IN--------

SELECT sname, class 

 FROM Student 

 WHERE class IN (3,4)

 

SELECT Student.sname 

 FROM Student 

 WHERE Student.stno 

IN (SELECT g.student_number FROM Grade_report g WHERE g.grade = 'A')

 

SELECT sname, class FROM Student WHERE class NOT IN (2)

 

SELECT * FROM department_to_major d 

 WHERE d.dcode 

NOT IN (SELECT dcode FROM department_to_major d, instructor i WHERE d.dcode=i.teaches

 

---INTERSECT(相交)---------------

SELECT s.stno 

 FROM Student s 

 INTERSECT 

 SELECT g.student_number 

 FROM Grade_report g 

 WHERE g.grade = 'A'

 

---NOT...IN----------

SELECT sname, class 

 FROM Student 

 WHERE class IN (2,3,4) AND NOT class IN (2)

 

SELECT sname 

 FROM Course c JOIN (Section se JOIN 

 (Student s JOIN Grade_report g 

 ON s.stno = g.student_number) 

 ON se.section_id = g.section_id) 

 ON c.course_number = se.course_num 

 AND c.course_name LIKE 'ACC%' 

 UNION 

 SELECT sname 

 FROM Student s JOIN 

 (Department_to_major d 

 JOIN (Course c JOIN 

 (Room r JOIN Section se 

 ON r.room = se.room) 

 ON se.course_num = c.course_number) 

 ON c.offering_dept = d.dcode) 

 ON s.major = d.dcode 

 AND r.ohead = 'Y'

 

---子查询-------------

SELECT * FROM Room WHERE capacity < (SELECT AVG(capacity) FROM Room)

SELECT * FROM Room WHERE bldg = (SELECT bldg FROM Room WHERE capacity > 10)

 

---GROUP BY---------------

SELECT class, COUNT(*) AS [count] FROM Student GROUP BY class

 

SELECT class, major, COUNT(*) AS [count] FROM Student GROUP BY major, class

 

---HAVING-----------

SELECT class, COUNT(*) AS [count] FROM Student GROUP BY class HAVING COUNT(*) > 9

 

---NOT EXISTS----------

SELECT name, langu 

FROM Cap x 

WHERE NOT EXISTS 

 (SELECT 'X' FROM Cap y WHERE NOT EXISTS 

 (SELECT 'X' FROM Cap z WHERE x.langu = z.langu AND y.name = z.name))