Netbeans/C3/Connecting-to-a-MySQL-Database/English-timed

From Script | Spoken-Tutorial
Jump to: navigation, search
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

Contributors and Content Editors

PoojaMoolya, Pratik kamble, Sandhya.np14