PHP mobile Internet Development notes (8) MySQL database basic review [2]

1. In order to ensure data integrity and consistency, the data table also needs to use constraints (constraint), indexes (indexes) to specify the field name, field type, and field attribute when creating the table ), the primary key (primarykey) and the foreign key (foreginkey. Constraint: notnull non-null constraint unique uniqueness constraint primarykey primary key

1. In order to ensure data integrity and consistency, the data table also needs to use constraints (constraint), indexes (indexes) to specify the field name, field type, and field attribute when creating the table ), primary key and foregin key. Constraint: not null non-null constraint unique uniqueness constraint primary key

I. Data Tables

To ensure data integrity and consistency, you must specify the field name, field type, and field attribute when creating a table. Constraints, indexes, and ), primary key and foregin key.

Constraints:

Not null non-empty Constraint

Unique uniqueness Constraint

Primary key constraint

Foreign key constraint of foreign key

Check Constraints

Auto_increment: automatically identifies a column (the value is automatically increased by 1)

Create a table:

Modify Table:

Modify the table structure, such as modifying the column type, adding new fields, deleting original fields, and changing the table name.

Alter table user modify/add/drop/change/rename

Delete table:

Add data (add one or more data records at a time)

Query:

Modify Table data:

Delete table records:

When the field in the table is too long: writing is inconvenient. We can use as to alias the field:

Remove duplicate queries and use distinct

The query value is in a certain range: select * from user where age between... and ..

Select * from user where name in ('"lixq", "lxq ");

Fuzzy query:

%: 0 to multiple characters

_: Represents any character

Ii. Indexing

In database development, indexes can improve query optimization, ensure data uniqueness, and Optimize search for a large number of text in any full-text index field. Index classification: primary key index (primary key), unique index (unique), general index, full text index (fulltext );

Iii. Database Table types and storage locations

Mysql supports multiple data table types, such as MYISAM, InnoDB, and MEMORY.
The default data table type is specified by the default-table-type option in the mysql server configuration file. When creating a new data table, you can use the engine or type option to determine the data table type.
MYISAM: A table lock mechanism is used to optimize multiple concurrent read/write operations. myisam does not support transactions or foreign keys, which improves access speed, myisam is the best choice for select and insert-based applications. myisam tables may be damaged, and tables may not be accessible after being damaged. myisam provides a repair tool.
InnoDB: A transaction security storage engine with the ability to submit, roll back, and crash recovery. It also supports a foreign key mechanism. Unlike most databases, mysql has a storage engine concept, the optimal storage engine is available for different storage requirements. However, compared with the myisam storage engine, the processing efficiency is lower and the space occupied by multiple columns is high.
View the default storage engine:

View the storage engines supported by the current database

Generally, data tables are stored in the data file under the mysql installation directory.

Iv. Default character set of data tables

In a mysql database, you can set a different character set for the database, data table, or even each data column. If no character set is specified when you create a database using the create table command, it is determined by the character-set-server option in the mysql configuration file.

Modify the my. ini file in the directory

default-character-set=utf8character-set-server=utf8

If a set names gbk is added to windows to display Chinese characters, the display will be normal.