mysql Archives - Page 2 of 3

Changing primary key of table

February 2, 2023February 2, 2023 by manish

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 … Read more

Alter table command to add foreign key

February 2, 2023February 2, 2023 by manish

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 … Read more

Drop foreign key constraints

February 2, 2023February 2, 2023 by manish

Some times we have to drop the foreign key constraint that already exists, for that we have to first get the foreign key constraint name. To see detailed description of created table we use following query.

SHOW CREATE TABLE location;

1	SHOW CREATE TABLE location;

Result of above query is

mysql> SHOW CREATE TABLE location;
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----+
| Table    | Create Table    |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----+
| location | CREATE TABLE `location` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `company_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `company_fk_id` (`company_id`),
  CONSTRAINT `company_fk_id` FOREIGN KEY (`company_id`) REFERENCES `company` (`i
d`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----+
1 row in set (0.08 sec)

mysql> SHOW CREATE TABLE location;

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

--------------------------------------------------------------------------------

-----+

| Table | Create Table |

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

--------------------------------------------------------------------------------

-----+

| location | CREATE TABLE `location` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(50) DEFAULT NULL,

`address` varchar(200) DEFAULT NULL,

`company_id` int(11) NOT NULL,

PRIMARY KEY (`id`),

KEY `company_fk_id` (`company_id`),

CONSTRAINT `company_fk_id` FOREIGN KEY (`company_id`) REFERENCES `company` (`i

d`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

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

--------------------------------------------------------------------------------

-----+

1 row in set (0.08 sec)

Here we can see that company_id column is referring company tables id … Read more

Show engine innodb status

February 2, 2023February 2, 2023 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

updating user login password

February 4, 2023February 2, 2023 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;

Updating the column data size

February 2, 2023February 2, 2023 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);

Insertion of a new record in a table

February 2, 2023February 2, 2023 by bharti

To insert a new record in a table we use Insert Query. Syntax: To insert all fields value in table we use following insert query

INSERT INTO Table_name VALUES
(value1, value2,....... value n);

1 2	INSERT INTO Table_name VALUES (value1, value2,....... value n);

To insert all or selected fields value in table we use following insert query

INSERT INTO Table_name(field1,field2,...field n) VALUES
(value1, value2,....... value n);

1 2	INSERT INTO Table_name(field1,field2,...field n) VALUES (value1, value2,....... value n);

We have created a table student as below

CREATE TABLE student(id int(10) NOT NULL AUTO_INCREMENT, 
     name varchar(50),address varchar(50), class varchar(30),age int(2),
    PRIMARY KEY(ID));

CREATE TABLE student(id int(10) NOT NULL AUTO_INCREMENT,

name varchar(50),address varchar(50), class varchar(30),age int(2),

PRIMARY KEY(ID));

To insert record in above … Read more

MySql Update table column from another table colunm

February 1, 2023February 1, 2023 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

MySql Select Database

January 29, 2023January 28, 2023 by manish

Suppose you have created database, to use it , write query: Syntax

   use database_name;

1	use database_name;

If we want to select ebhor database then use following query

mysq&gt;use ebhor;

1	mysq>use ebhor;

with the help of this query you can work on your created database.