mysql基础命令

mysql基础命令

USE sql_store;

-- 下面这三个句子顺序不可颠倒
SELECT * FROM customers
-- WHERE customer_id = 1
ORDER BY first_name

-- 和上面效果一样
SELECT * FROM sql_store.customers

USE sql_store;

SELECT
    name,
    unit_price,
    unit_price * 1.1 AS 'new price' -- 重命名 
FROM products

USE sql_store;

SELECT * FROM order_items
WHERE order_id = 6 AND (quantity * unit_price) >= 30

USE sql_store;

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

USE sql_store;

SELECT * FROM customers
WHERE points BETWEEN 1000 AND 3000

USE sql_store;

SELECT * FROM customers
WHERE last_name LIKE 'b%'   -- %代表任意长度字符串，相当于模糊匹配(不区分大小写)
-- WHERE last_name REGEXP 'b' 直接模糊匹配含有b的字符串，'^b'表示首字母为b，'b$'表示尾字母为b

--WHERE last_name REGEXP '^b|a' 首字母为b或者包含a

-- WHERE last_name REGEXP '[aim]b' 包含ab或ib或mb

-- WHERE last_name REGEXP '[a-h]b'

USE sql_store;  

SELECT * FROM customers
ORDER BY first_name -- 以fiest_name为第一关键字升序排序(末尾加上DESC为降序)

USE sql_store;

SELECT * FROM customers
ORDER BY points DESC
LIMIT 3 -- 取前三个

-- JOIN均默认为内连接(INNER JOIN)
USE sql_store;

SELECT order_id, o.customer_id, first_name, last_name FROM orders o -- 给orders取别名o
JOIN customers c    -- 连接不同的表
ON o.customer_id = c.customer_id    -- 以order_id 相等为标准连接
-- USING (customer_id) 当表名相同时，可用此句替换上面的

-- 多个表的数据连接
SELECT 
    clients.name,
    payment_methods.payment_method_id,
    date,
    amount
FROM payments
    JOIN clients
        ON payments.client_id = clients.client_id
    JOIN payment_methods
        ON payments.payment_id = payment_methods.payment_method_id

USE sql_store;

SELECT 
    customers.customer_id,
    first_name,
    order_id
FROM customers
    LEFT JOIN orders    -- left join 表示customer_id全部输出，即便等式不成立
        ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id

USE sql_store;

INSERT INTO customers
VALUE
(
    DEFAULT, 'Joun', 'Smith', '1990-01-01', NULL, 'address', 'city', 'CA', 200
);

USE sql_store;

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

INSERT INTO order_items
VALUES 
    (last_insert_id(), 1, 1, 2.59), -- 返回新插入行时生成的Id
    (last_insert_id(), 2, 1, 3.59)

USE sql_invoicing;
-- 新建一个表叫invoides_archive
CREATE TABLE invoices_archive AS
SELECT
        invoice_id,
        number,
        clients.name,
FROM invoices
    JOIN clients
        USING (client_id)
 WHERE payment_date IS NOT NULL

USE sql_invoicing;
-- 更新数据库
UPDATE invoices
SET payment_total = 10, payment_date = '2020-01-01'
WHERE invoice_id = 1

USE sql_store;
-- 子查询
UPDATE orders
SET comments = 'Golden customer'
WHERE customer_id IN (
    SELECT customer_id 
    FROM customers
    WHERE points  >= 3000
    )

USE sql_store;
-- 删除
DELETE FROM invoices
WHERE client_id = 1

USE sql_invoicing;

SELECT
    MAX(invoice_total) AS highest,
    MIN(invoice_total) AS lowest,
    COUNT(DISTINCT client_id) AS total_record   -- DISTINCT指取client_id不重复的个数
FROM invoices
WHERE invoice_date > '2019-07-01'

USE sql_invoicing;
-- 注意语句顺序
SELECT
    client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
WHERE invoice_date >= '2019-01-01'
GROUP BY client_id  -- 分组聚合
ORDER BY total_sales DESC

