MySQL Developing And Programming Blog

Working with MySQL SQL 46 Most dialects of GROUP BY require that the fields used in the clause be part of the SELECT itself. MySQL allows columns to be in the SELECT that aren t part of the GROUP BY. Limit In all the queries so far, all of the rows in the result are returned. There are times when you might want only a single row or a small set when there are many possible result rows. In such cases, you can limit the row count by using the LIMIT clause. For example: mysql> SELECT * FROM acc LIMIT 3; +———+———–+———-+—————-+ | acc_id | username | password | ts | +———+———–+———-+—————-+ | 1034033 | jsmith | smithy | 20021014165845 | | 1034034 | jime | NULL | 20021014165845 | | 1034067 | jthompson | james2 | 20021014165845 | +———+———–+———-+—————-+ 3 rows in set (0.00 sec) In this query example, the first three rows of the result are returned. We can execute the query again and pull another three rows, but instead of starting at the first row in the result, we use an offset value to get the next three rows. For example: mysql> SELECT * FROM acc LIMIT 3,3; +———+———–+———-+—————-+ | acc_id | username | password | ts | +———+———–+———-+—————-+ | 1034089 | sstanford | stanford | 20021014165845 | | 1034123 | blewis | lewis | 20021014165845 | | 1034154 | ysheets | sheets | 20021014165845 | +———+———–+———-+—————-+ 3 rows in set (0.00 sec) In this query, the code offsets to the fourth row and displays three of the results. If there aren t enough rows remaining in the result set, the system returns as many as it can. Dump to File Not all applications are able to use the output from a SQL query, but they are able to handle input in the form of a text file. The SELECT command in MySQL includes a clause called INTO [OUTFILE | DUMPFILE] that allows the result of a query to be placed in a file. As listed, there are two options for the INTO clause: OUTFILE and DUMPFILE. The OUTFILE option is used to dump all rows returned in a query. For example:
Hint: If you are looking for high quality webhost to host and run your jsp application check Vision web hosting jsp services

Introducing MySQL SQL 45 Fortunately, SQL has a SELECT clause called LIKE that lets you basically search the database for a substring within a column. The LIKE clause requires you to insert a wildcard character, %, into the string you are trying to locate. For example: mysql> SELECT acc_id ‘Account ID’, username FROM acc WHERE username LIKE ‘%smith ‘; +————+———-+ | Account ID | username | +————+———-+ | 1034033 | jsmith | +————+———-+ 1 row in set (0.00 sec) In this query, we ve asked for the account ID and username of all users with a username that begins with any string and ends with smith. The wildcard can be used in multiple places throughout the string. Let s say you need to find all user- names containing stan. Use the following query: mysql> SELECT acc_id ‘Account ID’, username FROM acc WHERE username LIKE ‘%stan%’; +————+———–+ | Account ID | username | +————+———–+ | 1034089 | sstanford | +————+———–+ 1 row in set (0.00 sec) To achieve your intended outcome, place the % wildcard at both the beginning and end of the stan string. Note that the more wildcard-matching the database system needs to do, the longer the system will take to return the result. Group By One of the things you should notice from the ORDER BY clause is it cannot be used to sort by multiple columns. MySQL includes another clause, called GROUP BY, that can be used to group together common values within multiple columns. For example, suppose you want to group on both the account number and username. The query is as follows: SELECT * FROM acc GROUP BY acc_id, username; MySQL has extended GROUP BY to allow the use of the ASC and DESC descriptors for sorting in a particular order. For example: SELECT * FROM acc GROUP BY acc_id DESC, username ASC;
Hint: If you are looking for good and high quality web space to host and run your java application check Vision java web hosting services

Working with MySQL SQL 44 | blewis | 20021014112252 | +———–+—————-+ 8 rows in set (0.00 sec) This query returns the username and timestamp for all rows in the table in descending order, thus displaying the accounts most recently entered. Changing Column Names If you look back at the previous query, you can see that the output table heading displays the string values for the columns in the table as entered when the table was first created. When we obtain the results of a query both in the client tool and programmatically, the same column names are used. We have the option of changing the displayed values. For example: mysql> SELECT acc_id ‘Account ID’, username ‘Username’, ts ‘Timestamp’ FROM acc WHERE ts < now() ORDER BY ts desc; +------------+-----------+----------------+ | Account ID | Username | Timestamp | +------------+-----------+----------------+ | 1034546 | jjmyers | 20021014113422 | | 1034154 | ysheets | 20021014113416 | | 1034089 | sstanford | 20021014113407 | | 1034067 | jthompson | 20021014113403 | | 1034055 | jdoe | 20021014112501 | | 1034033 | jsmith | 20021014112438 | | 1034034 | jime | 20021014112415 | | 1034123 | blewis | 20021014112252 | +------------+-----------+----------------+ 8 rows in set (0.00 sec) In this sample query, the three columns pulled from the table aren t displayed with their table names of acc_id, username, and ts, but new names are listed in the query. Although the column name change doesn t have anything to do with the data itself, it does provide a better presentation to the user. Like Another common problem with queries against a database is trying to find the exact row you are interested in using. For example, suppose you know that there is an account in the database table acc with a username ending with smith, but you don t know exactly what the full string is. If you attempt to query just using smith, you might find rows with usernames of smith but nothing else.
Note: If you are looking for good and quality webspace to host and run your java application check professional java hosting services

