Updating a table fields from another table columns we can user following syntax
|
1 2 3 4 5 |
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
To update records in a table we use update quey Syntax:
|
1 2 |
Update table_name set column name=value, column name=value.. where column= value; |
First select the table data
|
1 2 3 4 5 6 7 8 9 10 |
mysql> 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) |
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:
|
1 |
update student set name='Rohan Pandey' where id=4; |
we can see the updated table
|
1 2 3 4 5 6 7 8 9 10 |
mysql> 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) |
If you … Read more
To select all records from a table we use following query Syntax
|
1 |
select * from table_name; |
To select all row of student we use
|
1 |
select * from student; |
It will show following result
|
1 2 3 4 5 6 7 8 9 10 |
mysql> 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) |
To select perticular column from table we use
|
1 |
select column1,column2, ..column n from table_name; |
If we want to select id and name of student the we will write
|
1 |
select id,name from student; |
ther is no … Read more
To create database use following query: Syntax
|
1 |
create database database_name; |
To create a database with name ebhor use following query
|
1 |
mysql>create database ebhor; |
This query will create a new database.
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
|
1 2 3 |
alter table table_name alter column_name set default default_value; |
Example
|
1 2 3 |
alter table student alter semester set default "first"; |
|
1 2 3 |
alter table student alter address set default "Bhopal"; |
above query will set default value for column_name to provided default value.
To see created table structure show create table command is used Syntax
|
1 2 |
SHOW CREATE TABLE TABLE_NAME; |
Example
|
1 2 |
SHOW CREATE TABLE student; |
Result
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
+----------+-------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------+ | 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
|
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
To set Time zone in GMT set time_zone is used with specified time_zone value
|
1 |
SET time_zone='+00:00'; |
To check our change reflated on database use following query
|
1 |
SELECT @@global.time_zone, @@session.time_zone; |
|
1 2 3 4 5 6 |
+--------------------+---------------------+ | @@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
