mysql Archives - Ebhor.com

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

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 value for column that have default value that its default value will be inserted for example

mysql> insert into product(name) values('Mobile');

1	mysql> insert into product(name) values('Mobile');

We have inserted only name of product and value of quantity,price,status and add_date is assigned as default value.

mysql> select * from product;
+----+--------+----------+-------+-----------+---------------------+
| id | name   | quantity | price | status    | add_date            |
+----+--------+----------+-------+-----------+---------------------+
|  1 | Mobile |      100 | 10.22 | AVAILABLE | 2017-04-05 17:10:42 |
+----+--------+----------+-------+-----------+---------------------+
1 row in set (0.00 sec)

mysql> select * from product;

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

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

| 1 | Mobile | 100 | 10.22 | AVAILABLE | 2017-04-05 17:10:42 |

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

1 row in set (0.00 sec)

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

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.

mysql>  SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2017-04-05 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT CURDATE();

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

| CURDATE() |

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

| 2017-04-05 |

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

1 row in set (0.00 sec)

to convert string in to number we use following query

mysql>  SELECT CURDATE()+0;
+-------------+
| CURDATE()+0 |
+-------------+
|    20170405 |
+-------------+
1 row in set (0.06 sec)

mysql> SELECT CURDATE()+0;

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

| CURDATE()+0 |

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

| 20170405 |

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

1 row in set (0.06 sec)

To select current data and time we use NOW() whch returns data and time in ‘YYYY:MM:DD HH:MM:SS’ in string format or YYYYMMDDHHMMSS is numeric format.

mysql> select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2017-04-05 16:32:54 |
+---------------------+
1 row in set (0.00 sec)

mysql> select NOW();

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

| NOW() |

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

| 2017-04-05 16:32:54 |

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

1 row in set (0.00 sec)

mysql> select NOW()+0;
+----------------+
| NOW()+0        |
+----------------+
| 20170405163336 |
+----------------+
1 row in set (0.00 sec)

mysql> select NOW()+0;

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

| NOW()+0 |

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

| 20170405163336 |

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

1 row in set (0.00 sec)

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

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.

Mysql create table statement

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 primary key

