Adding More Data to the Test Table in MySQL

Q

How To Add More Data to the Testing Table in MySQL?

✍: FYIcenter.com

A

If you want to continue with other tutorial exercises in this FAQ collection, you need to add more data to the testing table. Follow the script below to add a new column and:

mysql> ALTER TABLE fyi_links ADD COLUMN tag VARCHAR(8);
mysql> UPDATE fyi_links SET tag = 'DEV' WHERE id = 101;
mysql> UPDATE fyi_links SET tag = 'DBA' WHERE id = 102;
mysql> UPDATE fyi_links SET tag = 'SQA' WHERE id = 103;

mysql> INSERT INTO fyi_links VALUES (104, 
   'www.mysql.com', '', '0', '2006-01-01', 'DBA');
mysql> INSERT INTO fyi_links VALUES (105,
   'www.oracle.com', '', '0', '2005-01-01', 'DBA');
mysql> INSERT INTO fyi_links VALUES (106,
   'www.php.net', '', '0', '2004-01-01', 'DEV');
mysql> INSERT INTO fyi_links VALUES (107, 
   'www.winrunner.com', '', '0', '2003-01-01', 'SQA');

mysql> UPDATE fyi_links SET counts = FLOOR(RAND()*10);

mysql> SELECT id, url, counts, DATE(created), tag 
   FROM fyi_links;
+-----+-------------------+--------+---------------+------+
| id  | url               | counts | DATE(created) | tag  |
+-----+-------------------+--------+---------------+------+
| 101 | dev.fyicenter.com |      4 | 2006-04-30    | DEV  |
| 102 | dba.fyicenter.com |      3 | 2006-07-01    | DBA  |
| 103 | sqa.fyicenter.com |      6 | 2006-07-01    | SQA  |
| 104 | www.mysql.com     |      1 | 2006-01-01    | DBA  |
| 105 | www.oracle.com    |      7 | 2005-01-01    | DBA  |
| 106 | www.php.net       |      4 | 2004-01-01    | DEV  |
| 107 | www.winrunner.com |      8 | 2003-01-01    | SQA  |
+-----+-------------------+--------+---------------+------+
7 rows in set (0.00 sec)

⇒ Sorting Query Output in MySQL

⇐ Select Rows with WHERE Clause in MySQL

⇑ SELECT Query Statements with GROUP BY in MySQL

⇑⇑ MySQL Database Tutorials

2017-11-02, 2715🔥, 0💬

Select Rows with WHERE Clause in MySQL
How To Select Some Rows from a Table in MySQL? If you don't want select all rows from a table, you can specify a WHERE clause to tell the query to return only the rows that meets the condition defined in the WHERE clause. The WHERE clause condition is a normal Boolean expression. If any data from ta... 2017-11-02, 3158🔥, 0💬

Sorting Query Output in MySQL
How To Sort the Query Output in MySQL? If you want the returning rows to be sorted, you can specify a sorting expression in the ORDER BY clause. The simplest sort expression is column name who's values will be sorted by. The following select statement returns rows sorted by the values in the "counts... 2017-11-02, 3035🔥, 0💬

Count Number of Rows with SELECT Statements in MySQL
How To Use SELECT Statement to Count Number of Rows in MySQL? If you want to count the number of rows, you can use the COUNT(*) function in the SELECT clause. The following tutorial exercise shows you some good example: mysql> SELECT COUNT(*) FROM fyi_links; +----------+ | COUNT(*) | +-------... 2018-01-06, 2972🔥, 0💬

Selecting All Columns of All Rows in MySQL
How To Select All Columns of All Rows from a Table in MySQL? The simplest query statement is the one that selects all columns of all rows from a single table: "SELECT * FROM tableName;". The (*) in the SELECT clause tells the query to return all columns. The missing WHERE clause tells the query to r... 2017-11-05, 2925🔥, 0💬

Using SELECT Statements in Views in MySQL
Can SELECT Statements Be Used on Views in MySQL? Select (query) statements can be used on views in the same way as tables. The following tutorial exercise helps you creating a view and running a query statement on the view: mysql> CREATE VIEW myLinks AS SELECT * FROM fyi_links WHERE url LIKE ... 2018-01-06, 2909🔥, 0💬

Sort by Multiple Columns in MySQL
Can the Query Output Be Sorted by Multiple Columns in MySQL? You can specifying multiple columns in the ORDER BY clause as shown in the following example statement, which returns employees' salaries sorted by department and salary value: mysql> SELECT tag, counts, url, DATE(created) FROM fyi_... 2017-11-02, 2802🔥, 0💬

Sorting Output in Descending Order in MySQL
How To Sort Output in Descending Order in MySQL? If you want to sort a column in descending order, you can specify the DESC keyword in the ORDER BY clause. The following SELECT statement first sorts the "tag" in descending order, then sorts the "counts" in ascending order: mysql> SELECT tag, ... 2017-11-02, 2796🔥, 0💬

What Is a SELECT Query Statement in MySQL
What Is a SELECT Query Statement in MySQL? The SELECT statement is also called the query statement. It is the most frequently used SQL statement in any database application. A SELECT statement allows you to retrieve data from one or more tables or views, with different selection criteria, grouping c... 2017-11-05, 2745🔥, 0💬

Selecting Columns in a Query in MySQL
How To Select Some Columns from a Table in MySQL? If you want explicitly tell the query to return some columns, you can specify the column names in the SELECT clause. The following select statement returns only two columns, "id" and "url" from the table "fyi_links": mysql> SELECT id, url FROM... 2017-11-05, 2722🔥, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.