mysql.docx
《mysql.docx》由会员分享，可在线阅读，更多相关《mysql.docx（17页珍藏版）》请在冰点文库上搜索。
mysql
数据库
库和表的显示
showdatabases;
showtables;
showcreatetable[tablename];
showcolumnsfrommm.student;表的列项describemm.student;
showindexesfrommm.student\G;%%显示索引号
select*frommm.student;所有记录记录
每张表只能存在一个主键
primarykey并且常常与AUTO_INCREMENT一起使用
约束包括:
非空约束NOTNULL
主键..KEY
唯一..UINQUE
默认..
外键..FOREIGNKEY
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
FOREIGNKEY:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
编辑默认存储引擎:
default-storage-engine=INNODB
外键约束下的表，父表修改后，子表是否进行修改。
四种类型：
CASCADE:
从父表删除或更新且自动删除或更新子表中匹配的列；
SETNULL：
从父表删除或更新行，并设置子表中的外键列为NULL；
RESTRICT:
拒绝对父表的删除或更新操作；
NOACTION:
标准SQL的关键字，在MySQL中与RESTRICT相同。
格式如下：
CREATETABLEtablename（foreignkey（字段名）referencesfathertable（字段名）ondeletecascade/setnull/noaction/restrict）
例子：
CREATETABLEusers1（
idsmallintunsignedprimarykeyauto_increment,
usernamevarchar（10）notnull,
pidsamllintunsigned,
foreignkey（pid）references（id）ondeletecascade）;
showcreatetableusers1;
*******************************
针对字段的操作:
添加/删除字段、修改列定义、修改列名称等。
针对约束的操作:
添加/删除各种约束
针对数据表的操作:
数据表更名（两种方式）
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
约束的操作:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
;
altertabletable2addidsmallintunsigned;//添加列
altertabletable2dropid;//删除列
altertabletable2addprimarykey（id）;//添加主键约束
altertabletable2dropprimarykey;//删除主键约束因为有且仅有一个逐渐约束.
altertabletable2adduique（username）;//加唯一约束
altertabletable2dropuiquekeyusername;//删唯一约束
altertablesubtb2addforeignkey（pid）referencesprovince（id）;//添加外部约束
altertableroottb2dropforeignkeypid;//删除外部约束
altertablesubtb2dropindexesid;//删除子表的索引
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
修改数据表的操作:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
:
1.修改列定义
ALTERTABLEtbl_nameMODIFY[COLUMN]col_namecolumn_definition[FIRST|AFTERcol_name];
SHOWCOLUMNSFROMusers2;
2.修改列名称
ALTERTABLEtbl_nameCHANGE[COLUMN]col_namenew_col_namecolumn_definition[FIRST|AFTERcol_name];
3.数据表更名
方法1
ALTERTABLEtbl_nameRENAME[TO/AS]new_tbl_name
ALTERTABLEusers2RENAMETOusers3;
如下例子：
ALTERTABLEusers2MODIFYidSMALLINTUNSIGNEDNOTNULLFIRST;//将id字段的位置提到第一列
ALTERTABLEusers2MODIFYidTINYINTUNSIGNEDNOTNULL;//修改数据类型,需注意数据丢失的问题
ALTERTABLEusers2CHANGEpidp_idTINYINTUNSIGNED;//修改列名称
ALTERTABLEusers2RENAMETOusers3;
1.插入记录
INSERT[INTO]tbl_name[（col_name,...）]{VALUES|VALUE}（{expr|DEFAULT},...）,（...）,...
USEtest;
inserttb3values（1,'Jonn'）（2,'Mark'）;
inserttb3（user_name）values（'Jonn'）;
CREATETABLEusers（
idSMALLINTUNSIGNEDPRIMARYKEYAUTO_INCREMENT,
usernameVARCHAR（20）NOTNULL,
passwordVARCHAR（32）NOTNULL,
ageTINYINTUNSIGNEDNOTNULLDEFAULT10,
sexBOOLEAN
）;
INSERTusersVALUES（NULL,'TOM','123',25,1）;//主键的赋值
INSERTusersVALUES（NULL,'John','456',25,1）;
INSERTusersVALUES（DEFAULT,'TOM','123',25,1）;
SELECT*FROMusers;
同时加入多条记录：
INSERTusersVALUES（NULL,'TOM','123',25,1）,（NULL,'ROSE',md5（'123'）,DEFAULT,0）;//md5哈希算法
SELECT*FROMusers;
2.更新记录
更新记录UPDATE，有两种方式，单表和多表
单表更新：
UPDATE[LOW_PRIORITY][IGNORE]table_referenceSETcol_name1={exp1|DEFAULT}[,col_name2=...][WHEREwhere_condition]【一般来说要用WHERE指定位置，不然所有数据都会被更新】
UPDATEuserssetage=age+5,sex=0;使表里所有记录的age加5
UPDATEuserssetage=age++10WHEREid%2=0;取id为偶数的位置
deletefromuserswhereid=6;
insertusersvalues（null,'111','222',33,null）;删除后再插入，插入的id号从最大的往上加，而不是填补删除的。
***************查询表达式---select占数据库的比列很大*************************
SELECTVERSION（）;
SELECTNOW（）;
SELECT3+5;
SELECTid,usernameFROMusers;//查询表达式中字段的顺序可以和表中的字段顺序不一致
SELECTusername,idFROMusers;
SELECTusers.id,users.usernameFROMusers;//可以区分来自不同表中的字段查询
SELECTidASuserId,usernameASunameFROMusers;//AS可以使用也可以不使用，建议使用
SELECTidusernameFROMusers;//只有一个字段显示
SELECT*FROMusersGROUPBY1;//默认自增
SELECT*FROMusersORDERBY1;
限制查询结果返回的数量
select*fromuserslimit2
select*fromuserslimit3,2
select*fromusersorderbyidlimit3,2;
inserttest（username）selectusernamefromuserswhereage>15
SELECTsex,ageFROMusersGROUPBY1HAVINGage>35;
SELECTsexFROMusersGROUPBY1HAVINGcount（id）>=2;
1.子查询是指在另一个查询语句中的SELECT子句。
例句：
SELECT*FROMt1WHEREcolumn1=（SELECTcolumn1FROMt2）;
其中，SELECT*FROMt1...称为OuterQuery[外查询]（或者OuterStatement）,
SELECTcolumn1FROMt2称为SubQuery[子查询]。
所以，我们说子查询是嵌套在外查询内部。
而事实上它有可能在子查询内部再嵌套子查询。
子查询必须出现在圆括号之间。
行级子查询
SELECT*FROMt1WHERE（col1,col2）=（SELECTcol3,col4FROMt2WHEREid=10）;
SELECT*FROMt1WHEREROW（col1,col2）=（SELECTcol3,col4FROMt2WHEREid=10）;
行级子查询的返回结果最多为一行。
优化子查询
--创建数据表
CREATETABLEIFNOTEXISTStdb_goods（
goods_idSMALLINTUNSIGNEDPRIMARYKEYAUTO_INCREMENT,
goods_nameVARCHAR（150）NOTNULL,
goods_cateVARCHAR（40）NOTNULL,
brand_nameVARCHAR（40）NOTNULL,
goods_priceDECIMAL（15,3）UNSIGNEDNOTNULLDEFAULT0,
is_showBOOLEANNOTNULLDEFAULT1,
is_saleoffBOOLEANNOTNULLDEFAULT0
）;
--写入记录
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'R510VC15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'Y400N14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'G150TH15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'X550CC15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'X240（20ALA0EYCD）12.5英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'U330P13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'SVP13226SCB13.3英寸触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'iPadminiMD531CH/A7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'iPadAirMD788CH/A9.7英寸平板电脑（16GWiFi版）','平板电脑','苹果','3388',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'iPadminiME279CH/A配备Retina显示屏7.9英寸平板电脑（16GWiFi版）','平板电脑','苹果','2788',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'IdeaCentreC34020英寸一体电脑','台式机','联想','3499',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'Vostro3800-R1206台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'iMacME086CH/A21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'AT7-7414LP台式电脑（i5-3450四核4G500G2G独显DVD键鼠Linux）','台式机','宏碁','3699',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'Z220SFFF4F06PA工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'PowerEdgeT110II服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'MacProMD878CH/A专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'HMZ-T3W头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'X3250M4机架式服务器2583i14','服务器/工作站','IBM','6888',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'玄龙精英版笔记本散热器','笔记本配件','九州风神','',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'HMZ-T3W头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT）;
INSERTtdb_goods（goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff）VALUES（'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT）;
--求所有电脑产品的平均价格,并且保留两位小数，AVG,MAX,MIN、COUNT、SUM为聚合函数
SELECTROUND（AVG（goods_price）,2）ASavg_priceFROMtdb_goods;
--查询所有价格大于平均价格的商品，并且按价格降序排序
SELECTgoods_id,goods_name,goods_priceFROMtdb_goodsWHEREgoods_price>5845.10ORDERBYgoods_priceDESC;
--使用子查询来实现
SELECTgoods_id,goods_name,goods_priceFROMtdb_goodsWHEREgoods_price>（SELECTROUND（AVG（goods_price）,2）ASavg_priceFROMtdb_goods）ORDERBYgoods_priceDESC;
--查询类型为“超记本”的商品价格
SELECTgoods_priceFROMtdb_goodsWHEREgoods_cate='超级本';
--查询价格大于或等于"超级本"价格的商品，并且按价格降序排列
SELECTgoods_id,goods_name,goods_priceFROMtdb_goodsWHEREgoods_price=ANY（SELECTgoods_priceFROMtdb_goodsWHEREgoods_cate='超级本'）ORDERBYgoods_priceDESC;
--=ANY或=SOME等价于IN
SELECTgoods_id,goods_name,goods_priceFROMtdb_goodsWHEREgoods_priceIN（SELECTgoods_priceFROMtdb_goodsWHEREgoods_cate='超级本'）ORDERBYgoods_priceDESC;
--创建“商品分类”表
CREATETABLEIFNOTEXISTStdb_goods_cates（
cate_idSMALLINTUNSIGNEDPRIMARYKEYAUTO_INCREMENT,
cate_nameVARCHAR（40）
）;
--查询tdb_goods表的所有记录，并且按"类别"分组
SELECTgoods_cateFROMtdb_goodsGROUPBYgoods_cate;
--将分组结果写入到tdb_goods_cates数据表
INSERTtdb_goods_cates（cate_name）SELECTgoods_cateFROMtdb_goodsGROUPBYgoods_cate;
--通过tdb_goods_cates数据表来更新tdb_goods表
UPDATEtdb_goodsINNERJOINtdb_goods_catesONgoods_cate=cate_nameSETgoods_cate=cate_id;
--通过CREATE...SELECT来创建数据表并且同时写入记录
--SELECTbrand_nameFROMtdb_goodsGROUPBY
升级会员 