Objective
MYSQL/MARIADB is an open-source small-scale relational database management system, because of its small size, speed, low total cost of ownership, especially the open source, many small and medium-sized web sites in order to reduce the total cost of ownership of the site to choose MySQL/mariadb As a Web site database.
Infrastructure architecture
650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6C/64/wKiom1VIWQ2xL6fXAAH3D7xD8-I218.jpg "title=" MySQL architecture. jpg "alt=" wkiom1viwq2xl6fxaah3d7xd8-i218.jpg "/>
MySQL Core components
Connection pooling: Authentication, thread reuse, connection limit, memory check, cache
SQL interface: DDL, DML, basic abstraction of relational databases
Parser: Query transformation, object permission check
Optimizer: Access path, performance-related statistics
Caches and buffers: I/O performance enhancement tools related to the storage engine itself
Storage Engine: MyISAM, InnoDB (variant: XtraDB), Memory, Merge, Federated, CSV, Archive, Blackholl, Aria, Sphinxse, Tokudb
Internal structure
650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6C/61/wKioL1VIYYbRl_iyAAFgCZBTxAQ451.jpg "title=" Internal structure. jpg "alt=" wkiol1viyybrl_iyaafgczbtxaq451.jpg "/>
Program categories and command options
Server-side programs: Start and listen on sockets; Mysqld, Mysqld_safe, Mysqld_multi
Client program: can connect to server via MySQL protocol and make request; MySQL, Mysqlbinlog, mysqladmin, mysqldump, etc.
Utility Program: Run on the host where the server process resides, implement some management or maintenance operations, MYISAMCHK
Common options for client programs
-U,--user #指定登陆用户-H,--host #指定登陆主机-P,--password #指定登陆密码--protocol={tcp|socket|memory|pipe} #指定协议-P,--Port #指定端口, Default listening port: Tcp/3306--socket #指定本地连接的sock文件, equivalent to--protocol socket--compress #数据传输采用压缩格式-D,--database #指定连接后默认使用的数据库-H,-- HTML #指定产生html输出-x,--xml #指定产生xml输出--safe-updates #拒绝使用无where子句的update或delete命令
Client program Commands
Interactive mode: #客户端命令 mysql> help #列出所有命令 mysql> \? #相当于help mysql> \c #取消命令执行 mysql> \g #发送命令至服务器端 mysql> \g #发送命令至服务器端, vertical display results mysql> \q #退出 mysql> \! #运行shell命令 mysql> \s #显示服务器端状态信息 mysql> \. /path/to/somefile.sql #批量执行sql mysql> \u #将指定的库设为默认库 # server-side commands mysql> help KEYWORD get Keyword Assistance batch mode: MySQL </path/f Rom/somefile.sql
Management Tools Mysqladmin
format:mysqladmin [options] command [arg] [command [ARG]] #常用命令create db_name: Create database drop db_name: Delete database debug: Open debug Log and record in Error log status: show brief status information --sleep #间隔秒数 --count #显示的次数extend-status: Displays all server state variables for mysqld flush-privileges: Refresh authorization table, Equivalent to the reload command flush-hosts: clear the DNS cache and the rejected client list cache flush-logs: Scroll logs, binary and relay logs flush-status: Reset each state variable flush-tables: close all currently open Table file handles;flush-treads: reset thread cache;password: Set Password ping: Testing whether the server is online processlist: displays all threads on the current server refresh: equivalent to performing flush-hosts and flush-logsshutdown: shutting down the server process ;start-slave, stop-slave: start, close;variables: display server variables from server thread
Functional Properties Supplement
command-line editing features
CTRL + A: Quickly move the cursor to the beginning of ctrl+e: Quickly move the cursor to the end of the line ctrl+w: Delete the word before the cursor ctrl+u: Delete all the content at the beginning of the cursor ctrl+y: Paste content deleted using Ctrl+w or Ctrl+u
Prompt
mysql> #等待输入命令 #续行 ' > #还需补全后半部单引号 > #还需补全后半部双引号 ' > #还需补全后半部反引号/*> #注释, do not execute, you need to end the comment at */
components of the SQL language
DDL: Data Definition language
DCL: Data Control language, such as authorization
DML: Data Manipulation language
Integrity Definition Language: DDL functional constraints (primary key, foreign key, unique key, condition, non-null, transaction)
View definition: Virtual table, stored SELECT statement
Transaction control
Data dictionary
Data dictionary: Systems Catalog (System catalog)
The metadata on the database server is saved
Meta data
The name of each field in each relationship the name of the relationship and the length of each field are constrained by the name of the view on each relationship and the definition of the view authorizes the user name of the user's authorization and account information statistics class data the number of fields in each relationship, the number of rows per relationship, and the storage method for each relationship;
The database that holds the metadata
Infomation_schemamysqlperformance_schema
Data types and Property modifiers
The function of the data type
① stored value types
Maximum storage space occupied by ②
③ fixed length, variable length
How ④ are indexed and sorted
Whether the ⑤ can be indexed
Data type
Character type
CHAR, Varcarh, Tinytext, TEXT, Mediumtext, Longtextbinary, VARBINARY, Tinyblob, BLOB, Mediumblog, Longblob modifier: null: Allowed to empty not NULL: null default ' String ' is not permitted: defaults, not for Textcharacter set ' set ': Set character set SHOW CHARACTER set; View available character sets collation ' collation ': Set collation of the character set sort show collation; view available collation default: Do not use the BLOB type Word have wildcard:%: matches any character of any length _: matches any single character;
Integral type
TINYINT, SMALLINT, Mediumint, INT, binint modifier: UNSIGNED: Unsigned nullnot nulldefaultauto_increment: autogrow # Special requirements: Non-empty, and must be a primary key or unique key
Floating point Type
FLOAT, double modifier: not nullnulldefaultunsigned
Boolean type
No dedicated Boolean, it is an alias of tinyint (1)
Date-Time Type
DATE, Time, DATETIME, TIMESTAMP, Year (2), year (4) modifier: Nullnot nulldefault VALUE
Built-in type
Enum: enumeration, which indicates that only one of the enum (' string1 ', ' string2 ') set can be selected from the given option: set, which means that the given element can be combined into a string set (' A ', ' B ', ' C ') modifier: Nullnot NUL Ldefault "
SQL mode and server variables
SQL mode: Used to qualify Mysqld's working characteristics
Traditional: Traditional mode
Strict_trans_tables: Use strict mode for tables that support transactions
Strict_all_tables: Use strict mode for all tables
Types of server variables
Global: Valid for all sessions
All sessions are inherited from the global when they are established, but each session maintains its own session-level variables independently of the completion of the inheritance
Modifying a global server variable only takes effect for subsequent sessions established
Requires administrative privileges
Session: Valid only for the current session
Changes take effect immediately
Do not require administrative permissions
How to Modify
Dynamic modification: Session level, immediate effect, global level, newly established session valid, restart service invalid
Static modification: To modify the configuration file, or to modify the value of the option passed to mysqld, which is valid after a restart
Note: Not all server variables support dynamic modification
View server Variables
Mysql> SHOW {global| SESSION} VARIABLES [like clause];mysql> SELECT @@{global| SESSION}. Variable_name;mysql> SELECT * from INFORMATION_SCHEMA. Global_variables WHERE variable_name= ';mysql> SELECT * from INFORMATION_SCHEMA. Session_variables WHERE variable_name= ';
modifying server variables
Dynamically modifying the value of a variable:mysql> SET {global| SESSION} variable_name= ' VALUE ' lets you set a permanent valid way: [Mysqld]sql_mode = ' strict_all_tables '
Statement Write case Description
①sql keyword and function name non-area character case
② the names of databases, tables, indexes, and views are case sensitive depending on the low-level OS and FS
③ stored procedures, stored functions, and event schedulers are case-insensitive, but trigger-sensitive
④ table aliases are case insensitive
⑤ field character data, type binary, blog, varbinary case-sensitive, other non-differentiated
DDL operations
Database operations
Creating a Database create {databases | SCHEMA} [IF not EXISTS] db_name Delete database drop {db | SCHEMA} [IF EXISTS] db_name modify database alter {db | SCHEMA} [IF EXISTS] Db_name
Table Operations
CREATE TABLE Create table [if not exists] tb_name (Col1_def,col2_def,primary key (Col_name, ...),unique (col1,...),index (col1,...)) [table_option]table_option:engine [=] engine_namecomment [=] ' string ' ROW_FORMAT [=] {default| dynamic| fixed| compressed| Redundant| compact}tablespace tablespace_name [storage {disk| memory| default}]create [temporary] table [if not exists] tbl_name # Copy table Data [(Create_definition,...)] [table_options] select_statementCREATE [TEMPORARY] table [if not exists] tbl_name #复制表结构 { like old_tbl_name | (Like old_tbl_name) } Delete Table Drop [temporary] table [if &NBSP;EXISTS]&NBSP;&NBSP;TBL_NAME&NBSP;[,&NBSP;TBL_NAME]&NBSP, ..... [restrict | cascade] #cascade会将有依赖关联的表一并删In addition to modifying the table alter [online | offline] [ignore] table tbl_name [ ALTER_SPECIFICATION&NBSP;[,&NBSP;ALTER_SPECIFICATION]&NBSP, ...] Alter_specification: #插入新字段ADD [column] col_name column_definition [first | after col_name ] #删除字段DROP [column] col_name# Modify Field Properties Alter [column] col_name {set DEFAULT literal | DROP DEFAULT} #修改字段名CHANGE [column] old_col_name new_col_name column_definition [first| After col_name] #修改字段类型及属性等MODIFY [column] col_name column_definition [first | after col_name] Modify table name alter table tb_name rename to new_tb_name; rename table old_name to new_name; #指定排序字段ORDER by col_name [, col_name ] #转换字符集及排序规则CONVERT to character set charset_name [collate collation_ Name
The end
MYSQL/MARIADB's knowledge comes first here, the follow-up article will continue to explain the relevant knowledge of MYSQL/MARIADB, interested can continue to pay attention. The above is only for individual learning to organize, if there are mistakes, big God do not spray ~ ~ ~
This article is from the "North Scholar" blog, please make sure to keep this source http://scholar.blog.51cto.com/9985645/1642286
MYSQL/MARIADB basic knowledge and DDL operation detailed