MySql: MySql Commands

MySql Commands with examples

mysql> mysql -h host -u user -p password;

mysql>SELECT VERSION();

mysql>select current_date;

mysql>select now();

mysql>show databases;

mysql>show tables;

mysql>alter table employee drop column department;

mysql>select user(), current_date;

+-------------------------------+--------------+

| user() | current_date |

+-------------------------------+--------------+

| retail_dba@gw02.itversity.com | 2019-05-20 |

+-------------------------------+--------------+

If you decide you do not want to execute a query that you are in the process of entering, cancel it by typing \c

mysql> select user()

-> \c

mysql>

mysql>create table Employee_Info(Emp_Id integer Not null AUTO_INCREMENT, Emp_Name varchar(100), Emp_DOB datetime, Emp_Age Integer, Primary Key(Emp_Id));

Query OK, 0 rows affected (0.21 sec)

mysql> describe EmpInfo;

+----------+--------------+------+-----+---------+-------+

+----------+--------------+------+-----+---------+-------+

| Emp_Id | int(11) | NO | PRI | NULL | |

| Emp_Age | int(11) | YES | | NULL | |

+----------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql>LOAD DATA LOCAL INFILE '/path/EmpInfo.txt' INTO TABLE EmpInfo;

//Insert data into table

insert into Employee_Info (Emp_Name,Emp_DOB, Emp_Age) values ('Umesh','1989-05-23 12:45:56', 45); //Date will be in YYYY-MM-DD HH:MM:SS format.

//Emp_Info

//Employee_Info

TRUNCATE

TRUNCATE SQL query removes all rows from a table, without logging the individual row deletions.

The following example removes all data from the Customers table.

TRUNCATE TABLE Customers;

TRUNCATE is a DDL command

TRUNCATE is executed using a table lock and whole table is locked for remove all records.

We cannot use WHERE clause with TRUNCATE.

TRUNCATE removes all rows from a table.

Minimal logging in transaction log, so it is faster performance wise.

TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

Identify column is reset to its seed value if table contains any identity column.

To use Truncate on a table you need at least ALTER permission on the table.

Truncate uses less transaction space than the Delete statement.

Truncate cannot be used with indexed views.

TRUNCATE is faster than DELETE.

DELETE

To execute a DELETE queue, delete permissions are required on the target table. If you need to use a WHERE clause in a DELETE, select permissions are required as well.

The following query deletes all rows from the Customers table.

DELETE FROM Customers;

The following SQL query deletes all rows from the Customers table where OrderID is greater than 1000.

DELETE FROM Customers WHERE OrderId > 1000;

DELETE is a DML command.

DELETE is executed using a row lock, each row in the table is locked for deletion.

We can use where clause with DELETE to filter & delete specific records.

The DELETE command is used to remove rows from a table based on WHERE condition.

It maintain the log, so it slower than TRUNCATE.

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.

Identity of column keep DELETE retains the identity.

To use Delete you need DELETE permission on the table.

Delete uses the more transaction space than Truncate statement.

Delete can be used with indexed views.

DROP

DROP table query removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables. DROP command requires ALTER permission on the schema to which the table belongs, CONTROL permission on the table, or membership in the db_ddladmin fixed database role.

The following SQL query drops the Customers table and its data and indexes from the current database.

DROP TABLE Customers ;

The DROP command removes a table from the database.

All the tables' rows, indexes and privileges will also be removed.

No DML triggers will be fired.

The operation cannot be rolled back.

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command.

DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back

mysql> select * from Employee_Info;

+--------+----------+---------------------+---------+

+--------+----------+---------------------+---------+

| 1 | NULL | 0000-00-00 00:00:00 | 33 |

| 2 | Verma | 1989-05-23 12:45:56 | 45 |

| 3 | Umesh | 1989-05-23 12:45:56 | 45 |

+--------+----------+---------------------+---------+

3 rows in set (0.00 sec)

mysql> update Employee_Info set Emp_Name = 'Chandra' where Emp_Id=1;

