MySQL数据库基本操作

插入记录

insert into 表名 values (列值,列值);
insert into 表名 (字段名,字段名) values (列值, 列值)
insert into 表名 values (列值,列值),(列值,列值),(列值,列值);

插入另一个表的查询纪录

insert into 表1  select * from 表2;
-- 表结构不一样（这种情况下得指定列名）
insert into 表(列名1,列名2,列名3)
　　select 列1,列2,列3 from 表2 ;
  
  -- 插入时判断数据库里是否有重复数据了
  insert into answer( subject_id, subject_no, option_name, group_name, user_answer, user_id)
    select 2,3,'ada','dad','kl2','lkdjsf' from dual where NOT EXISTS(
        select * from answer where  user_answer='kl2' and user_id='lkdjsf'
    )
   

 插入或更新(主键重复时用此语句)

-- 直接插入，但如果主键重复则执行 on duplicate后面的语句 
insert into user values  (null, #{username}, #{birthday}, #{sex}, #{address})
on duplicate key update 
     id = #{id},name= #{name},age=#{age}

-- 批量插入
-- 且主键（或唯一索引）不重复，如下：设置subject_id 与subject_no为唯一索引，插入以下3条数据，实际只有两条会被插入
insert into answer(subject_id, subject_no, option_name, group_name, user_answer, user_id)
    value (2, 3, 'ada', 'dad', 'kl2', 'lkdjsf'), (2, 4, 'addda', 'dad', 'kl2', 'lkdjsf'), (2, 4, 'addda', 'dad', 'kl2', 'lkdjsf')
on duplicate key update user_id =user_id;


-- 或者直接用replace
replace into user 
 values  (null, #{username}, #{birthday}, #{sex}, #{address})

 

复制表

-- 复制表结构及索引（不复制数据）
create table answer2 like answer;
insert into answer2 select * from answer; -- 向表插入数据

-- （不建议）复制出数据一样的表，但没有索引、默认值等
create table answer3 as select * from answer;

-- 查看创建表的SQL语句
show create table 表名;

 

---

删除记录

删除数据

delete from 表名 where 1=1;
truncate 表名;

-- 删除多条记录
delete from user where id in (11,12,13)

直接删除表

drop table 表名;
drop table if exists 表名;

删除重复数据

-- 删除一个表中的重复数据，只保留id最小的那条
delete from answer2 where id in(
    select tt.id from (
select t1.id
from answer2 t1
         inner join answer2 t2
where t1.option_name = t2.option_name
  and t1.id > t2.id group by t1.id) tt );
  
  
-- 方式2
delete from answer2 where id not in (
    select t.id from (
            select subject_id,subject_no,option_name,group_name,user_answer,min(id) id from answer2
            group by subject_id, subject_no, option_name, group_name, user_answer
    )t
)

 

联表多条件删除用 exists

delete from tmp_vloan_client_invitecode a 
    where exists (
        select 1 from (
            select loginid,count(1) from tmp_vloan_client_invitecode  group by loginid having count(1)>1
             ) b where a.loginid=b.loginid 
        ) and a.name is null ;

 

 

---

更改数据-updata

-- 更新某个字段
update 表名 set 列名=值 where 1=1;

-- 更新多个字段
update 表名 set 列名=值,列名=值 where 条件;

-- 从子查询中拿数据更新表字段
update answer t1 left join (select count(*) as cou, option_name from answer a group by a.option_name) a on t1.option_name = a.option_name
set t1.user_answer = a.cou,t1.user_id=a.cou where 1=1;

-- 交换两列的值
update answer t1 join answer t2 on t1.id = 1 and t2.id = 2
set t1.subject_no=t2.subject_no,
    t2.subject_no=t1.subject_no;

 

 

---

查找

select 字段 from 表 
  where 条件 
  group by 分组列 
  having 过滤条件 
  order by 排序列 
  limit 开始行, 返回行;
  
where  子句写在group by 的前面： 1. 先过滤再分组 2. 后面不能使用聚合函数
having 子句写在group by 的后面： 1. 先分组再过滤 2. 后面可以使用聚合函数

 分页

-- 从0开始, 返回行数
select * from student3 limit 0,5;

 

表结构相关

-- 查看表结构
desc student;
show create table table_student;

对表字段操作

-- 给表添加一个字段
alter table 表名 add 字段名 数据类型 comment '注释内容';
-- 修改列类型MODIFY
alter table 表名 modify 字段名 新的数据类型 comment '注释内容';
-- 修改列名和类型 CHANGE
alter table 表名 change 旧的字段名 新的字段名 数据类型

--更改表的注释
alter table test01 comment '注释内容';
-- 删除列
alter table 表名 drop 列名

改表名

-- 修改表名
rename table 旧的表名 to 新的表名;

-- 删除表
drop table 表名;

新建索引

-- 创建普通索引
CREATE INDEX index_name ON table_name (column_list)

-- 创建唯一索引
CREATE UNIQUE INDEX uk_users_name ON t_users(name);

-- 删除索引
drop index uk_users_name;

-- 查找索引
show index from table_name;

注意：唯一索引对null不起作用，也就是字段为null的话可以重复；

mysql 8.0 后才支持函数索引

 

 

已知字段名查找表

-- 查库里所有的表
select * from information_schema.TABLES;

-- 查库里所有的字段
select * from information_schema.COLUMNS where COLUMN_NAME like '%user%' ;

 

 

MySQL数据类型

数据类型	关键字
整型	int或integer，两个是一样。
浮点型	double或float，通常使用double
字符串型	可变长 varchar(长度)，定长：char(长度)
日期类型	date 日期 time 时间 datetime

浮点类型有两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）；

 

定点类型只有一种，就是 DECIMAL。

浮点类型和定点类型都可以用(M, D)来表示，其中M称为精度，表示总共的位数；D称为标度，表示小数的位数。

例：DECIMAL(6,2);范围是从-9999.99到9999.99

在mysql如何取准确的数值：

1. 可以细分成整数保存，例如价格类, 1.88元，保存到数据库为 188分。12.45克保存到数据库为 12450毫克。

2. 用DECIMAL保存

3. 存储的数据范围超出decimal的范围时，可以将数据按照整数和小数拆分，比如18.54，分成两个字段保存，18和54.