Mysql 常用命令集合

最新推荐文章于 2024-07-24 22:46:43 发布

Ys8888N

最新推荐文章于 2024-07-24 22:46:43 发布

阅读量461

点赞数

分类专栏： java 数据库/mysql 文章标签： mysql java 数据库

本文链接：https://blog.csdn.net/Ys8888N/article/details/125439140

版权

sql：
1. 数据插入
1.1 单表
1. INSERT INTO BEIMU VALUES(4,"贝珠",'18','2021-01-11')
2. INSERT INTO BEIMU SET `BEI_AGE` ='24'
3. INSERT INTO BEIMU(BEI_ID, `BEI_NAME`, `BEI_AGE`, `BEI_BIRTHDAY`) VALUES (3,'夸夸', 23,'2021-01-08');
1.2 双表数据合并
1. insert into customer select * from asett
2. insert into customer (cus_id,cus_no) select ast_id,ast_no from asett
1.3 复制原表表结构与数据
1. create table t_sb_t_1 select id,id from t_sb where id < 0
1.4 复制原表表结构，不复制数据
1. create table t_sb_t like t_sb
1.5 存在主键/唯一索引:
1. insert ignore into -- 存在则忽略主键:id 数据无变化; 唯一索引:id隐形增加
   2. insert into ...on duplicate key update id=values(id) -- 存在则更新,id隐形增长
   3. replace into -- 存在则删除旧数据, 插入新数据
   ps: 主从模式下会导致id不一致
   4. 从文本文件导入
load data local infile 'd:/test3.txt' IGNORE into table tb0 character set utf8 fields terminated by ',' lines terminated by '\n' (`username`,`age`,`description`)
LOAD DATA
[LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var
[, col_name_or_user_var] ...)]
[SET col_name={expr | DEFAULT}
[, col_name={expr | DEFAULT}] ...]

2. 修改
1. 修改字段
1. alter table t_sb_t modify id varchar(55) -- 修改字段
   2. alter table t_sb add name varchar(10)-- 添加字段
   3. alter table t_sb drop name--删除字段
   4. alter table t_sb change id objid varchar(50) -- 改字段名
   5. alter table t_sb rename t_sb_test-- 改表名
   6. update sys_dept_1 a inner join sys_dept_2 b on a.id=b.id
   set a.name = b.name -- 多表联合修改

3. 删除
1. drop table1,table2,table3; -- 删除表