MySQL - Quick Command Reference

Alter Table

Change data type of a column

mysql> alter table 
-> change   varchar(100); <-- new data type to be changed to

Update Table

Change value of a Column based on a condition

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

Mysql - execute SQL statements in a script file

1. without username / password

shell> mysql db_name < sqlfile.sql > output.txt

2. with username / password

mysql -u username -p database_name < sqlfile.sql

Data Base Performance Tips

Indexes - Faster Reads / Slower Inserts,Updates,Deletes -

Views – Simpler Query / Slower Reads and more work for DB

De normalization - More Space Req and cost of write operations / Faster Reads with fewer join’s required

Normalization - Lesser Space Req, low cost of write operations / Slower Reads with more join’s required.

ORACLE : who is connected to My Oracle Instance

You have a DB you call your own, but you cant seem to drop some users eventhough you have stopped all services you were running which might have connected to your Oracle Instnace,

Well they (Apps or users) can’t hide any longer … The Oracle’s best Spy is on your side – here is the SQL which can bring them to the open. ( you need to be the SysDBA to do this though )

1. set linesize 30000 – to make sure everything appears in a single line

2. SQL> select username, sid, serial#, status, osuser, process, machine, terminal, program, logon_time from v$session where username is not null and username not in (‘SYSTEM’,'SYS’,'DBSNMP’);

- All the agents are displayed in a table with their USERNAME, SID,SERIAL#,STATUS,OSUSER,PROCESS,MACHINE,TERMINAL,PROGRAM
you have to decide if you will shoot (force kill ) or negotiate ( stop the services in the machines they are running in )

MySQL - Run SQL Queries From A cmd prompt

Data setup and tear down is a common task during development and testing. Ability to execute sql queries from command prompt allows developers to automate these tasks.

You can achieve this by using the following command -

mysql -u user -p -e 'SQL Query' database

Where,
-u : Specify mysql database user name
-p : Prompt for password
-e : Execute sql query
database : Specify database name

note : You don't need to specify user and password if you have not set up authentication.