MYSQL/MARIADB basic knowledge and DDL operation detailed

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  EXISTS]  TBL_NAME [, TBL_NAME]&NBSP, ..... [restrict | cascade]   #cascade会将有依赖关联的表一并删In addition to modifying the table alter [online | offline] [ignore] table tbl_name    [ ALTER_SPECIFICATION [, 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