Updating a table fields from another table columns we can user following syntax
1
2
3
4
5
Update table1 inner join table2
ona.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 following fields user(id,fname,lname,mobileno,address,); personal(id,fname,lname,mobile_no);
Here c=consider user and person have common id then to update user from person we can use following query
1
2
3
4
5
6
7
update user asu
inner join personp
on
u.id=p.id
setu.fname=p.fname,
u.lname=p.lname,
u.mobile_no=p.mobno;
This will update user table with person tables fields.
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;
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.
if table created without any error then it will show following statement
Result:
1
Query OK,0rows affected(1.44sec)
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
1
2
3
4
5
6
7
8
9
10
11
12
13
createtablestudent(
idbigint(20)unsignedNOT NULLAUTO_INCREMENT,
fnamevarchar(100),
lnamevarchar(30),
addressvarchar(80),
mobile_novarchar(12),
email_idvarchar(100),
cityvarchar(50),
add_datetimestampDEFAULTCURRENT_TIMESTAMP,
primarykey(id),
uniquekey(mobile_no),
uniquekey(email_id)
)ENGINE=InnoDBDEFAULTCHARSET=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
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 this value as integer from 1 to 1073741824 by default its value is 50.
You can check default value of innodb-lock-wait-timeout by query
show variables like 'innodb_lock_wait_timeout';
OR
SHOW GLOBAL VARIABLES LIKE '%INNODB_LOCK_WAIT_TIMEOUT%';
OR
SELECT @@innodb_lock_wait_timeout;
All shows the default value.
Property Value for innodb_lock_wait_timeout
Property
Value
Command-Line Format
--innodb-lock-wait-timeout=#
System Variable
innodb-lock-wait-timeout
Scope
Global, Session
Dynamic
Yes
Type
Integer
Default Value
50
Minimum Value
1
Maximum Value
1073741824
Show affected tables
Get affected tables you can use following query
SHOW ENGINE INNODB STATUS\G
It shows locking information
When to increase or Decrease innodb-lock-wait-timeout value ?
You can descrese innodb_lock_wait_timeout for OLTP or High interactive systems.
If you have any high time consuming operation long queries in Data-ware house then you can increase its time
A transaction that tries to access a row that is locked by another InnoDB transaction waits at most this many seconds for write access to the row before issuing the following error:
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: