| Refresh | This website www.w3schools.blog/category/mysql/page/2 is currently offline. Cloudflare's Always Online™ shows a snapshot of this web page from the Internet Archive's Wayback Machine. To check for the live version, click Refresh. |
Find All Tables in MySQL With Specific Column Names in specific schema. SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN (‘columnName1′,’columnName2′, columnNameN) AND TABLE_SCHEMA=’Database Name’; Find All Tables in MySQL With Specific Column Names in all schemas. SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN (‘columnName1′,’columnName2’, columnNameN)
Get all Column of a specific Tables in MySQL. SELECT table_name as ‘Table Name’, column_name as ‘Column Name’ FROM information_schema.COLUMNS WHERE table_schema = ‘$DATABASE_NAME’; AND table_name = ‘$TABLE_NAME’; Get all Column of some Tables in MySQL. SELECT table_name as ‘Table Name’, column_name as ‘Column Name’ FROM information_schema.COLUMNS WHERE table_schema = ‘$DATABASE_NAME’; AND table_name in (‘$TABLE_NAME1’, … Read more
Get all Tables of a specific schema in MySQL. SELECT table_name as ‘Table Name’ FROM information_schema.TABLES WHERE table_schema = ‘$DATABASE_NAME’; Get all tables of all schemas in MySQL. SELECT table_name as ‘Table Name’ FROM information_schema.TABLES;
Get Record Count of a specific Table in MySQL. SELECT SUM(TABLE_ROWS) as ‘Total Record Count’ FROM information_schema.TABLES WHERE table_schema = ‘$DATABASE_NAME’ AND table_name = ‘$TABLE_NAME’; Get Record Count of all Tables of a specific schema in MySQL. SELECT SUM(TABLE_ROWS) as ‘Total Record Count’ FROM information_schema.TABLES WHERE table_schema = ‘$DATABASE_NAME’; Get Record Count of all Tables … Read more
Get size of a specific table in MySQL. SELECT table_name AS `Table_Name`, round(((data_length + index_length) / 1024 / 1024), 2) `Table Size in MB` FROM information_schema.TABLES WHERE table_schema = ‘$DATABASE_NAME’ AND table_name = ‘$TABLE_NAME’; Get size of all tables of a specific schema in MySQL. SELECT table_name AS `Table_Name`, round(((data_length + index_length) / 1024 / … Read more
First, execute below command to see all active processes. SHOW PROCESSLIST; Use below command to kill a specific process. Syntax: KILL id; Example KILL 5; Use below command to kill all processes. SELECT CONCAT(‘KILL ‘,id,’;’) FROM information_schema.processlist WHERE user=’root’ INTO OUTFILE ‘/tmp/processlist.txt’; Use below command to kill all processes of a user. kill USER username;
MySQL Data Types A data type specifies the type of data to be stored, the possible values, the type of operations that can be performed, and the way to process that data. A data type can vary from integer, floating point, boolean, etc., to many more and thus are divided into various categories. Numeric Data … Read more
CREATE TABLE In MySQL, the MySQL CREATE TABLE statement is used to create a new table in a database. Syntax 1: To create a Table in MySQL. CREATE TABLE table_name ( column_1 data_type column_constraint, column_2 data_type column_constraint, … ); Syntax 2: To see all the already created Tables in a database. SHOW tables; Syntax 3: … Read more
CREATE DATABASE in MySQL To create a MySQL database, open the MySQL Command Line Client. The MySQL Command Line Client will appear with a mysql> prompt. Write the already set password, and you are ready to create a MySQL database. Syntax: CREATE DATABASE name; Parameters: name: It is used to specify the name of the … Read more
UNHEX() FUNCTION The MySQL UNHEX function is used to convert a hexadecimal value to a string. Syntax: UNHEX (value); Parameters: value: It is used to specify the hexadecimal value to convert. Example: mysql> SELECT UNHEX (‘41’); Output: ‘A’ Explanation: The string for the specified hexadecimal value is returned.