《Mysql必知必会》笔记

sql语句代码

通配符与正则表达式

select prod_id,prod_name 
from products
where prod_name like 'j%'

通配符不区分大小写  %多个字符  _单个字符

=================================
正则表表达式不区分大小写  若需严格大小写  加 BINARY
. 表示匹配任意一个字符

SELECT prod_name
FROM products
where cast(prod_name as BINARY) REGEXP BINARY 'JetPack .000'

=======================================
select prod_name
from products
where prod_name REGEXP '1000|2000|.5|1'

|   匹配多个

================================
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;		
上面这句应该匹配成了  有1 有 2 有 3 ton 把 3空格ton　看作一个字符了



SELECT prod_name
from products
WHERE prod_name REGEXP '[^1.5] ton'

[]里每个字符是分开来看的  比如上面这个  就是查询非1 非.5的  

==============================
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name;

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

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

想查询vend_name里有.这个字符的  不能上面这样  为了匹配特殊字符，必须用\\为前导

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

 \\也可以用开引用元字符（具有特殊含义的字符）如：
 \\f 换页 \\n 换行 \\r 回车 \\t 制表 \\v 纵向制表


====================================
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] stickss?\\)'
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\\.]'
ORDER BY prod_name;


SELECT 'hello mysql' REGEXP '[0-9]';	  返回0  为什么

select CONCAT(vend_name,'(',vend_country,')') 
as new_colume
FROM vendors
ORDER BY vend_name


SELECT CONCAT(TRIM(vend_name),'(',RTRIM(vend_country),')')
as 拼接列
FROM vendors
order by vend_name


SELECT cust_id,order_num
from orders
where date(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

分组

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

==========================

注意  下面这个只返回一行
SELECT vend_id,COUNT(*) AS num_prods
FROM products

SELECT COALESCE(vend_id,'总数目'),COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
with rollup


COALESCE函数就是当前面的参数为null时  就用后面的参数替换

==================================
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
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 * item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity * item_price) >= 50
ORDER BY ordertotal;

子查询

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_contact 
FROM
    customers,
    orders,
    orderitems 
WHERE
    customers.cust_id = orders.cust_id 
    AND orderitems.order_num = orders.order_num 
    AND prod_id = 'TNT2';

# 使用视图检索(假如可以把整个查询包装成一个名为 productcustomers 的虚拟表)
SELECT
    cust_name,
    cust_contact 
FROM
    productcustomers 
WHERE
    prod_id = 'TNT2';

-- 这就是视图的作用。 productcustomers 是一个视图，作为视图，它
-- 不包含表中应该有的任何列或数据，它包含的是一个SQL查询（与上面用
-- 以正确联结表的相同的查询


# 创建视图
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;

# 创建视图
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;

# 双WHERE子句(视图中一个, SQL中一个)
SELECT * FROM customeremaillist WHERE cust_id = 10003;


# 创建视图(和计算字段结合)
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;

-- 如果视图定义中有以下操作，则不能进行视图的更新：
--  分组（使用 GROUP BY 和 HAVING ）；
--  联结；
--  子查询；
--  并；
--  聚集函数（ Min() 、 Count() 、 Sum() 等）
--      DISTINCT；
--  导出（计算）列

**********什么是存储过程

# 调用存储过程并返回数据
CALL productpricing(@pricelow, @pricehigh, @priceaverage);


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


# 调用存储过程
CALL productpricing();


# 为了在命令行中不出错，改变分隔符
DELIMITER //
CREATE PROCEDURE productpricing1()
BEGIN
    SELECT Max(prod_price) AS pricehigh FROM products;
END //
DELIMITER ;

CALL productpricing1();


# 删除存储过程
DROP PROCEDURE IF EXISTS productpricing1 ;


