mysql及mariadb常用命令一览

yum install mariadb-server mariadb -y

 yum remove mariadb-server mariadb -y

systemctl start mariadb

systemctl stop mariadb

systemctl restart mariadb

systemctl enable mariadb

[root@VM_0_7_centos ~]# mysql_secure_installation 
In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
为了登录MariaDB以保护它，我们需要当前的
root用户的密码。如果你刚刚安装了MariaDB，那么
你还没有设置root密码，密码是空白的，
所以你应该在这里按Enter键。




Enter current password for root (enter for none):  enter
输入root的当前密码（enter for none）：enter




Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
设置root密码可确保任何人都无法登录MariaDB
没有适当授权的root用户。




Set root password? [Y/n] y
设置root密码？ [是/否] y




New password:  Lzk0.001
Re-enter new password:  Lzk0.001
新密码：Lzk0.001
重新输入新密码：Lzk0.001




Password updated successfully!
Reloading privilege tables..
 ... Success!
密码更新成功！
重新加载权限表..
 ......成功！




By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
默认情况下，MariaDB安装具有匿名用户，允许任何人
无需创建用户帐户即可登录MariaDB
他们。这仅用于测试和进行安装
走得更顺一点你应该在进入之前删除它们
生产环境。




Remove anonymous users? [Y/n]  Y
删除匿名用户？ [是/否] Y.




 ... Success!
 ......成功！




Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
通常，只允许root用户从'localhost'连接。这个
确保有人无法猜测来自网络的root密码。




Disallow root login remotely? [Y/n]  Y
 ... Success!
禁止远程登录？ [是/否] Y.
 ......成功！




By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
默认情况下，MariaDB附带一个名为“test”的数据库，任何人都可以
访问。这也仅用于测试，应该删除
在进入生产环境之前。




Remove test database and access to it? [Y/n]  Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
删除测试数据库并访问它？ [是/否] Y.
  - 删除测试数据库......
 ......成功！
  - 删除测试数据库的权限...
 ......成功！




Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
重新加载权限表将确保到目前为止所做的所有更改
将立即生效。




Reload privilege tables now? [Y/n]  Y
 ... Success!
现在重新加载权限表？ [是/否] Y.
 ......成功！




Cleaning up...
打扫干净...




All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.
全部完成！如果您已完成上述所有步骤，请使用MariaDB
安装现在应该是安全的。




Thanks for using MariaDB!
感谢您使用MariaDB！

[root@VM_0_7_centos ~]# mysql -u root -p
Enter password: 之前设置的密码
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.60-MariaDB MariaDB Server




Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.




Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye

MariaDB [(none)]> create DATABASE websql;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| websql             |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use websql;
Database changed
MariaDB [websql]> 

MariaDB [(none)]> drop database websql;
Query OK, 0 rows affected (0.00 sec)

CREATE TABLE table_name (column_name column_type);

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

DROP TABLE table_name ;

INSERT INTO table_name ( field1, field2,...fieldN )VALUES( value1, value2,...valueN );

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

DELETE FROM table_name [WHERE Clause];

SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N] [OFFSET M];

  mysqldump --database DBname -uroot -p > DBname.sql 

  mysql --database websql -uroot -p < websql.sql