A review of MySQL database basics

One, data table

To ensure data integrity and consistency, you specify field names, field types, and field properties when you create a table, using constraints (constraint), indexes (index), primary keys (primary key), and foreign keys (Foregin key).

Constraint conditions:

NOT NULL Non-empty constraint

Unique uniqueness Constraint

PRIMARY KEY PRIMARY KEY constraint

FOREIGN key FOREIGN KEY constraint

Check checking constraint

Auto_increment Automatic identity column (value automatically increases by 1)

To create a table:

To modify a table:

Modify the structure of the table, such as modifying the type of column, adding new fields, deleting existing fields, changing table names

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

To delete a table:

Add data (add one at a time and add multiple at a time)

Inquire:

To modify table data:

To delete a record from a table:

When the field in the table is very long: writing is inconvenient, we can use as to give the field an alias:

Remove duplicates in query, use distinct

Query values are in a range: SELECT * from user where age between ... and..

The value of the query in the collection that gives the value: SELECT * from user where name in (' "Lixq", "Lxq");

Fuzzy query:

%: represents 0 to more characters

_: Represents an arbitrary character

Second, index

In database development, indexes can improve query optimization, ensure the uniqueness of data, and can optimize the search for a large number of text in any full-text indexed field: Primary key index (primary key), unique index (unique), General index (index), Full-text index ( Fulltext);

Third, database table types and storage location

MySQL supports a variety of data table types such as Myisam,innodb,memory

View the current database support storage engine

The data file is stored in the MySQL installation directory in general.

Four, the default character set of data table

In a MySQL database, you can set a different character set for a database, a datasheet, or even each data column, and when you create a database using the Build Table command, you don't specify any character set, is determined by the Character-set-server option in the MySQL configuration file.

Modify the My.ini file in the directory

?

1 2

<strong><strong><strong><strong><strong><strong><strong><strong ><strong><strong><strong><strong><strong><strong><strong>< Strong><strong><strong><strong><strong>default-character-set=utf8 character-set-server=utf8</strong></strong></strong></strong></strong></ strong></strong></strong></strong></strong></strong></strong></ strong></strong></strong></strong></strong></strong></strong></ Strong>

There is a problem with displaying Chinese in windows, plus a set names GBK display will be normal