Python之MySQL数据操作

一、增

1.1 单条数据增加

语法： insert into 表名(字段1，字段2...) values(字段1的值，字段2的值...) 

 1.2  多条插入

mysql> create table idc(
    -> id int auto_increment primary key,
    -> name varchar(32) not null,
    -> position varchar(10) not null)
    -> engine = innodb default charset = utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> desc idc;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(32) | NO   |     | NULL    |                |
| position | varchar(10) | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

# 插入多条数据
mysql> insert into idc(name,position) values('博兴','北京'),('大族','北京'),('纪蕴','上海');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from idc;
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
|  3 | 纪蕴   | 上海     |
+----+--------+----------+
3 rows in set (0.00 sec)

1.3 根据从其他表查到的数据插入到新表中

mysql> create table idc_new( id int auto_increment primary key, name varchar(32) not null, position varchar(10) not null) engine = innno
Query OK, 0 rows affected (0.00 sec)

mysql> select * from idc_new;
Empty set (0.00 sec)

mysql> select * from idc;
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
|  3 | 纪蕴   | 上海     |
+----+--------+----------+
3 rows in set (0.00 sec)

mysql> insert into idc_new(name,position) select name,position from idc;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from idc_new;
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
|  3 | 纪蕴   | 上海     |
+----+--------+----------+
3 rows in set (0.00 sec)

二、删

语法： delete from 表名 where 条件

三、改

语法： update 表名 set 字段1=新值,字段2=新值，字段3=新值... where 条件

修改多个字段

mysql> select * from idc;
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
|  3 | 纪蕴   | 上海     |
+----+--------+----------+
3 rows in set (0.00 sec)

mysql> update idc set name = '大龙',position='邯郸' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from idc;
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
|  3 | 大龙   | 邯郸     |
+----+--------+----------+
3 rows in set (0.00 sec)

四、查

4.1 单表查询

4.1.1  将表里的所有字段的所有数据都查出来

• *代表所有

select * from test;

4.1.2   只查指定字段的所有数据

# select 字段1，字段2 ... from 表；
select user,host from mysql.user;

4.1.3 别名

mysql> select * from idc_new;
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
|  3 | 纪蕴   | 上海     |
+----+--------+----------+
3 rows in set (0.00 sec)

mysql> select id,name as 机房名称,position as 机房位置 from idc_new;
+----+--------------+--------------+
| id | 机房名称     | 机房位置     |
+----+--------------+--------------+
|  1 | 博兴         | 北京         |
|  2 | 大族         | 北京         |
|  3 | 纪蕴         | 上海         |
+----+--------------+--------------+

4.1.4  等于，不等于

mysql> select * from idc_new;
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
|  3 | 纪蕴   | 上海     |
+----+--------+----------+
3 rows in set (0.00 sec)

mysql> select * from idc_new where position = '北京';
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
+----+--------+----------+
2 rows in set (0.00 sec)

mysql> select * from idc_new where position != '北京';
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  3 | 纪蕴   | 上海     |
+----+--------+----------+
1 row in set (0.00 sec)

4.1.5  in和not in

mysql> select * from idc_new;
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
|  3 | 纪蕴   | 上海     |
|  4 | 后场   | 邯郸     |
+----+--------+----------+
4 rows in set (0.00 sec)

mysql> select * from idc_new where id in (1,3,4);
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  3 | 纪蕴   | 上海     |
|  4 | 后场   | 邯郸     |
+----+--------+----------+
3 rows in set (0.00 sec)

mysql> select * from idc_new where id not in (1,3,4);
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  2 | 大族   | 北京     |
+----+--------+----------+
1 row in set (0.00 sec)

# in （可以某个SQL查询出来结果）

mysql> select * from idc;
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
|  3 | 大龙   | 邯郸     |
+----+--------+----------+
3 rows in set (0.00 sec)

mysql> select * from idc_new;
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
|  3 | 纪蕴   | 上海     |
|  4 | 后场   | 邯郸     |
+----+--------+----------+
4 rows in set (0.00 sec)

