Mysql燕十八学习笔记

1、mysql 三大列类型1. 数值型整型tinyint 、 smallint、 mediumint 、 int、 bigint小数型float(d,m),decimal(d,m)2. 字符串型char(m)varchar(m)text 文本类型3. 日期时间类型date 日期time 时间datetime 时间类型year年类型一 整型列的字节与存储范围从数学上讨论tinyint ，建表时既能足够存放，又不浪费空间。即：(1) . 占据空间(2) . 存储范围tinyint微小的列类型，1字节字节最小值最大值（带符号/无符号）（带符号/无符号）tinyint1-1281270255smallin

2、t2-3276832767065535mediumint3-2八232八23-102八24-1int4-2八312八31-102八32-1bigint8-2八632八63-102八64-1通过占用字节和所存范围分关系合理的选择类型例：tinyintmy号ql)table tst(- id int primary key auto_increment s- name uarchar(10)p- age tinyintjcharset : utfs； query ok, g rows affected (0 j 6 写ec)gaqlinsert into“日lues(张三,，no0)；uery o

3、k, 1 row affected, 1 warning (0.d sac) ysql select k from tost；id i name i age i1 i张三i25 i2 i 藤三i 127 irows in set (g.0g sec)insert into te5t(rwmeqge) udluw5(张三j 729)； ok, 1 row affected, 1 warning (q.00 tec)select m from test；id | name | age三三一二 长长长 bf- _d7row5 in s&t (0.qo c电匕)经测试，tinyint的默认为有符号-1

4、28127之间，当越界时自动取相应的边界值。若要存0255无符号时：列的可选属性：tinyint（m） unsigned zerofill.m：宽度（在。填充的时候才有意义）unsigned:无符号类型（非负），影响存储范围zerofill： 0填充（默认无符号）mysql) alter table tot add age3 tinyint(5) zorofill； query ok, 8 rows affected (0.07 sec) records j 8 duplicates： 0 warnings： 0mycql decc tect ；i field i typei null i k

5、ey i default i extraageage3uarchar(19)tinyint(m)tinyint(3) unsignedtinyint(1)tinyint(5) unsigned zerofil1novesvesyesvesvespri i nulli nulli nulli nulli nulli nullauto increment6 rows in set (0.01 sec)mysql insert into test(name9age1) ualuos(张三，2。)； query ok, 1 row affected (0.00 sec) myeql insert in

6、to test(name,age1) u&iuqs(李四，7。)； query ok, 1 row affected, 1 warning (0.00 sec) mysql select 乂 from test；+ -+i id i name i age i age1 i + +412 3 4 5 6 7三三三三四三四张张张张李张李25127-128127-1g0nullnullnullnullnullnullnull2007 rows in set (0.00 sgc)fnysql insert into teet- (name,age3)- ualu&s-赵六:5)；query ok, 1

7、 row affected (0.0。 sec)niy&ql* from test；ma aan( uiimmbi name i+i+1 23 45 g7 89张张张张李张李王赵一一一一三四三四五六null1 null 1nullnull1 null 1nullnull1 null 1nullnulli null |nullnull1 null 1null2g0i null |nullgi null |nullnull1127 1nullnull1 null 1g0305i nulli null6 07282 172529 rows in set (0.07 sec)列可以声明默认值，而且推荐

8、声明默认值not null default 0;ysql alt&r table test add ag4 tinyint(3) not null default 6； uery qic, 9 rows affected (0.13 sec) ecords： 9 duplicates： 0 uarnings： 0ysql goloet m from ；u, e 9 ai id i name i age i agel i age? i age3123456 7 39三三三三四三四五六一 张张张张李张李王赵一-25 imull1 null1 null127 1null1 nuul1 null-12

9、8 1null1 null1 null127 1null1 null1 null-100 inulli nulli nullnull 12001 null1 nullnull 101 null1 nullnull 1null11271 nullnull 1null1 null1 00005o0ouoogoo小数型/浮点型、定点型float(m,d):m代表总位数，d代表小数位(6,2)为例：-9999.99，9999.99my$ql create table t。4t2(- name uarchar(10) not null default ,- price float(6,2) not nu

10、ll default 0.0g- );query ok, 0 rows affected (0 j1 sec)mysql dsc tet2； +4-+ -t-+4-ifielditypeinulliidefaultiextraiblinameiuarchar( 10)inoiiiiiprice|float(672)inoii0 00iih1!m2 rows in set (0-01 sec)mgsql insert into testzfname,price) ualues(ueaglefly,19.9)； query ok, 1 ron affected (0.03 oec)mgsql se

