Mysql SQL语句详解

一、检索数据

1、SELECT语句

检索单列 ：

SELECT prod_name FROM Products;

检索多个列：

SELECT prod_id,prod_name,prod_price FROM Products;

检索多个列：

SELECT * FROM Products;

检索不同的值：

SELECT DISTINCT vend_id FROM Products;

(DISTINCT  关键字作用于所有的列，不仅仅是跟在其后的那一列)

2、限制结果(Mysql中行是从第0行开始计算的)

指定获取前几行：

SELECT prod_name FROM Products LIMIT 2;   -- (LIMIT表示的是获取的行数，LIMIT 2表示的是获取前两行数据，即第0行和第1行)

获取指定行数据：

SELECT prod_name FROM Products LIMIT 2 OFFSET 1; -- 表示从第1行开始，获取两行(获取的是行1和行2，没有行0）

获取指定行数据：

SELECT prod_name FROM Products LIMIT 1,2;     -- 该句是LIMIT 2 OFFSET 1的简化版，效果相同 

二、检索排序数据

1、按单列排序数据：

SELECT prod_name FROM Products ORDER BY prod_name;  -- 使用ORDER  BY 语句时，必须保证它是语句中最后一条字句

2、按多个列排序：

SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price,prod_name; -- 先按价格排序，若价格相同，按name排序

3、按列位置排序：

SELECT prod_id,prod_price,prod_name FROM Products ORDER BY 2,3; -- 效果同上面按多个列排序

4、指定排序方向：

SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC; -- 对price按降序排列，默认是按升序，也可用ASC指定

5、多个列排序：

SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC,prod_name; -- DESC关键字只应用到直接位于其前面的列名

(如果想在每个列上进行降序排列，必须对每一列指定DESC关键字)

三、用WHERE过滤数据

1、检查单个值：

SELECT prod_id,prod_price,prod_name FROM Products WHERE prod_price = 3.49; -- 使用ORDER BY和WHERE字句时，ORDER BY 应位于WHERE之后

2、不匹配检查：

SELECT prod_id,prod_price,prod_name FROM Products WHERE vend_id != 'DLL01'; -- 单引号用来限定字符串。如果将值与字符串类型的列进行比较，就需要限定引号。与数值列进行比较的值不用引号。

3、范围值检查：

SELECT prod_id,prod_price,prod_name FROM Products WHERE prod_price BETWEEN 5 AND 10;

4、检查是否为空值(NULL)：

SELECT cust_name FROM CUSTOMERS WHERE cust_email IS NULL; -- 空值是指列不包含值，它与字段包含0、空字符串或仅仅包含空格不同。

四、WHERE 高级数据过滤

1、AND操作符：

SELECT prod_id,prod_price,prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;

2、OR操作符：

SELECT prod_price,prod_name FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

3、求值顺序：

SELECT prod_name,prod_price,vend_id FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >= 10; -- 由于AND优先级比OR要高，所以会先将vend_id = 'BRS01' AND prod_price >= 10作为一个条件，接着再与vend_id = 'DLL01'进行OR操作

4、IN操作符：

SELECT prod_name,prod_price FROM Products WHERE vend_id IN ('DLL01','BRS01') ORDER BY prod_name; -- IN是WHERE字句中用来指定要匹配的清单的关键字，功能与OR相当。

5、NOT操作符：

SELECT prod_name,prod_price FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name; -- IN用于WHERE字句中用来表示否定其后条件，因此上面语句也可用 != 操作符完成

五、用通配符进行过滤

通配符本身实际上是WHERE字句中有特殊含义的字符。为在搜索字句中使用通配符，必须使用IKE操作符，LIKE是一个谓词。

1、百分号(%)通配符：

SELECT prod_id,prod_name FROM Products WHERE prod_name LIKE 'Fish%'; -- %表示任何字符出现任意次

2、下划线(_)通配符：

SELECT prod_id,prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear'; -- 匹配单个字符

六、创建计算字段

计算字段并不实际存在于数据库表中，它是运行在SELECT语句内创建的。

1、拼接字段：

SELECT Concat(vend_name,'(',vend_country,')') FROM Vendors ORDER BY vend_name;

     使用别名：

SELECT Concat(vend_name,'(',vend_country,')') AS vend_title FROM Vendors ORDER BY vend_name;

2、执行算数计算：

SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;

七、汇总数据

1、聚集函数：我们经常需要汇总数据而不是把它们实际检索出来。因此我们实际需要的是汇总信息(聚集函数都会忽略值为NULL的行)。

AVG()函数：

SELECT AVG(prod_price) AS avg_price FROM Products; -- 求某一列的平均值

COUNT()函数

用COUNT(*)对表中行的数目计数：

SELECT COUNT(*) AS num_cust FROM Customers;

用COUNT(column)对特定列计数：

SELECT COUNT(cust_email) AS num_cust FROM Customers;

MAX()函数：

SELECT MAX(prod_price) FROM Products; -- 返回指定列最大值

MIN()函数：

SELECT MIN(prod_price) FROM Products; -- 返回制定列最小值

SUM()函数：

SELECT SUM(prod_price) FROM Products;

2、聚集不同值

上述五个聚集函数都可如下使用：

对多有行执行计算，指定ALL参数或不指定参数，都按ALL计算。

只包含不同的值时，指定DISTINCT参数。DISTINCT可用于AVG()、SUM()，不能用于COUNT()，用于MAX()和MIN()无意义。

八、分组数据

使用分组可以将数据分为多个逻辑组，对每个分组进行聚集计算。

GROUP  BY自己可以包含任意数目的列。因而可以对分组进行嵌套，分得更细。嵌套了分组后，数据将在最后指定的分组上进行汇总。

GROUP  BY子句中列出的每一列都必须是检索列或有效的表达式。

如果在SELECT语句中使用了表达式，则必须在GROUP  BY子句中指定相同的表达式，不能使用别名。

SQL 实现不允许GROUP BY 列带有长度可变的数据类型(如文本和备注型字段)。

GROUP  BY子句必须出现在WHERE子句之后，ORDER BY 子句之前。

1、创建分组：

SELECT vend_id,count(*) AS num_prods FROM Products GROUP BY vend_id; -- GROUP BY 自己指示DBMS按vend_id排序并分组数据。这就会对每个vend_id而不是整个表计算num_prods一次。因为使用了GROUP  BY ，就不必指定要计算和估值的每个组了。系统会自动完成。GROUP BY子句指示DBMS分组数据，然后对每个组而不是整个结果集进行聚集。

2、过滤分组

SQL使用HAVING子句过滤分组。HAVING非常类似于WHERE。目前学过的所有类型的WHERE子句都可以使用HAVING替代。唯一的差别是WHERE过滤行，HAVING过滤列

HAVING过滤语句：

SELECT cust_id,count(*) AS num_cust FROM Orders GROUP BY cust_id HAVING COUNT(*) > 1;

SELECT vend_id,COUNT(*) AS num_prods FROM Products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) > 1; 

