Download
1 / 29
290 likes | 384 Views
Steven Emory Department of Computer Science California State University, Los Angeles. Using Relational Databases and SQL. Lecture 9: Data Definition Language. Topics for Today. Data Types Creating Tables Deleting Tables Adding Columns Deleting Columns Editing Columns Identity Columns
E N D
Steven Emory Department of Computer Science California State University, Los Angeles Using Relational Databases and SQL Lecture 9: Data Definition Language
Topics for Today • Data Types • Creating Tables • Deleting Tables • Adding Columns • Deleting Columns • Editing Columns • Identity Columns • Constraints
Integer Data Types • Data Types • TINYINT(width): 1 byte • SMALLINT(width): 2 bytes • MEDIUMINT(width): 3 bytes • INT(width): 4 bytes • BIGINT(width): 8 bytes • All (width) specifications are optional • INT given 104, displays 104 • INT(8) given 104, displays 00000104
Numeric Data Types • Data Types • FLOAT(n, d): 4 bytes • REAL(n, d): 8 bytes • All (n, d) specifications are optional. • FLOAT given 1.06, displays 1.06 • FLOAT(10, 1) given 1.06, displays 1.1 • FLOAT(10, 6) given 1.06, displays 1.060000
String and Date Data Types • Data Types • DATE: 3 bytes • CHAR(n): Variable bytes • VARCHAR(n): Variable bytes • All (n) specifications are optional • CHAR(1) Given ‘s’, displays s • CHAR(1) Given ‘sample’, displays ‘s’ • CHAR(10) Given ‘sample’, displays ‘sample’ • Prefer VARCHAR to CHAR
Special Notes • You can use DML and DDL commands in the console, query browser, and within an SQL script file • Transactions (BEGIN, ROLLBACK, and COMMIT) do not work with DDL commands. • Do not try to memorize all these commands • Use templates • After a while, you’ll remember them
Creating Tables • Syntax: • CREATETABLE ( fieldname datatype NULL | NOTNULL, fieldname datatype NULL | NOTNULL, ... fieldname datatype NULL | NOTNULL); • Each row specifies a column definition • Use NULL or NOT NULL to specify whether or not a column can contain NULL values
CREATE TABLE Example • Example: • CREATETABLE Musician ( Title VARCHAR(32) NOT NULL);INSERT INTO Musician VALUES(‘Guitarist’);INSERT INTO Musician VALUES(‘Pianist’);INSERT INTO Musician VALUES(‘Drummer’);INSERT INTO Musician VALUES(‘Vocalist’);INSERT INTO Musician VALUES(‘Bassist’);
CREATE TABLE Example • CREATE TABLE can also take an optional ENGINE parameter at the end (MySQL only): • CREATETABLE Musician ( Title VARCHAR(32) NOT NULL) Engine=InnoDB; • MySQL uses pluggable storage engines • Default is MyISAM • Another popular one is InnoDB • Currently only InnoDB supports foreign keys • Many uncommon ones are listed here
Dropping Tables • Syntax: • DROPTABLE [IFEXISTS] tablename; • Example: • DROPTABLE Artists; • NOTE: BEGIN and ROLLBACK do not work with DROPTABLE
Dropping Tables • When dropping tables (in MySQL only), it is useful to type in the following foreign key check commands when using the InnoDB storage engine • SET FOREIGN_KEY_CHECKS = 0; • DROPTABLEIFEXISTS tablename; • DROPTABLEIFEXISTS tablename; • DROPTABLEIFEXISTS tablename; • SET FOREIGN_KEY_CHECKS = 1;
Adding Columns • Syntaxes: • ALTERTABLE tablenameADD COLUMN column_definition; • ALTERTABLE tablenameADDCOLUMN column_definitionFIRST; • ALTERTABLE tablenameADDCOLUMN column_definitionAFTER column_name;
Adding Columns • Examples: • ALTERTABLE MusicianADDCOLUMN MusicianID INTNOT NULLFIRST; • ALTER TABLE MusicianADD COLUMN Type VARCHAR(16) NULL; • INSERT INTO MusicianVALUES(‘1’, ‘Guitarist’, ‘Electric’);
Dropping Columns • Syntax: • ALTERTABLE tablenameDROPCOLUMN column_name; • Examples: • ALTER TABLE MusicianDROP COLUMN Type; • ALTER TABLE MusicianDROP COLUMN MusicianID;
Editing Columns • MODIFY let’s you change a column’s data definition (without renaming it) • CHANGE does the same thing as MODIFY, but you can also specify a new column name • Syntax: • ALTERTABLE tablenameMODIFY fieldname column_definition; • ALTERTABLE tablenameCHANGE oldname newname column_definition
Editing Columns • Examples: • -- Let’s rename the Musician column to Instrument.ALTER TABLE MusicianCHANGE Musician Instrument VARCHAR(32) NOT NULL; • -- Let’s modify the Instrument column string length to make it really short.ALTER TABLE MusicianMODIFY Instrument VARCHAR(4) NOT NULL;
Constraints • A constraint is any kind of restriction placed on the data inserted into a table • Primary Key Constraints: Enforces uniqueness of data. • Foreign Key Constraints: A value must refer to an existing piece of data. • Default Constraints: Data not specifically inserted will take on default values. • Unique Constraints: Forces data (other than the primary key) to be unique.
Primary Key Constraints • CREATE TABLE Syntax: • Can add the keyword PRIMARYKEY at end of column definition • For more than one column you can use the CONSTRAINT keyword. • ALTER TABLE Syntax: • ALTERTABLE tablenameADDCONSTRAINT constraint_namePRIMARYKEY (field1, field2, ...);
Examples • Examples: • CREATETABLE Musician( Instrument INTNOTNULL, CONSTRAINT fk_musician PRIMARY KEY(Instrument)); • CREATETABLE Musician( Instrument INTNOTNULL PRIMARY KEY); • ALTER TABLE MusicianADDCONSTRAINT fk_musicianPRIMARY KEY(Instrument);
Foreign Key Constraints • Sometimes called integrity constraints • CREATE TABLE Syntax: • CONSTRAINT constraint_nameFOREIGNKEY(field1, field2, ...)REFERENCES parent_table(field1, field2, ...) • ALTER TABLE Syntax: • ALTERTABLE tablenameADDCONSTRAINT constraint_nameFOREIGNKEY (field1, field2, ...)REFERENCES parent_table(field1, field2, ...);
Foreign Key Constraints • Example: • -- Assign the proper foreign key to the taglines table.ALTERTABLE TaglinesADDCONSTRAINT fk_taglinesFOREIGNKEY(MovieID)REFERENCES Movies(MovieID); • -- Now try adding an invalid tagline.INSERTINTO TaglinesVALUES(1029837, ‘Some tagline goes here.’);
Unique Constraints • Use to make one or more columns (other than the primary key) contain only unique values • CREATE TABLE Syntax: • Just add the UNIQUE keyword at the end. • ALTER TABLE Syntax: • ALTERTABLE tablenameADDCONSTRAINT constraint_nameUNIQUE (fieldname1, fieldname2, ...);
Unique Constraints • Examples: • CREATE TABLE login( user_id INT NOT NULL PRIMARY KEY, username VARCHAR(32) NOT NULL UNIQUE, password VARCHAR(32) NOT NULL); • ALTER TABLE loginADD CONSTRAINT uq_usernameUNIQUE(username); • INSERTINTO login(1, ‘semory’, ‘mypassword’); • INSERTINTO login(2, ‘semory’, ‘anotherpass’);
Default Constraints • Using CREATE TABLE: • After specifying the datatype in the column definition, use the following:DEFAULT value • Using ALTER TABLE: • ALTERTABLE tablenameALTER fieldnameSETDEFAULT value;
Default Constraints • Examples: • DROP TABLE login; • CREATE TABLE login( user_id INT NOT NULL PRIMARY KEY, username VARCHAR(32) NOT NULL UNIQUE, password VARCHAR(32) DEFAULT ‘0xk2739’NOT NULL); • ALTERTABLE SalesPeopleALTER Base SETDEFAULT 100.00;
Dropping Constraints • Do not read the book on this! The book’s code will not work! Use the following syntax: • Syntax: • -- To drop a primary key...ALTERTABLE tablenameDROPPRIMARY KEY; • -- To drop a foreign key...ALTER TABLE tablenameDROP FOREIGN KEY keyname; • -- To drop any other key...ALTER TABLE tablename DROP KEY keyname;
Examples • Examples: • -- Drop the fk_taglines foreign key in the Taglines table that we added previously.ALTER TABLE TaglinesDROP FOREIGN KEY fk_taglines; • -- Drop the primary key in the Genres table.ALTER TABLE GenresDROP PRIMARY KEY; • -- Drop the uq_username unique key in the login table.ALTER TABLE login DROP KEY uq_username;
Identity Columns • Purpose is to auto-generate primary key values • MySQL uses the non-standard keyword AUTO_INCREMENT and you can only define it on the primary key • SQL standard uses GENERATE keyword • Syntax: • fieldname datatype NOT NULLAUTO_INCREMENT
Identity Column Example • Example: • DELETE FROM Musician; • ALTER TABLE MusicianMODIFY InstrumentVARCHAR(32) NOT NULL AUTO_INCREMENT; • INSERTINTO Musician VALUES(0, ‘Guitarist’);INSERTINTO Musician VALUES(0, ‘Pianist’);INSERTINTO Musician VALUES(0, ‘Vocalist’);INSERTINTO Musician VALUES(0, ‘Bassist’);INSERTINTO Musician VALUES(0, ‘Drummer’);
