Mysql 小海豚工具使用教程 mysql小海豚安装

oracle 用户–>表空间–>库–>表
mysql 系统–>库–>用户–>表

第一部分 安装

官网下载

安装

安装完成后,能在系统偏好设置里看到mysql的小海豚

环境配置

我们在终端输入mysql,发现提示commod not found，那是因为我们没配置系统的环境变量,下面我们来进行配置：
(1)查看mysql是否安装成功
终端输入命令cd /usr/local/mysql/bin (2）在终端加入环境路径
如果是bash,执行open ~/.bash_profile；如果是zsh,执行open ~/.zshrc 添加语句PATH=$PATH:/usr/local/mysql/bin,保存 --[我这里添加的是export PATH=${PATH}:/usr/local/mysql/bin]
立即生效，source ~/.bash_profile or source ~/.zshrc

(3) 登录mysql
mysql -uroot -p

安装完成

会在启动台里出现一个海豚图标,名称叫"mysql".

第二部分 建立本库数据库

--查看已有的数据库
show databases;

--修改密码
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('你的新密码');

--创建数据库
CREATE DATABASE mysql_shiyan;

--连接数据库
use mysql_shiyan;

第三部分 表结构的操作

1.主键自增长 AUTO_INCREMENT

需要清除表数据才能有效

ALTER TABLE app_xz_coupon_pool AUTO_INCREMENT = 10;
-- 通过np工具可直接设置（不用设置起始值）

2.修改字段（长度、注释等字段全部信息）

ALTER TABLE `xz`.`app_xz_jingdou_exchange_coupon` 
MODIFY COLUMN `coupon_img` varchar(310) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '优惠券标签' AFTER `show_time_end`;

ALTER TABLE `xz`.`app_xz_jingdou_exchange_coupon` 
MODIFY COLUMN `coupon_img` varchar(290)  COMMENT '优惠券图片';

-- 时间增加默认值
ALTER TABLE `fxb`.`fxb_jingbeans_pool_002` 
MODIFY COLUMN `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' AFTER `creator`;

-- 时间增加默认值，且更新时自动更新时间
ALTER TABLE `fxb`.`fxb_jingbeans_pool_002` 
MODIFY COLUMN `modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间' AFTER `modifier`;

-- 注意：CURRENT_TIMESTAMP默认值，在mysql版本低时，只能存在1个

3.新增字段

ALTER TABLE `user`
ADD COLUMN `username`  varchar(20) NULL DEFAULT '' COMMENT '用户名' AFTER `phone`,
ADD COLUMN `create_at`  datetime NULL COMMENT '创建时间' AFTER `username`;

-- 示例2
ALTER TABLE `app_xz_coupon_sort`
ADD COLUMN `sort_no_pro` int(10) DEFAULT NULL  COMMENT '省行销设置排序数字' AFTER `sort_no_must`,
ADD COLUMN `sort_no_default` int(10) DEFAULT NULL COMMENT '全国统一默认排序数字' AFTER `sort_no_must`;

--1.实际就是在新增字段上前加‘ADD COLUMN’；
--2.注意不要有中文空格。

4.时间timestap、默认当前时间、默认修改更新

-- 注：修改时需将所有信息补充，而不是仅写有调整的地方，如无COMMENT '创建时间'这一段，则created无注释
ALTER TABLE `xz`.`app_xz_coupon_sort_0622` 
MODIFY COLUMN `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' AFTER `creator`,
MODIFY COLUMN `modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间' AFTER `modifier`;

5.清除表数据

truncate table app_xz_coupon_pool

6.删除表

DROP TABLE app_xz_coupon_pool

7.注释

-- 1.查看所有表的注释
SELECT
table_name 表名,
table_comment 表说明
FROM
information_schema.TABLES
WHERE
table_schema = '数据库名'
ORDER BY
table_name

-- 2.查询所有表及字段的注释
SELECT
a.table_name 表名,
a.table_comment 表说明,
b.COLUMN_NAME 字段名,
b.column_comment 字段说明,
b.column_type 字段类型,
b.column_key 约束
FROM
information_schema. TABLES a
LEFT JOIN information_schema. COLUMNS b ON a.table_name = b.TABLE_NAME
WHERE
a.table_schema = '数据库名'
ORDER BY
a.table_name

-- 3.查询某表的所有字段的注释 -1
select 
COLUMN_NAME 字段名,
column_comment 字段说明,
column_type 字段类型,
column_key 约束 from information_schema.columns 
where table_schema = '数据库名'
and table_name = '表名' ; 

-- 4.查询某表的所有字段的注释 -2 注意表名不加单引号
show full columns from user_base;

-- 5.查看表生成的DDL 注意表名不加单引号
show create table 表名;
show create table user_base;

-- 结果:
CREATE TABLE `user_base` (
  `usr_id` varchar(100) NOT NULL COMMENT '主键',
  `usr_name` varchar(100) DEFAULT NULL COMMENT '名称',
  `pickname` varchar(100) DEFAULT NULL COMMENT '昵称',
  `usr_pwd` varchar(255) DEFAULT NULL COMMENT '用户密码',
  `created` varchar(255) DEFAULT NULL COMMENT '创建时间',
  `creator` varchar(255) DEFAULT NULL COMMENT '创建人',
  `modified` varchar(255) DEFAULT NULL COMMENT '修改时间',
  `modifier` varchar(255) DEFAULT NULL COMMENT '修改人',
  `status` int(2) DEFAULT NULL COMMENT '状态:0,注销;1,在用,正常;2,中间状态;3,其他.',
  `mobile` varchar(20) DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`usr_id`),
  UNIQUE KEY `idx_user_name` (`usr_name`),
  UNIQUE KEY `idx_user_mobile` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户基础表'

-- 6.新建表以及添加表和字段的注释
create table t_user(
    ID INT(19) primary key auto_increment  comment '主键',
    NAME VARCHAR(300) comment '姓名',
    CREATE_TIME date comment '创建时间'
)comment  = '用户信息表';


-- 7.修改表注释
alter table t_user comment  = '修改后的表注释信息(用户信息表)';

-- 8.修改字段注释
alter table t_user modify column id int comment '主键ID';

8.show full 查看表所有信息

-- 查询某表的所有字段的信息 -2 注意表名不加单引号
show full columns from user_base;

第四部分 表数据的操作

1.非空判断

--非空判断
select distinct brand from product where brand is not null order by brand;--100ms
select distinct brand from product where !ISNULL(`brand`) order by brand;--这个效率更高77ms

2.清除表数据

truncate table app_xz_coupon_pool

3.多条数据新增、批量新增

INSERT INTO  app_xz_coupon_pool (batch_count,coupon_key) VALUES(1,'小明'),(2,'小强'),(3,'小杜'),(4,'小李'),(5,'小白');

<!-- 注意：1.拼接语句注意values放在循环和itrm标签外 -->
<!-- 注意：2.foreach 外部不放括号 -->
<!-- 注意：3.不能忘记values -->
<!-- 注意：4.列表属性是ite... -->
<!-- 注意：5.不要写新增id查询 -->

  <insert id="save" keyColumn="id" keyProperty="id" parameterType="com.jd.xz.dto.CouponSortDo" useGeneratedKeys="true">
    insert into app_xz_coupon_sort
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="provinces!=null and provinces.size>0 ">
        province_id,
      </if>
      <if test="provinces!=null and provinces.size>0 ">
        province_name,
      </if>
      <if test="isDeleted != null">
        is_deleted,
      </if>
    </trim>
      VALUES
    <if test="provinces!=null and provinces.size>0">
      <foreach collection="provinces" item="item" open=" " close=" " separator=",">
          <trim prefix=" (" suffix=")" suffixOverrides=",">
              <if test="provinces!=null and provinces.size>0 ">
                  #{ite},
              </if>
              <if test="provinces!=null and provinces.size>0 ">
                  #{item.name},
              </if>
              <if test="isDeleted != null">
                  #{isDeleted,jdbcType=INTEGER},
              </if>
          </trim>
      </foreach>
    </if>
  </insert>

4.新增返回主键值

<insert id="insert" parameterType="com.jd.xz.entity.AppXtlBrandsServ" >
    <selectKey resultType="java.lang.Long" keyProperty="brandId" order="AFTER" >
      SELECT LAST_INSERT_ID()
    </selectKey>
    insert into app_xtl_brands_serv (brand_name)
    values (#{brandName})
  </insert>

5.case…when…then 根据条件更新

-- 停用 省
update xtl_pis_region r set  r.parent_id = '0909' ,modified=now(), 
status = 
case status
	when 2 then 3 
	when 1 then 1
end ,modifier = '乐山'
where parent_id = '1024083'


-- 启用 省
update xtl_pis_region r set  r.parent_id = '1024083' ,modified=now(), 
status = 
case status
	when 3 then 2 
	when 1 then 1
end ,
modifier = 'chenyajie11'
where parent_id = '0909'

update xtl_pis_region r set  r.parent_id = '1024083' ,modified=now(), status = case status when 3 then 2 end where parent_id = '0909'



UPDATE
shop_goods
SET 
goods_service = 
CASE 
    WHEN goods_service = '24小时发货,七天退换货,急速退款' THEN '2,1,3'
    WHEN goods_service = '七天退换货,急速退款' THEN '1,3'
    WHEN goods_service = '24小时发货,急速退款' THEN '2,3'
    WHEN goods_service = '七天退换货' THEN '1'
END
--————————————————
-- 原文链接：

应用：批量更新 多个优惠券 的所有省份 的数据

-- 多字段，多状态 批量修改
select id,putkey,province_id,province_name,sort_no_must,mustpush_time_start,mustpush_time_end, modifier, modified, is_deleted from app_xz_coupon_sort where putkey in('demoData','A5AROH_xIpeffAs_-naABEFoeHW8pRLuL_0pnHzy9nn0VbznjjU8') order by id;

-- 不同优惠券Putkey更新不同的 sort_no_must、mustpush_time_start、mustpush_time_end 的值
update app_xz_coupon_sort  set  
sort_no_must =  case putkey
	when 'demoData' then 333331 
	when 'A5AROH_xIpeffAs_-naABEFoeHW8pRLuL_0pnHzy9nn0VbznjjU8' then 666661 end 
,mustpush_time_start =  case putkey
	when 'demoData' then '2020-06-08 11:11:11' 
	when 'A5AROH_xIpeffAs_-naABEFoeHW8pRLuL_0pnHzy9nn0VbznjjU8' then '2020-06-08 22:00:00' end 
,mustpush_time_end =  case putkey
	when 'demoData' then '2020-09-01 22:22:22' 
	when 'A5AROH_xIpeffAs_-naABEFoeHW8pRLuL_0pnHzy9nn0VbznjjU8' then '2020-09-01 22:00:00' end 

,modified=now(), modifier = 'chenyajie11' where putkey in('demoData','A5AROH_xIpeffAs_-naABEFoeHW8pRLuL_0pnHzy9nn0VbznjjU8');

select id,putkey,province_id,province_name,sort_no_must,mustpush_time_start,mustpush_time_end, modifier, modified, is_deleted from app_xz_coupon_sort where putkey in('demoData','A5AROH_xIpeffAs_-naABEFoeHW8pRLuL_0pnHzy9nn0VbznjjU8') order by id;

6.and or 条件查询

-- 单独的或条件需用括号包起来
WHERE t.gather_erp = 'chenyajie11' and ( province_names_str like concat('%','雨巷','%') or province_names_str like concat('%','北京','%') )

7.根据一个表数据更新另外一个表

参考：

方法一：
update 更新表 set 字段 = (select 参考数据 from 参考表 where  参考表.id = 更新表.id);
update table_2 m  set m.column = (select column from table_1 mp where = );

方法二：
update table_1 t1,table_2 t2 set t1.column = t2.column where  = t2.pid;

实践应用：
update app_xz_coupon_sort t1,app_xz_coupon_center t2 set t1.center_id =  where t1.putkey = t2.url

查询函数

1.取最大值 greatest()

SELECT GREATEST('',3,4) from dual;
SELECT GREATEST(IF(ISNULL(NULL),0,2),3,4);

select GREATEST(sort_no_default,sort_no_must,sort_no_pro) 最大,id, center_id, sort_no_must, sort_no_pro, sort_no_default FROM app_xz_coupon_sort where center_id = 3 and province_id = 21 and is_deleted = 1 and id = 14

SELECT GREATEST(IF(ISNULL(NULL),0,2),3,4)

select id,mustpush_time_start, mustpush_time_end,send_time_start, send_time_end  FROM app_xz_coupon_sort where  id = 14;
select greatest(if(isnull(mustpush_time_start),0,mustpush_time_start) ,if(isnull(mustpush_time_end),0,mustpush_time_end),if(isnull(send_time_start),0,send_time_start),if(isnull(send_time_end),0,send_time_end)) 最大,id,mustpush_time_start, mustpush_time_end,send_time_start, send_time_end  FROM app_xz_coupon_sort where  id = 14;


select greatest(mustpush_time_start,mustpush_time_end,send_time_start,send_time_end) 最大,id,mustpush_time_start, mustpush_time_end,send_time_start, send_time_end  FROM app_xz_coupon_sort where  id = 14;

-- 
select greatest(time1,time2,time3) 最大,id,time1, time2,time3  FROM table_name where  id = 14;

2.concat() 模糊查询

concat(first_catg_name,'>',second_catg_name,'>',third_catg_name)
-- 还可用于模糊查询
province_names_str like concat('%','雨巷','%');

-- 查询 1,2,3,4,5 类似的
select  group_concat(id) from app_xz_coupon_sort where isnull(province_id)

3.group_concat 聚合函数

-- 查询 1,2,3,4,5 类似的
select  group_concat(id) from app_xz_coupon_sort where isnull(province_id);


 select ,r.gather_erp,r.gather_erp_name,r.region_id,r.region_name,r.parent_id,r.status,r.created,r.creator,r.modified,r.modifier, p.region_id province_id, p.region_name province_name ,p.status
 FROM xtl_pis_region r
 LEFT JOIN xtl_pis_region p
 ON r.region_id = p.parent_id
 where p.parent_id = '1024083'
 ;
 
 select ,r.gather_erp,r.gather_erp_name,r.region_id,r.region_name,r.parent_id,r.status,r.created,r.creator,r.modified,r.modifier ,p.status
 ,group_concat(p.region_name)
 FROM xtl_pis_region r
 LEFT JOIN xtl_pis_region p
 ON r.region_id = p.parent_id
 where isnull(r.parent_id)
 group by  
 ;

4.ifnull(modified,created)

若modified为空则取created

1、若更新时间为空，则取创建时间，然后倒序

2、若时间皆为空，则id倒序

select id,  create_time, update_time,ifnull(update_time,create_time) jg, ifnull(update_time,ifnull(create_time,id))  jg2
 FROM app_xtl_usr order by ifnull(update_time,create_time) desc,id desc

5.isnull(modified)

select * FROM xtl_pis_region where isnull(modified)

6.find_in_set(str,strlist)

-- 现有2条数据
1--11,22,33,44,55
2--111,222,333
select * from app_reward_activity where find_in_set('111',`name`);  --111,222,333

select * from app_reward_activity where find_in_set('1',`name`) ;  --无

参考:

7.regexp()

MySQL中REGEXP正则表达式使用大全

参考：

第五部分 索引

一、查看

SHOW INDEX FROM xtl_pis_sku_ext;

二、普通索引 index

alter table users_role add index index_taskId(`taskId`);

三、唯一索引 unique index

-- 创建唯一索引
alter table user_base add unique index idx_user_mobile(`mobile`); 
alter table user_base add unique index idx_user_name(`usr_name`); 
-- 删除唯一索引
alter table user_base drop index `usr_name`;


ALTER TABLE `brand`.`app_supplier_brand_payinfo` 
MODIFY COLUMN `dt` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '推送时间(2020-06-09)' AFTER `single_output_trend`,
ADD INDEX `idx_brand_uid`(`brand_uid`) COMMENT '品牌馆ID索引';

四、主键索引

主键的索引，一张表只能有一个

五、navicat premium 编辑索引

结构–> 【Fields】右边的【Indexes】里进行编辑

第六部分 其他情况

order by 可以用别名, where 不能用别名

select id gtime from xtl_pis_sku_ext 
where gtime = 868142
order by gtime desc