首页 > 代码库 > mariadb
mariadb
MariaDB数据库管理系统是MySQL的一个分支
1:数据库的安装启动
yum search mariadb yum install -y mariadb-server.x86_64 systemctl start mariadb #开启 systemctl enable mariadb
2：数据库的初始化
3：数据库的使用
《1》查询
MariaDB [(none)]> show databases; ##查看数据库中有的库 +--------------------+ | Database | +--------------------+ | information_schema | | linux | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec)
MariaDB [(none)]> use linux; ##切换到linux库中 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed MariaDB [linux]> show tables; ##查看linux库中含有的表格 +-----------------+ | Tables_in_linux | +-----------------+ | information | +-----------------+ 1 row in set (0.00 sec)
MariaDB [linux]> select * from information; ##查看information表格的所有信息 +----------+----------+------+ | username | password | age | +----------+----------+------+ | zpy | 123 | 19 | +----------+----------+------+ 1 row in set (0.00 sec)
MariaDB [linux]> desc message; ##查看表格结构 +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | username | varchar(50) | NO | | NULL | | | sex | varchar(10) | YES | | NULL | | | class | varchar(50) | YES | | NULL | | | age | varchar(4) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
《2》新建
MariaDB [(none)]> create database haha; ##新建haha库，从show databases;可以看到 Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | haha | | linux | | mysql | | performance_schema | +--------------------+ 5 rows in set (0.00 sec)
MariaDB [(none)]> use haha; Database changed MariaDB [haha]> create table xihao ( ##新建表格名为xihao -> username varchar(50), ##新建的表格表头包含的信息有username 并且字节数在50个范围内 -> favoritecolor varchar(50), -> favoritething varchar(30) ); Query OK, 0 rows affected (0.67 sec)
《3》 添加表格信息
MariaDB [haha]> insert into xihao values (‘qq‘,‘yellow‘,‘book‘);##给xihao表中添加信息 Query OK, 1 row affected (0.28 sec) MariaDB [haha]> select * from xihao; 查看添加的信息 +----------+---------------+---------------+ | username | favoritecolor | favoritething | +----------+---------------+---------------+ | qq | yellow | book | +----------+---------------+---------------+ 1 row in set (0.00 sec)
《4》 修改
MariaDB [haha]> alter table xihao rename message; ##修改表格名称 Query OK, 0 rows affected (0.30 sec) MariaDB [haha]> alter table message add favoritefood varchar(50); ##给message表格中添加favoratefood类 Query OK, 1 row affected (1.63 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [haha]> select * from message; +----------+---------------+---------------+--------------+ | username | favoritecolor | favoritething | favoritefood | +----------+---------------+---------------+--------------+ | qq | yellow | book | NULL | +----------+---------------+---------------+--------------+ 1 row in set (0.00 sec)
MariaDB [haha]> alter table message add sex varchar(10) after username; ##指定位置添加 Query OK, 1 row affected (0.39 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [haha]> select * from message; +----------+------+---------------+---------------+--------------+ | username | sex | favoritecolor | favoritething | favoritefood | +----------+------+---------------+---------------+--------------+ | qq | NULL | yellow | book | NULL | +----------+------+---------------+---------------+--------------+ 1 row in set (0.00 sec)
MariaDB [haha]> alter table message drop favoritefood; ##删除表格中favoritefood这一行 Query OK, 1 row affected (0.20 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [haha]> select * from message; +----------+------+---------------+---------------+ | username | sex | favoritecolor | favoritething | +----------+------+---------------+---------------+ | qq | NULL | yellow | book | +----------+------+---------------+---------------+ 1 row in set (0.00 sec)
MariaDB [haha]> update message set sex=‘man‘; ##更新表格中所有sex类信息为man Query OK, 1 row affected (1.29 sec) Rows matched: 2 Changed: 1 Warnings: 0 MariaDB [haha]> select * from message; +----------+------+---------------+---------------+ | username | sex | favoritecolor | favoritething | +----------+------+---------------+---------------+ | qq | man | yellow | book | | aaa | man | ss | dd | +----------+------+---------------+---------------+ 2 rows in set (0.00 sec)
MariaDB [haha]> update message set sex=‘woman‘ where username=‘qq‘; ##指定用户的信息更改 Query OK, 1 row affected (0.28 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [haha]> select * from message; +----------+-------+---------------+---------------+ | username | sex | favoritecolor | favoritething | +----------+-------+---------------+---------------+ | qq | woman | yellow | book | | aaa | man | ss | dd | +----------+-------+---------------+---------------+ 2 rows in set (0.00 sec)
《5》删除
drop database haha
drop table message
delete table message where username=‘qq‘ and sex=‘woman‘
《6》数据库备份
mysqldump -uroot -phahaha linux > /mnt/linux.aql ##将linux库备份到/mnt/linux.aql中 MariaDB [(none)]> drop database linux; ##删掉linuxk库 Query OK, 1 row affected (0.29 sec) mysql -uroot -phahaha -e ‘create database linux;‘ ##创建新的linux库 mysql -uroot -phahaha linux < /mnt/linux.aql ##将备份文件倒入新建的linux库
《7》当忘记root用户密码时；
systemctl stop mariadb ##停掉mariadb mysqld_safe --skip-grant-tables & ##mysql进入安全模式 [root@localhost ~]# mysql ##进入mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 1 Server version: 5.5.35-MariaDB MariaDB Server Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> use mysql #切换到mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mysql]> select * from user; ##查看user表 +-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+ | Host | User | Password | Select_priv | Insert_priv |###注：此处的 | Host | User | Password 都是表头Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | +-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+ | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | 注：此处的 | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9都是上面表头对应的信息 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | | | 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | | | ::1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | | +-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+ 3 rows in set (0.00 sec) MariaDB [mysql]> update user set Password=password(‘hahaha‘) where User=‘root‘; ##更新密码，使得密码为hahaha并且是加密字符 Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 MariaDB [mysql]> Bye [root@localhost ~]# ps ax |grep mysql ##过滤mysql的进程 6156 pts/2 S 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables 6299 pts/2 Sl 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock 6340 pts/2 R+ 0:00 grep --color=auto mysql [root@localhost ~]# kill -9 6156 6299 ##杀掉进程 [root@localhost ~]# ps ax |grep mysql 6342 pts/2 R+ 0:00 grep --color=auto mysql [1]+ Killed mysqld_safe --skip-grant-tables [root@localhost ~]# systemctl start mariadb ##启动正常登陆
《8》 用户授权
MariaDB [(none)]> create user zpy@localhost identified by ‘123‘; ##新建用户 MariaDB [(none)]> show grants for zpy@localhost; ##查看用户信息； MariaDB [(none)]> grant select on linux.* to zpy@localhost; ##给zpy用户可以查看linux库的权限。 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> grant delete on linux.* to zpy@localhost; #给zpy用户删除表格内容权限 MariaDB [(none)]> grant drop on linux.* to zpy@localhost; ##给用户zpydrop权限 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush privileges; ##重载授权表 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> revoke delete on linux.* from zpy@localhost ##撤销用户权限 MariaDB [(none)]> drop user zpy@localhost; ##删除用户zpy Query OK, 0 rows affected (0.00 sec)
本文出自 “12462896” 博客，请务必保留此出处http://12472896.blog.51cto.com/12462896/1955743
mariadb
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。