MySQL常用命令

最新推荐文章于 2024-03-18 23:02:29 发布
最新推荐文章于 2024-03-18 23:02:29 发布
阅读量2.2k 收藏 4
点赞数 1
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Zzzzz_zoe/article/details/114697257
版权

  1. ​ 显示可用的数据库 -> SHOW DATABASES；

  2. 显示数据库内的表 -> SHOW TABLES;

  3. 使用数据库/数据表 -> USE 库名/表名;

  4. 查看表结构 -> DESC 表名;/SHOW COLUMNS FROM 表名;

  5. 检索单个列 -> SELECT 列名 FROM 表名;

  6. 检索多个列 -> SELECT 列名1,列名2,列名3 FROM 表名;

  7. 检索所有列 -> SELECT * FROM 表名;

  8. 检索列数据都不重复 -> SELECT DISTINCT 列名1,列名2 FROM 表名;

  9. 检索列从行n开始的m行数据 -> SELECT 列名 FROM 表名 LIMIT n,m;

  10. 检索单列并按单列排序 -> SELECT 列名 FROM 表名 ORDER BY 列名;

  11. 检索多列并按多列排序 -> SELECT 列名1,列名2,列名3 FROM 表名 ORDER BY 列名4,列名5;（仅在存在多个相同列名4的数据时才会对列名5进行排序）

  12. 以降序检索 -> SELECT 列名1,列名2,列名3 FROM 表名 ORDER BY 列名4 DESC,列名5;（如果想在多个列上进行降序排列，必须对每个列指定DESC关键字）

  13. 检索一列最高的值 -> SELECT 列名1 FROM 表名 ORDER BY 列名1 DESC LIMIT 1;

  14. 检索匹配单个值 -> SELECT 列名1,列名2 FROM 表名 WHERE 列名3=/</> ‘匹配值’;

  15. 检索不匹配单个值 -> SELECT 列名1,列名2 FROM 表名 WHERE 列名3<>/!= ‘匹配值’;

  16. 范围值匹配 -> SELECT 列名1,列名2 FROM 表名 WHERE 列名3 BETWEEN min AND max;

  17. 匹配空值 -> SELECT 列名1 FROM 表名 WHERE 列名2 IS NULL;

  18. 多条件过滤 -> SELECT 列名1 列名2 FROM 表名 WHERE 列名3 = ‘匹配值1’ AND/OR 列名4 <= ‘匹配值2’;

  19. 多条件过滤AND和OR同时使用时应使用圆括号 -> SELECT 列名1,列名2 FROM 表名 WHERE (列名3 = ’匹配值1‘ OR 列名3 = ‘匹配值2’) AND 列名4 >= ‘匹配值3’;（AND和OR同时出现时，AND的优先级更高）

  20. 条件范围匹配 -> SELECT 列数1,列数2 FROM 表名 WHERE 列数3 IN (‘匹配值1’,‘匹配值2’) ORDER BY 列数4;

  21. 条件否定匹配 -> SELECT 列数1,列数2 FROM 表名 WHERE 列数3 NOT IN (‘匹配值1’,‘匹配值2’) ORDER BY 列数4;

    8、用通配符过滤

    为在搜索子句中使用通配符，必须使用LIKE操作符。

    % 表示任何字符出现的任意次数，包括0个字符

    匹配以x开头的单词
SELECT 列名1,列名2 FROM 表名 WHERE 列名3 LIKE 'x%';

    _ 表示只匹配单个字符而不是多个字符

    匹配以x开头长度为2的单词
SELECT 列名1,列名2 FROM 表名 WHERE 列名3 LIKE 'x_';

    通配符搜索的处理时间比其他搜索所花费时间更长，不能过度使用通配符。

    9、用正则表达式进行搜索

    LIKE和REGEXP的一个差别
    匹配整列中名为xxx的数据
SELECT 列名1 FROM 表名 WHERE 列名2 LIKE 'xxx';

    匹配整列中包含xxx的数据
SELECT 列名1 FROM 表名 WHERE 列名2 REGEXP 'xxx';

    MySQL正则表达式匹配不区分大小写，如果想要区分大小写，可使用BINARY关键字。

    进行OR匹配
    匹配xxx或yyy两串之一
SELECT 列名1 FROM 表名 WHERE 列名2 REGEXP 'xxx|yyy' ORDER BY 列名3;
    匹配几个字符之一
    匹配集合123中任意一个+xx的数据