mysql> select * from idc_new where id in (select id from idc);
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
|  3 | 纪蕴   | 上海     |
+----+--------+----------+
3 rows in set (0.00 sec)

4.1.6  between and

• 闭区间

mysql> select * from idc_new;
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
|  3 | 纪蕴   | 上海     |
|  4 | 后场   | 邯郸     |
+----+--------+----------+
4 rows in set (0.00 sec)

mysql> select * from idc_new where id between 1 and 3;
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
|  3 | 纪蕴   | 上海     |
+----+--------+----------+
3 rows in set (0.00 sec)

4.1.7  通配符

• % 多个
• _ 一个

# % 
mysql> select * from idc_new;
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
|  3 | 纪蕴   | 上海     |
|  4 | 后场   | 邯郸     |
+----+--------+----------+
4 rows in set (0.00 sec)

mysql> select * from idc_new where position like "北%";
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
+----+--------+----------+
2 rows in set (0.00 sec)

# —_代表一个占位符

mysql> select * from idc_new where position like "北_";
+----+--------+----------+
| id | name | position |
+----+--------+----------+
| 1 | 博兴 | 北京 |
| 2 | 大族 | 北京 |
+----+--------+----------+
2 rows in set (0.00 sec)

4.1.8  limit 

limit a ,b    从a往后查b条数据

mysql> select * from employee;
+----+----------+
| id | name     |
+----+----------+
|  1 | wangys   |
|  2 | wc       |
|  3 | huisz    |
|  4 | chaoyf   |
|  5 | anyl     |
|  6 | xiaolong |
|  7 | jiayue   |
|  8 | a        |
|  9 | b        |
| 10 | c        |
| 11 | d        |
| 12 | e        |
| 13 | f        |
| 14 | g        |
| 15 | h        |
+----+----------+
15 rows in set (0.00 sec)

mysql> select * from employee limit 5;
+----+--------+
| id | name   |
+----+--------+
|  1 | wangys |
|  2 | wc     |
|  3 | huisz  |
|  4 | chaoyf |
|  5 | anyl   |
+----+--------+
5 rows in set (0.00 sec)

mysql> select * from employee limit 5,5;
+----+----------+
| id | name     |
+----+----------+
|  6 | xiaolong |
|  7 | jiayue   |
|  8 | a        |
|  9 | b        |
| 10 | c        |
+----+----------+
5 rows in set (0.00 sec)

mysql> select * from employee limit 10,5;
+----+------+
| id | name |
+----+------+
| 11 | d    |
| 12 | e    |
| 13 | f    |
| 14 | g    |
| 15 | h    |
+----+------+
5 rows in set (0.00 sec)

分页

page = int(input('请输入要查看第几页'))
records = 10
page_offset = (page-1)*records # 计算从第几条数据开始
print('select * from t1 limit %s %s'%(page_offset,records))

4.1.9 排序

• asc 正序
• desc 倒叙
• 可以使用多个条件排序，前一个条件先进行排序，后面一个条件后进行排序

mysql> select * from idc_new;
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
|  3 | 纪蕴   | 上海     |
|  4 | 后场   | 邯郸     |
+----+--------+----------+
4 rows in set (0.00 sec)

mysql> select * from idc_new order by id asc;
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  1 | 博兴   | 北京     |
|  2 | 大族   | 北京     |
|  3 | 纪蕴   | 上海     |
|  4 | 后场   | 邯郸     |
+----+--------+----------+
4 rows in set (0.00 sec)

mysql> select * from idc_new order by id desc;
+----+--------+----------+
| id | name   | position |
+----+--------+----------+
|  4 | 后场   | 邯郸     |
|  3 | 纪蕴   | 上海     |
|  2 | 大族   | 北京     |
|  1 | 博兴   | 北京     |
+----+--------+----------+
4 rows in set (0.00 sec)

# 线倒叙，后limit 去后几行

