MySQL必知必会

已于 2024-06-02 17:46:49 修改
阅读量472 收藏 10
点赞数 5
文章标签： mysql android 数据库
于 2024-05-25 00:54:33 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/JKQ8525350/article/details/139187367
版权

第3章 使用MySQL

-- 选择数据库
USE crashcourse;

-- 了解数据库和表
SHOW DATABASES;

SHOW TABLES;

SHOW COLUMNS FROM customers;

第4章 检索数据

-- 检索单个列
SELECT prod_name
FROM products;

-- 检索多个列
SELECT prod_id, prod_name, prod_price 
FROM products;

-- 检索所有列
SELECT *
FROM products;

-- 检索不同的行
SELECT vend_id
FROM products;

SELECT DISTINCT vend_id 
FROM products;

-- 限制结果
SELECT prod_name
FROM products
LIMIT 5;

SELECT prod_name
FROM products
LIMIT 5,5;

-- 使用完全限定的表名
SELECT products.prod_name 
FROM products;

第5章 排序检索数据

-- 排序数据
SELECT prod_name
FROM products
ORDER BY prod_name;

-- 按多个列排序
SELECT prod_id, prod_price, prod_name 
FROM products
ORDER BY prod price, prod name;

-- 指定排序方向
SELECT prod_id, prod_price, prod_name 
FROM products
ORDER BY prod_price DESC;

SELECT prod_id, prod_price, prod_name 
FROM products
ORDER BY prod price DESC, prod_name;

SELECT prod_price
FROM products
ORDER BY prod_price DESC LIMIT 1;

第6章 过滤数据

-- 检查单个值
SELECT prod_name, prod_price 
FROM products
WHERE prod_price = 2.50;

SELECT prod_name, prod_price 
FROM products
WHERE prod_name = 'fuses';

SELECT prod_name, prod_price 
FROM products
WHERE prod_price < 10;

-- 不匹配检查
SELECT vend_id, prod_name 
FROM products
WHERE vend_id <> 1003;

SELECT vend_id, prod_name 
FROM products
WHERE vend_id != 1003;

-- 范围值检查
SELECT prod_name, prod_price 
FROM products
WHERE prod_price BETWEEN 5 AND 10;

-- 空值检查
SELECT prod_name
FROM products
WHERE prod_price IS NULL;

SELECT cust_id
FROM customers
WHERE cust_email IS NULL;

第7章 数据过滤

-- AND操作符
SELECT prod_id, prod_price, prod_name 
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;

-- OR操作符
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003;

-- 计算次序
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;

SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

-- IN操作符
SELECT prod_name, prod_price 
FROM products
WHERE vend_id IN (1002,1003) 
ORDER BY prod name;

SELECT prod_name, prod_price
WHERE vend_id = 1002 OR vend_id = 1003
FROM products
ORDER BY prod_name;

-- NOT操作符
SELECT prod_name, prod_price 
FROM products
WHERE vend_id NOT IN (1002,1003) 
ORDER BY prod_name;

第8章 用通配符进行过滤

-- %通配符
SELECT prod_id, prod_name 
FROM products
WHERE prod_name LIKE 'jet%';

SELECT prod_id, prod_name 
FROM products
WHERE prod_name LIKE '%anvi 1%';

-- _通配符
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ton anvil';

第9章 用正则表达式进行搜索

-- 基本字符匹配
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000' 
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000' 
ORDER BY prod_name;

-- 进行OR匹配
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000 | 2000' 
ORDER BY prod_name;

-- 匹配几个字符之一
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton ORDER BY prod_name';

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1 | 2 | 3 Ton' 
ORDER BY prod_name;

-- 匹配范围
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton' 
ORDER BY prod_name;

-- 匹配特殊字符
SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '.'
ORDER BY vend_name;

SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '\\.'
ORDER BY vend_name;

SELECT prod_name
FROM products
WHERE prod_name REGEXP '\ ( [0-9] sticks?\\)'
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name REGEXP ' [ [:digit:]] {4}' 
ORDER BY prod_name;

