Changing the default value of column in MySQL

After create table if you want to change default value for any column of table the we can use alter command in following way Syntax

Example

above query will set default value for column_name to provided default value.

MySql Update table column from another table colunm

Updating a table fields from another table columns we can user following syntax

There are two tables table1 and table2. Both table have common id value. To select appropriate field from table inner join in used and updating table1 fields from table2 fields set statement is used. For example there are two tables with … Read more

updating user login password

SET PASSWORD FOR ‘user’@’localhost’ = PASSWORD( ‘NewPassword’ ); SHOW VARIABLES LIKE ‘validate_password%’; # SET GLOBAL validate_password_length = 5; # SET GLOBAL validate_password_number_count = 0; # SET GLOBAL validate_password_mixed_case_count = 0; # SET GLOBAL validate_password_special_char_count = 0;

Providing default value for column

We can provide default value for a column. If we don specify its value then it will automatically assign a default value. We can specify default value on table creation. Syntax for adding default value is

for example

this will produce following output

while inserting any rowin table if we dont provide … Read more

Date and Time

There are various pre defined function for data and time we will see them one by one. If you want to show current time then we use CURTIME().It returns time in HH:MM:SS string format

below is HHMMSS in numeric format

To select current data we use CURDATE().It returns data in YYYY-MM-DD string format. … Read more

Finding all column name of table

Suppose we have a table user and we want to fetch all its column name.For this we use INFORMATION_SCHEMA. we will use following query to fetch all column name of table

  Above query will fetch all columns of user table available in social database. Read More Setting innodb_lock_wait_timeout Set time zone in mysql … Read more

Mysql create table statement

To create table in mysql following syntax is used

For example,

if table created without any error then it will show following statement Result:

This query will result a table with the above fields. The first column will hold number while other columns are type of varchar. Create Table in Mysql with … Read more

Updating the column data size

To create a table

after creating table we want to increase the size of description field from varchar(20) to varchar(100). for that we will use alter table command Syntax

Example

we can also decrease the size of column but it may cause the data loss. like

Setting innodb_lock_wait_timeout

What is innodb_lock_wait_timeout ? In MySql innodb_lock_wait_timeout is an Innodb transaction wait time in seconds for a row lock. An another transaction has to wait for specified innodb_lock_wait_timeout if a transaction is taking place.  innodb-lock-wait-timeout in a system variable in global and session scope this variable can set dynamically to both scopes. We can set … Read more

Show engine innodb status

InnoDB Monitors provide information about the InnoDB internal state. This information is useful for performance tuning. Each Monitor can be enabled by creating a table with a special name, which causes InnoDB to write Monitor output periodically. Also, output for the standard InnoDB Monitor is available on demand through the SHOW ENGINE INNODB STATUS SQL … Read more