mariadb数据库常用命令

在数据库中使用任何命令最好都用分号结尾

用户管理

        select user();

        #查看当前用户

        grant all on *.* to root@‘%’identified by '123'

        #给root用户所有的权限允许第三方登录（将root改成不存在的用户，也可以创建并赋予权限）

       grant select,create,update,delete on *.* to 'a'@'%' identified by '输入密码';

       #创建用户a,并给a添加查询，创建，更新，删除的权限（在任意主机上）

        grant all privileges on test03.grade to 'aaa'@'%';

       #给aaa赋予test03数据库的grade表最高权限（在任意主机上）

        show grants for 'aaa';

       #查看aaa用户的权限（root用户）

       revoke all on *.* from 'aaa'@'%';

       #收回aaa在任意数据库的任意表的最高权限

       show grants

       #查看权限

 

忘记mysql的root密码

        配置文件 /etc/my.conf.d/server.conf

        添加skip-grant-tables

        重启数据库 systemctl restart mysql

        进入数据库 mysql -uroot | mysql -uroot

        进入mysql数据库中 use mysql;

        修改密码 update user set password=password('填写密码') where user='root';

查看数据库

     show databases;

     #查看数据库

     show variables;

     #查看数据库参数

     show tables;

     #查看数据库中的表，要先进入数据库

     select  * from 表名;

     #查看 某个表中的所有数据

     mysqladmin --version

     #查看数据库版本

     show create database 库名;

     #查看创建数据库状态

      use 库名

     #使用哪个库

 

 修改数据库

       create database test01

       #创建数据库 test01

      create database test01 character set=utf8;

      #创建用户并支持中文

      show create database  test01

      #查看test01的字符集

      mysqladmin -u user -p create test01

      #直接在终端创建数据库，

      drop database test01

      #删除数据库

      mysqladmin -u root -p drop test01

      #另外你也可以在终端运行，

      alter database 库名 default character set=utf8;   

      #修改数据库默认字符集

 

备份/导出数据库

  要创建单个数据库的备份，在终端窗口中运行下列命令，

  mysqldump -u root -p密码 --databases  test > test.sql

 若要一次性创建多个数据库的备份则运行：

   mysqldump -u root -p密码 --databases  db_name1 db_name2 > db_name12_backup.sql

 从备份中恢复数据库

方法一

 从备份中恢复数据库，终端运行：

  mysql -u root -p密码 database_name < db_backup.sql

   但这条命令成功的前提是预先没有存在同名的数据库。如果想要恢复数据库数据到已经存在的数据库中，则需要用到 mysqlimport 命令：

  mysqlimport -u root -p密码 database_name < db_backup.sql

方法二

  进入数据库中确保没有同名的数据库,执行：

  source 备份的数据库名

 

MariaDB 数据类型

   MariaDB数据类型可以分为数字，日期和时间以及字符串值。

    使用数据类型的原则：够用就行，　尽量使用范围小的，而不用大的

• 常用的数据类型

1. 整数:int, bit   #bit自能存储0,1
2. 小数:decimal 　　#decimal(5,2)  表示一共5位数字小数点后取2位
3. 字符串:varchar, char　#varchar(10) 表示最多可输入10个字符，char(10)输入几个字符也要占10个字符，且最多输入10个10字符
4. 日期时间:date, time, datetime
5. 枚举类型(enum)
6. 自增:auto_incremen

• 约束

1. 主键primary key：物理上存储的顺序，不能重复，不能为空，默认有索引的功能
2. 非空not null：此字段不能为空
3. 唯一unique：此字段不允许重复
4. 默认default：当不填写此值时会使用默认值，如果填写则已填写为准
5. 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常

 

 

 表的相关命令

      create table 表名(字段 数据类型和约束可加多个,….可加多个字段用逗号逗号隔开); 

     #创建表 ，表的字段最好使用英文，字段后需要添加数据类型和约束，可添加多种约束

 

查看表

     select now();

     #显示当前时间

      desc 表名;

     #查看表结构

      show create table 表名；

     #查看表的创建

 

修改表

       alter table 表名 add 列名 类型;

      #修改表-添加字段

       alter table 表名 modify 列名 类型及约束;

       #修改表-修改字段:不重命名版

      alter table 表名 change 原名 新名 类型及约束;

      #修改表-修改字段:重命名版

        alter table 表名 drop 列名;

       #修改表-删除字段

删除表

         drop table 表名;

          #删除表

增表的内容

        insert into 表名 values(..),()…;

          #全列插入，每列都必须写入内容，写在括号里，用逗号分隔，（括号外用逗号分隔可一次插入多行） 主键字段 可以用0 null default 来站位,表示默认值

 

修改表的内容

        update 表名 set 列1=值1, 列2=值2... where 条件;

         #条件指的是修改那一部分

