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
MySQLi Articles
Page 334 of 341
How 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 MoreWhich MySQL datatype to used to store an IP address?
We can store an IP address with the help of INT unsigned. While using INSERT, include INET_ATON() and with SELECT, include INET_NTOA(). IP address is in dotted format. Let us see an example. Creating a table. mysql> create table IPV4AddressDemo -> ( -> `IPV4Address` INT UNSIGNED -> ); Query OK, 0 rows affected (0.52 sec) Inserting IP address into the table, with INET_ATON. mysql> insert into IPV4AddressDemo values(INET_ATON("120.0.0.1")); Query OK, 1 row affected (0.17 sec) To display all records. mysql> select *from IPV4AddressDemo; The following ...
Read MorePass array to MySQL stored routine?
We need to create a stored procedure to display how to pass array to MySQL stored routine. Let us first create a table for our example. Creating a table mysql> create table FindDemo -> ( -> name varchar(100) -> ); Query OK, 0 rows affected (0.46 sec) Inserting some records into the table. mysql> insert into FindDemo values('John'), ('Smith'); Query OK, 2 rows affected (0.13 sec) Records: 2 Duplicates: 0 Warnings: 0 To display all records. mysql> select *from FindDemo; The following is ...
Read More