USE sql_invoicing;

SELECT
    client_id,
    SUM(invoice_total) AS total_sales,
    COUNT(*) AS number_of_invoices
FROM invoices
GROUP BY client_id  WITH ROLLUP -- 对每组的分类合并求结果
HAVING total_sales >= 500   -- 对分组后的数据进行操作
-- 不能写HAVING payment_date,因为SELECT中没有payment_date

USE sql_hr;
-- 相关子查询
SELECT *
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE e.office_id = employees.office_id
)

USE sql_invoicing;

SELECT *
FROM clients
WHERE EXISTS (      -- 只返回是否存在下面WHERE的情况
    SELECT client_id
    FROM invoices
    WHERE client_id = clients.client_id
)

USE sql_invoicing;
-- SELECT 语句中的子查询
SELECT
    invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total)
        FROM invoices)
        AS 'invoice_avg',
    invoice_total - (SELECT invoice_avg) AS difference
FROM invoices

SELECT NOW()    -- 获取当前时间
SELECT CURDATE() --获取当前日期
SELECT CURTIME() --获取当前时间

USE sql_store;

SELECT
    order_id,
    IFNULL(shipper_id, 'Not assigned') AS shipper   -- 如果为空,则将NULL替换成Not assigned
FROM orders

USE sql_store;

CREATE VIEW orders_by_client AS
-- 后面接正常的SELECT... 表示创建一个视图，可以执行表的功能，但是不存储数据，只是作为临时是调用

如果需要更新view视图，那么view中不能含有DISTINCT，SUM等函数,在UPDATE中可能因为UPDATE而把更新的字段自动删除，所以需要在view的最后添加:WITH OPTION CHECK

-- 创建一个存储过程
DELIMITER $$
CREATE PROCEDURE get_client()
BEGIN
SELECT * FROM clients;
END$$

DELIMITER ;

--  CALL get_client()可以返回这个表

DROP PROCEDURE IF EXISTS get_client;    --非常安全的删除命令

DROP PROCEDURE IF EXISTS get_invoices_by_client

DELIMITER $$
CREATE PROCEDURE get_invoices_by_client 
(
    client_id INT   -- 输入id值
)
BEGIN
    IF client_id <= 0 THEN  -- 排除错误值
        SIGNAL SQLSTATE '22003'
    END IF;
    SELECT * FROM clients
    WHERE clients.client_id = IFNULL(client_id, clientd.client_id); -- 如果为空，则输出所有的值
END$$

DELIMITER ;

-- 函数主体
CREATE FUNCTION get_risk_factor_for_clients
(
    client_id INT
)   -- 参数
RETURNS INTEGER -- 返回值类型
READS SQL DATA
BEGIN
    DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0;    -- 定义变量
    DECLARE invoices_total DECIMAL(9, 2);
    DECLARE invoices_count INT;
    
    SELECT COUNT(*), SUM(invoice_total)
    INTO invoices_count, invoices_total -- 将值赋给变量
    FROM invoices
    WHERE invoices.client_id = client_id;
    
    SET risk_factor = invoices_total / invoices_count * 5;
    RETURN risk_factor;
END

USE sql_invoicing;

DELIMITER $$
CREATE TRIGGER payment_after_insert
AFTER INSERT ON payments    -- 在payment插入后更新表
        FOR EACH ROW

BEGIN
    UPDATE invoices
        set payment_total = payment_total + NEW.amount  -- 新添加的数据(如果是DELETE,则用OLD替换NEW)
        WHERE invoice_id = NEW.invoice_id;
END$$

DELIMITER ;

USE sql_store;
-- 创建事件,在改变数据库数据时候需要所有操作均实现完成，否则驳回事件
START TRANSACTION;

INSERT INTO orders(customer_id, order_date, status)
    VALUE (1, '2021-01-01', 1);
    
INSERT INTO order_items
    VALUE(LAST_INSERT_ID(), 1, 1, 1);

COMMIT;