mysql Archives - Page 3 of 3

MySql Update Table

January 28, 2023January 27, 2023 by bharti

To update records in a table we use update quey Syntax:

Update table_name set  column name=value, 
             column name=value.. where  column= value;

1 2	Update table_name set column name=value, column name=value.. where column= value;

First select the table data

mysql&gt; select * from student;
+----+-------------+-----------+-------+------+
| id | name        | address   | class | age  |
+----+-------------+-----------+-------+------+
|  1 | Ram Kumar   | New Delhi | 5th   |   10 |
|  2 | Mohan Kumar | New Delhi | 5th   |   10 |
|  3 | Sohan Kumar | Bhopal    | 5th   | NULL |
|  4 | Rohan Kumar | NULL      | 5th   |   11 |
+----+-------------+-----------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from student;

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

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

| 1 | Ram Kumar | New Delhi | 5th | 10 |

| 2 | Mohan Kumar | New Delhi | 5th | 10 |

| 4 | Rohan Kumar | NULL | 5th | 11 |

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

4 rows in set (0.00 sec)

by this query we get a table ‘student’ which we have created in our database as shown above. To update the above table, we use the query:

 update student set name='Rohan Pandey' where id=4;

1	update student set name='Rohan Pandey' where id=4;

we can see the updated table

mysql&gt; select * from student;
+----+--------------+-----------+-------+------+
| id | name         | address   | class | age  |
+----+--------------+-----------+-------+------+
|  1 | Ram Kumar    | New Delhi | 5th   |   10 |
|  2 | Mohan Kumar  | New Delhi | 5th   |   10 |
|  3 | Sohan Kumar  | Bhopal    | 5th   | NULL |
|  4 | Rohan Pandey | NULL      | 5th   |   11 |
+----+--------------+-----------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from student;

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

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

| 1 | Ram Kumar | New Delhi | 5th | 10 |

| 2 | Mohan Kumar | New Delhi | 5th | 10 |

| 4 | Rohan Pandey | NULL | 5th | 11 |

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

4 rows in set (0.00 sec)

If you … Read more

MySql Select Query

January 28, 2023January 27, 2023 by bharti

To select all records from a table we use following query Syntax

select * from table_name;

1	select * from table_name;

To select all row of student we use

select * from student;

1	select * from student;

It will show following result

mysql&gt;  select * from student;
+----+-------------+-----------+-------+------+
| id | name        | address   | class | age  |
+----+-------------+-----------+-------+------+
|  1 | Ram Kumar   | New Delhi | 5th   |   10 |
|  2 | Mohan Kumar | New Delhi | 5th   |   10 |
|  3 | Sohan Kumar | Bhopal    | 5th   | NULL |
|  4 | Rohan Kumar | NULL      | 5th   |   11 |
+----+-------------+-----------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from student;

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

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

| 1 | Ram Kumar | New Delhi | 5th | 10 |

| 2 | Mohan Kumar | New Delhi | 5th | 10 |

| 4 | Rohan Kumar | NULL | 5th | 11 |

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

4 rows in set (0.00 sec)

To select perticular column from table we use

select column1,column2, ..column n from table_name;

1	select column1,column2, ..column n from table_name;

If we want to select id and name of student the we will write

select id,name from student;

1	select id,name from student;

ther is no … Read more

MySql Create New Database

January 28, 2023January 27, 2023 by bharti

To create database use following query: Syntax

create database database_name;

1	create database database_name;

To create a database with name ebhor use following query

mysql&gt;create database ebhor;

1	mysql>create database ebhor;

This query will create a new database.

MySql Show all databases

January 28, 2023January 26, 2023 by manish

To see all the created databases we use show database query Syntax

show database;

1	show database;

Example

mysql&gt;show databases;

1	mysql>show databases;

It will show all the databases created in mysql.

Changing the default value of column in MySQL

January 21, 2023January 20, 2023 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.

Show mysql create table query

February 2, 2023January 2, 2023 by manish

To see created table structure show create table command is used Syntax

SHOW CREATE 
             TABLE TABLE_NAME;

1 2	SHOW CREATE TABLE TABLE_NAME;

Example

SHOW CREATE
           TABLE student;

1 2	SHOW CREATE TABLE student;

Result

+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------+
| Table    | Create Table|
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `first_name` varchar(30) DEFAULT NULL,
  `last_name` varchar(30) DEFAULT NULL,
  `semester` varchar(20) DEFAULT NULL,
  `address` varchar(80) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------+
1 row in set (0.17 sec)

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

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

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

| Table | Create Table|

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

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

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

| student | CREATE TABLE `student` (

`id` int(11) DEFAULT NULL,

`first_name` varchar(30) DEFAULT NULL,

`last_name` varchar(30) DEFAULT NULL,

`semester` varchar(20) DEFAULT NULL,

`address` varchar(80) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

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

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

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

1 row in set (0.17 sec)

To avoid showing leading and trailing dots we use \G with command syntax is as below Example

SHOW CREATE 
              TABLE student\G

1 2	SHOW CREATE TABLE student\G

Result *************************** 1. row *************************** Table: student Create Table: CREATE TABLE student ( id int(11) DEFAULT NULL, first_name varchar(30) DEFAULT … Read more

Set time zone in mysql

February 2, 2023January 2, 2023 by manish

To set Time zone in GMT set time_zone is used with specified time_zone value

      SET time_zone='+00:00';

1	SET time_zone='+00:00';

To check our change reflated on database use following query

       SELECT @@global.time_zone, @@session.time_zone;

1	SELECT @@global.time_zone, @@session.time_zone;

+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | +00:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

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

| @@global.time_zone | @@session.time_zone |

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

| SYSTEM | +00:00 |

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

1 row in set (0.00 sec)

Read More Setting innodb_lock_wait_timeout Granting Privileges to users Show engine innodb status Finding all column name of table