MySql

MYSQL QUERIES

1.  root@host# mysql -u root -p password;

2.  Enter password:*******

3.  mysql> use TUTORIALS;

4.  Database changed

5.  DELETE FROM table_name [WHERE Clause]

6.  Update table_name set column_name=value where condition

7.  Alter table table_name constraint PK PRIMARY KEY (column_name)

8.  Alter table table_name constraint FK FOREIGN KEY(COLUMN_NAME) refrences table_name(columns_name)

9.  Show databases;

10.   Show tables;

11.   Insert into table_name values(data_value according to column_type)

12.  Renaming a Table

13.  mysql> ALTER TABLE users RENAME public;

14.  Changing a columns datatype

15.  mysql> ALTER TABLE public MODIFY name CHAR(150);

16.  Renaming a Table and Changing its datatype at once

17.  mysql> ALTER table users CHANGE
-> email emailaddy CHAR (100);

18.  Adding a Column

19.  mysql> ALTER TABLE public ADD time TIMESTAMP;

20.  Remove a Column

21.  mysql> ALTER TABLE public DROP COLUMN time;

22.  SELECT c.contentid, c.contenttext, u.name
FROM content c, users u
WHERE c.contentid = $cntntid
AND c.userid = u.userid

23.   update user set password=PASSWORD("NEWPASSWORD") where User='vivek';

24.   mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass')

25.       ->                   WHERE User='root';

26.   mysql> FLUSH PRIVILEGES;

27.    

28.  BACKUP

C:\Documents and Settings\jagdeep.singh>mysqldump -u root -pnvish test>"c:\test.sql"

COMMAND  FOR LOGIN IN LINUX:  mysql -u root -p

RUN SQL SCRIPT ON LINUX: \. /soft/config/data.sql

\. /soft/2_Dec_2011/downloaded.sql [ \. Sql file path ][ source  Sql file path]

MYSQL ON LINUX

* To start mysql server:             

/etc/init.d/mysqld start

* To stop mysql server:

/etc/init.d/mysqld stop

* To restart mysql server

 /etc/init.d/mysqld restart

Tip: Redhat Linux also supports service command, which can be use to start, restart, stop any service:

# service mysqld start

# service mysqld stop

# service mysqld restart

LIMIT and OFFSET SQL Pagination

Both MySQL and PostgreSQL support a really cool feature called OFFSET that is usually used with a LIMIT clause.

The LIMIT clause is used to limit the number of results returned in a SQL statement. So if you have 1000 rows in a table, but only want to return the first 10, you would do something like this:

SELECT column FROM table LIMIT 10

This is similar to the TOP clause on Microsoft SQL Server. However the LIMIT clause always goes at the end of the query on MySQL, and PostgreSQL.

Now suppose you wanted to show results 11-20. With the OFFSET keyword its just as easy, the following query will do:

SELECT column FROM table LIMIT 10 OFFSET 10

This makes it easy to code multi page results or pagination with SQL. Often the approach used is to SELECT all the records, and then filter through them on the application server tier, rather than directly on the database. As you would imagine doing this on the database yields much better performance.

I have known that PostgreSQL supports the OFFSET keyword for quite some time, and for some reason I always thought it was not supported by MySQL. Well it turns out that it is supported now.

 

Return Random Records in MySQL

SELECT product_id, title, description
FROM products
WHERE active = 1
AND stock > 0
ORDER BY RAND()
LIMIT 4

UPDATE FOR DATE CASTING
SELECT CAST(Datetimefield AS DATE) as DateField, SUM(intfield) as SumField
FROM MyTable
GROUP BY CAST(Datetimefield AS DATE)
http://www.yolinux.com/TUTORIALS/LinuxTutorialMySQL.html

http://www.apachefriends.org/en/xampp-linux.html

http://www.informit.com/articles/article.aspx?p=339935&seqNum=2

http://theos.in/desktop-linux/tip-that-matters/how-do-i-restart-mysql-server/
http://www.petefreitag.com/item/451.cfm

Reset Root Password

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

Store Procedure:-

DELIMITER $$

USE `jagdeep_live`$$

DROP PROCEDURE IF EXISTS `procedureTest`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `procedureTest`(IN productId VARCHAR(20))
BEGIN
 DECLARE profit DECIMAL(18,2) default 0;
  DECLARE mrp DECIMAL(18,2) default 0;
  DECLARE sale DECIMAL(18,2) default 0;
  DECLARE b1 DECIMAL(18,2) DEFAULT 0;
  DECLARE b2 DECIMAL(18,2) DEFAULT 0;
  declare mrpCursor
   CURSOR FOR SELECT MAX(CASE WHEN pp.product_price_Type_id='LIST_PRICE' THEN pp.price END) AS "pMrp",   MAX(CASE WHEN pp.product_price_Type_id='DEFAULT_PRICE' THEN pp.price END) AS "pSale" 
    FROM product_price pp where (pp.product_id = convert(productId using latin1) collate latin1_general_ci ) GROUP BY pp.product_id;
   OPEN mrpCursor;
   FETCH mrpCursor INTO mrp,sale;
    set b1=(+mrp)-(+sale);
    set b2=(+b1)/(+mrp);
    SET profit :=(+b2)*100;
  select ROUND((+b2)*100,2);
END$$

DELIMITER ;

CALL procedureTest('JSDHJKHJH67687');



MySQL Config Settings:-
[mysqld]
lower-case-table-names=1
datadir=/var/lib/mysql/
# symbolic-links=0
#max-connections=10000
server-id=1
#log-bin=/var/log/mysqllogs/mysql-bin.log
#log-bin-index=/var/log/mysqllogs/mysql-bin.index
expire_logs_days=10
binlog_format=MIXED
key_buffer_size = 2147483648
max_allowed_packet=640M
table_cache=2400
max_connections=256
table_definition_cache=2400
max_heap_table_size=512M
tmp_table_size=512M
join_buffer_size=5M
query_cache_type=1
query_cache_size =128M
query_cache_limit=64M
expire_logs_days=10
open_files_limit=30000
innodb_lock_wait_timeout=300