Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
MySQL Articles
Page 347 of 355
How to get a list of MySQL views?
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 MoreHow to repair MySQL tables from the command line?
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 MoreHow can I restore the MySQL root user full privileges?
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 MoreHow to quit/ exit from MySQL stored procedure?
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 MoreMySQL select query to select rows from a table that are not in another table?
For our example, we will create two tables and apply Natural Left Join to get the rows from a table not present in the second table. Creating the first table. mysql> create table FirstTableDemo -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.48 sec) Inserting records into first table. mysql> insert into FirstTableDemo values(1, 'Bob'), (2, 'John'), (3, 'Carol'); Query OK, 3 rows affected (0.13 sec) Records: 3 Duplicates: 0 Warnings: 0 To display all ...
Read MoreHow to remove all non-alphanumeric characters from a string in MySQL?
Non-alphanumeric characters are as follows − @, !, #, &, (), ?, / There is no inbuilt function to remove non-alphanumeric characters from a string in MySQL. Therefore, we create a function which removes all non-alphanumeric characters. The function declaration and definition is as follows. mysql> delimiter // mysql> CREATE FUNCTION RemoveNonAlphaNumeric( s CHAR(255) ) RETURNS CHAR(255) DETERMINISTIC -> BEGIN -> DECLARE var1, length SMALLINT DEFAULT 1; -> DECLARE result CHAR(255) DEFAULT ''; -> DECLARE ch CHAR(1); ...
Read MoreWhy do integers in database row tuple have an 'L' suffix in MySQL?
The ‘L’ suffix concept in MySQL can be related with Python. In Python 2, the long integer literal is suffixed with L or l, but int and long have been binded into int in version 3. Therefore, there is no need for L or l. Adding large numbers in Python Version 3.7 (Python 3), without using any suffix. Here, if we suffix L or l, Python 3 gives an error. However, Python Version 2 suffixed with L or l will not give an error. The following is the output with no error. Hence, Python int is ...
Read MoreWhat is the maximum length of a table name in MySQL?
The maximum length of a table name is 64 characters long according to MySQl version 8.0.12. Check your installed MySQL version. mysql> select version(); The following is the output. +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.03 sec) We can check the maximum length of the table name at the time of creating it. If we give more than 64 characters, then it will not create a table and an error is thrown. Creating a table which has more than 64 characters of table name. mysql> ...
Read MoreWhat is difference between Boolean and tinyint(1) in MySQL?
The basic difference between Boolean and tinyint(1) is only in the naming convention. If we say that we need true or false values then Boolean comes to our mind, instead of tinyint(1). These data types are synonyms. It is up to us which data type we want to use- values can be 1 and 0 or true and false. The following is an example. Creating a table with Boolean data type. mysql> create table BooleanDemo -> ( -> Light Boolean -> ); Query OK, 0 rows affected (0.52 sec) ...
Read MoreWhat is cardinality in MySQL?
In MySQL, the term cardinality refers to the uniqueness of data values that can be put into columns. It is a kind of property which influences the ability to search, cluster and sort data. Cardinality can be of two types which are as follows − Low Cardinality − All values for a column must be same. High Cardinality − All values for a column must be unique. The concept of high cardinality is used if we put a constraint on a column in order to restrict duplicate values. High Cardinality The following is an example of High Cardinality, ...
Read More