对于数据库的一些日常操作记录(备份表, 插入数据, 修改表名, 分页查询优化)

使用Navicat工具, 直接右击复制表, 复制表的数据与结构(总是感觉有点low)使用mysql 的命令行操作

create table bak_tableName select * from old_tableName;

例如创建 user 表的备份表(会同时备份表的数据和结构)

create table user_20220122_bak select * from user;

不会备份表的数据, 只会备份表的结构

create table user_20220123_bak like user;

insert into target_tableName(列名1,2,3...)
select 对应的列名 from source_tableName;

例:

insert into `c_work_attendance_user`(user_id,mobile,`name`,store_id,community_id,department_id,standard_working_start_time,standard_off_duty_time,standard_working_hours)
SELECt
	uu.user_id,
	ur.mobile,
	uu.`name`,
	'402' store_id,
	'702' community_id,
	t2.department_id,
	t2.standard_working_start_time,
	t2.standard_off_duty_time,
	t2.standard_working_hours 
FROM
	u_user uu
	INNER JOIN u_user_owner_relation ur ON ur.user_id = uu.user_id AND ur.identity_type = '1001' AND ur.status_cd = '0'
	INNER JOIN building_owner bo ON bo.user_id = uu.user_id AND bo.status_cd = '0' AND bo.owner_type_cd = '1001' AND bo.community_id
	= '0079'
	INNER JOIN c_work_attendance_department_info t2 ON t2.mobile = ur.mobile 
WHERe
	uu.status_cd = '0'

rename table source_tableName to target_tableName;

例, 将表 user 修改为 u_user

rename table user to u_user;

-- 使用 子查询
SELECt * FROM table_name WHERe 条件 and id>= (
SELECt id FROM student 条件 LIMIT 'page*row',1) LIMIT 'row';

例: 查询出 姓 李的用户的数据

SELECt * FROM student WHERe	name like '李%' and id >= ( 
SELECt id FROM student WHERe name like '李%' LIMIT 'page*row' , 1) LIMIT 'row';