SELECT 列名1 FROM 表名 WHERE 列名2 REGEXP '[123]xx';
匹配非集合123中的任意一个+xx的数据
SELECT 列名1 FROM 表名 WHERE 列名2 REGEXP '[^123]xx';
    匹配范围
    匹配集合1~5中任意一个+xx的数据
SELECT 列名1 FROM 表名 WHERE 列名2 REGEXP '[1-5]xx';
    匹配特殊字符

    为匹配特殊字符应在要查找的字符前加 \\ 进行转义

    匹配包含.字符的值
SELECT 列名1 FROM 表名 WHERE 列名2 REGEXP '\\.' ORDER BY 列名3;
    匹配字符类

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2vXkCGSV-1615533802196)(C:\Users\ADMINI~1\AppData\Local\Temp\企业微信截图_16137032783453.png)]

    匹配多个实例

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ci0fqFyW-1615533802198)(C:\Users\ADMINI~1\AppData\Local\Temp\企业微信截图_16137035141830.png)]

    匹配包含(0-9任意一个字符 sticks/stick)的数据
SELECT 列名1 FROM 表名 WHERE 列名2 REGEXP '\\([0-9] sticks?\\)' ORDER BY 列名3;
匹配连在一起的4位数字
SELECT 列名1 FROM 表名 WHERE 列名2 REGEXP '[[:digit:]]{4}' ORDER BY 列名3;
    定位符

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rMgIOfL9-1615533802200)(C:\Users\ADMINI~1\AppData\Local\Temp\企业微信截图_16137044861799.png)]

    匹配以一个数(包括以小数点开始的数)开始的数据
SELECT 列名1 FROM 表名 WHERE 列名2 REGEXP '^[0-9\\.]' ORDER BY 列名3;

    10、拼接字段

    在MySQL的SELECT语句中，可使用Concat()函数来拼接两列。

    检索列名1(列名2)的拼接字段
SELECT Concat(列名1, '(', 列名2, ')') FROM 表名 ORDER BY 列名3;
检索列名1(列名2)的拼接字段,并去掉串左/右/左右两边的空格
SELECT Concat(LTrim/RTrim/Trim(列名1), '(', LTrim/RTrim/Trim(列名2), ')') FROM 表名 ORDER BY 列名3;
使用别名xxx
SELECT Concat(列名1, '(', 列名2, ')') AS xxx FROM 表名 ORDER BY 列名3;
    执行算术计算
    信息汇总(+-*/)
SELECT 列名1,列名2,列名3,列名1+/-/*//列名2/-/*//列名3 AS xxx FROM 表名;

    11、使用数据处理函数

    文本处理函数

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NYJdbEf4-1615533802202)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210219141306624.png)]

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jARPSoBo-1615533802203)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210219141328182.png)]

    转换为大写
SELECT 列名1,Upper(列名1) AS 列名2 FROM 表名 ORDER BY 列名3;
    日期和时间处理函数

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KNBYCqxF-1615533802205)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210219142250683.png)]

    根据日期匹配
SELECT 列名1,列名2 FROM 表名 WHERE Data(列名3) = ‘2021-01-02’;
匹配2021年1月的所有数据
SELECT 列名1,列名2 FROM 表名 WHERE Data(列名3) BETWEEN '2021-01-01' AND '2021-01-31';
SELECT 列名1,列名2 FROM 表名 WHERE Year(列名3) = 2021 AND Month(列名4) = 1;
    数值处理函数

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-d4rosGMa-1615533802206)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210219143335133.png)]

    12、汇总数据

    聚集函数

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aN0gLYlj-1615533802207)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210219150932986.png)]

    检索特定行/列的平均值
SELECT AVG(列名1) AS xxx FROM 表名 WHERE 列名2 = y;
对所有行计数，包括空行(NULL)
SELECT COUNT(*) AS xxx FROM 表名;
对特定列进行计数，忽略NULL值
SELECT COUNT(列名1) AS xxx FROM 表名;
检索列中最大值，忽略NULL行
SELECT MAX(列名1) AS xxx FROM 表名;
检索列中最小值，忽略NULL行
SELECT MIN(列名1) AS xxx FROM 表名;
检索指定列值的和，忽略NULL行
SELECT SUM(列名1) AS xxx FROM 表名 WHERE 列名2 = y;
    聚集不同值
    检索特定行/列的不同平均值
