Getting Started with SQL学习笔记

SQL

目录

CH1

CH4 Select

CH5 Where

CH6 Group by & Order by

CH7 CASE

CH8 Join

CH9 Database Design

CH10 Managing Data

Appendix A/B

---

Chapter 1

1. SQL stands for Structured Query Language

Chapter 4

select

1. use select to select columns

   select * from customers
   

2. use round(1.0000,2) to round the data

   round(price*1.07,2) as taxed_price
   

   round the number to 2 decimal places

3. use || to concatenate columns

   select name,
   city || ', ' || state as location
   from customer;
   

   MySQL and some others require using concat() func.

Chapter 5

where

1. using where on numbers

   SELECT * FROM station_data
   WHERE year = 2010;
   

2. can alse use !=,<> to get but 2010

   WHERE year <> 2010
   

3. Between … and …

   WHERE year BETWEEN 2005 and 2010
   

   can also use and (but a little verbose)

   where year>=2005 and year<=2010
   

4. IN

   WHERE MONTH IN (3,6,9,12)
   WHERE MONTH NOT IN (3,6,9,12)
   

5. must wrap text in single quotes

6. some more e.p.

   WHERE length(report_code) != 6
   

7. % & _

   • % is any number of characters and
   • _ is any single character

   WHERE report_code LIKE 'A%'
   

8. SQLite expects you to explicitly use 1 for true and 0 for false.

   WHERE tornado = 1 AND hail = 1;
   WHERE tornado AND hail;
   

   WHERE tornado = 0 AND hail = 1;
   WHERE NOT tornado AND hail;
   

9. ways to quary null

   • Nulls rarely qualify with anything and almost always get filtered out in a WHERE unless you explicitly handle them. So you have to use an OR to include nulls:

   WHERE precipitation IS NULL OR precipitation <= 0.5;
   

10. coalesce()

    • convert null to 0:

    WHERE coalesce(precipitation, 0) <= 0.5;
    

    • a coalesce() can be used in the SELECT statement too, and not just the WHERE

    SELECT report_code, coalesce(precipitation, 0) as rainfall 
    FROM station_data;
    

Chapter 6

group by & order by

1. order by

   SELECT year, month, COUNT(*) AS record_count FROM station_data
    WHERE tornado = 1
    GROUP BY year, month
   

2. order by

   ORDER BY year, month
   

3. Aggregate Functions

   SELECT month, AVG(temp) as avg_temp
   FROM station_data
   WHERE year >= 2000
   GROUP BY month
   

4. having

   SELECT year,
   SUM(precipitation) as total_precipitation
   FROM station_data
   GROUP BY year
   HAVING SUM(precipitation) > 30
   

   HAVING is the aggregated equivalent to WHERE

5. distinct

   SELECT DISTINCT station_number FROM station_data
   

Chapter 7

CASE Statements

1. case

   • You start a CASE statement with the word CASE and conclude it with an END.
   • Between those keywords, you specify each condition with a WHEN [condition] THEN [value]
   • have a catch-all value to default to if none of the conditions were met

   SELECT report_code, year, month, day, wind_speed,
   CASE
       WHEN wind_speed >= 40 THEN 'HIGH'
       WHEN wind_speed >= 30 AND wind_speed < 40 THEN 'MODERATE'
       ELSE 'LOW'
   END as wind_severity
   FROM station_data
   

   SELECT year,
   CASE
       WHEN wind_speed >= 40 THEN 'HIGH'
       WHEN wind_speed >= 30 THEN 'MODERATE'
       ELSE 'LOW'
   END as wind_severity,
   COUNT(*) as record_count
   FROM station_data
   GROUP BY 1, 2
   

2. The “Zero/Null” CASE Trick

   SELECT year, month,
   SUM(CASE WHEN tornado = 1 THEN precipitation ELSE 0 END) as tornado_precipitation,
   SUM(CASE WHEN tornado = 0 THEN precipitation ELSE 0 END) as non_tornado_precipitation
   
   FROM station_data
   GROUP BY year, month
   

Chapter 8

Join

1. inner join

   • With an INNER JOIN, any records that do not have a common joined value in both tables will be excluded.

   SELECT 
   CUSTOMER.CUSTOMER_ID,
   NAME,
   STREET_ADDRESS,
   CITY,
   STATE,
   ZIP,
   ORDER_DATE,
   SHIP_DATE,
   ORDER_ID,
   PRODUCT_ID,
   ORDER_QTY
   FROM CUSTOMER INNER JOIN CUSTOMER_ORDER
   ON CUSTOMER.CUSTOMER_ID = CUSTOMER_ORDER.CUSTOMER_ID
   

2. left join

   • If we want to include all records from the CUSTOMER table, we can accomplish this with a LEFT JOIN.
   • The LEFT JOIN will include all records on the “left” table, even if they have nothing to join to on the “right” table (which will be null)

   SELECT CUSTOMER.CUSTOMER_ID,
   NAME,
   STREET_ADDRESS,
   CITY,
   STATE,
   ZIP,
   ORDER_DATE,
   SHIP_DATE,
   ORDER_ID,
   PRODUCT_ID,
   ORDER_QTY
   FROM CUSTOMER left JOIN CUSTOMER_ORDER
   ON CUSTOMER.CUSTOMER_ID = CUSTOMER_ORDER.CUSTOMER_ID
   