Query OK, 1 row affected (0.08 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> Delete from Employee_Info where Emp_Id= 1;

Query OK, 1 row affected (0.04 sec)

mysql> select * from Employee_Info;

+--------+----------+---------------------+---------+

+--------+----------+---------------------+---------+

| 2 | Verma | 1989-05-23 12:45:56 | 45 |

| 3 | Umesh | 1989-05-23 12:45:56 | 45 |

+--------+----------+---------------------+---------+

2 rows in set (0.00 sec)

mysql> delete table Employee_Info;

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 'table Employee_Info' at line 1

mysql> delete from Employee_Info;

Query OK, 2 rows affected (0.02 sec)

mysql> delete from Employee_Info;

Query OK, 2 rows affected (0.02 sec)

mysql> insert into Employee_Info (Emp_Name,Emp_DOB, Emp_Age) values ('Umesh','1989-05-23 12:45:56', 45);

Query OK, 1 row affected (0.04 sec)

89-05-23 12:45:56', 45);yee_Info (Emp_Name,Emp_DOB, Emp_Age) values ('Umesh','19

Query OK, 1 row affected (0.09 sec)

mysql> insert into Employee_Info (Emp_Name,Emp_DOB, Emp_Age) values ('Umesh','1989-05-23 12:45:56', 35);

Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM Employee_Info;

+--------+----------+---------------------+---------+

+--------+----------+---------------------+---------+

| 4 | Umesh | 1989-05-23 12:45:56 | 45 |

| 5 | Umesh | 1989-05-23 12:45:56 | 45 |

| 6 | Umesh | 1989-05-23 12:45:56 | 35 |

+--------+----------+---------------------+---------+

3 rows in set (0.00 sec)

mysql> truncate table Employee_Info;

Query OK, 0 rows affected (0.15 sec)

mysql> SELECT * FROM Employee_Info;

Empty set (0.00 sec)

mysql> insert into Employee_Info (Emp_Name,Emp_DOB, Emp_Age) values ('Umesh','1989-05-23 12:45:56', 35);

Query OK, 1 row affected (0.05 sec)

mysql> insert into Employee_Info (Emp_Name,Emp_DOB, Emp_Age) values ('Umesh','1989-05-23 12:45:56', 45);

Query OK, 1 row affected (0.04 sec)

mysql> insert into Employee_Info (Emp_Name,Emp_DOB, Emp_Age) values ('Umesh','1989-05-23 12:45:56', 35);

Query OK, 1 row affected (0.04 sec)

+--------+----------+---------------------+---------+

+--------+----------+---------------------+---------+

| 1 | Umesh | 1989-05-23 12:45:56 | 35 |

| 2 | Umesh | 1989-05-23 12:45:56 | 45 |

| 3 | Umesh | 1989-05-23 12:45:56 | 35 |

+--------+----------+---------------------+---------+

3 rows in set (0.00 sec)

mysql> select * from Employee_Info where Emp_Name like '%_me_%';

+--------+----------+---------------------+---------+

+--------+----------+---------------------+---------+

| 1 | Umesh | 1989-05-23 12:45:56 | 35 |

| 2 | Umesh | 1989-05-23 12:45:56 | 45 |

| 3 | Umesh | 1989-05-23 12:45:56 | 35 |

+--------+----------+---------------------+---------+

3 rows in set (0.00 sec)

mysql> select * from Employee_Info where Emp_Name like 'U%';

+--------+----------+---------------------+---------+

+--------+----------+---------------------+---------+

| 1 | Umesh | 1989-05-23 12:45:56 | 35 |

| 2 | Umesh | 1989-05-23 12:45:56 | 45 |

| 3 | Umesh | 1989-05-23 12:45:56 | 35 |

+--------+----------+---------------------+---------+

3 rows in set (0.00 sec)

We want to find all of the customers whose last_name begins with 'Sm'.

SELECT customer_name FROM customers WHERE last_name LIKE 'Sm%';

You can also using the % wildcard multiple times within the same string. For example,

SELECT customer_name FROM customers WHERE last_name LIKE '%it%';

In this MySQL LIKE condition example, we are looking for all customers whose last_name contains the characters 'it'.

Remember that _ wildcard is looking for only one character.

For example:

SELECT supplier_name FROM suppliers WHERE supplier_name LIKE 'Sm_th';

This MySQL LIKE condition example would return all suppliers whose supplier_name is 5 characters long, where the first two characters are 'Sm' and the last two characters are 'th'. For example, it could return suppliers whose supplier_name is 'Smith', 'Smyth', 'Smath', 'Smeth', etc.

Here is another example:

SELECT * FROM suppliers WHERE account_number LIKE '12345_';

You might find that you are looking for an account number, but you only have 5 of the 6 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return suppliers whose account numbers are:

123450, 123451, 123452, 123453, 123454, 123455, 123456, 123457, 123458, 123459

If you want to find products whose product codes contain string _20 , you can use the pattern %\_20% as shown in the following query:

SELECT productCode, productName FROM products WHERE productCode LIKE '%\_20%';

productCode productName

S10_2016 1996 Moto Guzzi 1100i

S24_2000 1960 BSA Gold Star DBD34

S24_2011 18th century schooner

S24_2022 1938 Cadillac V-16 Presidential Limousine

S700_2047 HMS Bounty

Or you can specify a different escape character e.g., $ by using the ESCAPE clause:

SELECT productCode, productName FROM products WHERE productCode LIKE '%$_20%' ESCAPE '$';

productCode productName

S10_2016 1996 Moto Guzzi 1100i

S24_2000 1960 BSA Gold Star DBD34

S24_2011 18th century schooner

S24_2022 1938 Cadillac V-16 Presidential Limousine

S700_2047 HMS Bounty

To find names beginning with b:

mysql> SELECT * FROM pet WHERE name LIKE 'b%';

+--------+--------+---------+------+------------+------------+

+--------+--------+---------+------+------------+------------+

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |

+--------+--------+---------+------+------------+------------+

To find names ending with fy:

mysql> SELECT * FROM pet WHERE name LIKE '%fy';

+--------+--------+---------+------+------------+-------+

+--------+--------+---------+------+------------+-------+

| Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+--------+--------+---------+------+------------+-------+

To find names containing a w:

mysql> SELECT * FROM pet WHERE name LIKE '%w%';

+----------+-------+---------+------+------------+------------+

| name     | owner | species | sex  | birth      | death      |

+----------+-------+---------+------+------------+------------+

| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |

| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |

| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |

+----------+-------+---------+------+------------+------------+

To find names containing exactly five characters, use five instances of the _ pattern character:

mysql> SELECT * FROM pet WHERE name LIKE '_____';

+-------+--------+---------+------+------------+-------+

| name  | owner  | species | sex  | birth      | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |

| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |

+-------+--------+---------+------+------------+-------+

To find names beginning with b, use ^ to match the beginning of the name:

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');

+--------+--------+---------+------+------------+------------+

| name   | owner  | species | sex  | birth      | death      |

+--------+--------+---------+------+------------+------------+

| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |

| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |

+--------+--------+---------+------+------------+------------+

To find names ending with fy, use $ to match the end of the name:

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$');

+--------+--------+---------+------+------------+-------+

| name   | owner  | species | sex  | birth      | death |

+--------+--------+---------+------+------------+-------+

| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |

| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |

+--------+--------+---------+------+------------+-------+

To find names containing a w, use this query:

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');

+----------+-------+---------+------+------------+------------+

| name     | owner | species | sex  | birth      | death      |

+----------+-------+---------+------+------------+------------+

| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |

| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |

| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |

+----------+-------+---------+------+------------+------------+

To find names containing exactly five characters, use ^ and $ to match the beginning and end of the name, and five instances of . in between:

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');

+-------+--------+---------+------+------------+-------+

| name  | owner  | species | sex  | birth      | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |

| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |

+-------+--------+---------+------+------------+-------+

You could also write the previous query using the {n} (“repeat-n-times”) operator:

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$');

+-------+--------+---------+------+------------+-------+

| name  | owner  | species | sex  | birth      | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |

| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |

+-------+--------+---------+------+------------+-------+

Sorting Rows

mysql> SELECT name, birth FROM pet ORDER BY birth;

+----------+------------+

| name     | birth      |

+----------+------------+

| Buffy    | 1989-05-13 |

| Bowser   | 1989-08-31 |

| Fang     | 1990-08-27 |

| Fluffy   | 1993-02-04 |

| Claws    | 1994-03-17 |

| Slim     | 1996-04-29 |

| Whistler | 1997-12-09 |

| Chirpy   | 1998-09-11 |

| Puffball | 1999-03-30 |

+----------+------------+

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;

+----------+------------+

| name     | birth      |

+----------+------------+

| Puffball | 1999-03-30 |

| Chirpy   | 1998-09-11 |

| Whistler | 1997-12-09 |

| Slim     | 1996-04-29 |

| Claws    | 1994-03-17 |

| Fluffy   | 1993-02-04 |

| Fang     | 1990-08-27 |

| Bowser   | 1989-08-31 |

| Buffy    | 1989-05-13 |

+----------+------------+

You can sort on multiple columns, and you can sort different columns in different

 directions.For example, to sort by type of animal in ascending order, then by birth

 date within animal type in descending order (youngest animals first), use the

following query:

mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;

+----------+---------+------------+

| name     | species | birth      |

+----------+---------+------------+

| Chirpy   | bird    | 1998-09-11 |

| Whistler | bird    | 1997-12-09 |

| Claws    | cat     | 1994-03-17 |

| Fluffy   | cat     | 1993-02-04 |

| Fang     | dog     | 1990-08-27 |

| Bowser   | dog     | 1989-08-31 |

| Buffy    | dog     | 1989-05-13 |

| Puffball | hamster | 1999-03-30 |

| Slim     | snake   | 1996-04-29 |

+----------+---------+------------+

Date Calculations

SELECT name, birth, CURDATE(),

TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;

+----------+------------+------------+------+

| name     | birth      | CURDATE()  | age  |

+----------+------------+------------+------+

| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |

| Claws    | 1994-03-17 | 2003-08-19 |    9 |

| Buffy    | 1989-05-13 | 2003-08-19 |   14 |

| Fang     | 1990-08-27 | 2003-08-19 |   12 |

| Bowser   | 1989-08-31 | 2003-08-19 |   13 |

| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |

| Whistler | 1997-12-09 | 2003-08-19 |    5 |

| Slim     | 1996-04-29 | 2003-08-19 |    7 |

| Puffball | 1999-03-30 | 2003-08-19 |    4 |

+----------+------------+------------+------+

You can write the query so that it works no matter what the current month is, so that you do not have to use the number for a particular month. DATE_ADD() enables you to add a time interval to a given date. If you add a month to the value of CURDATE(), then extract the month part with MONTH(), the result produces the month in which to look for birthdays:

mysql> SELECT name, birth FROM pet

WHERE MONTH(birth)=MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

A different way to accomplish the same task is to add 1 to get the next month after

the current one after using the modulo function (MOD)to wrap the month value

to 0 if it is currently 12:

mysql> SELECT name, birth FROM pet

WHERE MONTH(birth) = MOD(MONTH(CURDATE()),12) +1;

mysql> SELECT '2018-10-31' + INTERVAL 1 DAY;

+-------------------------------+

| '2018-10-31' + INTERVAL 1 DAY |

+-------------------------------+

| 2018-11-01                    |

+-------------------------------+

mysql> SELECT '2018-10-32' + INTERVAL 1 DAY;

+-------------------------------+

| '2018-10-32' + INTERVAL 1 DAY |

+-------------------------------+

| NULL                          |

+-------------------------------+

mysql> SHOW WARNINGS;

+---------+------+----------------------------------------+

| Level   | Code | Message                                |

+---------+------+----------------------------------------+

| Warning | 1292 | Incorrect datetime value: '2018-10-32' |

+---------+------+----------------------------------------+

Earlier, you retrieved the names of the people who owned pets.You can

use COUNT() if you wantto find out how many pets each owner has:

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

+--------+----------+

| owner  | COUNT(*) |

+--------+----------+

| Benny  |        2 |

| Diane  |        2 |

| Gwen   |        3 |

| Harold |        2 |

+--------+----------+

Number of animals per combination of species and sex:

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

+---------+------+----------+

| species | sex  | COUNT(*) |

+---------+------+----------+

| bird    | NULL |        1 |

| bird    | f    |        1 |

| cat     | f    |        1 |

| cat     | m    |        1 |

| dog     | f    |        1 |

| dog     | m    |        2 |

| hamster | f    |        1 |

| snake   | m    |        1 |

+---------+------+----------+

You need not have two different tables to perform a join.Sometimes it is useful

to join a table to itself, if you wantto compare records in a table to other

records in that sametable. For example, to find breeding pairs among your pets,

you can join the pet table with itself to produce candidate pairs of live

males and females of like species:

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species FROM pet AS p1

INNER JOIN pet AS p2 ON p1.species = p2.species AND p1.sex = 'f'

AND p1.death IS NULL AND p2.sex = 'm' AND p2.death IS NULL;

+--------+------+-------+------+---------+

| name   | sex  | name  | sex  | species |

+--------+------+-------+------+---------+

| Fluffy | f    | Claws | m    | cat     |

| Buffy  | f    | Fang  | m    | dog     |

+--------+------+-------+------+---------+

The Maximum Value for a Column

SELECT MAX(article) AS article FROM shop;

+---------+

| article |

+---------+

|       4 |

+---------+

The Row Holding the Maximum of a Certain Column

SELECT article, dealer, price

FROM   shop

WHERE  price=(SELECT MAX(price) FROM shop);

+---------+--------+-------+

| article | dealer | price |

+---------+--------+-------+

|    0004 | D      | 19.95 |

+---------+--------+-------+

Other solutions are to use a LEFT JOIN or to sort all rows descending by price and

get only the first row using the MySQL-specific LIMIT clause:

SELECT s1.article, s1.dealer, s1.price

FROM shop s1

LEFT JOIN shop s2 ON s1.price < s2.price

WHERE s2.article IS NULL;

SELECT article, dealer, price

FROM shop

ORDER BY price DESC

LIMIT 1;

Maximum of Column per Group

SELECT article, MAX(price) AS price FROM   shop GROUP BY article

ORDER BY article;

+---------+-------+

| article | price |

+---------+-------+

|    0001 |  3.99 |

|    0002 | 10.99 |

|    0003 |  1.69 |

|    0004 | 19.95 |

+---------+-------+

The Rows Holding the Group-wise Maximum of a Certain Column

SELECT article, dealer, price FROM   shop s1

WHERE  price=(SELECT MAX(s2.price)

FROM shop s2 WHERE s1.article = s2.article)

ORDER BY article;

+---------+--------+-------+

| article | dealer | price |

+---------+--------+-------+

|    0001 | B      |  3.99 |

|    0002 | A      | 10.99 |

|    0003 | C      |  1.69 |

|    0004 | D      | 19.95 |

+---------+--------+-------+

Uncorrelated subqueries :

SELECT s1.article, dealer, s1.price

FROM shop s1 JOIN (   SELECT article, MAX(price) AS price

FROM shop  GROUP BY article) AS s2  ON s1.article = s2.article

AND s1.price = s2.priceORDER BY article;

Left Join :

SELECT s1.article, s1.dealer, s1.price

FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article

AND s1.price < s2.price

WHERE s2.article IS NULL ORDER BY s1.article;

Calculating Visits Per Day

CREATE TABLE t1 (year YEAR(4), month INT UNSIGNED, day INT UNSIGNED);

INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),

(2000,2,23),(2000,2,23);

The example table contains year-month-day values representing visits by

users to the page.

To determine how many differentdays in each month these visits occur,

use this query:

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1

GROUP BY year,month;

MySql

Tuesday, 21 May 2019

MySql Commands

MySql Commands with examples

Sorting Rows

Date Calculations

No comments:

Post a Comment

MySql Commands

Search This Blog