mysql基本操作和练习手册.pdf

整理了mysql的基本操作 和 练习手册.具体目录: 一 复习前的准备二 基础知识:1.数据库的连接2:库级知识3: 表级操作:3.1 显示库下面的表3.2 查看表的结构:3.3 查看表的创建过程:3.4 创建表:3.5 修改表4:列类型讲解整型:参数解释:数值型字符型日期时间类型5:增删改查基本操作5.1 插入数据5.3 . 修改数据5.4 . 删除数据5.5 select 查询6: 连接查询7 子查询8: 字符集客服端sql编码 character_set_client综合练习:三 查询知识练习1: 基础查询 whe1 show tables32查看表的结构:1 desc tablename:33查看表的创建过程:1 show create table tableName34创建表1 create table tbName列名称1列类型列参数][ not null default列2列名称N列类型[列参数][ not null defaultEngine myi sam/innodb charset utf&/gbk例子1 create table userid int auto incrementname varchar(20) not null defaultage tinyint unsigned not null default 0dex id (id)Engine=innodb charset=utf&e注: innodb是表引擎,也可以是 myL sam或其他,但最常用的是 myi sam和 innodb,9 charset常用的有utf8,gbk;35修改表35.1修改表之增加列:1 alter table tbName2add列名称1列类型[列参数][ not null default]#(ad之后的旧列名之后的语法和创建表时的列声明352修改表之修改列I alter table tbName2 change旧列名新列名列类型[列参数][ not null default]#(注:旧列名之后的语法和创建表时的列声353修改表之减少列1 alter table tbName2drop列名称;3.5.4修改表之增加主键1 alter table tbName add primary key(主键所在列名)3 9 alter table goods add primary key (id)该例是把主键建立在id列上355修改表之删除主键alter table tbName drop primary key3.56修改表之增加索引Lter table tbName add [unique l fulltext] index素引名(列名);35.7修改表之删除索引1 alter table tbName drop index索引名;358清空表的数据truncate tablename4列类型讲解整型:● tiny int(0~255/-128~127)smallint(0~65535/-32768~32767)mediumint int bigint(参考手册112)参数解释:● unsigned无符号(不能为负)0填充●M填充后的宽度tinyint unsignedt(6 zerofi数值型浮点型 float double1格式: float(M,D) unsigned\zerofill;字符型har(m)定长char(m)变长实存字符讠实占空间利用率2 char(m)0<=i<=MMi/m<=100%0<=i<=M+1,2/i+1/2<100%日期时间类型● year YYYY范围:1901~2155.可输入值2位和4位(如98,2012● date YYYY-MM-DD如:2010-03-14time HH: MM: SS $4: 19: 26:32datetime YYYY-MM-DD HH: MM: SS 0: 2010-03-14 19: 26: 32● timestamp YYYY-MM-DDHH:MMSS特性:不用赋值,该列会为自己赋当前的具体时间5:增删改查基本操作5.1插入数据1 insert into表名(co11,co12,…) values(va11,Va12插入指定列2 insert into表名 values(,,,);--插入所有列3 insert into表名 values次插入多行(vaL1,vaL2…vaL1,vaL2…)L1 val253.修改数据1 update tablename setL2-newval256 COLNLNwhere条件5.4.删除数据1 delete from tablengeme where条件55 select查询条件查询oa.条件表达式的意义,表达式为真,则该行取出b.比较运算符=,!=oc.ike, not like(%匹配任意多个字符,匹配任意单个字符)in; not in, between andd. is null is not nul分组go一般要配合5个聚合函数使用max,min,sum,avg, count筛选 having排序 order by限制6:连接查询16.1左连接Left join.. on3 table a left join table B on tableA. coll tableB col24例句: select列名 from table A left join table b on tableA.co11= tableB.co1266.2右链接: right join66.3内连技: inner JoLn左右连接都是以在左边的表的数据为准,沿着左表查右表内连接是以两张表都有的共同部分数据为准,也就是左右连接的数据之交集.7子查询Whee型子查询内层sq的返回值在 where后作为条件表达式的一部分196]: select from tablea where cola =(select colb from tableb where ..from型子查询内层sq查询结果作为一张表供外层的sq语句再次查询1例句: select+fm( select from…,) as tableName where8:字符集客服端sq编码 character set client服务器转化后的sq编码 character set connection服务器返回给客户端的结果集编码 character set results快速把以上3个变量设为相同值: set names字符集存储引擎 enginE=121 Myisan这度快不支持事务回滚2 Innodb速度慢支持事务,回滚①开启事务start transaction②运行sq;③提交,同时生效回滚 commit\rollback触发器 trigger监视地点表监视行为:增删改触发时间: after\before蝕发事件:增删改创建触发器语法create trigger anAmeafter/before insert/delete/updateon tableNamefor each rowsql;--触发语句删除触发器: drop trigger tgName;索引提高查询速度,但是降低了增删改的速度,所以使用索引时,要综合考虑索引不是越多越好,般我们在常出现于条件表达式中的列加索引值越分散的列,索引的效果越好索引类型primary key主键索引index普通索引unique index一性索引fulltext index全文索引综合练习连接上数据库服务器创建一个gbk编码的数据库建立商品表和栏目表字段如下商品表 goodsgoods id主键goods_name--商品名称cat id--栏目idbrand id--品牌idgoods_sn--货号goods_ number--库存量sh。p_ price--价格goods_ desc--商品详细描述栏目表: categorycat id--主键栏目名称parent _id-栏目的父id建表完成后,作以下操作:删除gods表的 goods_desc字段,及货号字段并增加字段 click count--点击量在 goods_name列上加唯一性索引在 shop_price列上加普通索引在ccik_ count,列上加普通索引删除clicκ count列上的索引对 goods表插入以下数据:I goods_id i goods_nameI cat_id I brand _id I goods _sn I goods_number I shop_price i click_count1KD87648|Ecs000000101388.004|诺基亚N85原装充电器8|1|EcS00000417|58.003|诺基亚原装5800耳机8|1Ecs00000224|68.005|索爱原装M2卡读卡器7|EcS0000058|20.006|胜创 KINGMAX内存卡110|ECs000006|15|42.007|诺基亚N85原装立体声耳机Hs-82|8|1|ECs000007|20|100.00a|飞利浦9@93|4|Ecs00000817|399.0099|诺基亚E6613|1|ECs00000913|2298.002010索爱C702c13|7|Ecs0000107|13280011|索爱C702c3|7|ECs00001111300.00|012|摩托罗拉A81013|2|ECS0000128|98300413|诺基亚5320× pressMusic|3|1|EcS0000131131004诺基亚5800XM1|Ecs0000144|2625.00615摩托罗拉A81013|2|ECS0000153|788.0016恒基伟业G10112|11|Ecs0000160|82333317|夏新N713|5|Ecs0000171|2300.00|218夏新T545|EcS00001812878.00019|三星SGH-F2586|ECS0000190|858.0020|三星BC013|6|ECS000020280.001421|金立A303|10ECs00002140|2000.0022|多普达 Touch HD3|3|ECS0000220|59990023|诺基亚N965|1Ecs0000238|3700.0024|P8063|9|ECS000024148|2000.00|3625|小灵通/回话50元充值卡13|0|ECS0000252|48.00026|小灵通/话20元充值卡13|0|ECS0000262|19.0027|联通100元充值卡15|0|Ecs0000272|95.0028|联通50元充值卡150|ECs0000280|45.0029|移动100元充值卡14|0|ECS000029090.00030|移动20元充值卡0|ECS0000309|18.0031摩托罗拉E832|Ecs000031113370032|诺基亚N853|1|Ecs0000321|3010.00三查询知识练习1:基础查询 where的练习:查出满足以下条件的商品11:主键为32的商品1 select goods_id, goods_name, shop_price from ecs_goods where goods_id=32;1.2:不属第3栏目的所有商品1 select goods_id, cat_id, goods_name, shop_price from ecs_goods where cat_id13:本店价格高于3000元的商品select goods_id, cat_id, goods_name, shop_price from ecs_goodswhere shop_price >3000;14:本店价格低于或等于100元的商品select goods_id, cat_id, goods_name, shop_price from ecs_goods where shop_price <=10015取出第4栏目或第11栏目的商品(不许用or)select goods_id, cat_id, goods_ name, shop_price from ecs _ goodswhere cat id in(4, 11)16:取出100<=价格<=500的商品(不许用andselect goods_id, cat_id goods_name, shop_price from ecs_goodswhere shop_ price between 100 and 50017:取出不属于第3栏目且不属于第11栏目的商品(and,或 not in分别实现)select goods_id, cat_id goods_name, shop_price from ecs_goods where cat_id -3 and cat_id! =11select goods_id, cat_id. goods_ _name, shop_price from ecs goods where cat_id not in (3, 11);18:取出价格大于100且小于300,或者大于4000且小于5000的商品()select goods_id, cat_ id, goods_name, shop_price from ecs_goods where shop_price>100 and shop_price <300 orshop_price >4000 and shop_price <500019:取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品select goods_id, cat_ id, goods_name, shop_price, click_count from ecs_goods wherecat id=3 and(shop_ price <1000 or shop_price>3000) and click count> 51.10取出第1个栏目下面的商品(注意1栏目下面没商品,但其子栏目下有)select goods id, cat id, goods name, shop_ price, click_count trom ecs_ goodswhere cat id in(2, 3, 4, 5)1.11取出名字以"诺基亚“开头的商品select goods_id, cat id, goods_name,shop_ price from ecs_goods where goods_name like‘诺基亚%;1.12取出名宇为诺基亚Nxx"的手机select goods _id, cat_id, goods_name, shop_ price from ecs_goodshere goods_name like'诺基亚N_’;113取出名字不以"诺基亚开头的商品select goods_id, cat_id, goods_name, shop_price from ecs_gooswhere goods_name not like诺基亚%;1.14:取出第3个栏目下面价格在1000到3000之间,并且点击量>5"诺基亚"开头的系列商品select goods_id, cat_id, goods_name, shop_price from ecs_goods wherecat_id=3 and shop_price >1000 and shop_price <3000 and click_count>5 and goods_name like WEI%;select goods_id, cat_id, goods_name, shop_price from ecs _goods whereshop_price between 1000 and 3000 and cat_id=3 and click_count>5 and goods_name like iEaL%1.15一道面试题有如下表和数组把num值处于[2029]之间改为20num值处于30,39]之间的,改为30man表I num I2523293437324548521.16练习题把god表中商品名为诺基亚xxx的商品,改为 HTCXXXX提示:大胆的把列看成变量,参与运算,甚至调用函数来处理substring(), concat(