MySQL - [02] 常用SQL
题记部分
一、连接MySQL服务器
1、常规连接方式
# 连接本地mysql服务器
mysql -u 用户名 -p
# 连接到指定mysql服务器,回车执行该命令之后需要输入密码
mysql -h 主机名或IP地址 -P 端口号 -u 用户名 -p
# 连接到当前mysql服务器,回车执行该命令不需要另外输入密码
mysql -h$(hostname -i) -pharley -uroot -P3306
2、使用套接字
mysql --socket=/path/to/your/mysql.sock -u 用户名 -p
二、DDL
2.4、update
(1)修改字段的部分内容
有一张表
harley.jdbc_config
,表中有字段url
,值的格式大致为jdbc:mysql://hostname:port/dbName?xxa=a&xxb=b&xxc=c
的值。现有如下需求:该表中hostname:port相同的记录有多条,但是hostname和port均发生变更,需要修改库中所有涉及到的记录。
关键SQL如下
update harley.jdbc_config
set url=concat(
substring_index(url, "://", 1),
"://",
"newHostname:newPort",
"/",
substring_index(substring_index(url, '://', -1), '/', -1))
where condition = XXX;
三、DML
DATABASE
-- 查询数据库
show databases;
-- 创建数据库
create database if not exists dbName;
-- 切换数据库
use dbName;
-- 删除数据库
drop database dbName;
drop database if exists dbName;
-- 查看当前使用的数据库
select database();
TABLE
-- 创建表
create table if not exists 表名(
字段名1 数据类型1,
字段名2 数据类型2,
...
字段名n 数据类型n
);
-- 添加数据
insert into 表名(字段名1,字段名2,...) values (值1,值2,...);
insert into 表名 values (值1,值2,...);
insert into 表名(字段名1,字段名2,...) values (值1,值2,...),(值1,值2,...),(值1,值2,...)...;
insert into 表名 values (值1,值2,...),(值1,值2,...),(值1,值2,...)...;
-- 修改数据(不加条件,则所有数据都修改)
update 表名 set 字段名1=值1,字段名2=值2,...[where 条件];
-- 修改表
alter table 表名 rename to 新表名; -- 重命名
alter table 表名 add 字段名 数据类型; -- 添加字段
alter table 表名 modify 字段名 新数据类型; -- 修改字段数据类型
alter table 表名 change 字段名 新字段名 新字段类型; -- 修改字段名以及字段的数据类型
alter table 表名 drop 字段名; -- 删除字段
-- 清空数据
truncate table 表名;
-- 修改自增序列
alter table 表名 auto_increment = 1;
-- 删除数据(不加条件,则所有数据都删除)
delete from 表名 [where 条件];
-- 删除表
drop table if exists 表名;
将version字段放到email字段后面
alter table user modify version int default 1 null comment '乐观锁' after email;
INDEX
-- 使用create index语句创建索引
create index 索引名称 on 表名(列名);
-- 在创建表的时候指定普通索引
create table table_index (
id int primary key auto_increment,
name varchar(20),
index index_name(name)
);
-- 使用alter table语句
alter table table_name add index index_name(column_name);
其他
-- 查看数据库harley_test的总数据大小(单位MB)
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='harley_test';
-- 查看MySQL中所有表的记录数、数据容量、索引容量
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;
-- 查看所有数据库及表的记录数、数据容量、索引容量
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;
-- 查看某个数据库所有表的记录数、数据容量、索引容量。
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='harley_test'
order by data_length desc, index_length desc;
Q&A
Q1 | 查看当前是服务端还是客户端
show variables like '%read%only%';
如果read_only为true,即为只读权限,客户端是只读权限。
Q2 | 查看系统时间&时区
-- 查看系统时间
select now();
-- 查看时区
show variables like '%time_zone%';
Q3 | mysql的某用户被锁住,如何解锁?
-- 使用root权限的用户
alter user 'username'@'localhost' ACCOUNT UNLOCK;
username
是要解锁的用户的用户名,localhost
也可以改为%
如果要查看用户是否被锁定,执行以下sql
select user,host,account_locked from mysql.user where user='username' and host='localhost';
Q4 | 如何根据表名查询该表所属的数据库
select table_schema from information_schema.TABLES where table_name = '表名';
mysqldump [options] database_name [table_name ...] > dump.sql
mysqldump -uroot -p test > dump.sql
-- 备份全库数据
mysqldump -u用户名 -p密码 --all-databases > /保存路径/文件名.sql
mysqldump -u用户名 -p密码 数据库名 > /保存路径/文件名.sql
— 要养成终身学习的习惯 —
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性