create table student(
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
fname varchar(100),
lname varchar(30),
address varchar(80),
mobile_no varchar(12),
email_id varchar(100),
city varchar(50),
add_date timestamp DEFAULT CURRENT_TIMESTAMP,
primary key(id),
unique key(mobile_no),
unique key(email_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table student(

id bigint(20) unsigned NOT NULL AUTO_INCREMENT,

fname varchar(100),

lname varchar(30),

address varchar(80),

mobile_no varchar(12),

email_id varchar(100),

city varchar(50),

add_date timestamp DEFAULT CURRENT_TIMESTAMP,

primary key(id),

unique key(mobile_no),

unique key(email_id)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Above mysql create statement create a student table with primary key id,
with auto increment field value and two unique keys mobile_no and email_id,
at the time of creating user add_date is added CURRENT_TIMESTAMP as default value.

This table also uses mysql create table auto_increment

MySql create table if not exists

Syntax of create table if not exist is
CREATE TABLE IF NOT EXISTS table_name(............);

To modity the student table with CREATE TABLE IF NOT EXISTS the query is as below

CREATE TABLE IF NOT EXISTS student(
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
fname varchar(100),
lname varchar(30),
address varchar(80),
mobile_no varchar(12),
email_id varchar(100),
city varchar(50),
add_date timestamp DEFAULT CURRENT_TIMESTAMP,
primary key(id),
unique key(mobile_no),
unique key(email_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS student(

id bigint(20) unsigned NOT NULL AUTO_INCREMENT,

fname varchar(100),

lname varchar(30),

address varchar(80),

mobile_no varchar(12),

email_id varchar(100),

city varchar(50),

add_date timestamp DEFAULT CURRENT_TIMESTAMP,

primary key(id),

unique key(mobile_no),

unique key(email_id)

)ENGINE=InnoDB DEFAULT CHARSET=utf8

Changing the default value for column

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.

Updating the column data size

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);

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 statement.

There are several types of InnoDB Monitors:

The standard InnoDB Monitor displays the following types of information:

Table and record locks held by each active transaction
Lock waits of a transactions
Semaphore waits of threads
Pending file I/O requests
Buffer pool statistics
Purge and insert buffer merge activity of the main InnoDB thread

To enable the standard InnoDB Monitor for periodic output, create a table named innodb_monitor. To obtain Monitor output on demand, use the SHOW ENGINE INNODB STATUS SQL statement to fetch the output to your client program. If you are using the mysql interactive client, the output is more readable if you replace the usual semicolon statement terminator with \G:

     SHOW ENGINE INNODB STATUS\G

1	SHOW ENGINE INNODB STATUS\G

Refrence: Link

Changing primary key of table

We have a table basic profile with following table structure

create table basic_profile(b_id int(10) NOT NULL AUTO_INCREMENT,
                           id1 int(10),
                           id2 int(10),
                          fname varchar(30),
                         lname varchar(30),
                         mailid varchar(50),
                         address varchar(100),
                        primary key(b_id));

create table basic_profile(b_id int(10) NOT NULL AUTO_INCREMENT,

id1 int(10),

id2 int(10),

fname varchar(30),

lname varchar(30),

mailid varchar(50),

address varchar(100),

primary key(b_id));

mysql> desc basic_profile;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| b_id    | int(10)      | NO   | PRI | NULL    | auto_increment |
| id1     | int(10)      | YES  |     | NULL    |                |
| id2     | int(10)      | YES  |     | NULL    |                |
| fname   | varchar(30)  | YES  |     | NULL    |                |
| lname   | varchar(30)  | YES  |     | NULL    |                |
| mailid  | varchar(50)  | YES  |     | NULL    |                |
| address | varchar(100) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
7 rows in set (0.06 sec)

mysql> desc basic_profile;

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

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

| b_id | int(10) | NO | PRI | NULL | auto_increment |

| id1 | int(10) | YES | | NULL | |

| id2 | int(10) | YES | | NULL | |

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

7 rows in set (0.06 sec)

we don’t want to use it as primary key so first we removed its auto increment the we will remove it from primary key
queries for these are

//remove auto increment
ALTER TABLE basic_profile MODIFY b_id INT NOT NULL;

//remove from primary key
//ALTER TABLE TABLE_NAME DROP PRIMARY KEY
ALTER TABLE basic_profile DROP PRIMARY KEY;

//remove auto increment

ALTER TABLE basic_profile MODIFY b_id INT NOT NULL;

//remove from primary key

//ALTER TABLE TABLE_NAME DROP PRIMARY KEY

ALTER TABLE basic_profile DROP PRIMARY KEY;

If you want to make primary key to some other column than we can use following query

ALTER TABLE basic_profile MODIFY id1 INT NOT NULL PRIMARY KEY ;

1	ALTER TABLE basic_profile MODIFY id1 INT NOT NULL PRIMARY KEY ;

above query will not make id1 as auto increment.
to make it a uto incremente we will execute following query insted of above

ALTER TABLE basic_profile MODIFY id1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT;

1	ALTER TABLE basic_profile MODIFY id1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT;

Now let us check the table description

mysql> desc basic_profile;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| b_id    | int(11)      | NO   |     | NULL    |                |
| id1     | int(11)      | NO   | PRI | NULL    | auto_increment |
| id2     | int(10)      | YES  |     | NULL    |                |
| fname   | varchar(30)  | YES  |     | NULL    |                |
| lname   | varchar(30)  | YES  |     | NULL    |                |
| mailid  | varchar(50)  | YES  |     | NULL    |                |
| address | varchar(100) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
7 rows in set (0.06 sec)

mysql> desc basic_profile;

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

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

| b_id | int(11) | NO | | NULL | |

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

| id2 | int(10) | YES | | NULL | |

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

7 rows in set (0.06 sec)

We can also make composite primary key in existing table for that we use following query

ALTER TABLE basic_profile ADD PRIMARY KEY(id1,id2)

1	ALTER TABLE basic_profile ADD PRIMARY KEY(id1,id2)

This will make id1 and id2 primary key of table.

Alter table command to add foreign key

We have two tables company and location. First table manages company details and second table stores location detail of company. one company can span in multiple location.
Initially we have created table for company and location as below.

create table company(id int(11) NOT NULL AUTO_INCREMENT,
                     name varchar(50),
                    description varchar(200),
                  PRIMARY KEY(id));

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

name varchar(50),

description varchar(200),

PRIMARY KEY(id));

create table location(id int(11) NOT NULL AUTO_INCREMENT,
                     name varchar(50),
                    address varchar(200),
                   PRIMARY KEY(id));

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

name varchar(50),

address varchar(200),

PRIMARY KEY(id));

checking the description of both table

mysql> desc company;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(50)  | YES  |     | NULL    |                |
| description | varchar(200) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
3 rows in set (0.09 sec)

mysql> desc company;

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

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

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

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

3 rows in set (0.09 sec)

mysql> desc location;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(50)  | YES  |     | NULL    |                |
| address | varchar(200) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.06 sec)

mysql> desc location;

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

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

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

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

3 rows in set (0.06 sec)

Now We find that one company can exists in multiple location so there is one to many relationship between company to location and to access location detail of company we have to create foreign key reference of company to location table.
We have already create location table so without deleted it again we will alter table to add foreign key refrence of company.
For adding new foreign key column we have to first add a column to location table then we will make it foreign key.
Before proceeding lets see the syntax of alter table command

Syntax

ALTER TABLEL table2
  add CONSTRAINT constraint_name
  FOREIGN KEY (column_name)
  REFERENCES table1(index_column)
  ON UPDATE reference_option
  ON DELETE reference_option

ALTER TABLEL table2

add CONSTRAINT constraint_name

FOREIGN KEY (column_name)

REFERENCES table1(index_column)

ON UPDATE reference_option

ON DELETE reference_option

//adding new column
//
ALTER TABLE location add company_id INT(11)   NOT NULL;

//making company_id column as foreign key
ALTER TABLE location
  add CONSTRAINT company_fk_id 
  FOREIGN KEY (company_id) 
  REFERENCES company(id)
  ON UPDATE CASCADE
  ON DELETE CASCADE;

//adding new column

ALTER TABLE location add company_id INT(11) NOT NULL;

//making company_id column as foreign key

ALTER TABLE location

add CONSTRAINT company_fk_id

FOREIGN KEY (company_id)

REFERENCES company(id)

ON UPDATE CASCADE

ON DELETE CASCADE;

Checking location description you will find that a company_id field is added to location table and it is reffeering to company tables

mysql> desc location;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(50)  | YES  |     | NULL    |                |
| address    | varchar(200) | YES  |     | NULL    |                |
| company_id | int(11)      | NO   | MUL | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc location;

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

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

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

| company_id | int(11) | NO | MUL | NULL | |

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

4 rows in set (0.00 sec)

To show detailed description you can use

SHOW CREATE TABLE location

1	SHOW CREATE TABLE location