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 [...]
Archive for category databases
Substring magic with mysql
Jan 24
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 . [...]
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 which [...]
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, PRIMARY KEY (d_id) ); insert into dice (roll) values (1); insert into dice (roll) values (2); insert into dice (roll) values (3); insert into dice (roll) values (4); insert into dice (roll) values (5); insert into dice [...]
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, report varchar(50), PRIMARY KEY (b_id) ); CREATE TABLE student ( s_id int(11) NOT NULL auto_increment, name varchar(15), PRIMARY KEY (s_id) ); insert into student (name) values (‘bob’); insert into bookreport (s_id,report) values ( [...]
Loading Data into Tables from Text Files. Assume you have the following table. CREATE TABLE loadtest ( pkey int(11) NOT NULL auto_increment, name varchar(20), exam int, score int, timeEnter timestamp(14), PRIMARY KEY (pkey) ); And you have the following formatted text file as shown below with the unix “tail” command: $ tail /tmp/out.txt ‘name22999990′,2,94 ‘name22999991′,3,93 [...]
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.
Through the mysql client: update mysql.user set password=password(‘NEW_PASSWORD’) where user=’USERNAME’ and host=’HOSTNAME’; flush privileges; Through the command line: mysqladmin -u USERNAME -p CURRENT_PWD password NEW_PWD Replace USERNAME, CURRENT_PWD and NEW_PWD with appropriate values.