数据库 // navicat使用 // mysql // python内连接数据库

ゝ一世哀愁。 2022-12-24 11:58 254阅读 0赞

# Navicat内建表+查询 #

插入语句：

> insert into 表名(属性1, 属性2, 属性3) value (xxx, xxx, xxx);  
> // 插入单条记录  
> insert into 表名(属性1, 属性2, 属性3) values (xxx, xxx, xxx), values (xxx, xxx, xxx), values (xxx, xxx, xxx);  
> // 插入多条记录

删除语句：

> delete from 表名 where 查询条件  
> 例子：  
> delete from student where name=”张三”;

修改语句：

> update 表名 set 属性名=xxx where 查询条件  
> 例子：  
> update student set name=‘小阿giao’ where id=3;

查询语句：

> select 需要查询的字段 from 表名 where 查询条件;  
> 例子：  
> select \* from student where sex=1;  
> // \*代表该记录的所有属性

排序：

> select \* from student order by sex ASC; // 升序排序  
> select \* from student order by sex DESC; // 逆序排序  
> select \* from student order by sex DESC, stu\_no DESC; // 多种排序

模糊匹配：

> select \* from student where name like ‘李%’ ; //姓李的同学  
> select \* from student where name like ‘%小%’; //名字中有小的同学  
> select \* from student where name like ‘%小’; //名字末尾是小的同学  
> select \* from student where name in (‘xxx’, ‘xxx’, ‘xxx’);  
> select sex, count(student\_no) from student group by sex;

> /\*  
> count()函数：统计当前查询结果个数，参数为所统计的属性名（列名），  
> 返回除去null以外的所有行的总数，  
> 如为\*则返回表格中所有存在的行的总数（包括null）  
> group by：根据一个或多个列对结果集进行分组。  
> \*/

> select course as “学科”, max(mark) as “最高分”, min(mark) as “最低分”, sum(mark) / count(\*)  
> from score  
> group by course  
> order by course;

多表查询：

> 1.  
> 
> select student.stu\_no, student.name, score.course, score.term, score.mark  
> from student, score  
> where student.stu\_no=score\_no;  
> // 通过 **stu\_no** 匹配连接两表

> 1.  
> 
> select student.stu\_no, student.name, score.course, score.term, score.mark  
> from student join score  
> on where student.stu\_no=score\_no;  
> // 用 **join…on** 将两张表拼成一张表，效果同上

> 3.左连接，以**前表**为主  
> select student.stu\_no, student.name, score.course, score.term, score.mark  
> from student **left** join score  
> on where student.stu\_no=score\_no;

> 4.右连接，以**后表**为主  
> select student.stu\_no, student.name, score.course, score.term, score.mark  
> from student **right** join score  
> on where student.stu\_no=score\_no;

> 嵌套子查询：  
> select stu\_no, course, mark from score // **外查询**  
> where stu\_no in  
> (  
> select stu\_no from student where name in (“张三”, “Alice”); //**内查询**  
> )

# python内连接数据库 #

# encoding = utf-8
    import MySQLdb
    
    def connect():
        # 连接mysql
        conn = MySQLdb.connect(
            host="121.36.230.205",  # 服务器地址
            port=3306,  # 端口
            user="root",  # 用户名
            passwd=" ",  # 密码
            db="test",  # 目标数据库
            charset="utf8"
        )
        cursor = conn.cursor()  # 获得cursor容器/光标
        return conn, cursor
    
    
    def close(conn, cursor):
        cursor.close()
        conn.close()
    
    
    def select(cursor):
        # 查询
        sql = "select * from test.student"  # sql命令
        with open("select.sql", "r", encoding="utf8")as f:
            sql2 = "\n".join(f.readlines())
        sql3 = "select * from student " \
               "where student_no in" \
               "(" \
               "select student_no from student where name in ('王小小', '王大大')" \
               ")"
        cursor.execute(sql3)    # 执行sql命令
        data = cursor.fetchall()    # 返回元组
        # data = cursor.fetchone()    # 返回单条数据
        for r in data:
            print(r[0], r[1], r[2])
    
    
    if __name__ == "__main__":
        conn, cursor = connect()
        select(cursor)
        close(conn, cursor)