MySQL
Types of Database
i. Centralized database.
ii. Distribute data base.
iii. personal data base. like google drive
iv. end user database.
v. commercial database. like Oracle
vi. Relational database.
vii. Cloud database.
viii. Object oriented database. Like MangoDB
run it on Postman.
before create it first u need to create
npm init
or
npm install express
get all user data
add new user
delete a user
add new user
delete a user
// get all user data
// add new user
// delete a user
const expressJS = require('express');
const bodyParser = require('body-parser');
const expressApp = expressJS();
// database
let users = [
{"id": 1, "name": "Sanjeet", "Class": "7th"},
{"id": 2, "name": "Kiaran", "Class": "8th"},
];
expressApp.use(bodyParser.urlencoded({extended:false}));
expressApp.use(bodyParser.json());
// get data of all the user
expressApp.get('/user', (req,res) =>{
res.json(users).status(200);
});
// post to add new users
expressApp.post('/user', (req,res) => {
const newUser = req.body;
users.push(newUser); // add new user
res.send("user is added in database").status(200);
});
// delete a user
expressApp.delete('/user/:id', (req,res) => {
const userID = req.params.id;
users = users.filter(user =>{
if(user.id != userID){
return true;
}
return false;
});
res.send("User is deleted Successfully").status(200);
});
expressApp.listen(8000, function() {
console.log("my server is running at port 8000");
});
How to Login ur mySql Database
just go to terminal and type:
mysql -u username -p -----> enter
How to create databases
mysql> CREATE DATABASES databaseName; ----> enter
DDL---> Data Definition Language
mysql> show DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | +--------------------+ 5 rows in set (0.72 sec) mysql> use school; Database changed mysql> show DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use school; Database changed mysql> CREATE TABLE user (id int,name varchar(25)); Query OK, 0 rows affected (0.62 sec) mysql> SHOW TABLES; +------------------+ | Tables_in_school | +------------------+ | user | +------------------+ 1 row in set (0.01 sec) mysql> describe user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(25) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.07 sec) mysql> alter table user rename column id to User_id; Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe user; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | User_id | int | YES | | NULL | | | name | varchar(25) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> alter table user add column gender varchar(10); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe user; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | User_id | int | YES | | NULL | | | name | varchar(25) | YES | | NULL | | | gender | varchar(10) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table user add column category varchar(20) NOT NULL; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe user; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | User_id | int | YES | | NULL | | | name | varchar(25) | YES | | NULL | | | gender | varchar(10) | YES | | NULL | | | category | varchar(20) | NO | | NULL | | +----------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> truncate table user; Query OK, 0 rows affected (0.05 sec) mysql> drop table if exits user; 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 'exits user' at line 1 mysql> drop table if exists user; Query OK, 0 rows affected (0.06 sec) mysql> describe user; ERROR 1146 (42S02): Table 'school.user' doesn't exist mysql>
DML--> Data Manipulation Language (insert, update and delete data)
mysql> use school
Database changed
mysql> create table user(
-> id int not null,
-> name varchar(255) not null,
-> gender varchar(10)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> describe table user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)
mysql> insert into user values(1,"Sanjeet","Female");
Query OK, 1 row affected (0.02 sec)
mysql> describe table user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> select * from user;
+----+---------+--------+
| id | name | gender |
+----+---------+--------+
| 1 | Sanjeet | Female |
+----+---------+--------+
1 row in set (0.00 sec)
mysql> insert into user (id, name) values (2,"Angela");
Query OK, 1 row affected (0.01 sec)
mysql> select * from user;
+----+---------+--------+
| id | name | gender |
+----+---------+--------+
| 1 | Sanjeet | Female |
| 2 | Angela | NULL |
+----+---------+--------+
2 rows in set (0.00 sec)
mysql> update user set gender= "Female" where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+----+---------+--------+
| id | name | gender |
+----+---------+--------+
| 1 | Sanjeet | Female |
| 2 | Angela | Female |
+----+---------+--------+
2 rows in set (0.00 sec)
mysql> delete from user where id =1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from user;
+----+--------+--------+
| id | name | gender |
+----+--------+--------+
| 2 | Angela | Female |
+----+--------+--------+
1 row in set (0.00 sec)
DQL: Data Query Language
mysql> insert into user values(1, "Dharmit", "Male");
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values(1, "Dolby", "Female");
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values(1, "Sayed", "Male");
Query OK, 1 row affected (0.01 sec)
mysql> insert into user values(1, "Angela Yu", "Female");
Query OK, 1 row affected (0.01 sec)
mysql> use school;
Database changed
mysql> select * from user;
+----+-----------+--------+
| id | name | gender |
+----+-----------+--------+
| 2 | Angela | Female |
| 1 | Dharmit | Male |
| 1 | Dolby | Female |
| 1 | Sayed | Male |
| 1 | Angela Yu | Female |
+----+-----------+--------+
5 rows in set (0.00 sec)
mysql> select * from user where gender="Female";
+----+-----------+--------+
| id | name | gender |
+----+-----------+--------+
| 2 | Angela | Female |
| 1 | Dolby | Female |
| 1 | Angela Yu | Female |
+----+-----------+--------+
3 rows in set (0.01 sec)
mysql> select id,name from user where gender="Female";
+----+-----------+
| id | name |
+----+-----------+
| 2 | Angela |
| 1 | Dolby |
| 1 | Angela Yu |
+----+-----------+
3 rows in set (0.00 sec)
mysql> select * from user order id;
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 'id' at line 1
mysql> select * from user order by id;
+----+-----------+--------+
| id | name | gender |
+----+-----------+--------+
| 1 | Dharmit | Male |
| 1 | Dolby | Female |
| 1 | Sayed | Male |
| 1 | Angela Yu | Female |
| 2 | Angela | Female |
+----+-----------+--------+
5 rows in set (0.00 sec)
mysql> select * from user order by desc;
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 'desc' at line 1
mysql> select * from user order by id desc;
+----+-----------+--------+
| id | name | gender |
+----+-----------+--------+
| 2 | Angela | Female |
| 1 | Dharmit | Male |
| 1 | Dolby | Female |
| 1 | Sayed | Male |
| 1 | Angela Yu | Female |
+----+-----------+--------+
5 rows in set (0.00 sec)
mysql> select name from user order by id desc;
+-----------+
| name |
+-----------+
| Angela |
| Dharmit |
| Dolby |
| Sayed |
| Angela Yu |
+-----------+
5 rows in set (0.00 sec)
mysql> select name from user order by name desc;
+-----------+
| name |
+-----------+
| Sayed |
| Dolby |
| Dharmit |
| Angela Yu |
| Angela |
+-----------+
5 rows in set (0.00 sec)
mysql> select name from user where id > 2 order by gender desc;
Empty set (0.00 sec)
mysql> select name from user where id > 3 order by gender desc;
Empty set (0.00 sec)