# 使用OUT参数
CREATE PROCEDURE productpricingwithavg(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 productpricingwithavg(@pricelow, @pricehigh, @priceaverage);
# 显示出来 
SELECT @pricelow;
SELECT @pricelow, @pricehigh, @priceaverage;


# 使用IN OUT参数
CREATE PROCEDURE ordertotal(IN onumber INT, OUT ototal DECIMAL(8,2))
BEGIN
    SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO ototal;
END;

CALL ordertotal(20005, @total);
SELECT @total;

# 可以用不同的参数反复调用存储过程
CALL ordertotal(20009, @total);
SELECT @total;


# 一个更智能的存储过程(带税求和和不带税求和)
-- Name: ordertotal
-- Parameters: onumber = oreder number
--                       taxable = 0 if not taxable, 1 if taxable
--             ototal = order total variable

CREATE PROCEDURE ordertotalsmart ( IN onumber INT, IN taxable BOOLEAN, OUT ototal DECIMAL ( 8, 2 ) ) COMMENT 'Obtain order, 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 ordertotalsmart(20005, 0, @total);
SELECT @total;
CALL ordertotalsmart(20005, 1, @total);
SELECT @total;

# 显示创建存储过程的语句
SHOW CREATE PROCEDURE ordertotalsmart;

# 显示所有存储过程的状态
SHOW PROCEDURE STATUS;

# 显示筛选后的存储过程的状态
SHOW PROCEDURE STATUS LIKE 'ordertotalsmart';

*********什么是游标

创建游标(存储过程处理完成后，游标就消失（因为它局限于存储过程)

存储过程一般会用到游标 会用到条件处理 declare…condition declare…handler

百度 或者看csdn收藏 游标有什么用 视图的好处 存储过程的好处

-- 使用游标涉及几个明确的步骤
--  在能够使用游标前，必须声明（定义）它。这个过程实际上没有
--  检索数据，它只是定义要使用的 SELECT 语句。
-- 一旦声明后，必须打开游标以供使用。这个过程用前面定义的
--  SELECT 语句把数据实际检索出来。
-- 对于填有数据的游标，根据需要取出（检索）各行。
-- 在结束游标使用时，必须关闭游标


# 创建游标(存储过程处理完成后，游标就消失（因为它局限于存储过程）)
CREATE PROCEDURE processorders()
BEGIN
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM ordres;
    
    -- 打开游标
    OPEN ordernumbers;
    
    
    -- 关闭游标 
    CLOSE ordernumbers;
    
END;

# 使用游标(FETCH)
CREATE PROCEDURE processorders1()
BEGIN
    
    -- Declare local variables
    DEClARE o INT;

    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    
    -- open cursor
    OPEN ordernumbers;
    
    
    -- Get order number
    FETCH ordernumbers INTO o;
    
    
    -- close cursor
    CLOSE ordernumbers;
    
    
END;


# 用游标循环获取行
CREATE PROCEDURE processorders2 ()
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 cursor
    OPEN ordernumbers;
    
    -- loop through all rows
    REPEAT
        
        -- Get order number
        FETCH ordernumbers INTO o;
    
    -- End of loop
    UNTIL done END REPEAT;
    
    -- close cursor
    CLOSE ordernumbers;
    
END;


# 实例
CREATE PROCEDURE porcessoredres4()
BEGIN

    -- Declare loca 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 cursor
    OPEN ordernumbers;
    
    -- Loop through all rows
    REPEAT
        
        -- Get order number
        FETCH ordernumbers INTO o;
        
        -- Get the total for this order
        CALL ordertotalsmart(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 cursor
        ClOSE ordernumbers;

END;


# 调用实例
CALL porcessoredres4();

存储过程的DECLARE…HANDLER语句

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

讲解

declare …handler for …语句

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement
 
handler_action:
    CONTINUE
  | EXIT
  | UNDO
 
condition_value:
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION

触发器

-- 触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。
-- 因此，每个表最多支持6个触发器（每条 INSERT 、 UPDATE和 DELETE 的之前和之后）
-- 只有表才支持触发器，视图不支持（临时表也不支持


# 创建一个触发器
CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW
BEGIN

END;


# 删除一个触发器
DROP TRIGGER newproduct;


# 创建一个触发器
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW 
BEGIN
    
END;


# DELETE触发器
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO archive_orders(order_num, ordre_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);

事务

事务的自动提交
（MYSQL默认每条语句是自动提交的）
autocommit 标志是针对每个连接而不是服务器的

# 事务 使用ROLLBACK
SELECT * FROM ordertotals;
START TRANSACTION;              
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;


# 使用COMMIT提交事务
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20009;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;


# 设置保留点并回滚到保留点
START TRANSACTION;
SELECT * FROM ordertotals;
SAVEPOINT delete1;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK TO delete1;
SELECT * FROM ordertotals;


# 取消MYSQL的自动提交（MYSQL默认每条语句是自动提交的）
# autocommit 标志是针对每个连接而不是服务器的
SET autocommit=0;

单引号和飘号

感觉单引号一般就是做一些字符的对比匹配那种 比如要查表中name有abc的 就是用单引号 飘号感觉像取值那种意思
单引号对的

飘号不对

飘号对的

SELECT `user` FROM user;

安全管理-权限

# 查看此MYSQL服务器上的所有用户
USE mysql;
USE mysql_need_know;
SELECT `user` FROM user;
SELECT * FROM user;

# 创建一个新的用户账号并指定密码(没有任何权限,要在之后分配权限)
CREATE USER bean IDENTIFIED BY '123456';

# 重命令用户
RENAME USER bean TO bforta;

# 删除一个用户(Mysql5.0之前只会删除账号，不会删除此账号的权限需要先用 REVOKE删除与账号相关的权限)
DROP USER bean;

# 查看用户权限
SHOW GRANTS FOR bforta;
SHOW GRANTS FOR root@localhost;

-- 用户定义为 user@host MySQL的权限用用户名和主机名结
-- 合定义。如果不指定主机名，则使用默认的主机名 % （授予用
-- 户访问权限而不管主机名

# 设置权限(bforta可以检索数据库crashcourse中的所有表)
GRANT SELECT ON crashcourse.* TO bforta;

# 移除权限
REVOKE SELECT ON crashcourse.* FROM bforta;

-- GRANT 和 REVOKE 可在几个层次上控制访问权限：
--  整个服务器，使用 GRANT ALL 和 REVOKE ALL；
--  整个数据库，使用 ON database.*；
--  特定的表，使用 ON database.table；
--  特定的列；
--  特定的存储过程


-- 未来的授权 在使用 GRANT 和 REVOKE 时，用户账号必须存在，
-- 但对所涉及的对象没有这个要求。这允许管理员在创建数据库
-- 和表之前设计和实现安全措施。
-- 这样做的副作用是，当某个数据库或表被删除时（用 DROP 语
-- 句），相关的访问权限仍然存在。而且，如果将来重新创建该
-- 数据库或表，这些权限仍然起作用

就是说  把什么对象的权限赋给用户  这个用户必须存在  但这个对象不需要一定存在


# 一次授予多个权限
GRANT SELECT, INSERT ON crashcourse.* TO bforta;


# 更改指定用户的密码(使用Password函数)
SET PASSWORD FOR bforta = Password('12345');

# 更改当前登陆用户的密码
SET PASSWORD = Password('123456');

数据库维护备份什么的

# 使用在命令行工具中使用mysqldump备份所有数据库到外部文件中

-- 备份：mysqldump -u root -p --databases 数据库1 数据库2 > xxx.sql
-- 还原：MySQL -uroot -p123456 <f:\all.sql
-- 常见选项：
-- --all-databases, -A： 备份所有数据库
-- --databases, -B： 用于备份多个数据库，如果没有该选项，mysqldump把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldum把每个名字都当作为数据库名。
-- 
-- --force, -f：即使发现sql错误，仍然继续备份
-- --host=host_name, -h host_name：备份主机名，默认为localhost
-- --no-data, -d：只导出表结构
-- --password[=password], -p[password]：密码
-- --port=port_num, -P port_num：制定TCP/IP连接时的端口号
-- --quick, -q：快速导出
-- --tables：覆盖 --databases or -B选项，后面所跟参数被视作表名
-- --user=user_name, -u user_name：用户名
-- --xml, -X：导出为xml文件
-- 1.备份全部数据库的数据和结构
-- 
-- mysqldump -uroot -p123456 -A >F:\all.sql
-- 
-- 2.备份全部数据库的结构（加 -d 参数）
-- 
-- mysqldump -uroot -p123456 -A-d>F:\all_struct.sql
-- 1.还原全部数据库:
-- 
-- (1) mysql命令行：mysql>source f:\all.sql
-- 
-- (2) 系统命令行： mysql -uroot -p123456 <f:\all.sql


# 检查表键
ANALYZE TABLE orders;

# 检查多表键
CHECK TABLE orders, orderitems;

性能问题

-- 首先，MySQL（与所有DBMS一样）具有特定的硬件建议。在学
-- 习和研究MySQL时，使用任何旧的计算机作为服务器都可以。但
-- 对用于生产的服务器来说，应该坚持遵循这些硬件建议。
--  一般来说，关键的生产DBMS应该运行在自己的专用服务器上。
--  MySQL是用一系列的默认设置预先配置的，从这些设置开始通常
-- 是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大
-- 小等。（为查看当前设置，可使用 SHOW VARIABLES; 和 SHOW
-- STATUS; 。）
--  MySQL一个多用户多线程的DBMS，换言之，它经常同时执行多
-- 个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执
-- 行缓慢。如果你遇到显著的性能不良，可使用 SHOW PROCESSLIST
-- 显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用
-- KILL 命令终结某个特定的进程（使用这个命令需要作为管理员登
-- 录）。
--  总是有不止一种方法编写同一条 SELECT 语句。应该试验联结、并、
-- 子查询等，找出最佳的方法。
--  使用 EXPLAIN 语句让MySQL解释它将如何执行一条 SELECT 语句。
 一般来说，存储过程执行得比一条一条地执行其中的各条MySQL 语句快。
--  应该总是使用正确的数据类型。
--  决不要检索比需求还要多的数据。换言之，不要用 SELECT * （除
-- 非你真正需要每个列）。
 有的操作（包括 INSERT replace）支持一个可选的 DELAYED 关键字，如果
-- 使用它，将把控制立即返回给调用程序，并且一旦有可能就实际
-- 执行该操作。
--  在导入数据时，应该关闭自动提交。你可能还想删除索引（包括
-- FULLTEXT 索引），然后在导入完成后再重建它们。
--  必须索引数据库表以改善数据检索的性能。确定索引什么不是一
-- 件微不足道的任务，需要分析使用的 SELECT 语句以找出重复的
-- WHERE 和 ORDER BY 子句。如果一个简单的 WHERE 子句返回结果所花
-- 的时间太长，则可以断定其中使用的列（或几个列）就是需要索
-- 引的对象。
 你的 SELECT 语句中有一系列复杂的 OR 条件吗？通过使用多条SELECT 语句和连接它们的 UNION 语句，你能看到极大的性能改进。
--  索引改善数据检索的性能，但损害数据插入、删除和更新的性能。
-- 如果你有一些表，它们收集数据且不经常被搜索，则在有必要之
-- 前不要索引它们。（索引可根据需要添加和删除。）
 LIKE 很慢。一般来说，最好是使用 FULLTEXT 而不是 LIKE 。 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面
-- 目全非了。由于表的使用和内容的更改，理想的优化和配置也会
-- 改变。
--  最重要的规则就是，每条规则在某些条件下都会被打破

mysql delayed_解析优化MySQL插入方法的五个妙招 20万条数据

binary关键字

cast函数

mysql各种子句执行顺序

SELECT子句的顺序
SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY > LIMIT;

group by是需要跟所有select 的不含聚集函数的字段 order by 不用

除聚集计算语句外， SELECT 语句中的每个列都必须在 GROUP BY 子句中给出

各种join

https://blog.csdn.net/weixin_41796956/article/details/85044152