-- 匹配多个实例
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[0-9] [0-9] [0-9] [0-9]' 
ORDER BY prod_name;

-- 定位符
SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]' 
ORDER BY prod_name;

第10章 创建计算字段

-- 拼接字段
SELECT Concat (vend_name, ' (',vend_country, ')') 
FROM vendors
ORDER BY vend_name;

SELECT Concat (RTrim (vend name), ' (',RTrim (vend_country), ')')
FROM vendors
ORDER BY vend_name;

-- 使用别名
SELECT Concat (RTrim (vend_name), ' (',RTrim (vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;

-- 执行算术计算
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price 
FROM orderitems
WHERE order_num = 20005;

第11章 使用数据处理函数

-- 文本处理函数
SELECT vend_name, Upper (vend_name) AS vend_name_upcase 
FROM vendors
ORDER BY vend_name;

SELECT cust_name, cust_contact 
FROM customers
WHERE cust_contact = 'Y.Lie';

SELECT cust_name, cust_contact
FROM customers
WHERE Soundex (cust_contact) = Soundex ( 'Y Lie');

-- 日期和时间处理函数
SELECT cust_id, order_num
FROM orders
WHERE order_date = '2005-09-01';

SELECT cust_id, order_num
FROM orders
WHERE Date (order_date) BETWEEN '2005-09-01' AND '2005-09-30';

SELECT cust_id, order_num
FROM orders
WHERE Year (order_date) = 2005 AND Month (order_date) = 9;

第12章 汇总数据

-- AVG()函数
SELECT AVG(prod_price) AS avg_price 
FROM products;

SELECT AVG(prod_price) AS avg price 
FROM products
WHERE vend_id = 1003;

-- COUNT()函数
SELECT COUNT(*)AS num_cust 
FROM customers;

SELECT COUNT(cust_email) AS num_cust 
FROM customers;

-- MAX()函数
SELECT MAX(prod_price) AS max_price 
FROM products;

-- MIN()函数
SELECT MIN(prod price) AS min price 
FROM products;

-- SUM()函数
SELECT SUM(quantity) AS items_ordered 
FROM orderitems
WHERE order_num = 20005;

SELECT SUM(item_price*quantity) AS total_price 
FROM orderitems
WHERE order_num = 20005;

-- 聚集不同值
SELECT AVG (DISTINCT prod price) AS avg price 
FROM products
WHERE vend_id = 1003;

-- 组合聚集函数
SELECT COUNT (*) AS num_items,
    MIN(prod_price) AS price_min,
    MAX(prod_price) AS price_max,
    AVG (prod_price) AS price_avg 
FROM products;

第13章 分组数据

-- 数据分组
SELECT COUNT (*) AS num prods 
FROM products
WHERE vend_id = 1003;

-- 创建分组
SELECT vend_id, COUNT(*) AS num_prods 
FROM products
GROUP BY vend_id;

-- 过滤分组
SELECT cust_id, COUNT (*) AS orders 
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

SELECT vend_id, COUNT (*) AS num prods 
FROM products
WHERE prod price > = 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

SELECT vend_id, COUNT (*) AS num prods 
FROM products
GROUP BY vend_id
HAVING COUNT(*) >= 2;

-- 分组和排序
SELECT order_num, SUM(quantity*item_price) AS ordertotal 
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price)>=50;

SELECT order_num, SUM(quantity* i tem_price) AS ordertotal 
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertotal;

第14章 使用子查询

-- 利用子查询进行过滤
SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';

SELECT cust_id
FROM orders
WHERE order_num IN (20005, 20007);

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num 
                    FROM orderitems
                    WHERE prod_id = 'TNT2')

SELECT cust_name, cust_contact 
FROM customers
WHERE cust_id IN (10001,10004);

SELECT cust_name, cust_contact 
FROM customers
WHERE cust_id IN (SELECT cust_id 
                  FROM orders
                  WHERE order_num IN (SELECT order_num 
                                      FROM orderitems
                                      WHERE prod id = 'TNT2'));

