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 307 of 355
Write a MySQL query where length of the records is longer than 1?
Here, we will use OCTET_LENGTH to check the length of a record since we want the records with length more than 1. Let us first create a table −mysql> create table DemoTable ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserName varchar(20), UserGender varchar(20) ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(UserName, UserGender) values('John', 'M'); Query OK, 1 row affected (0.82 sec) mysql> insert into DemoTable(UserName, UserGender) values('Carol', 'Male'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable(UserName, UserGender) values('Mia', 'Female'); Query OK, ...
Read MoreAlter row_format to dynamic in MySQL?
To alter row_format to dynamic in MySQL, following is the syntax:ALTER TABLE yourTableName ROW_FORMAT=DYNAMIC;Let us first create a table:mysql> create table DemoTable ( CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY, CustomerName varchar(200), CustomerAge int, CustomerAddress varchar(200) ); Query OK, 0 rows affected (0.73 sec)Let us check the description of table using DESC command:mysql> desc DemoTable;This will produce the following output:+-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra ...
Read MoreMySQL query to add 0's to numbers with less than 9 digits?
Use LPAD() to add 0's to numbers with less than 9 digits. Let us first create a table −mysql> create table DemoTable ( Value varchar(20) ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('3646465'); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable values('9485757591'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('485756'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('959585'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('124'); Query OK, 1 row affected ...
Read MoreWhat is the syntax for input parameters (variables) in a MySQL query?
To set a variable in MySQL, you need to use the SET command. Following is the syntax:set @yourVariableName:=yourValue; select *from yourTableName where yourColumnName=@yourVariableName;Let us first create a table:mysql> create table DemoTable ( Id int, FirstName varchar(20), LastName varchar(20) ); Query OK, 0 rows affected (0.83 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values(10, 'Carol', 'Taylor'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(20, 'John', 'Doe'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(30, 'John', 'Smith'); Query OK, 1 row ...
Read MoreHow to update a column of varchar type in MySQL to increase its length?
Let us first create a table. Here, we have two columns with varchar type −mysql> create table DemoTable ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserFirstName varchar(10), UserLastName varchar(20) , UserAge int ); Query OK, 0 rows affected (0.96 sec)Let us check the description of table using DESC command −mysql> desc DemoTable;This will produce the following output −+---------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+----------------+ | UserId | int(11) | NO ...
Read MoreHow to GROUP BY in a select query on positive or negative values?
Following is the syntax to GROUP BY in a select query on positive or negative values:select *from yourTableName group by -yourColumnName;Let us first create a table:mysql> create table DemoTable (Value int); Query OK, 0 rows affected (0.60 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values(-10); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values(-20); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.13 sec) mysql> insert ...
Read MoreHow to order by certain part of a string in MySQL?
You can use ORDER BY SUBSTRING() to order by certain part of a string in MySQL. Let us first create a table:mysql> create table DemoTable (UserId varchar(200)); Query OK, 0 rows affected (0.68 sec)Following is the query to insert records in the table using insert command:mysql> insert into DemoTable values('USER_1234'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('USER_John'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('USER_Sam'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('USER_Carol'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('USER_Bob'); Query OK, ...
Read MoreWhich datatype should I use for flag in MySQL?
To set a flag, you can set the type as tinyint(1) type. Following is the syntax −yourColumnName tinyint(1) DEFAULT 1;Let us first create a table −mysql> create table DemoTable ( ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(20), isMarried tinyint(1) DEFAULT 1 ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> INSERT INTO DemoTable(ClientName, isMarried) values('Larry', 0); Query OK, 1 row affected (0.16 sec) mysql> INSERT INTO DemoTable(ClientName) values('David'); Query OK, 1 row affected (0.12 sec) mysql> INSERT INTO DemoTable(ClientName, isMarried) values('Mike', 1); Query OK, 1 row affected (0.19 ...
Read MoreHow can I remove every column in a table in MySQL?
In order to remove every column in a table in MySQL, you can use DROP TABLE command. Following is the syntax:DROP TABLE yourTableName;Let us first create a table:mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentFirstName varchar(20), StudentLastName varchar(20), StudentAge int, StudentAddress varchar(200), StudentCountryName varchar(30), StudentDateOfBirth datetime ); Query OK, 0 rows affected (0.85 sec)Let us check the description of table using DESC command:mysql> desc DemoTable;This will produce the following output:+--------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key ...
Read MoreUpdate one column data to another column in MySQL if the second column is NOT NULL?
To update one column data to another column, you can use UPDATE command. Let us first create a table −mysql> create table DemoTable ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserFirstName varchar(20), ListOfName varchar(20) ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(UserFirstName, ListOfName) values('John', 'Larry'); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable(UserFirstName, ListOfName) values('Carol', null); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(UserFirstName, ListOfName) values('David', 'Sam'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable(UserFirstName, ListOfName) ...
Read More