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 119 of 341
In MySQL, how we can compute date by providing the year, week number and day of the week?nday of the week?
We can compute the date as follows −mysql> SET @year=2017, @week=15, @day=4; Query OK, 0 rows affected (0.00 sec)The above query will pass the value’2017’ ,’15’, ‘4’ in ‘year’, ’week’ and ‘day’ variables respectively. Then after applying the formula in the query below, we can get the date.mysql> SELECT Str_To_Date( Concat(@year,'-',@week,'-',If(@day=7,0,@day) ), '%Y-%U-%w' ) AS Date; +--------------+ | Date | +--------------+ | 2017-04-13 | +--------------+ 1 row in set (0.00 sec)
Read MoreWhat is the way to find business days between two specified dates in MySQL?
With the help of DATEDIFF(expr1, expr2) we can find the business days between two specified dates.For example, if we want to find business days between ‘2017-05-27’ and ‘2017-05-23’ then following would be MySQL query −mysql> Select DATEDIFF('2017-05-27','2017-05-23') AS 'Total Business Days'; +----------------------+ | Total Business Days | +----------------------+ | 4 | +----------------------+ 1 row in set (0.00 sec)
Read MoreIn MySQL, when VARCHAR data type will use 1-byte and when 2-bytes prefix length along with data?length along with data?
As we know that in MySQL, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. This length prefix points out the number of bytes in the value of data. The data value itself will decide that when VARCHAR data type will use 1-byte and when 2-byte prefix length.A column uses 1-byte length if values require no more than 255 bytes.A column uses 2-byte length if values may require more than 255 bytes.
Read MoreWhy we cannot use MySQL DATE data type along with time value?
The default format for MySQL DATE data type is “YYYY-MM-DD” and in this format, there is no possibility to store the time value. Hence, we can say that we cannot use DATE data type along with time value.As we can see in the following example MySQL returns only date value even on using time along with the date.mysql> select DATE("2017-09-25 09:34:21"); +-----------------------------------+ | DATE("2017-09-25 09:34:21") | +-----------------------------------+ | 2017-09-25 | +-----------------------------------+ 1 row in set (0.04 sec)However, in DATETIME and TIMESTAMP date data types we can use the time to date.
Read MoreHow to search a record by date in MySQL table?
Suppose we have a table ‘Order123’ having ProductName, Quantity and OrderDate columns as follows −mysql> Select * from Order123; +-------------+----------+------------+ | ProductName | Quantity | OrderDate | +-------------+----------+------------+ | A | 100 | 2017-05-25 | | B | 105 | 2017-05-25 | | C | 55 | 2017-05-25 | | D | 250 | 2017-05-26 | | E | 500 | 2017-05-26 | | ...
Read MoreWhat happens if I will delete a row from MySQL parent table?
While deleting the row from the parent table, if the data of that row is used in the child table then MySQL will throw an error because of the failure of FOREIGN KEY constraint. It can be understood with the example of two tables named ‘customer’ and ‘orders’. Here, ‘customer’ is the parent table and ‘orders’ is the child table. We cannot delete a row, that is used in child table ‘orders’, from the ‘customer’ table. It can be demonstrated by deleting the values from the parent table as follows −mysql> Select * from Customer; +----+--------+ | id | name ...
Read MoreWhen are two tables connected with MySQL FOREIGN KEY then how can we say that the integrity of data is maintained in child table?
Actually, foreign keys enforce referential integrity that helps us to maintain the consistency and integrity of the data automatically. It can be understood with the example of two tables named ‘customer’ and ‘orders’. Here, ‘customer’ is the parent table and ‘orders’ is the child table. We cannot create an order for a non-existent customer. It can be demonstrated by inserting the values in both the tables as follows −mysql> Select * from Customer; +----+--------+ | id | name | +----+--------+ | 1 | Gaurav | | 2 | Raman | | 3 | Harshit| | 4 | Aarav | ...
Read MoreIn case of FOREIGN KEY constraint, what kind of relationship is there between MySQL parent and child tables?
The relationship between parent and child table is One-to-Many relationship. It can be understood with the example of two tables named ‘customer’ and ‘orders’. Here, ‘customer’ is the parent table and ‘orders’ is the child table. The relationship is one-to—many because a customer can have more than one order. It can be demonstrated by inserting the values in both the tables as follows −mysql> Select * from Customer; +----+---------+ | id | name | +----+---------+ | 1 | Gaurav | | 2 | Raman | | 3 | Harshit | | 4 | Aarav | +----+---------+ ...
Read MoreHow can we drop UNIQUE constraint from a MySQL table?
For dropping UNIQUE constraint from a MySQL table, first of all, we must have to check the name of the index created by the UNIQUE constraint on the table. As we know that SHOW INDEX statement is used for this purpose. The ‘key_name’ in the result set of SHOW INDEX statement contains the name of the index. Now either with the help of DROP INDEX statement or ALTER TABLE statement, we can drop the UNIQUE constraint. The syntax for both the statements is as follows −SyntaxDROP INDEX index_name ON table_name; OR ALTER TABLE table_name DROP INDEX index_name;ExampleSuppose we have the ...
Read MoreHow can we create multicolumn UNIQUE indexes?
For creating multicolumn UNIQUE indexes we need to specify an index name on more than one column. Following example will create a multicolumn index named ‘id_fname_lname’ on the columns ‘empid’, ’first_name’, ’last_name’ of ‘employee’ table −mysql> Create UNIQUE INDEX id_fname_lname on employee(empid, first_name, last_name); Query OK, 0 rows affected (0.41 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe employee; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | empid | int(11) | YES | MUL | NULL | | | first_name | varchar(20) | YES | | NULL | | | ...
Read More