SELECT AVG(DISTINCT 列名1) AS xxx FROM 表名 WHERE 列名2 = y;
    组合聚集函数
    多个聚集函数组合检索
SELECT COUNT(*) AS 列名1,MIN(列名2) AS 列名3,MAX(列名4) AS 列名5,AVG(列名6) AS 列名7 FROM 表名;

    13、分组数据

    创建分组
    对检索列进行分组统计
SELECT 列名1,COUNT(*) AS 别名1 FROM 表名 GROUP BY 列名2;
对检索列进行分组统计，并对每个分组进行汇总统计
SELECT 列名1,COUNT(*) AS 别名1 FROM 表名 GROUP BY 列名2 WITH ROLLUP;
    过滤分组

    所有的WHERE子句都可以用HAVING来替代，唯一的差别是WHERE过滤行，而HAVING过滤分组。

    #检索过滤检索列分组统计结果大于2的数据
SELECT 列名1,COUNT(*) AS 别名1 FROM 表名 GROUP BY 列名2 HAVING COUNT(*) >= 2;
#检索具有2个(含)以上，特定列数据为10(含)以上的数据
SELECT 列名1,COUNT(*) AS 别名1 FROM 表名 WHERE 列名2 >= 10 GROUP BY 列名3 HAVING COUNT(*) >= 2;
    分组和排序

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Jx4yOTQM-1615533802207)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210220112412661.png)]

    #它检索总计订单价格大于等于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子句顺序

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SZSkVXDP-1615533802208)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210220113643090.png)]

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LJQbp8km-1615533802208)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210220113656174.png)]

    14、使用子查询

    利用子查询进行过滤
    /*
订单存储在两个表中。对于包含订单号、客户ID、
订单日期的每个订单，orders表存储一行。各订单的物品存储在相关的
orderitems表中。orders表不存储客户信息。它只存储客户的ID。实际
的客户信息存储在customers表中。
*/

#从两张表中列出订购物品TNT2的所有客户信息
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';
    作为计算字段使用子查询
    #显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。
SELECT cust_name,cust_state,(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;

    15、连结表

    外键：为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

    笛卡尔积：由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

    应该保证所有联结都有WHERE子句。

    联合查询的效率较高。

    创建联结(等值/内部联结)
    #两表联结查询订单信息
#WHERE子句联表查询
SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name,prod_name;
#INNER JOIN联表查询
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;

    16、创建高级联结

    使用表别名

    表别名不仅能用于WHERE子句，还可以用于SELECT的列表、ORDER BY子句以及语句的其他部分。

    #使用表别名
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';
    使用不同类型的联结

    联结分为内部联结(等值联结)、自联结、自然联结和外部联结。

    #自联结查询：自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句
#查询prod_id为DTNTR的物品的供应商相关信息
SELECT p1.prod_id,p1.prod_name FROM products AS p1,products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR'; 

#内部联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。
#自然联结：如R表和S表进行自然联结，R表和S表共同列为B列，则用R表中的每一项乘以S表中的每一项，得到结果后过滤出R.B = S.B的行，最后去除一个相同的列R.B或S.B任意一个的结果。

#左外联：显示左表T1中的所有行，并将右表T2中符合条件的加到左表T1中，右表T2中不符合条件就不用加入结果表中，并且用NULL表示。
#右外联：与左外联相反。
#全外联：显示左表T1、右表T2两边中的所有行，即把左外联结果表+右外联结果表组合在一起，然后过滤掉重复的。
#内联会过滤掉NULL行，外联则不会

#左外联检索所有客户信息
SELECT customers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.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;

    17、组合查询

    使用UNION
    #UNION和WHERE子句区别：对于单表查询UNION比WHERE子句复杂，但对于从多表查询UNION可能会更简答。

#UNION限制：UNION中的每个查询必须包含相同的列、表达式或聚集函数。
#UNION默认会从查询结果集中自动去除重复的行，如果想返回所有匹配行，则使用UNION ALL。

#检索价格小于5的所有物品的列表或供应商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 SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN(1001,1002);
    对组合查询结果排序
    #在用UNION组合查询时如果要对结果进行排序，则只能使用一条ORDER BY子句且必须在最后一条SELECT语句之后，不允许使用多条ORDER BY子句进行部分排序。

#检索价格小于5的所有物品的列表或供应商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语句接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-T8gAuj1X-1615533802209)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210226103516270.png)]

    在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。

    传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它
    们（而且次序正确）。

    #检索包含单词rabbit的数据
SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');

    19、插入数据

    插入完整的行

    如果对于表中不允许NULL值且没有默认值的列在INSERT操作中进行省略，则MySQL将产生一条错误消息。

    #插入信息到customers表中
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 Angles','CA','90046','USA',NULL,NULL);
    插入多个行
    #插入多行信息
INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)VALUES('Pep E. LaPew','100 Main Street','Los Angles','CA','90046','USA'),('M.Martian','42 Galaxy Way','New York','NY','11213','USA');
    插入检索出的数据
    #将custnew中的所有数据导入customers
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、更新和删除数据

    更新数据
    #更新客户10005的电子邮件地址
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。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。

    #删除客户10006的信息（删除一行）
