【2020Python修炼记】MySQL之 表相关操作

【目录】 一 存储引擎介绍 二 表介绍 三 创建表 四 查看表结构 五 数据类型 1、介绍 2、数值类型 3、日期类型 4、字符串类型 5、枚举类型与集合类型 六 表完整性约束...

【目录】

一 存储引擎介绍

二 表介绍

三 创建表

四 查看表结构

五 数据类型

1、介绍

2、数值类型

3、日期类型

4、字符串类型

5、枚举类型与集合类型

六 表完整性约束

1、介绍

2、not null与default

3、 unique

4、primary key

5、auto_increment

6、foreign key

七 修改表ALTER TABLE

八 复制表

九 删除表

 

一 、存储引擎介绍

1、什么是存储引擎

 日常生活中文件格式有很多中,并且针对不同的文件格式会有对应不同存储方式和处理机制(txt,pdf,word,mp4...)

针对不同的数据应该有对应的不同的处理机制来存储

存储引擎就是不同的处理机制

2、MySQL主要存储引擎

  • Innodb

    是MySQL5.5版本及之后默认的存储引擎

    存储数据更加的安全

  • myisam

    是MySQL5.5版本之前默认的存储引擎

    速度要比Innodb更快 但是我们更加注重的是数据的安全

  • memory

    内存引擎(数据全部存放在内存中) 断电数据丢失

  • blackhole

    无论存什么,都立刻消失(黑洞)

3、SQL语句查看存储引擎

 # 查看所有的存储引擎

show engines;

# 不同的存储引擎在存储表的时候 异同点
create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=blackhole;
create table t4(id int) engine=memory;

# 存数据
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);

二 、表介绍

 表相当于文件,表中的一条记录就相当于文件的一行内容,不同的是,表中的一条记录有对应的标题,称为表的字段。

三 、创建表

 # 语法

create table 表名(
字段名1 类型(宽度) 约束条件,
字段名2 类型(宽度) 约束条件,
字段名3 类型(宽度) 约束条件
)

# 注意
1 在同一张表中字段名不能重复

2 宽度和约束条件是可选的(可写可不写) 而字段名和字段类型是必须的
约束条件写的话 也支持写多个
字段名1 类型(宽度) 约束条件1 约束条件2...,
create table t5(id); 报错

3 最后一行不能有逗号
create table t6(
id int,
name char,
); 报错

 

"""补充"""
# 宽度
一般情况下指的是对存储数据的限制
create table t7(name char); 默认宽度是1
insert into t7 values('jason');
insert into t7 values(null); 关键字NULL


针对不同的版本会出现不同的效果
5.6版本默认没有开启严格模式 规定只能存一个字符你给了多个字符,那么我会自动帮你截取
5.7版本及以上或者开启了严格模式 那么规定只能存几个 就不能超,一旦超出范围立刻报错 Data too long for ....
"""严格模式到底开不开呢?"""
MySQL5.7之后的版本默认都是开启严格模式的
使用数据库的准则:
能尽量少的让数据库干活就尽量少 不要给数据库增加额外的压力

# 约束条件 null not null不能插入null
create table t8(id int,name char not null);

"""
宽度和约束条件到底是什么关系
宽度是用来限制数据的存储
约束条件是在宽度的基础之上增加的额外的约束
"""

四 、查看表结构

describe t1; #查看表结构,可简写为desc 表名

show create table t1G; #查看表详细结构,可加G

五 、数据类型

(一)数值类型

1、整型

  • 分类

    TINYINT SMALLINT MEDUIMINT INT BIGINT

  • 作用

    存储年龄、等级、id、号码等等

"""
以TINYINT 
    是否有符号
        默认情况下是带符号的
    超出会如何
        超出限制只存最大可接受值
"""
create table t9(id tinyint);
insert into t9 values(-129),(256);

# 约束条件之unsigned 无符号
create table t10(id tinyint unsigned);


create table t11(id int);
# int默认也是带符号的  
# 整型默认情况下都是带有符号的

# 针对整型 括号内的宽度到底是干嘛的
create table t12(id int(8));
insert into t12 values(123456789);

"""
特例:只有整型括号里面的数字不是表示限制位数
id int(8)
    如果数字没有超出8位 那么默认用空格填充至8位
    如果数字超出了8位 那么有几位就存几位(但是还是要遵守最大范围)
"""
create table t13(id int(8) unsigned zerofill);
# 用0填充至8位

# 总结:
针对整型字段 括号内无需指定宽度 因为它默认的宽度以及足够显示所有的数据了
整型类型 

严格模式

