跟燕十八学习PHP-第二十二天-mysql字符型列详解

 

/** 
燕十八 公益PHP培训 
课堂地址:YY频道88354001 
学习社区:www.zixue.it 
**/

mysql> # 学习tinyint的参数并验证字节与范围的关系
mysql> create table class (
-> sname varchar(20) not null default '',
-> age tinyint not null default 0
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.33 sec)

mysql> desc class;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sname | varchar(20) | NO | | | |
| age | tinyint(4) | NO | | 0 | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.52 sec)

mysql> #为其插入数据,age到底是-128到127,还是0-255呢?
mysql> insert into class
-> (sname,age)
-> values
-> ('刘备',28);
Query OK, 1 row affected (0.02 sec)

mysql> select * from class;
+-------+-----+
| sname | age |
+-------+-----+
| 刘备 | 28 |
+-------+-----+
1 row in set (0.00 sec)

mysql> insert into class
-> (sname,age)
-> values
-> ('彭祖',128);
ERROR 1264 (22003): Out of range value for column 'age' at row 1
mysql> #out of range,超出范围了.
mysql> insert into class (sname,age) values ('pengzu',-129);
ERROR 1264 (22003): Out of range value for column 'age' at row 1
mysql> insert into class (sname,age) values ('小孩',0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into class (sname,age) values ('树妖',255);
ERROR 1264 (22003): Out of range value for column 'age' at row 1
mysql> insert into class (sname,age) values ('树妖',127);
Query OK, 1 row affected (0.00 sec)

mysql> #经过上面的测试,age的范围是-128 到 127
mysql> #即*int ,不加特殊说明,默认是有符号
mysql> 
mysql> #加unsigned表示无符号,可以影响存储的范围
mysql> #加一个学分列
mysql> alter table class add score tinyint unsigned not null default 0;
Query OK, 3 rows affected (0.11 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> desc class;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| sname | varchar(20) | NO | | | |
| age | tinyint(4) | NO | | 0 | |
| score | tinyint(3) unsigned | NO | | 0 | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.19 sec)

mysql> # 测试其范围
mysql> insert into class(sname,score) values ('张飞',-1);
ERROR 1264 (22003): Out of range value for column 'score' at row 1
mysql> # -1 不能插入了,因为有符号,范围是0 255
mysql> insert into class(sname,score) values ('张飞',0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from class;
+-------+-----+-------+
| sname | age | score |
+-------+-----+-------+
| 刘备 | 28 | 0 |
| 小孩 | 0 | 0 |
| 树妖 | 127 | 0 |
| 张飞 | 0 | 0 |
+-------+-----+-------+
4 rows in set (0.00 sec)

mysql> insert into class(sname,score) values ('蛇妖',256);
ERROR 1264 (22003): Out of range value for column 'score' at row 1
mysql> insert into class(sname,score) values ('蛇妖',255);
Query OK, 1 row affected (0.00 sec)

mysql> select * from class;
+-------+-----+-------+
| sname | age | score |
+-------+-----+-------+
| 刘备 | 28 | 0 |
| 小孩 | 0 | 0 |
| 树妖 | 127 | 0 |
| 张飞 | 0 | 0 |
| 蛇妖 | 0 | 255 |
+-------+-----+-------+
5 rows in set (0.00 sec)

mysql> # 加列类型加unsigned表示其为无符号类型
mysql> # 分析M参数
mysql> alter table class add age1 tinyint(1) not null default 0;
Query OK, 5 rows affected (0.25 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> desc class;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| sname | varchar(20) | NO | | | |
| age | tinyint(4) | NO | | 0 | |
| score | tinyint(3) unsigned | NO | | 0 | |
| age1 | tinyint(1) | NO | | 0 | |
+-------+---------------------+------+-----+---------+-------+
4 rows in set (0.05 sec)

mysql> insert into class (sname,age1) values ('M的意思',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from class;
+---------+-----+-------+------+
| sname | age | score | age1 |
+---------+-----+-------+------+
| 刘备 | 28 | 0 | 0 |
| 小孩 | 0 | 0 | 0 |
| 树妖 | 127 | 0 | 0 |
| 张飞 | 0 | 0 | 0 |
| 蛇妖 | 0 | 255 | 0 |
| M的意思 | 0 | 0 | 3 |
+---------+-----+-------+------+
6 rows in set (0.00 sec)

mysql> insert into class (sname,age1) values ('再看M的意思',99);
Query OK, 1 row affected (0.03 sec)

mysql> select * from class;
+-------------+-----+-------+------+
| sname | age | score | age1 |
+-------------+-----+-------+------+
| 刘备 | 28 | 0 | 0 |
| 小孩 | 0 | 0 | 0 |
| 树妖 | 127 | 0 | 0 |
| 张飞 | 0 | 0 | 0 |
| 蛇妖 | 0 | 255 | 0 |
| M的意思 | 0 | 0 | 3 |
| 再看M的意思 | 0 | 0 | 99 |
+-------------+-----+-------+------+
7 rows in set (0.00 sec)

mysql> #zerofill zero是零,fill是填充,代表0代表
mysql> #M必须和zerofill配合才有意义.
mysql> #给学员增加一个学号
mysql> #1:学号不能为负,2,学号一般位数相同,即使不同,00013,01238
mysql> #即不够倍数,用0填充.
mysql> alter table add snum smallint(5) zerofill not null default 0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near 'add snum smallint(5) zerofill 
not null default 0' at line 1
mysql> alter table class add snum smallint(5) zerofill not null default 0;
Query OK, 7 rows affected (0.55 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql> select * from class;
+-------------+-----+-------+------+-------+
| sname | age | score | age1 | snum |
+-------------+-----+-------+------+-------+
| 刘备 | 28 | 0 | 0 | 00000 |
| 小孩 | 0 | 0 | 0 | 00000 |
| 树妖 | 127 | 0 | 0 | 00000 |
| 张飞 | 0 | 0 | 0 | 00000 |
| 蛇妖 | 0 | 255 | 0 | 00000 |
| M的意思 | 0 | 0 | 3 | 00000 |
| 再看M的意思 | 0 | 0 | 99 | 00000 |
+-------------+-----+-------+------+-------+
7 rows in set (0.06 sec)

mysql> insert into class (sname,snum) values ('吕布',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into class (sname,snum) values ('廖化',15);
Query OK, 1 row affected (0.00 sec)

mysql> select * from class;
+-------------+-----+-------+------+-------+
| sname | age | score | age1 | snum |
+-------------+-----+-------+------+-------+
| 刘备 | 28 | 0 | 0 | 00000 |
| 小孩 | 0 | 0 | 0 | 00000 |
| 树妖 | 127 | 0 | 0 | 00000 |
| 张飞 | 0 | 0 | 0 | 00000 |
| 蛇妖 | 0 | 255 | 0 | 00000 |
| M的意思 | 0 | 0 | 3 | 00000 |
| 再看M的意思 | 0 | 0 | 99 | 00000 |
| 吕布 | 0 | 0 | 0 | 00001 |
| 廖化 | 0 | 0 | 0 | 00015 |
+-------------+-----+-------+------+-------+
9 rows in set (0.01 sec)

mysql> # snum统一补0,而且补到5位.
mysql> #为什么补到5位,因为M为5
mysql> # 总结 M表示补0宽度,和zerofill配合使用才有意义.
mysql> desc class;
+-------+-------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------------------------+------+-----+---------+-------+
| sname | varchar(20) | NO | | | |
| age | tinyint(4) | NO | | 0 | |
| score | tinyint(3) unsigned | NO | | 0 | |
| age1 | tinyint(1) | NO | | 0 | |
| snum | smallint(5) unsigned zerofill | NO | | 00000 | |
+-------+-------------------------------+------+-----+---------+-------+
5 rows in set (0.08 sec)

mysql> #观察snum的类型,可知zerofill则同时必是unsigned类型.
mysql> create table salary (
-> sname varchar(20) not null default '',
-> gongzi float(6,2)
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.22 sec)

mysql> insert into salary values ('张三',-9999.99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into salary values ('李四',9999.99);
Query OK, 1 row affected (0.00 sec)

mysql> select * from salary;
+-------+----------+
| sname | gongzi |
+-------+----------+
| 张三 | -9999.99 |
| 李四 | 9999.99 |
+-------+----------+
2 rows in set (0.00 sec)

mysql> alter table salary add bonus float(5,2) unsigned not null default 0.00;
Query OK, 2 rows affected (0.55 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from class;
+-------------+-----+-------+------+-------+
| sname | age | score | age1 | snum |
+-------------+-----+-------+------+-------+
| 刘备 | 28 | 0 | 0 | 00000 |
| 小孩 | 0 | 0 | 0 | 00000 |
| 树妖 | 127 | 0 | 0 | 00000 |
| 张飞 | 0 | 0 | 0 | 00000 |
| 蛇妖 | 0 | 255 | 0 | 00000 |
| M的意思 | 0 | 0 | 3 | 00000 |
| 再看M的意思 | 0 | 0 | 99 | 00000 |
| 吕布 | 0 | 0 | 0 | 00001 |
| 廖化 | 0 | 0 | 0 | 00015 |
+-------------+-----+-------+------+-------+
9 rows in set (0.00 sec)

mysql> select * from salary;
+-------+----------+-------+
| sname | gongzi | bonus |
+-------+----------+-------+
| 张三 | -9999.99 | 0.00 |
| 李四 | 9999.99 | 0.00 |
+-------+----------+-------+
2 rows in set (0.00 sec)

mysql> # 发奖金
mysql> insert into salary (sname,bonus) values ('王五',888.88);
Query OK, 1 row affected (0.02 sec)

mysql> select * from bonus;
ERROR 1146 (42S02): Table 'test.bonus' doesn't exist
mysql> select * from salary;
+-------+----------+--------+
| sname | gongzi | bonus |
+-------+----------+--------+
| 张三 | -9999.99 | 0.00 |
| 李四 | 9999.99 | 0.00 |
| 王五 | NULL | 888.88 |
+-------+----------+--------+
3 rows in set (0.00 sec)

mysql> insert into salary (sname,bonus) values ('王五',-0.88);
ERROR 1264 (22003): Out of range value for column 'bonus' at row 1
mysql> # bonus是unsigned类型，不能为负
mysql> create table account (
-> id int not null default 0,
-> acc1 float(9,2) not null default 0.00,
-> acc2 decimal(9,2) not null default 0.00
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.19 sec)

mysql> insert into account
-> values 
-> (1,1234567.23,1234567.25);
Query OK, 1 row affected (0.02 sec)

mysql> select * from account;
+----+------------+------------+
| id | acc1 | acc2 |
+----+------------+------------+
| 1 | 1234567.25 | 1234567.25 |
+----+------------+------------+
1 row in set (0.00 sec)

mysql> delete from account;
Query OK, 1 row affected (0.05 sec)

mysql> insert into account
-> values 
-> (1,1234567.23,1234567.23);
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;
+----+------------+------------+
| id | acc1 | acc2 |
+----+------------+------------+
| 1 | 1234567.25 | 1234567.23 |
+----+------------+------------+
1 row in set (0.00 sec)

mysql> #通过上例可以看出float有时会损失精度.
mysql> #如果像账户这样的敏感字段,建议用decimal
mysql> #接下来学习char varchar ,text ,blob
mysql> # char 和varchar分别称为定长和变长类型
mysql> # 对于char(N),不够N个长度,用空格在尾部补够N个长度,浪费了尾部.
mysql> # 而对于varchar(N),不用空格补齐,但列内容前,有1-2个字节来标志该列的内容长度.
mysql> create table test (
-> ca char(6) not null default '',
-> vca varchar(6) not null default ''
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.20 sec)

mysql> insert into test values 
-> ('hello','hello');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+-------+-------+
| ca | vca |
+-------+-------+
| hello | hello |
+-------+-------+
1 row in set (0.00 sec)

mysql> insert into test values
-> ('aa ','aa ');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+-------+-------+
| ca | vca |
+-------+-------+
| hello | hello |
| aa | aa |
+-------+-------+
2 rows in set (0.00 sec)

mysql> # concat 连接字符串用的
mysql> select concat(ca,'!'),concat(vca,'!') from test;
+----------------+-----------------+
| concat(ca,'!') | concat(vca,'!') |
+----------------+-----------------+
| hello! | hello! |
| aa! | aa ! |
+----------------+-----------------+
2 rows in set (0.05 sec)

mysql> #char(M),varchar(M)中的M都是限定的字符数,不是字节数.
mysql> insert into test ('中国人','华夏民族源头');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near ''中国人','华夏民族源头')' at 
line 1
mysql> insert into test values ('中国人','华夏民族源头');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+--------+--------------+
| ca | vca |
+--------+--------------+
| hello | hello |
| aa | aa |
| 中国人 | 华夏民族源头 |
+--------+--------------+
3 rows in set (0.00 sec)

mysql> #还有一种错误认识,既然是字符，6个utf8中文,18个字节.
mysql> # 我存两个单词不成问题吧
mysql> insert into test values ('hello world','pretty women');
ERROR 1406 (22001): Data too long for column 'ca' at row 1
mysql> # text 文本类型,一般用来储存文章内容,新闻内容等.
mysql> # 声明text列时,不必给默认值.
mysql> create table test2 (
-> artice text not null default ''
-> );
ERROR 1101 (42000): BLOB/TEXT column 'artice' can't have a default value
mysql> create table test2 (
-> article text
-> );
Query OK, 0 rows affected (0.22 sec)

mysql> #blob的意义
mysql> alter table test2 (
-> img blob
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near '(
img blob
)' at line 1
mysql> alter table test2 add img blob;
Query OK, 0 rows affected (0.56 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test2;
+---------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------+------+-----+---------+-------+
| article | text | YES | | NULL | |
| img | blob | YES | | NULL | |
+---------+------+------+-----+---------+-------+
2 rows in set (0.06 sec)

mysql> insert into test2 values ('秦穷卖马','张飞赶驴');
Query OK, 1 row affected (0.08 sec)

mysql> select * from test2;
+----------+----------+
| article | img |
+----------+----------+
| 秦穷卖马 | 张飞赶驴 |
+----------+----------+
1 row in set (0.00 sec)

mysql> exit

 

燕十八老师太幽默了, 昨天的视频如下:

http://www.tudou.com/programs/view/8dNZsY-RUkk/