sql语句

当SQLite数据库中包含自增列时，会自动建立一个名为 sqlite_sequence 的表。这个表包含两个列：name和seq。name记录自增列所在的表，seq记录当前序号（下一条记录的编号就是当前序号加1）。如果想把某个自增列的序号归零，只需要修改 sqlite_sequence表就可以了。

UPDATE sqlite_sequence SET seq = 0 WHERE name = 'TableName';

oracle 查询用户表 表列名 表注释

查询表

select t.* from user_tables t where t.table_name='tablename';

查询表字列

select * from user_tab_columns c where c.TABLE_NAME='tablename';

查询注释

select cs.column_name,cs.comments from user_col_comments cs where cs.table_name='tablename';

mysql数据库调优

1. 是不是索引越多越好:并不是

	因为索引虽然加快了查询速度,但是索引也是有代价的,索引文件本身
是要消耗存储空间的,同时索引会加重插入、删除和修改记录的负担,另外
mysql在运行时也要消耗资源维护索引

2. mysql数据库关于InnoDB表设计的主键设置

	如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到
当前索引节点的后续位置,当一页写满,就会自动开辟新一页,这样就会形
成一个紧凑的索引结构,近似顺序填满,由于每次插入主键的值近似于随机
因此每次新记录都要被插到现有索引页的中间某个位置,此时MySQL不得
不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写
到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销
同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结
构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面,因此
只要可以,请尽量在InnoDB上采用自增字段做主键

sql语句关于group by在数据库版本中的查缺补漏

1. 在Mysql8.0及以上 only_full_group_by以及其他关于sql_mode原因报错详细解决方案(具体可详细百度)
2. 解决办法:这边个人建议不能去修改数据库配置,因为有些时候生产环境的时候牵一发而动全身
3. 如果小伙伴是oracle的熟练者那就不用多说了,因为oracle是直接不允许查询列与分组列不同的
4. 这边建议小伙伴都养成好习惯,当然这个你说我关联查询了百十来个字段,那这时候不得炸？所以这时候就是要认真考虑表结构了,当然这修改表结构显然在正式环境不切合实际,老铁还是一点一点加上去吧！！！！

错误的写法
		select id,name from test group by id
正确的写法
		select id,name from test group by id,name

修改数据库字符集

SELECT
	CONCAT( 'alter table ', a.table_name, ' convert to character set utf8mb4 collate utf8mb4_bin;' ) 
FROM
	(
	SELECT
		table_name 
	FROM
		information_schema.`TABLES` 
	WHERE
	TABLE_SCHEMA = 'test_mall_trade' 
	) a;

查看数据库中不为 InnoDB 引擎的表

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
  FROM information_schema.TABLES
 WHERE TABLE_SCHEMA NOT IN
      ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
   AND ENGINE != 'InnoDB';

查看数据库中表的大小及数据量

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS,
       CONCAT(round((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2), 'MB') as data
  FROM information_schema.TABLES
 WHERE TABLE_SCHEMA NOT IN 
       ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
 ORDER BY DATA_LENGTH + INDEX_LENGTH DESC;

查找数据库中无显式主键索引的表

SELECT t.TABLE_SCHEMA, t.TABLE_NAME
  FROM information_schema.TABLES t
 WHERE (t.TABLE_SCHEMA, t.TABLE_NAME) NOT IN
       (SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME
          FROM information_schema.COLUMNS
         WHERE COLUMN_KEY = 'PRI')
   AND t.TABLE_SCHEMA NOT IN
       ('sys', 'mysql', 'performance_schema', 'information_schema', 'test');

查找数据库中主键为联合主键的表

SELECT TABLE_SCHEMA, TABLE_NAME,
       GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ',') cols,
       MAX(SEQ_IN_INDEX) len
  FROM information_schema.STATISTICS
 WHERE INDEX_NAME = 'PRIMARY'
   AND TABLE_SCHEMA NOT IN
       ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
 GROUP BY TABLE_SCHEMA, TABLE_NAME
HAVING len > 1;

查找数据库中不为自增主键的表

SELECT TABLE_SCHEMA, TABLE_NAME
  FROM information_schema.TABLES
 WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
   AND (TABLE_SCHEMA,TABLE_NAME) NOT IN 
       (SELECT TABLE_SCHEMA, TABLE_NAME
          FROM information_schema.COLUMNS
         WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')
           AND IS_NULLABLE = 'NO'
           AND COLUMN_TYPE LIKE '%int%'
           AND COLUMN_KEY = 'PRI'
           AND EXTRA = 'auto_increment');

查看数据库中存在外键约束的表

