mysql -u root -p
in this example i use database as 'powerplant' with two tables 'genarator' and 'turbine' and crated database shown in this post.I think that it will help you to understand how to SQL queries work.
Show and Create databases and use database
show databases; #to view all databases CREATE DATABASE powerplant; #create database named it as 'powerplant' use powerplant;#to use powerplant database for other works
Show and Create table
CREATE TABLE `genarator` ( `id` INT(11) NOT NULL, `outputpower` DOUBLE NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `turbine` ( `id` INT(11) NOT NULL, `manufacturer` VARCHAR(50) NOT NULL, `details` VARCHAR(50) NOT NULL, `genarator` INT(11) NOT NULL, PRIMARY KEY (`id`), INDEX `FK__genarator` (`genarator`), CONSTRAINT `FK__genarator` FOREIGN KEY (`genarator`) REFERENCES `genarator` (`id`) ON UPDATE CASCADE ON DELETE CASCADE ); ALTER TABLE `turbine` ADD COLUMN `weight` DOUBLE NULL AFTER `genarator`; ALTER TABLE `turbine` ADD COLUMN `weight` DOUBLE NULL AFTER `genarator`; ALTER TABLE `turbine` ADD COLUMN `test` DOUBLE NULL ; ALTER TABLE `turbine` DROP COLUMN `test`; ALTER TABLE `turbine` ADD CONSTRAINT `FK__genarator` FOREIGN KEY (`genarator`) REFERENCES `genarator` (`id`); show tables; # to view all tables describe genarator; # to view genarator table with column details
Show data in a table
SELECT * FROM genarator;
Update data in a table
INSERT INTO `powerplant`.`genarator` (`id`, `outputpower`) VALUES ('1', '10');
INSERT INTO `powerplant`.`turbine` (`id`, `manufacturer`, `details`, `genarator`, `weight`) VALUES ('1', 'asitha', 'no', '1', '10');
UPDATE `powerplant`.`turbine` SET `manufacturer`='man1', `details`='yes' WHERE `id`=1;
#delete one row in a table
DELETE FROM `powerplant`.`turbine` WHERE `id`=2;
#delete whole table ('test' is table name)
DROP TABLE `test`;
All terminal command that i have typed and executed is shown below for your understanding. You can install any mysql server and execute this queries an what are the output
Terminal
mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.18-1 (Debian)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| JDBC |
| chatuser |
| demo |
| hib |
| hiblayer |
| inventory |
| login |
| mysql |
| onetomany |
| onetoone |
| performance_schema |
| personal |
| php |
| php-pos |
| phplogin |
| phpmyadmin |
| pos |
| posf |
| school |
| sys |
| test |
| welcome_2k18 |
| wordpress |
+--------------------+
24 rows in set (0.00 sec)
mysql> CREATE DATABASE powerplant;
Query OK, 1 row affected (0.00 sec)
mysql> use powerplant;
Database changed
mysql> CREATE TABLE `genarator` (
-> `id` INT(11) NOT NULL,
-> `outputpower` DOUBLE NOT NULL,
-> PRIMARY KEY (`id`)
-> );
Query OK, 0 rows affected (0.48 sec)
mysql> CREATE TABLE `turbine` (
-> `id` INT(11) NOT NULL,
-> `manufacturer` VARCHAR(50) NOT NULL,
-> `details` VARCHAR(50) NOT NULL,
-> `genarator` INT(11) NOT NULL,
-> PRIMARY KEY (`id`),
-> INDEX `FK__genarator` (`genarator`),
-> CONSTRAINT `FK__genarator` FOREIGN KEY (`genarator`) REFERENCES `genarator` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
-> );
Query OK, 0 rows affected (0.33 sec)
mysql> CREATE TABLE `test` (
-> `Column 1` INT(11) NULL DEFAULT NULL
-> );
Query OK, 0 rows affected (0.56 sec)
mysql> DROP TABLE `test`;
Query OK, 0 rows affected (0.20 sec)
mysql> show tables;
+----------------------+
| Tables_in_powerplant |
+----------------------+
| genarator |
| turbine |
+----------------------+
2 rows in set (0.00 sec)
mysql> describe genarator;
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| outputpower | double | NO | | NULL | |
+-------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO `powerplant`.`genarator` (`id`, `outputpower`) VALUES ('1', '10');
Query OK, 1 row affected (0.32 sec)
mysql> INSERT INTO `powerplant`.`turbine` (`id`, `manufacturer`, `details`, `genarator`, `weight`) VALUES ('1', 'asitha', 'no', '1', '10');
ERROR 1054 (42S22): Unknown column 'weight' in 'field list'
mysql>
mysql> ALTER TABLE `turbine`
-> ADD COLUMN `weight` DOUBLE NULL AFTER `genarator`;
Query OK, 0 rows affected (0.60 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `powerplant`.`turbine` (`id`, `manufacturer`, `details`, `genarator`, `weight`) VALUES ('1', 'asitha', 'no', '1', '10');
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM 'genarator';
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 ''genarator'' at line 1
mysql> SELECT * FROM genarator;
+----+-------------+
| id | outputpower |
+----+-------------+
| 1 | 10 |
+----+-------------+
1 row in set (0.01 sec)
mysql> SELECT * FROM turbine;
+----+--------------+---------+-----------+--------+
| id | manufacturer | details | genarator | weight |
+----+--------------+---------+-----------+--------+
| 1 | asitha | no | 1 | 10 |
+----+--------------+---------+-----------+--------+
1 row in set (0.00 sec)
mysql> UPDATE `powerplant`.`turbine` SET `manufacturer`='man1', `details`='yes' WHERE `id`=1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM turbine;
+----+--------------+---------+-----------+--------+
| id | manufacturer | details | genarator | weight |
+----+--------------+---------+-----------+--------+
| 1 | man1 | yes | 1 | 10 |
+----+--------------+---------+-----------+--------+
1 row in set (0.00 sec)
mysql> INSERT INTO `powerplant`.`turbine` (`id`, `manufacturer`, `details`, `genarator`, `weight`) VALUES ('2', 'man2', 'no', '1', '20');
Query OK, 1 row affected (0.30 sec)
mysql> SELECT * FROM turbine;
+----+--------------+---------+-----------+--------+
| id | manufacturer | details | genarator | weight |
+----+--------------+---------+-----------+--------+
| 1 | man1 | yes | 1 | 10 |
| 2 | man2 | no | 1 | 20 |
+----+--------------+---------+-----------+--------+
2 rows in set (0.00 sec)
mysql> DELETE FROM `powerplant`.`turbine` WHERE `id`=2;
Query OK, 1 row affected (0.31 sec)
mysql> SELECT * FROM turbine;
+----+--------------+---------+-----------+--------+
| id | manufacturer | details | genarator | weight |
+----+--------------+---------+-----------+--------+
| 1 | man1 | yes | 1 | 10 |
+----+--------------+---------+-----------+--------+
1 row in set (0.00 sec)
mysql> SELECT details from turbine;
+---------+
| details |
+---------+
| yes |
+---------+
1 row in set (0.00 sec)
mysql> INSERT INTO `powerplant`.`turbine` (`id`, `manufacturer`, `details`, `genarator`, `weight`) VALUES ('2', 'man2', 'no', '1', '20');
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM turbine;
+----+--------------+---------+-----------+--------+
| id | manufacturer | details | genarator | weight |
+----+--------------+---------+-----------+--------+
| 1 | man1 | yes | 1 | 10 |
| 2 | man2 | no | 1 | 20 |
+----+--------------+---------+-----------+--------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM turbine WHERE id='1';
+----+--------------+---------+-----------+--------+
| id | manufacturer | details | genarator | weight |
+----+--------------+---------+-----------+--------+
| 1 | man1 | yes | 1 | 10 |
+----+--------------+---------+-----------+--------+
1 row in set (0.00 sec)
mysql> exit
Bye
continued of mysql quary is in my other post next post
visit MySQL quary part 2
visit MySQL quary part 3
visit MySQL quary part 4
We can use HeidiSQL or PHPmyadmin like software for easily done mysql database manage.
Thank you Rubictron! This was very helpful.
ReplyDelete