MySQL Tutorial
My SQL Command:
1. Install MSQL while installation provide the username and password.
2. Note down the username and password of SQL command.
3. Login MySQL command prompt with username and password.
1. You can disconnect from MySQL database any time using exit command at mysql> prompt.
mysql> exit
Bye
SQL COMMAND FOR DATABASE OPERATION :
2. Create Database :
CREATE DATABASE TUTORIALS;
3. To Drop Database:
DROP DATABASE TUTORIALS;
4. To Select Database :
select database called TUTORIALS:
use TUTORIALS;
SQL COMMAND FOR TABLE SELECT OPERATION :
5. To Create table :
CREATE TABLE Login (username VARCHAR(100) NOT NULL, password VARCHAR(100) NOT NULL);
6. To Drop Table :
DROP TABLE Login:
DROP TABLE Login;
7. Insert command into table:
INSERT INTO Login ( username, password) VALUES( 'XXXX', 'YYYY' );
8. Select command into table :
Select query table :
select * from Login;
9.Where clause command in table select :
select * from Login where username='XXXX';
SQL COMMAND FOR UPDATE OPERATION :
10.Update command :
update Login set password='YYYY' where username='XXXX';
SQL COMMAND FOR DELETE OPERATION :
11. Delete command :
delete from Login where username='XXXX';
SQL COMMAND FOR CLAUSE OPERATION :
12.Like clause command:
select * from Login where username LIKE '%la';
13.Order by clause command :
SELECT * from Login ORDER BY username ASC; (ascending order)
SELECT * from Login ORDER BY username DESC; (descending order)
14.Using NULL values at Command Prompt:
CREATE TABLE UserInfo (username VARCHAR(100) NOT NULL, designation VARCHAR(100));
SQL COMMAND FOR INSERT OPERATION :
15. Insert command :
INSERT INTO UserInfo ( username, designation) VALUES( 'lakshmi', NULL );
INSERT INTO UserInfo ( username, designation) VALUES( 'kodiak', 'Company' );
16. Join command :
SELECT a.username, a.password, b.designation FROM Login a, UserInfo b WHERE a.username = b.username;
SQL COMMAND FOR JOIN OPERATION :
17.MySQL LEFT JOIN:
SELECT a.username, a.password, b.designation FROM Login a LEFT JOIN UserInfo b ON a.username = b.username;
18. Show no of columns in the table :
show columns from Login;
SQL COMMAND FOR ADD,MODIFY &CHANGE OPERATION :
19.To add column in the created table:
ADD :
alter table UserInfo add place VARCHAR(100);
show columns from UserInfo;
MODIFY :
ALTER TABLE UserInfo MODIFY place CHAR(10);
show columns from UserInfo;
CHANGE :
With CHANGE, the syntax is a bit different. After the CHANGE keyword, you name the column you want to change, then specify the new definition, which includes the new name. Try out following example:
SQL COMMAND FOR ALTER OPERATION :
20. Alter Command :
ALTER TABLE UserInfo change place Country CHAR(10);
show columns from UserInfo;
Simple and Unique Index:
A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.
While creating index it should be considered that what are the columns which will be used to make SQL queries and create one or more indexes on those columns.
Practically, Indexes are also type of tables which keeps primary key or index field and a pointer to each record in to the actual table.
The users cannot see the indexes, they are just used to speed up queries and will be used by Database Search Engine to locate records very fast.
INSERT and UPDATE statements takes more time on tables having indexes where as SELECT statements become fast on those tables. The reason is that while doing insert or update, database need to inert or update index values as well.
MySQL Temporary Tables:
The temporary tables could be very useful in some cases to keep temporary data. The most important thing that should be knows for temporary tables is that they will be deleted when the current client session terminates.
Temporary tables where added in MySQL version 3.23. If you use an older version of MySQL than 3.23 you can't use temporary tables, but you can use heap tables.
As stated earlier temporary tables will only last as long as the session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQl database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table.
CREATE TEMPORARY TABLE SalesSummary (
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Dropping Temporary Tables:
mysql> CREATE TEMPORARY TABLE SalesSummary (
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO SalesSummary
-> (product_name, total_sales, avg_unit_price, total_units_sold)
-> VALUES
-> ('cucumber', 100.25, 90, 2);
mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber | 100.25 | 90.00 | 2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE SalesSummary;
mysql> SELECT * FROM SalesSummary;
ERROR 1146: Table 'TUTORIALS.SalesSummary' doesn't exist
