MYSQL查询积累

1、查询每天数据总量

SELECT DATE_FORMAT(LASTMODYTIME,'%Y-%m-%d') as m,count(*)  from hr_employee GROUP BY m;

2、查询表结构

select table_name,column_name,column_comment,COLUMN_TYPE from information_schema.columns where table_schema ='yunda_org_manage' and table_name = '2_stlyrq';

3、查询视图结构

select VIEW_DEFINITION from information_schema.views where TABLE_SCHEMA='ydzp' and TABLE_NAME='interview_resume_rencai_orgseq_v'

4、查询索引信息

show index from yd_customize_org

5、创建索引

create index index_customize_obj on yd_customize_org (objid)

6、删除索引

alter table yd_customize_org  drop index index_customize_obj ;

7、判断表是否存在

SHOW TABLES LIKE '%tb_bp_d_case%';

8、批量修改某个字段中部分内容

update user SET username= REPLACE( username, '修改前', '修改后' ) where username like '%修改前%';

9、mysql向表中某字段后追加一段字符串:
update table_name set field=CONCAT(field,'',str)

10、mysql一条语句添加多条数据

INSERT INTO student
    (sno,sname,ssex,sage,sdept)
VALUES
    ('201215121','李勇','男',20,'CS'),
    ('201215122','刘晨','女',19,'CS'),
    ('201215123','王敏','女',18,'MA'),
    ('201215125','张立','男',19,'IS');
 

11、修改表名

alter table 2_stlyrq rename to 2_stlyrq_copy;

12、删除表

DROP TABLE table_name

13、查询没有主键的表

SELECT
    #CONCAT("truncate table ",table_name,";")
table_name
FROM
    information_schema. TABLES
WHERE
    table_schema = 'yunda_org_manage'
AND TABLE_NAME NOT IN (
    SELECT
        table_name
    FROM
        information_schema.table_constraints t
    JOIN information_schema.key_column_usage k USING (
        constraint_name,
        table_schema,
        table_name
    )
    WHERE
        t.constraint_type = 'PRIMARY KEY'
    AND t.table_schema = 'yunda_org_manage'
) limit 100;

yunda_org_manage:数据库名称