【002】Linux MySQL 学习笔记-数据类型与约束

DeusExMachina_V

已于 2022-05-12 12:08:50 修改

阅读量320

点赞数

分类专栏： Linux Mysql学习笔记文章标签： mysql linux 学习

于 2022-05-11 15:18:18 首次发布

本文链接：https://blog.csdn.net/DeusExMachina_V/article/details/124709048

版权

Linux Mysql学习笔记专栏收录该内容

7 篇文章 0 订阅

订阅专栏

Linux MySQL 学习笔记-数据类型与约束

1 数据类型
2 约束
3 练习
Reference

1 数据类型

数据类型用来约束你能够存放在数据库中的数据内容

数据类型的分类
1、数值类型
2、字符串类型
3、日期时间类型

1.1 数值类型

整型

整型	存储占的字节数	有符号数范围	无符号数范围
tinyint	1B(8bits)	-128～127	0～255(2^8-1)
smallint	2B	-32768~32767	0~65535
mediumint	3B	-8388608~8388607	0~16777215
int	4B	-2147483648~2147483647	0~4294967295
bigint	8B	-9223372036854775808~9223372036854775807	0~18446744073709551615

在存储的时候，默认是有符号数的

整型可以指定显示宽度，但是并不影响取值范围。

浮点型
float(M,D) M是总长度，D小数的位数存储空间4B
double(M,D) 存储空间8B ???

   MariaDB [test]> create table num(
    -> tiny tinyint,
    -> small smallint,
    -> medium mediumint,
    -> nint  int,
    -> big bigint);

MariaDB [test]> desc num;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| tiny   | tinyint(4)   | YES  |     | NULL    |       |
| small  | smallint(6)  | YES  |     | NULL    |       |
| medium | mediumint(9) | YES  |     | NULL    |       |
| nint   | int(11)      | YES  |     | NULL    |       |
| big    | bigint(20)   | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

MariaDB [test]> select * from num;
+------+-------+---------+-----------+-----------+
| tiny | small | medium  | nint      | big       |
+------+-------+---------+-----------+-----------+
|  127 | 32767 | 8388607 | 123456789 | 123456789 |
+------+-------+---------+-----------+-----------+

无符号数例：

MariaDB [test]> create table unnum( tiny tinyint unsigned, small smallint unsigned);
MariaDB [test]> insert into unnum values (123456789,123456789);
MariaDB [test]> select * from unnum;
+------+-------+
| tiny | small |
+------+-------+
|  255 | 65535 |
+------+-------+

zerofill：零填充，补0
id

001
010
100

MariaDB [test]> create table zf (id tinyint zerofill);
MariaDB [test]> insert into zf values (1);
MariaDB [test]> insert into zf values (11);
MariaDB [test]> insert into zf values (111);
MariaDB [test]> select * from zf ;
+------+
| id   |
+------+
|  001 |
|  011 |
|  111 |
+------+

1.2 字符串类型

char 定长字符串如：char(10) 占用10个字节括号中的长度范围1～255
varchar 变长字符串 varchar(10) 按照存的字符串的长度分配括号中的长度范围0～65535
例：存字符A使用varchar会占用2个字节的存储空间
为什么是2字节？
变长字符串需要1个或2个字节存储字符串的长度

values char(4) storage varchar(4) storage
‘’ ’ ’ 4B ‘’ 1B
‘AB’ 'AB ’ 4B ‘AB’ 3B
‘ABCD’ ‘ABCD’ 4B ‘ABCD’ 5B
‘ABCDEF’ ‘ABCD’ 4B ‘ABCD’ 5B

对于char型，存储时候长度不够，默认会在字符串后面补空格，但是查询时，空格会被脱掉。

enum：枚举类型多个给定值中选择一个 -------------- 单选
set：集合类型多个给定值中选者一个或多个 -------------- 不定项选择

1.3 日期时间类型

DATE：日期型  3字节
    格式：'YYYY-MM-DD'

TIME:
    格式：'HH:MM:SS'

DATETIME
    格式：'YYYY-MM-DD HH:MM:SS'

TIMESTAMP
    格式：'YYYY-MM-DD HH:MM:SS'

