Difference between revisions of "Netbeans/C3/Connecting-to-a-MySQL-Database/English-timed"
From Script | Spoken-Tutorial
PoojaMoolya (Talk | contribs) |
|||
(8 intermediate revisions by 3 users not shown) | |||
Line 6: | Line 6: | ||
|- | |- | ||
| 00:00 | | 00:00 | ||
− | |Hello everyone | + | |Hello everyone. |
|- | |- | ||
| 00:02 | | 00:02 | ||
− | |Welcome to the tutorial on ''' 'Connecting to a MySQL Database' ''' | + | |Welcome to the tutorial on ''' 'Connecting to a MySQL Database''''. |
|- | |- | ||
|00:07 | |00:07 | ||
− | |In this tutorial, we will look at | + | |In this tutorial, we will look at: |
|- | |- | ||
Line 30: | Line 30: | ||
|- | |- | ||
| 00:20 | | 00:20 | ||
− | |Creating '''database''' | + | | Creating '''database table'''s under which we will explore two methods: |
− | + | ||
− | + | ||
|- | |- | ||
|00:26 | |00:26 | ||
− | |using the sql editor | + | |using the '''sql editor''' |
|- | |- | ||
| 00:29 | | 00:29 | ||
− | |using the create table | + | |using the '''create table''' dialog and finally, |
− | + | ||
|- | |- | ||
| 00:33 | | 00:33 | ||
− | | Running an '''SQL''' script | + | |Running an '''SQL''' script. |
− | + | ||
|- | |- | ||
|00:37 | |00:37 | ||
− | |For this demonstration, I am using the '''Linux''' | + | |For this demonstration, I am using the '''Linux Operating System Ubuntu''' version '''12.04''' |
− | + | ||
|- | |- | ||
|00:44 | |00:44 | ||
− | |and ''' Netbeans IDE | + | |and ''' Netbeans IDE''' version '''7.1.1'''. |
|- | |- | ||
− | |||
|00:48 | |00:48 | ||
− | + | |You also need the ''' Java Development Kit (JDK)''' version '''6''' | |
− | |You also need | + | |
− | + | ||
− | + | ||
|- | |- | ||
− | |||
|00:54 | |00:54 | ||
− | |and ''' MySQL database''' server | + | |and ''' MySQL database''' server. |
− | + | ||
|- | |- | ||
− | |||
| 00:57 | | 00:57 | ||
− | + | |To learn this tutorial, basic understanding of '''database management''' is necessary. | |
− | |To learn this tutorial, basic understanding of ''' database''' | + | |
− | + | ||
|- | |- | ||
− | |||
|01:03 | |01:03 | ||
− | + | |To know more, watch ''' PHP and MySQL''' spoken tutorials on the '''link''' shown. | |
− | |To know more, watch ''' | + | |
|- | |- | ||
− | |||
|01:10 | |01:10 | ||
− | |||
|Other standard programming terminologies have been used in this tutorial. | |Other standard programming terminologies have been used in this tutorial. | ||
|- | |- | ||
− | |||
|01:16 | |01:16 | ||
− | + | |This tutorial demonstrates how to setup a connection to a '''MySQL database''' from the '''Netbeans IDE.''' | |
− | |This tutorial demonstrates how to setup a connection to a ''' MySQL database''' from the ''' Netbeans IDE.''' | + | |
− | + | ||
|- | |- | ||
− | |||
|01:24 | |01:24 | ||
− | + | |Once connected, we will work with '''MySQL''' in the IDE's '''Database Explorer'''. | |
− | |Once connected, we will work with '''MySQL''' in the ''' | + | |
− | + | ||
|- | |- | ||
− | |||
|01:31 | |01:31 | ||
− | + | |Let us switch to the '''IDE''' now. | |
− | |Let us switch to the ''' IDE''' now. | + | |
|- | |- | ||
− | |||
|01:36 | |01:36 | ||
− | + | | ''' Netbeans IDE''' comes bundled with support for the '''MySQL RDBMS.''' | |
− | | ''' Netbeans IDE''' comes bundled with support for the ''' MySQL RDBMS.''' | + | |
− | + | ||
|- | |- | ||
− | |||
| 01:42 | | 01:42 | ||
− | |||
|Before you access ''' MySQL''' database server in '''Netbeans,''' you must configure the '''MySQL server''' properties. | |Before you access ''' MySQL''' database server in '''Netbeans,''' you must configure the '''MySQL server''' properties. | ||
− | |||
|- | |- | ||
− | |||
| 01:51 | | 01:51 | ||
− | + | |Right-click the '''Databases''' node in the '''Services''' window. | |
− | |Right-click the ''' Databases''' node in the '''Services''' window. | + | |
|- | |- | ||
| 01:56 | | 01:56 | ||
− | |Choose ''' Register MySQL Server''' to open the '''MySQL server properties''' | + | |Choose ''' Register MySQL Server''' to open the '''MySQL server properties''' dialog-box. |
|- | |- | ||
| 02:05 | | 02:05 | ||
− | |Confirm that the | + | |Confirm that the '''Server Host Name''' and the '''Port''' are correct. |
− | + | ||
|- | |- | ||
| 02:10 | | 02:10 | ||
− | |Notice that the '''IDE''' enters | + | |Notice that the '''IDE''' enters "localhost" as the default '''server host name'''. |
|- | |- | ||
| 02:18 | | 02:18 | ||
− | |3306 is the default server port | + | |'''3306''' is the default '''server port Number'''. |
|- | |- | ||
| 02:23 | | 02:23 | ||
− | |Enter the '''Administrator Username''' if not displayed | + | |Enter the '''Administrator Username''', if not displayed. |
|- | |- | ||
|02:27 | |02:27 | ||
− | |On my system, the '''Administrator | + | |On my system, the '''Administrator Username''' is "root". |
|- | |- | ||
|02:33 | |02:33 | ||
− | |Enter the '''Administrator''' | + | |Enter the '''Administrator password'''. |
|- | |- | ||
Line 165: | Line 130: | ||
|- | |- | ||
|02:40 | |02:40 | ||
− | |Click the ''' Admin Properties''' tab at the top of the dialog box. | + | |Click the ''' Admin Properties''' tab at the top of the dialog-box. |
|- | |- | ||
Line 176: | Line 141: | ||
|- | |- | ||
− | |||
|02:56 | |02:56 | ||
− | + | |type or browse to the location of your ''' 'MySQL Administration' application'''. | |
− | |type or browse to the location of your ''' MySQL Administration''' | + | |
− | + | ||
− | + | ||
|- | |- | ||
− | |||
|03:02 | |03:02 | ||
− | + | |On my system, the location to the tool is ''' /usr/bin/mysqladmin'''. | |
− | |On my system, the location to the tool is ''' /usr/bin/mysqladmin''' | + | |
− | + | ||
− | + | ||
|- | |- | ||
− | |||
| 03:12 | | 03:12 | ||
− | |||
|Type any arguments for the admin tool in the '''Arguments''' field. | |Type any arguments for the admin tool in the '''Arguments''' field. | ||
|- | |- | ||
− | |||
| 03:18 | | 03:18 | ||
− | |||
|This can also be left blank. | |This can also be left blank. | ||
|- | |- | ||
− | |||
| 03:22 | | 03:22 | ||
− | + | |In the '''Path to start command:''' field, | |
− | |In the '''Path to start command: ''' field | + | |
|- | |- | ||
− | |||
| 03:25 | | 03:25 | ||
− | + | |type or browse to the location of the ''' MySQL start''' command. | |
− | |type or browse to the location of the ''' MySQL''' | + | |
− | + | ||
− | + | ||
|- | |- | ||
− | |||
| 03:29 | | 03:29 | ||
− | + | |On my system it is: '''/usr/bin/mysqld_safe'''. | |
− | |On my system it is ''' /usr/bin/mysqld_safe''' | + | |
− | + | ||
|- | |- | ||
− | |||
| 03:38 | | 03:38 | ||
− | |Type any arguments for the start command in the '''Arguments''' field. | + | |Type any arguments for the '''start''' command in the '''Arguments''' field. |
− | + | ||
− | + | ||
|- | |- | ||
| 03:42 | | 03:42 | ||
− | | Here, I will type ''' -u space root space start''' | + | | Here, I will type: ''' -u space root space start'''. |
|- | |- | ||
− | |||
| 03:51 | | 03:51 | ||
− | |In the '''Path to stop command: ''' | + | |In the '''Path to stop command:''', |
− | + | ||
|- | |- | ||
− | |||
| 03:54 | | 03:54 | ||
− | + | |type or browse to the location of the '''MySQL stop''' command. | |
− | |type or browse to the location of the '''MySQL''' | + | |
− | + | ||
− | + | ||
|- | |- | ||
− | |||
| 03:58 | | 03:58 | ||
− | + | | This is usually the path to the ''mysqladmin''' in the '''bin''' folder of the '''MySQL''' installation directory. | |
− | | This is usually the path to ''mysqladmin''' in the '''bin''' folder of the '''MySQL''' installation directory. | + | |
|- | |- | ||
− | |||
| 04:06 | | 04:06 | ||
− | + | |On my system, this is:''' /usr/bin/mysqladmin'''. | |
− | |On my system this is ''' /usr/bin/mysqladmin''' | + | |
|- | |- | ||
| 04:14 | | 04:14 | ||
− | |If the command is '''mysqladmin,''' in the '''Arguments''' field, type '''-u space root space stop.''' | + | |If the command is '''mysqladmin,''' in the '''Arguments''' field, type: '''-u space root space stop.''' |
|- | |- | ||
Line 270: | Line 201: | ||
|- | |- | ||
− | |||
| 04:33 | | 04:33 | ||
− | + | |Click '''OK.''' | |
− | |Click '''OK. ''' | + | |
|- | |- | ||
Line 281: | Line 210: | ||
|- | |- | ||
| 04:42 | | 04:42 | ||
− | |The '''MySQL''' | + | |The '''MySQL server''' node in the '''Service''' window, indicates whether the '''MySQL database server''' is connected. |
|- | |- | ||
− | |||
| 04:52 | | 04:52 | ||
− | |||
|After making sure that it is running, right-click the '''Databases >> MySQL server''' node and choose '''Connect. ''' | |After making sure that it is running, right-click the '''Databases >> MySQL server''' node and choose '''Connect. ''' | ||
|- | |- | ||
− | |||
| 05:05 | | 05:05 | ||
− | + | |When expanded, the '''MySQL server''' node displays all the available '''MySQL databases.''' | |
− | |When expanded, the '''MySQL''' | + | |
− | + | ||
|- | |- | ||
− | |||
| 05:13 | | 05:13 | ||
− | + | |A common way of interacting with databases is through an '''SQL Editor. ''' | |
− | |A common way of interacting with | + | |
|- | |- | ||
− | |||
| 05:19 | | 05:19 | ||
+ | | '''Netbeans''' has a built-in '''SQL Editor''' for this purpose. | ||
− | |||
|- | |- | ||
− | |||
| 05:23 | | 05:23 | ||
− | + | |You can access this by right-clicking on the '''connection''' node. | |
− | |You can access this by right-clicking on the connection node. | + | |
|- | |- | ||
− | |||
| 05:29 | | 05:29 | ||
− | + | | Let us now create a new '''database instance''' using the '''SQL Editor'''. | |
− | | Let us now create a new '''database''' | + | |
|- | |- | ||
− | |||
| 05:34 | | 05:34 | ||
− | + | |In the '''Services''' window, right-click the '''MySQL server''' node and choose '''Create Database'''. | |
− | |In the '''Services''' window, right-click the '''MySQL''' | + | |
|- | |- | ||
− | |||
| 05:44 | | 05:44 | ||
− | + | |In the '''Create Database''' dialog, type the name of the new '''database.''' | |
− | |In the '''Create Database | + | |
|- | |- | ||
| 05:50 | | 05:50 | ||
− | |I will name this | + | |I will name this "mynewdatabase". |
− | + | ||
|- | |- | ||
− | |||
| 05:56 | | 05:56 | ||
+ | |You can also grant full '''access''' to a given '''user'''. | ||
− | |||
|- | |- | ||
− | |||
| 06:01 | | 06:01 | ||
− | + | |By default, only the admin user has the '''permission'''s to perform certain '''command'''s. | |
− | |By default, only the admin user has the | + | |
|- | |- | ||
− | |||
| 06:08 | | 06:08 | ||
− | + | |The drop-down list allows you to assign these '''permission'''s to a specified user. | |
− | |The drop-down list allows you to assign these | + | |
|- | |- | ||
− | |||
| 06:13 | | 06:13 | ||
− | + | |It is a good practice to grant users most permissions, except to '''drop''' '''table'''s | |
− | |It is a good practice to grant users most permissions, except to drop | + | |
− | + | ||
|- | |- | ||
− | |||
| 06:18 | | 06:18 | ||
− | + | |and allow users to modify only those databases that are created by their '''application'''. | |
− | | | + | |
− | + | ||
|- | |- | ||
− | |||
| 06:25 | | 06:25 | ||
− | + | |But for now, we will leave the check-box unselected. | |
− | |But for now, we will leave the | + | |
|- | |- | ||
− | |||
| 06:30 | | 06:30 | ||
− | |||
|Click '''OK. ''' | |Click '''OK. ''' | ||
− | |||
|- | |- | ||
| 06:34 | | 06:34 | ||
− | |Let us now create | + | |Let us now create '''table'''s, populate them with data and modify the data maintained in tables. |
+ | |||
|- | |- | ||
− | |||
| 06:41 | | 06:41 | ||
− | + | | '''"mynewdatabase"''' is currently empty. | |
− | | '''mynewdatabase''' is currently empty. | + | |
|- | |- | ||
| 06:44 | | 06:44 | ||
− | |Let us explore the first method to input data in the tables. | + | |Let us explore the first method to '''input''' data in the tables. |
− | + | ||
− | + | ||
|- | |- | ||
− | |||
| 06:48 | | 06:48 | ||
+ | |In the '''Database explorer''', expand the '''mynewdatabase''' connection node. | ||
− | |||
|- | |- | ||
− | |||
| 06:58 | | 06:58 | ||
− | |||
|There are three sub folders: | |There are three sub folders: | ||
|- | |- | ||
− | |||
| 07:00 | | 07:00 | ||
− | |||
|''' Tables, Views''' and '''Procedures. ''' | |''' Tables, Views''' and '''Procedures. ''' | ||
− | |||
− | |||
|- | |- | ||
− | |||
| 07:04 | | 07:04 | ||
− | |||
|Right-click the '''Tables''' folder and choose '''Execute Command. ''' | |Right-click the '''Tables''' folder and choose '''Execute Command. ''' | ||
|- | |- | ||
− | |||
| 07:11 | | 07:11 | ||
+ | |A blank canvas opens in the ''' SQL Editor''', in the main window. | ||
− | |||
|- | |- | ||
− | |||
| 07:16 | | 07:16 | ||
+ | |Let us type a simple query in this ''' SQL editor'''. | ||
− | |||
|- | |- | ||
− | |||
| 07:30 | | 07:30 | ||
− | |||
|I have now typed a simple query in the '''SQL''' editor. | |I have now typed a simple query in the '''SQL''' editor. | ||
− | |||
|- | |- | ||
− | |||
| 07:36 | | 07:36 | ||
− | + | |This is a table definition for the ''' Counselor''' table, we are about to create. | |
− | |This is a table definition for the ''' Counselor''' table we are about to create. | + | |
− | + | ||
− | + | ||
|- | |- | ||
− | |||
| 07:42 | | 07:42 | ||
− | + | |To execute this query, either right-click the '''Run SQL''' icon in the task bar at the top | |
− | |To execute this query, either right-click the '''Run SQL''' icon in the task bar at the top | + | |
− | + | ||
|- | |- | ||
− | |||
| 07:51 | | 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 | | 08:04 | ||
− | + | |You can see this message in the '''Output''' window | |
− | |You can see this message in the '''Output''' window | + | |
|- | |- | ||
| 08:12 | | 08:12 | ||
− | |which says that the command was executed successfully | + | |which says that the command was executed successfully. |
− | + | ||
− | + | ||
|- | |- | ||
| 08:17 | | 08:17 | ||
− | |To verify these changes, right-click the '''Tables''' node in the '''Database''' | + | |To verify these changes, right-click the '''Tables''' node in the '''Database Explorer'''. |
− | + | ||
|- | |- | ||
Line 485: | Line 348: | ||
|Choose '''Refresh.''' | |Choose '''Refresh.''' | ||
|- | |- | ||
− | |||
| 08:28 | | 08:28 | ||
− | + | |This updates the current status of the specified database. | |
− | |This updates the current status of the specified | + | |
|- | |- | ||
− | |||
| 08:32 | | 08:32 | ||
− | |||
|The new '''Counselor''' table now displays under the '''Tables''' option. | |The new '''Counselor''' table now displays under the '''Tables''' option. | ||
− | |||
|- | |- | ||
− | |||
| 08:40 | | 08:40 | ||
− | + | |If you expand the '''Table''' node, you can see the '''column'''s that you created. | |
− | |If you expand the | + | |
|- | |- | ||
− | |||
| 08:46 | | 08:46 | ||
− | |||
|Let us now explore the next method to input data in the tables, | |Let us now explore the next method to input data in the tables, | ||
|- | |- | ||
− | |||
| 08:51 | | 08:51 | ||
− | + | |i.e. using the '''Create Table''' Dialog. | |
− | |i.e. | + | |
|- | |- | ||
− | |||
| 08:54 | | 08:54 | ||
+ | |In the '''Database''' Explorer, right-click the '''Tables''' node and choose '''Create Table. ''' | ||
− | |||
|- | |- | ||
− | |||
| 09:03 | | 09:03 | ||
− | + | |The '''Create Table''' dialog opens. | |
− | |The '''Create Table | + | |
|- | |- | ||
− | |||
| 09:06 | | 09:06 | ||
+ | |In the '''Table name''' text field, type "Subject". | ||
− | |||
|- | |- | ||
− | |||
| 09:13 | | 09:13 | ||
− | + | |Click on '''Add Column '''. | |
− | |Click on '''Add Column ''' | + | |
|- | |- | ||
− | |||
| 09:16 | | 09:16 | ||
+ | |In the '''Add Column''' dialog, type "id" in the '''Name''' field. | ||
− | |||
|- | |- | ||
− | |||
| 09:22 | | 09:22 | ||
− | |||
|Choose '''SMALLINT''' for data-type from the '''Type''' drop-down menu. | |Choose '''SMALLINT''' for data-type from the '''Type''' drop-down menu. | ||
|- | |- | ||
− | |||
| 09:30 | | 09:30 | ||
+ | |Select the '''Primary Key''' check-box in the '''Add Column''' dialog-box. | ||
− | |||
|- | |- | ||
− | |||
| 09:35 | | 09:35 | ||
− | + | |This is to specify the primary-key for your table. | |
− | |This is to specify the primary key for your table. | + | |
|- | |- | ||
− | |||
| 09:39 | | 09:39 | ||
− | + | |Note that when you select the '''Primary Key''' check-box, the '''Index''' and '''Unique''' check-boxes are automatically selected; | |
− | |Note that when you select the '''Key''' check box, the '''Index''' and '''Unique''' check boxes are automatically selected; | + | |
|- | |- | ||
− | |||
| 09:49 | | 09:49 | ||
− | + | |also the '''Null''' check-box is deselected. | |
− | | | + | |
|- | |- | ||
− | |||
| 09:53 | | 09:53 | ||
+ | |This is because primary-keys are used to identify an unique row in the '''database. ''' | ||
− | |||
|- | |- | ||
− | |||
| 09:59 | | 09:59 | ||
− | + | |Click '''OK'''. | |
− | |Click OK. | + | |
|- | |- | ||
− | |||
| 10:03 | | 10:03 | ||
− | |||
|Repeat this procedure to add the remaining columns, as shown on the screen. | |Repeat this procedure to add the remaining columns, as shown on the screen. | ||
|- | |- | ||
− | |||
| 10:09 | | 10:09 | ||
− | |We have now created a table named '''Subject''' that will hold data for '''Name, Description | + | |We have now created a table named '''Subject''' that will hold data for '''Name, Description''' and '''Counselor ID'''. |
|- | |- | ||
− | |||
| 10:20 | | 10:20 | ||
− | |||
|Click '''OK. ''' | |Click '''OK. ''' | ||
|- | |- | ||
− | |||
| 10:23 | | 10:23 | ||
− | + | |By running '''SQL querie'''s on a database, we can add, modify and delete data maintained in '''database structure'''s. | |
− | |By running '''SQL''' | + | |
|- | |- | ||
− | |||
| 10:32 | | 10:32 | ||
− | + | |Let us add a new '''record''' to the '''Counselor''' table. | |
− | |Let us add a new record to the '''Counselor''' table. | + | |
|- | |- | ||
− | |||
| 10:35 | | 10:35 | ||
− | |||
|Choose '''Execute Command''' from the '''Tables''' node context menu. | |Choose '''Execute Command''' from the '''Tables''' node context menu. | ||
|- | |- | ||
− | |||
| 10:43 | | 10:43 | ||
− | + | |A new '''SQL Editor''' opens in the main window. | |
− | |A new SQL Editor opens in the main window. | + | |
− | + | ||
|- | |- | ||
− | |||
| 10:47 | | 10:47 | ||
+ | |In the '''SQL Editor''', let us type a simple '''query'''. | ||
− | |||
|- | |- | ||
− | |||
| 11:00 | | 11:00 | ||
− | + | | To execute this query, right-click within the '''source editor''' and choose '''Run Statement'''. | |
− | | To execute this query, right-click within the source editor | + | |
|- | |- | ||
− | |||
| 11:07 | | 11:07 | ||
− | + | |Let us now verify if the new '''record''' has been added to the '''table'''. | |
− | |Let us now verify if the new record has been added to the table. | + | |
|- | |- | ||
− | |||
| 11:12 | | 11:12 | ||
− | + | |Right-click the '''Counselor''' table and choose '''View Data. ''' | |
− | |Right-click the '''Counselor''' table | + | |
|- | |- | ||
− | |||
| 11:18 | | 11:18 | ||
− | |||
|A new '''SQL Editor''' opens in the main window. | |A new '''SQL Editor''' opens in the main window. | ||
|- | |- | ||
− | |||
| 11:21 | | 11:21 | ||
− | |||
|A query to select all data from the table is automatically generated. | |A query to select all data from the table is automatically generated. | ||
|- | |- | ||
− | |||
| 11:27 | | 11:27 | ||
− | + | |The results of this statement are displayed in a '''table view''' below the '''workspace'''. | |
− | |The results of this statement are displayed in a table view below the workspace. | + | |
|- | |- | ||
− | |||
| 11:41 | | 11:41 | ||
− | |||
|Note that a new row has been added with the data we just supplied. | |Note that a new row has been added with the data we just supplied. | ||
|- | |- | ||
− | |||
| 11:46 | | 11:46 | ||
− | |||
|We can also run an external '''SQL''' script directly in the '''IDE. ''' | |We can also run an external '''SQL''' script directly in the '''IDE. ''' | ||
|- | |- | ||
− | |||
| 11:52 | | 11:52 | ||
− | |||
|I have a '''SQL''' query here for demonstrative purposes. | |I have a '''SQL''' query here for demonstrative purposes. | ||
− | |||
|- | |- | ||
− | |||
| 11:59 | | 11:59 | ||
− | |||
|This script creates two tables similar to the ones we have just created. | |This script creates two tables similar to the ones we have just created. | ||
− | |||
|- | |- | ||
− | |||
| 12:04 | | 12:04 | ||
− | + | |i.e. '''Counselor''' and '''Subject'''. | |
− | |i.e. '''Counselor''' and '''Subject''' | + | |
− | + | ||
|- | |- | ||
− | |||
| 12:09 | | 12:09 | ||
− | |||
|Because the script overwrites these tables, | |Because the script overwrites these tables, | ||
|- | |- | ||
− | |||
| 12:12 | | 12:12 | ||
− | |||
|we will delete these two tables if they already exist. | |we will delete these two tables if they already exist. | ||
− | |||
|- | |- | ||
− | |||
| 12:16 | | 12:16 | ||
− | |||
|To delete tables, right-click on the '''Counselor''' table | |To delete tables, right-click on the '''Counselor''' table | ||
|- | |- | ||
− | |||
| 12:21 | | 12:21 | ||
− | |||
|and choose '''Delete.''' | |and choose '''Delete.''' | ||
− | |||
|- | |- | ||
− | |||
| 12:24 | | 12:24 | ||
− | + | |Click '''Yes''' in the '''Confirm Object Deletion''' dialog-box. | |
− | |Click '''Yes''' in the '''Confirm Object Deletion''' | + | |
|- | |- | ||
− | |||
| 12:31 | | 12:31 | ||
− | + | |Repeat the same for the '''Subject''' table. | |
− | |Repeat the same for the '''Subject''' table | + | |
|- | |- | ||
− | |||
| 12:38 | | 12:38 | ||
− | |||
|Now, open the existing '''SQL''' query file from your system. | |Now, open the existing '''SQL''' query file from your system. | ||
|- | |- | ||
− | |||
| 12:43 | | 12:43 | ||
− | + | |From the '''File''' menu, choose '''Open File.''' | |
− | |From the '''File ''' menu, choose '''Open File.''' | + | |
|- | |- | ||
− | |||
| 12:48 | | 12:48 | ||
− | |||
|Browse to the location containing this file. | |Browse to the location containing this file. | ||
|- | |- | ||
− | |||
| 12:54 | | 12:54 | ||
− | + | |The script automatically opens in the '''SQL editor'''. | |
− | |The script automatically opens in the '''SQL''' | + | |
|- | |- | ||
− | |||
| 12:59 | | 12:59 | ||
− | |||
|Make sure the connection to '''mynewdatabase''' is selected. | |Make sure the connection to '''mynewdatabase''' is selected. | ||
|- | |- | ||
− | |||
| 13:03 | | 13:03 | ||
− | + | |Check this from the '''C onnection''' drop-down in the toolbar, at the top of the editor. | |
− | |Check this from the | + | |
|- | |- | ||
− | |||
| 13:13 | | 13:13 | ||
− | |||
|Click the '''Run SQL''' button in the task bar. | |Click the '''Run SQL''' button in the task bar. | ||
|- | |- | ||
− | |||
| 13:17 | | 13:17 | ||
− | + | |And the script is executed against the selected database. | |
− | |And the script is executed against the selected | + | |
|- | |- | ||
− | |||
| 13:22 | | 13:22 | ||
− | |||
|Right-click the '''mynewdatabase''' connection node and choose '''Refresh. ''' | |Right-click the '''mynewdatabase''' connection node and choose '''Refresh. ''' | ||
|- | |- | ||
− | |||
| 13:28 | | 13:28 | ||
− | + | |This updates the database component to the current status of the specified database. | |
− | |This updates the | + | |
|- | |- | ||
− | |||
| 13:34 | | 13:34 | ||
− | |||
|Right-click on any of these tables now and choose '''View Data.''' | |Right-click on any of these tables now and choose '''View Data.''' | ||
|- | |- | ||
− | |||
| 13:41 | | 13:41 | ||
− | + | |And, below the '''workspace''', you can see the data contained in the new tables. | |
− | |And below the workspace, you can see the data contained in the new tables. | + | |
|- | |- | ||
− | |||
| 13:52 | | 13:52 | ||
− | + | |In this tutorial, you learnt to- | |
− | |In this tutorial you learnt to | + | |
|- | |- | ||
− | |||
| 13:54 | | 13:54 | ||
− | |||
|configure '''MySQL''' on your computer | |configure '''MySQL''' on your computer | ||
|- | |- | ||
− | |||
| 13:57 | | 13:57 | ||
− | + | |set up a connection to the '''database server''' from the '''IDE ''' | |
− | |set up a connection to the '''database ''' | + | |
|- | |- | ||
− | |||
| 14:02 | | 14:02 | ||
− | |||
|create, delete, modify data and | |create, delete, modify data and | ||
|- | |- | ||
− | |||
| 14:06 | | 14:06 | ||
− | + | |'''run''' SQL queries. | |
− | | | + | |
|- | |- | ||
− | |||
| 14:10 | | 14:10 | ||
− | + | |As an assignment,create another database instance with tables. | |
− | |As an assignment, | + | |
|- | |- | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
| 14:15 | | 14:15 | ||
− | + | |Populate these tables with necessary data to maintain your personal book library. | |
− | |Populate these tables with necessary data to maintain your personal book library | + | |
− | + | ||
|- | |- | ||
− | |||
| 14:21 | | 14:21 | ||
− | + | |And, '''run''' these SQL statements to view '''data'''. | |
− | |And run these SQL statements to view data | + | |
|- | |- | ||
− | |||
| 14:29 | | 14:29 | ||
− | + | |I have created a similar database which maintains details of my personal movie library. | |
− | |I have created a similar | + | |
|- | |- | ||
− | |||
| 14:37 | | 14:37 | ||
− | |||
|Your assignment should resemble this. | |Your assignment should resemble this. | ||
− | |||
|- | |- | ||
− | |||
| 14:44 | | 14:44 | ||
− | |||
|Watch the video available at the link shown on the screen. | |Watch the video available at the link shown on the screen. | ||
|- | |- | ||
− | |||
| 14:48 | | 14:48 | ||
− | |||
|It summarizes the Spoken Tutorial project. | |It summarizes the Spoken Tutorial project. | ||
|- | |- | ||
− | |||
| 14:51 | | 14:51 | ||
− | |||
|If you do not have good bandwidth, you can download and watch it. | |If you do not have good bandwidth, you can download and watch it. | ||
|- | |- | ||
− | |||
| 14:56 | | 14:56 | ||
− | + | |The Spoken Tutorial project team: * conducts workshops using Spoken Tutorials. | |
− | |The Spoken Tutorial project team | + | |
− | + | ||
|- | |- | ||
− | |||
| 15:01 | | 15:01 | ||
− | |||
|Gives certificates to those who pass an online test. | |Gives certificates to those who pass an online test. | ||
− | |||
|- | |- | ||
− | |||
| 15:04 | | 15:04 | ||
− | + | |For more details, please write to: | |
− | |For more details please write to contact@spoken-tutorial.org | + | '''contact@spoken-tutorial.org''' |
− | + | ||
|- | |- | ||
− | |||
| 15:10 | | 15:10 | ||
− | + | |'''Spoken Tutorial''' project is a part of the '''Talk to a Teacher''' project. | |
− | |Spoken Tutorial | + | |
|- | |- | ||
− | |||
| 15:15 | | 15:15 | ||
− | + | | It is supported by the National Mission on education through ICT, MHRD, Government of India. | |
− | | It is | + | |
|- | |- | ||
− | |||
| 15:20 | | 15:20 | ||
− | + | |More information on this mission is available at the link provided here. | |
− | |More information on this mission is available at link provided here | + | |
|- | |- | ||
− | |||
| 15:27 | | 15:27 | ||
− | + | |This tutorial has been contributed by '''IT for Change'''. | |
− | |This tutorial has been contributed by | + | |
− | + | ||
|- | |- | ||
− | |||
| 15:30 | | 15:30 | ||
− | + | |Thank you. | |
− | |Thank you | + |
Latest revision as of 13:37, 28 October 2020
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 dialog 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 dialog-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 dialog, 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 dialog opens. |
09:06 | In the Table name text field, type "Subject". |
09:13 | Click on Add Column . |
09:16 | In the Add Column dialog, 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 dialog-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. |