Some issues are very important in all 134A projects: good user interface design, good software design, teamwork, and a well-written report. I'll be talking more later about the others. About the report:
You should start early, and you are welcome to bring your outline or
your draft to a TA office hour to discuss it and get ideas for improving
it. The report should be detailed enough to be interesting, but it should
also be six pages single-spaced at most. You should use the report-writing
skills that you learned in your general education and lab science classes.
These skills are important for graduate school, for software jobs with
top companies, and for project leader and management jobs in all organizations.
The answer from a query is called a "result set." Conceptually, a result set is the same as a new table, except that result sets are not stored persistently in the database.
Result sets can be ordered:
select author, date
from messages
where date > '2000-11-11'
order by date
Each line that adds detail to what should be selected is called a "clause."
The example above has from, where, and order by
clauses. By default order is increasing.
delete from messages where date < '2000-11-11';Syntax note: curdate() is a builtin SQL function, not a PHP function. Semantics note: the where clause is optional in delete and update commands. If you leave it out, every record will be affected. This is almost never what you want.
update messages set author = 'John Smith' where author = 'Smith';
insert messages (author, date) values('Brown',curdate());
Usually your PHP script will build up an SQL command by concatenating various substrings, and then send the command to be executed by the MySQL server. For example:
$cmd = "insert messages (author, date) values('Brown'," . today() . ")";In the PHP code above, today() is a PHP function.
$result = mysql_query($cmd, $link);
What happens when we create a new tuple and only provide values for some of its fields? Then the database server fills in default values for the remaining fields. The most common default value is NULL.
What happens when an insert command fails to specify a value for a field
that is supposed to be NOT NULL? I couldn't find the answer
in our MySQL book!
create table messages (Now every record should be inserted with a NULL value for the field named id. The server will then automatically assign a value to this field that is one larger than the largest value ever assigned previously to a record for this table.
...
id integer unsigned not null auto_increment primary key
... )
select count(*) from messages;The last example above reports how many messages are in the database from each of the seven days of the week.select count(*) from messages where author = 'Smith';
select max(date) from messages where author = 'Smith';
select distinct author from messages order by author;
select count(distinct author) from messages;
select dayofweek(date) as d, count(*) from messages group by d;
Note that distinct is a keyword, written without parentheses,
but dayofweek() is a builtin function. Other builtin group-based
functions include min, max, sum, and avg.
select messages.*, address.zip from messages, addressThis type of query is called a "join" query.
where messages.author = address.author
Notice that the join is specified in the where clause, just
like a logical condition on values in a single record.