We can achieve possible enum value with the help of ‘enum’. The following is the syntax. yourColumnName ENUM(value1, value2, ........................N) Let us first create a table. mysql> create table EnumDemo -> ( -> Light int, -> IsONOrOff ENUM('ON', 'OFF') -> ); Query OK, 0 rows affected (1.06 sec) In the above table, ENUM is used to get the possible value for “Light”. Inserting records. mysql> insert into EnumDemo values(1, 'ON'), (0, 'OFF'); Query OK, 2 rows affected (0.24 sec) Records: 2 Duplicates: ... Read More

We can quit/ exit from MySQL stored procedure with the help of the LEAVE command. The following is the syntax. Leave yourLabelName; The following is an example. Here, we are creating a new procedure. mysql> delimiter // mysql> CREATE PROCEDURE ExitQuitDemo2(IN Var1 VARCHAR(20)) -> proc_Exit:BEGIN -> IF Var1 IS NULL THEN -> LEAVE proc_Exit; -> END IF; -> END // Query OK, 0 rows affected (0.16 sec) Above, we have set the following LEAVE command to exit from the procedure. If ... Read More

We can restore the MySQL root user full privileges with the help of UPDATE command. Firstly, you need to stop mysqld and restart it with the --skip-grant-tables option. After that, connect to the mysqld server with only mysql (i.e. no -p option, and username may not be required). Issue the below given command in the mysql client to restore the MySQL root user with full privileges. mysql> UPDATE mysql.user SET Grant_priv = 'Y', Super_priv = 'Y' WHERE User = 'root'; Query OK, 0 rows affected (0.04 sec) Rows matched: 1 Changed: 0 Warnings: 0 Above, ... Read More

The repair of MySQL tables is only applicable to MyISAM engine type, not for InnoDB. Therefore, we need to change the Engine type to MyISAM. The following is an example. Creating a table mysql> create table RepairTableDemo -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.51 sec) To convert this table to engine type MyISAM, use ALTER. mysql> ALTER TABLE RepairTableDemo ENGINE = MyISAM; Query OK, 0 rows affected (1.14 sec) Records: 0 Duplicates: 0 Warnings: ... Read More

Union is a type of operator in MySQL. We can use ORDER BY with this to filter records. Use UNION if you want to select rows one after the other from several tables or several sets of rows from a single table all as a single result set. Let us see an example. Creating first table mysql> create table UnionDemo1 -> ( -> id int -> ); Query OK, 0 rows affected (0.59 sec) Inserting records into first table. mysql> insert into UnionDemo1 values(1), (4), (10); Query OK, ... Read More

To create a column with ‘false’ as the default value, we can use the concept of “default” at the time of creation of the table. Note − 0 represents false and 1 represents true. Creating a table using “default” false. mysql> create table TrueFalseTable -> ( -> Adult boolean default false -> ); Query OK, 0 rows affected (0.65 sec) Inserting records with no value, since we have set “default” above. mysql> insert into TrueFalseTable values(); Query OK, 1 row affected (0.16 sec) To display records. ... Read More

To count the total number of tables, use the concept of count(*) with table_schema. First, to check how many tables are present in our database "business", we need to use the 'show' command. mysql> show tables; The following is the output that displays all the tables in the database "business". +--------------------------+ | Tables_in_business | +--------------------------+ | addcheckconstraintdemo | | addcolumntable | | addconstraintdemo | | addnotnulldemo ... Read More

The statement select 1 from any table name means that it returns only 1. For example, If any table has 4 records then it will return 1 four times. Let us see an example. Firstly, we will create a table using the CREATE command. mysql> create table StudentTable -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.51 sec) Inserting records mysql> insert into StudentTable values(1, 'John'), (2, 'Carol'), (3, 'Smith'), (4, 'Bob'); Query OK, 4 rows affected (0.21 ... Read More

To get a list of MySQL views, we can use the SELECT command with LIKE operator. Let us see the syntax first. mysql> SELECT TABLE_SCHEMA, TABLE_NAME -> FROM information_schema.tables -> WHERE TABLE_TYPE LIKE 'VIEW'; The following is the output that displays the total number of views. +--------------+-----------------------------------------------+ | TABLE_SCHEMA | TABLE_NAME | +--------------+-----------------------------------------------+ | sys ... Read More

MySQL provides many functions that give the current connection information. For instance, to know about the current user, use the user() function. Syntax mysql> SELECT CURRENT_USER(); Here is the output that displays the name of the current user. +----------------+ | CURRENT_USER() | +----------------+ | root@% | +----------------+ 1 row in set (0.00 sec) In the above, % tells us about localhost. To check the current connection id, use the following method − mysql> SELECT CONNECTION_ID(); The following is the output that shows the current connection id. ... Read More