Let's say you're maintening a club members' database with the following columns:
| FirstName | LastName | BirthDate | JoinDate | MemberID |
| Roy | Campinelli | 1964-04-21 | 1999-11-14 | 001 |
| Ashok | Singh | 1972-08-13 | 1997-07-18 | 002 |
| Roy | Stanley | 1964-04-21 | 2001-01-15 | 003 |
| Sheryl | Singh | 1966-23-03 | 1997-07-18 | 004 |
We can perform all kinds of queries on this table. Let's call the table members.
select * from members;
The * is a wildcard which tells mysql server to show every information contained in the table. This query will return
| FirstName | LastName | BirthDate | JoinDate | MemberID |
| Roy | Campinelli | 1964-04-21 | 1999-11-14 | 001 |
| Ashok | Singh | 1972-08-13 | 1997-07-18 | 002 |
| Chuck | Stanley | 1980-12-17 | 2001-01-15 | 003 |
| Sheryl | Culina | 1966-23-03 | 1999-06-29 | 004 |
When querying a table, you can make results more specific by using WHERE. Eg.
select * from members where firstname = "Roy";
This will return
| FirstName | LastName | BirthDate | JoinDate | MemberID |
| Roy | Campinelli | 1964-04-21 | 1999-11-14 | 001 |
| Roy | Stanley | 1964-04-21 | 2001-01-15 | 003 |
Another example. Suppose you want members born on or after a certain date
select * from members where birthdate >= 1970-01-01;
This will return
| FirstName | LastName | BirthDate | JoinDate | MemberID |
| Ashok | Singh | 1972-08-13 | 1997-07-18 | 002 |
| Roy | Stanley | 1964-04-21 | 2001-01-15 | 003 |
You can see that from all the queries we've made so far, we obtained more than one result. But what if we only wanted the record of a single member? For that we need to use a column that is unique to each member, and in this case, it's the memberid column.
select * from members where memberid = 004;
This will return
| FirstName | LastName | BirthDate | JoinDate | MemberID |
| Sheryl | Singh | 1966-23-03 | 1997-07-18 | 004 |
These queries may seem simple and not worth the trouble of creating a database for. But imagine that you're managing a database with two million members. Will you still feel the same way? :)
Why do you need to compare dates? I could think of lots of reasons.
So let's say we want to get the ages of the members,
select now() - BirthDate from members;
where did now( ) come from in the above query? Well, now( ) is the function used to get the current date and time in mysql. You could use now( ) on it's own. Go ahead and try it, type
select now();
and see what you get.
So in the query select now() - BirthDate from members;, we get the the current date, the birthdate, and then find the difference. The result will be in number of days. But since we are interested in the ages of the members, we have to divide by 365.
select (now() - BirthDate) / 365 from members;
What we get from the above query will be decimal values, ie. something like 23.49, 56.01, etc. But wouldn't it be nice to get rid of the decimal part and just get nice plain integers? Sure it would. For that we will use the floor( ) function.
select floor((now() - BirthDate) / 365) from members;
This query will round down decimal numbers and output integers. You could also use ceil( ) or round( ) functions to round up instead of down. You probably don't want to round up a person's age though. :)
One more thing about this query. If you run it in mysql, you will notice that the result has a heading like
floor((now() - BirthDate)/365)
That heading is ugly isn't it. Well you can give it any heading you want using the AS attribute.
select floor((now() - BirthDate) / 365) AS age from members;
Run the query and you will see that the heading is now AGE.
You can sort the result in ascending or descending order using ORDER BY.
select floor((now() - BirthDate) / 365) AS age from members ORDER BY age desc;
select floor((now() - BirthDate) / 365) AS age from members ORDER BY age asc;
Updating multiple columns in a table: UPDATE tbl SET col1 = 'new_val', col2 = 'new_val', col3 = 'new_val' WHERE id = 9;
|
I initially had the below in this file, saved it under d:\mysql\data\personals.txt. mysql create table personals (fname varchar(23), lname varchar(23), address tinytext, id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)); With a dos console, I cd to d:\mysql\bin, and issued the command MYSQL < ..\DATA\PERSONALS.TXT Response was ERROR 1046 at line 1: No Database Selected Next I modified this file to read mysql create table personals.personals (fname varchar(23), lname varchar(23), address tinytext, id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)); As you can see, the database is indicated in personals.personals. Now I repeated the command MYSQL < ..\DATA\PERSONALS.TXT ERROR 1044 at line 1: Access denied for user: '@localhost' to database 'personals' Lastly I tried --user=root use personals mysql create table personals.personals (fname varchar(23), lname varchar(23), address tinytext, id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)); Now you see that we provided info for login as root, then change db to personals. The same command MYSQL < ..\DATA\PERSONALS.TXT this time did not produce any errors. This means it should have created my table called PERSONALS, under the db PERSONALS. Unfortunately, the table wasn't created. I wonder why.
How to replace value I needed to correct the value on a table row. There were over 200 records that needed updating. update tbl set FirstName= REPLACE(FirstName, 'Keneth', 'Kenneth')
|