Mysql your visual blueprint for creating open source databases- P7

Mysql your visual blueprint for creating open source databases- P7: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- P7 UPDATE DATA IN TABLES 5 If you are working with a table that has a timestamp column, this column is automatically updated with the current date and time in each row modified by any UPDATE query. If you want to update a row and preserve the current value of the timestamp column, you must explicitly set the column to its original value. For example, if you were to add a second address to the address table and perform a similar update, you may want to avoid updating the timestamp in the updatetime column. The following query accomplishes this: Example: UPDATE address SET address2=address, city2=city, state2=state, updatetime=updatetime; While setting a column to its own value normally has no effect, in a timestamp column this prevents the MySQL server from automatically updating the field. You can also set the timestamp column explicitly to a different value. For example, the following UPDATE query sets all rows to a specified updatetime value: Example: UPDATE address SET address2=address, city2=city, state2=state, updatetime=20030101120000;ˇ Type UPDATE mail SET Á Type city2=city, ‡ Type SELECT name, s The values of the columnsaddress2=address, and press state2=state, postal2=postal; address, address2 FROM mail; you specified are displayedEnter. and press Enter. and press Enter. for all rows. Verify that the address values were copied.s You are prompted for the s This completes thenext line. UPDATE query. All of the rows of the table are updated. 107 MySQLINCREMENT A COLUMN VALUE When a new test is administered to students, you may wantO ften, you will find it useful to update a columns value based on its existing value. The simplest to increment the numtests column for all of the rows in the example of this is to increment a numeric columns database. You can use a simple UPDATE query tovalue. This is easy to do in an UPDATE query by referring to accomplish this:the columns current value. UPDATE examsFor example, suppose you created a table to store exam SET numtests = numtests + 1;statistics for students. The following CREATE TABLE query This query adds one to the current value of the numtestscreates this simple table: column for each row and stores the resulting value in thatCREATE TABLE exams (name VARCHAR(80), rows numtests column, replacing the original value. The net numtests INT, totalscore INT, avgscore effect is to increment the numtests column for every student. TINYINT); As with other UPDATE queries, you could optionally add aThis creates a table called exams with four columns: name WHERE clause. Specifying a WHERE clause may be useful tofor the student name, numtests for the number of tests the increment the number of tests for only a single student orstudent has taken, totalscore for the total of all test scores, group of students. You can use a wide variety of arithmeticand avgscore for an average. operations on MySQL column values; these are listed in Chapter 7. INCREMENT A COLUMN VALUENote: This example uses the testdb ¤ Type CREATE TABLE exams › Type INSERT INTO exams Á Type (Ted,3,database and creates a new table. (name VARCHAR(80), and (name, numtests, totalscore) 220),(Sue,6, 510); ...