11、lect 近 from te$t2;+-71+i name i price i + - -+i ueaglefly i 19,90 i +-+1 row in set (0.00 sec)decimal(d,m):mysql alter table tost2- ddd- bigprice decimal(9,2) not null default 0.0；query ok, 0 roue affected (.12 qqc)records； 0 duplicates； q uarnings； 0mysql alter table test2- add- deciprice dciinal(9

12、,2) not null default 0.0；query ok, 0 rows affected (g.98 sec)records： g duplicates： 0 warnings： 0mysql dose test2；i field i typei null i key i default i extra ifa-inamivarqhar(io)inoiiiiipriceifloatfg,2)inoiiq.ggiiibigpriceidecimal(9,2)inoii0.06iiidecipriceidecimal(9,2)inoii3.06iim|bbbbb4 row$ in se

13、t (0.01 see) mysql insert into test2 - (name, bigpr ice, deci price) ualues (自行率， 1234567.23l , 123456l23,) ； query ok, 1 row affected (0.06 sec)+-+i nameipric&ibigpricei decipricei+十+i 自行车 i /口。i 1234567.23 i 1234567.23 i1 row in set to.00 sec)三.字符型列char：定长，char（m）,m代表宽度，即：可容纳的字符数 0=m=255 varchar：变

14、长,0=m=65535的2w6w个字符受字符集影响）text文本串，（约2w6w个字符受字符集影响）区别在哪呢？char定长：m个字符，如果存的小于 m个字符，实占m个字符 varchar： m个字符，存的小于 m个字符，设为 n, nm,实占n个 字符y更馋n.m代表宽度.口。506553kua等柒字符为物ail嶷2刘帆左右j宾强健可存字 将冥存字符实占空洵不照率cbarmm1mifm -1 口咯mm1s字符+（书_巩1+1-2) insert into stu- (name.waihao)- values- (*ueaglefly ,1veaglefly ，)；query qk. 1 ro

15、w effected, 2 warning? 58)mysql? select concat(name,!),concat(waihao,!) from stu；+i conca t(name, ?) i concat(waihao / !1) i+i张三！l小三，ii zhangan!i 小三！ii zhangsan!i 小三！ii ueaglef1*i ueagl&fly ?i+4 rows in set (0.00 sec)char利用率小于等于100%,而varchar永远小于100%, 1-2个字节用于标志实存字符长度。char（m）如何占据m个字符宽度？答：如果实际存储内容不足 m

16、个，则后面加空格补齐，取出来的时候再把后面的空格去掉，（所以如果内容后面有空格，将会被清除）选择原则：1 .空间利用效率（m固定选char）2 .速度速度上：定长速度快些，四.日期时间列类型date日期time时间datatime 时间类型year年类型1. year类型：1个字节表示1901-2155年【0000年表示错误时】如果输入两位，“0069”表示20002069,如果输入两位“ 7099”表示19701999如果记得麻烦，输入四位就行了、lmygql y； mbi fieldi typei null ikey i defaultiextra itmii yai ymr(h)i ve