-- 作为计算字段使用子查询
SELECT COUNT (*) AS orders 
FROM orders
WHERE cust_id = 10001;

SELECT cust_name, cust_state, (SELECT COUNT (*)
                               FROM orders
                               WHERE orders.cust_id = customers.cust_id) AS orders 
FROM customers
ORDER BY cust_name;

SELECT cust_name, cust_state, (SELECT COUNT (*) 
                               FROM orders
                               WHERE cust_id = cust_id) AS orders 
FROM customers
ORDER BY cust_name;

第15章 联结表

-- 创建连接
SELECT vend_name, prod_name, prod_price 
FROM vendors, products
WHERE vendors.vend_id=products.vend_id 
ORDER BY vend_name, prod_name;

SELECT vend_name, prod_name, prod_price 
FROM vendors, products
ORDER BY vend_name, prod_name;

-- 内连接
SELECT vend_name, prod_name, prod_price 
FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

-- 连接多个表
SELECT prod_name, vend_name, prod price, quantity 
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id 
    AND orderitems.prod id = products.prod_id 
    AND order_num = 20005;

SELECT cust_name, cust_contact 
FROM customers
WHERE cust_id IN (SELECT cust_id 
                  FROM orders
                  WHERE order_num IN (SELECT order_num 
                                      FROM orderitems
                                      WHERE prod_id = 'TNT2')
                   
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id=orders.cust_id 
    AND orderitems.order_num = orders.order_num 
    AND prod_id = 'TNT2';

第16章 创建高级联结

-- 使用表别名
SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi 
WHERE c.cust_id = o.cust_id
    AND oi.order_num = o.order_num
    AND prod_id = 'TNT2';

SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi 
WHERE c.cust_id = o.cust_id
    AND oi.order_num = o.order_num
    AND prod_id = 'TNT2';

-- 自联结
SELECT prod_id, prod_name FROM products
WHERE vend_id = (SELECT vend_id 
                 FROM products
                 WHERE prod_id = 'DTNTR');
                   
SELECT p1.prod id, p1.prod name 
FROM products AS p1, products AS p2WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';

-- 自然联结
SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item price
FROM customers AS c, orders AS o, orderitems AS oi 
WHERE c.cust_id = o.cust_id
    AND oi.order_num = o.order_num
    AND prod_id = 'FB';

-- 外部联结
SELECT customers.cust_id, orders.order_num 
FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id;
                   
SELECT customers.cust_id,orders.order_num 
FROM customers RIGHT OUTER JOIN orders ON orders.cust_id=customers.cust_id;

-- 使用带聚集函数的联结
SELECT customers.cust_name, customers.cust_id, COUNT (orders.order_num) AS num_ord 
FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id 
GROUP BY customers.cust_id;
                   
SELECT customers.cust_name, customers.cust_id, COUNT (orders.order_num) AS num_ord 
FROM customers LEFT OUTER JOIN orders ON customers.cust_id=orders.cust_id 
GROUP BY customers.cust_id;

第17章 组合查询

-- 使用UNION
SELECT vend_id, prod_id, prod_price FROM products
WHERE prod_price <=5;

SELECT vend_id, prod_id, prod_price FROM products
WHERE vend_id IN (1001,1002);

SELECT vend_id, prod_id, prod_price FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price FROM products
WHERE vend_id IN (1001,1002);

SELECT vend_id, prod_id, prod_price FROM products
WHERE prod_price <= 5
OR vend_id IN (1001,1002);

-- 包含或取消重复的行
SELECT vend_id, prod_id, prod_price FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id, prod_id, prod_price FROM products
WHERE vend_id IN (1001,1002);

-- 对组合查询结果排序
SELECT vend_id, prod_id, prod_price FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price FROM products
WHERE vend_id IN (1001,1002) ORDER BY vend_id, prod_price;

第18章 全文本搜索

-- 启用全文本搜索支持
CREATE TABLE 
 productnotes NOT NULL AUTO_INCREMENT,int
 note_id NOT NULL,char(10)
 prod_id NOT NULL,
 note_date datetime NULL,
 note_text text PRIMARY KEY(note_id), 
 FULLTEXT(note_text)
) ENGINE=MyISAM;

