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))
