想必大家在学校也学习过MySQL,可能学的懵懵懂懂,这个板块我们从入门开始,从最新的安装MySQL到学习MySQL语句,一步一步开始,一切都是新的,新的板块新的开始,大家一起努力,一起进步!!!
学习【MySQL】数据类型咱们按照下面的图解:
语法:
create table t1(num tinyint);
使用:
数值越界测试:
mysql> insert into t1 values (127);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (128);
1264 - Out of range value for column 'num' at row 1
mysql> insert into t1 values (-128);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (100);
Query OK, 1 row affected (0.00 sec)
注意:
语法:
位字段类型。M表示每个值的位数,范围从1到64。如果M被忽略,默认为1。
使用:
mysql> create table if not exists t3(
-> id int,
-> online bit(1)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t3 (id,online) values (1,0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 (id,online) values (2,1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t3 (id,online) values (3,2);
ERROR 1406 (22001): Data too long for column 'online' at row 1
mysql> select* from t3;
+------+--------+
| id | online |
+------+--------+
| 1 | |
| 2 | |
+------+--------+
解释:
这里对于插入越界的数据依旧是拦截不让插入,还有这里的0/1不显示,这是因为bit字段在显示时,是按照ASCII码对应的值显示,这里的0/1对应的ASCII码是不显示的。
修改后:
create table t4(name varchar(32), gender bit(1));
语法:
float[(m, d)] [unsigned] : M指定显示长度,d指定小数位数,占用空间4个字节。
举个栗子:float(4,2)表示的范围是-99.99 ~ 99.99,MySQL在保存值时会进行四舍五入
mysql> create table if not exists t4(
-> id int,
-> salary float(4,2)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t4 (id,salary) values (1,-99.99);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t4 (id,salary) values (2,99.99);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t4 (id,salary) values (2,23.145);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t4 (id,salary) values (2,-99.994);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t4 (id,salary) values (2,-99.995);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t4 (id,salary) values (2,99.994);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t4 (id,salary) values (2,99.995);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> select* from t4;
+------+--------+
| id | salary |
+------+--------+
| 1 | -99.99 |
| 2 | 99.99 |
| 2 | 23.14 |
| 2 | -99.99 |
| 2 | 99.99 |
+------+--------+
说明:
语法:
decimal(m, d) [unsigned] : 定点数m指定长度,d表示小数点的位数
decimal(5,2) 表示的范围是 -999.99 ~ 999.99
使用:
mysql> create table if not exists t5(
-> id int,
-> s1 float(10,8),
-> s2 decimal(10,8)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t5 (id,s1,s2) values (1,23.12345612,23.12345612);
Query OK, 1 row affected (0.00 sec)
mysql> select* from t5;
+------+-------------+-------------+
| id | s1 | s2 |
+------+-------------+-------------+
| 1 | 23.12345695 | 23.12345612 |
+------+-------------+-------------+
说明:
语法:
固定长度字符串,L是可以存储的长度,单位为字符,最大长度值可以为255。
在utf8编码当中,一个汉字为一个字符,一个字符为三字节
举个栗子:
mysql> create table t6(
-> id int,
-> name char(3)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t6 (id,name) values (1,'中');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t6 (id,name) values (1,'中国');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t6 (id,name) values (1,'中国人');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t6 (id,name) values (1,'中国人民');
ERROR 1406 (22001): Data too long for column 'name' at row 1
语法:
可变长度字符串,L表示字符长度,最大长度65535个字节
举个栗子:
mysql> create table tt11(name varchar(21845))charset=utf8; --验证了utf8确实是不能超过21844
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type,
not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
mysql> create table tt11(name varchar(21844)) charset=utf8;
Query OK, 0 rows affected (0.01 sec)
解释:
图解:
如何选择定长或变长字符串?
分类:
举个栗子:
mysql> create table t7(
-> t1 date,
-> t2 datetime,
-> t3 timestamp
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t7 (t1,t2) values ('2003-08-15','2008-08-10 12:01:01');
Query OK, 1 row affected (0.01 sec)
mysql> select* from t7;
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
| 2003-08-15 | 2008-08-10 12:01:01 | 2024-09-13 09:33:34 |
+------------+---------------------+---------------------+
1 row in set (0.01 sec)
mysql> update t7 set t1='1999-12-31'; #更新数据;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select* from t7;
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
| 1999-12-31 | 2008-08-10 12:01:01 | 2024-09-13 09:34:30 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> create table if not exists t8(
-> context text,
-> time timestamp
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t8 (context) values ('中国人民');
Query OK, 1 row affected (0.00 sec)
mysql> select* from t8;
+--------------+---------------------+
| context | time |
+--------------+---------------------+
| 中国人民 | 2024-09-13 09:36:39 |
+--------------+---------------------+
1 row in set (0.00 sec)
mysql> update t8 set context='中国人民站起来了'; #更新数据
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select* from t8;
+--------------------------+---------------------+
| context | time |
+--------------------------+---------------------+
| 中国人民站起来了 | 2024-09-13 09:37:27 |
+--------------------------+---------------------+
1 row in set (0.00 sec)
解释:
举个栗子:
mysql> create table if not exists t9(
-> name varchar(20),
-> gender enum('男','女'),
-> hobby set('代码','羽毛球','乒乓球','足球')
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t9 (name,gender,hobby) values ('刘备','男','代码');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t9 (name,gender,hobby) values ('孙尚香','女','代码,羽毛球');
Query OK, 1 row affected (0.00 sec)
#插入enum和set中不存在的值就会报错
mysql> insert into t9 (name,gender,hobby) values ('刘禅','unknown','代码,羽毛球');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> insert into t9 (name,gender,hobby) values ('刘禅','unknown','跑步');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
#enum在插入时可以写枚举常量值,也可以写常量对应下标(从1开始)
mysql> insert into t9 (name,gender,hobby) values ('曹操',1,'代码');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t9 (name,gender,hobby) values ('貂蝉',2,'代码');
Query OK, 1 row affected (0.00 sec)
mysql> select* from t9;
+-----------+--------+------------------+
| name | gender | hobby |
+-----------+--------+------------------+
| 刘备 | 男 | 代码 |
| 孙尚香 | 女 | 代码,羽毛球 |
| 曹操 | 男 | 代码 |
| 貂蝉 | 女 | 代码 |
+-----------+--------+------------------+
#set值在用数字时,是按照数字二进制bit位是否为1进行的
mysql> insert into t9 (name,gender,hobby) values ('孙权',1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t9 (name,gender,hobby) values ('周瑜',1,2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t9 (name,gender,hobby) values ('赵云',1,3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t9 (name,gender,hobby) values ('张飞',1,4);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t9 (name,gender,hobby) values ('关羽',1,5);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t9 (name,gender,hobby) values ('黄忠',1,6);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t9 (name,gender,hobby) values ('马超',1,15);
Query OK, 1 row affected (0.01 sec)
mysql> select* from t9;
+-----------+--------+-----------------------------------+
| name | gender | hobby |
+-----------+--------+-----------------------------------+
| 刘备 | 男 | 代码 |
| 孙尚香 | 女 | 代码,羽毛球 |
| 曹操 | 男 | 代码 |
| 貂蝉 | 女 | 代码 |
| 孙权 | 男 | 代码 |
| 周瑜 | 男 | 羽毛球 |
| 赵云 | 男 | 代码,羽毛球 |
| 张飞 | 男 | 乒乓球 |
| 关羽 | 男 | 代码,乒乓球 |
| 黄忠 | 男 | 羽毛球,乒乓球 |
| 马超 | 男 | 代码,羽毛球,乒乓球,足球 |
+-----------+--------+-----------------------------------+
查找:
查询爱好中包含乒乓球的人:
mysql> select* from t9 where find_in_set('乒乓球',hobby);
+--------+--------+-----------------------------------+
| name | gender | hobby |
+--------+--------+-----------------------------------+
| 张飞 | 男 | 乒乓球 |
| 关羽 | 男 | 代码,乒乓球 |
| 黄忠 | 男 | 羽毛球,乒乓球 |
| 马超 | 男 | 代码,羽毛球,乒乓球,足球 |
+--------+--------+-----------------------------------+
今天内容就到这里啦,时间过得很快,大家沉下心来好好学习,会有一定的收获的,大家多多坚持,嘻嘻,成功路上注定孤独,因为坚持的人不多。那请大家举起自己的小手给博主一键三连,有你们的支持是我最大的动力???,回见。