-- 进行全文本搜索
SELECT note_text
FROM productnotes
WHERE Match (note_text) Against ( 'rabbit');

SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';

SELECT note_text, Match(note_text) Against('rabbit') AS rank 
FROM productnotes;

-- 使用查询扩展
SELECT note_text
FROM productnotes
WHERE Match (note text) Against ( 'anvils');

SELECT note_text
FROM productnotes
WHERE Match (note_text) Against ( 'anvils' WITH QUERY EXPANSION);

-- 布尔文本搜索
SELECT note_text
FROM productnotes
WHERE Match (note_text) Against ( 'heavy' IN BOOLEAN MODE);

SELECT note_text
FROM productnotes
WHERE Match (note_text) Against ( 'heavy- rope" IN BOOLEAN MODE');
                                  
SELECT note_text
FROM productnotes
WHERE Match (note text) Against ( '+rabbit +bait' IN BOOLEAN MODE);

SELECT note_text
FROM productnotes
WHERE Match (note text) Against ( 'rabbit bait' IN BOOLEAN MODE);

SELECT note_text
FROM productnotes
WHERE Match (note text) Against ("rabbit bait" 'IN BOOLEAN MODE');
                                 
SELECT note_text
FROM productnotes
WHERE Match (note text) Against ( '> rabbit < carrot' IN BOOLEAN MODE);

SELECT note_text
FROM productnotes
WHERE Match (note text) Against ( '+safe+ (< combination)' IN BOOLEAN MODE);

第19章 插入数据

-- 插入完整的行
INSERT INTO customers VALUES (NULL,'Pep E.LaPew','100 Main Street','Los Angeles', 'CA',' 90046', 'USA',NULL,NULL);

INSERT INTO customers (cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,    cust_contact,cust_email)
VALUES ( 'Pep E.LaPew',' 100 Main Street','Los Angeles',' CA','90046','USA',NULL,NULL);

INSERT INTO customers (cust_name,cust_contact,cust_email,cust_address,cust_city,cust_state,cust_zip,cust_country)
VALUES('Pep E.LaPew',NULL,NULL,'100 Main Street','Los Angeles',' CA','90046','USA');

