Python/MySQL（二、表操作以及连接）
Python/MySQL（二、表操作以及连接）
mysql表操作： 主键：一个表只能有一个主键。主键可以由多列组成。 外键 ：可以进行联合外键，操作。
mysql> create table yuan(id int auto_increment,yuangongname int,bumen_id int, primary key(id,yuangongname))engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.43 sec)
查看表创建的类型：
show create table 表名；
mysql> show create table class; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | class | CREATE TABLE `class` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `caption` char(10) DEFAULT NULL, PRIMARY KEY (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
show create table 表名 \G；
mysql> show create table class\G; *************************** 1. row *************************** Table: class Create Table: CREATE TABLE `class` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `caption` char(10) DEFAULT NULL, PRIMARY KEY (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified
alter table 表名 auto_increment=20; 设置自增的起始位。
mysql> alter table class auto_increment=20; Query OK, 0 rows affected (0.11 sec)
步长：
基于会话级别：
show session variables like 'auto_inc%'; 查看会话的步长 mysql> show session variables like 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set, 1 warning (0.00 sec)
set session auto_increment_increment=2; 设置会话步长
mysql> set session auto_increment_increment=2; Query OK, 0 rows affected (0.00 sec) set session auto_incrment_offset=10; mysql> set session auto_increment_offset=10; Query OK, 0 rows affected (0.00 sec)
基于全局级别：
show global variables like ' auto_inc%'; 查看全局步长
show global variables like ' auto_inc%'; 查看全局步长 mysql> show global variables like 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set, 1 warning (0.00 sec)
set global auto_increment_increment=2; 设置全局步长
mysql> set global auto_increment_increment=2; Query OK, 0 rows affected (0.02 sec) set global auto_incrment_offset=10; mysql> set global auto_increment_increment=1; Query OK, 0 rows affected (0.00 sec)
数据库中自增的共有（二种）
1.自增步长； 2.自增主键；
唯一索引：
unique 唯一索引名称（列名，列名） 唯一：约束不能重复（可以为空），加速查找 主键：约束 不能重复且不能为空
创建唯一索引：
mysql> create table biao(id int auto_increment primary key,name char(10),unique s_t(id))engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.82 sec)
外键的扩展：
一对多：例如，一个公司有很多员工但是部门只有固定的4个
创建一个部门表；
mysql> create table bumen(id int auto_increment primary key,bumenname char(10))engine=innodb default charset=utf8; Query OK, 0 rows affected (0.55 sec) mysql> show tables; +--------------+ | Tables_in_b2 | +--------------+ | b1 | | biao | | bumen | | class | | course | | score | | student | | teacher | +--------------+ 8 rows in set (0.00 sec)
创建员工表： mysql> create table yuangong(id int auto_increment primary key,yuangongname char(10))engine=innodb default charset=utf8; Query OK, 0 rows affected (0.62 sec) mysql> show tables; +--------------+ | Tables_in_b2 | +--------------+ | b1 | | biao | | bumen | | class | | course | | score | | student | | teacher | | yuangong | +--------------+ 9 rows in set (0.00 sec) 为员工表添加信息： mysql> insert into yuangong(yuangongname) values('张三'); Query OK, 1 row affected (0.11 sec) mysql> insert into yuangong(yuangongname) values('张si'),('张ts'),('张li'); Query OK, 3 rows affected (0.06 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from yuangong; +----+--------------+ | id | yuangongname | +----+--------------+ | 1 | 张三 | | 2 | 张si | | 3 | 张ts | | 4 | 张li | +----+--------------+ 4 rows in set (0.00 sec) 为部门表添加信息： mysql> insert into bumen(bumenname) values('IT'),('BT'),('kT'),('XT'); Query OK, 4 rows affected (0.05 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from bumen; +----+-----------+ | id | bumenname | +----+-----------+ | 1 | IT | | 2 | BT | | 3 | kT | | 4 | XT | +----+-----------+ 4 rows in set (0.00 sec)
一对一：
例如公司有多个员工，公司分配邮箱，每个员工只能有一个邮箱不能重复有多，就是一对一的
多对多：
例如，相亲记录表（用户表 1，用户表 2 ）俩个表的内容可以进行多次匹配 就实现多对多效果；
SQL语句数据行操作补充；
增 insert into biao(name,age) values('alex',18); insert into biao(name,age) values('alex',18),('egon',10); insert into biao(name,age) select name,age from biao2; 删： delete from biao； delete from tb12 where id !=2 delete from tb12 where id =2 delete from tb12 where id > 2 delete from tb12 where id >=2 delete from tb12 where id >=2 or name='alex' 改： update biao set name='alex' where id>2 and name='egon' update biao set name='alex',age=19 where id>2 and name='egon'; 查： select * from biao: select name from biao; select id,name from biao where id>22 or name='alex'; select * from tb12 where id in (select id from tb11) select * from tb12 where id between 5 and 12; (闭区间包含自己本身)
通配符：
select * from biao where name like 'a%' (查看以a开头的任意内容) select * from biao where name like 'a_' (查看以a开头的下一个内容) select * from biao where name like '%a%' (查看包含a的内容) select * from biao where naem like '%a' （查看以a结尾的内容）
分页：
select * from biao limit 10; (查看10的内容从0开始排列）) select * from biao limit 1,10;(查看从1开始后面的10个内容) select * from biao limeit 10 offset 20;(查看从20开始后面的10个人内容)
排序：
select *from biao order by id desc (从大到小的排序) select *from biao order by id asc (从小到大的排序)
查看后10条数据：
select *from biao order by id desc limit 10; (把表的内容进行反转 ，然后再获取10条数据)
分组：(count . max . min . sum . avg)
select count(id),min(id),max(id),part_id from mysql> select count(cid) from class group by cid; +------------+ | count(cid) | +------------+ | 1 | | 1 | | 1 | +------------+ 3 rows in set (0.04 sec) mysql> select caption from class group by caption; +----------+ | caption | +----------+ | 一年三班 | | 三年一班 | | 三年二班 | +----------+ 3 rows in set (0.04 sec) mysql> select sid,class_id from student group by sid,class_id; +-----+----------+ | sid | class_id | +-----+----------+ | 1 | 1 | | 2 | 1 | | 3 | 2 | +-----+----------+ 3 rows in set (0.04 sec) mysql> select sid from student group by sid having max(sid)>1; +-----+ | sid | +-----+ | 2 | | 3 | +-----+ 2 rows in set (0.09 sec) **** 如果对于聚合函数结果进行二次筛选时？必须使用having **** select count(id),part_id from userinfo5 group by part_id having count(id) > 1; select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;
连表操作 （left join 表名 on 有相关关系的进行连接，并设置是否相等，如果不设置就是（的卡尔基效果，表的行数相乘））
mysql> select * from teacher join course on course.tearch_id=teacher.tid; +-----+-------+-----+-------+-----------+ | tid | tname | cid | cname | tearch_id | +-----+-------+-----+-------+-----------+ | 1 | 波多 | 1 | 生物 | 1 | | 1 | 波多 | 2 | 体育 | 1 | | 2 | 苍空 | 3 | 物理 | 2 | +-----+-------+-----+-------+-----------+ 3 rows in set (0.03 sec)
mysql> select * from teacher right join course on course.tearch_id=teacher.tid; +------+-------+-----+-------+-----------+ | tid | tname | cid | cname | tearch_id | +------+-------+-----+-------+-----------+ | 1 | 波多 | 1 | 生物 | 1 | | 1 | 波多 | 2 | 体育 | 1 | | 2 | 苍空 | 3 | 物理 | 2 | +------+-------+-----+-------+-----------+ 3 rows in set (0.00 sec)
靠左连接 （left join 表名 on 有相关关系的进行连接）
mysql> select * from teacher left join course on course.tearch_id=teacher.tid; +-----+-------+------+-------+-----------+ | tid | tname | cid | cname | tearch_id | +-----+-------+------+-------+-----------+ | 1 | 波多 | 1 | 生物 | 1 | | 1 | 波多 | 2 | 体育 | 1 | | 2 | 苍空 | 3 | 物理 | 2 | | 3 | 饭岛 | NULL | NULL | NULL | +-----+-------+------+-------+-----------+ 4 rows in set (0.00 sec)
靠右连接：（right join 表名 on 有相关关系的进行连接）
mysql> select * from teacher right join course on course.tearch_id=teacher.tid; +------+-------+-----+-------+-----------+ | tid | tname | cid | cname | tearch_id | +------+-------+-----+-------+-----------+ | 1 | 波多 | 1 | 生物 | 1 | | 1 | 波多 | 2 | 体育 | 1 | | 2 | 苍空 | 3 | 物理 | 2 | +------+-------+-----+-------+-----------+ 3 rows in set (0.00 sec) mysql>
不显示NULL（空）行（inner）
mysql> select * from teacher inner join course on course.tearch_id=teacher.tid; +-----+-------+-----+-------+-----------+ | tid | tname | cid | cname | tearch_id | +-----+-------+-----+-------+-----------+ | 1 | 波多 | 1 | 生物 | 1 | | 1 | 波多 | 2 | 体育 | 1 | | 2 | 苍空 | 3 | 物理 | 2 | +-----+-------+-----+-------+-----------+ 3 rows in set (0.00 sec)
筛选条件：
这些条件可以在对表进行操作时代入
1 in 在 2 not in 不在 3 and 和 4 or 或 5 = 等于 6 ！= 不等于 7 > 大于 8 < 小于 9 >= 大于等于 10 <= 小于等于 11 between and 在什么之间 和 .....