# 如何查看严格模式
show variables like "%mode";

模糊匹配/查询
    关键字 like
        %:匹配任意多个字符
        _:匹配任意单个字符

# 修改严格模式
    set session  只在当前窗口有效
    set global   全局有效
    
    set global sql_mode = 'STRICT_TRANS_TABLES';
    
    修改完之后 重新进入服务端即可

2、浮点型

  • 分类

    FLOAT、DOUBLE、DECIMAL

  • 作用

    身高、体重、薪资

# 存储限制
float(255,30)  # 总共255位 小数部分占30位
double(255,30)  # 总共255位 小数部分占30位
decimal(65,30)  # 总共65位 小数部分占30位

# 精确度验证
create table t15(id float(255,30));
create table t16(id double(255,30));
create table t17(id decimal(65,30));
"""你们在前期不要给我用反向键 所有的命令全部手敲!!!增加熟练度"""

insert into t15 values(1.111111111111111111111111111111);
insert into t16 values(1.111111111111111111111111111111);
insert into t17 values(1.111111111111111111111111111111);

float < double < decimal
# 要结合实际应用场景 三者都能使用

(三)其他类型

3、日期类型

 分类:

date:年月日 2020-5-4

datetime:年月日时分秒 2020-5-4 11:11:11

time:时分秒11:11:11

Year:2020

create table student(
    id int,
    name varchar(16),
    born_year year,
    birth date,
    study_time time,
    reg_time datetime
);
insert into student values(1,'egon','1880','1880-11-11','11:11:11','2020-11-11 11:11:11');

4、字符串类型

"""
char
    定长
    char(4)     数据超过四个字符直接报错 不够四个字符空格补全
varchar
    变长
    varchar(4)  数据超过四个字符直接报错 不够有几个存几个
"""
create table t18(name char(4));
create table t19(name varchar(4));

insert into t18 values('a');
insert into t19 values('a');

# 介绍一个小方法 char_length统计字段长度
select char_length(name) from t18;
select char_length(name) from t19;
"""
首先可以肯定的是 char硬盘上存的绝对是真正的数据 带有空格的
但是在显示的时候MySQL会自动将多余的空格剔除
"""

# 再次修改sql_mode 让MySQL不要做自动剔除操作
set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
分类
"""
char
    缺点:浪费空间
    优点:存取都很简单
        直接按照固定的字符存取数据即可
        jason egon alex wusir tank 
        存按照五个字符存 取也直接按照五个字符取
        
varchar
    优点:节省空间
    缺点:存取较为麻烦
        1bytes+jason 1bytes+egon 1bytes+alex 1bytes+tank 
        
        存的时候需要制作报头
        取的时候也需要先读取报头 之后才能读取真实数据
        
以前基本上都是用的char 其实现在用varchar的也挺多
"""

补充:
    进来公司之后你完全不需要考虑字段类型和字段名
    因为产品经理给你发的邮件上已经全部指明了
char与varchar对比

5、枚举类型与集合类型

 分类:

"""
枚举(enum) :多选一
集合(set):   多选多
"""

create table user(
    id int,
    name char(16),
    gender enum('male','female','others')
);
insert into user values(1,'jason','male');  正常
insert into user values(2,'egon','xxxxooo');  报错
# 枚举字段 后期在存数据的时候只能从枚举里面选择一个存储 


create table teacher(
    id int,
    name char(16),
    gender enum('male','female','others'),
    hobby set('read','DBJ','hecha')
);
insert into teacher values(1,'jason','male','read');  正常
insert into teacher values(2,'egon','female','DBJ,hecha');  正常
insert into teacher values(3,'tank','others','生蚝'); 报错
# 集合可以只写一个  但是不能写没有列举的
具体使用

六 、表完整性约束

1、介绍

约束条件与数据类型的宽度一样,都是可选参数

作用:用于保证数据的完整性和一致性 主要分为:

PRIMARY KEY (PK) 标识该字段为该表的主键,可以唯一的标识记录

FOREIGN KEY (FK) 标识该字段为该表的外键

NOT NULL 标识该字段不能为空

UNIQUE KEY (UK) 标识该字段的值是唯一的

AUTO_INCREMENT 标识该字段的值自动增长(整数类型,而且为主键)

DEFAULT 为该字段设置默认值

UNSIGNED 无符号

ZEROFILL 使用0填充

说明:

1. 是否允许为空,默认NULL,可设置NOT NULL,字段不允许为空,必须赋值

2. 字段是否有默认值,缺省的默认值是NULL,如果插入记录时不给字段赋值,此字段使用默认值

