python Mysql 基本操作

1、mysql常用增删查改

连接：
mysql -uroot -pgloryroad
mysql -uroot -p
password：

mysql -h39.1.1.1 -uroot -pgloryroad
默认端口：3306

mysql -h39.1.1.1 -uroot -pgloryroad -P3306 #端口号

show databases;
create database test51;

#查看当前使用的数据库
use jin;
select database();

#使用哪个数据库
use test;

#创建数据库gloryroad
CREATE DATABASE IF NOT EXISTS gloryroad DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

#查看存在的表
show tables;

#创建表
create table student(
    id int(11) not null auto_increment,
    name varchar(20) not null,
    age int(3),
    admissiondate datetime default NOW(),
    primary key(id)
    )engine=innodb default charset=utf8;

#查看建表语句
show create table student;

#删除表
drop table student;

#插入表数据
insert into student (id,name,age,admissiondate) values(1,"张三","18","2019-10-10 10:10:10");
insert into student values(2,"李四","29",now());

insert into student(name,age) values("王五","18");

#****************批量插入数据***********
insert into student (name,age) values("赵六",38),("郑七",48),("田八",58);

#修改数据
update student set name = "孙八",age=18 where name="孙八";

#删除表数据
delete from student where name = "孙八" and age=18;

#查询数据 and or !=
select * from student where name="张三" and age=18;
select * from student where name="张三" or age=18;
select * from student where name!="张三" and age!=18;

#模糊查询 like '%san'
select * from student where name like "%san" and age=18;
select * from student where name like "%臣秀%";

#字段的别名
select name 姓名,age 年龄 from student where name like "%臣秀%";

#count(*),avg(),sum()
select count(age) 年龄 from student;
select AVG(age) from student;
select SUM(age) from student;

#查询结果小数取整 round
select ROUND(AVG(age),3) from student;

#排重 distinct
select distinct age from student;

# in,not in; not in 不走索引 效率比较低
select name  from student where age in (18,48);

#分组
select age,COUNT(*) from student group by age;
#having
select age,COUNT(*) from student group by age having COUNT(*)>1;

select id,age,COUNT(*) from student where id>3 group by id,age having COUNT(*)>=1;

#排序asc,desc
select * from student order by age desc;

#子查询
select * from student where age in(select age from student where id>2);
create table grade11(
    id int(11) auto_increment,
    stuid int(11),
    course varchar(20) not null,
    grade int(5),
    primary key(id)
    )engine innodb character set utf8;

select * from student where id in (select stuid from grade11 where grade>80);

#union（结果会排重）、union all（结果不会排重）

select id from student where id>3
union
select id from grade11 where grade<=90;
select id from student where id>3
union all
select id from grade11 where grade<=90;

#执行计划查看
explain select s.name ,g.course_name ,g.grade from student s,grade g where s.id =g.stu_id \G;

#左连接、右连接、内连接
#右连接 显示右表的全部记录，关联不到的显示null
#左连接 显示坐标的全部记录，关联不到的显示null
select   s.`name`, g.`course`,g.`grade` from  grade11 g right join student s on g.stuid = s.`id`;

#加索引
alter table grade11 add index stuid_indx(stuid);

#查看数据库最大连接数
show variables like 'max_connections';

#查看自动提交参数
show variables like '%autocommit%';

#查看表字段
desc grade;

#limit 从第6行取记录，取3条；
select * from studentinfo limit 5,3;

2、python 操作mysql的常用小例子

import pymysql

# 打开数据库连接
conn = pymysql.connect(
    host = "127.0.0.1", 
    port = 3306,
    user = "root", 
    passwd = "root", 
    db = "jin11", 
    charset = "utf8")  

# 使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
print(cursor)
print(type(cursor))

#建库操作：

cur.execute('CREATE DATABASE IF NOT EXISTS pythonDBnew1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')

#建表操作
cur.execute('''CREATE TABLE `User`(
    `id` int(11) DEFAULT NULL,
    `name` varchar(255) DEFAULT NULL,
    `password` varchar(255) DEFAULT NULL,
     `birthday` date DEFAULT NULL
                   )ENGINE=innodb DEFAULT CHARSET=utf8;''')

##SQL注入 类似在输入框输入一些条件拼接SQL，比如输入 or 1=1,导致查询条件永远满足

#使用模板字符串插入数据
sql = "insert into user values(%s, %s, %s, %s)"
insert = cursor.execute(sql, (3,'lucy','efg','1993-02-01'))

#oracle的操作风格

　　sql="select t.env_sub_type,t.env_key,t.env_key_value from b_env_info t where t.env_no=:1 and t.env_sub_type=:2"
　　cur.execute(sql,[env_num,db_name])  #列表中的元素替代 占位符 :1,:2

#批量插入数据
for i in range(10,20):
    # 另一种插入数据方法，通过格式字符串传入值，此方式可以防止sql注入
    sql = "insert into user values(%s, %s, %s, %s)"
    cursor.execute(sql, (random.randint(1,10000),'lucy'+str(random.randint(1,10000)),'efg'+str(random.randint(1,10000)),now()))

##批量插入多条数据
sql = "insert into user values(%s, %s, %s, %s)"
insert = cursor.executemany(sql, [
    (5,'tom','tom','1989-03-17'), 
    (6,'amy','test','1898-12-01'),
    (7,'lily','linux','1994-06-23')])

#获取查询结果的单条数据
cursor.execute("select * from user")
while 1:
  res = cursor.fetchone()
  if res is None:
    # 表示已经取完结果集
    break
  print(res)
  #将读取到的时间格式化
  print(res[-1].strftime("%Y-%m-%d"))

#获取查询结果的多条数据
cursor.execute("select * from user")

# 获取游标处两条数据
resTuple = cursor.fetchmany(2)
print("结果集类型：", type(resTuple))
for i in resTuple:
  print(i)

#获取全部查询结果
cursor.execute("select * from user")
resSet = cursor.fetchall()
print("共%s条数据。" %len(resSet))
print(resSet)

# 批量更新数据
cursor.executemany("update user set password = %s where name=%s", [('tomx2x', 'tom'), ('Tomx2x', 'amy')])

# 批量删除数据
delete = cursor.executemany("delete from user where name=%s", [('amy',), ('lily',)])
print("删除语句影响的行数：", delete)

#事务回滚
conn.rollback()

#操作完成后，提交
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()