Foreign

Download as txt, pdf, or txt
Download as txt, pdf, or txt
You are on page 1of 5

Enter password: ****

Welcome to the MySQL monitor. Commands end with ; or \g.


Your MySQL connection id is 12
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

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 |
| mysql |
| performance_schema |
| sakila |
| shital |
| sys |
| world |
+--------------------+
7 rows in set (0.02 sec)

mysql> use shital;


Database changed
mysql> show tables;
+------------------+
| Tables_in_shital |
+------------------+
| emp |
| person |
| stud |
| student |
+------------------+
4 rows in set (0.01 sec)

mysql> create table personn(pid int not null auto_increment,name


varchar(20),primary key(pid));
Query OK, 0 rows affected (0.04 sec)

mysql> desc person;


+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| pid | int | YES | | NULL | |
| pname | varchar(20) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| country | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> desc personn;


+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| pid | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into personn(name)


values('shital','vrush','palak','vaishi','anya','nik');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into personn(name) values('shital'),('vrush'),('palak'),('vaishi'),
('anya'),('nik');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from personn;


+-----+--------+
| pid | name |
+-----+--------+
| 1 | shital |
| 2 | vrush |
| 3 | palak |
| 4 | vaishi |
| 5 | anya |
| 6 | nik |
+-----+--------+
6 rows in set (0.00 sec)

mysql> create table orders(oid int,oname varchar(20),pid int,foreign


key(pid)references personn(pid));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into order values(1,'dell',1),(2,'iphone',2),(3,'hp',2),


(4,'lenevo',3);
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 'order
values(1,'dell',1),(2,'iphone',2),(3,'hp',2),(4,'lenevo',3)' at line 1
mysql> insert into order(oid,oname,pid)values(1,'dell',1),(2,'iphone',2),
(3,'hp',2),(4,'lenevo',3);
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
'order(oid,oname,pid)values(1,'dell',1),(2,'iphone',2),(3,'hp',2),(4,'lenevo',3)'
at line 1
mysql> insert into orders values(1,'dell',1),(2,'iphone',2),(3,'hp',2),
(4,'lenevo',3);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from orders;


+------+--------+------+
| oid | oname | pid |
+------+--------+------+
| 1 | dell | 1 |
| 2 | iphone | 2 |
| 3 | hp | 2 |
| 4 | lenevo | 3 |
+------+--------+------+
4 rows in set (0.00 sec)

mysql> alter table personn add age int not null;


Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from personn;


+-----+--------+-----+
| pid | name | age |
+-----+--------+-----+
| 1 | shital | 0 |
| 2 | vrush | 0 |
| 3 | palak | 0 |
| 4 | vaishi | 0 |
| 5 | anya | 0 |
| 6 | nik | 0 |
+-----+--------+-----+
6 rows in set (0.00 sec)

mysql> update personn set age=20 where pid in(1,2)


-> ^C
mysql> update personn set age=20 where pid in(1,2);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> update personn set age=23 where pid in(3);


Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update personn set age=18 where pid in(4);


Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from personn;


+-----+--------+-----+
| pid | name | age |
+-----+--------+-----+
| 1 | shital | 20 |
| 2 | vrush | 20 |
| 3 | palak | 23 |
| 4 | vaishi | 18 |
| 5 | anya | 0 |
| 6 | nik | 0 |
+-----+--------+-----+
6 rows in set (0.00 sec)

mysql> update personn set age=28 where pid in(5,6);


Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from personn;


+-----+--------+-----+
| pid | name | age |
+-----+--------+-----+
| 1 | shital | 20 |
| 2 | vrush | 20 |
| 3 | palak | 23 |
| 4 | vaishi | 18 |
| 5 | anya | 28 |
| 6 | nik | 28 |
+-----+--------+-----+
6 rows in set (0.00 sec)
mysql> select count(age) from personn where age>18;
+------------+
| count(age) |
+------------+
| 5 |
+------------+
1 row in set (0.00 sec)

mysql> select count(age) from personn where age>21;


+------------+
| count(age) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)

mysql> select count(name) from personn where name like ;


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 '' at
line 1
mysql> select count(name) from personn where name like '%l';
+-------------+
| count(name) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)

mysql> select min(age) from personn;


+----------+
| min(age) |
+----------+
| 18 |
+----------+
1 row in set (0.00 sec)

mysql> select max(age) from personn;


+----------+
| max(age) |
+----------+
| 28 |
+----------+
1 row in set (0.00 sec)

mysql> select avg(age) from personn;


+----------+
| avg(age) |
+----------+
| 22.8333 |
+----------+
1 row in set (0.00 sec)

mysql> select count(distinct name) from personn;


+----------------------+
| count(distinct name) |
+----------------------+
| 6 |
+----------------------+
1 row in set (0.00 sec)
mysql> select count(distinct age) from personn;
+---------------------+
| count(distinct age) |
+---------------------+
| 4 |
+---------------------+
1 row in set (0.00 sec)

mysql>

You might also like