46.MySQL数据库3
今日内容概要
今日内容详细
""" # 补充知识点，插入数据的时候，可以指定字段顺序 create table t1( id int, name char(16) ); insert into t1(name,id） values('jason',1); create table t2( id int, name char(16) not null, gender enum('male','female','others') default 'male' ); mysql> desc t2; +--------+-------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(16) | NO | | NULL | | | gender | enum('male','female','other') | YES | | male | | +--------+-------------------------------+------+-----+---------+-------+ 3 rows in set (0.05 sec) insert into t2(id,name) values(1,'jason'); insert into t2 values(2,'egon','female'); mysql> select * from t2; +------+------------------+--------+ | id | name | gender | +------+------------------+--------+ | 1 | jason | male | | 2 | egon | female | +------+------------------+--------+ 2 rows in set (0.00 sec) """
""" # 单列唯一 create table t3( id int unique, name char(16) ); insert into t3 values(1,'jason'),(1,'egon'); insert into t3 values(1,'jason'),(2,'egon'); mysql> desc t3; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | char(16) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.05 sec) mysql> insert into t3 values(1,'jason'),(1,'egon'); ERROR 1062 (23000): Duplicate entry '1' for key 'id' # 联合唯一 ip 和 port 单个都可以重复，但是加在一起必须唯一 id ip port 1 1 1 2 1 2 3 2 1 4 3 1 create table t4( id int, ip char(16), port int, unique(ip,port) ); mysql> desc t4; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | ip | char(16) | YES | MUL | NULL | | | port | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.05 sec) insert into t4 values(1,'127.0.0.1',8080); insert into t4 values(2,'127.0.0.1',8081); insert into t4 values(3,'127.0.0.2',8080); mysql> insert into t4 values(3,'127.0.0.2',8080); Query OK, 1 row affected (0.05 sec) 前面三条执行成功 insert into t4 values(4,'127.0.0.1',8080); mysql> insert into t4 values(4,'127.0.0.1',8080); ERROR 1062 (23000): Duplicate entry '127.0.0.1 -8080' for key 'ip' """
""" # 1.单单从约束效果上来看 primary key 等价于 not null + unique 非空且唯一！！！！ create table t5(id int primary key); mysql> desc t5; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.04 sec) insert into t5 values(null); mysql> insert into t5 values(null); ERROR 1048 (23000): Column 'id' cannot be null insert into t5 values(1),(2); insert into t5 values(3),(3); mysql> insert into t5 values(3),(3); ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY' #2.它除了有约束效果之外，还是存储引擎Innodb存储引擎组织数据的依据 Innodb 存储引擎规定在创建表的时候，必须要有primary key 因为它类似于数的目录，能够帮助提升查询效率，并且也是建表的依据 2.1一张表中有且只能有一个主键，如果你没有设置主键，那么会从上往下搜索知道遇到一个非空且唯一的字段将它主动升为主键 create table t6( id int, name char(16), age int not null unique, addr char(32) not null unique ); mysql> desc t6; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(16) | YES | | NULL | | | age | int(11) | NO | PRI | NULL | | | addr | char(32) | NO | UNI | NULL | | +-------+----------+------+-----+---------+-------+ 4 rows in set (0.06 sec) 2.2 如果表中既没有设置主键也没有其他任何非空且唯一的字段，那么Innodb会采用自己内部提供的一个
隐藏字段作为主键，隐藏意味着你无法使用它，就无法提升查询速度 2.3 一张表中通常都应该有一个主键字段，并且通常将id/uid/sid字段作为主键 #单个字段主键 create table t5( id int primary key, name char(16) ); #联合主键（多个字段联合起来作为表的主键，本质还是一个主键） create table t7( id int, ip char(16), port int, primary key(ip,port) ); mysql> desc t7; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | ip | char(16) | NO | PRI | NULL | | | port | int(11) | NO | PRI | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 也就意味着，以后我们在创建表的时候，id字段一定要加primary key """
""" # 当编号特别多的时候，人为的去维护太麻烦 create table t8( id int primary key auto_increment, name char(16) ); mysql> desc t8; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(16) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 2 rows in set (0.05 sec) insert into t8(name) values('jason'),('egon'),('tank'); mysql> insert into t8(name) values('jason'),('egon'),('tank'); Query OK, 3 rows affected (0.05 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t8; +----+------------------+ | id | name | +----+------------------+ | 1 | jason | | 2 | egon | | 3 | tank | +----+------------------+ 3 rows in set (0.00 sec) #注意！！！！！！ auto_increment通常都是加在key上，不能给普通字段加 create table t9( id int primary key auto_increment, name char(16), cid int auto_increment ); mysql> create table t9( -> id int primary key auto_increment, -> name char(16), -> cid int auto_increment -> ); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column
and it must be defined as a key """
以后再创建表的时候：id字段要这样定义
id int primary key auto_increment
补充
""" delete from 在删除表数据的时候，主键的自增不会停止 mysql> delete from t8; Query OK, 3 rows affected (0.05 sec) mysql> insert into t8(name) values('egon'); Query OK, 1 row affected (0.05 sec) mysql> select * from t8; +----+------------------+ | id | name | +----+------------------+ | 4 | egon | +----+------------------+ 1 row in set (0.00 sec) truncate t8 清空数据并且重置主键 mysql> truncate t8; Query OK, 0 rows affected (0.08 sec) mysql> select * from t8; Empty set (0.00 sec) mysql> insert into t8(name) values('egon'); Query OK, 1 row affected (0.05 sec) mysql> select * from t8; +----+------------------+ | id | name | +----+------------------+ | 1 | egon | +----+------------------+ 1 row in set (0.00 sec) """
""" 定义一张员工表，表中有很多字段 id name gender dep_name dep_desc 员工表 id emp_name emp_gender dep_name dep_desc 1 jason male 外交部 漂泊游荡 2 egon female 教学部 教书育人 3 tank male 教学部 教书育人 4 kevin male 教学部 教书育人 5 oscar female 技术部 技术能力有限公司 # 1 该表的组织结构不是很清晰（可忽略） # 2 浪费硬盘空间，数据重复（可忽略） # 3 数据的扩展性差，无法修改（不可忽略） #如何优化 上述问题就类似于你将所有的代码都写在了同一个py文件中 将员工表拆分：员工表 部门表 员工表 id emp_name emp_gender 1 jason male 2 egon female 3 tank male 4 kevin male 5 oscar female 部门表 id dep_name dep_desc 1 外交部 漂泊游荡 2 教学部 教书育人 3 技术部 技术能力有限公司 拆分好之后，随之而来的问题是：两张表之间没有了联系 应该给员工表加一个字段 对应的部门应该是id 员工表 外键 id emp_name emp_gender dep_id 1 jason male 1 2 egon female 2 3 tank male 2 4 kevin male 2 5 oscar female 3 """
外键就是帮助我们建立表与表之间的关系的
foreign key
表与表之间最多有四种关系
一对多
多对多
一对一
没有关系
""" 在判断表与表关系的时候，一定要换位思考，分别站在两张表的角度思考 以员工表和部门表为例 先站在员工表的角度：员工表里的一个员工能否对应多个部门 不能！！！！（这个时候还不能得出两张表的关系） 再站在部门表的角度：部门表里的一个部门能否有多个员工 能！！！！！ 结论：员工表和部门表是单向的一对多关系 ，那么部门表和员工表就构成了“一对多”的表关系 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, emp_name char(16), emp_gender enum('male','female','others') default 'male', dep_id int, foreign key(dep_ip) references dep(id) ); mysql> desc dep; +----------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | dep_name | char(16) | YES | | NULL | | | dep_desc | char(32) | YES | | NULL | | +----------+----------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> desc emp; +------------+--------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | emp_name | char(16) | YES | | NULL | | | emp_gender | enum('male','female','others') | YES | | male | | | dep_id | int(11) | YES | MUL | NULL | | +------------+--------------------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) # 如果先插入员工表的数据，会报错！！！ insert into emp(emp_name,dep_id) values('egon',1); mysql> insert into emp(emp_name,dep_id) values('egon',1); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`day46`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`)) # 所以需要 1.先给被关联的数据表插入数据 insert into dep(dep_name,dep_desc) values('外交部','漂泊流浪'),('教学部','教书育人'),('技术部','技术有限公司'); mysql> select * from dep; +----+-----------+--------------------+ | id | dep_name | dep_desc | +----+-----------+--------------------+ | 1 | 外交部 | 漂泊流浪 | | 2 | 教学部 | 教书育人 | | 3 | 技术部 | 技术有限公司 | +----+-----------+--------------------+ 3 rows in set (0.00 sec) 2.再给员工表插入数据 insert into emp(name,dep_id) values ('jason',1),('egon',2),('tank',2),('kevin',3),('lili',3); mysql> select * from emp; +----+----------+------------+--------+ | id | emp_name | emp_gender | dep_id | +----+----------+------------+--------+ | 7 | jason | male | 1 | | 8 | egon | male | 2 | | 9 | tank | male | 2 | | 10 | kevin | male | 3 | | 11 | lili | male | 3 | +----+----------+------------+--------+ 5 rows in set (0.00 sec) 问题： 1.修改emp里面的dep_id 字段，或者dep里面的id字段 update dep set id = 200 where id = 2; # 不行 2.删除dep表里的数据 delete fro 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, emp_name char(16), emp_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('外交部','漂泊流浪'),('教学部','教书育人'),('技术部','技术有限公司'); insert into emp(emp_name,dep_id) values('jason',1),('egon',2),('tank',2),('kevin',3),('lili',3); 这时候再去修改，对应的数据就会自动改变 mysql> update dep set id=200 where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from dep; +-----+-----------+--------------------+ | id | dep_name | dep_desc | +-----+-----------+--------------------+ | 1 | 外交部 | 漂泊流浪 | | 3 | 技术部 | 技术有限公司 | | 200 | 教学部 | 教书育人 | +-----+-----------+--------------------+ 3 rows in set (0.00 sec) mysql> select * from emp; +----+----------+------------+--------+ | id | emp_name | emp_gender | dep_id | +----+----------+------------+--------+ | 1 | jason | male | 1 | | 2 | egon | male | 200 | | 3 | tank | male | 200 | | 4 | kevin | male | 3 | | 5 | lili | male | 3 | +----+----------+------------+--------+ 5 rows in set (0.00 sec) """
多对多表关系
""" 图书表和作者表 book id title price 1 python入门到放弃 21000 2 葵花宝典 6666 3 前端基础 9999 4 水浒传 123.23 author id name age 1 jason 18 2 egon 84 确定图书表和作者表的关系 先站在图书表的角度： 一本书可不可以有多个作者 可以！！！！ 再站在作者表的角度： 一个作者能不能写多本书 可以！！！！ 结论： 图书表和作者表是双向的一对多 那么他们的表关系就是“多对多” 的表关系 知道了以上两个表的关系之后，我们前面学过，要确定表关系，需要用到外键，因此在book表中加一个外键auto_id ,
同样的在author表中也要加一个外加book_id 各自加了外键之后，原来的表变成下面的样子 book id title price author_id 1 python入门到放弃 21000 1,2 2 葵花宝典 6666 1 3 前端基础 9999 2 4 水浒传 123.23 1 author id name age book_id 1 jason 18 1,2 2 egon 84 1,2 确定了外键之后，我们尝试用sql语句创建这两张表 create table book( id int primary key auto_increment, title char(32), price int, author_id int, foreign key(author_id) references author(id) on update cascade on delete cascade ); create table author( id int primary key auto_increment, name varchar(32), age int, book_id int, foreign key(book_id) references book(id) on update cascade on delete cascade ); 以上创建表的语句写好之后，我们分析一波，建立外键的时候，有几个要注意的问题 1.一对多表关系，外键建在多的一方 2.在创建表的时候，需要先创建被关联的表 3.在录入数据的时候，需要先录入被关联表的数据 这里我们看第2点，站在book的角度，被关联表是author 站在author表的角度，被关联表是book 问题就来了，到底先创建哪个表，陷入了“死循环” 按照上述的方式创建，一个都别想成功 其实我们只是想记录书籍与作者的关系 针对多对多字段表关系，不能在两张原有的表中建立外键 需要你单独再开设一张表，专门用来存储两张表数据之间的关系 这时候的表如下： book id title price 1 python入门到放弃 21000 2 葵花宝典 6666 3 前端基础 9999 4 水浒传 123.23 author id name age 1 jason 18 2 egon 84 book2author id book_id author_id 1 1 1 2 1 2 3 2 1 4 3 2 5 4 1 接下来再去创建三张表 creata table book( id int primary key auto_increment, title varchar(32), price int ); create table author( id int primary key auto_increment, name varchar(32), age int ); create book2author( id int primary key auto_increment, book_id int, author_id int, foreign key(book_id) references book(id) on update cascade on delete cascade， foreign key(author_id) references author(id) on update cascade on delete cascade ); win7命令窗口复制代码：右键标记，选中代码，按enter键，即可复制 mysql> desc book; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | title | varchar(32) | YES | | NULL | | | price | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.03 sec) mysql> desc author; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(32) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql> desc book2author; +-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | book_id | int(11) | YES | MUL | NULL | | | author_id | int(11) | YES | MUL | NULL | | +-----------+---------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) 建好表之后，开始插入数据 insert into book(title,price) values('python入门到放弃',21000),('葵花宝典',6666),('前端基础',9999),('水浒传',123); insert into author(name,age) values('jason',18),('egon',73),('tank',25),('oscar',45); mysql> select * from book; +----+-----------------------+-------+ | id | title | price | +----+-----------------------+-------+ | 1 | python入门到放弃 | 21000 | | 2 | 葵花宝典 | 6666 | | 3 | 前端基础 | 9999 | | 4 | 水浒传 | 123 | +----+-----------------------+-------+ 4 rows in set (0.00 sec) mysql> select * from author; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | jason | 18 | | 2 | egon | 73 | | 3 | tank | 25 | | 4 | oscar | 45 | +----+-------+------+ 4 rows in set (0.00 sec) 接下来插入数据到book2author表中 insert into book2author(book_id,author_id) values(1,1),(1,2),(2,1),(3,2),(4,1); mysql> select * from book2author; +----+---------+-----------+ | id | book_id | author_id | +----+---------+-----------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 2 | 1 | | 4 | 3 | 2 | | 5 | 4 | 1 | +----+---------+-----------+ 5 rows in set (0.00 sec) 现在我们建立好了关系，也插入了数据，我们去更新和删除书籍表中和作者表中的一条数据，看看与其关联的表是否有变化 update book set id = 100 where id = 1; mysql> select * from book2author; +----+---------+-----------+ | id | book_id | author_id | +----+---------+-----------+ | 1 | 100 | 1 | | 2 | 100 | 2 | | 3 | 2 | 1 | | 4 | 3 | 2 | | 5 | 4 | 1 | +----+---------+-----------+ 5 rows in set (0.00 sec) delete from book where id = 100; """
一对一表关系
""" id name age addr phone bobby email... 如果一个表里的字段特别多，每次查询又不是所有的字段都需要查询到 这时候就要将表一份为二： 用户表 id name age 用户详情表 id addr phone hobby email.... 站在用户表的角度 一个用户能否对用多个用户详情，不能 再站在用户详情表的角度 一个用户详情表能否对应多个用户，不能 结论：单向的一对多都不成立，那么这个时候，两张表之间的关系 要么一对一 要么没有关系 客户表和学生表 在报名之前是客户 报名之后是学生（期间有一些客户不会报名） author id name age 1 jason 18 2 egon 73 authororderdetail id phone addr 1 110 安徽芜湖 2 120 山东菏泽 客户表和学生表只能一对一 一对一外键建立在任意一方都可以，但是建议建在查询频率较高的那张表中 create table authordetail( id int primary key auto_increment, phone int , addr varchar(64) ); create table author1( id int primary key auto_increment, name varchar(32), age_int, authordetail_id int unique, foreign key(authordetail_id) references authordetail(id) on update cascade on delete cascade ); 插入数据 insert into authordetail(phone,addr) values(110,'安徽芜湖'),(120,'折江杭州'); 由于表关系是一对一，因此外键authordetail_id 必须加unique约束，才可以 在插入数据的时候，就不能插入相同的authordetail_id 比如下面语句就会报错 insert into author1(name,age,authordetail_id) values('egon',18,1),('jason',25,1); mysql> insert into author1(name,age,authordetail_id) values('egon',18,1),('jason ',25,1); ERROR 1062 (23000): Duplicate entry '1' for key 'authordetail_id' 如果改成这样就可以了： insert into author1(name,age,authordetail_id) values('egon',18,1),('jason',25,2); 插播一个解决mysql终端输入没法执行的方法： mysql> insert into author1(name,age,authordetail) values('\c '> \c '> ; '> insert into authordetail(phone,addr) values(110,'安徽芜湖'),(120,'折江杭 州'); '> ); '> quit '> /sfadsas '> f '> ga '> sfg '> \g '> '\c mysql> 在 '>后面输入 '\c 即可 """
表关系的建立需要用到foreign key
一对多
外键字段建在多的一方
多对多
外键字段建在中间表
一对一
建在任意一方都可以，建议建在查询频率高的表中
判断表之间的关系
采用换位思考法
""" MySQL对大小写是不敏感的 1.修改表名 alter table 表名 rename 新表名; 2.增加字段 alter table 表名 add 字段名 字段类型(宽度) 约束条件; alter table 表名 add 字段名 字段类型(宽度) 约束条件 first; alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名; 3.删除字段 alter table 表名 drop 字段名; 4.修改字段 alter table 表名 modify 字段名 字段类型(宽度) 约束条件; alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件; """
""" 我们SQL语句的查询结果其实也是一张虚拟表 """ create table 新表名 select * from 就表名; #不能复制主键、外键 create table new_table select * from old_table where id > 3;
