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)
筛选条件：
这些条件可以在对表进行操作时代入
in 在
not in 不在
and 和
or 或
= 等于
！= 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
between and 在什么之间 和 .....