mysql数据库sql语句_数据库常用sql语句汇总 - 全文

对于数据库服务器，现在市面上流行的有很多，个人使用过的有Oracle、Mysql和DB2，个人最喜欢的就是Mysql，开源界神一样的作品之一，虽然，被Oracle收购之后更新速度明显慢了下来，但是依然被市场广泛使用(黑一下甲骨文)。

SQL是目前使用最为广泛的数据库语言之一。这里，我总结了在数据库上，用SQL语言对数据排序、过滤和分组，以及表、视图、联结、子查询、游标、存储过程和触发器等内容。

1.检索数据

SELECT prod_nameFROM Products;

#检索单列

SELECT prod_id， prod_name， prod_priceFROMProducts;

#检索多列

SELECT * FROM Products;

#检索所有列

SELECT DISTINCTvend_id FROMProducts;

#检索不同的值

SELECTprod_name FROM Products LIMIT 5;

#返回不超过5行数据

SELECTprod_name FROM Products LIMIT 5 OFFSET 5;

#返回从第5行起的5行数据。LIMIT指定返回的行数，LIMIT带的OFFSET指定从哪儿开始。

/* SELECT prod_name， vend_id

FROMProducts; */

SELECTprod_name

FROMProducts;

#多行注释

2.排序检索数据

SELECTprod_name

FROMProducts

ORDER BYprod_name;

#排序数据

SELECT prod_id， prod_price， prod_name

FROMProducts

ORDER BY prod_price， prod_name;

#按多个列排序

SELECT prod_id， prod_price， prod_name

FROMProducts

ORDER BY 2， 3;

#按列位置排序，第三行表示先按prod_price， 再按prod_name进行排序

SELECT prod_id， prod_price， prod_name

FROMProducts

ORDER BY prod_priceDESC， prod_name;

#prod_price列以降序排序，而prod_name列(在每个价格内)仍然按标准的升序排序

3.过滤数据

SELECT prod_name， prod_price

FROMProducts

WHERE prod_price《 10;

#检查单个值

SELECT prod_name， prod_price

FROMProducts

WHERE vend_id 《》 ‘DLL01’;

#不匹配检查

SELECT prod_name， prod_price

FROMProducts

WHERE prod_priceBETWEEN 5 AND 10;

#范围值检查

SELECT cust_name

FROMCUSTOMERS

WHERE cust_emailIS NULL;

#空值检查

4.高级数据过滤

SELECTprod_id， prod_price， prod_name

FROMProducts

WHERE vend_id = ‘DLL01’ANDprod_price 《= 4;

#AND操作符

SELECTprod_name， prod_price

FROMProducts

WHEREvend_id=’DLL01’ OR vend_id=’BRS01’;

#OR操作符

SELECTprod_name， prod_price

FROMProducts

WHERE (vend_id = ’DLL01’ORvend_id=’BRS01’)

ANDprod_price 》= 10；

#求值顺序 AND的优先级高于OR

SELECTprod_name， prod_price

FROMProducts

WHERE vend_idIN (‘DLL01’，’BRS01’)

ORDER BY prod_name;

#IN操作符

SELECT prod_name

FROMProducts

WHERE NOTvend_id = ‘DLL01’

ORDER BY prod_name;

#NOT 操作符

SELECT prod_name

FROMProducts

WHEREvend_id 《》 ‘DLL01’

ORDER BY prod_name;

#NOT 操作符

5.通配符进行过滤

SELECT prod_id， prod_name

FROMProducts

WHERE prod_nameLIKE ‘Fish%’;

#%表示任何字符出现任意次数，找出所有以词Fish起头的产品

SELECT prod_id， prod_name

FROMProducts

WHERE prod_nameLIKE ‘%bean bag%’;

#‘%bean bag%’表示匹配任何位置上包含文本bean bag的值，不论它在之前或之后出现什么字符

SELECT prod_name

FROMProducts

WHERE prod_nameLIKE ‘F%y’;

#找出以F起头，以y结尾的所有产品

根据邮件地址的一部分来查找电子邮件，例如WHERE email LIKE ‘b%@forta.com’

WHERE prod_nameLIKE ‘%’; #不会匹配产品名称为NULL的行，其它均可

%代表搜索模式中给定位置的0个、1个或多个字符

下划线的用途与%一样，但它只匹配单个字符，而不是多个字符

SELECT prod_id， prod_name

FROMProducts

WHERE prod_nameLIKE ‘__inchteddy bear’;

#搜索模式要求匹配两个通配符而不是一个

方括号(［］)通配符用来指定一个字符集，它必须匹配指定位置(通配符的位置)的一个字符

SELECT cust_contact

FROMCustomers

