SQL入门之读了必懂不懂再试一遍绝对懂系列

2022-07-25  本文已影响0人  Abububiu

Mosh MySQL教程 笔记

本文使用教程中配套的数据库

SELECT 
  last_name, 
  first_name, 
  points, 
  points + 10 AS 'discount factor'
FROM customers

SELECT * FROM customers
WHERE points > 10

WHERE语句的条件可以是逻辑运算表达式，算术表达式，或者多个逻辑表达式的，比如>, <, =, AND, OR等

SELECT * FROM customers
WHERE points > 10 AND state = 'VA'

SELECT * FROM customers
WHERE NOT (points > 10 AND state = 'VA')

优先级AND > OR 和 NOT，可根据需求混合使用

SELECT * FROM customer
WHERE state = 'VA' OR state='FL' OR state='GA'

或者使用IN运算符

SELECT * FROM customer
WHERE state IN ('VA', 'FL', 'GA')

SELECT * FROM customer
WHERE points BETWEEN 10 AND 100

SELECT * FROM customer
WHERE last_name LIKE '%field%' OR last_name LIKE '%ted%'

SELECT * FROM customer
WHERE last_name REGEXP 'field'

^ 符号表示字符串开头，即字符串必须以^符号后面的字符串开头

SELECT * FROM customer
WHERE last_name REGEXP '^field'

%符号表示字符串结尾，即字符串必须以^符号前面的字符串结尾

SELECT * FROM customer
WHERE last_name REGEXP 'field%'

｜符号表示多个匹配模式，有或运算符的意思，但是，多个匹配模式中，必须有一个匹配上才算满足条件

SELECT * FROM customer
WHERE last_name REGEXP '^field|eg|ei'

上例表示查询以field开头，或者包含eg或者ei的last_name的用户信息

[]符号表示可选字符，匹配上方括号里的字符和方括号外的字符的任意组合即满足筛选条件。如例子中，字符串中有ee或te或ce的字符串即满足要求

SELECT * FROM customer
WHERE last_name REGEXP '[etc]e'

当方括号内字符串较多且为连续字符时，可以使用-表示范围
SELECT * FROM customer
WHERE last_name REGEXP '[a-k]e'

SELECT * FROM customer
WHERE phone IS NULL

SELECT * FROM customer
WHERE phone IS NULL
ORDER BY first_name DESC, last_name

SELECT * FROM customer
WHERE phone IS NULL
LIMIT 3

只会返回前3条phone为NULL的数据
LIMIT还有个偏移量参数，告诉LIMIT从第几条数据开始取，如LIMIT 6, 3表示从第6条数据为起始，往后取3条，也就是取出第7，8，9条数据

SELECT * FROM orders
JOIN customers
  ON orders.customer_id = customers.customer_id

如果不需要SELECT所有数据，选择某几列的话，需要注意两张表的列重名问题，如果有重名，需要指定表名，列名，此时可以使用别名，给数据表起另一个名字，简化语句，通常在JOIN的时候，也都会使用别名来避免类似问题，如：

SELECT c.customer_id, c.first_name FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id

如果JOIN时，使用的列名两张表时相同的，则可以使用USING简写，如：

SELECT c.customer_id, c.first_name FROM orders o
JOIN customers c
  USING(customer_id)

跨数据库连表查询的话，只需要在表名前加上数据库名作为前缀
数据库名.表名

多表连接的话，需要多个JOIN语句，比如：

SELECT c.customer_id, c.first_name FROM orders o
JOIN customers c
  USING(customer_id)
JOIN order_status os
  ON o.status = os.order_status_id

多个连接条件的话，需要使用AND运算符，比如：

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

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

在上述语句的执行结果中，可能customers表里的某些用户是没有订单ID的。
RIGHT JOIN就是右表(orders)的信息会全部返回，不管条件满不满足，若右表中内容在左表中不存在，则为NULL(但右表都是有订单的，有订单的，肯定有用户，不会有NULL的数据)，比如

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

SELECT * FROM customers
CROSS JOIN products

SELECT first_name
FROM customers
UNION
SELECT name
FROM orders

INSERT INTO customers
VALUES(
  DEFAULT,
'John',
'Smith'
'1990-01-01',
NULL,
'address',
'city',
'CA',
DEFAULT
)

其中DEFAULT是使用表的默认值，如果不想写默认值，可以只提供没有默认值的列插入的，有默认值的，MYSQL会自动使用默认值，例如：

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