DELETE FROM customers WHERE cust_id = 10006;

    21、创建和操纵表

    创建表

    在创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它。如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。这样做不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在，并且仅在表名不存在时创建它。

    表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

    #创建客户表
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;
#创建由多个列组成的主键的订单表
CREATE TABLE orderitems
(
 order_num int NOT NULL,
 order_item int NOT NULL,
 prod_id char(10) NOT NULL,
 quantity int NOT NULL,
 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 customers;
    重命名表
    #将表customers2重命名为customers
RENAME TABLE customers2 TO customers;

    22、视图

    视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

    视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种MySQL的SELECT语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据。

    使用视图
    #视图用CREATE VIEW语句来创建。
#使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
#用DROP删除视图，其语法为DROP VIEW viewname;。
#更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

#视图最常见的应用之一是隐藏复杂的SQL，这通常会涉及联结

#创建名为productcustomers的视图，返回已订购了任意产品的所有客户的列表
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;
#从视图中检索订购了产品TNT2的客户
SELECT cust_name,cust_contact FROM productcustomers WHERE prod_id = 'TNT2';

#视图的另一常见用途是重新格式化检索出的数据，当我们经常需要用到一条SQL的执行结果时，可以将此语句转化为视图
CREATE VIEW vendorlocations AS SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;
#检索出以创建所有邮件标签的数据
SELECT * FROM vendorlocations;
    更新视图

    视图是可更新的（即，可以对它们使用INSERT、UPDATE和DELETE）。更新一个视图将更新其基表（视图本身没有数据）。如果你对视图增加或删除行，实际上是对其基表增加或删除行。

    如果SQL语句不能确定基数据，即包含：分组(GROUP BY和HAVING)，联结，子查询，并，聚集函数(Min()、Count()、Sum()等)，DISTINCT，导出列等等时则不能进行视图的更新。

    一般，应该将视图用于检索（SELECT语句）而不用于更新（INSERT、UPDATE和DELETE）。

    23、使用存储过程

    存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。

    使用存储过程有3个主要的好处，即简单、安全、高性能。

    为了获得包括何时、由谁创建等详细信息的存储过程列表，使用SHOWPROCEDURE STATUS。

    创建存储过程
    #创建返回平均价格的存储过程
#如果需要参数，则将参数填入()内
CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END;
#使用存储过程
CALL productpricing();
    删除存储过程

    如果指定的过程不存在，则DROP PROCEDURE将产生一个错误。当过程存在想删除它时（如果过程不存在也不产生错误）可使用DROP PROCEDURE IF EXISTS。

    #删除存储
DROP PROCEDURE productpricing;
    使用参数

    MySQL支持IN（传递给存储过程）、OUT（从存储过程传出）和INOUT（对存储过程传入和传出）类型的参数。存储过程的代码位于BEGIN和END语句内，如下所见，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）。

    #此存储过程接受3个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。
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;

#所有MySQL变量都必须以@开始。
#调用存储过程
CALL productpricing(@pricelow,
                 @pricehigh,
                	@priceaverage);

#显示检索出的产品的平均价格
SELECT @priceaverage;
#获取3个值
SELECT @pricehigh,@pricelow,@priceaverage;

#创建存储过程：ordertotal接受订单号并返回该订单的合计
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;
    建立智能存储过程
    #创建获取增加营业税的订单合计
-- 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,@total);
SELECT @total;

    24、使用游标

    当需要在检索出来的行中前进或后退一行或多行时就得使用游标。

    游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

    #从游标中检索单个行（第一行）
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，SQLSTATE'02000'是一个未找到条件
	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;

    25、使用触发器

    如果你想要某条语句（或某些语句）在事件发生时自动执行则需要使用触发器。

    在MySQL 5中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一。

    触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

    创建和删除触发器
    #创建触发器并将结果存入arg中
CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added' INTO @arg;

#删除触发器(触发器不能更新或覆盖)
DROP TRIGGER newproduct;
    使用触发器
    #INSERT触发器(在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行)
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num INTO @arg;

#DELETE触发器(在DELETE触发器代码内，可以引用一个名为OLD的虚拟表，访问被删除的行)
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触发器(在UPDATE触发器代码中，可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值)
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

    26、管理事物处理

    事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。

    #使用ROLLBACK来回退（撤销）MySQL语句
SELECT * FROM ordertotals;
#标识事务的开始
START TRANSACTION
DELETE FROM ordertotals;
SELECT * 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 delete1;
ROLLBACK TO delete1;

    27、字符集和校对顺序

    #查看所支持的字符集列表
SHOW CHARACTER SET;

#查看所支持校对列表
SHOW COLLATION;

#创建表时指定字符集和校对
CREATE TABLE mytable
(
 columnn1 INT,
 columnn2 VARCHAR(10)
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;

    28、数据库维护

    备份数据
    1. 使用mysqldump转储所有数据库内容。
    2. 用命令行实用程序mysqlhotcopy从一个数据库复制所有数据。
    3. 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。

    为了保证所有数据被写到磁盘（包括索引数据），可能需要在进行备份前使用FLUSH TABLES语句。

    查看日志文件

    在使用日志时，可用FLUSH LOGS语句来刷新和重新开始所有日志文件。

    • 错误日志。它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err，位于data目录中。此日志名可用–log-error命令行选项更改。
    • 查询日志。它记录所有MySQL活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志通常名为hostname.log，位于data目录中。此名字可以用–log命令行选项更改。
    • 二进制日志。它记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为hostname-bin，位于data目录内。此名字可以用–log-bin命令行选项更改。注意，这个日志文件是MySQL5中添加的，以前的MySQL版本中使用的是更新日志。
    • 缓慢查询日志。顾名思义，此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为hostname-slow.log ，位于data目录中。此名 字可以用–log-slow-queries命令行选项更改。

    29、改善性能

    • 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句。

    • 一般来说，存储过程执行得比一条一条地执行其中的各条MySQL语句快。

    • 尽量不要使用SELECT *。

    • 必须索引数据库表以改善数据检索的性能。需要分析使用的SELECT语句以找出重复的WHERE和ORDER BY子句。如果一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象。

    • 将SELECT语句中的OR条件替换为使用多条SELECT语句和连接它们的UNION语句。

    • 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果你有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们。（索引可根据需要添加和删除。）

Zzzzz_zoe
关注
  • 1
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 7
    评论
MySQL 常用命令
09-11
MySQL 常用命令 MySQL 是一个流行的关系数据库管理系统，它提供了许多有用的命令来管理和操作数据库。下面我们将学习一些常用的 MySQL 命令，从连接 MySQL 到修改密码、增加用户等方面。 一、连接 MySQL 连接 ...
MySQL常用命令大全
12-27
这些只是MySQL常用命令的一部分，实际操作中可能涉及更多复杂查询、事务处理、视图、存储过程等高级功能。通过深入学习和实践，你可以熟练掌握MySQL，为你的项目提供强大的数据支持。记得在操作时谨慎行事，尤其是...
面试题解--Java（全）
qq_45424937的博客
08-08 2071
Java 线程不安全的 线程安全的 StringBuilder StringBuffer ArrayList Vector HashMap Hashtable 基本数据类型 字符型：char 布尔型：Boolean 数值型： 整数类型：byte（1字节）， short（2字节）， int（4字节）， long（8字节） 浮点型：float（4字节）， double（8字节） byte：8位，最大存储数据量是255，存放的数据范围是-128~127之间。 short：16位，
Kubernetes 150 个操作练习 (上)
艾希射日
04-25 3651
title: “Kubernetes 150 个操作练习（上）” date: 2020-04-23T15:50:53+08:00 draft: false tags: [“Kubernetes”] description: [“Kubernetes”] 原文链接：Practice Enough With These 150 Questions for the CKAD Exam。 看了 Medi...
Mysql常用命令详细大全
热门推荐
努力让自己发光，对的人才能迎着光而来
07-01 9万+
登录数据库：mysql -uroot -p 退出数据库：exit/quit 查看当前系统下的数据库：show databases; 创建数据库：create 库名; 使用数据库：use 库名; 查看表：show tables; 建表：create table 表名 (字段名+空格+数据类型) 查看表结构：desc 表名; 添值：insert into 表名 values (值); 查看表中所有数据：select * from 表名; 查询建表时的结构：show create table 表名; 删除字段中.
MYSQL常用命令
weixin_38778769的博客
09-15 5920
MYSQL常用命令
Mysql常用命令大全
qetdtd的博客
05-20 2121
本文包含mysql常用命令，共计四个大类，第一类 DDL（Data Definition Languages)数据定义语言 ，第二类 DML（Data Manipulation Language）：数据操作语句，第三类 DCL（Data Control Language）：数据控制语句，非官方分类：DQL：查询操作
Mysql常用命令
Amanda240045的博客
05-05 1002
MySQL常用命令总结
最新发布
阿莫夕林的博客
03-18 2165
MySQL是一个功能强大且广泛使用的开源关系型数据库管理系统。随着互联网和数据应用的不断发展，熟练掌握MySQL的使用对于开发人员和数据库管理员来说变得越来越重要。MySQL提供了丰富的命令和功能，能够满足各种数据库操作需求。本文将总结一些常用的MySQL命令，帮助大家更好地掌握和使用MySQL。
mac mysql常用命令
liangmeng556的博客
08-11 1463
5. 创建表 `create table 表名(id(字段名) int(字段类型), name varchar(15));6. 插入数据 `insert into 表明 values(id, name)`8. 查看创建表信息 `show create table 表名;1. 创建数据库 `create database 库名`。7. 查看表数据 `select * from 表名`3. 使用数据库 `use 库名`...
MySQL 常用命令汇总
fuge2008的博客
11-20 4868
一、Mysql 常用命令 show databases; 显示数据库 create database name; 创建数据库 use databasename; 选择数据库 drop database name 直接删除数据库,不提醒 show tables; 显示表 describe tablename; 显示具体的表结构 select 中加上 distinct 去除重复字段 mysqladmi...
MySQL常用命令大全.pdf
01-04
对于任何数据库管理员或开发者来说，熟悉MySQL的常用命令是至关重要的。下面我们将详细探讨在标题和描述中提到的一些基本命令。 1. `mysql` 命令： `mysql` 用于连接到MySQL服务器。基本语法是 `mysql -h 主机地址...
mysql常用命令大全 mysql常用命令总结
09-10
本文主要涵盖了MySQL的一些基本命令，包括数据库的创建与管理，以及用户权限的授予和撤销。 首先，创建和管理MySQL数据库涉及以下步骤： 1. 使用`SHOW DATABASES;`命令可以查看服务器上现有的所有数据库。 2. `...
mysql常用命令总结适用于mysql8.0
12-22
对于MySQL 8.0，了解并熟练掌握常用命令对于数据库管理至关重要。以下是一些关键的MySQL命令及其详细解释： 1. **登录数据库**: 使用`mysql -u root -p`命令登录MySQL服务器，其中`root`是用户名，系统会提示输入...
MySQL常用命令汇总.rar
12-24
以上仅是MySQL常用命令的一部分，实际操作中还有更多高级功能，如视图、存储过程、触发器、索引、事务处理等。通过深入学习和实践，你可以更高效地管理和维护MySQL数据库。在“MySQL常用命令汇总.pdf”文件中，应有...
mysql常用命令
08-12
### MySQL常用命令详解 #### 一、查看当前系统中的所有数据库 **命令**: `SHOW DATABASES;` 在MySQL中，`SHOW DATABASES;` 命令用于列出当前系统中存在的所有数据库。这对于管理员来说是非常实用的功能，可以帮助...
MySQL常用命令详解
本文主要介绍了MySQL的一些常用命令，包括数据库管理、表操作、用户权限管理和数据查询等基础功能。 在MySQL数据库管理系统中，掌握一些基本的SQL语句是非常必要的。以下是一些常用的MySQL命令： 1. 显示数据库：`...
评论 7
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值