SELECT c.TABLE_SCHEMA,
       c.REFERENCED_TABLE_NAME,
       c.REFERENCED_COLUMN_NAME,
       c.TABLE_NAME,
       c.COLUMN_NAME,
       c.CONSTRAINT_NAME,
       t.TABLE_COMMENT,
       r.UPDATE_RULE,
       r.DELETE_RULE
  FROM information_schema.KEY_COLUMN_USAGE c
  JOIN information_schema.TABLES t
    ON t.TABLE_NAME = c.TABLE_NAME
  JOIN information_schema.REFERENTIAL_CONSTRAINTS r
    ON r.TABLE_NAME = c.TABLE_NAME
   AND r.CONSTRAINT_NAME = c.CONSTRAINT_NAME
   AND r.REFERENCED_TABLE_NAME = c.REFERENCED_TABLE_NAME
 WHERE c.REFERENCED_TABLE_NAME IS NOT NULL;

查找数据库中没有被使用的索引

SELECT a.OBJECT_SCHEMA, a.OBJECT_NAME, a.INDEX_NAME, b.TABLE_ROWS
  FROM performance_schema.TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE a
 INNER JOIN information_schema.TABLES b
    ON a.OBJECT_SCHEMA = b.TABLE_SCHEMA
   AND a.OBJECT_NAME = b.TABLE_SCHEMA
 WHERE a.INDEX_NAME IS NOT NULL
   AND a.INDEX_NAME != 'PRIMARY'
   AND a.COUNT_STAR = 0
   AND OBJECT_SCHEMA NOT IN
       ('sys', 'mysql', 'performance_schema', 'information_schema')
 ORDER BY OBJECT_SCHEMA, OBJECT_NAME;

查看数据库中的锁请求信息

SELECT r.TRX_ISOLATION_LEVEL,
       r.TRX_ID                              WAITING_TRX_ID,
       r.TRX_MYSQL_THREAD_ID  WAITING_TRX_THREAD,
       r.TRX_STATE                       WAITING_TRX_STATE,
       lr.LOCK_MODE                    WAITING_TRX_LOCK_MODE,
       lr.LOCK_TYPE                     WAITING_TRX_LOCK_TYPE,
       lr.LOCK_TABLE                   WAITING_TRX_LOCK_TABLE,
       lr.LOCK_INDEX                    WAITING_TRX_LOCK_INDEX,
       r.TRX_QUERY                      WAITING_TRX_QUERY,
       b.TRX_ID                              BLOCKING_TRX_ID,
       b.TRX_MYSQL_THREAD_ID  BLOCKING_TRX_THREAD,
       b.TRX_STATE                       BLOCKING_TRX_STATE,
       lb.LOCK_MODE                    BLOCKING_TRX_LOCK_MODE,
       lb.LOCK_TYPE                     BLOCKING_TRX_LOCK_TYPE,
       lb.LOCK_TABLE                   BLOCKING_TRX_LOCK_TABLE,
       lb.LOCK_INDEX                    BLOCKING_TRX_LOCK_INDEX,
       b.TRX_QUERY                     BLOCKING_QUERY
  FROM information_schema.INNODB_LOCK_WAITS w
 INNER JOIN INFORMATION_SCHEMA.INNODB_TRX b
    ON b.TRX_ID = W.BLOCKING_TRX_ID
 INNER JOIN information_schema.INNODB_TRX R
    ON r.TRX_ID = W.REQUESTING_TRX_ID
 INNER JOIN information_schema.INNODB_LOCKS lb
    ON lb.LOCK_TRX_ID = W.BLOCKING_TRX_ID
 INNER JOIN information_schema.INNODB_LOCKS lr
    ON lr.LOCK_TRX_ID = W.REQUESTING_TRX_ID;

MySQL时间加减

MySQL 为日期增加一个时间间隔：date_add()

now()       //now函数为获取当前时间

select date_add(now(), interval 1 day); - 加1天

select date_add(now(), interval 1 hour); -加1小时

select date_add(now(), interval 1 minute); - 加1分钟

select date_add(now(), interval 1 second); -加1秒

select date_add(now(), interval 1 microsecond);-加1毫秒

select date_add(now(), interval 1 week);-加1周

select date_add(now(), interval 1 month);-加1月

select date_add(now(), interval 1 quarter);-加1季

select date_add(now(), interval 1 year);-加1年

MySQL adddate(), addtime()函数，可以用date_add() 来替代。

MySQL 为日期减去一个时间间隔：date_sub()

MySQL date_sub() 日期时间函数 和date_add() 用法一致。

MySQL 中subdate(),subtime()函数，建议，用date_sub()来替代。

批量新增数据

1.oracle

DECLARE
i INT;
BEGIN 
i:=1;
WHILE(i<=5000000) 
LOOP
    i:=i+1;
    INSERT INTO USER_JSON(ID,USER_NAME,USER_NUM,USER_SEX) 
    VALUES (i,'张三','123','男');
END LOOP; 
COMMIT;
END;

2.mysql

begin
    declare i int;  # declare语句是在复合语句中声明变量的指令
    set i=1;
    while i<=10000 do
        insert into users(username,`password`,`name`) values(i,'1','张三');
        set i=i+1;
    end while;
end