MYSQ Database Common

1. Create a database table:

CREATE TABLE table_name (fields_name1 datatype,fields_name2 datatype ...);

2. View the table:

Show tables;

3. Create a table with the main health constraint (including Tangokian and multi-character types)

1. Single KEY constraint

Field name data type, primary key[default value]

2. Specify the master after all columns have been defined

Constraint < constraint name >primary key[default value]

4. Multi-field Federated primary key

The main jian is composed of several fields together

。 Grammar:

Primary key[Field 1, Field 2, ...]

5. Using FOREIGN KEY constraints

A foreign key is used to establish a link between data in two tables, which can be one or more columns. A table can have one or more foreign keys. Foreign keys correspond to referential integrity, a table's foreign keys can be null, and if not NULL, each foreign key value must be equal to a value of the primary key in another table.

FOREIGN key: First it is a field in the table, it can not be the primary key of this table, but corresponds to the primary key of the other table. The primary role of foreign keys is to guarantee the integrity of data references, and after defining an external key, it is not allowed to delete rows that have association relationships in another table. The role of foreign keys is to maintain data consistency, integrity.

Primary table (parent table): For two tables with associated relationships, the table that contains the primary key in the associated field is the primary table.

From a table (child table): For two tables with associated relationships, the table that contains the foreign key for the associated field is from the table.

Grammar:

Constraint <>foreign key Field 1, "Field 2", references "Main Table name" main column 1, primary key column 2 ...

The foreign key name is the name of the defined foreign KEY constraint, and a table cannot have a foreign key with the same name; The field name represents the field column where the child table needs to add a foreign key constraint; The primary table name is the name of the table on which the foreign key is dependent;

Association refers to the connection between related tables in a relational database. It is represented by a compatible or identical property or group of attributes. The foreign key of the child table must be associated with the primary key of the parent table, and the data type of the associated field must match. If the type is different, an error occurs when the child table is created.

6. Use non-null constraints

A non-null constraint is that the field value cannot be empty.

Field name data type NOT NULL

7. Use of uniqueness constraints

Using uniqueness constraints The unique constraint requires that the column be unique. Allowed to be empty, but only one null value can appear. A unique constraint ensures that a column or columns do not have duplicate values.

(1) Specify a unique constraint directly after the column is defined.

Field name data type unique

(2) Specify a UNIQUE constraint after all columns have been defined.

Constraint < constraint name >unique < field name >

Unique and primary key differences: A table can have more than one field declared unique, but only one primary key declaration; The column declared as primary key is not allowed to have a null value. However, a field declared as unique allows null values to exist.

8. Using Default Constraints

Default constraint Specifies the default value for a column. such as male students more, gender can be the default male. If you do not assign a value to this field when inserting a new record, the system will automatically assign a value to the field as a male.

Field name data type default defaults

9, set the property value of the table automatically increase

In a database application, you often want the system to automatically generate primary key values for a field each time a new record is inserted. You can do this by adding the Auto_increment keyword to the table primary key. Default, auto_incrtement constraints in MySQL. And the field must be part of the primary key. Auto_increment. The field of the constraint can be any integer type (tinyint,smallint,int,bigint).

Syntax rules:

Field name data type Auto_increment

4.2 Viewing the data table structure

1. View the table BASIC Structure statement describe TABLE_NAME or DESC table name;

2. View table Detail Structure Statement show CREATE TABLE

Show CREATE table < table name >\G;

4.3 Modifying data tables

1. ALTER TABLE < old table name > Rename to < new table name >;

4.4 Modifying the data type of a field

Alter table < table name > Modify < Field name >< data type >

4.5 Modifying field names

Alter Table name change old field name new data type for new field name;

4.6 Adding fields

Alter Table name add new field name data type constraint first after existing field name

4.7 Delete a field

Removing a field removes a field from a table in a data table

Alter Table Name drop field name;

4.8 Alignment position of the center of repair field

Alter Table name Modify field 1 data type First|after Field 2

4.9 Changing the table's storage engine

ALTER TABLE name engine= the changed storage engine name;

4.10 Delete a FOREIGN KEY constraint for a table

Alter table Name drop FOREIGN key FOREIGN KEY constraint name.

4.11 Deleting a data table

Drop table if exist table 1, table 2, table 3 ... Table N;

4.12 Deleting a primary table associated with another table

In the case of a foreign key association between data tables, if you delete the parent table directly, the result will show a failure. The reason is the direct deletion, which destroys the referential integrity of the table. If you have to delete, you can delete the child table associated with it, and then delete the parent table, just to delete the data from the two tables at the same time. In some cases, however, you might want to keep the child table, so that if you want to delete the parent table separately, you can simply cancel the foreign KEY constraint for the associated table and then delete the parent table.

MYSQ Database Common