Introducing MySQL SQL 43 As you can see in the output from the query, the data is displayed in alphabetical order based on the username. You can also sort based on a numeric column: mysql> SELECT * FROM acc ORDER BY acc_id; +———+———–+———-+—————-+ | acc_id | username | password | ts | +———+———–+———-+—————-+ | 1034033 | jsmith | smithy | 20021014112438 | | 1034034 | jime | NULL | 20021014112415 | | 1034055 | jdoe | doey | 20021014112501 | | 1034067 | jthompson | james2 | 20021014113403 | | 1034089 | sstanford | stanford | 20021014113407 | | 1034123 | blewis | lewis | 20021014112252 | | 1034154 | ysheets | sheets | 20021014113416 | | 1034546 | jjmyers | NULL | 20021014113422 | +———+———–+———-+—————-+ 8 rows in set (0.00 sec) Now the records are ordered based on the acc_id, which is an integer. The ORDER BY clause can also be used with the WHERE clause. For example: mysql> SELECT * FROM acc WHERE ts < now() ORDER BY ts; +---------+-----------+----------+----------------+ | acc_id | username | password | ts | +---------+-----------+----------+----------------+ | 1034123 | blewis | lewis | 20021014112252 | | 1034034 | jime | NULL | 20021014112415 | | 1034033 | jsmith | smithy | 20021014112438 | | 1034055 | jdoe | doey | 20021014112501 | | 1034067 | jthompson | james2 | 20021014113403 | | 1034089 | sstanford | stanford | 20021014113407 | | 1034154 | ysheets | sheets | 20021014113416 | | 1034546 | jjmyers | NULL | 20021014113422 | +---------+-----------+----------+----------------+ 8 rows in set (0.00 sec) As you might have noticed, the default ordering used by ORDER BY is ascending order. You can change this by adding the string desc to the end of the clause. For example: mysql> SELECT username, ts FROM acc WHERE ts < now() ORDER BY ts desc; +-----------+----------------+ | username | ts | +-----------+----------------+ | jjmyers | 20021014113422 | | ysheets | 20021014113416 | | sstanford | 20021014113407 | | jthompson | 20021014113403 | | jdoe | 20021014112501 | | jsmith | 20021014112438 | | jime | 20021014112415 |
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 42 In this query, the system selects all of the rows in the acc table where a value is NULL, and the username value is jime. SELECT Statement Extensions Up to this point, we have been showing simple SELECT commands both with and without conditions. The SELECT command has a whole list of extensions that can be used to further filter and manipulate the data received from the database. MySQL s SELECT includes the following extensions: SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL] select_expression,… [INTO {OUTFILE | DUMPFILE} ‘file_name’ export_options] [FROM table_references [WHERE where_definition] [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], … [HAVING where_definition] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,…] [LIMIT [offset,] rows] [PROCEDURE procedure_name] [FOR UPDATE | LOCK IN SHARE MODE]] Let s look at a few of the additions to the SELECT command. Order By When we pulled data from the database table in the query examples earlier, MySQL returned the data in the same order it was placed in the table. For the most part, this works just fine because we just want to get the data out of the database. At other times, it might be important that the data be ordered in some specific fashion. For example, suppose you want to sort the data in ascending order (the default) based on the username: mysql> SELECT * FROM acc ORDER BY username; +———+———–+———-+—————-+ | acc_id | username | password | ts | +———+———–+———-+—————-+ | 1034123 | blewis | lewis | 20021014112252 | | 1034055 | jdoe | doey | 20021014112501 | | 1034034 | jime | NULL | 20021014112415 | | 1034546 | jjmyers | NULL | 20021014113422 | | 1034033 | jsmith | smithy | 20021014112438 | | 1034067 | jthompson | james2 | 20021014113403 | | 1034089 | sstanford | stanford | 20021014113407 | | 1034154 | ysheets | sheets | 20021014113416 | +———+———–+———-+—————-+ 8 rows in set (0.00 sec)
Note: If you are looking for cheapest and affordable webspace to host and run your servlet application check Astra j2ee hosting services

