226 lines
No EOL
6 KiB
Markdown
226 lines
No EOL
6 KiB
Markdown
# SQL
|
||
|
||
- **Importing and exporting mysql dump file**
|
||
1. To import:
|
||
|
||
```bash
|
||
mysql -u username -p database_name < file.sql
|
||
```
|
||
|
||
1. To export:
|
||
`mysqldump -u root -p hospitals > C:\Users\harsh\Downloads\Documents\hospitals.sql`
|
||
2. to export only schema
|
||
|
||
- Postgresql
|
||
|
||
to load postgresql database
|
||
|
||
```bash
|
||
psql -U postgres smart-brain
|
||
```
|
||
|
||
Here postgres is owner name and smart-brain is name of data base. It will prompt for password which is U2FsdGVkX1/k+uOTLs4gSYVmxYAJEQybNVGnxIQVHyg=. Refer to
|
||
|
||
[Web development](../../Export-9fad9947-5bd5-4195-952b-6512c5a4beb1/Web%20development%2083dd4e6675f04ba782a6865a434a0598/Web%20development%2083dd4e6675f04ba782a6865a434a0598.md)
|
||
|
||
for decrypting it.
|
||
|
||
To load postgresql without database:
|
||
|
||
`psql -U postgres`
|
||
|
||
To create database:
|
||
|
||
`createdb mydb -U postgres`
|
||
|
||
or
|
||
|
||
```bash
|
||
psql -U postgres
|
||
create database mydb;
|
||
```
|
||
|
||
To get list of database:
|
||
|
||
`\l`
|
||
|
||
To connect to database when login as user
|
||
|
||
`\connect dbname`
|
||
|
||
`\q`: To quit
|
||
- List tables, views and sequences
|
||
`\d`
|
||
- Describe tables
|
||
`\d <table_name>`
|
||
|
||
- MYSQL
|
||
|
||
Online compiler: [https://www.mycompiler.io/new/sql](https://www.mycompiler.io/new/sql)
|
||
|
||
To load mysql shell in command prompt:
|
||
|
||
`mysql -u root -p`
|
||
|
||
Here root is owner_name. This will prompt for password which is ‘password’.
|
||
|
||
To get list of database:
|
||
|
||
`show databases;`
|
||
|
||
To create database
|
||
```sql
|
||
drop database if exists database_name;
|
||
create database database_name;
|
||
```
|
||
|
||
To load database
|
||
|
||
`use hospitals;`
|
||
|
||
hospitals here is name of database
|
||
|
||
Once database is loaded, to get list of tables:
|
||
|
||
`show tables;`
|
||
|
||
To get schema of tables:
|
||
|
||
`desc patients;` or `describe patients;`
|
||
|
||
where patients is name of table
|
||
|
||
- Creating table
|
||
|
||
```sql
|
||
create table project (
|
||
emp_id int not null,
|
||
id int not null,
|
||
name varchar(30),
|
||
location varchar(40),
|
||
primary key(emp_id)
|
||
);
|
||
```
|
||
- Adding Auto increment
|
||
|
||
```sql
|
||
-- To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:
|
||
ALTER TABLE Persons AUTO_INCREMENT=100;
|
||
|
||
alter table employee
|
||
modify emp_id int not null auto_increment;
|
||
```
|
||
|
||
- Constraints
|
||
|
||
[How to use ADD CONSTRAINT in SQL (with examples) (cockroachlabs.com)](https://www.cockroachlabs.com/blog/sql-add-constraint/)
|
||
|
||
- LIKE
|
||
|
||
[SQL LIKE Operator (w3schools.com)](https://www.w3schools.com/SQL/sql_like.asp)
|
||
|
||
SELECT *column1, column2, ...* FROM *table_name* WHERE *columnN* LIKE *pattern*;
|
||
|
||
**Tip:** You can also combine any number of conditions using `AND` or `OR` operators.
|
||
|
||
Here are some examples showing different `LIKE` operators with '%' and '_' wildcards:
|
||
|
||
| LIKE Operator | Description |
|
||
| --- | --- |
|
||
| WHERE CustomerName LIKE 'a%' | Finds any values that start with "a" |
|
||
| WHERE CustomerName LIKE '%a' | Finds any values that end with "a" |
|
||
| WHERE CustomerName LIKE '%or%' | Finds any values that have "or" in any position |
|
||
| WHERE CustomerName LIKE '_r%' | Finds any values that have "r" in the second position |
|
||
| WHERE CustomerName LIKE 'a_%' | Finds any values that start with "a" and are at least 2 characters in length |
|
||
| WHERE CustomerName LIKE 'a__%' | Finds any values that start with "a" and are at least 3 characters in length |
|
||
| WHERE ContactName LIKE 'a%o' | Finds any values that start with "a" and ends with "o" |
|
||
- JOIN
|
||
|
||
```sql
|
||
create table Member (
|
||
'Member ID' int not null,
|
||
'First Name' varchar(50),
|
||
'Last Name' varchar(50),
|
||
gender varchar(10),
|
||
age int not null,
|
||
country varchar(50),
|
||
joined_date date,
|
||
primary key('Member ID')
|
||
);
|
||
|
||
create table Booking (
|
||
BookingID int not null,
|
||
Room varchar(50),
|
||
|
||
Days int,
|
||
cost per day float,
|
||
status varchar(30),
|
||
primary key(BookingID)
|
||
);
|
||
|
||
create table Make (
|
||
Date date,
|
||
MemberID int not null,
|
||
BookingID int not null
|
||
);
|
||
|
||
-- Main ............
|
||
SELECT 'First Name', 'Last Name', Age, 'Date'
|
||
FROM Member as me JOIN Make as ma JOIN Booking as b
|
||
ON me.'Member ID'=ma.'MemberID' and ma.BookingID=b.BookingID;
|
||
```
|
||
|
||
- Insert into
|
||
|
||
```sql
|
||
INSERT INTO table_name (column1, column2, column3, ...)
|
||
VALUES (value1, value2, value3, ...);
|
||
```
|
||
|
||
or
|
||
|
||
```sql
|
||
INSERT INTO table_name
|
||
VALUES (value1, value2, value3, ...);
|
||
```
|
||
|
||
For multiple rows
|
||
|
||
```sql
|
||
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
|
||
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ), (5, 'Hardik', 27, 'Bhopal', 8500.00 ), (6, 'Komal', 22, 'MP', 4500.00 );
|
||
```
|
||
|
||
- Delete records
|
||
```sql
|
||
-- to delete all records
|
||
delete from table_name;
|
||
|
||
-- to delete record id=5
|
||
delete from table_name
|
||
where id=5;
|
||
|
||
```
|
||
- Date
|
||
|
||
```sql
|
||
create table Member (
|
||
...
|
||
.....
|
||
joined_date date,
|
||
|
||
);
|
||
|
||
insert into Member
|
||
values(1582,'Mara','Hash','Female',25,'Uganda','2020-08-16'), (3549,'Kathleen','Hanner','Female',25,'United States','2017-12-12');
|
||
|
||
```
|
||
|
||
|
||
If there column name contain spaces then enclose them with ticks → `MEMBER ID`
|
||
|
||
- Handwritten
|
||
|
||
[sql.pdf](../../_resources/sql.pdf)
|
||
|
||
[sql.pdf](../../_resources/sql%201.pdf) |