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
-
Economics & Finance
MySQL Articles
Page 64 of 355
What is unsigned in MySQL?
Unsigned allows us to enter positive value; you cannot give any negative number. Let us create a table to understand unsigned in MySQL. To create a table, we will use the CREATE command.Let us create a table −mysql> CREATE table UnsignedDemo -> ( -> id int unsigned -> ); Query OK, 0 rows affected (0.61 sec)After that I will insert only positive values. Let us insert some records −mysql> INSERT into UnsignedDemo values(124); Query OK, 1 row affected (0.09 sec) mysql> INSERT into UnsignedDemo values(78967); Query OK, 1 row affected (0.14 sec)I am displaying all the records with the ...
Read MoreHow to enable MySQL Query Log?
To enable query log, use the command SET global. You cannot use set general_log in MySQL version 8.0.12 version. If you are using version 8.0.12, then you will get the below given error. Here, we are trying to set general_log to see what error will come −mysql> SET general_log = 1;The following is the output −ERROR 1229 (HY000): Variable 'general_log' is a GLOBAL variable and should be set with SET GLOBAL Now, we will use the global command with general_log. The query is as follows −mysql> SET global general_log = 1; Query OK, 0 rows affected (0.10 sec)To check it ...
Read MoreSELECT DISTINCT vs GROUP BY in MySQL?
SELECT DISTINCT can be used to give distinct values. Use it to remove duplicate records and it can be used with aggregate function as well. For example: MAX, AVG etc. This can be applied on a single column.Now, I am creating a table to use SELECT DISTINCT for a column. Creating a table with the help of CREATE command −mysql> CREATE TABLE DistinctDemo -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.64 sec)Inserting records −mysql> INSERT into DistinctDemo values(1, 'John'); Query OK, 1 row affected (0.17 sec) mysql> INSERT into DistinctDemo values(2, ...
Read MoreHow can I search (case-insensitive) in a column using LIKE wildcard?
We can do this with the help of lower() with column name. Firstly, we will create a table with the help of CREATE command.Creating a table −mysql> CREATE table InCaseSensDemo -> ( -> Name varchar(100) -> ); Query OK, 0 rows affected (0.50 sec)Inserting records into the table with the help of INSERT command −mysql> INSERT into InCaseSensDemo values('JOhN'); Query OK, 1 row affected (0.11 sec) mysql> INSERT into InCaseSensDemo values('bob'); Query OK, 1 row affected (0.21 sec) mysql> INSERT into InCaseSensDemo values('BoB'); Query OK, 1 row affected (0.13 sec) mysql> INSERT into InCaseSensDemo values('Bob'); Query OK, ...
Read MoreHow to make SQL case sensitive string comparison in MySQL?
Firstly, we will create a table with the help of CREATE command.Creating a table −mysql> CREATE table InCaseSensDemo -> ( -> Name varchar(100) -> ); Query OK, 0 rows affected (0.50 sec)Inserting records into the table with the help of INSERT command −mysql> INSERT into InCaseSensDemo values('JOhN'); Query OK, 1 row affected (0.11 sec) mysql> INSERT into InCaseSensDemo values('bob'); Query OK, 1 row affected (0.21 sec) mysql> INSERT into InCaseSensDemo values('BoB'); Query OK, 1 row affected (0.13 sec) mysql> INSERT into InCaseSensDemo values('Bob'); Query OK, 1 row affected (0.18 sec)Displaying all the records with the help of ...
Read MoreHow to select a column name with spaces in MySQL?
To select a column name with spaces, use the back tick symbol with column name. The symbol is ( ` `). Back tick is displayed in the keyboard below the tilde operator ( ~).Firstly, create a table −mysql> CREATE table SpaceColumn -> ( -> `Student Name` varchar(100) -> ); Query OK, 0 rows affected (0.48 sec)Inserting recordsmysql> INSERT into SpaceColumn values('John'); Query OK, 1 row affected (0.18 sec) mysql> INSERT into SpaceColumn values('Bob'); Query OK, 1 row affected (0.17 sec)The syntax to get column name with space is as follows −SELECT `column_name` from yourTableName; Now I will apply the ...
Read MoreWhat is the MySQL VARCHAR max size?
The MySQL version before 5.0.3 was capable of storing 255 characters but from the version 5.0.3 , it is capable of storing 65, 535 characters.MySQL official documentation states −The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65, 535 bytes, which is shared among all columns) and the character set used. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21, 844 characters. Keep in mind that the limitation of ...
Read MoreHow to find out port of MySQL Server?
To find the port of the MySQL server, the command show can be used. Its syntax is as follows −show variables where variable_name=’port’;The above syntax is used to get the port number of the MySQL server using the following query −mysql> show variables where variable_name = 'port';After executing the above command, port of MySQL server is obtained as 3306. This can be seen in the following output −+---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ 1 row in set (0.01 sec)Alternatively, the system variable @@port can also be used to find ...
Read MoreFind records from one MySQL table which don't exist in another?
To find the records from one MySQL table which don’t exist in another table we can use the subquery for the table which does not have the records. This can be better understood using the given steps −First a table is created using the create command. The table name is ‘PresentHistory’ and it has two columns. This is given as follows −mysql> CREATE table PresentHistory -> ( -> HisID int, -> HisName varchar(100) -> ); Query OK, 0 rows affected (0.54 sec)After creating the table, some records are inserted that will be present in the second table as well. This ...
Read MoreWhich one is better to insert NULL or empty string in MySQL?
In innoDB, NULL occupies less space as compared to empty string. Also, the NULL length is null while length of the empty string is 0.To understand this, a table is created with the help of create command which is given as follows −mysql> CREATE table DemoEmptyAndNULL -> ( -> Message varchar(100) -> ); Query OK, 0 rows affected (0.49 sec)After creating the table successfully, an empty record is inserted into the table with the help of insert command which is as follows −mysql> INSERT into DemoEmptyAndNULL values(' '); Query OK, 1 row affected (0.17 sec)After inserting the record, we can ...
Read More