MySQL Articles - Page 181 of 355

The DROP TABLE removes the table completely and also removes all data. If you want to remove all data completely and wants the table structure, then you can use TRUNCATE TABLE command. The TRUNCATE command will recreate the table.Let us first check the DROP TABLE. For that, we will first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Name varchar(20)    ); Query OK, 0 rows affected (0.20 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name) values('Larry'); Query OK, 1 row affected (0.07 sec) ... Read More

You can use elt() along with rand() for this. Let us select random number from a specific list.mysql> SELECT ELT(FLOOR(RAND() * 10) + 1,    100, 200, 300, 400, 500, 600, 700, 800, 900, 1000) AS random_value_from_listOfValues;This will produce the following output −+--------------------------------+ | random_value_from_listOfValues | +--------------------------------+ | 1000 | +--------------------------------+ 1 row in set (0.00 sec)Now we will run the query again to select random number from a specific list.mysql> SELECT ELT(FLOOR(RAND() * ... Read More

You can use DEFAULT CURRENT_TIMESTAMP. Keep in mind that it will work only at the time of insertion. Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Arrivaltime TIMESTAMP DEFAULT CURRENT_TIMESTAMP    ); Query OK, 0 rows affected (0.31 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Arrivaltime) values('2018-01-31 10:34:56'); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable(Arrivaltime) values('2019-01-31 11:10:12'); Query OK, 1 row affected (0.04 sec)Display ... Read More

To fetch only N rows at a time, you can use LIMIT operator. Following is the syntax −select *from yourTableNameLIMIT 0, N;Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(20) ); Query OK, 0 rows affected (0.25 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name) values('John'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable(Name) values('Larry'); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable(Name) values('Bob'); ... Read More

Use aggregate function SUM() to get the sum of a column in al rows. Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Amount int    ); Query OK, 0 rows affected (0.20 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Amount) values(50); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable(Amount) values(60); Query OK, 1 row affected (0.04 sec) mysql> insert into DemoTable(Amount) values(70); Query OK, 1 row affected (0.10 sec)Display all records from the table using select statement −mysql> ... Read More

You need to follow some steps to add a new column after a specific column and defining default value. In order to achieve this, you need to use ALTER command. Let us first create a table −mysql> create table DemoTable    (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentFirstName varchar(20),    StudentAge int,    StudentCountryName varchar(100)    ); Query OK, 0 rows affected (0.21 sec)Let us check the description of table −mysql> desc DemoTable;This will produce the following output −+--------------------+--------------+------+-----+---------+----------------+ | Field              | Type         | Null | ... Read More

For using MySQL CASE statement while using UPDATE Query, you can use CASE statement. Let us first create a table −mysql> create table DemoTable    (    UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserScore int    ); Query OK, 0 rows affected (0.29 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(UserScore) values(100); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable(UserScore) values(110); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable(UserScore) values(120); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable(UserScore) values(200); Query OK, 1 ... Read More

You need to use FIND_IN_SET() for this. Let us first create a table −mysql> create table DemoTable ( CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY, CustomerName varchar(20), CustomerAllProductPrice text ); Query OK, 0 rows affected (0.30 sec)Insert some records in the table using insert command. Here, we are inserting numbers separated by comma −mysql> insert into DemoTable(CustomerName, CustomerAllProductPrice) values('Chris', '245, 345, 678, 90, 45, 56, 78'); Query OK, 1 row affected (0.03 sec) mysql> insert into DemoTable(CustomerName, CustomerAllProductPrice) values('Chris', '98, 99, 90, 56, 77'); ... Read More

For performing mathematical operations and working with conditions, you can consider CASE statement. Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    FruitName varchar(100),    FruitPrice int    ); Query OK, 0 rows affected (0.26 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(FruitName, FruitPrice) values('Orange', 250); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(FruitName, FruitPrice) values('Banana', 100); Query OK, 1 row affected (0.05 sec) mysql> insert into DemoTable(FruitName, FruitPrice) values('Apple', 150); Query OK, 1 row affected (0.05 sec) ... Read More

The variable table_type doesn’t work since this variable is deprecated as of MySQL 5.5.3. Use default_storage_engine instead. Following is the syntax −SET default_storage_engine = yourTableEngine;The table engine name may be InnoDB or MyISAM. Here, we will set engine type to MyISAM −mysql> SET default_storage_engine=MyISAM; Query OK,  0 rows affected (0.00 sec)Let us create a table.mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY ); Query OK,  0 rows affected (0.40 sec)Now check the engine type of above table −mysql> SHOW TABLE STATUS WHERE Name = 'DemoTable';This will produce the following output −+--------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | ... Read More