《MySQL必知必会》总结

最新推荐文章于 2024-03-18 16:58:50 发布

Do_My_Best_

最新推荐文章于 2024-03-18 16:58:50 发布

阅读量217

点赞数

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_18724841/article/details/120230162

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL必知必会

1.登陆和使用

1.1 登陆

mysql -uroot -proot

1.2 使用

use crashcourse;

1.3 查看数据库/表

常用

SHOW DATABASES; --查看数据库列表
SHOW TABLES; --查看表的列表
SHOW COLUMNS FROM customers; --查看表中的列信息
DESCRIBE customers; --查看表中的列信息(快捷方式)

非常用

SHOW STATUS; --显示服务器状态信息
SHOW CREATE DATABASES; --显示创建数据库的语句
SHOW CREATE TABLE; --显示创建表的语句
SHOW GRANTS; --显示授权用户的安全权限
SHOW ERRORS; --显示服务器错误
SHOW WARNINGS; --显示服务器警告

2.查询

2.1 简单查询

SELECT * FROM customers; --查询全部列
SELECT customers.cust_id,custo mers.cust_name,customers.cust_address FROM customers; --查询部分列
SELECT DISTINCT vend_id FROM products; --查询不重复的值
SELECT prod_name FROM products LIMIT 5; --限制返回不多于5行
SELECT prod_name FROM products LIMIT 5,5; ----限制返回从第5行开始不多于5行（从0开始）

2.2 排序查询

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,prod_name; --指定排序方向，默认为升序(ASC),降序为DESC

2.3 过滤数据

SELECT prod_name,prod_price FROM products WHERE prod_price = 2.5; -- 用where限制条件，=,!=,<>,>,>=,BETWEEN AND
SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 1 AND 5; -- BETWEEN AND 限制范围
SELECT cust_id FROM customers WHERE cust_email IS NULL; --筛选空值,IS not NULL 筛选非空值
SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10; --AND表示多个条件都符合
SELECT prod_name,prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003; --OR表示符合一个条件即可
SELECT prod_name,prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10; --AND优先级大于OR，要适当使用括号消除歧义
SELECT prod_name,prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name; --IN操作符指定条件范围，范围内每个条件用逗号分隔，相当于OR
SELECT prod_name,prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name; --NOT用于否定后跟条件的关键字
SELECT prod_id,prod_name FROM products WHERE prod_name LIKE 'JET%'; --LIKE关键字使用通配符，%代表任意长度字符但无法搜索到NULL，_代表一个字符

2.4 正则表达式

LIKE和REGEXP之间的差别：

LIKE匹配整个列。如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回（除非使用通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。这是一个非常重要的差别。
LIKE和REGEXP的不同在于，LIKE匹配整个串而REGEXP匹配子串。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使REGEXP的作用与LIKE一样。

匹配单个字符

SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000'; --搜索两个串之一，使用|
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton'; --匹配几个字符之一，使用[]括起来的字符
SELECT prod_name FROM products WHERE prod_name REGEXP '[^12] Ton'; --[^12]表示匹配除了12后的任何东西

匹配范围

SELECT prod_name FROM products WHERE prod_name REGEXP  '[1-5] Ton'; --[1-5]匹配[12345]，[a-z]匹配任意字母
SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.'; --.代表任意字符，要用\\转义，- | []等其他特殊字符都需要转义
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)'; --?代表0次或者1次，可以匹配stick和sticks
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)'; --匹配4位连续数字
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]'; --匹配数字或者.开头的，^的双重用途 ^有两种用法。在集合中（用[和]定义），用它来否定该集合，否则，用来指串的开始处。

在这里插入图片描述

2.6 计算字段查询

拼接字段（Concat( )函数）

将值联结到一起构成单个值。

SELECT CONCAT(RTRIM(vend_name),' (',vend_country,')') AS vend_title FROM vendors ORDER BY vend_name; --CONCAT()拼接字符串，RTrim()函数去掉值右边的所有空格,使用vend_title作为别名

