set @b:=0;
set @b1:=3;   #
#select distinct salary,@b:=@b+1 as Row_no from emp
select  * from emp where salary=(
select salary from (
select a.salary,@b:=@b+1 as row_no from (
select distinct salary from emp
) a) b
where b.row_no=@b1
) 

 

 

 Exists :Table2 id exists in table1 so it will fetch data from table1

select * from table1 a where exists  (select distinct  b.Emp_id from table2 b
where a.emp_id=b.Emp_id ) ;

Not exists : emp_id is not exists from table1 i will return only not exists in table2 

select * from table1 a where not exists (select distinct  b.Emp_id from table2 b
where a.emp_id=b.Emp_id ) ;

 Q. How to recover forget root password ?

if you forget root mysql password so If you want to recover mysql password & reset new password .

Step 1 : STOP mysql service 

#systemctl stop mysqld

Step 2: 

#mysqld_safe --skip-grant-tables &  

Step 3: Login in mysql

#mysql  -uroot    ( Enter )

Step 4: Reset new password 

#use mysql   (Change database)

# update USER  set password=password('NewPassword') where User='root' ;

#flush privileges;

#quit 

Step 6: Restart mysql server and resolve issue.

#systemctl  restart mysqld

Index : 1. Available metadata access methods (2). Structures of information_ schema (3). Using available commands to view metadata (4).Differences between SHOW statements and INFORMATION_SCHEMA tables (5).The MYSQL show client program (6).Using INFORMATION_SCHEMA queries to create shell commands and SQL statements

Metadata:

A database is structured collection of data but Metadata is just like index about the data MySql provides the facility  access to metadata for database , table and other object managed by the database server.

INFORMATION_SCHEMA is database default lunch with mysql instance,It is stores information
about all the other databases .It is store only read only tables.but it is actually views not base tables ,so there are no files associated with them , and you cannot set triggers on them. If you can use INFORMATION_SCHEMA as the default  database , you can only read the contens of tables not perform INSERT UPDATE etc operations .

SELECT   TABLE_NAME
 FROM     INFORMATION_SCHEMA.TABLES
     WHERE    TABLE_SCHEMA = 'information_schema'
     ORDER BY TABLE_NAME;

Table Information

• COLUMNS : Columns in tables and views

• ENGINES : Storage engines
• SCHEMATA: Databases
• TABLES :Tables in databases
• VIEWS :Views in databases

Partitioning

• PARTITIONS: Table partitions

• FILES: Files in which MySQL NDB disk data tables are stored

Privileges

• COLUMN_PRIVILEGES: Column privileges held by MySQL user accounts

• SCHEMA_PRIVILEGES: Database privileges held by MySQL user accounts
• TABLE_PRIVILEGES: Table privileges held by MySQL user accounts
• USER_PRIVILEGES: Global privileges held by MySQL user accounts

Character Set Support

• CHARACTER_SETS: Available character sets

• COLLATIONS: Collations for each character set
• COLLATION_CHARACTER_SET_APPLICABILITY: Collations that are applicable to a particular character set

Constraints and Indexes

• KEY_COLUMN_USAGE: Constraints on key columns

• REFERENTIAL_CONSTRAINTS: Foreign keys
• STATISTICS: Table indexes
• TABLE_CONSTRAINTS: Constraints on tables

Server Settings and Status

• KEY_COLUMN_USAGE: Constraints

• GLOBAL_STATUS: The status values for all connections to MySQL
• GLOBAL_VARIABLES: The values that are used for new connections to MySQL
• PLUGINS: Server plugins
• ROCESSLIST: Indicates which threads are running
• SESSION_STATUS: The status values for the current connection to MySQL
• SESSION_VARIABLES: The values that are in effect for the current connection to MySQL

Routines and Related Information

• EVENTS: Scheduled events

• ROUTINES: Stored procedures and functions
• TRIGGERS: Triggers in databases
• PARAMETERS: Stored procedure and function parameters, and stored functions

InnoDB

• INNODB_CMPand INNODB_CMP_RESET: Status on operations related to compressed InnoDB tables

• INNODB_CMPMEMand INNODB_CMPMEM_RESET: Status on compressed pages within the InnoDB buffer pool
• INNODB_LOCKS: Each lock that an InnoDB transaction has requested and holds
• INNODB_LOCK_WAITS: One or more row locks for each blocked InnoDB transaction
• INNODB_TRX: Every transaction currently executing inside InnoDB
• TABLESPACES: Active tablespaces

MySql Collection : is set of rules which are compare character in character set .

#SHOW COLLATION

