Knowledge Never Sets
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.
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 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 as u inner join person p on u.id=p.id set u.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.
Syntax for adding default value is
1 | column_name datatype DEFAULT value; |
for example
1 2 3 4 5 6 7 | 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
1 2 3 4 5 6 7 8 9 10 11 12 | 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) |
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
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.
1 2 3 4 5 6 7 | 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) |
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
1 2 3 4 5 6 7 8 9 | mysql>SELECT CURTIME(); +-----------+ | CURTIME() | +-----------+ | 16:06:23 | +-----------+ 1 row in set (0.06 sec) |
below is HHMMSS in numeric format
1 2 3 4 5 6 7 | 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.
1 2 3 4 5 6 7 | mysql> SELECT CURDATE(); +------------+ | CURDATE() | +------------+ | 2017-04-05 | +------------+ 1 row in set (0.00 sec) |
to convert string in to number we use following query
1 2 3 4 5 6 7 | 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.
1 2 3 4 5 6 7 | mysql> select NOW(); +---------------------+ | NOW() | +---------------------+ | 2017-04-05 16:32:54 | +---------------------+ 1 row in set (0.00 sec) |
1 2 3 4 5 6 7 | mysql> select NOW()+0; +----------------+ | NOW()+0 | +----------------+ | 20170405163336 | +----------------+ 1 row in set (0.00 sec) |
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
1 2 3 4 | 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.
To create table in mysql following syntax is used
1 2 3 4 | create Table table_ name(column _name1 data_ type, column _name2 data_ type, column_ name3 data type, ……..); |
For example,
1 2 3 | 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:
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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
Syntax of create table if not exist isCREATE TABLE IF NOT EXISTS table_name(............);
To modity the student table with CREATE TABLE IF NOT EXISTS the query is as below
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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 |
To create a table
1 2 3 4 5 6 7 | 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
1 | alter table expense modify description varchar(100); |
Example
1 | alter table expense modify description varchar(100); |
we can also decrease the size of column but it may cause the data loss.
like
1 | alter table expense modify description varchar(50); |
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 |
|---|---|
| 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 |
Get affected tables you can use following query
SHOW ENGINE INNODB STATUS\G
It shows locking information
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:
1 | ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
When a lock wait timeout occurs, the current statement is rolled back (not the entire transaction).
to show default value of innodb_lock_wait_timeout we use
1 | show variables like 'innodb_lock_wait_timeout'; |
innodb_lock_wait_timeout can be set at run time with global and session scope
1 2 3 4 5 6 | SET GLOBAL innodb_lock_wait_timeout = 100; SELECT @@innodb_lock_wait_timeout; SET SESSION innodb_lock_wait_timeout = 100; SELECT @@innodb_lock_wait_timeout; |
SELECT @@innodb_lock_wait_timeout;
will show innodb_lock_wait_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:
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:
1 | SHOW ENGINE INNODB STATUS\G |
Refrence: Link