3. Grouping JOINs

   SELECT
   CUSTOMER.CUSTOMER_ID,
   NAME AS CUSTOMER_NAME,
   sum(ORDER_QTY * PRICE) as TOTAL_REVENUE
   FROM CUSTOMER_ORDER
   INNER JOIN CUSTOMER
   ON CUSTOMER.CUSTOMER_ID = CUSTOMER_ORDER.CUSTOMER_ID
   INNER JOIN PRODUCT
   ON CUSTOMER_ORDER.PRODUCT_ID = PRODUCT.PRODUCT_ID
   GROUP BY 1,2
   

Chapter 9

Database Design

1. Design questions

   • What are the business requirements?
   • What tables will I need to fulfill those requirements?
   • What columns will each table contain?
   • How will the tables be normalized?
   • What will their parent/child relationships be?

   Populating data should be part of the planning process too. If the data is not maintainable and kept up to date, then the design has already failed.

2. Data questions

   • How much data will be populated into these tables?
   • Who/what will populate the data into these tables?
   • Where will the data come from?
   • Do we need processes to automatically populate the tables?

3. Security questions

   • Who should have access to this database?
   • Who should have access to which tables? Read-only access? Write access?
   • Is this database critical to business operations?
   • What backup plans do we have in the event of disaster/failure?
   • Should changes to tables be logged?
   • If the database is used for websites or web applications, is it secure?

4. primary key

   • if you specified primary key on the two fields , you can never have two records with the same combination
   • No duplicates are allowed

5. foreign key

   • a foreign key does not enforce uniqueness, as it is the “many” in a “one-to-many” relationship.

6. creat table

   CREATE TABLE COMPANY (
       COMPANY_ID INTEGER PRIMARY KEY AUTOINCREMENT,
       NAME VARCHAR(30) NOT NULL,
       DESCRIPTION VARCHAR(60),
       PRIMARY_CONTACT_ID INTEGER NOT NULL
   );
   

   column constraints: PRIMARY KEY, FOREIGN KEY, NOT NULL, AUTOINCREMENT, and DEFAULT, etc

7. foreign key

   • foreign key in a child table isntied to the primary key of a parent table

8. Creating Views

   CREATE VIEW [view_name] AS [a SELECT query]
   

Chapter 10

Managing Data

1. multiple insert

   INSERT INTO ATTENDEE (FIRST_NAME, LAST_NAME, PHONE, EMAIL, VIP)
   VALUES
   ('Jon', 'Skeeter',4802185842,'john.skeeter@rex.net', 1),
   ('Sam','Scala',2156783401,'sam.scala@gmail.com', 0),
   ('Brittany','Fisher',5932857296,'brittany.fisher@outlook.com', 0)
   

2. migrate data from one table to another

   INSERT INTO ATTENDEE (FIRST_NAME, LAST_NAME, PHONE, EMAIL)
   SELECT FIRST_NAME, LAST_NAME, PHONE, EMAIL
   FROM SOME_OTHER_TABLE
   

3. delete

   DELETE FROM ATTENDEE
   WHERE PHONE IS NULL
   AND EMAIL IS NULL
   

   • Executing that query gives us a preview of what records will be deleted:

   SELECT * FROM ATTENDEE
   WHERE PHONE IS NULL
   AND EMAIL IS NULL
   

4. TRUNCATE TABLE

   TRUNCATE TABLE ATTENDEE
   

   • not used in SQLite
   • allow the database engine to reset the autoincrements for any primary keys as well as any other constraint behaviors. It also allows it to make some optimizations behind the scenes to reset the table.
   • sqlite allow some similar optimizations when you run a DELETE without a WHERE.

5. update

   • The UPDATE modifies existing records.

   UPDATE ATTENDEE SET EMAIL = UPPER(EMAIL);
   
   UPDATE ATTENDEE SET FIRST_NAME = UPPER(FIRST_NAME), LAST_NAME = UPPER(LAST_NAME);
   
   UPDATE ATTENDEE SET VIP = 1
   WHERE ATTENDEE_ID IN (3,4);
   

6. drop

   • remove a table altogether

   DROP TABLE MY_UNWANTED_TABLE
   

Appendix A

Operators and Functions

1. Date and Time Functions

   SELECT DATE('now');
   SELECT DATE('now','-1 day');
   

   SELECT TIME('now')
   SELECT TIME('16:31','+1 minute')
   

APPENDIX B

Supplementary Topics

1. Further Topics of Interest

   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-INkluA3B-1617552209580)(pic/Further%20Topics%20of%20Interest.jpg)]

2. index

   CREATE INDEX price_index ON PRODUCT(price);
   DROP INDEX price_index;
   

   • We name the index price_index and we apply it on the PRODUCT table, and in parentheses we specify it on the price column.
   • indexes should only be used for very large tables that have noticeable performance issues with SELECT statements. You should avoid using indexes on small tables as the overhead will actually slow performance down. You should also avoid using indexes on tables that update heavily and frequently.

3. Transactions

   • BEGIN TRANSACTION; or BEGIN; :begin the transacton until ‘commit’ or ‘ROLLBACK’
   • COMMIT : save the change
   • ROLLBACK : cancel the change

   BEGIN;
   DELETE FROM COMPANY WHERE AGE = 25;
   ROLLBACK;
   

   BEGIN;
   DELETE FROM COMPANY WHERE AGE = 25;
   COMMIT;