1 / 70

Comprehensive Guide to SQL Data Types and Schema Creation

Learn about SQL data types including boolean, numeric, character strings, temporal data, and intervals. Understand schema creation with examples and syntax. Master SQL fundamentals for database management.

patriciahopkins
Download Presentation

Comprehensive Guide to SQL Data Types and Schema Creation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 2. Complete SQL Tianqing Zhang School of Computer (School of Software) SCU 2006

  2. 2.1 Basic SQL • SQL (Structured Query Language) • The de facto standard query language for relational DBMSs 事实标准 • both DDL (Data Definition Language) and DML (Data Manipulation Language) • A historical note • SQL1 (ANSI 1989) • SQL2 or SQL-92 (ANSI 1992) • SQL3 (SQL99) • 学习方法：背命令，实践，理解 Tianqing Zhang SCU

  3. 2.1.1 SQL Data Definition Language • Creating a Database • Understanding SQL Data Types • Numeric Data • Character Strings • Bit Strings • Temporal Data • Date and Time • Intervals • Operations on Dates • Creating a Domain • Creating a Table • Destroying a Table • Modifying a Table Schema Tianqing Zhang SCU

  4. Backus Naur Form notation • upper-case  reserved words • lower-case  user-defined words • |  choice • { }  a required element • [ ]  an optional element • [，…]  optional repetitions Tianqing Zhang SCU

  5. Creating a Schema 1 • Why? • 允许多个用户使用一个数据库而不会干扰其它用户。 • 把数据库对象组织成逻辑组，让它们更便于管理。 • 第三方的应用可以放在不同的模式中， 这样它们就不会和其它对象的名字冲突。 Tianqing Zhang SCU

  6. Creating a Schema 2 • How? • CREATE SCHEMA database-name [AUTHORIZATION user-identifier]; • 访问在模式中的对象: schema.table • Public Schema: [public.]table • DROP SCHEMA database-name [RESTRICT | CASCADE]; • RESTRICT 受限 有对象，不能删 • CASCADE 级联 有对象，一起删 Tianqing Zhang SCU

  7. SQL Data Types 1 • Boolean Data • BOOLEAN TURE/FALSE • Numeric Data • Exact Numbers • INTEGER (INT，INT4) 2,147,483,648 to 2,147,483,647 • SMALLINT -32768 to 32767 • BIGINT (INT8) 8位整数 • Approximate Numbers • FLOAT[precision] • REAL • DOUBLE PRECISION • Formatted Numbers • DECIMAL(i,j), DEC(i,j) or NUMERIC(i,j) Tianqing Zhang SCU

  8. SQL Data Types 2 • Character Strings • Fixed length n • CHAR(n) or CHARACTER(n) 定长串 不足充空 space • Varying length of maximum n • VARCHAR(n) or CHAR VARYING (n) 变长串 • VARCHAR2(n) ORACLE用 • Bit Strings • Fixed length n: BIT(n)定长位串 • Varying length of maximum n:VARBIT(n) or BIT VARYING (n)变长位串 Tianqing Zhang SCU

  9. SQL Data Types 3 • Temporal Data 日期时间 • Date and Time • DATE stores calendar values representing YEAR, MONTH, and DAY. • ‘2000-02-29’推荐to_date('2000/02/29' , ‘yyyy/mm/dd' ) • TIME defines HOURS, MINUTES, and SECONDS in a twenty-four-hour notation: HH:MM:SS • TIME(i) defines i additional decimal fractions of seconds: HH:MM:SS:ddd...d. • Example, in TIME(4), a time could be 22:20:01:2345 • TIME WITH TIME ZONE includes the displacement [+13:00 to -12:59] from standard universal time zone: HH:MM:SS+/-hh:mm. • TIMESTAMP represents a complete date and time with six fractions of seconds and optional time zone. 时间戳postgreSQL now( )函数 Tianqing Zhang SCU

  10. SQL Data Types 4 • Intervals 间隔 • An interval results when two dates are subtracted. • Example: OrderDate – ShipDate • INTERVAL YEAR (3) to MONTH could be an interval between 0-0 (0 years and 0 months) and 999-11 (999 years and 11 months). SQL标准 • postgreSQL 形式‘999 YEAR 11 MONTH’ • INTERVAL DAY(2) to MINUTE could be an interval between 00:00:00 (0 days. 0 hours, 0 minutes) to 99:23:59 (99 days, 23 hours, and 59 minutes). • postgreSQL ‘99 day 23 hour 59 minute’ • A date plus or minus an interval yields a new date. • CURRENT_DATE + INTERVAL '1' MONTH • postgreSQL CURRENT_DATA +’ 1 MONTH’ Tianqing Zhang SCU

  11. SQL Data Types 5 • Valid combinations of DATETIME and intervals : • Datetime - Datetime = Interval of year/month or day/time • Datetime (+ or -) Interval = Datetime • Interval (* or /) Number = Interval • Interval (+ or -) Interval = Interval Tianqing Zhang SCU

  12. An Example Tianqing Zhang SCU

  13. Creating a Domain • Domain 域 • SQL-92 allows the definition of new data types, more precisely data type macros, for columns expressed in terms of the basic data types but not in terms of other domains. 用基本类型定义 • Syntax • CREATE DOMAIN domainName [AS] dataType [DEFAULT defaultOption] [CHECK (searchCondition)] • Example • CREATE DOMAIN hour_dom AS INTEGER DEFAULT 0 CHECK (VALUE >= 0); • CREATE DOMAIN genderDom AS CHAR DEFAULT ‘M’ CHECK (VALUE IN (‘F’, ‘f’,‘M’ ,‘m’)); --定义 • gender genderDom NOT NULL --使用 Tianqing Zhang SCU

  14. Creating a Table 1 • Syntax CREATE TABLE tableName ({columName dataType [NOT NULL] [UNIQUE] [DEFAULT defaultOption] [CHECK (searchCondition)] [,…] } [PRIMARY KEY (listOfColumns),] {[UNIQUE (listOfColumns),] [,…]} {[FOREIGN KEY (listOfFireignKeyColumns) REFERENCES parentTableName [ (listOfCandidateKeyColumns)], [MATCH {PARTIAL | FULL} [ON UPDATE referentialAction] [ON DELETE referentialAction]] [,…]} {[CHECK (searchCondition)] [,…] } ) Tianqing Zhang SCU

  15. Creating a Table 2 • An Example: CREATE TABLE BOOK ( Book_Id NUMERIC(6) NOT NULL, Edition NUMERIC(3) NOT NULL, BorrowerMemNo NUMERIC(4), BorrowDueDate DATE, CallNumber VARCHAR(8) NOT NULL, LibCheck ssn_dom, PRIMARY KEY (Book_Id), FOREIGN KEY (BorrowerMemNo) REFERENCES MEMBER(MemNo), FOREIGN KEY (CallNumber) REFERENCES TITLE(CallNumber), FOREIGN KEY (LibCheck) REFERENCES LIBRARIAN(SSN) ); Tianqing Zhang SCU

  16. Creating a Table 3 • Primary and foreign keys can be specified at the same level as the definition of the column, as long as they are simple atomic ones.如果单值， 主外码也可定义为列级完整性约束 CREATE TABLE LIBRARIAN ( SSN ssn_dom NOT NULL PRIMARY KEY, Name name_dom NOT NULL, Address address_dom, Salary DEC(4.2) DEFAULT 0.0 CHECK(Salary >= 0), Gender gender_dom, Birthday DATE, SUPERSSN ssn_dom FOREIGN KEY REFERENCES LIBRARIAN(SSN), Section sectno_dom FOREIGN KEY REFERENCES SECTION(SectionId) ); Tianqing Zhang SCU

  17. Creating a Table 4 • Primary keys composed of more than one column can be specified. CREATE TABLE DEPENDENT ( LIBSSN ssn_dom, Name name_dom NOT NULL, Birthday DATE, Kinship CHAR(5) DEFAULT 'none', PRIMARY KEY (LIBSSN, Name), FOREIGN KEY (LIBSSN) REFERENCES LIBRARIAN(SSN) ); Tianqing Zhang SCU

  18. Creating a Table 5 • The unique key word specifies each row must have unique values -- specify that a set of columns may serve as an alternate key. 备选码 CREATE TABLE BOOK ( Book_Id NUMERIC(6) NOT NULL, Edition NUMERIC(3) NOT NULL, BorrowerMemNo NUMERIC(4), BorrowDueDate DATE, CallNumber VARCHAR(8) NOT NULL UNQUE, LibCheck ssn_dom, Primary Key (Book_Id), FOREIGN KEY (BorrowerMemNo) REFERENCES MEMBER(MemNo), FOREIGN KEY (CallNumber) REFERENCES TITLE(CallNumber), FOREIGN KEY (LibCheck) REFERENCES LIBRARIAN(SSN) ); Tianqing Zhang SCU

  19. Destroying a Table • Syntax DROP TABLE tableName [ RESTRICT | CASCADE ] • With the CASCADE option, the table and all references to it are removed. • With the RESTRICT option, the table is removed if it is not referenced. 默认 • Example • DROP TABLE MEMBER CASCADE; • DROP TABLE MEMBER RESTRICT; Tianqing Zhang SCU

  20. Modifying a Table Schema 1 • Syntax ALTER TABLE tableName [ADD [COLUMN] columnName dataType [NOT NULL] [UNIQUE] [DEFAULT defaultOption] [CHECK (searchCondition)]] [DROP [COLUMN] columnName [ RESTRICT | CASCADE ]] [ADD [CONSTRAINT [constraintName]]tableConstraintDefinition] [DROP CONSTRAINT constraintName [ RESTRICT | CASCADE ]] [ALTER [COLUMN] SET DEFAULT defaultOption] [ALTER [COLUMN] DROP DEFAULT] Tianqing Zhang SCU

  21. Modifying a Table Schema 2 • ALTER TABLE table-name ALTER • 修改字段数据类型 • ALTER TABLE LIBRARIAN ALTER Salary NUMBER(6,2); • ALTER TABLE LIBRARIAN ALTER COLUMN Salary TYPE NUMBER(6,2); --postgreSQL • 改变字段的缺省值 • ALTER TABLE DEPENDENT ALTER COLUMN kinship DROP DEFAULT; • ALTER TABLE BOOK ALTER COLUMN BorrowerMemNo SET DEFAULT NULL; • 增加一个不能写成表约束的非空约束 • ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; Tianqing Zhang SCU

  22. Modifying a Table Schema 3 • ALTER TABLE table-name ADD • 增加字段 • ALTER TABLE products ADD COLUMN description text ; • 增加约束 • ALTER TABLE products ADD CHECK (name <> ''); • ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); • ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups; Tianqing Zhang SCU

  23. Modifying a Table Schema 4 • ALTER TABLE table-name DROP • 删除字段 • ALTER TABLE products DROP COLUMN description RESTRICT; --默认受限删除 • ALTER TABLE products DROP COLUMN description CASCADE; --CASCADE 授权删除任何依赖该字段的东西 • 删除约束 • ALTER TABLE products DROP CONSTRAINT constraint_name; • ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL; Tianqing Zhang SCU

  24. 2.1.2 SQL Data Manipulation Language • The Complete SELECT Statement • Syntax SELECT [DISTINCT | ALL] { * | [columnExpression [AS newName]] [,…] } FROM tableName [alias] [,…] [WHERE condition] --元组条件 [GROUP BY columnList] --分组 [HAVING condition] --分组条件 [ORDER BY columnList] --排序 • Example 全表查询 SELECT * FROM BOOK; Tianqing Zhang SCU

  25. SELECT Statement 1 • Resulting Tables as Sets • If duplicate elimination is desired, it can be achieved using the DISTINCT keyword in the SELECT clause.消重 SELECT DISTINCT CallNumber FROM BOOK; Tianqing Zhang SCU

  26. SELECT Statement 2 • Aliasing in SQL: The AS Operator 别名 • Renaming attributes in the result of a query: 结果属性别名 SELECT Name AS Librarian_Name FROM LIBRARIAN WHERE Salary > 18000; • Table alias can be achieved with the AS operator in the FROM-clause 表别名 SELECT * FROM LIBRARIAN AS L WHERE L.name = 'Ruchi Jones'; Tianqing Zhang SCU

  27. SELECT Statement 3 • Calculated fields • Example:Produce a list of monthly salaries for all librarians, showing the name and the monthly salary details. SELECT staffNo,fName,lName,salary/12 FROM Staff; SELECT staffNo,fName,lName,salary/12 AS monthlySalary FROM Staff; Tianqing Zhang SCU

  28. SELECT Statement 4 • Comparing NULL Values • NULL can appear in a column 不能用= <>来比较 • SQL provides the IS NULL and IS NOT NULL operators to test for NULL values 用IS NULL和IS NOT NULL来比较 SELECT Book_id FROM BOOK WHERE BorrowerMemNo IS NULL; Tianqing Zhang SCU

  29. SELECT Statement 5 • NULL 引起的三值逻辑问题 • 三值 • True • False • Unknown • WHERE子句为真的才被选出 • 引入IS NOT FALSE • Example: 区别？ SELECT Name FROM Libarian WHERE Salary < 29000 AND Gender = 'M‘; SELECT Name FROM Libarian WHERE (Salary < 29000 AND Gender = 'M')IS NOT FALSE; Tianqing Zhang SCU

  30. SELECT Statement 6 • Range Conditions • Range search conditions, like the condition "retrieve all the librarians whose salary is between 25000 and 35000“, will be expressed with a conjunction SELECT * FROM LIBRARIAN WHERE Salary >= 25000 AND Salary <= 35000; • Using the BETWEEN operator SELECT * FROM LIBRARIAN WHERE Salary BETWEEN 25000 AND 35000; Tianqing Zhang SCU

  31. SELECT Statement 7 • Pattern Matching and String Concatenation • A percent sign `%‘ indicates a match with an arbitrary number of characters including spaces. %任意个字符 • An underscore sign ‘_’ matches a single arbitrary character. _ 一个字符 • Example 1: Retrieve all the Librarians whose first name is Nick. SELECT SSN, Name FROM LIBRARIAN WHERE Name LIKE 'Nick%'; Tianqing Zhang SCU

  32. SELECT Statement 8 • Pattern Matching and String Concatenation • A percent sign `%‘ indicates a match with an arbitrary number of characters including spaces. %任意个字符 • An underscore sign ‘_’ matches a single arbitrary character. _ 一个字符 • Example 2: Display in a single column the full name of all members in Pittsburgh (area code number 412) who have phone numbers, the first digit of which is a 6 and the third a 3. ||连接串 SELECT Fname || MI || Lname AS Name, PhoneNumber FROM MEMBER WHERE PhoneNumber LIKE '(412) 6_3%'; Tianqing Zhang SCU

  33. SELECT Statement 9 • Pattern Matching and String Concatenation • In the event that the search pattern includes a percent sign, we can use an escape character to cause the SQL interpreter to interpret the percent sign as itself. 如何表示&和_本身？ 转义字 • Example: Display the name and call number of all the book titles that contain '10%' as part of the title name. SELECT Name, CallNumber FROM TITLES WHERE Name LIKE '%10&%%' ESCAPE '&'; Tianqing Zhang SCU

  34. SELECT Statement 10 • Set membership search condition (IN/NOT IN) • Example: List all managers and supervisors. SELECT staffNo,fName,lName,position FROM Staff WHERE position IN (‘Manager’, ‘Supervisor’); • Example: List all librarians who are not managers and supervisors. SELECT staffNo,fName,lName,position FROM Staff WHERE position NOT IN (‘Manager’, ‘Supervisor’); SELECT staffNo,fName,lName,position FROM Staff WHERE position NOT IN (‘Manager’, ‘Supervisor’) is not false; Tianqing Zhang SCU

  35. SELECT Statement 11 • Aggregate Functions • Five aggregate functions:SUM, MAX, MIN, AVG, and COUNT 五个基本集函数 • Example: Display all the statistics about librarian salaries. SELECT SUM (Salary) AS TotalSalaries, --求和 MAX (Salary) AS MaxSalary, --最大值 MIN (Salary) AS MinSalary, --最小值 AVG (Salary) AS AvgSalary, --平均值 COUNT (*) AS Cardinality, --元组计数 COUNT (DISTINCT Salary) AS Salarylevels –不重复非空值计数 FROM LIBRARIAN; Tianqing Zhang SCU

  36. SELECT Statement 12 • Aggregate Functions • Distinguish between count(*) and count(column). count(*)对所有选出元组计数， count(column)只对column非NULL的原组计数。 • Example: SELECT count(*) FROM librarian; SELECT count(salary) FROM librarian; Tianqing Zhang SCU

  37. SELECT Statement 13 • Grouping in SQL: The GROUP BY and HAVING Clauses 分组分组限制 • In SQL, we can group rows together based on the value of some attributes that we call the grouping attributes. • Example: List the call number of the books that have more than two editions. SELECT CallNumber, COUNT(*) AS NoPopularBooks FROM BOOK WHERE Edition >= 3 GROUP BY CallNumber; Tianqing Zhang SCU

  38. SELECT Statement 14 • Grouping in SQL: The GROUP BY and HAVING Clauses 分组分组限制 • The HAVING clause to select only those groups that we are interested in. HAVING短语对分组进行限制 • Example: assume that we are interested in finding out the number of copies of the popular books (for example, with Edition >=3), for which at least five copies exist in the library. SELECT CallNumber, COUNT(*) AS NoPopularBooks FROM BOOK WHERE Edition >= 3 --元组限制 GROUP BY CallNumber --分组 HAVING COUNT (*) >= 5; --分组限制 Tianqing Zhang SCU

  39. SELECT Statement 15 • Grouping in SQL: The GROUP BY and HAVING Clauses 分组分组限制 • A wrong example: SELECT Book_Id, CallNumber, COUNT(*) AS NoPopularBooks FROM BOOK WHERE Edition >= 3 GROUP BY CallNumber; • It’s wrong, why? Tianqing Zhang SCU

  40. SELECT Statement 16 • Grouping in SQL: The GROUP BY and HAVING Clauses 分组分组限制 • A wrong example: SELECT Book_Id, CallNumber, COUNT(*) AS NoPopularBooks FROM BOOK WHERE Edition >= 3 GROUP BY CallNumber; • It’s wrong, why? • SELECT后一般只能出现集函数和GROUP BY后的分组列 Tianqing Zhang SCU

  41. Nested Queries and Set Comparisons • In some queries, the selection conditions can be expressed more easily in terms of set comparisons. 查询中使用集合 • Explicit definition： (1, 2, 3)， ('Science', 'Art', 'Children'). • Implicit definition：use an inner SELECT statement (subquery) whose output table (set) is used by the selection condition of the outer SELECT statement in order to produce the final result. nested queries 嵌套查询 • SELECT MemNo, Fname, MI, Lname FROM MEMBER WHERE MemNo =- -必须保证子查询单值 ( SELECT BorrowerMemNo FROM BOOK WHERE Book_id = 999); Tianqing Zhang SCU

  42. Set Membership 1 • The IN and NOT IN operators check for simple membership. • Example：List each head librarian's SSN, along with their section, except those of the Science, Art, and Children sections. • SELECT HeadSSN, Name FROM SECTION WHERE Name NOT IN ('Science', 'Art', 'Children'); Tianqing Zhang SCU

  43. Set Membership 2 • The IN and NOT IN operators check for simple membership. • Example：List each head librarian's SSN, along with their section, except those of the Science, Art, and Children sections. • SELECT HeadSSN, Name FROM SECTION WHERE Name NOT IN ('Science', 'Art', 'Children'); • 思考：如果Name为NULL，情况怎样？ Tianqing Zhang SCU

  44. Set Membership 3 • IS NOT FALSE: • SELECT HeadSSN, Name FROM SECTION WHERE Name NOT IN ('Science', 'Art', 'Children') IS NOT FALSE; - -允许NULL • IS TRUE: • SELECT HeadSSN, Name FROM SECTION WHERE Name NOT IN ('Science', 'Art', 'Children') IS TRUE; - -明确不允许NULL，可省略 Tianqing Zhang SCU

  45. Set Membership 4 • An example using the IN operator： • List the last names of all librarians who are not head librarians. • SELECT LName FROM LIBRARIAN WHERE SSN NOT IN (SELECT LIBSSN FROM SECTION) IS NOT FALSE; Tianqing Zhang SCU

  46. Quantified Set Comparisons 1 • The test for membership, which is an equality comparison, can be generalized to other comparisons ( =, <>, >, >=, <, <= ) and can be quantified using ANY (that is, SOME) or ALL. • Comparisons ( =, <>, >, >=, <, <= ) 要求子查询单值，类型可比，子查询只能在比较运算符右边。 • Example：List the SSN of all librarians whose salary is lower than that of the librarian whose SSN is ‘S3-07-021’. • SELECT SSN FROM Librarian WHERE salary < (SELECT salary FROM Librarian WHERE SSN = ‘S3-07-021’); Tianqing Zhang SCU

  47. Quantified Set Comparisons 2 • Comparisons ( =, <>, >, >=, <, <= ) + (ANY or ALL) 实现各种复杂语义 • > ANY 不是最小，>= ALL 最大，… • Example 1： List the SSN of all librarians whose salary is the lowest in the library. • SELECT SSN FROM Librarian WHERE salary <= ALL (SELECT salary FROM Librarian); Tianqing Zhang SCU

  48. Quantified Set Comparisons 3 • Comparisons ( =, <>, >, >=, <, <= ) + (ANY or ALL) 实现各种复杂语义 • Example 2： List the name and SSN of all head librarians whose salary is lower than that of any librarian who is not a head librarian. • SELECT Name, SSN FROM LIBRARIAN, SECTION WHERE SSN = HeadSSN AND salary < ANY (SELECT Salary FROM LIBRARIAN L WHERE L.SSN NOT IN ( SELECT HeadSSN FROM SECTION ) IS NOT FALSE); Tianqing Zhang SCU

  49. Set Comparisons: EMPTY and UNIQUE 1 • The EXISTS (not empty) and NOT EXISTS (empty) operators test for emptiness of a result. 存在测试 • Example 1: List the names and SSNs of all librarians who are not head librarians. • SELECT L.SSN, L.Name FROM LIBRARIAN L WHERE NOT EXISTS (SELECT * FROM SECTION WHERE L.SSN = HeadSSN); - -内外层相关 • 相关子查询：计算时，类似两重循环。外层带值入内层，测试是否有值。 Tianqing Zhang SCU

  50. Set Comparisons: EMPTY and UNIQUE 2 • The EXISTS (not empty) and NOT EXISTS (empty) operators test for emptiness of a result. • Example 2: List the member numbers of all library member who haven’t borrowed any book. • SELECT MemNo FROM Member M WHERE NOT EXISTS (SELECT * FROM Book WHERE M.MemNo = BorrowerMemNo); - -内外层相关 Tianqing Zhang SCU

More Related