Mysql23 a point of knowledge

1. It is a language of interpretation: write a sentence that does not require a complete compilation of execution.

2. 1. No "", string Using ' ' contains

3. A table has only one primary key, but a primary key can be a key combination of multiple fields

4. Entity integrity: An entity means a record. This completeness is to ensure that each record is not a duplicate record. It means something.

5. The four most common paradigms currently used in relational databases are:

First Paradigm (1NF) Second normal form (2NF) the third normal form (3NF) BCN paradigm (BCNF) When designing a database, the paradigm of minimum requirements that a database needs to meet is the first paradigm. The requirements table for the first paradigm cannot have duplicate fields, and each field can no longer be split. If there is a database even if the first paradigm is not satisfied, it can not be called a relational database. It is possible to create tables in this relational database only if the requirements of the first paradigm are met. On the basis of satisfying the first paradigm, the database can be refined in one step. Refinement can make the database meet the requirements of the second paradigm. The third paradigm and the BCN paradigm can be obtained by thinning in turn. 6.4.1 MySQL data type introduction MySQL database provides a variety of data types. Different types have their own characteristics, and the range of use is not the same. Also, the storage method is different. The 4.1.1 integer type is the most basic data type in the database. Both integer and smallint types are supported in standard SQL. In addition to supporting both types, the MySQL database extends support for tinyint, mediumint, and bigint. The range of values for various integer types, the number of bytes stored, and the characteristics. Integer byte number the range of unsigned number of the value range of signed number tinyint 1 0~255-128~127smallint 2 0~65535-32768~12767mediumint 3 0~16777215-8388608~8388607in T 4 0~4294967295-2147483648~2147483647integer 4 0~4294967295-2147483648~2147483647bigint 8 0~18446744073709551615- 9223372036954775808~9223372036854775807 7.

Insert Chinese garbled, change the character set of the table:

ALTER TABLE sudents CONVERT to CHARACTER SET UTF8;

8.Update student set id=1,name= ' Zhangsan ' limit 5,10 (excluding 5, starting from sixth 6-11)

8.

CREATE table score_new like score creates the same table, just no data

CREATE TABLE Score_bak as SELECT * From score Backup table

mysqldump-uroot-p123456 Data > Data.sql

9. Referential integrity: The value of a field in one table is the value of a field that is referenced from another table. The referenced table is the foreign key table, and the referenced table is the primary key table

10.

5. Impact on the operation

①. When adding data, first add the primary key table and add the foreign key table data
②. Delete the primary key table data before deleting the foreign key table data

Cascading actions: Not recommended: data integrity is compromised
Do nothing: The error is an error, the deletion is deleted
Cascade: Deletes the primary table record, and records that reference the value from the table are also deleted
Set NULL: Delete the primary table record, set the field value from the table to NULL, provided that it can be null
Set to default: Delete the primary table record, set the field value from the table to default, provided that the default

6. PRIMARY KEY constraint (PK Primary key) Unique key constraint (UQ unique) foreign KEY constraint (FK foreign key) default value constraint (DF default) Check constraint (CK check)

Mysql23 a point of knowledge