Introducing MySQL SQL 41 This SELECT command tells the database to pull all columns, using the * character, from the acc table. The database responds with a table using a heading with the column names found in the database used when we first defined the table. Next, all of the data from the table is placed in the output table and displayed accordingly. Now we can limit the columns of data with our SELECT: mysql> SELECT acc_id, username FROM acc; +———+———–+ | acc_id | username | +———+———–+ | 1034033 | jsmith | | 1034055 | jdoe | | 1034067 | jthompson | | 1034089 | sstanford | | 1034123 | blewis | | 1034154 | ysheets | | 1034034 | jime | | 1034546 | jjmyers | +———+———–+ 8 rows in set (0.00 sec) In this example, we have specifically listed the columns we wish to pull data from and at the same time requested all of the data. The system will output the data in the familiar table format. The same query will be used, but a condition is placed on the data we wish to pull. mysql> SELECT acc_id, username FROM acc WHERE username = ‘jime’; +———+———-+ | acc_id | username | +———+———-+ | 1034034 | jime | +———+———-+ 1 row in set (0.00 sec) The same query is used here, but a WHERE clause limits the data to be pulled based on the actual value found in the username field. The condition with the SELECT query can hold logical operators to further refine the selection criteria. For example: mysql> SELECT * FROM acc WHERE password IS NULL AND username = ‘jime’; +———+———-+———-+—————-+ | acc_id | username | password | ts | +———+———-+———-+—————-+ | 1034034 | jime | NULL | 20021014112415 | +———+———-+———-+—————-+ 1 row in set (0.00 sec)

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

Working with MySQL SQL 40 Let s examine the INSERT command a little more closely. First consider the order of the data. The order must match the columns defined in the table as shown by the DESCRIBE command. The second important factor is the use of single quotes to indicate a value is a string and should be treated as such by MySQL. If you didn t want to insert a password into a row, you could use a NULL value. For example: mysql> INSERT INTO acc VALUES(1034034, ‘jime’, NULL, now()); Query OK, 1 row affected (0.00 sec) In this example, the NULL value is placed directly into the database in place of a string value. Selects Once you ve inserted your data into a database, you can extract that data to make business decisions. You pull data from the database by using the SELECT command, which has the following format: SELECT FROM WHERE The SELECT command has three different components at its core. The first is the element, which tells the database the columns where values should be returned. The element can be * (representing all columns) or a list of columns separated by commas. The second component is the element, which represents the exact table from which the data should come. The third component is the element, which represents under what conditions the data should be pulled from the database. First, we pull data using the simplest SELECT: mysql> select * from acc; +———+———–+———-+—————-+ | acc_id | username | password | ts | +———+———–+———-+—————-+ | 1034033 | jsmith | smithy | 20021014112438 | | 1034055 | jdoe | doey | 20021014112501 | | 1034067 | jthompson | james2 | 20021014113403 | | 1034089 | sstanford | stanford | 20021014113407 | | 1034123 | blewis | lewis | 20021014112252 | | 1034154 | ysheets | sheets | 20021014113416 | | 1034034 | jime | NULL | 20021014112415 | | 1034546 | jjmyers | NULL | 20021014113422 | +———+———–+———-+—————-+ 8 rows in set (0.00 sec)
Hint: If you are looking for high quality webhost to host and run your jsp application check Vision web hosting jsp services

Introducing MySQL SQL 39 The SHOW TABLES command lists all of the available tables within a given database. To verify that the table was created successfully and to view the various columns, execute the following command: mysql> describe acc; +———-+—————+——+—–+———+——-+ | Field | Type | NULL | Key | Default | Extra | +———-+—————+——+—–+———+——-+ | acc_id | int(11) | | PRI | 0 | | | username | varchar(64) | YES | | NULL | | | password | varchar(64) | YES | | NULL | | | ts | timestamp(14) | YES | | NULL | | +———-+—————+——+—–+———+——-+ 4 rows in set (0.00 sec) You can view the columns and their definitions within a table by issuing the DESCRIBE

command. If you discover a problem with any definition, you can use the ALTER TABLE command. For our example, we are able to verify that the information was created successfully. Inserts With our database and table defined, we need to populate it with sample data. Here s the data that we would like to get into the table: acc_id username password 1034033 jsmith smithy 1034055 jdoe doey 1034067 jthompson james2 1034089 sstanford stanford 1034123 blewis lewis 1034154 ysheets sheets We can place the data in the database table by using the INSERT command. The format of the MySQL INSERT command is: INSERT INTO
VALUES(,, ) We have to issue three INSERT commands to get all of the information into the database. Here s the output from one INSERT: mysql> INSERT INTO acc VALUES(1034033, ‘jsmith’, ’smithy’, now()); Query OK, 1 row affected (0.00 sec) Two more INSERT commands and all of our sample data is in the table. MySQL also includes a command called LOAD DATA, which populates a database table from a properly formatted text file.

