Difference between revisions of "Netbeans/C3/Connecting-to-a-MySQL-Database/English-timed"
From Script | Spoken-Tutorial
PoojaMoolya (Talk | contribs) (Created page with '{| Border=1 || Time || Narration |- | 00.00 |Hello everyone, |- | 00.02 |Welcome to the tutorial on ''' 'Connecting to a MySQL Database' ''' |- |00.07 |In this tutorial, w…') |
|||
| Line 1: | Line 1: | ||
{| Border=1 | {| Border=1 | ||
| − | || Time | + | || '''Time''' |
| − | + | || '''Narration''' | |
| − | || Narration | + | |
| − | + | ||
|- | |- | ||
Revision as of 18:01, 9 July 2014
| Time | Narration |
| 00.00 | Hello everyone, |
| 00.02 | Welcome to the tutorial on 'Connecting to a MySQL Database' |
| 00.07 | In this tutorial, we will look at |
| 00.09 | Configuring MySQL server properties |
| 00.14 | Starting the MySQL server |
| 00.17 | Creating and connecting to the database |
| 00.20 | Creating database tables, under which we will explore two methods:
|
| 00.26 | using the sql editor, |
| 00.29 | using the create table dialogue and, finally,
|
| 00.33 | Running an SQL script
|
| 00.37 | For this demonstration, I am using the Linux Operating System Ubuntu v12.04,
|
| 00.44 | and Netbeans IDE v7.1.1 |
| 00.48 | You also need, the Java Development Kit (JDK) v6
|
| 00.54 | and MySQL database server
|
| 00.57 | To learn this tutorial, basic understanding of database management is necessary.
|
| 01.03 | To know more, watch PHPandMySQL spoken tutorials on the link shown. |
| 01.10 | Other standard programming terminologies have been used in this tutorial. |
| 01.16 | This tutorial demonstrates how to setup a connection to a MySQL database from the Netbeans IDE.
|
| 01.24 | Once connected, we will work with MySQL in the IDE's Database Explorer.
|
| 01.31 | Let us switch to the IDE now. |
| 01.36 | Netbeans IDE comes bundled with support for the MySQL RDBMS.
|
| 01.42 | Before you access MySQL database server in Netbeans, you must configure the MySQL server properties.
|
| 01.51 | Right-click the Databases node in the Services window. |
| 01.56 | Choose Register MySQL Server to open the MySQL server properties dialogue box. |
| 02.05 | Confirm that the server host name and the port are correct.
|
| 02.10 | Notice that the IDE enters localhost as the default server host name. |
| 02.18 | 3306 is the default server port number. |
| 02.23 | Enter the Administrator Username if not displayed |
| 02.27 | On my system, the Administrator username is root |
| 02.33 | Enter the Administrator password. |
| 02.36 | On my system, the password is blank. |
| 02.40 | Click the Admin Properties tab at the top of the dialog box. |
| 02.45 | This allows you to enter information for controlling the MySQL server. |
| 02.51 | In the Path/URL to admin tool: field, |
| 02.56 | type or browse to the location of your MySQL Administration application.
|
| 03.02 | On my system, the location to the tool is /usr/bin/mysqladmin
|
| 03.12 | Type any arguments for the admin tool in the Arguments field. |
| 03.18 | This can also be left blank. |
| 03.22 | In the Path to start command: field |
| 03.25 | type or browse to the location of the MySQL start command.
|
| 03.29 | On my system it is /usr/bin/mysqld_safe
|
| 03.38 | Type any arguments for the start command in the Arguments field.
|
| 03.42 | Here, I will type -u space root space start |
| 03.51 | In the Path to stop command:
|
| 03.54 | type or browse to the location of the MySQL stop command.
|
| 03.58 | This is usually the path to mysqladmin' in the bin folder of the MySQL installation directory. |
| 04.06 | On my system this is /usr/bin/mysqladmin |
| 04.14 | If the command is mysqladmin, in the Arguments field, type -u space root space stop. |
| 04.27 | When finished, the Admin Properties tab should resemble what is shown on the screen. |
| 04.33 | Click OK. |
| 04.36 | First ensure that the MySQL database server is running on your machine. |
| 04.42 | The MySQL server node in the Service window indicates whether the MySQL database server is connected. |
| 04.52 | After making sure that it is running, right-click the Databases >> MySQL server node and choose Connect. |
| 05.05 | When expanded, the MySQL server node displays all the available MySQL databases.
|
| 05.13 | A common way of interacting with databases is through an SQL Editor. |
| 05.19 | Netbeans has a built-in SQL Editor for this purpose. |
| 05.23 | You can access this by right-clicking on the connection node. |
| 05.29 | Let us now create a new database instance using the SQL Editor. |
| 05.34 | In the Services window, right-click the MySQL server node and choose Create Database. |
| 05.44 | In the Create Database dialogue , type the name of the new database. |
| 05.50 | I will name this mynewdatabase.
|
| 05.56 | You can also grant full access to a given user. |
| 06.01 | By default, only the admin user has the permissions to perform certain commands. |
| 06.08 | The drop-down list allows you to assign these permissions to a specified user. |
| 06.13 | It is a good practice to grant users most permissions, except to drop tables.
|
| 06.18 | And allow users to modify only those databases that are created by their application.
|
| 06.25 | But for now, we will leave the checkbox unselected. |
| 06.30 | Click OK.
|
| 06.34 | Let us now create tables, populate them with data, and modify the data maintained in tables. |
| 06.41 | mynewdatabase is currently empty. |
| 06.44 | Let us explore the first method to input data in the tables.
|
| 06.48 | In the Database explorer, expand the mynewdatabase connection node. |
| 06.58 | There are three sub folders: |
| 07.00 | Tables, Views and Procedures.
|
| 07.04 | Right-click the Tables folder and choose Execute Command. |
| 07.11 | A blank canvas opens in the SQL Editor in the main window. |
| 07.16 | Let us type a simple query in this SQL editor. |
| 07.30 | I have now typed a simple query in the SQL editor.
|
| 07.36 | This is a table definition for the Counselor table we are about to create.
|
| 07.42 | To execute this query, either right-click the Run SQL icon in the task bar at the top.
|
| 07.51 | Or right-click within the SQL Editor and choose Run Statement.
|
| 08.00 | The IDE generates the Counselor table in the database. |
| 08.04 | You can see this message in the Output window, |
| 08.12 | which says that the command was executed successfully,
|
| 08.17 | To verify these changes, right-click the Tables node in the Database Explorer.
|
| 08.25 | Choose Refresh. |
| 08.28 | This updates the current status of the specified database. |
| 08.32 | The new Counselor table now displays under the Tables option.
|
| 08.40 | If you expand the table node, you can see the columns that you created. |
| 08.46 | Let us now explore the next method to input data in the tables, |
| 08.51 | i.e. Using the Create Table Dialog |
| 08.54 | In the Database Explorer, right-click the Tables node, and choose Create Table. |
| 09.03 | The Create Table dialogue opens. |
| 09.06 | In the Table name text field, type Subject |
| 09.13 | Click on Add Column |
| 09.16 | In the Add Column dialogue, type id in the Name field. |
| 09.22 | Choose SMALLINT for data-type from the Type drop-down menu. |
| 09.30 | Select the Primary Key checkbox in the Add Column dialog box. |
| 09.35 | This is to specify the primary key for your table. |
| 09.39 | Note that when you select the Key check box, the Index and Unique check boxes are automatically selected; |
| 09.49 | Also the Null check box is deselected. |
| 09.53 | This is because primary keys are used to identify a unique row in the database. |
| 09.59 | Click OK. |
| 10.03 | Repeat this procedure to add the remaining columns, as shown on the screen. |
| 10.09 | We have now created a table named Subject that will hold data for Name, Description, and Counselor ID |
| 10.20 | Click OK. |
| 10.23 | By running SQL queries on a database, we can add, modify and delete data maintained in database structures. |
| 10.32 | Let us add a new record to the Counselor table. |
| 10.35 | Choose Execute Command from the Tables node context menu. |
| 10.43 | A new SQL Editor opens in the main window.
|
| 10.47 | In the SQL Editor, let us type a simple query: |
| 11.00 | To execute this query, right-click within the source editor, and choose Run Statement |
| 11.07 | Let us now verify if the new record has been added to the table. |
| 11.12 | Right-click the Counselor table, and choose View Data. |
| 11.18 | A new SQL Editor opens in the main window. |
| 11.21 | A query to select all data from the table is automatically generated. |
| 11.27 | The results of this statement are displayed in a table view below the workspace. |
| 11.41 | Note that a new row has been added with the data we just supplied. |
| 11.46 | We can also run an external SQL script directly in the IDE. |
| 11.52 | I have a SQL query here for demonstrative purposes.
|
| 11.59 | This script creates two tables similar to the ones we have just created.
|
| 12.04 | i.e. Counselor and Subject
|
| 12.09 | Because the script overwrites these tables, |
| 12.12 | we will delete these two tables if they already exist.
|
| 12.16 | To delete tables, right-click on the Counselor table |
| 12.21 | and choose Delete.
|
| 12.24 | Click Yes in the Confirm Object Deletion dialogue box. |
| 12.31 | Repeat the same for the Subject table |
| 12.38 | Now, open the existing SQL query file from your system. |
| 12.43 | From the File menu, choose Open File. |
| 12.48 | Browse to the location containing this file. |
| 12.54 | The script automatically opens in the SQL editor. |
| 12.59 | Make sure the connection to mynewdatabase is selected. |
| 13.03 | Check this from the connection drop-down in the toolbar at the top of the editor. |
| 13.13 | Click the Run SQL button in the task bar. |
| 13.17 | And the script is executed against the selected database. |
| 13.22 | Right-click the mynewdatabase connection node and choose Refresh. |
| 13.28 | This updates the database component to the current status of the specified database. |
| 13.34 | Right-click on any of these tables now and choose View Data. |
| 13.41 | And below the workspace, you can see the data contained in the new tables. |
| 13.52 | In this tutorial you learnt to, |
| 13.54 | configure MySQL on your computer |
| 13.57 | set up a connection to the database server from the IDE |
| 14.02 | create, delete, modify data and |
| 14.06 | run SQL queries |
| 14.10 | As an assignment, |
| 14.11 | Create another database instance with tables |
| 14.15 | Populate these tables with necessary data to maintain your personal book library
|
| 14.21 | And run these SQL statements to view data |
| 14.29 | I have created a similar database which maintains details of my personal movie library. |
| 14.37 | Your assignment should resemble this.
|
| 14.44 | Watch the video available at the link shown on the screen. |
| 14.48 | It summarizes the Spoken Tutorial project. |
| 14.51 | If you do not have good bandwidth, you can download and watch it. |
| 14.56 | The Spoken Tutorial project team conduct workshops using Spoken Tutorials.
|
| 15.01 | Gives certificates to those who pass an online test.
|
| 15.04 | For more details please write to contact@spoken-tutorial.org
|
| 15.10 | Spoken Tutorial Project is a part of the Talk to a Teacher Project |
| 15.15 | It is Supported by the National Mission on education through ICT, MHRD, Government of India |
| 15.20 | More information on this mission is available at link provided here |
| 15.27 | This tutorial has been contributed by IT for Change
|
| 15.30 | Thank you |