算术计算

SELECT prod_id,quantity,item_price,quantity * item_price AS expanded_price FROM orderitems WHERE order_num = 20005; --计算20005订单的总价，支持的运算符 + - * /

2.7 数据处理函数

文本处理函数

SELECT vend_name,UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name; --转化为大写的函数

在这里插入图片描述

日期和时间处理函数

不管是插入或更新表值还是用WHERE子句进行过滤，日期必须为格式yyyy-mm-dd。

SELECT cust_id,order_num FROM orders WHERE order_date = '2005-09-01'; --筛选出对应日期的订单
SELECT cust_id,order_num FROM orders WHERE DATE(order_date) = '2005-09-01'; --Date()只取日期进行比较，Time()只取时间进行比较
SELECT cust_id,order_num FROM orders WHERE YEAR(order_date) = 2005 AND MONTH(order_date) = 9; --检索出order_date为2005年9月的所有行。

在这里插入图片描述

数值处理函数

数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算。

在这里插入图片描述

2.8 汇总数据

聚集函数

SELECT AVG(prod_price) AS avg_price FROM products; --AVG()函数计算平均值，只用于单个列，为了获得多个列的平均值必须使用多个AVG()函数，AVG()函数忽略列值为NULL的行。
SELECT COUNT(*) AS num_cust FROM customers; --使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
SELECT COUNT(cust_email) AS num_cast FROM customers; --使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
SELECT MAX(prod_price) AS max_price FROM products; --MAX()返回指定列中的最大值。MAX()要求指定列名。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。MAX()函数忽略列值为NULL的行。
SELECT MIN(prod_price) AS min_price FROM products; ----MIN()返回指定列中的最小值。在用于文本数据时，如果数据按相应的列排序，则MIN()返回最前面的行。MIN()函数忽略列值为NULL的行。
SELECT SUM(quantity) AS item_ordered FROM orderitems WHERE order_num = 20005; --SUM()用来返回指定列值的和（总计）。

聚集不同值（DISTINCT）

SELECT AVG(DISTINCT prod_price) AS avg_price FROM products; --使用了DISTINCT参数，因此平均值只考虑各个不同的价格。DISTINCT不能用于COUNT(*)，不允许使用COUNT（DISTINCT），否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。

组合聚合函数

SELECT COUNT(*) AS num_iterms,MIN(prod_price) AS min_price,MAX(prod_price) AS max_price, AVG(prod_price) AS avg_price FROM products; --一条语句包含多个聚合函数

2.9 分组数据

创建分组

GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，
为数据分组提供更细致的控制。
如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上
进行汇总。换句话说，在建立分组时，指定的所有列都一起计算
（所以不能从个别的列取回数据）。
GROUP BY子句中列出的每个列都必须是检索列或有效的表达式
（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在
GROUP BY子句中指定相同的表达式。不能使用别名。
除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子
句中给出。
如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列
中有多行NULL值，它们将分为一组。
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id; --。GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。

过滤分组

HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行，而HAVING过滤分组。HAVING支持所有WHERE操作符在第6章和第7章中，我们学习了WHERE子句的条件（包括通配符条件和带多个操作符的子句）。所学过的有关WHERE的所有这些技术和选项都适用于HAVING。它们的句法是相同的，只是关键字有差别。

SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*) >= 2; --HAVING过滤COUNT(*) >=2（两个以上的订单）的那些分组。
SELECT vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2; --同时使用WHERE和HAVING子句,。WHERE子句过滤所有prod_price至少为10的行。然后按vend_id分组数据，HAVING子句过滤计数为2或2以上的分组。
SELECT order_num,SUM(quantity * item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity * item_price) >= 20 ORDER BY ordertotal; --分组数据，以便SUM(*)函数能够返回总计订单价格。HAVING子句过滤数据，使得只返回总计订单价格大于等于50的订单。最后，用ORDERBY子句排序输出。

