1 / 48

A Guide to MySQL

3. A Guide to MySQL. Objectives. Start MySQL and learn how to use the MySQL Reference Manual Create a database Change (activate) a database Create tables using MySQL Create and run SQL commands in MySQL. Objectives (continued). Identify and use data types to define columns in tables

nikita
Download Presentation

A Guide to MySQL

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. 3 A Guide to MySQL

  2. Objectives • Start MySQL and learn how to use the MySQL Reference Manual • Create a database • Change (activate) a database • Create tables using MySQL • Create and run SQL commands in MySQL A Guide to MySQL

  3. Objectives (continued) • Identify and use data types to define columns in tables • Understand and use nulls • Add rows to tables • View table data • Correct errors in a database A Guide to MySQL

  4. Objectives (continued) • Save SQL commands and results to a file • Describe a table’s layout using MySQL A Guide to MySQL

  5. Introduction • Structured Query Language (SQL): • Popular and widely used language for retrieving and manipulating database data • Developed in mid-1970s under the name SEQUEL • Renamed SQL in 1980 • Used by most DBMSs A Guide to MySQL

  6. Introduction to MySQL • Starting MySQL • Obtaining help • Accessing the MySQL Reference Manual A Guide to MySQL

  7. Starting MySQL • Windows XP • Click Start button • Point to All Programs • Point to MySQL on menu • Point to MySQL Server 4.1 • Click MySQL Command Line Client • Must enter password in Command Line Client window A Guide to MySQL

  8. Obtaining Help in MySQL • Type \h at MySQL> prompt • Type “help” followed by name of command • help contents • help union A Guide to MySQL

  9. A Guide to MySQL

  10. Using MySQL Reference Manual to Get Help • Click MySQL Manual - Table of Contents on MySQL 4.1 submenu • Can access online A Guide to MySQL

  11. A Guide to MySQL

  12. Creating a Database • Must create a database before creating tables • Use CREATE DATABASE command • Include database name A Guide to MySQL

  13. Creating a Database (continued) A Guide to MySQL

  14. Changing the Default Database • Default database: database to which all subsequent commands pertain • USE command, followed by database name: • Changes the default database • Execute at the start of every session A Guide to MySQL

  15. Creating a Table • Describe the layout of each table in the database • Use CREATE TABLE command • TABLE is followed by the table name • Follow this with the names and data types of the columns in the table • Data types define type and size of data A Guide to MySQL

  16. Table and Column Name Restrictions • Names cannot exceed 18 characters • Must start with a letter • Can contain letters, numbers, and underscores (_) • Cannot contain spaces A Guide to MySQL

  17. Creating the REP Table A Guide to MySQL

  18. Entering Commands in MySQL • Commands are free-format; no rules stating specific words in specific positions • Press ENTER to move to the next line in a command • Indicate the end of a command by typing a semicolon • Commands are not case sensitive A Guide to MySQL

  19. Running SQL Commands A Guide to MySQL

  20. Editing SQL Commands • Statement history: stores most recently used command • Editing commands: • Use arrow keys to move up, down, left, and right • Use Ctrl+A to move to beginning of line • Use Ctrl+E to move to end of line • Use Backspace and Delete keys A Guide to MySQL

  21. Errors in SQL Commands A Guide to MySQL

  22. Editing MySQL Commands • Press Up arrow key to go to top line • Press Enter key to move to next line if line is correct • Use Right and Left arrow keys to move to location of error • Press ENTER key when line is correct • If Enter is not pressed on a line, line not part of the revised command A Guide to MySQL

  23. Dropping a Table • Can correct errors by dropping (deleting) a table and starting over • Useful when table is created before errors are discovered • Command is followed by the table to be dropped and a semicolon • Any data in table also deleted A Guide to MySQL

  24. Data Types • For each table column, type of data must be defined • Common data types: • CHAR(n) • VARCHAR(n) • DATE • DECIMAL(p,q) • INT • SMALLINT A Guide to MySQL

  25. Nulls • A special value to represent situation when actual value is not known for a column • Can specify whether to allow nulls in the individual columns • Should not allow nulls for primary key columns A Guide to MySQL

  26. Implementation of Nulls • Use NOT NULL clause in CREATE TABLE command to exclude the use of nulls in a column • Default is to allow null values • If a column is defined as NOT NULL, system will reject any attempt to store a null value there A Guide to MySQL

  27. Adding Rows to a Table • INSERT command: • INSERT INTO followed by table name • VALUES command followed by specific values in parentheses • Values for character columns in single quotation marks A Guide to MySQL

  28. The Insert Command A Guide to MySQL

  29. Modifying the INSERT Command • To add new rows modify previous INSERT command • Use same editing techniques as those used to correct errors A Guide to MySQL

  30. Adding Additional Rows A Guide to MySQL

  31. The INSERT Command with Nulls • Use a special format of INSERT command to enter a null value in a table • Identify the names of the columns that accept non-null values, then list only the non-null values after the VALUES command A Guide to MySQL

  32. The INSERT Command with Nulls • Enter only non-null values • Precisely indicate values you are entering by listing the columns A Guide to MySQL

  33. The INSERT Command with Nulls (continued) A Guide to MySQL

  34. Viewing Table Data • Use SELECT command to display all the rows and columns in a table • SELECT * FROM followed by the name of the table • Ends with a semicolon A Guide to MySQL

  35. Viewing Table Data (continued) A Guide to MySQL

  36. Viewing Table Data (continued) A Guide to MySQL

  37. Correcting Errors In the Database • UPDATE command is used to update a value in a table • DELETE command allows you to delete a record • INSERT command allows you to add a record A Guide to MySQL

  38. Correcting Errors in the Database • UPDATE: change the value in a table • DELETE: delete a row from a table A Guide to MySQL

  39. Correcting Errors in the Database (continued) A Guide to MySQL

  40. Correcting Errors in the Database (continued) A Guide to MySQL

  41. Saving SQL Commands • Allows you to use commands again without retyping • Different methods for each SQL implementation you are using • Oracle SQL*Plus and SQL*Plus Worksheet use a script file • Access saves queries as objects • MySQL uses an editor to save text files A Guide to MySQL

  42. Saving SQL Commands • Script file: • File containing SQL commands • Use a text editor or word processor to create • Save with a .txt file name extension • Run in MySQL: • SOURCE file name • \. file name • Include full path if file is in folder other than default A Guide to MySQL

  43. Creating the Remaining Database Tables • Execute appropriate CREATE TABLE and INSERT commands • Save these commands to a secondary storage device A Guide to MySQL

  44. Describing a Table A Guide to MySQL

  45. Summary • Use MySQL Command Line Client window to enter commands • Type \h or help to obtain help at the mysql> prompt • Use MySQL Reference Manual for more detailed help A Guide to MySQL

  46. Summary (continued) • Use the CREATE DATABASE command to create a database • Use the USE command to change the default database • Use the CREATE TABLE command to create tables • Use the DROP TABLE command to delete a table A Guide to MySQL

  47. Summary (continued) • CHAR, VARCHAR, DATE, DECIMAL, INT and SMALLINT data types • Use INSERT command to add rows • Use NOT Null clause to identify columns that cannot have a null value • Use SELECT command to view data in a table A Guide to MySQL

  48. Summary (continued) • Use UPDATE command to change the value in a column • Use DELETE command to delete a row • Use SHOW COLUMNS command to display a table’s structure A Guide to MySQL

More Related