MySQL Core Knowledge essentials

1 Overall knowledge introduction : MySQL basic operation and use, MySQL optimization (index, sub-table, etc.), MySQL deployment (read/write separation, load balancing, etc.)

Database Basic Introduction : Database concept, common database, Web application three major software division, PHP Dynamic Language features (processing data), data storage (script runtime and execution completed), database system

relational database : concept (relational + two-dimensional table), teaching system examples, common relational database, relational database features (all data same structure, no data also allocated storage space)

non-relational database : Key-value pairs, common non-relational databases, non-relational database features (memory run, HDD sync)

Typical concepts of relational databases : Row rows (Record Records), Column col (Fields field), SQL (concept, DDL,DML (DML+DQL), DCL)

M Ysql Basic Introduction : MySQL software introduction (c/S structure), MySQL operation steps, client work, MySQL installed separately, MySQL directory and file introduction, MySQL server-side management (services and Mysqld.exe), Client Connection server (connection authentication, execute SQL, exit) MySQL service management (NET, service management, Mysqld.exe–defaults-file=my.ini path)

M Ysql data Object : MySQL server à database à data table à field

Basic SQL Operations : library operations (create[if not exists][charset,collation], library folder Introduction, database naming rules, special name processing, show[like pattern], wildcard, View library creation statements; drop;alter), Table Operations (table and field dependencies; create [engine,charset], table file parsing, table-to-Library relationships, reserved word conflicts, creating [like];show[like pattern] from existing tables, View table fields [desc/describe/show columns from tb_name], view the tables creation statement; Drop;alter [Operation add/drop/modify/change][Location First After],rename To rename, data manipulation (insert into Omit field and select field, select From[where condition],= comparison; delete from[where];update set[where]; Delete and update irreversible)

Character Set : Quick fix problem (set names), character set, MySQL character set (data storage, data transfer), data storage character set (server side, library, table, field, plot), Character set priority (field à table à library à server side), Data transfer process Character set (plot (client clientà connection layer connectionà server à client result), Character_set variable, set names analysis)

Proofing Sets: Character comparison (ASCII comparison), proofing set work (view show collation), proofing set settings (collate= proofing set in Table options), proofing set naming conventions (_bin binary, _ci[case insensitive], _cs[case sensitive]), UTF8&GBK (UTF8 does not support Chinese comparison, GBK support Pinyin), proofing set late modification invalid

Storage Engine : Overview MySQL storage engine, main engine [InnoDB and MyISAM], future development of engine, engine difference (myisam[query and new, full-text index],innodb[update and delete, transaction, 5.5 Full-text index], file difference)

garbled Problem Solving : Graphing (browser-php--mysql)

field (column) type : MySQL data type (numeric, String, datetime), numeric type (integer [Tinyint,smallint,mediumint,int,bigint], display width, 0 fill, unsigned; float type [ Float,double], Syntax float (m,d), floating point support scientific notation, fixed-point number [Decimal (M,D)], point-to-point storage method Difference), String type (char (l); varchar (l); text ; char vs. varchar comparison table, MySQL record total length limit, text unrestricted (10 bytes); Enumeration enum[value storage, 2 bytes, single selection, easy to insert [numeric or string], starting from 1]; set set[storage mode binary, multiple selection, 8 bytes]; binary text), Date and Time type (datetime[format, 8 byte storage, time span 9999];date[];time[time interval];year[1 byte storage, span 1900-2155 or 70-69];timestamp[standard integer storage, Performance consistent with DateTime])

Column properties (constraints): null (concept [different from PHP], syntax null/not NULL), default (defaults, usage, using the default keyword when inserting data), key (primary key [primary key, unique identifier, design table plot, Primary key advantage, primary key syntax includes build table and modify, delete primary key]; unique key [Unique key, advantage, added include build table and modify table structure, delete unique key flat [only by deleting index]]), auto Grow (auto_increment, common primary key ID), Comments (Comment,sql General Comment syntax [--Space, #,/* block comment */])

Relationship: Relationship classification (1:n[the relationship between students and class, design];m:n[teacher-class correspondence, design];1:1[student-student relationship])

foreign KEY (constraint): Foreign key (foreign key, student and class relationship, foreign key function, foreign key definition [foreign key (logical), FOREIGN KEY constraint (syntactically)], add foreign key [CREATE TABLE and modify table structure], constrain function, delete constraint [constraint name], increase constraint name [ Constraint], constraint corresponding to the index), FOREIGN KEY constraint (main table and slave table, constraint operation [Strict control restrict, empty set NULL, CASCADE CASCADE], constraint range [delete and update], foreign key support [InnoDB, type strictly consistent, The empty action field must be empty])

paradigm : Paradigm definition (normal Format), paradigm level (1 to 6), database paradigm (meet to third level), 1NF (atomicity), 2NF (partial dependency, composite primary key), 3NF (transitive dependency), paradigm somewhat (generic, reduce data redundancy), Inverse normalization (setting the necessary data redundancy, efficiency versus disk space)

Advanced Data Manipulation : Insert data (select INSERT INTO Tb_name SELECT clause, primary key conflict [insert INTO ... on duplicate key update], replace insert [replace into]), Delete data (with Truncate],delete and limit, clear table [difference from truncate), modify data (with the by-date and limit), query data (select options [All and distinct], field list [ expressions, field aliases],from clauses [multiple tables, table aliases, virtual table dual], five clauses [Where,group By,having,order by,limit]), WHERE clauses (operation flow, MySQL Boolean type and value, expression [relational operator =, In and not in,between and and not between and,is null and are NOT NULL, logical operators, like operators, wildcard operators, operator Precedence]), GROUP BY clauses (grouping statistics, aggregate functions [count ( For non-null field statistics), Max (), Min (), SUM (), AVG ()],grou_concat (field) [Group private field Connection],group by[sort Asc|desc], multi-field grouping, backtracking statistics [with rollup], HAVING clause (similar to where function, you can use field aliases, you can use aggregate functions, unlike where operation data), ORDER BY clause (sort, multi-field sort), limit clause (restrict number of records, specify index location, paging)

Federated Queries : Union queries (Union, Union of results, conditional simplification, union options [All and distinct], number of fields consistent type does not matter [cause, var_dump see results],union the SELECT clause is sorted separately [ Clauses need parentheses, need to match limit])

Jobs: Implementing a user creating a database on the browser side, creating a data table

Connection Query : concept, Connection classification (internal connection [inner join [on], participating connection data must exist, where alternative on, default connection mode, connection principle, where and on difference, using deduplication], outer connection [Left/right Join on, the data participating in the connection does not correspond to the left left table data, right outside the right table data],full all outside MySQL support, on and using use], cross-connect [unconditional mode within the connection], natural connection [natural [left/right] Join , rather than using with other connections, cannot use on])

subquery: Sub-query concept, function, sub-query classification (position classification [where,from,exists], return form classification [scalar subquery [Single value, for comparison], sub-query [with in and not In,any,all and some], row subquery [ Build rows with parentheses syntax], table subquery [typical from subquery]])

views : Concepts (Virtual tables), view functions (querying data, providing interfaces, hiding real data), view management (creating a [CREATE VIEW as SELECT clause], deleting a view [drop view], modifying the view [ALTER View v_name AS Select clause], view view [show Create View v_name, view field, view create statement], view algorithm [concept, merge algorithm merge, temp table temptable, undefined undefined, view specify algorithm [create Algorithm=temptable view])

Data Backup Restore : Export (SELECT INTO outfile ' path ', export file cannot have duplicate name, field delimiter [fields terminated by,enclosed by,escaped by], record delimiter [lines Terminated by/starting by], export file encoding), import (Load Data infile ' path ' into table Tb_name option)

Data backup : MyISAM backup, mysqldump (mysqldump client, syntax, table backup [Library table/-B library], database backup), restore (Source,mysql Library Name table name < SQL file)

Transaction : Transaction purpose, transaction operation (open [Start transaction], commit [commit], rollback [rollback], rollback point [savepoint name,rollback to name]), Transaction fundamentals (MySQL executes SQL mode [Execute + commit], default Autocommit autocommit), transaction security log (save Transaction execution intermediate result ib_logfile,innodb support transaction), transaction features acid (atomicity [Atomicity, Transaction is whole], consistency [consistency, data integrity consistency before and after transaction], persistence [durability Transaction once committed, data change permanent], isolation [isolation, multiuser concurrency, mutual isolation])

triggers : Concepts, Syntax (CREATE TRIGGER T_name event type on table for each row], event timing [Before,after], event type [insert,delete,update], MySQL does not support the creation of multiple departure programs on the same event; Delete [Drop trigger t_name]; get trigger related records [old,new], branching structure [if condition then code end If], multi-statement [Begin,end], statement Terminator)

variables : System variables, custom variables (set, using variables, select INTO, variable assignment [: =]), variable validity, variable scope

SQL Functions : Built-in functions (SUBSTRING,CHAR_LENGTH,LENGTH,INSTR,LPAD,INSERT,STRCMP), custom functions (concepts, function features [function name, parameter list, function body, return value], syntax [ Create function f_name (parameter list) returns return value type], call function [select F_name (parameter)], branch structure, loop structure [tag], internal definition variable [global variable @var, local variable declare var] , interrupt (leave,iterate))

stored procedures : concepts, differences from functions, syntax (CREATE PROCEDURE Pr_name (procedure parameter in Out inout), procedure parameter type, call to procedure)

M ysql User Management : root password Forgotten (reload, MySQL Admin user's table user), reset root password (with parameter restart Service [--skip-grant-tables], login without password, modify user table root password [update Set], encryption algorithm [Sha1,md5,password (), restart]), normal user management (create user [created users user_name[identified by ' password '], user name form [[email protected]], Delete users [drop user [email protected]], set user rights (add [Grant permission list on table name to user], permissions list, table name representation method, special permissions [All privileges], flush cache [Flush Privileges,flush tables], remove user rights [REVOKE permissions list on table name from user])

index : concept, MySQL index type [primary key index, unique index, full-text index, normal index, composite index]

php operation MySQL: PHP transformation Client, open the extension, typical extension (mysql,mysqli,pdo-mysql), Libmysql.dll (Connection service provider), check whether MySQL is loaded (PHPinfo), PHP operation MySQL (connection authentication mysql_connect (), prepare and send SQL statement mysql_query, Process execution Results mysql_ Fetch_assoc (), releasing the resource [result set], closing connection mysql_close ()), mysql_query (Mysql_query return value [Boolean, result set], statement with return value [Select,show,desc], Statements that do not return a value [Insert,update,delete, DDL]), other common functions (Mysql_errno (), mysql_error (), Common handling, mysql_fetch_array (), Mysql_fetch_ Row () difference, Mysql_data_seek (), mysql_affected_rows,mysql_insert_id)

Function: Registration successful jump, paging (one-time, limit)

MySQL Core Knowledge essentials