17、s i i nullii:1 row in set (0.01 see)叫sql insert into y- ualu9( 19g1 )；querg ok, 1 row affected (0.00 bgcimyeql insert into y ualues( 97)； query ok. 1 row affected (0.00 secjmysql) insert into y udluos( 12)；query ok, 1 row affected (0.00 decmysql select 找 from y；4-十i 1901 ii 1997 ii 2912 i t+3 rows i

18、n set (0 . gq sbg)2. date类型：典型格式，1992-08-121000-01-01 9999-12-31my&ql create table d(- title uarchar(30)t- dt date- )charset utf8；query ok, 0 rows affected (0.13 see)my$ql incert into d- ualues- (开国大典，1949-10-0)；query ok, 1 row affected, 1 naming (0,00 sec) mydql select 我 from d；+!十i titlei dtii 开国大

19、典i 1949-10-01 i+h41 row in set (0.00 sc)3. time类型，典型格式 hh:mm:ss-838:59:59 +838:59:59mysql create table t(- tm time- );query ok, g rows affected (g, 12mysql insert into t ualues( 111：11 ： 11 b)；query ok, 1 row affected (0.00 sec)mysql select 、 from t；k十il1 row in set (g.og sec)4. datetime 典型格式：1989-0

20、5-06 12:23:341000-01-01 00:00:009999-12-31 23:59:59、/、a “、日注息:在开发中很少使用日期时间类型来表示一个需要精确到秒的列，原 因：虽然日期时间类型能够精确到秒，而且方便查看。但是计算不 便。用时间戳来表示。时间戳：1970-01-01 00:00:00到当前的秒数mysql create table usersf- name uarchar(2q) not nul1 default *1 ,- rgtimo datetime not null default * 1oog-oi-g1 09：00：001- )charet utf8；qu

21、ery ok, 0 rows affected (0.12 sec)mgsql desc u&ers； +-+i fielditypeinulli key idefaultiextrj+#1 nameiuarchar(20) inoiiii regtimidatotimeinoii1009-01-01 0000:06 i+-k-t+ +, -*2 rows in 存m (日.01 sec)my号ql) insert into- (name)- nalue$- 张三1;query ok, 1 row affected (0.00 sec)mysql insert into users u自luo

22、s- (李四2015-6272 11 ：40 ：351)： query 0ic, 1 row affected (d.go sc)my$ql&elct兴from user；+h+i nameiregtimei+h+i 张三i 1000-01-01 00:00:90 ii 奉四i 2015-02-12 11:4g:35 i+2 rows in (0.q0 see)面试1:当表示性别时，可用1或。表示男女，如,ntgsql) create table teaeh&r(- name uarchar(20),- gender tinyint- ):query 0ks 0 rows affected (

23、9(12 $ec)叫sqlinsert into teather ueluea( 张三，.1)：querg 0 select 某 from teacher；iname igenderi+d+i张三 i 11+4+enum不符合关系型数据1row inset(0,00 sec)可能会问到为什么不用enum枚举，原因:库设计理念，而且字节上不比tinyint少。实例:niycql create table- id int primary key auto_increment,- hame char(3) not null default- age tinyint unsigned not null

24、 default 0,- 01nmi1 uarchar(36) not nul 1 default f- tel char( 11j not nul 1 default- salary d&eimal(7,2) not null default 1soo.god 5- riqi date not null default ,2015-02-121- )ehareet utfs；query ok, 6 rows affected (0.12 sec)nysql dose class：+ -+t=+ q-+i field1type1null1 key1default1extra1.x ._ x.t

25、tt十i id1int(i1)1no1 prl1null1auto.increment1i nan91char3)1no1111i1tinyint(3) unaignd1no11011i otrail1varchar(sq)1no11111 e1chbr(111no11111 salary1deciual(7,2)1no11isee.eo111 riqi1date1no112q1502-12 i1*.一 + -+ + -+ + -+t in c6.01增删改查之案例过程分析 用户注册表单收集，提交数据，注册页面收集到表单的数据后，形成insert语句，user表插入该条数据，用户注册完成前台用

26、户中心，用户新昵称，根据新昵称和用户id,形成update语句，完成昵称修改管理后台点击会员列表，此时，网页形成 select语句，查询出所有会员的数据，完成会员的查看管理员后台选中某用户并且删除，捕捉此用户的id,根据用户id形成相应的delete语句 执行delete语句，完成用户的删除mysql create database tshop charset utf8；.query ok, 1 row affected (o.0g sec)hmysql create table goods( - goods_id int primary key auto-increment, - cat.i

27、d smallint not null default 0, - goods.sn char(15) not null default ， -good$_name varchar(30) not null default ,- click_count mediumint unsigned not null default g,- brand.id smallint not null default 0,- goods_number smallint not null default 0,- market-price decimal(7,2) not null default 0.00,- sh

28、op-price decimalc?,2) not null default 0.00,- add.time int unsigned not null default 0- )charset utf8；query ok, 0 rows affected (0.15 sec)mysql create table category(- cat-id smallint primary key auto_increment,- cat-name uarchar(30) not null default 1,- parent_id smallint not null default 0- )chars

29、et utf8；query ok, 0 rows affected (0.12 sec)mysql create table brand(- brandid smallint primary key auto_incrment,- brand-name uarchar(30) not null default- )charst utf8；query ok, g rows effected (0.10 sec)nysql insert into tshop.goods- select- goods.id,cat_id,goods_sn,goods_name,click_count,- brand

30、_id,goods_number,market_price,5hqp_pice,- add_time from hop.ecs_goods；quertj ok, 32 routs affected h 1 warning (9.07 secj(record ： 32 duplicates ： 0 warnings： 1my&ql ins&rt iftto tshop.category- select cat_idfcatb_nanieiparent_id- from shop.ecs_catogory：query ok, 19 rotis affected (g.66 sec)records：

31、 19 duplicates： 6 warnings! 0mysql insert into tshop.brand- select- brand id,brand name- from shop.ecs_brand；query ok, 13 rows affected (0.q1 sec)records； 13 duplicates； 0 warnings： 0阶段总结(一)列类型的概念数值型整型 tinyint smallint mediumint int bigint整型的unsigned代表无符号，zerofill代表0填充，m代 表宽度(在0填充)浮点型/定点型float(m,d)

32、unsigned m 精度，即总位数，d 代表小数位 decimal比float更精确。字符型char(m)定长，可存储的字符数，m=255varchar(m)变长，可存储的字节数， m=65535char与 varchar的不同点char（m）,实占m个字符，不够m个右侧补空格，取出时，在去除右侧空格，导致右侧真有空格时会丢失。varchar（m） ，有 1-2个字节来标记真实的长度，日期时间型year 1901-2155，如果输2 位， 00-69 之间+2000， 70-99 之间 +1900date yyyy-mm-dd 范围在 1000-01-01 9999-12-31time hh

33、 :ii:ss范围在-838:59:59 838:59:59datetime yy-mm-dd hh:ii:ss 1000-01-01 00:00:00 9999-12-31 23:59:59开发中的一个问题 精 确到秒的时间表示方式， 不是用 datetime ，而是用 int 来表示时间戳用时间戳方便计算，而且方便格式化成不同的显示样式。建表语句create table 表明 （列名称 ，列类型列属性默认值 ，：.）engine 引擎名 charset 字符集增： insert答：往哪张表增，增那几列，各为什么值？insert into 表名（列 1，列 2， .列 n）values（值1

34、，值2.值 n）* 如果不声明插入的列，则默认插入所有列。改： update答：修改哪张表，修改那几列，修改成什么值？在哪几行上生效？update 表名set列1 = 值1，列 2 = 值 2， .列 n =值 nwhere 表达式；删： delete删除哪张表的数据，删除哪些行？delete from 表名where 表达式查： select * from 表名查询的五种语句： where， group ， having， order by， limit一 where 条件查询比较运算符1. in ,等于1 n任意一个。例： select goods_id from goods where

35、cat_id in(4,5);2. between 值 1 and 值 2，表示在值1 和值 2 之间例： select goods_id from goods where cat_id between 1 and 5;逻辑运算符1. not 逻辑非例： select good_id from goods where cat_id not in(4,5);2. or 逻辑或3. and 逻辑与模糊查询：案例：想查找诺基亚开头的所有商品like 像， % 通配任意字符_ 单个字符select goods_id from goods where goods_name like 诺基亚 ;selec

36、t goods_id from goods where goods_name like 诺基亚 _ 二.group分组查询group by作用：把 行按 字段分组语法：group by col1,col2coln运用场合：常见于统计场合，如按栏目计算帖子数，统计每个人的平均成绩等。group与统计函数max:求最大，min ： 求最小，sum：求总和，avg：求平均，count:求总行数练习:mysql=查询每个栏目下最贵的商品价格mysql select cat_id,max(shcp_price) from goods group by cat_id；i cat-id i max(shop

37、_price) i81113141516823.335999.632378.063700.00160.6042,004b.0099.0095,09899.001.+410 rows in set (g.d7 sec)mysql株查出最大1最新)的商品编号myql geloct max(good_icl) from goods；i max(good$_icl) i33 i2.3.mysql #查出最旧(最小)的商品价格mysql select min(goods_id) from goods； k-十i min(goods_id) i -十i1 i+1 row in eet (0.00 s&c)

38、 my$ql select sum(goods_number) from goods；i $um(good&_number) i522 i4.1 row in (0.00 sec)叫“1tt查询本店所有商品的平均价格叫sql select aug(shop_pric) from goods；i aug(ehop_price) i +4i 1222.10063 i 1 row in st (o + 0g sec)5.mysql林本店一共有笠少个商品my&ql select c0unt(*) from goods；+i counto) i32 i1 row in set (0,00 sec)6.m

39、ysql件查询每个栏目 下面最贵商品mysql select cat_id,ma(price) from goods group by cat;i cat_id i itiax(shop_pri.ce) i23458134561 1 1 1823 335999.00zs78.3700.qd100.0042. od7.1 rows in set (g.00 sec)811131415168.+10 row* in 号0t号c)myeql)#查询每个栏目大最便宜商品mgsql select cat-id.min(shop_price) from goods group by cat-id； +11

