当前位置：首页 >数据库 > python--MySQL 库,表的详细操作

python--MySQL 库,表的详细操作

数据库

一库操作

　　数据库命名规则

　　　　可以由数字,字母,下划线,@, #, $

　　　　区分大小写

　　　　唯一性

　　　　不能使用关键字如 create select

　　　　不能单独使用数字

　　　　最长128位

　　　　# 这些是对上次的补充.

二表操作

　　1. MySQL的基础数据类型

　　　　简单介绍一下:

python--MySQL 库,表的详细操作 _ JavaClub全栈架构师技术笔记

#1. 数字：整型：tinyinit  int  bigint小数：float ：在位数比较短的情况下不精准double ：在位数比较长的情况下不精准0.000001230123123123存成：0.000001230000decimal：（如果用小数，则用推荐使用decimal）精准内部原理是以字符串形式去存#2. 字符串：char（10）：简单粗暴，浪费空间，存取速度快root存成root000000varchar：精准，节省空间，存取速度慢sql优化：创建表时，定长的类型往前放，变长的往后放比如性别比如地址或描述信息>255个字符，超了就把文件路径存放到数据库中。比如图片，视频等找一个文件服务器，数据库中只存路径或url。#3. 时间类型：最常用：datetime#4. 枚举类型与集合类型

　　在这里介绍一下枚举个集合类型的用法

