数据库常用的sql语句汇总
SQL是目前使用最为广泛的数据库语言之一。这里，我总结了在数据库上，用SQL语言对数据排序、过滤和分组，以及表、视图、联结、子查询、游标、存储过程和触发器等内容。
数据库相关
查所有数据库showdatabases;
创建数据库createdatabase数据库名;
查看数据库showcreatedatabase数据库名;//显示当初创建这个库的时候使用什么样的sql语句
创建数据库指定字符集createdatabase数据库名charactersetutf8/gbk
删除数据库dropdatabase数据库名;
使用数据库use数据库名;
表相关
创建表createtable表名(idint,namevarchar(10));//表名区分大小写
查看所有表showtables;
查看单个表属性showcreatetable表名;//使用的什么创建语句，可以在后面加\G使描述更清晰
查看表字段desc表名;
创建表指定引擎和字符集createtable表名(idint,namevarchar(10))engine=myisam/innodbcharset=utf8/gbk;
删除表droptable[ifexists]表名;删除表（可选择添加是否存在则删除）
DROPTABLEIFEXISTS`abc`;
CREATETABLE`abc`(
`id`mediumint(8)unsignedNOTNULLAUTO_INCREMENTcomment'商品名称',
`name`char(80)NOTNULLDEFAULT''comment'商品名称',
`title`char(20)NOTNULLDEFAULT''comment'商品名称',
`type`tinyint(1)NOTNULLDEFAULT'1'comment'商品名称',
`condition`char(100)NOTNULLDEFAULT''comment'商品名称',
`show`bitDEFAULT1comment'是否可见',
`price`decimal(5,2)notnullcomment'价格',
`status`enum('0','1','2')NOTNULLDEFAULT'0'comment'状态',
PRIMARYKEY(`id`),
UNIQUEKEY`name`(`name`)
)ENGINE=INNODBDEFAULTCHARSET=utf8;
建立数据库：
CREATEDATABASEIFNOTEXISTSmy_dbdefaultcharsetutf8COLLATEutf8_general_ci;
约束
notnull非空
default默认约束语句，用于约束对应列中的值的默认值，除非默认值为空值，否则不可插入空值
unique唯一约束语句，用于约束对应列中的值不能重复，可以有空值，但只能出现一个空值
primary主键=唯一+非空
auto_increment自动增长，用于系统自动生成字段的主键值
foreignkey(从表id)reference主表名（id）；表与表之间建立联系
修改表
修改表名renametable旧表名to新表名;
修改表名altertable旧表名rename新表名
修改字段数据类型altertable表名modify字段名数据类型
修改表属性altertable表名engine=myisam/innodbcharset=utf8/gbk;
添加表字段altertable表名add新字段名新数据类型[约束][first/after已存在字段名];
删除表字段altertable表名drop字段名;
修改表字段名和类型altertable表名change旧字段名新字段名类型;
修改表的类型和位置altertable表名modify字段名类型first/after已存在字段名;
删除表droptable表名;
更改表的存储引擎altertable表名engine=新的存储引擎;
删除表的外键约束altertable表名dropforeignkey外键名;//删除所有的外键之后，才能删除对应的主键所在的表
数据相关
插入数据：
insertinto表名values(5,‘xiaoming',null);
insertinto表名(字段名1,字段名2…)values(2,‘aa'…);
insertinto表名values(5,‘xiaoming',null),(5,‘xiaoming',null),(5,‘xiaoming',null);
insertinto表名(字段名1,字段名2)values(2,‘aa'),(2,‘aa'),(2,‘aa');
查询
select*from表名;
selectnamefrom表名;
select*from表名whereid=10;
修改
update表名set要修改的字段名=100where根据字段名=10;
删除
deletefrom表名where字段名=10;
下面是补充
1.检索数据
SELECTprod_nameFROMProducts; #检索单列 SELECTprod_id,prod_name,prod_priceFROMProducts; #检索多列 SELECT*FROMProducts; #检索所有列 SELECTDISTINCTvend_idFROMProducts; #检索不同的值 SELECTprod_nameFROMProductsLIMIT5; #返回不超过5行数据 SELECTprod_nameFROMProductsLIMIT5OFFSET5; #返回从第5行起的5行数据。LIMIT指定返回的行数，LIMIT带的OFFSET指定从哪儿开始。 /*SELECTprod_name,vend_id FROMProducts;*/ SELECTprod_name FROMProducts; #多行注释
2.排序检索数据
SELECTprod_name FROMProducts ORDERBYprod_name; #排序数据 SELECTprod_id,prod_price,prod_name FROMProducts ORDERBYprod_price,prod_name; #按多个列排序 SELECTprod_id,prod_price,prod_name FROMProducts ORDERBY2,3; #按列位置排序，第三行表示先按prod_price,再按prod_name进行排序 SELECTprod_id,prod_price,prod_name FROMProducts ORDERBYprod_priceDESC,prod_name; #prod_price列以降序排序，而prod_name列（在每个价格内）仍然按标准的升序排序
3.过滤数据
SELECTprod_name,prod_price FROMProducts WHEREprod_price<10; #检查单个值 SELECTprod_name,prod_price FROMProducts WHEREvend_id<>‘DLL01'; #不匹配检查 SELECTprod_name,prod_price FROMProducts WHEREprod_priceBETWEEN5AND10; #范围值检查 SELECTcust_name FROMCUSTOMERS WHEREcust_emailISNULL; #空值检查
4.高级数据过滤
SELECTprod_id,prod_price,prod_name FROMProducts WHEREvend_id=‘DLL01'ANDprod_price<=4; #AND操作符 SELECTprod_name,prod_price FROMProducts WHEREvend_id='DLL01'ORvend_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 WHEREvend_idIN(‘DLL01','BRS01') ORDERBYprod_name; #IN操作符 SELECTprod_name FROMProducts WHERENOTvend_id=‘DLL01' ORDERBYprod_name; #NOT操作符 SELECTprod_name FROMProducts WHEREvend_id<>‘DLL01' ORDERBYprod_name; #NOT操作符
5.通配符进行过滤
SELECTprod_id,prod_name FROMProducts WHEREprod_nameLIKE‘Fish%'; #%表示任何字符出现任意次数，找出所有以词Fish起头的产品 SELECTprod_id,prod_name FROMProducts WHEREprod_nameLIKE‘%beanbag%'; #‘%beanbag%'表示匹配任何位置上包含文本beanbag的值，不论它在之前或之后出现什么字符 SELECTprod_name FROMProducts WHEREprod_nameLIKE‘F%y'; #找出以F起头，以y结尾的所有产品
根据邮件地址的一部分来查找电子邮件，例如WHEREemailLIKE‘b%@forta.com'
WHEREprod_nameLIKE‘%';#不会匹配产品名称为NULL的行，其它均可
%代表搜索模式中给定位置的0个、1个或多个字符
下划线的用途与%一样，但它只匹配单个字符，而不是多个字符
SELECTprod_id,prod_name FROMProducts WHEREprod_nameLIKE‘__inchteddybear'; #搜索模式要求匹配两个通配符而不是一个
方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符
SELECTcust_contact FROMCustomers WHEREcust_contactLIKE‘[JM]%' ORDERBYcust_contact;
#[JM]匹配方括号中任意一个字符，它也只能匹配单个字符，任何多于一个字符的名字都不匹配。[JM]之后的%通配符匹配第一个字符之后的任意数目的字符，返回所需结果。
SELECTcust_contact FROMCustomers WHEREcust_contactLIKE‘[^JM]%' ORDERBYcust_contact; #以J和M之外的任意字符起头的任意联系人名
6.创建计算字段
SELECTConcat(vend_name,‘(‘,vend_country,‘)') FROMVendors ORDERBYvend_name; 输出 BearEmporium(USA) BearsRUs(USA) DollHouseInc.(USA) FunandGames(England) SELECTConcat(vend_name,‘(‘,vend_country,‘)') ASvend_title FROMVendors ORDERBYvend_name;#给拼接而成新字段起了一个名称 SELECTprod_id, quantity, item_price, quantity*item_priceASexpanded_price FROMOrderItems WHEREorder_num=20008; #汇总物品的价格
7.使用函数处理数据
SELECTvend_name,UPPER(vend_name)ASvend_name_upcase FROMVendors ORDERBYvend_name; #文本处理函数 SELECTcust_name,cust_contact FROMCustomers WHERESOUNDEX(cust_contact)=SOUNDEX(‘MichaelGreen'); #SOUNDEX()函数搜索，匹配所有发音类似于MichaelGreen的联系名 SELECTorder_num FROMOrders WHEREYEAR(order_date)=2012; #从日期中提取年份
8.数据汇总
SELECTAVG(prod_price)ASavg_price FROMProducts; WHEREvend_id=‘DLL01'; SELECTCOUNT(*)ASnum_cust FROMCustomers; #COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值 SELECTCOUNT(cust_email)ASnum_cust FROMCustomers; #只对具有电子邮件地址的客户计数 SELECTMAX(prod_price)ASmax_price FROMProducts; #返回Products表中最贵物品的价格 SELECTMIN(prod_price)ASmin_price FROMProducts; #返回Products表中最便宜物品的价格 SELECTSUM(quantity)ASitems_ordered FROMOrderItems WHEREorder_num=20005; #SUM(quantity)返回订单中所有物品数量之和，WHERE子句保证只统计某个物品订单中的物品 SELECTSUM(item_price*quantity)AStotal_price FROMOrderItems WHEREorder_num=20005; #SUM(item_price*quantity)返回订单中所有物品价钱之和，WHERE子句保证只统计某个物品订单中的物品 SELECTAVG(DISTINCTprod_price)ASavg_price FROMProducts WHEREvend_id=‘DLL01'; #使用DISTINCT参数，平均值只考虑各个不同的价格 SELECTCOUNT(*)ASnum_items, MIN(prod_price)ASprice_min, MAX(prod_price)ASprice_max, AVG(prod_price)ASprice_avg FROMProducts; #组合聚集函数
9.分组数据
SELECTvend_id,COUNT(*)ASnum_prods FROMProducts GROUPBYvend_id; #创建分组 SELECTvend_id,COUNT(*)ASnum_prods FROMProducts WHEREprod_price>=4 GROUPBYvend_id HAVINGCOUNT(*)>=2; #WHERE子句过滤所有prod_price至少为4的行，然后按vend_id分组数据，HAVING子句过滤计数为2或2以上的分组。 SELECTorder_num,COUNT(*)ASitems FROMOrderItems GROUPBYorder_num HAVINGCOUNT(*)>=3 ORDERBYitems,order_num; #按订购物品的数目排序输出
10.使用子查询
SELECTcust_id FROMOrders WHEREorder_numIN(SELECTorder_num FROMOrderItems WHEREprod_id=‘RGAN01'); SELECTcust_name,cust_contact FROMCustomers WHEREcust_idIN(‘10000000004',‘10000000005');
11.联结表
SELECTvend_name,prod_name,prod_price FROMVendors,Products WHEREVendorsvend_id=Products.vend_id; #创建联结 SELECTvend_name,prod_name,prod_price FROMVendorsINNERJOINProducts ONVendors.vend_id=Products.vend_id; #内联结 SELECTprod_name,vend_name,prod_price,quantity FROMOrderItems,Products,Vendors WHEREProducts.vend_id=Vendors.vend_id ANDOrderItems.prod_id=Products.prod_id ANDorder_num=20007; #联结多个表
12.创建高级联结
SELECTc1.cust_id,c1.cust_name,c1.cust_contact FROMCustomersASc1,CustomersASc2 WHEREc1.cust_name=c2.cust_name ANDc2.cust_contact=‘JimJones'; #自联结，此查询中需要的两个表实际上是相同的表 SELECTC.*,O.order_num,O.order_date, OI.prod_id,OI.quantity,OI.item_price FROMCustomersASC,OrdersASO,OrderItemsASOI WHEREC.cust_id=O.cust_id ANDOI.order_num=O.order_num ANDprod_id=‘RGAN01'; #自然联结排除多次出现，使每一列只返回一次 SELECTCustomers.cust_id,Orders.order_num FROMCustomersLEFTOUTERJOINOrders ONCustomers.cust_id=Orders.cust_id; #从FROM子句左边的表Customers表中选择所有行 SELECTCustomers.cust_id,Orders.order_num FROMCustomersRIGHTOUTERJOINOrders ONOrders.cust_id=Customers.cust_id; #从右边的表中选择所有行。 SELECTCustomers.cust_id,Orders.order_num FROMOrdersFULLOUTERJOINCustomers ONOrders.cust_id=Customers.cust_id; #检索两个表中的所有行并关联那些可以关联的行
13.组合查询
SELECTcust_name,cust_contact,cust_email FROMCustomers WHEREcust_stateIN(‘IL',‘IN',‘MI') UNION SELECTcust_name,cust_contact,cust_email FROMCustomers WHEREcust_name=‘Fun4ALL' ORDERBYcust_name,cust_contact; #SQL允许执行多个查询，并将结果作为一个查询结果集返回
14.插入数据
INSERTINTOCustomers(cust_id, Cust_name, Cust_address, Cust_city, Cust_state, Cust_zip, Cust_country, Cust_contact, Cust_email) VALUES(‘100000000006', ‘ToyLand', ‘123AnyStreet', ‘NewYork', ‘NY', ‘111111', ‘USA', NULL, NULL); #插入完整的行 INSERTINTOCustomers(cust_id, Cust_contact, Cust_email, Cust_name, Cust_address, Cust_city, Cust_state, Cust_zip, Cust_country) SELECTcust_id, Cust_contact, Cust_email, Cust_name, Cust_address, Cust_city, Cust_state, Cust_zip, Cust_country FROMCustNew； #将另一个表中的顾客列合并到Customers表中。 SELECT* INTOCustCopy FROMCustomers; #从一个表复制到另一个表中
15.更新和删除数据
UPDATECustomers SETcust_contact=‘SamRoberts', Cust_email=‘sam@toyland.com' WHEREcust_id=‘100000000000006'; #更新多个列 UPDATECustomers SETcust_email=NULL WHEREcust_id=‘1000000005'; #删除某个列 DELETEFROMCustomers WHEREcust_id=‘1000000006'; #删除数据
16.创建和操纵表
CREATETABLEOrderItems ( Order_numINTEGERNOTNULL, Order_itemINTEGERNOTNULL, Prod_idCHAR(10)NOTNULL, QuantityINTEGERNOTNULLDEFAULT1, Item_priceDECIMAL(8,2)NOTNULL ); ALTERTABLEVendors ADDvend_phoneCHAR(20); #给表增加一个名为vend_phone的列，其数据类型为CHAR ALTERTABLEVendors DROPCOLUMNvend_phone; #该表中的某列 DROPTABLECustCopy; #删除表
17.高级SQL特性
主键：表中一列（或多个列）的值唯一标识表中的每一行。主键是一种特殊的约束，用来保证一列或一组列的值唯一标识表中的每一行。这方便直接或交互地处理表中的行。没有主键，要安全地UPDATE或DELETE特定行而不影响其他行会非常困难。
①任意两行的主键值都不相同；
②每行都具有一个主键值（即列中不允许NULL值）
③包含主键值的列从不修改或更新。
④主键值不能重用
CREATETABLEVendors ( Vend_idCHAR(10)NOTNULLPRIMARYKEY, Vend_nameCHAR(50)NOTNULL, Vend_addressCHAR(50)NULL, Vend_cityCHAR(5)NULL, Vend_stateCHAR(10)NULL, Vend_zipCHAR(10)NULL, Vend_countryCHAR(50)NULL ); ALTERTABLEVendors ADDCONSTRAINTPRIMARYKEY(vend_id);
#给表vend_id列定义添加关键字PRIMARYKEY,使其成为主键