# 常用命令

# 登录数据库

mysql -uroot -p123123

# 操作数据库

# 创建数据库
create database `jeecg-boot` default character set utf8mb4 collate utf8mb4_general_ci;
# 查看数据库
show databases;
# 选择使用数据库
use test_01;
# 删除数据库
drop database test_02;

# 操作表

# 创建表

DROP TABLE IF EXISTS `tb_01`;
CREATE TABLE `tb_02` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `name` varchar(100) DEFAULT NULL,
    `icon` varchar(500) DEFAULT NULL COMMENT '分类图标',
    `help_count` int(11) DEFAULT NULL COMMENT '专题数量',
    `show_status` int(2) DEFAULT NULL,
    `sort` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='帮助分类表';

# 查看表定义

desc tb_01;
# 显示创建表的sql
show create table tb_02;
# 删除表
drop table tb_02;
# 更改表名
alter table tb_02 rename tb_02_02;

# 操作字段

修改字段 modify
添加字段 add
删除字段 drop
字段改名 change

alter table tb_02 modify sort int(12);
alter table tb_02 add age int(3);
alter table tb_02 drop age;
alter table tb_02 change age age1 int(4);

# 操作数据(DML)

# 插入数据

# 1.指定名称插入
insert into tb_02 (id, name, icon, help_count, show_status, sort, age1) values (13, '你知道吗', 'why', '123213', 01, 12, 12);
# 2.不指定名称插入
insert into tb_02 values (14, '爱一个是多么的痛苦。。', 'why', '123213', 01, 12, 18);
# 3.批量插入
insert into tb_02 values (15, '打到你哭。。', 'ku', '213', 01, 12, 18),
                         (16, '你咋没厉害。。', 'ku', '213', 01, 12, 18);

# 更新数据

update tb_02 set show_status=02, sort=19 where id = 15;

# 删除数据

delete from tb_02 where id=13;

# 查询数据

# 查询不重复记录
select distinct icon from tb_02;
# 条件查询
select * from tb_02 where show_status = 1 and sort<18;
# 排序和限制 默认 -- 生序 desc -- 降序
select * from tb_02 order by sort desc limit 2;
# 分页查询
select * from oms_order order by member_id limit 3,5;
# 连接查询
select * from oms_order oo left join oms_order_item ooi on oo.id = ooi.order_id;
# 子查询
select * from oms_order_item where order_id in (select id from oms_order);

# 数据库操作(DCL)

# 查看账号权限

show grants for 'root'@'localhost';

# 账号相关

# 删除账号
drop user 'test'@'localhost';
# 修改当账号密码
set password = password('123456');
# 管理员修改其他账号密码
set password for 'test'@'localhost' = password ('123123');

# 字符集

# 查看字符集
show variables like 'character%';
# 创建数据库时指定字符集
create database test2 character set utf8;

# 时区

# 查看当前时区
show variables like '%time_zone%';
# 修改 mysql 全局时区为东八区(北京时间)
set global time_zone = '+8:00';
# 修改当前会话时区
set time_zone = '+8:00';
# 立即生效
flush privileges;