mysql常用操作
总结mysql的连接、库表操作、增删改查等常用语法规则。便于日常使用
目录
shell连接和库操作
表操作
查询
插入数据
更新数据
删除数据
用户权限
获取库、配置、系统信息
shell连接和库操作
本地、远程连接命令
mysql -u root -p；
mysql -h 192.168.2.3 -P 3306 -u root -p；
mysql -u root -p test；
查看所有数据库
show DATABASES;
新建、删除库
create database t1；
drop database db1；
使用某数据库
use dvwa;
表操作
查看该库下所有表名称
show tables;
创建表(最简操作)
create table a1(na char(9),n int(2));
创建数据表
1 使用主键约束
主键两类型：单字段主键，多字段联合主键
定义列同时指定单字段主键
create table t1
(id INT(11) PRIMARY KEY,
name char(6));
定义完所有列后指定单字段主键
create table t1
(id INT(11),
name char(6),
primary key(id));
指定多字段联合主键
create table t1
(id INT(11),
name char(6),
num int(12),
primary key(id,name));
2 使用外键约束 外键约束不能跨引擎使用
外键：在两个表的数据之间建立链接，可以是一列或多列，一个表有一个或多个外键。
外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键的值必须等于另一个表中主键的某个值。
外键是表的一个字段，可以不是本表的主键，但对应另一个表的主键。定义外键后，不允许删除另一个表中具有关联关系的行。
外键的主要作用是保持数据的一致性、完整性。
主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。
CONSTARINT 外键名 FOREIGN KEY(字段名1，字段名2...) REFERENCES 主表名(主键列1，主键列2...)
定义t1
create table t1
(id INT(11) primary key，
name char(6) not null
);
定义表ta，并在ta上创建外键约束
create table ta
(id INT(10) primary key，
name char(6) not null，
deptID INT(5),
CONSTARINT XXX FOREIGN KEY(deptID) REFERENCES t1(id)
);
3 使用非空约束
使用非空约束的字段，插入时没有指定值会报错
create table t1
(id INT(11) primary key，
name char(6) not null
);
4 使用唯一性约束
要求该列唯一，允许空，但只能有一个空，确保一列或几列无重复值
定义列时指定
create table t1
(id INT(11)，
name char(6) unique
);
定义完所有列再指定
constraint 约束名 unique(字段名)
create table t1
(id INT(11)，
name char(6),
constraint sth unique(id)
);
5 使用默认约束
指定某列的默认值，插入时未赋值，系统使用默认值赋值
create table t1
(id INT(11)，
name char(6) default 123,
);
6 设置表的属性值自加
插入新纪录，系统自动生成字段的主键值，一个表只能一个字段使用，该字段必是主键的一部分
支持任意整数类型（tinyint smallin int bigint）
create table t1
(id INT(11) primary key auto_increment,
name char(6)
);
删除表
drop table a1;
修改表名
alter table t1 rename ta;
查看数据表结构
查看基本结构
describe t1;
等效于 desc t1;
查看详细结构
显示创建时的create语句
show create table t1\G;
插入数据到表
insert into a1 values('test',1);
查看表所有内容
select * from a1;
删除表中某条数据
DELETE FROM testjson WHERE name='1';
清空表的所有数据
delete from xxx
delete * from wp_comments;
truncate table wp_comments; #table可不写
delete、truncate区别：
truncate是整体删除（速度较快）,delete是逐条删除,慢
truncate不写服务器log，delete写服务器log，
truncate不激活trigger(触发器)，但是会重置Identity（标识列、自增字段），
相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的ID数。
而delete删除以后，Identity依旧是接着被删除的最近的那一条记录ID加1后进行记录。
修改字段名
alter table 表名 change 旧字段名 新字段名 新数据类型
# 新数据类型可跟原来一样，不能省略
# 也可只改数据类型 不改字段名（新旧字段名设置成一样）
修改字段数据类型
alter table 表名 modify 字段名 数据类型;
alter table t1 modify name varchar(10);
修改表字符集
ALTER TABLE 8A101 DEFAULT CHARACTER SET utf8mb4;
Select CONCAT( 'ALTER TABLE ', table_name, ' DEFAULT CHARACTER SET utf8mb4;' ) FROM information_schema.tables Where table_name L'8%';
添加字段
alter table 表名 add 新字段名 数据类型 [约束条件] [first|after 已存在的；
# first 设置为第一字段，after设置在id字段后，默认添加到最后
添加无完整约束的字段
alter table t1 add num int(10);
添加有完整约束的字段
alter table t1 add num int(10) not unll;
删除字段
alter table 表名 字段名；
修改字段排列位置
alter table t1 modify 字段1 数据类型 first|afxxx；
更改表的存储引擎
alter table 表名 engine=新引擎；
删除表的外键约束
外键一旦删除就会解除主从表的关联关系
alter table 表名 drop foreing key 外键约束名；
drop [if exists] 表1，表2…;
drop table t1；
drop table t1，t2，t3；
删除被其他表关联的主表
表间存在外键关联时，直接删除主表会失败，可先删子表再删主表
# 相关err：error 1217
创建表后添加主键约束
Alter table qa_user add primary key(id);
查询
基本查询
select {*|字段列表} [from 表1，表2… [where 表达式 [...] ] ]
select [字段1，字段2...] from [表或视图] where [查询条件]；
表单查询
使用*通配查询所有字段
select * from t1;
查询指定单个或多个字段
select 列名 from 表名;
select 列名1,列名2,列名... from 表名;
查询指定内容
select name,num from t1 where num>6;
带in关键字查询（指定范围）
select num from t1 where num in (1,10) order by name;
select num from t1 where num no in (1,10) order by name;
# order by 排序
带 between and 的范围查询(包含开始和结束值)
select name from t1 where num between 100 and 102;
带like 字符匹配查询，通配符%,_可和like一起使用
select name from t1 where name like "%aa%'';
select name from t1 where name like "_a__'';
#%匹配任意长度字符、包括0字符
‘_’只匹配任意一个字符,可多个‘_’匹配多个字符
查询空值 isnull
select name from t1 where num is null;
# 空值不同于0和空字符串
多条件查询 and
select name from t1 where num<1 and age>2;
# 可多个and
多条件查询 or
select name from t1 where num<1 or age>2;
# or和and可以一起使用，and优先级高于or
查询结果不重复（消除重复内容）
select distinct name from t1;
查询结果排序
select name from t1 order by name；
# 指定单列排序
select name,num from t1 order by name,num；
# 指定多列排序，先排name再排num
select name,num from t1 order by name desc,num asc；
# 指定排序方式 desc降序 asc升序
分组查询
[group by 字段] [ having 条件表达式]
1 创建分组：
select name ,count(*) as total from t1 group by age;
select id,group_concat(name) as aname from t1 by id;
2 使用having过滤分组
select id,group_concat(name) as aname from t1 by id having count(name)>1;
# having、where 都用于过滤，having在数据分组后过滤
3 在group by 子句子中使用with rollup
select name ,count(*) as total from t1 group by age with rollup;
# 在查询记录后增加记录总和
4 多字段分组
select * from t1 group by name,num;
5 group by、order by 一起使用
限制查询结果数量 limit
limit [位置偏移量(即从哪一行开始显示)] 行数
select * from t1 limit 100；
聚合函数查询（对获取的数据进行分析和报告）
count()
select count(*) as r from t1;
select count(name) as r from t1;
计算表的总行数(不忽略空值)，计算列的总行数(忽略空值)；
sum()
select sum(num) as r from t1 ;
# 返回指定列的总和，忽略值为null的行
avg()
select avg(num) as r from t1 ;
# 求平均值
max()
select max(num) as r from t1 ;
#返回最大值，支持字符类数据
min()
select min(num) as r from t1 ;
#返回最小值
连接查询
内连接(INNER JOIN)查询
使用比较运算符进行表间某些列的比较操作，并列出这些列中与连接条件相匹配的数据
select ta.id,name,num from ta,tb where ta.id =tb.id
# ta tb中均有id，需要万千限定表名ta.id
# 两张表一样的 称为自连接
外连接查询
左连接
右连接
符合条件连接查询
子查询
一个查询语句嵌套在另一个查询语句的查询
带any、some关键字的子查询
select num from t1 where num >any(select num1 from t2);
# 返回t2所有值，然后比较num，num1大小，大于t2任何一个值即符合
带all关键字的子查询
select n1 from t1 where n1 >all(select n2 from t2);
#返回大于所有n2的n1的值
带exists关键字的子查询
select * from t1 where exists (select num from t2 where name='zz');
#exists结果为ture(至少返回一行)才进行外层查询，false不
select * from t1 where no exists (select num from t2 where name='zz');
带in关键字的子查询
select n1 from t1 where n1 in (select n2 from t2 where n2>1)
带比较运算符的子查询
select n1,name from t1 where n1>(select...)
合并查询（UNION）
select n1 from t1 union select n2 from t2;
select n1 from t1 union all select n2 from t2;
#all:不删除重复行，也不自动排序
为表、字段取别名
表名
表名 as 别名
select * from t as t1
字段名
select name as aname ,num as n1 from t ;
#也可对聚合查询的结果取别名
使用正则表达式查询
select n from t where name regexp '^c';
插入数据
为表所有字段插入
insert into t1(id,name) values(1,"a");
为表的指定字段插入
insert into t1(name) values("a");
同时插入多条记录
insert into t1(id,name) values(1,"a"), values(1,"a")...;
将查询结果插入表中
insert into t1(name) select name1 from t2;
更新数据
update t1 set name='a',num=12 where id=2;
update t1 set age='a',num=12 where age>10;
#如果忽略where 将更新所有行
删除数据
delete from t1 where id =1;
删除所有内容
delect from t1；
truncate t1；
#truncate直接删除表，快
获取库、配置、系统信息
查询所有库的大小
select
TABLE_SCHEMA,
concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
ORDER BY data_size desc;
查询总大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES
统计库下表数量
SELECT COUNT(TABLE_NAME) FROM information_schema.tables WHERE TABLE_SCHEMA = 'power-data'
查看当前时间+日期
select now(); select sysdate();
now() 执行开始时，sysdate()执行动态获取
获取当前日期：
curdate(); current_date(); current_date();
获取当前时间：
curtime(); current_time(); current_time;
获取UTC时间
UTC日期：utc_date(); UTC时间：utc_time(); UTC日期+时间：utc_timestamp;
查看状态
SHOW [统计范围] STATUS [LIKE '状态项名称'] 统计范围关键字分为GLOBAL和SESSION(或LOCAL)两种。
show status; 直接返回300条状态
show status like 'uptime'；
show status后的LIKE关键字也可以使用'_' 或'%'等通配符来进行模糊匹配
show status like 'Thread_%';
查看所有配置参数
show variables;
查看数据存储路径
show global variables like "%datadir%"
查看错误日志名称和存储位置
show variables like 'log_error';
查看通用日志保存类型（可文件、表格格式）
show variables like "%log_output%";
修改某条配置参数
set xxx='xxx'；
set global xxx='xxx'；
用户权限
开启root远程访问权限
update user set host='%' where Host='localhost' and User='root'
flush privileges;
刷新
flush privileges;
查看用户权限
show grants for user1; show grants for 'u1'@'%'
