Summarize the MySQL statements learned today

Key words

• Enter Mysql:mysql-uroot-p
• View database: Show databases
• Enter the database: Use the ' S_name
• View tables in the database: show tables

Above in Doc demo, later in the tool to demonstrate the case of MySQL is not sensitive

• Creating a library: Create database if not EXISTS database ' S_name
• Creating table: Create table if not EXISTS table ' s_name (column name 1 property, column Name 2 property,......)
  

• 1 Create Table  if  not existsMyTable (2m_idINT PRIMARY KEYauto_increment,/* Set the m_id to type int to constrain the primary key (unique not duplicate NOT null) value to grow/3M_nameVARCHAR( -) not NULL,/* Set M_name to varchar length 20 constrained to NOT NULL * /4M_genderVARCHAR(4)DEFAULT 'male',/* Set M_genderFor a varchar length of 4 constraint for the default value of male*/
  5M_idnoVARCHAR( +)UNIQUE/* Set M_gender to a varchar length of 19 constraint as unique (no repetition can be null) */
   6 )default CHARSET=utf8/* Set the character encoding of the table * /

• To view the properties of a table: Desc table ' S_name
• View the contents of the table: SELECT * from table ' S_name
• Add table Structure (column): ALTER TABLE table ' s_name Add Column List property
• Modify table structure (columns): Alter tables table ' s_name change column name modified column Name property constraint if you modify only the column's data type or constraint, the column name of the modified column and the modified column name can be the same as the following: ALTER TABLE table ' s_name Modify column Name property constraint
• 1 alter table mytable change m_phone m_emil VARCHAR
• Delete table structure (column): ALTER TABLE table ' s_name drop column name
• 1 alter table mytable DROP COLUMN m_emil
• Copy table: CREATE table new table name SELECT * from data source table where 1=0; * Represents the entire content can be replaced by the required column where the 1=0 condition statement means that only the table structure is replicated
• CREATE INDEX: Creates index index ' s_name on TABLE ' s_name (column name 1, column name 2,....); indexes are typically created on a unique constraint after having a primary key
• Delete index: ALTER TABLE table ' s_name Drop index index ' s_name
• Set FOREIGN KEY constraint: constraint fk_table ' s_name_ column name foreign key (column name) referenges othertable ' s_name (associated column); othertable ' s_name (associative column) refers to the primary key in the primary table, and the primary key association of the foreign key and the primary table

Summarize the MySQL statements learned today