WHERE cust_contactLIKE ‘［JM］%’

ORDER BY cust_contact;

#［JM］匹配方括号中任意一个字符，它也只能匹配单个字符，任何多于一个字符的名字都不匹配。［JM］之后的%通配符匹配第一个字符之后的任意数目的字符，返回所需结果。

SELECT cust_contact

FROMCustomers

WHERE cust_contactLIKE ‘［^JM］%’

ORDER BY cust_contact;

#以J和M之外的任意字符起头的任意联系人名

6.创建计算字段

SELECT Concat(vend_name， ‘ (‘， vend_country， ‘)’)

FROMVendors

ORDER BY vend_name;

输出

Bear Emporium(USA)

Bears R Us (USA)

Doll House Inc.(USA)

Fun and Games(England)

SELECT Concat(vend_name， ‘ (‘， vend_country， ‘)’)

ASvend_TItle

FROMVendors

ORDER BY vend_name; #给拼接而成新字段起了一个名称

SELECT prod_id，

quanTIty，

item_price，

quanTIty*item_price AS expanded_price

FROMOrderItems

WHERE order_num = 20008;

#汇总物品的价格

7.使用函数处理数据

SELECT vend_name， UPPER(vend_name)AS vend_name_upcase

FROMVendors

ORDER BY vend_name;

#文本处理函数

SELECT cust_name， cust_contact

FROMCustomers

WHERE SOUNDEX(cust_contact) =SOUNDEX(‘MichaelGreen’);

# SOUNDEX()函数搜索，匹配所有发音类似于Michael Green 的联系名

SELECT order_num

FROMOrders

WHERE YEAR(order_date) = 2012;

#从日期中提取年份

8.数据汇总

SELECT AVG(prod_price)ASavg_price

FROMProducts;

WHERE vend_id = ‘DLL01’;

SELECT COUNT(*)ASnum_cust

FROMCustomers;

#COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值(NULL)还是非空值

SELECT COUNT(cust_email)ASnum_cust

FROMCustomers;

#只对具有电子邮件地址的客户计数

SELECT MAX(prod_price)ASmax_price

FROMProducts;

#返回Products表中最贵物品的价格

SELECT MIN(prod_price)ASmin_price

FROMProducts;

#返回Products表中最便宜物品的价格

SELECT SUM(quantity)ASitems_ordered

FROMOrderItems

WHERE order_num = 20005;

#SUM(quantity)返回订单中所有物品数量之和，WHERE 子句保证只统计某个物品订单中的物品

SELECT SUM(item_price*quantity)AS total_price

FROMOrderItems

WHERE order_num = 20005;

#SUM(item_price*quantity)返回订单中所有物品价钱之和，WHERE子句保证只统计某个物品订单中的物品

SELECT AVG(DISTINCTprod_price)AS avg_price

FROMProducts

WHERE vend_id = ‘DLL01’;

#使用DISTINCT参数，平均值只考虑各个不同的价格

SELECT COUNT(*) AS num_items，

MIN(prod_price)AS price_min，

MAX(prod_price)AS price_max，

AVG(prod_price)AS price_avg

FROMProducts;

#组合聚集函数

9.分组数据

SELECT vend_id，COUNT(*) AS num_prods

FROMProducts

GROUP BY vend_id;

#创建分组

SELECT vend_id，COUNT(*) AS num_prods

FROMProducts

WHERE prod_price 》= 4

GROUP BY vend_id

HAVING COUNT(*) 》= 2;

#WHERE 子句过滤所有prod_price至少为4的行，然后按vend_id分组数据，HAVING子句过滤计数为2或2以上的分组。

SELECT order_num，COUNT(*) AS items

FROMOrderItems

GROUP BY order_num

HAVING COUNT(*) 》= 3

ORDER BY items， order_num;

#按订购物品的数目排序输出

10.使用子查询

SELECT cust_id

FROMOrders

WHERE order_numIN (SELECT order_num

FROM OrderItems

WHERE prod_id = ‘RGAN01’);

SELECT cust_name， cust_contact

FROMCustomers

WHERE cust_idIN (‘10000000004’， ‘10000000005’);

11.联结表

SELECT vend_name， prod_name， prod_price

FROMVendors， Products

WHERE Vendors vend_id = Products.vend_id;

#创建联结

SELECT vend_name， prod_name， prod_price

FROMVendorsINNER JOIN Products

ONVendors.vend_id = Products.vend_id;

#内联结

SELECT prod_name， vend_name， prod_price， quantity

FROMOrderItems， Products， Vendors

WHERE Products.vend_id = Vendors.vend_id

ANDOrderItems.prod_id = Products.prod_id

ANDorder_num = 20007;

