文章目录
一.检索数据
1.检索单个列
SELECT prod_name FROM produtcts;
- 多条SQL语句必须以分号(;)分隔
- SQL语句不区分大小写
- 在处理SQL语句时，其中所有的空格都被忽略
2.检索多个列
SELECT prod_id,prod_name,prod_price FROM products;
SQL语句一般返回原始的，无格式的数据。数据的格式化是一个表示问题，而不是一个检索问题。
3.检索所有列
使用星号(*)通配符来达到
4.检索不同的行
使用关键字DISTINCT
注意：不能部分使用DISTINCT,DISTINCT关键字应用于所有列而不仅是前置它的列
5.限制结果
使用LIMIT关键字
LIMIT 5 表示返回不多于5行
LIMIT 5，5 指示返回从行5开始的5行
6.使用完全限定的表名
二.排序检索数据
排序数据
ORDER BY子句取一个或多个列的名字，据此对输出进行排序
注意：用非检索的列排序数据是完全合法的
指定排序方向
ASC 升序(默认)
DESC 降序
注意：
- 在多个列上进行降序排序，必须对每个列指定DESC关键字
- 在字典(dictionary)排序顺序中，A被视为与a相同，这是MySQL的默认行为，但是，数据库管理员可以改变这种行为
三.过滤数据
在select语句中，数据根据WHERE子句中指定的搜索条件进行过滤
在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误
WHERE子句操作符
| 操作符 | 说明 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| != | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| BETWEEN | 在指定的两个值之间 |
| IS NULL | 检查具有NULL值的列 |
四.数据过滤
AND操作符
匹配满足所有过滤条件的行
OR操作符
匹配满足任一条件的行
计算次序
AND在计算次序中优先级比OR高
SELECT prod_name,prod_price
FROM products
WHERE vend_id=1002 OR vend_id=1003 AND prod_price>=10;
SQL在处理OR操作符前，优先处理AND操作符，所以上述sql语句被理解为由供应商1003制造的任何价格为10美元(含)以上的产品，或者由供应商1002制造的任何产品，而不管其价格如何。
这个问题可以使用圆括号来消除歧义
SELECT prod_name,prod_price
FROM products
WHERE (vend_id=1002 OR vend_id=1003) AND prod_price>=10;
IN操作符
IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配
使用IN操作符的优点：
- 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观
- 在使用IN时，计算的次序更容易管理
- IN操作符一般比OR操作符清单执行更块
- IN的最大优点是可以包含其他SELECT语句，使得能够动态地建立WHERE子句
NOT操作符
WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件
注意：MySQL仅支持使用NOT对IN,BETWEEN和EXISTS子句取反
五.使用通配符进行过滤
LIKE操作符
为了搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL,后跟的搜索模式利用通配符而不是直接相等匹配进行比较
百分号(%)通配符
%表示任何字符出现任意次数
注意：
- 注意尾空格，尾空格可能会干扰通配符匹配
- 注意NULL %不能匹配NULL
下划线(_)通配符
下划线只匹配单个字符
使用通配符的技巧
- 不要过度使用通配符
- 在确实需要使用通配符时，如非必要，否则不要把它们用在搜索模式的开始处
六.用正则表达式进行搜索
七.创建计算字段
计算字段并不实际存在于数据库表中，计算字段是运行时在SELECT语句内创建的
拼接字段
在MySQL的SELECT语句中，可使用Concat()函数来拼接两个列
SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country),')')
AS vend_title
FROM vendors
ORDER BY vend_name;
注意：多数DBMS使用+或||来实现拼接，MySQL则使用Concat()函数来实现，当把SQL语句转换为MySQL语句时一定要把这个区别铭记于心。
执行算术计算
计算字段的另一常见用途是对检索出的数据进行算术计算
SELECT prod_id,quantity,item_price,quantity*item_price
AS expanded_price
FROM orderitems
WHERE order_num=20005;
八.使用数据处理函数
文本处理函数
| 函数 | 说明 |
|---|---|
| Left() | 返回串左边的字符 |
| Length() | 返回串的长度 |
| Locate() | 找出串的一个子串 |
| Lower() | 将串转换为小写 |
| LTrim() | 去掉串左边的空格 |
| Right() | 返回串右边的字符 |
| RTrim() | 去掉串右边的空格 |
| Soundex() | 返回串的SOUNDEX值 |
| SubString() | 返回子串的字符 |
| Upper() | 将串转换为小写 |
日期和时间处理函数
| 函数 | 说明 |
|---|---|
| AddDate() | 增加一个日期(天，周等) |
| AddTime() | 增加一个时间(时，分等) |
| CurDate() | 返回当前日期 |
| CurTime() | 返回当前日期 |
| Date() | 返回日期时间的日期部分 |
| DateDiff() | 计算两个日期之差 |
| Date_Format() | 返回一个格式化的日期或时间串 |
| Day() | 返回一个日期的天数部分 |
| DayOfWeek() | 对于一个日期，返回对应的星期几 |
| Hour() | 返回一个时间的小时部分 |
| Minute() | 返回一个时间的分钟部分 |
| Month() | 返回一个日期的月份部分 |
| Now() | 返回当前日期和时间 |
| Second() | 返回一个时间的秒部分 |
| Time() | 返回一个日期时间的时间部分 |
| Year() | 返回一个日期的年龄部分 |
注意：
- 应该总是使用4位数字的年份：2位数字的年份，MySQL处理00-69为2000-2069，处理70-99为1970-1999
- 如果要的是日期，请使用Date()
SELECT cust_id,order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
这种方法需要记住每个月有多少天和闰年2月的问题，还有一种方法：
SELECT cust_id,order_num
FROM orders
WHERE Year(order_date)=2005 AND Month(order_date)=9;
Year()是一个从日期中返回年份的函数，Month()从日期中返回月份
数值处理函数
| 函数 | 说明 |
|---|---|
| Abs() | 返回一个数的绝对值 |
| Cos() | 返回一个角度的余弦 |
| Exp() | 返回一个数的指数值 |
| Mod() | 返回除操作的余数 |
| Pi() | 返回圆周率 |
| Rand() | 返回一个随机数 |
| Sin() | 返回一个角度的正弦 |
| Sqrt() | 返回一个数的平方根 |
| Tan() | 返回一个角度的正切 |
九.汇总数据
聚集函数
运行在行组上，计算和返回单个值的函数
AVG()函数
- AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数列出。为了获得多个列的平均值，必须使用多个AVG()函数
- AVG()函数忽略列值为NULL的行
COUNT()函数
- 使用COUNT(*)对表中行的数目进行计算，不管表列中包含的是空值(NULL)还是非空值
- 使用COUNT(column)对特定列中的具有值的行进行计数，忽略NULL值
MAX()函数
- 在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行
- MAX()函数忽略列值为NULL的行
MIN()函数
- 在用于文本数据时，如果数据按相应的列排序，则MIN()返回最前面的行
- MIN()函数忽略列值为NULL的行
SUM()函数
SUM()函数忽略列值为NULL的行
聚集不同值
- 对所有的行执行计算，指定ALL参数或不同参数
- 只包含不同的值，指定DISTINCT参数
组合聚集函数
十.分组数据
创建分组
使用GROUP BY子句前，需要知道一些重要的规定：
- GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制
- 如果GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总
- GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名
- 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出
- 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组
- GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前
过滤分组
HAVING
- WHERE子句的条件(包括通配符条件和带多个操作符的子句)，都使用于HAVING,它们的句法是相同的，只是关键字有差别。
- WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤
分组和排序
ORDER BY和GROUP BY的差别
| ORDER BY | GROUP BY |
|---|---|
| 排序产生的输出 | 分组行。但输出可能不是分组的顺序 |
| 任意列都可以使用(甚至非选择的列也可以使用) | 只可能使用选择列或表达式列，而且必须使用每个选择列表达式 |
| 不一定需要 | 如果与聚集函数一起使用列(或表达式)，则必须使用 |
SELECT子句顺序
SELECT子句及其顺序
| 子句 | 说明 | 是否必须使用 |
|---|---|---|
| SELECT | 要返回的列或表达式 | 是 |
| FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
| WHERE | 行级过滤 | 否 |
| GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
| HAVING | 组级过滤 | 否 |
| ORDER BY | 输出排序顺序 | 否 |
| LIMIT | 要检索的行数 | 否 |
十一.使用子查询
利用子查询进行过滤
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'));
- 列必须匹配：在WHERE子句中使用子查询，应该保证SELECT语句具有与WHERE子句中相同数目的列。
- 虽然子查询一般与IN操作符结合使用，但也可以用于测试（=），不等于（<>）等。
作为计算字段使用子查询
SELECT cust_name,cust_state,
(SELECT COUNT(*)
FROM orders
WHERE orders.cust_id=customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;
相关子查询(correlated subquery)：涉及外部查询的子查询
十二.联结表
外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
可伸缩性：能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好(scale well)
内部联结：
1.使用where子句
2.使用INNER JOIN…ON
SELECT prod_name,vend_name,prod_price,quantity
FROM orderitems,products,vendors
WHERE products.vend_id=vendors.vend_id
ADD orderitems.prod_id=products.prod_id
ADD order_num=20005;
性能考虑：MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表，联结的表越多，性能下降的越厉害。
十三.创建高级联结
使用表别名
别名除了用于列名和计算字段外，SQL还允许给表名起别名
使用不同类型的联结
1.自联结
使用子查询
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 p2
WHERE p1.vend_id=p2.vend_id
AND P2.prod_id='DTNTR';
用自联结而不用子查询：自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多
2.自然联结
3.外部联结
使用关键字OUTER JOIN来指定联结的类型，与内部联结关联两个表中的行不同的是，外部联结还包括没有关联的行。
在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表(RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表)。
左外联结
SELECT customers.cust_id,orders.order_num
FROM customers LEFT OUTER 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;
4.使用带聚集函数的联结
十四.组合查询
1.使用UNION
可用UNION操作符来组合数条SQL查询，利用UNION,可给出多条SELECT语句，将它们的结果组合成单个结果集
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);
2.UNION规则
- UNION必须有两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔
- UNION中的每个查询必须包含相同的列，表达式或聚集函数
- 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型
3.包含或取消重复的行
UNION从查询结果集中自动去除了重复的行，如果想要返回所有匹配行，可使用UNION ALL而不是UNION
4.对组合查询结果排序
在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句
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;
十五.全文本搜索
理解全文本搜索
并非所有的引擎都支持全文本搜索：
- MyISAM支持全文本搜索
- InnoDB不支持全文本搜索
使用LIKE关键字，能够查找包含特殊值或部分值的行，使用正则表达式，可以编写查找所需行的非常复杂的匹配模式。它们存在几个重要的限制：
- 性能——通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。
- 明确控制——使用通配符和正则表达式匹配，很难明确地控制匹配什么和不匹配什么。
- 智能化的结果——虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但它们都不能提供一种智能化的选择结果的方法。
使用全文本搜索
1.启动全文本搜索支持
CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datetime NOT NULL,
note_text text NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
)ENGINE=MyISAM;
- 在定义之后，MySQL自动维护该索引，在增加，更新或删除行时，索引随之自动更新
- 可以指定多个列
- 可以在创建时指定FULLTEXT，或者在稍后指定。
- 更新索引要花费时间，如果正在导入数据到一个新表，此时不应该启动FULLTEXT索引。应该首先导入所有数据，然后再修改表，定义FULLTEXT。
2.进行全文本搜索
在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索的表达式
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');
也可以使用LIKE子句完成
SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';
这两条SELECT语句同样检索出两行，但次序不同，全文本搜索的一个重要部分就是对结果排序，具有较高等级的行先返回。
3.使用查询扩展
查询扩展用来设法放宽所返回的全文本搜索结果的范围：
SELECT note_text
FROM productontes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
4.布尔文本搜索
MySQL支持全文本搜索的另外一种形式，称为布尔方式(boolean mode):
- 要匹配的词
- 要排斥的词
- 排列提示
- 表达式分组
- 另外一些内容
即使没有FULLTEXT索引也可以使用，布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，即使没有定义FULLTEXT索引，也可以使用它。但这是一种非常缓慢的操作。
SELECT note_text
FROM productnotes
WHERE Mathch(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
匹配包含heavy但不包含任意以rope开始的词的行
布尔操作符
| 布尔操作符 | 说明 |
|---|---|
| + | 包含，词必须存在 |
| - | 排除，词必须不出现 |
| > | 包含，而且增加等级值 |
| < | 包含，且减少等级值 |
| () | 把词组成子表达式 |
| ~ | 取消一个词的排序值 |
| * | 词尾的通配符 |
| " " | 定义一个短语 |
十六.插入数据
1.插入完整的行
１.存储到每个表列中的数据在VAULES子句中给出，对每个列必须提供一个值，如果某个列没有值，应该使用NULL值(假定表允许对该列指定空值)。每个列必须以它们在表中出现的次序填充。
INSERT INTO customers
VALUES (NULL,
'Pep E.LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA',
NULL,
NULL);
2.在表名后的括号里明确地给出了列名，在插入行时，MySQL将用VAULES列表中的相应值填入列表中的对应项。因为提供了列名，VALUES必须以其指定的次序匹配指定的列名，不一定按各个列出现在实际表中的次序。其优点是，即使表的结构该表，此INSERT语句仍能正确工作。
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操作中省略某些列。省略的列必须满足以下某个条件。
- 该列定义为允许NULL值(无值或空值)
- 在表定义中给出默认值，这表示如果不给出值，将使用默认值
2.插入多个行
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'
),
VALUES (
'M.Martian',
'42 Galaxy Way',
'New York',
'NY',
'11213',
'USA'
);
此技术可以提高数据处理的性能，因为MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快。
3.插入检索出的数据
INSERT SELECT,顾名思义，它是由一条INSERT语句和一条SELECT语句组成的
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;
在填充custnew时，不应该使用已经在customers中使用过的cust_id值(如果主键值重复，后续的INSERT操作将会失败)或仅省略这列值让MySQL在导入数据的过程中产生新值。
注意：
- INSERT和SELECT语句中使用的列名不一定要求匹配，事实上，MySQL甚至不关心SELECT返回的列名，它使用的是列的位置。
- INSERT SELECT中SELECT语句可包含WHERE子句以过滤插入的数据
十七.更新和删除数据
更新数据
UPDATE语句由3部分组成：
- 要更新的表
- 列名和它们的新值
- 确定要更新行的过滤条件
UPDATE customers
SET cust_name='The Fudds',
cust_email='elmer@fudd.com'
WHERE cust_id=10005;
- 在UPDATE语句中使用子查询：UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据
- 如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出现一个错误，则整个UPDATE操作被取消(错误发生前更新的所有行被恢复到它们原来的值)，即使是发生错误，也继续进行更新，可使用IGNORE关键字，如下所示：UPDATE IGNORE customers…
- 为了删除某个列中的值，可设置它为NULL(假如表定义允许NULL值)
删除数据
为了从一个表中删除(去掉)数据，使用DELETE语句。可以两种方式使用DELETE:
- 从表中删除特定的行
- 从表中删除所有行
DELETE FROM customers
WHERE cust_id=10006;
注意：
- DELETE不需要列名或通配符，DELETE删除整行而不是删除列，为了删除指定的列，请使用UPDATE语句
- DELETE语句从表中删除行，甚至是删除表中所有行，但是，DELETE不删除表本身
- 如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快(TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据)。
十七．创建和操纵表
创建表
1.表创建基础
- 新表的名字，在关键字CREATE TABLE之后给出
- 表列的名字和定义，用逗号分隔
注意：
在创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它。如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。
2.使用NULL值
NULL值就是没有值或缺值，允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行，换句话说，在插入或更新行时，该列必须有值。
3.主键
PRIMARY KEY(order_num,order_item)
- 主键可以在创建表时定义，或者在创建表之后定义
- 主键为其值唯一标识表中每个行的列。主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。
4.使用AUTO_INCREMENT
- AUTO_INCREENT告诉MySQL,本列每当增加一列时自动增量，每次执行一个INSERT操作时，MySQL自动对该列增量，给该列赋予下一个可用的值。
- 每个表只允许一个AUTO_INCREMENT列，而且它必须被索引。
- last_insert_id()函数返回最后一个AUTO_INCREMENT值
5.指定默认值
- 如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定
- MySQL不允许使用函数作为默认值，它只支持常量
6.引擎类型
-默认引擎是MyISAM
- 引擎类型可以混用
- 外键不能跨引擎：混用引擎类型有一个大缺陷，外键不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键
更新表
1.给表增加一个列
ALTER TABLE vendors
ADD vend_phone CHAR(20);
2.删除一个列
ALTER TABLE vendors
DROP COLUMN vend_phone;
3.定义外键
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) REFERENCES orders (order_num);
注意;在使用ALTER TABLE时，应该在进行改动前做一个完整的备份(模式和数据的备份)。
删除表
1.删除整个表
DROP TABLE customers;
2.重命名表
RENAME TABLE customers2 TO customers;
也可以同时对多个表重命名
RENAME TABLE backup_customers TO customers,
backup_vendors TO vendors,
backup_products TO products;
十八.使用视图
视图
视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询
为什么使用视图
- 重写SQL语句
- 简化复杂的SQL操作，在编写查询后，可以方便地重用它而不必知道它的基本查询细节
- 使用表的组成部分而不是整个表
- 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据
视图的规则和限制
- 与表一样，视图必须唯一命名
- 对于可以创建的视图数目没有限制
- 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予
- 视图可以嵌套，既可以利用从其他视图中检索数据的查询来构造一个视图
- ORDER BY可以用在视图中，但如果从该视图检索数据的SELECT语句中叶包含ORDER BY ,那么该视图中的ORDER BY将被覆盖
- 视图不能索引，也不能有关联的触发器或默认值
- 视图可以和表一起使用
使用视图
1.视图的创建
- 视图用CREATE VIEW 语句来创建
- 使用SHOW CREATE VIEW viewname;来查看创建视图的语句
- 用DROP删除视图，其语法为DROP VIEW viewname
- 更新视图时，可以先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图
2.更新视图
通常，视图是可更新的。更新一个视图将更新其基表，如果你对视图增加或删除行，实际上是对其基表增加或删除行
但是并非所有视图都是可更新的，如果MySQL不能正确地确定被更新的基数据，则不允许更新。即如果视图中有以下操作，则不能进行视图的更新：
- 分组(使用GROUP BY和HAVING);
- 联结
- 子查询
- 并
- 聚集函数(Min(),Count(),Sum()等)
- DISTINCT
- 导出(计算)列
十九.使用存储过程
存储过程
存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合，可将其视为批文件，虽然它们的作用不仅限于批处理。
为什么要使用存储过程
- 通过把处理封装在容易使用的单元中，简化复杂的操作
- 由于不要求反复建立一系列处理步骤，这保证了数据的完整性
- 简化对变动的管理。如果表名，列名或业务逻辑(或别的内容)有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化
- 提高性能。因为使用存储过程比使用单独的SQL语句要快
- 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更灵活的代码
- 一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验
执行存储过程
1.创建存储过程
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END;
mysql命令行客户机的分隔符
默认的MySQL语句分隔符为；，mysql命令行实用程序也使用；作为语句分隔符。如果命令行实用程序要解释存储过程自身内的；字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现语法错误。
解决办法是临时更改命令行实用程序的语句分隔符：
DELIMITER //
DELIMITER //告诉命令行实用程序使用//作为新的语句结束分隔符。
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END //
恢复为原来的语句分隔符可使用DELIMITER ;
除\符号外，任何字符都可以用作语句分隔符
- 删除存储过程
DROP PROCEDURE productpricing;
3.使用参数
变量(variable) 内存中一个特定的位置，用来临时存储数据
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支持IN(传递给存储过程)，OUT(从存储过程传出)和INOUT(对存储过程传入和传出)类型的参数。
调用这个存储过程，必须指定3个变量名：
CALL productpricing(@pricelow,@pricehigh,@priceverage);
变量名 所有MySQL变量都必须以@开始
CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGING
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num=onumber
INTO ototal;
END;
为了调用这个存储过程，可使用以下语句：
CALL ordertotal(20005,@total);
第一个参数是IN所以是传入，第二个参数是OUT所以是传出；
为了显示合计，可如下进行:
SELECT @total;
4.建立智能存储过程
5.检查存储过程
为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句
SHOW CREATE PROCEDURE ordertotal;
为了获得包括何时，由谁创建等详细信息的存储过程列表，使用：
SHOW PROCEDURE STATUS;
为了限制其输出，可使用LIKE指定一个过滤模式：
SHOW PROCEDURE STATUS LIKE 'ordertotal';
二十.使用游标
游标
游标(cursor)是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行流浪或作出更改。
只能用于存储过程：
不像多数DBMS,MYSQL游标只能用于存储过程(和函数)
使用游标
- 在能够使用游标前，必须声明(定义)它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
- 一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
- 对于填有数据的游标，根据需要取出(检索)各行。
- 在结束游标使用时，必须关闭游标。
创建游标
游标用DECLARE语句创建。DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE其他子句。
下面的语句定义了名为ordernumbers的游标，使用了可以检索所有订单的SELECT语句：
CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
END;
打开和关闭游标
游标用OPEN CURSOR语句来打来
OPEN ordernumbers;
游标用CLOSE CURSOR语句来关闭
CLOSE ordernumbers;
CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。
在一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用声明过的游标不需要再次声明，用OPEN语句打开它就可以了。
隐含关闭：
如果你不明确关闭游标，MySQL将会在到达END语句时自动关闭它。
使用游标数据
在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据(所需的列)，检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索一行(不重复读取同一行)。
二十一.使用触发器
触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句(或位于BEGIN和END语句之间的一组语句)：
- DELETE;
- INSERT;
- UPDATE;
其他MySQL语句不支持触发器
创建触发器
在创建触发器时，需要给出4条信息：
- 唯一的触发器名；
- 触发器关联的表；
- 触发器应该响应的活动(DELETE,INSERT或UPDATE);
- 触发器何时执行(处理之前还是之后)
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';
只有表才支持触发器，视图不支持(临时表也不支持)
触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器(每条INSERT,UPDATE和DELETE的之前和之后)。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个队INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。
删除触发器
DROP TRIGGER newproduct;
触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，然后再重新创建
使用触发器
1.INSERT触发器
- 在INSERT触发器代码内，可引用一个名为NEW的虚拟机，访问被插入的行；
- 在BEFORE INSERT触发器中，NEW中的值也可以被更新；
- 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值；
2.DELETE触发器
- 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
- OLD中的值全部都是只读，不能更新；
3.UPDATE触发器
- 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟机访问以前(UPDATE语句前)的值，引用一个名为NEW的虚拟表访问新更新的值
- 在BEFORE UPDATE触发器中，NEW中的值可能也被更新(允许更改将要用于UPDATE语句中的值)；
- OLD中的值全部都是只读的，不能更新；
二十二.管理事务处理
事务处理
并非所有引擎都支持事务处理
- MyISAM不支持明确的事务处理管理
- InnoDB支持事务处理
事务处理(transaction processing)可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行
几个术语：
- 事务(transaction)指一组SQL语句；
- 回退(rollback)指撤销指定SQL语句的过程；
- 提交(commit)指将未存储的SQL语句结果写入数据库；
- 保留点(savepoint)指事务处理中设置的临时占位符(place-holder),你可以对它发布回退
控制事务处理
MySQL使用下面的语句来标识事务的开始：
START TRANSACTION;
1.使用ROLLBACK
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT *FROM ordertotals;
ROLLBACK;
SELECT *FROM ordertotals;
2.使用COMMIT
一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交(implicit commit),即提交(写或保存)操作是自动进行的。
但是，在事务处理快中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句
START TRANSACTION;
DELETE FROM orderitems WHERE order_num=20010;
DELETE FROM orders WHERE order_num=20010;
COMMIT;
隐含事务关闭：当COMMIT或ROLLBACK语句执行后，事务会自动关闭(将来的更改会隐含提交)
3.使用保留点
简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但是，只是对简单的事物处理才能这样做，更复杂的事物处理可能需要部分提交或回退。
为了支持回退部分事物处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。
创建占位符：
SAVEPOINT delete1;
每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。为了回退到本例给出的保留点，可如下进行：
ROLLBACK TO delete1;
释放保留点：
保留点在事务处理完成(执行一条ROLLBACK或COMMIT)后自动释放，自MySQL5以来，也可以用RELEASE SAVAPOINT明确地释放保留点。
4.更改默认的提交行为
为指示MySQL不自动提交更改，需要使用以下语句：
SET autocommit=0;
autocommit标志决定是否自动提交更改，不管有没有COMMIT语句。设置autocommit为0指示MySQL不自动提交更改(直到autocommit被设置为真为止)
二十三.全球化和本地化
字符集和校队顺序
以下重要术语：
- 字符集为字母和符号的集合；
- 编码为某个字符集成员的内部指示；
- 校队为规定字符如何比较的指令；
查看所支持的字符集完整列表：
SHOW CHARACTER SET;
为了查看所支持校队的完整列表：
SHOW COLLATION;
此语句显示所有可用的校队，以及它们适用的字符集
为了给表指定字符集和校对，可使用带子句的CREATE TABLE
CREATE TABLE mytable
(
columnn1 INT,
columnn2 VARCHAR(10),
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
此语句创建一个包含两列的表，并指定一个字符集和一个校对顺序
一般，MySQL如下确定使用什么样的字符集和校对：
- 如果指定CHARACTER SET和COLLATE两者，则使用这些值
- 如果只指定CHARACTER SET,则使用此字符集及其默认的校对
- 如果既不指定CHARACTER SET，也不指定COLLATE,则使用数据库默认
除了能指定字符集和校对的表范围外，MySQL还允许对每个列设置它们，如下所示：
CREATE TABLE mytable
(
columnn1 INT,
columnn2 VARCHAR(10),
column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
校对在对用ORDER BY子句检索出来的数据排序时起重要的作用，如果你需要用与创建表时不同的校对顺序排序特定的SELECT语句，可以在SELECT语句自身中进行：
SELECT *FROM customers
ORDER BY lastname,firstname COLLATE latin1_general_cs;
二十四.安全管理
管理用户
1.查看账户
MySQL用户账户和信息存储在名为mysql的MySQL数据库中。
USE mysql
SELECT user FROM user;
2.创建用户账户
CREATE USER ben IDENTIFIED BY 'P@$$Ord';
CREATE USER创建一个新用户账号，在创建用户账户时不一定需要口令。
指定散列口令
IDENTIFIED BY指定的口令为纯文本，MySQL将在保存到user表之前对其进行加密。为了作为散列值指定口令，使用IDENTIFIED BY PASSWORD
使用GRANT或INSERT
GRANT语句也可以创建用户账户，此外，也可以通过直接插入行到user表来增加用户，不过为了安全起见，一般不建议这样做。
重新命名一个用户账户:
RENAME USER ben TO bforta;
3.删除用户账户
DROP USER bforta;
4.设置访问权限
为看到赋予用户访问权限，使用SHOW GRANTS FOR
SHOW GRANTS FOR bforta;
USAGE ON . 表示根本没有权限
为设置权限，使用GRANT语句,GRANT要求你至少给出以下信息:
- 要授予的权限；
- 被授予访问权限的数据库或表；
- 用户名；
以下例子给出GRANT的用法：
GRANT SELECT ON crashcourse .* TO bforta;
此GRANT允许用户在crashcourse.*(crashcourse 数据库的所有表)上使用SELECT
GRANT的反操作为REVOKE,用它来撤销特定的权限
REVOKE SELECT ON crashcourse.* FROM bforta;
这条REVOKE语句取消刚赋予用户bforta的SELECT访问权限。被撤销的访问权限必须存在，否则会出错。
GRANT和REVOKE可在几个层次上控制访问权限：
- 整个服务器，使用GRANT ALL和REVOKE ALL;
- 整个数据库，使用ON database.*;
- 特定的表，使用ON database.table;
- 特定的表，使用ON database.table;
- 特定的列；
- 特定的存储过程
权限表：
| 权限 | 说明 |
|---|---|
| ALL | 除GRANT OPTION外的所有权限 |
| ALTER | 使用ALTER TABLE |
| ALTER ROUTINE | 使用ALTER PROCEDURE和DROP PROCEDURE |
| CREATE | 使用CREATE TABLE |
| CREATE ROUTINE | 使用CREATE PROCEDURE |
| CREATE TEMPORARY TABLE | 使用CREATE TEMPORARY TABLE |
| CREATE USER | 使用CREATE USER,DROP USER,RENAME USER和REVOKE ALL PRIVILEGES |
| CREATE VIEW | 使用CREATE VIEW |
| DELETE | 使用DELETE |
| DROP | 使用DROP TABLE |
| EXECUTE | 使用CALL和存储过程 |
| FILE | 使用SELECT INTO OUTFILE和LOAD DATA INFILE |
| GRANT OPTION | 使用GRANT和REVOKE |
| INDEX | 使用CREATE INDEX和DROP INDEX |
| INSERT | 使用INSERT |
| LOCK TABLES | 使用LOCK TABLES |
| PROCESS | 使用SHOW FULL PROCESSSLIST |
| RELOAD | 使用FLUSE |
| REPLICATION CLIENT | 服务器位置的访问 |
| REPLICATION SLAVE | 由复制从属使用 |
| SELECT | 使用SELECT |
| SHOW DATABASES | 使用SHOW DATABASES |
| SHOW VIEW | 使用SHOW CREATE VIEW |
| SHUTDOWN | 使用mysqladmin shutdown(用来关闭MySQL) |
| SUPER | 使用CHANGE MASTER,KILL,LOGS,PURGE,MASTER和SET GLOBAL。还允许mysqladmin调试登录 |
| UPDATE | 使用UPDATE |
| USAGE | 无访问权限 |
简化多次授权
可通过列出各权限并用逗号分隔，将多条GRANT语句串在一起，如下所以：
GRANT SELECT ,INSERT ON crashcourse.* TO bforta;
5.更改口令
为了更改用户口令，可使用SET PASSWORD语句，新口令必须如下加密：
SET PASSWORD FOR bforta=Password('n3w p@$$w0rd');
SET PASSWORD还可以用来设置你自己的口令:
SET PASSWORD=Password('n3w p@$$w0rd');
在不指定用户名时，SET PASSWORD更新当前登录用户的口令
二十五.数据库维护
备份数据
像所有数据一样，MySQL的数据也必须经常备份。由于MySQL数据库是基于磁盘的文件，普通的备份系统和例程就能备份MySQL的数据。但是,由于这些文件总是处于打开和使用状态，普通的文件副本备份不一定总是有效。
下面列出这个问题的可能解决方案：
- 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件。
- 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据(并非所有数据库引擎都支持这个实用程序)。
- 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件名必须不存在，否则会出错。数据可以用RESTORE TABLE来复原。
首先刷新未写数据：为保证所有数据被写到磁盘(包括索引数据)，可能需要在进行备份前使用FLUSH TABLES语句
进行数据库维护
- ANALYZE TABLE,用来检查键是否正确
- CHECK TABLE用来针对许多问题对表进行检查。在MyISAM表上还对索引进行检查。CHECK TABLE支持一系列的用于MyISAM表的方式。CHANGED检查自最后一次检查以来改动过的表。EXTENDED执行最彻底的检查，FAST只检查未正常关闭的表，MEDIUM检查所有被删除的链接并进行键检验，QUICK只进行快速扫描。如下所示，CHECK TABLE发现和修复问题。
- 如果MyISAM表访问产生不正确和不一致的结果，可能需要用REPAIR TABLE来修复相应的表。这条语句不应该经常使用，如果需要经常使用，可能会有更大的问题要解决。
- 如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所有的问题，从而优化表的性能。
诊断启动问题
在排除系统启动问题时，首先应该尽量用手动启动服务器。MySQL服务器自身通过在命令行上执行mysql启动。下面是几个重要的mysqld命令行选项：
- –help显示帮助——一个选项列表；
- –safe-mode装载减去某些最佳配置的服务器；
- –verbose显示全文本消息；
- –version显示版本信息然后退出
查看日志文件
MySQL维护管理员依赖的一系列日志文件。主要的日志文件有以下几种：
- 错误日志。它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err,位于data目录上。此日志名可用–log-error命令行选项更改
- 查询日志。它记录所有MySQL活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它，此日志通常名为hostname.log,位于data目录中。此名字可以用–log命令行选项更改。
- 二进制日志，它记录更改过数据(或者可能更新过数据)的所有语句。此日志通常名为hostname-bin,位于data目录内。此名字可以用–log-bin命令行选项更改。
- 缓慢查询日志。顾名思义，此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为hostname-slow.log,位于data目录中。此名字可以用–log-slow-queries命令行选项更改。
5253
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
