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
MySQLi Articles
Page 319 of 341
How to add column values in MySQL without using aggregate function?
You can add column values without using aggregate function like sum(). For that, the syntax is as follows −SELECT *, (yourColumnName1+yourColumnName2+yourColumnName3, ....N) as anyVariableName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table AddingColumnDemo -> ( -> StudentId int, -> StudentName varchar(20), -> MathMarks int, -> PhysicsMarks int, -> ChemistryMarks int -> ); Query OK, 0 rows affected (0.82 sec)Insert records in the table using insert command. The query is as follows −mysql> insert into AddingColumnDemo values(1, 'John', 35, ...
Read MoreHow to output MySQL query results in CSV format and display it on the screen, not a file?
To get the output MySQL query result in CSV format, use concat(). The syntax is as follows −mysql> select concat(StudentId, ', ', StudentName, ', ', StudentAge) as CSVFormat from CSVFormatOutputs;To understand the above syntax, let us create a table. The query to create a table is as follows−mysql> create table CSVFormatOutputs -> ( -> StudentId int not null auto_increment, -> StudentName varchar(20), -> StudentAge int, -> PRIMARY KEY(StudentId) -> ); Query OK, 0 rows affected (1.15 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into CSVFormatOutputs(StudentName, ...
Read MoreChanging year in MySQL date?
To change the year in MySQL date, you need to use DATE_FORMAT() function with UPDATE command. The syntax is as follows.UPDATE yourTableName SET yourDateColumnName = DATE_FORMAT(yourDateColumnName ,'yourYearValue-%m-%d');To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ChangeYear -> ( -> id int not null auto_increment, -> ArrivalTime date, -> PRIMARY KEY(id) -> ); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into ChangeYear(ArrivalTime) values(date_add(now(), interval -2 year)); Query OK, 1 row affected, 1 warning ...
Read MoreEquivalent of SQL Server IDENTITY Column in MySQL?
Equivalent of Microsoft SQL Server IDENTITY column in MySQL is AUTO_INCREMENT. The IDENTITY in SQL Server acts like AUTO_INCREMENT in MySQL.The syntax is as follows −CREATE TABLE yourTableName ( yourColumnName1 dataType NOT NULL AUTO_INCREMENT, yourColumnName2 dataType, . . . N, PRIMARY KEY(yourColumnName1) );In MySQL, you need to use primary key if your column is auto_increment otherwise MySQL will give an error. Look at the error −mysql> create table EquivalentOfIdentityInMySQL -> ( -> ProductId int NOT NULL AUTO_INCREMENT, -> ProductName varchar(30) -> ); ERROR 1075 (42000) − Incorrect table definition; ...
Read MoreCheck if a string contains numbers in MySQL?
To check a string contains numbers, you can use regexp i.e. Regular Expressions. The syntax is as follows −SELECT *FROM yourTableName where yourColumnName REGEXP ‘[0-9]’;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table StringContainsNumber -> ( -> Id int not null auto_increment, -> Words text, -> primary key(Id) -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into StringContainsNumber(Words) values('He12345llo'); Query OK, 1 row affected (0.19 sec) ...
Read MoreGenerate the row count (serial number) of records after returning the result in MySQL query?
To generate serial number i.e. row count in MySQL query, use the following syntax.SELECT @yourVariableName − = @yourVariableName+1 anyAliasName, yourColumnName1, yourColumnName2, yourColumnName3, ....N from yourTableName , (select @yourVariableName − = 0) as yourVariableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table tblStudentInformation -> ( -> StudentName varchar(20), -> StudentAge int, -> StudentMathMarks int -> ); 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 tblStudentInformation values('Carol', ...
Read MoreRemove trailing zeros in decimal value with changing length in MySQL?
You can remove trailing zeros using TRIM() function. The syntax is as follows.SELECT TRIM(yourColumnName)+0 FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeTrailingZeroInDecimal -> ( -> Id int not null auto_increment, -> Amount decimal(5, 2), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.01 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into removeTrailingZeroInDecimal(Amount) values(405.50); Query OK, 1 row affected (0.22 sec) mysql> insert into removeTrailingZeroInDecimal(Amount) values(23.05); Query OK, ...
Read MoreHow do you OR two MySQL LIKE statements?
You can OR two like statements using the following syntax −SELECT *FROM yourTableName WHERE (yourColumnName like '%yourValue1%' OR yourColumnNamelike '%yourValue2%') AND yourColumnName = yourValue;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ORLikeDemo -> ( -> Id int not null auto_increment, -> FirstName varchar(15), -> LastName varchar(15), -> Primary Key(Id) -> ); Query OK, 0 rows affected (1.19 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into ORLikeDemo(FirstName, LastName) values('John', 'Smith'); Query OK, ...
Read MoreHow to alter the database engine of a MySQL database table?
First, determine the type of MySQL database i.e. whether its engine is InnoDB or MyISAM. To achieve this, use engine column from the information_schema.columns.tables.The syntax is as follows.SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ’yourDatabaseName’ AND TABLE_NAME = ’yourTableName’;Here, I have a table with the name ‘StudentInformations’ −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)Now you can know the table is using InnoDB or MyISAM using the implementation of above syntax. Our ...
Read MoreNeed help selecting non-empty column values from MySQL?
Select non-empty column values using NOT IS NULL and TRIM() function. The syntax is as follows.SELECT * FROM yourTableName WHERE yourColumnName IS NOT NULL AND TRIM(yourColumnName) ' ';You can select non-empty value as well as whitespace from column using the same TRIM() function.To understand the syntax we discussed above, let us create a table. The query to create a table is as follows −mysql> create table SelectNonEmptyValues -> ( -> Id int not null auto_increment, -> Name varchar(30), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.62 sec)Insert records in the table ...
Read More