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 54 of 355
MySQL Order By specific strings?
Order by the choice of strings you want, using the FIELD() function. The syntax is as follows −SELECT *FROM yourTableName ORDER BY FIELD(yourColumnName, ’yourValue1’, ’yourValue2’, ’yourValue3’, ....N);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table OrderByListOfStrings -> ( -> Id int not null auto_increment, -> CarName varchar(20), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into OrderByListOfStrings(CarName) values('Ford'); Query OK, 1 row affected ...
Read MoreHow to update two columns in a MySQL database?
You can update two columns using SET command separated with comma(, ). The syntax is as follows −UPDATE yourTableName SET yourColumnName1 = ’yourValue1’, yourColumnName2 = ’yourValue2’ where yourCondition;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table StudentInformations -> ( -> StudentId int not null auto_increment, -> StudentFirstName varchar(20), -> StudentLastName varchar(20), -> Primary Key(StudentId) -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into StudentInformations(StudentFirstName, StudentLastName) ...
Read MoreGet table column names in alphabetical order in MySQL?
To get the table column names in alphabetical order, you need to use ORDER BY. The syntax is as follows −SELECT anyReferenceName.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS anyReferenceName WHERE anyReferenceName.TABLE_NAME = ’yourTableName’ ORDER BY anyReferenceName.COLUMN_NAMEFirst, we need to get all the columns and then we need to use ORDER BY. In the above query, we are getting all columns using INFORMATION_SCHEMA.COLUMNS.To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ColumnsOrder -> ( -> StudentFirstName varchar(20), -> Id int, -> StudentAge int, -> StudentLastName varchar(20) ...
Read MoreFind max and second max salary for a MySQL Employee table?
You can get max and second max salary from an Employee table using LIMIT OFFSET. The syntax is as follows −SELECT yourColumnName1, yourColumnName2, ....N from yourTableName ORDER BY yourColumnName desc limit 2 offset 0;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table EmployeeMaxAndSecondMaxSalary -> ( -> EmployeeId int, -> Employeename varchar(20), -> EmployeeSalary int -> ); Query OK, 0 rows affected (0.88 sec)Insert some records in the table using insert command −mysql> insert into EmployeeMaxAndSecondMaxSalary values(1, 'John', 34566); Query OK, 1 row ...
Read MoreHow to ORDER BY LIKE in MySQL?
To order by like in MySQL, use the case statement. The syntax is as follows −SELECT *FROM yourTableName ORDER BY CASE WHEN yourColumnName like '%yourPatternValue1%' then 1 WHEN yourColumnName like '%yourPatternValue2%' then 2 else 3 end;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table OrderByLikeDemo -> ( -> Id int, -> Name varchar(100) -> ); Query OK, 0 rows affected (1.84 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into OrderByLikeDemo values(100, ...
Read MoreHow to add a NOT NULL column in MySQL?
You can add a not null column at the time of table creation or you can use it for an existing table.Case 1 − Add a not null column at the time of creating a table. The syntax is as followsCREATE TABLE yourTableName ( yourColumnName1 dataType NOT NULL, yourColumnName2 dataType . . . N );The query to create a table is as followsmysql> create table NotNullAtCreationOfTable -> ( -> Id int not null, -> Name varchar(100) -> ); Query OK, 0 rows affected (0.60 sec)In the above table, we ...
Read MoreHow to select sum or 0 if no records exist in MySQL?
You can use aggregate function sum() inside COALESCE(). The below syntax returns the sum of all if the record exists otherwise 0 is returned. The syntax is as follows.select COALESCE(sum(yourColumnName2), 0) AS anyVariableName from yourTableName where yourColumnName1 like '%yourValue%';To understand the above syntax, let us create a table. The query to create a table is as follows.mysql> create table SumDemo -> ( -> Words varchar(100), -> Counter int -> ); Query OK, 0 rows affected (0.93 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into SumDemo values('Are You There', 10); Query OK, 1 ...
Read MoreWhat is the MySQL user creation script?
First, create a user and password using CREATE command. The syntax is as follows.CREATE USER 'yourUserName'@'localhost' IDENTIFIED BY 'yourPassword';The syntax to give all privileges of the specific database to the user is as follows.GRANT ALL PRIVILEGES ON yourDatabaseName . * TO 'yourUserName'@'localhost';Now you can implement the above syntaxes to create a user and grant all privileges.Step 1 − Create a userThe query is as follows.mysql> create user 'Adam Smith'@'localhost' IDENTIFIED BY 'Adam123456'; Query OK, 0 rows affected (0.29 sec)Step 2 − Grant all privileges to the user.The query is as follows.mysql> GRANT ALL PRIVILEGES ON test . * TO 'Adam ...
Read MoreList MySQL tables and sizes ordered by size?
You can do this with the help of information_schema.tables. The syntax is as follows -SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024), 2) "MB Size" FROM information_schema.TABLES WHERE table_schema = "yourDatabaseName" ORDER BY (data_length + index_length) ASC;To understand the above syntax, let us implement it for any database. Here, I am using database TEST. Let us see the query for our database TEST.mysql> SELECT TABLE_NAME, table_rows, data_length, index_length, -> round(((data_length + index_length) / 1024 / 1024), 2) "MB Size" -> FROM information_schema.TABLES WHERE table_schema = "test" -> ORDER BY (data_length + index_length) ASC;The following is the ...
Read MoreCan we use LIKE and OR together in MySql?
You can use LIKE with OR operator which works same as IN operator.Let us see the syntax for both the cases −Case 1 − Using Like with OR operator.select *from yourTableName where yourColumnName Like ‘Value1’ or yourColumnName Like ‘Value2’ or yourColumnName Like ‘Value3’ . . . NCase 2 − Using IN operator.select *from yourTableName where IN(value1, value2, value3, .....N);To understand both the syntaxes, let us create a table. The query to create a table is as follows −mysql> create table LikeDemo −> ( −> Id varchar(20) −> ); Query OK, 0 rows affected (0.58 sec)Now you can insert records in ...
Read More