1 / 26

Creating Tables, Setting Constraints, and Datatypes

Creating Tables, Setting Constraints, and Datatypes. What is a constraint and why do we use it? What is a datatype? What does CHAR mean?. What can this mean?. Language explained. CREATE TABLE tablename (column1 datatype, column2 datatype,

kort
Download Presentation

Creating Tables, Setting Constraints, and Datatypes

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. Creating Tables, Setting Constraints, and Datatypes What is a constraint and why do we use it? What is a datatype? What does CHAR mean?

  2. What can this mean? CS 105 Fall 2006

  3. Language explained CREATE TABLE tablename (column1 datatype, column2 datatype, column3 datatype ); create table means create a table ( begin defining fields with a ( ) end of fields is signaled by ) ; end of statement Add the ) mark! CS 105 Fall 2006

  4. Validation, Constraints NOT NULL means that the column must have a value in each row. If NULL is used, that column may be left empty in a given row. • The database automatically checks that entered data is appropriate to the field type • If the field is a phone number, you can create a constraint that input is to be numbers only and no letters are allowed. • We will not cover "Input Masks" this semester CS 105 Fall 2006

  5. What is a constraint? A constraint is basically a rule associated with a column that the data entered into that column must follow. • "Not Null" -- a column can't be left blank • VARCHAR(30) -- entry of varying length, maximum possible is 30 characters, can be less • See SAMS book, lessons 17 and 22 CS 105 Fall 2006

  6. Design View in SQLyog: Unsigned means than no sign is accepted in front of a number. That means it won’t accept –2, for example. • How do we determine what to accept in each field? • These are the table properties: SAMS Lesson 17 CS 105 Fall 2006

  7. Doing nearly the same thing in SQLyog DemoForClass CREATE TABLE 'DemoForClass' ( ‘FirstName' varchar(12) NOT NULL default'First Name', 'LastName' varchar(12) NOT NULL default'Last Name', 'Year' year(4) NOT NULL default'2006', 'SSN' varchar(11) NOT NULL default'000-00-0000') CS 105 Fall 2006

  8. What the table looks like Note: the name Kingfishersmith was truncated CS 105 Fall 2006

  9. Creating a table in SQLyog CS 105 Fall 2006

  10. Number value in a field PK is for Primary Key Binary is for pictures, etc. No negative numbers allowed, and field will not take letters! CS 105 Fall 2006

  11. Starting to make sense? Table name • CREATE TABLE employee • (ssnCHAR(11) NOT NULL, first VARCHAR(15), • last VARCHAR(20) NOT NULL, • age INT(3), • address VARCHAR(30), • city VARCHAR(20), • state CHAR(2)); Field names: Datatype: *Note: MySQL may change CHAR to VARCHAR in this situation—see the MySQL Manual. CS 105 Fall 2006

  12. Language layout "column1" "datatype" [constraint], ssn CHAR(11) NOT NULL, CS 105 Fall 2006

  13. Default value in SQL CREATE TABLE tablestudents (FirstName VARCHAR (12) DEFAULT‘First Name’ NOT NULL (etc.) CS 105 Fall 2006

  14. Adding Items Can Be Tricky CS 105 Fall 2006

  15. NULL Values and calculations • If you add a value to another value that is NULL, the answer that MySQL gives you is NULL! The same thing sometimes happens in our Compass GradeBook—if a score is missing, sometimes the total score is not computed. It all depends on what software you use. CS 105 Fall 2006

  16. Alter Table – Using a Query CS 105 Fall 2006

  17. Variation 1 of the Insert Statement • If you know the order of the fields, you don’t have to specify the field names INSERT INTO Customers VALUES ('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com'); CS 105 Fall 2006

  18. Variation 2 of Insert Statement • You don’t have to insert a value for every field, unless it is a required (NOT NULL, PRIMARY KEY) field INSERT INTO Customers (cust_id, cust_name, cust_email) VALUES ('1000000001', 'Village Toys', 'sales@villagetoys.com'); CS 105 Fall 2006

  19. Update Statement • See page 131 and 132 of SAM’s book • Modifies data in a table UPDATE players SET firstname='fred' WHERE ssn='899-98-9989' CS 105 Fall 2006

  20. Before running Update CS 105 Fall 2006

  21. After running the statement CS 105 Fall 2006

  22. The Primary Key specifies the field that uniquely identifies each record in the Table Primary Key as shown in old client CS 105 Fall 2006

  23. Setting a Primary Key • A primary key is a column (field) that uniquely identifies the rest of the data in any given row. • More than one field can be combined to establish a primary key (e.g., Deanne + Smith rather than Smith) • (MySQL may give you trouble, but trust us) CS 105 Fall 2006

  24. Datatypes Datatypes specify what the type of data can be for that particular field. • A field called "Last_Name“ should have a "VARCHAR" (variable-length character) datatype. • A field called “SSN” should have a “char” datatype--that would constrain the size to exactly 9 or 11 characters. CS 105 Fall 2006

  25. Popular datatypes – see Appendix D • char(size)Fixed-length character string. Size is specified in parenthesis. Max 255 bytes. • varchar(size)Variable-length character string. Max size is specified in parenthesis. • TEXT – a character string that does not have a fixed length CS 105 Fall 2006

  26. More datatypes • number(size) • unsigned (no negative numbers) • Tinyint (integer) • This is very complicated—do not worry about it for our course. For details see http://www.mysql.com/doc/ • date Date value • Timestamp ---YYYY-MM-DD CS 105 Fall 2006

More Related