删除表的内容

        delete from 表名 where 条件;

物理删除

        delete from 表名;

        #清空表中的所有的数据

 

逻辑删除

        #用一条字段来表示 这条信息是否已经不能在使用了, 用alter table给表添加一个 bit类型的字段，或者创建表的时候直接创建

       select * from 表名;  #进行查看

        select * from 表名 where 带有bit的字段=bit值; #筛选

       #逻辑删除不是真正的删除，一般是用通过bit值来筛选需要的数据

 

查看表的内容

        select  字段1 as '别名',字段2 as '别名', …. from 表名;

         # 使用as给字段起别名    

      select 表名.字段1,表名.字段2 from 表名;

      #通过表名字段查询，可输入不同的表名

        select 别名.字段1,s别名.字段2,…. from 表名 as 别名;

        # 给表起别名查询

       select distinct 字段 from 表名;

       #  消除重复行

 

条件查询
          select * from 表名 where 字段 比较运算符 值;

            # 比较运算符有(=)(<)(>)(<=)(>=)  可以通过and(&&) or(||) 使用多个运算符

     常用的使用方法：

          select * from 表名 where 字段 between 值 and 值；

          select * from 表名 where 字段  比较运算符 or 字段 比较运算符

 

 模糊查询
                % 替代1个或者多个字符甚至是没有

                _ 下划线表示一个字符

               select * from 表名 where 字段 like '%表中数据的关键字%';

               select * from 表名 where 字段 like '收索几个字就写入几个下划线';

 

范围查询
         in (1,3,8)表示在一个非连续的范围内
          select * from 表名 where 字段 in (第一个值, 第二个值,….);

         #in 后面括号中用逗号分隔开的数字表示单个数字，不代表范围，可以用逗号分隔添加多个数值，列如（15，25，35，56）：表示一个15，一个25，一个35，一个56

 

空判断

              select * from 表名 where 字段 is null;

              #判断is null,查看字段为空的信息

              select * from 表名 where 字段 is not null;

              判断非空is not null,查看字段为不为空的信息

 

排序             
               select * from 表名 where 条件 order by 字段 asc或desc;

               # order by 字段
              # asc从小到大排列,即升序
              # desc从大到小排序，即降序

 

聚合函数

   总数:count(*)   用*统计是最准确的，也可以在括号里加入某个字段，可能会不准确，最好使用*

  最大值:max(字段)

  最小值:min(字段)

  求和:sum(字段)

  平均值:avg(字段)

       select 函数类型 from 表名 where 条件;

       select round(avg(字段),3) from 表名;

       # 保留3位小数

 

分组

 group by
        select 字段 from 表名 group by 字段;

         #对表中某个字段中的内容分组

         group_concat()  按组显示括号中的内容

         select 字段1,group_concat(字段2) from 表名 where 条件 group by 字段1;

 

分页
     显示5页
    select * from 表名 limit 页5;

      分页显示，每页显示2条数据

       select * from 表名 limit 0, 2;

     

连接查询

    inner join

      select * from 表名1 inner join 表名2;

      #两个表连接查询

      select * from 表名1 inner join 表名2 on 表名1.字段=表名2.字段;   

      #连接两个表的对应字段

      select 表名1.字段, 表名2.字段 from 表名1 inner join 表名2 on 表名1.字段=表名2.字段;

       #按要求显示连接内容 可以使用别名

 

左关联   

select * from 表名1 left join 表名2 on 表名1.字段=表名2.字段;

  # 左关联，以左边表为基准，条件对应不上的显示null

 

右关联

   select * from 表名2 left join 表名1 on 表名1.字段=表名2.字段;

  #右关联，以后边为基准，条件对应不上的显示null

 

自关联

   在一个表中建立关联

   列如: select * from 表名 as 别名1 inner join 表名 as 别名2 on 别名1.字段1=别名2.字段2 where 别名1.字段='关键数据';

 

视图

    视图的用途就是查询

      create view 视图名称 as select语句;

      #定义视图，建议以v_开头

      show tables;

      #查看视图：查看表会将所有的视图也列出来

      drop view 视图名称;

       #删除视图

 

事务

        begin或 transaction

        #开启事务

            commit;

       #将缓存中的数据变更维护到物理表中

        rollback;

      #放弃缓存中变更的数据

 

索引

 创建索引

  方式一：建表时创建索引

create table create_index(
id int primary key,
name varchar(10) unique,
age int,
key (age)
);

方式二：对于已经存在的表，添加索引

如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
字段类型如果不是字符串，可以不填写长度部分

create index 索引名称 on 表名(字段名称(长度))
例：
create index age_index on create_index(age);

    drop index 索引名称 on 表名;

    #删除索引

    show profiles;

    #查看sql执行时间

   set profiling=1;

   #打开sql语句执行时间 零时修改，重启数据库失效