#联结多个表

12.创建高级联结

SELECT c1.cust_id， c1.cust_name， c1.cust_contact

FROMCustomersAS c1， Customers AS c2

WHERE c1.cust_name = c2.cust_name

ANDc2.cust_contact = ‘Jim Jones’;

#自联结，此查询中需要的两个表实际上是相同的表

SELECT C. *， O.order_num， O.order_date，

OI.prod_id， OI.quantity， OI.item_price

FROMCustomersAS C， Orders AS O， OrderItems AS OI

WHERE C.cust_id = O.cust_id

ANDOI.order_num = O.order_num

ANDprod_id = ‘RGAN01’;

#自然联结排除多次出现，使每一列只返回一次

SELECT Customers.cust_id， Orders.order_num

FROMCustomersLEFT OUTER JOIN Orders

ONCustomers.cust_id = Orders.cust_id;

#从FROM子句左边的表Customers表中选择所有行

SELECT Customers.cust_id， Orders.order_num

FROMCustomersRIGHT OUTER JOIN Orders

ONOrders.cust_id =Customers.cust_id;

#从右边的表中选择所有行。

SELECT Customers.cust_id， Orders.order_num

FROMOrdersFULL OUTER JOIN Customers

ONOrders.cust_id = Customers.cust_id;

#检索两个表中的所有行并关联那些可以关联的行

13.组合查询

SELECT cust_name， cust_contact， cust_email

FROMCustomers

WHERE cust_state IN (‘IL’， ‘IN’， ‘MI’)

UNION

SELECT cust_name， cust_contact， cust_email

FROMCustomers

WHERE cust_name = ‘Fun4ALL’

ORDER BY cust_name， cust_contact;

#SQL允许执行多个查询，并将结果作为一个查询结果集返回

14.插入数据

INSERT INTO Customers(cust_id，

Cust_name，

Cust_address，

Cust_city，

Cust_state，

Cust_zip，

Cust_country，

Cust_contact，

Cust_email)

VALUES(‘100000000006’，

‘Toy Land’，

‘123 Any Street’，

‘New York’，

‘NY’，

‘111111’，

‘USA’，

NULL，

NULL);

#插入完整的行

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

FROMCustNew；

#将另一个表中的顾客列合并到Customers表中。

SELECT *

INTOCustCopy

FROMCustomers;

#从一个表复制到另一个表中

15.更新和删除数据

UPDATE Customers

SETcust_contact = ‘Sam Roberts’，

Cust_email = ‘sam@toyland.com’

WHERE cust_id = ‘100000000000006’;

#更新多个列

UPDATE Customers

SETcust_email = NULL

WHERE cust_id = ‘1000000005’;

#删除某个列

DELETE FROM Customers

WHERE cust_id = ‘1000000006’;

#删除数据

16. 创建和操纵表

CREATE TABLE OrderItems

(

Order_num INTEGER NOT NULL，

Order_item INTEGER NOT NULL，

Prod_id CHAR(10) NOT NULL，

Quantity INTEGER NOT NULL DEFAULT 1，

Item_price DECIMAL(8， 2) NOT NULL

);

ALTER TABLE Vendors

ADDvend_phone CHAR(20);

#给表增加一个名为vend_phone的列，其数据类型为CHAR

ALTER TABLE Vendors

DROP COLUMN vend_phone;

#该表中的某列

DROP TABLE CustCopy;

#删除表

17.高级SQL特性

主键：表中一列(或多个列)的值唯一标识表中的每一行。主键是一种特殊的约束，用来保证一列或一组列的值唯一标识表中的每一行。这方便直接或交互地处理表中的行。没有主键，要安全地UPDATE 或DELETE特定行而不影响其他行会非常困难。

①任意两行的主键值都不相同；

②每行都具有一个主键值(即列中不允许NULL值)

③包含主键值的列从不修改或更新。

④主键值不能重用

CREATE TABLE Vendors

(

Vend_id CHAR(10) NOT NULL PRIMARYKEY，

Vend_name CHAR(50) NOT NULL，

Vend_address CHAR(50) NULL，

Vend_city CHAR(5) NULL，

Vend_state CHAR(10) NULL，

Vend_zip CHAR(10) NULL，

Vend_country CHAR(50) NULL

);

ALTER TABLE Vendors

ADD CONSTRAINT PRIMARY KEY (vend_id);

#给表vend_id 列定义添加关键字PRIMARYKEY， 使其成为主键

其实一般用的就是查询，插入，删除等语句而已。。。。但学学存储过程是好事。。。以后数据方面的东西就不用在程序里搞喽。。而且程序与数据库只要一个来回通讯就可以搞定所有数据的操作。。。.