40、 cat_id i min(shop_price) i823.33286.601383.q0 3700.0058.0620.00 13.0018 . d645.00899.005811131415169.mysql 查询每个栏目的商品的邛均价格mgsql select cat_id,aug(shop_pric) from goods group by cat_id；i cat_id i aug(shop_price) i323.33dgg0 1746.g66667 2297,060060 3700.ogoogo75.333333 i 31,009060 33,500060 54.0690gb

41、70.oo0ogo893.00d06010 rows in set (0.0g secj叫sql)样查询每个栏目下的商品总类n)ysql select cat_id,countf) from goods group by cat_id； +i cat_id | count(箕)i101110 rows in set (0 + 0g sec)ysql k请查询出本店每个商品比市场价格低多少钱？nycql ceuct godg.id,goodg_nam.market_pric - shop_pric。from goodc叫”1)11继续体会列名当成变量看叫号ql#查询每个栏目下面积压的货恭吗5ql

42、select cat_idf$um(9hop_price gqods,numb9r)- from goods- group by cat_id；+- 4+i cat_id i sum(shop_price goods_number) i0.06685035.9。6891.0029600.0q4618.06790,06134.90162.qq190 j9g89960.0012.10 rows in set (0.00 sec)mysql tl可以给列或计算结果取别名,用nysql s&lout cat_id:eum(shop_price x goods_number)- as hk- from