YEAR
    格式：'YYYY'

    MariaDB [test]> create table time_test(
    -> d date,
    -> t time,
    -> dt datetime,
    -> ts timestamp,
    -> y year);
Query OK, 0 rows affected (0.04 sec)    

MariaDB [test]> insert into time_test values(now(),now(),now(),now(),now());

    MariaDB [test]> select * from time_test;
+------------+----------+---------------------+---------------------+------+
| d          | t        | dt                  | ts                  | y    |
+------------+----------+---------------------+---------------------+------+
| 2017-06-01 | 10:17:07 | 2017-06-01 10:17:07 | 2017-06-01 10:17:07 | 2017 |
+------------+----------+---------------------+---------------------+------+

2 约束

约束也叫完整性约束

完整性：是指数据库中存放的数据是有意义的、正确的
约束：为了保证数据的正确性和相容性，对关系模型提出的某些约束条件或者规则
注意：约束一般用于字段上

约束有哪些？

非空	唯一	默认	主键	外键	自增
not null	unique	default	primary key	foreign key	auto_increment

语法：字段名数据类型[宽度|not null|unique|default 默认值|auto_increment|

2.1 默认值

 MariaDB [test]> create table t1(name varchar(10),sex char(10) default 'male' );
    MariaDB [test]> insert into t1 values();
    Query OK, 1 row affected (0.02 sec)
    MariaDB [test]> select * from t1;
    +------+------+
    | name | sex  |
    +------+------+
    | NULL | male |
    +------+------+
    1 row in set (0.00 sec)

默认值：当用户向表中插入数据时，指定了该字段的值，那么就插入该值，否则就插入默认值

修改默认值的两种方法

  MariaDB [test]> alter table t1 modify sex varchar(10) default 'jim';

MariaDB [test]> alter table t1 alter sex set default 'tom';

2.2 非空 not null

   MariaDB [test]> select * from t1 where name is null;   #查询name字段为null的行
    MariaDB [test]> select * from t1 where name is not null;  #查询name字段为不为null的行
    MariaDB [test]> create table t2 (id int not null ,name char(10));
    MariaDB [test]> insert into t2 values();
    MariaDB [test]> select * from t2;    #会将不允许为空的id字段转换成0
    +----+------+
    | id | name |
    +----+------+
    |  0 | NULL |
    +----+------+

字段类型为字符串型，非空约束会将空值转换成空字符串

  MariaDB [test]> alter table t2 modify name char(10) not null;
    MariaDB [test]> select * from t2;
    +----+------+
    | id | name |
    +----+------+
    |  0 |      |
    +----+------+

2.3 唯一

MariaDB [test]> create table t3 (id int unique,name char (10));
MariaDB [test]> insert into t3 values ();
MariaDB [test]> insert into t3 values ();   #唯一性约束对空值无效
MariaDB [test]> select * from t3;
+------+------+
| id   | name |
+------+------+
|    1 | jim  |
|    2 | jim  |
| NULL | NULL |
| NULL | NULL |
+------+------+
4 rows in set (0.00 sec)

MariaDB [test]> insert into t3 values (1,'tom');   
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

2.4 自增 auto_increment

    要求：
 >1）该字段必须是数值型
    2）字段上要有唯一性索引或者主键

 MariaDB [test]> create table t5 (
        -> id int primary key auto_increment,name char(10));

    MariaDB [test]> desc t5;
    +-------+----------+------+-----+---------+----------------+
    | Field | Type     | Null | Key | Default | Extra          |
    +-------+----------+------+-----+---------+----------------+
    | id    | int(11)  | NO   | PRI | NULL    | auto_increment |
    | name  | char(10) | YES  |     | NULL    |                |
    +-------+----------+------+-----+---------+----------------+

    MariaDB [test]> insert into t5 values ();    #id 为1
    MariaDB [test]> insert into t5 values ();    #id 为2
    MariaDB [test]> insert into t5 values (6);  #id 为6
    MariaDB [test]> insert into t5 values ();    #id 为7

总结：
1.当自增字段发生断档时，值会从最大值继续自增
2.当delete删除最大值时，下一个值仍然从删除之前的最大值继续自增
3.当truncate表时，值从1开始重新计算         DDL

2.5 主键

    主键是表中的特殊字段，这个字段能够唯一标识表中的每一条记录。
    一张表只能有一个主键
    主键的用途：快速定位数据
    主键要满足的条件：非空且唯一
    primary key == not null  + unique

    1）使用单个字段做主键
        a、在字段后直接指定主键约束(列级约束，默认值为null)

 MariaDB [test]> create table t6 (id int primary key,age int , name char(10));
            MariaDB [test]> desc t6;
            +-------+----------+------+-----+---------+-------+
            | Field | Type     | Null | Key | Default | Extra |
            +-------+----------+------+-----+---------+-------+
            | id    | int(11)  | NO   | PRI | NULL    |       |
            | age   | int(11)  | YES  |     | NULL    |       |
            | name  | char(10) | YES  |     | NULL    |       |
            +-------+----------+------+-----+---------+-------+
            3 rows in set (0.00 sec)

            MariaDB [test]> insert into t6 values (1,15,'jim');
            MariaDB [test]> insert into t6 values (1,16,'tom');
                ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
                
            MariaDB [test]> insert into t6 values();
            MariaDB [test]> insert into t6 values();
                ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

        b、整张表的所有字段都定义完成之后再来指定主键(表级约束，默认值是0)
            MariaDB [test]> create table t8 ( id int,name char(10),primary key (id));
            MariaDB [test]> desc t8;
            +-------+----------+------+-----+---------+-------+
            | Field | Type     | Null | Key | Default | Extra |
            +-------+----------+------+-----+---------+-------+
            | id    | int(11)  | NO   | PRI | 0       |       |
            | name  | char(10) | YES  |     | NULL    |       |
            +-------+----------+------+-----+---------+-------+
            MariaDB [test]> insert into t8 values ();
            MariaDB [test]> insert into t8 values ();
            ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

            MariaDB [test]> insert into t8 values (1,'jim');
            MariaDB [test]> insert into t8 values (1,'tom');
            ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

    2）多个字段做联合主键    
        注意：联合主键只能在所有字段都定义完成之后，才能定义主键。
        MariaDB [test]> desc mysql.user\G;    #host和user字段做联合主键

  ***************** 1. row ************
          Field: Host
           Type: char(60)
           Null: NO
            Key: PRI
        Default: 
          Extra: 
        ****************** 2. row ************
          Field: User
           Type: char(16)
           Null: NO
            Key: PRI
        Default: 
          Extra: 
        ******************* 3. row ************
          Field: Password
           Type: char(41)
           Null: NO
            Key: 
        Default: 
          Extra: 

        HOST           NAME   PASSWORD
        127.0.0.1     root      123
        127.0.0.1     tom      123
        127.0.0.1     root      456
        192.168.1.2 root      456

        MariaDB [test]> create table t8 ( HOST char(15),NAME char(10),PASSWORD char(50) ,primary key (HOST,NAME));

2.6 外键 foreign key

外键：一个表的数据依赖另一张表的主键列的数据，如果在主键列没有出现的值，是不能够出现在外键字段的。

创建外键的条件：
1）存储引擎必须是innodb
2）相关联字段数据类型要一致
3）最好在外键列上建索引(目的是为了减少扫描范围，不创建也可以，值是影响性能)

例：
class 班级表（父表）

ID	Class
1	yun 0215
2	yun 0308
3	yun 0316
4	yun 0411

student (子表)

CLASS_ID	NAME	AGE
1	jim	18
1	tom	19
2	xiao	20
2	ming	21
5	~~hong~~	19

创建父表

MariaDB [test]> create table class (ID int primary key,class char(20));

创建子表

MariaDB [test]> create table student (CLASS_ID int,NAME char(20),AGE int,foreign key(CLASS_ID)  references class(ID));

向父表内插入数据

MariaDB [test]> insert into class values(1,'yun0215'),
            -> (2,'yun0308'),
            -> (3,'yun0316'),
            -> (4,'yun0411');
        MariaDB [test]> select * from class;
        +----+---------+
        | ID | class   |
        +----+---------+
        |  1 | yun0215 |
        |  2 | yun0308 |
        |  3 | yun0316 |
        |  4 | yun0411 |
        +----+---------+

向子表内插入数据

  MariaDB [test]> insert into student values 
            -> (1,'jim',18),
            -> (1,'tom',19),
            -> (2,'xiao',20);

MariaDB [test]> insert into student values (5,'hong',19);   #插入父表中不存在的班级号
    ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`CLASS_ID`) REFERENCES `class` (`ID`))