# Collation, Charset, Id, Default, Compiled, Sortlen, Pad_attribute
   'armscii8_bin', 'armscii8', '64', '', 'Yes', '1', 'PAD SPACE'
    'armscii8_general_ci', 'armscii8', '32', 'Yes', 'Yes', '1', 'PAD SPACE'
    'ascii_bin', 'ascii', '65', '', 'Yes', '1', 'PAD SPACE'
    'ascii_general_ci', 'ascii', '11', 'Yes', 'Yes', '1', 'PAD SPACE'
    'big5_bin', 'big5', '84', '', 'Yes', '1', 'PAD SPACE'
    'big5_chinese_ci', 'big5', '1', 'Yes', 'Yes', '1', 'PAD SPACE'

Character set: is set of symbols and encoding techniques and it is set of rules for comparing character in Character set It is compare according to variety of collections.

# Charset, Description, Default collation, Maxlen
    'utf16', 'UTF-16 Unicode', 'utf16_general_ci', '4'
    'utf16le', 'UTF-16LE Unicode', 'utf16le_general_ci', '4'

Examples :

In latin1 each character is exactly one byte long. In utf8 a character can consist of more than one byte. Consequently utf8 has more characters than latin1 .

# Charset, Description, Default collation, Maxlen
    'latin1', 'cp1252 West European', 'latin1_swedish_ci', '1'
 

# How to set character set

Example:

MYSQL>CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8_unicode_ci; 

# How to Change character set

MYSQL> ALTER DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

SHOW & DESCRIBE SYNTEX : 

mysql> SHOW DATABASES; 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| test               | 
| world_innodb       | 
+--------------------+

MySQL> SHOW TABLES; 
MySQL> SHOW TABLES FROM mysql; 
MySQL> SHOW TABLES FROM INFORMATION_SCHEMA; 
MySQL> SHOW COLUMNS FROM CountryLanguage; 
MySQL> SHOW FULL COLUMNS FROM CountryLanguage\G

Additional SHOW Statement Examples

1. SHOW with LIKE and WHERE:

MySQL> SHOW DATABASES LIKE 'm%'; 
MySQL> SHOW COLUMNS FROM Country 
       WHERE `Default` IS NULL; 

2. Other SHOW statements:

MySQL> SHOW INDEX FROM City\G 
MySQL> SHOW CHARACTER SET; 
MySQL> SHOW COLLATION;

DESCRIBE

DESCRIBEcan be abbreviated as DESC, as follows;

mysql> DESCRIBE table_name; 
and 
mysql> DESC table_name; 

Example below shows INFORMATION_SCHEMA table information

MySQL> DESCRIBE INFORMATION_SCHEMA.CHARACTER_SETS; 
+----------------------+-------------+------+-----+---------+-------+ 
| Field                | Type        | Null | Key | Default | Extra | 
+----------------------+-------------+------+-----+---------+-------+ 
| CHARACTER_SET_NAME   | varchar(64) | NO   |     |         |       | 
| DEFAULT_COLLATE_NAME | varchar(64) | NO   |     |         |       | 
| DESCRIPTION          | varchar(60) | NO   |     |         |       | 
| MAXLEN               | bigint(3)   | NO   |     | 0       |       | 
+----------------------+-------------+------+-----+---------+-------+

The following is equivalent to the above DESCRIBE/DESC examples:

mysql> SHOW COLUMNS FROM table_name; 

mysqlshow: Examples

You can show information for all databases, or for a specific database, table, and/or column using the mysqlshow query. For example:

shell> mysqlshow -u -p 
+--------------------+ 
|     Databases      | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| test               | 
| world_innodb       | 
+--------------------+

shell> mysqlshow world_innodb 
shell> mysqlshow world_innodb City 
shell> mysqlshow world_innodb City CountryCode 
shell> mysqlshow "w%"

 

We are taking backup only function & store procedure from mysql database.

[root@VMs]# mysqldump  -u db_user -p -h 192.168.101.51 --routines --column_statistics=0 --no-create-info --no-data --no-create-db --skip-opt mydb > mydb_live_proc_funaction.sql

 Table is not found in Database. How to skip table is not exist in database.

mysqldump  -u db_user -p -h 192.168.xx.xx  --routines --column_statistics=0       --no-create-info --no-data --no-create-db --skip-opt  --ignore-table=test.audit  --ignore-table=test.audit_old test --ignore-table=test.blog > test_db_live_proc_funaction_56.sql
Enter password:

I have one table Student .So i want to fetch data according to percentage 

and give remark according to position .

set @no=0 ;
select
@no:=@no+1 as S_No
,name,percentage,
 CASE  WHEN  percentage >= 90 Then  'excellent'
 WHEN percentage >=60  then 'first'
 WHEN percentage >=59  then 'second'
 When percentage >=35  then 'third'
 else 'Fail' end as Remark
 from student