MYSQL

Relational database systems use a model that organizes data into tables of rows (also called records or tuples) and columns (also called attributes or fields). Generally, columns represent categories of data, while rows represent individual instances.These tables can be linked or related using keys. Each row in a table is identified using a unique key, called a primary key. This primary key can be added to another table, becoming a foreign key.
1.can not open a file from zip(idk why), should unzip first
2.first execute icon → left sidebar to refresh(do not forget to display the sidebar) → click the rightmost icon from a table in which want to process data → can close the database and focus on the table
3.‘;’ after one execution command, ’ ’ /" " need to be put around data value including(some numbers in database) pay attention: any space in quotes is a space
4.can do arithmetic (multiplication, subtraction…), put in a new line, the use of parenthesis(), - - two hyphen mean notation
5.KEY words : SELECT, FROM, AS, WHERE, ORDER BY, AND, OR(order precedence: AND > OR), NOT, IN, BETWEEN, REGEXP, NULL, DESC,LIMIT, (INNER)JOIN, ON, OUTER JOIN, USING, CROSS, NATURAL
7.>, <, <>/! = ,=, ….
‘%’ →any numbers of the character (‘b%’ which indicates start with b and no matter how many numbers after b) , ’ _’ →just single character,
‘^’ →the beginning of a string, '${}^{\prime }\to theendofastring{,}^{\prime }{|}^{\prime }\to logicalOR{,}^{\prime }[a-c{]}^{\prime }{,}^{\prime }[abcd{]}^{\prime }(te$|ks$ ) means either end with te or ks and (te|ks) $ is wrong
8. state = ‘VA’ OR ‘FL’ is false because OR can not combine boolean with string so it can only state = ‘VA’ OR state =‘FL’ or state IN (‘VA’, ‘FL’)
9. SELECT * FROM sql_invoicing.invoices WHERE client_id=2 而SELECT client_id=2 FROM sql_invoicing.invoices这个是错误的，应该是select 后面只能加coloum名字
SELECT *，client_id * invoice_total AS price_total FROM sql_invoicing.invoices ORDER BY price_total DESC 而不是SELECT * FROM sql_invoicing.invoices ORDER BY client_id * invoice_total AS price_total DESC
10.defalut query order is the unique value So in relational databases everytable should have a primary key column, and the values in that column should uniquely identify the records in that table.
10. LIMIT 6,3: 6 is an offset, skip the first 6 records and resturn next 3 records LIMIT 3: return only the first 3 records
11. INT, VARCHAR(50), PK,NN,AI,DEFAULT
12. execute 时select one database
13. the second excute I’ll just execute whatever statement your cursor is inside of which
14. if you have run create database and after that you write all other your coloumns/properities in then you will not succeed bec datebase exist. Maybe you can just use the SECOND exectue icon just after every time you just add a new command or content(https://www.youtube.com/watch?v=p3qvj9hO_Bo&t=764s 13.05)
15. 如果提示一些1064啥的error第一个先看上一行内容有没有用逗号了
16.ASC: ascending order(A-Z) DESC: descending order(Z-A)

create

albums has the primary key because that is the key to finding the album records from each other the uniqueness and band ID is referencing the band’s table so that is a foreign key referencing a foreign table so we need to define that relationship between the band ID and the band table. sequel will no longer let us create an album if we give it a band ID that doesn’t already exist in the band table also if we try to delete a
band that has albums linking to that
band it’ll throw an error saying that
you have a albums that exists for this
band so you can’t delete the band unless
you also delete the albums that go with
that band

CREATE DATABASE record_company;
USE record_company;
CREATE TABLE bands (
	id INT NOT NULL AUTO_INCREMENT,
    -- add an ID column in order to uniquely identify that row in that table from all the other rows inside(distinguish these two bands from each other) and we can save that ID in the albums table and that'll allow us to reference the band's table
from within the albums table
	name VARCHAR(255) NOT NULL,
    -- our column can no longer have any null values inside of it which means it must always have a name defined
    PRIMARY KEY (id)
);
CREATE TABLE albums (
	id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    release_year INT,
    band_id INT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (band_id) REFERENCES bands (id)
);

为什么我FOREIGN KEY (band_id) REFERENCES bands (id)后我都foreign keys没有反应啊

inner join:

SELECT order_id, o.customer_id, first_name, last_name
From oreders o 
JOIN customers c
	ON o.customer_id = c.customer_id;

join across database:

USE sql_inventory;
SELECT *
FROM sql_store.order_items oi
JOIN products p 
	ON oi.product_id = p.product_id;

self join:

USE sql_hr;
SELECT 
	e.employee_id,
	e.first_name,
	m.first_name AS manager   
FROM employees e
JOIN employees m      (must have different alias)
	ON e.reports_to = m.employee_id;

join multiple tables:

USE sql_store;
SELECT 
	o.order_id,
    o.order_date,
    c.first_name,
    c.last_name,
    os.name AS status
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id
JOIN order_statuses os
	ON o.status = os.order_status_id

compound join conditions

SELECT *
FROM order_items oi
JOIN order_item_notes oin
	ON oi.order_id = oin.order_id
    AND oi.product_id = oin.product_id

Implicit Join Syntax

SELECT *
FROM orders o
JOIN customers c
   ON o.customer_id = c.customer_id;
-- Implicit Join Syntax
SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id;

if foget WHERE o.customer_id = c.customer_id: 相当于一条customer记录要对应所有的order记录，下一条customer记录要对应所有的order记录…这样才能把所有信息显示完(don’t forget customer_id is different in o and c)但是为什么啊？？？看不懂 喔喔，我知道了因为他没有WHERE的条件标准，所以就会将表格所有内容显示出来，而是combination- 10*(10C1)
SLECT 就是要显示的coloum 然后where就是条件筛选，最后output所有相关的记录

outer join:

WITHOUT the key word LEFT/RIGHT We only see customers who have an order in our order systems like 2,5,6 (c.customer_id = o.customer_id:for a given customer, if we do have an order and match his condition, that record is returned.) and if we want to see customers whether they have an order or not不管在order里有没有他们的顺序 like customer_id 1:Babara, we need outer join
left outer join:
When we use a left join, all the records from the left table, in this case customers are returned whether this condition is true or not. So we get all the customers, and if they do have an order, we’ll see the order id as well.

SELECT 
	c.customer_id,
    c.first_name,
    o.order_id
 FROM customers c
 LEFT JOIN orders o
	ON c.customer_id = o.customer_id
ORDER BY c.customer_id

right outer join:
Now what if we use a right join. In this case, all the records from the orders table are returned whether this condition is true or not.

SELECT 
	c.customer_id,
    c.first_name,
    o.order_id
 FROM orders o
RIGHT JOIN customers c
	ON c.customer_id = o.customer_id
ORDER BY c.customer_id

outer join between multiple tables:

SELECT 
	c.customer_id,
    c.first_name,
    o.order_id,
    sh.shipper_id,
    sh.name AS shipper
FROM customers c
 LEFT JOIN orders o
	ON c.customer_id = o.customer_id
LEFT JOIN shippers sh
	ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id

when list the column, pay attention to coloum name like id is not the specific status
self outer join:
because we want to get every employee in this table whether they have a manager or not

USE sql_hr;
SELECT 
	e.employee_id,
	e.first_name,
	m.first_name AS manager   
FROM employees e
LEFT JOIN employees m      
	ON e.reports_to = m.employee_id;

the using clause

SELECT *
FROM order_items oi
JOIN order_item_notes oin
--	ON oi.order_id = oin.order_id
--    AND oi.product_id = oin.product_id
USING (order_id,product_id)

if the column name is exactly the same across different tables.

natural join

SELECT 
	o.order_id,
    c.first_name
FROM orders o
NATURAL JOIN customers c

difference between natural and inner join

cross join:

all combination of ambur with different products–combiantion-10*(10C1)

SELECT 
	c.first_name AS customer,
    p.name AS product
FROM customers c
CROSS JOIN products p
-- implicit syntax
FROM customers c,products p
ORDER BY c.first_name

union

combine the result-set of two or more SELECT statements.(one requirement:Every SELECT statement within UNION must have the same number of columns)

SELECT
	order_id,
    order_date,
    'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT
	order_id,
    order_date,
    'Archived' AS status
FROM orders
WHERE order_date < '2019-01-01'

--so this one listing all the customers and shippers in one coloumn named first_name
SELECT first_name
FROM customers
UNION 
SELECT name
FROM shippers

WHERE points BETWEEN 2000 AND 3000
WHERE 2000 <= points <= 3000 is false and it shows all elements without range boundary

insert

先点开带tool icon的，然后根据他们的属性和变量去写(If AI not need to write)

INSERT INTO customers
VALUES (
	DEFAULT,
    'John',
    'Smith',
    '1990-01-01',
    NULL,
    'address',
    'city',
    'CA',
    30)
--another
INSERT INTO customers (
	first_name,
    last_name,
    birth_date,
    address,
    city,
    state,
    points
)
VALUES (
	'John',
    'Smith',
    '1990-01-01',
    'address',
    'city',
    'CA',
    30)

insert hierarchical rows

INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2019-01-02', 1);
-- if the previous orders have 10 rows, now the orders will add a new row and have 11 rows
INSERT INTO order_items
VALUES
	(last_insert_id(),1,1,2.95),
    (last_insert_id(),2,1,3.95)
--the order_items will add 2 rows have same number--11   

create a copy of a table

CREATE TABLE orders_archived AS
SELECT * FROM orders

delete all data in orders_archived(not delet the table): just right click the truncate option
select is a subquery(a subquery is a select statement that is within another SQL
statement.) in an insert statement and bec the present one is deleted it’s just empty for new

INSERT INTO orders_archived
SELECT * FROM orders
WHERE order_date < '2019-01-01'

drop the table

DROP DATABASE orders_archived

USE sql_invoicing;
CREATE TABLE invoices_archived AS
SELECT i.invoice_id,
	   i.number,
       i.client_id AS client,
       i.invoice_total,
       i.payment_total,
       invoice_date,-- bec these three are unique in all table
       due_date,
       payment_date
FROM invoices i
JOIN clients c
	USING (client_id) 
--do not forget use USING key word to replace ON i.client_id=c.client_id
WHERE payment_date IS NOT NULL

子查询Subquery

SELECT......(SELECT.....)
FROM.......(SELECT.....)
WHERE.....(SELECT.....)

update a single row

UPDATE invoices
SET payment_total = 10, payment_date = '2019-01-01'
--SET payment_total = invoice_total * 5/ DEFAULT, payment_date = due_date
WHERE invoice_id =1

update multiple rows

click off Safe Updates and don’t foegrt to log out and then go back–>pass those multipe rows
make sure you are it the bold font database before use your table in the aimed database

UPDATE invoices
SET payment_total = invoice_total * 5, payment_date = due_date
WHERE client_id IN (3,4)

using subquery in udate
imagine the user types in the name of the client, so first we should find theid for that client, and then use that id to update all the invoices

UPDATE invoices
SET payment_total = invoice_total * 0.5, 
	payment_date = due_date
WHERE client_id IN 
				(SELECT client_id
                FROM clients
                WHERE state IN ('CA', 'NY'))
               -- = 
			   --(SELECT client_id
               --FROM clients
               --WHERE name = 'Myworks')
--The IN operator allows you to specify multiple values in a WHERE clause. 

The IN operator allows you to specify multiple values in a WHERE clause.
you can fisrt selcet the subquey and click excute to see what clinet id we get and )


