joplin-notes/Export-9fad9947-5bd5-4195-952b-6512c5a4beb1/Web development 83dd4e6675f04ba782a6865a434a0598/SQL 4f218c66fb524cc1ba28ef7c5342e101.md

SQL

• Importing and exporting mysql dump file

  1. To import:

  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

  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

  for decrypting it.

  To load postgresql without database:

  psql -U postgres

  To create database:

  createdb mydb -U postgres

  or

  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

  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

   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

    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

    -- 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)

  • LIKE

    SQL LIKE Operator (w3schools.com)

    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

    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

    INSERT INTO table_name (column1, column2, column3, ...)
    VALUES (value1, value2, value3, ...);
    

    or

    INSERT INTO table_name
    VALUES (value1, value2, value3, ...);
    

    For multiple rows

    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

  -- to delete all records
  delete from table_name;
  
  -- to delete record id=5
  delete from table_name 
  where id=5;
  
  

  • Date

    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

  sql.pdf