A Guide to MySQL

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