mysql Archives - Ebhor.com

Changing the default value of column in MySQL

February 19, 2021February 17, 2021 by manish

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

 alter table table_name 
         alter column_name  
              set default default_value;

alter table table_name

alter column_name

set default default_value;

Example

 alter table student 
         alter semester  
              set default "first";

alter table student

alter semester

set default "first";

 alter table student 
         alter address  
              set default "Bhopal";

alter table student

alter address

set default "Bhopal";

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

MySql Update table column from another table colunm

September 27, 2020January 12, 2018 by manish

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

Update table1 inner join table2
on a.id=b.id
set
 a.field1=b.field1
b.field2=b.field3

Update table1 inner join table2

on a.id=b.id

set

a.field1=b.field1

b.field2=b.field3

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

November 8, 2019April 19, 2017 by manish

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

November 8, 2019April 5, 2017 by manish

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

column_name datatype DEFAULT value;

1	column_name datatype DEFAULT value;

for example

create table product( id int(11) NOT NULL AUTO_INCREMENT,
                       name varchar(50) NOT NULL,
                       quantity int(10) DEFAULT 100,
                      price  decimal(6,2) DEFAULT 10.22,
                      status varchar(50) DEFAULT 'AVAILABLE',
                      add_date timestamp DEFAULT CURRENT_TIMESTAMP,
                       PRIMARY KEY(id));

create table product( id int(11) NOT NULL AUTO_INCREMENT,

name varchar(50) NOT NULL,

quantity int(10) DEFAULT 100,

price decimal(6,2) DEFAULT 10.22,

status varchar(50) DEFAULT 'AVAILABLE',

add_date timestamp DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY(id));

this will produce following output

mysql> desc product;
+----------+--------------+------+-----+-------------------+----------------+
| Field    | Type         | Null | Key | Default           | Extra          |
+----------+--------------+------+-----+-------------------+----------------+
| id       | int(11)      | NO   | PRI | NULL              | auto_increment |
| name     | varchar(50)  | NO   |     | NULL              |                |
| quantity | int(10)      | YES  |     | 100               |                |
| price    | decimal(6,2) | YES  |     | 10.22             |                |
| status   | varchar(50)  | YES  |     | AVAILABLE         |                |
| add_date | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+----------+--------------+------+-----+-------------------+----------------+
6 rows in set (0.06 sec)

mysql> desc product;

+----------+--------------+------+-----+-------------------+----------------+

+----------+--------------+------+-----+-------------------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| quantity | int(10) | YES | | 100 | |

| price | decimal(6,2) | YES | | 10.22 | |

+----------+--------------+------+-----+-------------------+----------------+

6 rows in set (0.06 sec)

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

Date and Time

November 8, 2019April 5, 2017 by manish

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

mysql>SELECT CURTIME();


+-----------+
| CURTIME() |
+-----------+
| 16:06:23  |
+-----------+
1 row in set (0.06 sec)

mysql>SELECT CURTIME();

+-----------+

| CURTIME() |

+-----------+

| 16:06:23 |

+-----------+

1 row in set (0.06 sec)

below is HHMMSS in numeric format

mysql> select CURTIME()+0;
+-------------+
| CURTIME()+0 |
+-------------+
|      162404 |
+-------------+
1 row in set (0.06 sec)

mysql> select CURTIME()+0;

+-------------+

| CURTIME()+0 |

+-------------+

| 162404 |

+-------------+

1 row in set (0.06 sec)

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

Finding all column name of table

August 16, 2021January 13, 2015 by manish

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

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='social' 
    AND `TABLE_NAME`='user';

SELECT `COLUMN_NAME`

FROM `INFORMATION_SCHEMA`.`COLUMNS`

WHERE `TABLE_SCHEMA`='social'

AND `TABLE_NAME`='user';

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

June 3, 2020January 10, 2015 by bharti

To create table in mysql following syntax is used

  create Table table_ name(column _name1 data_ type,
                           column _name2 data_ type,
                           column_ name3 data type,
                                               ……..);

create Table table_ name(column _name1 data_ type,

column _name2 data_ type,

column_ name3 data type,

……..);

For example,

  create table student(id int,
                   first_name varchar(30),last_name varchar(30),
                   semester varchar(20),address varchar(80));

create table student(id int,

first_name varchar(30),last_name varchar(30),

semester varchar(20),address varchar(80));

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

    Query OK, 0 rows affected (1.44 sec)

1	Query OK, 0 rows affected (1.44 sec)

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

November 8, 2019July 30, 2014 by manish

To create a table

create table expense(
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
param varchar(50),
value decimal(15,2),
dt  date,
description varchar(20),
primary key(id))ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table expense(

id bigint(20) unsigned NOT NULL AUTO_INCREMENT,

param varchar(50),

value decimal(15,2),

dt date,

description varchar(20),

primary key(id))ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

alter table expense modify description varchar(100);

1	alter table expense modify description varchar(100);

Example

alter table expense modify description varchar(100);

1	alter table expense modify description varchar(100);

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

alter table expense modify description varchar(50);

1	alter table expense modify description varchar(50);

Setting innodb_lock_wait_timeout

August 16, 2021March 18, 2014 by manish

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

August 16, 2021March 18, 2014 by manish

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