mysql用于指定被搜索列个是,MySQL

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;