MySQL Developing And Programming Blog

Introducing MySQL SQL 51 Using SHOW MySQL includes a command called SHOW, which allows a developer or administrator to see details about databases, tables, and the database system itself. In this section, we look at the various SHOW commands and explain what information they provide. Note that in some of the commands an optional LIKE can be used to filter the information provided by the command. The % wildcard is used just like as you do in the SELECT command use of LIKE. We cover the most popular SHOW commands here and save some of them for Chapter 13, Database Administration. SHOW DATABASES The SHOW DATABASES command shows all of the databases available on the current database server. You use the LIKE command to limit the output. For example: mysql> show databases; +———-+ | Database | +———-+ | accounts | | ca | | mysql | | test | +———-+ 4 rows in set (0.03 sec) SHOW TABLES The SHOW TABLES command displays all of the tables within a particular database. The full format of the command is: SHOW [OPEN] TABLES [FROM databaseName] [LIKE wildcardString] Notice that there are a number of optional components to the command. The [OPEN] option will show only those databases that are currently being accessed by a client. If SHOW TABLES is executed, it requires that a database currently be active by executing the USE command. You can use the FROM databaseName option to query the tables available in any database on the system. For example: mysql> SHOW TABLES FROM mysql; +—————–+ | Tables_in_mysql | +—————–+ | columns_priv | | db | | func | | host | | tables_priv | | user | +—————–+ 6 rows in set (0.03 sec)
Note: If you are looking for inexpensive but high quality provider to host and run your serlvet application check Astra servlet hosting services

Working with MySQL SQL 50 mysql> SELECT * FROM acc WHERE acc_id = ‘1034055′; +——–+——–+———-+—————-+—————+ |acc_id |username| password | ts | act_ts | +——–+——–+———-+—————-+—————+ |1034055 | jdoe | ime | 20021014212553 | 20021014212444| +——–+——–+———-+—————-+—————+ 1 row in set (0.01 sec) Now we can insert the new row: mysql> INSERT INTO acc VALUES(1034055, ‘jdoe’, ‘newpass’, 0, @time); Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 A final SELECT will show both of the rows and how they relate through the act_ts column of the new row and the ts of the old row: mysql> SELECT * FROM acc WHERE acc_id = ‘1034055′; +——-+——–+———-+—————-+—————-+ |acc_id |username| password | ts | act_ts | +——-+——–+———-+—————-+—————-+ |1034055| jdoe | newpass | 00000000000000 | 20021014212553 | |1034055| jdoe | ime | 20021014212553 | 20021014212444 | +——-+——–+———-+—————-+—————-+ 2 rows in set (0.01 sec) We can always know the active row by including ts=0 in our queries. Deletes When data is no longer needed in a database, you can use the DELETE command to remove a row. However, if you want to maintain the history of the rows in the database, you should instead make the row inactive. First, let s show the removal of a row. The query looks like this: DELETE FROM acc WHERE acc_id = ‘1034154′; The query will select the appropriate row based on the WHERE clause. Another use of the DELETE command is: DELETE FROM acc; This query doesn t include a WHERE clause and thus will remove all rows from the specified database table. To maintain the history of the rows in the database, you shouldn t use the DELETE command because the row will be permanently removed. In that case, the best way to delete the row is to make the row inactive by setting the ts of the row to a timestamp other than 0. In most cases, you want to update the current row to a current timestamp value so that the row has a record of when it was made inactive.
Note: If you are looking for top 10 and very good webhost to host and run your jsp application check Actions jsp hosting services

Introducing MySQL SQL 49 To support this type of update, we ve changed the table acc a bit. The new table definition looks like this: mysql> describe acc; +———-+—————+——+—–+———+——-+ | Field | Type | NULL | Key | Default | Extra | +———-+—————+——+—–+———+——-+ | acc_id | int(11) | | PRI | 0 | | | username | varchar(64) | | PRI | | | | password | varchar(64) | YES | | NULL | | | ts | timestamp(14) | YES | PRI | NULL | | | act_ts | timestamp(14) | YES | | NULL | | +———-+—————+——+—–+———+——-+ 5 rows in set (0.00 sec) As you can see, we ve added an act_ts column defined as a timestamp; defined the username, acc_id, and ts not to be NULL; and defined the primary key as a combination of acc_id, username, and ts. To show the process of doing the update, consider the row with an acc_id of 1034055. When the initial row was placed in the database, the ts column was set to 0, and the act_ts was set to the actual time the row was inserted. Here s the output of a SELECT showing the row: mysql> SELECT * FROM acc WHERE acc_id = ‘1034055′; +——+——–+———-+—————-+————+ | acc_id |username| password | ts | act_ts | +——+——–+———-+—————-+————+ |1034055 | jdoe | ime | 00000000000000 |20021014212444| +——–+——–+———-+—————-+————–+ 1 row in set (0.00 sec) Next, we need to insert a new row into the database. In order for the database to remain consistent, we need to relate the old row to the new row using a timestamp. The timestamp needs to be the same, so the first step is to obtain the current time and place it in a temporary variable. We accomplish this by using a SET command and local system variable. For example: mysql> set @time=now(); Query OK, 0 rows affected (0.03 sec) The @time variable now holds a timestamp, and it can be used to insert the new row and change the old row. First, the old row is updated and the ts column is set to the current time: mysql> UPDATE acc SET ts=@time WHERE acc_id = 1034055; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Here s the query to show the new ts value in the old row:
Quick Hint: If you are looking for best quality webspace to host and run your tomcat application check Vision tomcat hosting services

