MySQL base + master-slave replication, read/write separation, high availability principle

1) Create database wpj1105;

2) Create database wpj1105;

3) Determine if there is a library: drop database if exists wpj1105;

4) Use the database using wpj1105; 5) #显示数据库中的表 show tables;

6 "CREATE TABLE create table  Student ( id int auto_increment primary key, name varchar ( 50 Span style= "font-family: Song body; Font-weight:bold; font-size:16px; " >20 ),  date varchar ( 50 ),  content varchar ( 100

7) Lookup Table select xxxxx from student;

8) The structure of the display table: DESC student;

9) Add data insert into student values (null, ' AA ', ' Male ', ' 1988-10-2 ', ' ... ');

Ten) to modify the encoding of a data table : ALTER TABLE ' test ' DEFAULT CHARACTER SET UTF8

One ) ALTER table ' test ' change ' name ' "Name ' VARCHAR (CHARACTER) set UTF8 not NULL; This command is used to encode the Name field in table test to UTF8

12) Modify a data update student set sex= ' man ' where id=4;

Delete from student where id=5;

14 Delete data delete from  student where id= ;&NBSP;15) # and  and select * from student where date> ' 1988-1-2 '  and  date< ' 1988-12-1 ';  16) # or  or    select * from student  where date< ' 1988-11-2 '  or date> ' 1988-12-1 ';  17) #between     select * from student where date between  ' 1988-1-2 ' and ' 1988-12-1 ';

18) Modify Data Update C set age= id=2; update c set name= ' Flower ', age=21 , sex= ' female ' where id=2

Views: A view is a result set that is returned after a SELECT statement executes. So when we create the view, the main work falls on the creation of this SQL query statement.

Use occasions: Permission control, do not want users to access some of the table contains sensitive information columns, such as salary ... The key information comes from a number of complex related tables, can create a view to extract the information we need to simplify the operation;

For example, there are three tables, table one: Student name, school number, address, table II: Account ID, account name, corresponding course, Table III: Student ID, corresponding account ID. At this point if I want to find a student's information:

SELECT ' UC '. ' ID ' as ' id ', ' u '. ' Name ' as ' username ', ' C '. ' Name ' as ' coursename ' from ' User ' U ' left JOIN ' user_c Ourse ' UC ' On (' U ', ' id ' = ' UC '. ' userid ')) left JOIN ' course ' C ' on (' UC '. ' CourseID ' = ' C '. ' ID ')) WHERE U. ' Name ' = ' Xiao Zhang '

If you create a view:

Algorithm ' view_user_course ' user_course ' UC ' (' U '. ' id ' course ' C ' (' UC '. ' CourseID '

If you need a query statement at this point:

SELECT vuc.username, vuc.coursenamefrom view_user_course vucwhere vuc.username = ' Xiao Zhang '

Algorithm=undefined: Specifies the processing algorithm of the view;

definer= ' root ' @ ' localhost ': Specifies the creator of the view;

SQL Security Definer: Specifies how security is validated when the view queries data;

Can be done, to say that the view is a reference to a number of basic tables, a virtual table, query the results of the execution of the statement, do not store the specific data (the basic table data has changed, the view will be changed), but it does exist in the database, so for the table and view in the database if there is no good distinction,

There are two ways to implement a view, namely the merge algorithm and the temporal table algorithm, which refers to merging the view-defined SQL into the query SQL when querying the view, such as CREATE VIEW v1 as SELECT * from user where sex=m; when we want to query the view, MySQL will select Id,name from v1; Merge to select Id,name from user where sex=m ...; The temporal table algorithm is to save the view data to a temporary table, check the temporary table when querying. Both the merging algorithm and the Temporal table algorithm bring additional overhead, and if you use a temporary table it can make MySQL optimizations difficult, such as indexing. And the view introduces some other problems that make the logic behind it very complex. So the view is seldom used now.

Index: First declare that the index is for a column in a data table, divided into two types: single-column index and composite index. It is used to quickly identify rows that have a specific value in a column.

MySQL Common indexes are: primary key index, unique index, normal index, full-text index, composite index

PRIMARY key (primary key index) ALTER TABLE ' table_name ' ADD PRIMARY KEY (' col ')

Unique (unique index) ALTER TABLE ' table_name ' ADD UNIQUE (' col ')

Index (normal index) ALTER TABLE ' table_name ' ADD INDEX index_name (' col ')

         fulltext (full-text index)      alter TABLE ' table_name ' ADD fulltext (' col ')
         Combination index    ALTER TABLE ' table_name ' ADD index index_name (' col1 ', ' col2 ', ' Col3 ')  

     CREATE INDEX:
       1. The search is often required on columns that can speed up searches;
      2. on the column that is the primary key, enforces the uniqueness of the column and arranges the structure of the data in the organization table;
      3. often used on connected columns, these columns are mainly foreign keys that can speed up the connection;
      4. You often need to create an index on a column that is searched by scope, because the index is sorted and its specified range is contiguous;
      5. The indexes are often created on columns that need to be sorted, because the indexes are sorted so that the query can use the sorting of the indexes to speed up the sort query time;
      6. often used in where

     Advantages and disadvantages of indexes
     Advantages:
      1. by creating a unique index, you can guarantee the uniqueness of each row of data in a database table.
      2. Can greatly speed up the retrieval of data, which is the main reason for creating indexes.
      3. The connection between tables and tables can be accelerated, particularly in terms of achieving referential integrity of data.
      4. When you use grouping and sort clauses for data retrieval, you can also significantly reduce the time to group and sort in queries.
      5. By using an index, you can improve the performance of your system by using an optimized hidden device during the query.

Disadvantages:
1. It takes time to create indexes and maintain indexes, and this time increases as the amount of data increases.
2. The index needs to occupy physical space, in addition to the data table to occupy the data space, each index also occupies a certain amount of physical space, if you want to establish a clustered index then the space will be larger.
3. when the data in the table is added, deleted and modified, the index should be maintained dynamically, thus reducing the maintenance speed of the data.

because indexes are very memory-indexed, they need to be added with caution, and those fields need to be indexed.

Engine:

There are two main engines of MySQL: InnoDB and myiasm.

the approximate differences between the two storage engines are shown in:
1) InnoDB support transactions, MyISAM not supported, this is very important. Transactions are an advanced way of handling, such as in some column additions and deletions, as long as the error can be rolled back to restore, and MyISAM will not be.
2) MyISAM suitable for query and insert-based applications, InnoDB suitable for frequent modification and involving high-security applications
3) InnoDB support foreign key, MyISAM not support
4) from MySQL5.5.5 onwards, InnoDB is the default engine
5) InnoDB does not support index of Fulltext type
6) InnoDB does not save the number of rows in the table, such as SELECT COUNT (*) from table, InnoDB needs to scan through the entire table to calculate how many rows, but MyISAM simply reads out the number of rows saved. Note that when the COUNT (*) statement contains a where condition, MyISAM also needs to scan the entire table
7) for self-growing fields, InnoDB must contain only the index of the field, but you can establish a federated index with other fields in the MyISAM table
8) When emptying the entire table, InnoDB is a one-line deletion, which is very inefficient. MyISAM will rebuild the table.
9) InnoDB supports row locks (or, in some cases, lock whole tables, such as Update table set a=1 where user like '%lee% '

MySQL base + master-slave replication, read/write separation, high availability principle