Mysql is a wonderful database, and while many use it, most people only scratch the surface of what the database can do. One of the practical functions available is the substring_index function, and an imaginary mailing list example is a nice way to show how to use it.
Let imagine we have a mailinglist in a [...]
Archive for category databases
Substring magic with mysql
Jan 24
Mysql metadata
Nov 17
If you’re a developer and use mysql, I’m sure you’re aware that it’s a database and it quite good at storing data, but one of the neat things about Mysql (and most other databases) is also their ability to provide meta-data on the contents of the database.
Most people know how to use the meta-data queries [...]
It seems there are two camps when it comes to SQL and how to do database optimizations – the “generic camp” and “the specialist camp”. While I don’t consider myself an extremist, I am absolutely in the specialist camp and this little post is an explanation of why.
SQL is a generic database langauge . There [...]
Navigating around in mysql is quite easy. You can see which databases by using the command “show databases“. There will always exist a database called “mysql” which is used by mysql itself.
If your user account has access to a database, you can access it by using the “use DATABASENAME” command. You can se [...]
Date calc with SQL
Apr 8
Micro tip of the day: How many days has past since a date field in the database?
SELECT (TO_DAYS(NOW()) – TO_DAYS(date_field)) AS days_past FROM tablename
Mysql: Random dice
Jan 1
Getting a random roll of the dice:
CREATE TABLE dice (
d_id int(11) NOT NULL auto_increment,
roll int,
[...]
Assume you have the table “exams” in the database “test”.Then, the following will give you XML output if executed from the shell prompt with the “-X” option. For html output use the “-H” option.
$ mysql -X -e “select * from exams” test
Finding records that do not match between two tables.
CREATE TABLE bookreport (
b_id int(11) NOT NULL auto_increment,
s_id int(11) NOT NULL,
[...]
Loading Data into Tables from Text Files.
Assume you have the following table.
CREATE TABLE loadtest (
pkey int(11) NOT NULL auto_increment,
[...]
To dump data into a comma separated file use this:
SELECT * INTO OUTFILE ‘tablename.csv’
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘n’
FROM tablename;
Replace tablename with the tablename of the table you which to dump to a file.