SELECT order_num,COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items,order_num; -- 该句使用GROUP BY分组数据，以便COUNT(*) 函数能返回每个订单中的物品数目。HAVING子句过滤数据。最后ORDR BY 子句排序输出。

九、子查询

1、利用子查询进行过滤

列出需要订购物品RGAN01的所有顾客：

SELECT cust_name,cust_contact   -- 检索子查询中返回的所有顾客ID的顾客信息FROM Customers WHERE cust_id IN (SELECT cust_id  --  检索具有子查询中列出的订单编号的所有顾客的IDFROM orders WHERE order_num IN (SELECT order_num   -- 检索包含物品RGAN01的所有顾客FROM OrderItems WHERE prod_id = 'RGAN01')); 

2、作为计算字段使用子查询

SELECT cust_name,cust_state, (SELECT COUNT(*) FROM orders WHERE Customers.cust_id = orders.cust_id) AS order_num FROM Customers ORDER BY cust_name;

十、连接查询

按连接类型分为：内连接、外连接和交叉连接。

1、内连接分为三种：

等值连接：连接条件中使用等号(=)比较被连接列的列值，包括重复列。

不等值连接：连接条件中使用除等号意外的比较运算符比较被连接的列值。

自然连接：等值连接去除连接中的重复列即可。

自然连接实例：

SELECT vend_name,prod_name,prod_priceFROM Vendors,Products WHERE Vendors.vend_id = Products.vend_id;

上述不同表示方式：

SELECT vend_name,prod_name,prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;

2、外连接

分为左外连接、右外连接和全外连接。

左外连接实例：

SELECT Customers.cust_id,orders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

3、交叉连接(即笛卡尔积)：

SELECT vend_name,prod_name,prod_priceFROM Vendors,products;

十一、组合查询

合并查询用到的情况：

在一个查询中从不同的表返回结构数据

对一个表执行多个查询，按一个查询返回数据

注意的规则：

UNION必须由两条或两条以上的SELECT语句组成。

UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过，各个列不需要以相同的次序列出)。

列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐式转换的类型。

若想返回所有的匹配行，可使用UNION  ALL。

用union实现Illinois、Indiana和Michigan美国这几个州的所有顾客的报表，还想包括所有的Fun4All：

SELECT cust_name,cust_contact,cust_email FROM customers WHERE cust_state IN ('IL','IN','MI') UNION SELECT cust_name,cust_contact,cust_emailFROM customersWHERE cust_name = 'Fun4All';

十二、插入数据

1、向Customers表中插入一条记录(可插入部分行或完整一行)：

INSERT INTO Customers(cust_id,<span style="white-space:pre"></span>      cust_name,      cust_address,      cust_city,      cust_state,      cust_zip,      cust_country,      cust_contact,      cust_email) VALUES ('1000000006','Toy Land','123 Any Street','New York','NY','11111','USA',NULL,NULL);

2、插入检索出的数据

INSERT INTO Customers(cust_id,      cust_name,      cust_address,      cust_city,      cust_state,      cust_zip,      cust_country,      cust_contact,      cust_email)SELECT  cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_emailFROM CustNew;

3、从一个表复制到另一个表

CREATE TABLE CustCopy ASSELECT * FROM Customers;

