首页 > 代码库 > Python/MySQL表操作以及连接

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;

 

 连表操作;

 

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)

 

 靠左连接;

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)

 

 靠右连接:

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行

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)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Python/MySQL表操作以及连接