Difference between revisions of "Netbeans/C3/Connecting-to-a-MySQL-Database/English-timed"
From Script | Spoken-Tutorial
Sandhya.np14 (Talk | contribs) |
PoojaMoolya (Talk | contribs) |
||
Line 38: | Line 38: | ||
|- | |- | ||
| 00:29 | | 00:29 | ||
− | |using the '''create table''' | + | |using the '''create table''' dialog and finally, |
|- | |- | ||
Line 98: | Line 98: | ||
|- | |- | ||
| 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. |
|- | |- | ||
Line 242: | Line 242: | ||
|- | |- | ||
| 05:44 | | 05:44 | ||
− | |In the '''Create Database''' | + | |In the '''Create Database''' dialog, type the name of the new '''database.''' |
|- | |- | ||
Line 373: | Line 373: | ||
|- | |- | ||
| 09:03 | | 09:03 | ||
− | |The '''Create Table''' | + | |The '''Create Table''' dialog opens. |
|- | |- | ||
Line 385: | Line 385: | ||
|- | |- | ||
| 09:16 | | 09:16 | ||
− | |In the '''Add Column''' | + | |In the '''Add Column''' dialog, type "id" in the '''Name''' field. |
|- | |- | ||
Line 509: | Line 509: | ||
|- | |- | ||
| 12:24 | | 12:24 | ||
− | |Click '''Yes''' in the '''Confirm Object Deletion''' | + | |Click '''Yes''' in the '''Confirm Object Deletion''' dialog-box. |
|- | |- |
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. |