Increment an exisitng value in Mysql

To increment or decrement and existing number in a table (you do not need to read the value first)  please read on : Increment the value 'timer' by one for the row in table 'movies' where 'movie_id' is '24', use: UPDATE movies SET timer=timer+1 WHERE movie_id=24 Note  to decrement use timer=timer-1 Enjoy Read more..

February 20th, 2008 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Use CONCAT to include text in SELECT results in MySQL

Function CONCAT allows the SELECT statements to act like printf commands by ataching text results to query results and so on Example : Generate a list of links in HTML from a table 'mylinks' with the columns 'urls' (http://www.ossmall.info) with 'title' (Open Software Solutions) The output will look like this : <a href="http://www.ossmall.info">Open Software Solutions</a><br /> To do this you must use a select statement like: SELECT CONCAT('<a href="', urls, '">', title, '</a><br />') FROM mylinks; Read more..

February 20th, 2008 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Rename or change name of MySQL table

This is how to change the name of an existing table tableone to tabletwo : RENAME TABLE  tableone TO tabletwo;  Note : you must issue this command with an user that has the correct priviledges Read more..

February 20th, 2008 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Show or list tables in a MySQL database

First step is to select a database , after that you can view the tables  1 . view all of the tables in the selected database : SHOW TABLES; 2.  view all of the tables in a different database (not selected): SHOW TABLES IN different_db; 3.  view a specific table in the selected  database : SHOW TABLES IN newDB LIKE '%time'; The above command will find all tables that end in "time"  from the database newDB Read more..

February 19th, 2008 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

MySQL dump import -> Error 1217

If you use  innodb tables and foreign key constraints, importing a mysqldump may generate foreign key errors. To resolve this issue please read on : At the beginning of the dump file add this : SET FOREIGN_KEY_CHECKS=0; At the end of the dump file add this : SET FOREIGN_KEY_CHECKS=1; This will disable foreign key checks for the mysqldump import session only. This will allow the data to be imported without the error being generated. Read more..

February 19th, 2008 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Delete a column from an existing MySQL table

This is how to delete the column my_thing from the table your_table ,  (use the following SQL command: ) ALTER TABLE 'your_table' DROP 'my_thing' enjoy Read more..

February 19th, 2008 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Add a column to an existing MySQL table

We will  add a column called email to the mycontacts table created in Create a basic MySQL table with a datatype of VARCHAR(90) To do this use the following SQL statements: 1.  this statement will add the column at the bottom of the table  ALTER TABLE mycontacts ADD email VARCHAR(90); 2.  this statement will add the column after a specific column ( such as name ) ALTER TABLE mycontacts ADD email VARCHAR(90) AFTER name; 3. this statement will add the column at the begining of ... Read more..

February 19th, 2008 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Create a basic MySQL table

Creating tables in databases is the  first step to store your data To create a table you must describe the columns and their atributes. We will create a table to hold contact information with four columns: contact_id, name, email, and birthdate. contact_id column =  integer number that is 20 decimal places long ( it is created with an INT(20) datatype). name column holds the full name of a contact, which we  will will set be no longer than 50 characters long, so the ... Read more..

February 18th, 2008 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Drop or delete a table in MySQL

This is how to remove a table from a MySQL database, and remove all of its data, use the following SQL command: 1.  log in mysql 2.  issue command : use yourdatabase;  3. issue command : drop table if exists yourtable; The last command will delete the database if it exists , it is usefull because if it does not exist it will not output any error Read more..

February 18th, 2008 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Display a list of databases on a MySQL server

This is how to list the databases that exist in a MySQL server, use the 'show databases' SQL command: show databases; +------------------+ | Database                | +------------------+ | database1              | | mysql                     | | test                         | +------------------+ It will display all the databases your user has access to Read more..

February 18th, 2008 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Create a MySQL database

Log in MySql To create a database called 'yourdatabase' use the following SQL command: create database yourdatabase; Read more..

February 18th, 2008 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Change the MySQL root user password

To change the MySQL root password to DifferentPass , use: mysqladmin -u root password DifferentPass Keep in mind , root is a powerfull user in mysql , use Hard Passwords ! Read more..

February 17th, 2008 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Dump Content using mysqldump

You do not need to enter mysql for this , here is the exact sintax you must use : mysqldump -u [username] -p [database_name] > /path/to/file.sql  example mysqldump -u mario  -p marioDB > /tmp/marioDB.sql Read more..

February 17th, 2008 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Create a MySQL user account

This is how to create a user youruser with password YourPassword  and grant them full access to the  database named yourdatabase, connect to the database with mysql and issue the command: grant all on yourdatabase.* to user@localhost identified by 'YourPassword'; Read more..

February 17th, 2008 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Import CSV file directly into MySQL

You can use mysql to import directly a csv file . You do not need to make a script to do this anymore From an Excel file , you need to export it as CSV ,  Remove the CSV Headers and the Excel data  from the file ,  and after that it can be inserted in MySql Here is an example : load data local infile 'your.csv' into table tblUniq fields terminated by ',' enclosed by '"' lines terminated by '\n' (yourName, yourCity, yourComments) Read more..

February 17th, 2008 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Change max_allowed_packet setting fast

If you want to change the  max_allowed_packet fast, without modifying any confis or restart mysql ? Follow this howto : mysql> show variables like 'max%' ; +-----------------------+------------+ | Variable_name | Value | +-----------------------+------------+ | max_allowed_packet | 1048576 | ... mysql> set max_allowed_packet = 1500000; Query OK, 0 rows affected (0.03 sec) mysql> show variables like 'max%' ; +-----------------------+------------+ | Variable_name | Value ... Read more..

January 12th, 2008 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Installation: configured for encryption, C API, and user defined functions.

Installation: configured for encryption, C API, and user defined functions. ./configure --with-openssl --enable-thread-safe-client --with-mysqld-ldflags=-rdynamic The --with-openssl is very helpful for creating your own password file. Also, if doing C API, having thread safe calls "could" come in handly...it's what I use. See (TIP 27) for user defined functions. Complete ... Read more..

November 15th, 2007 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Mysql Tricks : error 1016

"error: 1016: Can't open file:" If it's from an orphaned file, not in the database but on disk, then, the disk file may need to be deleted. myisamchk can help with damaged files. It's best to stop the database. # su - # mysqladmin shutdown ... Read more..

November 14th, 2007 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Mysql Tricks : Quick Status

Quick Status: mysql> \s -------------- /usr/local/bin/mysql Ver 14.3 Distrib 4.1.1-alpha, for pc-linux (i686) Connection id: 642 Current database: Current user: prog@localhost SSL: ... Read more..

November 14th, 2007 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button

Mysql Tricks : Print defaults for the current client connection

Print defaults for the current client connection $ my_print_defaults client mysql --port=3306 --socket=/tmp/mysql.sock --no-auto-rehash Note, for client connections, you can put setting in the user's .my.cnf file. ... Read more..

November 14th, 2007 admin Posted in MySQL No Comments »

AddThis Social Bookmark Button


Page 1 of 212»