How to change mysql engine from Myisam to Innodb
ALTER TABLE tbl_employees ENGINE=INNODB;
Category Archives: SQ L& MYsql
How to upload large size file upload through phpmyadmin
If you are using Xampp server then use the following method go to the below folder phpmyadmin/libraries/config.default.php inside config.default.php edit the line as like below $cfg[‘ExecTimeLimit’] = 0;
Getting the following error while import sql file in phpmyadmin.
Fatal error: Out of memory (allocated 998506496) (tried to allocate 496760462 bytes)phpMyAdmin\libraries\import.lib.php on line 373 Step1:Copy the .sql file into the xammp folder in the below path xampp/mysql/bin Step2:create a database called testdatabase in phpmyadmin step3: Execute the below command through command prompt C:\xampp\mysql\bin>mysql -u root -D testdatabase < db_dump_db.sql
How to copy a table from one database to another database including structure and data in mysql
There are different options for copying table from one database to another database. Two methods i m giving here 1.In phpmyadmin one option is there to copy tables.Phpmyadmin if you go to operations tab->copy database to using the below options using a checkbox. Structure only Structure and data Data only CREATE DATABASE before copying Add… Read More »
Column ’emp_id’ in IN/ALL/ANY subquery is ambiguous
Below query execution throwing the error Column ’emp_id’ in IN/ALL/ANY subquery is ambiguous select employees.emp_id,emp_reg_date from employees join emp_linking_table on emp_linking_table.emp_id=employees.emp_id where emp_id not in( select emp_id from data_record_table) solution: if you change condition like as below will give proper result where employees.emp_id not in( select emp_id from data_record_table)
CASE When Not working with varchar (default value null) in mysql.. please check the solution
create a table using the below sql statement
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE TABLE IF NOT EXISTS `students` ( `id` int(10) unsigned NOT NULL, `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `first_name` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL, `last_name` varchar(250) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(250) COLLATE utf8_unicode_ci NOT NULL, `age` int(10) NOT NULL, `totalmarks` varchar(250) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Dumping data for table `students` -- INSERT INTO `students` (`id`, `created_at`, `updated_at`, `first_name`, `last_name`, `email`, `age`, `totalmarks`) VALUES (23, '2015-04-28 06:09:38', '2015-05-15 23:55:29', 'John', 'mathew', 'vasanthanpv@gmail.com', 33, '333'), (26, '2015-05-16 01:19:24', '2015-05-16 01:19:24', 'vasanthan', 'pv', 'vasanthanpv@gmail.com', 44, '333'), (27, '2015-05-16 01:19:46', '2015-05-16 01:19:46', 'Martin', 'jacob', 'vasanthanpv@gmail.com', 22, '22'), (28, '2015-05-16 01:20:06', '2015-05-16 01:20:06', 'Manu', 'Ram', 'vasanthanpv1@gmail.com', 32, '55'), (30, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, '', '', 0, ''); |
if we use the below mysql query output as like below
|
1 2 3 |
SELECT id,case age when '' then 'No Age' else age end as sudent_ages,case first_name when '' then 'No First Name' else first_name end as sudent_first_name from students |
you can see the result with NULL value for the first_name field. if you use the query as in a newformat ..with IFNULL condition it wil work. below query will work for you SELECT… Read More »
How to join the below two table using Joins
Below are the two database tables tbl_questions
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE IF NOT EXISTS `tbl_questions` ( `id` int(10) NOT NULL, `question` varchar(250) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; -- -- Dumping data for table `tbl_questions` -- INSERT INTO `tbl_questions` (`id`, `question`) VALUES (1, 'Prime minister India'), (2, 'President of India'); |
tbl_answer
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<pre class="lang:mysql decode:true " >CREATE TABLE IF NOT EXISTS `tbl_answer` ( `id` int(10) NOT NULL, `question_id` int(10) NOT NULL, `answer` varchar(250) NOT NULL, `status` varchar(3) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1; -- -- Dumping data for table `tbl_answer` --</pre> INSERT INTO `tbl_answer` (`id`, `question_id`, `answer`, `status`) VALUES (1, 1, 'Narendra Modi', 'y'), (2, 1, 'Manmohan singh', 'n'), (3, 1, 'Sonia Gandhi', 'n'), (4, 1, 'Rahul gandhi', 'n'), (5, 2, 'Pranabh Mukherji', 'y'), (6, 2, 'Prakash karat', 'n'), (7, 2, 'Sasi tharoor', 'n'), (8, 2, 'APJ abdul Kalam', 'n'); |
tbl_questions tbl_answer Output would be like this i need to get ouput as above, anybody is there to help. please comment….
Count of multiple fields in a join query in mysql
This is the query i used to fetch count of multiple fields from my table.I used four times select query inside a query before doing join
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select proj.id,proj.projectname,count(bugtbl.id) as totalbug,(select count(id) from tbl_bug_history where bug_id = bugtbl.id and status = 'invalid' ) as invalidcount,(select count(id) from tbl_bug_history where bug_id = bugtbl.id and status = 'feedback') as feedbackcount,(select count(id) from tbl_bug_history where bug_id = bugtbl.id and status = 'duplicate') as duplicatecount,(select count(id) from tbl_bug_history where bug_id = bugtbl.id and status = 'normal') as normalcount from tbl_projects proj left join tbl_bug bugtbl on bugtbl.project_id=proj.id group by proj.projectname |
you can see the database structure and query result as below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
CREATE TABLE IF NOT EXISTS `tbl_projects` ( `id` int(11) NOT NULL AUTO_INCREMENT, `projectname` varchar(250) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `tbl_projects` -- INSERT INTO `tbl_projects` (`id`, `projectname`) VALUES (1, 'project-one'), (2, 'project-two'), (3, 'project-three'), (4, 'project-four'); CREATE TABLE IF NOT EXISTS `tbl_bug_history` ( `id` int(10) NOT NULL AUTO_INCREMENT, `bug_id` int(10) NOT NULL, `status` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ; -- -- Dumping data for table `tbl_bug_history` -- INSERT INTO `tbl_bug_history` (`id`, `bug_id`, `status`) VALUES (1, 1, 'invalid'), (2, 1, 'invalid'), (3, 2, 'invalid'), (4, 2, 'invalid'), (5, 3, 'duplicate'), (6, 3, 'duplicate'), (7, 4, 'feedback'), (8, 4, 'feedback'), (9, 5, 'duplicate'), (10, 5, 'duplicate'), (11, 6, 'duplicate'), (12, 6, 'invalid'), (13, 7, 'feedback'), (14, 7, 'normal'), (15, 8, 'duplicate'), (16, 8, 'normal'), (17, 9, 'feedback'), (18, 9, 'feedback'), (19, 10, 'invalid'), (20, 10, 'feedback'); CREATE TABLE IF NOT EXISTS `tbl_bug` ( `id` int(10) NOT NULL AUTO_INCREMENT, `project_id` int(10) NOT NULL, `bugname` varchar(250) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; -- -- Dumping data for table `tbl_bug` -- INSERT INTO `tbl_bug` (`id`, `project_id`, `bugname`) VALUES (1, 1, 'first-bug'), (2, 1, 'second-bug'), (3, 1, 'bug-third'), (4, 1, 'bug-four'), (5, 1, 'bug-give'), (6, 1, 'master-bug'), (7, 2, 'error-notice'), (8, 3, 'invalid bug'), (9, 4, 'insufficinet memory'), (10, 4, 'hello bug'); |
Trigger Execution examples in mysql
We have two database tables. while updating the table entry trigger will execute. tbl_experience tbl_salary after updating the experience in the tbl_experience table. automaticaly update the salary column in the tbl_salary Step1: Use the below code to create tables through phpmyadmin-paste the below code into the query window.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE TABLE IF NOT EXISTS `tbl_experience` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(250) NOT NULL, `exp` int(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; -- -- Dumping data for table `tbl_experience` -- INSERT INTO `tbl_experience` (`id`, `name`, `exp`) VALUES (1, 'vasanthan', 1), (2, 'rajesh', 2); CREATE TABLE IF NOT EXISTS `tbl_salary` ( `id` int(10) NOT NULL AUTO_INCREMENT, `empid` int(10) NOT NULL, `salary` int(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; |
Step2: execute the below script through… Read More »
IS NULL usage in mysql-php
Example: we have two tables tbl_students,tbl_marks using a join query to fetch the students not having records in marks table.It should work using a single query see the blow example without using subquery. For creating students and marks table use the below query
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
CREATE TABLE IF NOT EXISTS `tbl_marks` ( `id` int(10) NOT NULL AUTO_INCREMENT, `studentid` int(10) NOT NULL, `marks` int(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; -- -- Dumping data for table `tbl_marks` -- INSERT INTO `tbl_marks` (`id`, `studentid`, `marks`) VALUES (1, 2, 10), (2, 3, 15), (3, 4, 11), (4, 5, 19), (5, 6, 20); -- -------------------------------------------------------- -- -- Table structure for table `tbl_students` -- CREATE TABLE IF NOT EXISTS `tbl_students` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; -- -- Dumping data for table `tbl_students` -- INSERT INTO `tbl_students` (`id`, `name`) VALUES (1, 'vasanthan'), (2, 'jayesh'), (3, 'nasar'), (4, 'sreejith'), (5, 'arun'), (6, 'clinton') |
use the below query to display null records
|
1 2 3 4 |
SELECT name, marks FROM tbl_students LEFT JOIN tbl_marks ON tbl_students.id = tbl_marks.studentid WHERE marks IS NULL |