MariaDB [test]> delete from class where ID=1;   #删除父表中有外键依赖的数据
            ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`CLASS_ID`) REFERENCES `class` (`ID`))

总结：

子表中的关联数据依赖于父表，不能向子表中插入父表中不存在的值
2）不能删除父表中被子表所依赖的记录
删除父表中被依赖的行的方法：
1）删除外键约束
2）指定级联操作的选项

on delete cascade 级联删除
on update cascade 级联更新

 MariaDB [test]> drop table student;
    
    MariaDB [test]> create table student (CLASS_ID int ,NAME char(20),AGE int,foreign key(CLASS_ID) references class (ID) on delete cascade on update cascade);
    
    MariaDB [test]> insert into student values 
        -> (1,'jim',18), 
        -> (1,'tom',19),
        -> (2,'xiao',20),
        -> (2,'ming',21);
        
    MariaDB [test]> select * from student;
    +----------+------+------+
    | CLASS_ID | NAME | AGE  |
    +----------+------+------+
    |        1 | jim  |   18 |
    |        1 | tom  |   19 |
    |        2 | xiao |   20 |
    |        2 | ming |   21 |
    +----------+------+------+
    
    MariaDB [test]> delete from class where ID=2;
    
    MariaDB [test]> select * from class;
    +----+---------+
    | ID | class   |
    +----+---------+
    |  1 | yun0215 |
    |  3 | yun0316 |
    |  4 | yun0411 |
    +----+---------+
    
    MariaDB [test]> select * from student;
    +----------+------+------+
    | CLASS_ID | NAME | AGE  |
    +----------+------+------+
    |        1 | jim  |   18 |
    |        1 | tom  |   19 |
    +----------+------+------+
    
    MariaDB [test]> update class set ID=2 where ID=1;

    MariaDB [test]> select * from class;
    +----+---------+
    | ID | class   |
    +----+---------+
    |  2 | yun0215 |
    |  3 | yun0316 |
    |  4 | yun0411 |
    +----+---------+
    
    MariaDB [test]> select * from student;
    +----------+------+------+
    | CLASS_ID | NAME | AGE  |
    +----------+------+------+
    |        2 | jim  |   18 |
    |        2 | tom  |   19 |
    +----------+------+------+

