数据库常用sql语句汇总.docx
- 文档编号:26834685
- 上传时间:2023-06-23
- 格式:DOCX
- 页数:19
- 大小:20.64KB
数据库常用sql语句汇总.docx
《数据库常用sql语句汇总.docx》由会员分享,可在线阅读,更多相关《数据库常用sql语句汇总.docx(19页珍藏版)》请在冰豆网上搜索。
数据库常用sql语句汇总
数据库常用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%@'
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@'
WHEREcust_id=‘100000000000006';
#更新多个列
UPDATECustomers
SETcust_email=NUL
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 常用 sql 语句 汇总