Mysql your visual blueprint for creating open source databases- P8

Mysql your visual blueprint for creating open source databases- P8:The show tables command displays a list of tables available in the currently selected database. You can use this command when you are unsure of the exact name of a table within the database.
Nội dung trích xuất từ tài liệu:
Mysql your visual blueprint for creating open source databases- P8 USING SELECT QUERIES 6 When you use GROUP BY, you can select any number of This example uses a simple new table, columns as well as functions such as COUNT and AVG that scores. The CREATE TABLE command aggregate the data from the grouped rows. When you are for this table only needs to specify two not using GROUP BY, and you use a function like COUNT or columns. AVG, you cannot select any normal column values in the same query. The following table describes the available Example: functions in MySQL for use with GROUP BY clauses. CREATE TABLE scores ( name VARCHAR(50), FUNCTION DESCRIPTION score INT UNSIGNED); COUNT Number of rows Although this is a very basic table, you can achieve impressive results by storing AVG Average value multiple scores for each name and using SUM Total value the various MySQL grouping functions. MIN Minimum value MAX Maximum value STD Standard deviation BIT_OR Bitwise OR BIT_AND Bitwise AND° Type SELECT name, · Type GROUP BY name; ‚ Type SELECT name, ± Type FROM scores GROUPCOUNT(*) FROM scores and press Enter. AVG(score), and press Enter. BY name; and press Enter.and press Enter. s The number of scores for — Type MIN(score), s The name, average, each student is displayed. MAX(score) and press Enter. minimum score, and maximum score are displayed for each student. 127 MySQLDISPLAY DATA FROM MULTIPLE TABLES relationship between the two tables. In the example above,M ySQL is known as a relational database system, and one of the most important features of a the WHERE condition indicates that rows should be matched relational database is the ability to work with when the author columns from both tables are equal.relationships between different tables. You can use SELECT To refer to columns when working with multiple tables, youto retrieve related data from multiple tables. must specify the table name for each column. Separate theFor example, the quotes table stores quotations and their table and column names with a period. The column youcorresponding authors. If you had a separate table, named match between the tables does not have to be included inauthors, that stored birth and death dates for a list of authors, the results. The following SELECT query displays only theyou could combine the two in a single SELECT query: quote field from the quotes table and the corresponding born field from the authors table:SELECT * FROM quotes, authors WHERE quotes.author = authors.author; SELECT quotes.quote, authors.born FROM quotes, authorsThis query combines, or joins, data from the two tables. WHERE quotes.author = authors.author;Each row in the result includes a combination of thecolumns from the quotes table and the columns of the Although the columns that match across the two tablesauthors table. A row is returned for each row that matches in this example are both named author, the names do notbetween the tables. need to be similar. However, they should ...