-- 插入多个行
INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
VALUES('Pep E.LaPew','100 Main Street','Los Angeles','CA','90046','USA');
INSERT INTO customers (cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
VALUES ( 'M.Martian',' 42 Galaxy Way','New York','NY','11213','USA');

INSERT INTO customers (cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
VALUES ('Pep E.LaPew',' 100 Main Street','Los Angeles','CA',' 90046','USA','M.Martian','42 Galaxy Way',' New York', 'NY','11213',' USA ');

-- 插入检索出的数据
INSERT INTO customers (cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
    SELECT cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country
    FROM custnew;

第20章 更新和删除数据

-- 更新数据
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

UPDATE customers
SET cust_name = 'The Fudds',cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

UPDATE customers
SET cust_email = NULL 
WHERE cust_id = 10005;

-- 删除数据
DELETE FROM customers WHERE cust_id = 10006;

第21章 创建和操纵表

-- 表创建基础
CREATE TABLE customers,
(
 cust_id int NOT NULL AUTO_INCREMENT,
 cust_name char(50) NOT NULL,
 cust_address char(50) NULL,
 cust_city char(50) NULL,
 cust_state char(5) NULL,
 cust_zip char(10) NULL,
 cust_country char(50) NULL,
 cust_contact char(50) NULL,
 cust_email char(255)NULL,
 PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

-- 使用NULL值
CREATE TABLE orders
(
 order_num int NOT NULL AUTO_INCREMENT,
 order_date datetime NOT NULL,
 cust_id int NOT NULL,
 PRIMARY KEY (order_num)
) ENGINE=InnoDB;

-- 主键再介绍
CREATE TABLE vendors
(
 vend_id int NOT NULL AUTO_INCREMENT,
 vend_name char (50) NOT NULL,
 vend_address char(50) NULL,
 vend_city char(5) NULL,
 vend_state char(10) NULL,
 vend_zip  char(10) NULL,
 vend_country char(50) NULL,
 PRIMARY KEY (vend_id)
) ENGINE=InnoDB;

-- 指定默认值
CREATE TABLE orderitems
(
 order_num int NOT NULL
 order_item int NOT NULL
 prod_id char(10) NOT NULL,
 quantity int NOT NULL DEFAULT 1,
 item_price decimal(8,2) NOT NULL,
 PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

-- 更新表
ALTER TABLE vendors ADD vend_phone CHAR(20);

ALTER TABLE vendors DROP COLUMN vend_phone;

-- 删除表
DROP TABLE customers2;

-- 重命名表
RENAME TABLE customers 2 TO customers;

第22章 使用视图

-- 视图
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust id = orders.cust id 
    AND orderitems.order_num = orders.order_num 
    AND prod_id = 'TNT2';

SELECT cust_name, cust_contact 
FROM productcustomers
WHERE prod_id = 'TNT2';

-- 利用视图简化复杂的联结
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id 
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id 
    AND orderitems.order_num = orders.order_num;

SELECT cust_name, cust_contact 
FROM productcustomers
WHERE prod_id = 'TNT2';

-- 用视图重新格式化检索出的数据
SELECT Concat (RTrim (vend name), ' (',RTrim (vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;

CREATE VIEW vendorlocations AS
SELECT Concat (RTrim (vend_name), ' (',RTrim (vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;

SELECT *
FROM vendorlocations;

-- 用视图过滤不想要的数据
CREATE VIEW customeremaillist AS 
    SELECT cust_id, cust_name, cust_email 
    FROM customers
    WHERE cust_email IS NOT NULL;

SELECT *
FROM customeremaillist;

-- 使用视图与计算字段
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price 
FROM orderitems
WHERE order_num = 20005;

CREATE VIEW orderitemsexpanded AS 
SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price 
FROM orderitems;

SELECT *
FROM orderitemsexpanded WHERE order_num = 20005;

第23章 使用储存过程

-- 执行储存过程
CALL productpricing (@apricelow,
                     @pricehigh,
                     @priceaverage);

-- 创建储存过程
CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage 
    FROM products;
END;

CALL productpricing();

-- 删除储存过程
DROP PROCEDURE productpricing;

CREATE PROCEDURE productpricing (
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
BEGIN
    SELECT Min(prod_price)
    INTO pl
    FROM products;
    SELECT Max(prod_price)
    INTO ph
    FROM products;
    SELECT Avg (prod price)
    INTO pa
    FROM products;
END;
                                 
CALL productpricing(@pricelow,
                    @apricehigh,
                    @priceaverage);
                                 
SELECT @priceaverage;
                                 
SELECT @pricehigh, @apricelow, @apriceaverage;
                                 
CREATE PROCEDURE ordertotal (
    IN onumber INT,
    OUT ototal DECIMAL(8,2)
BEGIN
    SELECT Sum (i tem price* quantity)
    FROM orderitems
    WHERE order_num = onumber INTO ototal;
END;
                             
CALL ordertotal (20005, atotal);
                             
SELECT @total;
                             
CALL ordertotal (20009, @total);
    SELECT @total;

-- 建立智能存储过程
-- Name: ordertotal
-- Parameters: onumber = order number
--           taxable = 0 if not taxable, 1 if taxable
--           ototal = order total variable
CREATE PROCEDURE ordertotal (
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
    -- Declare variable for total
    DECLARE total DECIMAL(8,2);
    -- Declare tax percentage
    DECLARE taxrate INT DEFAULT 6;
    -- Get the order total
    SELECT Sum(item_price*quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO total;
    -- Is this taxable?
    IF taxable THEN
        -- Yes, so add taxrate to the total
        SELECT total+(total/100*taxrate) INTO total;
    END IF;
                             
-- And finally, save to out variable 
    SELECT total INTO ototal;
END;
                             
CALL ordertotal (20005, 0, @total);
SELECT @total;
                             
CALL ordertotal (20005, 1, atotal);
SELECT @total;

-- 检查存储过程
SHOW CREATE PROCEDURE ordertotal;

第24章 使用游标

-- 创建游标
CREATE PROCEDURE processorders ()
BEGIN
    DECLAREordernumbers CURSOR 
    FOR
    SELECT order_num FROM orders;
END;

-- 打开和关闭游标
OPEN ordernumbers;

CLOSE ordernumbers;

CREATE PROCEDURE processorders ()
BEGIN
    -- Declarethe cursor
    DECLARE ordernumbers CURSOR FOR
    SELECT order_num FROM orders;
    -- Open the cursor
    OPEN ordernumbers;
    -- Close the cursor
    CLOSE ordernumbers;
END;

-- 使用游标数据
CREATE PROCEDURE processorders ()
BEGIN
    -- Declare local variables
    DECLARE O INT;
    -- Declare the cursor
    DECLARE ordernumbers CURSOR 
    FOR
    SELECT order_num FROM orders;
    -- Open the cursor
    OPEN ordernumbers;
    -- Get order number
    FETCH ordernumbers INTO o;
    -- Close the cursor
    CLOSE ordernumbers;
END;

CREATE PROCEDURE processorders ()
BEGIN
    -- Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE O INT;
    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    -- Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    -- Open the cursor
    OPEN ordernumbers;
    -- Loop through all rows
    REPEAT
        -- Get order number
        FETCH ordernumbers INTO o;
    -- End of loop
    UNTIL done END REPEAT;
    -- Close the cursor
    CLOSE ordernumbers;
END;

CREATE PROCEDURE processorders ()
BEGIN
    -- Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE O INT;
    DECLARE t DECIMAL(8,2);
    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    -- Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    -- Create a table to store the results
    CREATE TABLE IF NOT EXISTS ordertotals
    (order_num INT, total DECIMAL(8,2));
    -- Open the cursor
    OPEN ordernumbers;
    -- Loop through all rows
    REPEAT
        -- Get order number
        FETCH ordernumbers INTO o;
        -- Get the total for this order
        CALL ordertotal(o, 1, t);
        -- Insert order and total into ordertotals
        INSERT INTO ordertotals(order_num,total)
        VALUES(o, t);
        -- End of loop
    UNTIL done END REPEAT;
    -- Close the cursor
    CLOSE ordernumbers;
END;

SELECT *
FROM ordertotals;

第25章 使用触发器

-- 创建触发器
CREATE TRIGGER newproduct AFTER INSERT ON products 
FOR EACH ROW SELECT 'Product added';

-- 删除触发器
DROP TRIGGER newproduct;

-- INSERT触发器
CREATE TRIGGER neworder AFTER INSERT ON orders 
FOR EACH ROW SELECT NEW.order_num;

INSERT INTO orders (order_date, cust_id)
VALUES (Now (),10001);

-- DELETE触发器
CREATE TRIGGER deleteorder BEFORE DELETE ON orders 
FOR EACH ROW
BEGIN
    INSERT INTO archive_orders (order_num, order_date, cust_id)
    VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

-- UPDATE触发器
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors 
FOR EACH ROW SET NEW.vend_state = Upper (NEW.vend_state);

第26章 管理事务处理

-- 控制事务处理
START TRANSACTION

-- 使用ROLLBACK
SELECT* FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

-- 使用COMMIT
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

-- 使用保留点
SAVEPOINT deletel;

ROLLBACK TO deletel;

-- 更改默认的提交行为
SET autocommit=0;
白酒永远的神
关注
Mysql必知必会
12-14
当有多个排序条件时，系统会先按第一个条件排序，再按后续条件进行次级排序。 在SQL中，`AND` 和 `OR` 用于组合条件，`AND` 具有较高的优先级。例如，`WHERE age BETWEEN 20 AND 30` 或 `WHERE age > 20 OR age 。`...
MYSQL必知必会
rainbowzhouj的博客
07-21 756
面试时，常考的SQL相关的知识点，这里都有啦～
mysql必知必会
qq_36022463的博客
11-26 870
让客户机应用 处理数据库的工作，会极大的影响应用的性能，，并且使所创建的应用完全不具备可伸缩性，，，此外，，如果在客户机上过滤数据，，服务器不得不通过网络发送多余的数据，，这导致网络宽带的浪费。主键： primary key ，，一列(一组列)，，其值能够唯一区分表中的每一行，，主键用来表示一个特定的行，，没有主键，更新或删除表中特定行很困难，，因为没有安全的方法保证只涉及相关的行。列： column ，，表中的一个字段，所有表都是由一个列或者多个列组成，，根据列，，对数据进行排序和过滤。
mysql的必知必会_Mysql必知必会
weixin_32537985的博客
01-19 785
1.数据库相关概念数据库 :保存有组织的数据的容器(一个文件或一组文件),即一个以某种有组织的方式存储的数据集合.数据库管理系统:数据库软件(DBMS).表:结构化文件,可以用来存储某种特定类型的数据.列:表是由列组成,列是表中的一个字段,所有表都是由一个或多个列组成的.列中存储着表中某部分的信息.数据类型:所容许的数据的类型.行:表中的一个记录.水平行,垂直列.模式:可以用来描述数据库中特定的表...
MySQL必知必会之实践篇记录汇总
自成背后的博客
03-04 812
文章目录1. 字段类型定义2. 创建和修改表3. 操作表中的数据之增删改查4. 如何正确设置主键5. 如何做关联查询6. 条件语句：WHERE 与 HAVING有什么不同7. 聚合函数：怎么高效地进行分组统计8. 时间函数：时间类数据，MySQL是怎么处理的9. 如何进行数学计算、字符串处理和条件判断10. 索引：如何提高查询的速度11. 事务：怎么确保关联操作正确执行12. 临时表：复杂查询，如何保存中间结果 1. 字段类型定义 2. 创建和修改表 一些常用的创建表的SQL语句：
mysql必知必会读书笔记
小杰312的博客
04-25 3581
本文纯粹就是小杰为了更加全面的了解学习mysql的读书笔记, 小杰觉得这本书写的相当不错, 感兴趣的朋友可以去读阅。 小杰在文末也会附上它的pdf的云盘地址, 大家一起学习，共同进步, 知识不分高低, 计算机的学习小杰认为也是一个 量变 ---> 质变 的过程 天道酬勤, 水滴石穿, 在不同的阶段就干好自己当前阶段力所能及之事, 至少是没有在寝室的床上瘫着消磨时光 ---...
读mysql必知必会有感_读《MySql必知必会》笔记
weixin_29418485的博客
02-12 521
MySql必知必会2017-12-21意义：记录个人不注意的，或不明确的，或不知道的细节方法技巧，此书250页登陆：mysql -u root-p -h myserver -P 9999SHOW STATUS，用于显示广泛的服务器状态信息DESCRIBE tableName;是SHOW COLUMNS FROM tableName;的一种快捷方式SHOW CREATE DATABASE和SHOW ...
mysql必知必会_《MySQL必知必会》学习小结
weixin_33344572的博客
01-18 207
关于SQL，之前通过sqlzoo的题目，完成了入门，也仅仅是入门而已。最近都在忙着投简历和找新的数据分析项目做(为了练python和面试的时候有的聊)，所以SQL放了一段时间没练。目前的工作用不到，业余时间也不去练习，这是迟早要荒废的节奏啊。BUT，如果你去拉勾网翻翻业务支持的数据分析岗位(这个岗位对于转行到数据分析的同志来说，个人觉得比较容易上手)，SQL出镜率很高。所以，这两天花了不少时间研读...
mysql必知必会题目_MYSQL必知必会挑战题答案
weixin_31681589的博客
02-28 844
SQLsql语句执行顺序(8) SELECT(9)DISTINCT(1) FROM (3) JOIN (2) ON (4) WHERE (5) GROUP BY (6) WITH {CUBE|ROLLUP}(7) HAVING (10) ORDER BY (11) LIMIT (1) FROM:对FROM子句中的左表和右表执行笛卡儿积，产生虚拟表VT1;(2) ON: 对虚拟表V...
基于SpringBoot+Vue+MySQL的养老院管理系统
程序员大金的博客
09-20 837
基于SpringBoot+Vue+MySQL的养老院管理系统，附源码。
Java项目实战II基于Java+Spring Boot+MySQL的洗衣店订单管理系统（开发文档+源码+数据库）
2401_86524610的博客
09-20 1304
随着生活节奏的加快，现代人对便捷、高效服务的需求日益增长，洗衣店作为日常生活服务的重要组成部分，其订单管理效率直接影响着客户体验和经营效益。传统的洗衣店订单管理方式大多依赖纸质记录或简单的电子表格，这种方式不仅效率低下，而且容易出错，难以追踪和统计订单信息。为了解决这些问题，我们设计并实现了一款基于Java+Spring Boot+MySQL的洗衣店订单管理系统。该系统充分利用了Spring Boot的简化开发和MySQL数据库的强大性能，实现了订单信息的数字化管理。
mysql的查询操作
weixin_45710581的博客
09-24 843
MySQL的查询操作是数据库管理和数据检索的核心。通过SQL（Structured Query Language，结构化查询语言）语句，用户可以执行包括数据检索、数据插入、更新和删除在内的多种操作。在本文中，我们将重点讨论数据检索（即SELECT语句）的高级用法和最佳实践。
MYSQL SWAP 内存 vm.swappiness
jnrjian的博客
09-20 931
关于 vm.swappiness 的困惑来自于这样一个事实：在较旧的 Red Hat 内核中，vm.swappiness 的值为 0 会导致最少量的交换以避免内存不足的情况。在较新的内核中（从 RHEL 内核 2.6.32-303 开始​​），值为 0 将完全禁用交换，但值为 1 将提供最少量的交换以避免内存不足的情况。在较旧的内核中，0 和 1 之间的实际差异并不大，但 1 是避免较新内核中出现 OOM 情况的安全设置（当然，假设有一些交换空间可用）。考虑到系统自动将未使用的页面交换到磁盘。
Mysql梳理7——分页查询
haozihua的博客
09-22 443
注意：MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;如果我们知道返回结果只有 1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。背景1：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？背景2：表里有 4 条数据，我们只想要显示第 2、3 条数据怎么办呢？所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。第二个参数“行数”指示返回的记录条数。
Linux递归找出目录下最近被修改文件（最近一段时间内被修改过的最新文件）（最近修改文件、最新文件、查找文件）（监控目录、监控mysql文件）
最新发布
Dontla的博客
09-26 488
目录下最近60分钟内修改的最新文件。如果你需要查找的时间范围不同，可以调整。表示最近10分钟）。
Web+Mysql——MyBatis
qq_73643693的博客
09-20 1477
MyBatis 是一款优秀的持久层框架，用于简化 JDBC 开发MyBatis 本是 Apache 的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了google code，并且改名为MyBatis。2013年11月迁移到Github官网：https://mybatis.org/mybatis-3/zh/index.html负责将数据到保存到数据库的那一层代码。以后开发我们会将操作数据库的Java代码作为持久层。
csdn mysql必知必会
01-26
CSND MySQL必知必会是一本针对MySQL数据库的入门教程，旨在帮助读者快速上手掌握MySQL数据库的基本知识和使用技巧。全书分为三个部分，分别是MySQL数据库基础知识、MySQL数据库操作和MySQL数据库管理与优化，内容...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值