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 | +----+--------+--------+----+--------+
