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 328 of 355
What 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 MoreCount number of times value appears in particular column in MySQL?
You can use aggregate function count() with group by. The syntax is as follows.select yourColumnName, count(*) as anyVariableName from yourtableName group by yourColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows.mysql> create table CountSameValue -> ( -> Id int, -> Name varchar(100), -> Marks int -> ); Query OK, 0 rows affected (0.70 sec)Insert records in the table using insert command. The query is as follows.mysql> insert into CountSameValue values(1, 'Sam', 67); Query OK, 1 row affected (0.17 sec) mysql> insert into CountSameValue values(2, 'Mike', 87); Query OK, 1 ...
Read MoreHow to select domain name from email address in MySQL?
To select domain name from email address, you can use in-built SUBSTRING_INDEX() function from MySQL.To understand the concept, let us create a table. The following is the query to create a table.mysql> create table selectDomainNameOnly −> ( −> UserEmailAddress varchar(200) −> ); Query OK, 0 rows affected (0.52 sec)Insert records in the table using insert command. The record will have email-ids from which we need to fetch the domain name. The query is as follows −mysql> insert into selectDomainNameOnly values('John123@yahoo.com'); Query OK, 1 row affected (0.10 sec) mysql> insert into selectDomainNameOnly ...
Read More