MySql常见语句

#查版本
SELECT VERSION();
#查当前时间
SELECT NOW();
#查时间戳
SELECT FROM_UNIXTIME(1715270400);
#日期转时间戳
SELECT UNIX_TIMESTAMP("2024-05-10 00:00:00");
SELECT CURDATE();
#备份数据库
mysqldump -h127.0.0.1 -uroot -p123456 db_2024.* > mysqldump.sql
#mysql登录
mysql -uroot -p
#导入数据库
use db_2024;
source /path/to/mysqldump.sql;
                    

授权用户

#授权
grant all privileges on db_2024.* to 'user_xx'@'%' identified by '123456';
#取消授权
REVOKE all privileges ON db_2024.* FROM 'user_xx'@'%';
#刷新权限
flush privileges;
                    

创建数据库

create database db_2024 default charset 'utf8mb4';
show database;
use db_2024;
                    

创建表

show tables;
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `open_id` varchar(255) NOT NULL DEFAULT '' COMMENT 'OPEN ID',
  `account` varchar(255) NOT NULL COMMENT '账号',
  `nickname` varchar(255) NOT NULL DEFAULT '' COMMENT '昵称',
  `password` varchar(255) NOT NULL DEFAULT '' COMMENT '密码',
  `avatar` varchar(255) NOT NULL DEFAULT '' COMMENT '头像',
  `email` varchar(255) NOT NULL DEFAULT '' COMMENT '邮箱',
  `mobile` char(11) NOT NULL DEFAULT '' COMMENT '手机号',
  `is_locked` tinyint NOT NULL DEFAULT '0' COMMENT '锁定，1是 0否',
  `status` tinyint NOT NULL DEFAULT '0' COMMENT '状态，0有效 1无效 2已删除',
  `last_login_ip` varchar(255) NOT NULL DEFAULT '' COMMENT '最后登录IP',
  `last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
  `expired_at` datetime DEFAULT NULL COMMENT '过期时间',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted_at` datetime DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `account` (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';


#创建临时表
create table user like old_user;
create temporary table user like user;
create procedure p_user delimiter //;

      declare var_time=current_timestamp;

      declare data=select * from user where last_login_time>var_time;

      fetch from data;

      //todo select insert here

end //;

                    

修改字段

修改表结构
alter table user add column `column1` varchar(255) NOT NULL default '' COMMENT '字段' after  `deleted_at`;
ALTER TABLE user drop column `column1`;
ALTER TABLE user add unique key key1(`column1`);
alter table user change old_column `column1` varchar(255) NOT NULL default '' COMMENT '字段';
#清空表数据（记得先备份数据）
truncate table user;
#删除表（表删除，数据也被删，记得先备份数据）
drop table user;
                    

查询语句

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'db_2024' ;
SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='db_2024' AND TABLE_NAME='user';
SELECT * FROM user where id=1 limit 1;
SELECT count(1) as total,count(distinct username) count_distinct_username FROM user where is_online=1;
SELECT * FROM user where id in (1,2,3) limit 1;
SELECT * FROM user where id <> 1 limit 1;
SELECT * FROM user where username like '%zhang%' limit 1,10;
SELECT * FROM user where last_login_time is not null limit 1,10;
SELECT * FROM user where find_in_set("玩游戏",hobbies);
#子查询
SELECT user.*,(select 1 from t_user_log where t_user_log.user_id=user.id order by id desc limit 1) as user_log
FROM user where id=1 limit 1;
#关联查询
SELECT user.*,t_user_bank.* from user inner join t_user_bank.user_id=user.id where user.id=1;
SELECT user.*,t_user_bank.* from user left join t_user_bank.user_id=user.id where user.id=1;
SELECT user.*,t_user_bank.* from user right join t_user_bank.user_id=user.id where user.id=1;
#having
SELECT * FROM t_user_bank where status=1 group by user_id having count(user_id) > 1;
SELECT *,group_concat(id order by id desc separator "\n") as concatUserId
FROM t_user_bank
where status=1 group by user_id;
SELECT * FROM user where status=1 order by status desc;
#临时表
SELECT * FROM user where user_id in (SELECT user_id from order where status=1);
SELECT o.* FROM (SELECT user_id from order where status=1) as o order by o.id desc;

#插入语句
insert into user (username,email) values("zhangsan","test@test.com");
insert into user (username,email) values("zhangsan","zhangsan@test.com"),("lisi","lisi@test.com");
insert into user select * from old_user;
#替换语句
replace into test (id,age,name) value (2,2,'t2');
#更新语句
update user set status=2,update_at=current_timestamp where id=1 limit 1;
#删除语句
delete from user where id=1 limit 1;