Note: If you are looking for good and affordable webspace to host and run your servlet application check Sandzak servlet hosting services

Posted in developing | No Comments »

Working with MySQL SQL 38 We have to (Java web hosting)

Friday, December 15th, 2006

Working with MySQL SQL 38 We have to pick one of these data types for each of the pieces of data. Clearly, the username and password will be some number of characters. The question is whether we should use the CHAR or VARCHAR data type to represent the characters. The CHAR data type should be used if the character string will be a specific length and never change. In the case of a username and password, this is not the case. The user will be allowed to pick his or her username and password. This means we should use the VARCHAR data type for our character strings. Next, we need to determine the total number of characters that will be allowed in each of the strings. A value of 64 is more than likely enough. Finally, our attention turns to the account ID. Should the account ID be saved as an integer whole number or as a character string? If there is ever a chance the account ID will include alpha characters, then the ID should be a character string. With an integer, there are a few different types that can be used based on the potential size of the ID. For our example, let s use an INT data type for the field. Another characteristic that we want to place on the account ID is a primary key. A primary key basically states that the value in this column will be unique and thus can be used to uniquely identify any specific row in the table. Once we have identified all of the fields and assigned each a type, we can create the table. To create a nontransactional table, use this command: mysql> create table acc ( acc_id int primary key, username varchar(64), password varchar(64), ts timestamp); Query OK, 0 rows affected (0.00 sec) To create a table that will handle transactions, use this command: mysql> create table acc ( acc_id int primary key, username varchar(64), password varchar(64), ts timestamp) type=bdb; Query OK, 0 rows affected (0.01 sec) We can see all of the tables in our database with the following command: mysql> show tables; +——————–+ | Tables_in_accounts | +——————–+ | acc | +——————–+ 1 row in set (0.00 sec)
Note: If you are looking for reliable and quality webspace company to host and run your servlet application check professional servlet hosting services

Posted in developing | No Comments »

Introducing MySQL SQL 37 ENUM Selects one from at (Web hosting jsp)

Thursday, December 14th, 2006

Introducing MySQL SQL 37 ENUM Selects one from at most 65,535 character strings, represented as 1 or 2 bytes. SET Combines at most 255 character strings, represented as 1 8. TINYINT 8-bit integer represented as 1 byte SMALLINT 16-bit integer represented as 2 bytes MEDIUMINT 24-bit integer represented as 3 bytes INT, INTEGER 32-bit integer represented as 4 bytes BIGINT 64-bit integer represented as 8 bytes FLOAT floating-point number, 8-place precision represented as 4 bytes DOUBLE floating-point number, 16-place precision represented as 8 bytes DECIMAL(p, s) fixed-point number, saved as a character string; arbitrary number of digits represented as one byte per digit + 2 bytes overhead DATE date in the form ‘2001-12-31′, range 1000-01-01 to 9999-12-31 represented as 3 bytes TIME time in the form ‘23:59:59′ represented as 3 bytes DATETIME combination of DATE and TIME in the form ‘2002-10-05 23:59:59′ represented as 8 bytes YEAR year 1900 2155 represented as 1 byte TIMESTAMP date and time in the form 20011231325959 for times between 1970 and 2038 represented as 4 bytes CHAR(n) character string with specified length, maximum 255 characters represented as n bytes VARCHAR(n) character string with variable length, maximum n characters (n < 256) represented as one byte per character (actual length) + 1 TINYTEXT character string with variable length, maximum 255 characters represented as n + 1bytes TEXT character string with variable length, maximum 216 -1 characters represented as n + 2bytes MEDIUMTEXT character string with variable length, maximum 224 -1 characters represented as n + 3bytes LONGTEXT character string with variable length, maximum 232 -1 characters represented as n + 4bytes TINYBLOB binary data, variable length, max 255 bytes BLOB binary data, variable length, max 216 -1 bytes MEDIUMBLOB binary data, variable length,max 224 -1 bytes LONGBLOB binary data, variable length,max 232 -1 bytes ENUM select one from at most 65,535 character strings represented as 1 or 2 bytes SET combine atmost 255 character strings represented as 1 8 bytes

Note: If you are looking for good and affordable webspace to host and run your servlet application check Sandzak servlet hosting services

Posted in developing | No Comments »

« Previous Entries

Next Entries »

• You are currently browsing the MySQL Developing And Programming Blog weblog archives for December, 2006.

• Pages

  • About Me

• Archives

  • February 2007
  • January 2007
  • December 2006
  • November 2006
  • October 2006

• Categories

  • developing (470)

---

MySQL Developing And Programming Blog is proudly powered by cheap hosting
Entries (RSS) and Comments (RSS).