sex enum('male','female') not null default 'male'

age int unsigned NOT NULL default 20   #必须为正值(无符号) 不允许为空 默认是20

3. 是否是key

主键 primary key

外键 foreign key

索引 (index,unique...)

 

2、not null与default

是否可空,null表示空,非字符串 not null - 不可空 null - 可空

默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值

create table tb1( nid int not null defalut 2, num int not null ) 

3、 unique

============设置唯一约束 UNIQUE===============
方法一:
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);


方法二:
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)
);


mysql> insert into department1 values(1,'IT','技术');
Query OK, 1 row affected (0.00 sec)
mysql> insert into department1 values(1,'IT','技术');
ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'
设置唯一约束 UNIQUE
mysql> create table t1(id int not null unique);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
not null+unique的化学反应
create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);

mysql> insert into service values
    -> (1,'nginx','192.168.0.10',80),
    -> (2,'haproxy','192.168.0.20',80),
    -> (3,'mysql','192.168.0.30',3306)
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'
联合唯一

4、primary key

 从约束角度看primary key字段的值不为空且唯一,那我们直接使用not null+unique不就可以了吗,要它干什么?

主键primary key是innodb存储引擎组织数据的依据,innodb称之为索引组织表,一张表中必须有且只有一个主键。

一个表中可以:

单列做主键 多列做主键(复合主键)

============单列做主键===============
#方法一:not null+unique
create table department1(
id int not null unique, #主键
name varchar(20) not null unique,
comment varchar(100)
);

mysql> desc department1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   | UNI | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

#方法二:在某一个字段后用primary key
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);

mysql> desc department2;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.00 sec)

#方法三:在所有字段后单独定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id); #创建主键并为其命名pk_name

mysql> desc department3;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)
单列主键
==================多列做主键================
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);


mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip           | varchar(15) | NO   | PRI | NULL    |       |
| port         | char(5)     | NO   | PRI | NULL    |       |
| service_name | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into service values
    -> ('172.16.45.10','3306','mysqld'),
    -> ('172.16.45.11','3306','mariadb')
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
多列主键

5、auto_increment

 约束字段为自动增长,被约束的字段必须同时被key约束

#不指定id,则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | male    |                |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values
    -> ('egon'),
    -> ('alex')
    -> ;

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
|  2 | alex | male |
+----+------+------+


#也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  1 | egon | male   |
|  2 | alex | male   |
|  4 | asb  | female |
|  7 | wsb  | female |
+----+------+--------+


#对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values('ysb');
mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  8 | ysb  | male |
+----+------+------+

#应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name) values('egon');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
+----+------+------+
1 row in set (0.00 sec)
auto_increment

6、foreign key

 外键就是用来帮助我们建立表与表之间关系的

foreign key

 表的关系

表与表之间最多只有四种关系
一对多关系
在MySQL的关系中没有多对一一说
一对多 多对一 都叫一对多!!!
多对多关系
一对一关系
没有关系

"""
判断表与表之间关系的时候 前期不熟悉的情况下 一定要按照我给你的建议
换位思考  分别站在两张表的角度考虑

员工表与部门表为例
    先站在员工表
        思考一个员工能否对应多个部门(一条员工数据能否对应多条部门数据)
            不能!!!
            (不能直接得出结论 一定要两张表都考虑完全)
    再站在部门表
        思考一个部门能否对应多个员工(一个部门数据能否对应多条员工数据)
            能!!!
    得出结论
        员工表与部门表示单向的一对多
        所以表关系就是一对多
"""
foreign key
    1 一对多表关系   外键字段建在多的一方
    2 在创建表的时候 一定要先建被关联表 
    3 在录入数据的时候 也必须先录入被关联表
# SQL语句建立表关系
create table dep(
    id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);

create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);
insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);


# 修改dep表里面的id字段
update dep set id=200 where id=2;  不行
# 删除dep表里面的数据
delete from dep;  不行

# 1 先删除教学部对应的员工数据 之后再删除部门
    操作太过繁琐
    
# 2 真正做到数据之间有关系
    更新就同步更新
    删除就同步删除
"""
级联更新   >>>   同步更新
级联删除   >>>   同步删除
"""
create table dep(
    id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);

create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);
insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);
一对多
"""
图书表和作者表
"""
create table book(
    id int primary key auto_increment,
    title varchar(32),
    price int,
    author_id int,
    foreign key(author_id) references author(id) 
    on update cascade  # 同步更新
    on delete cascade  
                        
  • 发表于 2020-05-05 20:33
  • 阅读 ( 148 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除