mysql数据库查询语言2

文中有一些对数据库具体操作的举例，而举例内容不便都一起上传。

 

6. 自连接
    是一种特殊的连接方式
        连接需要对 左表和右表分别起别名<左表和右表是同一表>

    select * from 表 as 别名1 join 表 as 别名2 on 条件;

    查询笛卡尔积的记录数量
        select count(*) from areas as pro join areas as city;

    查询北京市下属所有区
        左表取北京市相关aid atitle 等信息 右表取下一级行政机构<该机构的pid 应该和北京市的aid相等>
        mysql> select * from areas as pro join areas as city on pro.aid = city.pid where pro.atitle='北京市';
        mysql> select * from areas as pro join areas as city on pro.aid = city.pid where pro.atitle='广东省';

7. 子查询

    分类: 
        标量子查询 返回的结果是 一个值 一行一列
        列                  一列多行
        行                  一行多列

    标量子查询 结果为一行一列

        找出身高高于平均身高的学员信息
        1 求出平均身高 172.15
        2 select * from students where height > 172.15;
        mysql> select * from students where height > (select avg(height) from students);

    行子查询 结果为一行多列
        求出班级中 身高最高并且年龄最大的学生信息
        1 求出  最高的身高 最大的年龄  185 29

        2 select * from students where height = 185 and age = 29;
          select * from students where (height,age) = (185,29);
          select * from students where (height,age) = (select max(height),max(age) from students);
    列子查询 结果为一列多行
        找出已经开班班级的所有学生信息
        1 找出已经开班的班级信息   1,2
        2 select * from students where cls_id in (1,2);
          select * from students where cls_id in (select id from students);

8.     SQL语句中特殊的语法
    insert into ... select 
    mysql> insert into goods_cates (name)  select cate_name from goods group by cate_name;
    # 也可以在创建表的同时直接对内容进行添加
        create table goods_brand(
        #因为直接添加了name字段的数据，而主键信息没有指定，所以必须设置auto_increment自动增长
    -> id int unsigned primary key auto_increment,
    -> name varchar(40) not null)
    -> select brand_name from goods group by brand_name;

    连表更新
        使用一个表的字段数据去修改另一个表中字段的数据

    mysql> select * from 表;
        select * from goods join goods_cates on goods.cate_name = goods_cates.name; 
        把这个 goods join goods_cates on goods.cate_name = goods_cates.name 看作一张虚表
    
    update 表  set 字段 = 值 where 条件;

        使用 商品分类表中 的id 去更新 goods表商品表中cate_name
        # 标准SQL
        update goods join goods_cates on goods.cate_name = goods_cates.name
            set goods.cate_name = goods_cates.id;

        # MYSQL支持
        update goods,goods_cates
            set goods.cate_name = goods_cates.id
            where goods.cate_name = goods_cates.name;

        
        使用  品牌表中的品牌id去 修改商品表中 品牌信息
        update goods join good_brands on goods.brand_name = goods_brands.name    
            set goods.brand_name = goods_brands.id;

        修改商品表的表结构中的字段名 类型 约束

        mysql> alter table goods change cate_name cate_id int unsigned not null,
            change brand_name brand_id int unsigned not null;

9. 外键约束：带有唯一性约束
    含有外键的表为子表   提供外键所需的数据的表为父表
    子表的外键数据在  父表数据中一定要存在

    增加外键约束的字段一定引用的是其他表的主键吗?  不一定  ,但是一般都是 引用的其他表的主键
    添加外键约束
        创建表时直接添加
        # 添加外键时，必须保证外键的数据类型与引用的数据类型一致。
            mysql> alter table goods add foreign key (cate_id) references goods_cates(id);

        表已存在 修改表结构
            create table xxx(
                id xxxx,
                cate_id int xxxx,
                foreign key (cate_id) references goods_cates(id);
                );
    查看外键约束
        mysql> show create table goods;
        
        得到外键约束名为 
    删除外键约束
        alter table 表名 drop FOREIGN key 外键约束名称;

10. 范式-ER图-了解
    数据库设计范式
        1范式 字段中不允许出现多维度数据
        2范式 1范式 + 必须有主键 + 非主键字段必须完全依赖于主键(而不能部分依赖)
        3范式 2范式 + 非主键字段必须直接依赖于主键(而不能传递依赖)
    
    E-R模型
        实体 对应 数据库中一个表
        联系 1:1 1:多可以直接表示在数据库中; 多:多则需要使用 中间关系表 表示
        属性 对应 数据库中表中一个字段

11. 数据库编程 
    
    使用程序的方式 和数据库进行交互 完成对数据的增删改查<自动化>
    步骤:
    import pymysql
    # setting 设置解释器
    1 创建和数据库的连接
    con = pymysql.connect(host="127.0.0.1",port=3306,user = "root")
    2 获取游标

    3 通过游标执行SQL语句
        如果有数据更新操作 需要调用连接对象.commit()方法提交数据

    4 关闭游标

    5 关闭连接
        
        # 使用数据库需先导入pymysql
        import pymysql
        
        # 1. 创建连接
        # host：服务器主机地址  port 数据库端口号  user 用户名 password密码 database 数据库名字  charset编码字符集
        conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="mysql", database="python18", charset="utf8")
        # 连接之后可以获取一个游标
        cur = conn.cursor()
        
        # SQL语句 可以直接用在python语句中
        # sql = 'select * from students;'
        sqll = "select * from students;update students set name = '校花' where id =1;insert into students (name,age) values ('张三丰',18);"
        
        # 通过游标执行(execute)SQL语句
        content = cur.execute(sqll)
        
        # 通过游标获取执行结果(fetchall获取全部的结果)(fetchone获取一个结果)
        # 获取的结果是一个元组，每一条执行结果也都是一个元组((),(),())
        print(cur.fetchall())
        
        # 如果需要将修改的数据保存到数据库中　需要提交commit　到数据库
        # 提交: 使用连接对象的commit方法
        conn.commit()
        
        # 关闭游标
        cur.close()
        
        # 关闭连接
        conn.close()