Posted by: phillipnb | August 1, 2012

Some Useful Sqls


Let me deviate from PHP, JQuery etc and talk about SQL in this post. There are a few sql queries which we keep using again and again and there are others which we may use it maybe once in a few months. In this post we will browse through a few sqls which is a mix of both the categories – some of them we use frequently and some of them we use rarely. (All the sqls described below have been tested using MySql).

(1).Displaying all the tables in a database.
This is one of the frequent questions on a number of php forums – how to see or list all the tables in a database. This can be achieved by using the sql:
SHOW TABLES;

(2).Search whether a particular table is there in the database.
.There will be instances where we would like to know if a table is there in the database. Instead of listing all the tables and going through the list one by one, we would like to know directly if a concerned table is there. For that, we use the following sql:
SELECT table_name FROM information_schema.tables WHERE table_schema = 'your databasename' AND table_name = 'your table name';

(3).Get a count of the tables in your database
Some times we would like to know the total number of tables in our database. The sql to get this is: If my database name is my_database then the sql is
SELECT count(table_name) FROM information_schema.tables WHERE table_schema = 'my_database'

(4).Adding a new field to a table.
Most of us know the syntax for altering a table but we keep searching for it and some times keep trying the wrong sql. Here is the sql to add a new field to an existing table. Alter a table to add a new field. So, if I have a table called ‘mytable’ with the last field name being old_last_field and I want to add a new field named new_field_name of varchar(20) then the sql will be like this:
ALTER TABLE mytable ADD new_field_name VARCHAR(20) AFTER old_last_field

(5).Alter a table and add an index.
To add an index called index_my_table to the table called my_table and field called my_table_field, here is the sql:
ALTER TABLE my_table ADD INDEX index_my_table (my_table_field);

(6).Alter table and change the data type of field.
So if I want the datatype of a field to be changed to decimal with default value as null, then use this sql:
ALTER TABLE my_table MODIFY field_name decimal(14,3) NULL;

(7).Create a new column and copy another column into this new column.
If I have a table called mytable with fields id and name, let us add a new field named new_id. To add the new field to mytable we will use the sql:
ALTER TABLE mytable ADD new_id varchar(2) NOT NULL
Now to copy the values in the id field into the new_id field we will use the following sql:
UPDATE mytable AS A INNER JOIN
(SELECT * FROM mytable)
as B on B.id = A.id
set A.new_id = B.id

(8).Delete rows using joins:
Suppose I want to delete from tableA a row which has a common column in another table called tableB. The sql to use will be:
DELETE FROM tableA USING tableA JOIN tableB ON tableA.fieldname = tableB.fieldname;

(9).To change the engine on my_table from myisam to innodb
ALTER TABLE my_table ENGINE = InnoDB;

I hope these sqls are useful to you. Let me know your comments.

Happy PHPing

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: