Control Flow in SQL

SELECT name AS Name, category AS Category,
IF(winter>500, "Sells", "Slow")
AS Trend FROM sales;

http://www.roseindia.net/mysql/mysql5/flow-control-constructs.shtml

Rename tables

RENAME TABLE table1 TO table2;

Add a user

connect mysql
INSERT INTO user VALUES(’%',’monty’,PASSWORD(’some_pass’);
FLUSH PRIVILEGES;

Monitoring

The number of mysql processes running at any given time should be monitored. I do this manually from time to time, using SHOW PROCESSLIST in mysql. I had a problem in the past with processes never expiring and the number of connections maxed out. Set timeout to resolve this. Put report id and user id in the comment before running the sql. So, you can trace back what the report the sql for and who issued it when you need to kill it or analyze how to fix it.

Create a table from other table

CREATE TABLE emps2 as select * from emps;

One downside to this approach on both MySQL and Oracle is that it doesn’t make an exact replica of the table, for example any index against the table will not be receated for the new table.

CREATE TABLE emp5 like emp;

The downside of this method is that the data isn’t copied across in the way it is using an SQL statement in the create. But it’s easy to do that using a command like follows.

INSERT INTO emps5 select * from emps;