数据库常用命令及基础知识

2017/10/04 - 数据库

用户和授权相关命令

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,
    addrs varchar(2048),
    ports varchar(2048),
    hosts varchar(2048),
    package_data varchar(4096),
    creat_time timestamp 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 

数据库提示语

http://blog.csdn.net/dba_waterbin/article/details/8779502

小表:只存比较新的数据,旧数据删除,控制表数据的量

分表:不同表名uin1、uin2。。。来存放数据

分区:同分表,但由MySql帮我们分

未完待续


如果文章对您有帮助,欢迎扫描下方二维码赞助(一分也是爱噢),谢谢

Search

    一分也是爱噢 一分也是爱

    目录