用户和授权相关命令
create user 'username1'@'%' IDENTIFIED BY 'password';//创建用户
GRANT ALL ON *.* TO 'username1'@'%';//授权
GRANT SELECT ON `conn`.* TO 'dbuser'@'%'
GRANT SELECT ON *.* TO 'dbuser'@'%' IDENTIFIED BY PASSWORD '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E'
SHOW GRANTS for dbuser; //查看用户权限
SELECT DISTINCT CONCAT('User:''',user,'''@''',host,''';') AS query FROM mysql.user; //查看所有用户
update mysql.user set host = 'localhost' where USER='dbuser'
update mysql.user set host = '%' where USER='dbuser';//更新用户访问地址来源范围
FLUSH PRIVILEGES; //权限设置生效
DDL相关
create table if not existstask_form(
id intunsigned not null primary key,
type intunsigned not null,
rate tinyintunsigned,
addrsvarchar(2048),
portsvarchar(2048),
hostsvarchar(2048),
package_datavarchar(4096),
creat_timetimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);//创建
alter table task_form modify column rate int;//更改
alter table domain_monitor33 add attention_user varchar(1024) NOT NULL DEFAULT '';
alter table domain_monitor33 change creater_name creater_name varchar(32) NOT NULL DEFAULT '';
create table domain_monitor_bak_2 like domain_monitor;
show create database ip_config_ex;
show create table `xx.gxx.com`
DML相关
truncate table tablename;//删除表所有数据
select count(distinct cmd, subcmd) from ip_config_module_conn;
select * fromxxxx order by record_time desc limit 2;
select supplier_id, supplier_name from suppliers UNION select company_id, company_name from companies ORDER BY 2;
SELECT * FROM products WHERE id=3 FOR UPDATE //for update就等于是加上了一个写锁,会把表锁住无法修改;
update tasks setper_users=5000 where id in(805307245, 805307246, 805307247, 805307248,805307249,805307465, 805307471, 805307472);
delete fromnet_status_source where TIMESTAMPDIFF(HOUR, time, now())> 5;
join http://www.nowamagic.net/librarys/veda/detail/936
INSERT INTO domain_monitor_bak_2 SELECT * FROM domain_monitor;
数据库字段类型
BLOB类型的字段用于存储二进制数据
BLOB是个类型系列,包括:TinyBlob、Blob、MediumBlob、LongBlob,这几个类型之间的唯一区别是在存储文件的最大大小上不同。
类型 大小(单位:字节)
TinyBlob 最大 255
Blob 最大 65K
MediumBlob 最大 16M
LongBlob 最大 4G
Blob 最大 65K
MediumBlob 最大 16M
LongBlob 最大 4G
其他
查看数据库表大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema = 'hummer_cs_cmd_delayed';
SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH) FROM information_schema.TABLES whereTABLE_SCHEMA='数据库名';
查看数据库有多少个表
SELECT count(*) TABLES, table_schema FROM information_schema.TABLES where table_schema = 'domain' GROUP BY table_schema;
连接数据库linux命令
mysql -N -B -uxxx-pxxxxx --connect_timeout=10 -h 1xxx.xxx.xx.xx database
mysql -h XX.xxx.xxx.xx-uxxxx-pxxx
/usr/local/mysql/bin/mysql -uroot -p
数据库提示语
http://blog.csdn.net/dba_waterbin/article/details/8779502
小表
只存比较新的数据,旧数据删除,控制表数据的量
分表
不同表名uin1、uin2。。。来存放数据
分区
同分表,但由MySql帮我们分
备注
mysql安装:https://www.jianshu.com/p/fe2ae064e530
新用户登录失败问题:https://www.jianshu.com/p/834b0a47fc08
未完待续