MySQL Common interview problem summary (i)

001 Database Application System Design

1. Planning

2. Demand Analysis

3. Conceptual model Design

4. Logical Design

5. Physical Design

6. Programming and debugging

7. Operation and Maintenance.

002 Creating a Database

CREATE datebase database_name

003 View the database

Show DATABASE

004 Select Database

Use database_name

005 Deleting a database

Dorp DATABASE database_name

006 View Supported Engines

Show engines;

007 viewing the default supported storage engine

Show VARIABLES like ' storage_engine% '

008 Creating a Table

Crate TABLE table_name (

Field_name Data_type,

Field_name Data_type,

...

Field_name data_type

)

009 View Table Definition

DESC[RIBE] table_name

010 Deleting a table

DROP TABLE table_name

011 Modifying the table

ALTER TABLE old_table_name RENAME [to] New_table_name

012 Add Field

ALTER TABLE table_name ADD field_name data_type

013 View table Detailed definition

Show CREATE TABLE table_name

014 Add fields to the first position in a table

ALTER TABLE table_name ADD field_name data_type

015 Add fields after the specified position in the table

ALTER TABLE table_name ADD field_name data_type after Field_name

016 Delete Fields

ALTER TABLE table_name Dorp field_name

017 modifying fields

ALTER TABLE table_name MODIFY field_name data_type

018 modifying field names

ALTER TABLE table_name Change old_field_name new_field_name Old_data_type

019 modifying field names and properties at the same time

ALTER TABLE table_name Change old_field_name new_field_name new_data_type

020 Changing the Order of fields

ALTER TABLE table_name MODIFY field_name_1 data_type [first]| [After Field_name_2]

021 field non-null constraint

Field_name Data_type not NULL

022 Field Default values

Field_name data_type DEFAULT Default_value

023 Setting a UNIQUE constraint

1, Field_name data_type UNIQUE

2, CONSTRAINT constraint_name UNIQUE (field_name)

024 PRIMARY KEY constraints

Field_name data_type PRIMARY KEY

025 multiple Field primary keys

CONSTRAINT constraint_name PRIMARY KEY (field_name_1, field_name_2, ...)

026 field values automatically increase

Field_name data_type auto_increment

027 Setting FOREIGN KEY constraints

CONSTRAINT constraint_name FOREIGN KEY (filed_name)

REFERENCES Other_table_name (Other_field_name)

028 Create a normal index when creating a table

[Express can be first]

| indicates selection

TABLE_NAME (

column_name

Index| KEY [Index_name] (Field_name [(Index_length)] [asc| DESC])

)

029 Creating an index on a table that already exists

1. CREATE INDEX index_name on table_name (

Field_name [(Index_length)] [asc| DESC])

2. ALTER TABLE table_name ADD index| KEY

Index_name (Field_name [(Index_length)] [asc| DESC])

030 Create a unique index when creating a table

TABLE_NAME (

column_name

UNIQUE index| KEY [Index_name] (

Field_name [(Index_length)] [asc| DESC])

)