十三、更新和删除操作

更新操作

更新所有行：

UPDATE Customers SET cust_email = '[e-mail]'

更新特定行

UPDATE Customers SET cust_email = '[e-mail]'WHERE cust_id = '100000005';

在字段后追加字符串

update Customers set all_child_id=concat(all_child_id,','), child_id=concat(child_id,',')where id in(99,234);

删除操作

删除特定行：

DELETE FROM Customers WHERE cust_id = '1000000006';

删除所有行：

DELETE FROM Customers;

十四、表的创建、更改和删除

创建表：

CREATE TABLE OrderItems(  order_num  int          NOT NULL ,  order_item int          NOT NULL ,  prod_id    char(10)     NOT NULL ,  quantity   int          NOT NULL  DEFAULT 1,  item_price decimal(8,2) NOT NULL );

更新表

给表增加列：

ALTER TABLE VendorsADD vend_phone CHAR(20);

给表删除列：

ALTER TAB LE VendorsDROP COLUMN vend_phone;

删除表：

DROP TABLE CustCopy;

十五、视图

创建视图：

CREATE VIEW CustomerEmailList AS SELECT cust_id,cust_name,cust_email FROM Customers WHERE cust_email IS  NOT NULL;

十六、存储过程

简单说来，存储过程就是为以后使用而保存的一条或多条SQL语句。可将其视为批文件，虽然它们的作用不仅限于批处理。

实例：

DELIMITER &&CREATE PROCEDURE num_Customers(OUT count_num INT)BEGIN SELECT COUNT(*) INTO count_numFROM Customers;END  &&DELIMITER ;

十七、事务(transaction)

使用事务处理，通过确保成批的SQL操作要么完全执行，要么完全不执行，来维护数据库的完整性。

事务(transaction)：指一组SQL语句。

回退(rollback)：撤销指定SQL语句的过程。可以回退INSERT、UODATE和DELETE语句，不能回退SELECT、CREATE、DROP语句。

提交(commit)：指将未存储的SQL语句结果写入数据库表。

保留点(savepoint)：指事务处理中设置的临时占位符。

实例：

START TRANSACTION; -- 开启事务 INSERT INTO Customers(cust_id,cust_name)VALUES('1000000010','Toy Emporium');SAVEPOINT StartOrder; -- 使用保留点，如果需要ROLLBACK，可以回退到某个保留点INSERT INTO Orders(order_num,order_date,cust_id)VALUES(20100,'2001/12/1','1000000010');ROLLBACK TO StartOrder; -- 回退到保留点StartOrder COMMIT;-- 提交事务

十八、游标(cursor)

游标是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而不被该语句检索出来的结果集。

游标使用步骤：定义游标、打开游标、对填有数据的游标，取出各行、关闭游标。

十九、约束

约束：管理如何插入或处理数据库数据的规则。

主键：用来保证一列中的值是唯一的，表中的一列的值唯一标志表中的一行。

CREATE TABLE Vendors(vend_id CHAR(10) NOT NULL PRIMARY KEY,vend_name CHAR(20) NOT NULL);

另一中表示：

ALTER TABLE Vendors ADD CONSTRAINT PRIMARY KEY(vend_id);

</pre><p></p></blockquote></blockquote><blockquote style="margin:0 0 0 40px; border:none; padding:0px"><p></p><p>外键：保证引用完整性</p><pre name="code" class="sql">CREATE TABLE Orders(order_num INTEGER NOT NULL PRIMARY KEY,order_date DATETIME NOT NULL,cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id));

ALTER TABLE OrdersADD CONSTRAINT FOREIGN KEY(cust_id) REFERENCES Custoemrs(cust_id);

唯一约束：用来保证一列中的数据唯一。

Create table MyTable ( id varchar(32) not null,     name varchar (32),     unique (id,name) );

检查约束(CHECK)：用来保证一列中的数据满足一组指定的条件。

CREATE TABLE OrderItems(order_num INTEGER NOT NULL,order_item INTEGER NOT NULL,prod_id CHAR(10) NOT NULL,quantity INTEGER NOT NULL CHECK(quantity > 0));

二十、索引

可以在一个或多个列上定义索引，使DBMS保存其内容的一个排过序的列表。DBMS搜索排过序的索引，找出匹配的位置，然后检索这些行。

CREATE INDEX prod_name_indON Products (prod_name);

删除索引语句

ALTER TABLE tb_air_table DROP INDEX ux_union_index;

创建唯一索引语句

ALTER TABLE `tb_bonus_record` ADD UNIQUE INDEX `ux_union_index` (`order_id` ASC, `open_id` ASC, `user_id` ASC)  COMMENT '';

二十一、触发器

与触发器不一样(存储过程只是简单的存储SQL语句)，触发器与单个的表相关联。

触发器常见用途：

保证数据一致性。例如在INSERT操作中将所有州名转换为大写。

基于某个表的变动在其他表上执行活动。进行额外的验证并根据需要回退数据。

计算计算列的值或更新时间戳。

参考书目：《SQL必知必会》(美 Ben Forta著  第四版)

所用脚本下载自：http://www.forta.com/books/0672336073/