Working with MySQL SQL 48 UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2, …] [WHERE where_definition] [LIMIT #] If you have a user who changes his or her password, you can use the UPDATE command to make the change in the database. Consider the following SELECT, UPDATE, SELECT combination: mysql> SELECT * FROM acc WHERE username=’jime’; +———+———-+———-+—————-+ | acc_id | username | password | ts | +———+———-+———-+—————-+ | 1034034 | jime | NULL | 20021014165845 | +———+———-+———-+—————-+ 1 row in set (0.00 sec) mysql> UPDATE acc SET password=’ime’ WHERE username=’jime’; Query OK, 1 rows affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM acc WHERE username=’jime’; +———+———-+———-+—————-+ | acc_id | username | password | ts | +———+———-+———-+—————-+ | 1034034 | jime | ime | 20021014204947 | +———+———-+———-+—————-+ 1 row in set (0.00 sec) In this combination of SQL commands, we display the row where the username is jime. The password is shown to be NULL. We use the UPDATE command to change the password to ime. Notice that the UPDATE command instructs a specific table to be updated; then the column that needs to be changed is indicated by SET. If we have to change numerous columns, we can use multiple SETs and separate them by commas. Finally, we can use a condition to limit the rows changed. The last SELECT command shows that the row was updated correctly. The second way to update a database is to never change a row in the database but instead to inactivate one row and insert a new one. In order to do this type of update, you must include two timestamp fields in each row. The first is called an active timestamp, and the second is just the timestamp. The most active row in the database for a particular key has a timestamp of 0. The active timestamp will be the time when the row was inserted. Once the row is inserted, the active timestamp of the current row is copied to the timestamp (ts field) of the inactive row.

Hint: This post is supported by Gama web hosting php services

Introducing MySQL SQL 47 mysql> SELECT * FROM acc INTO OUTFILE ‘test.outfile’; Query OK, 8 rows affected (0.00 sec) This query results in a text file with all of the rows, as shown here: 1034033 jsmith smithy 20021014165845 1034034 jime N 20021014165845 1034067 jthompson james2 20021014165845 1034089 sstanford stanford 20021014165845 1034123 blewis lewis 20021014165845 1034154 ysheets sheets 20021014165845 1034546 jjmyers N 20021014165845 1034055 jdoe doey 20021014165908 Notice that the NULL values are converted to N and line terminations are provided. If you need the data sorted, you can add the appropriate clauses to the query. MySQL also includes the clause INTO DUMPFILE, which basically dumps a single row into a file without any sort of special processing. The DUMPFILE is typically used to output a BLOB to a file. Our sample database doesn t include a BLOB, but the query might look like the following: SELECT pic_blob FROM images INTO DUMPFILE ‘world.jpg’ WHERE pic_name = ‘World’; Counting If you consider the various SELECT queries we ve created in this section, you will note that they all output some number of result rows. What if we want a query that counts the total number of rows in a result? The total count can be returned using the count(*) option. For example: mysql> SELECT count(*) FROM acc; +———-+ | count(*) | +———-+ | 8 | +———-+ 1 row in set (0.00 sec) Here we execute a SELECT to return the total number of rows in the acc table. Notice that the count value is returned as a column in the result. The column heading value can be changed, as we explained earlier in this section. Updates The first major SQL statement we covered was INSERT, which you use to place data into your database. This was followed by the SELECT statement, which you use to pull the data from your database. What do you do if you want to change the data within a row? You have two options. The first is to just make the change. You can do this with the UPDATE command:

Hint: If you are looking for very good and affordable webspace to host and run your tomcat hosting application check Sandzak.com tomcat web hosting provider