Mysql | MariaDB | 常用操作 | 增删改查 | 表 | 数据库

用户权限

查看用户权限

select user,host from mysql.user;

修改用户权限

GRANT
  [权限] 
ON [库.表] 
TO [用户名]@[IP] 
IDENTIFIED BY [密码] 
# WITH GRANT OPTION;

需要刷新权限

FLUSH PRIVILEGES;

如果需要远程访问需要将mysql监听端口改成0.0.0.0

修改用户密码

set password for 'userName'@'%'=password('password')

数据库操作

查看所有数据库

show databases;

查看数据库所有表

show tables;

查看数据库大小

mysql查看数据库大小 - Withfeel - 博客园
1、进入information_schema 数据库（存放了其他的数据库的信息）

use information_schema;

2、查询所有数据的大小：

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;

3、查看指定数据库的大小：

比如查看数据库home的大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home';

4、查看指定数据库的某个表的大小

比如查看数据库home中 members 表的大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home' and table_name='members';

表操作

查看表结构

desc tableName;

删除数据表

drop删除内容和定义，释放空间。

drop table table_name ;

删除内容、释放空间但不删除定义，数据表的结构还在。

truncate table table_name ;

删除有外键约束的表时：

• 删除之前先执行，删除外键约束

SET foreign_key_checks = 0

• 删除完之后再执行，启动外键约束

SET foreign_key_checks = 1

修改字段

• 新增id字段（自增，非空，主键）

alter table tableName add id int unsigned not Null auto_increment primary key;

• 删出表字段

 alter table tableName drop colName;

• 修改字段

alter table 表名称 change 字段名称 字段名称 字段类型 [是否允许非空];

alter table 表名称 modify 字段名称 字段类型 [是否允许非空];

• 删出主键

alter table table_test drop primary key;

• 新增外键

alter table tableName1 add foreign key(colName1) references tableName2(colName2);

更新数据

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

获取表行数

select count(1) from tableName;
select count(*) from tableName;
select count(rowkey) from tableName;
select count(col) from tableName;

1. select count(col1) from test;
   当col1 字段not null时，遍历索引idx_col1，每读取一条记录将count总数加1;
   当col1 字段允许为null，遍历索引idx_col1，每读取一条记录，先判断是否为null，不为null时将count总数加1。

2. select count(id) from test;
   由于id是主键，具有唯一属性，所以只需要遍历主键索引，过程中不需要判断是否null，每读取一条记录将count总数加1。

3. select count(1) from test;
   遍历整张表，但不取具体的行数据到内存，只是对于每返回的一行，server层都放一个数字’1’进去，每一行数据将count总数加1。

4. select count() from test;
   遍历整张表，但对于count()，MySQL做了优化，不会读取具体的行数据，只要读取到一行，就按行累加。

count(字段)的方式，如果字段上有索引，如果允许为null，对于每一个字段的值还要进行一次判断；如果字段上没有索引，那么也是要走主键索引。所以一般情况下，count(主键)快于count(字段)。
但是如果主键索引的数据行非常大，而字段索引的键值比较短，也就是说，遍历主键索引所读取的数据块要多于遍历二级索引的数据块，那么有可能count(字段)要快于count(主键)。

综合起来，几个count的效率排序如下：
count(*)>count(1)>count(主键)>count(字段)