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 156 of 355
Combine two MySQL fields and update a third one with result?
Following is the syntax to combine two fields in MySQL −alter table yourTableName add column yourColumnName dataType; update yourTableName set yourAddedColumnName =concat(yourColumnName1, ' ', yourColumnName2);Let us first create a table −mysql> create table DemoTable1590 -> ( -> FirstName varchar(20), -> LastName varchar(20) -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1590 values('Adam', 'Smith'); Query OK, 1 row affected (0.45 sec) mysql> insert into DemoTable1590 values('John', 'Doe'); Query OK, 1 row affected (0.42 sec) mysql> insert into DemoTable1590 values('David', 'Miller'); Query OK, 1 row affected ...
Read MoreCan we create a database with a numeric name with MySQL?
You cannot create database with numeric name as shown below −mysql> create database 1233;This will produce the following output −ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1233' at line 1To create a database with numeric name, you need to use backticks around the database name −create database yourDatabaseName;Let us implement the above syntax −mysql> create database `1233`; Query OK, 1 row affected (0.20 sec)Now you can switch to the same database −mysql> use `1233`; Database changed
Read MoreHow can I see how long statements take to execute on the MySQL command line?
For every single statement on MySQL command line, it shows the exact time to execute the specific statement.Let us first create a table −mysql> create table DemoTable1589 -> ( -> EmployeeId int, -> EmployeeName varchar(20) -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1589 values(101, 'Sam'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1589 values(102, 'Bob'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1589 values(103, 'David'); Query OK, 1 row affected (0.16 sec)Display all records from the table ...
Read MoreSelect maximum of sum of two columns in MySQL
To select maximum of sum of two columns, use aggregate function MAX() along with subquery. Let us first create a table −mysql> create table DemoTable1587 -> ( -> Value1 int, -> Value2 int -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1587 values(30, 50); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1587 values(80, 90); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1587 values(40, 67); Query OK, 1 row affected (0.13 sec)Display all records from the table using select ...
Read MoreDisplay information about field names in MySQL including TYPE, KEY, etc.
To display information about field names, the syntax is as follows −show columns from yourTableName;Let us first create a table −mysql> create table DemoTable1586 -> ( -> EmployeeId int, -> EmployeeFirstName varchar(20), -> EmployeeLastName varchar(20), -> EmployeeAge int, -> EmployeeCountryName varchar(20), -> EmployeeSalary int -> ); Query OK, 0 rows affected (0.78 sec)Following is the query to display field names −mysql> show columns from DemoTable1586;This will produce the following output −+---------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | ...
Read MoreWhat will happen if we have set UNIQUE and multiple insertion with duplicate values
An error will arise and nothing will get inserted in the table Let us see an example and create a table −mysql> create table DemoTable1585 -> ( -> StudentId int, -> StudentMarks int, -> UNIQUE(StudentId) -> ); Query OK, 0 rows affected (1.02 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1585 values(1,87),(2,98),(3,91),(3,48); ERROR 1062 (23000): Duplicate entry '3' for key 'StudentId'Display all records from the table using select statement −mysql> select * from DemoTable1585;This will produce the following output. Nothing gets inserted:Empty set (0.00 sec)
Read MoreDeleting partial data from a field in MySQL?
To delete partial data, use UPDATE command along with REPLACE(). Let us first create a table −mysql> create table DemoTable1583 -> ( -> GameDetails text -> ); Query OK, 0 rows affected (1.38 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1583 values('=Candy, 2000'); Query OK, 1 row affected (0.53 sec) mysql> insert into DemoTable1583 values('=Lucky29, 10000'); Query OK, 1 row affected (0.25 sec)Display all records from the table using select statement −mysql> select * from DemoTable1583;This will produce the following output −+------------------------------------------------------------+ | GameDetails ...
Read MoreQuery MySQL table and fetch rows posted before the last 3 days?
Let’s say the current date is −'2019-10-20We will first see an example and create a table −mysql> create table DemoTable1582 -> ( -> PostedDate datetime -> ); Query OK, 0 rows affected (13.36 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1582 values('2019-01-21 12:34:40'); Query OK, 1 row affected (1.06 sec) mysql> insert into DemoTable1582 values('2019-10-15 11:00:00'); Query OK, 1 row affected (0.87 sec) mysql> insert into DemoTable1582 values('2019-10-25 1:10:00'); Query OK, 1 row affected (1.14 sec)Display all records from the table using select statement −mysql> select * from DemoTable1582;This will produce the ...
Read MoreHow to ensure that MySQL rows are unique?
To ensure that MySQL rows are unique, you need to use UNIQUE constraint. Let us first create a table −mysql> create table DemoTable1580 -> ( -> id int, -> Name varchar(20), -> Age int -> ); Query OK, 0 rows affected (0.73 sec)Here is the query to create unique constraints to ensure MySQL rows are unique −mysql> alter table DemoTable1580 add unique index(id, Name, Age); Query OK, 0 rows affected (0.45 sec) Records: 0 Duplicates: 0 Warnings: 0Insert some records in the table using insert command −mysql> insert into DemoTable1580 values(101, 'Chris', 21); Query OK, ...
Read MoreMySQL select and insert in two tables with a single query
Here is the query to create first table.mysql> create table DemoTable1 -> ( -> StudentName varchar(20), -> StudentMarks int -> ); Query OK, 0 rows affected (0.67 sec)To understand the above concept, let us create second table.mysql> create table DemoTable2 -> ( -> Name varchar(20) -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2 values('Chris'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select * from DemoTable2;This will produce the following output −+-------+ ...
Read More