Question: you nedd refer the comments in orders of customers who have points above 3000 as gold

UPDATE orders
SET comments = 'GOLD'
WHERE customer_id IN 
				(SELECT customer_id
                FROM customers 
                WHERE points > 3000)

deleting row

DELETE FROM invoices
WHERE client_id = (
				SELECT client_id 
				FROM clients
				WHERE name = 'Myworks'
				--operand should contain 1 column(s) if 
				--SELECT *
				--FROM clients
				--WHERE name = 'Myworks'
)

restore the database to the previous one–>file–>open sql script–>excute the wanted one–>refresh

Aggregate functions

A function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning

Count(*): Returns total number of records .i.e 6.
Sum(salary): Sum all Non Null values of Column salary i.e., 310
Avg(salary) = Sum(salary) / count(salary) = 310/5
Min(salary): Minimum value in the salary column except NULL i.e., 40.
Max(salary): Maximum value in the salary i.e., 80.

GROUP BY

The GROUP BY statement groups rows that have the same values into summary rows
相当于按照国家分类，计算个数find the number of customers in each country:

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

Having

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.
lists the number of customers in each country and Only include countries with more than 5 customers:

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

String function

string function
SELECT SUBSTRING(‘SQL Tutorial’, 1, 3) AS ExtractString ----- SQL
The UPPER() function converts a string to upper-case.
SELECT CONCAT(‘SQL’, ’ is’, ’ fun!') ----- SQL is fun!
LENGTH() not LEN()
SELECT REPLACE(‘SQL Tutorial’, ‘SQL’, ‘HTML’); ---- HTML Tutorial

Date/Time operator

function
NOW() — Returns the current date and time as a value in ‘YYYY-MM-DD HH:MM:SS’
SELECT DATE(‘2003-12-31 01:02:03’) ---- 2003-12-31
( SELECT DATE(‘2018-08-29’) - DATE(‘2018-01-01’) AS days )
SELECT TIMESTAMP(“2017-07-23”) ----- 2017-07-23 00:00:00
EXTRACT(part FROM date)
SELECT DATEDIFF(hour, ‘2017/08/25 07:00’, ‘2017/08/25 12:45’) AS DateDiff----5

SELECT ROUND((UNIX_TIMESTAMP(DATE(‘2018-08-29’)) - UNIX_TIMESTAMP(DATE(‘2018-01-01’))
/ (606024) , 0) AS days; ---- 240 why?
SELECT DATEDIFF(DATE(‘2018-08-29’) , DATE(‘2018-01-01’)) AS days;

Other functions

SELECT CAST(25.65 AS int) ---- 25
SELECT ROUND(235.415, 2) AS RoundValue — 235.420 (Round the number to 2 decimal places, 2.83620364 can be round to two decimal places as 2.84)
ROUND( number, decimal_places [, operation ] )这个第三个参数：如果为0或不存在，则正常四舍五入；如果为具体数字，则把后面的数字都变成0，不四舍五入 https://www.techonthenet.com/sql_server/functions/round.php
The OFFSET clause specifies the number of rows to skip before starting to return rows from the query.
SELECT * FROM Customers LIMIT 3;
ABS(number)绝对值
SQUARE(8)–64
SELECT POWER(4, 2);–4^2=16 mysql没有square``

Nested Queries

When it comes time to actually implement this inside the system, most query optimizer will try to rewrite this as a JOIN right because it the worst way to actually execute this is to essentially have two for loops where you loop over every single tuple in the student table and for every single two but you then reimpose the same query over and over and over again

Outer query and think about what’s the actual answer you want to produce like what are the actual attributes you what to see and then you worry about how you’re actually going to filter them and get what you want：the name from the student table and then where we get those values we’ll figure it out. The inner query we can write it the student ID and the set of people about take the class. We check to see whether it exists in that set

Reverse–For every single tuple in the enroll table where the course ID equals ‘15-445’ I’m gonna then do a matchup in this student table where the student IDs are the same right there’s essentially doing a join inside my output of my select statement right

下面的错误，具体的看ppt 43/47—

SELECT s.name FROM student s
WHERE s.name IN (SELECT c.name FROM class c WHERE c.number >= 1)
ORDER BY c.number LIMIT 1   

can you think of an inner query as a nested for loop？
all those operators like IN EXISTS ANY are just trying to say for the entire set of tuples that are in the
inner query, check to see whether any of them matches, you’re not you’re not really iterating over every single one. if you think of the outer query sort of as a for loop you’re iterating every single tuple but then the set portion
the evaluation of the inner query is always at server and a bag or set set level ？

Window function

Window functions can be simply explained as calculation functions similar to aggregating, but where normal aggregating via the GROUP BY clause combines then hides the individual rows being aggregated, window functions have access to individual rows and can add some of the attributes from those rows into the result set.

Common Table Expression

WITH