DISTINCT
// 返回vend_id列中不同的值
SELECT DISTINCT vend_id FROM products;
// 返回vend_id列和pro_name列都不同的值
SELECT DISTINCT vend_id, pro_name FROM products;
DISTINCT关键词应用于所有列,不仅是其后面的第一列
LIMIT
limit限制检索出的结果的显示条数,即结果已经出来,只是按不同要求显示
// 显示检索结果的头3条
SELECT DISTINCT vend_id, prod_name FROM products LIMIT 3;
// 从第3条(不包括第3条)开始往后的4条
SELECT DISTINCT vend_id, prod_name FROM products LIMIT 3, 4;
// 等价
SELECT DISTINCT vend_id, prod_name FROM products LIMIT 4 OFFSET 3;
完全限定行名和表名
SELECT products.vend_id FROM ts.products; // products存在库ts中
// 等价
SELECT vend_id FROM products;
ORDER BY
检索出的结果按某(几)列排序(默认升序)
// 按显示列排序
SELECT prod_name FROM products ORDER BY prod_name;
// 按非显示类排序
SELECT prod_name FROM products ORDER BY prod_price;
// prod_price相同情况下看prod_name
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;
// prod_price(升序)相同情况下看prod_name(降序)
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name DESC;
DESC关键字只应用到直接位于其前面的列名,如果想在多个列上进行降序排序,必须对每个列指定DESC关键字。
ORDER BY和LIMIT组合可以找最大最小值
SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;
如果使用LIMIT,它必须在ORDER BY后面
WHERE
同时使用WHERE和ORDER BY,应该让ORDER BY位于WHERE后面
BETWEEN匹配范围内所有值,包括开始值和结束值
SELECT prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
NULL空值与0,字空字符串,空格不同
SELECT cust_id FROM customers WHERE cust_email IS NULL;
AND和OR组合要注意,SQL优先处理AND操作符,想要优先处理OR,可以加圆括号
// 列出价格为10(含)以上且由1002或1003制作的所有产品,没有括号则意义不同
SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
任何时候使用具有AND和OR操作符的WHERE子句,都应该使用圆括号明确的分组操作符,不要过分依赖默认计算次序。
IN操作符用来指定条件范围,范围内的每个条件(圆括号内)都可以进行匹配。
SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002,1003,1005) ORDER BY prod_name;
可以使用OR改写,但优先使用IN
NOT支持对IN,BETWEEN和EXISTS字句取反
SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002,1003);
通配符
LIKE搭配通配符在WHERE子句中使用
'%'表示任何字符出现任何次数
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
尾空格可能会产生干扰,如anvil,如果保存它时后面多了空格,则'%anvil'将不会匹配,解决办法可以是后面加个%,更好的方法是使用函数去掉收尾空格。
虽然似乎%通配符可以匹配任何东西,但有一个例外,即NULL,即使是WHERE prod_name LIKE '%' 也不能匹配值为NULL的行。
'_'匹配单个字符
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';
不要过度使用通配符,慢。
函数
CONCAT
CONCAT()拼接多个串
// 去掉AS,字段名显示为前面一长串
SELECT CONCAT(vend_name,'(',vend_country,')') AS addr FROM vendors ORDER BY vend_name;
TRIM
TRIM()去掉串左右两边空格,RTRIM(),LTRIM()分别取右边,左边空格
算术运算
SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM orderitems WHERE order_num=20005;
聚集函数
聚集函数运行在行组上
函数
说明
AVG
返回某列平均值
COUNT
返回某列行数
MAX
返回某列最大值
MIN
返回某列最小值
SUM
返回某列之和
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id=1003;
COUNT()有两种使用方法
COUNT(*)对表中行的数目进行计数,不管列表中包含的是空值(NULL)还是非空值
COUNT(column)对特定列中具有值的行进行计数,忽略NULL值
SELECT COUNT(*) AS num_cust FROM customers; // 5条
SELECT COUNT(cust_email) AS num_cust FROM customers; // 3条
MAX()一般用于找出最大数值或日期值,忽略NULL,但MySQL允许将它用来返回任意列中的最大值,在用于文本数据时,如果按相应的列排序,则MAX()返回最后一行。
SELECT MAX(prod_price) AS max_price FROM products;
MIN()使用同MAX(),用在文本数据上,则返回第一行
SELECT MIN(prod_price) AS min_price FROM products;
SUM()忽略NULL值,可以合计计算,如下面第二个例子
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语句的GROUP BY子句中建立的。
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;
分组1.PNG
// 加上WITH ROLLUP可以统计各分组和
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;
分组2.PNG
使用GROUP BY需要注意的地方
GROUP BY子句可以包含任意数目的列
GROUP BY子句中出现的每个列都必须是检索列或有效表达式(但不能是聚集函数),如果在SELECT中使用表达式,则必须在GROUP BY子句中指定相同的表达式,不能使用别名
除聚集计算语句外,SELECT语句中的每个列都必须在GROUP BY子句中给出
如果分组列中具有NULL,则NULL将作为一个分组返回,如果有多行NULL,它们分为一组
GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前
过滤分组
HAVING过滤分组,WHERE过滤行。
WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤,WHERE排除的行不包括在分组中,这可能会改变计算值,从而影响HAVING子句中基于这些值过滤掉的分组。
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
同时使用WHERE和HAVING
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;
搭配ORDER BY
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50;
未排序.PNG
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertotal;
有排序.PNG
SELECT子句及其顺序
子句
说明
是否必须使用
SELECT
要返回的咧或表达式
是
FROM
从中检索数据的表
仅在从表选择数据时使用
WHERE
行级过滤
否
GROUP BY
分组
仅在按组计算聚集时使用
HAVING
组级过滤
否
ORDER BY
输出排序顺序
否
LIMIT
要检索的行数
否
子查询
SELECT cust_name
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语句中关联表,返回一组结果。
内部联结
基于两个表之间的相等测试,这种联结也称为等值联结。
使用INNER JOIN(首选)
SELECT vendors.vend_name, products.prod_name, products.prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
使用WHERE
SELECT vendors.vend_name, products.prod_name, products.prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id;
联结多张表,上面子查询中的例子可改写成
SELECT customers.cust_name
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND orderitems.prod_id = 'TNT2';
给表起别名的好处
缩短SQL语句
允许在单条SELECT语句中多次使用相同的表,别名可用于WHERE子句,ORDER BY子句,SELECT的列表等。
上面一条使用别名改写
SELECT c.cust_name
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 oi.prod_id = 'TNT2';
查询生产DTNTR的供应商生产的全部产品
// 错误写法,别名只能在单条SELECT语句中有效
SELECT p.prod_id
FROM products AS p
WHERE p.vend_id = (SELECT p.vend_id
FROM p
WHERE p.prod_id = 'DTNTR');
正确写法
SELECT p1.prod_id
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR';
外部联结
外部联结包含了那些在相关表中没有关联行的行。
内部联结,检索所有客户及其订单
SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;
内联.PNG
外部联结,包括那些没有订单的客户
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
外联.PNG
在使用OUTER JOIN语法时,必须使用RIGHT或LEFT关键字指定包括所有行的表(RIGHT指定的是OUTER JOIN右边的表,LEFT是左边的表),它们之间差别是所关联的表的顺序不同,换句话说,左外联结可以通过颠倒FROM或WHERE子句中表的顺序转换为右联结。因此两种类型的外部联结可互换使用,而究竟使用哪一种纯粹是根据方便而定。
带聚集函数的联结,检索所有客户及每个客户所下的订单数
SELECT customers.cust_id, COUNT(orders.order_num) as num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
聚集函数的联结.PNG
此例使用内联则没有10002行
组合查询
利用UNION操作符将多条SELECT语句组合成一个结果集。
检索价格小于等于5,以及供应商为1001,1002的产品
SELECT prod_id, vend_id, prod_price
FROM products
WHERE prod_price <= 5
OR vend_id IN (1001,1002);
上面为使用WHERE,下面为使用UNION
SELECT prod_id, vend_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT prod_id, vend_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);
在这个简单的例子中,使用UNION可能比使用WHERE复杂,但对于更加复杂的过滤条件,或者从多个表中检索数据,使用UNION可能会使处理更简单。
UNION使用规则
-UNION必须由两条或两条以上的SELECT语句组成,语句之间用UNION分隔
UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)
列数据类型必须兼容,类型不必完全相同,但必须是DBMS可以隐含地转换的类型(例如不同的数值类型或不同的日期类型)
UNION会去掉多个查询中重复的行,如果不想去掉重复行,可使用UNION ALL
搭配ORDER BY使用,只能出现在最后一条SELECT语句后,用来排序所有SELECT语句返回的结果
SELECT prod_id, vend_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT prod_id, vend_id, prod_price
FROM products
WHERE vend_id IN (1001,1002)
ORDER BY vend_id, prod_price;
全文索引
MySQL5.6之后InnoDB引擎也支持全文索引,之前是MyISAM才支持。
在相应的列建立全文索引之后,可以使用两个函数Match()和Against()执行全文搜索,其中Match()指定被搜索的列,Against()指定要使用的搜索表达式。
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('rabbit');
全文搜索.PNG
传递给Match()的值必须与FULLTEXT()定义中的相同,如果指定多个列,则必须列出它们(而且次序正确),除非使用BINARY方式,否则全文本搜索不区分大小写。
全文搜索的一个重要部分是对结果排序,具有较高等级的行排在前面,等级由MySQL根据行中词的数目、唯一词的数目、整个索引词的总数以及包含该词的行的数目计算出来的。
SELECT note_text, MATCH(note_text) AGAINST('rabbit') AS rank
FROM productnotes;
等级计算.PNG
插入数据
INSERT是用来插入行到数据库表中的,插入可以用几种方式使用
插入完整的行
插入行的一部分
插入多行
插入某些查询的结果
如果表的定义允许,则可以在INSERT操作中省略某些列,省略的列必须满足一下某个条件。
该列定义允许NULL值(无值或空值)
在表定义中给出默认值,这表示如果不给出值,将使用默认值
INSERT操作可能很耗时(特别是有很多索引需要更新时),而且它可能降低等待处理SELECT语句的性能。如果数据检索是最重要的,则可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY,指示MySQL降低INSERT语句的优先级,如下所示
INSERT LOW_PRIORITY INTO
这也适用于UPDATE和DELETE操作。
单条INSERT语句处理多个插入比使用多条INSERT语句快
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES(
'Pep',
'100 Street',
'CA',
'90046',
'USA'
),
(
'Mar',
'42 Street',
'New York',
'NY',
'11213',
'USA'
);
每组值用一对圆括号括起来,用逗号分隔。
INSERT可以将一条SELECT语句的结果插入表中,这就是所谓的INSERT SELECT。
假设你想从一张表中合并客户列表到你的customers表,不需要每次读取一行然后再插入,可以如下
INSERT INTO customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city
FROM custnew;
如果custnew表为空,操作也合法。这个例子导入了cust_id,如果你不能保证它不重复,则可以省略这列,MySQL会自动生成新值。SELECT中还可以使用WHERE过滤要插入的数据。
为简单起见,这个例子在INSERT和SELECT语句中使用了相同的列名,但其实不一定要求列名匹配。MySQL使用的是列的位置,即SELECT中的第一列将用来填充INSERT中的第一列,如此等等,只要数据类型兼容即可。
更新数据
UPDATE操作一定要带上WHERE子句
UPDATE customers
SET cust_name = 'cy',
cust_email = 'cy@123.com'
WHERE cust_id = 1002;
如果用UPDATE更新多行,过程中出现错误,则整个UPDATE操作被取消。可以使用IGNORE关键字忽略错误:UPDATE IGNORE customers...
为删除某个列的值,可设置它为NULL(假设表定义允许NULL值)
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 1005;
删除数据
DELETE操作一定要带上WHERE子句
DELETE FROM customers
WHERE cust_id = 1006;
如果想删除表中所有行,不要使用DELETE,而使用TRUNCAT TABLE语句,它速度快,实际是删除原来的表并重新创建一个表,而不是像DELETE一样逐行删除。
创建表
主键值必须唯一,如果主键使用单个列,则它的值必须唯一,如果使用多个列,则这些列的组合必须唯一。
CREATE TABLE orderitems
(
order_num int NOT NULL,
order_item int NOT NULL,
prod_id char(10) NOT NULL,
item_price decimal(8,2) NOT NULL,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (order_num, order_item)
) ENGIN=InnoDB;
使用DEFAULT指定默认值
如果一个列被指定为AUTO_INCREMENT(一般为自动递增主键),也可以在INSERT语句中指定一个值,只要它是唯一的(至今未使用过),该值用来替代自动生成的值,后续将从该值开始递增。如何在插入一行后获得这个主键值呢?使用last_insert_id()函数,即SELECT last_insert_id()
视图
视图是虚拟的表,不含实际的数据,只包含使用时动态检索的查询。
创建视图语句
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = order.cust_id
AND orderitems.order_num = order.order_num;
使用视图
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';
使用视图的原因
重用SQL语句
简化复杂的SQL操作
使用表的组成部分而不是整个表
保护数据,可以给用户授权表的特定部分的访问权限而不是整个表的访问权限
在视图创建之后,可以对其执行SELECT操作,过滤和排序数据,将视图联结到其他视图或表,甚至能添加和更新数据(添加和更新存在某些限制)。视图使用不当会产生性能问题。
事务处理
事务处理是一种机制,用来管理必须成批执行的MySQL操作,以保证数据库不包含不完整的操作结果。
MySQL使用下面的语句来标识事务的开始:
START TRANSACTION
ROLLBACK用来回退START TRANSACTION之后的所有语句。可以回退INSERT,UPDATE,DELETE操作,但不能回退CREATE,DROP操作。
一般的MySQL语句都是隐含提交的(可以SET autocommit=0; 取消自动提交,这针对本次连接,而不是服务器),但在事务中不会,只有明确使用COMMIT提交才能生效。
START TRANSCATION;
DELETE FROM ordersitems WHERE order_num = 2001;
DELETE FROM orders WHERE order_num = 2001;
COMMIT;
最后的COMMIT语句仅在两条删除操作不出错时提交更改。
当COMMIT或ROLLBACK语句执行后,事务自动关闭,之后的语句回复隐含提交。
为了支持回退部分事务处理,可以在合适位置放置占位符,这样就可以按需回退到某个占位符。使用SVAEPOINT创建占位符。
SAVEPOINT del1;
回退到某个占位符,使用ROLLBACK TO
ROLLBACK TO del1;
字符集和校对顺序
字符集为字母和符号的集合
编码为某个字符集成员的内部表示
校对为规定字符如何比较的指令
查看支持的字符集
SHOW CHRACTER SET;
查看支持的校对
SHOW COLLATION;
通常安装MySQL后有默认的字符集,字符集有默认的校对。查看方式如下
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';
实际上,字符集很少是服务器范围(甚至数据库范围)的设置。不同的表,甚至不同的列都可能需要不同的字符集,而且两者都可以在创建表时指定。
CREATE TABLE mytable
(
column1 INT,
column2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
CREATE TABLE mytable
(
column1 INT,
column2 VARCHAR(10),
column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
SELECT * FROM customers
ORDER BY lastname, firstname COLLATE latin1_general_cs;
技巧
设置插入数据时时间字段自动插入
alter table table_name
MODIFY column create_time datetime default CURRENT_TIMESTAMP;