mysql复习笔记-by燕十八

mysql复习一:复习前的准备1:确认你已安装wamp2:确认你已安装ecshop,并且ecshop的数据库名为shop二基础知识:1.数据库的连接mysql-u-p-h-u用户名-p密码-hhost主机2:库级知识2.1显示数据库:showdatabases;2.2选择数据库:usedbname;2.3创建数据库:createdatabasedbnamecharsetutf8;2.3删除数据库:dropdatabasedbname;3:表级操作:3.1显示库下面的表showtables;3.2查看表的结构:desctableName;3.3查看表的创建过程:showcreatetabletableName;3.4创建表:createtabletbName(列名称1列类型[列参数][notnulldefault],....列2.......列名称N列类型[列参数][notnulldefault])enginemyisam/innodbcharsetutf8/gbk3.4的例子:createtableuser(idintauto_increment,namevarchar(20)notnulldefault'',agetinyintunsignednotnulldefault0,indexid(id))engine=innodbcharset=utf8;注:innodb是表引擎,也可以是myisam或其他,但最常用的是myisam和innodb,charset常用的有utf8,gbk;3.5修改表3.5.1修改表之增加列:altertabletbNameadd列名称１列类型[列参数][notnulldefault]#(add之后的旧列名之后的语法和创建表时的列声明一样)3.5.2修改表之修改列altertabletbNamechange旧列名新列名列类型[列参数][notnulldefault](注:旧列名之后的语法和创建表时的列声明一样)3.5.3修改表之减少列:altertabletbNamedrop列名称;3.5.4修改表之增加主键altertabletbNameaddprimarykey(主键所在列名);例:altertablegoodsaddprimarykey(id)该例是把主键建立在id列上3.5.5修改表之删除主键altertabletbNamedropprimarykey;3.5.6修改表之增加索引altertabletbNameadd[unique|fulltext]index索引名(列名);3.5.7修改表之删除索引altertabletbNamedropindex索引名;3.5.8清空表的数据truncatetableName;4:列类型讲解列类型:整型:tinyint(0~255/-128~127)smallint(0~65535/-32768~32767)mediumintintbigint(参考手册11.2)参数解释:unsigned无符号(不能为负)zerofill0填充M填充后的宽度举例:tinyintunsigned;tinyint(6)zerofill;数值型浮点型:floatdouble格式:float(M,D)unsigned\zerofill;字符型char(m)定长varchar(m)变长text列实存字符i实占空间利用率char(M)0=i=MMi/m=100%varchar(M)0=i=Mi+1,2i/i+1/2100%yearYYYY范围:1901~2155.可输入值2位和4位(如98,2012)日期时间类型dateYYYY-MM-DD如:2010-03-14timeHH:MM:SS如:19:26:32datetimeYYYY-MM-DDHH:MM:SS如:2010-03-1419:26:32timestampYYYY-MM-DDHH:MM:SS特性:不用赋值,该列会为自己赋当前的具体时间5:增删改查基本操作5.1插入数据insertinto表名(col1,col2,……)values(val1,val2……);--插入指定列insertinto表名values(,,,,);--插入所有列insertinto表名values--一次插入多行(val1,val2……),(val1,val2……),(val1,val2……);5.3修改数据updatetablenamesetcol1=newval1,col2=newval2,......colN=newvalNwhere条件;5.4，删除数据deletefromtablenaemewhere条件;5.5，select查询（1）条件查询wherea.条件表达式的意义，表达式为真，则该行取出b.比较运算符=，!=，==c.like,notlike('%'匹配任意多个字符,'_'匹配任意单个字符)in,notin,betweenandd.isnull,isnotnull（2）分组groupby一般要配合5个聚合函数使用:max,min,sum,avg,count（3）筛选having（4）排序orderby（5）限制limit6:连接查询6.1，左连接..leftjoin..ontableAleftjointableBontableA.col1=tableB.col2;例句:select列名fromtableAleftjointableBontableA.col1=tableB.col22.右链接:rightjoin3.内连接:innerjoin左右连接都是以在左边的表的数据为准,沿着左表查右表.内连接是以两张表都有的共同部分数据为准,也就是左右连接的数据之交集.7子查询where型子查询:内层sql的返回值在where后作为条件表达式的一部分例句:select*fromtableAwherecolA=(selectcolBfromtableBwhere...);from型子查询:内层sql查询结果,作为一张表,供外层的sql语句再次查询例句:select*from(select*from...)astableNamewhere....8:字符集客服端sql编码character_set_client服务器转化后的sql编码character_set_connection服务器返回给客户端的结果集编码character_set_results快速把以上3个变量设为相同值:setnames字符集存储引擎engine=1\21Myisam速度快不支持事务回滚2Innodb速度慢支持事务,回滚①开启事务starttransaction②运行sql;③提交,同时生效\回滚commit\rollback触发器trigger监视地点:表监视行为:增删改触发时间:after\before触发事件:增删改创建触发器语法createtriggertgNameafter/beforeinsert/delete/updateontableNameforeachrowsql;--触发语句删除触发器:droptriggertgName;索引提高查询速度,但是降低了增删改的速度,所以使用索引时,要综合考虑.索引不是越多越好,一般我们在常出现于条件表达式中的列加索引.值越分散的列，索引的效果越好索引类型primarykey主键索引index普通索引uniqueindex唯一性索引fulltextindex全文索引综合练习:连接上数据库服务器创建一个gbk编码的数据库建立商品表和栏目表,字段如下:商品表:goodsgoods_id--主键,goods_name--商品名称cat_id--栏目idbrand_id--品牌idgoods_sn--货号goods_number--库存量shop_price--价格goods_desc--商品详细描述栏目表:categorycat_id--主键cat_name--栏目名称parent_id--栏目的父id建表完成后,作以下操作:删除goods表的goods_desc字段,及货号字段并增加字段:click_count--点击量在goods_name列上加唯一性索引在shop_price列上加普通索引在clcik_count列上加普通索引删除click_count列上的索引对goods表插入以下数据:+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+|goods_id|goods_name|cat_id|brand_id|goods_sn|goods_number|shop_price|click_count|+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+|1|KD876|4|8|ECS000000|10|1388.00|7||4|诺基亚N85原装充电器|8|1|ECS000004|17|58.00|0||3|诺基亚原装5800耳机|8|1|ECS000002|24|68.00|3||5|索爱原装M2卡读卡器|11|7|ECS000005|8|20.00|3||6|胜创KINGMAX内存卡|11|0|ECS000006|15|42.00|0||7|诺基亚N85原装立体声耳机HS-82|8|1|ECS000007|20|100.00|0||8|飞利浦9@9v|3|4|ECS000008|17|399.00|9||9|诺基亚E66|3|1|ECS000009|13|2298.00|20||10|索爱C702c|3|7|ECS000010|7|1328.00|11||11|索爱C702c|3|7|ECS000011|1|1300.00|0||12|摩托罗拉A810|3|2|ECS000012|8|983.00|14||13|诺基亚5320XpressMusic|3|1|ECS000013|8|1311.00|13||14|诺基亚5800XM|4|1|ECS000014|4|2625.00|6||15|摩托罗拉A810|3|2|ECS000015|3|788.00|8||16|恒基伟业G101|2|11|ECS000016|0|823.33|3||17|夏新N7|3|5|ECS000017|1|2300.00|2||18|夏新T5|4|5|ECS000018|1|2878.00|0||19|三星SGH-F258|3|6|ECS000019|0|858.00|7||20|三星BC01|3|6|ECS000020|13|280.00|14||21|金立A30|3|10|ECS000021|40|2000.00|4||22|多普达TouchHD|3|3|ECS000022|0|5999.00|15||23|诺基亚N96|5|1|ECS000023|8|3700.00|17||24|P806|3|9|ECS000024|148|2000.00|36||25|小灵通/固话50元充值卡|13|0|ECS000025|2|48.00|0||26|小灵通/固话20元充值卡|13|0|ECS000026|2|19.00|0||27|联通100元充值卡|15|0|ECS000027|2|95.00|0||28|联通50元充值卡|15|0|ECS000028|0|45.00|0||29|移动100元充值卡|14|0|ECS000029|0|90.00|0||30|移动20元充值卡|14|0|ECS000030|9|18.00|1||31|摩托罗拉E8|3|2|ECS000031|1|1337.00|5||32|诺基亚N85|3|1|ECS000032|1|3