(第7章课件)2013版mydbSQLforStudents.ppt

Chapter 7Structured Query Language (SQL) Chapter Objectives Learn basic SQL statements for creating database constructs Learn basic SQL SELECT statements and options for processing a single table Learn basic SQL SELECT statements for processing multiple tables with subqueries (EXISTS/NOT EXISTS is optional) Learn basic SQL SELECT statements for processing multiple tables with joins Learn SQL statements to add, modify, and delete data Understand the purpose of indexes and views SQL DDL and DML Sample Database Sample Database Sample Data Sample EMPLOYEE Relation Sample PROJECT Relation Sample ASSIGNMENT Relation CREATE TABLE CREATE TABLE statement is used for creating relations Each column is described with three parts: column name, data type, and optional constraints Example 1 CREATE TABLE PROJECT ( ProjectID Integer Primary Key, Name Char(25) Unique Not Null, Department VarChar(100) Null, MaxHours Numeric(6,1) Default 100); 注： Numeric为Microsoft SQL Server支持的十进制数 Constraints Constraints can be defined within the CREATE TABLE statement, or they can be added to the table after it is created using the ALTER table statement Six types of constraints: PRIMARY KEY may not have null values 创建复合键约束有2种方式 创建单属性键约束有3种方式 UNIQUE may have null values NULL/NOT NULL FOREIGN KEY 注意参照表和被参照表的关系 CHECK DEFAULT CHECK约束 经常用来限制列可能具有的值； CHECK约束必须编写成要么为真要么为假，并使得它们只需要利用常量或者利用所在表里的当前行的值就可以计算出来； CHECK约束不得包含子查询； CREATE TABLE: Example Example 2 苗 p97 CREATE TABLE 学生( 学号 CHAR(5) NOT NULL UNIQUE, 姓名CHAR(8) NOT NULL , 年龄 SMALLINT DEFAULT 20, 性别 CHAR(2), 所在系 CHAR(20), CONSTRAINT C2 CHECK(性别IN (’男’,’女’))); 中英文混合时需注意标点符号在英文状态下输入 此语句创建了Primary Key约束吗？ ALTER Statement ALTER statement changes table structure, properties, or constraints after it has been created ALTER statement can be used to create a composite primary key constraint(当主键为复合键时可使用ALTER statement 来定义) FOREING KEY CONSTRAIN: Example ALTER TABLE ASSIGNMENT ADD CONSTRAINT EmpFK FOREIGN K