精华内容
下载资源
问答
  • where条件里面不到的字段不创建索引 单值索引/复合索引的选择问题,who?(高并发下倾向创建组合索引) 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度 查询中统计或分组的字段 哪些情况不适合...

    哪些情况适合建索引?

    • 主键自动建立唯一索引
    • 频繁作为查询条件的字段应该创建索引
    • 查询中与其他表关联的字段,外键关系建立索引
    • 频繁更新的字段不适合建立索引(因为每次更新不止更新了记录还会更新索引)
    • where条件里面用不到的字段不创建索引
    • 单值索引/复合索引的选择问题,who?(高并发下倾向创建组合索引)
    • 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
    • 查询中统计或分组的字段

    哪些情况不适合建索引?

    • 表记录太少

    • 经常增删改的表(提高了查询的速度,同时却降低了更新表的速度,因为更新时,不仅要保存数据,还要保存一下索引文件)

    • 数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列建立索引(如何某个数据列包含了许多的重复内容,为他建立索引就没有太大的实际内容)

    展开全文
  • 最近在使用JPA做项目,虽然JPA使用起来比mybatis方便,但是涉及到多表关联查询的时候就麻烦,往往需要自己写SQL语句。但是如果查询的时候条件不确定,比如说:查询员工可以通过工号或者姓名来查询。我们不知道他要...

    最近在使用JPA做项目,虽然JPA使用起来比mybatis方便,但是涉及到多表关联查询的时候就麻烦,往往需要自己写SQL语句。但是如果查询的时候条件不确定,比如说:查询员工可以通过工号或者姓名来查询。我们不知道他要通过工号还是姓名,或者两个条件都要,又或者什么条件都不要,但是要查询全部信息。
    通过MySQL数据库的 if 判断 可以完美解决这个问题,以下就是例子,当传入的工号或者姓名为空时,就选择1=1,条件成立,查询全部。如果传入的参数不为空,则按着参数的值进行匹配。以下是代码。备注:?1 代表方法的参数列表第一个参数。以此类推,?2 就是第二个参数,同时也可以通过@param(“xxx”)来指定参数,使用的时候把 ?1 改成 :xxx 。

    普通查询

     //查询员工
    @Query(value = "SELECT * FROM  emp " +
           "WHERE  if(?1= '' OR ?1 is null ,1=1, code like %?1%) " +
           " AND if(?2= '' OR ?2 is null ,1=1, `name` like %?2%) " ,
            nativeQuery = true)
     List<Map> findEmp( String code,  String name);
    

    带分页查询 (备注:不带分组GROUP BY )
    如果需要带分页的话,需要传入JPA的分页对象 Pageable,需要两段SQL,一个SQL用于查询数据,一个SQL用于查询总记录数,要在@Query的countQuery注入查询总记录数的SQL,代码如下。

     //查询员工
    @Query(value = "SELECT * FROM  emp " +
           "WHERE  if(?1= '' OR ?1 is null ,1=1, code like %?1%) " +
           " AND if(?2= '' OR ?2 is null ,1=1, `name` like %?2%) " ,
           countQuery = "SELECT count(*) FROM emp "+
           "WHERE  if(?1= '' OR ?1 is null ,1=1, code like %?1%) " +
           " AND if(?2= '' OR ?2 is null ,1=1, `name` like %?2%) " ,
           nativeQuery = true)
     Page<Map> findEmp( String code,  String name);
    

    带分页和分组查询
    如果加了GROUP BY 进行分组就会报错,原因是查询总记录数的时候,返回值必须是单个数值,分组后查询的结果有可能是多条记录,所以就报错。那我们就把这多条记录当成一张表,然后在进行统计总数。

     //查询员工
    @Query(value = "SELECT * FROM  emp " +
           "WHERE  if(?1= '' OR ?1 is null ,1=1, code like %?1%) " +
           " AND if(?2= '' OR ?2 is null ,1=1, `name` like %?2%) " +
           " GROUP BY dept_id ",
           countQuery = "SELECT count(*) FROM (SELECT count(*) FROM emp "+
           "WHERE  if(?1= '' OR ?1 is null ,1=1, code like %?1%) " +
           " AND if(?2= '' OR ?2 is null ,1=1, `name` like %?2%) " +
           "GROUP BY dept_id ) tempTable",
           nativeQuery = true)
     Page<Map> findEmp( String code,  String name);
    
    展开全文
  • 这里,我总结了在数据库上,SQL语言对数据排序、过滤和分组,以及表、视图、联结、子查询、游标、存储过程和触发器等内容。 数据库相关 查所有数据库 show databases; 创建数据库 create database 数据库名; 查看...
  • 如果给我们一张表,我们是从(from)这张表开始查,然后如果需要过滤就(where)过滤,之后如果引用了聚合函数,就需要进行分组(group by),接下来如果我们需要对这前面阶段–分组得到的结果进行二次过滤的时候,...

    如题:2019年10月

    分析:

    从答案和题干,删除表,首先想到的就是drop table.

    alter table:更改表的结构。

    delete table:没有此用法,sql中只有delete from tbl_name;并且也只是删除表中的数据。

    revoke table:也没有此用法,revoke是删除某用户权限。

    从这里也可以看出,虽然SQL命令从书中看,有的非常长,不好记忆!但其实是“SQL都是为解决具体问题而产生的,这个应用的情形,一定要脑补出来,才能记得住”。

    以后遇到了这样的语法题,一定要深挖些“为解决何种问题”。这样每遇到一个解释一个,时间长了,就会熟记SQL。反正,就那么多命令。

    详见P148页,答案:选B,如下:

    注:除了数据库的设计思想外,就是语言了。掌握SQL也是很实用的。

    什么是SQL呢?

    Structured Query Language的缩写,意思是结构化查询语言,是一种在数据库管理系统(Relational :关系 Database Management System, RDBMS)中查询数据,或通过RDBMS对数据库中的数据进行更改的语言。

    常见的数据库:

    • Oracle Database:甲骨文公司的RDBMS
    • SQL Server :微软公司的RDBMS
    • DB2:IBM 公司的RDBMS
    • PostgreSQL:开源的RDBMS
    • MySQL :开源的RDBMS

    SQL语句执行过程:

    用户在客户端通过SQL语言,将需要的数据和对数据进行的操作的请求发送给RDBMS,RDBMS 根据该语句的内容返回所请求的数据,或者对存储在数据库中的数据进行更新。

    如同linux的shell命令一样中间是隔了一层操作系统的,正是由于Rdbms的存在才使得数据实现集中高效的处理和操作,其实是将数据与操作分离,还是计算机分层的思想。

    根据对RDBMS 赋予的指令种类的不同,SQL 语句可以分为以下三类。

    ●DDL(Data Definition Language,数据定义语言)

    用来定义和管理数据对象,包括:数据库、数据表、函数、视图、索引、触发器等。DDL 包含以下几种指令。

    CREATE: 创建数据库和表等对象

    DROP: 删除数据库和表等对象

    ALTER: 修改数据库和表等对象的结构

     

    ●DML(Data Manipulation Language,数据操纵语言)

    用来操作数据库对象中所包含的数据,增、删、改、查。DML 包含以下几种指令。

    SELECT:查询表中的数据,这部分非常重要,也是使用最为频繁的语句,详见:《SQL考点之SQL查询》

    INSERT:向表中插入新数据

    UPDATE:更新表中的数据

    DELETE:删除表中的数据

     

    ●DCL(Data Control Language,数据控制语言)

    用来确认或者取消对数据库中的数据进行的变更。除此之外,还可以对RDBMS 的用户是否有权限操作数据库中的对象(数据库表等)进行设定。DCL 包含以下几种指令。

    COMMIT: 确认对数据库中的数据进行的变更

    ROLLBACK: 取消对数据库中的数据进行的变更

    GRANT: 赋予用户操作权限

    REVOKE: 取消用户的操作权限

    Ⅰ. DDL(Data Definition Language,数据定义语言)

    1、 创建数据库(CREATE)

    语法:

    如: 

    CREATE DATABASE shop;

    切换数据库语法:use db_name;

    修改数据库:alter {database | schema}[db_name];

    如:

    alter database mysql_test
    default character set gb2312
    default collate gb2312_chinese_ci;

    删除数据库:drop {database | schema}[if exists]db_name;

    查看数据库:show {database | schema}[like 'pattern' | where expt];

    注:like 关键字用于匹配指定的数据库名称,where从句用于指定数据库名称查询范围的条件。

     

    2、创建表(CREATE)

    基本语法:

    注:temporary:临时,表示创建的是临时表,不被永久存储。当断开数据库时,会自动的删除。

    如: 

    CREATE TABLE Product
    (product_id     CHAR(4)      NOT NULL,
     product_name   VARCHAR(100) NOT NULL,
     product_type   VARCHAR(32)  NOT NULL,
     sale_price     INTEGER      ,
     purchase_price INTEGER      ,
     regist_date    DATE         ,
     PRIMARY KEY (product_id));

    每一列的数据类型(后述)是必须要指定的,数据类型包括:

    • INTEGER 整数型
    • NUMERIC ( 全体位数, 小数位数)
    • CHAR 定长字符串
    • VARCHAR 可变长字符串
    • DATE 日期型

     

    删除表(DROP)

    语法:

     注:restrict:限制 cascade:级联

    DROP TABLE Product;

     

    表定义的更新(ALTER)

    • 在表中增加一列(ADD COLUMN)
    ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);
    #比如书上的例子
    alter table mysql_test.customers
    add column cast_city char(10) not null default 'wuhan' after cust_sex;

    也可以使用 add primary key、add foreign key 、add index为表添加主键,外键,索引。

    • 同时修改列名称或数据类型(change [column])

    将数据库mysql中表customers的cust_sex重命名为sex,且将数据类型更改为char(1),允许其为NULL,默认值为‘M’。

    alter table mysql_test.customers
    change column cust_sex sex char(1) NULL default 'M';
    • 修改列的数据类型(modify [column]),可通过 ‘first' 'after'修改指定列在表中的位置。

     将cust_name数据类型由之前char(50)改成char(20),并将此列设置为第一列

    alter table mysql.custormers
    modify column cust_name char(20) first;
    • 修改或删除列的默认值添加子句(alter [column])

    将custormers的cust_city列的默认值改为字符常量‘bejing’

    alter table mysql.custormers
    alter column cust_city set default 'beijing';
    • 在表中删除一列(DROP COLUMN)
    ALTER TABLE Product DROP COLUMN product_name_pinyin;

    也可以使用 drop primary key、drop foreign key 、drop index为表删除主键,外键,索引。

    • 变更表名(RENAME)或添加(rename [to])子句
    alter table mysql_test.custormers
    rename to mysql_test.backup_custormers;

     或

    RENAME TABLE Poduct to Product;

    查看表:

    A、查看表名称(show tables)

    语法:

    show [full] tables [{from | in}db_name][like 'pattern' | where expr];

    B、查看表结构(show columns)或describe

    show [full] columns {form | in} tb_name [{from | in} db_name] [like 'pattern' | where expr];

    {describe | desc} tb_name [col_name |wild];  --mysql的show columns from的快捷方式

    3、创建索引:

    索引(Index)是帮助MySQL高效获取数据的数据结构。
    在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的。MyISAM和InnoDB存储引擎只支持BTREE索引,MEMORY/HEAP存储引擎支持HASH和BTREE索引。    

    • 索引的使用原则

    A、主键自动建立唯一索引
    B、频繁作为查询条件的字段应该创建索引
    C、查询中与其他表关联的字段,外键关系建立索引
    D、频繁更新的字段不适合建立索引,因为每次更新不单单是更新了记录还会更新索引
    E、WHERE条件里用不到的字段不创建索引
    F、单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)
    G、查询中排序的字段,排序的字段若通过索引去访问将大大提高排序速度
    H、查询中统计或者分组字段
    不适合使用索引的场合:
    A、对经常更新的表就避免对其进行过多的索引,对经常用于查询的字段应该创建索引。
    B、数据量小的表最好不要使用索引,由于数据较少,可能查询全部数据花费的时间比遍历索引的时间还要短,索引就可能不会产生优化效果。
    C、在不同值少的列上不要建立索引,比如在学生表的"性别"字段上只有男,女两个不同值。在一个不同值较多的列可以建立索引。

    • 索引的分类

    单列索引

    单列索引只包含单个列,但一个表中可以有多个单列索引。
    A、普通索引
    普通索引允许在定义索引的列中插入重复值和空值。
    B、唯一索引
    索引列中的值必须是唯一的,但是允许为空值。
    C、主键索引
    主键索引是一种特殊的唯一索引,不允许有空值。

    复合索引

    在表中的多个字段组合上创建的索引,只有在查询条件中使用了组合的多个字段的左边字段时,索引才会被使用,使用复合索引时遵循最左前缀集合。

    全文索引

    全文索引,只有MyISAM存储引擎支持,只能在CHAR、VARCHAR、TEXT类型字段上使用全文索引。
    全文索引主要用来查找文本中的关键字,而不是直接与索引中的值相比较。在数据量较大时候,先将数据放入一个没有全文索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。

    空间索引

    空间索引是对空间数据类型的字段建立的索引,MySQL中的空间数据类型有四种:GEOMETRY、POINT、LINESTRING、POLYGON。
     在创建空间索引时,使用SPATIAL关键字。
     空间索引必须使用MyISAM存储引擎, 并且空间索引的字段必须为非空。

    • 创建表时创建索引的语法:
    CREATE TABLE table_name[col_name data type]
    [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][index_name](col_name[length])[asc|desc]

    如:

    在mysql_test,新建一个卖家信息表,seller,卖家ID和售卖产品类型作为主键,并在当月销量上创建索引。

    create table seller
    (
        seller_id int not null auto_increment,
        seller_name char(50) not null,
        seller_address char(50) null,
        seller_cotace char(50) null,
        product_type int(5) null,
        sales int null,
        primary key(seller_id,product_type),
        index index_seller(sales)
    );

     

    向已有表添加索引的语法:

    ALTER TABLE tablename ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [indexname] (col_name)[ASC|DESC];
    CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] indexname ON tablename(col_name[length])[ASC|DESC];

    unique|fulltext为可选参数,分别表示唯一索引、全文索引
    index和key为同义词,两者作用相同,用来指定创建索引
    col_name为需要创建索引的字段列,该列必须从数据表中该定义的多个列中选择
    index_name指定索引的名称,为可选参数,如果不指定,默认col_name为索引值
    length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度
    asc或desc指定升序或降序的索引值存储
    在创建索引时如果不指定索引名,默认使用字段名作为索引名。

    如:

    在seller表姓名列上添加一个非唯一索引,取名为index_seller_name.

    alter table mysql.seller
    add index index_seller_name(seller_name);

    普通索引创建:

    直接创建索引
    CREATE INDEX index_name ON tablename[column[length][asc | desc]]

    注:length用于指定使用列的前n个字符来创建索引。asc | desc 用于指定索引是按升序还是降序排列
    修改表结构
    ALTER TABLE table_name ADD INDEX index_name ON (column(length))
    创建表时指定索引

    CREATE TABLE tablename
    (
    col_name1 type,
    col_name2 type,
    INDEX index_name(col_name)
    );

    唯一索引的创建

    索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
    直接创建唯一索引
    CREATE UNIQUE INDEX indexName ON tablename(column(length))
    修改表结构
    ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
    创建表时直接指定

    CREATE TABLE tablename
    (
    col_name1 type,
    col_name2 type,
    UNIQUE INDEX index_name(col_name)
    );

    主键索引的创建

    修改表结构
    ALTER TABLE table_name ADD PRIMARY KEY(col_name)
    创建表时直接指定

    CREATE TABLE tablename
    (
    col_name1 type,
    col_name2 type,
    PRIMARY KEY(col_name)
    );

    复合索引的创建

    直接创建复合索引
    CREATE INDEX indexName ON tablename(col_name1,col_name2)
    修改表结构
    ALTER TABLE table_name ADD INDEX indexName(col_name1,col_name2)
    创建表时直接指定

    CREATE TABLE tablename
    (
    col_name1 type,
    col_name2 type,
    INDEX index_name(col_name1,col_name2)
    );

    全文索引的创建

    直接创建全文索引
    CREATE FULLTEXT INDEX indexName ON tablename(col_name)
    修改表结构
    ALTER TABLE table_name ADD FULLTEXT INDEX indexName(col_name)
    创建表时直接指定

    CREATE TABLE tablename
    (
    col_name1 type,
    col_name2 type,
    FULLTEXT INDEX index_name(col_name)
    );

    在使用全文索引时,需要借助MATCH AGAINST操作,而不是一般的WHERE语句加LIKE。全文索引的限制比较多,比如只能使用MyISAM存储引擎,比如只能在CHAR、VARCHAR、TEXT上设置全文索引。比如搜索的关键字默认至少要4个字符,比如搜索的关键字太短就会被忽略掉。
    SELECT * FROM tablename WHERE MATCH(col_name) AGAINST('pattern');
    col_name为全文索引列,'pattern'为匹配的字符串

    • 索引的删除
    DROP INDEX [indexName] ON tablename; 
    ALTER TABLE tablename DROP INDEX indexname;
    • 索引信息的查看
    show {index | keys | indexs}
    {from | in} tbl_name
    [{from | in}db_name]
    [where expr]

    4、创建视图

    视图是由SELECT查询语句所定义的一个虚拟表,是查看数据的一种非常有效的方式。视图包含一系列带有名称的数据列和数据行,但视图中的数据并不真实存在于数据库中,视图返回的是结果集。

    三级模式中,外模式对应到数据库中的概念就是视图。视图对重构数据库提供了一定程度的逻辑独立性,使用户可以将注意力集中在所关心地数据上。

    • 创建视图(CREATE VIEW)

    语法:

    create view view_name [(column_list)]
    as select_statement
    [with[cascaded | local] check option]

     注:

    view_name:视图名称在数据库中必须是唯一的。

    column_list:为视图中的每个列指定明确的名称,缺省则使用基本表与源视图一样的列名。

    with check option:用于指定在可更新视图上所进行的修改都要符合select_statement所指定的限制条件

    [cascaded | local],修改时,决定检测范围。cascaded:对所有相关视图进行检查。local只对定义视图进行检查。

    如:创建学生信息的视图:

    create view studentview
    as select studentID, sname, sex from TStudent;

    再如书上:创建视图 customers_view,要求显示所有男性信息,并保证今后对该视图修改都符合性别为男性。

    create view mysql_test.customers_view
    as
    select * from mysql_test.customers
    where cust_sex='M'
    with check option;

     再如:

    CREATE VIEW ProductSum (product_type, cnt_product)
    AS
    SELECT product_type, COUNT(*)
      FROM Product
     GROUP BY product_type;

    注意:定义视图时不能使用ORDER BY子句

    • 使用视图

    视图的使用和普通表一样。这是对视图最常用的操作。

    select * from studentview;

    再如书上:在视图customers_view中查找id号为905的客户姓名及地址

    select cus_name,cus_addr
    from mysql_test.customers_view
    where cus_id=905;

     

    • 删除视图(DROP VIEW),必须具有删除的权限

    语法:

    drop view [if exists]
     view_name[,view_name]...
    [restrict | cascade]

     restrict:限制 cascade:级联

    如:

    DROP VIEW ProductSum;
    • 修改视图定义 alter view

    语法与 create view一样,不再重复。

    也可以先删除视图,再创建视图来实现。

    如:

    CREATE OR REPLACE VIEW viewname AS SELECT [...] FROM [...];
    
    alter view studentview 
    as 
    select studentID as 学号, sname as 姓名, sex as 性别 from TStudent;
    • 修改视图数据: 

    不能在一张由多张关联表连接而成的视图上做同时修改两张表的操作;
    视图与表是一对一关系情况:如果没有其它约束(如视图中没有的字段,在基本表中是必填字段情况),可以进行增删改数据操作。

    如果视图的基表是一张表,可以通过视图向基表插入记录,要求视图中的没有的列允许为空。
    A、通过视图插入数据到表

    insert into studentview(studentID, sname, sex)VALUES('01001', '孙悟空', '男');

    MySQL数据库高级(三)——视图

      查询插入的记录,可以看到通过视图没有的列,值为空或默认值。

    B、通过视图删除表中记录
    视图的基表只能有一张表,如果有多张表,将不知道从哪一张表删除。

    delete from studentview where studentid='01001';

    C、通过视图修改表中记录
    只能修改视图中有的列。

    update studentview set sname='孙悟空' where studentid='00001';

     

    • 查看视图:

    查看已有的视图 show create view

    语法:

    show create view view_name

    view_name:要查看视图的名称。

    查看视图的信息 

    describe viewname;
    desc scoreview;

    查看所有的表和视图 

    show tables;

     查看视图的信息

    show fields from scoreview;

     

    子查询(一次性视图)

    -- 在FROM子句中直接书写定义视图的SELECT语句
    SELECT product_type, cnt_product
      FROM ( SELECT product_type, COUNT(*) AS cnt_product
                FROM Product
              GROUP BY product_type ) AS ProductSum;
    • 标量子查询

    在WHERE子句中使用标量子查询

    SELECT product_id, product_name, sale_price
      FROM Product
     WHERE sale_price > (SELECT AVG(sale_price)
                         FROM Product);

    注意:能够使用常数或者列名的地方,无论是SELECT 子句、GROUP BY 子句、HAVING 子句,还是ORDER BY 子句,几乎所有的地方都可以使用标量子查询。

    • 关联子查询
    SELECT product_type, product_name, sale_price
      FROM Product AS P1 
     WHERE sale_price > (SELECT AVG(sale_price)
                              FROM Product AS P2 
                          WHERE P1.product_type = P2.product_type
                            GROUP BY product_type);

    这里起到关键作用的就是在子查询中添加的WHERE 子句的条件。该条件的意思就是,在同一商品种类中对各商品的销售单价和平均单价进行比较。

    • 视图实例:

    1、使用视图创建视图

    创建视图的查询的表称为基表,基表可以是视图和表。

    create view sview
    as select studentID, sname, sex 
    from studentview where studentID>990 and sex='男';

    2、创建学生成绩表的视图

    创建一个视图,视图包含学生 学号、姓名、学科和成绩。

    create view view1
    as select a.StudentID,a.Sname,c.subJectName,b.mark  
    from TStudent a 
    join TScore b on a.StudentID=b.StudentID 
    join TSubject c on b.subJectID=c.subJectID;

     

    MySQL数据库高级(三)——视图

    创建成绩视图,包含学号、姓名、计算机网络课程成绩、数据结构成绩、JAVA开发成绩。

    create view scoreview
    as select studentid 学号,sname 姓名,
    AVG(case subjectname when '计算机网络' then mark END) 计算机网络,
    AVG(case subjectname when '数据结构' then mark END) 数据结构,
    AVG(case subjectname when 'JAVA开发' then mark END)  JAVA开发 from view1
    group by 学号;

     

    MySQL数据库高级(三)——视图

     

    ​​​​​​​

    Ⅱ. DML(Data Manipulation Language,数据操纵语言)

    只列出 表 增删改,查询详见《SQL考点之SQL查询、SQL支持数据类型》

    1、向表中插入数据(INSERT... values)

    语法:

    insert [into] tbl_name [(col_name,...)]
    {values | value}({expr | default},...),(...),...
    • 包含列清单
    INSERT INTO Product (product_id, product_name, product_type, sale_price, purchase_price, regist_date) 
    VALUES ('0001', 'T恤衫','衣服', 1000, 500, '2009-09-20');

    这个命令也是很常用的,一定要熟写熟记!!!!!

    • 省略列清单
    START TRANSACTION; 
    INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
    INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
    INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
    INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
    INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
    INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
    INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
    INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11');
    COMMIT;

     

    • insert...set给表中某列指定列值
    insert [into] tbl_name
    set col_name={expr | default},...
    • insert select从其他表中复制数据

    语法:

    insert [into] tbl_name [(col_name...)]
    select ...
    
    INSERT INTO ProductCopy (product_id, product_name, product_type,sale_price, purchase_price, regist_date)
    SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
      FROM Product;
    •  INSERT 语句中的SELECT 语句,也可以使用WHERE 子句或者GROUP BY 子句等。
    INSERT INTO ProductType (product_type, sum_sale_price, sum_purchase_price)
    SELECT product_type, SUM(sale_price), SUM(purchase_price)
      FROM Product
     GROUP BY product_type;

     

    2、数据的删除(DELETE)

    语法:

    delete from tbl_name
    [where where condition]
    [order by ...]
    [limit row_count]
    • 清空表
    DELETE FROM Product;

     

    • 指定删除对象(搜索型DELETE)
    DELETE FROM Product
     WHERE sale_price >= 4000;

    指定删除,是很常用的。

    • 根据另一张表的条件删除记录

    DELETE a FROM table_a a join table_b b on a.column1=b.column1 [WHERE condition&gt;] ;

    删除分数小于60分的学生

    delete a from TStudent a join TScore b on a.`StudentID`=b.`StudentID`
    where b.mark<60

    也可以使用子查询实现。
    删除分数表中分数大于90的学生

    delete from TStudent where studentid in (select studentid from TScore where mark&gt;90);

    3、数据的更新(UPDATE)

    语法:

    update tbl_name
    set col_name = {expr | default} [,col_name2={expr2 | default}]...
    [where where_condition]
    [order by ...]
    [limit row_count]

    注:order by:限定表中行被修改的次序

    limit:限定被修改的行数。 

    • 更新整列
    UPDATE Product
       SET regist_date = '2009-10-10';

     

    • 指定条件的更新(搜索型UPDATE)
    UPDATE Product
       SET sale_price = sale_price * 10
     WHERE product_type = '厨房用具';

    指定条件更新,是很常用的。

    • 多列更新
    UPDATE Product
       SET sale_price = sale_price * 10,
           purchase_price = purchase_price / 2
     WHERE product_type = '厨房用具';
    • 根据另一张表的条件更改记录
    UPDATE table_nameA a join table_nameB b 
    on a. column_name1=b. column_name1
         SET a.column_name1 = value1,
        a.column_name2=value2,……,
         a.column_namen=valuen
        WHERE b. column_name2>20

    如:将有不及格的学生姓名后加*标记

    update TStudent a join TScore b on a.`StudentID`=b.`StudentID`
    set a.`Sname`=concat(sname,'*') where b.mark<60

    同时更改两张表的列
    实例:把分数低于60分的学生,加5分,并在学生姓名添加标记+

    update TStudent a join TScore b on a.`StudentID`=b.`StudentID`
    set a.`Sname`=concat(sname,'+'), b.mark=b.`mark`+5
     where b.mark<60;

     子查询也能实现相同功能
    以下语句将分数有大于98分的学生姓名后加#号标记

    update TStudent set Sname=concat(sname,'#') where studentid in
    (select studentid from TScore where mark>98);

    Ⅲ. DCL(Data Control Language,数据控制语言)

    1、创建事务(START TRANSACTION) - 提交处理(COMMIT)

    START TRANSACTION;
        -- 将运动T恤的销售单价降低1000日元
        UPDATE Product
           SET sale_price = sale_price - 1000
         WHERE product_name = '运动T恤';
        -- 将T恤衫的销售单价上浮1000日元
        UPDATE Product
           SET sale_price = sale_price + 1000
         WHERE product_name = 'T恤衫';
    COMMIT;

     

    2、取消处理(ROLLBACK)

    START TRANSACTION;
        -- 将运动T恤的销售单价降低1000日元
        UPDATE Product
           SET sale_price = sale_price - 1000
         WHERE product_name = '运动T恤';
        -- 将T恤衫的销售单价上浮1000日元
        UPDATE Product
           SET sale_price = sale_price + 1000
         WHERE product_name = 'T恤衫';
    ROLLBACK;

     

    展开全文
  • 学习到pandas的groupby功能的时候,相当迷惑,这玩意儿干嘛的? 网上查询了一下,全是具体的用法列,要么就是从某些书本上“copy”下来的段落,也没清楚明白的讲groupby功能到底是什么。 大概知道它和数据库...

    学习到pandas的groupby功能的时候,相当迷惑,这玩意儿干嘛用的?
    网上查询了一下,全是具体的用法用列,要么就是从某些书本上“copy”下来的段落,也没清楚明白的讲groupby功能到底是什么。
    大概知道它和数据库操作比较类似,可惜我没有学过数据库,因此无法对比参照。
    至少我看那些文章,是越看越糊涂,它被发明出来解决什么问题的?
    想象一个如下的数据集:

    frame=pd.DataFrame(np.arange(12).reshape((4,3)),index=[['a','a','b','b'],[1,2,1,2]],
     columns=[['ohio','ohio','colorado'],['green','red','green']])
    

    这里写图片描述
    类似数组中的元素可以是数组,列表中的元素可以是列表的嵌套形式。
    DataFrame中的值也可以是DataFrame或者series嵌套。
    表现出来的外观就是多重索引,称为层次化索引。
    把黄线,红线部分看成一个整体:
    这里写图片描述
    这个frame就是一个2行2列的dataFrame结构,行索引是’a’,‘b’,列索引是’ohio’,‘colorado’,‘ohio’的值又是一个dataFrame,‘colorado’的值是一个series。
    对于frame来说,按照以前学习过的访问方法,也是能够访问的。
    比如:

    frame['ohio']#取一列
    frame.loc['a']#取一行。
    frame.loc['a','ohio']['red']  #取a行ohio列之下red列
    

    这里写图片描述
    但是当嵌套比较复杂的时候,人很容易犯晕,犯错。
    个人理解,python有一个设计理念:

    Flat is better than nested
    扁平胜于嵌套
    当需要处理高纬度数据时,用层次化索引的方式能够以低纬度表示高纬度数据,这个例子就是以2维表示了3维。
    但是编写代码的时候,处理嵌套还是比较麻烦,groupy功能就是为了让嵌套变得扁平,起码编写代码的时候能以扁平方式书写。
    看看frame的index

    frame.index
    

    这里写图片描述
    系统告诉我们是一个多重索引MultiIndex,分了两个levels级别。
    为了方便使用groupby,我们要给这两个levels分别命名(随便取)

    frame.index.names=['good','bad']
    

    这里写图片描述
    注意good和bad是levels的名字,而不是索引名字。
    groupby()函数以levels的名字来分组。

    #以第一层索引来分组,即以包含‘a’和‘b’索引分组。
    gr=frame.groupby('good')
    

    这里写图片描述
    此时原始数据集并没有被真正分组保存到gt中,仅仅是一个对象,描述了需要分组的信息而已。
    那groupby什么时候发挥效果呢?它是与统计函数配合使用。
    比如使用求和sum,那么以‘a’,‘b’索引为分组依据的数据,就以求和的方式被“聚合”了。
    这里写图片描述
    可以看出,第二个levels级别bad消失了,也就是说现在每一行的嵌套层级消失了,假如不看列索引的话,整个dataFrame结构成了标准的‘两行’。
    同理,我们可以通过给多重列索引命名,然后调用groupby,把列中的嵌套层级给‘折叠’。
    #####个人总结:
    groupby并不是随便一个数据集都能用的,它是用来处理多层次索引中的数据,更具体的说,是处理多层次索引中数据的统计问题。
    它的外在表现形式,就是我们指定的那个分组索引(无论行列),与该索引本身包含的细分值,在整个DataFrame结构中被’折叠’了。

    #####层次化索引补充:
    对groupby理解的还不准确,翻看了一会儿官方文档,有了新认识。
    所谓的pandas多层次化索引,事实上是人为规定的。
    站在top层面的角度,pandas是把原始数据给重新组织了一下。
    通俗的说,数据还是那些数据没变,只不过以我们想要的结构关系给整理起来,不同的角度去观察这些数据,数据自然就会外在表现出不同的组织结构。
    以之前最通常的城市结构来说,以表格型表现:
    这里写图片描述
    行索引就是0到4,levels只有一层。
    这里写图片描述
    假如我们换一个角度去观察,把所有的列都当行,调用stack():
    这里写图片描述
    此时这个newdf结构就完全改变了,它的index就成了多重索引:
    这里写图片描述
    要想转变回去,调用unstack().
    这里写图片描述
    可以看出,任何一个我们最常用的表格型,都能进行这样多重索引的转变。
    这就是为什么说,所谓的多层次索引,事实上是人为规定的。
    有了多重索引,自然也能进行groupby分组操作。
    以这个例子来说,这种转变没有意义。
    因为我们处理数据,是为了把复杂的变成简单,而不是把简单的变成复杂。
    在df表格上可以很容易的进行各种统计操作,转化成newdf反而更复杂了。
    而pandas设计层次化索引,以及相关操作的函数,是为了真正解决高纬度问题,而不是上面那种‘闲的蛋疼’。


    层次化是为了解决类似如下问题,原始数据中已经隐藏包涵了多层问题:

    frame=pd.DataFrame({'a':range(7),'b':range(7,0,-1),
                        'c':['one','one','one','two','two','two','two'],
                        'd':[0,1,2,0,1,2,3]})
    

    这里写图片描述
    原始数据frame已经包涵了多层问题,利用set_index把原始数据重新组织:

    frame2=frame.set_index(['c','d'])
    

    这里写图片描述
    检查一下frame2的index
    这里写图片描述
    对类似这种问题,groupby技术才是使用正途,可以直接在frame原始数据上进行分组操作:

    frame.groupby('c').sum()
    

    这里写图片描述
    因为d列也是数值,所以同样被聚合起来了(默认情况下,所有数值列都会被聚合),但是d列显然不是我们想要的。
    还是在转换完成的frame2上面进行分组

     frame2.groupby('c').sum()
    

    这里写图片描述
    d列被排除了,sum()结果更加清晰,和人工计算结论相同。


    #####对比补充:
    对比一下没有层次化索引,没有groupby的情况,假如还是那个原始数据frame:
    这里写图片描述
    我们想要求满足c列同值的情况,也就是同属于‘one’,'two’的部分,那怎么办?
    首先,肯定是对原始数据条件切分,切出同属于’one’的部分:

    one=frame[frame['c']=='one']
    

    这里写图片描述
    再对one进行求和:

    one['a'].sum()
    one['b'].sum()
    

    这里写图片描述
    对比groupby,最后的结果肯定是一样的,a列的sum值都为3,18,但是groupby技术更加方便快捷,而且求出的结果也成为了一个DataFrame,观察使用更加清晰。
    #####举一反三
    该例稍加变化,就成了一个学生成绩表,可以求男同学和女同学的平均成绩。

    
    frame=pd.DataFrame({'姓名':['甲','乙','丙','丁','戊','己','庚'],
                        '成绩':range(70,0,-10),
                        '性别':['男','男','男','女','女','女','女'],
                        'd':[0,1,2,0,1,2,3]
                       })
    

    这里写图片描述

    #转换成层次化索引形式
    frame2=frame.set_index(['性别','d'])
    

    这里写图片描述

    #性别分组,求平均
    frame2.groupby('性别').mean()
    

    这里写图片描述
    因为pandas的groupby功能,默认数值列被聚合,非数值列排除掉,‘姓名’列显然属于非数值,故而排除。
    人工验算一下,男同学(70+60+50)/3=60,女同学(40+30+20+10)/4=25,结论ok。
    再次证明groupby技术方便快捷,而且求出的结果也成为了一个DataFrame,观察使用更加清晰。
    这里写图片描述

    展开全文
  • 1.8 什么时候不应该使用DBMS 18 1.9 小结 18 复习题 18 练习题 19 选读文献 19 第2章 数据库系统的概念和体系结构 20 2.1 数据模型、模式和实例 20 2.1.1 数据模型的分类 21 2.1.2 模式、...
  • oracle数据库经典题目

    2011-02-17 15:05:20
    13. _____语句修改表的一行或多行数据。( A ) A.Update B.set C.Select D.Where 14. 使用什么命令可以清除表中所有的内容? ( D ) A.INSERT B.UPDATE C. DELETE D.TRUNCATE 15.关于模式的描述下列哪一项不...
  • 最近在看group by 的时候,不大理解怎么分组,大学学这个地方的时候就有点稀里糊涂的,查询了很多文章现在终于理解了。 group by 我的理解就是对某个列进行分组,列相同的可以放在一起再聚合函数来查找想要的结果...
  • 什么窗口函数?

    2020-04-16 18:06:15
    目录窗口函数具有什么功能?但是这些功能我们的group看差别:那么为什么叫窗口函数?窗口函数的语法: 窗口函数具有什么功能?...很明显当我们使用group by分组时改变了原来数据库的行数,即生成了一张新表,但是...
  • 什么时候RethinkDB是一个好的选择?当你的应用很大程度上有赖于数据的实时反馈时,RethinkDB 就会成为一个很棒的选择。“查询-响应”式的数据库访问模型在web上的确很有用,它可以直接映射到HTTP的“请求-响应”。而...
  • mysql索引为什么用B+树

    2020-04-27 09:53:05
    mysql索引为什么用B+树 前言: 对于数据库的需求,我们需要高频率的范围查询,分组和排序。 数据库什么要设计索引? 我们有一个需求 ,我要写一个笔记可能字数很少我们只需要寥寥几页就可以查找到自己的需要...
  • 对两个数据库有相同的权限要求,我们必须手工设置两个数据库的权限,或者编写脚本设置权限。如果用户数 量较少,比如25个以下,而且这些用户的权限变化不是很频繁,SQL Server验证的登录或许适用。但是,在几 乎所有...
  • 首先我创建了两个库,每个库两张表.(工作的时候,每个公司最好有自己的数据库模型,产品也可以看,模型工具一般PD(power designer)什么的,起来简单规范方便,建议萌新学习) · 第二个库crtest2是复制第...
  • 索引 什么是索引? 索引是对数据库表中的一列或多列值进行排序的一种结构,使用索引可以快速访问数据库表中的特定信息。...在使用分组和排序进行检索的时候,可以减少查询中分组和排序的时间 ...
  • 然后从短信中用正则的分组去拿到验证码(当然验证码必须是什么格式) 贴出关键代码: 注册监听短信数据库的  ContentObserver c=new ContentObserver(han) { @Override public void onChange(boolean selfChange...
  • 近期做注冊的时候看到非常多app在手机接受到短信的时候直接填写验证码到界面省略用户...然后从短信中用正则的分组去拿到验证码(当然验证码必须是什么格式) 贴出关键代码: 注冊监听短信数据库的 ContentObse...
  • 最近做注册的时候看到很多app在手机接受到短信的时候直接填写验证码到界面省略用户...然后从短信中用正则的分组去拿到验证码(当然验证码必须是什么格式) 贴出关键代码: 注册监听短信数据库的 ContentObse
  • 实习感悟——SQL语句

    2014-08-31 17:54:00
    字面意思我们一看就知道groupby通过分组的意思,通过数据库某个字段的分组我们可以做什么?联系到生活中,我们给一组对象分组主要就是为了统计,计算。比如计算平均值,计算总和,查出本组最大的,最小的,等等。...
  • mysql rollup&&cube

    千次阅读 2016-04-14 20:36:06
    当需要对数据库数据进行分类统计的时候,往往...那什么时候用with cube?什么时候用with rollup呢? 一、两者的区别   CUBE 生成的结果集显示了所选列中值的所有组合(group by 后面的列组合)的聚合。   ROLLUP
  • 什么时候使用继承,什么时候用接口 filter,listener,servlet的区别 了解前端吗,了解vue路由吗, 写一个正则表达式 Java的三大特性,什么含义(继承,封装, 多态) 常用的sql语句,分组语句 get和post请求的区别 ...
  • 金字塔结构

    2011-07-20 18:31:12
    因为学过的知识因为缺少归类分组,所以在大脑中杂乱无章,当需要时候,大脑检索时需要耗费太多的时间和精力,最后可能导致死机。学过计算机的都知道数据库数据库管理系统,方便数据的管理和查询。而
  • 什么是DOClever? DOClever是一个商业化开源产品,完全免费。无论你是前端工程师,还是后端工程师,接口永远都是两者交互的桥梁,所以DOClever专为中小型团队量身打造,旨在解决接口的管理,测试与数据生成,实现...
  • 因为学过的知识因为缺少归类分组,所以在大脑中杂乱无章,当需要时候,大脑检索时需要耗费太多的时间和精力,最后可能导致死机。学过计算机的都知道数据库数据库管理系统,方便数据的管理和查询。而我们的大脑...
  • 因为学过的知识因为缺少归类分组,所以在大脑中杂乱无章,当需要时候,大脑检索时需要耗费太多的时间和精力,最后可能导致死机。学过计算机的都知道数据库数据库管理系统,方便数据的管理和查询。而我们的大脑...

空空如也

空空如也

1 2 3 4 5 ... 7
收藏数 140
精华内容 56
热门标签
关键字:

数据库什么时候用分组