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  --  检索具有子查询中列出的订单编号的所有顾客的ID
FROM 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_price
FROM 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_price
FROM 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_email
FROM customers
WHERE 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_email
FROM CustNew;

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

CREATE TABLE CustCopy AS
SELECT * FROM Customers;

十三、更新和删除操作

更新操作

更新所有行:

UPDATE Customers 
SET cust_email = 'kim@thetheorystore.com'
更新特定行

UPDATE Customers 
SET cust_email = 'kim@thetheorystore.com'
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 Vendors
ADD vend_phone CHAR(20);
给表删除列:
ALTER TAB LE Vendors
DROP 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_num
		FROM 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 Orders
ADD 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_ind
ON 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/




  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值