1、用mysql创建一张学生信息表，表中包含学号、姓名、性别、年龄、班级、专业。2、向表中插入10条数据3、查询所有学生信息4、查询所有男生信息5、统计每个专业的人数6、删除年龄小于18岁的

1、用mysql创建一张学生信息表，表中包含学号、姓名、性别、年龄、班级、专业。

2、向表中插入10条数据

3、查询所有学生信息

4、查询所有男生信息

5、统计每个专业的人数

6、删除年龄小于18岁的学生

#需在终端 pip install pymysql
#确保数据库能打开
import pymysql
sql="""
create table stu(
    id int primary key,
    stuname varchar(50),
    stusex varchar(20),
    stuage int,
    class varchar(20),
    major varchar(50)
);
"""
data=[
    (1,"anan","女",19,"39021","云计算技术"),
    (2,"eve","女",19,"39021","大数据技术"),
    (3,"bes","女",19,"39021","云计算技术"),
    (4,"anna","女",17,"39021","云计算技术"),
    (5,"Vic","男",18,"39021","云计算技术"),
    (6,"Roy","男",19,"39021","大数据技术"),
    (7,"hans","男",16,"39021","云计算技术"),
    (8,"jane","女",15,"39021","大数据技术"),
    (9,"lucy","女",20,"39021","云计算技术"),
    (10,"june","女",14,"39021","大数据技术")
]
#利用python操作数据库
try:
    #1.建立连接
    #database='stuinfo'确保已存在数据库中    con=pymysql.connect(host='localhost',user='root',password='000000',database='stuinfo',charset='utf8')
    print("连接成功")
    #2.创建游标对象
    cur=con.cursor()
    #3.执行sql语句，结果得res
    cur.execute(sql)
    cur.executemany("insert into stu values(%s,%s,%s,%s,%s,%s)",data)
    #5.查询数据
    
    print("查询所有学生信息")
    cur.execute("select * from stu")
    rows3=cur.fetchall()
    for i in rows3:
        print(i)
    
    cur.execute("select * from stu where stusex='男'")
    rows4=cur.fetchall()
    print("查询所有男生信息：")
    for i in rows4:
        print(i)
    
    cur.execute("select major,count(*) from stu group by major")
    rows5=cur.fetchall()
    print("查询每个专业的人数：")
    for i in rows5:
        print(i)
        
    cur.execute("delete from stu where stuage<18")
    cur.execute("select * from stu ")
    rows6=cur.fetchall()
    print("查询删除年龄小于18的人数：")
    for i in rows6:
        print(i)
    
    #4.提交事务
    con.commit()
    #6.关闭连接与游标
    cur.close()
    con.close()
except Exception as err:
    print(err)