当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数据库调优
- 是不是索引越多越好:并不是
因为索引虽然加快了查询速度,但是索引也是有代价的,索引文件本身
是要消耗存储空间的,同时索引会加重插入、删除和修改记录的负担,另外
mysql在运行时也要消耗资源维护索引
- mysql数据库关于InnoDB表设计的主键设置
如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到
当前索引节点的后续位置,当一页写满,就会自动开辟新一页,这样就会形
成一个紧凑的索引结构,近似顺序填满,由于每次插入主键的值近似于随机
因此每次新记录都要被插到现有索引页的中间某个位置,此时MySQL不得
不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写
到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销
同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结
构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面,因此
只要可以,请尽量在InnoDB上采用自增字段做主键
sql语句关于group by在数据库版本中的查缺补漏
- 在Mysql8.0及以上 only_full_group_by以及其他关于sql_mode原因报错详细解决方案(具体可详细百度)
- 解决办法:这边个人建议不能去修改数据库配置,因为有些时候生产环境的时候牵一发而动全身
- 如果小伙伴是oracle的熟练者那就不用多说了,因为oracle是直接不允许查询列与分组列不同的
- 这边建议小伙伴都养成好习惯,当然这个你说我关联查询了百十来个字段,那这时候不得炸?所以这时候就是要认真考虑表结构了,当然这修改表结构显然在正式环境不切合实际,老铁还是一点一点加上去吧!!!!
错误的写法
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