1、安装mariadb
sudo pacman -S mariadb
2、启动mariadb的服务
systemctl start mariadb
3、应用root连接到服务器
mysql -u sqlxxl -p
Enter password:输入自己的密码
4、第零屏说明：
1 Welcome to the MariaDB monitor. Commands end with ; or g. 2 Your MariaDB connection id is 3 3 Server version: 10.5.8-MariaDB Arch Linux 4 5 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. 6 7 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. 8 9 MariaDB [(none)]>
1）：第1行是欢迎信息，命令行以“;”结尾或者以“”g结尾
2）：第2行表明本次连接的标识号是3，若出现问题时才需要
3）：第3行表明Mariadb的版本号时：10.5.8
4）：第5行展示了版权信息
5）：第7行展示如何获取帮助信息，如何清除输出信息
5、帮助信息：
MariaDB [(none)]> help General information about MariaDB can be found at http://mariadb.org List of all client commands: Note that all text commands must be first on line and end with ';' ? (?) Synonym for `help'. clear (c) Clear the current input statement. connect ( ) Reconnect to the server. Optional arguments are db and host. delimiter (d) Set statement delimiter. edit (e) Edit command with $EDITOR. ego (G) Send command to MariaDB server, display result vertically. exit (q) Exit mysql. Same as quit. go (g) Send command to MariaDB server. help (h) Display this help. nopager ( ) Disable pager, print to stdout. notee ( ) Don't write into outfile. pager (P) Set PAGER [to_pager]. Print the query results via PAGER. print (p) Print current command. prompt (R) Change your mysql prompt. quit (q) Quit mysql. rehash (#) Rebuild completion hash. source (.) Execute an SQL script file. Takes a file name as an argument. status (s) Get status information from the server. system (!) Execute a system shell command. tee (T) Set outfile [to_outfile]. Append everything into given outfile. use (u) Use another database. Takes database name as argument. charset (C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (W) Show warnings after every statement. nowarning (w) Don't show warnings after every statement. For server side help, type 'help contents' MariaDB [(none)]> help contents You asked for help about help category: "Contents" For more information, type 'help <item>', where <item> is one of the following categories: Account Management Administration Compound Statements Data Definition Data Manipulation Data Types Functions Functions and Modifiers for Use with GROUP BY Geographic Features Help Metadata Language Structure Plugins Procedures Sequences Table Maintenance Transactions User-Defined Functions Utility MariaDB [(none)]> help Data Manipulation You asked for help about help category: "Data Manipulation" For more information, type 'help <item>', where <item> is one of the following topics: CALL Concurrent Inserts DELETE DO DUAL EXCEPT FOR UPDATE GROUP BY HANDLER Commands HIGH_PRIORITY and LOW_PRIORITY IGNORE INSERT INSERT - Default & Duplicate Values INSERT DELAYED INSERT IGNORE INSERT ON DUPLICATE KEY UPDATE INSERT SELECT INTERSECT JOIN Syntax LIMIT LOAD DATA INFILE LOAD XML LOCK IN SHARE MODE Non-Recursive Common Table Expressions Overview ORDER BY PROCEDURE REPLACE Recursive Common Table Expressions Overview SELECT SELECT INTO DUMPFILE SELECT INTO OUTFILE SELECT WITH ROLLUP UNION UPDATE WITH MariaDB [(none)]> help SHOW DATABASES Name: 'SHOW DATABASES' Description: Syntax ------ SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr] Description ----------- SHOW DATABASES lists the databases on the MariaDB server host. SHOW SCHEMAS is a synonym for SHOW DATABASES. The LIKE clause, if present on its own, indicates which database names to match. The WHERE and LIKE clauses can be given to select rows using more general conditions, as discussed in Extended SHOW. You see only those databases for which you have some kind of privilege, unless you have the global SHOW DATABASES privilege. You can also get this list using the mysqlshow command. If the server was started with the --skip-show-database option, you cannot use this statement at all unless you have the SHOW DATABASES privilege. Example SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ SHOW DATABASES LIKE 'm%'; +---------------+ | Database (m%) | +---------------+ | mysql | +---------------+ URL: https://mariadb.com/kb/en/library/show-databases/
6、数据库基本操作：
几个约定：
1)、mysql命令是不区分大小写的
2)、mysql中数据库、表名、属性名都是需要区分大小写
常见操作：
1)、查看系统中数据库
2)、创建数据库
3)、删除数据库
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | student_info | +--------------------+ 4 rows in set (0.040 sec) MariaDB [(none)]> create database sample; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sample | | student_info | +--------------------+ 5 rows in set (0.001 sec) MariaDB [(none)]> drop database sample; Query OK, 0 rows affected (0.036 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | student_info | +--------------------+ 4 rows in set (0.001 sec)
7、数据库存储引擎,查看数据库引擎,数据库引擎的特点
MariaDB [(none)]> help show engines; Name: 'SHOW ENGINES' Description: Syntax ------ SHOW [STORAGE] ENGINES Description ----------- SHOW ENGINES displays status information about the server's storage engines. This is particularly useful for checking whether a storage engine is supported, or to see what the default engine is. SHOW TABLE TYPES is a deprecated synonym. The information_schema.ENGINES table provides the same information. Since storage engines are plugins, different information about them is also shown in the information_schema.PLUGINS table and by the SHOW PLUGINS statement. Note that both MySQL's InnoDB and Percona's XtraDB replacement are labeled as InnoDB. However, if XtraDB is in use, it will be specified in the COMMENT field. See XtraDB and InnoDB. The same applies to FederatedX. The output consists of the following columns: Engine indicates the engine's name. Support indicates whether the engine is installed, and whether it is the default engine for the current session. Comment is a brief description. Transactions, XA and Savepoints indicate whether transactions, XA transactions and transaction savepoints are supported by the engine. Examples -------- SHOW ENGINESG *************************** 1. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES *************************** 2. row *************************** Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *************************** 3. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO *************************** 4. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO *************************** 5. row *************************** Engine: FEDERATED Support: YES Comment: FederatedX pluggable storage engine Transactions: YES XA: NO Savepoints: YES *************************** 6. row *************************** Engine: MRG_MyISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 7. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO Savepoints: NO *************************** 8. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO *************************** 9. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO *************************** 10. row *************************** Engine: Aria Support: YES Comment: Crash-safe tables with MyISAM heritage Transactions: NO XA: NO Savepoints: NO 10 rows in set (0.00 sec)
MyISAM：需要临时存放数据；数据量不大，数据安全不高时；
MEMORY：需要很高的处理效率，表主要用来插入和查询记录时；
InnoDB： 需要提交、回滚和崩溃恢复能力的事务安全；需要并发控制时