Difference between revisions of "Netbeans/C3/Connecting-to-a-MySQL-Database/English-timed"

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

Contributors and Content Editors

PoojaMoolya, Pratik kamble, Sandhya.np14