MariaDB

关闭防火墙和selinux

#systemctl stop firewalld

 

安装服务端和客户端mariadb

# yum install mariadb-server mariadb -y

mariadb配置文件位置

# vi /etc/my.cnf

 开启服务

# systemctl start mariadb

设置成开机自启动

# systemctl enable mariadb

添加编码格式为utf-8

重启服务，进入数据库

# systemctl restart mariadb

# mysql

---

 

查看库

MariaDB [(none)]> show databases;

库的存放位置

# cd /var/lib/mysql/

进入到某个库

MariaDB [(none)]> use mysql

查看当前在哪个库

MariaDB [mysql]> select database();

创建库

MariaDB [mysql]> create database aa;

查看库中的表

MariaDB [aa]> show tables;

创建表

分开写

MariaDB [aa]> create table bb (
-> id int,
-> name varchar(10),
-> city varchar(10));

写在一行

MariaDB [aa]> create table cc (id int,name varchar(10),city varchar(10));

---

 

删除表

MariaDB [aa]> drop table cc;

cc表存在，则删除，不存在，则不执行

MariaDB [aa]> drop table if exists cc;

一次删除多张表，用逗号隔开

MariaDB [aa]> #drop table bb,cc;

---

 

查看表的结构

MariaDB [aa]> describe bb;

MariaDB [aa]> desc aa;

查看表的属性

MariaDB [aa]> show create table bb\G

修改表的属性，以修改编码为例

MariaDB [aa]> alter table bb character set gbk;

MariaDB [aa]> show create table bb\G

MariaDB [aa]> alter table bb charset=utf8;

MariaDB [aa]> show create table bb\G

---

 

修改系统的编码，

老版本的方法

#vi /etc/sysconfig/i18n

rh7

#vi /etc/locale.conf

# locale

---

 

添加列，删除列

MariaDB [aa]> alter table aa add phone char(10);

MariaDB [aa]> alter table aa drop phone;

调整插入顺序

MariaDB [aa]> alter table aa add phone char(10) first;

MariaDB [aa]> alter table aa add phone char(10) after name;

修改表的结构

 MariaDB [aa]> alter table aa modify name varchar(10);

MariaDB [aa]> alter table aa change city city varchar(11);

MariaDB [aa]> alter table aa change city addr varchar(10);

修改表名

MariaDB [aa]> rename table aa to cc;

 

插入数据

insert into 表名 (列1,列2,列3,……) values (值1,值2,值3,……)

MariaDB [aa]> insert into cc (id,name,phone,addr) values (2,‘bob‘,8888888,‘beijing‘);

MariaDB [aa]> insert into cc (id,name) values (3,‘mary‘);

MariaDB [aa]> insert into cc values (4,‘zhangsan‘,6666666,‘shanghai‘);

一次插入多个数据

MariaDB [aa]> insert into cc values
-> (5,‘lisi‘,1111111,‘hangzhou‘),
-> (5,‘wangwu‘,2222222,‘nanjing‘);

 

 

 

查看表中所有数据

select * from 表名

MariaDB [aa]> select * from cc;

查看具体的列

select 列1,列2,…… from 表名;

MariaDB [aa]> select id,name from cc;

MariaDB [aa]> select id,name,addr from cc;

查看具体的某一行

MariaDB [aa]> select * from cc where id=3;

MariaDB [aa]> select * from cc where id>=4;

 

MariaDB [aa]> select * from cc where id>4;

MariaDB [aa]> select * from cc where id<=2;

and

MariaDB [aa]> select * from cc where id>=2 and id<=4;

or

MariaDB [aa]> select * from cc where id<=2 or id>=4;

in

MariaDB [aa]> select * from cc where id in (2,3,4);

MariaDB [aa]> select * from cc where id=2 or id=3 or id=4;

between

 MariaDB [aa]> select * from cc where id between 2 and 4;

查看具体的某个数据

MariaDB [aa]> select * from cc where name=‘tom‘;

模糊匹配，使用通配符进行匹配的话，就不能使用= 而要使用like

MySQL里的两个通配符

_（下划线）表示一个字符

%（百分号）表示任意字符

MariaDB [aa]> select * from cc where name like ‘m%‘;

查看MySQL里面所有的参数，通常用like和通配符过滤查看

MariaDB [aa]> show variables;

MariaDB [aa]> show variables like ‘%data%‘;

MariaDB [aa]> show variables like ‘%engine%‘;

---

 

把cc表复制给aa表（aa表原来没有）

MariaDB [aa]> create table aa as select * from cc;

把aa表数据插入到bb表（bb表存在）

MariaDB [aa]> insert into bb select * from aa;

 

---

 

清空、删除表

MariaDB [aa]> delete from cc;

 MariaDB [aa]> truncate table dd;

---

 

删除表中某个数据

delete from 表 where xxxx;

MariaDB [aa]> delete from bb where id>=4;

修改表中某个数据

update 表名 set 列=新值 where xxxx;

MariaDB [aa]> update bb set name=‘tom11111‘;

 MariaDB [aa]> update bb set name=‘bob222‘ where id=1;

 

授权

MariaDB [aa]> grant all on *.* to root@‘%‘ identified by ‘redhat‘;

 

MariaDB

标签：name   防火   des   ide   emctl   update   重启   sysconfig   my.cnf