1 / 23

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?. SQL commands below. CREATE TABLE DemoForClass (FirstName varchar (12) NOT NULL default 'First Name',

anjolie-beard
Télécharger la présentation

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? CS 105 Spring 2010

  2. SQL commands below 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 Spring 2010

  3. What the table looks like Note: the name Kingfishersmith was truncated CS 105 Spring 2010

  4. 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 don’t forget the ) CS 105 Spring 2010

  5. Creating a table in SQLyog Using a Wizard CS 105 Spring 2010

  6. Language layout “field name" "datatype" [other constraints], ssn CHAR(11) NOT NULL, CS 105 Spring 2010

  7. The Primary Key specifies the field that uniquely identifies each record in the Table Primary Key as shown in old client CS 105 Spring 2010

  8. Setting a Primary Key • A primary key is a column (field) that uniquely identifies the rest of the data in any given record. • 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 Spring 2010

  9. 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 will automatically check that entered data is appropriate to the field type • If the field is an item price, 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 Spring 2010

  10. 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 • It is required • See SAMS book, lessons 17 and 22 CS 105 Spring 2010

  11. 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. VARCHAR(30) -- entry of varying length,maximumpossible is 30 characters, can be less • TEXT – a character string that does nothave a fixed length CS 105 Spring 2010

  12. More datatypes • number(size) • unsigned(no negative numbers) • date Date value • Timestamp ---YYYY-MM-DD CS 105 Spring 2010

  13. 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 Spring 2010

  14. Default value in SQL CREATE TABLEtablestudents (FirstName VARCHAR (12) DEFAULT‘First Name’ NOT NULL (etc.) CS 105 Spring 2010

  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 Spring 2010

  16. Adding Items Can Be Tricky CS 105 Spring 2010

  17. Alter Table – Using a Query Adding a field…. CS 105 Spring 2010

  18. 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 Spring 2010

  19. 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 Spring 2010

  20. 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 Spring 2010

  21. Before running Update CS 105 Spring 2010

  22. After running the statement CS 105 Spring 2010

  23. What did we learn? • What is a constraint? • How do we change information? • Can a Primary Key be Null? CS 105 Spring 2010

More Related