在这里插入图片描述

SELECT子句顺序

2.10 使用子查询

利用子查询进行过滤

列出订购物品TNT2的所有客户

(1) 检索包含物品TNT2的所有订单的编号。
(2) 检索具有前一步骤列出的订单编号的所有客户的ID。
(3) 检索前一步骤返回的所有客户ID的客户信息。

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'));

作为计算字段使用子查询

显示customers表中每个客户的订单总数。

(1) 从customers表中检索客户列表。
(2) 对于检索出的每个客户，统计其在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;

2.11 连结表

外键（foreign key）

外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
可伸缩性（scale）

能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好（scale well）。

创建联结

SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id = products.vend_id; --匹配的两个列以vendors.vend_id 和products.vend_id指定

笛卡儿积（cartesian product）

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

SELECT vend_name,prod_name,prod_price FROM vendors,products ORDER BY vend_name,prod_name; --相应的笛卡儿积不是我们所想要的。这里返回的数据用每个供应商匹配了每个产品，它包括了供应商不正确的产品。实际上有的供应商根本就没有产品。

内部联结（INNER JOIN … ON）

目前为止所用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内部联结。其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型。

此语句中的SELECT与前面的SELECT语句相同，但FROM子句不同。这里，两个表之间的关系是FROM子句的组成部分，以INNERJOIN指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。ANSI SQL规范首选INNER JOIN语法。

SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id; --INNER JOIN ... ON表示内连接后连接条件

自联结（需要使用别名）

此查询中需要的两个表实际上是相同的表，因此products表在FROM子句中出现了两次。虽然这是完全合法的，但对products的引用具有二义性，因为MySQL不知道你引用的是products表中的哪个实例。为解决此问题，使用了表别名。products的第一次出现为别名p1，第二次出现为别名p2。

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。

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'; --要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品

自然联结（NATURAL JOIN）

“ 自然联结 ”不需要使用where 或者是 on 限定条件，它会自动根据字段的只加以匹配，这就是“自然”两个字的含义，即自发的匹配。但是，自然结合这种自发的匹配并不是随意的它必须有一个严格的限制条件，那就是两个表中必须要具有公共字段，而且这两个公共字段的名称必须一样，值得类型也必须一样，才能让其“ 自发 ”的匹配。

SELECT p.*,v.* FROM products AS p NATURAL JOIN vendors AS v;

外部联结（LEFT OUTER JOIN … ON）

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。

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 LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id; --左外联结，包含未下单的客户。使用LEFT或者RIGHT指定包含其所有行的表。

使用带聚集函数的联结

SELECT customers.cust_id,COUNT(orders.order_num) FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id; --检索所有客户及每个客户下的订单数,不包含从未下单的客户
SELECT customers.cust_id,COUNT(orders.order_num) FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id; --包含未下单的客户

2.12 组合查询（UNION）

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。

有两种基本情况，其中需要使用组合查询：

在单个查询中从不同的表返回类似结构的数据；
对单个表执行多个查询，按单个查询返回数据。

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检索价格不高于5的所有物品。第二条SELECT使用IN找出供应商1001和1002生产的所有物品。
SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5 OR vend_id IN (1001,1002); --使用多和where子句实现相同的效果
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); --在使用UNION时，重复的行被自动取消，，可使用UNION ALL返回所有匹配行。
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) ORDER BY vend_id,prod_price; --在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句。

UNION规则

UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。
UNION几乎总是完成与多个WHERE条件相同的工作。UNION ALL为UNION的一种形式，它完成WHERE子句完成不了的工作。如果确实需要每个条件的匹配行全部出现（包括重复行），则必须使用UNION ALL而不是WHERE。

2.13 全文本搜索

全文本搜索

SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('rabbit'); --在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。
SELECT note_text FROM productnotes WHERE note_text LIKE('%rabbit%'); --可以用LIKE实现相同的功能
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('anvils' WITH QUERY EXPANSION); --查询拓展

使用说明

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

搜索不区分大小写除非使用BINARY方式（本章中没有介绍），否则全文本搜索不区分大小写。
全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回。
全文本搜索提供了简单LIKE搜索不能提供的功能。而且，由于数据是索引的，全文本搜索还相当快。

查询拓展

首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；其次，MySQL检查这些匹配行并选择所有有用的词（我们将会简要地解释MySQL如何断定什么有用，什么无用）。再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。

SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('anvils' WITH QUERY EXPANSION); --查询拓展

查询扩展极大地增加了返回的行数，但这样做也增加了你实际上并不想要的行的数目。

布尔文本搜索

MySQL支持全文本搜索的另外一种形式，称为布尔方式（booleanmode）。以布尔方式，可以提供关于如下内容的细节：

要匹配的词；
要排斥的词（如果某行包含这个词，则不返回该行，即使它包含
其他指定的词也是如此）；
排列提示（指定某些词比其他词更重要，更重要的词等级更高）；
表达式分组；
另外一些内容。

使用说明：

即使没有FULLTEXT索引也可以使用布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，即使没有定义FULLTEXT索引，也可以使用它。但这是一种非常缓慢的操作（其性能将随着数据量的增加而降低）。
在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表（请参阅MySQL文档以了解如何完成此工作）。
许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。
忽略词中的单引号。例如，don’t索引为dont。
不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。
仅在MyISAM数据库引擎中支持全文本搜索。

SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('heavy -rope*' IN BOOLEAN MODE); --匹配包含heavy但不包含任意以rope开始的词的行
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('+rabbit +bait' IN BOOLEAN MODE); --搜索匹配包含词rabbit和bait的行
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('rabbit bait' IN BOOLEAN MODE); --搜索匹配包含rabbit和bait中的至少一个词的行
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('"rabbit bait"' IN BOOLEAN MODE); --搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('>rabbit <carrot' IN BOOLEAN MODE); --匹配rabbit和carrot，增加前者的等级，降低后者的等级。
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('+safe +(<combination)' IN BOOLEAN MODE); --搜索匹配词safe和combination，降低后者的等级。

在这里插入图片描述

3.插入

3.1 插入完整的行

INSERT INTO customers VALUES (NULL,'Pep E. LaPew','100 Main Street','LosAngeles','CA','90046','USA',NULL,NULL); --插入一个新客户到customers表，对每个列必须提供一个值,如果某个列没有值（如上面的cust_contact和cust_email列），应该使用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','LosAngeles','CA','90046','USA',NULL,NULL);
--表名后的括号里明确地给出了列名。在插入行时，MySQL将用VALUES列表中的相应值填入列表中的对应项。

3.2 插入多行

INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) VALUES ('Pep E. LaPew','100 Main Street','LosAngeles','CA','90046','USA'),('M. Martian','42 Galaxy Way','New York','NY','11213','USA'); --同时插入多行数据，也可以用多个Select语句

3.3 插入检索出的数据

INSERT INTO customers SELECT * FROM custnew; --使用INSERT SELECT从custnew中将所有数据导入
customers

4. 更新数据

基本的UPDATE语句由3部分组成，分别是：

要更新的表；
列名和它们的新值；
确定要更新行的过滤条件。

UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005; --客户10005现在有了电子邮件地址，因此他的记录需要更新，更新单个列
UPDATE customers SET cust_name = 'The Fudds',cust_email = 'elmer@fudd.com' WHERE cust_id = 10005; --更新多个列

不要省略WHERE子句在使用UPDATE时一定要注意细心。因为稍不注意，就会更新表中所有行。
IGNORE关键字如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。为即使是发生错误，也继续进行更新，可使用IGNORE关键字，如下所示：UPDATE IGNORE customers…
为了删除某个列的值，可设置它为NULL（假如表定义允许NULL值）。

