西安电子科技大学：《数据库系统 DataBase System》课程教学资源（PPT课件讲稿）Unit 3 SQL

DataBase System ta Unit 3 SQL 3.1 Introduction 13.2 Setting Up the database 4 3.3 Queries 43. 4 View 43.5 Data manipulation M 3.6 Security Haichang Gao, Software School, Xidian University 3

DataBase System Haichang Gao , Software School , Xidian University 3 Unit 3 SQL 3.1 Introduction 3.2 Setting Up the Database 3.3 Queries 3.4 View 3.5 Data manipulation 3.6 Security

DataBase System About SQL g SQL is of the ability to implementing database in a computerized environment G The sQL is an non-procedural language G Power of SQL E Data definition ( DDL) ( Include Other database object definition E Interactive Data manipulation(DML) H Embedded SQL and dynamic SQL(数据库应用课讲) A Integrity Authorization (Security) Transaction Control Haichang Gao, Software School, Xidian University

DataBase System Haichang Gao , Software School , Xidian University 4 About SQL SQL is of the ability to implementing database in a computerized environment. The SQL is an non-procedural language. Power of SQL:  Data definition (DDL) ( Include Other database object definition )  Interactive Data manipulation (DML)  Embedded SQL and dynamic SQL(数据库应用课讲)  Integrity  Authorization (Security)  Transaction Control

DataBase System 多 History of SQL H IBM Sequel (specifying QUeries As Relational Expression, 1972)language developed as part of System R project at the IBM San Jose research lab H SEQUEL (Structured English QUEry language, 1974) Renamed SQL( Structured Query Language, 1976) E ANSI and ISO standard SQL SQL-86, SQL-89(SQL1) >SQL-92 (SQL2 SQL: 1999(SQL3) SOL:2003 A Commercial systems offer most, if not all, sQL-92 features plus varying feature sets from later standards and special proprietary features Haichang Gao, Software School, Xidian University

DataBase System Haichang Gao , Software School , Xidian University 5 History of SQL  IBM Sequel (Specifying QUeries As Relational Expression, 1972) language developed as part of System R project at the IBM San Jose Research Lab  SEQUEL (Structured English QUEry Language,1974) Renamed SQL (Structured Query Language, 1976)  ANSI and ISO standard SQL: ➢ SQL-86, SQL-89 (SQL1) ➢ SQL-92 (SQL2) ➢ SQL:1999 (SQL3) ➢ SQL:2003  Commercial systems offer most, if not all, SQL-92 features, plus varying feature sets from later standards and special proprietary features

DataBase System ta Unit 3 SQL 43.1 Introduction 43.2 Setting Up the Database 4 3.3 Queries 43. 4 View 43.5 Data manipulation M 3.6 Security Haichang Gao, Software School, Xidian University 6

DataBase System Haichang Gao , Software School , Xidian University 6 Unit 3 SQL 3.1 Introduction 3.2 Setting Up the Database 3.3 Queries 3.4 View 3.5 Data manipulation 3.6 Security

DataBase System Data Definition Language Data Definition Language Ddl) provide the abilities to setting up a database Cg Ddl allows the specification of not only a set of relations but also information about each relation Including s The schema for each relation t The domain of values associated with each attribute u Integrity constraints a The set of indices to be maintained for each relations H Security and authorization information for each relation H The physical storage structure of each relation on disk Haichang Gao, Software School, Xidian University

DataBase System Haichang Gao , Software School , Xidian University 7 Data Definition Language Data Definition Language (DDL) provide the abilities to setting up a database. DDL allows the specification of not only a set of relations but also information about each relation, including:  The schema for each relation.  The domain of values associated with each attribute.  Integrity constraints  The set of indices to be maintained for each relations.  Security and authorization information for each relation.  The physical storage structure of each relation on disk

DataBase System Ks Domain Types in SQL H char(n): Fixed length character string, with user-specified length n E varchar(n): Variable length character strings, with user specified maximum length n E int: Integer(a finite subset of the integers that is machine dependent) E smallint: Small integer(a machine-dependent subset of the integer domain type) E numeric(p, d): Fixed point number, with user-specified precision of p digits, with n digits to the right of decimal point Haichang Gao, Software School, Xidian University 8

DataBase System Haichang Gao , Software School , Xidian University 8 Domain Types in SQL  char(n): Fixed length character string, with user-specified length n.  varchar(n): Variable length character strings, with user￾specified maximum length n.  int: Integer (a finite subset of the integers that is machine￾dependent).  smallint: Small integer (a machine-dependent subset of the integer domain type).  numeric(p,d): Fixed point number, with user-specified precision of p digits, with n digits to the right of decimal point

DataBase System Ks Domain Types in SQL E real, double precision: Floating point and double-precision floating point numbers, with machine-dependent precision E float(n): Floating point number, with user-specified precision of at least n digits E date: Dates, containing a(4 digit)year, month and date Example: date 2005-7-27 H time: Time of day, in hours, minutes and seconds Example: time 09: 00: 30 time 09: 00: 30.75 E timestamp: date plus time of day Example: timestamp 2005-7-27 09: 00: 30.75 Haichang Gao, Software School, Xidian University

DataBase System Haichang Gao , Software School , Xidian University 9 Domain Types in SQL  real, double precision: Floating point and double-precision floating point numbers, with machine-dependent precision.  float(n): Floating point number, with user-specified precision of at least n digits.  date: Dates, containing a (4 digit) year, month and date ➢ Example: date ‘2005-7-27’  time: Time of day, in hours, minutes and seconds. ➢ Example: time ‘09:00:30’ time ‘09:00:30.75’  timestamp: date plus time of day ➢ Example: timestamp ‘2005-7-27 09:00:30.75’

DataBase System Ks Domain Types in SQL E blob: binary large object - object is a large collection of uninterpreted binary data( whose interpretation is left to an application outside of the database system) H clob: character large object --object is a large collection of character data A USer -defined domain >Example: create domain money numeric(12, 2) Haichang Gao, Software School, Xidian University 10

DataBase System Haichang Gao , Software School , Xidian University 10 Domain Types in SQL  blob: binary large object -- object is a large collection of uninterpreted binary data (whose interpretation is left to an application outside of the database system).  clob: character large object -- object is a large collection of character data.  User-defined domain: ➢ Example: create domain money numeric(12, 2)

DataBase System Creating DataBase Creating the Banking data base: t Database schema: branch(branch name, branch city, assets customer(customer name, customer street, customer city depositor (customer name, account number account account number, branch name, balance borrower(customer name, loan number) loan(loan number, branch name, amount) H Creating database Banking steps: D)CREATE DATABASE Banking .(Syntax lie on DBMS) 2) Creating referenced tables(被参照关系) 3)Creating referencing tables 4)Creating other object of database Haichang Gao, Software School, Xidian University 11

DataBase System Haichang Gao , Software School , Xidian University 11 Creating DataBase  Creating the Banking database:  Database Schema: branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) depositor (customer_name, account_number) account (account_number, branch_name, balance) borrower (customer_name, loan_number) loan (loan_number, branch_name, amount)  Creating database Banking steps: 1) CREATE DATABASE Banking … (syntax lie on DBMS) 2) Creating referenced tables(被参照关系) 3) Creating referencing tables 4) Creating other object of database

DataBase System Creating Tables Syntax An sql relation is defined using the create table command CREATE TaBLE ( [k] i] Haichang Gao, Software School, Xidian University 12

DataBase System Haichang Gao , Software School , Xidian University 12 Creating Tables An SQL relation is defined using the create table command:  CREATE TABLE ( [ [ [] , [ ...n ] ] [ ], ] );  Syntax