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 314 of 341
Using MySQL, can I sort a column but allow 0 to come last?
You can sort a column, with 0 come last with the help of ORDER BY. The syntax is as follows −select *from yourTableName order by yourFieldName = 0, yourFieldName;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table SortColumnZeroAtLastDemo −> ( −> RankNumber int −> ); Query OK, 0 rows affected (1.40 sec)Now you can insert records in the table using the following query −mysql> insert into SortColumnZeroAtLastDemo values(100); Query OK, 1 row affected (0.20 sec) mysql> insert into SortColumnZeroAtLastDemo values(0); Query OK, 1 ...
Read MoreAppending data to a MySQL field that already has data in it?
You can append data to a MySQL database field with the help of in-built CONCAT() function.The syntax is as follows −update yourTableName set yourColumnName = CONCAT(yourColumnName, ’AppendValue’);To understand the above concept, let us create a table. The query to create a table −mysql> create table AppendingDataDemo −> ( −> FirstNameAndLastName varchar(200) −> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table with the help of insert command. The query is as follows −mysql> insert into AppendingDataDemo values('John'); Query OK, 1 row affected (0.27 sec) mysql> insert ...
Read MoreWhat is the SQL command to return the field names of a table?
To return the field names of a table, you can use desc command. The syntax is as follows −desc yourTableName;Or you can use column_name field from information_schema.columns table. The syntax is as follows −select column_name from information_schema.columns where table_name = ’yourTableName’;To understand both the syntax, let’s say we have a table ‘ExtractCommentDemo1’.Using the first syntax −mysql> desc ExtractCommentDemo1;The following is the output displaying the fields −+----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | UserId | int(11) | YES | | NULL ...
Read MoreInsert datetime into another datetime field in MySQL?
You can achieve this with the help of update command. To understand the method, let us create a table. The query to create a table is as follows −mysql> create table AddDateTimeWithOther −> ( −> Id int, −> IssueDate datetime, −> DueDate datetime −> ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table with insert statement. The query is as follows −mysql> insert into AddDateTimeWithOther values(100, now(), date_add(now(), interval -3 year)); Query OK, 1 row affected (0.13 sec) mysql> insert ...
Read MoreCan MySQL automatically store timestamp in a row?
Yes, you can achieve this in the following two ways.First Approach At the time of creation of a table.Second Approach At the time of writing query.The syntax is as follows.CREATE TABLE yourTableName ( yourDateTimeColumnName datetime default current_timestamp );You can use alter command.The syntax is as follows.ALTER TABLE yourTableName ADD yourColumnName datetime DEFAULT CURRENT_TIMESTAMP;Implement both the syntaxes now.The first approach is as follows.mysql> create table CurrentTimeStampDemo -> ( -> CreationDate datetime default current_timestamp -> ); Query OK, 0 rows affected (0.61 sec)If you do not pass any parameter for the column ‘CreationDate’, MySQL by default stores the current timestamp.Insert record in ...
Read MoreHow to get a list of MySQL user hosts?
Firstly, get a list of MySQL user accounts, using MySQL.user table. You can use select user column from MySQL.user table to get a list of MySQL user accounts.The query is as follows −mysql> select user from MySQL.user;The following output displays all the users −+------------------+ | user | +------------------+ | Manish | | mysql.infoschema | | mysql.session | | mysql.sys | | root | | am | +------------------+ 6 rows in set (0.06 ...
Read MoreRetrieve first 40 characters of a text field in MySQL?
To get the first 40 characters from a text field, use LEFT() function from MySQL. The syntax is as follows −SELECT LEFT(yourColumnName, 40) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table retrieveFirst40Characters −> ( −> AllWords text −> ); Query OK, 0 rows affected (0.59 sec)Now you can insert some record in the table with the help of insert command. The query is as follows −mysql> insert into retrieveFirst40Characters values('This is a query demo ...
Read MoreHow to cut only the first character in MySQL string?
To cut only the first character, use the substr() function with UPDATE command. The syntax is as follows.UPDATE yourTableName set yourColumnName=substr(yourColumnName, 2);To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table CutStringDemo -> ( -> Value varchar(100) -> ); Query OK, 0 rows affected (0.66 sec)Now you can insert some records in the table using insert command. The query is as follows.mysql> insert into CutStringDemo values(', 12, 3456'); Query OK, 1 row affected (0.14 sec) mysql> insert into CutStringDemo values(', 23, 9867'); Query OK, 1 row affected ...
Read MoreHow to replace values of select return in MySQL?
You can use select case statement for this. The syntax is as follows.select yourColumnName1, yourColumnName2, ...N, case when yourColumnName=1 then 'true' else 'false' end 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 selectReturnDemo -> ( -> Id int, -> Name varchar(100), -> isGreaterthan18 tinyint(1) -> ); Query OK, 0 rows affected (0.62 sec)Now you can insert some records in the table using insert command. The query is as follows.mysql> insert into selectReturnDemo values(1, 'Carol', 0); Query OK, 1 row affected (0.23 sec) mysql> ...
Read MoreHow to check for duplicates in MySQL table over multiple columns?
To check for duplicates in MySQL, you can use group by having clause. The syntax is as follows.select yourColumnName1, yourColumnName2, ......N, count(*) as anyVariableName from yourTableName group by yourColumnName1, yourColumnName2 having count(*) > 1;To understand the above syntax, let us create a table. The query to create a table is as follows.mysql> create table DuplicateDemo -> ( -> StudentId int not null, -> StudentFirstName varchar(100), -> StudentLastName varchar(100), -> Primary Key(StudentId) -> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into DuplicateDemo values(1, 'John', 'Smith'); Query ...
Read More