MySQL Learning Advanced

The default database engine before storage Engine Myisammysql 5.0 is most commonly used. has a high insertion, query speed, but does not support the transaction InnoDB transactional database of the preferred engine, support acid transactions, support row-level locking, MySQL 5.5 has become the default database engine-- The MyISAM table is locked in the inserted entire table. InnoDB only locks the record. All if you perform a large number of update and insert, you should use InnoDB, or MyISAM if you are primarily performing select. BDB from Berkeley db, another option for transactional databases, support for other transactional features such as commit and rollback memory storage engine that all data is placed in the RAM, with extremely high insertion, update and query efficiency. However, it occupies a memory space that is proportional to the amount of data. and its contents will be lost when MySQL restarts. Merge will combine a certain number of MyISAM tables into a whole, very useful in ultra-large data storage archive is ideal for storing large numbers of independent, historical data. Because they are not often read. Archive has an efficient insertion speed, but its support for queries is relatively poor federated unites different MySQL servers and logically makes up a complete database. Ideal for distributed applications cluster/ndb high-redundancy storage engines, combined with multiple data machines to provide services to improve overall performance and security. Suitable for applications with large data volumes, security and performance requirements CSV: A storage engine that logically divides data by commas. It will create a. csv file for each data table in the database subdirectory. This is an ordinary text file, where each row of data occupies a line of text. The CSV storage engine does not support indexing. Blackhole: Black hole engine, any data written will disappear, generally used to record binlog do replication relay example storage engine is a stub engine that does nothing. It is intended as an example of MySQL source code to demonstrate how to start writing a new storage engine. Similarly, its main interest is to developers. The EXAMPLE storage engine does not support indexing. MySQL data type

(1) Numeric type

(2) String type

(3) Date and time type

SQL language

Constraints

Constraint type:	PRIMARY Key	Default Value	only	FOREIGN Key	Non-empty
Key words:	PRIMARY KEY	DEFAULT	UNIQUE	FOREIGN KEY	Not NULL

The primary key (PRIMARY key) is used to constrain a row in the table, and as an identifier for this row, the primary key is the key to the exact row in one table, and the primary key is important. The primary key requires that the data for this row cannot be duplicated and cannot be empty.

There is also a special primary key-the composite primary key. A primary key can be a column in a table, or it can be identified by two or more columns in a table

The default value constraint specifies what to do when there is a column with the default constraint and the Insert data is empty.

The default constraint will only be reflected when using the INSERT statement (as described in the previous experiment), where the position of the default constraint will be populated with the value of default if there is no value in the INSERT statement

A unique constraint is simple, which specifies that the value of a column specified in a table must not have duplicate values, that is, each value in this column is unique.

Insert fails if there is a unique constraint when the newly inserted data and the existing data are duplicated by the INSERT statement.

The foreign key (FOREIGN key) ensures both data integrity and the relationship between tables.

A table can have multiple foreign keys, and each foreign key must references (reference) the primary key of another table, the column that is constrained by the foreign key, and the value must have a corresponding value in the column it references.

Insert fails if the value of the foreign KEY constraint does not correspond in the reference column, such as the following command, if there is no DPT3 in the Reference column (Dpt_name of the Department table) at insert

A non-null constraint (NOT NULL), which can be understood by a name, is a non-null constrained column that must not be empty when inserting a value.

Violations of non-null constraints in MySQL, will not error, there will only be warnings.

CREATE DATABASEMysql_shiyan; UseMysql_shiyan;CREATE TABLEDepartment (Dpt_nameCHAR( -) not NULL, People_numINT(Ten)DEFAULT 'Ten',  CONSTRAINTDpt_pkPRIMARY KEY(dpt_name) # #约束名 DPT_PK, PRIMARY KEY constraint);CREATE TABLEEmployee (IDINT(Ten)PRIMARY KEY, nameCHAR( -), ageINT(Ten), SalaryINT(Ten) not NULL, PhoneINT( A) not NULL, IN_DPTCHAR( -) not NULL,  UNIQUE(phone),CONSTRAINTEmp_fkFOREIGN KEY(IN_DPT)REFERENCESDepartment (dpt_name) # #外键约束); CREATE TABLEProject (Proj_numINT(Ten) not NULL, Proj_nameCHAR( -) not NULL, start_date date not NULL, end_date dateDEFAULT '2017-08-15', OF_DPTCHAR( -)REFERENCESDepartment (Dpt_name),CONSTRAINTProj_pkPRIMARY KEY(Proj_num,proj_name));

ExampleTable Action 1, creating a table CREATE TABLE 表名 （属性名 数据类型 [完整性约束条件]， 属性名 数据类型 [完整性约束条件]， 属性名 数据类型 [完整性约束条件]）2. View Table Structure

Describe table name

3. View table structure

show createtable表名

show createtable表名 \G

4. Modify Table name

altertable旧表名 rename [to] 新表名；

5. Modify the data type of a field alter table 表名 modify 属性名 数据类型； 6. Modify field names

altertable表名 change 旧属性名 新属性名 [新数据类型]；

7. Add Field

altertable 表名 add 属性名1 数据类型 [完整性约束条件] [first] after属性名2；

8. Delete a field

altertable 表名 drop属性名；

9. Delete the associated table

(1) Delete a foreign key constraint for a table
A foreign key is a special field that associates a table with its parent table. When creating a table, the FOREIGN KEY constraint is already set. The following statement is required to remove the association between them.

alter table 表名 drop foreign key外键别名；

(2) Delete a normal table that is not associated drop table 表名； (3) Delete the parent table that is associated with another table

When you delete a table related to a relationship, the drop table example1 will cause an error because a foreign key depends on the table

For example, if you create a example4 table that relies on the example1 table, the foreign key of the Example4 table stu_id depends on the primary key of the example1 table. Example1 table when the parent table of the Example4 table.

If you want to delete the Example4 table, you must first remove this dependency. The simplest way is to delete the child table Example4 First, and then delete the parent table example1. However, this may affect other data in the child table.

Another method is to remove the foreign key constraint for the child table first, and then delete the parent table. This method does not affect the other data of the child table, which can guarantee the security of the database.

For example, the foreign key alias of the Example4 table is D_FK, and the foreign KEY constraint of the example4 is removed

altertable example4 drop foreign keyd_fk;

You can see if it has been deleted by show create TABLE Example4 \g.
And then execute drop tableexample1;

Successful execution indicates that the operation was successful.

Help

\h View Help

\s Viewing the current environment

\c Discard Current Input command

Create a user and authorize

CREATE USER ' username ' @ ' host ' identified by ' password ';

GRANT {All | SELECT | INSERT | UPDATE} on Databasename.tablename to ' username ' @ ' host '

Examples used in work:

BEGIN      DECLAREwin_idVARCHAR( -); DECLAREWin_countVARCHAR( -); SELECTId fromWindows_vps_logORDER  byIdDESCLIMIT1  intowin_id; SELECT Count(ID) fromWindows_vps_log intoWin_count; IFWin_count>5000000  Then        Delete  fromWindows_vps_logwhereId<=win_id-3000000; END IF;END

Stored Procedures

SELECTMachine_host, CONCAT (LAN_IP,':', ' Port ') as"Problem machine", Dial_time fromClient_infoWHEREmacinch (        SELECTmac fromWindows_vps_logWHEREInvalid= 0         andTimestampdiff (MINUTE, Create_time, now ())>  the    )ORDER  byMachine_hostdesc；

Multi-Table Query

MySQL Learning Advanced