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;

多表查询：

select student.stu_no, student.name, score.course, score.term, score.mark
from student, score
where student.stu_no=score_no;
// 通过 stu_no 匹配连接两表

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)