Mysql之常用操作
在此总结,经常使用MySQL数据库的时候的常用操作
在此总结,经常使用MySQL数据库的时候的常用操作
1. 连接数据库
mysql -h'IP地址' -u'用户名' -p'密码' -D库名
//参数说明： -D指定库名直接进入库 省去进入之后再使用use database 命令
2. 命令中常用操作
1）进入某个数据库
use 库名
2）查看表结构
desc 表名
3）查看创建表的语句
show create table 表名
4) 查看单条sql允许插入的包的大小
show VARIABLES like '%max_allowed_packet%';
//临时修改该值
set global max_allowed_packet = 1024*1024*20 //20M
//永久修改
编辑文件my.cnf ,在【mysqld】或者mysql的server配置段进行修改， 修改完毕之后就行重启MySQL
4）查看数据库编码
show VARIABLES like '%char%';
5) 查看版本信息
select @@version； or select VERSION();
6) 查看慢日志信息
show VARIABLES like 'slow_query_log';
8) 查询日志情况
show VARIABLES like '%log%'; //对日志中的某项进行设置 set global log queries not using indexes=on set global slow_query_log=on;
9) 查看当前状态
status;
10)查询当前所在的库
select database();
11) 查看当前数据库队列
show processlist;
12) 计算某个库中数据的大小
select concat(round(sum(data_length/1024/1024)),'MB') as total from information_schema.tables where table_schema='数据库名称';
13）查询mysql中所有用户
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
14）查看所有库的存储情况
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;
15）查看某个库的各表的存储情况
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='mysql' order by data_length desc, index_length desc;
16）查看某个数据库的存储量
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 where table_schema='mysql';
17）查看所有数据库各标的容量
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;
3.数据表相关操作
1）表的修改 alter 表 modify 字段 类型 alter 表 change 字段 新字段 类型 2)同时插入多条数据 insert into 表（字段1，字段2） values（'a','aa'）,('b','bb')...; 3） 创建新用户并授权 grant all on 库名.表名 to '用户名'@'地址' identified by '密码';
权限立即生效: FLUSH PRIVILEGES 4) 创建新用户 create user 名称 identified by '密码'； 5） 修改密码 set PASSWORD for '用户名'@'localhost' = PASSWORD('新密码');
6） 同时对两个字段相同的数据进行排序
group column1,column2;
7) 将int(10)存储的时间戳转化成日期的格式
FROM_UNIXTIME(timestamp, '%Y-%m-%d') 参数： %M 月名字(January……December) %W 星期名字(Sunday……Saturday) %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） %Y 年, 数字, 4 位 %y 年, 数字, 2 位 %a 缩写的星期名字(Sun……Sat) %d 月份中的天数, 数字(00……31) %e 月份中的天数, 数字(0……31) %m 月, 数字(01……12) %c 月, 数字(1……12) %b 缩写的月份名字(Jan……Dec) %j 一年中的天数(001……366) %H 小时(00……23) %k 小时(0……23) %h 小时(01……12) %I 小时(01……12) %l 小时(1……12) %i 分钟, 数字(00……59) %r 时间,12 小时(hh:mm:ss [AP]M) %T 时间,24 小时(hh:mm:ss) %S 秒(00……59) %s 秒(00……59) %p AM或PM %w 一个星期中的天数(0=Sunday ……6=Saturday ） %U 星期(0……52), 这里星期天是星期的第一天 %u 星期(0……52), 这里星期一是星期的第一天 %% 一个文字“%”
8)导出数据库字段结合navicat导出运行之后的结果
SELECT TABLE_NAME as '表名', COLUMN_NAME as '字段名', DATA_TYPE as '字段类型', IS_NULLABLE as '允许NULL', COLUMN_COMMENT as '字段说明', EXTRA AS '主键' FROM information_schema. COLUMNS WHERE TABLE_SCHEMA = '库名';
9)备份数据表
# 复制表结构及数据到新表 create table 新表 select * from 旧表 # 只复制表结构到新表 select table 新表 select * from 旧表 where 1=2 让where条件不成立即可 # 另外一种方式 create table 新表 like 旧表 # 将数据添加到新表 insert into 新表 select * from 旧表
4. 常见SQL处理
1） 同时查询并更新数据
条件： 更新intro的内容，条件是uid=26 可能会想当然的认为： update 'data' set intro = '新的内容' where id in (select id from 'data' where uid=26 );
一定会报错： 
正确： update `data` as a inner join (select id from `data` where uid=26) as b on a.id = b.id set a.intro = '新内容';
UPDATE t_account_trans_info AS ii INNER JOIN t_user AS uu ON ii.tran_user_id = uu.id SET ii.text2 = uu.link_man where ii.tran_user_id = uu.id
# 将表中biz为空的数据进行填充
update mp_account_ext e INNER JOIN mp_account a ON e.mp_aid = a.id set e.biz = a.biz WHERE e.id < 7103 and e.biz = '';
正解:
UPDATE tb_testSET NAME = (select name from ( SELECT NAME FROM tb_test WHERE id = 2) as t)WHERE `status` = 1;
2）同时对两个关联的表的数据进行删除
DELETE m,r from app_member m LEFT JOIN wx_registered r ON m.id = r.userid WHERE m.id = 1343;
3) 将一个表中的数据导入到另外一个表中
当表A和表B的表结构一致时，直接插入即可。 insert into A select * from B; 当表结构不一致时（字段大小、类型都相同） insert into A(col1, col2) select col1, col2 from B;
注意点：
1. 如果表中出现了类似于end，order等关键词的时候，只需要使用反引号引起来即可
常见错误:
1. Can't find any matching row in the user table
grant all on test.* to 'test'@'localhost' 需要指定 密码就可以了 grant all on test.* 'test'@'localhost' identified by '123456'; flush privileges;