43、goods- group by cat_id；i cat id i hk23458134560.00 685035.60 6s91.00 29600.00 4618.00790.00 1 34.00162.00 199.00 89900.0013.三. having10 rows in set f0.07 sec) mysqi 杳询比市场价省钱2k元以上的商品以及该商品mysql 所省的钱whwo和having分别实现;mysql select goods_id,goods_name,markt_priceshop_price- as 省钱 - 一-一- from goods- where 省

44、钱 200；error 1054 (42s22) ： unknown column ,省钱，in where clause19g347812341211111222233277.60 ii 459.60 ii 265.60 ii 262.20 ii 525.0q ii 460.00 ii 575.60 ii 400.00 ii 1199.80 ii 740.00 i400.00 ii 267.39 ii 602.00 i191013141 718212223243132i省钱277.60 ii 459.6。 ii 265.60 ii 262.20 ii 525.00 ii h60.00 ii

45、575.60 ii hoo.oq ii 1199.80 ii 740.00 i400.00 ii 267.39 ii 602.00 inysql升杳询比市场价省钱20。元以上的商品以及该商品nysql目所省的钱（whe。和hauing分别实现）nysql select .market.price-shop.price- as省钱- from goods- where markt.price-shop.price 200：+4+i goods.id i goods.nami 省钱 ikd87g雷基亚e66索詈:c702c诺基亚5320 xpressmusic

46、诺基亚5800xm夏新n7豆新t5金立a3o名普大touch hd诺基亚n96p806摩斗罗拉e8诺基亚n85mysql卦查询比市场价省钱2。元以上的商品以及该商品mysql卦所省的钱（where和hauing分别实现）mysql select goods id,goods name,market_price-shop.price- as省钱- from goods- having 省钱 200 ；+i goods_id i 9oods_namq +kd876诺基亚e66索爱c702c诺基亚5320 xprossmusic诺基亚5800xm夏新n7夏新t5金立a30多普达touch hd诺箕亚

47、n96p80g摩托罗拉e8诺基亚n851 #同上题，只不过查在第三个栏目下比市场价低20。元以上的商品1 select goods-id,cat_id,market-price - shop-price- as省钱- from goods- where cat-id = 3- hauing 省钱 200；goods-id i cat_id i 省钱333333333459.60265.60262.20460.0。400.001199.80400.00267.39602.0。rows in set (0.07 sec叫sql带查询积压贷款超过2m元的栏目，mysql #以及该栏目积压的贷款mys

48、ql select cat_id,sum(shop_pricexgoods_number)- as贷款- from goods- group by cat_id- hauing 货款 26000;+i cat_id i 货款i+i3|685035.0|i5i296o0.goii16|89900.00|+3 rows in set (0.00 sec)*例题:设有成缰表景u如下:姓名科目分数 i张三数学90 张三语文50 张三地理40 李四语文55 李四政治45 王五政治30试查询两门及两门以上不及格同学的平均分只用一个select,不用子查询和左连接。十rows in st (0.60 sec)i name i score 66 i 卜44三三三四四五张张张李李王select name($cor? #1+算每个人的挂科科目数my&ql select nam&,sum(seore0 from stu1i name i sum(scor&selectnaine7uin(scori as gk,- aug(score)- as pj- from stu1 group by name hauing gk =