MySQL crud operations + usage view

"0" README 0.1) This part of the text described in the "MySQL must Know", designed to review "MySQL data retrieval + query + Full-text Search" basic knowledge;
"1" Insert data 1) Insert is used to insert (or add) rows into a database table. The insert can be used in several ways (methods):

M1) insert a full line; m2) insert a portion of the line; m3) insert multiple lines; M4) Insert the results of some queries;

2) Insert the full line

< strong>2.1) for a column that belongs to Auto_increment, we replace it with insert null; 2.2) Write a security method for inserting rows: to specify the Insert column name, followed by the values of each value, and not just insert values; 2.3) Omit columns: If the table definition allows, you can omit some columns from the insert operation. The ellipsis column must meet one of the following conditions (conditions)

C1) The column is defined to allow null values;

C2) gives the default value in the table definition, which means that if the value is not given, the value is used;

2.4) Improve overall performance: the database is often accessed by multiple clients, and the task of managing what requests and in what order Processing is MySQL. If data retrieval is important, you can instruct MySQL to lower the priority of the INSERT statement by adding the keyword low_priority between insert and into; (such as insert low_priority into);

3) Insert multiple lines
Attention) Improve insert performance:MySQL handles multiple inserts with a single INSERT statement faster than using multiple INSERT statements, so it is recommended to use the Insert method as shown;
4) Insert the retrieved data

4.1) Intro: The data from select is used as the raw data for insert insertion;

"2" Update and delete data 1) Update data

1.0) The UPDATE statement consists of 3 parts: The table to be updated, the column name and their new value, the filter condition that determines the row to update, 1.1) updates a single column and updates multiple columns

1.2) Ignore keyword: if more than one row is updated with the UPDATE statement and an error occurs when one or more rows are updated in those rows, the update operation is canceled. Even if an error occurs, continue with the update, you can use the Ignore keyword (update ignore product)

1.3) in order to delete the value of a column, you can set it to null (assuming that the table allows null values); (Picture t85)

2) Delete data Attention) Delete Row using Delete, delete column using update (NULL);
3) Update and delete guidelines (rules), following are the habits that many SQL programmers follow when using update and delete;

R1) never use UPDATE and DELETE statements without a WHERE clause, R2) to ensure that each table has a primary key unless it is really intended to update and delete each row; R3) before using the WHERE clause with the UPDATE and DELETE statements, you should test with select to ensure that it filters the correct records in case the WHERE clause being writtenis incorrect; R4) Use a database that enforces referential integrity, so that MySQL does not allow the deletion of rows that have data associated with other tables;

"3" Creating and manipulating tables 1) Create a table

1.1) If you want to create a table when it does not exist, you should give the if not EXISTS statement before the indication.
1.2) Use null value
Attention) understand null: do not confuse null value with empty string, null value is no value, it is not an empty string. If you specify "(two single quotation marks, in which no characters are in between), this is allowed in the NOT NULL column; The empty string is a valid value, it is not a value; 1.3) Primary key re-introduction: Single primary key and composite primary key;
1.4) Use auto_increment: auto increment; Attention)

A1) Determine the value of Auto_increment: One disadvantage of having MySQL generate a primary key is that you don't know who these values are;

A2) How to use Auto_increment Column when you get this value? Select LAST_INSERT_ID () to obtain;

1.5) Specify default value: If no value is given when inserting a row, MySQL allows you to specify the default value to use at this time. The default value is specified with the default keyword in the column definition of CREATE table;

1.6) Engine type:
1.6.1) Intro to Engine:MySQL has an internal engine that specifically manages and processes data. When you use the CREATE TABLE statement, the engine creates the table specifically, and when you use a SELECT statement or other DB processing, the engine processes your request internally; 1.6.2) If the engine statement is omitted, the default is MyISAM; (Dry----it is strongly recommended to add engine keywords to specify database table engines) 1.6.3) need to know the following several MySQL engines:

engine1) InnoDB: is a reliable transaction processing engine that does not support full-text search;

engine2) MEMORY: functionally equivalent to MyISAM, But because the data is stored in memory,

it is very fast;

engine3) MyISAM:MyISAM is a high performance engine that supports full-text search, but does not support transactional processing;

Attention) foreign keys cannot cross the engine, even if a table with an engine cannot reference a foreign key with a table that uses a different engine;

2) The following discussion is about updating tables, deleting tables, and renaming the contents of tables

the following is transferred from: http://blog.163.com/zhangjie_0303/blog/static/99082706201191911653778/

ALTER table: Add, modify, delete table columns, constraints, and other table definitions.

View columns: DESC table name; ALTER TABLE T_book Rename to BBB; Add columns: ALTER TABLE name add column name varchar (30); Delete column: ALTER TABLE name drop COL umn column name; modify column name Mysql:alter table BBB change nnnnn hh int; Modify column name Sqlserver:exec sp_rename ' t_student.name ', ' nn ', ' column '; Modify Column name oracle:lter table BBB Rename column nnnnn to hh int; Modify column properties: ALTER TABLE T_book modify name varchar (22);

For spec relative info, please visit MySQL add column + Modify column + Delete column

"4" Use View 1) Intro to view: view is a virtual table. Unlike a table that contains data, a view contains only queries that dynamically reduce data when used;

1.1) If the entire query is packaged as a virtual table named Product_vendor, you can easily retrieve the same data as follows;

1.2) This is what the view does:Product_vendor is a view that, as a view, does not contain any columns or data that should be in the table, it contains a SQL query; The view contains a SQL query)

2) Why use views? (reasons)

R1) reuse of SQL statements; R2) simplifies complex SQL operations; R3) uses the components of the table rather than the entire table; R4) to protect data; R5) After you create a view, you can use it in the same way as the table. You can perform select operations on the view, filter and sort operations, connect views to other views or tables, and even add and update data;

Attention View is simply a facility for viewing data stored elsewhere, and the view itself contains no data, so the data they return is retrieved from other tables. When you add or change data in these tables, the view returns the changed data; 3) Some limitations of view usage (restriction)

R1) There is no limit to the number of views that can be created; R2) in order to create a view, you must have sufficient access rights; R3) views can be nested, That is, you can construct a view by using a query that retrieves data from other views; R4)The order by can be used in the view, but if the SELECT statement that retrieves data from the view also contains an order by, the order by in that view will be overwritten; R5) views cannot be indexed, or have associated triggers or default values; R6) views can be used with tables;

4) Working with views

4.1) view is created withthe CREATE VIEW statement, 4.2) uses the show create VIEW viewname to view the statement that created the view; 4.3) Use drop to delete the view, whose syntax is drop view viewname; 4.4) When updating the view, you can use the drop and create again, or you can use the Create or replace vie directly W. If the view that you want to update does not exist, the 2nd UPDATE statement creates a view, and if the view that you want to update exists, the 2nd statement replaces the original view;

"4.1" uses views to simplify complex joins "4.2" reformat the retrieved data with a view
Attention) is like using a view as a database table, including filtering data and using views and calculated fields;
"4.3" Update view 1) Update a view to update its base table: If you add or delete rows to the image, you are actually adding or deleting rows to their base tables; 2) However, not all views are updatable. Basically, if MySQL does not correctly determine which base data is being updated, it does not allow updates (including insertions and updates); This also means that updates (operations) cannot be made if the following actions are defined in the view:

O1) grouping (using group by and having); O2) coupling; O3) subquery; O4) o5) aggregation function (min () function, count () function, sum () function, etc.); O6)distinct; O7) export (calculate) column;

Attention) view is mainly used for data retrieval, rather than to update data;

MySQL crud operations + usage view