总结：有了级联删除和级联更新选项，父表中的数据发生删除或者更新时，子表中相关数据也会发生相应的变化。

删除外键
语法：

alter table 表名 drop foreign key 外键的名字

查看外键的名字

  MariaDB [test]> show create table student\G;   #红色字体为外键的名字
            **************** 1. row **************
                   Table: student
            Create Table: CREATE TABLE `student` (
              `CLASS_ID` int(11) DEFAULT NULL,
              `NAME` char(20) DEFAULT NULL,
              `AGE` int(11) DEFAULT NULL,
              KEY `CLASS_ID` (`CLASS_ID`),
              CONSTRAINT `student_ibfk_1` FOREIGN KEY (`CLASS_ID`) REFERENCES `class` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1
            1 row in set (0.03 sec)

            ERROR: No query specified

    MariaDB [test]> alter table student drop foreign key student_ibfk_1; 

    MariaDB [test]> show create table student\G; 
    *************** 1. row ***************
           Table: student
    Create Table: CREATE TABLE `student` (
      `CLASS_ID` int(11) DEFAULT NULL,
      `NAME` char(20) DEFAULT NULL,
      `AGE` int(11) DEFAULT NULL,
      KEY `CLASS_ID` (`CLASS_ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)

3 练习

在qiangfeng库中创建一个表 stu_info ,有如下字段:
stu_id 整型,数据不能为空,不能重复. 学生人数最多4万左右
stu_name 字符串,学生名字为2-16个英文字符,不能为空
sex 字符串,性别为male或female,不能为空
phone 手机号码,国内手机号码为11位数字,不能为空
QQ QQ号, 5-11位数字,可以为空

创建此表,并使用合适的数据类型及约束

use qianfeng
create table stu_info (
stu_id mediumint primary key,
stu_name char(16) not null,
sex enum('male','female') not null,
phone bigint not null,
QQ bigint);

在qiangfeng库中创建一个表 score, 有如下字段
id 整型, 只有在 stu_info 存在的数据才可以insert进来
linux 整型, 此字段及以下字段内容都是0-100的数字
shell 整型
mysql 整型

create table score (
id smallint,
linux int,
shell int,
mysql int,
foreign key(id) reference stu_info(stu_id));

往以上两表中导入数据.
stu_id从1开始,依次递增
stu_name为系统用户所有的用户名
sex随机
phone随机
QQ随机
linux,shell,mysql随机,范围为0-100

#!/bin/bash
i=1
while read line;do
        stu_id=$i
        let i++
        stu_name=$(echo $line|awk -F: '{print $1}')
        shuiji=$[RANDOM%2]
        [ $shuiji -eq 0 ] && sex='male' || sex='female'
        phone=$[RANDOM% 10000+ 10000]$[RANDOM% 99999+ 10000]$[RANDOM% 9+1]
        QQ=$[RANDOM% 900+100]$[RANDOM%10000]$[RANDOM% 10000]
        linux=$[RANDOM% 101]
        shell=$[RANDOM% 101]
        mysql=$[RANDOM% 101]
        echo $stu_id$stu_name$sex$phone$QQ$linux$shell$mysql
done < /etc/passwd

方法2

#!/bin/bash
j=1
sx=(female male)
ph=(13 14 15 17 18 19)
for i in $(awk -F: '{print $1}' /etc/passwd);do
        stu_name=$i
        stu_id=$j
        sex=${sx[RANDOM%2]}
        phone=${ph[RANDOM%6]}$[RANDOM%90000+10000]$[RANDOM%9000+1000]
       
        #生成随机的QQ
        n5=$[RANDOM%9+1]$[RANDOM%10]$[RANDOM%10]$[RANDOM%10]$[RANDOM%10]
        n6=$n5$[RANDOM%10]
        n7=$n6$[RANDOM%10]
        n8=$n7$[RANDOM%10]
        n9=$n8$[RANDOM%10]
        n10=$n9$[RANDOM%10]
        n11=$n10$[RANDOM%10]
        qq=($n5 $n6 $n7 $n8 $n9 $n10 $n11)
        k=$[RANDOM%7]
        QQ=${qq[k]}
       
        linux=$[RANDOM%101]
        shell=$[RANDOM%101]
        mysql=$[RANDOM%101]
        mysql -e "insert into qianfeng.stu_info values ($stu_id,'$stu_name','$sex',$phone,$QQ);"
        mysql -e "insert into qianfeng.score values ($stu_id,$linux,$shell,$mysql);"
        let j++
done

方法3

#!/bin/bash
i=1
while read line;do
    stu_name=`echo $line|awk -F: '{print $1}'`
    stu_id=$i
    let i++
    [ $[RANDOM%2] -eq 1 ] && sex="male" || sex="female"
    a=$[RANDOM%7+3]
    b=
    for((j=1;j<=9;j++));do
        b=$b$[RANDOM%10]
    done
    phone=1$a$b
    qqa=$[RANDOM%7+4]
    qqb=$[RANDOM%8+1]
    qqc=
    for c in `seq $qqa`;do
        qqc=$qqc$[RANDOM%10]
    done
    qq=$qqb$qqc
    #mysql -p1 -e "insert into qianfeng.stu_info values($stu_id,'$stu_name','$sex','$phone','$qq')"
    linux=$[RANDOM%101]
    shell=$[RANDOM%101]
    mysql=$[RANDOM%101]
    mysql -p1 -e "insert into qianfeng.score values($stu_id,$linux,$shell,$mysql)"
done < /etc/passwd

创建一个表stu_score,内容为所有学生的id,姓名及各科的分数

create table stu_score select * from score;
alter table stu_score add stu_name varchar(16) after id;
insert into stu_score(stu_name) select stu_name from stu_info;