How to Set Up a MySQL Database

1. How to Set Up a MySQL Database Stanislava Armstrong http://www.cs.nott.ac.uk/~saw/teaching/G64DBS/lecture_slides.html How to Set Up a MySQL Database

2. Download MySQL • You can download MySQL from their website: http://dev.mysql.com/downloads/mysql/ • You can register or click on the link (it’s a rather small one) “No thanks, just take me to downloads!” • Then choose one of the local mirror sites • Follow the on screen instructions to install MySQL • Start up MySQL command line (from the Start menu) How to Set Up a MySQL Database

3. Databases - USE • After you log in you will need to select a database to work with. Use: USE <database_name>; How to Set Up a MySQL Database

4. Databases – SHOW, CREATE and DELETE • To see a list of available databases use: SHOW DATABASES; • To create a new database use: CREATE DATABASE <name>; • To delete a database use: DROP DATABASE <name>; How to Set Up a MySQL Database

5. Execute From File • To execute SQL from a file, the file needs to be saved in the directory where you are running MySQL from. • File extension should be .txt SOURCE <file_name>.txt; • Easier to correct errors • Copy and paste the solution from the file to your coursework • You can come back and have a look at/run the code at any time How to Set Up a MySQL Database

6. SQL and the Data Dictionary • The data dictionary or catalogue stores • Information about database tables • Information about the columns of tables • Other information - users, locks, indexes, and more • This is ‘metadata’ • Some DBMSs let you query the catalogue • In MySQL you can access the metadata in several ways • There are ‘system tables’ with metadata in them • You can also DESCRIBE tables How to Set Up a MySQL Database

7. MySQL Data Dictionary • To find out what tables and sequences you have defined use: SHOW TABLES; • A list of tables names will be displayed like this: How to Set Up a MySQL Database

8. MySQL Data Dictionary • To see how a table was created, and therfore all colums, data types, constraints, etc. use SHOW CREATE TABLE <TABLE_NAME>; • A list of tables names will be displayed like this: How to Set Up a MySQL Database

9. MySQLData Dictionary • To find the details of a table use DESCRIBE <table name>; How to Set Up a MySQL Database

10. MySQLData Dictionary • To delete a table use DROP TABLE <table name>; How to Set Up a MySQL Database

11. Also Useful to Know • Use the up and down arrows on your keyboard to get the previously executed commands. • # and - - are used to declare a one line comment. Anything that comes after either of those symbols will be ignored • /*...*/ can be used for a in line comment. Anything b/n them will be ignored • /*... . . ...*/ they can also be used to declare a multiple line comment How to Set Up a MySQL Database

12. Keywords' Definitions • The easiest way to find about the full functionality and syntax related to a keyword is to Google it! • Good search term: “MySQL <keyword>” • Usually returns an entry from MySQL documentation in first place • For a “quick and dirty” look up http://www.pantz.org/software/mysql/mysqlcommands.html has a list of commonly used commands. How to Set Up a MySQL Database