UPDATE customers SET cust_email = NULL WHERE cust_id = 10005; --其中NULL用来去除cust_email列中的值

5. 删除数据

DELETE FROM customers WHERE cust_id = 10006; --在这个例子中，只删除客户10006。如果省略WHERE子句，它将删除表中每个客户。
TRUNCATE TABLE  table_name; --清除表

删除表的内容而不是表 DELETE语句从表中删除行，甚至是删除表中所有行。但是，DELETE不删除表本身。
更快的删除如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。

6. 创建和操纵表

6.1 创建表

创建表

CREATE TABLE customers1
(
  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; --创建表

如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。

主键

主键值必须唯一。即，表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。
主键为其值唯一标识表中每个行的列。主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。

PRIMARY KEY (cust_id) --单个列作为主键
PRIMARY KEY (order_num,order_item) --多个列作为主键

自增（AUTO_INCREMENT）

cust_id      int       NOT NULL AUTO_INCREMENT,

AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。
每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。
覆盖AUTO_INCREMENT，可以简单地在INSERT语句中指定一个值，只要它是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值。

SELECT LAST_INSERT_ID() FROM customers; --此语句返回最后一个AUTO_INCREMENT值，然后可以将它用于后续的MySQL语句

指定默认值

CREATE TABLE orderitems1
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL DEFAULT 1, --指示MySQL，在未给出数量的情况下使用数量1。
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

引擎类型

MySQL有一个具体管理和处理数据的内部引擎。在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求。多数时候，此引擎都隐藏在DBMS内，不需要过多关注它。
常见的引擎:
- InnoDB是一个可靠的事务处理引擎（参见第26章），它不支持全文本搜索；
- MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
- MyISAM是一个性能极高的引擎，它支持全文本搜索（参见第18章），但不支持事务处理。
引擎类型可以混用，外键不能跨引擎

6.2 更新表

添加删除列

ALTER TABLE vendors ADD vend_phone CHAR(20); --vendors表增加一个名为vend_phone的列
ALTER TABLE vendors DROP vend_phone; --删除刚刚添加的列

增加外键

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

增加和删除主键

ALTER TABLE orderitems2 ADD PRIMARY KEY (order_num); --增加主键
ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint; --删除主键

6.4 删除表

DROP TABLE customers1;

6.5 重命名表

RENAME TABLE orderitems1 TO orderitems2;

7. 视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。
为什么使用视图？
- 重用SQL语句。
- 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。使用表的组成部分而不是整个表。
- 保护数据。可以给用户授予表的特定部分的访问权限而不是整个
  表的访问权限。
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
视图的规则和限制
- 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
- 对于可以创建的视图数目没有限制。
- 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
- 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
- ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
- 视图不能索引，也不能有关联的触发器或默认值。
- 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。
视图的使用
- 视图用CREATE VIEW语句来创建。
- 使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
- 用DROP删除视图，其语法为DROP VIEW viewname;。
- 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原
  有视图。

利用视图简化复杂的联结

视图极大地简化了复杂SQL语句的使用。利用视图，可一次性编写基础的SQL，然后根据需要多次使用。

CREATE VIEW productcustomers AS SELECT cust_name,cust_contact,prod_id FROM customers,orders,orderitems WHERE customers.cust_id = orders.cust_id AND orders.order_num = orderitems.order_num; --创建一个名为productcustomers的视图，它联结三个
表，以返回已订购了任意产品的所有客户的列表
SELECT cust_name,cust_contact FROM productcustomers WHERE prod_id = 'TNT2';

用视图重新格式化检索出的数据

SELECT CONCAT(RTRIM(vend_name),' (',vend_country,')') AS vend_title FROM vendors ORDER BY vend_name;

假如经常需要这个格式的结果。不必在每次需要时执行联结，创建一个视图，每次需要时使用它即可。为把此语句转换为视图，可按如下进行：

CREATE VIEW vendorlocation AS SELECT CONCAT(RTRIM(vend_name),' (',vend_country,')') AS vend_title FROM vendors ORDER BY vend_name; 
SELECT * FROM vendorlocation;

用视图过滤不想要的数据

视图对于应用普通的WHERE 子句也很有用。例如，可以定义customeremaillist视图，它过滤没有电子邮件地址的客户。为此目的，可使用下面的语句：

CREATE VIEW customeremaillist AS SELECT cust_id,cust_name,cust_email FROM customers WHERE cust_email IS NOT NULL;
SELECT * FROM customeremaillist;

使用视图与计算字段

检索某个特定订单中的物品，计算每种物品的总价格。

CREATE VIEW orderitemexpanded AS SELECT prod_id,quantity,item_price,quantity * item_price AS expanded_price FROM orderitems WHERE order_num = 20005;
SELECT * FROM orderitemexpanded;

更新视图

通常，视图是可更新的（即，可以对它们使用INSERT、UPDATE和DELETE）。更新一个视图将更新其基表（可以回忆一下，视图本身没有数据）。如果你对视图增加或删除行，实际上是对其基表增加或删除行。
但是，并非所有视图都是可更新的。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：
- 分组（使用GROUP BY和HAVING）；
- 联结；
- 子查询；
- 并；
- 聚集函数（Min()、Count()、Sum()等）；
- DISTINCT；
- 导出（计算）列。
将视图用于检索
- 一般，应该将视图用于检索（SELECT语句）而不用于更新（INSERT、UPDATE和DELETE）。

8. 存储过程

为什么要使用存储过程
- 通过把处理封装在容易使用的单元中，简化复杂的操作。
- 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
- 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减
  少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
- 提高性能。因为使用存储过程比使用单独的SQL语句要快。
- 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。
- 换句话说，使用存储过程有3个主要的好处，即简单、安全、高性能。显然，它们都很重要。不过，在将SQL代码转换为存储过程前，
- 缺陷：一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。

8.1 使用存储过程

创建存储过程

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; --此存储过程接受3个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL支持IN（传递给存储过程）、OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数。存储过程的代码位于BEGIN和END语句内，如前所见，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）。

使用存储过程

CALL productpricing(@pricelow,@pricehigh,@priceaverage); --使用存储过程
SELECT @pricelow,@pricehigh,@priceaverage; --显示检索出的值

例2：使用IN和OUT参数，ordertotal接受订单号并返回该订单的合计：
创建存储过程

CREATE PROCEDURE oedertotal(
	IN onumber INT,
	OUT ototal DECIMAL(8,2)
)
BEGIN
	SELECT SUM(item_price * quantity) FROM orderitems
	WHERE order_num = onumber INTO ototal;
END; --onumber定义为IN，因为订单号被传入存储过程。ototal定义为OUT，因为要从存储过程返回合计。SELECT语句使用这两个参数，WHERE子句使用onumber选择正确的行，INTO使用ototal存储计算出来的合计。

使用存储过程

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

检查存储过程

SHOW CREATE PROCEDURE oedertotal; 
SHOW PROCEDURE STATUS; --获得包括何时、由谁创建等详细信息的存储过程列表

9. 游标

10. 触发器

Do_My_Best_

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
《MySQL必知必会》总结

MySQL必知必会1.登陆和使用1.1 登陆mysql -uroot -proot1.2 使用use crashcourse;1.3 查看数据库/表常用SHOW DATABASES; --查看数据库列表SHOW TABLES; --查看表的列表SHOW COLUMNS FROM customers; --查看表中的列信息DESCRIBE customers; --查看表中的列信息(快捷方式)非常用SHOW STATUS; --显示服务器状态信息SHOW CREATE
复制链接

扫一扫