PHP mobile internet development notes (8)-MySQL database basic review [2] _ MySQL

PHP mobile internet development notes (8)-MySQL database basic review [2] bitsCN.com

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 % 20 table % 20 user % 20 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.

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.

BitsCN.com