mysql> create table consumer( -> name varchar(50),-> sex enum('male','female'),-> level enum('vip1','vip2','vip3','vip4','vip5'), #在指定范围内，多选一-> hobby set('play','music','read','study') #在指定范围内，多选多-> );mysql> insert into consumer values  -> ('xiaogui','male','vip5','read,study'),-> ('taibai','female','vip1','music');mysql> select * from consumer;+------+--------+-------+------------+| name | sex| level | hobby  |+------+--------+-------+------------+| xiaogui | male| vip5  | read,study || taibai | female| vip1 |music|+------+--------+-------+------------+

　　2.MySQL 完整性约束

　　　　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 必须为正值（无符号） 不允许为空 默认是203. 是否是key主键 primary key外键 foreign key索引 (index,unique...)

　　　　2. not null 与 default

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

　　　　　　not null - 不可空

　　　　　　null - 可空

　　　　下面我们测试一下

==================not null====================mysql> create table t1(id int); #id字段默认可以插入空mysql> desc t1;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | YES  | | NULL||+-------+---------+------+-----+---------+-------+mysql> insert into t1 values(); #可以插入空mysql> create table t2(id int not null); #设置字段id不为空mysql> desc t2;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | NO| | NULL||+-------+---------+------+-----+---------+-------+mysql> insert into t2 values(); #不能插入空ERROR 1364 (HY000): Field 'id' doesn't have a default value==================default====================#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值mysql> create table t3(id int default 1);mysql> alter table t3 modify id int not null default 1;==================综合练习====================mysql> create table student(-> name varchar(20) not null,-> age int(3) unsigned not null default 18,-> sex enum('male','female') default 'male',-> hobby set('play','study','read','music') default 'play,music'-> );mysql> desc student;+-------+------------------------------------+------+-----+------------+-------+| Field | Type| Null | Key | Default| Extra |+-------+------------------------------------+------+-----+------------+-------+| name  | varchar(20)| NO| | NULL||| age| int(3) unsigned| NO| | 18 ||| sex| enum('male','female')  | YES  | | male||| hobby | set('play','study','read','music') | YES  | | play,music ||+-------+------------------------------------+------+-----+------------+-------+mysql> insert into student(name) values('chao');mysql> select * from student;+------+-----+------+------------+| name | age | sex  | hobby  |+------+-----+------+------------+| chao |  18 | male | play,music |+------+-----+------+------------+not null和default测试

　　注意一点：如果是非严格模式，int类型不传值的话会默认为0，因为null不是int类型的，字段是int类型，所以他会自动将null变为0.

　　　　3.unique

　　　　　　独一无二, 唯一属性,id,身份证号等

　　　　　　是一种key, 唯一键, 是在数据类型之外的附加属性,其实还有加速查询的作用

============设置唯一约束 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创建

　　　　4, primary key

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

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

unique key和primary key都是MySQL的特殊类型，不仅仅是个字段约束条件，还称为索引，可以加快查询速度，这个索引功能我们后面再讲，现在只讲一下这些key作为约束条件的效果。一张表中必须要有一个主键,这样查询的时候会减少IO次数.我们一般都会把id作为主键.

　　主键的用法

============单列做主键===============#方法一：not null+uniquecreate 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 keycreate 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 keycreate table department3(id int,name varchar(20),comment varchar(100),constraint pk_name primary key(id); #创建主键并为其命名pk_namemysql> 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)单列主键测试

　　　　5. auto_increment　

　　　　　　当我们把id设置auto_increment的属性后,我们就不用在在输入id了.

　　　　　　约束字段为自动增长，被约束的字段必须同时被key约束，也就是说只能给约束成key的字段加自增属性，默认起始位置为1，步长也为1.

#不指定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 |+----+------+------+#也可以指定idmysql> 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 |+----+------+------+row in set (0.00 sec)auto_increment测试

　　6. foreign key(外键)

　　　　外键其实就是表名表和表之间的关系.一共有三种关系,一对一,多对一,多对多.

　　　　我们先总结一下foreign key的下面几个约束作用：

　　　　　　1、先要建立被关联的表才能建立关联表

　　　　　　2、在插入数据记录的时候，要先想被关联表中插入数据，才能往关联表里面插入数据

　　　　　　3、更新或者删除数据的时候，都需要考虑关联表和被关联表的关系

　　　　　　　　解决方案：

　　　　　　　　　　a.删除表的时候，先删除关联表，再删除被关联表

　　　　　　　　　　b.重建表的时候，在加外键关联的时候加上这两句：on delete cascade 和 on update cascade

　　找表的关系步骤

分析步骤：#1、先站在左表的角度去找是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）#2、再站在右表的角度去找是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）#3、总结：#多对一：如果只有步骤1成立，则是左表多对一右表如果只有步骤2成立，则是右表多对一左表#多对多如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系#一对一:如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可找表关系的步骤

　　多对一或一对多

#一对多或称为多对一三张表：出版社，作者信息，书一对多（或多对一）：一个出版社可以出版多本书关联方式：foreign key

　　一对多简单示例

=====================多对一=====================create table press(id int primary key auto_increment,name varchar(20));create table book(id int primary key auto_increment,name varchar(20),press_id int not null,foreign key(press_id) references press(id)on delete cascadeon update cascade);insert into press(name) values('北京工业地雷出版社'),('人民音乐不好听出版社'),('知识产权没有用出版社');insert into book(name,press_id) values('九阳神功',1),('九阴真经',2),('九阴白骨爪',2),('独孤九剑',3),('降龙十巴掌',2),('葵花宝典',3)一对多的简单示例

　　多对多

#多对多三张表：出版社，作者信息，书多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多　　关联方式：foreign key+一张新的表

　　多对多简单示例

=====================多对多=====================create table author(id int primary key auto_increment,name varchar(20));#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了create table author2book(id int not null unique auto_increment,author_id int not null,book_id int not null,constraint fk_author foreign key(author_id) references author(id)on delete cascadeon update cascade,constraint fk_book foreign key(book_id) references book(id)on delete cascadeon update cascade,primary key(author_id,book_id));#插入四个作者，id依次排开insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');#每个作者与自己的代表作如下egon: 九阳神功九阴真经九阴白骨爪独孤九剑降龙十巴掌葵花宝典alex: 九阳神功葵花宝典yuanhao:独孤九剑降龙十巴掌葵花宝典wpq:九阳神功insert into author2book(author_id,book_id) values(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(2,1),(2,6),(3,4),(3,5),(3,6),(4,1);多对多简单示例

　　一对一

#一对一两张表：学生表和客户表一对一：一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系关联方式：foreign key+unique

　　一对一简单示例

#一定是student来foreign key表customer，这样就保证了：#1 学生一定是一个客户，#2 客户不一定是学生，但有可能成为一个学生create table customer(id int primary key auto_increment,name varchar(20) not null,qq varchar(10) not null,phone char(16) not null);create table student(id int primary key auto_increment,class_name varchar(20) not null,customer_id int unique, #该字段一定要是唯一的foreign key(customer_id) references customer(id) #外键的字段一定要保证uniqueon delete cascadeon update cascade);#增加客户insert into customer(name,qq,phone) values('李飞机','31811231',13811341220),('王大炮','123123123',15213146809),('守榴弹','283818181',1867141331),('吴坦克','283818181',1851143312),('赢火箭','888818181',1861243314),('战地雷','112312312',18811431230);#增加学生insert into student(class_name,customer_id) values('脱产3班',3),('周末19期',4),('周末19期',5);一对一简单示例