多行的插入：只需提供多个需要插入的行，用英文逗号分隔即可：

INSERT INTO shippers (name)
VALUES ('Shipper1'),
               ('Shipper2'),
               ('Shipper3'),

相关联的多表的插入：碰到A表和B表数据有关联的时候，在A表插入数据后，也需要在B表插入数据，这时候会碰到如何获取A表中新插入数据的ID问题，可以使用MYSQL内建方法LAST_INSERT_ID()获取上一次插入的ID，例如：

INSERT INTO orders(customer_id, order_date, status)
VALUES (1, '2019-01-02', 1);

INSERT INTO order_items
VALUES (LAST_INSERT_ID(), 1, 1, 2,95),
               (LAST_INSERT_ID(), 1, 1, 2,95),

UPDATE invoices
SET
  payment_total = invoice_total * 0.5
WHERE client_id = 3

DELETE FROM invoices
WHERE invoice_id = 1

SELECT 
  client_id,
  SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id

除了按照用户这个单一条件对数据分组，也可以按多个条件对数据分组，满足多个条件相同的数据分为一组，如：

SELECT 
  state,
  city,
  SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients USING(client_id)
GROUP BY state, city

SELECT 
  client_id,
  SUM(invoice_total) AS total_sales
  COUNT(*) AS number_of_invoices
FROM invoices
GROUP BY client_id
HAVING totals_sales > 500

通常来说，一个带有分组数据并要排序的数据，SQL语句书写模版是这样的：

SELECT * 
FROM xxxx表
WHERE xxxx条件
GROUP BY
HAVING xxx条件
ORDER BY xxxx排序条件

SELECT
  state,
  city,
  SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients c USING(client_id)
GROUP BY state, city WITH ROLLUP

SELECT *
FROM products
WHERE unit_pirce > (
    SELECT unit_price
    FROM products
    WHERE product_id = 3
)

上述语句是在查找单价大于product_id = 3的商品单价的商品有哪些。在执行时，会首先执行括号内的语句，也就是我们说的子查询语句，然后带着子查询的结果，执行外层查询。

SELECT * FROM products
WHERE product_id IN (
    SELECT DISTINCT product_id
    FROM order_items
)

SELECT * 
FROM invoices
WHERE invoice_total >  ALL(
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
)

上述查询就是在寻找满足大于所有client_id = 3的用户的invoice_total的记录，为了方便理解，可以将上述查询写成

SELECT * 
FROM invoices
WHERE invoice_total >  ALL(100,300,500)

就是找出所有invoice_total大于100且大与300且大于500的记录

SELECT *
FROM clients
WHERE client_id IN (
    SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
)

SELECT *
FROM clients
WHERE client_id = ANY (
    SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
)

SELECT *
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM empolyees
    WHERE office_id = e.office_id
)

 SELECT *
FROM clients c
WHERE EXIST (
    SELECT client_id
    FROM invoices
    WHERE client_id = c.client_id
)

 SELECT *
FROM clients c
WHERE IN (
    SELECT client_id
    FROM invoices
    WHERE client_id = c.client_id
)

SELECT
    invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total) FROM invoices AS invoice_average,
    invoice_total - (SELECT invoice_average) AS difference
FROM invoices

FROM子查询

