MySQL Total review

After learning the summary of MySQL, if in doubt, welcome, common progress! 1, Introduction: DDL: Data Definition Language (LANGUAGE,DDL) basic operation: Create drop  Alter DML　　　　: Data manipulation LANGUAGE,DML Basic operations: INSERT,   Update, delete, select. DCL: Data Control LANGUAGE,DCL basic operations: Commit, rollback (things), Grant, revoke (User role) 2, operations on the database: (1) Add Database: Create Databa SE database name; (2) Delete database: drop databases database name; 3, operation on table: (1) CREATE TABLE: Name of Creation Table table, (2) Delete table (includes data and table structure): drop table table name; (3) Delete tables (Data preserving table structure only): Delete from table name where condition, (4) Add data: INSERT into table name (column name 1, column Name 2, ...). VALUES (column value 1, column value 2, ...).　　　　）; (5) Modify data: Update table name set Modify Column 1 = Modify value 1, modify column 2 = Modify value 2, .... where condition; (6) Find data: SELECT */lookup column from table name where condition; note: where after format: wher 　　　　e condition [Group BY column name [having condition]] [ORDER BY column name [Asc/desc]]; 4, constraint: (1) PRIMARY KEY constraint: Set Primary KEY (2) FOREIGN KEY constraint: Set foreign KEY (3) NULL constraint: null NOT NULL for set allow (4) Unique constraint: Set unique, does not allow duplicate (5) Default constraint: Set default value  5, transaction MySQL transaction is mainly used to deal with large operations, high complexity of data. For example: When the bank transfers, the deduction of money and transfer of money must be executed, this is a business.　　　　When the transaction does not complete the error is not allowed, if the error can be rolled back operations. Operation: (1) operation in MySQL: Set autocommit=0; (MySQLThe default autocommit=1, in this case, each execution of a SQL statement, the database is a single operation, set to 0, only when committed to the database data operation) begin; SQL statement 1; SQL statement 2; rollback; (rollback operation, such as an error when a transaction is in progress, rollback to begin state) commit; (Commit operation, if commit, rollback operation cannot　　　　　　　　) (2) Operate in Java: try{QL statement 1; SQL statement 2; ... commit;　　　　} catch (Exception e) {rollback;//If an error is rolled back} 6, view (Learn) (1) CREATE VIEW: Creates a view name as SQL statement; (2) Modify the view: Alter view already has the view name as SQL statement; (3) Delete view: Drop views already have a view name;  7, index Purpose: Index is to improve query efficiency, can be analogous dictionary (1) general index (b + Tree index): Crea TE index index name on the table where the index is located (add indexed column 1, add indexed column 2, ...). ) (2) Unique index: Create unique index name on the table where the index is located (add indexed column 1, add indexed column 2, ...). You cannot add the same data when the primary key is returned by default when you add a unique index (3) Full-text index: Create fulltext index name on the table where index is located (add indexed column 1, add indexed column 2, ...).　　　　When querying, the word processing will be processed to improve the efficiency (4) Delete index: Drop forcedure index name on the table where the index is located;  8, stored procedure stored procedure Simple understanding is the Java method.　　　　　　　(1) Creating a stored procedure: Create procedure Stored procedure name (parameter of type in/out) begin select ' Output content '; End (2) Delete the creation process:drop procedure Stored procedure name, (3) Call stored procedure name (), (4) stored procedure can also define variables (declare i int), while, if-else, etc. The  9, ER diagram E-r method is the abbreviation for the entity-contact method (entity-relationship approach). 　　　　　　　　It is an effective method to describe the conceptual structure model of the real world. Solid type: The rectangle is indicated, the entity name;  attribute is indicated in the rectangle box: It is represented by an oval or rounded rectangle, and is connected with the corresponding entity by a non-oriented edge; The multivalued attribute is connected by two lines; The main attribute name is underlined;  contact: A diamond-shaped box indicating the contact name, and The non-aligned edges are connected with the entities concerned, and the type of contact on the non-edge side label   in the E-r diagram clearly indicates 1 to many relationships, 1 to 1 relationships and many-to-many relationships.   1 to 1 relationship in two entity line direction write 1;  1 to multi-relationship in 1 of the party write 1, many of the parties write n;  many-to-many relationship is in two entities connected in the direction of each write n,m 10, three paradigms (1) the first fan　　　　Formula (1NF): The fields in a database table are single attributes and cannot be divided. (2) Second normal form (2NF): A non-critical field in a database table does not have a partial function dependency on any of the candidate key fields. (In the second paradigm the combination of the primary key (AB) inside a or B and other fields can not have a combination of repetition) (3) The third paradigm (3NF): On the basis of the second paradigm, if there is no non-critical field in the data table for any of the candidate key field of the transfer function dependency is in accordance with the third paradigm. (There can be no combination of fields and fields in the third paradigm.)

MySQL Total review