mynotes/mysql/MySQL常用命令.md

MySQL 用户

-- 登录
mysql -u<用户名> -p[密码] 
-- 修改密码
mysqladmin -u<用户名> -p[密码] password <new_password>   

数据库

MySQL数据库中的 UTF8 编码使用3个字节存储字段，无法存储emoji 表情这样的字段数据。 MySQL5.5.3+ 后来加入了 utf8mb4 支持，完全兼容 utf8。utf8mb4 最多使用4个字节存储字符，这样就可以存储emoji表情了。 Tip：

• jdbc url 中不要加 characterEncoding=UTF-8 或者 characterEncoding=UTF8MB4。连接器会自动识别字符编码的。
• utf8mb4 对MySQL 版本要求最低 5.5.3
• utf8mb4 对 jdbc mysql驱动版本最低 5.1.41

-- 显示所有的数据库
show databases;

-- 创建数据库，设置字符集utf8mb4，校对集 utf8mb4_general_ci，支持emoj表情
create database db_name default character set utf8mb4 collate utf8mb4_general_ci;

-- 查看创建数据库的语句
show create database db_name;

备份和恢复

-- 导出数据库（注意：当前命令是在cmd命令行下执行）
mysqldump -u<用户名> -p[密码] db_name > filename

-- 恢复
-- 1.先创建一个数据库
create database db_name default character set utf8 collate utf8_general_ci;
-- 2.使用该数据库
use db_name;
-- 3.导入数据库数据
source filename;

表操作

-- 创建表
create table if not exists student(
	id int(11) primary key auto_increment,
	name varchar(50) unique not null,
	age tinyint(2) comment '年龄',
	sex tinyint(1) default 0 comment '0男1女'
) ENGINE=INNODB comment '学生表';

-- 查看创建表的原始语句
show create table table_name;

-- 查看表结构
desc table_name;

-- 查看所有表
show tables;

-- 删除表
drop table table_name;
drop table if exists table_name;

-- 重命名表
alter table table_name rename new_table_name;

列操作

-- 添加列
alter table table_name add column column_name int not null;

-- 删除列
alter table table_name drop column column_name;

-- 修改列属性
alter table table_name modify column column_name float not null;

-- 修改列
alter table table_name change column column_name_1 column_name2 float default 0;

索引

-- 添加索引
alter table table_name add index ind_column_name(column_name);
create index ind_column_name on table_name(column_name);

-- 删除索引
alter table table_name drop index ind_column_name;
drop index ind_column_name on table_name(column_name);

-- 查看索引
show index from table_name;

--  PS1：索引是不可修改的，只能删除后再创建
--  PS2：对表记录的删除会造成索引的存储碎片，过多的存储碎片不仅占用存储空间，还会降低数据库运行速度。重建索引能够有效的进行“碎片整理”。
--  查看索引存储碎片（当 Data_free 列值大于0时表示有碎片，值越大碎片越多）
show table status like 'table_name';

增删改查

-- 插入
insert into student(id, name, age, sex) values(1, '小明', 23, 0);
-- 批量插入
insert into student(name, age, sex) values
('小强', 18, 0),
('小华', 28, 1),
('小张', 23, 1);

-- 删除
delete from student where id=1;

-- 更新
update student set age=24 where id=2;

-- 查询
select * from student;

日期时间转换

-- 当前时间
select now(); -- 结果：2024-04-10 08:41:12
select sysdate(); -- 结果：2024-04-10 08:41:12
-- sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值；

-- 当前时间戳
select current_timestamp, current_timestamp();

-- 日期转为字符串
select date_format(sysdate, '%Y%m%d%H%i%s');
-- 时间转为字符串
select time_format(now(), '%Y%m%d%H%i%s');

-- 字符串转为日期
select str_to_date('2024-01-01', '%Y-%m-%d');
select str_to_date('2024-01-01 08:30:12', '%Y-%m-%d %h:%i:%s');

插入的特殊操作

INSERT SELECT

-- 给所有女学生插入一条礼品记录  
INSERT INTO student_gift(student_id, gift)   
SELECT id, '手绳' as gift   
FROM student   
WHERE sex=1 ;  

INSERT FROM

-- 将 student 表数据复制到 student_bak 表  
INSERT INTO student_bak FROM student ;

更新的特殊操作

更新值为null

-- 直接设置等于常量null
update student s set s.age=null where s.id=3;

注意： 如果列设置了NOT NULL，必须先修改这个列的属性。否则会报错：1048 - Column 'name' cannot be null

替换字符

-- 将 student 表字段 name 中的 '小' 全部替换成 '大'  
UPDATE student SET name=REPLACE(name, '小', '大') WHERE name LIKE '%小%' ;

关联更新

-- 将student_bak表的字段更新到student表中  
UPDATE student s
JOIN student_bak b 
	ON b.id=s.id  
SET s.name=b.name, s.age=b.age;