Difference between revisions of "Netbeans/C3/Connecting-to-a-MySQL-Database/English-timed"
From Script | Spoken-Tutorial
PoojaMoolya (Talk | contribs) |
Sandhya.np14 (Talk | contribs) |
||
| Line 110: | Line 110: | ||
|- | |- | ||
| 02:18 | | 02:18 | ||
| − | |'''3306''' is the default '''server port''' | + | |'''3306''' is the default '''server port Number'''. |
|- | |- | ||
Revision as of 16:21, 30 August 2018
| 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 version 12.04 |
| 00:44 | and Netbeans IDE version 7.1.1. |
| 00:48 | You also need the Java Development Kit (JDK) version 6 |
| 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 PHP and MySQL 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 the 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 check-box 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 check-box 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 Primary 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 an 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 C onnection 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,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: * conducts 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 the link provided here. |
| 15:27 | This tutorial has been contributed by IT for Change. |
| 15:30 | Thank you. |