SELECT *
FROM (
    SELECT
        invoice_id,
        invoice_total,
       (SELECT AVG(invoice_total) FROM invoices AS invoice_average,
        invoice_total - (SELECT invoice_average) AS difference
    FROM invoices
) AS sales_summary
WHERE total_sales IS NOT NULL

CREATE VIEW sales_by_client AS
SELECT
    c.client_id,
    c.name,
    SUM(invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING(client_id)
GROUP BY client_id, name

之后，就可以把seales_by_client当成一张表来使用，表的内容就是VIEW里写的查询的结果

CREATE OR REPALACE VIEW sales_by_client AS
SELECT
    c.client_id,
    c.name,
    SUM(invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING(client_id)
GROUP BY client_id, name

CREATE DATABASE IF NOT EXIST your_db_name;

DROP DATABSE IF EXIST your_db_name;

-- 可以在创建表之前，删除相同的表，如果存在的话，但，慎用
-- DROP TABLE IF EXIST your_table_name;
CREATE TABLE IF NOT EXIST your_table_name
(
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name  VARCHAR(50) NOT NULL DEFAULT '',
    points      INT NOT NULL DEFAULT 0 COMMENT 'points of customer',
    email       VARCHAR(255) NOT NULL UNIQUE
);

ALTER TABLE customers
    ADD last_name VARCHAR(50) NOT NULL AFTER first_name,
    ADD city      VARCHAR(50) NOT NULL,
    DROP points,
    MODIFY first_name VARCHAR(50) DEFAULT ''
;   

DELIMITER $$
CREATE TRIGGER payments_after_insert --在给触发器命名时，遵照触发条件表，执行时机，触发条件，例如这里在payments表插入数据之后，触发触发器
    AFTER INSERT ON payments  -- 指明在payments表插入数据后执行，切记不要在触发器中修改payments表，不然触发器会一直被触发，除了AFTER，还有BEFORE，条件检测除了INSERT，还有UPDATE等常用语句
    FOR EACH ROW
BEGIN   -- 触发器执行的内容
    UPDATE invoices
    SET payment_total = payment_total + NEW.amount  -- NEW表示新增或删除后的新数据，OLD表示新增或删除前的旧数据
    WHERE invoice_id = NEW.invoice_id
END $$

DELIMITER ;

SHOW VARIABLES LIKE 'event%`

如果没有打开，使用SET命令打开EVENT

SET GLOBAL event_scheduler = ON

创建EVENT使用CREATE EVENT命令

DELIMITER $$
CREATE EVENT yearly_delete_stale_audit_rows
ON SCHEDULE
    EVERY 1 YEAR STARTS '2019-01-01` ENDS '2029-01-01
DO BEGIN
    DELETE FROM payments_audit
    WHERE action_data < NOW() - INTERVAL 1 YEAR
END $$

DELEMITER ;

只需要在某一时间执行一次的任务，使用AT，定时重复执行的，使用EVERY，定时的时间可以是小时，天，年，或一个其他的时间区间

查询EVENT使用SHOW EVENTS 或按EVENT名字查找SHOW EVENT LIKE 'xxxx'
删除EVENT使用DROP EVENT IF EXISTS your_event_name
修改EVENT使用ALTER EVENT，用法与CREATE EVENT一致
暂停EVENT使用ALTER EVENT your_event_name DISABLE
重启EVENT使用ALTER EVENT your_event_name ENABLE

START TRANSACTION;
your query
COMMIT;

顺便说一下，DELETE，INSERT，UPDATE，这3个在执行过程中，MYSQL会自动添加一个Transaction来包裹这些语句，所以DELETE，INSERT，UPDATE也是事务的执行。

CREATE INDEX idx_state ON customers (state)

索引名称可以看出，是对customers表里的state加了索引。
使用SHOW INDEX IN YOUR_TABKE_NAME 语句可以查询指定表的索引，可以看到，在创建表的时候，主键本身就是索引，称为聚集索引，每张表只有一个聚集索引

CREATE INDEX idx_lastname ON customers (last_name(20))

语句中的20，就是指定last_name的前20个字符作为索引，20个字符这个数字是试出来的最少的唯一区分出不同字符的前缀数量，可以使用COUNT函数，一直替换LEFT函数的第二个参数，直到查询出的结果的数量满足你的要求：

SELECT COUNT(DISTINCT LEFT(last_name, 1)) FROM customers

CREATE FULLTEXT INDEX idx_title_body ON posts (title, body);

查询全文索引时，需要使用MATCH方法指定查询的字段名，AGAINST指定查询的内容

SELECT *
FROM posts
WHERE MATCH(title, body) AGAINST('content you want to search');

全文索引支持两种模式，自然语言模式(默认模式)和布尔模式，自然语言模式就是类似使用谷歌搜索一样，布尔模式，是可以排出某些指定的内容来搜索。
自然语言模式：

SELECT *
FROM posts
WHERE MATCH(title, body) AGAINST('latest news');

布尔模式：

SELECT *
FROM posts
WHERE MATCH(title, body) AGAINST('latest -news' IN BOOLEAN MODE);

布尔模式下，对于需要去掉的内容前加负号即可，上述布尔模式的查询就是搜索包括latest，但不包括news的内容

CREATE INDEX idx_lastname_state ON customers (last_name, state);

创建符合索引时，需要注意几点：
a. 将使用频率最高的列放在索引列的前面
b. 将基数高的列放在索引列的前面
c. 将能尽量缩小搜索范围的列放在索引列的前面

EXPLAIN SELECT * FROM table
  1. 存储过程
    coming soon
上一篇下一篇

猜你喜欢

热点阅读