mysql> select * from idc_new order by id desc limit 2;
+----+--------+----------+
| id | name | position |
+----+--------+----------+
| 4 | 后场 | 邯郸 |
| 3 | 纪蕴 | 上海 |
+----+--------+----------+
2 rows in set (0.00 sec)

 

4.1.10  分组

group by  对谁进行分组

mysql> select * from departments;
+----+--------+
| id | name   |
+----+--------+
|  4 | IT     |
|  5 | 销售   |
|  6 | 产品   |
+----+--------+
3 rows in set (0.00 sec)

mysql> select * from employee;
+----+--------+--------+
| id | name   | dpt_id |
+----+--------+--------+
|  1 | 老王   |      4 |
|  2 | 老郭   |      6 |
|  3 | 老刘   |      5 |
|  4 | 老惠   |      4 |
+----+--------+--------+
4 rows in set (0.00 sec)

# 查看各个部门都有多少人
mysql> select count(1),dpt_id from employee group by dpt_id;
+----------+--------+
| count(1) | dpt_id |
+----------+--------+
|        2 |      4 |
|        1 |      5 |
|        1 |      6 |
+----------+--------+
3 rows in set (0.00 sec)

如果对于聚合函数结果进行二次筛选时，必须使用having 

# 查看部门人数大于1的部门
mysql> select count(1),dpt_id from employee group by dpt_id having count(1) >1;
+----------+--------+
| count(1) | dpt_id |
+----------+--------+
|        2 |      4 |
+----------+--------+
1 row in set (0.00 sec)

4.2 连表查询

4.2.1 连表查询的方法

mysql> select * from departments;
+----+--------+
| id | name   |
+----+--------+
|  4 | IT     |
|  5 | 销售   |
|  6 | 产品   |
+----+--------+
3 rows in set (0.00 sec)

mysql> select * from employee;
+----+--------+--------+
| id | name   | dpt_id |
+----+--------+--------+
|  1 | 老王   |      4 |
|  2 | 老郭   |      6 |
|  3 | 老刘   |      5 |
|  4 | 老惠   |      4 |
mysql> select * from departments,employee where departments.id = employee.dpt_id;
+----+--------+----+--------+--------+
| id | name   | id | name   | dpt_id |
+----+--------+----+--------+--------+
|  4 | IT     |  1 | 老王   |      4 |
|  6 | 产品   |  2 | 老郭   |      6 |
|  5 | 销售   |  3 | 老刘   |      5 |
|  4 | IT     |  4 | 老惠   |      4 |
+----+--------+----+--------+--------+
   |      4 |
+----+--------+--------+
4 rows in set (0.00 sec)

 

mysql> select * from employee left join departments on employee.dpt_id = departments.id;
+----+--------+--------+------+--------+
| id | name   | dpt_id | id   | name   |
+----+--------+--------+------+--------+
|  1 | 老王   |      4 |    4 | IT     |
|  4 | 老惠   |      4 |    4 | IT     |
|  3 | 老刘   |      5 |    5 | 销售   |
|  2 | 老郭   |      6 |    6 | 产品   |
+----+--------+--------+------+--------+
4 rows in set (0.00 sec)

mysql> select * from employee right join departments on employee.dpt_id = departments.id;
+------+--------+--------+----+--------+
| id   | name   | dpt_id | id | name   |
+------+--------+--------+----+--------+
|    1 | 老王   |      4 |  4 | IT     |
|    2 | 老郭   |      6 |  6 | 产品   |
|    3 | 老刘   |      5 |  5 | 销售   |
|    4 | 老惠   |      4 |  4 | IT     |
+------+--------+--------+----+--------+
4 rows in set (0.00 sec)

mysql> select * from employee inner join departments on employee.dpt_id = departments.id;
+----+--------+--------+----+--------+
| id | name   | dpt_id | id | name   |
+----+--------+--------+----+--------+
|  1 | 老王   |      4 |  4 | IT     |
|  2 | 老郭   |      6 |  6 | 产品   |
|  3 | 老刘   |      5 |  5 | 销售   |
|  4 | 老惠   |      4 |  4 | IT     |
+----+--------+--------+----+--------+