Yorkville High School Computer Science Department
Yorkville High School Computer Science Department on Facebook  Yorkville High School Computer Science Department Twitter Feed  Yorkville High School Computer Science Department on Instagram

Yorkville High School Computer Science

ASSIGNMENTS: Compiler Part 2 - December 3, 2018 :: Challenges 7 - December 7, 2018 :: Network App - December 14, 2018 >>

Database Programming :: Lessons :: SQL Updates

Creating Tables

You can use the CREATE TABLE command to make new tables in a SQL database. The structure is the following:

CREATE TABLE <table_name> (
	<attribute> <type>
		[not null] [unique] [primary key]
		[,<primary key constraint>]
		[{,<foreign key constraint>}]);

All of the sections in [] above are optional and you can have multiple attribute/type pairs and multiple foreign key constraints. The type of an attribute can be any of the following:

There are more types than those listed above, but the above types will be the types you use the most often. Some types, such as the date and time types are useful in specific situations. Below is an example that creates the Branch table from the most recent project:

CREATE TABLE `Branch` (
  `BranchNum` decimal(2,0) NOT NULL PRIMARY KEY,
  `BranchName` char(50) DEFAULT NULL,
  `BranchLocation` char(50) DEFAULT NULL,
  `NumEmployees` decimal(2,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The engine property sets the storage engine for the DBMS. While there are over a dozen different engines the two most popular are InnoDB and MyISAM. InnoDB allows you to set foreign keys through phpMyAdmin so you should always make sure your tables are set to InnoDB.

Alter Tables

The ALTER TABLE command allows you to make changes to the structure of a table that already exists. You can add new attributes or modify existing attributes.

ALTER TABLE <table_name> add (
	<attribute> <type>
);

ALTER TABLE <table_name> modify (
	<attribute> <new_length>
);
    

The following examples add or modify the Branch table:

ALTER TABLE `Branch` ADD
	(`BranchZip` integer);

ALTER TABLE `Branch` MODIFY
	(`BranchName` char(255));

You can use the DROP TABLE command to remove a table and all its data.

DROP TABLE <table_name>;

You can delete data from a table using the DELETE command. You can use a WHERE condition to specify what data to delete like the following example:

DELETE FROM `Branch` WHERE `BranchName` = 'Oswego';

Changing the datatype of an attribute is trickier since it cannot be done directly. To do so you have to create a temporary table with the tuples from the original table. The data from the original table is then deleted so the table structure can be modified. The data from the temporary table is then copied back to the original table. The following example shows how this can be done to change the BranchName type to text:

CREATE TABLE `tempTable` AS SELECT * FROM `Branch`;
DELETE `Branch`;
ALTER TABLE `Branch` MODIFY (`BranchName` text);
INSERT INTO `Branch` SELECT * FROM `tempTable`;
DROP `tempTable`;

Insert and Update

There are three general ways to insert data into a table using the INSERT command. The first option inserts the data in the order the attributes are stored:

INSERT INTO `Branch`
	VALUES(5, 'Plano', '249 S. West St.', 5);

The second option allows you to insert the data and specify the attribute:

INSERT INTO `Branch`
	(`BranchNum`, `BranchName`, `BranchLocation`, `NumEmployees`)
	VALUES(5, 'Plano', '249 S. West St.', 5);

The final option is what was used previously to transfer data from a temporary table. You can insert the resulting data from a query:

INSERT INTO `Branch` SELECT * FROM `tempTable`;

Finally, the UPDATE command is used to modify data within a table. The WHERE clause selects the tuples to be modified and the SET clause specifies the attributes to be modified along with their new values.

UPDATE `Branch`
	SET `Employees` = 8
	WHERE `BranchName` = 'Downtown Yorkville';
Yorkville High School Computer Science Department on Facebook Yorkville High School Computer Science Department Twitter Feed Yorkville High School Computer Science Department on Instagram