MySQL操作命令大全

create database mydb;

show databases;

drop database mydb;

use mydb;

select database(); 

show tables;

create table user (
	id int(11) not null comment '序号',
	username varchar (50) null comment '姓名',
	sex tinyint(4) null comment '性别',
	age int(11) null comment '年龄',
	education int(11) null comment '学历，枚举值..',
	primary key (`id`) using btree
);

desc user;

drop table user;

truncate table user;

delete from user;

rename table old_user to new_user;
rename table user to old_user, user_bak to user; --多表间快速修改

alter table user add column id int(11) not null;

alter table user drop column id;

alter table user add index index_name (username);

alter table user drop index index_name;

insert into user(id, username, sex) VALUEs(1, 'Tom', 1);

delete from user where id = 1;

update user set username = 'Jay' where id = 1;

select * from user where id = 1;

select distinct id from user;

# MySQL没有merge into方法
UPDATE user u,
	   user_tmp ut 
SET u.username = ut.username 
WHERE
	u.id = ut.id;

select * from user order by id;

select education from user group by education;

# MySQL没有merge into方法
SELECT
	education,
	GROUP_CONCAT( username ORDER BY username ) 
FROM user
GROUP BY
	education;

select now();
select current_timestamp();

select date_format('2020-11-19 20:06:00','%Y-%m-%d %H:%i:%s'); --2020-11-19 20:06:00

select str_to_date('2020-11-19', '%Y-%m-%d'); --2020-11-19
select str_to_date('2020-11-19 09:00:00', '%Y-%m-%d %H:%i:%s'); --2020-11-19 09:00:00 

select to_days('2020-01-01'); --737790
select time_to_sec('01:00:02'); --3602
select sec_to_time(3602); --'01:00:02'

select makedate(2020,65); --'2020-03-05'
select maketime(20,25,30); --'20:25:30'

select unix_timestamp('2020-11-19 20:30:00'); -- 1605789000
select from_unixtime(1605789000); --'2020-11-19 20:30:00'

select date_add('2020-11-19', interval 1 year); --2021-11-19
select date_sub('2020-11-19', interval 1 month); --2020-10-19

select timestampdiff(month,'2020-09-01','2021-09-01'); --12