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

for example

this will produce following output

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

We have inserted only name of product and value of quantity,price,status and add_date is assigned as default value.

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

below is HHMMSS in numeric format

To select current data we use CURDATE().It returns data in YYYY-MM-DD string format.

to convert string in to number we use following query

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.

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

Above query will fetch all columns of user table available in social 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

Example

above query will set default value for column_name to provided default value.

To create a table

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

Example

we can also decrease the size of column but it may cause the data loss.
like

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:

1. Table and record locks held by each active transaction
2. Lock waits of a transactions
3. Semaphore waits of threads
4. Pending file I/O requests
5. Buffer pool statistics
6. 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:

Refrence: Link

We have a table basic profile with following table structure

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

If you want to make primary key to some other column than we can use following query

above query will not make id1 as auto increment.
to make it a uto incremente we will execute following query insted of above

Now let us check the table description

We can also make composite primary key in existing table for that we use following query

This will make id1 and id2 primary key of table.